Contents
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
RMNOCASE_fake-SQLiteSpy64.dll.bak 19 Feb 2022 64-bit version of fake RMNOCASE collation for use with 64-bit SQLiteSpy.
RMNOCASE_fake-SQLiteSpy.dll.bak 27 June 2013 32-bit version for use with 32-bit SQLiteSpy.
Neither is identical to 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 download)
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.
Jerry
thejerrybryan
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.
Jerry
Ksquared333
Rebuilding Indexes
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.:-(
Kim D
ve3meo
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.
I’ve just installed SQLiteSpy v1.9.14 Win64.
I can’t get it to recognise the RMNOCASE dll.
I download the RMNOCASE dll (16.0 KB (16,384 bytes)) from the link above into the same folder as SQLiteSpy.exe.
I remove the .bak extension leaving it as ‘RMNOCASE_fake-SQLiteSpy.dll’
I enter that file name into the SQLiteSpy options.
When I create a new database it says it can’t find the module:
“Loading this extension failed:
RMNOCASE_fake-SQLiteSpy.dll
The specified module could not be found.”
It can load another test module (MediaMonkey.dll).
Any suggestions what may be wrong?
Hello kidhazy. Sorry for the late reply. It seems that the site has stopped sending me email notifications requiring admin attention, such as approving your comment, and I just saw it now. Have you sorted out your problem? I wonder if it is a 32-bit versus 64-bit issue. The 64-bit app may require a 64-bit extension. My install is version 1.9.13 32-bit and the 64-bit version came out in 1.9.14 in Feb 2020. I suspect that the fake DLL has to be recompiled for 64-bit as was the case for the unifuzz.dll I use with SQLite Expert Personal.
You are correct Tom Holden…
I just upgraded to SQLiteSpy 1.9.15. The RMNOCASE_fake-SQLiteSpy.dll file that can be downloaded above is a 32 bit extension. Trying to load it as an extension into the 64-bit version of SQLiteSpy fails. I went back and installed the 32 bit version of SQLiteSpy on Win 10 (latest version) and the extension was accepted as expected.
Thanks for the confirmation, John. I regret I wasn’t prescient enough 9 years ago to have compiled a 64-bit version at that time. I’ll dig back into my files to see if I can find the source code. I don’t know if I have the software tool let alone the mental ones to recompile it!
With the guidance from the developer of SQLiteSpy, I was able to recompile his Delphi project for a fake RMNOCASE to make a 64-bit DLL for use with the 64-bit SQLiteSpy. This page has been updated.
I’ve finally upgraded my main pc to Windows 10 with a new clean installation on a different hard drive so am having to re-install all my software, including SQLite ODBC drivers and SQLite Spy. It must be 10 years since I got SQLite and RMNOCASE all working nicely and I’ve forgotten what I did then.
I thought it would be tidy to put SQLite Spy in a directory within the Windows Program Files structure -wrong! These folders are somewhat protected and it can’t then write back the .db3 file with your installed extension for RMNOCASE. Either change the write protection or move SQLite Spy to a folder outside the Windows ones. I created c:\Other Software and put it in there.
I’ll add another post about RMNOCASE working with the SQLite ODBC drivers when I’ve finished testing!
I’ve had the 32-bit version in Program Files (x86) with a shortcut to it in the Start Menu for a very long time. The SQLiteSpy.db3 file is in the same folder with the default protections (can’t write) and is dated 2017! Yet, I inspected it and the extension for RMNOCASE is in it. I must have set it up outside Program Files and then moved it there because it has worked reliably through successive versions of Spy ever since.
Although I’ve had the 64-bit version for some time, I must not have completed getting it going because I found it in Program Files with unifuzz.dll but no .db3 file and no RMNOCASE_fake-SQLiteSpy64.dll. That config may have been an attempt to see if the 64-bit version could use unifuzz as an extension after which I followed up with Spy developer Ralf about getting a 64-bit version of his extension which never made it to the Program Files folder. I did encounter the issue you raised and my solution was essentially updating a copy of the 32-bit .db3 file with the 64-bit extension name and moving it into the folder. Despite unrestricting the .db3 file and the folder, any attempt to edit it with Spy while it is in the folder returns a read-only error. Maybe I don’t fully understand the Security controls!
So I copied them out to C:\ProgramsOther and that allowed db3 to be edited and now, I do see the db3 getting updated when a SQLite session is closed. Moreover, I had thought the “Reopen database|sql” functions only applied to the current session but now I see the db3 keeping a history, which it couldn’t do (but didn’t protest) while in Program Files and the recent files are listed. It was keeping something somewhere because there seemed to be an interaction between the 64-bit and 32-bit in sharing the folder path last opened.
So thanks for pointing out the issue you ran into – my situation is improved as a result.