cf2:WebDB

Purpose

This component provides a JavaScript API that allows web applications to store and query large sets of structured data in a database embedded within a user's browser (i.e. a client-side SQLite database) for both online and offline usage. Page authors/developers can create applications that utilize SQL for data mining, searching, sorting, etc.

The following API is based on the Web SQL Database specification.

Exported Features: cf2:WebDB

Imported Features: cf2:Class

JavaScript

The V$.webdb class provides the following method:

openDatabase(name, version, displayName, estimatedSize, creationCallback)

Creates a database object either using the existing database or creating a new one, i.e. this method will open a database if it already exists; if the database does not exist the method will create it first. The newly constructed database object represents the database with the given name.

Parameter	Description	Type
name	The name (case-sensitive) of the database to open or create.	String
version	The database version. It can be an empty string. The database version number is required in order to open the database and it needs to match the database version that the client uses. A database can only have one version at a time.	String
displayName	A textual description of the database.	String
estimatedSize	An estimated size, in bytes, of the data that will be stored in the database.	Unsigned Long
creationCallback	An optional callback object containing a method to be invoked if the database has not yet been created. It can, for example, contain table creation SQL statements. The callback, if provided, is invoked with the database having the empty string as its version regardless of the database version given by the `version` parameter.	DatabaseCallback

The V$.webdb.Database class provides the following methods:

void transaction(callback, errorCallback, successCallback)

Opens a database in read/write mode, i.e. it allows both read and write SQL statements to run on the database. A transaction is a set of operations considered as one single operation. If a transaction fails, the updates to the database are not committed. Note that this method will lock the entire database.

Parameter	Description	Type
callback	The transaction callback invoked with the `SQLTransaction` object that represents the transaction. The transaction callback executes the query.	SQLTransactionCallback
errorCallback	An optional callback object containing a method to handle transaction errors.	SQLTransactionErrorCallback
successCallback	An optional callback object containing a method to be executed when the transaction succeeds.	SQLVoidCallback

void readTransaction(callback, errorCallback, successCallback)

Opens a database in read-only mode, i.e. it allows only read SQL statements to run on the database. It imposes a shared read lock on the database.

Parameter	Description	Type
callback	The transaction callback invoked with the `SQLTransaction` object that represents the transaction. The transaction callback executes the query.	SQLTransactionCallback
errorCallback	An optional callback object containing a method to handle transaction errors.	SQLTransactionErrorCallback
successCallback	An optional callback object containing a method to be executed when the transaction succeeds.	SQLVoidCallback

void changeVersion(oldVersion, newVersion, callback, errorCallback, successCallback);

Allows scripts to atomically verify the database version number and change it at the same time as doing a schema update.

Parameter	Description	Type
oldVersion	The old database version.	String
newVersion	The new database version.	String
callback	An object containing the transaction callback invoked with the `SQLTransaction` object that represents the transaction.	SQLTransactionCallback
errorCallback	An optional callback object containing a method to be called when an SQL error occurs.	SQLTransactionErrorCallback
successCallback	An optional callback object containing a method to be called when an SQL statement is executed successfully.	SQLVoidCallback

DOMString version(): Returns the database version.

The V$.webdb.SQLTransaction class provides the following method:

void executeSql(sqlStatement, arguments, callback, errorCallback)

Executes an SQL statement with the specified parameters. This method is used for both read and write SQL statements.

Parameter	Description	Type
sqlStatement	The SQL statement to execute. The SQL specific to SQLite must be used.	String
arguments	An optional parameter containing SQL arguments represented by question marks '?'.	ObjectArray
callback	An optional callback object containing a method to process the results of queries. The callback receives the `transaction` object and the `resultSet` object.	SQLStatementCallback
errorCallback	An optional callback object containing a method to be invoked when the `executeSql()` method fails.	SQLStatementErrorCallback

The V$.webdb.SQLResultSet class provides the following methods:

long insertId(): Returns the ID of the row that the SQL statement inserted into the database, or the ID of the last row if more than one row has been inserted.

long rowsAffected(): Returns the number of rows that were changed by the SQL statement. If the statement has not changed any rows, the method returns zero.

SQLResultSetRowList rows().: Returns an SQLResultSetRowList object containing the rows returned by a SELECT statement. If the statement has not returned any rows, then the object is empty, i.e. its length is zero.

The V$.webdb.SQLResultSetRowList class provides the following methods:

long length(): The number of rows returned by the database.

any item(index)

Returns the row with the given index number. If there is no row with the index given, the method returns null.

Parameter	Description	Type
index	The index of the row to return.	Long

void forEach(f)

Iterates over rows, in the order returned by the database, invoking the f function for each row.

Parameter	Description	Type
f	The function to invoke.	Function

The V$.webdb.SQLError class provides the following methods:

short code()

Returns the value of the error code. It can have the following values:

Numeric value	Constant	Description
0	UNKNOWN_ERR	Indicates that the transaction failed for reasons unrelated to the database itself and not covered by any other error code.
1	DATABASE_ERR	The statement failed for reasons related to the database, but not covered by any other error code.
2	VERSION_ERR	Indicates that the database version does not match the expected version.
3	TOO_LARGE_ERR	The statement failed because the data returned from the database were too large.
4	QUOTA_ERR	Indicates that the statement failed because of insufficient storage space.
5	SYNTAX_ERR	Indicates that the 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, or the statement tried to modify the database but the transaction was executed in read-only mode.
6	CONSTRAINT_ERR	An `INSERT`, `UPDATE`, or `REPLACE` statement failed due to a constraint error.
7	TIMEOUT_ERR	A timeout occurred while waiting for a transaction lock.

