Main
Language
Runtime
Tools
FAQs
Changes
Download
Licence
cheesy

The Idle-SQLite module

This is the (very) preliminary documentation for the Idle-SQLite3 database module. This module allows you to create and access SQLite3 databases. After a require('sqlite3') the exported functions are called with prefix sqlite3. However, most sqlite3 functions are called via an object-oriented interface to either database or SQL statement objects; see below for details.

This documentation does not attempt to describe how SQLite3 itself works, it just describes the Idle binding and the available functions. For more information about the SQL features supported by SQLite3 and details about the syntax of SQL statements and queries, please see the SQLite3 documentation. Using some of the advanced features (the use of callbacks, for instance) will require some familiarity with the SQLite3 API.

Note: all scripts that use the sqlite3 module will try to load idlesql.dll at runtime: this library contains the database engine and the Idle bindings. Please make sure that this DLL is found somewhere in the PATH.

Back to the main Idle Runtime library documentation.

SQLite3 functions
top of page

sqlite3.complete(sql)

Returns true if the string sql comprises one or more complete SQL statements and false otherwise.

sqlite3.open(filename)
top of page

Opens (or creates if it does not exist) an SQLite database with name filename and returns its handle as userdata (the returned object should be used for all further method calls in connection with this specific database, see Database methods). Example:

myDB=sqlite3.open('MyDatabase.sqlite3')  -- open
-- do some database calls...
myDB:close()  -- close

In case of an error, the function returns nil, an error code and an error message.

sqlite3.open_memory()
top of page

Opens an SQLite database in memory and returns its handle as userdata. In case of an error, the function returns nil, an error code and an error message. (In-memory databases are volatile as they are never stored on disk.)

sqlite3.temp_directory([temp])
top of page

Sets or queries the directory used by SQLite for temporary files. If string temp is a directory name or nil, the temporary directory is set accordingly and the old value is returned. If temp is missing, the function simply returns the current temporary directory.

sqlite3.version()
top of page

Returns a string with SQLite version information, in the form 'x.y[.z]'.

Database methods
top of page

After opening a database with sqlite3.open() or sqlite3.open_memory() the returned database object should be used for all further method calls in connection with that database. An open database object supports the following methods.

db:busy_handler([func[,udata]])
top of page

Sets or removes a busy handler for a database. func is either an Idle function that implements the busy handler or nil to remove a previously set handler. This function returns nothing.

The handler function is called with two parameters: udata and the number of (re-)tries for a pending transaction. It should return nil, false or 0 if the transaction is to be aborted. All other values will result in another attempt to perform the transaction. (See the SQLite documentation for important hints about writing busy handlers.)

db:busy_timeout(t)
top of page

Sets a busy handler that waits for t milliseconds if a transaction can not proceed. Calling this function will remove any busy handler set by db:busy_handler(); calling it with an argument less than or equal to 0 will turn off all busy handlers.

db:changes()
top of page

This function returns the number of database rows that were changed (or inserted or deleted) by the most recent SQL statement. Only changes that are directly specified by INSERT, UPDATE, or DELETE statements are counted. Auxiliary changes caused by triggers are not counted. Use db:total_changes() to find the total number of changes.

db:close()
top of page

Closes a database. All SQL statements prepared using db:prepare() should have been finalised before this function is called. The function returns sqlite3.OK on success or else a numerical error code (see the list of Numerical error and result codes).

db:close_vm(temponly)
top of page

Finalises all statements that have not been explicitly finalised. If temponly is true, only internal, temporary statements are finalised. This function returns nothing.

db:create_aggregate(name,nargs,step,final)
top of page

This function creates an aggregate callback function. Aggregates perform an operation over all rows in a query. name is a string with the name of the aggregate function as given in an SQL statement; nargs is the number of arguments this call will provide. step is the actual Idle function that gets called once for every row; it should accept a function context (see Methods for callback contexts) plus the same number of parameters as given in nargs. final is a function that is called once after all rows have been processed; it receives one argument, the function context.

The function context can be used inside the two callback functions to communicate with SQLite3. Here is a simple example:

db:exec[=[
  CREATE TABLE numbers(num1,num2);
  INSERT INTO numbers VALUES(1,11);
  INSERT INTO numbers VALUES(2,22);
  INSERT INTO numbers VALUES(3,33);
]=]

local num_sum=0
local function oneRow(context,num)
  -- add one column in all rows
  num_sum=num_sum+num
end

local function afterLast(context)
  -- return sum after last row has been processed
  context:result_number(num_sum)
  num_sum=0
end

db:create_aggregate("do_the_sums",1,oneRow,afterLast)
for sum in db:urows('SELECT do_the_sums(num1) FROM numbers') do
  print("Sum of col 1:",sum)
