GroupFromSQL utility- creates RM group from SQL

#1 · 2023-07-03, 9:14 pm

I just created a release for a new script that is helping my RM work.

The script takes a SQL statement, runs it and creates a RM group from the output. It is similar to and uses ideas from Tom's code here on sqlitetoolsforrootsmagic. The difference is that it can be run anytime with a double click. No need to open a SQLlite manager app or command line.

https://github.com/ricko2001/Genealogy-scripts/releases/tag/GroupFromSQL_v1.0.0.0

As this is a ver 1.0 release, I'd be glad to hear of suggestions etc.

This is the ReadMe file if you just want to check it out but don't want to download anything right now.

https://github.com/ricko2001/Genealogy-scripts/blob/main/RM%20-Group%20from%20SQL/ReadMe.txt

Jaime Teas has reacted to this post.

#2 · 2023-07-04, 10:07 pm

Looks potentially very useful, Richard! I've wished for a long time to replace or update my old RMtrix utility to incorporate group creation and refresh but the programming skills involved are more than I can come up with so it stays out of reach. I hope to give your script a try. I must have installed Python some time ago because I've found v3.1 updated from the Windows store already installed.

#3 · 2023-07-06, 7:13 pm

Quote from Richard Otter on 2023-07-06, 7:13 pm
Thanks, Tom
One of the queries I'm using often is one that I created from your recursive queries from years ago.
https://sqlitetoolsforrootsmagic.com/recursive-queries-ancestors-and-descendants/
Thanks for doing the pioneering work with recursive queries.
I selects all of a person's cousins (but not spouses). I never figured out how to do this in RM.
I basically combined the ancestors and descendants queries into the one.
I removed your options for non-birth/biological parents and I had to remove the run-time
variables altogether to make it compatible with my utility.
When this is in your ini files, I don't expect you'll want to change
the start person too often.
--===========================================DIV50==
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 RelFather=0
UNION
SELECT PersonID, ChildTable.ChildID FROM PersonTable
LEFT JOIN FamilyTable ON PersonID=MotherID
LEFT JOIN ChildTable USING(FamilyID)
WHERE RelMother=0 ),
--
descendant_of_person(DescendantID) AS
(SELECT ChildID FROM AncestorList
UNION
SELECT ChildID FROM child_of
INNER JOIN descendant_of_person ON ParentID = DescendantID ),
--
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 RelFather=0
UNION
SELECT PersonID, MotherID AS ParentID FROM PersonTable
LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
LEFT JOIN FamilyTable USING(FamilyID)
WHERE RelMother=0 ),
--
ancestor_of_person(AncestorID) AS
(SELECT ParentID FROM parent_of
-- SPECIFY START PERSON HERE
WHERE ChildID= 1 -- SPECIFY START PERSON HERE
UNION --ALL
SELECT ParentID FROM parent_of
INNER JOIN ancestor_of_person ON ChildID = AncestorID ),
--
AncestorList (ChildID) AS
(SELECT AncestorID FROM ancestor_of_person, PersonTable
WHERE ancestor_of_person.AncestorID=PersonTable.PersonID )
--
SELECT DescendantID as PersonID
FROM descendant_of_person
--===========================================DIV50==
For future reference-
The utility and prepared SQL to use with it, can be found at
https://richardotter.github.io

Thanks, Tom

One of the queries I'm using often is one that I created from your recursive queries from years ago.

Recursive Queries – Ancestors and Descendants #recursive #ancestors #descendants

Thanks for doing the pioneering work with recursive queries.

I selects all of a person's cousins (but not spouses). I never figured out how to do this in RM.
I basically combined the ancestors and descendants queries into the one.

I removed your options for non-birth/biological parents and I had to remove the run-time
variables altogether to make it compatible with my utility.
When this is in your ini files, I don't expect you'll want to change
the start person too often.

--===========================================DIV50==
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 RelFather=0
UNION
SELECT PersonID, ChildTable.ChildID FROM PersonTable
LEFT JOIN FamilyTable ON PersonID=MotherID
LEFT JOIN ChildTable USING(FamilyID)
WHERE RelMother=0 ),
--
descendant_of_person(DescendantID) AS
(SELECT ChildID FROM AncestorList
UNION
SELECT ChildID FROM child_of
INNER JOIN descendant_of_person ON ParentID = DescendantID ),
--
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 RelFather=0
UNION
SELECT PersonID, MotherID AS ParentID FROM PersonTable
LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
LEFT JOIN FamilyTable USING(FamilyID)
WHERE RelMother=0 ),
--
ancestor_of_person(AncestorID) AS
(SELECT ParentID FROM parent_of
-- SPECIFY START PERSON HERE
WHERE ChildID= 1 -- SPECIFY START PERSON HERE
UNION --ALL
SELECT ParentID FROM parent_of
INNER JOIN ancestor_of_person ON ChildID = AncestorID ),
--
AncestorList (ChildID) AS
(SELECT AncestorID FROM ancestor_of_person, PersonTable
WHERE ancestor_of_person.AncestorID=PersonTable.PersonID )
--
SELECT DescendantID as PersonID
FROM descendant_of_person

--===========================================DIV50==

For future reference-
The utility and prepared SQL to use with it, can be found at
https://richardotter.github.io