--Group-Extract.sql
/* extracts a group of people with everything related to them to a new database
2017-07-09 Tom Holden ve3meo
*/
/*
-- Excecute this statement first to get the GroupID of the Group to be extracted
SELECT DISTINCT LabelName AS GroupName, GroupID 
FROM GroupTable JOIN LabelTable ON GroupID=LabelValue AND LabelType = 0
ORDER BY GroupName
;

-- THEN store that GroupID in a temp table 
DROP TABLE IF EXISTS zGroupIDReg
;
CREATE TEMP TABLE zGroupIDReg
AS
SELECT LabelValue AS GroupID, LabelName
FROM main.LabelTable
WHERE LabelValue = $GroupID -- for SQLiteSpy, replace $GroupID with the desired GroupID number
;

-- THEN Attach to the originating database the empty target database asliased as "Z"
-- edit the statement below with the full path and name of your target between the quotes
ATTACH "C:\Users\Tom\Documents\FamilyTree\RM7\Thomas Mailing families group extract.rmgc" AS Z
;
*/

BEGIN
;
-- All FactTypes
INSERT OR REPLACE INTO z.FactTypeTable
SELECT * FROM main.FactTypeTable
;
-- All Roles
INSERT OR REPLACE INTO z.RoleTable
SELECT * FROM main.RoleTable
;
-- All Places except LDS Temples already
INSERT OR REPLACE INTO z.PlaceTable
SELECT * FROM main.PlaceTable P
WHERE P.PlaceType <> 1
;
-- All custom Source Templates
INSERT OR REPLACE INTO z.SourceTemplateTable
SELECT * FROM main.SourceTemplateTable ST
WHERE ST.TemplateID > 9999
;
-- LabelTable record for Group selected by its ID number
INSERT OR REPLACE INTO Z.LabelTable 
SELECT * FROM main.LabelTable WHERE LabelValue = (SELECT GroupID FROM zGroupIDReg)
;
-- GroupTable records for selected Group
INSERT OR REPLACE INTO z.GroupTable
SELECT * FROM main.GroupTable WHERE GroupID = (SELECT GroupID FROM zGroupIDReg)
;
-- All PersonTable records within Group
INSERT OR REPLACE INTO z.PersonTable
SELECT P.* FROM main.PersonTable P, main.GroupTable G 
WHERE P.PersonID BETWEEN G.StartID AND G.EndID
AND G.GroupID = (SELECT GroupID FROM zGroupIDReg)
;
-- Delete SpouseIDs (spousal FamilyID) from PersonTable if spouse not in Group
UPDATE z.PersonTable SET SpouseID = 0 WHERE SpouseID NOT IN (SELECT FamilyID FROM z.FamilyTable)
;

-- NameTable records for Group members
INSERT OR REPLACE INTO z.NameTable
SELECT N.* FROM main.NameTable N, z.PersonTable P
WHERE N.OwnerID = P.PersonID
;
-- FamilyTable records for Group members including RIN=0 spouses
INSERT OR REPLACE INTO z.FamilyTable
SELECT F.* FROM main.FamilyTable F, z.PersonTable P
WHERE F.FatherID IN (SELECT PersonID FROM z.PersonTable)
AND F.MotherID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL
SELECT F.* FROM main.FamilyTable F, z.PersonTable P
WHERE F.FatherID IN (SELECT PersonID FROM z.PersonTable)
AND F.MotherID = 0
UNION ALL
SELECT F.* FROM main.FamilyTable F, z.PersonTable P
WHERE F.MotherID IN (SELECT PersonID FROM z.PersonTable)
AND F.FatherID = 0
;

-- EventTable records for Group members
INSERT OR REPLACE INTO z.EventTable
SELECT E.* FROM main.EventTable E
WHERE E.OwnerType = 0
AND E.OwnerID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL -- Family records
SELECT E.* FROM main.EventTable E
WHERE E.OwnerType = 1
AND E.OwnerID IN (SELECT FamilyID FROM z.FamilyTable)
;

-- WitnessTable records for events of Group members
INSERT OR REPLACE INTO z.WitnessTable
SELECT W.* FROM main.WitnessTable W
JOIN main.EventTable USING(EventID)
;

