SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Events – Move Short Note to Description #events #tmg #notes

Posted on by Tom Holden

Do you have a lot of empty Description fields for facts or events that you would like to fill with short contents from the corresponding Note field? This might be the case for certain fact types after an import from TMG, directly or via GEDCOM. Or maybe you have chosen to enable the Description field for a fact type after adding many with sentence-useful content in the Notes. This script may help you with that chore.

The query moves content from Event Note to empty but enabled Event Description if the note is 100 chars or less for all such events of a selected Fact Type. To select a Fact Type, you must use a SQLite manager that supports SQLite run-time variables, such as SQLite Expert Personal (SQLiteSpy does not). When the query is run, a box will pop out prompting for the entry of the Fact Type’s abbreviation. This is the label as seen in the Edit Person screen. By entering the wildcard character (%), all Fact Types having the Description enabled will be processed.

EventNoteToDescription-Move.sql

Some TMG users exploit its split-Memo capability in which the text is sub-divided by the “||” separator. It may be that a variant of this query would move only the first part (M1) to the Description field provided it is 100 characters or less.

Conversely, given that imported Descriptions may contain the separator, a query that would move everything after it from Description to Note might be useful.

Comments?

Discussions & comments from Wikispaces site


robertjm

moving only part, based upon separator, would be awesome!

robertjm
25 December 2015 21:07:42

I’d love to be able to move only a part of the Note to different fields. When I first used TMG I made copious use of the Note field, with separators. Now, I’m finding I want to move stuff around. But, I want to be particular of what I move, and where. Having a rudimentary understanding of Sqllite, I’m not able to “role my own” right now.


ve3meo

ve3meo
25 December 2015 21:18:34

To be able to programmatically extract something from the Note field requires that it be delineated systematically.

Notes Invisible Revealed #notes #tmg #gedcom

Posted on by Tom Holden

GEDCOM files imported from The Master Genealogist and probably other sources may exhibit disappearance of citations and notes for tags in the TMG Name Group. Citations and memos for tags from the Name group that export to the GEDCOM NAME tag will disappear from those that are the first or only NAME tag imported by RootsMagic. They are in the database – just in places you cannot get at through the current user interface. The loss of citations from GEDCOMs of other origins was addressed with SQLite queries in Citations Invisible Revealed. This page provides queries to address the Notes issue.

Notes, Invisible – List.sql Lists the persons and their invisible notes.

Notes, Invisible – Convert to General.sql Appends the invisible note from the primary name to the general note for the person and deletes the first.

Ancestry.com Sync #ancestrycom #gedcom #ftm2014

Posted on by Tom Holden

Under development

This page attempts to find the best practices for working between RootsMagic and Ancestry.com with highest completeness, fidelity and efficiency. It supersedes an earlier page, Ancestry.com and RootsMagic 5, as a result of new understanding of the roles of certain custom GEDCOM tags and a change in what is exported by Ancestry.com.

Summary

Upload RootsMagic GEDCOM to Ancestry.com

  • For previously downloaded Ancestry source references to be recognised by Ancestry, convert the values stored in notes to custom “_APID” tags.
  • Be aware of the RootsMagic database fields not exported or its GEDCOM tags ignored by Ancestry.com

Download Ancestry.com GEDCOM to RootsMagic

  • For ShareMerge to operate on persons previously uploaded, find all “1 UID” and replace with “1 _UID”
  • For Ancestry source references to be preserved, find all custom “_APID” tags and convert to notes.
  • Be aware of the Ancestry Family Tree field values that are not exported

Ancestry.com GEDCOM export/import shortcomings

The GEDCOM exported from Ancestry.com (actually ancestry.ca in my case) is incomplete. Nor does its import include all the values exported. Uploading the GEDCOM to a new Ancestry Member Tree and comparing it to the original tree is a quick way of finding the deficiencies in the export-import round trip. The following have been found to be missing in the imported tree; those asterisked are exported but not imported:

Overview

  • Web Links

Media Gallery

  • Media* (link to a page for each uploaded item is exported but import does nothing with it)

Source

For a user defined source:

  • Publisher Location
  • Publisher Date

Repository

For a user defined repository:

  • Address*
  • Phone Number*
  • Email*
  • Call Number*
  • Notes*

Source Citation

For a user defined source:

Citation Information

  • Web Address moved into “Other information”*

ShareMerge Persons using RM’s UniqueID

There is a way to transport RootsMagic’s internal UniqueID for a person uploaded to an Ancestry Family Tree and have it returned to a RootsMagic database without jumping through the hoops with the custom RMID event I described in the RootsMagic Forums in this post. Ancestry imports the value of the _UID tag exported by RootsMagic, stores it invisibly, and exports it with a UID tag. RootsMagic does not recognise the UID tag but does if it is prepended with the underscore, the same as it exports. The value is imported into the UniqueID for that newly created person in a RM database. If there are two persons in a database with the same UniqueID, ShareMerge merges them.
It is a simple text editor search and replace to find all “1 UID” and replace with “1 _UID” in the GEDCOM exported from Ancestry but it is a bit of a nuisance. Persons in the AFT that did not originate from the RM export but were created within Ancestry are NOT accompanied by a UID tag.
It would be desirable that either Ancestry exported the UID value with the same tag that it saw on import OR that RootsMagic would recognise the UID tag as equivalent to its _UID tag.

Syncing Ancestry Sources via the Ancestry _APID Tag

TBD

Manually Adding _APID Tags for Ancestry Sources

The following is adapted from a post in the ReunionTalk Forum

On manually citing a source from Ancestry, add the Ancestry APID tag to the Source Details Comments, in the same format as the process that converts such tags in an Ancestry GEDCOM to a Comment.

An advantage in doing this is that you can do all your research using RootsMagic, adding a _APID tag detail against each source citation; export the GEDCOM; run a regular expression search and replace to adjust the GEDCOM file and when importing into Ancestry.com, the source citations are all linked to the original Ancestry source records. A disadvantage is it takes alot more effort and validating that the APID details are correct.

The format is _APID 1,<ID>::<Header #> where <ID> is a unique value for each Ancestry source record type and <Header #> is the header which can be extracted from the URL.

The APID tags are found in the GEDCOM file for Ancestry sources that have been saved in your tree. In Ancestry, click on Manage Tree and there is an Export Tree button to click (it takes a few seconds to generate and then you have an option to download the file). This saves a GEDCOM file (text file) which when opened contains the APID tags for each citation (i.e. search for _APID).

Ancestry to my knowledge use these tags internally in their web site and FTM software but the GEDCOM file you export, will show these (provided you have saved Ancestry sources).

For example, a residence event would look like this (some date and some address and source# would be specific to your research entry. The APID details would be specific to the Ancestry record for this research.

1 RESI
2 DATE Some date
2 PLAC Some address
2 SOUR @Source#@
3 PAGE Some date
3 _APID 1,1836::21884408

The format is 1, followed by a APID database tag (sounds complicated but each record collection in Ancestry has a separate tag ID number followed by 2 colons, followed by the header tag for your record. When you look at the URL for each Ancestry record, there is a long text but it has a h=, the number that follows is the header.

This information is keyed into RootsMagic. Once you know the Ancestry tag for one type of records, e.g. UK census 1911, you can use this for all 1911 census citation details with only the header part changing for each. The example above is the tag ID for NZ electoral roll information.

In the above example, the citation Comment you would enter in RootsMagic would be “{_APID 1,1836::21884413}”, sans quotes.

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 http://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
--;