Privatize Living #private #reports #gedcom

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:

• Privatize living people (GEDCOM only)
• Include private facts
• Include {private} notes
• Strip { } brackets

thus providing many more combinations by which these outputs may be tailored.

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 “}”.

PrivatizeLiving.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