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”

external image user_none_lg.jpg ve3meo Apr 13, 2014

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

]

Discussions & comments from Wikispaces site


thejerrybryan

Recursive Query

thejerrybryan
04 July 2011 02:23:38

I have been away from the computer most of the day, but prior to that I had already completed a series of queries that are very similar to the ones you you just posted. I was planning to post them when I got back on the computer tonight. I’ll try to get mine posted as soon as I can, just as a way to compare notes.

I don’t know of any way to do the kind of recursion with SQL that would be required to do something to “all ancestors”, but my update to “set parents of those born before 1906 to not living” can be run multiple times, and each time it would pick up at least one new generation. So we can sort of fake out a recursion simply by running the same update a bunch of times.

It will probably be tomorrow before I get anything else posted.

Jerry


ve3meo

ve3meo
14 April 2014 02:34:12

Have a look at Ancestors+Query+-+Recursive

Tom

Inline comments


ve3meo

Comment: It is now possible: http://sqlitetool…

ve3meo
14 April 2014 02:40:35

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.