end
for sum in db:urows('SELECT do_the_sums(num2) FROM numbers') do
  print("Sum of col 2:",sum)
end

This prints:

Sum of col 1:   6
Sum of col 2:   66

db:create_collation(name,func)
top of page

This creates a collation callback. A collation callback is used to establish a collation order, mostly for string comparisons and sorting purposes. name is a string with the name of the collation to be created; func is a function that accepts two string arguments, compares them and returns 0 if both strings are identical, -1 if the first argument is lower in the collation order than the second and 1 if the first argument is higher in the collation order than the second. A simple example:

local function collate(s1,s2)
  s1=s1:lower()
  s2=s2:lower()
  if s1==s2 then return 0
  elseif s1<s2 then return -1
  else return 1 end
end

db:exec[=[
  CREATE TABLE test(id INTEGER PRIMARY KEY,content COLLATE CINSENS);
  INSERT INTO test VALUES(NULL,'hello world');
  INSERT INTO test VALUES(NULL,'Buenos dias');
  INSERT INTO test VALUES(NULL,'HELLO WORLD');
]=]
db:create_collation('CINSENS',collate)
for row in db:nrows('SELECT * FROM test') do print(row.id,row.content) end

db:create_function(name,nargs,func)
top of page

This function creates a callback function. This callback is called by SQLite3 once for every row in a query. name is a string with the name of the callback function as given in an SQL statement; nargs is the number of arguments this call will provide. func is the actual Idle function that gets called once for every row; it should accept a function context (see Methods for callback contexts) plus the same number of parameters as given in nargs. Here is an example:

db:exec'CREATE TABLE test(col1,col2,col3)'
db:exec'INSERT INTO test VALUES(1,2,4)'
db:exec'INSERT INTO test VALUES(2,4,9)'
db:exec'INSERT INTO test VALUES(3,6,16)'

db:create_function('sum_cols',3,function(ctx,a,b,c)
  ctx:result_number(a+b+c)
end))

for col1,col2,col3,sum in
    db:urows('SELECT *,sum_cols(col1,col2,col3) FROM test') do
  util.printf('%2i+%2i+%2i=%2i\n',col1,col2,col3,sum)
end

db:errcode() or db:error_code()
top of page

Returns the numerical result code (or extended result code) for the most recent failed call associated with database db. See Numerical error and result codes for details.

db:errmsg() or db:error_message()
top of page

Returns a string that contains an error message for the most recent failed call associated with database db.

db:exec(sql[,func[,udata]]) or db:execute(sql[,func[,udata]])
top of page

Compiles and executes the SQL statement(s) given in string sql. The statements are simply executed one after the other and not stored. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

If one or more of the SQL statements are queries, then the callback function specified in func is invoked once for each row of the query result (if func is nil, no callback is invoked). The callback receives four arguments: udata (the third parameter of the db:exec() call), the number of columns in the row, a table with the column values and another table with the column names. The callback function should return 0. If the callback returns a non-zero value then the query is aborted, all subsequent SQL statements are skipped and db:exec() returns sqlite3.ABORT. Here is a simple example:

sql=[=[
  CREATE TABLE numbers(num1,num2,str);
  INSERT INTO numbers VALUES(1,11,"ABC");
  INSERT INTO numbers VALUES(2,22,"DEF");
  INSERT INTO numbers VALUES(3,33,"UVW");
  INSERT INTO numbers VALUES(4,44,"XYZ");
  SELECT * FROM numbers;
]=]

function showrow(udata,cols,values,names)
  assert(udata=='test_udata')
  print('exec:')
  for i=1,cols do print('',names[i],values[i]) end
  return 0
end

db:exec(sql,showrow,'test_udata')

db:interrupt()
top of page

This function causes any pending database operation to abort and return at the next opportunity. This function returns nothing.

db:isopen()
top of page

Returns true if database db is open, false otherwise.

db:last_insert_rowid()
top of page

This function returns the rowid of the most recent INSERT into the database. If no inserts have ever occurred, 0 is returned. (Each row in an SQLite table has a unique 64-bit signed integer key called the 'rowid'. This id is always available as an undeclared column named ROWID, OID, or _ROWID_. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.)

If an INSERT occurs within a trigger, then the rowid of the inserted row is returned as long as the trigger is running. Once the trigger terminates, the value returned reverts to the last value inserted before the trigger fired.

db:nrows(sql)
top of page

Creates an iterator that returns the successive rows selected by the SQL statement given in string sql. Each call to the iterator returns a table in which the named fields correspond to the columns in the database. Here is an example:

