SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

SQLite Error 26 – A Surprising Cause

Posted on by Tom Holden
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

Posted on by Tom Holden

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

New App Aids RM Media Management

Posted on by Tom Holden

TestExternalFiles by Richard J. Otter is a new Windows utility that helps identify these issues:

  • A file on disk may get renamed, or moved, breaking the link
    from the database.
  • A file may be added to the media folder on disk but then not attached to the
    desired database element.
  • A file may be added to RM, but then detached from all sources, facts etc , leaving it
    “un-tagged”.
  • A file may be added to the database more than once.

That #media is one of the largest tags in the cloud in the sidebar says something about needed improvements in RootsMagic’s media management over its lifetime to date. Previously published scripts or apps may also have addressed one or more of these issues but what is novel or different about TestExternalFiles is:

  • Developed in the Python programming language
  • Executable either from the Python script (.py) if you have Python installed or as a standalone command line utility (.exe) for which #Python need not be installed
  • Optional exclusion of specified folders and/or files from the directory listing to be compared with the database’s collection of media items
  • Outputs a .txt file listing the results of its queries under these headings:
    • Files Not Found
    • Unreferenced Files
    • Referenced Folders
    • Files with no Tags
    • Duplicated Files

TestExternalFiles has no user interface. One edits a .ini file to specify paths to the database file under test, the root folder for the media files and the output file along with setting which options are to be run and listing the exclusions. Double-clicking on the .py or .exe in the File Manager (or otherwise launching the utility) results in a brief flash of a blank window and it’s done. It is fast! Look for and open the output file to see the results of its analysis and act on as wanted.

Download the latest release from Richard’s github server ricko2001/Genealogy-scripts where you will also find fuller documentation and a bunch of other tools he has developed for his own use. The quality of his coding reflects his professional career in software development.

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!

Source Citation Sort RM8 #sources #citations

Posted on by Tom Holden

Issue

As for earlier versions, the order in which Citations for a given fact are presented in RM8 is the order in which they were added. Some users wish for them to be sorted alphabetically by name.

Solution

A brute force method was described for earlier versions at Source Citation Sort . In that case, the name of the Master Source was presented in Citation Manager so the CitationTable itself had to be reordered according to the Source Name and required links from tables for media and web tags to track the changes. With the introduction of TreeShare, there was a further complication for links between Citations and Ancestry.com. Revisions to the database structure in RM8 cause that script to fail.

Luckily, those changes in database design for RM8 that enabled reusable citations (master citation concept) make the job of sorting the citations easier and faster than for pre- RM8. It is the new CitationLinkTable that must be re-ordered and that has no knock-on effect for media, web tags or Ancestry.com links which remain linked to the CitationTable which does not need to be re-ordered. The new CitationName field requires that it be the second sorting field after the Source Name.

To sort citations in a RM8 database, load the following script into a SQLite manager with a fake #RMNOCASE extension and execute it against the target .rmtree file (backup first!). Note that the database file likely must not be open in RootsMagic as it may cause a ‘file is locked’ error on the outboard SQLite. When you reopen the database in RM8, run Files > Tools > Rebuild Indexes to clear out indexing errors from the outboard process reported by RM8 Test Integrity.

CitationSort-RM8Download

This post is the outcome of the Forum discussion CitationSort.sql giving an error when run..,

RMTREE Properties Query for RM8

Posted on by Tom Holden

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

RMTREE_Properties.sqlDownload

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.

Site Maintenance & Updates

Posted on by Tom Holden

Yeah, really interesting… but you may notice some things are better than they have been:

  • the Forum pages update from your post much more quickly
  • images that previously were illegible and would not open to full size now do

Forum

It’s bothered me for quite a while that it would take several seconds for a post to the Forum to show up on the screen; sometimes the browser would time out but the post would show upon refreshing. Then, recently, it got worse: after timing out, the post was invisible, even though it showed as the latest one. Meanwhile, Blog posts continued responsively with nearly instant display after publishing.

It took a visit to the Asgaros Forums Support Forum to discover others having the same issue and that a tweak to Litespeed cacheing to exempt the /forum/ path was all it took to fix the problem. I had only recently installed the Litespeed plugin so the non-display after posting issue has not been long-lived. And I suspect that the first issue, the delayed display of a Forum post, could be related to when the site host, Hostinger.com, converted the server(s) that host this site from Apache to Litespeed. That would have been sometime after 2019 as that’s when they started to trial Litespeed according to this article: Improving Website Performance with LiteSpeed.

I don’t know if Litespeed is providing any real benefit and, since I have not gone through the setup and configuration process other than making this one exemption, maybe it isn’t. For example, I think I’m supposed to activate a ‘crawler’ and some other things. There are many administrative pages involved and much stuff to read…

