Chapter 5. Client-Side Data Storage

If you know HTML, CSS, and JavaScript, you already have the tools you need to develop Android applications. This hands-on book shows you how to use these open source web standards to design and build apps that can be adapted for any Android device -- without having to use Java. Buy the print book or ebook or purchase it in iBooks.

Most software applications need to store data in some sort of persistent fashion in order to be useful. When it comes to web apps, this task has traditionally been handled either with a server-side database or cookies set in the browser. With the advent of HTML5, web developers now have a couple more options: Web Storage, and Web SQL Database.

Web Storage

Web Storage comes in two flavors—localStorage and sessionStorage—and are very similar to cookies in that they allow you to use JavaScript to set name/value pairs that you can then retrieve across multiple page reloads.

Unlike cookies, however, Web Storage data is not sent across the wire with the browser request—it lives entirely in the client. Therefore, it’s feasible to store much more data than you would want to with cookies.

Note

At the time of this writing, browser size limits for Web Storage are still in flux. However, my most recent tests indicate that the limit is right around 2.5MB.

Functionally, localStorage and sessionStorage are the same. They differ only in terms of persistence and scope:

localStorage

Data is saved even after the window is closed and is available to all windows (or tabs) that loaded from the same source (must be the same domain name, protocol, and port). This is useful for things like application preferences.

sessionStorage

Data is stored with the window object. Other windows/tabs are not aware of the values, and the data is discarded when the window/tab is closed. Useful for window-specific state like active tab highlight, or the sort order of a table.

Note

In any of the following examples, you can substitute sessionStorage anywhere you see localStorage, but remember that sessionStorage goes away when you close the window or tab.

Setting a value is as simple as:

localStorage.setItem('age', 40);

Accessing a stored value is equally simple:

var age = localStorage.getItem('age');

You can delete a specific key/value pair from storage with like so:

localStorage.removeItem('age');

Or, you can delete all key/value pairs like so:

localStorage.clear();

Assuming that your keys are valid JavaScript tokens (i.e. no spaces, no punctuation other than underscores, etc.) you can use this alternate syntax:

localStorage.age = 40 // Set the value of age
var age = localStorage.age; // Get the value of age
delete localStorage.age; // Remove age from storage
        

Note

The localStorage and sessionStorage keys are stored separately. If you use the same key name in each, they will not conflict with each other.

Saving User Settings to Local Storage

On to a practical example. Let’s update the Settings panel of the example app you started working on in Chapter 4, Animation so that it stores the form values in localStorage.

We are going to be writing a fair amount of JavaScript in this chapter, and I don’t want to jam it all in the head section of our HTML document. To keep our code organized, you need to create a file called kilo.js in the same directory as your HTML document, and update the head of your HTML document with a reference to kilo.js:

                
<head>
    <title>Kilo</title>
    <link type="text/css" rel="stylesheet" media="screen" href="jqtouch/jqtouch.css">
    <link type="text/css" rel="stylesheet" media="screen" href="themes/jqt/theme.css">
    <script type="text/javascript" src="jqtouch/jquery.js"></script>
    <script type="text/javascript" src="jqtouch/jqtouch.js"></script>
    <script type="text/javascript" src="kilo.js"></script>
</head>
                
            

Alert readers will notice that I’ve also removed the jQTouch constructor from the head of the HTML document. It’s not gone though; I just moved it into kilo.js. So be sure you’ve removed that from your main HTML file, and create the kilo.js file in the same directory with the following contents, then reload the main HTML document in your browser to make sure it’s still working:

var jQT = $.jQTouch({
    icon: 'kilo.png',
});
            

With that little bit of code reorganization out of the way, it’s time to add the code needed to save the settings. You need to override the submit action of the Settings form and replace it with a custom function called saveSettings(). Thanks to jQuery, you can accomplish this with a single line of code, which you must place in the document ready function. Add the following to kilo.js:

$(document).ready(function(){
    $('#settings form').submit(saveSettings);
});
            

The net result of this is that when the user submits the settings form, the saveSettings() function will run instead of the form actually getting submitted.

