techno.blog("Dion")

I have been having fun playing with Google Gears. As I build applications with it (such as RSS Bling) I find myself repeating a few things on the database side.

Just wanting objects back

Instead of working with result sets, I just want to think in objects / hashes coming back.

To grab one fella I can:

bob = db.selectRow('person', 'id = 1');

or

bob = db.selectOne('select * from person where id = 1');
bob = db.selectOne('select * from person where id = ?', [1]);

console.log(bob.name); // write out 'Bob'

It is more interesting when you return back a bunch:

db.selectAll('select * from person where name like ?', ['bob%'], function(person) {
document.getElementById('selectAll').innerHTML += ' ' + person.name;
});

The callback gets passed in an object representation of the row. You can get back all of the results, but you should favour dealing with a row at a time instead of waiting around.

Inserting and updating

You often have an object that you want to fling in. This is easy to do via:

var bob = {name: 'Bob', url: 'http://bob.com', description: 'whee'};
db.insertRow('person', bob);
db.insertRow('person', bob, 'name = ?', ['Bob']);

The last form will only do the insert if another Bob isn’t already in there.

You can then update via:

db.updateRow('person', person); // assumes that 'id' is the id, else you pass it in

or force the row in via insert or update (if it exists update, else insert):

db.forceRow('person', person);

To round things off you can db.deleteRow('person', bob);, db.dropTable("person");, and db.run('create table....');.

run() is a simple wrapper around execute() that handles logging and such for you.

To get started with the database you just need to:

var db = new GearsDB('gears-test'); // db name

All of this is in an open source project gears-dblib, and you can see simple tests/examples running.

The project also runs with Firebug Lite so you can play with simple console.log type things even if you don’t have Firebug installed (e.g. want to test on IE). Very nice indeed.

Of course, someone will rewrite ActiveRecord and Hibernate in JavaScript shortly to work with Gears ;)