Facts+-+Split+Shared+to+Individual

This page looks into the challenges involved in converting shared events to individual ones and tries to come up with a solution using SQLite queries. =The Quandary of Shared Events= toc We are caught in a quandary between using shared events in RootsMagic to advantage in its management and reporting versus their incompatibility with most external software, especially through GEDCOM. Until RootsMagic comes up with a satisfactory splitting option on export, we seek other workarounds or avoidance: =Applications that import RootsMagic Shared Events= While most other applications have no support for shared events, a small number do preserve the data when importing from RootsMagic: Converts: converts shared events into individual ones Preserves: converts RM shared events into application's version of shared events.
 * 1) Use shared events to the fullest extent but forego exporting to external software that may have advantages for certain kinds of analysis and reporting.
 * 2) Forego using shared events so that you can more fully enjoy the benefits of exporting to external software.
 * 3) Use shared events and after export apply MarkVS' GEDCOM splitting function integrated in his GED Image Tracer. As of writing, it does a basic split but with flaws as a consequence of the inherent challenges that we will get into below.
 * 4) Use shared events and after export import into David Knight's iOS app GedView and save its GEDCOM for use with other software. As of ver 3.4.1: "Basic support added for shared events / facts as used by RootsMagic. GedView will load the event/fact and copy it to the individuals who share it." I have yet to examine the results and it does require the iOS device.
 * ~ Application ||~ Converts ||~ Preserves ||~ Description ||
 * > GedSite ||= X ||=  || Generates website from GEDCOM ||
 * > GedView ||= X ||=  || GEDCOM editor/viewer for iOS ||
 * > Legacy Family Tree ||=  ||= X || Full family tree/genealogy software ||

**The Challenges in Converting Shared to Individual Events**
 * 1) The unique data for the event itself for the Principal role is stored in EventTable and can remain unchanged.
 * 2) Witnesses or sharees of the event are identified in the WitnessTable with their unique info: RoleID, custom sentence, Note.
 * 3) Event data from the EventTable along with the sharee's data from the WitnessTable and other supporting data from other tables need to be combined and entered as a new record in the EventTable for each sharee of an event.
 * 4) Some Fact Types, such as Census, can be split into individual Census facts.
 * 5) Most other Fact Types, such as Baptism, must not be split into individual Baptism facts; only the Principal was baptised. What to do with the sharees? Convert to the Miscellaneous fact type or some custom fact such as "Share".
 * 6) Do we need a sentence template for these Share facts, given that the objective is to export to third party software that does not use it? I hope nothing and maybe we have to suppress the sentence if we use a standard fact. No one sentence template will likely satisfy all fact types and roles. The default Census sentence template is decidedly unsuitable for the Census 'share' event created.
 * 7) What do we put in the Description field of the Share fact? Probably the name(s) of the Principal(s), the name of his/her/their event type and the role of the sharee would be essential. Note the plural names if the shared event is a Family fact type. The default Census fact types do not use Description so will not show the householder's(s') name(s) nor the subject's role.
 * 8) We cannot invent new citations for the sharee event but can copy the citations for the Principal; they may need to be customised for the Share fact.
 * 9) Some may want to operate on an intermediary database for export, eliminating all shared events with individual events.
 * 10) Others may want to have both shared and individual events in their working database, using the indiv share events for export, the shared events for reports. That's possible but keeping the Census and Fact types complicates things - their settings will have to be changed back and forth.

=Meeting the Challenge=

ToDo:

 * 1) Family events
 * 2) special Fact Types, e.g. Census, Residence that should keep the same type
 * 3) citation media
 * 4) auto create 'Share' Fact Type, if not existing, complete with a default sentence template
 * 5) webtags
 * 6) fact don't exist
 * 7) citation
 * 8) research log?
 * 9) cleanup temp used IsPrimary and Flagsfields
 * 10) do something with Share events generated for non-Person witness no longer generated. Now appending non-Person Name, Role Note to shared event Note in easily found privacy brackets.
 * 11) temp use of EventTable.IsPrimary and CitationTable.Flags
 * 12) don't generate spurious events or citations
 * 13) stop misusing these fields
 * 14) Consider coexistence of Shared events and 'Share' events - former for RM internal use, latter for GEDCOM - could split script into two, second part deletes the Sharers so running the first part only leaves both in place. Done
 * 15) Split: would leave Shared events as is and add Share events. User control of FactType output options and Private options might prove satisfactory.
 * 16) Unshare: would remove the non-Principals from the shared events
 * 17) Undo: would restore the non-Principals to shared events and delete the Share events and related citations, media tags and web tags
 * 18) Hide tracks: would drop the query created tables from the database
 * 19) Should Name, Role of Persons who shared an event be appended to Note of once-shared event?
 * 20) Describe the queries, how to use them, illustrate results

