Problem Adding Entries to MediaLinkTable #media #views #citations #sources

I don’t usually post a page here requesting help, but I’ll make an exception in this case. I have created a query that adds rows to the MediaLinkTable. The purpose of the added rows has to do with the fact that I’m an extreme source splitter and therefore I link media files to RM’s Master Sources and not to RM’s Source Details. I now find that because of the way I would like my data to appear in a new product called GedSite, it would be convenient for the same media files to be linked to all the Source Details associated with each Master Source.

It seems like a query that’s simple enough. After running the query, the MediaLinkTable looks perfect from SQLiteSpy. After running the query, the data look perfect from the RM user interface. After running the query, the data looks perfect in GEDCOM I export for use with GedSite. And after running the query, the data looks perfect in GedSite (well, not quite perfect – there is some duplication of some of the data, but that’s acknowledged to be a bug in GedSite and not a problem in the GEDCOM). So what’s the problem? After running the query, I’m no longer able to tag any media files in RM to any kind of object to which media files can be tagged.

When I say that after running the query that the data looks perfect in the RM user interface, I mean the following. There are several ways to look at RM’s media tags, and for example you can do it from the Media Gallery. If you highlight a particular media item, you see all the tags in a panel on the right side of the screen. Tags to Master Sources manifest in the list as tags to “sources” and tags to Source Details manifest in the list as tags to “citations”. After running the query, all the tags are there, both the tags to “sources” that were there before running the query and tags to “citations” that were added by the query.

A minor glitch that I don’t think has anything to do with anything is that the MediaLinkTable is an indexed table, and you have to run RM’s File->Database tools->Rebuild Indexes tool to rebuild the indexes after running the query, but I have done so.

The symptom when trying to add new media tags from the RM user interface is that doing so initially looks like it has worked, but then what appears to be the newly added tag hasn’t been added after all. There is no error message. This is a wild shot in the dark, but I have the sense that my query needs to do something to “close out” or “commit” what it has done, and I’m probably missing something obvious. So any advice would be most appreciated. The text of my query follows. The rather curious looking condition AND S.Name LIKE(‘*%’) is because all my extremely split sources have a name that starts with an * and all my older lumped sources have a name that doesn’t start with an *. I only want to apply this query to the extremely split sources. The condition ON ML.OwnerType = 3 serves to select only existing media links to Master Sources because those are the ones I want to replicate as links to Source Details.


P.S. This query needs to do an update on a JOIN and SQLite does not support doing an update on a JOIN, or at least not directly. You can still do an update on a JOIN in SQLite by hiding the JOIN in a sub-query or by hiding the JOIN in a view. I have hidden the JOIN (several of them, actually) in a view.


-- Create media links for all citations whose master sources have media links,
-- making the media links for the citations match the media links for the master sources.
DROP VIEW IF EXISTS CitationsNeedingMedia;
CREATE TEMP VIEW CitationsNeedingMedia AS
SELECT C.CitationID AS OwnerID, S.SourceID, S.Name, ML.MediaID, M.MediaFile, 4 as OwnerType,
       ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4,
       ML.SortOrder, ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom, ML.Note,
       ML.Caption, ML.RefNumber, ML.Date, ML.SortDate, ML.Description
FROM CitationTable AS C
     SourceTable AS S ON C.SourceID = S.SourceID AND S.Name LIKE('*%')
     MediaLinktable AS ML ON ML.OwnerType = 3 AND ML.OwnerID = S.SourceID
     MultimediaTable AS M ON ML.MediaID = M.MediaID
ORDER BY S.Name, C.CitationID;
INSERT OR ROLLBACK INTO MediaLinkTable (MediaID, OwnerType, OwnerID,  -- These are the "new" items. In particular
                                                                      -- OwnerType must be 4 and OwnerID must be the CitationID
                                        IsPrimary, Include1, Include2, Include3, Include4,
                                        SortOrder, RectLeft, RectTop, RectRight, RectBottom, Note,
                                        Caption, RefNumber, Date, SortDate,Description)
SELECT MediaID, OwnerType, OwnerID,
       IsPrimary, Include1,  Include2, Include3,  Include4,
       SortOrder, RectLeft,  RectTop,  RectRight, RectBottom, Note,
       Caption,   RefNumber, Date,     SortDate,  Description
FROM CitationsNeedingMedia;

Discussions & comments from Wikispaces site


RootsMagic needs to close and reopen the database

06 February 2017 22:11:05

I see the same behaviour, Jerry. Closing and reopening RootsMagic clears the blockage; I expect it is simply a matter of closing and reopening the database. RM must set some internal counter or pointer on one of the affected tables to its last rowid and when it tries to append a record at that pointer + 1, it hits a SQLite error which it does not report. Your script has already created a row at that point and it has to be unique.



07 February 2017 03:21:04

Thanks. I guess I thought I had already tried the close and open RM trick, but I’ll try it again. In the meantime, here is a question – is this an issue you have encountered before? Which is to say is the same thing likely to happen with any script that adds rows to a table? Most of my queries have been just reports not updates. And my few updates have usually updated a row in place rather than adding new rows. So this is sort of new territory for me.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.