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 + '/' +

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 );'

O The first thing to note is there is a variable named db in the global scope of the application. This variable is to hold a reference to the database connection once we've established it. It is defined in the global scope because we're going to have to refer to it all over the place.

© These four lines define some vars for the openDatabase call: shortName

A string that will refer to the database file on disk. version

A number for managing upgrades and backward compatibility when you need to change your database schema (i.e., check the database version on app launch—if it's old, create the new database and migrate the data from one to the other). displayName

A string that will be presented in the interface to the user. For example, the display name appears in the Storage tab of the Developer Tools in Chrome desktop (View^-Developer^-Developer Tools).

maxSize

The maximum number of kilobytes to which you will allow your database to grow.

Database size limits are still being implemented by browser vendors at this time, but the W3C recommends an arbitrary 5 MB limit per origin. If your database grows beyond the limit, the user will automatically be asked to allow or deny the size increase. If he allows the increase, the database size limit will be upped to 10 MB. If he denies the increase, a QUOTA_ERR error will be returned. See Table 5-1 for a list of database error codes.

© With the parameters set, this line calls openDatabase and stores the connection in the db variable. If the database doesn't already exist, it will be created.

O All database queries must take place in the context of a transaction, so we begin one here by calling the transaction method of the db object. The remaining lines make up a function that is sent to the transaction as the sole parameter.

© This line begins an anonymous function and passes the transaction object into it. To be perfectly honest, I think passing the transaction object into its own callback function is weird (why not just use this?), but that's what you have to do.

© Once inside the function, we call the executeSql method of the transaction object to execute a standard CREATE TABLE query. The IF NOT EXISTS clause prevents the table from being created if it already exists.

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 clientside 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 5-3. The interface even allows you to send arbitrary SQL queries to the database by clicking on the database name (see Figure 5-4).

Figure 5-3. The Storage tab in Chrome's Developer Tools with some test records displayed
+2 0

Post a comment