Scripts
Four scripts are provided for user choice:
 * 1) Facts-SplitSharedToIndiv.sql is to be run first and will create an Individual fact matching the shared fact for each non-Principal role. It should not be repeated.
 * 2) Facts-Unshare.sql would be run next if it is desirable to eliminate shared facts from the database. The decision to do so is reversible, in the short term, at least. If the motivation for splitting was that 3rd party software did not recognise shared facts, it may not be necessary to unshare for the export.
 * 3) Facts-Split-Undo.sql can be run to eliminate the events created by the first script and restore the shared facts deleted by the second, provided the following script has not been executed
 * 4) Facts-Split-HideTracks.sql rids the database of the tables created by the first script;

Split Shared to Individual
2016-01-17 As above but does not include Principal's RIN in the Description field in split events Kim Mills has done a very clear, well illustrated explanation of how to use these functions in RMtrix to split shared events prior to exporting and uploading to Rootsweb and Ancestry.com trees. Read it on her Footsteps of the Past [|blog]. code format="sql" -- Facts-SplitSharedToIndiv.sql -- 2012-12-19 Tom Holden ve3meo /* 2012-12-21 rev. divided script into separate files.

Run this query to convert shared events to Individual 'Share' events, except Census, Residence fact types whose Fact Name is preserved.

Creates a new Individual Fact Type named and abbreviated 'Share', without the quotes, if none exists.

Witness not in File and their note is appended to the shared event note and enclosed in privacy brackets, thus {** ... **}.

See end of script for steps to UNDO the changes



-- DELETE FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share'; -- Create Share facttype if it does not exist INSERT OR IGNORE INTO FactTypeTable

SELECT (  SELECT FactTypeID FROM FactTypeTable   WHERE LOWER(Abbrev) LIKE 'share'   ) AS FactTypeID, 0 AS OwnerType, 'Share' AS Name, 'Share' AS Abbrev, 'EVEN' AS GedcomTag, 1 AS UseValue, 1 AS UseDate, 1 AS UsePlace, '[person] was involved as a< [Desc]>< [Date]>< [PlaceDetails]>< [Place]>.' AS Sentence, -1 AS Flags

-- keep track of the current last key rowid's before the adding of the Share events DROP TABLE IF Exists zTmpShareSplit

CREATE TABLE IF NOT EXISTS zTmpShareSplit (TableName TEXT, Maxrowid INTEGER) INSERT OR REPLACE INTO zTmpShareSplit SELECT 'FactTypeTable', (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share')

INSERT OR REPLACE INTO zTmpShareSplit SELECT 'EventTable', MAX(EventID) FROM EventTable INSERT OR REPLACE INTO zTmpShareSplit SELECT 'CitationTable', MAX(CitationID) FROM CitationTable INSERT OR REPLACE INTO zTmpShareSplit SELECT 'MediaLinkTable', MAX(LinkID) FROM MediaLinkTable INSERT OR REPLACE INTO zTmpShareSplit SELECT 'URLTable', MAX(LinkID) FROM URLTable

-- Split Shared non-family facts INSERT INTO EventTable

SELECT NULL AS EventID, CASE E.EventType WHEN 18 THEN 18 -- Census to Census WHEN 311 THEN 18 -- Census (family) to Census WHEN 29 THEN 29 -- Residence to Residence WHEN 310 THEN 29 -- Residence (family) to Residence ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share END AS EventType, E.OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID, E.Date, E.SortDate, E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0 E.IsPrivate, E.Proof, E.Status, E.EditDate, '' AS Sentence, LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) || ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details, W.Note AS Note FROM WitnessTable W INNER JOIN EventTable E USING(EventID) INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID INNER JOIN RoleTable R ON W.Role = R.RoleID INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary WHERE E.OwnerType = 0 -- Person AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree

-- Split Shared family facts INSERT INTO EventTable

SELECT NULL AS EventID, CASE E.EventType WHEN 18 THEN 18 -- Census to Census WHEN 311 THEN 18 -- Census (family) to Census WHEN 29 THEN 29 -- Residence to Residence WHEN 310 THEN 29 -- Residence (family) to Residence ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share END AS EventType, 0 AS OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID, E.Date, E.SortDate, E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0 E.IsPrivate, E.Proof, E.Status, E.EditDate, '' AS Sentence, LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) || ' of ' || N1.Given || ' ' || N1.Surname || '-' || N1.OwnerID || ' & ' || N2.Given || ' ' || N2.Surname || '-' || N2.OwnerID AS Details, W.Note AS Note FROM WitnessTable W INNER JOIN EventTable E USING(EventID) INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID INNER JOIN RoleTable R ON W.Role = R.RoleID INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID LEFT JOIN NameTable N1 ON F.FatherID = N1.OwnerID AND +N1.IsPrimary -- to get Husband Names LEFT JOIN NameTable N2 ON F.MotherID = N2.OwnerID AND +N2.IsPrimary -- to get Wife Names WHERE E.OwnerType = 1 -- Family AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree

