HTML5 Database

Learn how to use the HTML5 database for storing client-side data. Client side storage is enabling the next generation of mobile applications.

Good things stored up

In this column we’re going to have a look at some simple List Widget sorting and then move on to examining data storage using a WebOS Depot and more importantly the HTML5 database.

Looking beyond the WebOS widgets for a moment, it is important to recognize that the lessons learned here for data persistence transfer to both “traditional” web applications running on desktops as well as to the new breed of mobile applications which leverage the WebKit browser engine including phonegap, appcelerator, rhomobile and other run-it-in-the-browser-control-oriented development tools and strategies.

The core functionality of interest is the HTML5 database storage capabilities which are presently implemented as a wrapper to an underlying SQLite database layer. We will look at the code for making the storage take place and also have a look behind the curtain of the WebOS data organization — I was really curious to see where everything was hiding and thought you might be curious also. Source code for this application is available on the Linux Magazine Google Code Hosting site .

Sorting things out

Before jumping into the land of data storage, I want to finish out what we started  in our last column , so let’s first have a look at what it takes to sort a List Widget.

There are two aspects to our approach to sorting. The first is the User Interface necessary to allow the user to determine the sort order and the second is the code to perform the sorting. The image below shows our application presenting a few sorting options to the user in a ListSelector widget.

As you can see, we allow three different sort fields: Name, Position, and Number. Selecting one of these causes the ListWidget to be re-sorted appropriately. The code below shows how to setup the ListSelector.

 this.controller.setupWidget("sortBy",
	{
	choices: [{label:"Name",value:"name"},
	         {label:"Position",value:"position"},
	         {label:"Number",value:"number"}
			],
			modelProperty: 'value',
			label:'Sort By',
			labelPlacement: Mojo.Widget.labelPlacementLeft
	},
	this.sortedby
	);

this.handleSortByChanged = this.sortByChanged.bindAsEventListener(this);
this.controller.listen('sortBy',Mojo.Event.propertyChanged,this.handleSortByChanged);

The user-suppliled function named sortByChanged is invoked when the ListSelector’s selection is changed. This function grabs the new sort-by value and then initiates a sort on the array of players in our roster. Once the array is sorted, we notify the scene that our data has changed — this will prompt the list to be redrawn because it is “watching” this particular data model.

 this.mySortFunc = this.sortFunc.bind(this);

//Mojo.Log.info("sort by changed [" + event.value + "]");
this.sortedby.value = event.value;
this.listModel.items.sort(this.mySortFunc);
this.controller.modelChanged(this.listModel, this);

The sort function is a member of the Array object and takes a single, optional argument which is a user-defined sort function. Because we want to control which field is sorted on, we need to provide this custom function, as shown below.

 HomeAssistant.prototype.sortFunc= function (a,b) {
	try {
		//Mojo.Log.info("Sorting by " + this.sortedby.value);
		if (a[this.sortedby.value] == b[this.sortedby.value]) return 0;
		if (a[this.sortedby.value]> b[this.sortedby.value]) return 1;
		return -1;
	} catch (e) {
		Mojo.Log.info("error in sortFunc" + e);
		return 0; // default to no difference
	}
}

Note the use of the sortedBy field’s value to index in by name to a JavaScript object — very cool feature.

And that’s pretty much it — provide the sortFunc to the sort method and then update the ListWidget with a call to this.controller.modelChanged(..). And — very important for WebOS development, make sure that your functions are “bound” to “this” with a call to bind(this) on any method you want to have access to class level data. Failure to do this will result in not a small amount of frustration. Yes, experience talking here.

OK, enough sorting, let’s get on to the data persistence!

Data storage strategy

As a quick refresher on this application — it is a classic “List” application. It has a single data model with an array named “items” which holds our data and an HTML template is used to display the data. If you need some more info on List Widget fundamentals, you are encouraged to look at Using Lists in WebOS — Mastering the Oldest Mobile Profession  for a more in-depth look at the popular widget.

Our list data is stored as an array of the variable named listModel.

 this.listModel = {items: [] }

In order to display data in our list, we have to get data into the items array. In order to persist our data, we need to get data out of the items array. Simple enough, right?

WebOS has three storage methods: Cookies, Depots and HTML5.

Cookies are good for small amounts of data — up to about 4K and are the preferred storage mechanism for things like user names and preferences. We’re not interested in Cookies here.

