SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

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

Posted on by Tom Holden

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

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

  4. Has anyone tried to use unifuzz64.dll with DB.Browser.for.SQLite-3.12.0-win64 (https://sqlitebrowser.org/about/) ?
    In my hands, DB Browser loads the extension without any warnings, but when I load a test database and switch to a table with the RMNOCASE collation sequence, I get the warning that RMNOCASE cannot be found.

    I like the DB Browser app because it can use regular expressions in its data grid filtering.

    I also ran PRAGMA integrity_check and it returned just “ok” no matter which app I used. How do your run it to show a problem?

    Does any of this really matter? Can’t one just re-index the database when first opened in RM and solve any RMNOCASE missing issues?
    Thanks
    Richard Otter

  6. The unifuzz64.dll does work on DB Browser for SQLite (https://sqlitebrowser.org/about/). Download the 64 bit program (it could probably work with the 32 bit program and 32 bit dll but I did not test that) and there are two options. But, first, save the unifuzz64.dll to the install directory of DB Browser for SQLite (C:\Program Files\DB Browser for SQLite\ by default).

    If you want to add the extension and have it enabled for every database you put on there, then go to Edit->Preferences->Extensions->Add Extension (icon) and add the unifuzz64.dll (C:\Program Files\DB Browser for SQLite\unifuzz64.dll); then save. It will give you the warning “Collation needed! Proceed?” one time each time you open a database and you do stuff with the RMNOCASE, but it seems to be a false positive; with the dll file I can sort for example, the PlaceTable by name without clicking yes. So Just click the cross that one time for that dialog box and it should be fine. However, I could be wrong and bad things could still happen to your database; so up to you to do it or not.

    The other way is to only load the extension for one database each time you load up a database you want to load it up for. To do that, open the database and go to Tools->Load Extension.

    I recommend doing the first option if you only use the Browser for RM files.

  7. I’m now successfully using SQLite Studio with the unifuzz dll.
    If you copy the unifuzz.dll file (use 64 bit version if using 64 bit SQLite Studio) to the installed SQLite Studio\ext folder (e.g. C:\Program Files\SQLiteStudio\ext ) , it adds the extension automatically and you simply have to, in SQLite Studio, select Tools, Extension Manager and select unifuzz (or unifuzz64), select “for all databases” and select the tick button to confirm changes.
    Very easy to install.

  9. I last used Dbeaver 15 mos ago to try out its Entity Relationship Diagram feature on a RM database. I don’t recall if it threw an objection about the missing RMNOCASE collation or if I succeeded in adding it. Maybe the ERD doesn’t care. Let us know how it works out for you.

Leave a Reply

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