-- Copy citation from shared event to Share events INSERT INTO CitationTable SELECT NULL AS CitationID, C.OwnerType, C.SourceID, E.EventID AS OwnerID, C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber, C.CitationID AS Flags, -- temp use of Flags to link media and webtags to new citations, set to 0 later C.Fields FROM CitationTable C INNER JOIN EventTable E ON C.OwnerID = E.IsPrimary AND C.OwnerType = 2 AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')

-- Copy Media tag from shared event to Share events

INSERT INTO MediaLinkTable

SELECT NULL AS LinkID, ML.MediaID, ML.OwnerType, E.EventID AS OwnerID, 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 MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.IsPrimary -- remember IsPrimary temp holds the shared EventID AND ML.OwnerType =2 AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')

-- Copy Media tag from citation of shared event to citation of Share events

INSERT INTO MediaLinkTable

SELECT NULL AS LinkID, ML.MediaID, ML.OwnerType, C.CitationID AS OwnerID, 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 MediaLinkTable ML INNER JOIN CitationTable C ON ML.OwnerID = C.Flags -- remember Flags temp holds the CitationID of the shared Event AND ML.OwnerType =4 AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')



-- Copy WebTag from citation of shared event to citation of Share events

INSERT INTO URLTable

SELECT NULL AS LinkID, 4 AS OwnerType, C.CitationID, U.LinkType, U.Name, U.URL, U.Note FROM URLTable U INNER JOIN CitationTable C ON U.OwnerID = C.Flags -- remember Flags temp holds the CitationID of the shared Event AND U.OwnerType = 4 AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')

-- Append Name and Note from witness not in database to the original shared event note

UPDATE EventTable SET Note = -- SELECT Note || -- SELECT '{** ' || CAST(X'0A0D' AS TEXT) || (SELECT W.Given || ' ' || W.Surname || ' was ' || LOWER(R.RoleName) || '. '   || W.Note   FROM WitnessTable W   INNER JOIN RoleTable R ON W.Role = R.RoleID   WHERE EventTable.EventID = W.EventID   AND W.PersonID = 0  ) || CAST(X'0A0D' AS TEXT) || '**}' -- FROM EventTable WHERE EventTable.EventID IN (SELECT EventID FROM WitnessTable WHERE PersonID = 0)

-- Reset EventTable.IsPrimary temp used for Shared EventID to 0 UPDATE EventTable SET IsPrimary = 0 WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')

-- Reset CitationTable.Flags temp used for Shared Event CitationID to 0 UPDATE CitationTable SET Flags = 0 WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')

-- Make a backup of the WitnessTable before wiping it DROP TABLE IF EXISTS WitnessTableSafe CREATE TABLE IF NOT EXISTS WitnessTableSafe AS SELECT * FROM WitnessTable

/* DONE! Shared events still shared but now the sharers also have individual unshared events with their particular note. When SQLiteSpy closes the database, any temp tables in memory will be gone but WitnessTableSafe will remain in the database and could be used to restore the Sharings before running the script again. So, too, remains zTmpShareSplit. Facts-Split-Undo.sql depends on their presence to reverse the splits.

Facts-Split-HideTracks.sql drops these tables from the database, no going back.



code

Unshare
code format="sql" -- Facts-Unshare.sql /* 2012-12-21 Tom Holden ve3meo

This simple script unshares all shared facts by deleting all rows from the WitnessTable.



-- Delete Sharings DELETE FROM WitnessTable

code

Undo Splits and Reshare
code format="sql" -- Facts-Split-Undo.sql /* 2012-12-21 Tom Holden ve3meo Reverses the changes made by Facts-SplitSharedToIndiv.sql Requires tables zTmpShareSplit and WitnessTableSafe from the Split query

Deletes the new Indiv Share, Census and Residence events and related citations, media tags and webtags and restores the Sharings. Sort of an UNDO... but not complete; the appends to the original fact note remain.

DELETE FROM EventTable WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable'); DELETE FROM CitationTable WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable'); DELETE FROM MediaLinkTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'MediaLinkTable'); DELETE FROM URLTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'URLTable'); -- To restore the Sharings: INSERT INTO WitnessTable SELECT * FROM WitnessTableSafe

code

Hide Tracks
code format="sql" -- Facts-Split-HideTracks.sql /* 2012-12-21 Tom Holden ve3meo

Drops the tables created by Facts-SplitSharedToIndiv.sql It does not hide all traces as the Share events themselves attest and THERE IS NO GOING BACK: Facts-Split-Undo.sql will fail.

DROP TABLE WitnessTableSafe DROP TABLE zTmpShareSplit code