Software | Secret Software | Writing
XUL, mozStorage and SQLite
I've just been writing a fairly involved XUL application which makes use of the mozStorage extension to store its data in a relational database. I'm far, far from an expert on this, and I'm not all that hot a Javascript programmer, so some of the stylistic features may be able to be improved. However, I do have something that works and that I think is neat, and wanted to share how it all works. So please consider this less of a "how to" and more of a "how I did it". I hope it's helpful.
Preparations
To use storage in XULrunner,
you'll need a XULrunner compiled with the storage extension.
Sorry to state the obvious but it caught me out!
XULRunner >= 1.8 should have storage compiled in,
so grab a recent build,
or make sure you enable storage when compiling it yourself.
Next, you'll need a SQLite database. You have two options here. You can either build one yourself in SQL, or you can do what I did and cheat, shipping your own template database file with all the tables and any data you want pre-seeded. You can copy the template to the user's profile directory like so:
var dserv = Components.classes["@mozilla.org/file/directory_service;1"]
.getService(Components.interfaces.nsIProperties);
// Here's where the file will end up in the profile directory
var file = dserv.get("ProfD", Components.interfaces.nsIFile);
file.append("workship.db");
// This is the template file
var ours = dserv.get("AChrom", Components.interfaces.nsIFile);
ours.append("workship.db");
// Copy if we haven't got it
if (!file.exists())
ours.copyTo(dserv.get("ProfD", Components.interfaces.nsIFile),
"workship.db");
Now we're in a state where we can make a database connection to the file:
var storageService = Components.classes["@mozilla.org/storage/service;1"]
.getService(Components.interfaces.mozIStorageService);
var mDBConn = storageService.openDatabase(file);
And we're off.
Object relational mapping
Here's where a big style difference may come in. I come from a Perl background and am a particular fan of ORM modules like Class::DBI, which allow me to pretend that the database is just an object factory, and not have to worry about all that SQL stuff. So when I came to Javascript, I did the same. Here's the magic function I use to do all the hard work:
function doSQL(sql, targetClass, callback) {
var rv = new Array;
var statement = mDBConn.createStatement(sql);
while (statement.executeStep()) {
var c;
var thisArray = new Array;
for (c=0; c < statement.numEntries; c++) {
thisArray.push(statement.getString(c));
}
var thing = new targetClass(thisArray);
if (callback) { callback(thing) }
rv.push(thing);
}
return rv;
}
This basically runs an SQL query, turns each result row into an object, and optionally calls a callback on that object, returning the whole set of objects as an array. It's slightly naughty in that it returns every attribute as a string; but SQLite is a typeless database, and JavaScript handles string-integer conversion for you, so it ought to be OK.
It requires a bit of scaffolding for each table:
function Playlist (myA) { this.id = myA[0]; this.name = myA[1]; }
Playlist.retrieve = function (id) {
var arr = doSQL("SELECT id, name FROM playlist WHERE id = "+id, Playlist);
if (arr) { return arr[0] }
};
Playlist.retrieveAll = function (callback) {
return doSQL("SELECT id, name FROM playlist", Playlist, callback);
};
function Song (myA) {
this.id = myA[0];
this.song_key = myA[1];
this.title = myA[2];
this.first_line = myA[3];
this.xml = myA[4];
}
Song.retrieve = function (id) {
var arr = doSQL("SELECT * FROM song WHERE id = "+id, Song);
if (arr) { return arr[0] }
};
Song.retrieveAll = function (callback) {
return doSQL("SELECT * FROM song", Song, callback);
}
Where did all this get us? Well, to fill a listbox with a list of playlists, we can now say:
Playlist.retrieveAll(function (pl) {
var li = document.createElement("listitem");
li.setAttribute("label", pl.name);
listbox.appendChild(li);
});
Has-many relationships
A playlist is a list of songs, with its own name. There's a table called play_item which says which songs are in which playlist in which order. It's natural for us to want to be able to get a list of the songs for each playlist. Here's how it's done: (edited for readability)
Playlist.prototype.songs = function (callback) {
return doSQL("SELECT song.id, song_key, title, first_line, xml
FROM song, play_item
WHERE play_item.playlist = "+this.id+"
AND play_item.song = song.id
ORDER BY play_item.position
", Song, callback);
}
If we call songs on an object returned by Playlist.retrieve or Playlist.retrieveAll, we get an array of songs in that playlist.
Creation and deletion
We can now define methods to create and delete playlists and songs. Deleting a playlist is interesting because we also have to delete the stuff in play_item that refers to that playlist:
Playlist.prototype.delete = function () {
this.delete_items(); // Tidy up
var statement = mDBConn.createStatement("DELETE FROM playlist WHERE ID = (?1)");
statement.bindInt32Parameter(0, this.id);
statement.execute();
};
Playlist.prototype.delete_items = function () {
var statement = mDBConn.createStatement("DELETE FROM play_item WHERE playlist= (?1)");
statement.bindInt32Parameter(0, this.id);
statement.execute();
};
Now when we're creating our list of playlists, we can add a button like so:
var delbut = document.createElement("button");
delbut.setAttribute("oncommand", "delete_pl("+pl.id+")");
delbut.setAttribute("label", "Delete");
li.setAttribute("id", "playlist"+pl.id);
li.appendChild(delbut);
And this fires a function:
var promptService =
Components.classes["@mozilla.org/embedcomp/prompt-service;1"]
.getService(Components.interfaces.nsIPromptService);
function delete_pl (id) {
var pl = document.getElementById("playlist"+id);
if (promptService.confirm(window, "Delete playlist", "Do you want to delete this playlist?")) {
target.removeChild(pl);
Playlist.retrieve(id).delete();
}
}
(Thinking about it, you could probably stash the Playlist object inside the DOM element, but I don't know how brittle that would be.)
Inserting is a little more involved. Once you've inserted a row in the database, you want to return an object that corresponds to that row. The problem is that you can't know one of the most useful attributes - the ID - until the row has been inserted. Here's the solution:
function auto_increment_value () {
var statement = mDBConn.createStatement("SELECT last_insert_rowid()");
statement.executeStep();
return statement.getInt32(0);
}
Playlist.create = function (name) {
var statement = mDBConn.createStatement(
"INSERT INTO playlist (name) VALUES (?1)"
);
statement.bindStringParameter(0, name);
statement.execute();
return new Playlist([auto_increment_value(), name]);
};
last_insert_rowid is an SQLite function that tells you the ID of the last inserted row. We return an object with that information in it. Now we can say:
<toolbarbutton label="Add Playlist" oncommand="create_pl()"/>
...
function create_pl () {
var name = prompt("New playlist name?");
var playlist = Playlist.create(name);
// Create a listitem as above and add it to the list of playlists
}
Notice how we bind parameters to the SQL statement in Playlist.create. This is something that ideally we ought to have been doing all along to stop SQL injection attacks, but in practice, our program so far has not fed user input to the SQL statements but has been generating the data from known sources like IDs. You can't be too careful, though. This isn't a general SQL programming tutorial so I'm not going to go into that.
SQL-driven trees
Even if you don't like the object-relational mapping, you ought to like this. Typically, in XUL, you have tree widgets driven either from static data, Javascript arrays, or from an RDF source. The first is inflexible, the second is inefficient and the third is goddamn impenetrable. We're going to create a tree widget that gets its data from an SQL table. It's going to be a table of songs in our database. Here's the XUL:
<tree id="songlist">
<treecols>
<treecol id="title" label="Title" flex="1"/>
<treecol id="first_line" label="First Line" flex="2"/>
</treecols>
<treechildren />
</tree>
This tree has its own view, naturally, since it's the view that will populate it from the database:
var tree = document.getElementById("songlist");
tree.view = new songTreeView();
The songTreeView starts off as an ordinary object implementing the TreeView interface:
function songTreeView()
{
this.setTree = function(treebox){ this.treebox=treebox; };
this.isContainer = function(row){ return false; };
this.isSeparator = function(row){ return false; };
this.isSorted = function(row){ return false; };
this.getLevel = function(row){ return 0; };
this.getImageSrc = function(row,col){ return null; };
this.getRowProperties = function(row,props){ };
this.getCellProperties = function(row,col,props){};
this.getColumnProperties = function(colid,col,props){};
This is standard boilerplate that I copied basically without thinking. We next need to tell the tree how big it is; that is, how many rows in the database table:
var statement = mDBConn.createStatement("SELECT count(*) FROM song");
statement.executeStep();
this.rowCount = statement.getString(0);
The most important function for a view is the getCellText function which, when given a row number and a column object, returns the data in that cell. Now we could, if we wanted to, suck all the data out of the database, turn it into an array, and then index into that array for each row object and index into that row object to get the cell.
This would be efficient, in a way. There'd only be one SQL query for the whole tree. On the other hand, if there are ten thousand rows in our database, and our table only showed ten rows at a time, this would be hideous inefficient. We'd be retrieving loads of data that potentially, nobody is looking at. Instead we're going to come at this at a bit of an angle.
First, we'll implement a function that returns a Song object for a given row. This is complicated by the fact that rows are not the same as IDs. If we just did the obvious SELECT * FROM song WHERE id=<row> then our table would be full of empty rows where songs had been deleted. Instead, we just want to say, "give me the X'th row returned by the SQL statement I just ran." In SQLite, this is OFFSET X LIMIT 1.
Also, it makes sense for us to implement a simple cache. Because of the way tree works, we need to populate row 0 column 0, then row 0 column 1, then row 0 column 2. We're looking up row 0 three times, but don't particularly want to check the database three times. So we only do a database query if we're looking up a different row to the one we just looked up; if we're looking up the same row, we just return it from the cache:
this.getSongAtIndex = function(row) {
if (row != this.cacheRowNum){
this.cacheRowNum = row;
var sql= "SELECT * FROM song OFFSET "+row+" LIMIT 1";
var res = doSQL(sql, Song);
this.cacheRow = res[0];
}
return this.cacheRow;
};
Now for the magic. We still need to implement getCellText. But this is now trivial:
this.getCellText = function(row,column){
return (this.getSongAtIndex(row))[column.id];
};
If the column's ID is, say, first_line, then this is equivalent to calling this.getSongAtIndex(row).first_line which returns the first line of the object from the cache. Job done! Our tree view will be populated from the database.
Resorting
Finally, to get clever. We want to be able to click on one of the headers of the widget, and have the data sorted either ascending or descending based on the column we just clicked. Thankfully this is something that SQL is very good at.
First, we'll set up some variables which contain information about the ordering of the tree:
this.columns = ["title", "first_line"];
this.orderColumn = this.columns[0];
this.orderDirection = 0;
The first line specifies the columns that are going to be in our tree; the second, the name of the initial column to sort on; the third, the initial sort direction.
Now we must modify our getSongAtIndex function to add an ORDER clause to the SQL statement:
this.getSongAtIndex = function(row) {
if (row != this.cacheRowNum){
var orderclause = this.orderColumn + (this.orderDirection ? "": " DESC");
this.cacheRowNum = row;
var limitclause = "LIMIT 1 OFFSET "+row;
var sql= "SELECT * FROM song ORDER BY "+orderclause+" "+limitclause
var res = doSQL(sql, Song);
this.cacheRow = res[0];
}
return this.cacheRow;
};
This gives us a sorted tree. Next, we want to modify the sort order by clicking on the headers. Thankfully, tree views have a built in "clicked on the header" method, called cycleHeader. Implementing this method is quite easy. If we've clicked on the header we're already ordering things by, this means that we want to reverse the direction of the ordering; otherwise, order by the new column. In any case, repaint the table and invalidate the cache:
this.cycleHeader = function (col) {
if (col.id == this.orderColumn)
this.orderDirection = !this.orderDirection;
else
this.orderColumn = col.id;
this.treebox.invalidate();
this.cacheRowNum = -1; // Invalidate cache
}
There we go: SQL-driven tree widgets, populating a table live from a database in your XUL application. Is it not nifty?