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 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; } } };var DBController = function() {