Web SQL是基于浏览器中嵌入的sqlite，继承了平面文件（flat-file）数据库的优点（高并发性）但缺少一些特性（存储过程（stored procs）和其它一些高端数据库特性）。
Web SQL Database is a structured database with all the functionality - and complexity - of a typical SQL-powered relational database. Indexed Database sits somewhere between the two. It has free-form key-value pairs, like Web Storage, but also the capability to index fields from those values, so searching is much faster.
- Supported on major mobile browsers (Android Browser, Mobile Safari, Opera Mobile) as well as several desktop browsers (Chrome, Safari, Opera).
- Good performance generally, being an asynchronous API. Database interaction won’t lock up the user interface. (Synchronous API is also available for WebWorkers.)
- Good search performance, since data can be indexed according to search keys.
- Robust, since it supports a transactional database model.
- Easier to maintain integrity of data, due to rigid data structure.
- Deprecated. Will not be supported on IE or Firefox, and will probably be phased out from the other browsers at some stage.
- Steep learning curve, requiring knowledge of relational databases and SQL.
Suffers from object-relational impedance mismatch.
Diminishes agility, as database schema must be defined upfront, with all records in a table matching the same structure.
The following test confirms support for WebSQL:
Calls to databases made via the WebSQL API are made asynchronously via transactions to avoid the user interface from locking up, as database interaction may occur from several windows at a time.
The three core API methods are:
openDatabase: This method creates the database object either using existing database or creating new one.
transaction: This method give us the ability to control a transaction and performing either commit or rollback based on the situation.
executeSql: This method is used to execute actual SQL query.
To create and open a database, use openDatabase()on the Window object, for example:
Above method took following five paramters:
- Database name
The second example above specifies an empty string for the version. In this case, the database opens no matter what the database version is. (An
openDatabase()call specifying the wrong version for an existing database throws an
INVALID_STATE_ERRexception.) You can then query the version by examining the database object’s version property, for example:
var version = db.version;
- Size of database
- Creation callback. You can supply a function as an optional fifth argument to serve as a callback when a database is created. It may be used to call the
changeversion()method, in which case the callback is invoked with an empty string for the database version.
Note that you don’t need to close a client-side Web SQL database when you’re done working with it.
Performing database transactions is superior to running SQL statements directly because transactions are not committed if they fail and you can undo them if needed. Transactions also allow you to handle errors using a callback. To implement a transaction, specify a callback function such as the following:
transaction() method takes one to three arguments:
- a required transaction callback, in which
- an optional transaction error callback
- an optional success callback.
executeSQL() method to specify SQL statements for read and write operations. The method protects against SQL injection and provides a callback method to process the results of any SQL queries you specify. The
executeSQL() method takes from one to four arguments:
executeSql(sqlStatement, arguments, callback, errorCallback)
- a required SQL statement
- an optional object array of arguments
- an optional SQL statement callback. This success callback takes two arguments: a
SQLTransactionobject and a
- an optional SQL statement error callback
According to the specification, the
SQLResultSet object is defined like this:
insertIdreturns the row ID of the row that the SQL statement inserted into the database (or the last one if more than one has been inserted).
rowsAffectedreturns the number of rows that were modified by the SQL statement.
SQLResultSetRowListobject. it contains the rows returned by a SELECT statement.
SQLResultSetRowList is described like this in the specification:
lengthis the number of rows returned by the database
item(index)method returns the row with the given index number. If there is no such row, then this method returns null. You can then access the different fields by doing for instance
The example below creates the database if it doesn’t exist, adds a two-column table to the database, and adds a row of data to the table:
To capture data from the user or an external source, use ? placeholders to map that data into the SQL query. This ensures the data doesn’t compromise database security, for example from SQL injection:
value are external variables, and
executeSql maps the items in the array to the ?s.
To select values from the table, use a callback to capture the results:
No fields are mapped in the above query, but to use the third argument you need to pass in an empty array as the second argument.
The SQL statement callback for
executeSQL() is called with the transaction object and a SQL statement result object. The result gives access to the ID of the last inserted row, the number of rows affected, and an indexed list representing the rows returned, in the order returned.
The result object contains an array-like rows object. It has a length, but to access individual rows you need to use
results.rows.item(i), where i is the index of the row. This returns an object representation of each row. For example, if your database has a name and an age field, the row contains a name and an age property. The value of the age field can be accessed using results.
Each database has one version at a time and multiple versions cannot exist at one time. Versions allow you to manage schema changes incrementally.
You can change the version of a client-side Web SQL database using the
changeversion() takes the following arguments: required old and new version numbers, optional SQL transaction callback, optional SQL transaction error callback, and optional success callback.
Asynchronous API errors are reported using callbacks that have a SQLError object as one of their arguments. SQLError contains a code from the table below and a localized message string.
Error codes are:
- 0 UNKNOWN_ERROR Transaction failed for reasons unrelated to the DB
- 1 DATABASE_ERROR Statement failed for DB reasons not covered by other code
- 2 VERSION_ERROR DB version doesn’t match expected version
- 3 TOO_LARGE_ERROR Data returned from DB was too large. Try the SQL LIMIT modifier.
- 4 QUOTA_ERROR Insufficient remaining storage
- 5 SYNTAX_ERROR Syntax error, argument mismatch, or unallowed statement
- 6 CONSTRAINT_ERROR An INSERT, UPDATE, or REPLACE statement failed due to a constraint error
- 7 TIMEOUT_ERROR Timeout waiting for transaction lock