-- Group-Ancestors.sql
/*
2014-04-15 Tom Holden ve3meo
Creates and updates a named group of ancestors 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 "!Anc: Holden, Robert Alexander-155." but the
query will update any group with a name beginning "!Anc:" 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 '!Anc: Surname, Given-RIN.'
INSERT
	OR IGNORE
INTO LabelTable
VALUES (
	(
		SELECT LabelID
		FROM LabelTable
		WHERE LabelName LIKE '!Anc:%-' || (
				SELECT RIN
				FROM xGroupIDTable
				) || '.'
		)
	,0
	,(
		SELECT ifnull(MAX(LabelValue), 0) + 1
		FROM LabelTable
		) -- ifnull() needed if LabelTable is empty
	,'!Anc: ' || (
		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 '!Anc:%-' || (
				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
		)
	,AncestorID AS StartID
	,AncestorID AS EndID
FROM (
	WITH RECURSIVE parent_of(ChildID, ParentID) AS (
			SELECT PersonID
				,FatherID AS ParentID
			FROM PersonTable
			LEFT JOIN ChildTable ON PersonID = ChildTable.ChildID
			LEFT JOIN FamilyTable 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
				,MotherID AS ParentID
			FROM PersonTable
			LEFT JOIN ChildTable ON PersonID = ChildTable.ChildID
			LEFT JOIN FamilyTable 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)
			)
		,ancestor_of_person(AncestorID) AS (
			SELECT ParentID
			FROM parent_of
			WHERE ChildID = (
					SELECT RIN
					FROM xGroupIDTable
					) --enter RIN of starting person at runtime
			
			UNION --ALL
			
			SELECT ParentID
			FROM parent_of
			INNER JOIN ancestor_of_person ON ChildID = AncestorID
			)
	SELECT AncestorID
	FROM ancestor_of_person
		,PersonTable
	WHERE ancestor_of_person.AncestorID = PersonTable.PersonID
	);