Pulling Together All the Events for An Individual #person #events #lifeline

This discussion was started around the Database System Catalog page and all posts up to its continuation on this page can be reviewed in full here.

In summary, MarkVS posed this problem:

One thing that I wanted to do, and I expect might be of interest to others, is to be able to
stream all the events for an individual and then, depending on what reporting I wanted to
do, sort and/or group and/or filter the records accordingly.
...
From what I have learned to date it appears to be neceesary to run three separate queries
and combine the results to get the "complete" datastream. These are the queries I think
you need:
 
1) Firstly the straightforward one. The Events held on the Event Table for individuals need
to be pulled off. It is important to select only those with an OWNERTYPE of 0. You can link
to the various other tables from this data to pick up the FactType description, name of the
person etc etc, as required.
 
2) Secondly, the family events. These are also held in the Event Table but with an OWNERTYPE
of 1. This means that the event being recorded acutally applies to 2 people. So to get this
data into a stream of events you need to take the value of the OWNERID field and use this to
look up the record in the FAMILY table (ie not the PERSON table as you would if the OWNERTYPE
was 0). In the Family table you will see the Husband and Wife ID and these can be used to pick
up the names of the two people to whom the event applies. Then in theory generate an Event
into the data stream for both of them.
 
3) Thirdly we need to pick up Shared events. These are recorded in the Witness table. This
holds the Person ID to link back to the Name of the person and the Event ID to link back to
the Event record (and from there to the fact description etc etc).
 

romermb responded with a query design based on a UNION of five SELECTs, demonstrating the use of constants and NULLs in the SELECT fields to build a uniform results set from each SELECT allowing the UNIONs to append the result sets together.

One of the five early queries is extracted below, showing the constant ‘Principal’ and NULL being used to fill columns to correspond with the layout of the other queries; COLLATE NOCASE being used to replace the RMNOCASE collation sequence that must be embedded in the RootsMagic software and unavailable to third party SQLite managers; COUNT and GROUP BY to flag and skip duplicates. This version was revised by ve3meo with explicit use of INDEXED BY and NOT INDEXED clauses to override sqlite’s query optimiser which was found to adversely affect the speed of this set of queries by a factor of ~1000, in the worst case. Later versions avoided this deprecated practice by hiding the IsPrimary field from the SQLite query optimiser so that it would not choose a slow index; one slight change in the WHERE clause is all that is needed: +NameTable.IsPrimary=1.

The current version is extensively revised from the original and is compatible with a wider set of SQLite managers plus OpenOffice Base than was version 9 and runs as fast or faster. It incorporates facts for individuals, couples, parenting and shared facts with fairly comprehensive date reporting. Now includes persons not in a database tree but named as having a role at an event for a person in a tree.

Download: AllFacts4Person11-OO.sql
TH 2021-03-03 tested on #RM8.

UNION ALL -- add all events for Individual
SELECT
  Facttypetable.Name COLLATE Nocase ,
  'Principal' ,
  Nametable.Ownerid ,
  Nametable.Surname COLLATE Nocase ,
  Nametable.Suffix COLLATE Nocase ,
  Nametable.Prefix COLLATE Nocase ,
  Nametable.Given COLLATE Nocase ,
  NULL ,
  NULL ,
  NULL ,
  NULL ,
  NULL ,
  COUNT( 1 )
FROM
  Eventtable NOT INDEXED INNER JOIN
  Facttypetable NOT INDEXED ON Eventtable.Eventtype = Facttypetable.Facttypeid INNER JOIN
  Nametable INDEXED BY Idxnameownerid ON Eventtable.Ownerid = Nametable.Ownerid
WHERE
  Eventtable.Ownertype = 0 AND Nametable.Isprimary = 1
GROUP BY
  1 ,
  2 ,
  3 ,
  4 ,
  5 ,
  6 ,
  7 ,
  8 ,
  9 ,
  10 ,
  11 ,
  12

Discussions & comments from Wikispaces site