22. Storage
Most HTML 5–compatible browsers provide web applications with the ability to read and write key/value pairs from/to a local storage facility and to read and write data from/to a local SQL database. Neither of those capabilities is a direct functions of HTML but instead something typical browsers made available to JavaScript code running within the browser.
These capabilities were originally delivered through implementations of the W3C Web SQL Database Specification (www.w3.org/TR/webdatabase) and the W3C Web Storage API Specification (www.w3.org/TR/webstorage). The Web Storage API is still valid, but the W3C has stopped work on the Web SQL specification, and instead developers typically work with the SQLite (www.sqlite.com) database engine included with most modern smartphone browsers.
A PhoneGap application can easily leverage the browser container’s storage capabilities directly from within their applications; this is not anything PhoneGap-specific. For older mobile devices that have not implemented HTML 5 or either storage option directly, the PhoneGap team implemented the W3C Web SQL Database Specification and the Web Storage API into the PhoneGap API.
If a mobile platform includes support for these storage options, the phonegap.js file for the particular platform will just omit the objects, properties, and methods for the storage option. If not supported by the device, the PhoneGap JavaScript library will include the implementation of the storage capabilities and the appropriate supporting code to make it work on the mobile device.
Since the capabilities provided by this PhoneGap API are based upon standards that have been available for a while and are used heavily by developers today, this chapter will not cover the API in great detail.
A sample application has been created to help illustrate the features of the PhoneGap Storage API. Example 22-1 illustrates how to build a simple mileage tracker application using both Web Storage and Web SQL capabilities.
Because of the length of the application, it was not possible to include the application source code in this chapter. Relevant portions of the application code and screen shots of the application in action are shown within the chapter, but to see the completed application, code you will need to point your browser of choice to the book’s web site at www.phonegapessentials.com and look for the example project files in the Code section of the site.
Local Storage
The local storage capabilities of PhoneGap allow an application to persist data as key/value pairs stored with the application. The W3C Web Storage API includes support for both session and local storage, but the current release of PhoneGap seems to have support for the local storage option. Session storage is for transient values that are needed only during a particular session with the application; it is designed to allow the application to make use of the storage area while the application is running, but the data values stored there will be erased when the application closes. The local storage option is designed to support data that needs to be available between sessions—maintained when the application closes and available when the application launches again. The best use case for local storage would be for the storage of configuration settings for an application; this is something you would want available every time the application executed.
Data stored using local storage is maintained in key/value pairs. To write a value to local storage, an application would use the following code:
window.localStorage.setItem("key_name", value);
If the application wanted to store a value for a purge interval configuration setting for example, it would use the following code:
thePurgeInterval =
document.getElementById("purgeInterval").value;
window.localStorage.setItem("purgeInterval",
thePurgeInterval);
To retrieve a value from local storage, an application would use the following code:
purgeInterval = window.localStorage.getItem("key_name");
To retrieve the value for the purge interval configuration setting, an application would use the following code:
thePurgeInterval = window.localStorage.getItem("purgeInterval");
That’s it—that’s all there is to this part of the API. When working with an application that leverages this API, you can use the debugging capabilities of your browser to view the settings for local storage, as shown in Figure 22-1. In this example, I worked out the kinks of the application using the desktop browser and then switched over to the mobile device once I knew everything was working. In the figure, the key/value pair for purgeInterval is highlighted.
Figure 22-1 Example 22-1 running in the desktop browser debugger
SQL Database
The Storage API implements a simple database an application can read from and write to using Structured Query Language (SQL). To use this API, an application must first open the database using the following code:
theDB = window.openDatabase(db_name, db_version,
db_display_name, db_size);
In this example, the call to openDatabase simply creates a database object that exposes some methods an application can use to manipulate the database. There are no callbacks functions that need to be implemented. The openDatabase method takes the following parameters:
• db_name: The name of the database. This will be the file name for the database when it’s written to device memory.
• db_version: The version number for the database. An application can query this version number and upgrade the database schema as needed using the changeVersion method of the database object.
• db_display_name: The display name for the database.
• db_size: The amount of space allocated for the database in bytes. When allocating space, keep in mind that mobile devices may have limitations on the size of databases they can support, so allocate only the amount of space you think the application will need.
In the Example 22-1 application created for this chapter, the database is opened using the following code:
theDB = window.openDatabase("mtDB", "1.0", "Mileage Tracker",
3 * 1024 * 1024);
At this point, the application has access to a database object, and space has been allocated for the database in persistent storage on the device. From this point forward, everything you do with the database is performed using SQL statements. For this example, I allocated 3 MB of storage, although there is no way this particular application will need that much space.
Using this API, an application must wrap its SQL statements within a transaction. Transactions allow a database engine to process multiple SQL statements sequentially and recover gracefully (back to the starting point if possible) if an error occurs while they are processing the statements. Transactions are most useful when performing actions against a database that have parts that must all be executed for the action to be complete. The best example of this would be a banking transaction: When transferring money from one account to another, you will want the transaction to roll back (cancel cleanly) if the money is successfully taken from your account but then cannot be credited to another.
To create a transaction that can be used to execute one or more SQL statements against a database, an application will use the following code:
theDB.transaction(createTable, onTxError, onTxSuccess);
In this example, a function called createTable is passed to the transaction; this is the function that will execute the SQL statement used to create the database table used by the application. There are also two callback functions: the onTxError and onTxSuccess functions that are passed to the method. Please note that the callback functions are passed in a different order than they are for any other API in this book.
Note
The transaction method of the database object is the only example in this book where an error function is passed as a parameter to a method before the success callback function. In every other example in the book, the success function has always been first, followed by the error function. In this case, the success function is optional and the error function is required, so that’s why the error function comes first.
I have to admit that this caused me quite a bit of trouble as I built the sample application. Everything was working, but I couldn’t figure out why the transaction’s onError function fired every time the application wrote to the database. Yes, I had the parameters switched and listed the success function first like I had for every other example. Let my mistake save you some time: Be sure to pay attention to the order of callback functions when using this API.
Let’s talk about the callback functions before we dig into the createTable function.
The onTxError function is passed two objects. One is a transaction object, tx, which as you’ll see in a minute can be used to execute SQL statements against the database. The other is an error object that exposes an error code and error message that can be used to help identify and possibly correct the error that occurred. The following function shows how a simple function can be constructed that displays an error to the user. When this function executes, the application can assume that the transaction has rolled back and any changes that were made as part of this transaction have been discarded.
function onTxError(tx, err) {
var msgText;
if(err) {
//Tell the user what happened
msgText = "TX: " + err.message + " (" + err.code + ")";
} else {
msgText = "TX: Unknown error";
}
console.error(msgText);
alert(msgText);
}
The onTxSuccess function is simply a way to let the application know the transaction completed. In most cases, there’s really nothing to do, so you may not even implement the function in your applications. The function is not passed any values, so all the function can really do is write a status update to the console or update the application’s UI, as shown in the following example:
function onTxSuccess() {
console.log("TX: success");
}
Passed to the transaction is a function that’s executed to perform whatever updates are needed on the database. The first thing an application should do after opening a database is create or open one or more tables the application needs to store its data. Fortunately, the SQL statement that creates a table will simply open the table if it already exists. The following code is the example createTable function that is used to create the table required by the application. The function is passed a transaction object, tx, that can be used by the application to execute SQL statements, as shown here:
function createTable(tx) {
var sqlStr = 'CREATE TABLE IF NOT EXISTS MILEAGE
(tripDate INT, miles INT, notes TEXT)';
console.log(sqlStr);
tx.executeSql(sqlStr, [], onSqlSuccess, onSqlError);
}
In this example, the SQL statement creates a MILEAGE table consisting of three columns (tripDate, miles, and notes). The statement is executed through a call to tx.executeSql, which takes the following parameters:
• SQL statement: The SQL statement that will be executed against the database object the transaction is associated with
• Values: An array of values that are passed to the SQL statement (this will be described later)
• Success function: The name of the function that will be executed after the SQL statement has executed successfully
• Error function: The name of the function that will be executed if there is an error executing the SQL statement
Note
Note the order of the callback functions; in this case, the success callback precedes the error callback (as it has been for most of the APIs in this book).
The success callback function is passed two parameters: a transaction object (which can be used to execute additional SQL statements) and a results object, which contains the results of the operation. The results object exposes the following properties:
• insertId: The row ID for the row of data that was added to the table if the SQL statement executed contained an INSERT statement.
• rowAffected: The number of table rows that were changed by the SQL statement. A value of zero indicated that no rows were affected.
• rows: An object containing the rows returned by the SQL statement. The object will be empty if the SQL statement returns no rows.
The following code shows a sample success function. The function writes some information about the result set to the console and then loops through the results.
function onSqlSuccess(tx, res) {
if(res) {
console.log("Insert ID: " + res.insertID);
console.log("Row affected: " + res.rowAffected);
if (res.rows) {
var len = res.rows.length;
if(len > 0) {
for(var i = 0; i < len; i++) {
//Do something with the row data
}
} else {
alert("No records processed.");
}
}
} else {
alert("No results returned.");
}
}
The onSqlError function operates the same as the onTxError function described earlier; they both do the same thing, only at a different part of the process. The function is passed transaction and error objects, as shown in the following example:
function onSqlError(tx, err) {
var msgText;
if(err) {
msgText = "SQL: " + err.message + " (" + err.code + ")";
} else {
msgText = "SQL: Unknown error";
}
console.error(msgText);
alert(msgText);
}
After all of this processing has completed, the application displays a screen similar to the one shown in Figure 22-2. At this point, the user can start to populate the MILEAGE table with data by filling out the form shown in the figure and clicking the Save button.
Figure 22-2 Mileage tracker running on a BlackBerry Torch 9800
At this point, all the application needs to do is execute another SQL statement to add the user-provided data to the table. To do this, the application fires off another transaction, as shown in the following code:
theDB.transaction(insertRecord, onTxError, onTxSuccess);
The application’s insertRecord function does the work to add the data to the table. The following code shows a simplified version of the function that has the table row values hard-coded into the function:
function insertRecord(tx) {
var sqlStr = 'INSERT INTO MILEAGE (tripDate, miles, notes)
VALUES ("2011-12-09", 42, "Travel to Acme Dynamite")';
tx.executeSql(sqlStr, [], onSuccess, onError);
}
The application uses dynamic values as shown in the following code. The function first pulls some values from the form and then passes the values to the SQL statement. Since the application is recording date values and needs to be able to retrieve mileage records by date and ordered by date, the date value needs to be stored in the table in numeric format. To accomplish this, I used a free JavaScript library called Date.fromString() from Joey Mazzarelli (http://joey.mazzarelli.com/2008/11/25/easy-date-parsing-with-javascript) that allows the application to take the inputted date string directly into a JavaScript Date object. Once the Date object is available, the application uses the valueOf() method to get the date value in numeric format as needed.
function insertRecord(tx) {
//Create a new date object to hold the date the user entered
var tmpDate = new
Date.fromString(document.getElementById("editDate").value);
var tmpMiles = document.getElementById("editNumMiles").value;
var tmpNotes = document.getElementById("editNotes").value;
var sqlStr = 'INSERT INTO MILEAGE (tripDate, miles, notes)
VALUES (?, ?, ?)';
console.log(sqlStr);
tx.executeSql(sqlStr,
[tmpDate.valueOf(), tmpMiles, tmpNotes],
onSqlSuccess, onSqlError);
}
Remember how the call to executeSQL could take an array of values as a parameter? In the example shown, the SQL statement includes a VALUES parameter and a parenthetical group of question marks, as shown in the following example:
INSERT INTO MILEAGE (tripDate, miles, notes) VALUES (?,?,?)
Each question mark refers to a particular field value in the INSERT statement. The table row values for tripDate, miles, and notes are then passed into the SQL statement as an array of values, as shown in the following example:
tx.executeSql(sqlStr, [tmpDate.valueOf(), tmpMiles, tmpNotes],
onSqlSuccess, onSqlError);
Getting data out of the table requires yet another SQL statement. The following function fires off a transaction to query the MILEAGE table; the processing of the results of the query is done in the onQueryResults function that follows:
function openView(viewType) {
var sqlStr = "SELECT * FROM MILEAGE ORDER BY tripDate ASC";
theDB.transaction(function(tx){tx.executeSql(sqlStr, [],
onQuerySuccess, onQueryFailure);
}, onTxError, onTxSuccess);
}
In this example, I broke with one of the conventions I’ve used throughout most of the book. In general, I’ve broken out all functions in order to make the code more readable. In the openView function, the full function (not shown) does some work to create the appropriate page heading and SQL statement depending on which view was selected. The application then needed to pass the appropriate SQL statement to the transaction function, and the cleanest way to do that was just to pass in the function’s code as an anonymous function to the call to tx.executeSql. I’ve simplified the JavaScript code in the example function, showing only how to generate the SQL statement for one of the views, but when you look at the full example application, you’ll see how the use of anonymous functions makes this code simpler although harder to read.
In the onQuerySuccess function, the code takes the results returned from the execution of the SQL statement and generates a page similar to the one shown in Figure 22-3. The table column values are returned in the results array; the application uses results.rows.item(i).ColumnName to retrieve values for each column and then build the appropriate HTML content before assigning it to the page.
function onQuerySuccess(tx, results) {
if(results.rows) {
console.log("Rows: " + results.rows);
var len = results.rows.length;
if(len > 0) {
var htmlStr = "";
for(var i = 0; i < len; i++) {
var theDate = new Date(results.rows.item(i).tripDate);
htmlStr += '<b>Date:</b> ' + theDate.toDateString() +
'<br />';
var numMiles = results.rows.item(i).miles;
if(numMiles > 1) {
htmlStr += '<b>Miles:</b> ' + numMiles +
' miles<br />';
} else {
htmlStr += '<b>Miles:</b> 1 mile<br />';
}
//Check to see if there are any notes before writing
// anything to the page
var theNotes = results.rows.item(i).notes;
if(theNotes.length > 0) {
htmlStr += '<b>Notes:</b> ' + theNotes + '<br />';
}
htmlStr += '<hr />';
}
//Use JQuery's $() to assign the content to the page
$("#viewData").html(htmlStr);
//Then open the View page to display the data
$.mobile.changePage("#dataView", "slide", false, true);
} else {
alert("No rows.");
}
} else {
alert("No records match selection criteria.");
}
}
Figure 22-3 Example 22-1 displaying query results
Most of what’s covered here is related to SQL statements and how to work with database tables; there’s not much that’s really PhoneGapish. As shown, once you have the database opened, it’s all executing SQL statements and writing callback functions.
As older devices drop out of use, I expect the PhoneGap development team to simply drop this API and let applications use the native SQLite capabilities available in most modern smartphones.