When the saveSettings() function is called, it grabs the values from the three form inputs using jQuery’s val() function and saves each in a localStorage variable of the same name. Add this function to kilo.js:

function saveSettings() {
    localStorage.age = $('#age').val();
    localStorage.budget = $('#budget').val();
    localStorage.weight = $('#weight').val();
    jQT.goBack();
    return false;
}
            

Once the values are stored, I use the jQuery goBack() function (on the second to last line) to dismiss the panel and return to the previous page. I then return false to prevent the default action of the submit event that triggers this function. Had I omitted this line, the current page would reload, which is not what we want.

At this point, a user can launch the app, navigate to the Settings panel, enter their settings, and submit the form to save their settings to localStorage.

Since we are not clearing the fields when the form is submitted, the values that the user enters will still be there when she navigates back to the Settings panel. However, this is not because the values have been saved to localStorage; it’s just because they are still just sitting there after having been typed in.

Therefore, the next time the user launches that app and navigates to the Settings panel, the fields will be empty even though they have been saved.

To remedy this, we need to load the settings using the loadSettings() function, so add the following function to kilo.js:

function loadSettings() {
    $('#age').val(localStorage.age);
    $('#budget').val(localStorage.budget);
    $('#weight').val(localStorage.weight);
}
            

The loadSettings() function is the opposite of the saveSettings() function; i.e. it uses jQuery’s val() function to set the three fields of the Settings form to the corresponding values saved in localStorage.

Now that we have a loadSettings() function, we need to trigger it. The most obvious time to do this is when the app launches. To make this happen, I simply add a line to the document ready function in kilo.js:

$(document).ready(function(){
    $('#settings form').submit(saveSettings);
    loadSettings();
});
            

Unfortunately, loading the settings only at startup leaves a loophole that occurs if the user navigates to the Settings panel, changes some values, and taps the cancel button without submitting the form.

In this case, the newly changed values will still be sitting there the next time the user visits the Settings panel; not because the values were saved (they weren’t) but because they are still just sitting there. If she closed and reopened the app, the displayed values would revert to the saved values because the loadSettings() function would refresh them at startup.

There are several ways that we could rectify this situation, but I think the most appropriate is to refresh the displayed values whenever the Settings panel begins to move, either into or out of view.

Thanks to jQTouch, this is a simple matter of binding the loadSettings() function to the pageAnimationStart event of the Settings panel. Replace the line you just added with the code shown in bold instead:

$(document).ready(function(){
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
});
            

The JavaScript contained in the kilo.js file now provides persistent data support for the Settings panel. When you view the code we’ve written to make this happen, there’s really not much to it. Here is everything in kilo.js so far:

var jQT = $.jQTouch({
    icon: 'kilo.png',
});
$(document).ready(function(){
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
});
function loadSettings() {
    $('#age').val(localStorage.age);
    $('#budget').val(localStorage.budget);
    $('#weight').val(localStorage.weight);
}
function saveSettings() {
    localStorage.age = $('#age').val();
    localStorage.budget = $('#budget').val();
    localStorage.weight = $('#weight').val();
    jQT.goBack();
    return false;
}
            

Saving the Selected Date to Session Storage

Ultimately, what I want to do is set up the Date panel such that when it’s displayed, it will check the database for any records entered for that date, and display them as an edge to edge list. This requires that the Date panel know what date was tapped on the Dates panel.

I also want to allow the user to add and delete entries from the database, so I’ll have to add support for the + button that already exists on the Date panel, and the Delete button in the Date panel entry template (more on this later).

The first step is to let the Date panel know what item was clicked when the user navigated to it from the Dates panel. With this piece of information, you can calculate the appropriate date context. To do so, you need to add some lines to the document ready function in kilo.js:

$(document).ready(function(){
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
    $('#dates li a').click(function(){1
        var dayOffset = this.id;2
        var date = new Date();3
        date.setDate(date.getDate() - dayOffset);
        sessionStorage.currentDate = date.getMonth() + 1 + '/' + 
                                     date.getDate() + '/' + 
                                     date.getFullYear();4
        refreshEntries();5
    });
});
            

1

