RMNOCASE – faking it in SQLite Expert, command-line shell et al #rmnocase

SQLite Expert now takes the lead as the most compatible with the RootsMagic RMNOCASE collation, thanks to the C extension unifuzz.dll authored or revised by Jean-Christophe Deschamps. Unifuzz can be used with other SQLite managers that support extensions, including the command-line shell sqlite3.exe but not, regrettably, SQLiteSpy.

This is not simply a renamed equivalent of the SQLite NOCASE collation (see RMNOCASE – faking it in SQLiteSpy); rather, it is a very comprehensive compilation of the unicode character set. Against a RootsMagic database comprising 8091 different Unicode characters in Surnames, PRAGMA Integrity_Check returns 187 missing rowids from indexes idxSurname and idxSurnameGiven with SQLite Expert using this substitute collation. SQLiteSpy returns 16042 missing rowids from the same two indexes including 9 from idxSourceTemplateName with its fake collation. RootsMagic itself reports two missing rowids from each of the two Person Name indexes so there is something wrong even with the genuine RMNOCASE collation.

Download unifuzz.dll

unifuzz.dll or unifuzz.dll.bak (remove .bak extension after d/l, added due some systems’ security)
Download and save to the same folder where you have the executable file for SQLite Expert or the command-line shell (or other SQLite manager that supports C extensions).

Using unifuzz.dll with SQLite Expert

Version 3.5

  1. Under the menu item Tools > Options > Show/Hide Features, check the box labelled “Load/Unload Extensions” to reveal these selections in the File menu. The options is saved between sessions.
  2. To load the extension, simply select File > Load Extension and use the resulting “Select extension file” window to browse to, select and open unifuzz.dll. OK the default value “sqlite_extension_init” in the Entry Point window. That’s it! You now have a very (if not perfectly) compatible RMNOCASE collation associated with the database.

Version 4 and 5

The Show/Hide Features option is missing from this version as is File > Load Extension. The only choice is to right-click on the database name in the sidebar to invoke the drop-down menu which includes Load Extension and proceed as in step 2 for ver 3.5. Because unifuzz.dll is a 32-bit extension, it will work with only the 32-bit version of SQLite Expert. The Unload Extension function is inoperable and there is no way to tell if an extension has been loaded.

There is a functional difference between the free SQLite Expert Personal edition and the $59 Professional edition. Personal requires that you load the extension for each database, each time you open the database while Pro can be set to automatically load an extension. The free SQLiteSpy can be set to auto load an extension but can only have one main database open at a time while Expert can have multiple databases open.

Using unifuzz.dll with SQLite3.exe

This C extension can be used with the command line shell, which opens up some potentially powerful possibilities with Windows command scripts (batch files). To load the extension, from the SQLite prompt enter the dot command:

sqlite> .load "unifuzz.dll"

If SQLite3.exe and unifuzz.dll are not in the same directory, then the full path to unifuzz.dll must be provided between the quotes.

The shell can be launched to execute a sql script to both load this “RMNOCASE” extension and run sql commands – here’s a simple example. At the Windows command prompt, open on the folder where the database file, sqlite3 and unifuzz are all located, enter the following or open a batch command file with the same content:

sqlite3 "RootsMagic database filename with .rmgc extension" < "sql script filename"

Example SQL script file to be called from the command line:

.LOAD "unifuzz.dll"
 demonstrate that RMNOCASE is available by returning list of surnames
 - if not available, an error is returned
SELECT Surname FROM NameTable ORDER BY Surname;

Unifuzz.c Source


Discussions & comments from Wikispaces site


unifuzz run time loadable extension for OS’s other than Windows

05 September 2017 00:54:38

Hooray! This has been on my ToDo list for a long time and I finally worked on this this weekend. As mentioned above, being able to run queries from the command line opens up a host of powerful possibilities. Unfortunately, the provided DLL above only works on Windows. I run RootsMagic on macOS, but macOS being Unix-like based, the sqlite3 command line tool can be compiled and used. If you try to run a query without accommodating for RMNOCASE, you get the dreaded “Error: no such collation sequence: RMNOCASE” message. Long story short, this is now working on my MacBook Pro. The details can be found here: https://github.com/mooredan/unifuzz . This should work for a Linux system as well. If someone wants a binary, let me know if you don’t want to (or can’t) roll your own.

I hope to integrate this with PHP and GD for creation of some custom web-based charts….


20 September 2017 22:14:45

I installed SQLite Expert Personal (5.2 (x64).
I opened my RootsMagic database.
I entered the following in the SQL window:
select surname
from nametable;
I got no such collation sequence: RMNOCASE

I did a right click on my database
I selected Load Extension
I selected the the File Name (C:Program FilesSQLite ExpertPersonal 5unifuzz.dll)
I entered sqlite_extension_init for the Entry Point
I left auto unchecked
I clicked OK
I got a popup of The specified module could not be found

3 Replies to “RMNOCASE – faking it in SQLite Expert, command-line shell et al #rmnocase

  1. I am working on incorporating unifuzz into a XOJO application that should be able to run on both Windows and Mac. If successful this should allow incorporation of the queries stored here into a menu structure that could be run without a separate SQLite tool.

    • That would be great, Keith. I integrated numerous scripts in my RMtrix app in Visual C# but resurrecting my novice skills seems daunting. Keep us posted!

  2. “Because unifuzz.dll is a 32-bit extension, it will work with only the 32-bit version of SQLite Expert.”

    Has anyone build a 64-bit extension for unifuzz.dll? I have environments where I can’t use a 32-bit extension and really would like to have a 64-bit extension. Please.

Leave a Reply