What is SQLitePCL?

Note: I meant to write this earlier but I’ve been unbelievably busy with work and traveling.  Here it is at long last.

If you use Couchbase Lite then perhaps you have notice a dependency we have called SQLitePCL. You might be wondering what this is, or perhaps you’ve checked out the repo only to be confused. I work with it extensively and so I thought I’d talk a bit about it.

SQLite is one of the most ubiquitous libraries in the world of embedded computing.  A public domain library that has been developed for at least the past 16 years it is a pinnacle of fine software development and is thought to be the second most deployed library in the world (behind libz).  The actual implementation is written in ANSI C and it has been ported to probably every operating system imaginable.  There are hundreds of different projects attempting to bind this implementation to other programming languages.  SQLitePCL is one of those projects, but it attempts a very broad and flexible scope which is nice for Couchbase Lite .NET because we cover a wide variety of operating systems and platforms.

The 0.9.1 release has increased the flexibility even more.  One of the great challenges with SQLite is that it is so intensely customizable that there is no one size fits all for anyone.  On top of that, there are other extensions to SQLite that attempt to bring things like encryption and soon you are no longer maintainable.

The two main packages are SQLitePCL.raw, and SQLitePCL.ugly.  The former consists of solely the C definitions that exist in the library (things like sqlite3_open).  The latter adds in a bit of gruntwork to take care of disposing things and keeping track of internal objects but it’s very close to the metal.  It’s not meant for application use, but for use by library writers (like me) who can fine tune their database access according to their needs.  These two assemblies can be thought of as an interface.  The actual implementation is provided in separate nuget packages, or left entirely up to you.

If you want to put this into other terms, it’s a bit like how C / C++ is compiled.  You have a header file and an implementation file.  The header file makes a promise about what is in the implementation file, and the implementation file contains…well…the implementation.  In a similar fashion SQLitePCL.raw and SQLitePCL.ugly are like header files, but you also need the implementation (actually, there is a “default” implementation inside these two as you will see later).  Couchbase Lite will get an implementation via a plugin file:  System SQLite or SQLCipher (or coming soon, Custom SQLite).  Let’s take a look into how each one will work.

First there is “System SQLite” which means “use the OS provided sqlite3 binary.”  The exception is Windows, which doesn’t provide sqlite3 by default and is unlikely to be present in a system path.  Also, as of Android N system sqlite use is not allowed anymore.  So, basically we need the default implementation from SQLitePCL as well as a custom implementation for Windows.  So this plugin will depend on raw and ugly (as they all do) and in addition SQLitePCL.plugin.sqlite3.  This package contains an implementation which binds to esqlite3.dll instead of sqlite3.dll.  To use it you can call

raw.SetProvider(new SQLite3Provider_esqlite3());

This will set up the library to consume a file called esqlite3.dll, but it is not provided by this for desktop platforms (mobile platforms contain a copy embedded).  This is to provide flexibility in case someone wants to put in their own version of esqlite3.dll that they compiled.  There is another package which contains a precompiled default one to use:  SQLitePCL.native.sqlite3.  In fact there are many of these packages compiled using various compilers and options.  Note that this does not need to be included in the library (i.e. Couchbase Lite) but in the consuming application.

As for SQLCipher, instead there is another plugin to use called SQLitePCL.plugin.sqlcipher which consumes a file called sqlcipher.dll instead of sqlite3.dll.  It has an implementation that can be registered as follows:

raw.SetProvider(new SQLite3Provider_sqlcipher());

Again, in case you want to provide your own native sqlcipher build it is not provided by default for desktop platforms.  To get the desktop platform native binaries you can use SQLitePCL.native.sqlcipher packages.

Do you see a pattern emerging?  SQLitePCL.plugin.* are packages that redirect where to get the sqlite native implementation from, and SQLitePCL.native.* packages provide options for said native implementation.  The exception to this is some mobile platforms.  iOS historically could not load dynamic libraries at runtime (except for ones blessed by Apple) and thus everything needed to be statically compiled into the application.  This is possible by providing some hints to the Xamarin compiler via attributes and build settings, but it means that everything must exist inside of the plugin package (i.e. there is no native package for iOS).  Similarly, not many people are likely to go through the trouble of compiling their own version of SQLCipher for Android, and so it is easier to simply embed the native libraries right into the plugin assembly.

To summarize the way that Couchbase Lite uses this system:

System SQLite
Windows: SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlite3, SQLitePCL.native.sqlite3
All others: SQLitePCL.raw, SQLitePCL.ugly

.NET: SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlcipher, SQLitePCL.native.sqlcipher (three platforms)
All others: SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlcipher

Hope this helps.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s