As most of my readers realise, my blog revolves around my real world issues and findings. One of my most recent tasks has been handling the beast that is, offline storage in PhoneGap 3. I know a lot of readers will be thinking “what’s the issue?” well, before I can jump in to the technical bits of this blog post I’m going to give you a quick background on the project I’m working on at present.

The project I’m working on at moment, is as you guessed a PhoneGap project. It’s a project that is handling huge amounts of JSON data and imagery which is synced between the device and a web application. There are a number of questions that come in to play when you are handling such large amounts of data, for example:

  • What happens if the app crashes?
  • What happens when the temp directories are cleared?
  • What happens if a sync is carried out on a poor connection?

The solution to the problems above, aren’t particularly easy solutions. App crashes are a very real problem which is more noticeable on Android devices, take the camera plugin in PhoneGap for example… When you execute the call to take a picture from your devices camera, your application effectively becomes a background task (something I found out with a hell of a lot of research) so if the device struggles to meet the memory demand of the applications running it will happily forfeit your app in favour of the camera. At this point, the camera will attempt to return to the application and find that your application isn’t active. Therefore, you have lost your data.

As with app crashes, temp directory clear outs are particularly common but this time platforms reversed. Apples iOS is the biggest culprit when it comes to clearing out temp directories, the key problem with this is when you use the local storage plugin – it is written to a temp directory. It is fairly well documented on the internet that since iOS 5.1 Apple have made the local storage directory a temp directory and therefore, your data is not persistent on the device. Imagine storing a days worth of important information in to local storage to find out the next morning everything is lost? Nightmare, huh?

The third question happens to have the easiest solution. With most PhoneGap applications I’ve written they’ve interfaced directly with a REST API that I have also written. In my REST APIs my returned data always contains an object with a key of “success” which will either a) return true, b) return false or c) not return at all. To avoid data loss in this instance you simply have to check that success is true, before eliminating the data from your local storage.

Storage Methods

This brings me on to my next subject, storage methods. As any well-versed PhoneGap developer will know, there’s more than 1 way to skin a cat. PhoneGap brings a couple of storage options to developers in (fairly) easy to implement ways.

Local Storage

Local Storage is the HTML5 API specification, developed for the purpose of giving developers some way of pulling information down to a device for permanent storage.

As I explained above though, it’s not without its flaws! The key once again is the fact that the lovely folk at Apple have made this a temp storage area and therefore data loss risk is huge.

On that note, I’m going to move on to the next and most viable option.

Web SQL

Hurray! SQL! It’s one of, if not the most commonly used technologies in the world and if you don’t know it this article is definitely not for you. I’ve used many implementations of SQL, from MySQL to MSSQL and even Oracles iSQL they all share one thing in common – the language. As with any SQL database, it’s fantastically easy to store information and retrieve information in a structured, easy to maintain format.

At this point, I’m going to shut up and get down and dirty with some code examples. Lets get started by creating our Database:

document.addEventListener('deviceready', onDeviceReady, false);
function onDeviceReady(){
    var db = window.openDatabase('MyDB', '1.0', 'My test DB', 5242880);
}

What the above line does is simple, it writes a database to your device. The first parameter “MyDB” is the name of your database, the second is your version, the third is a description of the database and the fourth is your expected size. In my case I have given the database 5MB of room to play with. Now lets create a table for storing image paths for example:

function onDeviceReady(){
    var db = window.openDatabase('MyDB', '1.0', 'My test DB', 5242880);
    db.transaction(runTransaction, error, success);
}

function runTransaction(t){
    t.executeSql('CREATE TABLE IF NOT EXISTS images (id unique, path)');
}

function error(err){
    console.log('Error creating tables: '+err);
}

function success(){
    console.log('Successfully created tables');
}

So how about storing images? Yep you guessed it, same as any SQL database. For example purposes, I will add it to the runTransaction function from above.

function runTransaction(t){
    t.executeSql('CREATE TABLE IF NOT EXISTS images (id unique, path)');
    t.executeSql("INSERT INTO images (path) VALUES ('your-path-to-image.jpg')");
}

It really is that simple! Here are various other methods for using Web SQL:

function runTransaction(t){
    t.executeSql('CREATE TABLE IF NOT EXISTS images (id unique, path)');
    t.executeSql("INSERT INTO images (path) VALUES ('your-path-to-image.jpg')");
    t.executeSql("DELETE FROM images WHERE path = 'your-path-to-image.jpg'");
    t.executeSql('DROP TABLE IF EXISTS images');
}

Lets select some data!

db.transaction('runSelect', error);

function runSelect(t){
    t.executeSql('SELECT * FROM images', [], success, error);
}

function success(t, results){
    console.log('Number of rows: '+results.rows.length);
    console.log('Last inserted row: '+results.insertId);

    for(var i = 0; i < len; i++){
        console.log('Row: '+i);
        console.log('ID: '+results.rows.item(i).id);
        console.log('Path: '+results.rows.item(i).path);
    }
}

function error(err){
    console.log('There was an error processing the SQL: '+err);
}

In the example above, we run the select transaction – pass the results in to the success callback and console log the information from the returned data set.

This is it for the first part, I will be writing a second part shortly that will explain how to take images using the camera plugin and move them to a permanent location on the device!

As usual, comments are welcome! :)