RM9 Data Documentation Initiative

Please join me in welcoming Kevin McLarnon ( @kmclarnon ) in the role of Editor on this site and, especially, for volunteering to lead a renewed initiative to improve the documentation we have on the design of the RootsMagic v9 #database. He is counting on users better-versed than himself in sqlite3 and #RM9 to collaborate, contribute to and comment on the #datadefinitions but brings to the process experience with Google Workspace teams, websites and pedagogy.

The locus of activity is the page RM9 Data Dictionary which links to the collaborative spreadsheet on which all are invited to comment. Some of you will be approached for special contributions and you can also contact him directly. You will hear more from Kevin about the project in blog posts and/or Forum messages.

Database Revert RM9 to RM8 to RM7

Introduction

Conversion from #RM9 to #RM8 is pretty trivial with little loss except for RM9’s new features. From RM8 to #RM7 is much more complex given the significant structural changes between the two. To convert from RM9 to RM7 is simply a two-stage process with a RM8 compatible file as an intermediary. It’s possible to combine the two into one procedure but that’s for another time.

Losses

Comparison of database properties between the original RM9 database and after having been converted down to RM7 via RM8 and then upconverted back to RM9. Demonstrates that the core data has been preserved with the only variance in the number of Citations (0.05% increase) and the number of Tasks (12% decrease).

That there should be a variance in Tasks is unsurprising because of the structural changes between RM7 and RM9; Task Folders from RM9 could be non-existent in RM7.

The tiny variance in Citations is perhaps more surprising as it might be expected to go in the opposite direction, e.g., citations for Associations in the original RM9 database would be absent in RM7 and unrecoverable in the re-upgrade to RM9. In this case, however, it is because a few of the original RM9 Citations were reused and there were 6131 Citation Links which were transformed to 6131 individual Citations in the downgrade from RM8 to RM7.

The loss of Associations in the downgrade has no effect on the Events count as they are records in a different table, only reported in RM9’s Enhanced Properties List which also reveals the loss of Task folders.

Original RM9 database on the left; to the right is the traveller from RM9 down to RM7 and back.

Of course, the database properties lists do not tell the whole story. It would also be unsurprising to find that Notes, especially formatting, may not be preserved in a downgrade or upgrade due to the major reiterations of the Note Editor code. Certainly, new features in RM8 or RM9 will not be supported in a lower version and those that are database dependent, including Associations, Color Code Sets and Labels, and Saved Search Criteria cannot survive a round trip from a higher version to lower and back.

Stage 1: From RM9 to RM8

The only barrier to RM8 opening a RM9 .rmtree file is the version number of the database. For RM8, it cannot deal with a version higher than 8000; RM9’s is 9000 while RM7’s is 6000 (as it is for RM6). This statement opens the door to RM8:

UPDATE ConfigTable
  SET DataRec=
    CAST(
         REPLACE(DataRec,'<Root><Version>9000</Version>',
                         '<Root><Version>8000</Version>')
         AS BLOB
        )
WHERE RecID=1
; 

The next step is only needed to prevent RM8 from offering the Association fact type (which it cannot support) as a conventional individual type which one could use to add such events, leading to confusion should the database be reopened in RM9.

DELETE FROM FactTypeTable WHERE FactTypeID=902; -- Association Fact Type

--DELETE FROM EventTable WHERE EventType=902; -- Association individual event, if any

The second statement is only needed should such Association individual events have been created before upgrading back to RM9.

A possible enhancement to this down-conversion from RM9 to RM8 might be to convert Associations in RM9 to either Shared Events or simple individual type events with the name of the other person in the event description.

Stage 2: From RM8 to RM7

The heavy work in developing a procedure for this down-conversion was covered in Convert RM8 Database to RM7.

DBeaver Heredis 2023 and RootsMagic 9

They say a picture is worth a thousand words. This page compares pictures of the #RM9 and Heredis 2023 databases (both SQLite 3) as automatically created by the same tool.

Entity Relationship Diagrams

