A database utility class for PhoneGap

I've done a few projects now that make use of PhoneGap's database support. Like most things in PhoneGap, it just plain works. But I've encountered a few things I thought could be done a bit easier, so I've built a simple utility class for my future projects. I thought I'd share it with folks and see if it would be useful for others.

My utility library has the following features:

executeBatch: Given a file path (via URL), you can have PhoneGap read in an XML file that contains a set of SQL commands. So for example:

myDbController.executeBatch("sql/createtables.xml",successHandler,errHandler);

Where your XML would look like so:

<sql> <statement> create table foo if not exists foo(....) </statement> <statement> create table moo if not exists foo(....) </statement> </sql>

This was done to support the fact that unlike Adobe AIR, the SQLite support in PhoneGap doesn't allow you to ship a pre-populated database. (Although it is possible via a workaround: Prepopulate SQLite DataBase in PhoneGap Application.) The syntax for this was based on work from my fellow evangelist, Christope Coenraets.

executeSql: As you can guess, this simply wraps up executing SQL. While PhoneGap doesn't make this necessarily hard, I found the API a bit awkward in terms of all the callbacks you had to use. ("All" sounds like a lot - it's more like two - but you get the idea.) So for example:

dbController.executeSql("select * from notes", gotNote, errHandler);

The other nice thing this will do is automatically take the result set and return it as a simple array of objects. Again, it's not difficult to work with the normal result set, this is just a bit simpler.

Finally, the class has a simple init() handler that sets up your connections for you. What makes it nice is that it can also automatically call your batch scripts for you. So for example:

dbController.init("main","data/seed.xml",dbReady);

The code is below and is free to use by anyone.

var DBController = function() {

var db,success,failure;

return {

init:function(name,importscript,successHandler) { //todo - allow for version db = window.openDatabase(name,"1.0",name,100000); if(typeof importscript !== "undefined") { console.log("being asked to run a script"); if(typeof successHandler === "undefined") throw "Invalid call - must pass success handler when importing data"; this.executeBatch(importscript,successHandler); } },

executeBatch:function(path,successHandler,errorHandler) { success=successHandler; failure=errorHandler;

$.get(path, {}, this.gotFile, "xml"); },

//sql, successHandler, errorHandler are required executeSql:function(sql,args,successHandler,errorHandler) { console.log('going to run '+sql+ ' '+arguments.length); //Don't like this - but way to make args be optional and in 2nd place if(arguments.length == 3) { successHandler = arguments[1]; errorHandler = arguments[2]; args = []; } db.transaction( function(tx) { tx.executeSql(sql,args,function(tx,res) { //todo - figure out fraking scoping rules and why line below didnt work, nor this.X //res = translateResultSet(res); var result = []; for(var i=0; i<res.rows.length; i++) { result.push(res.rows.item(i)); } successHandler(result); })} , errorHandler) },

gotFile:function(doc) { var statements = []; var statementNodes=doc.getElementsByTagName("statement"); for(var i=0; i<statementNodes.length; i++) { statements.push(statementNodes[i].textContent); } if(statements.length) { db.transaction(function(tx) { //do nothing for(var i=0;i<statements.length;i++) { tx.executeSql(statements[i]); } }, failure,success); } },

translateResultSet:function(res) { var result = []; for(var i=0; i<res.rows.length; i++) { result.push(res.rows.item(i)); } return result;

}

}

};

Archived Comments

Comment 1 by Mikel posted on 1/31/2012 at 5:46 PM

This looks like a really cool way to ship your app with some data. Thanks!

Comment 2 by Kelly Jones posted on 2/24/2012 at 11:34 AM

Fantastic post. Here’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/

Comment 3 by Artem posted on 5/15/2012 at 11:57 PM

Hi Raymond,

Thanks a lot for this solution! I've spent days looking for it.

One question though, where should I put my xml file? I'm not sure I completely understand which folder that should be.

Thanks in advance for your help!

- Artem

Comment 4 by Raymond Camden posted on 5/16/2012 at 12:00 AM

In the example above (first code block), you see:

sql/createtables.xml

This is relative from the file calling it - typically your index.html file for your PhoneGap project.

I recommend a subdirectory. You can call it anything you want.

Comment 5 by Artem posted on 5/16/2012 at 12:23 AM

Thanks Raymond! You are the best!

May the force be with you! (c)

Comment 6 by Peter Andres posted on 11/16/2012 at 1:05 PM

Hi,
i've got a question: Will the database automatically be stored somewhere? If yes, where?

Comment 7 by Raymond Camden posted on 11/16/2012 at 9:57 PM

It is stored in the file system automatically.