Lua SQLite 2 reference manual

Author: Tiago Dionizio
Contact: tngd@mega.ist.utl.pt
Status: Stable version
Date: 2004-08-31
Version: 1.0

Lua SQLite 2 provides an interface to the SQLite 2 databases.

Lua SQLite is a Lua 5 binding to allow users/developers to manipulate SQLite 2 databases from lua.

Contents

Usage

To load the Lua SQLite 2 library execute:

require('sqlite')

When loaded, it will create a new global variable of type table sqlite.

Download

lsqlite-1.0.tar.gz

(Detailed information comming soon...)

Installation

(TODO)

SQLite functions

Library functions

Functions directly related to the SQLite 2 API.

sqlite.version

version = sqlite.version()

Returns string representing SQLite's library version.

sqlite.encoding

encoding = sqlite.encoding()

Returns a string representing SQLite's library encoding (set at compile time).

sqlite.open

db = sqlite.open(filename [, mode])

Open database with name filename using mode mode. Mode defaults to 0 (zero) if omitted.

Returns a database handle to work with the recently opened database. In case of error, returns nil and an error message.

To see how to interact with the database read description of associated methods which is described bellow.

See also: db

sqlite.open_memory

db = sqlite.open_memory([mode])

Same as sqlite.open(':memory:', mode). :memory: is used by SQLite to create an in-memory database.

Convenience function - particular case where filename is :memory:.

See also: db

sqlite.error_string

message = sqlite.error_string(code)

Returns the description of the SQLite error code.

sqlite.complete

ok = sqlite.complete(sql)

Returns a boolean value of true if the SQL statement sql is complete, false otherwise.

sqlite.encode_binary

encoded_str = sqlite.encode_binary(str)

Encode a binary string str so that it contains no instances of characters \' or \000. The output can be used as a string value in an INSERT or UPDATE statement. Use sqlite.decode_binary to convert the string back into its original binary.

Returns the encoded string.

sqlite.decode_binary

str = sqlite.decode_binary(encoded_str)

This routine reverses the encoding created by sqlite.encode_binary.

sqlite.XXX

Possible values of XXX:

  • OK

    This value is returned if everything worked and there were no errors.

  • INTERNAL

    This value indicates that an internal consistency check within the SQLite library failed. This can only happen if there is a bug in the SQLite library. If you ever get an SQLITE_INTERNAL reply from an db:exec call, please report the problem on the SQLite mailing list.

  • ERROR

    This return value indicates that there was an error in the SQL that was passed into the db:exec.

  • PERM

    This return value says that the access permissions on the database file are such that the file cannot be opened.

  • ABORT

    This value is returned if the callback function returns non-zero.

  • BUSY

    This return code indicates that another program has the database locked.

  • LOCKED

    This return code is similar to - BUSY in that it indicates that the database is locked. But the source of the lock is a recursive call to db:exec. This return can only occur if you attempt to invoke db:exec from within a callback routine of a query from a prior invocation of db:exec. Recursive calls to db:exec are allowed as long as they do not attempt to write the same table.

  • NOMEM

    This value is returned if a call to malloc fails.

  • READONLY

    This return code indicates that an attempt was made to write to a database file that is opened for reading only.

  • INTERRUPT

    This value is returned if a call to db:interrupt interrupts a database operation in progress.

  • IOERR

    This value is returned if the operating system informs SQLite that it is unable to perform some disk I/O operation. This could mean that there is no more space left on the disk.

  • CORRUPT

    This value is returned if SQLite detects that the database it is working on has become corrupted. Corruption might occur due to a rogue process writing to the database file or it might happen due to an perviously undetected logic error in of SQLite. This value is also returned if a disk I/O error occurs in such a way that SQLite is forced to leave the database file in a corrupted state. The latter should only happen due to a hardware or operating system malfunction.

  • FULL

    This value is returned if an insertion failed because there is no space left on the disk, or the database is too big to hold any more information. The latter case should only occur for databases that are larger than 2GB in size.

  • CANTOPEN

    This value is returned if the database file could not be opened for some reason.

  • PROTOCOL

    This value is returned if some other process is messing with file locks and has violated the file locking protocol that SQLite uses on its rollback journal files.

  • SCHEMA

    When the database first opened, SQLite reads the database schema into memory and uses that schema to parse new SQL statements. If another process changes the schema, the command currently being processed will abort because the virtual machine code generated assumed the old schema. This is the return code for such cases. Retrying the command usually will clear the problem.

  • TOOBIG

    SQLite will not store more than about 1 megabyte of data in a single row of a single table. If you attempt to store more than 1 megabyte in a single row, this is the return code you get.

  • CONSTRAINT

    This constant is returned if the SQL statement would have violated a database constraint.

  • MISMATCH

    This error occurs when there is an attempt to insert non-integer data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite ignores the data type and allows any kind of data to be stored. But an INTEGER PRIMARY KEY column is only allowed to store integer data.

  • MISUSE

    This error might occur if one or more of the SQLite API routines is used incorrectly.

  • NOLFS

    This error means that you have attempts to create or access a file database file that is larger that 2GB on a legacy Unix machine that lacks large file support.

  • ROW

    This is one of the return codes from the vm:step routine which is part of the non-callback API. It indicates that another row of result data is available.

  • DONE

    This is one of the return codes from the vm:step routine which is part of the non-callback API. It indicates that the SQL statement has been completely executed and the vm:finalize routine is ready to be called.

  • NUMERIC, TEXT, ARGS

    These are the possible values to use when registering SQL functions and define the datatype of the function.

