Psychic Origami

Opening the database

The main function we're concerned with is openDatabase which let's us open/create a database that can only be accessed by the current domain (in a similar manner to cookies). In the TODO app I use this code to see if openDatabase is available and output a message if not and stop any further processing:

if ( !window.openDatabase ) {
    $('add_note').innerHTML = "Browser does not support local storage."
    return;
}

(note $ is a shortcut to document.getElementById in the TODO app code)

Assuming we find the openDatabase function we can then get a database object thus:

var dbName = '5KTodo',
     db = openDatabase(dbName, '1.0' /*version*/, dbName, 65536 /*max size*/);

In this case we create/open a database called 5KTodo. We provide a version number (not really used at this stage, but is intended for allowing upgrading database schemas) and a max size. The max size is provided as a hint to the browser as to how big the database will grow. The upper limit for this is defined by the browser (and is about 5M in Safari).

Now once we've got our database object we can start running some SQL. We do this using the database object's transaction method and the transaction's (tx) executeSql function:

db.transaction(
    function(tx) {
        tx.executeSql(sql, params, callback);
    }
);

That will run the sql using the given params (replacing ? in the sql string with the param values to avoid sql-injection attacks) and then calls the callback with the results (if it succeeded).

Creating the tables

The first time we run the app the database will not exist and will contains no database tables, so we need to run some SQL to create the tables:

function dbTransaction(fn) {
    db.transaction(fn);
}

/*create todo table*/
dbTransaction(
    function (tx) {
        tx.executeSql('CREATE TABLE todo (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, description TEXT NOT NULL DEFAULT "", todo_order INTEGER NOT NULL DEFAULT 0, done INTEGER NOT NULL DEFAULT 0 );');
    }
);

On the next run of the application that will fail silently, which is fine as it only needs to run successfully once (to create the tables). It's key that the create table statement runs in it's own transaction though, so when it fails it doesn't stop anything else running. Note that dbTransaction used here is only so that YUICompressor can better compress the code, as this code is from an example for the 5K App.

Reading from the database

After creating the tables (or doing nothing if they are already there) the next step is to see what's already in the database. Unlike the create table statement we expect to get some data back, so we provide a callback to get the results:

dbTransaction(
    function (tx) {
        listTODOs(tx, 'SELECT * FROM todo ORDER BY todo_order ASC', []);
    }
);

/*run the sql to read the notes and insert them into the DOM*/
function listTODOs(tx, sql, params) {
    tx.executeSql(
        sql, params,
        function(tx, results) {
            var rows = results.rows;
            for ( var i = 0; i < rows.length; i++ ) {
                var row = rows.item(i),
                    li = createElement('li'),
                    checkbox = createElement('input'),
                    label = createElement('label');
                setAttribute(checkbox, 'type','checkbox');
                setAttribute(checkbox, 'id', 'checkbox_'+row['id']);
                setAttribute(label, 'for', 'checkbox_'+row['id']);
                appendChild(label, createTextNode(row['description']));
                appendChild(li,checkbox);
                appendChild(li,label);
                setAttribute(li,'id', 'note_'+row['id']);

                if ( !row['done'] ) {
                    // not done, so insert above text field
                    notesList.insertBefore(li,$('add_note'));
                }
                else {
                    // done so put below text field
                    setAttribute(checkbox,'checked','checked');
                    li.className = 'done';
                    appendChild(notesList, li);
                }

                attachTODOHandlers(row['id'],li,checkbox);
            }
        });
}

(note there are a lot of shortcut functions in there, but hopefully you should get the idea)

What that basically does is execute 'SELECT * FROM todo ORDER BY todo_order ASC' on the database in a transaction (tx) and then takes the results and inserts them into the DOM.

Inserting into the database

Inserting into the database is pretty straightforward - we just use an SQL insert statement. Though in the case of the 5K TODO app we need to do a couple of other things. The first is figuring out the next highest todo_order, then we insert the TODO item and next we re-select the TODO items from the database (with the id of the newly inserted item - results.insertId). The 2nd part is a bit overly obtuse in some respects, but was done so as to help cut down on code size (to fit under the 5K limit):

function insertTODO(description) {
    dbTransaction(
        function(tx) {
            // set order to be one after items that aren't done
            tx.executeSql('SELECT MAX(todo_order) AS next_order FROM todo WHERE done=0', [],
                function(tx,results) {
                    var order = results.rows.item(0)['next_order'] + 1;
                    tx.executeSql('INSERT INTO todo (description,todo_order) VALUES(?,?)', [description,order],
                        function(tx,results) {
                            listTODOs(tx, 'SELECT * FROM todo WHERE id=?', [results.insertId]);
                            updateTODOOrder(tx);
                        }
                    );
                }
            );
        }
    );
}

As the later SQL statements depend on results from the earlier statement(s) they actually get run in the statement callback functions.

It's fairly straightforward to access a database this way. For those familiar with databases it should all be pretty familiar territory. There's plenty of more info on Apple's site about using the Javascript database. You may also want to look at the source code the the 5K TODO app, though be warned a little bit of readability has been sacrificed for code size.