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 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.
  Facts+-+Split+Shared+to+Individual
   
  I think he has also made it part of the RMtrix program that makes it
  easier to run.
  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.
  >
  >
  

RMtrix User Experience

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

• 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
  

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

• UPDATE EventTable
  SET EventType = 1
  WHERE EventType = 1016
  ;

• At this point open the database again with Rootsmagic, use File > Database Tools > Rebuild Indexes and check the changes you just made.

• 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
  

  :rolleyes:

  and remember to experiment first and always back up before applying any queries.

Discussions & comments from Wikispaces site