Full length blog

Understanding the RootsMagic 8 database – Type Decodes #rm8 #datadefinitions

Many tables within RootsMagic have fields which are know to mean something but do not have lookup tables. So far I have collected the following: (more to follow)

Fields named OwnerType – see Understanding the RootsMagic 8 database – Ownership

Addresses

AddressType field

ValueMeaning
0Correspondence
1Repository

Citations

The Quality field in the Citation Link table is three characters, each character having meanings.

Character PositionValueMeaning
1PPrimary Information Quality
1SSecondary Information Quality
1~Unknown Information Quality
2DDirect Evidence Quality
2IIndirect Evidence Quality
2NNegative Evidence Quality
2~Unknown Evidence Quality
3OOriginal Source Quality
3XDerived Source Quality
3~Unknown Source Quality

Multimedia

Field MediaType defines the media format of the file

ValueMeaning
1Image
2File
3Sound
4Video

Names

The NameType field gives the type of the name

ValueMeaning
0undefined/any
1aka
2birth
3immigrant
4maiden
5married
6nickname
7other spelling

Places

PlaceType field

ValueMeaning
0Place
1LDS Temples (Built In)
2PlaceDetail

Tags

TagType field defines what is being tagged. This equates to the label table in RM7

ValueMeaning
0Group
1Task Folder

Tasks

TaskType field – this has changed from RM7

ValueMeaning
1Research Log/ Task Folder
2ToDo
3Correspondence

TaskStatus field – some changes in RM8

ValueMeaning
0New
1Open
2Completed
3Pending – On Hold
4Problem
5Cancelled

Understanding the RootsMagic 8 database – Ownership #database #datadefinitions #rm8