On this line, I’m using jQuery’s click() function to bind the JavaScript code that follows to the click event of the links on the Dates panel.

2

Here, I’m grabbing the id of the clicked object and storing it in the dayOffset variable. If you recall, the links on the Dates panel have ids ranging from 0 to 5, so the id of the clicked link will correspond to the number of days needed to calculate the clicked date (i.e. 0 days in the past equals today, 1 day in the past equals yesterday, 2 days in the past equals the day before yesterday, etc.).

Note

In this context, the this keyword will contain a reference to the object that was the target of the click event.

3

On this line, I create a new JavaScript Date object and store it in a variable named date. Initially, this date will be set to the particular moment in time that it was created, so on the next line, I subtract the dayOffset from the result of the getDate() function, and then use setDate() to change the date to the selected date (a dayOffset of 0 would be today, 1 would be yesterday, and so on).

4

Here, I build a MM/DD/YYYY formatted date string and save it to sessionStorage as currentDate.

Note

The getMonth() method of the date object returns values from 0-11, January being 0. Therefore, I have to add 1 to it to generate the correct value for the formatted string.

5

Finally, I call the refreshEntries() function. The job of the refreshEntries() function is to update the incoming Date panel appropriately based on the date that was tapped on the Dates panel. For now, I’ll just set it up to update the toolbar title of the Dates panel with the selected date, so you can see it’s working. Without it, you'd just see the word “Date” as shown in Figure 5.1, “Before the refreshEntries() function, the title just says “Date”...”. Figure 5.2, “...and after the refreshEntries() function, the title reflects the selected date.” shows the refreshEntries() function in action. Add this function to kilo.js.

function refreshEntries() {
    var currentDate = sessionStorage.currentDate;
    $('#date h1').text(currentDate);
}
            

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

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

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

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

Next, we’ll move on to a more powerful and complex client-side data storage method that we’ll use to store the user’s food entries on the Date panel.

Web SQL 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.

Note

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

Developers can use standard SQL statements to create tables, insert, update, select, and delete rows, etc. 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.

Creating a Database

Now that our Date panel knows what date the user has selected, we have all the info we need to allow users 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). I’ll add some lines to kilo.js to do so:

var db;1
$(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';2
    var version = '1.0';
    var displayName = 'Kilo';
    var maxSize = 65536;
    db = openDatabase(shortName, version, displayName, maxSize);3
    db.transaction(4
        function(transaction) {5
            transaction.executeSql(6
                '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 );'
            );
        }
    );
});
            

1

The first thing to note is that I’ve added a variable named db to the global scope of the application. This variable will be used to hold a reference to the database connection once we’ve established it. I defined it in the global scope because we’re going to have to refer to it all over the place.

2

On these four lines, I’m defining some vars for the openDatabase call:

shortName

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

version

A number that you can use to manage upgrades and backwards 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 to the user in the interface. 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 think you database will grow.

Note

Database size limits are still being implemented by browser vendors at this time, but the W3C recommends an arbitrary 5MB 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 10MB. If he denies the increase, a QUOTA_ERR error will be returned. See Table 5.1, “Web Database Error Codes” for a list of database error codes.

3

With my parameters set, I call openDatabase on this line and store the connection in the db variable. If the database doesn’t already exist, it will be created.

Now that we have a database connection, we need to create an entries table if one doesn’t already exist.

4

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

5

Here, I begin an anonymous function and pass the transaction object into it. To be perfectly honest, I think passing the transaction object into its own callback function is weird (i.e. why not just use this?), but that’s what you have to do.

6

Once inside the function, I 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 your 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, then click 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 Storage tab in Chrome’s Developer Tools with some test records displayed.”. The interface even allows you to send arbitrary SQL queries to the database by clicking on the database name (see Figure 5.4, “The Storage tab in Chrome’s Developer Tools allows you to execute arbitrary SQL statements against your database.”).

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

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

Figure 5.4. The Storage tab in Chrome’s Developer Tools allows you to execute arbitrary SQL statements against your database.

The Storage tab in Chrome’s Developer Tools allows you to execute arbitrary SQL statements against your database.

Inserting Rows

