RMNOCASE+-+faking+it+in+SQLiteSpy

=Background= RootsMagic 4+ has an internal, proprietary collation sequence called RMNOCASE that controls the order in which names of people, places, sources, etc. are sorted. Fields that are defined to use this collation cannot be sorted by an external SQLite tool without overriding it with another collation sequence, e.g.: NOCASE, nor can they be edited by the external tool without its own RMNOCASE collation. Despite its lack of the latter, SQLiteSpy was at the top of the list of SQLite managers for quite some time since the early days of this wiki. During that time, the only tool we knew of to get around the RMNOCASE obstruction was with the $30 SQLite Developer by renaming one of its UTF-8 collations. Then, with a custom extension described below, SQLiteSpy could do it, even more easily than SQLite Developer, for free. More on the SQLiteSpy story follows but, as of July, 2013, there is a new leader on the RMNOCASE front: see RMNOCASE - faking it in SQLite Expert, command-line shell et al.

**Faking RMNOCASE in SQLiteSpy** Thanks to Ralf, the developer of the SQLite3 database engine for Delphi (DISQLite3) on which SQLiteSpy and RootsMagic 4 and up are based, I have compiled an extension for SQLiteSpy that adds a fake RMNOCASE collation sequence, thus enabling Spy to freely query and, indeed, edit the .rmgc databases. No more need to declare COLLATE NOCASE when comparing and ordering certain fields; now it's possible to modify values in all fields and to insert or delete rows in all tables using the free SQLiteSpy.

But be warned, this collation is not identical to RMNOCASE and there may be some risks - probably none if the English alphabet is used exclusively. All it does is provide the NOCASE collation sequence to SQLiteSpy under another name, RMNOCASE. With other alphabets, anticipate that there will be differences in sorted lists and search results between RootsMagic and SQLiteSpy; editing, with non-English characters, a field that is collated and indexed with RMNOCASE will corrupt the index.

The good news is that RootsMagic 5 has a REINDEX tool that will rebuild the indexes using its internal RMNOCASE. For RootsMagic 4, the only way to rebuild the indexes is to transfer the data to a new database via drag'n'drop or GEDCOM. It is advisable to REINDEX in RootsMagic after adding or modifying any name-type data with an outboard SQLite manager.

Download & Installation
** UPDATED! ** New version dated 27 June 2013 (16KB supersedes 40KB 26 June) corrects an error which resulted in INTEGRITY_CHECK still reporting missing rowids in some indexes after REINDEXing by SQLiteSpy. Replace the earlier version. Still not compatible with RootsMagic RMNOCASE so you must still use its Rebuild Indexes Database Tool after any SQLiteSpy queries that change data or REINDEX.

Download to the same folder where you have installed SQLiteSpy.exe. Open SQLiteSpy without a database and note the list of Collations numbers 7 and does not contain RMNOCASE. Register the extension via the menu path Options > Options > tab Extensions where you will enter the filename and OK. Create a new database (File > New) and note the list of Collations now includes RMNOCASE. It did before when a .rmgc file was open but SQLiteSpy had no access to the RM collation sequence. Now SQLiteSpy accesses the NOCASE collation whenever RMNOCASE is invoked. As long as SQLiteSpy stays open, the fake RMNOCASE collation remains accessible for every database opened during the session. The extension automatically reloads when SQLiteSpy is restarted, provided SQLiteSpy.exe is in a folder over which you have full privileges to allow Spy to write the file SQLiteSpy.db3 which stores the options (Spy does not report any problem if it cannot write the file). Alternatively, the extension can be loaded by command in the SQL editor and this command can be stored in a script. From the [|SQLiteSpy product description], " Extensions can be loaded for the current database via SELECT load_extension('FileName.dll'); or automatically for all databases by entering extension file names to the Options dialog".