For the past few days I've been working on a proof of concept PhoneGap application that demonstrates an example of database synchronization. This is a fairly complex topic and I'm only going to demonstrate one version of this, but I hope the concepts, code, and example application are useful to people hoping to tackle the same problem.

Before we start digging into code, let me explain what the application will demonstrate and the type of synchronization it will use. Our sample application is going to use a built-in database for documentation. Most mobile applications don't really have a lot of documentation. Anything that complex may not make sense on mobile anyway. But for whatever reason you want to use, the application has a large set of documents stored in the database.

This documentation may or may not ship with the application, but we want the application to sync with a remote server in order to get the latest and greatest documentation. Our synchronization "rules" will be simple:

  • If the remote server adds new docs, the app needs to get it.
  • If the remote server updates existing docs, the app needs to update its copy.
  • If the remote server deletes a doc, the app needs to delete its local copy.

In this scenario, we don't have to worry about user generated content and handling updates bidirectionally. That allows us to simplify things quite a bit.

We have two pieces to this proof of concept, the server and the client. I'm going to quickly talk about the server-side code, but as this is mainly a post about PhoneGap, I won't go into deep detail. I built a very simple ColdFusion application using ORM to allow me to add, edit, and delete objects I called "helpdocuments". These documents contained:

  • A title (short string)
  • A body (not so short string)
  • A field representing when the document was last updated
  • A token (built around a UUID, more on why we need this later)
  • A deleted flag (to support 'soft deletes')

You can find the code for this simple application attached to this blog entry. But really - it's just simple CRUD and web forms:

Outside of the service component I'll be using to expose this data remotely, you really don't have to worry about the details and forms here. Let's turn our attention to the client side now.

First off - do not forget that PhoneGap provides a wrapper to WebSQL, an embedded database you can use with JavaScript. You can check out the API docs as well as the video demonstration over on Adobe TV. My application will create a mirror version of the remote database to store a copy of the documentation. This involves:

  • Creating the initial database
  • Creating the empty table
  • Asking the server for data, and later on, asking the server for new data
  • Rendering the data (in our case, I'm just going to list the titles of the help docs)

Since the actual display is rather trivial, I'll show the HTML for the application first:


<!DOCTYPE html>
<html>
<head>
	<title>PhoneGap SyncDB Test</title>
	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
	<script type="text/javascript" src="cordova-1.5.0.js"></script>
	<script type="text/javascript" src="main.js"></script>
</head>
<body onload="init()">

<div id="docs"></div>

</body>
</html>

Yeah, that's it. Obviously a real application would have a bit more meat to it. Let's take a look at main.js, the core logic file for the application. This one is a bit more complex so we're going to take it in chunks.


var db;
//var updateurl = "http://localhost/testingzone/dbsyncexample/serverbackend/service.cfc?method=getupdates&returnformat=json";
var updateurl = "http://www.raymondcamden.com/demos/2012/apr/3/serverbackend/service.cfc?method=getupdates&returnformat=json";

function init() {
	document.addEventListener("deviceready",startup);
}

In the first block, we've got a few variable definitions. The db variable will contain a reference to my database. The updateurl is simply the remote server. (And by the way, that URL isn't going to work after I publish this article.) Our init function listens for PhoneGap's deviceready event. This is PhoneGap's way of saying, "It's Business Time."


function startup() {
	console.log("Starting up...");
	db = window.openDatabase("main","1","Main DB",1000000);
	db.transaction(initDB,dbError,dbReady);
}

Here we have the startup function. It opens the database and begins a transaction. Note that I could have put the logic inside an anonymous function within the transaction call. You're going to see an example of that later. But in my opinion, the initialization routine would be complex enough to warrant moving it into its own function to keep things better organized. (It really isn't that complex, but I still feel like it was the right decision to make.)

Here's the error handler. Again - this would typically be more robust in a production environment:


function dbError(e) {
	console.log("SQL ERROR");
	console.dir(e);
}

Now let's look at initDB:


function initDB(tx) {
	tx.executeSql("create table if not exists docs(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, body TEXT, lastupdated DATE, token TEXT)");
}

