SQLite Tools for RootsMagic

Intro

I first reported on the #sqlean extensions for SQLite3 in my 2022 post In Praise of SQLite. What I did not pick up then that I have just now discovered is that sqlean supports the creation of user-defined or custom functions using SQLite syntax. Maybe it did not at that time or my fluency in the language of code development is just too poor to have recognised it. Regardless, I have now been enthusiastically exploring that capability, hitting lots of bumps but thrilled that, at last, I’ve found a way to take complex SQL and package it into a function that greatly simplifies and eases the development of scripts that interact with the RootsMagic database structure. And there is an acceptable penalty in performance that only becomes noticeable on large datasets or slow systems.  

Moreover, a custom function can be easily created in #SQLiteSpy by copying a block of SQLite code from a working script into Notepad++ for minor changes and, thence, into the SQLiteSpy editor. To illustrate, I will focus on the conversion of the script in the post Date Decoder to the function rmDateDecode().

Performance

Custom functions may seem a simple matter for those with skills in a language that can interact with SQLite. For those of us closer to the luddite end of the spectrum, they seem out of reach. Some years ago when I saw that SQLite Studio supported custom SQLite functions, I dove in only to be greatly disappointed by its performance; that was a dead end. So I was delighted that there was no obvious penalty in moving a complex string parsing and conditional string building code from a script into a sqlean custom function, as reported in the table below.    

rmDateDecode() tested on the same database having 8368 rows in the EventTable, 3940 of which had a text or valid date:

Installing the extension

This message in the SQLiteSpy extensions dialog gave me pause (and still makes me a little unsure): 

While I have experienced many bumps building the rmDateDecode() function, I have tested many of the functions included in sqlean and they all work. And, once working, my custom function has been reliable.

Download Windows 64-bit package and extract either define.dll (and other extensions, if desired) or the bundle of extensions included in sqlean.dll to the same folder as your executable for SQLiteSpy.exe Win64-bit edition.

The SQLiteSpy extensions dialog is accessed via the menu Options > Options > Extensions.

Type in the name of the extension file and, if not in the same folder, the relative or full path. Mine is in a sub-folder named ‘sqlean’. The extension will load the next time SQLiteSpy opens or connects to a database. It manifests itself as an empty table named ‘sqlean_define’ when there is no connected database.

With the extension successfully loaded, you’re ready to begin using functions built into sqlean. Try some examples from the Main Set. For the rest of this post, our attention will be on the define set.

Building a custom function per the instructions

I was puzzled at the outset that the examples for scalar functions did not seem relevant to what I wanted for decoding values from the Date column of RM’s EventTable. However, with a test RM database connected, I blundered ahead at making a simple function that would extract the first yyyymmdd string from [Date].

SELECT define(‘rmDateExtract’, ‘SUBSTR(:rmDate, 4, 8)’);

Executing that statement added the following record to the sqlean_define table:

…and did whatever is necessary in memory to make the function accessible to the SQLite engine.

‘:rmDate’ is what I chose as the name of the one variable to be passed to the function. Then the SQLite statement:

 SELECT rmDateExtract([Date]) FROM EventTable;

…sends the value of the Date field from each row in the table to the function which returns the substring.

That’s certainly more readable than what is stored:

…but we want to do better. And here’s where the fun and frustration began.

Note that the define() syntax requires the SQL code to be a string within single quotes. However, the DateDecoder.sql script has string values to compare, to be concatenated, or empty strings to be issued. I ran into this issue using a Visual Studio C# years ago to build my old #rmtrix utility. Conversion of working, regular code of this sort into a string that would be correctly presented to the SQLite engine was a trial and error headache. A regular line like this:

Coalesce( Nullif( '-' || Substr( [Date] , 10 , 2 ) , '-00' ) , '' )

has to be converted to this:

Coalesce( Nullif( '||'''-'''||' || Substr( :rmDate , 10 , 2 ) , '||'''-00'''||' ) , '''' ) 

I got it almost right using Notepad++ the first time but “almost” is a fail. Ultimately, after many fails, I narrowed it down to the empty string '' (2 single quotes). While '''-''' is needed to pass '-' to SQLite, '''''' does not pass on ''; the sequence '''' does! (those are all single quotes)

An easier, more reliable way to build a custom function

Here was the Eureka! Moment. What ended up in the sqlite_define.[body] cell was the original regular code. Moreover, that is a permanent table unless explicitly DROPped from the database. And whatever sqlean put into memory for a custom function is gone once SQLiteSpy is closed, maybe even when the database is disconnected. That implies that sqlean reads the table on opening or when a custom function is called and does its magic in memory. So what if I inserted an empty record into the table and edited it with SQLiteSpy directly to add a function? I copied the block of regular SQL into the [body] cell and filled the other two cells appropriately. Closed and reopened SQLiteSpy on the database and tried the function. Voila!

This is so much easier and is what one can do in the SQLiteStudio interface; it’s a pity that it is such a slug. Here’s a shot of the sqlite_define table contents with three user-defined functions:

A Caution

One thing I am experiencing which remains concerning is that on closing a connection or the app, I frequently, if not always, get the following error message since diving deep into custom functions with sqlean: 

I also get a “database is locked” message at times when adding or editing a function in the sqlean_table. The two may be related but I do not know the cause. Hopefully, this is not a sign of possible corruption per the warning from the SQLiteSpy Extensions dialog window.

And there are sqlean instructions about memory:

define_free()
Frees up occupied resources (compiled statements cache). Should always be called before disconnecting. E.g. select define_free();

I don’t know whether closing SQliteSpy releases the cache. If not, we’ll have to see if there is an erosion of available memory over time.

Download

This is the block of SQL for pasting into the [body] cell for the rmDateDecode scalar function in the sqlean_define table.