An Entity-relationship Diagram (ERD) says a lot about a database. @patjones recently posted a RM9 Database Diagram with many of the linkages or relationships among its tables that are made on-the-fly by the SQL queries from the RM software or the ones found on this site. What a database Entity Relationship Diagram editor generates from a database’s self-definition includes the tables, fields and types, stored Triggers (actions on 1 or more tables by some database event) and stored Views (stored queries). So it is interesting to compare the automatically generated ERDs of the RM9 database against that of the Heredis 2023, from the same editor, DBeaver.

RootsMagic 9.0 Diagram

Heredis 2023 Diagram

Comparison

ObjectRootsMagic 9.0Heredis 2023
Tables2965
Views00
Indexes46123
Table Triggers0156

The comparison table and diagram show that the Heredis database is considerably more complex than RootsMagic 9 with more than double the number of tables and nearly triple the number of indexes which accelerate the association of rows across tables and speed up searches. And more divergent is the extensive use of Triggers by H and their complete absence in RM. What do these numbers say about the two databases?

Tables

That there are more tables in H than RM is from a couple of causes:

  • data normalisation: I think H is more rigorous in applying the principal, e.g., it has a separate table for the Alternate Name while RM has but one for all types of names and uses a field to flag which is the Primary record for a given person. Moreover, H has names in multiple tables: the Primary appears to be in the equivalent of RM’s PersonTable (Individus) while there is another for Surnames (Noms) and one for Given Names (Prenoms). Similarly, RM’s MediaLinkTable which relates a media item to a Person, Couple, Event, Source, Citation, Place… (according to an OwnerType value) is achieved in H by separate tables for each ‘OwnerType’.
  • a larger feature set? Hard to say. I’m not using H sufficiently to identify the differences and my understanding of table and field names in French is not thorough.

Views

Neither has stored views (stored queries) so that tells us nothing. I’ve used temporary Views on RM databases, lasting until the database is closed by the sqlite manager. It’s possible that both applications do, too, but we cannot see them from outside the app’s sqlite engine.

Indexes

With double the number of tables, it stands to reason that there could be double the number of indexes. I don’t know why there should be triple but, in the past, I’ve suggested an index be added or revised in RM. SQLite is itself very good at optimising queries and may spawn an index that it thinks is appropriate unless there is a pre-defined one that includes all the key fields of the query. Maybe H developers have attempted to cover all the possible bases with explicit indexes.

What I have yet to find out is if there is any custom collation sequence in H as there is in RM which has given us database explorers so much grief.

Triggers

This is the most pronounced difference between RM and H. H has many routine operations embedded in the database, being executed by the database engine, rather than in the higher level code which then has to talk to the database engine to make the same thing happen. For example, H has a trigger named “MajDateMediasEvenementInsert” which automatically updates the ‘Date modified’ value in its ‘EventTable’ when a link has been added to their ‘MediaLinkTable’. Looks like this:

CREATE TRIGGER "MajDateMediasEvenementInsert"
AFTER INSERT ON LiensMediaEvenement
FOR EACH ROW
BEGIN
UPDATE Evenements SET DateModification=MAX(NEW.DateModification, Evenements.DateModification) WHERE Evenements.CodeID=NEW.XrefEvenement;
END;

Maybe not the best example for comparison because I don’t think RM would update the EventTable when a media item is tagged to an event.

DBeaver

Having searched around for a (free) application that could create an Entity Relationship Diagram (ERD) from a SQLite 3 database, I landed on one that might be described as an Integrated Development Environment (IDE) for databases, the DBeaver Community Edition. It is self-described as a “Universal Database Tool” and I’ve only superficially scratched its surface figuring out how to connect a SQLite database, generate the ERD from its definition and edit it to illustrate relationships. But it is much more powerful than that as it looks to be a multi-database manager with which one can query the database data. And there is a Pro version which adds many more capabilities.

RM9 Data Definitions #datadefinitions

RootsMagic 9.0.0.0 was released on Feb 27, 2023 with some new features necessitating changes to the database design. All previous version #database files undergo an upgrade procedure to the RM9 structure and are not backward compatible. However, #RM9 and #RM8 table structures are identical with the following exceptions:

PersonTable

9 new fields named color1, …, color9 have been added in support of the ColorCode sets 1 through 10. The original “color” field is set 1.

FanTable, FanTypeTable

New tables added in support of the new Associations feature. They bear a resemblance, respectively, to the EventTable and FactTypeTable.

PayloadTable