DOMString message(): Returns an error message describing the error encountered.

Page authors can define their own callback methods to handle the corresponding events.

void handleEvent(database);

A callback method invoked by the openDatabase method if the database does not exist.

Parameter	Description	Type
database	The object representing the database.	Database

void handleEvent();: A callback method executed upon a successful transaction.

void handleEvent(transaction);

A callback method that handles transactions.

Parameter	Description	Type
transaction	The object representing the transaction.	SQLTransaction

void handleEvent(error);

A callback method that handles transaction errors.

Parameter	Description	Type
error	The object that represents the error.	SQLError

void handleEvent(transaction, resultSet)

A callback method that is called with the transaction object and the resultSet object. The resultSet object contains an array-like rows object. It provides access to the number of rows affected by the transaction, the ID of the last inserted row, and an indexed list representing the rows returned, in the order returned.

Parameter	Description	Type
transaction	The object representing the transaction.	SQLTransaction
resultSet	The object that represents the result of the statement.	SQLResultSet

void handleEvent(transaction, error)

A callback method to handle statement errors.

Parameter	Description	Type
transaction	The object representing the transaction.	SQLTransaction
error	The object that represents the error. It provides an error message describing the error encountered (via the `message` attribute), and the corresponding error code (via the `code` attribute). Please see the description of the `code()` method for the list of error codes.	SQLError

Limitations

The following restrictions/limitations apply:

MCS only supports asynchronous access to client-side databases.
MCS does not correct nor validate SQL statements. In addition, MCS does not fix any device specific issues with SQL and therefore results of executed queries may differ across devices.

In addition, the list of possible browser specific issues includes, but is not limited to:

Most browsers set the storage limit to 50MB per origin.
Most browsers require user's approval before creating databases larger than certain size (e.g. larger than 5MB).

Example

The following example illustrates the use of the API. The page displays a list of the dates when the page was opened. The information is stored in a client-side database.

<?xml version="1.0" encoding="UTF-8"?>
<html xmlns="http://www.w3.org/2002/06/xhtml2"
  xmlns:mcs="http://www.volantis.com/xmlns/2006/01/xdime/mcs"
  xmlns:cf2="http://www.volantis.com/xmlns/2009/07/cf2"
  xmlns:ui="http://www.volantis.com/xmlns/2009/07/cf2/ui"
  xmlns:sel="http://www.w3.org/2004/06/diselect">
  <head>
    <title>Web SQL Database</title>
    <mcs:script src="/scripts/webdb.mscr"/>
  </head>
  <body>
    <div>
      <sel:select>
        <sel:when expr="mcs:feature('cf2:WebDB')"> You have visited this site at: <div>
            <div id="id0">You have found a bug!</div>
          </div>
          <ui:button>
            <span style="color:red">[CLEAR]</span>
            <cf2:on event="cf2:activate"> clear(); </cf2:on>
          </ui:button>
          <div>
            <div id="id1">Errors:</div>
          </div>
        </sel:when>
        <sel:otherwise>
          <div>Web SQL Database is not supported</div>
        </sel:otherwise>
      </sel:select>
    </div>
  </body>
</html>

The webdb.mscr script policy must import the cf2:WebDB feature. The script referenced by the policy needs to contain the following JavaScript code:

var cf2db = V$.webdb.openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
// renders date read from database on page
function updateResult(tx, results) {
  var msg = "";
  results.rows().forEach(
    function(row){
      msg = msg + "<div>" + row.id+" "+row.log + "</div>";
    }
  );
  var div = V$E("id0");
  div.innerHTML = msg;
}

var sqlTransactionErrorCallback = {
  handleEvent:function(error){
    logError(error.message());
  }
}

var sqlStatementErrorCallback = {
  handleEvent:function(transaction, error){
    logError(error.message());
  }
}

var sqlStatementCallback = {
  handleEvent:function(transaction, error){
    //do nothing
  }
}

function logError(message){
  var div = V$E("id1");
  var log = document.createElement("div");
  log.innerHTML = "SQL Error: " + message;
  div.appendChild(log);
}

/**
 * Deletes all data from database
 */
function clear(){
  cf2db.transaction(
    {
      handleEvent:function(tx){
        tx.executeSql('DELETE FROM LOGS',
          [],
          {
            handleEvent:function(tx,results){
              updateResult(tx, results)
            }
          },
          sqlStatementErrorCallback
        );
      }
    },
    sqlTransactionErrorCallback
  );
}

/**
 * When entering the page:
 * 1) create database if it does not exist
 * 2) store entry date time in database
 * 3) read data from database and render it on page
 */
V$C.creation(function(c){
    //create and insert into database
  cf2db.transaction(
    {
      handleEvent:function(tx){
        tx.executeSql(
          'CREATE TABLE IF NOT EXISTS LOGS (id unique, log)',
          [],
          sqlStatementCallback,
          sqlStatementErrorCallback);
        tx.executeSql(
          'INSERT INTO LOGS (id,log) VALUES (datetime("now"),"")',
          [],
          sqlStatementCallback,
          sqlStatementErrorCallback);
        }
      },
      sqlTransactionErrorCallback
  );
  //read from database
  cf2db.transaction(
    {
      handleEvent:function(tx){
        tx.executeSql(
          'SELECT * FROM LOGS',
          [],
          {
            handleEvent:function(tx, results){
              updateResult(tx, results)
            }
          },
          sqlStatementErrorCallback
        );
      }
    },
    sqlTransactionErrorCallback
  );
});

cf2:WebDB

Purpose

JavaScript

Limitations

Example

Related topics