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
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
|
|
|
|
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). |
|
|
|
|
Return Value |
|
|
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" |
|
|
|
|
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. |
|
|
|
|
Return value: |
void |
|
Description: |
Completely deletes the currently opened database. Closes the database first if necessary. |
Attributes
|
|
|
|
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. |
|
|
|
|
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
|
|
|
|
Return value: |
boolean |
|
Description: |
Returns true if you can call data extraction methods. |
|
|
|
|
Return value: |
void |
|
Description: |
Advances to the next row of the results. |
|
|
|
|
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
|
|
|
|
Return value: |
int |
|
Description: |
Returns the number of fields in this result set. |
|
|
|
|
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. |
|
|
|
|
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. |
|
|
|
|
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>
