Convert Database to NOCASE #rmnocase

There are a number of reasons that it would be desirable or necessary to get out from under the limitations imposed by the proprietary RMNOCASE collation sequence used for indexing (comparing strings) in RootsMagic 4 and 5. Without a SQLite manager that can provide a fake substitute with the same name, there are several key fields that cannot be edited and tables that cannot have rows added or deleted along with added complexity in queries. Until recently, the only such manager that I had come across was SQLite Developer, the license for which is around $30 for personal use, an obstacle to some not to mention that it is less than an ideal tool. Now, SQLiteSpy can do it with one extension and SQLite Expert can do it with another, superior one.

In Comparing Two RM Databases, Jerry tried unsuccessfully to use SQLite Compare which has no fake RMNOCASE support. That stimulated the idea that simply exporting the data of each database to two other non-RootsMagic SQLite databases would enable SQLite Compare to compare the data with the integral NOCASE collation instead of RMNOCASE. That led to the development of the following queries:

RM4_CREATE_as_DB3_NOCASE.sql This query was built by copying the database schema of the RM database in data definition language (DDL) and replacing all instances of RMNOCASE with NOCASE.

RM4_Copy_data_to_DB3_NOCASE.sql Download and save both to your RM/SQLite workspace. See bottom of page for the RM5 versions.


  1. Open your SQLite manager
  2. Create and open a new empty database with the default .db3 extension (this is not mandatory, it could be .rmgc if you intend to open it with RootsMagic)
  3. Load the first query file and execute. The new database structure is created.
  4. Execute this statement, substituting the full pathname to your source RootsMagic database file between the single quotes (and change the RM# to suit): ATTACH DATABASE ‘full URI to the RM4 file’ AS RM4 ;
  5. Load and execute the second file – now you have all the data from your RM4 database in a DB3 with standard collations.
  6. Execute this statement (change the RM# to match step 4) to disconnect your RM database: DETACH RM4 ;

Now you are free to do what you want with the data you have exported from RM4.

For RM5, as the database structure is different, so too must the Create and Copy queries be different from those for RM4.

Discussions & comments from Wikispaces site


This all works, now that I’ve had time to play with it

18 December 2011 04:28:54

All I’ve done so far is to make two very small test databases, test1.rmgc and test2.rmgc that originally were identical. I made a minor change to test2.rmgc. Next, I copied the databases to test1.db3 and test2.db3, respectivedly, where the db3 versions had the NOCASE option rather than the RMNOCASE option and where I used Tom’s scripts and directions to accomplish the copy. Finally, I ran SQLiteCompare against the db3 versions of the databases, and all was well. SQLiteCompare did not complain about RMNOCASE, and it accurately and easily identifed the differences between test1.db3 and test2.db3.

One thing I haven’t tried yet is to be sure that RM5 itself can operate directly on the NOCASE version of the databases. As Tom suggests, I’m sure it will work fine. If it does, it will greatly facilitate testing. What I will be doing will be to drag-and-drop (or GEDCOM Export/Import) between two databases and analyze what data, if any, is lost or changed by the process.



18 December 2011 16:27:05

I have confirmed that RM5 can operate on a NOCASE version of a database just fine. So I can make a standard RMNOCASE version of a database and convert it to a NOCASE version of a database just fine. And I can compare NOCASE versions of two databases just fine using the SQLiteCompare utility, which is a very wonderful utility.

But nevertheless, I’m totally unable to complete the project I wished to complete. What I wished to do was to drag-and-drop or Export/Import a RM5 database and then to compare the original with the copy to see what data might be lost in the process. But beyond a very small test database with only one or two people with one or two facts, the project is really hopeless. Here is the problem.

Suppose I start with a new, blank database and add one individual. Suppose I then add a birth fact to the individual, add a death fact to the individual, and delete the birth fact. The EventID for the birth fact was 1 and the EventID for the death fact was (and still is) 2. After a drag-and-drop operation into a new database, the EventID for the death fact is 1. Multiply this effect by tens of thousands of people and hundreds of thousands of events, and the new database will have hundreds of thousands of differences with the old databases, even if the drag-and-drop operation is perfect.

I only kick myself for not thinking about such an obvious problem before even starting this project.



Leave a Reply