Contents

    The RootsMagic 8 database structure has seen a few changes from RM7 and one of these is that more objects can “own” other objects in the data. A task can have media, citations and web tags for example.

    To achieve this many tables have an OwnerType field and an OwnerID field and the owner type is consistent so for example 0 in owner type always means the owner is a person and the owner id is the person record number (RIN).

    In order to write code against this structure I have produced the following chart where items in the left hand column can be the owner and the items across the top are owned.

    Owner Type ValueOwner TypeCan Own URLCan Own PlaceCan Own Place DetailCan Own MediaCan Own TaskCan Own AddressCan Own CitationCan Own NameCan Own ChildCan Own Event
    0PersonY  YYYYY Y
    1Family   YYYY YY
    2Event YYYY Y  
    3SourceY  Y YY  
    4CitationY  Y     
    5PlaceY YYY    
    6TaskY  Y  YY  
    7Name   YY Y  
    8not used (was General task in RM7 with 0 ownerid)         
    14Place DetailY  YY    
    15not used (was Research Item in RM7)         
    18Task Folder (update of Research Log)    Y    
    Ownership table – RM8

    As a further note:
    When it says “Tags” in RM8 application it refers to Owner Links which can be edited at this point in the program

    When it says “Used” in RM8 application it refers to Owner Links but they are not editable at this point in the program

    When it says “Web Tags” in RM8 application it refers to URL

    Database Diagram for RM8

    Group Queries #namedgroup #colorcoding #rm8 #rm7

    Contents

      In RootsMagic 7 group names are stored in the LabelTable and the group members in GroupTable with group id and RIN. The LabelTypes in the LabelTable are 0 – groups,  and thus it is the label value field that links to the group id in the group table.

      Each person can only be in one group and the colour of that group is stored not against the group, but against each person on the person table. So only the last applied colour is valid.

      The colour codes are:

      Colour IDColour
      1red
      2lime
      3blue
      4fuschia
      5yellow
      6aqua
      7silver
      8maroon
      9green
      10navy
      11purple
      12brown
      13teal
      14grey

      However being RootsMagic there’s a gotcha of course, since the GroupTable doesn’t have the obvious one entry for each person in a group but stores the start and end RIN of clusters of people in the group.
      SQLite however can cope with this by using the BETWEEN keyword so we have

      WHERE RIN BETWEEN StartNo AND EndNo

      which is the tidy readable equivalent of

      WHERE (RIN >= StartNo) AND (RIN <= EndNo)
      

      and both methods work perfectly.

      So the SQL to list all the people in all the groups is:

      with n as 
      (Select
               NameTable.OwnerID AS 'RIN1',
               NameTable.Surname COLLATE NOCASE AS 'Surname',
               NameTable.Given COLLATE NOCASE AS 'Given'
      FROM NameTable		 
      where NameTable.IsPrimary = 1)
      
      Select LabelValue as GroupID, LabelName as GroupName, Description COLLATE RMNOCASE as Description,
      n.RIN1 as RIN, n.Surname, n.Given, p.Color
      FROM LabelTable l INNER JOIN GroupTable g
      ON l.LabelValue = g.GroupID 
      INNER JOIN PersonTable p
      ON n.RIN1 = p.PersonID
      LEFT OUTER JOIN n on n.RIN1 BETWEEN g.StartID AND g.EndId
      WHERE LabelType = 0
      ORDER BY GroupID, Surname, Given, RIN
      

      For RootsMagic8, the table names and structures have changed a little.

      The GroupTable is still the same structure but group numbers have been changed by adding 1000 to the RM7 value. The LabelTable is renamed to the TagTable and LabelID becomes TagID and LabelType becomes TagType. Groups are still TagType 0 but TagType 1 is Research Log names.

      So our SQL for RM8 is

      with n as 
      (Select
               NameTable.OwnerID AS 'RIN1',
               NameTable.Surname COLLATE NOCASE AS 'Surname',
               NameTable.Given COLLATE NOCASE AS 'Given'
      FROM NameTable		 
      where NameTable.IsPrimary = 1)
      
      Select TagValue as GroupID, TagName as GroupName, Description COLLATE RMNOCASE as Description,
      n.RIN1 as RIN, n.Surname, n.Given, p.Color
      FROM TagTable l INNER JOIN GroupTable g
      ON l.TagValue = g.GroupID 
      INNER JOIN PersonTable p
      ON n.RIN1 = p.PersonID
      LEFT OUTER JOIN n on n.RIN1 BETWEEN g.StartID and g.EndId
      WHERE TagType = 0
      ORDER BY GroupID, Surname, Given, RIN
      

      Having done this it is quite straightforward to update an existing group from a SQL query. I have two groups that I like to update in this way, the first is all those marked as Living and the second is all those who died before they reached the age of 14 so are unlikely to have descendants. All following code is for RootsMagic 7 and will not work with version 8.

      /* Update people in living group */
      /* List all living */
      With liv as
      (Select
               n.OwnerID AS 'OwnerID', g.GroupID
      	 FROM NameTable n INNER JOIN PersonTable p
      ON n.OwnerID = p.PersonID		 
      LEFT OUTER JOIN GroupTable g
      ON g.GroupID = 1 -- replace the 1 with the correct group id
      AND n.OwnerID BETWEEN g.StartID and g.EndID
      where n.IsPrimary = 1 
      and p.living=1
      )
      INSERT INTO GroupTable (GroupID, StartID, EndID) 
        SELECT 1 as GroupID, -- replace the 1 with the correct group id
      	liv.OwnerID,liv.OwnerID
      	FROM liv
      	Where liv.GroupID IS NULL;
      

      Now update the colour by using the same CTE

      With liv as
      (Select
               n.OwnerID AS 'OwnerID', g.GroupID
      	 FROM NameTable n INNER JOIN PersonTable p
      ON n.OwnerID = p.PersonID		 
      LEFT OUTER JOIN GroupTable g
      ON g.GroupID = 1 -- replace the 1 with the correct group id
      AND n.OwnerID BETWEEN g.StartID and g.EndID
      where n.IsPrimary = 1 
      and p.living=1
      )
      UPDATE PersonTable 
      SET p.Color = 2 –- replace the 2 with the desired colour number
      WHERE EXISTS (
              SELECT OwnerID
              FROM liv
              WHERE liv.OwnerID = PersonTable.PersonID);
      

      The final WHERE EXISTS construct is required by a quirk of SQLite that it does not have the UPDATE… FROM syntax of standard SQL in the version used by RootsMagic 7. This may change in future.

      The similar code for updating the group for those who died before age 14 is

      with per as
      (Select
               NameTable.OwnerID AS 'OwnerID',
               NameTable.Surname COLLATE NOCASE AS 'Surname',
               NameTable.Given COLLATE NOCASE AS 'Given',
      		 NameTable.BirthYear,
      		 NameTable.DeathYear,
      		 GroupTable.GroupID
      FROM NameTable LEFT OUTER JOIN GroupTable
      ON GroupTable.GroupID = 1   -- replace the 1 with the correct group id
      AND NameTable.OwnerID BETWEEN GroupTable.StartID and GroupTable.EndID	
      where NameTable.IsPrimary = 1 and (DeathYear > 0) and (DeathYear - BirthYear) < 14)
      INSERT INTO GroupTable (GroupID, StartID, EndID) 
       SELECT 1 as GroupID, -- replace the 1 with the correct group id
      per.OwnerID,per.OwnerID
      	FROM per
      	Where per.GroupID IS NULL;
      

      Now update the colour by using the same CTE

      with per as
      (Select
               NameTable.OwnerID AS 'OwnerID',
               NameTable.Surname COLLATE NOCASE AS 'Surname',
               NameTable.Given COLLATE NOCASE AS 'Given',
      		 NameTable.BirthYear,
      		 NameTable.DeathYear,
      		 GroupTable.GroupID
      FROM NameTable LEFT OUTER JOIN GroupTable
      ON GroupTable.GroupID = 1   -- replace the 1 with the correct group id
      AND NameTable.OwnerID BETWEEN GroupTable.StartID and GroupTable.EndID	
      where NameTable.IsPrimary = 1 and (DeathYear > 0) and (DeathYear - BirthYear) < 14)
      UPDATE PersonTable 
      SET Color = 2 –- replace the 2 with the desired colour number
      WHERE EXISTS (
              SELECT OwnerID
              FROM per
              WHERE per.OwnerID = PersonTable.PersonID);
      

      I use these SQL scripts to ensure that my groups are updated when I have made changes to my databases and they ensure I don’t miss adding people who should be in the groups.

      Pat Jones
      SQL and Business Intelligence Developer

      Common Table Expressions – The Building Blocks of SQL

      Contents

        Common Table Expressions (CTE) are a feature that was first introduced to SQL in about 2005 and provide a method of defining a temporary result set then using that in the query as if it were another table.

        Yes, subqueries do the same thing but a CTE can be used repeatedly in the query after it has been declared without further re-declaration whereas a sub-query has to be declared every time it is used. Views could also be created but that involves a physical object within the database being created and remembering to tidy up and delete it later.

        The CTE is declared at the start of a query using the WITH construct so I can write:

        WITH pers AS
        (
        Select
                 NameTable.OwnerID AS RIN1,
                 NameTable.Surname COLLATE NOCASE AS Surname,
                 NameTable.Given  COLLATE NOCASE AS Given
        FROM NameTable		 
        where NameTable.IsPrimary = 1
        )
        

        The very first word of the first CTE has to be WITH and this has to be the very start of the query, apart from comments. Then the name of the CTE is declared folowed by AS then its definition in brackets. CTE names are best kept short as they will be used further on in the query. Now I have an item I can use in the rest of my query so that continues:

        SELECT 
        EventID, 
        EventType,
        pers.RIN1,
        pers.Surname,
        pers.Given
        FROM EventTable as ev
        LEFT OUTER JOIN pers ON
        ev.OwnerID = pers.RIN1 
        AND ev.OwnerType = 0
        

        so you can see pers is treated as if it were another table or view in the query.

        This is just a simple example using a CTE once. A query can contain multiple CTE and once the first has been declared, subsequent CTE are declared by continuing the WITH clause and putting
        , ctename AS
        (
        my cte code
        )
        rather than another WITH. So I can add another CTE to get both parent details for a family and this can (but does not have to) use the first CTE.

        ,fam as
        (
        select FamilyID, RIN1, Surname, Given
        from FamilyTable f inner join pers
        on f.FatherID = pers.RIN1
        where f.FatherID > 0
        UNION
        select FamilyID, RIN1, Surname, Given
        from FamilyTable f inner join pers
        on f.MotherID = pers.RIN1
        where f.MotherID > 0
        )
        

        All this happens before the main query and the main query simply uses those CTE as if they were tables.
        So main query could now be

        SELECT 
        EventID, 
        EventType,
        f.Name COLLATE NOCASE AS FactType,
        ev.OwnerType,
        ev.OwnerTypeN, 
        case when ev.OwnerType = 1 THEN fam.Surname
        	ELSE pers.Surname
        	END as Surname,
        case when ev.OwnerType = 1 THEN fam.Given
        	ELSE pers.Given
        	END as Given,
        case when ev.OwnerType = 1 THEN fam.RIN1
        	ELSE pers.RIN1
        	END as RIN1,
        ev.Eventdate,
        ev.proof,
        ev.placeID,
        ev.SiteID, 
        ev.details
        from ev /* join in person where owner type is person */
        INNER JOIN FactTypeTable f ON
                 e.EventType = f.FactTypeID
        LEFT OUTER JOIN pers ON
                 ev.OwnerID = pers.RIN1 and ev.OwnerType = 0
        /* join in the family persons for owner type = family */
        LEFT OUTER JOIN fam ON
                 ev.OwnerID = fam.FamilyID and ev.OwnerType = 1
        WHERE    ev.EventType <> 35 /* ignore reference numbers */
        /* and (ev.Proof = 0) – not proven (remove surrounding comment markers /* and */ to include this restriction)*/
        ORDER BY Surname, given, RIN1, eventtype
        

        The overall layout is
        WITH a AS (something) , b AS (something else) , c AS (something more) SELECT ……….

        Note the left outer join syntax which allows all records from the left hand table to be included even if there is not a matching record on the right hand side. Missing data is set to null values. Inner JOIN and just plain JOIN would leave out records which do not have a match for the person id and an owner type of 0 and in fact the whole query would just mess up.

        A good rule for SQL readability is to always fully specify the join type so never use just JOIN but always INNER JOIN or LEFT OUTER JOIN.
        There is also a RIGHT OUTER JOIN and a FULL OUTER JOIN but SQLite does not support these constructs.

        Another useful implementation of the CTE is to expand out tables in RM that contain dates and fields that need collation changes. The date expansion code is longwinded and distracting from the main query. Putting it in a CTE gets it out of the way, leaving the main query readable.

        My full query example attached is a list of facts and their citations, including the owner (person or family). By altering the where clause this can list facts without citations or facts not marked proven (or both).

        With the database changes between RM7 and RM8 the CTE is most useful to convert queries written for version 7 to run against a version 8 database. The CitationTable from version 7 has been split into CitationTable and CitationLinkTable in version 8 but using a CTE we can emulate the version 7 structure then just change the table name in the main query.

        So we have:

        With Cit8 as
        (Select c.CitationID, cl.OwnerType, cl.OwnerID, c.SourceID, cl.Quality, cl.IsPrivate, c.Comments, c.ActualText, c.RefNumber, cl.Flags, c.Fields
        from CitationTable c INNER JOIN CitationLinkTable cl
        ON c.CitationID = cl.CitationID)
        

        then continue with original query but change all occurrences of CitationTable to Cit8
        So a simple list of citations with their sources becomes:

        With Cit8 as
        (Select c.CitationID, cl.OwnerType, cl.OwnerID, c.SourceID, cl.Quality, cl.IsPrivate, c.Comments, c.ActualText, c.RefNumber, cl.Flags, c.Fields
        
        from CitationTable c INNER JOIN CitationLinkTable cl
        ON c.CitationID = cl.CitationID)
        SELECT * FROM Cit8 LEFT OUTER JOIN SourceTable ON CitationTable.SourceID = SourceTable.SourceID
        

        I have built queries with many CTE to progressively build up data for complex reporting and find it a far more efficient way of coding which can be easily tested level by level.

        My query to list facts and their citations is attached in two versions, cte-facts-citations-RM7.sql for RootsMagic version 7 databases
        and cte-facts-citations-RM8.sql for RootsMagic version 8 databases.

        Further Reading:

        This article is aimed at SQL Server but SQLite uses the same syntax.
        https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-cte-basics/

        Pat Jones
        SQL and Business Intelligence Developer

        Comparing #Database Definitions RM8 versus RM7 #datadefinitions

        Contents

          Comparing the CREATE statements for a database file of each version reveals changes to persisting tables in addition to tables deleted and added per First Look at RM8 #Database. This post provides DDL files from RM7.6.5 and the RM8 Preview 7.9.101. The two files can be compared line by line in Notepad++ using the Compare plugin, a screenshot of which is shown below.

          By rearranging the order of statements in the two different files to juxtapose tables whose names have changed, the coloured comparison is even more effective. The next screenshot shows how RM7’s LabelTable has been transformed to RM8’s TagTable. RM7’s ResearchItemTable and ResearchTable have been consolidated into RM8’s TaskTable with an indirection via the new TaskLinkTable.

          Comparison after rearranging DDL files for improved juxtapositioning of similar statements.

          These graphical comparisons help in finding the more subtle changes that may imply things to watch out for. For example, why has SortDate been changed from Integer to BigInt?

          First Look at RM8 #Database

          More changes than expected with the result that many (most?) of the queries and scripts published on this site won’t work with RM8 databases.

          New Tables

          CitationLinkTable: was expected to support the announced new feature of master citations.

          AncestryTable and FamilySearchTable: replace the former LinkAncestryTable and LinkTable, now gone.

          TagTable: looks to have replaced LabelTable, now gone.

          TaskTable and TaskLinkTable: appear to have replaced ResearchTable and ResearchItemTable, now gone.

          Global Change

          Every Table has, as its last column, a new column named UTCModDate, apparently intended to track the date on which a record was added or last changed.

          Query to Search Parents by Children’s Given Names #search #family

          Contents

            File above incorporates revision from discussion below to correct the count of children. Tested with #rm7 #rm8 and should be compatible with prior versions. TH 2021-03-03

            The following script was developed in response to the discussion Search for family by given names of children (was RM-8)

            -- three_childrens_names.sql
            -- Jerry Bryan, 22 May 2020
            -- This query searches for parents based on given names of their children.
            -- It gets a hit when three or more children have any primary or alternate
            -- given names that are in a hardwired list. The report lists the parents.

            SELECT COUNT(C.ChildID) AS fam_size,
            F.FatherID, father.Given, father.Surname, F.MotherID, mother.Given, mother.Surname
            FROM ChildTable AS C
            JOIN
            (SELECT DISTINCT NN.OwnerID
            FROM NameTable AS NN
            WHERE NN.Given LIKE("%Mary%") OR NN.Given LIKE ("%John%") OR NN.Given LIKE("%Elizabeth%"))
            AS N ON N.OwnerID = C.ChildID
            JOIN
            FamilyTable AS F ON F.FamilyID = C.FamilyID
            JOIN
            NameTable AS father ON father.OwnerID = F.FatherID
            JOIN
            NameTable AS mother ON mother.OwnerID = F.MotherID
            GROUP BY C.FamilyID
            HAVING fam_size >= 3
            ORDER BY father.Surname, father.Given, mother.Surname, mother.Given;

            Jerry