SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Source Templates #sources #sourcetemplates #rmnocase #conversion #editable

Posted on by Tom Holden

This page collects info and queries about Source Templates.

A Trio of Templates

Framing Citations of the 1930 US Census with RootsMagic 5 Free Form, Standard and Custom Source Templates.
This is a 15 page study of the usage and results of three very different templates, coming to the conclusion that RootsMagic 5’s Source Template feature is still buggy and incomplete and that a custom variant of the built-in Free Form has advantages over other templates.
Source Templates, A Comparative Example.pdf
For an argument against the conclusions of this study, see Rebuttal by Jeff La Marca.

User-Editable Copies of Master Source Templates

The RootsMagic user is blocked from editing the ‘built-in’ Source Templates. Within RootsMagic 4&5, you can copy individual templates; the copy is user-editable. Alternatively, you can import all 413 of the Master source templates as user-editable copies from this file.
RootsMagicSTuser.rmst Updated 6 Jan 2012 to the RM5 templates.

The templates have all been renamed with a leading asterisk and will precede the uneditable ones in the Source Templates List.

Convert Sources With Uneditable Templates to Editable

Revised 18 Feb 2010

The superseded procedure picked out only those built-in templates actually used in the database, copied them to user-editable templates and re-pointed the Sources accordingly. It was later discovered that future importing of user-defined templates could result in a collision of TemplateIDs preventing further imports so that procedure has been abandoned. This new procedure requires the importing of RootsMagicuser.rmst into the Source Templates List first, using RootsMagic itself. The SQLite procedure is then run to match those built-in templates, which are used by Sources in the database, with the corresponding editable one and generates a result set containing SQL UPDATE commands to revise the SourceTable. These commands are copied to a SQL editor and run against the database.

No further work is required such as copying Source data over to the new Source and likewise for each citation of that source. For a large source list using several built-in templates requiring modification and extensively cited, the savings in effort would be significant.

N.B. All these procedures modify tables in the RootsMagic database and require a SQLite manager that can provide a RMNOCASE collation or spoof thereof. SharpPlus SQLite Developer has been used successfully with these procedures. SQLiteSpy with a fake RMNOCASE extension loaded should also work as will SQLite Expert Personal.

SrcTmpltsConvert2.sql Converts Sources from using built-in templates to the previously imported user editable copies of same. rev 2014-06-15 to match names of built-in templates with names of user-editable copies prepended by the asterisk used in the current RoostMagicSTuser.rmst file, rather than the underscore character of the initial file.
SrcTmpltsRevert.sql Reverts a database having been processed by the superseded procedure (SrcTmpltsConvert.sql) to its pre-conversion state.
SrcTmpltsRevert2.sql Reverts a database having been processed by the new procedure (SrcTmpltsConvert2.sql) to its pre-conversion state. N.B. revise with asterisk instead of underscore as the prepended character for user-editable copies of built-in templates.

List of Source Templates used and by what

This query is on a page of its own.

Fix Extra Line Feeds in Footnote

Vital Records (state-level, online derivatives)

This problem was first reportedby David E. Cann on 1 Oct 2011 in the ROOTSMAGIC-USERS newsgroup. The sentence template for the long footnote for this source type contains two extraneous Carriage Return control codes which results in unwanted whitespace when it is printed in a report’s footnotes and endnotes, as well as in the Source List report. It appears to be present in the RootsMagic.st file from 3 Feb 2010 and may even pre-date that file. Correcting it in RootsMagic.st using a no-wrap text editor appears to neither affect a pre-existing database nor, surprisingly, pass it on to new database files created post-correction. It’s as though the Source Template specifications contained in RootsMagic.st are now embedded in the program file (ver 4.1.2.1 20 Sep 2011) but the program has not been stripped of all the vestiges of the old way templates were distributed.

Until the developers release an update of RM4 that corrects this template and previously created databases, the following SQLite query may be the only way to remove the extra lines from within the database. Of course, one can edit a report’s RTF file but that would have to be done every time the report is generated. Better to fix the fault at source.

