Logo Home    Page list

How to Query RootsMagic #sqlitespy #integrity #database

Table of Contents

SQLiteSpy User Experience
RMtrix User Experience
Example - SQLiteSpy Quick Integrity Check
Example - Change fact type globally


Using many of these SQL queries or procedures is pretty easy with a SQLite manager such as SQLiteSpy as illustrated in the example below. Even easier, because you don't even see the SQL scripts that underlay them, is to use the utilities bundled in the dead simple RMtrix application. The stories following relate the experiences of novice users who accomplished the same task, converting shared facts to individual ones for GEDCOM export to software that does not support RM's shared events. One used SQLiteSpy; the other did it with RMtrix.

SQLiteSpy User Experience

Responding to a message bemoaning the problem with shared facts, Kim Mills posted this on RootsMagic-Users-L:

RMtrix User Experience

In the same thread, Bonnie responded that she had become a new RMtrix user!

Example - SQLiteSpy Quick Integrity Check

SQLiteSpy is one of my favourites - for its clean interface, speed and display of results. Its installation is really simple - download a ZIP file from here and extract SQLiteSpy.exe to wherever you want. There is no Windows install to perform, the one exe file is complete and ready to run. You will want to create a shortcut and place it in your Start Menu and/or Desktop so you can readily launch it later. Once past the basic familiarisation, you will want to liberate SQLiteSpy from the shackles of RootsMagic proprietary RMNOCASE collation sequence by following the instructions on RMNOCASE - faking it in SQLiteSpy.

SQLiteSpy follows the conventions for Windows programs pretty well. To open a RootsMagic database, use File > Open Database to open the conventional Open dialog window. By default, you will only see files with the extension '.db'; to see RootsMagic files, either select 'Any file (*.*)' from the Files of type selection box OR in the File name entry box, type '*.rmgc' (without the quotes). You may also want to be extra safe by checking the box Open as read-only.


Once SQLiteSpy has opened a RootsMagic database, you will see a screen as below, but with the right side empty.

Type a SQL command or set of commands in the SQL Editor area (each command must end with the semi-colon). In the example is the command to do an integrity check without checking the indices (SQLiteSpy cannot do the full integrity check on a RootsMagic database).

Press function key F9 or click Execute and select Execute SQL to execute the commands in the SQL Editor.

The results of the commands will be shown in the middle pane (if the integrity check had passed, it would simply display 'ok'. Any cell that is selected in the results pane will be expanded in the bottom pane. You can adjust the dividers between the panes to see more or less.

Pretty easy! If you got this far using SQLiteSpy, then you are away to the races!

The File menu is where you can load one of the SQL command files you can download from this site and save a command file you have made yourself. SQLiteSpy does a nice job of tracking recently opened databases and SQL files - just use the Reopen selection.

SQLiteSpy can execute all the commands in the SQL Editor (F9) or just a command that is highlighted (Ctrl-F9). Remember, each command ends with a semi-colon and those that retrieve data begin with SELECT. You don't modify anything with a SELECT.

Don't forget - RMNOCASE - faking it in SQLiteSpy for maximum access to your database.

Example - Change fact type globally

The following problem was posted in the RootsMagic Forums by user royviggo at

Forum user vyger responded with this well-written guide to using SQLiteSpy to accomplish the task:



SqliteSpy copying to the clipboatd

dlwhite03 07 May 2017 13:56:00

I can't figure out how to copy the results of a query to the clipboard. I tried copying the cells but it thinks I'm trying to move the cell. I tried Select All but it only selects the column. Is there some obvious way that I'm not seeing?

ve3meo 07 May 2017 20:43:02

Select any cell. Ctrl-C copies the entire row in which the cell is located
Select any cell. Ctrl-A, Ctrl-C copies all rows

You are probably misled by the highlighting - I was,too, initially.



How to have query accept input from the keyboard.

momakid 09 September 2017 02:51:10

I have the following query:

Select f.FamilyID,
(select n1.surname || ", " || n1.given || " " || f.FatherID from nametable n1 where f.fatherid = n1.ownerid and n1.isprimary = 1) as Husband,
(select n2.given || " " || n2.surname || " " || f.MotherID from nametable n2 where f.motherid = n2.ownerid and n2.isprimary = 1) as Wife,
(select n3.given || " " || n3.surname || " " || f.MotherID || " " || n3.suffix from nametable n3 where f.motherid = n3.ownerid and n3.isprimary = 0) as Alternate
from familytable f
where f.familyid = 1364;

The results displays the record for familyid 1364. How do I change the where statement to be able to input the familyid instead of having to change the familyid every time the query is run?

ve3meo 09 September 2017 03:02:59

Instead of 1364 use a run time parameter name such as $FamilyID (or $EnterFamilyID). You will need SQLite Expert Personal or another SQLite manager that supports run-time parameters; SQLiteSpy does not.