Wednesday, June 20, 2012

SQLite: how to get started with extensions


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

Back To CDOT - My trip and my goals

The Trip

So I've been gone for the month of May, I took advantage of the opportunity of being able to get a month off of my coop and CDOT work. I left with 3 long time friends for a backpacking trip across as many countries as I could and to see as many things as I could.

While I was gone I visited 5 countries around western europe, a brief itenary or the trip went as follows: 

  • London 
    • London -> Paris via train 
  • Paris 
    • Paris -> Lisbon via plane 
    • Lisbon -> Lagos via train 
  • Lagos 
    • Lagos -> Lisbon via train 
  • Lisbon 
    • Lisbon -> Madrid via plane
  • Madrid 
    • Madrid -> Barcelona via train 
  • Barcelona 
    • Barcelona -> Nice via car 
  • Nice
    • Nice -> Paris via train 
    • Paris -> Home 

I cannot say enough good things about this trip, I also cannot say enough good things about staying in hostels. While an entire blog post can be written on how to choose the right hostile at the very least take advantage of sites dedicated to finding and reviewing hostels. This one in particular was our favourite HostelBookers.
Lagos, Portugal

I'm not going to go into detail about the trip, I could go on forever, there are 32gb of pictures and videos to go through so here is one of my favourites to the left.




I've been back for 2 weeks now, and its taken a while to get my barrings back. Getting back to CDOT has put three projects in front of me.

To change topics a bit, some of my personal goals upon coming back have been to refocus on the tasks I have at hand and become better at time management by more strictly budgetting myself. Additionally, alot of pressure is mounting on Grace's exit and I will have some large shoes to fill. Coming back has re ignited my focus, trying to learn everything that Grace knows is a challenge of knowing the right things to ask and recording all the things that happen during our pair programming.

On the topic of projects, we are currently working on a SQLite database adapter for NexJ's open health framework. Since I've come back it has gone in for code review and we are still in talks with NexJ about making a final deliverable. I hope to have this done in the following month, the final deliverable should have a multi-platform build system for our driver in adapter to the adapter code.

Another project is for NexJ and it is something that no one really knows anything about, I have tried my best to research the components that seem relevant. Really though, the final step is for me to compile my prelim research of limitations in the proposed software until more defined specs come in.

Finally my old friend BerrySync or Sync for the Blackberry. While it was amazing that we finished it so quickly last year, it is not a perfect product and it is littered with problems. I should have some time to focus on certain performance and caching issues, hopefully people will stop hating on the app in BlackBerry's market. Additionally I might need to look into Blackberry OS capabilities. I'm budgeting 2 - 3 weeks of full time work to solve these issues if time allows.