UPDATE SourceTemplateTable SET Footnote = '<[Jurisdiction] ><[Creator], ><?[ItemTitle]|"[ItemTitle]," <[ItemType:Lower:Abbrev], >><i>[WebsiteTitle]</i><?[ItemTitle]| |, <[ItemType:Lower:Abbrev] >>([URL] : <[AccessType]|accessed> [AccessDate]), [ItemOfInterest].'
 WHERE Footnote LIKE '<[Jurisdiction] ><[Creator], ><?[ItemTitle]|"[ItemTitle]," <[ItemType:Lower:Abbrev], >><i>[WebsiteTitle]</i><?[ItemTitle]| |, <[ItemType:Lower:Abbrev] >>([URL] : <[AccessType]|accessed> [AccessDate]), [ItemOfInterest].';

A Better Free Form Template

This section has been moved to a new page: Source Template, A Better Free Form

Discussions & comments from Wikispaces site


ve3meo

Attempted correction of existing databases

ve3meo
31 December 2009 19:52:10

The proprietary RMNOCASE collation sequence prevents the 3rd party SQLite managers from updating sourcetemplatetable. I had thought to offset the corrected templates TemplateID by 11000, save as a .rmst file, import as user defined templates, delete all records with TemplateID < 10000, and then subtract 11000 from TemplateID where it’s >= 11000. But none of the 3 or 4 SQLite managers I tried would allow the transaction to complete|commit|end. RMNOCASE always got in the way.

A wild card would be to do the import as I describe, create a new table identical to sourcetemplatetable without RMNOCASE, populate it with the TemplateID >=10000 from existing sourcetemplatetable, do the subtraction on the new table (that works), delete the old table, rename the new table to sourcetemplatetable, recreate the index. I wonder what would happen???
TomH

Group – Descendants #descendants #namedgroup #recursive

Posted on by Tom Holden

This query creates or updates a named group of descendants of a given person. This new query is unlimited in the number of generations as it is based on Recursive Queries – Ancestors and Descendants which exploit capability introduced to SQLite in Feb 2014. When run, you will be prompted for the Record Number (RIN) of the person whose descendants are to be in the group and for the type of descendant relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).

Group-Descendants.sql

-- 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
);

Group – Ancestors #ancestors #namedgroup #recursive

Posted on by Tom Holden

This query supersedes Ancestors Named Group which was limited to 12 generations due to its dependence on the non-recursive Ancestors Query. This new query is unlimited as it is based on Recursive Queries – Ancestors and Descendants which exploit capability introduced to SQLite in Feb 2014. When run, you will be prompted for the Record Number (RIN) of the person whose ancestors are be in the group and for the type of ancestral relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).

Group-Ancestors.sql

-- 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
    );

Ancestors Query – Recursive #recursive

Posted on by Tom Holden

The result of this query is a list of the Record Numbers of the birth or bloodline ancestors of a given person. Not very useful by itself, it is a first effort at a recursive query exploiting the WITH RECURSIVE syntax and common-table-expressions support introduced in SQLite 3.8.3 2014-02-03. The earlier RecursiveAncestors.sql

-- RecursiveAncestors.sql
/*
2014-04-13 Tom Holden ve3meo
 
Generates the list of RINs for the ancestors of a person.
 
Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
*/
 
