Combining SpatiaLite and PhoneGap
After reading Roman’s post about SpatiaLite, didn’t you get sweaty palms just thinking about how awesome it would be to combine the database with your mobile device, so you could carry it around with you? Despair no longer, because we at Disy have been working hard to make exactly that happen.
We also went the extra mile to package it as a PhoneGap plugin, so you can install it into your app with a single command! If this sounds too good to be true, then read on and find out how we did it.
Why not Use the Vendor SQLite?
Every important mobile OS already provides some flavor of simple SQL database à la SQLite to the apps running on a device, nice proprietary APIs and regular vendor updates inclusive. So why would anyone want to ship their own version of a database with their app?
The answer is that there are some types of data these stock databases are just not equipped to deal with. Especially if you rely heavily on spatial data, you want the database to speak the same language as you do. Just have a look at all of these sweet and delicious SQL functions supported by SpatiaLite. It is like walking through a candy shop trying not to eat everything right away.
In addition, once you have the source code you can do things a normal SQLite database just isn’t able to, for example use a fully encrypted database. Take a look inside sqlite3.h:
/*
** Specify the key for an encrypted database. This routine should be
** called right after sqlite3_open().
**
** The code to implement this API is not available in the public
** release of SQLite.
*/
SQLITE_API int SQLITE_STDCALL sqlite3_key(
sqlite3 *db, /* Database to be rekeyed */
const void *pKey, int nKey /* The key */
);
PhoneGap and SpatiaLite
‘So, what’s the big deal?’ you might wonder.
You know how to use extensions with SQLite, just execute a statement similar to SELECT load_extension('libspatialite-2.dll'); on your database and you are good to go.
Indeed, in a nicely set up desktop or server environment this works just fine.
The headaches start once you want to use this technique on a mobile device or any sandboxed environment.
First of all, once the statement is executed, the database will ask the operating system to search for the lib in your file system, load it and execute some code from it. If you think this sounds like it might be a security issue, you are absolutely right. Once you allow applications to execute dynamically loaded code, sandboxing becomes a nightmare to handle. I think this might be the reason why (most) mobile device operating systems prohibit apps from doing this very thing.
But what fun would software development be without some layers and abstractions? So, to make the problem a litte more interesting, we want to use an abstraction over an app’s native code by using PhoneGap. We can use this to develop our app in a sandboxed environment with JavaScript once, so that PhoneGap is able to run it on a multitude of different devices. Of course, we want to use a device’s capabilities like the camera or filesystem at some point. That is why PhoneGap provides a lot of plugins that can be called from the JavaScript part of the app, once added to the project.
Since we are using PhoneGap to develop our next version of Cadenza Mobile, we need a PhoneGap plugin that is able to connect to a SpatiaLite database from our app.
The SpatiaLite Plugin for PhoneGap
Why not Use an Existing Plugin?
As it turns out, there are already many PhoneGap plugins that are able to connect to a SQLite database. So a critical reader might be inclined to think that we were trying to reinvent the wheel by writing yet another plugin. However, these plugins generally do not support the SpatiaLite database extension because of the security issues mentioned earlier. And even those plugins that do support SpatiaLite are either only available for a single platform like Android or lack important features, such as transaction support.
So, we forked the most mature SQLite plugin available to build our own plugin. This already provided most of the necessary native glue code as well as a solid core for the JavaScript part of the plugin. We did, however, have to change a few things to get the plugin to work as intended, as you’ll see…
Installation
You can find the plugin’s source code on GitHub.
As of now, the plugin is not yet registered in the public plugin register, so you have to use the GitHub URL to install it.
To install the plugin, use the cordova command line tool inside your project folder:
cordova plugin add https://github.com/DisyInformationssysteme/Cordova-spatialite-storage
To remove the plugin, use the cordova command line tool again:
cordova plugin rm cordova-spatialite-storage
Usage
Once you’ve added the plugin to your project, you should be able to use the database like this:
var query = '
SELECT AsGeoJSON(myGeometryColumn) geometryJson
FROM myDbTable LIMIT ?';
var args = [20]; // only get the first 20 results
var querySuccess = function(results) {
// yay, sql results here...
}
var queryError = function(error) { // oh no... }
// a file URI to your database,
// e.g. "file:///storage/emulated/0/database.db"
var spatialiteURI = ...;
var db = window.sqlitePlugin.openDatabase({name: spatialiteURI});
db.transaction(function(tx) {
tx.executeSql(query, args, querySuccess, queryError);
})
You can also have a look at the documentation of the plugin we forked from!
Changes we made to the original plugin
Necessary iOS Changes
The native part of the original iOS plugin works by providing and compiling the full SQLite source code and accessing the native SQLite functions from the Objective-C code. This is a very compact and clean way to do it, because the complete source code is provided in a single header and source file. SpatiaLite, on the other hand, comes with over 300 header files. Also, we had already precompiled the SpatiaLite library so we wouldn’t have to deal with all those source files in the plugin. Or so we thought.
One problem was that the config file didn’t have a concept of “header directories”, so every header file had to be included with its full path. Just look at this. We had to create this whole part of the config file via script. Bad PhoneGap, bad.
Once everything was compiling, we had to init the SpatiaLite part of the database (the whole reason we were doing all this).
Many online resources will tell you just to call spatialite_init(0) and be done with it.
This will not work with newer versions of SpatiaLite!
And it won’t even give you a clue as to why that is.
In fact, the function was replaced by the newer spatialite_init_ex(db, cache, 1);, but without consulting the API, you’d never know.
But wait, there is more, the fun keeps on building…
As you can see, the newer function also accepts a few parameters, such as a void* ptr to some chunk of memory that is now your responsibility to allocate and free again:
void* cache = spatialite_alloc_connection ();
// init spatialite
// ...
// wait till the database is closed
spatialite_cleanup_ex(cache);
Necessary Android Changes
Interestingly, the Android native version works a bit differently than the iOS one. We compiled it using a different setup, so that the packed binary itself automagically initializes the SpatiaLite part when opening a database. So, the good news is that the Java part of our Android plugin did not have to jump through any hoops to initialize the database. The bad news is that all of the native glue code was missing entirely.
The original plugin used the native Android SQL APIs to speak to the SQLite database. The problem with this is that these Java classes do not know what return types to expect from the SpatiaLite part of the database. They throw Exceptions all over the place from incompatible types and parse errors because they don’t know the SpatiaLite SQL functions.
To solve this problem we took the jsqlite glue code from another PhoneGap plugin and incorporated it into our plugin. This worked great, but required us to almost completely rewrite the Android part of our plugin. The native Android database API features many developer-friendly functions, for example to start a transaction. The imported jsqlite code directly calls the compiled database code via JNI, which means we can only call the functions present in the SQLite C++ headers.
‘Why is this a problem?’ you might ask.
If you look closely, you will see that the C++ headers do not mention such things as transactions or database cursors, like the Android API does.
The actual way to start a transaction is to execute a special BEGIN sql statement.
Nothing new here, the Android API does pretty much the same thing, however it is rather tedious to reimplement it all.
Next Steps
As of now, only Android and iOS are supported by our plugin. The good news is that support for other platforms, such as Windows for Mobile, should not be too hard to add, since the original plugin already supports them. So if anyone wants to dive in: we glady accept pull requests. :)
The title image is called MIND THE GAP (surprise) and was published by CGP Grey under CC-BY 2.0.
Share via
