SQLite: how to get started with extensions

Preface

As part of a series of blogs I need to release detailing my work at CDOT, this will focus on how to build your own c extension to the hugely popular tiny and fast database engine.

As part of my project to build an SQLite adapter for NexJ's open health framework. We've needed to extend the out of the box functionality for SQLite, this ranges from hacking at java code to hacking at our JDBC to hacking SQLite extensions to hacking the SQLite source itself.

So this post will go over what you need to do to write your C extension and more importantly how to load it.

 

The Minimum

 

Let's lay down the groundwork at a minimum you need to refer to SQLite's C api, there you'll see the publically visible utilities you'll use for your extension.

Specifically lets look at creating our own SQL function, this means that it will be usable during queries and other activities. For this you'll need to take a look at: create function, the rest of the green code should be the boilerplate code for loadable extensions where "sqlite_extension_init()" is the entry point that the SQLite api knows about.



This is the most basic loadable extension you can make, now to tackle the problem of loading it:

First compile this c file into a dll for your current system, I suggest you look up how to do this based on what compiler you're using.

Second assuming we have binary.{dll | so | ... }, there are two ways to load your extension inside an SQLite shell you can call .load filename OR using SQL you can run this query select load_extension('filename').

And its that easy! Reference this page for SQLite's loadable extension documentation.

Lastly there is a third way to include your extensions, this will be detailed more when I release a blog post about our build system. However the very basics are to add



during SQLite's initialization, in our version of SQLite this is added after SQLite loads its internal functions in main.c by putting this line in addition to adding the extension location to the existing build system.

An Example

Finally here is example source for our extension that creats the SQL function binary, that works with the existing SQLite function hex(). Binary converts hex string input into blob output.

Take not of the how to handle input errors and other unexpected errors

https://bitbucket.org/gbatumbya/sqlite/src/9cc68159218e/ext/nexj/sqlitext.c#cl-29