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

3. Classes
   1. Database
   2. ResultSet

4. Location of Database Files

5. Local Modifications to SQLite
6. 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

Attributes

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

Methods for extracting data

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>