Xojo SQLite databases: Enabling ICU for full Unicode support and adding custom functions

Unicode Support (ICU)

Xojo's default Database class REALSQLDatabase (or its replacement SQLiteDatabase), which is based on sqlite, does not support international (non-english) characters. For instance, if you try to use the SQL function upper on a french phrase such as Tête-à-Tête, you will get TêTE-à-TêTE, i.e. only the plain ASCII chars (a-z) will get uppercased but not the special french chars. This missing unicode support also affects sorting and related comparison functions.

The usual solution to this is to compile the sqlite3 library with ICU support and then include this lib in your program. This is not a viable option with Xojo because you'd also have to write your own Database plugin to make the sqlite lib usable in Xojo (while the sqlite lib is open source, Xojo's plugin is not, or this would be less of an issue). Furtunately, though, Xojo's plugin allows dynamic loading of extensions (like plugins) into the sqlite engine.

Still, you need to have a readily built ICU lib for sqlite before you can load it. And unfortunately, there is none publically available as far as I could see, at least not for OS X. Therefore, I'm making my own built version available, along with the source code. Mind you, only for OS X, though, as I do not have the time to make and test versions for Windows and Linux as well. I believe, however, that you can more easily find .dll and .so files of the ICU-sqlite lib on the web and use them with these instructions.

Custom SQL Functions

I recently also started using sqlite's FTS (Fast Text Search) functionality. At the end of the FTS page, examples are shown on how to implement a smart ranking feature when searching for text. To implement it, a custom ranking function needs to be added to the SQL engine. Again, this is usually done by writing C code, and then load that as an extension. But since my code that controls what needs ranking is written in Xojo, I'd need to have this ranking function written in Xojo as well, in order to have access to the other data I've collected in my program relevant to performing the ranking.

Adding a custom functions to sqlite is fairly easy: Invoke the SQL command "SELECT load_extension('libfile_path', 'init_function_name')" is all it takes.

It would be great if we could simply pass the path to own code that's been compiled by Xojo, along with a custom init function we've prepared for this. Unfortunately, that doesn't work because all methods in Xojo are local (non-exported), making it impossible for sqlite to find any functions in our code.

Extension Stub

We need to use a small helper code file that provides the init function we need to implement. Here's the C code (I've removed some of the non-essential parts):

    #include <sqlite3ext.h>
    SQLITE_EXTENSION_INIT1

    typedef __cdecl int (*callback_func) (const void *pApi, void *db);

    static callback_func callback;

    void SetCallback(void *p) {
        callback = p;
    }

    int sqlite3_extension_init (
      sqlite3 *db,
      char **pzErrMsg,
      const sqlite3_api_routines *pApi)
    {
        SQLITE_EXTENSION_INIT2(pApi);
        if (callback) {
            callback (pApi, db);
        }
        return SQLITE_OK;
    }

The first relevant part here is the SetCallback function. This is a function we'll call from Xojo before loading this extension into the sqlite engine. We pass it the address of a Xojo function in which we'll handle the installation of our custom functions. The SetCallback function stores the address of our Xojo function for later use. Then, once we load this extension using the "SELECT load_extension" command, sqlite3_extension_init will get called. Its code then invokes our previously set Xojo function through the stored callback function pointer. That's basically all it does.

(This code should be portable for Windows and Linux as well - the actual source code contains some extra instructions for Windows DLLs).

Once the extension gets loaded and our callback function gets called, we perform the registration of the custom functions. How that's done is documented in the SQLite docs.

Using the sqlite API

There is another challenge, though. Our custom function certainly wants to be able to receive parameters, return values and maybe even perform database access operations on its own. For this, sqlite provides a large set of functions through its API. The problem is: While it's fairly easy to invoke them from C code, it's rather tedious to do in Xojo, because they're accessed through an array of function pointers (note the pApi parameter the sqlite3_extension_init gets passed - that's where all those pointers are located). In C, macros take care of making them convenient to call, but in Xojo it means a bit more work for us.

Basically, to call a function, e.g. sqlite3_result_int, we must first find out which place its function pointer occupies in said array. And that can only be found out by looking at the header file (sqlite3ext.h) and counting through the positions. Turns out that sqlite3_result_int is at index 82, for instance.

I have then located some of the other functions to access passed parameters and to set returned results and make proxy functions for them to make calling them easier. It should also make it clear how to add more API functions if needed.

Download it all

To sum this all up, I'm now presenting to you a ready-made Xojo project along with compiled libs for both extensions and their source code with Xcode project files. The project shows how to load both extensions and then tests the results, proving that both the unicode extension and the added custom functions work. Note, however, that it only works on OS X out of the box. I've tested it with both Real Studio 2012r2.1 and Xojo 2015r2.2.

The proejct uses the older REALSQLDatabase class. If you replace it with the newer SQLiteDatabase class, you need to also add this line each time before you try to install an exension, or you'll get an "Not authorized" error:

db.LoadExtensions = true

I believe the extensions can also be built for Windows and Linux without too much effort if you follow the sqlite instructions under the topic "Compiling A Loadable Extension". If you succeed in this and like to share your results, I'm happy to include them in my downloadable file or link to yours.

As usual, my demo code is free of restrictions. Use it as you like.

Download here.

Alternative by MonkeyBread Software

MonkeyBread Software has recently added ICU support to their SQLite plugin as well.