/* modelled on https://www.sqlite.org/lang_with.html example:
 
The next example uses two common table expressions in a single WITH clause. The following table records a family tree:
 
CREATE TABLE family(
  name TEXT PRIMARY KEY,
  mom TEXT REFERENCES family,
  dad TEXT REFERENCES family,
  born DATETIME,
  died DATETIME, -- NULL if still alive
  -- other content
);
The "family" table is similar to the earlier "org" table except that now there are two parents to each member. We want to know all living ancestors of Alice, from oldest to youngest. An ordinary common table expression, "parent_of", is defined first. That ordinary CTE is a view that can be used to find all parents of any individual. That ordinary CTE is then used in the "ancestor_of_alice" recursive CTE. The recursive CTE is then used in the final query:
 
WITH RECURSIVE
  parent_of(name, parent) AS
    (SELECT name, mom FROM family UNION SELECT name, dad FROM family),
  ancestor_of_alice(name) AS
    (SELECT parent FROM parent_of WHERE name='Alice'
     UNION ALL
     SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
 WHERE ancestor_of_alice.name=family.name
   AND died IS NULL
 ORDER BY born;
 
*/
 
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 RelFather=0 --birth father (remove WHERE constraint 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 RelMother=0 --birth mother (remove WHERE constraint to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of
       WHERE ChildID=$Person(RIN) --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
;

Don’t ask me how it works – I’m not sure…

 

Ancestors Named Group #namedgroup #pedigree

Posted on by Tom Holden

Intro

RootsMagic 4 users have been seeking an improvement to Named Groups, namely "dynamic refresh" or, at least, a manual refresh button. The idea is that a group’s membership should be updated on demand following the rules that set it up in the first place. Currently, a group is static, requiring the user to edit the group, setting up the rules all over again.This query provides a manual refresh for a specific group, i.e., the ancestors of a specified person. While not the most challenging criterion for group membership and thus not a big time-saver, nonetheless it demonstrates the principle upon which more complex rules could be based. AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.

Download

AncestorsGroup.sql
NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST

Usage

  1. Backup your database before you do this every time until you are confident that it does no harm.
  2. In RM4, create a named group having the words "ancestors" and "#RIN" in it, where RIN is the number of the person whose ancestors are to be in the group, e.g., "Tom’s ancestors #269". Do not leave this group empty, add anyone immediately or do the next step before creating any more new groups (bug in RM4).
  3. Open your RM4 database with SQLite Expert Personal and load AncestorsGroup.sql into the SQL editor.
  4. Execute the query (F5)
  5. You will be prompted to enter a value for @StartRIN: enter the number of the person in the name of the group, in this example "269" (without quotes and the leading #).
  6. When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.

Discussion

The RM4 GroupTable allows for group members to be defined in ranges of consecutive numbers between a StartID and EndID, thus shortening the table versus one row per member. This script makes no attempt to use this capability so it consumes one row per member with EndID=StartID. I don’t know if there is any significant efficiency gained as a result of ranges.

Discussions & comments from Wikispaces site


ve3meo

Comment: “AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.‍”

ve3meo
03 September 2018 19:34:25

ve3meo Apr 13, 2014

This query would benefit from Ancestors+Query+-+Recursive . Unlimited number of generations and a much shorter, faster query.

Citations – Delete Duplicates #citations #sources #duplicates

Posted on by Tom Holden

In RootsMagic 4 (and maybe still in RM6), merging databases having common people with identical citations has resulted in duplicate citations for the merged person or couple and their events. These are counted in RM’s database properties report but not flagged as a problem and may not be detected until running a report or editing a person. An early SQLite query provides a more comprehensive report – see the page RMGC_Properties – Query and/or the RMtrix program. Another early query generates a spreadsheet report of all citations with a column counting duplicates – see All Citations & Dupes Count – Query, from which one could work to locate all instances and manually delete them one at a time. In March, 2014, RootsMagic Forums user Vyger posted under a 2010 topic on anomalies from merging that he was faced with a large number of duplicate citations and no easy way to get rid of them – pick up the the discussion here. He was sent the script below and reported that it successfully cleaned out all 916 duplicates with no apparent side effects. As this script may be useful to others, it is posted for the first time here, four years after its initial development.

Caveat Emptor: the script was written at the time of RM4 and does not check for differences between webtags for citations, a feature introduced later, and some other long-established fields, e.g. Quality. The Media system also changed from RM4 so the script may not handle differences in citation media tags correctly. Therefore, be sure to run this on a copy of your database, not your master, and review carefully before adopting the resulting database to go forward.

Citations-Dupes-Delete.sql

-- Citations-Dupes-Delete.sql
-- 2010-02-13 ve3meo
-- Deletes duplicate citations for each person, family and events
-- Uses LEFT OUTER JOINS to include the most orphaned citations
--     Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
--     and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
--     reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
--     Requires a temp table because of speed degradation when incorporated in main selects;
--     filtering can be done on screen in SQLiteDeveloper.
--
--     QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
--      managers merely say BLOB.
-- rev 2010-12-11
--  align with RMGC_Properties procedure; prev version was too loose.
--
-- BEGIN
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations
;
CREATE TEMP TABLE tmpCitations (CITID INTEGER, SrcID INTEGER, RIN INTEGER, Uniq INTEGER, RinCitation TEXT);
 
INSERT INTO tmpCitations
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || 'Personal' || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)
  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Couple citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=1 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- Citations for Alternate Names
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  n.OwnerID || 'Alternate Name' || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=7 AND +n.IsPrimary=0
-- GROUP BY RinCitation
ORDER BY rincitation
;
 
-- Delete Duplicates
DELETE FROM CitationTable
 WHERE CitationID IN
  (
   SELECT c.CitID
    FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
    ORDER BY c.CitID
   )
;
 
-- Count duplicates
--SELECT '- Duplicate Citations', COUNT(1), 'identical in most respects, cluttering reports'
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;
 
-- List Duplicates
--SELECT c.CitID
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;

Group – Unconnected Persons #namedgroup

Posted on by Tom Holden

Problem

I have some experience with SQL but would like to create a query that would create a named group of Orphans that would contain the people in the file that have no parents, spouses, or children. These people are most likely remnants from deleting trees in my old program that I used. Another reason is that one of my gedcoms had family information missing so the entire file imported as individuals that were unconnected.

In the sample file there is only one orphaned person in the file. In my personal file there are actually hundreds of them. Since the file is large, I would like to be able to run the query that would update the named group as I work through merging two large files (over 300,000 people) so I can try to keep track of who is still orphaned in the tree.

sample data.rmgc

A Solution

This approach involves two steps of development:

  1. Script to store the RIN (PersonID) of these so-called “Orphans” in a temporary table
  2. Script to create/update a Named Group from the temp table

They are combined into a single script downloadable from the bottom of the page. It is efficient: in a database of 160,000 people, it created a group of the 1000 unconnected persons in ~3 seconds. It takes longer than that for RootsMagic to initially display the group in the sidebar; People View displays the group very quickly.

A message about this solution has been posted to the RootsMagic Forums at https://forums.rootsmagic.com/index.php?/topic/13326-dynamic-group-for-unconnected-persons/.

The “Orphans” Table

-- Persons-NoParentNoSpouseNoChild.sql
/*
2014-01-23 Tom Holden ve3meo
2014-01-24 rev changed table name to more generic
 
Creates a temporary table xGroupTempTable of PersonIDs (RINs)
of those who have neither a parent nor a spouse nor a child.
To be used with another procedure to update a named group.
*/
DROP TABLE
IF EXISTS xGroupTempTable;
 
CREATE TEMP TABLE
IF NOT EXISTS xGroupTempTable AS
    SELECT *
    FROM (
        --- Persons not in FamilyTable, either no spouse or no child
        SELECT PersonID
        FROM PersonTable
 
        EXCEPT
 
        SELECT *
        FROM (
            SELECT FatherID AS PersonID
            FROM FamilyTable
 
            UNION
 
            SELECT MotherID AS PersonID
            FROM FamilyTable
            )
        ) NATURAL
    INNER JOIN (
        -- persons with no parent
        SELECT PersonID
        FROM PersonTable
 
        EXCEPT
 
        SELECT ChildID
        FROM ChildTable
        );

Try the above script. Examine the temp table and look up some persons identified therein using RootsMagic to confirm that it is working as expected.

The “Orphans” Named Group

This script was extracted and adapted to work with the above script from Group – Persons with Text Dates. It creates and maintains a RM Group named “SQL: Unconnected Persons” from the temporary table xGroupTempTable.

After running this script with RootsMagic open simultaneously on the same database, it is necessary to refresh the Sidebar Group display. One does so by selecting a different group and re-selecting “SQL: Unconnected Persons”. Unfortunately, if this is the only group in the Groups list, you may have to close and reopen the database to refresh the list of members.

-- Group-UnconnectedPersons.sql
/*
2014-01-24 Tom Holden ve3meo
Creates and updates a named group of persons
having no parent, no spouse, nor a child in the database
from a temp table xGroupTempTable created by another script
*/
-- Create Named Group if it does not exist 'SQL: Unconnected Persons'
INSERT
    OR IGNORE
INTO LabelTable
VALUES (
    (
        SELECT LabelID
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        )
    ,0
    ,(
        SELECT ifnull(MAX(LabelValue), 0) + 1
        FROM LabelTable
        ) -- ifnull() needed if LabelTable is empty
    ,'SQL: Unconnected Persons'
    ,'SQLite query'
    );
 
-- Delete all members of the named group
DELETE
FROM GroupTable
WHERE GroupID = (
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        );
 
-- Add members to the named group
INSERT INTO GroupTable
SELECT NULL
    ,(
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        )
    ,PersonID AS StartID
    ,PersonID AS EndID
FROM (
    SELECT DISTINCT PersonID
    FROM xGroupTempTable
    );

Download complete script

This file combines the two scripts into one so that a single pass takes care of updating the group. Group-UnconnectedPersons.sql

Discussions & comments from Wikispaces site


ve3meo

Comment: “keep track of who is still orphaned”

ve3meo
03 September 2018 21:24:57

ve3meo Jan 22, 2014

Have you tried RM’s Tools > Count Trees function? Each orphan will be listed as a tree with a Count of 1 and the Start Person will be the orphan.
mom2twinzz Jan 22, 2014

Yes. However, since I am dealing with potentially hundreds of these orphans, grouping them is more helpful than having the tree finder find each tree each and every time I am trying to connect a person.

What happened is that I had a computer malfunction that apparently truncated my gedcom from Legacy family tree. I no longer have the original LFT file, so direct importing the file into RM is impossible. Looking at a tree count, I have over 11,000 trees more than 10000 are orphaned trees, but that have information that I still want to merge in. Automatic merging has failed since the information was conflicting (middle names exist in one file but not the other, etc.) and failed to merge most of the people. So I am forced to manually merge people. Having a named group of orphans helps me to keep track of where I am in the merge process.

Facts – Reference Numbers for person, spouse and parents. #facttypes #events #refno

Posted on by Tom Holden

Purpose

This page responds to a request posted in the RootsMagic Forums by Forum member JoyceAE5 on 21 Jan 2014.

Basically, what I want is a list of everyone in my database with the following information:
 
Person's RefNo, Person's Name, Spouse's RefNo, Spouse's Name, Father's RefNo, Father's Name, Mother's RefNo & Mother's Name

Building Blocks

List of RINs and Names

It’s easy enough to get every person’s RIN and Name:

SELECT Per.PersonID AS "RIN"
    ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.Given AS
    "Name"
FROM PersonTable Per
INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
    AND +Nam.IsPrimary -- excludes Alternate Names;

Sample results: 

RIN Name
...
286 CLENDENON , Magdalena
287 HOLDEN , Abigal
288 HOLDEN , Barbery
289 MCDONALD , Samuel Charles
...

List of RINs and RefNos

What about each Person’s RefNo value?

SELECT Per.PersonID AS "RIN"
    ,CAST(Evt.Details AS TEXT) AS "RefNo"
FROM PersonTable Per
LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
    AND Evt.EventType = 35 -- the FactTypeID for the Reference Number fact is 35
    AND Evt.OwnerType = 0
    -- restricts to events for individuals; redundant in this case because the FactType is so restricted
    ;

which gives:

RIN    RefNo
...
328    Groves340
329    Groves284
329    HLAF139
330
...

Note that RIN 329 has two RefNo and 330 has none. If many persons have two or more RefNos, the number of combinations reported will go up exponentially; if a person, her spouse and her parents all have two RefNos, that person will be listed 16 times (2^4).

Combine two lists into a lookup table

Since we want the RefNo and Name for each Person, his spouse and parents, it would be most efficient to combine these results in a single lookup table so that the processing need not be repeated for each person and these relatives. That’s achieved by JOINing the two queries on the common field, RIN and storing a query of the results to a temporary table:

DROP TABLE IF EXISTS xNamesRefNoTable;
 
CREATE TEMP TABLE IF NOT EXISTS xNamesRefNoTable AS
    SELECT *
    FROM (
        SELECT Per.PersonID AS RIN
            ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.
            Given || ' -' || Per.PersonID AS NAME
        FROM PersonTable Per
        INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
            AND + Nam.IsPrimary -- excludes Alternate Names;
        ) NATURAL
    INNER JOIN (
        SELECT Per.PersonID AS RIN
            ,CAST(Evt.Details AS TEXT) AS RefNo
        FROM PersonTable Per
        LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
            AND Evt.EventType = 35
            -- the FactTypeID for the Reference Number fact is 35
            AND Evt.OwnerType = 0
            -- restricts to events for individuals; redundant in this case because the FactType is so restricted
        );

Sample from this table, displayed as tab-delimited:

RIN    NAME    RefNo
66    FITCHETT Sr., U.E.,  Joseph -66    Groves073
66    FITCHETT Sr., U.E.,  Joseph -66    HLAF337
67    MCARTHUR ,  Nancy Ann -67    Groves074
68    FITCHETT ,  Dennis -68    Groves075
68    FITCHETT ,  Dennis -68    HLAF361

Note that the RIN has also been appended to the Name for convenience.

Report Query

Now to put together the Person, Spouse and Parents. I’m leaping to a complete report query that uses the temporary table created above, rather than building it up piece-meal. If you examine it from the inside out, you will see how it builds up a query listing the RINs of the person, spouse, father and mother. Then the temporary table is looked up from each RIN to retrieve the corresponding RefNo and Name. 

SELECT RINS.RIN
    ,Person.[RefNo]
    ,Person.[NAME]
    ,Spouse.[RefNo]
    ,Spouse.[NAME]
    ,Father.[RefNo]
    ,Father.[Name]
    ,Mother.[RefNo]
    ,Mother.NAME
FROM (
    SELECT Pert.PersonID AS RIN
        ,Spouses.SpouseID
        ,Parents.[FatherID]
        ,Parents.MotherID
    FROM PersonTable Pert
    LEFT JOIN (
        -- Get RIN of Spouse (MotherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[MotherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[FatherID]
 
        UNION
 
        -- Get RIN of Spouse (FatherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[MotherID]
        ) AS Spouses ON Pert.[PersonID] = Spouses.RIN
    LEFT JOIN (
        -- Get RINs of Parents
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID]
            ,Fam.[MotherID]
        FROM PersonTable Per
        LEFT JOIN ChildTable Child ON Per.PersonID = Child.[ChildID]
        INNER JOIN FamilyTable Fam USING (FamilyID)
        ) AS Parents ON Pert.[PersonID] = Parents.RIN
    ) AS RINS NATURAL
INNER JOIN xNamesRefNoTable AS Person
LEFT JOIN xNamesRefNoTable AS Spouse ON RINS.SpouseID = Spouse.[RIN]
LEFT JOIN xNamesRefNoTable AS Father ON FatherID = Father.[RIN]
LEFT JOIN xNamesRefNoTable AS Mother ON MotherID = Mother.[RIN];

Sample output

RINS.RIN Person.[RefNo] Person.[NAME] Spouse.[RefNo] Spouse.[NAME] Father.[RefNo] Father.[Name] Mother.[RefNo] Mother.Name
917 HLAF061 HARTLEY , Florence -917 HLAF102 BLAKESTON , Sidney -918 HLAF039 HARTLEY , Rev. George -875 HLAF040 COWIESON , Annetta Jane -876
918 HLAF102 BLAKESTON , Sidney -918 HLAF061 HARTLEY , Florence -917
919 HLAF103 BLAKESTON , Zella -919 HLAF102 BLAKESTON , Sidney -918 HLAF061 HARTLEY , Florence -917

Download Final Script

The whole script combining all the queries can be run in one fell swoop. Download it here: Facts-RefNos_person_spouse_parents.sql

Privatize Living #private #reports #gedcom

Posted on by Tom Holden

This script sets private the events, alternate name facts, and personal notes for persons whose Living flag is set. Thus, reports and GEDCOM can take full advantage of the options:

  • Privatize living people (GEDCOM only)
  • Include private facts
  • Include {private} notes
  • Strip { } brackets

thus providing many more combinations by which these outputs may be tailored.

This is a first pass script, lightly tested so use the usual precautions and feedback any problems. There is a known problem with privatizing notes that are already privatized – RM may output a closing brace character “}”.

PrivatizeLiving.sql

-- PrivatizeLiving.sql
/* 2013-12-14 Tom Holden ve3meo
Privatizes events, alt names, personal notes for persons with Living flag set
so that reports can be made with names of living persons but no details while
full details are outputted for the deceased.
 
To Do: privatize family note when one spouse is Living.
 
N.B.: There is a series of queries in a comment block at the end which can be run
to UNDO the privatization, it is nevertheless advisable to run this script against
a copy of your database in case the process is not perfectlt reversible. Of course,
you may wish to keep the resulting privatization if it suits your purposes so that
reversal is unnecessary.
*/
 
-- Make a table of persons marked Living
DROP TABLE IF EXISTS xLivingTable
;
CREATE TEMP TABLE IF NOT EXISTS xLivingTable
AS
SELECT PersonID, Note, TRIM(CAST(Note AS TEXT)) AS NoteTxt FROM PersonTable WHERE Living
;
 
-- Make a backup table of EventIDs and their IsPrivate setting
-- for all persons marked Living; must cover individual and family events
DROP TABLE IF EXISTS xLivingEventsBak
;
CREATE TABLE IF NOT EXISTS xLivingEventsBak
AS
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Individual events
INNER JOIN xLivingTable Lv ON E.OwnerID = Lv.PersonID AND E.OwnerType = 0
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living husband
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.FatherID = Lv.PersonID
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living wife
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.MotherID = Lv.PersonID
;
 
-- SET EventTable.IsPrivate = 1 for all events in xLivingEventsBak
UPDATE EventTable
SET IsPrivate = 1
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
 
/*
-- Alternate Names can also be set private and perhaps desirably so so we repeat
the pattern above
*/
-- Make a backup table of non-primary NameIDs and their IsPrivate setting
-- for all persons marked Living;
DROP TABLE IF EXISTS xLivingNamesBak
;
CREATE TABLE IF NOT EXISTS xLivingNamesBak
AS
SELECT NameID, IsPrivate FROM NameTable
WHERE OwnerID IN
(SELECT PersonID FROM xLivingTable)
ORDER BY NameID
;
 
-- Privatize Alternate Names for Persons marked Living in the database
UPDATE NameTable SET IsPrivate = 1
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
 
-- PRIVATIZE Personal Notes for persons marked Living by enclosing in braces {}
UPDATE PersonTable
SET Note =
--SELECT
CAST(REPLACE(CAST(Note AS TEXT), CAST(Note AS TEXT), '{' || CAST(Note AS TEXT) || '}') AS BLOB)
--FROM PersonTable
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
 
/*
-- BLOCK OF UNDO queries to reverse the above changes.
 
-- UNDO Privatize events by SET EventTable.IsPrivate = 0 for all events in
  xLivingEventsBak that have IsPrivate = 0
UPDATE EventTable
SET IsPrivate = 0
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
-- UNDO privatization of Alt Names for Living persons
UPDATE NameTable SET IsPrivate = 0
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
-- UNDO Privatize Personal Notes by updating from xLivingTable
UPDATE PersonTable
SET Note =
(SELECT Note FROM xLivingTable Lv WHERE PersonTable.PersonID = Lv.PersonID)
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
*/
-- END of script