Class: Database

Defined in:

../coffee/api.coffee

Overview

Represents an SQLite database

Instance Method Summary

# (Database) run(sql, params) Execute an SQL query, ignoring the rows it returns.
# (Array<QueryResults>) exec(sql) Execute an SQL query, and returns the result.
# (Database) each(sql, params, callback, done) Execute an sql statement, and call a callback for each row of result.
# (Statement) prepare(sql, params) Prepare an SQL statement
# (Uint8Array) export() Exports the contents of the database to a binary array
# (void) close() Close the database, and all associated prepared statements.

Constructor Details

# (void) constructor(data)

Open a new database either by creating a new one or opening an existing one, stored in the byte array passed in first argument

Parameters:

data ( Array<Integer> ) — An array of bytes representing an SQLite database file

Instance Method Details

# (Database) run(sql, params)

Execute an SQL query, ignoring the rows it returns.

If you use the params argument, you cannot provide an sql string that contains several queries (separated by ';')

Examples:

Insert values in a table

  db.run("INSERT INTO test VALUES (:age, :name)", {':age':18, ':name':'John'});

Parameters:

sql ( String ) — a string containing some SQL text to execute
params ( Array ) — (optional) When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed.

Returns:

( Database ) — The database object (usefull for method chaining)

# (Array<QueryResults>) exec(sql)

Execute an SQL query, and returns the result.

This is a wrapper against Database.prepare, Statement.step, Statement.get, and Statement.free.

The result is an array of result elements. There are as many result elements as the number of statements in your sql string (statements are separated by a semicolon)

Each result element is an object with two properties: 'columns' : the name of the columns of the result (as returned by Statement.getColumnNames()) 'values' : an array of rows. Each row is itself an array of values

Example use

We have the following table, named test :

id	age	name
1	1	Ling
2	18	Paul
3	3	Markus

We query it like that:

var db = new SQL.Database();
var res = db.exec("SELECT id FROM test; SELECT age,name FROM test;");

res is now :

    [
         {columns: ['id'], values:[[1],[2],[3]]},
         {columns: ['age','name'], values:[[1,'Ling'],[18,'Paul'],[3,'Markus']]}
    ]

Parameters:

sql ( String ) — a string containing some SQL text to execute

Returns:

( Array<QueryResults> ) — An array of results.

# (Database) each(sql, params, callback, done)

Execute an sql statement, and call a callback for each row of result.

Currently this method is synchronous, it will not return until the callback has been called on every row of the result. But this might change.

bound to the parameters given as the second argument to the query

Examples:

Read values from a table

  db.each("SELECT name,age FROM users WHERE age >= $majority",
  				{$majority:18},
  				function(row){console.log(row.name + " is a grown-up.")}
  			);

Parameters:

sql ( String ) — A string of SQL text. Can contain placeholders that will be
params ( Array<String,Number,null,Uint8Array> ) — (optional) Parameters to bind
callback ( Function(Object) ) — A function that will be called on each row of result
done ( Function ) — A function that will be called when all rows have been retrieved

Returns:

( Database ) — The database object. Usefull for method chaining

# (Statement) prepare(sql, params)

Prepare an SQL statement

Parameters:

sql ( String ) — a string of SQL, that can contain placeholders ('?', ':VVV', ':AAA', '@AAA')
params ( Array ) — (optional) values to bind to placeholders

Throws:

( String ) — SQLite error

Returns:

( Statement ) — the resulting statement

# (Uint8Array) export()

Exports the contents of the database to a binary array

Returns:

( Uint8Array ) — An array of bytes of the SQLite3 database file

# (void) close()

Close the database, and all associated prepared statements.

The memory associated to the database and all associated statements will be freed.

Warning: A statement belonging to a database that has been closed cannot be used anymore.

Databases must be closed, when you're finished with them, or the memory consumption will grow forever