I thought I would post some results from a little project I’ve recently been working on. Consider a descendant narrative report in RM in any of the supported formats: outline, NEHGS, etc. In any of these reports, the parents of the spouses will be listed as a part of the birth sentence for the spouse, viz. “Jane Doe, daughter of John Doe and Sarah Smith, was born in 1848″. My project has been to assure that I have all the appropriate data and documentation for the parents, even though the data itself will not appear in this particular report except for the names of the parents.
My strategy is to color code such parents of spouses using navy as the color. Navy is not otherwise used as a color in my database. I can then do searches for individuals who are colored navy or make a Named Group out of them or whatever. In other words, I use my set of navy people as sort of a “to do list” for my research.I should explain that I have a Named Group in my database called Active_Research consisting of all the individuals who I am most actively working on. Everybody in my Active_Research group is color coded green and everybody who is color coded green is in my Active_Research group.
So suppose I want to color code as navy all the parents of the spouses of the descendants of John Doe, and by the way John Doe and all his descendants and their spouses are already color coded green and are already in the Active_Research group. So in RM, I do the following.
- Color code John Doe and all his descendants and spouses as teal. This temporarily disturbs their normal green color, but I will restore the green before I’m done. And just like navy, teal normally is never used anywhere in my database.
- Color code John Doe and all his descendants but not their spouses as green.
At this point, the direct descendants of John Doe have been restored to green, and the spouses have been colored as teal. So I need to color code the parents of all the teal people as navy, and then restore the teal people to green. I will then have restored all my green people to green, and will have color coded all the parents of the spouses of John Doe’s descendants as navy.
It’s trivial to restore the teal people to green from within RM, but before I do I need to color the parents of all the teal people as navy using SQLite scripts. The script to do so is included below. In order to accomplish the desired processing, I experimented with the idea of using views – something I had never done before in SQLite although I had used views before in other SQL environments.
My SQL tends to use lots of subqueries – probably many more than most people would use. It’s just my style, I guess. And I was thinking that all my subqueries might be a lot easier to develop, debug, and manage if they were views than if they were true subqueries.
To tell you the truth, I sort of went overboard. Because I usually code with lots of subqueries, when I replaced the subqueries with views I ended up with lots of views. Some of my views could have been combined, but splitting the views down into as many small pieces as I did made them really easy to develop.
The process of using views proved to be extremely easy. For each view, I would develop it and debug it as a standard query (not as a subquery). Once it was working, all I had to do to convert the query to a view was to precede it with a CREATE VIEW statement. I used CREATE TEMP VIEW to avoid storing the views themselves in my RM database.
If you look at my script, the first SQL statement that really does any processing of my RM database is the UPDATE statement itself. Within the UPDATE statement, there is a subquery which says “SELECT ParentID AS PersonID from ParentView”. ParentView is one of the views I created, and the reference to ParentView kicks off a whole chain of references to all the other views I created. A possible alternative coding style might have been an UPDATE statement that was 15 or 20 lines long with nested subquery on top of nested subquery. All that nested subquery stuff is still really happening, but it’s hidden in all the nested views.
By the way, I really wanted my UPDATE statement simply to say UPDATE ParentView SET COLOR = 10. Such a statement makes perfectly good sense to me, but you can’t update a view. So I had to update an RM table and move the reference to ParentView into a subquery.
Jerry
DROP VIEW IF EXISTS ChildView; DROP VIEW IF EXISTS TealView; DROP VIEW IF EXISTS FamilyView; DROP VIEW IF EXISTS MotherView; DROP VIEW IF EXISTS FatherView; DROP VIEW IF EXISTS ParentView; CREATE TEMP VIEW ChildView AS -- View to produce the RIN and FamilyID of everyone who has parents SELECT C.ChildID, C.FamilyID FROM ChildTable AS C ORDER BY C.ChildID; CREATE TEMP VIEW TealView AS -- View to produce the RIN of everyone who is colored Teal SELECT P.PersonID FROM PersonTable AS P WHERE Color = 13 -- color is teal ORDER BY P.PersonID; CREATE TEMP VIEW FamilyView AS -- View to produce the RIN and FamilyID of everyone who has parents and is colored teal SELECT N.PersonID, C.FamilyID FROM TealView AS N INNER JOIN ChildView AS C ON C.ChildID = N.PersonID ORDER BY FamilyID; CREATE TEMP VIEW MotherView AS -- View to produce the RIN of the mother of everyone who has parents and is colored teal SELECT F.MotherID AS ParentID FROM FamilyTable AS F INNER JOIN FamilyView AS G ON F.FamilyID = G.FamilyID ORDER BY F.MotherID; CREATE TEMP VIEW FatherView AS -- View to produce the RIN of the father of everyone who has parents and is colored Teal SELECT F.FatherID AS ParentID FROM FamilyTable AS F INNER JOIN FamilyView AS G ON F.FamilyID = G.FamilyID ORDER BY F.FatherID; CREATE TEMP VIEW ParentView AS -- View to produce the RIN of the parents of everyone who has parents and is colored Teal SELECT ParentID FROM MotherView AS ParentID UNION SELECT ParentID FROM FatherView AS ParentID ORDER BY ParentID; UPDATE PersonTable SET Color = 10 WHERE PersonTable.PersonID IN (SELECT ParentID AS PersonID FROM ParentView); -- Update parents of Teal people to be Navy people SELECT * FROM PersonTable WHERE Color = 10; -- Display the Navy people, Teal people will be restored to Green back in RM