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, unifuzz64.dll

unifuzz.dll or unifuzz.dll.bak (for 32-bit apps. Remove .bak extension after d/l, added due some systems’ security)

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).

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. 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.

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 and compiling

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


mooredan

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

mooredan
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….


momakid

momakid
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

9 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.

  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.

      • No, I played with this for a few days with no success and through up my hands. I decided to work on my genealogy rather than tools. But I just looked at the RM website generation tools and decided I would really like to build my own generation tool, but need to be able to access the database. All my environments are 64 bit these days so we really need a 64 bit unifuzz.dll and/or unifuzz.so. So I’m still stuck on this. If we had this, then I would like to start building python3 modules that interact with RM to make it easy for third partys to write add on tools. In an ideal world, RM would create a Python wrapper for RM.

        • I think I have compiled a 64-bit version using gcc. It’s linked in the page above. Untested but it is nearly twice the size and my 32-bit SQLite Expert Personal objects that it is not 32-bit.

          • Tom,
            Just saw this, THANKS! I tried to download the 64bit but the link just displays a garbage page rather then doing a download. you might want to zip the it up so it is forced to download.

  3. Left-click on unifuzz64.dll and right-click > Save as both trigger a download on both Chrome and Edge browsers on Win 10. What browser are you on?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.