Logo Home    Page list

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

UNION ALL -- add all events for Individual
  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 )
  Facttypetable NOT INDEXED ON Eventtable.Eventtype = Facttypetable.Facttypeid INNER JOIN
  Nametable INDEXED BY Idxnameownerid ON Eventtable.Ownerid = Nametable.Ownerid
  Eventtable.Ownertype = 0 AND Nametable.Isprimary = 1
  1 ,
  2 ,
  3 ,
  4 ,
  5 ,
  6 ,
  7 ,
  8 ,
  9 ,
  10 ,
  11 ,



Incredible speedup using one index

ve3meo 11 January 2010 20:49:47

I was able to make this baby really fly by the application of just one index at appropriate spots. Huge effect on the first two SELECTs, negligible on the next two, and unknown on the Witnesstable SELECT since I have so little data.

But some SQLite managers return an error - same ones that were going the fastest at querying without the index, i.e., using older releases of SQLite. The ones that were gong really slow without the index use the newer releases and execute fastest with the index. SQLite 3.6.17 seems to be a sweet spot that does comparatively well both with and without index, but far faster with. SQLite Developer with a reversion to a 3.6.17 DLL being that one.

I'll upload the file AllFacts4Person4.sql

ve3meo 11 January 2010 20:53:18

Pity one cannot edit a posted message. Let's try the Wikitext file link again:


Incredible speedup using a temp table

ve3meo 16 January 2010 22:27:02

The second technique that is compatible with older versions of sqlite and in accord with the sqlite strictures on the use of the INDEXED clauses is to CREATE a TEMPorary TABLE AS a SELECT query that does not include the WHERE conditions that caused the query optimiser to choose the wrong index. Then query the temp table using those conditions. There is no index for this table or it is one that the user can create so the query optimiser is just fine. Example is AllFacts4Person8.sql.

A problem is that some SQLManagers cannot keep up with sqlite3's execution of the table creation and don't recognise its existence when the table is queried from within the same script. A workaround is to do it in two steps:
1. Table creation
2. Table query

SQLiteSpy 1.8.11 with sqlite 3.6.21 and DBManager 3.2 with sqlite 3.3.13 work as all of them should.