-- AllFacts4Person#.sql romermb's incredible combination of events, shared events plus 'marriage' and alternate names as Facts 
-- #1 2010-01-06 original
-- #2 2010-01-06 rev by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable, tried to speed up using UNION ALL
--    - not much gain - still ~270 secs on a 50,000 event, 16,000 person table.
-- #3 2010-01-07 rev by romermb to incorporate COUNT, GROUP BY
-- #4 2010-01-11 rev by ve3meo for 80:1 speedup using index idxNameOwnerID; now ~3sec using SQLiteSpy but won't run on SQLiteman, 
--    DBManager et al that use old SQLite releases. Runs on SQLiteSpy (fastest) and SQLite Developer (most useful results)
-- #5 2010-01-12 rev by ve3meo for more explicit control over indexing after running ANALYZE caused execution time to skyrocket 
--    from <3s to 190s; sqlite's auto query optimisation changed order and selected less appropriate indexes for prior rev after ANALYZE.
-- #6 2010-01-14 rev by ve3meo to add births of children as facts for parents
-- #7 2010-01-14 rev by ve3meo redesigned to eliminate INNER JOINS and INDEXED clauses to achieve high speed with auto query optimisation
--    and to run on older versions of sqlite3 (SQLiteman, DBManager...)
-- #8 2010-01-16 rev by ve3meo #7 failed to achieve the desired result without the use of INDEXED clauses; replaced the queries to 
--    add Family facts to the Husband and Wife by a process that builds a temporary table which is then queried to get the Family facts 
--    per partner of a couple. Blazingly fast (<4s for ~72500 records out) but some managers cannot keep up with sqlite3. SQLiteSpy and 
--    DBManager succeed. Others throw errors requiring the temporary table to be built in one pass, the queries for the results in a second.
--    Rearranged column order to put Fact and Role Type in middle.
--------------------------------
-- Start by building a temporary table of couples, including alternate names because 
-- all queries of this sort on the original tables that exclude them get bogged down in
-- large databases because the sqlite query optimiser picks the wrong index for NameTable

DROP TABLE IF exists temp.CouplesTable ;
CREATE TEMP TABLE IF NOT EXISTS temp.CouplesTable AS 
SELECT   Facttypetable.Name COLLATE Nocase AS Fact , 
  'Partner' AS 'Role Type' , 
  Nametable1.Ownerid COLLATE binary AS  RIN , 
  Nametable1.Isprimary COLLATE binary AS IsPrimary1, 
  Nametable1.Surname COLLATE Nocase AS Surname , 
  Nametable1.Suffix COLLATE Nocase AS Suffix , 
  Nametable1.Prefix COLLATE Nocase AS Prefix , 
  Nametable1.Given COLLATE Nocase AS 'Given Name' , 
  Nametable2.Ownerid COLLATE binary AS 'Sharer RIN' , 
  Nametable2.Isprimary COLLATE binary AS IsPrimary2, 
  Nametable2.Surname COLLATE Nocase AS 'Sharer Surname' , 
  Nametable2.Suffix COLLATE Nocase AS 'Sharer Suffix' , 
  Nametable2.Prefix COLLATE Nocase AS 'Sharer Prefix' , 
  Nametable2.Given COLLATE Nocase AS 'Sharer Given Name'
FROM 
  Eventtable , 
  Familytable , 
  Nametable AS Nametable1 , 
  Nametable AS Nametable2 , 
  Facttypetable  
WHERE 
  Eventtable.Ownertype = 1 AND 
  Eventtable.Ownerid = Familytable.Familyid AND 
  Familytable.Fatherid = Nametable1.Ownerid AND 
  Familytable.Motherid = Nametable2.Ownerid AND 
  Eventtable.Eventtype = Facttypetable.Facttypeid ;

-- Now we can filter the alternate names out of the temporary CouplesTable in our next
-- query which sets the names of the columns for all the results.  
-- Couples Facts - Husband first
-- EXPLAIN QUERY PLAN
SELECT     RIN , 
  Surname , 
  Suffix , 
  Prefix , 
  "Given Name" , 
   Fact , 
  "Role Type" , 
  "Sharer RIN" , 
  "Sharer Surname" , 
  "Sharer Suffix" , 
  "Sharer Prefix" , 
  "Sharer Given Name",
  COUNT(1) AS Count
FROM temp.CouplesTable
WHERE IsPrimary1=1 AND IsPrimary2=1  
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

UNION ALL
-- Couples Facts - Wife first
SELECT   "Sharer RIN" , 
  "Sharer Surname" , 
  "Sharer Suffix" , 
  "Sharer Prefix" , 
  "Sharer Given Name",
  Fact , 
  "Role Type" , 
  RIN , 
  Surname , 
  Suffix , 
  Prefix , 
  "Given Name",  
  COUNT(1) AS Count
