-- Group-Descendants.sql
/*
2014-04-15 Tom Holden ve3meo
Creates and updates a named group of descendants of a person using an unlimited 
recursive tree climb. Does not include starting person in the group. An example 
of an auto-generated group name is "!Desc: Holden, Robert Alexander-155." but the
query will update any group with a name beginning "!Desc:" followed by anything 
to "-nn.", where nn is the RIN number followed by the period character.

Requires support for SQLite 3.8.3 or later and named parameters.
Developed and tested with SQLite Expert Personal 3.5.36.2456
*/
-- Register the RIN of the starting person for following queries
-- to preclude repeated entry
DROP TABLE

IF EXISTS xGroupIDTable;
	CREATE TEMP TABLE

IF NOT EXISTS xGroupIDTable AS
	SELECT @StartRIN AS RIN
		,-- named parameter is prompted for user entry
		NULL AS GroupID -- to be filled in after finding or creating the group label
		;

-- Create Named Group if it does not exist '!Desc: Surname, Given-RIN.'
INSERT
	OR IGNORE
INTO LabelTable
VALUES (
	(
		SELECT LabelID
		FROM LabelTable
		WHERE LabelName LIKE '!Desc:%-' || (
				SELECT RIN
				FROM xGroupIDTable
				) || '.'
		)
	,0
	,(
		SELECT ifnull(MAX(LabelValue), 0) + 1
		FROM LabelTable
		) -- ifnull() needed if LabelTable is empty
	,'!Desc: ' || (
		SELECT Surname || ', ' || Given
		FROM NameTable
		WHERE OwnerID = (
				SELECT RIN
				FROM xGroupIDTable
				)
			AND + IsPrimary
		) || '-' || (
		SELECT RIN
		FROM xGroupIDTable
		) || '.'
	,'SQLite query'
	);

-- register GroupId in the temp table for following queries
UPDATE xGroupIDTable
SET GroupID = (
		SELECT LabelValue
		FROM LabelTable
		WHERE LabelName LIKE '!Desc:%-' || (
				SELECT RIN
				FROM xGroupIDTable
				) || '.'
		);

-- Delete all members of the named group
DELETE
FROM GroupTable
WHERE GroupID = (
		SELECT GroupID
		FROM xGroupIDTable
		);

-- Add members to the named group
INSERT INTO GroupTable
SELECT NULL
	,(
		SELECT GroupID
		FROM xGroupIDTable
		)
	,DescendantID AS StartID
	,DescendantID AS EndID
FROM (
WITH RECURSIVE
  child_of(ParentID, ChildID) AS
    (SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=FatherID        
       LEFT JOIN ChildTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelFather=0         
         ELSE 1         
         END       
         --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
     UNION
     SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=MotherID        
       LEFT JOIN ChildTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelMother=0         
         ELSE 1         
         END
         --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)
     ),
  descendant_of_person(DescendantID) AS
    (SELECT ChildID FROM child_of 
       WHERE ParentID=(
					SELECT RIN
					FROM xGroupIDTable
					) --RIN of starting person entered at runtime
     UNION --ALL
     SELECT ChildID FROM child_of 
       INNER JOIN descendant_of_person ON ParentID = DescendantID)
SELECT DescendantID FROM descendant_of_person, PersonTable
 WHERE descendant_of_person.DescendantID=PersonTable.PersonID
);