Database Diagram for RootsMagic version 9 – there are probably more table relationships to be added.
SQLite Tools for RootsMagic
Exploit your RootsMagic family tree database with SQLite Tools
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.
| Table | RecType | OwnerType | OwnerID | |
| Payload | 1 (SavedCriteriaSearch) | 8 | 0 | |
| Payload | 2 (SavedCriteriaGroup) | 20 | TagTable.TagValue, GroupTable.GroupID | |
| FANTable | 19 | CitationLinkTable, MediaLinkTable,… |
Table Specs
This is a tab-delimited result of a query of the tables.
Remove Duplicate Merged Notes
Introduction
This script was prompted by a discussion in the RootsMagic Community on Feb 10 ’23: Notes are duplicated. The duplicate begins with MERGED NOTE. It is not uncommon, viz this Google search for the MERGED NOTE flag generated by RootsMagic. Its results return many pages, from sites.rootsmagic.com and from other sites with content from RM databases. There must be many more unpublished cases among users who have used RM’s Merge functions.
Problem
From RM4 on, merging of persons preserved the Person (General) Note value of the secondary person by concatenating it with that of the primary person separated by the string ‘– MERGED NOTE ————‘ and some whitespace. In some cases, both persons had the same Note but RM concatenated them anyway. And in others, there have been multiple merges into a person with duplication of notes occurring in any pattern.
Complicating the test for duplication is that some notes are identical except for the loss of trailing whitespace (when GEDCOM is involved) or the dropping of a symbol such as “<” or perhaps some punctuation.
Solution
This script parses the PersonTable.Note into the parts between the MERGED NOTE flags, keeps the alphanumerically distinctly different parts and concatenates them into a new Note with a symbolic demarcation ‘-+-+-+-+-+-+-+-‘ that is less brazen but still useful. It presents a table with both the NewNote and the OldNote for inspection.
Updating the PersonTable with the new Note is done by executing a selected statement within a comments block. The original flagged Notes are copied into a temporary table from which they can be restored, prior to re-executing the script after updating PersonTable with the revised Note.
Requirements
Uses Common Table Expression (CTE) and the REGEXP_REPLACE() function and was tested on SQLiteSpy 1.9.15 Win32. Any current SQLite manager that has been compiled with a relatively recent SQLite source code (CTE was introduced a few years ago) and has an extension or includes the REGEXP_REPLACE() function using the same syntax as SQLiteSpy should work.
Usage
- Close the database in RM with a backup.
- Open the database in SQLiteSpy or equivalent.
- Load script and execute.
Executing the whole script (F9 in Spy) does not change the data in the PersonTable; it presents you with a table to compare the draft new Note to the original|current Note. You have to select|highlight the UPDATE statement in the comments at the bottom of the script and execute just it (Ctrl+F9) to apply the new Note to the PersonTable. I set it up that way so that unwanted outcomes might be found and mitigated by editing the original Note BEFORE the database is changed. - Inspect the draft NewNote against the OldNote.
- If satisfied with the results, select the first UPDATE statement at the end of the script and execute it (Ctrl+F9 in Spy). See screenshot below. You could add a constraint to the query to update only to the last Person you have reviewed, assuming you are working in order of ascending RIN:
AND PersonID <= last_PersonID_checked; - If you wish to revert to the original note, select the second UPDATE statement at the end of the script before running the script again (which will update the temporary table PersonNoteOriginal) or before closing the database out of sqlite.
- The three temporary views and the temporary table created by the script won’t empty until the script is run a second time, reflecting that the table has been emptied of those with the MERGED NOTE flag; they will all be dropped on closing the database from the SQLite manager.
Script Download
2023-02-17 Script revised for easier comparison of new vs old Notes and name changed.
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!
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:
- Download and install the SQLite command-line interface if you don’t already have it: https://sqlite.org/download.html
- Open a terminal or command prompt window and navigate to the directory where the SQLite executable is located.
- Connect to your RootsMagic database by typing the following command, replacing “database_file” with the path to your database file:
sqlite3 database_file
- 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';
- 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
- 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.
Surname ALL-CAPS to Propercase #names
Introduction
This procedure responds to a call for help from a RootsMagic user who had received a 40,000 person GEDCOM file in which all the surnames were in capital letters and which he wanted to integrate with a database in which surnames are mixed case. How could the SURNAMES be converted to Surnames without editing in RM each of the 40k people or a Search and Replace for each unique name (numbering in the thousands)? I suggested a few ways (see Alternative Solutions below) but realised that a wholly SQLite solution working directly on the database might be do-able and be the best.
Update
2022-11-02: I have to admit that I undertook the challenge of doing this procedure without realising that NameClean had the ALL-CAPS conversion feature. I may have had no prior need to use it and merrily presumed that the requestor had hit a dead end trying to do it in RM7. While NameClean executes the updates much more slowly than mine does, that may be of no serious consequence for a one-time operation. And, to my surprise, NameClean does have some inventory of Scottish “Mac…” surnames which it defaults to, rather than upper-casing the following letter, regardless, i.e., MACRON (French) does not become MacRon but Macron, MACKINTOSH becomes Mackintosh whereas MACNEILL=>MacNeil, etc. “MC” is likely rule-based: MCCOY=>McCoy.
Development
The current release of SQLite 3 does not have a Titlecase or Propercase conversion function but at least one SQLite manager, SQLiteSpeed, has an internal extension with it. I tried it first with mixed results on a small set of test names (second column in the table below). The white cells have acceptable results, yellow unacceptable, peach ambiguous because the result might be acceptable for one person but not for another (cultural variation…).
Scouring the Internet, I turned up a very clever recursive query for Titlecase conversion that could work in any current-enough SQLite manager as it uses the standard functions. Tried it with SQLiteSpeed, SQLiteSpy and SQLite Expert Personal. The first two seem to have inconsistent support for Unicode, e.g., in the lower() or substr() functions while Expert provided the best results and execution was very fast.
Considering the procedure, I thought the conversion should only apply to names that are ALL CAPS and not to any others so as not to undo correct entries such as the green test names. That prompted the REGEXP filter which behaved differently on Spy and Expert and not at all on Speed but I eventually settled on one using standard functions common to all.
Further, there should be an intermediate step to inspect the converted names before applying them to the database NameTable to allow for deletions and corrections of undesired results that can only be found through inspection.
The end result is a SQL file that is not a single script intended to be executed in one go. Rather, it contains a series of short scripts enclosed as comments, each being a step to be executed in order. If you execute the whole file accidentally, there is no result because everything is commented. Each step executes very quickly but for the user interaction. The slowest execution is the actual update of the NameTable which still took only 35s for 35,000 records on a near-obsolete i5 Windows 10 laptop and hard-disk drive.
Procedure
Requirements
- SQLite Expert Personal or equivalent with full support for Unicode, run-time variables and loadable extensions
- RMNOCASE – faking it in SQLite Expert, command-line shell et al #rmnocase
- You’ve backed up your database or are working on a disposable version!
- Read and follow the instructions
- If you are on MacOS and can make it work with some SQLite tool, please let us know!
Download
29 Oct 2022
Usage
- Open and select the RM database in SQLIte Expert Personal and ensure the RMNOCASE extension is loaded. Any RM database from V4 to at least V8 should work.
- Load the SQL file in the Editor. Read through it to familiarise yourself with its comments, format…
- You will get no result other than a warning if you simply execute the file because the code is within comments. You must select (highlight) a block of code within each STEP and execute it, in sequence.
- STEP ONE: makes a copy of the Surnames and key field from NameTable in the permanent SurnamesOLD table. The last step removes this table from the database. All other tables created in the procedure are temporary, in memory only, and are lost when the database is closed.
- STEP TWO: makes temp.SurnameWORK table of unique UPPER CASE Surnames to be converted to Proper Case. Prompts for first letter on which to filter. Leave blank to get all.
You will be repeatedly prompted in subsequent steps. - STEP THREE: Converts Surnames from SurnamesWORK to Proper Names in the temp.ProperName table using the recursive query adapted for this purpose. The query lists the results for review but edits must be made in the Data view on the ProperName table. You can use its sorting and filtering tools to help with a large number of records.
- ADDENDUM 1: Creates temp.SpecialName table to adjust names beginning with Mac or Mc by capitalising the following letter. This code could be expanded for other special cases. The table can be reviewed and edited before being applied to the ProperName table in…
- ADDENDUM 2: Updates the ProperName table from the SpecialName table.
- STEP FOUR: Updates the NameTable from the ProperName table.
- STEP FIVE: Contains a number of queries with which to check results.
- STEP SIX: Leave no trace behind if you are finished – deletes the ProperName table from the database.
- N.B.: When you next open the database with RootsMagic, you must run the File > Database tools > Rebuild Indexes else it will fail the Integrity Check and may give some strange results in sorting and searching.
You will be repeatedly prompted in subsequent steps.
Alternative Solutions
- A higher level language such as perl has the needed Titlecase() function which could be used to process and return the result to a sqlite query from the platform.
- Spreadsheet and text editors may have the needed function: Google Sheets and Notepad++ both do. You could export a query of the NameID and Surname fields from NameTable. Process the list externally. Import the revised list to a temporary table and update the NameTable from it.
- Use Notepad++ or a scripting tool to operate on the GEDCOM file. For example, adapt this procedure on NotePad++. While it’s been written for something other than GEDCOM, I think the regex for GEDCOM to cover hyphenated and non-hyphenated names would be:
(?:1 NAME |\G(?!^))[ -]\K(\w)(\w+) - Other genealogy software do have a conversion tool: Legacy Family Tree has it built in and offers a feature-restricted free trial; Family Historian has an available user-developed plugin that you could use in its 30-day unrestricted trial. You’d want to check if there are any important losses in the import-export operations needed in using one of these.
SQLite Error 26 – A Surprising Cause
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:
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:
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:
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:
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
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: #sqlean
- 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
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
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:
- Direct Import of RM8 database into RM7 – Part 1
- Direct Import of RM8 database into RM7 – Part 2
- Forum discussion: Direct Import of RM8 database into RM7 – major update
For a full understanding of what has gone into the current procedure, they remain useful references.
Procedure
- 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.
- 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.
- Open the target RM7 database with your SQLite manager (one with a fake RMNOCASE collation – see #rmnocase).
- Open your SQLite manager’s SQL Editor on this database file and load into it the script listed above under Solution.
- 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.
- Execute the script.
- On reopening the target RM7 database with RM7, run File>Database Tools.
- 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
- 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.
- 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.
- 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).
- 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.
- 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).
- 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.
- 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.
- 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.
- 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.
- 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.
- Good luck!
Source Citation Sort RM8 #sources #citations
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.
This post is the outcome of the Forum discussion CitationSort.sql giving an error when run..,