Now that we have a database set up to receive some entries, we can set about building the createEntry() function. First, you have to override the submit event of the #createEntry form. You can do so by binding the createEntry() function to the submit event in the document ready function in kilo.js (here I just show the first few lines with the added line of code in bold):

$(document).ready(function(){
    $('#createEntry form').submit(createEntry);
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
    ...

Now when a user submits the #createEntry form, the createEntry() function will be called. Next, add the following to kilo.js to create the record in the database:

function createEntry() {
    var date = sessionStorage.currentDate;1
    var calories = $('#calories').val();
    var food = $('#food').val();
    db.transaction(2
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);', 
                [date, calories, food], 
                function(){
                    refreshEntries();
                    jQT.goBack();
                }, 
                errorHandler
            );
        }
    );
    return false;
}
            

1

I’m setting some variables that I’m going to use in the SQL query. If you recall (see the section called “Saving the Selected Date to Session Storage”), the date that the user tapped on the Dates panel was stored in sessionStorage.currentDate. The other two values (calories and food) are pulled out of the data entry form using the same approach that we saw earlier with the Settings form.

2

Then, I open a database transaction and run an executeSql() call. Here I am passing four parameters to the executeSql() method:

'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);'

This is the statement that will be executed. The question marks are data placeholders.

[date, calories, food]

This is an array of the values being sent to the database. They correspond by position with the data placeholder question marks in SQL statement.

function(){refreshEntries();jQT.goBack();}

This anonymous function will execute if the SQL query is successful.

errorHandler

This is the name of the function that will execute if the SQL query fails.

Note

Note that quotes (' or ") around the ? placeholders are not necessary—escaping and quoting of data is handled automatically.

Error Handling

Assuming the insert is successful, the anonymous function passed as the third parameter will be executed. It calls the refreshEntries() function (which, at the moment only updates the title of the Date panel, but soon, it will make entries you create appear in the list there), and it simulates a tap on the cancel button to dismiss the New Entry panel and return to the Date panel. As we saw earlier with the Settings panel, the cancel button does not cancel the submit action—it’s really just a back button labeled "Cancel" that isn’t shaped like a left arrow.

If the insert is not successful, the errorHandler() function will run. Add the following to the kilo.js file:

function errorHandler(transaction, error) {
    alert('Oops. Error was '+error.message+' (Code '+error.code+')');
    return true;
}
            

The error handler is passed two parameters: the transaction object and the error object. Here, I’m using the error object to alert the user to the message and error code that were thrown.

Error handlers must return true or false. When an error handler returns true (i.e. "Yes, this is a fatal error"), execution is halted and the entire transaction is rolled back. When an error handler returns false (i.e. "No, this is not a fatal error"), execution will continue.

In some cases, you might want to branch based on the type of error to decide whether you should return true or false. Table 5.1, “Web Database Error Codes” at the end of this chapter shows the (current) possible error codes according to the W3C Web SQL Database working draft specification.

You may have noticed that the error handler function accepts a transaction object in addition to the error object. It’s conceivable that in some cases you might want to execute a SQL statement inside of the error handler; perhaps to log the error or record some metadata for debugging or crash reporting purposes. The transaction object parameter allows you to make more executeSql() calls from inside the error handler, like so (this is just an example; it will not run unless you’ve created the errors table that it refers to):

function errorHandler(transaction, error) {
  alert('Oops. Error was '+error.message+' (Code '+error.code+')');
  transaction.executeSql('INSERT INTO errors (code, message) VALUES (?, ?);', 
                         [error.code, error.message]);
  return false;
}

Please take special note of the fact that I have to return false from the error handler if I want my executeSql() statement to run. If I return true (or nothing at all), the entire transaction—including this SQL statement—will be rolled back, thereby preventing the desired result.

Note

Although I won’t be doing so in my examples, you should know that you can also specify success and error handlers on the transaction method itself. This gives you a convenient location to execute code after a long series of executeSql() statements have been completed.

Oddly, the parameter order for the transaction method’s callbacks is defined to be error, then success (the reverse of the order for executeSql()). Here’s a version of the createEntry() function with transaction callbacks added toward the end (don’t add these to kilo.js because we haven’t defined either of these methods):

function createEntry() {
    var date = sessionStorage.currentDate;
    var calories = $('#calories').val();
    var food = $('#food').val();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);', 
                [date, calories, food], 
                function(){
                    refreshEntries();
                    jQT.goBack();
                }, 
                errorHandler
            );
        }, 
        transactionErrorHandler, 
        transactionSuccessHandler
    );
    return false;
}
                

