LuaCheia Reference Manual

sqlite

Synopsis

cheia.load “sqlite”

db, errmsg = sqlite.open(“/path/to/database”)
errnum, errmsg = sqlite.exec(db, “INSERT INTO my_table (fieldA, FieldB) VALUES 'foo', 'bar'”)
vm, errnum, tail, errmsg = db:compile(“SELECT * FROM my_table”)
errnum = vm:step()
row = vm:data()
errnum, errmsg = vm:finalize()
db:close()

Description

Provides a Lua interface for the SQLite lightweight relational database library.

SQLite is a fast, simple, and easy-to-use file-based database engine, which supports most of SQL92.

This module provides the ‘without callbacks’ SQLite interface. Typical usage is to use db:compile(sql) to create a virtual machine which executes the SQL query. For most purposes, this can be thought of as an forward-only cursor over the result set. To obtain each row, call vm:step() to move the cursor, followed by either vm:data() or vm:idata() to get the data.

There is also a simplified iterator interface, an example of which is given below.

Note: SQLite does rather coarse locking. This can make it unsuitable for environments where concurrent accesses are the norm. A large setting for db:busy_timeout() may be helpful. Another consequence is that reads and writes must not overlap, so it is important to finalize VMs as soon as possible.

Examples

Using the iterator interface:

db = assert(sqlite.open(“/path/to/database”))
for r in db:rows(“SELECT * FROM some_table”) do
print(r.field1, r.field2, r.field3)
end
db:close()

This function prints a table of the results of an SQL expression:

function print_results(db, sql)
local vm = assert(db:compile(sql))

local r = vm:step()
local n = 0

-- First print the field names
if r == sqlite.ROW then
for k,v in ipairs(vm:iname()) do
io.write(string.format(“%20s”, v))
n = n+1
end
io.write(“\n”, string.rep(“-”, n*20), “\n”)
end

-- Then for each row print the values
while r == sqlite.ROW do
for k,v in ipairs(vm:idata()) do
io.write(string.format(“%20s”, v))
end
io.write(“\n”)
r = vm:step();
end
vm:finalize()
end

Reference

sqlite.version()

Returns the SQLite version, e.g. “2.8.13”.

Returns:
string	SQLite version.

sqlite.encoding()

Returns the character encoding being used, e.g. “iso8859”.

Returns:
string	Description.

sqlite.open(name, [mode])

Opens a database file. The mode parameter is currently ignored by SQLite. Open “:memory:” for an in-memory database, or "" for a temporary file database which will be deleted when it is closed.

Parameters:
name : string	File name.
mode : number	File mode.
Returns:
database	Database handle.
string	Error message.

sqlite.error_string(errno)

Obtains an error description from an error code.

Parameters:
errno : number	An SQLite error number.
Returns:
string	The corresponding string.

The database handle

These functions are also available in the form of, e.g. sqlite.close(db).

db:close()

Finalizes any active VMs and closes the database. The database is also automatically closed when both it and all descendent VMs are garbage-collected, however relying on this can unnecessarily extend locks.

db:last_insert_rowid()

Returns the ID of the last row inserted into the database.

Returns:
number	The last row ID.

db:changes()

Returns the number of rows added/deleted/modified since the database was last quiescent, i.e. typically the number of changes made by the last db:exec() or since the last db:compile().

Returns:
number	The number of changes.

db:interrupt()

Can be called from a different thread to abort the current database operation.

In general, it is a bad idea to share database connections across threads. Use separate connections.

db:busy_timeout(timeout)

Causes the engine to wait at least timeout milliseconds before returning sqlite.BUSY when it encounders a locked database.

Parameters:
timeout : number	The timeout in ms.

db:compile(sql)

Compiles an SQL statement into a virtual machine and returns a handle for it.

Parameters:
sql : string	The SQL to compile.
Returns:
vm	The VM handle.
string	An error message.
number	An error code.
string	Any characters which were not processed.

db:exec(sql)

Compiles and executes an SQL statement, and does not return any result rows. The first return value is a convenience for use with assert().

Parameters:
sql : string	The SQL to execute.
Returns:
boolean	true if successful.
string	An error message.
number	An error code.

db:rows(sql)

Compiles an SQL statement, and returns results in a format suitable for use with Lua's generalized for construct. See the example above.

Note: When used as a for loop iterator, this method simply returns no rows if there was an error. To check for errors, use the db:last_error() method afterwards (it should return sqlite.OK). Avoid using break to leave the loop early, since in this case the VM used will not be finalized until it is garbage-collected.

Parameters:
sql : string	The SQL to execute.
Returns:
function	The vm:next_row() function.
vm	The VM handle.
number	An error code.

db:last_error()

Returns the result code from the last SQLite library function called, whether successful or not.

Returns:
number	An error code.

The VM handle

These functions are also available in the form of, e.g. sqlite.step(vm).

vm:bind(table)

Binds ‘?’ parameters of a query to values. values should be a table mapping 1-based parameter indexes to values (or false to indicate NULL). Non-numeric indexes are ignored. This function internally makes multiple sqlite_bind() calls, so the return code is the most recent error (i.e. result differing from sqlite.OK), or sqlite.OK if all calls succeeded. Note: This function is experimental, and the interface may change.

Parameters:
table : values	Table of parameters to bind.
Returns:
number	An error code.

vm:reset()

Resets a VM to the state it was in just after it was compiled, ready to re-run the query. Useful to re-use a query containing ‘?’ parameters. The first return value is a convenience for use with assert().

Returns:
boolean	true is successful.
string	An error message.
number	An error code.

vm:step()

Advances to the next row of the VM recordset. Returns sqlite.ROW on success, or sqlite.DONE after the last row.

Returns:
number	An error code.

vm:finalize()

Completes an SQL statement started by sqlite.compile() and frees the VM used. The VM is also finalized automatically on garbage-collection, however relying on this may cause an associated lock to be held longer than it should be. The first return value is a convenience for use with assert().

Returns:
boolean	true if successful.
string	An error message.
number	An error code.

vm:idata()

Returns a numbered table containing the data of the current row.

Returns:
table	The row data.

vm:iname()

Returns a numbered table containing the field names of the result table.

Returns:
table	The field names.

vm:itype()

Returns a numbered table containing the field types of the result table.

Returns:
table	The field types.

vm:data()

Returns a table mapping field names to field contents for the current row.

Returns:
table	The row data.

vm:type()

Returns a table mapping field names to field types for the result table.

Returns:
table	The field types.

vm:next_row([lasterr])

Steps the VM and returns a table mapping names to contents for the new row. After the last row, finalizes the VM and returns nil.

Parameters:
lasterr : number	Internal use. If supplied, must be sqlite.OK.
Returns:
table	The row data.

Issues

SQLite is not quite suitable for binary data yet.

Missing callbacks and user-defined functions.

Revision history

Added in LuaCheia 5.0.

Credits

Uses the SQLite library by D. Richard Hipp.

Lua module and documentation by Jamie Webb and Doug Currie.

Return to main site

Introduction

» Writing LuaCheia modules

Module Reference

» bit
» cgi
» fuzzy
» md5
» pack
» rex
» SDL
» shelve
» sqlite

Appendices

» Module path conventions
» Module names
» Application Binary Interface
» Credits