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.

Jerry

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
          JOIN
     SourceTable AS S ON C.SourceID = S.SourceID AND S.Name LIKE('*%')
        JOIN
     MediaLinktable AS ML ON ML.OwnerType = 3 AND ML.OwnerID = S.SourceID
        JOIN
     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