How+to+Query+RootsMagic

toc =Promo= 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 RM//trix//.

=SQLiteSpy User Experience= Responding to a message bemoaning the problem with shared facts, Kim Mills posted this on [|RootsMagic-Users-L]: > code 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. http://sqlitetoolsforrootsmagic.wikispaces.com/Facts+-+Split+Shared+to+Individual

I think he has also made it part of the RMtrix program that makes it easier to run. http://sqlitetoolsforrootsmagic.wikispaces.com/Bundled+Utilities+-+RMtrix

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.

Kim

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

code =RM//trix// User Experience= In the same thread, Bonnie responded that she had become a new RM//trix// user! > code 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.

Installing Rmtrix: 1) The source for Rmtrix is http://sqlitetoolsforrootsmagic.wikispaces.co/Bundled+Utilities+-+RMtrix 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 b) ->Unshare 3) Export to a gedcom (this file can be uploaded to wc.rootsweb.ancestry.com) 4) Delete the COPY of the database

-Bonnie

code

=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 __t__ype// selection box OR in the //File __n__ame// entry box, type '*.rmgc' (without the quotes). You may also want to be extra safe by checking the box //Open as __r__ead-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// [|http://forums.rootsmagic.com/index.php?/topic/13013-change-fact-type-globally-after-import-from-legacy/#entry60476]:

> 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?

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

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

> http://sqlitetoolsfo.../SQLiteManagers

> 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 **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventTable **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">, Don't look at it in too much detail but the **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventType **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> number corresponds to the **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">FactTypeID **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> in the **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">FactTypeTable **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">, in other words they are linked and this **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventTable **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> contains all your facts both Individual and Family.

> <span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">Jump back to the **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">FactTypeTable **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> and examine your facts. For the purpose of explanation I will use an example where a **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">FactTypeID #1016 **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> is found called **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">BORN **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">, well we know that this is should probably be the Rootsmagic built in Birth Fact which is **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">FactTypeID #1 **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">. So once we are happy that we want to globally make that change go back to the **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventTable **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> by double clicking on it and all we want to do is change all instances of **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventType #1016 **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> to **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">EventType#1 **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">, 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.

> code format="sql" UPDATE EventTable SET EventType = 1 Where EventType = 1016 code > <span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">At this point open the database again with Rootsmagic, use **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">File > Database Tools > Rebuild Indexes **<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;"> and check the changes you just made.

> <span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">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.

> //<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">**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 ** // //<span style="background-color: #ffffff; color: #282828; font-family: helvetica,arial,sans-serif; font-size: 14px;">**and remember to experiment first and always back up before applying any queries.** //