Images

Another thing that has been niggling me since transferring to WordPress from Wikispaces was that so many images were illegible because they were shrunk by this theme with its relatively narrow column and one had to right-click to get a menu which included ‘Open image…’. At that time, it was not obvious to me, as a newcomer to WordPress, that there was an easy way to make an image have a hyperlink to itself. Now I know there is when posting and there probably was then but it wasn’t obvious and, anyway, hundreds of posts and images were batch transferred so I did not go through each one with the editor at that time. Now I’ve done so over the course of several hours, inspecting all 290 posts and enabling the hyperlink to itself (I swear that was not a feature back then!). So, if you cannot read an image here, left-click on it to see the full size.

WordPress and Plugin Updates

Months can go by before I update the WordPress version and the 29 installed Plugins (5 inactive). They are currently up-to-date. I’ve no idea whether there is anything obvious that you see. One area that has been a bit of a headache has been the number of obviously fake accounts being registered. It’s not bad at the moment but I cannot tell if that is due to improved security or just a lull in the number of attempts.

Direct Import of RM8 database into RM7 – Part 2

Posted on by Tom Holden

WARNING 2022-06-18: This script has been found defective in the handling of reused citations – see Comments at the bottom.

UPDATE 2022-06-12: a version of the script now imports the RM8 ConfigTable to RM7 as it appears to be compatible with RM7, so far.

Because the RM8 import or conversion of a RM7 files seems to preserve the RM7 ConfigTable records such as settings for a Book in Publisher, the return of the data to RM7 will include those, making the round trip from RM7 to RM8 and back transparent except for Research Items and Folders.

Because it is possible that the RM8 ConfigTable could become incompatible with RM7, both the earlier and the amended versions of the script are listed below.

UPDATE 2022-05-14: with one fix, there is a procedure in RM8 that would be more attractive operationally than using these outboard scripts:

RM8’s File > Export Data > DropBox creates a copy of the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android). It is a complete conversion except, in my test, for Roles for shared events, which should be an easy fix for the developers. In my test, the RoleTable was empty which invalidates every shared event.

Moreover, that Export copies over the ConfigTable records which appear to include Custom Reports and Book Publisher settings that originated in the RM7 database which had been converted 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 changes and the hopefully-to-be-fixed Roles for shared events.

Summary

This enhanced script now transfers Ancestry TreeShare data and FamilySearch data from RM8 to RM7, seemingly transparently, and converts #RM8 Tasks and Folders to #RM7 To-Do’s and Logs with some changes due to the different structures for these areas of the two versions of database. It is now a complete data transfer, including between ConfigTables.

Direct import with the script 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. It’s possible to edit in RM8 and view the result in a report from RM7 as fast or faster than in a report from RM8!

Procedure

Same as for Direct Import of RM8 database into RM7 – Part 1 but with one of these scripts:

Import_RM8_to_RM7-Part2.sqlDownload

Above version does not import the RM8 ConfigTable; the amended version below does.

Import_RM8_to_RM7-Part2A.sqlDownload

Folders, and Logs, and Tags, and Tasks, and To-Do’s! Oh my!

Because of structural changes in these areas, I found it very difficult to understand fully how they relate and may not have succeeded. There is an element of irreversible conversion from RM7 to 8 that compromises what can come back because Folders and Logs are not identical. This is the least transparent part of the import and I won’t be surprised to hear of some problem with it.

Caveats

  • The script was developed and tested (not very fully) using SQLiteSpy 1.9.15 with an extension for a fake RMNOCASE collation between databases from RootsMagic versions 8.1.8 and 7.7 on Windows 10. At some point, RootsMagic 8 or its successor will change in a way that breaks the script.
  • It’s recommended to run the RM7 File>Database Tools>Rebuild Index on the target database after importing because the script applies a mismatching collation to the database table indexes, which may give rise to some erroneous behaviour in searching and sorting, more likely with alphabets other than Basic Latin.
  • I can keep the source database file open in RM8 and the destination database file open in RM7 and run the script in SQLiteSpy which reads the first and reads and writes the second. There is a risk that a database file may appear to be locked by one of the applications which may result in an error in RootsMagic for which the only recourse is to close the program. The SQLite manager may get around the problem without restarting by issuing the command END TRANSACTION. If the RM8 database (alias “RM8db”) is still ATTACHed to the main database (the target RM7 database alias “main”) then issue the command DETACH DATABASE RM8db.

Direct Import of RM8 database into RM7 – Part 1

Posted on by Tom Holden

