Set+Living+Flag


 * Superseded 2015-01-25. See Living Flag - Set Globally.**

Discussion in [|RootsMagic-Users] pointed out some limitations with the Set Living tool in RootsMagic and the risk of unsetting persons that have been previously set correctly. This page discusses how SQLite might help and is intended to start the development of some useful queries.

The Living flag is stored in the PersonTable in the column named 'Living'. It has two values: 1=True (Alive), 0=False (Dead).

This query can set all persons to the same state: code format="sql" UPDATE PersonTable SET Living=0; -- Dead 0, Living 1 code This query can find all persons with a Death fact: code format="sql" -- Persons with death facts SELECT PersonID FROM PersonTable, EventTable WHERE PersonID=OwnerID AND EventType=2;

code This query can find all persons with an Individual fact (excluding Family facts) before a given year (1906, in this example): code format="sql" -- Persons with any Individual facts dated earlier than 1906 SELECT PersonID FROM PersonTable, EventTable WHERE PersonID=OwnerID AND OwnerType=0 AND Date LIKE 'D%' AND substr(Date,4,4)<'1906';

code We can combine these into one query that will set the Living flag to False for persons with a Death fact or having an individual fact dated earlier than 1906: code format="sql" UPDATE PersonTable SET Living=0 -- Dead 0, Living 1 WHERE PersonID IN (    -- Persons with death facts     SELECT PersonID FROM PersonTable, EventTable      WHERE PersonID=OwnerID      AND EventType=2     UNION     -- Persons with any Individual facts dated earlier than 1906     SELECT PersonID FROM PersonTable, EventTable      WHERE PersonID=OwnerID      AND OwnerType=0      AND Date LIKE 'D%'      AND substr(Date,4,4)<'1906'     ) code This query can be extended to include Family facts (Marriage, Divorce, etc.) older than the given year. We'll leave that to later or for someone else to add.

So that's all well and good but for a lot of ancestors, there may be no events, let alone dates. If no Death fact has been entered, then the above queries will leave the Living flag alone; if it was set to True, that's the way it will stay. It would be great, therefore, to set the Living flag to false for all ancestors and children of some person whose events occurred before the trigger year of, say, 1906. This is probably not readily done within SQLite because it requires a recursion routine through PersonTable and FamilyTable. It can readily be done in a high level programming language that calls SQLite and operates on the results with additional calls. You can see the problem of doing what is essentially the Pedigree tree by examining this 5-generation paternal line query: code format="sql" SELECT P1.PersonID||','||P1.FatherID||ifnull(','||P2.FatherID,)||ifnull(','||P3.FatherID,)||ifnull(','||P4.FatherID,'') AS Pedigree FROM (SELECT P.PersonID, F.FatherID FROM PersonTable P, FamilyTable F INNER JOIN PersonTable H ON(F.FatherID=H.PersonID) WHERE P.ParentID=F.FamilyID) AS P1 LEFT JOIN (SELECT P.PersonID, F.FatherID FROM PersonTable P, FamilyTable F INNER JOIN PersonTable H ON(F.FatherID=H.PersonID) WHERE P.ParentID=F.FamilyID) AS P2 ON (P1.FatherID=P2.PersonID) LEFT JOIN (SELECT P.PersonID, F.FatherID FROM PersonTable P, FamilyTable F INNER JOIN PersonTable H ON(F.FatherID=H.PersonID) WHERE P.ParentID=F.FamilyID) AS P3 ON (P2.FatherID=P3.PersonID) LEFT JOIN (SELECT P.PersonID, F.FatherID FROM PersonTable P, FamilyTable F INNER JOIN PersonTable H ON(F.FatherID=H.PersonID) WHERE P.ParentID=F.FamilyID) AS P4 ON (P3.FatherID=P4.PersonID) code It produces results like this: code Pedigree 1,116 4,7 5,7 6,254,155,829,138 8,829,138,821 11,829,138,821

code A starting person's RIN is on the left, father next to the right, grandfather next, etc. Results could be constrained to those first persons having old event dates and an UPDATE of the Living flag to 0 for each PersonID (RIN) in that person's list of paternal parents. However, we would need to grow the query with another LEFT JOIN for each additional generation, hence the need for a recursive routine that would re-use the same core query for each generation until the last ancestor was found. And this was just the paternal line - to add the maternal line and each set of grandparents at each generation is unwieldy without recursion.


 * If someone can find a way to recurse using SQLite, that would be great; otherwise, some high-level programming is required.**