Set Living Flag #update #living

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:

UPDATE PersonTable
 SET Living=0; -- Dead 0, Living 1

This query can find all persons with a Death fact:

     -- Persons with death facts
     SELECT PersonID FROM PersonTable, EventTable
      WHERE PersonID=OwnerID
      AND EventType=2;

This query can find all persons with an Individual fact (excluding Family facts) before a given year (1906, in this example):

     -- 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';

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:

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'
     )
;

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:

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)
;

It produces results like this:

Pedigree
1,116
4,7
5,7
6,254,155,829,138
8,829,138,821
11,829,138,821

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.
[inline comment: “find a way to recurse using SQLite”

ve3meo Apr 13, 2014

It is now possible: Ancestors+Query+-+Recursive

]

Discussions & comments from Wikispaces site

Inline comments