gear database api

 

Database API

The Database API provides browser-local relational data storage to your JavaScript web application. Gears uses the open source SQLite database system.

Contents

  1. Overview
  2. Example
  1. Classes
    1. Database
    2. ResultSet
  1. Location of Database Files
  1. Local Modifications to SQLite
  2. Full-Text Search

Overview

The Database API is used to persistently store an application user's data on the user's computer. Data is stored using the same-origin security policy, meaning that a web application cannot access data outside of its domain (see Security).

Data is stored and retrieved by executing SQL statements. For information on the SQL syntax supported, see the SQLite document "SQL as Understood By SQLite", and also local modifications to SQLite, below. Gears includes SQLite's full-text search extension fts2.

Security considerations

SQL statements passed to execute() can and should use bind parameters (?) to prevent SQL injection attacks. Read about database security best practices on the Security page.

Permission

This API requires user permission. If you would like to customize the default dialog, you can explicitly call google.gears.factory.getPermission() - see how.

Example

<script type="text/javascript" src="gears_init.js"></script>

<script type="text/javascript">

var db = google.gears.factory.create('beta.database');

db.open('database-test');

db.execute('create table if not exists Test' +

           ' (Phrase text, Timestamp int)');

db.execute('insert into Test values (?, ?)', ['Monkey!', new Date().getTime()]);

var rs = db.execute('select * from Test order by Timestamp desc');

while (rs.isValidRow()) {

  alert(rs.field(0) + '@' + rs.field(1));

  rs.next();

}

rs.close();

</script>

Classes

Database class

   void      open([name])

   ResultSet execute(sqlStatement, [argArray])

   void      close()

   void      remove()

   readonly attribute int lastInsertRowId

   readonly attribute int rowsAffected

ResultSet class

   boolean isValidRow()

   void    next()

   void    close()

   int     fieldCount()

   string  fieldName(int fieldIndex)

   variant field(int fieldIndex)

   variant fieldByName(string fieldName)

Database class

Methods

open([name])

 

Return value:

void

Parameters:

name - optional.

Exceptions:

Throws an exception if an error occurs.

 

Currently the name, if supplied and of length greater than zero, must consist only of visible ASCII characters excluding the following characters:

 

/ \ : * ? " < > | ; ,

 

Otherwise, open() will throw an exception. Before finalization of the API we expect to remove this restriction.

Description:

Opens the database name, or an unnamed database if name is omitted. name is local to the application's origin (see Security).

execute(sqlStatement, [argArray])

 

Return Value

ResultSet

Parameters

sqlStatement is a string containing a SQL statement, with ? as a placeholder for bind parameters.

 

argArray is an optional array of bind parameters to be substituted for the placeholders.

Exceptions

Throws an exception if the SQL statement fails to execute. See the exception object's message attribute for details.

 

Note: If multiple processes (including Workers) attempt to write to the database at the same time, one can fail. It is up to the application to retry in these situations.

Description

Substitute zero or more bind parameters from argArray into sqlStatement and execute the resulting SQL statement. There must be exactly as many items in argArray as their are ? placeholders in sqlStatement. argArray can be omitted if there are no placeholders. The results of executing the statement are returned in a ResultSet.

 

open() must be called and must return successfully before calling execute().

Example:

resultSet = db.execute (

  'INSERT INTO MYTABLE VALUES (?, ?, ?) WHERE id=?',

  [1, 2, 'three four', 5]

);

 

SQLite automatically quote-escapes bind parameters, so in execution the statement expands to:

'INSERT INTO MYTABLE VALUES (1, 2, "three four") WHERE id=5'

 

For information on SQL syntax, see the SQLite documentation "SQL as Understood By SQLite"

 

close()

 

Return value:

void

Exceptions:

Throws an exception if an error occurs.

Description:

Closes the database connection, if any, currently associated with this Database instance. Calling Database.close() is not required.

 

 

remove()

 

Return value:

void

Description:

Completely deletes the currently opened database. Closes the database first if necessary.

Attributes

lastInsertRowId

 

Return value:

readonly attribute int

Description:

Represents the rowid of the most recent insert on this Database instance.

Returns zero if no inserts have ever occurred on this instance.

 

Note that all rows in every table have a rowid, even rows in tables that do not have integer-type primary keys. Thus, every successful insert updates this attribute.

rowsAffected

 

Return value:

readonly attribute int

Description:

Represents the number of database rows that were changed, inserted, or deleted by the most recently completed INSERT, UPDATE, or DELETE statement on this Database instance.

 

Returns zero if no row changes have ever occurred on this instance.

 

Note: An unconstrained delete of all rows in a table (DELETE FROM table) will return zero rather than the number of rows that were originally present in the table; if you need the number of rows, use DELETE FROM table WHERE 1 instead, though be aware that this is slower than an unconstrained delete.

 

ResultSet class

Back to top

A ResultSet is returned from a successful call to Database.execute(). It contains the results of executing the SQL statement.

