window.markdeepOptions={tocStyle:"medium"} Sqlite

Contents

Sqlite
  1.1  Eval
  1.2  Oncolumn
  1.3  Query
    1.3.1  Query Options
    1.3.2  Outputs
    1.3.3  Inputs

Javascript Shell Interpreter
 DOWNLOAD / DEVELOP / DEPLOY

Types Builtin Reference  Index  Fossil  Demos Log Test Debug Misc

   

Sqlite

The Sqlite driver (and MySql) provides various ways to execute sql:

Here is an example:

var r, age, db  = new Sqlite('testsql.db');
db.eval('CREATE TABLE players(name TEXT,age INTEGER);');
db.query('INSERT INTO players VALUES(?,?)', {values:["Barry",44]});
age = db.onecolumn('SELECT age FROM players WHERE name = "Barry"');
r = db.query('SELECT * FROM players');
puts(r[0].name, r[0].age);

Options passed in the object argument to new Sqlite(), may specify any of the following:

Option Type Description Default
bindWarn BOOL Treat failed variable binds as a warning. false
debug CUSTOM Enable debug trace for various operations.
queryOpts CUSTOM Default options for exec.
forceInt CUSTOM Bind float as int if possible.
maxStmts INT Max cache size for compiled statements.
mutex CUSTOM Mutex type to use.
name DSTRING Name for this db handle.
nocreate BOOL Database is must already exist. false
readonly BOOL Database is readonly. false
vfs VALUE VFS to use.

Some options can later be changed using the conf() method, eg.

db.conf({maxStmts:100});

See tests/sqlite.jsi for a more complete example.

   

Eval

The eval() method is used to execute simple Sql. It takes no options, and returns number of rows changed (sqlite3_changed).

It can also be used to execute multiple semicolon-separated statements:

db.exec('CREATE TABLE foo(a,b);'+
'INSERT INTO foo VALUES(1,2);'+
'INSERT INTO foo VALUES("X","Y")');

This makes it useful for bulk loading.

   

Oncolumn

onecolumn() provides no inputs or outputs. It simply returns the first column of the first row. The mode and other options are ignored.

var maxid = db.oncolumn('SELECT max(id) FROM foo');
   

Query

The workhorse method is query() which:

Here is an example:

var x = db.query('SELECT * FROM foo');
   

Query Options

Query options can be controlled either of two ways. Per query, as in:

db.query('SELECT * FROM test1', {mode:'json'});

or we can change the defaults (for the connection) like so:

db.conf({queryOpts:{mode:'json'}});
db.query('SELECT * FROM test1');
db.query('SELECT * FROM test2');

Here is a list of the available query() options:

Option Type Description Default
callback FUNC Function to call with each row result.
cdata STRKEY Name of Cdata array object to use.
headers BOOL First row returned contains column labels.
limit INT Maximum number of returned values.
mapundef BOOL In variable bind, map an 'undefined' var to null.
mode CUSTOM Set output mode of returned data.
nocache BOOL Query is not to be cached.
nullvalue STRKEY Null string output (for non js/json mode).
retChanged BOOL Query returns value of sqlite3_changed().
separator STRKEY Separator string (for csv and text mode).
typeCheck CUSTOM Type check mode. warn
table STRKEY Table name for mode=insert.
values ARRAY Values for ? bind parameters.
varName STRBUF Array var for ? bind parameters.
width CUSTOM In column mode, set column widths.

   

Outputs

The returned value from a query is determined by the chosen output mode.

The default mode (rows) just returns an array of objects, which looks like this:

[ { a:1, b:2 }, { a:"X", b:"Y" } ]

The choices for mode are a superset of those available in the sqlite3 command-line tool, namely:

Mode Description Purpose
array1d Flat array of values script
arrays An array of row-arrays script
column Column aligned text text
csv Comma (or separator) separated values export
html Html table rows browser
insert Sql insert statements export
json JSON string as an array of objects browser
json2 JSON string with names/values in separate arrays browser
line One value per line in name=value form export
list The default sqlite3 output text
none No output
rows An array of row-objects (the default) script
tabs Tab separator delineated values script

We can change the output mode for a query() using:

db.query('SELECT * FROM foo', {mode:'list'});
1|2|X Y|3|Z

Output for some modes is affected by the headers and separator options.

   

JSON

The json modes are useful when data is destined to be sent to a web browser, eg. via websockets.

db.exec('DROP TABLE IF EXISTS foo; CREATE TABLE foo(a,b);');
var n = 0, x = 99;
while (n++ < 3) {
    db.query('INSERT INTO foo VALUES(@x,@n)');
    x -= 4;
}
x=db.query('SELECT * FROM foo',{mode:'json'});
[ {"a":99, "b":1}, {"a":95, "b":2}, {"a":91, "b":3} ]

Where large amounts of data are involved, the headers option can be used to reduce size:

db.query('SELECT * FROM foo',{mode:'json', headers:true});
[ ["a", "b"], [99, 1], [95, 2], [91, 3] ]

The “json2" mode is used to split headers and values out into separate members:

db.query('SELECT * FROM foo',{mode:'json2'});
{ "names": [ "a", "b" ], "values": [ [99, 1 ], [95, 2 ], [91, 3 ] ] }
   

Callback Function

Normally, query() will execute an entire query before returning the result. There are two ways to change this:

