Home Page list
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.
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
- 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.
- 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:
demonstrate that RMNOCASE is available by returning list of surnames
- if not available, an error is returned
SELECT Surname FROM NameTable ORDER BY Surname;