Selecting Rows and Handling Result Sets

My next step is to expand the refreshEntries() function to do more than just set the title bar to the selected date. Specifically, I’m going to query the database for entries on the selected date, and then append them to the #date ul element using the hidden entryTemplate HTML for structure. It’s been a while since we looked at that code, so here’s the Date panel again (it’s already in index.html, so you don’t need to add it again):

<div id="date">
    <div class="toolbar">
        <h1>Date</h1>
        <a class="button back" href="#">Back</a>
        <a class="button slideup" href="#createEntry">+</a>
    </div>
    <ul class="edgetoedge">
        <li id="entryTemplate" class="entry" style="display:none">1
            <span class="label">Label</span>
            <span class="calories">000</span>
            <span class="delete">Delete</span>
        </li>
    </ul>
</div>
                
            

1

Recall that I had set the style attribute of the li to display: none which makes it not show up on the page. I did this so I could use that HTML snippet as a "template" for the database rows.

Here’s the complete refreshEntries() function; you must replace the existing refreshEntries() function in kilo.js with this:

function refreshEntries() {
    var currentDate = sessionStorage.currentDate;1
    $('#date h1').text(currentDate);
    $('#date ul li:gt(0)').remove();2
    db.transaction(3
        function(transaction) {
            transaction.executeSql(
                'SELECT * FROM entries WHERE date = ? ORDER BY food;',4 
                [currentDate], 5
                function (transaction, result) {6
                    for (var i=0; i < result.rows.length; i++) {
                        var row = result.rows.item(i);7
                        var newEntryRow = $('#entryTemplate').clone();8
                        newEntryRow.removeAttr('id');
                        newEntryRow.removeAttr('style');
                        newEntryRow.data('entryId', row.id);9
                        newEntryRow.appendTo('#date ul');10
                        newEntryRow.find('.label').text(row.food);
                        newEntryRow.find('.calories').text(row.calories);
                    }
                }, 
                errorHandler
            );
        }
    );
}
            

1

These two lines set the toolbar title of the Date panel to the contents of the currentDate value saved in sessionStorage.

2

On this line I’m using jQuery’s gt() function (gt stands for “greater than”) to select and remove any li elements with an index greater than 0. The first time through, this will do nothing because the only li will be the one with the id of entryTemplate, which has an index of 0 and is hidden anyhow. However, on subsequent visits to the page we need to remove any other lis before appending rows from the database again. Otherwise, items would end up appearing multiple times in the list because we’d be adding the same items over and over again.

3

On these three lines, I’m setting up a database transaction and the executeSql statement.

4

This line contains the first parameter for the executeSql statement. It’s a simple SELECT statement with a question mark acting as a data placeholder.

5

This is a single element array that contains the currently selected date. This will replace the question mark in the SQL query.

6

This anonymous function will be called in the event of a successful query. It accepts two parameters: transaction, and result.

The transaction object can be used within the success handler to send new queries to the database, as we saw with the error handler previously. However, there is no need to do that in this case, so we won’t be using it.

The result object is what we are most interested in here. It has three read only properties: rowsAffected which you can use to determine the number of rows affected by an insert, update, or delete query; insertId which returns the primary key of the last row created in an insert operation; and rows which has the records that were found.

The rows object will contain 0 or more row objects, and also has a length property that I use in the for loop on the next line.

7

On this line, I use the item() method of the rows object to set the row variable to the contents of the current row.

8

On this line, I clone() the template li and remove its id and style attributes on the next two lines. Removing the style will make make the row visible, and removing the id is important because otherwise we would end up with multiple items on the page with the same id.

9