-- ChildTable records for Group members whose parents are in Group
INSERT OR REPLACE INTO z.ChildTable
SELECT C.* FROM main.ChildTable C
WHERE C.ChildID IN (SELECT PersonID FROM z.PersonTable)
AND C.FamilyID IN (SELECT FamilyID FROM z.FamilyTable)
;

-- CitationTable records for Group members
INSERT OR REPLACE INTO z.CitationTable
SELECT C.* FROM main.CitationTable C
WHERE C.OwnerType = 0 AND C.OwnerID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL
SELECT C.* FROM main.CitationTable C
WHERE C.OwnerType = 1 AND C.OwnerID IN (SELECT FamilyID FROM z.FamilyTable)
UNION ALL
SELECT C.* FROM main.CitationTable C
WHERE C.OwnerType = 2 AND C.OwnerID IN (SELECT EventID FROM z.EventTable)
UNION ALL
SELECT C.* FROM main.CitationTable C
WHERE C.OwnerType = 7 AND C.OwnerID IN (SELECT NameID FROM z.NameTable)
;

-- SourceTable records used by Citations for Group
INSERT OR REPLACE INTO z.SourceTable
SELECT DISTINCT S.* FROM main.SourceTable S
JOIN z.CitationTable USING(SourceID)
;

-- MediaLinkTable records used by Group
INSERT OR REPLACE INTO z.MediaLinkTable
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 0 AND ML.OwnerID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 1 AND ML.OwnerID IN (SELECT FamilyID FROM z.FamilyTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 2 AND ML.OwnerID IN (SELECT EventID FROM z.EventTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 3 AND ML.OwnerID IN (SELECT SourceID FROM z.SourceTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 4 AND ML.OwnerID IN (SELECT CitationID FROM z.CitationTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 5 AND ML.OwnerID IN (SELECT PlaceID FROM z.PlaceTable)
UNION ALL
SELECT ML.* FROM main.MediaLinkTable ML
WHERE ML.OwnerType = 7 AND ML.OwnerID IN (SELECT NameID FROM z.NameTable)
;

-- MultiMediaTable records for Group
INSERT OR REPLACE INTO z.MultimediaTable
SELECT DISTINCT M.* FROM main.MultimediaTable M
JOIN z.MediaLinkTable USING(MediaID)
;

-- ResearchTable records for Group
INSERT OR REPLACE INTO z.ResearchTable
SELECT R.* FROM main.ResearchTable R
WHERE R.OwnerType = 0 AND R.OwnerID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL
SELECT R.* FROM main.ResearchTable R
WHERE R.OwnerType = 1 AND R.OwnerID IN (SELECT FamilyID FROM z.FamilyTable)
UNION ALL
SELECT R.* FROM main.ResearchTable R
WHERE R.OwnerType = 2 AND R.OwnerID IN (SELECT EventID FROM z.EventTable)
UNION ALL
SELECT R.* FROM main.ResearchTable R
WHERE R.OwnerType = 5 AND R.OwnerID IN (SELECT PlaceID FROM z.PlaceTable)
UNION ALL -- this criterion may be too general and could be narrowed by TaskType
SELECT R.* FROM main.ResearchTable R
WHERE R.OwnerType = 8 -- Tasks (0), General Research Logs (1) and Correspondence (2)
;

-- ResearchItemTable records for Research Logs
INSERT OR REPLACE INTO z.ResearchItemTable
SELECT DISTINCT RI.* FROM main.ResearchItemTable RI
JOIN z.ResearchTable R ON RI.LogID=R.TaskID
;


-- AddressLinkTable records for Group
INSERT OR REPLACE INTO z.AddressLinkTable
SELECT AL.* FROM main.AddressLinkTable AL
WHERE AL.OwnerType = 0 AND AL.OwnerID IN (SELECT PersonID FROM z.PersonTable)
UNION ALL
SELECT AL.* FROM main.AddressLinkTable AL
WHERE AL.OwnerType = 1 AND AL.OwnerID IN (SELECT FamilyID FROM z.FamilyTable)
UNION ALL
SELECT AL.* FROM main.AddressLinkTable AL
WHERE AL.OwnerType = 3 AND AL.OwnerID IN (SELECT SourceID FROM z.SourceTable)
UNION ALL
SELECT AL.* FROM main.AddressLinkTable AL
WHERE AL.OwnerType = 6 AND AL.OwnerID IN (SELECT TaskID FROM z.ResearchTable)
-- ToDo: 
;

-- AddressTable records for Group
INSERT OR REPLACE INTO z.AddressTable
SELECT DISTINCT A.* FROM main.AddressTable A
JOIN z.AddressLinkTable USING(AddressID)
;

-- LinkAncestryTable records for Group
INSERT OR REPLACE INTO z.LinkAncestryTable
SELECT LA.* FROM main.LinkAncestryTable LA
JOIN z.PersonTable P ON LA.rmID = P.PersonID AND LA.LinkType = 0
UNION ALL
SELECT LA.* FROM main.LinkAncestryTable LA
JOIN z.CitationTable C ON LA.rmID = C.CitationID AND LA.LinkType = 4
UNION ALL
SELECT LA.* FROM main.LinkAncestryTable LA
JOIN z.MultimediaTable M ON LA.rmID = M.MediaID AND LA.LinkType = 11
-- ToDo: are there other linktypes?
;

-- LinkTable records for Group
INSERT OR REPLACE INTO z.LinkTable
SELECT L.* FROM main.LinkTable L
JOIN z.PersonTable P ON L.rmID = P.PersonID AND L.LinkType = 0
-- ToDo: other linktypes?
;

-- URLTable records for Group et al
INSERT OR REPLACE INTO z.URLTable
SELECT U.* FROM main.URLTable U
JOIN z.PersonTable P ON U.OwnerID = P.PersonID AND U.OwnerType = 0
UNION ALL
SELECT U.* FROM main.URLTable U
JOIN z.SourceTable S ON U.OwnerID = S.SourceID AND U.OwnerType = 3
UNION ALL
SELECT U.* FROM main.URLTable U
JOIN z.CitationTable C ON U.OwnerID = C.CitationID AND U.OwnerType = 4
UNION ALL
SELECT U.* FROM main.URLTable U
JOIN z.PlaceTable P ON U.OwnerID = P.PlaceID AND U.OwnerType = 5
UNION ALL
SELECT U.* FROM main.URLTable U
JOIN z.ResearchItemTable RS ON U.OwnerID = RS.ItemID AND U.OwnerType = 15
;

-- ExclusionTable records for Group
INSERT OR REPLACE INTO z.ExclusionTable
SELECT EX.* FROM main.ExclusionTable EX
WHERE EX.ID1 IN (SELECT PersonID FROM z.PersonTable)
AND EX.ExclusionType = 2 -- no known other types
;

-- Backup target ConfigTable

DROP TABLE IF EXISTS z.ConfigTableBAK
;

ALTER TABLE z.ConfigTable RENAME TO ConfigTableBAK
;

CREATE TABLE z.ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB );

DROP INDEX IF EXISTS z.idxRecType
;
CREATE INDEX z.idxRecType ON ConfigTable (RecType)
;
INSERT OR REPLACE INTO z.ConfigTable SELECT * FROM main.ConfigTable -- does bring in DataRec value!!
;

/*
CREATE TABLE z.ConfigTableBAK AS SELECT * FROM z.ConfigTable -- doesn't bring in DataRec value!!
;
INSERT OR REPLACE INTO z.ConfigTableBAK SELECT * FROM z.ConfigTable -- doesn't bring in DataRec value!!
;
*/
-- ToDo: 

COMMIT
;

SELECT
'
####### #    # 
#     # #   #  
#     # #  #   
#     # ###    
#     # #  #   
#     # #   #  
####### #    # 
Script completed without SQLite error. 
Close main and target databases from SQLite manager.
Open target database with RootsMagic.
Run File > Database Tools > Rebuild Indexes
Explore, Compare, Play!'
AS Instructions
;
----------- END OF SCRIPT ------------