Lua SQLite (database) interface
LuaSQLite 3 is a thin wrapper around the public domain SQLite3 database engine.
The lsqlite3 module supports the creation and manipulation of SQLite3 databases. 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.
This documentation does not attempt to describe how SQLite3 itself works, it just describes the Lua 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 http://www.sqlite.org/.
Using some of the advanced features (how to use callbacks, for instance) will require some familiarity with the SQLite3 API.
General technique
Numerical error and result codes
The following constants are defined by module sqlite3:
The lsqlite3 module supports the creation and manipulation of SQLite3 databases. 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.
This documentation does not attempt to describe how SQLite3 itself works, it just describes the Lua 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 http://www.sqlite.org/.
Using some of the advanced features (how to use callbacks, for instance) will require some familiarity with the SQLite3 API.
General technique
-
Open the database with either sqlite3.open or sqlite3.open_memory
Execute "action" SQL statements with db:exec
To query data from the database:
-
Prepare an SQL statement with db:prepare
Get column names with stmt:get_names or similar
Step through each row with stmt:step
Get column values with stmt:get_values or similar
After all rows are extracted, finalize the statement with stmt:finalize
Numerical error and result codes
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
Lua functions
- context:aggregate_count - Returns the number of calls to the aggregate step function.
- context:get_aggregate_data - Returns the user-definable data field for callback functions.
- context:result - This function sets the result of a callback function to res.
- context:result_blob - This function sets the result of a callback function to the binary string in blob.
- context:result_error - This function sets the result of a callback function to the error value in err.
- context:result_int - This function sets the result of a callback function to the integer value in number
- context:result_null - This function sets the result of a callback function to nil.
- context:result_number - This function sets the result of a callback function to the value number.
- context:result_text - This function sets the result of a callback function to the string in str.
- context:set_aggregate_data - Set the user-definable data field for callback functions to udata.
- context:user_data - Returns the userdata parameter given in the call to install the callback function.
- db:busy_handler - Sets or removes a busy handler for a database
- db:busy_timeout - Sets a busy handler
- db:changes - Returns number of changes by the most recent SQL statement
- db:close - Closes a database
- db:close_vm - Finalizes all statements that have not been explicitly finalized
- db:create_aggregate - Creates an aggregate callback function
- db:create_collation - Creates a collation callback
- db:create_function - Creates a callback function
- db:errcode - Returns the most recent result code
- db:errmsg - Returns the most recent error message
- db:exec - Executes SQL statements
- db:interrupt - Interrupts any pending operation
- db:isopen - Tests if the database is open
- db:last_insert_rowid - Returns the key of the most recent insert into the database
- db:nrows - Creates an iterator that returns a table of rows from a SELECT - keyed by name
- db:prepare - Compiles an SQL statement
- db:progress_handler - Installs a progress callback function
- db:rows - Creates an iterator that returns a table of rows from a SELECT - numerically keyed
- db:total_changes - Returns total number of changes
- db:trace - Installs debug trace callback
- db:urows - Creates an iterator that returns the rows from a SELECT
- sqlite3.complete - Checks for a complete SQL statement
- sqlite3.open - Opens or creates a database
- sqlite3.open_memory - Opens a database in memory
- sqlite3.version - Returns the SQLite version
- stmt:bind - Binds a value to a parameter
- stmt:bind_blob - Binds a blob to a parameter
- stmt:bind_names - Binds values in a table to parameters
- stmt:bind_parameter_count - Returns the largest parameter index
- stmt:bind_parameter_name - Returns the name of the n'th paremeter
- stmt:bind_values - Binds values to parameters
- stmt:columns - Returns number of columns in the result set
- stmt:finalize - Frees a prepared statement
- stmt:get_name - Returns the name of a column in a result set
- stmt:get_named_types - Returns a table of names and types for the columns in a result set
- stmt:get_named_values - Returns a table of names and values for the columns in a result set
- stmt:get_names - Returns a table of the names of all columns in a result set
- stmt:get_type - Returns the type of a column in a result set
- stmt:get_types - Returns a table of the types of all columns in a result set
- stmt:get_unames - Returns a list of names of the columns in a result set
- stmt:get_utypes - Returns a list of types of the columns in a result set
- stmt:get_uvalues - Returns a list of values of the columns in a result set
- stmt:get_value - Returns the value of a column in a result set
- stmt:get_values - Returns a table of the values of all columns in a result set
- stmt:isopen - Checks if the statement is open
- stmt:nrows - Creates an iterator that returns a table of rows from a SELECT - keyed by name
- stmt:reset - Resets an SQL statement, so it can be executed again
- stmt:rows - Creates an iterator that returns a table of rows from a SELECT - numerically keyed
- stmt:step - Executes a previously-prepared SQL statement
- stmt:urows - Creates an iterator that returns the rows from a SELECT