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.
3995
is
$39.95
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?
SELECT
date(timestamp),
CASE
WHEN amount = 0 THEN
'0.00'
WHEN amount % 10 = 0 THEN
CAST (amount / 100.0 AS TEXT) || '0'
ELSE
CAST (amount / 100.0 AS TEXT)
END,
description
FROM ...
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.
Implement function
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:
static void
convert
(
sqlite3_context* context,
int argc,
sqlite3_value** argv
)
{
...
That’s all good. You get the argument passed with one of the sqlite3_value() functions:
num = sqlite3_value_int(argv[0]);
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() function:
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?
Loading extension
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 void (*)(void)?
void
sqlite3_extension_init
()
{
sqlite3_create_function(db, "money", ...);
}
That’s not going to cut it. Where do I get db from?
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.
int
sqlite3_extension_init
(
sqlite3* db
)
{
sqlite3_create_function(db, "money", ...);
return SQLITE_OK;
}
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:
$ sqlite3
sqlite> .load m<TAB>
sqlite> .load money.so
Error: money.so: cannot open shared object file: No such file or directory
sqlite>
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
sqlite>
There. Now we can try it:
sqlite> SELECT money(3995);
39.95
sqlite>
Nice!
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 .quit sqlite3, restart, and .load again.
AfC
Postscript
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:
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
int
sqlite3_extension_init
(
sqlite3* db,
const char** err,
const sqlite3_api_routines* api
)
{
SQLITE_EXTENSION_INIT2(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.
@afcowie
+Andrew Cowie
0x5CB48AEA
/andrew