WARNING 2022-06-18: This script has been found defective in the handling of reused citations - see Comments at the bottom of Direct Import of RM8 database into RM7 – Part 2.

Issue

As described in Returning to RM7.5 custom reports, groups and Publisher from RM8 via GEDCOM, there are reasons some users of RM8 would like to be able to transfer data to RM7 more directly than through GEDCOM as the medium is unsatisfactory. In addition to inherent losses identified in GEDCOM & DnD transfer losses, it involves more steps than one might have with a more direct approach.

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. Therefore, this first part of a direct import solution addresses the low-hanging fruit and maybe that's where it will stop, depending on the difficulty of going further. Nonetheless, it may be adequate for many purposes and is being published now so that procedural problems it may have with certain databases may be identified through usage by more people.

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.
  3. Open the target RM7 database with your SQLite manager (one with a fake RMNOCASE collation - see #rmnocase).
  4. Open SQLite manager's SQL Editor on this database and load into it the script Import_RM8_to_RM7.sql.
  5. Edit this script to change the path and name in the ATTACH DATABASE line to that of your source RM8 database.
  6. Execute the script.
  7. On reopening the target RM7 database with RM7, run File>Database Tools.
  8. Media links that pointed to items below the Windows User's root folder will be broken as will those that were downloaded via TreeShare to a sub-folder of that containing the source RM8 database. RM8 uses symbols for those paths that RM7 does not recognise. Use the Media Gallery's Fix Broken Media Links tool or, much faster, Search & Replace (Ctrl+H) on Media filenames to fix them.

Considerations

  1. If you upgraded from a RM7 database to RM8 when your RM7 database had a well-developed Book specification or extensive Custom Reports, try importing the data from RM8 back into a copy of that database. You may be pleasantly surprised that those reports still work, even though you may have made many changes in your tree.
  2. This procedure transfers groups; GEDCOM does not.
  3. 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.
  4. 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.
  5. I get a memory access error in citations using Find Everywhere on a 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 imported.
  6. 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.
  7. Good luck!

Returning to RM7.5 custom reports, groups and Publisher from RM8 via GEDCOM

Posted on by Tom Holden

UPDATE 2022-05-14: better than GEDCOM is to convert directly from RM8 to RM7 for which there are a couple of possibilities:

  1. RM8’s File > Export Data > DropBox creates a copy of the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android). It is a complete conversion except, in my test, for Roles for shared events, which should be an easy fix for the developers. In my test, the RoleTable was empty which invalidates every shared event.
  2. Use one of my SQLite scripts that were developed subsequent to this original post:
    Direct Import of RM8 database into RM7 – Part 1
    Direct Import of RM8 database into RM7 – Part 2

Once the RM8 Export to DropBox is fixed, it would be operationally more attractive and is supported by RM Inc.

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 way back using the RM user interface is via #GEDCOM which, along with other attendant losses (see GEDCOM & DnD transfer losses) does not import #namedgroup and custom #reports. The original #RM7 database contains the custom reports and book specifications and possibly useful groups as they existed at the time of committing to the upgrade but importing into it results in an overload of duplicates, as described by PatrickR in his Apr 9 post to the RootsMagic User Community: Is there a way to copy custom reports to another database?

A solution

Years ago, I had an approach to creating a quasi-empty shell database file from a populated database that I thought might be adapted to this particular case: Depopulate but keep Customs, Places, Sources. Maybe a variant that depopulated but instead kept groups, custom reports, and book definitions could be used to import the GEDCOM from RM8 to good effect. I’m pleased to report that my experiment was successful, to the extent that GEDCOM transfer allows.

Procedure

  1. Review GEDCOM & DnD transfer losses and, in your RM8 database, ensure that your Fact Types are all set to export to GEDCOM. Run the RM8 database tools and Export the database having chosen to export everyone and everything.
  2. In RM7, make a copy or backup of the original database from which the RM8 ‘child’ was created. Run the RM7 database tools. Close the RM7 database file.
  3. Open the RM7 database file with your SQLite manager (one with a fake #RMNOCASE extension), load and execute this SQL file:
Depopulate-RM7_5.sqlDownload
  1. Reopen the now-depopulated database file with RM7.
  2. Run the RM7 Database Tools.
  3. Import the GEDCOM file from RM8 choosing the option
    Preserve record numbers
  4. Done! Review what you’ve got. Note that the Media Gallery has to build thumbnails anew.

Possibilities

It should be possible to transfer data more completely by directly copying data from the RM8 database to the RM7.5 database via SQLite. However, because of changes in database structure, it’s not straightforward for several tables. Also, there may be serious incompatibility between the ConfigTable content of the two versions which may preclude the carryover of custom reports and other settings.