MediaTags+-+Copy+Shared+Facts+Media+To+Sharee+Personal

This script responds as a workaround to a 13 Dec 2011 wish expressed by MarkVS, that RootsMagic's Media Album/Gallery and other Media dialog windows that show MediaTags should also show the indirect tags to shared events for the Persons having non-Principal roles, sometimes called "sharees" and the shared event in their context a "sharee event". To do so can only be done by programming changes; one year later, nothing has changed. A workaround is to generate Personal mediatags from the shared event's media for these non-Principal sharees. That's what this script does.

Considerations:
 * 1) Assuming the shared event's mediatag is Primary for that event and Included in Scrapbook, so, too is each inherited mediatag for each sharee event. Therefore, the script generates a Primary-No, Scrapbook-No mediatag for the Person to avoid conflict with the truly Personal and other script-generated Personal mediatags and so that the Person Scrapbook does not receive duplicates.
 * 2) These workaround mediatags probably should have some unique property that would distinguish them from regular mediatags and the indirect ones that may appear if and when RootsMagic is enhanced to display them. The current script does so, perhaps too subtly, by adding a sentence to the Comment field in the Media Tag dialog window (Description field in MediaLinkTable). There are other techniques that might be better suited for future script operations - e.g., an apparently unused Note field in MediaLinkTable or wrapping the sentence in some distinct string sequence such as the "{ **...** }" pairing used for distinctive privacy flags in Facts - Split Shared to Individual (note that they do not function as privacy flags in the MediaTag Comments/Description field because it is not outputted in any report anyway).
 * 3) If someone uses Facts - Split Shared to Individual in combination with this script, there certainly will be real duplicate mediatags for each person, one is this workaround Personal tag, the other is the tag for the newly created Individual event. A procedure is needed to delete the duplicate Personal ones. It could rely on a more distinctive branding as discussed in 2) or simply look for basic matches with mediatags for events as in MediaTags - Delete Personal Having Fact Duplicates.

2013-01-05 1.1 outputs media tag for sharees having no defined role in the event. Sentence in tag description reads "Shared in the ...". 2012-12-28 V1 complete - tags for both family and non-family shared events 2012-12-27 V0 creates mediatags for shared individual events, not for shared family events



code format="sql" -- MediaTags-CopySharedFactsMediaToShareePersonal.sql /* 2012-12-27 Tom Holden ve3meo 2012-12-28 V1 complete 2013-01-05 1.1 outputs media tag for sharees having no defined role in the event. Sentence in tag description reads "Shared in the ...".

Creates a media tag, for media already tagged to a shared event, to the Persons sharing the event. The Description contains the original Description appended with the Person's role name, the event the Person shared in and the name(s) of the Principal(s) in the event.

Version 1 deals with tags for shared non-family and family events.

Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script.

INSERT OR REPLACE INTO MediaLinkTable

-- tags for shared individual (non-family) events SELECT NULL AS LinkID, MediaID, 0 AS OwnerType, OwnerID, 0 AS IsPrimary, 0 AS Include1, 0 AS Include2, 0 AS Include3, 0 AS Include4, 0 AS SortOrder, 0 AS RectLeft, 0 AS RectTop, 0 AS RectRight, 0 AS RectBottom, '' AS Note, Caption, RefNumber, Date, SortDate, Description FROM ( SELECT ML.MediaID AS MediaID, W.PersonID AS OwnerID, ML.Caption AS Caption, ML.RefNumber AS RefNumber, ML.Date AS Date, ML.SortDate AS SortDate, ML.Description || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || N.Given  || ' '  || N.Surname  || '-'  || N.OwnerID  AS Description FROM MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.EventID AND ML.OwnerType = 2 -- Event AND E.OwnerType = 0 -- Person INNER JOIN WitnessTable W USING(EventID) INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID LEFT JOIN RoleTable R ON W.Role = R.RoleID INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary WHERE W.PersonID > 0 AND ML.MediaID || '.' || W.PersonID NOT IN ( SELECT DISTINCT MediaID || '.' || OwnerID FROM MediaLinkTable WHERE OwnerType = 0 ORDER BY MediaID, OwnerID )

UNION -- tags for shared family events SELECT ML.MediaID AS MediaID, W.PersonID AS OwnerID, ML.Caption AS Caption, ML.RefNumber AS RefNumber, ML.Date AS Date, ML.SortDate AS SortDate, ML.Description || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || Husb.Given  || ' '  || Husb.Surname  || '-'  || Husb.OwnerID  || ' & '  || Wife.Given  || ' '  || Wife.Surname  || '-'  || Wife.OwnerID

AS Description FROM MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.EventID AND ML.OwnerType = 2 -- Event AND E.OwnerType = 1 -- FamilyPerson INNER JOIN FamilyTable Fam ON E.OwnerID = Fam.FamilyID LEFT JOIN NameTable Husb ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary LEFT JOIN NameTable Wife ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary INNER JOIN WitnessTable W USING(EventID) INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID LEFT JOIN RoleTable R ON W.Role = R.RoleID --INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary WHERE W.PersonID > 0 AND W.PersonID || '.' || ML.MediaID NOT IN ( SELECT DISTINCT MediaID || '.' || OwnerID FROM MediaLinkTable WHERE OwnerType = 0 ORDER BY MediaID, OwnerID ) )



-- USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above code