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.
RMNOCASE_fake-SQLiteSpy.dll.bak 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. (Remove .bak extension after d/l)
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”.
Discussions & comments from Wikispaces site
Unable to download fake RMNOCASE dll
18 December 2011 01:24:50
Well, I can download it, but within a second or two it has been deleted by my Norton anti-virus. I’ve been looking for a way to whitelist the fake RMNOCASE dll, but it looks like I will have to convince the Norton server to whitelist it for everybody rather than being able to whitelist it here on my local machine just for myself. Big brotherism run amok.
I’ll keep investigationg.
18 December 2011 03:34:58
I got it to work. I had to ignore a pretty severe warning from Norton and move the file out of their Quarantine. Having done so, it all works.
In the beginning of researching the problem, it looked like I was going to have to submit the fake RMNOCASE dll to Norton for their engineers to analyze before they would whitelist it, but I found a way to force Norton to accept the dll without having to submit the dll to Norton for approval.
24 December 2015 23:47:18
So I ran RMNOCASE so I could use .rmgc files on my smallest RM data file (2912 KB). Then I ran an sql. Then I asked RM to Rebuild the Index because I do have some non-English characters in some of my names. That was over an hour ago and the index still isn’t rebuilt. I shudder to think what would happen if I rebuild an index for my biggest files of 72,000 KB or 80,000 KB. Am I doing something wrong? I hope this index gets rebuilt before I need to shut down the computer for the night.:-(
25 December 2015 00:53:20
I don’t think you are doing anything wrong. The problem is that the RootsMagic Rebuild Indexes does more than the SQLite REINDEX function which it includes. It also updates the BirthYear and DeathYear columns in NameTable and that is what I suspect is slow. I was delighted that Bruce added the REINDEX function but dismayed that he combined it. Better they were kept separate but that might have caused more user confusion. Check your Windows Resource Monitor for cpu and memory utilisation and hard drive traffic. Perhaps there is some room for optimisation.