On this line, I store the value of the row’s id property as data on the li itself (we’ll need that later if the user decides to delete the entry).

10

This is where I append the li element to the parent ul. On the next two lines, I update the label and calories span child elements of the li with the corresponding data from the row object.

With all this out of the way, our Date panel will display an li for each row in the database that corresponds to the selected date. Each row will have a label, calories, and a delete button. Once I create a few rows, you can see that we need to add a bit of CSS to style things up nicely (Figure 5.5, “The entries are showing up now, but they still need to be fancied up with some CSS.”).

Figure 5.5. The entries are showing up now, but they still need to be fancied up with some CSS.

The entries are showing up now, but they still need to be fancied up with some CSS.

Save the following CSS into a file named kilo.css (save this in the same directory as the HTML file):

#date ul li {
    position: relative;
}
#date ul li span {
    color: #FFFFFF;
    text-shadow: 0 1px 2px rgba(0,0,0,.7);
}
#date ul li .delete {
    position: absolute;
    top: 5px;
    right: 6px;
    font-size: 12px;
    line-height: 30px;
    padding: 0 3px;
    border-width: 0 5px;
    -webkit-border-image: url(themes/jqt/img/button.png) 0 5 0 5;
}            
            

Now, link to kilo.css by adding the following line to the head section of index.html:

<link type="text/css" rel="stylesheet" media="screen" href="kilo.css">

Figure 5.6. The entries with CSS applied.

The entries with CSS applied.

Although the Delete buttons now look like buttons, they won’t do anything when tapped at this point. This is because I set them up using the span tag, which is not an interactive element in an HTML page.

Deleting Rows

To make our Delete buttons do something when clicked, I need to bind a click event handler to them with jQuery. I did the same sort of thing earlier with the items on the Date panel using jQuery’s click() method.

Unfortunately, that approach won’t work in this case. Unlike the items on the Dates panel, the entries on the Date panel are not static. Which is to say that they are added and removed throughout the course of the user’s session. In fact, when the application launches, there are no entries visible on the Date panel at all. Therefore, we have nothing to bind the click to at launch.

The solution is to bind click events to the delete buttons as they are created by the refreshEntries() function. To do so, add the lines shown in bold to the end of the for loop:

    ...
    newEntryRow.find('.calories').text(row.calories);
    newEntryRow.find('.delete').click(function(){1
        var clickedEntry = $(this).parent();2
        var clickedEntryId = clickedEntry.data('entryId');3
        deleteEntryById(clickedEntryId);4
        clickedEntry.slideUp();
    });
}
            

1

The function begins by specifying that we are looking for any elements have a class of delete inside of an element that has an id of date, and calling the click() method on those elements. The click() method accepts the anonymous function that will be used to handle the event as its only parameter.

2

When the click handler is triggered, the parent of the delete button (i.e. the li) is located and stored in the clickedEntry variable.

3

On this line, I’m setting the clickedEntryId variable to the value of the entryId I stored on the li element when it was created by the refreshEntries() function.

4

On this line, I pass the clicked id into the deleteEntryById() function, and then on the next line, I use jQuery’s slideUp() method to gracefully remove the li from the page.

Add the following deleteEntryById() function to kilo.js to remove the entry from the database:

function deleteEntryById(id) {
    db.transaction(
        function(transaction) {
            transaction.executeSql('DELETE FROM entries WHERE id=?;', 
              [id], null, errorHandler);
        }
    );
}
            

As we’ve seen in previous examples, I open a transaction, pass it a callback function with the transaction object as the parameter, and call the executeSql() method. I’m passing in the SQL query, and the id of the clicked record as the first two arguments. The third argument is where the success handler would go, but I don’t need one, so I just specify null. As the fourth argument, I specify the same error handler that we’ve been using all along.

And there you have it. It may have taken a lot of description to get to this point, but in reality we haven’t had to write all that much code. In fact, kilo.js only contains about 100 lines of JavaScript (Example 5.1, “The complete JavaScript listing for Kilo database interaction.”).

Example 5.1. The complete JavaScript listing for Kilo database interaction.