See also: sqlite.error_string

db object functions

db:close

db:close()

Closes the database and frees associated memory.

db:isopen

isopen = db:isopen()

Returns true if the database is still open. After db:close is called the handle will no longer be valid and db:isopen will return false.

db:last_insert_rowid

rowid = db:last_insert_rowid()

Returns the integer key of the most recent insert in the database.

db:changes

changes = db:changes()

This function returns the number of database rows that were changed (or inserted or deleted) by the most recent called db:exec.

db:interrupt

db:interrupt()

This function causes any pending database operation to abort and return at its earliest opportunity.

Even though most of the times the current operation can be aborted by means of callbacks this function does not have a specific use at the moment, and usually this can be called by means of a hook or signal.

It is provided for completness (but this doesn't mean you will not find it usefull).

db:create_function

db:create_function(name, nargs, fun, datatype [, udata])

Creates a normal SQL function associated to this particular database handle.

  • name

    is the name of the function.

  • nargs

    is the number of arguments the function will accept (-1 means any number)

  • fun

    the lua function to call

  • datatype

    the type of the values returned by the function; this can be sqlite.NUMERIC, sqlite.TEXT or sqlite.ARGS

  • udata

    a value that will be passed as an extra parameter to the function when it is called - optional parameter

The parameters passed to the callback are of the form:

  • udata

    the 5'th parameter passed to db:create_function (udata)

  • ...

    the parameters passed to the function by the SQLite engine

When using a variable number of arguments a good usage example might be by declaring the callback function function foo(udata, ...) and check parameters passed through the local table arg and check table.getn(arg) to check the number of arguments passed. These can be accessed by arg[i] where i is the number of the parameter.

Returns false in case of error, true otherwise.

See also: db:create_aggregate

db:create_aggregate

db:create_aggregate(name, nargs, step, finalize, datatype [, udata])

Creates an aggregate SQL function associated to this particular database handle.

  • name

    is the name of the function.

  • nargs

    is the number of arguments the function will accept (-1 means any number)

  • step

    the lua function to called for each row

  • finalize

    the lua function to call to get the final result

  • datatype

    the type of the values returned by the function; this can be sqlite.NUMERIC, sqlite.TEXT or sqlite.ARGS

  • udata

    a value that will be passed as an extra parameter to the function when it is called - optional parameter

The parameters passed to the step callback are of the form:

  • udata

    the 6'th parameter passed to create_function (udata)

  • ctx

    the associated value for the 'life time' of this function (a table)

  • count

    the number of calls made so far (1 is the first)

  • ...

    the parameters passed to the function by the SQLite engine

The parameters passed to the finalize callback are of the form:

  • udata

    the 6'th parameter passed to create_function (udata)

  • ctx

    the associated value for the 'life time' of this function (a table)

  • count

    the number of calls made so far (1 is the first)

Only the returned values of the finalize function will be used.

Returns false in case of error, true otherwise.

See also: db:create_function

db:trace

db:trace(fun [, udata])

Register a function that is called at every invocation of db:exec or db:compile.

The parameters passed to the registered function are as follow:

  • udata

    the second parameter's value passed to db:trace

  • sql

    the SQL statement passed to exec or compile

Calling the function with a nil fun will remove the handler.

db:progress_handler

db:progress_handler(fun, opnum [, udata])

Register a progress callback fun that is to be invoked once for every opnum virtual machine opcodes. The user data udata parameter is optional.

Parameters passed to the registered function are as follow:

If the callback function returns a true value the current query operation is aborted and an error value sqlite.ABORT will be returned by the main function.

Calling the function with a nil fun will remove the handler.

db:busy_handler

db:busy_handler(fun [, udata])

Register a function to be called whenever an attempt is made to open a database table that is currently locked by another process or thread.

If the callback returns a nil or a false value the current operation is aborted and the main function returns sqlite.BUSY, or else it will attempt to open the table again and the cycle repeats.

The parameters passed to the callback function are as follow:

  • udata

    the second parameter passed to db:busy_handler

  • tries

    the number of times the table has been busy

  • name

    the name of the locked table

Calling the function with a nil fun will remove the handler.

db:busy_timeout

db:busy_timeout(ms)

This routine sets a busy handler that sleeps for a while when a table is locked. The handler will sleep multiple times until at least ms milleseconds of sleeping have been done. After ms milleseconds of sleeping, the handler returns 0 which causes main function to return sqlite.BUSY.

db:exec

error_code, error_message = db:exec(sql [, fun [, user] ])

A function to executes one or more statements of SQL.

If one or more of the SQL statements are queries, then the callback function specified by the second parameter is invoked once for each row of the query result. This callback should normally return false or nil, or else the query is aborted, all subsequent SQL statements are skipped and the exec function returns sqlite.ABORT.

Parameters passed to the callback function are as follow:

  • udata

    the third parameter passed to db:exec

  • num

    the number of columns in the query result

  • values

    the values of the columns

  • names

    the names of the columns

The function returns sqlite.OK in case of success or an error value and an error string describing the problem.

See also: sqlite.XXX

db:compile

-- in case of error sqltail contains the error message
vm, sqltail, errcode = db:compile(sql)

Callback free interface to work with queries.

In case of success, returns a virtual machine handle and the rest of the sql that was not processed, sqltail.

In case of error, returns nil, followed by an error message and the error value from sqlite.

See also: vm, sqlite.XXX

db:rows

-- db:rows(sql)

returns an iterator function for use with for loops:

for row in db:rows('select * from t') do
    print(unpack(row))
end

in case of error (sqlite error) a (lua) error will be generated interrupting the script instead of breaking the for loop silently

See also: db:urows

db:urows

-- db:urows(sql)

returns an iterator function for use with for loops:

for var1, var2, ..., varn in db:urows('select * from t') do
    print(var1, ..., varn)
end

in case of error (sqlite error) a (lua) error will be generated interrupting the script instead of breaking the for loop silently

See also: db:rows

db:close_vm

db:close_vm([temp_only])

Closes the associated virtual machines.

Usefull when recovering from errors and want to make sure there aren't any virtual machines accessing the database at the moment.

If temp_only is true, only temporary virtual machines are closed; these are created when using db:rows and db:urows.

vm object functions

The virtual machine created by db:compile.

vm:isopen

isopen = vm:isopen()

Returns true if the virtual machine is still valid

vm:step

error_code = vm:step()

If db:compile is executed successefully, vm:step is called to execute the sql statement as far as it can go and returns.

The return value will be one of sqlite.DONE, sqlite.ERROR, sqlite.BUSY, sqlite.ROW or sqlite.MISUSE.

When the return value is sqlite.ROW, you can call vm:get_values, vm:get_names or vm:get_types to access values of the current row of the query's result.

When the return value is sqlite.DONE, the query was terminated successefully and should call vm:finalize terminate the operation and free the associated (internal) virtual machine used on this callback free interface.

See also: sqlite.XXX, db:compile

vm:finalize

error_code, error_message = vm:finalize()

Terminates the current operation started by db:compile. This can be called before vm:step returns sqlite.DONE (or an error code) or even after db:compile.

Returns sqlite.OK in case of success or an error code followed by a descriptive error message.

See also: sqlite.XXX, db:compile

vm:reset

error_code, error_message = vm:reset()

Resets the virtual machine to its initial state.

Returns sqlite error number, followed by an error message in case of error.

See also: sqlite.XXX, db:compile

vm:bind

error_code, error_message = vm:bind(index, value)

If the SQL that was handed to db:compile contains variables that are represeted in the SQL text by a question mark (?), this routine is used to assign values to those variables.

The left most index is 1.

To bind a NULL value, call vm:bind with a nil value.

Returns sqlite error code. In case of error an error message is also returned after the sqlite error code.

See also: sqlite.XXX, db:compile, vm:step

vm:get_names

names = vm:get_names()

Returns a table with the names of the columns associated with the current row that was fetched by vm:step.

vm:get_types

types = vm:get_types()

Returns a table with the names of the tyles of the columns associated with the current row that was fetched by vm:step.

vm:get_values

values = vm:get_values()

Returns a table with the values of the columns associated with the current row that was fetched by vm:step.

vm:get_unames

name1, name2, ..., nameN = vm:get_unames()

Return multiple values instead of a table with the wanted information.

See also: vm:get_names

vm:get_utypes

type1, type2, ..., typeN = vm:get_utypes()

Return multiple values instead of a table with the wanted information.

See also: vm:get_types

vm:get_uvalues

value1, value2, ..., valueN = vm:get_uvalues()

Return multiple values instead of a table with the wanted information.

See also: vm:get_values

vm:get_named_values

row = vm:get_named_values()

Returns a table with a column name->value indexing.

Example:

sql = 'select * from t'
vm = db:compile(sql)
while (vm:step() == sqlite.ROW) do
    print(row['name'], row['age'])
end
vm:finalize()

vm:get_named_types

types = vm:get_named_types()

Returns a table with a column name->type indexing.

Compatibility functions

To attempt to make this binding compatible with others created by other lua users, a few functions have been added:

Compatibility name Correspondent function
vm:idata vm:get_values
vm:inamed vm:get_names
vm:itypes vm:get_types
vm:data vm:get_named_values
vm:type vm:get_named_types

Notes on current implementation

The user data parameters are passed before other parameters to callback functions to maintain an uniform interface.

Calling a method with invalid values or at unapproriate times will cause an error to be thrown, such as attempting to use a database after closing it.

For more information on SQLite read the SQLite documents. This library attempts to give a simple and easy interface for this library but it should behave similar (or the same) when using it's C interface.

Some features can be disabled at compile time, such as in memory database and the progress handler callback. Check lsqlite.c for more details.