A ResultSet is immutable, subsequent changes to the underlying database do not affect the contents.

Iterate over the rows of the result set using isValidRow(), next(), and close(), calling data extraction methods for valid rows. For example:

while (rs.isValidRow()) {

  console.log(rs.fieldName(0) + " == " + rs.field(0));

  rs.next();

}

rs.close();

Methods

isValidRow()

 

Return value:

boolean

Description:

Returns true if you can call data extraction methods.

next()

 

Return value:

void

Description:

Advances to the next row of the results.

close()

 

Return value:

void

Exceptions:

Throws an exception if an error occurs.

Description:

Releases the state associated with this result set

 

You are required to call close() when you are finished with any result set.

 

Note: there is currently a feature request to have close called automatically when the result set goes out of scope.

 

Methods for extracting data

fieldCount()

 

Return value:

int

Description:

Returns the number of fields in this result set.

fieldName(int fieldIndex)

 

Return value:

string

Exceptions:

Throws an exception if fieldIndex is out of range.

Parameters:

fieldIndex : the zero-based index of the desired field

Description:

Returns the name of the specified field in the current result set. This name is derived from the SQL statement which was executed.

field(int fieldIndex)

 

Return value:

variant

Exceptions:

Throws an exception if fieldIndex is out of range.

Parameters:

fieldIndex : the zero-based index of the desired field

Description:

Returns the contents of the specified field in the current row.

fieldByName(string fieldName)

 

Return value:

variant

Exceptions:

Throws an exception if fieldName names a nonexistent field.

Parameters:

fieldName: the name of the desired field

Description:

Returns the contents of the specified field in the current row.

 

Location of Database File

Database files that your application creates are stored on the user's computer in a location that is determined by the browser being used and the platform.

Windows Vista - Internet Explorer

Location: {FOLDERID_LocalAppDataLow}\Google\Google Gears for Internet Explorer

Example: C:\Users\Bob\AppData\LocalLow\Google\Google Gears for Internet Explorer

Windows Vista - Firefox - Database files are stored in the user local profile directory.

Location: C:\Users\<username>\AppData\Local\Mozilla\Firefox\Profiles\{profile}.default\Google Gears for Firefox

Example: C:\Users\Bob\AppData\Local\Mozilla\Firefox\Profiles\uelib44s.default\Google Gears for Firefox

Windows Vista - Chrome - Database files are stored in the user local profile directory.

Location: C:\Users\<username>\AppData\Local\Google\Chrome\User Data\Default\Plugin Data\Google Gears

Example: C:\Users\Bob\AppData\Local\Google\Chrome\User Data\Default\Plugin Data\Google Gears

Windows XP - Internet Explorer - Database files are stored in the user local profile directory.

Location: C:\Documents and Settings\<username>\Local Settings\Application Data\Google\Google Gears for Internet Explorer

Example: C:\Documents and Settings\Bob\Local Settings\Application Data\Google\Google Gears for Internet Explorer

Windows XP - Firefox - Database files are stored in the user local profile directory.

Location: C:\Documents and Settings\<username>\Local Settings\Application Data\Mozilla\Firefox\Profiles\{profile}\Google Gears for Firefox

Example: C:\Documents and Settings\Bob\Local Settings\Application Data\Mozilla\Firefox\Profiles\uelib44s.default\Google Gears for Firefox

Windows XP - Chrome - Database files are stored in the user local profile directory.

Location: C:\Documents and Settings\<username>\Local Settings\Application Data\Google\Chrome\User Data\Default\Plugin Data\Google Gears

Example: C:\Documents and Settings\Bob\Local Settings\Application Data\Google\Chrome\User Data\Default\Plugin Data\Google Gears

Mac OS X - Safari - Database files are stored in the user Application Support directory.

Location: ~/Library/Application Support/Google/Google Gears for Safari

Example: /Users/Bob/Library/Application Support/Google/Google Gears for Safari

Mac OS X - Firefox - Database files are stored in the user local profile directory.

Location: Users/<username>/Library/Caches/Firefox/Profiles/{profile}.default/Google Gears for Firefox

Example: Users/Bob/Library/Caches/Firefox/Profiles/08ywpi3q.default/Google Gears for Firefox

Linux - Firefox - Database files are stored in the user home directory.

Location: ~bob/.mozilla/firefox/<firefox's profile id>/Google Gears for Firefox

Example: ~bob/.mozilla/firefox/08ywpi3q.default/Google Gears for Firefox

Windows Mobile 5.0 and 6.0

Location: \Application Data\Google\Google Gears for Internet Explorer

 

Local Modifications to SQLite

This section describes modifications that Gears makes to SQLite.

Attached databases

The SQLite ATTACH and DETACH commands can be used to open an arbitrary SQLite database on the user's disk. For this reason, these commands have been disabled in Gears. In the future this functionality may be exposed in a way which respects the same-origin security policy.

PRAGMA settings

