-- SetBirthOnlyRelationships.sql
/* 2024-07-03 Tom Holden ve3meo
RM4-10's Relationship Calculator and Set Relationships tools along
with lineage reports and charts completely ignore the non-Birth relationship 
settings offered in the Edit Person screen and follow either all possible paths 
or the shortest path or maybe something in-between. The result is undesirable in
many cases and there is no way around it in RM other than deleting each such 
relationship one at a time. This script deletes all non-Birth relationships to
allow the analysis based solely on Birth relationships and then restores the 
non-Birth relationships.

Caveat: Where a person is a child solely of one Birth parent and a non-Birth parent,
the child is disconnected from the Birth parent. To maintain the link to the one Birth
parent requires it to be a Child in another Family comprising the Birth parent and another
Birth parent or an unknown parent.

Usage: There are 3 steps, 1 and 3 are done by selecting the corresponding block of script
and executing just it. #2 is done within RM.  
 STEP 1: Move all child-parent relationships to a temporary table
 STEP 2: RUN RELATIONSHIPS TOOLS, REPORTS, CHARTS IN RM
 STEP 3: RESTORE Non-Birth RELATIONSHIPS FROM TEMP TABLE
*/
-----------------------------------
-- STEP 1: Move all child-parent relationships to a temporary table
DROP TABLE IF EXISTS xNotBirthChild
;
CREATE TABLE xNotBirthChild AS
SELECT * FROM ChildTable
WHERE RelFather>0 OR RelMother>0
;
DELETE FROM ChildTable
WHERE RecID IN
 (SELECT RecID FROM xNotBirthChild)
;
-----------------------------------
-- STEP 2: RUN RELATIONSHIPS TOOLS, REPORTS, CHARTS IN RM
--
-- DO NOT EDIT RELATIONSHIPS UNTIL AFTER THE NEXT STEP
-----------------------------------
-- STEP 3: RESTORE Non-Birth RELATIONSHIPS FROM TEMP TABLE
INSERT INTO ChildTable
SELECT * FROM xNotBirthChild
;
DROP TABLE IF EXISTS xNotBirthChild
;
-- DONE
-----------------------------------