The Depot is used to store Javascript objects — essentially by breaking down each member of an object and writing it out to a “private” HTML5 database. We will demonstrate this technique by reading and writing the entire array from/to a Depot.

The HTML5 database API is a DOM-accessible wrapper around the SQLite database. In simple terms this means we can use sql-oriented programming to create tables and perform basic inserts, updates, deletes, etc. against a relational database. The idea of doing this from within a web page is really a paradigm shift after so many years of not being able to store or retrieve relational data without a round-trip to the server. Ajax has certainly helped, but this local storage has the potential to be a real game-changer.

House-keeping

This sample application was setup in a fashion to (relatively) easily switch between using a Depot, HTML5, and no persistence at all. To switch between the three options, you need to comment/uncomment a couple of lines in the HomeAssistant.js file.

 // change these values as desired between:
// depot
// html5
// none
this.storageTechnique = "Depot";
this.depotHandle = null;
//this.storageTechnique = "HTML5";
//this.dbHandle = null;
//this.storageTechnique = "none";

Let’s have a look at the Depot first, so we comment out the other options for now. Note that in your applications you will just decide which data persistence strategy you want to employ and then code it appropriately — these options are here for demonstration purposes only.

Depot storage

In order to work with a Depot, we first must create it.

 this.depotHandle = new Mojo.Depot({name:"listfun2depot",version : 1,estimatedSize:1000,replace: false},this.depotOpenSuccess.bind(this),this.depotOpenFailure.bind(this));

Note that the Depot api is very “callback” intensive. We create our Depot with a name, a version, an estimated size, and an flag for replacement. We also provide callback-handlers for success and failure.

Assuming success in our Depot open/creation, the depotOpenSuccess function is called. Again, note the “bind(this)”!

 HomeAssistant.prototype.depotOpenSuccess = function () {
...
this.depotHandle.get("roster",this.depotLoadOK.bind(this),this.depotLoadFail.bind(this));
...
}

We are using the “get” method of the Depot to retrieve an object identified by the key “roster”. Upon success, the depotLoadOK function is invoked.

 HomeAssistant.prototype.depotLoadOK = function (result) {
...
	this.listModel.items = result;
	this.controller.modelChanged(this.listModel,this);
...
}

We assign our array to be equal to the data retrieved from the Depot under the “roster” key and then update the model. It really is that simple, because we stored the entire array into the Depot. Let’s look at what it takes to “save” our data to the Depot.

 this.depotHandle.add("roster", this.listModel.items, this.depotSaveSuccess.bind(this),this.depotSaveFailure.bind(this));

Here we use the “add” method of the Depot, passing in our items array and the requisite callback functions.

The Depot has other methods for removing one or all entries, etc. For a complete syntax reference, visit the  Palm Developer website .

Behind the scenes with Depot

So our Depot is storing data, but where? At present, the WebOS jams the Depot into an HTML5 database. You can find your application’s Depot database in a subdirectory of the /var/home/root/html5-databases directory. Each application has its own sub-directory.

 root@qemux86:/var/home/root/html5-databases/ 
file_.var.usr.palm.applications.com.msiservices.listfun3_0# ls -l
-rw-r--r--    1 root     root         6144 Oct 26 03:36 000000000000001b.db
-rw-r--r--    1 root     root        16384 Oct 26 03:38 000000000000001c.db

Of course, these names are clear as mud. Let’s see if we can learn what those names mean.

Back in the /var/home/root/html5-databases directory there is a file named Databases.db.

 root@qemux86:/var/home/root/html5-databases# ls -l
-rw-r--r--    1 root     root        10240 Oct 26 04:30 Databases.db
drwx------    2 root     root         1024 Oct 26 03:18 file_.usr.lib.luna.system.luna-applauncher_0
drwx------    2 root     root         1024 Oct 19 08:33 file_.usr.palm.applications.com.palm.app.browser_0
drwx------    2 root     root         1024 Oct 25 20:07 file_.usr.palm.applications.com.palm.app.email_0
drwx------    2 root     root         1024 Oct  4 23:03 file_.usr.palm.applications.com.palm.app.firstuse_0
drwx------    2 root     root         1024 Oct 25 20:07 file_.usr.palm.applications.com.palm.app.phone_0
drwx------    2 root     root         1024 Oct 26 03:56 file_.var.usr.palm.applications.com.msiservices.listfun3_0

Let’s have a look at the contents of this file by opening the database up with sqlite command line tool.

 root@qemux86:/var/home/root/html5-databases# sqlite3 Databases.db
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Databases  Origins