Either way this results in invocation of the callback for each row result:

function myfunc(n) { puts("a=" + n.a + ", b=" + n.b); }
db.query('SELECT * FROM foo',myfunc);

If the callback function returns false, evaluation will terminate.

db.query('SELECT * FROM foo', function (n) {
    puts("a=" + n.a + ", b=" + n.b);
    if (a>1) return false;
  });
   

Inputs

Sql inputs can be easily formatted using strings:

var a=1, b='big';
db.query('INSERT INTO foo VALUES('+a+*','*+b+')');

However this raises issues of security and predictability. Fortunately variable binding is easy.

   

Bindings

Sqlite variable binding uses "?” placeholders to refer to array elements., eg:

db.query('INSERT INTO foo VALUES(?,?)', {values:[11,12]});

var vals = [9,10];
db.query('INSERT INTO foo VALUES(?,?)', {values:vals});

which for a small number of parameters is more than adequate.

   

Named-Binds

Sqlite named-bindings begin with the characters: :, @, and $.

Here is an example:

var x1=24.5, x2="Barry", x3="Box";
db.query('INSERT INTO test2 VALUES( :x1, @x2, $x3 );');

The $ bind may append round-brackets () to refer to compound variables.

This example binds to objects members:

var y = {a:4, b:"Perry", c:"Pack"};
db.query('INSERT INTO test2 VALUES( $y(a), $y(b), $y(c) );');

And this one to arrays:

var Z = 2;
var y = [9, 'Figgy', 'Fall'];
db.query('INSERT INTO test2 VALUES( $y(0), $y(1), $y([Z]) );');

Or more usefully:

var y = [
    {a:4, b:"Perry", c:"Pack"},
    {a:9, b:'Figgy', c:'Fall'}
];
for (var i=0; i < y.length; i++)
    db.query('INSERT INTO test2 VALUES($y([i].a), $y([i].b), $y([i].c);');

The contents of the round-brackets can contain multiple levels of dereference (but not expressions).

Here is a selection of bindings, and their variables:

Binding Variable Comment
:X X
@X X
$X X
$X(a) X.a Implicit object member
$X(9) X[9] Implicit array (leading digits)
$X([a]) X[a] Explicit array
$X(a.b) X.a.b Compound object
$X([a].b) X[a].b Compound array + object, etc

   

Types

A type specifier may also be included in a $X(Y) binding, as in:

var y = {a:4, b:"Purry", c:"Pax"};
db.query('INSERT INTO test2 VALUES( $y(a:integer), $y(b:string), $y(c:string) );');

The type is the part after the colon ":”, and just before the close round-brace.

By default, a type is used to convert data sent to MySql to the correct type.

Type specifiers are supported for all variants of \(X(Y) binding, such as:

var Z = 0;
var x = ['Figgy'];
var y = {c:'Fall'};
db.query('INSERT INTO test3 VALUES( $x(0:string), $y(c:string), $x([Z]:string) );');

The supported type names are: | Type | Description | |-----------|-----------------------| | bool | A tiny/bit value | | double | A double value | | integer | A 64 bit wide integer | | string | A string | | blob | A blob | | date | A date value | | datetime | A date+time value | | time | A time value | | timestamp | A unix timestamp | We can also change the type-checking behaviour via the typeCheck query option: For example, we can instead cause an error to be kicked an error with:

var x = [ 'bad' ];
db.query('UPDATE test SET n = $x(0:number) );', {typeCheck:'error'});

The valid typeCheck modes are: | Value | Description | |---------|--------------------------------------------------| | convert | Coerce value to the requested type (the default) | | warn | Generate a warning | | error | Generate an error | | disable | Ignore type specifiers | Miscellaneous ---- ### User Functions SQL functions can be defined in javascript using func():

db.func('bar',function(n) { return n+'.000'; });
puts(db.onecolumn('SELECT bar(a) FROM foo WHERE b == 2;'));

### Timestamps Sqlite performs internal time calculations based on the Gregorian calendar. But Javascript time functions use the unix epoch to store the number of milliseconds since Jan 1, 1970 UTC. We can create a table with a DEFAULT date field as a number using:

CREATE TABLE mtable(
  name,
  mytime DATETIME DEFAULT(round((julianday('now') - 2440587.5)*86400000.0))
);

We can output this as a text time stamp using:

SELECT strftime('%Y-%m-%d %H:%M:%f',mytime/1000.0,'unixepoch') FROM mytable;
SELECT strftime('%Y-%m-%d %H:%M:%f',mytime/1000.0,*'unixepoch'*,'localtime') FROM mytable;
2015-01-12 13:46:40.252 2015-01-12 18:46:40.252

### Caching In the interest of efficiency, compiled queries are cached on a per connection basis. The size of the cache is controlled by the maxStmts option. You can also disable caching for individual querys with nocache. And any query begining in ';' will not be cached. ### Building The Sqlite driver comes (by default) builtin to Jsi. It can also be built the shared library can be built (for unix) with:

make libmysql

C-API ---- See [DbQuery](DBQuery) for a Sqlite C-API. var startOfMarkDeep=true; window.alreadyProcessedMarkdeep||(document.body.style.visibility='visible'); document.title=location.pathname.match(/\/([\w]+)[^\/]*\)/)[1];

formatted by Markdeep 1.03