Privatize+Living

This script sets private the events, alternate name facts, and personal notes for persons whose Living flag is set. Thus, reports and GEDCOM can take full advantage of the options: thus providing many more combinations by which these outputs may be tailored.
 * Privatize living people (GEDCOM only)
 * Include private facts
 * Include {private} notes
 * Strip { } brackets

This is a first pass script, lightly tested so use the usual precautions and feedback any problems. There is a known problem with privatizing notes that are already privatized - RM may output a closing brace character "}".

code format="sql" -- PrivatizeLiving.sql /* 2013-12-14 Tom Holden ve3meo Privatizes events, alt names, personal notes for persons with Living flag set so that reports can be made with names of living persons but no details while full details are outputted for the deceased.

To Do: privatize family note when one spouse is Living.

N.B.: There is a series of queries in a comment block at the end which can be run to UNDO the privatization, it is nevertheless advisable to run this script against a copy of your database in case the process is not perfectlt reversible. Of course, you may wish to keep the resulting privatization if it suits your purposes so that reversal is unnecessary.

-- Make a table of persons marked Living DROP TABLE IF EXISTS xLivingTable CREATE TEMP TABLE IF NOT EXISTS xLivingTable AS SELECT PersonID, Note, TRIM(CAST(Note AS TEXT)) AS NoteTxt FROM PersonTable WHERE Living

-- Make a backup table of EventIDs and their IsPrivate setting -- for all persons marked Living; must cover individual and family events DROP TABLE IF EXISTS xLivingEventsBak CREATE TABLE IF NOT EXISTS xLivingEventsBak AS SELECT E.EventID, E.IsPrivate FROM EventTable E -- Individual events INNER JOIN xLivingTable Lv ON E.OwnerID = Lv.PersonID AND E.OwnerType = 0 UNION SELECT E.EventID, E.IsPrivate FROM EventTable E -- Spousal events for living husband INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1 INNER JOIN xLivingTable Lv ON Fm.FatherID = Lv.PersonID UNION SELECT E.EventID, E.IsPrivate FROM EventTable E -- Spousal events for living wife INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1 INNER JOIN xLivingTable Lv ON Fm.MotherID = Lv.PersonID

-- SET EventTable.IsPrivate = 1 for all events in xLivingEventsBak UPDATE EventTable SET IsPrivate = 1 WHERE EventID IN (SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)

/* -- Alternate Names can also be set private and perhaps desirably so so we repeat the pattern above -- Make a backup table of non-primary NameIDs and their IsPrivate setting -- for all persons marked Living; DROP TABLE IF EXISTS xLivingNamesBak CREATE TABLE IF NOT EXISTS xLivingNamesBak AS SELECT NameID, IsPrivate FROM NameTable WHERE OwnerID IN (SELECT PersonID FROM xLivingTable) ORDER BY NameID

-- Privatize Alternate Names for Persons marked Living in the database UPDATE NameTable SET IsPrivate = 1 WHERE NameID IN ( SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate )

-- PRIVATIZE Personal Notes for persons marked Living by enclosing in braces {} UPDATE PersonTable SET Note = --SELECT CAST(REPLACE(CAST(Note AS TEXT), CAST(Note AS TEXT), '{' || CAST(Note AS TEXT) || '}') AS BLOB) --FROM PersonTable WHERE PersonID IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')

/* -- BLOCK OF UNDO queries to reverse the above changes.

-- UNDO Privatize events by SET EventTable.IsPrivate = 0 for all events in xLivingEventsBak that have IsPrivate = 0 UPDATE EventTable SET IsPrivate = 0 WHERE EventID IN (SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)

-- UNDO privatization of Alt Names for Living persons UPDATE NameTable SET IsPrivate = 0 WHERE NameID IN ( SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate )

-- UNDO Privatize Personal Notes by updating from xLivingTable UPDATE PersonTable SET Note = (SELECT Note FROM xLivingTable Lv WHERE PersonTable.PersonID = Lv.PersonID) WHERE PersonID IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}') -- END of script

code