Building Android Apps: Web SQL Database (part 1) - Creating a Database

Of all the exciting features of HTML5, the one that rocks my world the most is the Web SQL Database. The Web SQL Database spec gives developers a simple but powerful JavaScript database API to store persistent data in a local SQLite database.

Figure 1. Before the refreshEntries() function, the title just says “Date”...

Figure 2. ...and after the refreshEntries() function, the title reflects the selected date

---

Note:

Technically, the Web SQL Database spec is not part of HTML5. It was broken out of the original HTML5 spec into its own spec, but in casual conversation it’s often still referred to as an “HTML5 feature.”

---

Developers can use standard SQL statements to create tables and to insert, update, select, and delete rows. The JavaScript database API even supports transactions. We’re talking about SQL here, so there is an inherent complexity. Regardless, this is a game-changing feature, so time spent getting your head around it will be well rewarded.

1. Creating a Database

Now that our Date panel knows which date the user has selected, we have all the information we need to allow the user to create entries. Before we can write the createEntry() function, we need to set up a database table to store the submitted data (this is a one-time operation). We’ll add some lines to kilo.js to do so:

var db;
$(document).ready(function(){
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
    $('#dates li a').click(function(){
        var dayOffset = this.id;
        var date = new Date();
        date.setDate(date.getDate() - dayOffset);
        sessionStorage.currentDate = date.getMonth() + 1 + '/' + 
                                     date.getDate() + '/' + 
                                     date.getFullYear();
        refreshEntries();
    });
    var shortName = 'Kilo';
    var version = '1.0';
    var displayName = 'Kilo';
    var maxSize = 65536;
    db = openDatabase(shortName, version, displayName, maxSize);
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'CREATE TABLE IF NOT EXISTS entries ' +
                '  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                '   date DATE NOT NULL, food TEXT NOT NULL, ' +
                ' calories INTEGER NOT NULL );'
            );
        }
    );
});

					  

If you were to launch the app as is, it would create a database named Kilo on the Android phone.

In the desktop version of Chrome, you can actually view and interact with your client-side databases by navigating to View→Developer→Developer Tools, and clicking the Storage tab.

The Developer Tools included in desktop Chrome are extremely helpful when debugging. By default, it appears as a pane of your current browser window. If you click the undock icon (hover over the icons at the bottom left to see what they do), it will appear in a separate window, as shown in Figure 3. The interface even allows you to send arbitrary SQL queries to the database by clicking on the database name (see Figure 4).

Figure 3. The Storage tab in Chrome’s Developer Tools with some test records displayed