db:exec[=[
  CREATE TABLE numbers(num1,num2);
  INSERT INTO numbers VALUES(1,11);
  INSERT INTO numbers VALUES(2,22);
  INSERT INTO numbers VALUES(3,33);
]=]
for a in db:nrows('SELECT * FROM numbers') do table.print(a) end

This script prints:

num2: 11
num1: 1
num2: 22
num1: 2
num2: 33
num1: 3

db:prepare(sql)
top of page

This function compiles the SQL statement in string sql into an internal representation and returns this as userdata. The returned object should be used for all further method calls in connection with this specific SQL statement (see Methods for prepared statements).

db:progress_handler(n,func,udata)
top of page

This function installs a callback function func that is invoked periodically during long-running calls to db:exec() or stmt:step(). The progress callback is invoked once for every n internal operations, where n is the first argument to this function. udata is passed to the progress callback function each time it is invoked. If a call to db:exec() or stmt:step() results in fewer than n operations being executed, then the progress callback is never invoked. Only a single progress callback function may be registered for each opened database and a call to this function will overwrite any previously set callback function. To remove the progress callback altogether, pass nil as the second argument.

If the progress callback returns a result other than 0, then the current query is immediately terminated, any database changes are rolled back and the containing db:exec() or stmt:step() call returns sqlite3.INTERRUPT. This feature can be used to cancel long-running queries.

db:rows(sql)
top of page

Creates an iterator that returns the successive rows selected by the SQL statement given in string sql. Each call to the iterator returns a table in which the numerical indices 1 to n correspond to the selected columns 1 to n in the database. Here is an example:

db:exec[=[
  CREATE TABLE numbers(num1,num2);
  INSERT INTO numbers VALUES(1,11);
  INSERT INTO numbers VALUES(2,22);
  INSERT INTO numbers VALUES(3,33);
]=]
for a in db:rows('SELECT * FROM numbers') do table.print(a) end

This script prints:

1: 1
2: 11
1: 2
2: 22
1: 3
2: 33

db:total_changes()
top of page

This function returns the number of database rows that have been modified by INSERT, UPDATE or DELETE statements since the database was opened. This includes UPDATE, INSERT and DELETE statements executed as part of trigger programs. All changes are counted as soon as the statement that produces them is completed by calling either stmt:reset() or stmt:finalize().

db:trace(func,udata)
top of page

This function installs a trace callback handler. func is an Idle function that is called by SQLite3 just before the evaluation of an SQL statement. This callback receives two arguments: the first is the udata argument used when the callback was installed; the second is a string with the SQL statement about to be executed.

db:urows(sql)
top of page

Creates an iterator that returns the successive rows selected by the SQL statement given in string sql. Each call to the iterator returns a list of items which correspond to the columns in the currently selected row. Here is an example:

db:exec[=[
  CREATE TABLE numbers(num1,num2);
  INSERT INTO numbers VALUES(1,11);
  INSERT INTO numbers VALUES(2,22);
  INSERT INTO numbers VALUES(3,33);
]=]
for num1,num2 in db:urows('SELECT * FROM numbers') do print(num1,num2) end

This script prints:

1       11
2       22
3       33

Methods for prepared statements
top of page

After creating a prepared statement with db:prepare() the returned statement object should be used for all further calls in connection with that statement. Statement objects support the following methods.

stmt:bind(n[,value])
top of page

Binds value to host parameter n. If the type of value is string or number, it is bound as text or double, respectively. If value is a boolean or nil or missing, any previous binding is removed. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

stmt:bind_blob(n,blob)
top of page

Binds string blob (which can be a binary string) as a blob to host parameter n. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

stmt:bind_names(nametable)
top of page

Binds the values in nametable to host parameters. If the host parameters are named (i.e., of the form ":AAA" or "$AAA") then this function looks for appropriately named fields in nametable; if the host parameters are not named, it looks for numerical fields 1 to the number of host parameters. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

stmt:bind_parameter_count()
top of page

Returns the largest host parameter index in prepared statement stmt. When the host parameters are of the forms ":AAA" or "?", then they are assigned sequentially increasing numbers beginning with one, so the value returned is the number of parameters. However if the same host parameter name is used multiple times, each occurrence is given the same number, so the value returned is the number of unique host parameter names.

If host parameters of the form "?NNN" are used (where NNN is an integer) then there might be gaps in the numbering and the value returned by this interface is the index of the host parameter with the largest index value.

stmt:bind_parameter_name(n)
top of page

Returns the name of the n-th parameter in prepared statement stmt. Host parameters of the form ":AAA" or "@AAA" or "$VVV" have a name which is the string ":AAA" or "@AAA" or "$VVV". In other words, the initial ":" or "$" or "@" is included as part of the name. Parameters of the form "?" or "?NNN" have no name. The first bound parameter has an index of 1, not 0. If the value n is out of range or if the n-th parameter is nameless, then nil is returned. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes)

