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>

100 Vim commands every programmer should know

 

Since the 70's, Vi is one of the programmer's best friend. Nevermind you're new to Vi or not, here's a big list of 100 useful commands, organized by topic, which will make your coder life better.

Basics

:e filename

Open filename for edition

:w

Save file

:q

Exit Vim

:w!

Exit Vim without saving

Search

/word

Search word from top to bottom

?word

Search word from bottom to top

/jo[ha]n

Search john or joan

/\< the

Search the, theatre or then

/the\>

Search the or breathe

/\< the\>

Search the

/\< ¦.\>

Search all words of 4 letters

/\/

Search fred but not alfred or frederick

/fred\|joe

Search fred or joe

/\<\d\d\d\d\>

Search exactly 4 digits

/^\n\{3}

Find 3 empty lines

:bufdo /searchstr/

Search in all open files

Replace

:%s/old/new/g

Replace all occurences of old by new in file

:%s/old/new/gw

Replace all occurences with confirmation

:2,35s/old/new/g

Replace all occurences between lines 2 and 35

:5,$s/old/new/g

Replace all occurences from line 5 to EOF

:%s/^/hello/g

Replace the begining of each line by hello

:%s/$/Harry/g

Replace the end of each line by Harry

:%s/onward/forward/gi

Replace onward by forward, case unsensitive

:%s/ *$//g

Delete all white spaces

:g/string/d

Delete all lines containing string

:v/string/d

Delete all lines containing which didnt contain string

:s/Bill/Steve/

Replace the first occurence of Bill by Steve in current line

:s/Bill/Steve/g

Replace Bill by Steve in current line

:%s/Bill/Steve/g

Replace Bill by Steve in all the file

:%s/\r//g

Delete DOS carriage returns (^M)

:%s/\r/\r/g

Transform DOS carriage returns in returns

:%s#<[^>]\+>##g

Delete HTML tags but keeps text

:%s/^\(.*\)\n\1$/\1/

Delete lines which appears twice

Ctrl+a

Increment number under the cursor

Ctrl+x

Decrement number under cursor

ggVGg?

Change text to Rot13

Case

Vu

Lowercase line

VU

Uppercase line

g~~

Invert case

vEU

Switch word to uppercase

vE~

Modify word case

ggguG

Set all text to lowercase

:set ignorecase

Ignore case in searches

:set smartcase

Ignore case in searches excepted if an uppercase letter is used

:%s/\<./\u&/g

Sets first letter of each word to uppercase

:%s/\<./\l&/g

Sets first letter of each word to lowercase

:%s/.*/\u&

Sets first letter of each line to uppercase

:%s/.*/\l&

Sets first letter of each line to lowercase

Read/Write files

:1,10 w outfile

Saves lines 1 to 10 in outfile

:1,10 w >> outfile

Appends lines 1 to 10 to outfile

:r infile

Insert the content of infile

:23r infile

Insert the content of infile under line 23

File explorer

:e .

Open integrated file explorer

:Sex

Split window and open integrated file explorer

:browse e

Graphical file explorer

:ls

List buffers

:cd ..

Move to parent directory

:args

List files

:args *.php

Open file list

:grep expression *.php

Returns a list of .php files contening expression

gf

Open file name under cursor

Interact with Unix

:!pwd

Execute the pwd unix command, then returns to Vi

!!pwd

Execute the pwd unix command and insert output in file

:sh

Temporary returns to Unix

$exit

Retourns to Vi

Alignment

:%!fmt

Align all lines

!}fmt

Align all lines at the current position

5!!fmt

Align the next 5 lines

Tabs

:tabnew

Creates a new tab

gt

Show next tab

:tabfirst

Show first tab

:tablast

Show last tab

:tabm n(position)

Rearrange tabs

:tabdo %s/foo/bar/g

Execute a command in all tabs

:tab ball

Puts all open files in tabs

Window spliting

:e filename

Edit filename in current window

:split filename

Split the window and open filename

ctrl-w up arrow

Puts cursor in top window

ctrl-w ctrl-w

Puts cursor in next window

ctrl-w_

Maximise current window

ctrl-w=

Gives the same size to all windows

10 ctrl-w+

Add 10 lines to current window

:vsplit file

Split window vertically

:sview file

Same as :split in readonly mode

:hide

Close current window

:­nly

Close all windows, excepted current

:b 2

Open #2 in this window

Auto-completion

Ctrl+n Ctrl+p (in insert mode)

Complete word

Ctrl+x Ctrl+l

Complete line

:set dictionary=dict

Define dict as a dictionnary

Ctrl+x Ctrl+k

Complete with dictionnary

Marks

mk

Marks current position as k

˜k

Moves cursor to mark k

d™k

Delete all until mark k

Abbreviations

:ab mail mail@provider.org

Define mail as abbreviation of mail@provider.org

Text indent

:set autoindent

Turn on auto-indent

:set smartindent

Turn on intelligent auto-indent

:set shiftwidth=4

Defines 4 spaces as indent size

ctrl-t, ctrl-d

Indent/un-indent in insert mode

>>

Indent

<<

Un-indent

Syntax highlighting

:syntax on

Turn on syntax highlighting

:syntax off

Turn off syntax highlighting

:set syntax=perl

Force syntax highlighting

 

从 <http://www.catswhocode.com/blog/100-vim-commands-every-programmer-should-know> 插入