FROM temp.CouplesTable
WHERE IsPrimary1=1 AND IsPrimary2=1   
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

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

UNION ALL
-- add Alternate Name as a 'Fact'
SELECT   Ownerid , 
  Surname COLLATE Nocase , 
  Suffix COLLATE Nocase , 
  Prefix COLLATE Nocase , 
  Given COLLATE Nocase , 
  'Alternate name' , 
  'Principal' , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  Count( 1 ) 
FROM 
  Nametable 
WHERE 
  Isprimary = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 

UNION ALL
-- add shared events other than family as 'Facts' - revised by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable
SELECT 
  Nametable1.Ownerid , 
  Nametable1.Surname COLLATE Nocase , 
  Nametable1.Suffix COLLATE Nocase , 
  Nametable1.Prefix COLLATE Nocase , 
  Nametable1.Given COLLATE Nocase , 
  Facttypetable.Name COLLATE Nocase , 
  Rolename COLLATE Nocase , 
  Eventtable.Ownerid , 
  Nametable2.Surname COLLATE Nocase , 
  Nametable2.Suffix COLLATE Nocase , 
  Nametable2.Prefix COLLATE Nocase , 
  Nametable2.Given COLLATE Nocase , 
  Count( 1 ) 
FROM 
  Eventtable , 
  Witnesstable , 
  Roletable , 
  Facttypetable , 
  Nametable AS Nametable1 , 
  Nametable AS Nametable2 
WHERE 
  Witnesstable.Eventid = Eventtable.Eventid AND 
  Witnesstable.Role = Roletable.Roleid AND 
  Eventtable.Eventtype = Facttypetable.Facttypeid AND 
  Witnesstable.Personid = Nametable1.Ownerid AND 
  Eventtable.Ownerid = Nametable2.Ownerid AND 
  Nametable1.Isprimary = 1 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 

UNION ALL 
-- Add fact for Fathers having children 
SELECT 
  P.Personid AS Rin , 
  N.Surname COLLATE Nocase , 
  N.Suffix COLLATE Nocase , 
  N.Prefix COLLATE Nocase , 
  N.Given COLLATE Nocase , 
  'Fathered' AS Fact , 
  'Parent' AS 'role type' , 
  C.Childid , 
  N2.Surname COLLATE Nocase AS 'child surname' , 
  N2.Suffix COLLATE Nocase AS 'child suffix' , 
  N2.Prefix COLLATE Nocase AS 'child prefix' , 
  N2.Given COLLATE Nocase AS 'child given name' , 
  Count( 1 ) AS Count 
FROM 
  Persontable P , 
  Nametable N , 
  Familytable F , 
  Childtable C , 
  Nametable N2 , 
  Eventtable E , 
  Facttypetable F2 
WHERE 
  P.Personid = N.Ownerid AND 
  P.Personid = F.Fatherid AND 
  F.Familyid = C.Familyid AND 
  C.Childid = N2.Ownerid AND 
  C.Childid = E.Ownerid AND 
  E.Eventtype = F2.Facttypeid AND 
  F2.Facttypeid = 1 AND 
  N.Isprimary = 1 AND 
  C.Relfather = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 

UNION ALL 
-- Add fact for Mothers having children
SELECT 
  P.Personid AS Rin , 
  N.Surname COLLATE Nocase , 
  N.Suffix COLLATE Nocase , 
  N.Prefix COLLATE Nocase , 
  N.Given COLLATE Nocase , 
  'Mothered' AS Fact , 
  'Parent' AS 'role type' , 
  C.Childid , 
  N2.Surname COLLATE Nocase AS 'child surname' , 
  N2.Suffix COLLATE Nocase AS 'child suffix' , 
  N2.Prefix COLLATE Nocase AS 'child prefix' , 
  N2.Given COLLATE Nocase AS 'child given name' , 
  Count( 1 ) AS Count 
FROM 
  Persontable P , 
  Nametable N , 
  Familytable F , 
  Childtable C , 
  Nametable N2 , 
  Eventtable E , 
  Facttypetable F2 
WHERE 
  P.Personid = N.Ownerid AND 
  P.Personid = F.Motherid AND 
  F.Familyid = C.Familyid AND 
  C.Childid = N2.Ownerid AND 
  C.Childid = E.Ownerid AND 
  E.Eventtype = F2.Facttypeid AND 
  F2.Facttypeid = 1 AND 
  N.Isprimary = 1 AND 
  C.RelMother = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 
ORDER BY 
  Rin ;