New table added in support of Saved Criteria Search and Saved Criteria Group. So far:

OwnerType

New OwnerType values have been added in support of the new tables. These will be identified as encountered through usage and testing.

TableRecTypeOwnerTypeOwnerID
Payload1 (SavedCriteriaSearch)80
Payload2 (SavedCriteriaGroup)20TagTable.TagValue, GroupTable.GroupID
FANTable19CitationLinkTable, MediaLinkTable,…

Table Specs

This is a tab-delimited result of a query of the tables.

Using ChatGPT AI to get help on querying the RM database

Introduction

With all the excitement surrounding how well Artificial Intelligence is doing to write papers that can fool teachers and having seen someone post a message about using it to generate some software coding, I was curious to see what the ChatGPT interactive AI research tool could say about creating a SQLite query of a RootsMagic database. The answer, at the moment, is ‘not much’ but it does tell you how to get started with the SQLite Command-Line Interface (CLI). The composition itself is very good!

Screenshot of part of the response, full response transcribed below.

How do I query my RootsMagic sqlite database?

You can use the SQLite command-line interface to query your RootsMagic database. To do this, follow these steps:

  1. Download and install the SQLite command-line interface if you don’t already have it: https://sqlite.org/download.html
  2. Open a terminal or command prompt window and navigate to the directory where the SQLite executable is located.
  3. Connect to your RootsMagic database by typing the following command, replacing “database_file” with the path to your database file:
sqlite3 database_file
  1. Once you are connected to the database, you can run SQL queries against it by typing them into the command prompt and hitting enter. For example, to get a list of all tables in the database, you can run the following query:
SELECT name FROM sqlite_master WHERE type='table';
  1. To see the schema for a particular table, you can use the .schema command, followed by the name of the table. For example:
.schema people
  1. To see the contents of a table, you can use the SELECT statement. For example:
SELECT * FROM people;

This will display all rows and columns in the “people” table. You can use various clauses in your SELECT statement, such as WHERE, GROUP BY, and ORDER BY, to filter and sort the results.

I hope this helps! Let me know if you have any questions. (How polite and generous is that!?)

What database engine is used for a RootsMagic 7 database?

RootsMagic 7 uses the Firebird (Oops! got that wrong- that’s pre-RM4) open-source database engine to store and manage data in its databases. Firebird is a powerful and reliable database engine that is well-suited for use in genealogy software like RootsMagic. It is capable of handling large amounts of data and can support a wide range of data types and structures. Firebird is also designed to be easy to use and maintain, making it a good choice for software like RootsMagic that is intended for use by non-technical users.

Firebird is based on the InterBase database engine, which was developed by Borland in the 1980s. Firebird was created as an open-source fork of InterBase in the early 2000s, and has since become a popular choice for a variety of applications that require a robust and reliable database engine.

SQLite Error 26 – A Surprising Cause

User could not open their “.rmgc” file, getting this message.

A user with this problem could not open their RM7 database file, contacted Tech Support which could not help and was referred by the agent to me. I warned them that there was probably nothing I could do because any version of SQLite I had would report the same error as RM7’s SQLite database engine and be unable to open it. Sure enough, here’s what SQLiteSpy reported:

SQLite Error 26 as reported by SQLiteSpy.
Other SQLite managers will report similarly to the highlighted error message.

As I could not open the .rmgc file with SQLiteSpy, I had a look at it with a text editor (PSPad in this case; NotePad++ is a good alternative), thinking that maybe the header got corrupted. Here’s what I saw:

Header of the “.rmgc” file as viewed in PSPad.

Two things (highlighted) jumped out at me: “PK” and a filename ending in “.rmgc”. Was this actually a RootsMagic 7 backup file whose extension had been changed from “.rmgb” to “.rmgc”? The RM backup file, probably even from the era of Family Origins, is a data compressed ZIP file and I had used, as early as the ’80’s, the original PKZIP utility for MS-DOS – there’s that “PK”. Sure enough, changing the extension to “.zip” allowed Windows File Manager, which integrates the Zip functions, to look inside and see that there was a single file in the archive whose uncompressed size was 3 times the size of the compressed backup file. Its name was actually “T.Thornley.rmgc”, a pre-RM8 RM database filename.

