Twitter: raymondcamden


Address: Lafayette, LA, USA

A database utility class for PhoneGap

01-26-2012 8,463 views Mobile, Development 7 Comments

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:

view plain print about
1myDbController.executeBatch("sql/createtables.xml",successHandler,errHandler);

Where your XML would look like so:

view plain print about
1<sql>
2<statement>
3create table foo if not exists foo(....)
4</statement>
5<statement>
6create table moo if not exists foo(....)
7</statement>
8</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:

view plain print about
1dbController.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:

view plain print about
1dbController.init("main","data/seed.xml",dbReady);

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

view plain print about
1var DBController = function() {
2
3    var db,success,failure;
4    
5    return {
6
7        init:function(name,importscript,successHandler)    {
8            //todo - allow for version
9            db = window.openDatabase(name,"1.0",name,100000);            
10            if(typeof importscript !== "undefined") {
11                console.log("being asked to run a script");
12                if(typeof successHandler === "undefined") throw "Invalid call - must pass success handler when importing data";
13                this.executeBatch(importscript,successHandler);
14            }
15        },
16
17        executeBatch:function(path,successHandler,errorHandler) {
18            success=successHandler;
19            failure=errorHandler;
20            
21            $.get(path, {}, this.gotFile, "xml");
22        },
23
24        //sql, successHandler, errorHandler are required
25        executeSql:function(sql,args,successHandler,errorHandler) {
26            console.log('going to run '+sql+ ' '+arguments.length);
27            //Don't like this - but way to make args be optional and in 2nd place
28            if(arguments.length == 3) {
29                successHandler = arguments[1];
30                errorHandler = arguments[2];
31                args = [];
32            }
33            db.transaction(
34                function(tx) { tx.executeSql(sql,args,function(tx,res) {
35                    //todo - figure out fraking scoping rules and why line below didnt work, nor this.X
36                    //res = translateResultSet(res);
37                    var result = [];
38                    for(var i=0; i<res.rows.length; i++) {
39                        result.push(res.rows.item(i));
40                    }
41                    successHandler(result);
42                })}
43            , errorHandler)    
44        },
45        
46            
47        gotFile:function(doc) {
48            var statements = [];
49            var statementNodes=doc.getElementsByTagName("statement");
50            for(var i=0; i<statementNodes.length; i++) {
51                statements.push(statementNodes[i].textContent);
52            }
53            if(statements.length) {
54                db.transaction(function(tx) {
55                    //do nothing
56                    for(var i=0;i<statements.length;i++) {
57                        tx.executeSql(statements[i]);
58                    }
59                }, failure,success);
60            }
61        },
62        
63        translateResultSet:function(res) {
64            var result = [];
65            for(var i=0; i<res.rows.length; i++) {
66                result.push(res.rows.item(i));
67            }
68            return result;
69            
70        }
71            
72    }
73    
74};

7 Comments

These comments will soon be imported into Disqus. To add a comment, use Disqus above.
  • Mikel #
    Commented on 01-31-2012 at 6:46 AM
    This looks like a really cool way to ship your app with some data. Thanks!
  • Kelly Jones #
    Commented on 02-24-2012 at 12: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/
  • Artem #
    Commented on 05-15-2012 at 2: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
  • Commented on 05-15-2012 at 3:00 PM
    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.
  • Artem #
    Commented on 05-15-2012 at 3:23 PM
    Thanks Raymond! You are the best!

    May the force be with you! (c)
  • Peter Andres #
    Commented on 11-16-2012 at 2:05 AM
    Hi,
    i've got a question: Will the database automatically be stored somewhere? If yes, where?
  • Commented on 11-16-2012 at 10:57 AM
    It is stored in the file system automatically.