SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Convert RM8 Database to RM7 #rm8 #rm7

Posted on by Tom Holden

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

Import_RM8_to_RM7-V3.sqlDownload

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!

14 Replies to “Convert RM8 Database to RM7 #rm8 #rm7”

  1. Tom, much thanks for all your efforts on this project. As I’m sure you already know, I’m still on RM7 so I don’t yet need to run your RM8 to RM7 script. I will test it thoroughly at some point prior to my final RM8 conversion, but it looks like you have covered most or all of the essential problems.

    One issue that I have thought about is that I don’t use RM7’s To Do Lists and Research Logs, but my RM7 database is still full of such items that I entered before a gave up and abandoned their use. I plan to delete what I had entered into RM7 before any final conversion to RM8 to avoid having a mess of RM8’s Tasks. What I have done so far is to run a full RM7 To Do List report and saved it as an RTF file. The file appears to preserve everything I had done in RM7’s To Do list in the unlikely event that I ever wanted to use the data again. So I will probably just delete all the rows from the relevant RM7 tables prior to a final RM8 conversion.

  2. Tom, thank you very much for this solution, that I like a lot.
    An observation I made is that citations that have been merged in RM8 are not translated back to RM7 correctly.
    This means, don’t merge citations in RM8 if you want to go back to RM7.
    Cornelius

    • I’m surprised by your comment because the latest version creates a copy of the citation in RM7 for every use of that citation in RM8. Apart from the unavoidable loss of Citation Name which is unique to RM8, it is a perfect replica.

  4. Although I’m a MAC user I do have Windows versions of RM8 and RM7 as well running under Parallels Desktop. I use Treeshare a lot and am only too aware of the problems RM8 is having when dealing with Ancestry sources and citations. I decided to import my current RM8 database to RM7 to see what resulted. The script worked well and took 21 secs on my 40k person database. The only wrinkle was the mediapath symbol which in my RM8 db is ? . A quick SQL script soon put that right.

    • Thanks, John. I forgot having known way back that “?” is the symbol for the path set for the Media Folder in Settings > Folder Settings.

      And that “*” is the symbol for the path to the folder containing the subject .rmtree database.

      I’ll update the script to add the “?” symbol and correct the description of the “*” symbol.

  7. But a new database is generated by “Import_RM8_to_RM7-V3” from a RM9 database, which can not be opened by RM7.
    After changing the sql command:
    …. ‘8000’, ‘6000’) …
    to
    …’9000′, ‘6000’) …

    RM7 can open the reimported database from RM9.
    I only checked the database properties, and they all were identical, between original RM7 database and reimported RM7 database. 🙂

    • Is that all?! I guess it stands to reason.

      The change in structure added a couple of new tables for Associations and they more or less stand on their own although, if an Association event is added to a person, there must be a record added to the EventTable that becomes orphaned (disconnected) from the new FANTable when converted to RM7. There’s no or little harm in that; just needless clutter, I should think.

      The other change, off the top of my head is the addition of colorcoding sets to the PersonTable, 9 additional columns. But the original “color” column name (presented in the UI as set #1) remained the same so it gets copied to RM7 intact while sets 2-10 are lost, of course.

      Thanks for checking that out, Cornelius!

  9. My first use of the script ran into a syntax error on the following code.

    –Convert Tasks for Events to Individual or Family owning the event
    UPDATE ResearchTable
    SET (OwnerID, OwnerType) =
    (SELECT E.OwnerID, E.OwnerType
    FROM EventTable E
    WHERE ResearchTable.OwnerID=E.EventID
    )
    WHERE OwnerType=2
    ;

    I managed to get it to work as follows.

    UPDATE ResearchTable
    SET
    OwnerID = (SELECT E.OwnerID FROM EventTable E WHERE ResearchTable.OwnerID=E.EventID),
    OwnerType = (SELECT E.OwnerType FROM EventTable E WHERE ResearchTable.OwnerID=E.EventID)
    WHERE OwnerType=2
    ;

    I wonder if I’m using an obsolete version of SQLite that doesn’t support probably what is a newer (and friendlier) syntax. I’m using SQLiteSpy 1.9.11 Win32. My SQLite version is 3.13.0.

    It turns out I don’t need this step anyway because I don’t use RM7’s ToDo Lists and Research Logs nor RM9’s Tasks.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.