Then, having changed the extension from “.zip” to “.rmgb”, RM7 happily extracted “T.Thornley.rmgc” from the Zip/Backup file using File>Restore and successfully opened it. Here’s what the header looks like for the resulting “.rmgc” file, or any unencrypted SQLite 3 file, including any RM8 “.rmtree” database file:

Header of the restored “.rmgc” file as viewed in PSPad.

Apparently, the user had been changing backup filenames to the “.rmgc” extension for some time as they reported that they could not “open” a backup dated later than 2020. I can see how this might be possible due to a misunderstanding of the RM Backup/Restore procedures and a trap in the RM User Interface: the backup dialog allows the user to change the extension when naming the backup file. It probably should append “.rmgb” (or “.rmbackup” for RM8) regardless; after all, that extension is required for RM to find its backups, distinct from “.zip” files and in the clutter of other file types.

RM8 reports a misleading error

If one uses File > Open to the mislabelled “.rmgc” file, there is no immediate error as in the case of RM7 but a series of dialogs having to do with conversion to RM8 until the Import starts and hangs with this “Send error report” dialog:

RM8 error message on attempting to “Open” the mislabelled “.rmgc” file.

One would infer that it was an unencrypted database file missing a required table but that is not the case. The correct error message is provided by RM7 and it does not freeze up as is the case with RM8.

In Praise of SQLite

