You need a SQLite manager such as SQLiteSpy to read and modify a RootsMagic database using the queries or scripts from this wiki. That’s because RootsMagic incorporates the SQLite 3 database engine to write and read its database files.
SQLite acts on the database in response to SQL statements or commands it is given. These are commonly called “queries” which is a bit misleading because a query can also modify the data. A script comprises one or more statements. Because RootsMagic does not accept user inputted SQL statements, we use an outboard SQLite manager, an application that provides an interface for the user to input SQL statements to operate on a database file.
Because SQLite 3 is a free, open-source package, there are lots of applications that can open a RootsMagic file. I recommend SQLiteSpy to start with on Windows – I don’t know Mac. See SQLite Managers for download links.
You will also need to extend your SQLite manager to substitute for the secret RootsMagic RMNOCASE collation sequence having a large multilingual alphabet which is used to sort names of people, places, etc. . Some of the queries simply will not work without it, returning the error “SQLite3 Error 1 – no such collation sequence: RMNOCASE”. See RMNOCASE – faking it… to get the substitute.
OK, get ready to rumble… First and foremost, make a backup of your RootsMagic database or a copy of it that you can play with so that if it gets messed up by your queries your master is intact. If you do not know how to do that, do not pass Go but proceed directly to Jail!
With SQLiteSpy installed and opened:
- File > Open.
- Browse to the folder where your play database is located.
- Start typing the first letters of the database filename or enter *.rmgc to get a list of the RootsMagic database files
- Select your file and it will now be registered in the left panel.
- Click on the + signs to expand and see the list of tables.
- Double-click on a table name and SQLiteSpy will automatically issue a (hidden) SQL statement or query to list all of the records in that table. Results will display to the right in the middle pane . Select any cell in the results pane and you can see its content in the bottom pane.
- In the upper pane, type in “SELECT Surname, Given FROM NameTable;” and then press F9 or click on menu Execute > Execute SQL. The results of this user-inputted query display the same way as for the macro query of step 6.
Congrats! You have run two queries on your database.
You can copy and paste a script from this wiki into the same SQL editor space or download a script file and use File > Open SQL to load it into the editor. Read about the experiences of other beginners at How to query RootsMagic.
I have had the same database open in both SQLiteSpy and RootsMagic with little conflict. After running a script that modifies data, you may need to refresh a RootsMagic screen to see the effect; closing and reopening the database is sure to do so. RootsMagic sometimes (wrongly) leaves a lock on the database that prevents SQLiteSpy from executing a query; closing RootsMagic is the only solution.
After modifying a database outside of RootsMagic, it is advisable that when you next go to use it in RootsMagic to run the RM Database tool “Test database integrity”. If it is OK, you are good to go. However any change made to a name is likely to cause the test to fail and require that you apply the “Rebuild indexes” tool. I have yet to see any harm come to a database by the inconsistencies between the fake and the actual RMNOCASE collations.
For more on using SQLiteSpy, this tutorial (while unrelated to RootsMagic and genealogy) does provide useful explanations:
[TUTORIAL] SQLiteSpy 101 – Civilization Fanatics’ Forums