sqlite> select * from Databases;
1|file_.usr.lib.luna.system.luna-applauncher_0|paged_launcher_apps|paged_launcher_apps|0|0000000000000001.db
2|file_.usr.palm.applications.com.palm.app.email_0|email_accounts|email_accounts|0|0000000000000002.db
3|file_.usr.palm.applications.com.palm.app.phone_0|carrierbook|carrierbook|0|0000000000000003.db
5|file_.usr.palm.applications.com.palm.app.browser_0|browser_data|BrowserData|46080|0000000000000005.db
7|file_.usr.palm.applications.com.palm.app.firstuse_0|currentState|currentState|0|0000000000000007.db
27|file_.var.usr.palm.applications.com.msiservices.listfun3_0|rosterhtml5 |Roster Database|1000|000000000000001b.db
28|file_.var.usr.palm.applications.com.msiservices.listfun3_0|listfun2depot|listfun2depot|1000|000000000000001c.db
sqlite>

Presumably all of the “open” functions, whether Depot or HTML5 database, pass through here to get the folder name and database file name of interest. For example, we can learn from this information that our depot is stored in the 000000000000001c.db file. Let’s have a look!

 root@qemux86:/var/home/root/html5-databases/ 
file_.var.usr.palm.applications.com.msiservices.listfun3_0# sqlite3 000000000000001c.db
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
__WebKitDatabaseInfoTable__  properties
insinfo                      toc

Our Depot data is spread between the properties and toc database tables.

 sqlite> .schema toc
CREATE TABLE 'toc' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'bucket' text NOT NULL DEFAULT '', 'key' text NOT NULL ON CONFLICT REPLACE DEFAULT '', UNIQUE ('bucket', 'ke  y'));

sqlite> .schema properties
CREATE TABLE 'properties' ('frowid' integer, 'value' text DEFAULT '', 'left' integer default 0, 'right' integer default 0, 'name' text DEFAULT '', 'type' text DEFAULT '', Foreig  n Key(frowid) references toc(id), Primary key(frowid, left, right));

sqlite> select * from toc;
7|defaultbucket|roster

sqlite> select * from properties;
7|Cooper|3|4|name|string
7|Guard|5|6|position|string
7|21|7|8|number|number
7|(container)|2|9|0|object
7|Kareem|11|12|name|string
7|Center|13|14|position|string
7|33|15|16|number|number
7|(container)|10|17|1|object
7|Magic|19|20|name|string
7|Guard|21|22|position|string
7|32|23|24|number|number
7|(container)|18|25|2|object
7|Mickey Mouse|27|28|name|string
7|Guard|29|30|position|string
7|55|31|32|number|number
7|(container)|26|33|3|object
7|Rambis|35|36|name|string
7|Forward|37|38|position|string
7|31|39|40|number|number
7|(container)|34|41|4|object
7|Scott|43|44|name|string
7|Guard|45|46|position|string
7|4|47|48|number|number
7|(container)|42|49|5|object
7|(container)|1|50||array
sqlite>

With enough time and insomnia, we can walk our way through this data — but only if we’re bored. The Depot just works and that’s good enough for us for now. Let’s have a look at the HTML5 database.

HTML5 database functionality

As a reminder, we need to set the code in our example to use the HTML5 routines:

 // change these values as desired between:
// depot
// html5
// none
//this.storageTechnique = "Depot";
//this.depotHandle = null;
this.storageTechnique = "HTML5";
this.dbHandle = null;
//this.storageTechnique = "none";

Note that this discussion of HTML5 database support is valid for other environments beyond WebOS, namely: WebKit-enabled Safari, iPhone’s browser, Firefox 3.5.1+, Android’s browser and any other browser claiming to support the database spec of HTML5. The term HTML5 database is really a bit awkward but that is how it is known at present.

In order to use an HTML5 database, we must first open/create it. This is accomplished with a simple call to the openDatabase method. Note that this method is exposed without another object reference — just call it. I have also seen it called as window.openDatabase(…) in a Webkit example.

 this.dbHandle = openDatabase("rosterhtml5 ","1.0","Roster Database",1000);

This is the easy part. What takes a little more effort is wrapping our head around the manner in which sql is “submitted” in a transaction.