Contents

    Just learned of some interesting things about SQLite in this article that popped up in my Google News feed: In Praise of SQLite by Nikos Vaggalis, dated 28 July 2022. The latest version of SQLite is 3.39.2. I don’t monitor releases so this story made me aware of some significant changes delivered in 3.39.0 and other developments in the library and in extensions. I’ll highlight some that I think will be of potential interest to RootsMagic Toolmakers but recommend you read the original report. Check your current SQLite manager for updates that may bring it up to using the 3.39.x library.

    • Changes:
      • Added (long overdue) support for RIGHT and FULL OUTER JOIN
      • Added the sqlite3_db_name() interface
      • The HAVING clause of a SELECT statement is now allowed on any aggregate query, even queries that do not have a GROUP BY clause
    • sqlean, a Github repo that collects extensions covering all the missing SQLite functions:
      • fileio: read and write files
      • fuzzy: fuzzy string matching and phonetics
      • re: regular expressions
      • text: string functions
      • unicode: Unicode support
      • uuid: Universally Unique IDentifiers
      • vsv: CSV files as virtual tables
      • closure: navigate hierarchic tables with parent/child relationships
      • envfuncs: read environment variables
      • eval: run arbitrary SQL statements
      • isodate: additional date and time functions
      • text2: additional string functions
      • xmltojson: convert XML to JSON string
      • zipfile: read and write zip files
    • Utilities:
      • LiteTree, a modification of the SQLite engine to support branching, like git!
      • SQLSite, a tool for serving simple websites, static files and JSON APIs directly from a SQLite database.
      • sql.js, SQLite compiled to JavaScript.sql.js is a javascript SQL database. It allows you to create a relational database and query it entirely in the browser

    Convert RM8 Database to RM7 #rm8 #rm7

    Issue

    As of writing, still no Book Publisher feature in RM8 and along with other persistent issues, some users wish to return to the RM7.5 database from which they had ‘upgraded’. However, the only ways back using the RM user interface are lossy:

    • #GEDCOM which, along with other attendant losses (see GEDCOM & DnD transfer losses) does not import #namedgroup and custom #reports.
    • RM8’s File > Export Data > DropBox translates the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android) but it loses shared events, and likely more.

    A more complete conversion is wanted.

    2023-05-04: Update re #RM9: despite the inclusion of the Book Publisher feature missing from RM8, there are RM9 users who seek a way to return to RM7 for other reasons. A tweak to a RM9 database to change its version number to RM8 opens up the solution below to carry through to RM7. See Database Revert RM9 to RM8 to RM7.

    Solution

    Both RM7 and RM8 use similar SQLite databases so, obviously, a transfer of data through SQLite operations is the most direct means possible. However, “similar” is not “identical” and there are challenges with transferring Tasks, TreeShare, FamilySearch, Custom Reports and Report Settings and other File Settings, History, Bookmarks, et al. The script above provides the most thorough and complete conversion achieved to date and is built on and supersedes:

    For a full understanding of what has gone into the current procedure, they remain useful references.

    Procedure

    1. In RM8, open and prepare your RM8 database file for export by running the set of File>Tools>Database Tools. Note the file’s full path and name for entry into the SQLite script. Close the database.
    2. In RM7, create a new empty database or open the database you wish to overwrite (I’ll leave it to you to backup or make a copy!). If an existing file, run the RM7 set of File>Database Tools. Note the file’s full path and name so you can find it with your SQLite manager. Close the database.
    3. Open the target RM7 database with your SQLite manager (one with a fake RMNOCASE collation – see #rmnocase).
    4. Open your SQLite manager’s SQL Editor on this database file and load into it the script listed above under Solution.
    5. Edit this script to change the path and name in the ATTACH DATABASE line near the beginning to that of your source RM8 .rmtree database file. Edit 2 more lines near the end flagged by **** to explicit media paths.
    6. Execute the script.
    7. On reopening the target RM7 database with RM7, run File>Database Tools.
    8. If Media links are broken despite the edits you made to the script, use the Media Gallery’s Fix Broken Media Links tool and/or, as appropriate, Search & Replace (Ctrl+H) on Media filenames to fix them.

    Notes

    1. Copies over the ConfigTable records from RM8 which can include Custom Reports and Book Publisher settings that originated in the RM7 database which had been upgraded to RM8, thereby making the round trip from RM7 to RM8 back to RM7 pretty much intact, except for possible losses in the area of Research Logs and Folders due to structural differences.
    2. This procedure is orders-of-magnitude faster than GEDCOM export-import, approximately 2 minutes from a 330MB RM8 database file, a few seconds for a small one. 
    3. A surprising outcome from this script development is that I’ve used the script to demonstrate  that RM 8.1.8 TreeShare falsely reports mismatches between Ancestry Sources and RM’s copies after applying ‘Merge all duplicate citations ‘. See: TreeShare mysteries from RM8.1.8 ‘Merge all duplicate citations’ (likewise for RM8.2).
    4. At some point, a RootsMagic 8 update or its successor will change the database in a way that breaks the script or causes an incompatibility.
    5. RootsMagic 7 is no longer being developed so features such as TreeShare and FamilySearch integration will break when those services change their APIs (the Application Program Interface with which RM7 interacts).
    6. Color-coding suffers in translation because RM8 has 28 colours, 13 of which are outside the range for RM7 and those that are within do not all map to the corresponding color.
    7. I was ‘lazy’ with data typing despite there having been changes between the two versions. SQLite itself is also ‘loose’ with enforcement. For example, many fields in RM7 that were type BLOB became type TEXT in RM8. My prior experience with RM in the past was that it did not care when the content was textual so I’ve made no attempt to CAST these TEXT fields back to BLOB on import. Yet, it’s possible it may give rise to some obscure error.
    8. I get a memory access error in citations using Find Everywhere on one file that originated in RM7 and imported back from RM8, yet there is no such error in the original nor in the RM8 upgrade. That’s an obscure error not present in other files I’ve converted.
    9. Mac users probably cannot run RM7 any more so this procedure is of little interest except for its potential to solve some RM8 problems by bouncing the data down to RM7 and back to RM8.
    10. Please let me know what errors you encounter, discoveries you’ve made, benefits you’ve realised…, probably best through the Forum, given its message editor is superior to the Comment editor.
    11. Good luck!

    RMTREE Properties Query for RM8

    Contents

      This is an ‘upgrade’ from RMGC_Properties – Query #database for pre-RM8 databases. It provides an expansion over the information provided on the RM8 Home – File Properties screen. With a SQLite manager that supports runtime variables, a summary report can be given (just those variables with no leading hyphen).

      It ATTACHes a reference empty, unused RM8 database that you provide from your up-to-date version of RM8 against which certain built-in items are compared, e.g., Fact Types and Source Templates, from your database under test. You’ll need to edit a line in the script to point to that empty database on your system.

      Here’s an example of the script’s output for a database that was created around the time of the first public release:

      ValueVariableRemark
      6000Versionvs Control version 8000
      – pre/post RM8 releaseWARNING! Database from a pre-release version of RM8
      1608Peopleall records in PersonTable
      0– Nameless Peopleno record in NameTable for that RIN
      153– Unresolved Duplicate Name Pairspairs of Given and Surnames, not flagged as “Not a Problem”
      20– Resolved* Duplicate Name Pairsflagged as “Not a Problem” – flags lost on transfer
      5– Unresolved Duplicates with Media Linkssecondary persons’ links lost on merge
      257Alternate namesall records in NameTable where IsPrimary=0
      0– Orphaned Alternate names*no Primary name record found
      551Familiesall records in FamilyTable
      69Fact Typesno. of records from FactTypeTable
      5– Custom Fact Typesno. of custom Fact Types
      9– Customised Built-in Fact Typesno. of built-in Fact Types modified
      34– Unused Fact Typesno. of Fact Types not used
      0– Blank Fact Type NamesFactTypes must be named
      0– Blank FactType SentencesFactType needing definition
      6733Eventsall records of EventTable
      0– Orphaned Eventsevents for which no person or family match in respective tables
      138– Event WitnessesAll records in WitnessTable of persons sharing events
      17— Nominal Witnessesnot Persons from database, but named in WitnessTable as sharing an event
      0— Headless WitnessesPersonID (RIN) in WitnessTable missing from PersonTable
      0— Witnesses to Lost EventsEventID in WitnessTable cannot be found in EventTable
      0— Witnesses with blank Roleno role has been assigned from RoleTable or the RoleTable role is empty
      5— Witnesses with Custom Sentencea custom sentence has been assigned, unique to this witness
      26— Witnesses with Notea note has been entered for this witness to an event
      84— Rolesno. of records from RoleTable
      26— Custom Rolesno. of custom roles
      1— Customised Built-in Rolesno. of built-in roles modified
      54— Unused Rolesno. of roles not used
      0— Blank Role NamesRoles needing definition
      2— Blank Role SentencesRoles needing definition
      1485Total Placesall records in PlaceTable incl Places and Place Details (Sites)
      177– System Placessystem supplied Places: LDS Temples
      1081– User Placesuser defined Places excl Sites
      330— Used, having Geo-coordinatesnon-empty Lat or Long
      42— Unused User Places*not used by EventTable, will be dropped in a transfer
      227— User Place Detailsuser defined Sites
      22— Used, having Place Detail Notes*Site Notes will be lost in a transfer
      60— Used, having Geo-coordinatesnon-empty Lat or Long
      13— Unused Place Details*Sites will be lost in a transfer
      833Source Templates# of records from SourceTemplateTable
      418– Custom Source Templates# of custom Source Templates
      415– Unupdated Built-in SourceTemplates# not matching reference database
      392– Unused Custom SourceTemplates*lost on transfer
      1– Incomplete Source Templatesmissing part of definition
      256Total Sourcesall records from SourceTable
      3– Unused Sources*SourceTable records unused by CitationTable
      6080Total Citationsall records from CitationTable
      6080Total Citation Linksall records from CitationLinkTable
      11– Duplicate Citationsidentical in most respects, cluttering reports
      0– Sourceless Citations*no SourceTable record for this CitationTable record
      54– Headless Citations*CitationTable records for which no Person, Event, Family, AltName found; cleaned on transfer
      18Repositoriesall records from AddressTable of type Repository
      827Multimedia itemsall records from MultimediaTable
      7– lacking thumbnailprobably an imported reference to an image file that has yet to be found
      0– duplicate multimedia filenamesprobably having different paths
      25– with Date & Description*(TBC) if a record has both, the Description is lost in a transfer
      3894Multimedia linksall records from MediaLinkTable
      0– duplicate multimedia linksimage appears multiple times for person, fact
      26Addressesall records from AddressTable of type Address
      0– blank namesName field of AddressTable record is blank
      43Tasksall records from TaskTable
      2– Research Logall records from TaskTable of Type 1
      41– ToDoall records from TaskTable of Type 2
      0– Correspondenceall records from TaskTable of Type 3
      1Foldersall records from TagTable of Type 1
      34Groups*all records from TagTable of Type 0
      * NOT TRANSFERABLEvia GEDCOM or Drag&Drop to another RM database

      Future additions could include information about the FamilySearch and Ancestry linkages.