var jQT = $.jQTouch({
    icon: 'kilo.png',
});
var db;
$(document).ready(function(){
    $('#createEntry form').submit(createEntry);
    $('#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);'
            );
        }
    );
});
function loadSettings() {
    $('#age').val(localStorage.age);
    $('#budget').val(localStorage.budget);
    $('#weight').val(localStorage.weight);
}
function saveSettings() {
    localStorage.age = $('#age').val();
    localStorage.budget = $('#budget').val();
    localStorage.weight = $('#weight').val();
    jQT.goBack();
    return false;
}
function createEntry() {
    var date = sessionStorage.currentDate;
    var calories = $('#calories').val();
    var food = $('#food').val();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);', 
                [date, calories, food], 
                function(){
                    refreshEntries();
                    jQT.goBack();
                }, 
                errorHandler
            );
        }
    );
    return false;
}
function refreshEntries() {
    var currentDate = sessionStorage.currentDate;
    $('#date h1').text(currentDate);
    $('#date ul li:gt(0)').remove();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'SELECT * FROM entries WHERE date = ? ORDER BY food;', 
                [currentDate], 
                function (transaction, result) {
                    for (var i=0; i < result.rows.length; i++) {
                        var row = result.rows.item(i);
                        var newEntryRow = $('#entryTemplate').clone();
                        newEntryRow.removeAttr('id');
                        newEntryRow.removeAttr('style');
                        newEntryRow.data('entryId', row.id);
                        newEntryRow.appendTo('#date ul');
                        newEntryRow.find('.label').text(row.food);
                        newEntryRow.find('.calories').text(row.calories);
                        newEntryRow.find('.delete').click(function(){
                            var clickedEntry = $(this).parent();
                            var clickedEntryId = clickedEntry.data('entryId');
                            deleteEntryById(clickedEntryId);
                            clickedEntry.slideUp();
                        });
                    }
                }, 
                errorHandler
            );
        }
    );
}
function deleteEntryById(id) {
    db.transaction(
        function(transaction) {
            transaction.executeSql('DELETE FROM entries WHERE id=?;', 
              [id], null, errorHandler);
        }
    );
}
function errorHandler(transaction, error) {
    alert('Oops. Error was '+error.message+' (Code '+error.code+')');
    return true;
}
                

What You’ve Learned

In this chapter, you learned two ways to store user data on the client: Web Storage and Web SQL database. The Web SQL database in particular opens up a world of possibilities for web-based application developers.

The only thing stopping us from running this example application in offline mode is that we have to initially connect to the web server each time the app is launched to download the HTML and related resources. Wouldn’t it be schweet if we could just cache all that stuff locally on the device?

Yeah it would.

Web Database Error Code Reference

Table 5.1. Web Database Error Codes

ConstantCodeSituation
UNKNOWN_ERR0The transaction failed for reasons unrelated to the database itself and not covered by any other error code.
DATABASE_ERR1The statement failed for database reasons not covered by any other error code.
VERSION_ERR2The operation failed because the actual database version was not what it should be. For example, a statement found that the actual database version no longer matched the expected version of the Database or DatabaseSync object, or the Database.changeVersion() or DatabaseSync.changeVersion() methods were passed a version that doesn’t match the actual database version.
TOO_LARGE_ERR3The statement failed because the data returned from the database was too large. The SQL LIMIT modifier might be useful to reduce the size of the result set.
QUOTA_ERR4The statement failed because there was not enough remaining storage space, or the storage quota was reached and the user declined to give more space to the database.
SYNTAX_ERR5The statement failed because of a syntax error, or the number of arguments did not match the number of ? placeholders in the statement, or the statement tried to use a statement that is not allowed, such as BEGIN, COMMIT, or ROLLBACK, or the statement tried to use a verb that could modify the database but the transaction was read-only.
CONSTRAINT_ERR6An INSERT, UPDATE, or REPLACE statement failed due to a constraint failure. For example, because a row was being inserted and the value given for the primary key column duplicated the value of an existing row.
TIMEOUT_ERR7A lock for the transaction could not be obtained in a reasonable time.

Site last updated on: November 17, 2010 at 11:11:58 AM PST