Contents
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
ve3meo