It’s fairly common in financial systems to represent monetary amounts as cents rather than trying to represent dollars and cents with floating point [and all the rounding errors and inaccuracy that comes from using base 2 floating point to represent concrete base 10 numbers]. Call it fixed point if you like. Anyway, in an SQLite database I’m working with, the schema represents amounts as integer numbers of cents.
I found myself constantly having to convert a number of cents to a number with two decimal places. Would you believe I came up with this?
WHEN amount = 0 THEN
WHEN amount % 10 = 0 THEN
CAST (amount / 100.0 AS TEXT) || '0'
CAST (amount / 100.0 AS TEXT)
I mean, cool that you can do that with SQL, but yikes. What I really wanted was a SQL function that would just do the conversion from cents to money, all nicely formatted.
SELECT date(timestamp), money(amount), description FROM ...
SQLite has a very straight-forward extension mechanism. Admittedly I already had a grip on the basics of the SQLite C API because I’d written a hyper-thin Java wrapper for it, but it writing an extension in C to define a new scalar SQL function was pretty simple.
You create a funciton with a call to
sqlite3_create_function() and end up implementing it by specifying poitners to functions with the signature
void (*xFunc)(sqlite3_context* ,int , sqlite3_value**). Alright, should be easy:
That’s all good. You get the argument passed with one of the
num = sqlite3_value_int(argv);
do something with it, say:
dollars = num / 100;
pennies = num % 100;
snprintf(str, 11, "%7d.%02d", dollars, pennies);
and send it back with a
sqlite3_result_text(context, str, -1, SQLITE_TRANSIENT);
Easy enough API to use. I should add, the main SQLite C API is lovely — you get a database connection, you prepare a statement, then you step through the results. Of course, that’s what you do with any database engine in any language, but doing it from C against SQLite is really clean.
To register my function I just have to name it, say how many parameters it has, and pass a function pointer to it, like this:
sqlite3_create_function(db, "money", 1, ..., convert, NULL, NULL);
Some details omitted there, but you get the idea. Simple enough. Except for one thing. Where do you make that call from? Some sort of entry point, presumably, but what? And meanwhile, where do I get that
sqlite3*, the database connection, from?
Hunting around, there’s a “load extension” function. Ok, that’s promising, and it says that the default entry point that will be looked for is “
sqlite3_extension_init” but it’s not entirely obvious from the documentation for
sqlite3_load_extension() what (if anything) the signature for the entry point is. The usual
sqlite3_create_function(db, "money", ...);
That’s not going to cut it. Where do I get
Well, I put two and two together and guessed that actually the entry point function is invoked with a pointer to the open database connection on the call stack, because, well, you need one. And sure enough it seems to work. Eventually I found the actual signature described on the reference page for
sqlite3_auto_extension() — one of the few times it was really hard to find something.
sqlite3_create_function(db, "money", ...);
There we go. Compile it up into a nice little shared library and you’re on your way.
$ gcc $CFLAGS -shared -lsqlite -o money.so money.c
Last bit, get it loaded. Programmatically you’d use that load extension function, but if you’re working from SQLite’s command line interface, you use the
.load instruction. The catch here is that although the
sqlite3 interface does filename completion, this doesn’t work:
sqlite> .load m<TAB>
sqlite> .load money.so
Error: money.so: cannot open shared object file: No such file or directory
Bah Humbug. It seems to be looking on the
LD_LIBRARY_PATH. Ok, fair enough, but a bit annoying that it’d complete a file it can’t load. Whatever:
sqlite> .load ./money.so
There. Now we can try it:
sqlite> SELECT money(3995);
That’s obviously a lot of work just to coerce two decimal places, but it also means in future I can do custom things like currency symbols, thousands separators, etc. Cool. I’m not about to write actual reports in SQL, but this is really handy for debugging and exploring the schema I’m working with.
Incidentally, there’s no
.unload so if you change your sources and recompile you’ve got to
sqlite3, restart, and
The extension API is actually more powerful than this — you can override the inherited behaviour of SQLite functions themselves. You do that by including
sqlite3ext.h and placing the following macros:
const char** err,
const sqlite3_api_routines* api
which is more correct than what I was able to get away with above. Hooray for the lack of type safety of dynamic symbols. There’s a wiki page that describes the correct usage further.