You can see my table creation scheme uses columns that match up with my server. The only column missing is the "soft delete" one. My thinking here was that while my server wants to keep deleted documents around so that they can (possibly) be restored later, my mobile application should try to stay as thin as possible.

This is a great time now to talk about the token column. Both my server and client side have it. Why? In order to keep documents in sync from one database server to my mobile applications, I need a way to uniquely identify records. I'm not even sure if the WebSQL client (SQLite) supports inserts with assigned primary keys, but in my mind, the easiest way to handle this was to simply let each "side" handle the primary keys their own way and use the UUID as a way to connect them. This could possibly be done better.

After our table creation script runs, we finally get to dbReady:


function dbReady() {
	console.log("DB initialization done.");
	//begin sync process
	if(navigator.network && navigator.network.connection.type != Connection.NONE) syncDB();
	else displayDocs();
}

As the comment says, this is where we begin our sync process. However, we only do this if the client is actually online. If it isn't, we immediately display our docs. On first run we won't have any, but after that we'll have something. This ensures that offline or online, we're going to support the user reading and interacting with the database.

Ok - now for the complex portion. Let's look at our synchronization function.


function syncDB() {
	$("#docs").html("Refreshing documentation...");

I begin with a simple message letting the user know that important things are going on. Really important things.


	var date = localStorage["lastdate"]?localStorage["lastdate"]:'';
	console.log("Will get items after "+date);

Local storage? I thought we were using a database? We are! But there's no reason we can't use both. While working on this proof of concept I had an interesting problem. I want to minimize the amount of data going back and forth between the application and the server. To do that, my application needs to tell the server what its most recent update was. One way of doing that would be run SQL against itself and get the date on the last updated document. That works fine... except for deletes. If the most recent update on the server was to delete something, then the client's most recent date wouldn't match. As I said above, we aren't keeping those deleted records. So as a compromise, I simply used a value in localStorage. Later on you will see where this gets updated.


	$.get(updateurl, {date:date}, function(resp,code) {
		console.log("back from getting updates with "+resp.length + " items to process.");

Here's our server call. It basically pings the remote code and passes in a date. The server asks for all records modified since that date. The result is an array of objects.


resp.forEach(function(ob) {
	db.transaction(function(ctx) {
		ctx.executeSql("select id from docs where token = ?", [ob.token], function(tx,checkres) {
			if(checkres.rows.length) {
				console.log("possible update/delete");
				if(!ob.deleted) {
					console.log("updating "+ob.title+ " "+ob.lastupdated);
					tx.executeSql("update docs set title=?,body=?,lastupdated=? where token=?", [ob.title,ob.body,ob.lastupdated,ob.token]);
				} else {
					console.log("deleting "+ob.title+ " "+ob.lastupdated);
					tx.executeSql("delete from docs where token = ?", [ob.token]);
				}
			} else {
				//only insert if not deleted
				console.log("possible insert");
				if(!ob.deleted) {
					console.log("inserting "+ob.title+ " "+ob.lastupdated);
					tx.executeSql("insert into docs(title,body,lastupdated,token) values(?,?,?,?)", [ob.title,ob.body,ob.lastupdated,ob.token]);
				}
			}

		});
	});
});

Holy smokes that's a lot! Let's take it piece by piece. We begin with a simple forEach iterator over the array. For each item, we need to see if already exists. That's done in the SQL that checks for the token.

If a record exists, then we either need to update it, or delete it. Remember that our server keeps deleted documents and simply flags them. You can see my check for that flag here:


if(!ob.deleted)

If the object wasn't deleted, we perform an update. If it was deleted, we perform a delete.

On the other side of the conditional, we only need to do an insert if the object isn't a delete.

Finally - we update our localStorage flag and call displayDocs too. Here's the display function - and again - this was built to be simple.


function displayDocs() {
    db.transaction(function(tx) {
        tx.executeSql("select title from docs order by title asc", [], function(tx, results) {
            var s = "<h2>Docs</h2>";
            for(var i=0; i<results.rows.length; i++) {
                s += results.rows.item(i).title + "<br/>";
            }
            $("#docs").html(s);
        });
    });
}

The end result is an application that updates its data when online and continues to work fine offline. You can download the entire code base below. Any questions?

Download attached file.