The SQLite PRAGMA command allows setting and inspection of various platform settings, including certain settings which could potentially be used to compromise security. At this time, Gears disables PRAGMA, though it is possible that specific PRAGMA uses may be re-enabled or exposed in the Database interface.

The default PRAGMA settings for Gears which differ from the SQLite defaults:

  • PRAGMA encoding = 'UTF-8';
    This controls the encoding used to store textual data on disk. UTF-16 encoding on disk is almost never a win on desktop machines, it is generally faster to read the smaller amount of UTF-8 data and decode it on the fly.
  • PRAGMA auto_vacuum = 1;
    Over time database files can become filled with gaps where data has been deleted. These gaps can be recovered with the VACUUM command, but VACUUM can lock the database for extended periods of time, making it a challenge to integrate into interactive applications. auto_vacuum mode recovers these gaps incrementally as they are generated.
  • PRAGMA page_size = 4096;
    Desktop operating systems mostly have default virtual memory and disk block sizes of 4k and higher.
  • PRAGMA cache_size = 2048;
    Provides 8M of page cache.
  • PRAGMA synchronous = NORMAL;
    Synchronous controls whether data is synchronized to disk before COMMIT commands return (commands not in transactions are implicitly wrapped in one). Setting synchronous to OFF can provide a significant performance boost, at the expense of potential data corruption. Much of the benefit of turning synchronous off can generally be achieved by using a combination of large transactions and WorkerPool.

 

Full-Text Search

Gears includes an SQLite extension called fts2, for "Full-Text Search". fts2 allows you to create a table and search for words in TEXT data. An fts2 table is created as follows:

db.execute('CREATE VIRTUAL TABLE recipe USING fts2(dish, ingredients)');

This creates an fts2 table named 'recipe', with fields 'dish' and 'ingredients'. All fts2 fields are of type TEXT. Data in the table is manipulated using standard SQL commands such as INSERT, UPDATE, and DELETE. Like other SQLite tables, the fts2 table has an implicit unique rowid field, which acts as a unique index.

fts2 tables have some unique properties:

  • No indices other than the rowid and full-text indices are allowed.
  • fts2 tables contain a special field with the same name as the table. This field is used in search queries, as described below.
  • Because the special field exists, is important to always list the specific fields being inserted in INSERT statements, and list specific result fields in SELECT statements. SELECT * will throw exceptions when run on an fts2 table.

Full-text Query Syntax

To query using the full-text index, use the MATCH operator as follows:

<table_or_field_name> MATCH <query>

  • If <table_or_field_name> is the name of the table, then the match is done against all fields of the table.
  • If <table_or_field_name> is the name of a field, then the match is done against that field.

 

The following returns the names of all recipes which include 'tomatoes' in any field:

var rs = db.execute('SELECT dish FROM recipe WHERE recipe MATCH ?', ['tomatoes']);

Examples of fts2 query syntax:

cheese tomatoes

Find rows containing 'cheese' and 'tomatoes' in any field.

dish:stew tomatoes

Find rows with 'stew' in the dish field, and 'tomatoes' in any field.

cheese OR tomatoes

Find rows containing either 'cheese' or 'tomatoes' in any field. Note that OR operator must be capitalized.

"green tomatoes"

Find rows where 'green' is immediately followed by 'tomatoes', in any single field.

cheese -tomatoes

Find rows containing 'cheese' in any field, and not containing 'tomatoes' in any field.

ch*

Find rows containing words which start with 'ch', including rows containing 'cheese' or 'chowder'. The '*' must come at the end of the word.

Using Indices with fts2

fts2 tables are restricted to contain only TEXT fields and the full-text index. To simulate additional indices or non-TEXT fields, an auxiliary table can be used:

db.execute('CREATE TABLE recipe_aux (dish TEXT PRIMARY KEY, rating INTEGER)');

db.execute('CREATE VIRTUAL TABLE recipe USING fts2(dish, ingredients)');

A logical row is split across the recipe and recipe_aux tables, joined on the rowid. The recipe_aux table constrains dish to be unique, and adds field rating, which will not be included in the full-text index. For example, to search for recipes with 'cheese' that have rating higher than 3:

var rs = db.execute('SELECT recipe.rowid FROM recipe, recipe_aux ' +

                    ' WHERE recipe.rowid = recipe_aux.rowid AND recipe_aux.rating > ? AND recipe MATCH ?',

                    [3, 'cheese']);

Insertions, deletions, and updates should be done within transactions to keep the tables consistent:

db.execute('BEGIN');

db.execute('INSERT INTO recipe_aux (dish, rating) VALUES (?, ?)', ['soup', 3]);

db.execute('INSERT INTO recipe (rowid, dish, ingredients) ' +

           'VALUES (last_insert_rowid(), ?, ?)',

           ['soup', 'meat carrots celery noodles']);

db.execute('COMMIT');

 

源文档 <http://code.google.com/intl/zh-CN/apis/gears/api_database.html>