It would be really nice if we could make simple synchronous calls to the database but I have given up on anything in Javascript being that way — probably for good reasons, but it is a bit of a learning curve for those of us who have rarely chosen the “asynchronous” version of functions in other development environments. OK, enough whining, let’s talk about using SQL with our HTML5 database.

To submit SQL, we must actually pass a function to the transaction method of our database. The function itself calls a function called executeSQL and it takes four parameters:

1. The SQL we want to execute
2. Any parameterized values for the SQL statement, if any, passed as an array
3. An OnSuccess callback function
4. An OnError callback function

Here is the code we use to get player data out of our the database:

 this.dbHandle.transaction(function(tx) {
	tx.executeSql("SELECT * FROM tbl_roster",
	 // parameters
	 [],
	 // good result
	 function(tx,result){
		try {
			for (var i = 0; i < result.rows.length; i++) {
				var row = result.rows.item(i);
				var p = new Player();
				p.name = row['pname'];
				p.position = row['pposition'];
				p.number = row['pnumber'];
				this.listModel.items.push(p);
			}
			this.listModel.items.sort(this.mySortFunc);
			this.controller.modelChanged(this.listModel, this);
		} catch (e) {
			Mojo.Log.error("EEEEEEEEEEEEE  Error fetching rows from database... " + e);
		}
	 }.bind(this),
	// error
	 function(tx,error){
		Mojo.Log.info("bad result on query: " + error.message + " let's try to create the db table");
		this.createHTML5Table();
	 }.bind(this)
	 );
}.bind(this));

We are simply asking for all of the records in the table named tbl_roster. There are no parameters in this query so we pass in an empty array. The “success” function is provided in-line and steps through the result set. Note that the datatype of the result is the SQLResultSet from the SQLite library. As we pull out each row from the database, we add it to our “items” array with a call to the push method of the array. When we’ve pulled all of the records, we sort the list and then update the UI to show the records.

In the event of an error, we display the error to the log and then attempt to create the table. This is really a bit lazy, but it gets the job done. If there is a more severe error, we would have to do some more debugging. You are free to add that yourselves.

OK, so let’s have a look at what happens when a new player is added. Considering my dream was always to play for the Los Angeles Lakers, I am going to add myself to the roster — please excuse the indulgence.

We have a function called handleNewPlayer which needs to take care of this data storage. The function is pasted below.

 HomeAssistant.prototype.handleNewPlayer = function(player) {
try {
	Mojo.Log.info("handleNewPlayer: " + player.name + "," + player.position + "," + player.number);
	this.listModel.items.push(player);
	this.listModel.items.sort(this.mySortFunc);
	this.controller.modelChanged(this.listModel, this);

	switch (this.storageTechnique) {
		case "Depot":
			this.saveRoster();
			break;
		case "HTML5":
			this.dbHandle.transaction(function(tx){
				tx.executeSql("insert into tbl_roster (pname,pposition,pnumber) values (?,?,?)", [player.name, player.position, player.number.toFixed(0)]);
			});
			break;
	}
}catch (e) {
	Mojo.Log.error("EEEEEEEEEEEEEE error handling new player " + e);
}
}

Note that this function takes a single parameter representing the new player. The new player is added to the list of items with a call to push and then the list is sorted. Next we want to persist the data. You will notice that when we are using the Depot, we simply save the entire list, however with HTML5 the approach is to do things record by record. Note the third parameter has the toFixed(0) method invoked. This keeps the numeric value clean without a decimal portion showing — decimals don’t fit too well on basketball jerseys!

Deleting players and resetting the roster follow a similar approach. Let’s have a look at the database via our SSH connection to the emulator.

 root@qemux86:/var/home/root/html5-databases/ 
file_.var.usr.palm.applications.com.msiservices.listfun3_0# sqlite3 000000000000001b.db

SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
__WebKitDatabaseInfoTable__  tbl_roster

sqlite> .schema tbl_roster
CREATE TABLE tbl_roster (pname TEXT,pposition TEXT,pnumber TEXT);

sqlite> select * from tbl_roster;
Magic|Guard|32
Scott|Guard|4
Cooper|Guard|21
Kareem|Center|33
Rambis|Forward|31
Frank Ableson|Guard|24
sqlite>

That wraps up our survey of data persistence on WebOS in particular and HTML5 database storage in general. I would highly recommend checking out the code to get a better feel for working with the callbacks necessary to make HTML5 databases to function. And don’t forget to “bind” your functions or you won’t be able to access the data you need at the place and place of interest! Happy databasing.