https://developer.apple.com/library/iad/documentation/iPhone/Conceptual/SafariJSDatabaseGuide/UsingtheJavascriptDatabase/UsingtheJavascriptDatabase.html
The sections in this chapter guide you through the basic steps of creating a
JavaScript-based application that takes advantage of the JavaScript database.
Note: This chapter covers only the JavaScript API for making SQL queries,
not the different types of queries themselves. At this level, all queries
behave similarly (except that not all queries provide any actual data to their data callbacks).
Before you can use a database or create tables within the database,
you must first open a connection to the database. When you open a database,
an empty database is automatically created if the database you request does not exist.
Thus, the processes for opening and creating a database are identical.
Open a Database
To open a database, you must obtain a database object with the
openDatabase method as follows:
Listing 4-1 Creating and opening a database
For now you should set the version number field to 1.0;
database versioning is described in more detail in Working With Database Versions.
The short name is the name for your database as stored on disk
(usually in ~/Library/Safari/Databases/). This argument controls
which database you are accessing.
The display name field contains a name to be used by the browser if it
needs to describe your database in any user interaction, such as asking
permission to enlarge the database.
The maximum size field tells the browser the size to which you expect
your database to grow. The browser normally prevents a runaway web
application from using excessive local resources by setting limits
on the size of each site’s database. When a database change would
cause the database to exceed that limit, the user is notified and
asked for permission to allow the database to grow further.
If you know that you are going to be filling the database with a lot of content,
you should specify an ample size here. By so doing, the user is only asked for
permission once when creating the database instead of every few megabytes
as the database grows.
The browser may set limits on how large a value you can specify for this field,
but the details of these limits are not yet fully defined.
Creating Tables
The remainder of this chapter assumes a database that contains a single
table with the following schema:
CREATE TABLE people(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL DEFAULT "John Doe",
shirt TEXT NOT NULL DEFAULT "Purple"
);
You can create this table and insert a few initial values with the following functions:
Listing 4-2 Creating a SQL table
Note: The errorHandler callback may be omitted in the call to executeSql if you don’t want to capture errors.
This is, of course, a fairly simple example. Things get slightly more complicated when you are
performing dependent queries, such as creating a new row in one table and inserting that row’s
ID into a field in another table to create a relationship between those rows.
For more complex examples, see the appendix.
To obtain the number of rows modified by a query, check the rowsAffected field of the result set object.
To obtain the ID of the last row inserted, check the insertId field of the result set object, then
perform the second query from within the data callback of the first query. For example:
Listing 4-5 SQL insert query example
One more issue that you may run into is multiple tables that contain columns with the same name.
Because result rows are indexed by column name, you must alias any such columns to unique
names if you want to access them. For example, the following query:
SELECT * FROM tbl_a,tbl_b ...
does not usefully allow access to tbl_a.id and tbl_b.id, but:
SELECT tbl_a.id AS tbl_a_id, tbl_b.id AS tbl_b_id, * FROM tbl_a, tbl_b ...
provides unique names for the id fields so that you can access them. The following snippet is an example of this query in actual use:
Listing 4-6 SQL query with aliased field names
function testAliases(){
var db = getDB();
if (!db) {
alert('Could not open database connection.');
}
db.transaction(
function (transaction) {
var query="SELECT tbl_a.id AS tbl_a_id, tbl_b.id AS tbl_b_id, * FROM tbl_a, tbl_b where tbl_b.name_id = tbl_a
.id;";
transaction.executeSql(query, [],
function (transaction, resultSet) {
var string = "";
for (var i=0; i
Handling Errors
You can handle errors at two levels: at the query level and at the transaction level.
Per-Query Error Callbacks
The per-query error-handling callback is rather straightforward. If the callback returns true,
the entire transaction is rolled back. If the callback returns false, the transaction continues
as if nothing had gone wrong.
Thus, if you are executing a query that is optional—if a failure of that particular query should
not cause the transaction to fail—you should pass in a callback that returns false. If a failure
of the query should cause the entire transaction to fail, you should pass in a callback that returns true.
Of course, you can also pass in a callback that decides whether to return true or false depending
on the nature of the error.
If you do not provide an error callback at all, the error is treated as fatal and causes the
transaction to roll back.
For a sample snippet, see errorHandler in Listing 4-4.
For a list of possible error codes that can appear in the error.code field, see Error Codes.
Transaction Error Callbacks
In addition to handling errors on a per-query basis (as described in Per-Query Error Callbacks),
you can also check for success or failure of the entire transaction.
For example:
Listing 4-7 Sample transaction error callback
Upon successful completion of the transaction, the success callback is called.
If the transaction fails because any portion thereof fails, the error callback is called instead.
As with the error callback for individual queries, the transaction error callback
takes an error object parameter. For a list of possible error codes that can appear in
the error.code field, see Error Codes.
Error Codes
The error codes currently defined are as follows:
0: Other non-database-related error.
1: Other database-related error.
2: The version of the database is not the version that you requested.
3: Data set too large. There are limits in place on the maximum result size that can
be returned by a single query. If you see this error, you should either use the LIMIT
and OFFSET constraints in the query to reduce the number of results returned or
rewrite the query to return a more specific subset of the results.
4: Storage limit exceeded. Either the space available for storage is exhausted or
the user declined to allow the database to grow beyond the existing limit.
5: Lock contention error. If the first query in a transaction does not modify data,
the transaction takes a read-write lock for reading. It then upgrades that
lock to a writer lock if a subsequent query attempts to
modify data. If another query takes a writer lock ahead of it, any reads prior to
that point are untrustworthy, so the entire transaction must be repeated.
If you receive this error, you should retry the transaction.
6: Constraint failure. This occurs when an INSERT, UPDATE, or REPLACE query results
in an empty set because a constraint on a table could not be met. For example, you might
receive this error if it would cause two rows to contain the same non-null value in a
column marked as the primary key or marked with the UNIQUE constraint.
Additional error codes may be added in the future as the need arises.
Working With Database Versions
To make it easier for you to enhance your application without breaking compatibility with earlier versions of
your databases, the JavaScript database supports versioning. With this support, you can modify the schema
atomically, making changes in the process of doing so.
When you open a database, if the existing version matches the version you specify, the database is opened.
Otherwise, the openDatabase call throws an exception with a value of 2. See Error Codes for more possible
exception values.
If you specify an empty string for the version, the database is opened regardless of the database version.
You can then query the version by examining the database object’s version property. For example:
Listing 4-8 Obtaining the current database version
var db = openDatabase(shortName, "", displayName, maxSize);
var version = db.version; // For example, "1.0"
Once you know what version you are dealing with, you can atomically update the database to a new
version (optionally with a modified schema or modified data) by calling the changeVersion method.
For example:
Listing 4-9 Changing database versions
function cv_1_0_2_0(transaction)
{
transaction.executeSql('alter table people rename to person', [], nullDataHandler, errorHandler);
}
function oops_1_0_2_0(error)
{
alert('oops in 1.0 -> 2.0 conversion. Error was '+error.message);
alert('DB Version: '+db.version);
return true; // treat all errors as fatal
}
function success_1_0_2_0()
{
alert("Database changed from version 1.0 to version 2.0.");
}
function testVersionChange()
{
var db = getDB();
if (!db) {
alert('Could not open database connection.');
}
if (db.changeVersion) {
alert('cv possible.');
} else {
alert('version changes not possible in this browser version.');
}
if (db.version == "1.0") {
try {
// comment out for crash recovery.
db.changeVersion("1.0", "2.0", cv_1_0_2_0, oops_1_0_2_0, success_1_0_2_0);
} catch(e) {
alert('changeversion 1.0 -> 2.0 failed');
alert('DB Version: '+db.version);
}
}
}
Note: Calling the above function renames the table people to person. If you create a page containing the
examples from this chapter, the other code will recreate the people table on the next page load, and a
second rename will fail because the person table will already exist from the previous rename.
Thus, to test this function more than once, you would have to execute the query DROP TABLE person; prior
to renaming the people table.
In some versions of Safari, the database version field does not change after a changeVersion call until
you reload the page. Usually, this is not a problem. However, it is a problem if you call the changeVersion
method more than once.
Unfortunately, the only way for your code to see the new version number is by closing the browser window.
If you get an error code 2 (see Error Codes) and the database version you passed in for the old version
matches the version in db.version, you should either assume that the version change already happened or
display an alert instructing the user to close and reopen the browser window.
A Complete Example
For a complete example of basic JavaScript database operations, see Database Example: A Simple Text Editor.