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.
unifuzz64.dll for 64-bit apps, added 2020-03-05, tested with SQLite Expert Personal 64bit 5.3.5
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).
- 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.
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. Use unifuzz64.dll with the 64-bit version. 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.
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 is readily compiled using the gcc compiler, mine came bundled in Dev-C++ Integrated Development Environment. I couldn’t figure out how to tell the IDE what I wanted to do but Jean-Christophe provided the command line for the gcc compiler at line 541 of his source code so I followed that. As the source is written, you want to extract to a folder the sqlite3.h and sqlite3ext.h files from the zip or tar file named sqlite-amalgamation-versionnumber on the SQLite Download Page under the heading Source Code. In a sub-folder below those .h files, place the unifuzz.c file. Then in the command line interface, issue:
pathtogcc\gcc -O2 -lm -shared unifuzz.c -o unifuzz64.dll
The DLL file will be produced in the same folder as the unifuzz.c file.
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:
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 Files\SQLite ExpertPersonal 5\unifuzz.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