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

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.

 

CDOT

 

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.

BerrySync: Fetcher.java, now with more github

after a little bit of work ironing out the BerrySync app. Ive shlapped together my synchronization service library. I've dubbed it fetcher.java, now on github.


My Design

I created the library to be as modular as I could, I'm not happy with the results yet but this is the first step after all.

The Fetcher class is a singleton and in order to be used it requires a service to be set, this service must be of the iFetcher interface. That allows me to hotswap or customize the service that I'm using.

On top of that each service can have a different structure or set of needs, so attached to the iFetcher interface are setters for the seperate modules:

• Encryption module
• Json decoding module
• Network connector module

This Release

 I've implemented the bare minimum required steps for FireFox's Sync service. My chosen platform was BlackBerry(obviously), and as a result the modules contain platform specific code. Meaning that different modules will need to be created to port this service to different platforms.


What Can I Do?

Fetcher.getInstance().setLogin("asdf","asdf","asdf").pull();
< This screenshot is the alpha visual representation of data pulled by Fetcher >
1,  2.

BerrySync: Untested code is broken code

TLDR - What did I learn?

In the irc channels I lurk, a phrase reigns true. "Untested code is broken code". I overlooked this bug because my tests didn't keep up with the code. Don't be lazy, you will not remember you took short cuts a month ago.



BerrySync's Show Stopper
 
So I'm very quick to point fingers, So naturally when our program stops decrypting 75% of the I went straight to http://bit.ly/r9vKFS. You should note RIM is abiding by this document http://bit.ly/ey4Fg and if you read further down you'll note that this algorithm padding standard does not mention AES encryption which is tragic because its the only symmetric key unformatter engine that RIM supplies, meaning you'll need it for AES encryption! For example BerrySync's crypto module sans error control:

AESKey key = new AESKey(keyData);
InitializationVector iv = new InitializationVector(ivData);
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
BlockEncryptor encryptor = new BlockEncryptor(
     new PKCS5FormatterEngine(
          new AESCBCEncryptorEngine(key, iv)), outputStream);
encryptor.write(plainText);
encryptor.close();
outputStream.close();
return outputStream.toByteArray(); 

I And you should check out http://bit.ly/rplM0s. Here you'll see that Mozilla is abiding to http://bit.ly/piPqcJ. The 2.1 standard includes AES encryption in the spec, hold on a minute. RIM must be doing something totally off the wall here. All of that combined with the knowledge that using third party and unlicensed encryption and getting your app into AppWorld would involve many headaches and much money ala ECCN.



Wrong

As with most things my finger pointing was wrong, the bug actually lived on this line

data = data.replace('-', '\0'); 

Where my lazy attempt at removing characters from a string totally backfired. The interesting thing is that during rare cases, my sync account and about 25% of others tested it didn't actually make a difference and everything worked as intended. Where the other 75% of accounts saw a Bad Padding Exception during decryption.