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.

Usage:

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.
RM5_CREATE_as_DB3_NOCASE.sql
RM5_Copy_data_to_DB3_NOCASE.sql

Discussions & comments from Wikispaces site