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.

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.

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.

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.

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.

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.

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.

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.

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!

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 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:

The SQL we want to execute

Any parameterized values for the SQL statement, if any, passed as an array

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.

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.

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.

Comments on "HTML5 Database; Better than Sliced Bread?"

aotto

Your browser is running as root? Seriously, you\’ve got to be kidding.

Yes, it is confusing to keep track of all this HTML5 and related activity with various standards, standards bodies, collections of standards, what is moving where, and which implementations are doing or planning to do what or not what. The state of all this and its expected continued mode-of-operation will cause disillusionment amongst many developers and have them continue to turn to better-managed (at least from their perspective) platforms.

Hi there, i read your blog occasionally and i own a similar one and i was just wondering if you get a lot of spam remarks? If so how do you reduce it, any plugin or anything you can advise? I get so much lately it’s driving me insane so any support is very much appreciated.

It’s a pity you don’t have a donate button! I’d most certainly donate to this superb blog! I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to new updates and will talk about this site with my Facebook group. Talk soon!

Good day! I know this is kind of off topic but I was wondering which blog platform are you using for this website? I’m getting fed up of WordPress because I’ve had issues with hackers and I’m looking at options for another platform. I would be great if you could point me in the direction of a good platform.

I keep listening to the newscast lecture about getting boundless online grant applications so I have been looking around for the finest site to get one. Could you advise me please, where could i find some?