stmt:bind_values(value1,value2,...,valueN)
top of page

Binds the given values to host parameters. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

stmt:columns()
top of page

Returns the number of columns in the result set returned by statement stmt or 0 if the statement does not return data (for example an UPDATE).

stmt:finalize()
top of page

This function frees prepared statement stmt. If the statement was executed successfully, or not executed at all, then sqlite3.OK is returned. If execution of the statement failed then an error code is returned.

stmt:get_name(n)
top of page

Returns the name of column n in the result set of statement stmt. (The left-most column is number 0.)

stmt:get_named_types()
top of page

Returns a table with the names and types of all columns in the result set of statement stmt.

stmt:get_named_values()
top of page

This function returns a table with names and values of all columns in the current result row of a query.

stmt:get_names()
top of page

This function returns an array with the names of all columns in the result set returned by statement stmt.

stmt:get_type(n)
top of page

Returns the type of column n in the result set of statement stmt. (The left-most column is number 0.)

stmt:get_types()
top of page

This function returns an array with the types of all columns in the result set returned by statement stmt.

stmt:get_unames()
top of page

This function returns a list with the names of all columns in the result set returned by statement stmt.

stmt:get_utypes()
top of page

This function returns a list with the types of all columns in the result set returned by statement stmt.

stmt:get_uvalues()
top of page

This function returns a list with the values of all columns in the current result row of a query.

stmt:get_value(n)
top of page

Returns the value of column n in the result set of statement stmt. (The left-most column is number 0.)

stmt:get_values()
top of page

This function returns an array with the values of all columns in the result set returned by statement stmt.

stmt:isopen()
top of page

Returns true if stmt has not yet been finalised, false otherwise.

stmt:nrows()
top of page

Returns an function that iterates over the names and values of the result set of statement stmt. Each iteration returns a table with the names and values for the current row.

stmt:reset()
top of page

This function resets SQL statement stmt, so that it is ready to be re-executed. Any statement variables that had values bound to them using the stmt:bind*() functions retain their values.

stmt:rows()
top of page

Returns an function that iterates over the values of the result set of statement stmt. Each iteration returns an array with the values for the current row.

stmt:step()
top of page

This function must be called to evaluate the (next iteration of the) prepared statement stmt. It will return one of the following values:

stmt:urows()
top of page

Returns an function that iterates over the values of the result set of statement stmt. Each iteration returns a list with the values for the current row.

Methods for callback contexts
top of page

A callback context is available as a parameter inside the callback functions db:create_aggregate() and db:create_function(). It can be used to get further information about the state of a query.

context:aggregate_count()
top of page

Returns the number of calls to the aggregate step function.

context:get_aggregate_data()
top of page

Returns the user-definable data field for callback funtions.

context:set_aggregate_data(udata)
top of page

Set the user-definable data field for callback funtions to udata.

context:result(res)
top of page

This function sets the result of a callback function to res. The type of the result depends on the type of res and is either a number or a string or nil. All other values will raise an error message.

context:result_null()
top of page

This function sets the result of a callback function to nil. It returns nothing.

context:result_number(number) or context:result_double(number)
top of page

This function sets the result of a callback function to the value number. It returns nothing.

context:result_int(number)
top of page

This function sets the result of a callback function to the integer value in number. It returns nothing.

context:result_text(str)
top of page

This function sets the result of a callback function to the string in str. It returns nothing.

context:result_blob(blob)
top of page

This function sets the result of a callback function to the binary string in blob. It returns nothing.

context:result_error(err)
top of page

This function sets the result of a callback function to the error value in err. It returns nothing.

context:user_data()
top of page

Returns the userdata parameter given in the call to install the callback function (see db:create_aggregate() and db:create_function() for details).

Numerical error and result codes
top of page

The following constants are defined by module sqlite3:

OK: 0          ERROR: 1       INTERNAL: 2    PERM: 3        ABORT: 4
BUSY: 5        LOCKED: 6      NOMEM: 7       READONLY: 8    INTERRUPT: 9
IOERR: 10      CORRUPT: 11    NOTFOUND: 12   FULL: 13       CANTOPEN: 14
PROTOCOL: 15   EMPTY: 16      SCHEMA: 17     TOOBIG: 18     CONSTRAINT: 19
MISMATCH: 20   MISUSE: 21     NOLFS: 22      FORMAT: 24     RANGE: 25
NOTADB: 26     ROW: 100       DONE: 101

For details about their exact meaning please see the SQLite3 documentation.



$$ built from IdleSQLite.txt d106963c4f77 Mon Sep 27 13:27:10 2010 +0000 thomasl $$