Home Page list
How to Query RootsMagic #sqlitespy #integrity #database
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
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
Bonnie, I use the shared facts to and this used to be a real frustration
point for me to. But there are a few solutions.
If you just want the _SHAR part removed so it doesn't repeat in the
RootsWeb tree. Go to your tree settings, then to living persons, under
there is a section to exclude tags. Paste _SHAR there. Now it will
look like a normal census or event for JUST the primary person.
But if you want the census or other events to be converted to a real
individual fact for each person, Tom on this group came up with a great
little program for that.
Here is a link to the page that shows how it works.
I think he has also made it part of the RMtrix program that makes it
easier to run.
Basically make a COPY of your database, then run the utility to split
the facts, then the second one to remove the shared facts. This way the
primary person doesn't have 2 copies of the original fact. Then export
the gedcom as you normally would.
On 02/26/2013 06:54 PM, Bonnie Weisel wrote:
> I thought it would be a convenience and time saver to start using
> "Shared Facts". Unfortunately it doesn't work out when exporting and
> uploading a gedcom to wc.rootsweb.ancestry.com.
RMtrix User Experience
In the same thread, Bonnie responded that she had become a new RMtrix
I'd like to thank Denise and Kim for their replies on 2/26 to my
questions about exporting a database with shared facts. When exporting
a Rootsmagic database, if you want the sharee to have a copy of the
facts, you will need to use two sqlite procedures.
Last night I installed and ran Rmtrix, exported my database, all in less
than 5 minutes. My suggestion is this is the easiest route to
accomplish the task. I've copied my how-to notes below.
1) The source for Rmtrix is
2) Download Rmtrix and run setup.
To upload a tree with shared facts:
1) Make a COPY of the Rootsmagic database
2) Run Rmtrix and open the COPY of the database
Events->Convert Shared to Individual
a) ->SPLIT Shared to Individual
3) Export to a gedcom (this file can be uploaded to
4) Delete the COPY of the database
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!
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
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:
- Hi, I'm a new RootsMagic user, and I imported a tree from Legacy. Got my custom fact types in Legacy as new fact types in RootsMagic, and thats nice. But some of them already exists as fact types in RootsMagic, so I wonder if there is a way to change fact type globally? I can change fact type on one fact at a time, but it will be very boring to change about 500 facts for 2500 persons...
- I can edit the new fact types, but I think it's silly to have two Residence facts, so I want to change them if possible. Any tips?
- I would use the SQLliteSpy option and do regularly as I try as far as possible to keep within the Rootsmagic standard set of facts. I do this to maintain some compatibility with the recognised standard and often import gedcoms from other users with multiple custom facts. A simple explanation of how to achieve this global change appears later in this post.
- Personally I would go to Tom's page below and download SQLiteSpy, remember to follow the link to "fake collation" and follow his instructions.
- For a novice with no database knowledge this can look very complicated but a lot can be achieved with very basic single table changes which this question asks/requests, so do read on.
- I would first make a copy of your database by finding it in Windows Explorer, Copy it and Paste it which will give you an exact copy, rename this copy as something so you will easily recognize it as your test database and try SQLiteSpy on that database first. Even when sure what you are now about to do on your working database always back it up before running any queries on it.
- Once you have opened your Rootsmagic database with SQLiteSpy remembering to change the "SQLite3 Database" file option to "Any File" you will see all the tables in the database to the left.
- Double Click on FactTypeTable and you will see a list of all Fact Types (one per row) and each identified by a unique number FactTypeID. As TomH said anything greater than 999 is a user defined fact.
- Now Double Click on EventTable, Don't look at it in too much detail but the EventType number corresponds to the FactTypeID in the FactTypeTable, in other words they are linked and this EventTable contains all your facts both Individual and Family.
- Jump back to the FactTypeTable and examine your facts. For the purpose of explanation I will use an example where a FactTypeID #1016 is found called BORN, well we know that this is should probably be the Rootsmagic built in Birth Fact which is FactTypeID #1. So once we are happy that we want to globally make that change go back to the EventTable by double clicking on it and all we want to do is change all instances of EventType #1016 to EventType#1, really just a find and replace operation. Copy the simple code below into the pane at the top of SQLiteSpy and press F9. The bottom left info bar below the list of tables will now report the time it took to complete the operation and how many records were updated.
- TomH would be very expert in these queries but everything in baby steps and I can understand how some of his more complex queries can appear a bit foreboding so a primary school beginners guide would certainly get more people into using SQL IMO.
- Edit : Hopefully nobody pasted the code snippet literally without changing the the SET EventType - Where EventType numbers to their own requirements. I was going to say hopefully it goes without saying but it's better to be said just in case
and remember to experiment first and always back up before applying any queries.