Lua-Sqlite3 Documentation

Table of Contents

Overview

Lua-Sqlite3 is a binding of sqlite (http://www.sqlite.org) Version 3 to Lua (http://www.lua.org/).

Lua-Sqlite3 is unique in contrast to other database bindings that it consists of two layers.

The first layer, written in C, is called the 'backend'. The backend translates the C-api of the sqlite library version 3 to lua. The backend mainly converts data types from C to lua and vice versa and expose most C functions from the sqlite library to lua.

The second layers are called 'frontends' and are written in lua. A frontend provides a specific view and interface to a database. For example it's the job of a frontend to translate error codes from the sqlite library to error messages in lua. It's also the job of a fronted to arrange for garbage collection of database handles and to make sure, that the functions exposed by the backend are called in the right order.

Currently, Lua-Sqlite3 implements two frontends:

  1. An specialized frontend, simple named 'Sqlite3', which explodes all capabilities of the sqlite library in a comfortable way.
  2. A frontend, named 'LuaSQL-Sqlite3' which provides a LuaSQL compatible interface as used in the Kepler Project.

The first frontend named 'Sqlite3' provides following advantages:

The second frontend named 'LuaSQL-Sqlite3' is fully compatible with the database standard defined by the Kepler Project. This frontend provides currently no extensions.

Summary

The complete package is named 'Lua-Sqlite3'. It consists of a backend, named 'libluasqlite3' and currently two frontends, named 'Sqlite3' and 'LuaSQL-Sqlite3'.

The backend is a thin wrapper to expose the C-api of sqlite library version 3 to lua. The backend is written in C.

The frontends are build on top of the backend and are written in lua. The frontends provide a specific interface to a database.

Installation

Lua-Sqlite3 comes with a configure script to customize the paths where to install and where to find headers and libraries.

The configure options --with-lua-dir and --with-sqlite3-dir tell where you installed lua and sqlite3 to search for headers and libraries.

If you are using shared libraries and didn't installed sqlite3 or lua to a standard directory searched by the dynamic linker of your operating system, you could run configure with --enable-rpath to make sure, that Lua-Sqlite3 will find the shared libraries without setting LD_LIBRARY_PATH.

After you run configure, run make to build the C-library. After successful build of the C-library you could also run the provided testsuite to make sure all work as expected and finally install the package. 

# ./configure --help
# ./configure ...
# make all
# make check
# make install

If you plan to use Lua-Sqlite3 on Windows or if you would like to link Lua-Sqlite3 statically to your host program, you should fiddle with the backend loader, a lua function named load_libluasqlite3() defined in the file libluasqlite3-loader.lua. Actually this file is created from a template, called libluasqlite3-loader.lua.in. The changes needed to the loader should be obvious.

Warning: Maybe the loader process and behavior will change in future releases again.

Warning 2: The loader process will definitively change when Lua-Sqlite3 drops support for Lua-5.0 while switching to Lua-5.1 package style.

Sqlite3 Frontend

The Lua-Sqlite3 frontend provides an object oriented view to a sqlite3 database. There are only two types of objects: A database object and a statement object. The objects itself are really tables, but that doesn't matters.

A Database object is created when opening a database was successful. A statement object is created when you compile a SQL statement using an open database object.

These objects provide different methods that you call to perform the desired function. There are methods to close objects and methods to execute SQL statements.

A method is always called with the colon syntax. An error is raised, if you use a dot ('.') instead a colon (':').for example: 

-- Ok
db:close()

-- Error! Don't do this!
db.close()

-- This works too, but doesn't look nice and is error prone
db.close(db)

However, there is an exception. The 'sqlite3' namespace isn't an object, it's just a namespace, a table with functions. You must use a dot ('.'), using a colon (':') results in undefined behavior or raises an error.

Error handling

If a method on any Lua-Sqlite3 object was successful, something is returned that evaluates to true, if used in an expression. Mostly these are newly created objects or returned rows.

There are two possible error sources:

  1. Errors in the application, for example SQL syntax errors, closing a database twice or trying to execute a statement on a closed database.
  2. Errors reported from sqlite3 itself, for example a locked database, or an interrupted query and so on.

In the first case, an error in Lua-Sqlite3 usage, the error is reported with the Lua function error(), which results in terminating the last protected function call.

In the second case, when an error during executing a SQL statement occurs, two values are returned: A nil value and an string containing the error message.

This behavior will result in easy catching of all errors using assert() and pcall(): 

function foobar()
  assert( some_sqlite3_function() )
  assert( another_sqlite3_call() )
  assert( a_third_call_to_sqlite3() )
end

ok, errmsg = pcall( foobar )

if not ok then
  print("An database error occurred:", errmsg)
end

Open and Close

You can open a database that resides in a file on a disc, or you could open a database in memory. As stated above, make sure you use a single point ('.') to call the functions in the 'sqlite3' namespace. Don't use a colon (':') because 'sqlite3' isn't an object: 

-- Open a file database
db = sqlite3.open("filename")

-- Open a temporary database in memory
db = sqlite3.open_memory()

To close a database you invoke the method db:close(). Because this call is a method on the database object, you must use a colon (':') to separate the method name from the object name: 

db:close()

If you close a database, all uncommitted transactions are rolled back, all resources are released automatically. You don't need to close other objects related to this database, but if you would like, you could do so.

If closing the database was successful db:close() returns the database object itself.

If there was an error, for example closing the same database twice, an error is raised. When there was an error in sqlite3 itself, a nil and the error message is returned.

Executing simple SQL Statements

To execute SQL statements which don't return rows, user db:exec() with the SQL statement as an argument: 

db:exec( "CREATE TABLE test (id, data)" )
db:exec[[ INSERT INTO test VALUES (1, "Hello World") ]]

You can also execute multiple SQL statements using a single db:exec() call. The only prerequisite is that none of the statements return rows. You can use statements like CREATE, INSERT, UPDATE, DELETE and so on, but you can't issue a SELECT statement inside a multiple db:exec(). Doing so will raise an error.

Multiple SQL statements must be separated using semicolons: 

db:exec[[
  BEGIN TRANSACTION;
    CREATE TABLE test (id, data);
      INSERT INTO test VALUES (1, "Hello World");
      INSERT INTO test VALUES (2, "Hello Lua");
      INSERT INTO test VALUES (3, "Hello Sqlite3");
  END TRANSACTION
]]

Fetching Rows using SELECT Statements

To execute a SELECT statement, you can't use db:exec(). Doing so raises an error instead.

Rows returned from SELECT statements are fetched using an iterator. This works like ipairs() or pairs() in for-loops on tables.

You have to choose among three different ways, how the fetched rows should be returned to your loop:

  1. For each row an array with column data is returned. The columns are indexed with integers.
  2. For each row a table with the column data is returned, but the columns are indexed with the column names.
  3. Each column data is returned directly.

An example will make this clear: 

-- Returns a row as an integer indexed array
for row in db:irows("SELECT * FROM test") do
  print(row[1], row[2])
end

-- Returns a row as an table, indexed by column names
for row in db:rows("SELECT * FROM test") do
  print(row.id, row.data)
end

-- Returns each column directly
for id, data in db:cols("SELECT * FROM test") do
  print(id, data)
end

Using db:cols() there exists a subtle caveat: By definition, a for loop in Lua terminates when the first value in the returned values is nil. So you can't use the db:cols() iterator, if your SELECT statement returns rows which first column may contain NULLs (which are converted to NIL). If you do so, immediately before the row that first column contains a NULL, the loop will terminate early.

To prevent this behavior, you could either make sure that the first column in your SELECT statement will never be NULL, for example the first column could always be a row id, or alternatively you could insert a constant and a dummy variable. Example: 

for _, id, data in db:cols("SELECT 1, * FROM test") do
  print(id, data)
end

This loop will always return all rows, because the first column is always '1' (the '1' is stored in the dummy variable '_').

Fetching a Single Row

Often you only need the first row that a SELECT statement returns. For example counting the number of rows in a table and so on. Using db:rows() and related for such a task is a mess, instead you should use db:first_row() and related for this task: 

row = db:first_irow("SELECT count(*) FROM test")
print(row[1])

row = db:first_row("SELECT count(*) AS count FROM test")
print(row.count)

count = db:first_cols("SELECT count(*) FROM test")
print(count)

There doesn't exist a caveat in db:first_cols() like in db:cols(), instead always exactly the columns of the first row from the SELECT statement are returned.

Simple Prepared Statements

If you have to repeatedly execute the same SQL statement, it will be more efficient if you compile those statements and reuse the compiled form multiple times.

When a statement becomes compiled, the statement is parsed and translated to a virtual machine which is stored in a binary representation form in the sqlite3 library itself. This results in a much faster execution of the statement because every time you reuse a compiled statement, the parsing and building of the virtual machine is omitted.

Compiling a statement is done with db:prepare(). It will return a compiled statement object: 

stmt = db:prepare("SELECT * FROM test")

To use a compiled statement, it defines the same methods to execute and query a statement like the db object itself. The examples above could be rewritten to: 

stmt = db:prepare("SELECT * FROM test")

for row in stmt:irows() do
  print(row[1], row[2])
end

for row in stmt:rows() do
  print(row.id, row.data)
end

for id, data in stmt:cols() do
  print(id, data)
end

And: 

stmt = db:prepare("SELECT count(*) AS count FROM test")

row = stmt:first_irow()
print(row[1])

row = stmt:first_row()
print(row.count)

count = stmt:first_cols()
print(count)

You could even compile multiple SQL statements: 

stmt = db:prepare[[
    INSERT INTO test VALUES (1, "Hello World");
    INSERT INTO test VALUES (2, "Hello Lua");
    INSERT INTO test VALUES (3, "Hello Sqlite3")
]]

stmt:exec()

Prepared Statements with Parameters (Binding)

You can compile SQL statements with placeholder or parameters. So you can reuse the statements later and bind values to the placeholders. With this you can easily reuse parameterized and complicated SQL statements.

There are two possibilities to use placeholders or parameters. Mixing the two possibilities in a single compiled statement is not allowed.

  1. Numbered, anonymous parameters using '?'.
  2. Named parameters using ':name' or '$name'.

Anonymous Parameters

To use a anonymous parameter simply insert a question mark ('?') in your SQL statement where you would like later bind values to. Then, after you compiled your SQL statement using db:prepare() everytime you would like to bind values to your placeholders, call stmt:bind() to bind the values. The arguments to stmt:bind() are binded to the placeholder in the order the question marks appear in the SQL statement. If the number of arguments to stmt:bind() doesn't match the number of placeholders (question marks) in your SQL statement, an error will be raised.

You could use parameters and bindings with every valid SQL statement. You can even use placeholders with multiple statements: 

insert_stmt = db:prepare[[
  INSERT INTO test VALUES (?, ?);
  INSERT INTO test VALUES (?, ?)
]]

function insert(id1, data1, id2, data2)
  insert_stmt:bind(id1, data1, id2, data2)
  insert_stmt:exec()
end

insert( 1, "Hello World",   2, "Hello Lua" )
insert( 3, "Hello Sqlite3", 4, "Hello User" )

get_stmt = db:prepare("SELECT data FROM test WHERE test.id = ?")

function get_data(id)
  get_stmt:bind(id)
  return get_stmt:first_cols()
end

print( get_data(1) )
print( get_data(2) )
print( get_data(3) )
print( get_data(4) )

Named Parameters

If you have to bind a lot of values or if you have to use the same value twice or more times in a SQL statement, using the question mark as a placeholder is error prone because you have to count the question marks and make sure you call stmt:bind() with all the values in the right order.

Alternatively, you can use named placeholder. A named placeholder is a parameter, which begins with a colon (':') or a dollar sign ('$') followed by alphanumerical characters that build a valid identifier.

To define the order of your named placeholder to the stmt:bind() function, you can optionally submit an array with the names of your parameters to db:prepare() as the first argument.

The leading colon or dollar sign is optional in the parameter name array. (But of course, the colon or dollar sign in the SQL statement is mandatory.)

The ordering of the parameter names in the array determines the order of the arguments to the later stmt:bind() call. 

db:exec("CREATE TABLE person_name (id, name)")
db:exec("CREATE TABLE person_email (id, email)")
db:exec("CREATE TABLE person_address (id, address)")

-- '$' and ':' are optional
parameter_names = { ":id", "$name", "address", "email" }

stmt = db:prepare(parameter_names, [[
  BEGIN TRANSACTION;
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
  COMMIT
]])

function insert(id, name, address, email)
  stmt:bind(id, name, address, email)
  stmt:exec()
end

insert( 1, "Michael", "Germany", "mroth@nessie.de" )
insert( 2, "John",    "USA",     "john@usa.org" )
insert( 3, "Hans",    "France",  "hans@france.com" )

Automatic Parameter Name Mapping

If you don't submit an array containing parameter names to stmt:prepare() the parameter names array becomes automatically build. The parameter names in the automatically build array are ordered according to their first occurrence in the SQL statement. 

...

stmt = db:prepare[[
  BEGIN TRANSACTION;
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
  COMMIT
]]

function insert(id, name, address, email)
  -- Please note the different ordering
  stmt:bind(id, name, email, address)
  stmt:exec()
end

...

Parameter Passing with a Name/Value Table

If you are using named parameters in your statements, you can pass the arguments to stmt:bind() with a name/value table: 

...

stmt = db:prepare[[
  BEGIN TRANSACTION;
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
  COMMIT
]]

function insert(id, name, address, email)
  args = { }
  args.id = id
  args.name = name
  args.address = address
  args.email = args.email
  stmt:bind(args)
  stmt:exec()
end

-- A shorter version equal to the above
function insert2(id, name, address, email)
  stmt:bind{ id=id, name=name, address=address, email=email}
  stmt:exec()
end

...

Querying Parameter Names

To query the available parameters in a compiled statement you can use stmt:parameter_names() which returns an array. The parameter names in the returned array have their leading colon or dollar sign stripped: 

...

stmt = db:prepare[[
  BEGIN TRANSACTION;
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
  COMMIT
]]

names = stmt:parameter_names()

print( table.getn(names) )      -- "4"
print( names[1] )               -- "id"
print( names[2] )               -- "name"
print( names[3] )               -- "email"
print( names[4] )               -- "address"

...

Defining User Functions

You can define user functions in Sqlite3. User defined functions are called from the SQL language interpreted by Sqlite3, back to Lua. You can define functions which calculate complicated things or you can use these user defined functions to call back to Lua from SQL triggers.

You need to submit the name of the user function, the number of arguments the user function receives and the function itself: 

function sql_add_ten(a)
  return a + 10
end

db:set_function("add_ten", 1, sql_add_ten)

for id, add_ten, data in db::rows("SELECT id, add_ten(id), data FROM test") do
  print(id, add_ten, data)
end

You can define functions with variable number of arguments. To do this you have to submit -1 as the number of arguments to db:set_function(). The documentation of sqlite3 states, that any negative number will signal a variable argument function, but at least for sqlite release 3.0.5 this is wrong. 

function my_max(...)
  local result = 0
  for _, value in ipairs(arg) do
    result = math.max(result, value)
  end
  return result
end

db:set_function("my_max", -1, my_max)

max1 = db:first_cols("SELECT my_max(17, 7)")
max2 = db:first_cols("SELECT my_max(1, 2, 3, 4, 5)")

print(max1, max2)       -- 17     5

Defining User Aggregates

A aggregate is a function, which is called multiple times, for each row in the query once, that returns a single value at the end. It is only slightly more complicate than an ordinary function.

To define a user aggregate, you have to register a function which returns two functions every time it is called. The first function is used to update an internal state of the user aggregate and the second function is used to fetch the result from the user aggregate.

The exact steps are:

An example will make this more clear: 

db:exec[[
  CREATE TABLE numbers (num1, num2);
  INSERT INTO numbers VALUES(1, 2);
  INSERT INTO numbers VALUES(3, 4);
  INSERT INTO numbers VALUES(5, 6);
]]

function my_product_sum_aggregate()
  local product_sum = 0

  local function step(a, b)
    local product = a * b
    product_sum = product_sum + product
  end

  local function final(num_called)
    return product_sum / num_called
  end

  return step, final
end

db:set_aggregate("product_sum", 2, my_product_sum_aggregate)

print( db:first_cols("SELECT product_sum(num1, num2) FROM numbers") )

You can define user aggregates with variable number of arguments. In works analogous defining user functions with variable number of arguments.

Collatores and Collation Handler

Needs to be written...

Busy Timeout and Handler

In Sqlite3 there are two ways to deal with locked databases. You can set a timeout or set a handler.

Busy Timeout

If you set a timeout, Sqlite3 will try as many milliseconds as specified: 

-- Open the database
db = sqlite3.open("filename")

-- Set 2 seconds busy timeout
db:set_busy_timeout(2 * 1000)

-- Use the database
db:exec(...)

Busy Handler

You could also set a handler, which gets called in the most cases if the database is locked. A busy handler could do some other work or wait a few milliseconds. The return value of a busy handler determines if Sqlite3 tries to continue with the current transaction or abort the transaction with a "busy" error.

If the busy handler returns 0, false or nil, no additional attempts are made by Sqlite3 to proceed with a transaction. All other values result in a new attempt to be made by Sqlite3.

A busy handler gets called with one argument, the number of attempts prior made without success in a transaction. 

-- Open the database
db = sqlite3.open("filename")

-- Ten attempts are made to proceed, if the database is locked
function my_busy_handler(attempts_made)
  if attempts_made < 10 then
    return true
  else
    return false
  end
end

-- Set the new busy handler
db:set_busy_handler(my_busy_handler)

-- Use the database
db:exec(...)

Progress, Authorizer, Trace and Commit Handler

You guess it, needs to be written...

But wait, the trace handler is documented: ;-)

Trace Handler

The trace handler in Sqlite3 isn't really a trace handler but it traces effectively compiling SQL statements. So the trace handler is called every time a db:exec() or db:prepare() is executed. The handler gets one argument with the string that was compiled: 

function mytrace(sql_string)
  print("Sqlite3:", sql_string)
end

db:set_trace_handler(mytrace)

Note: The tracing behavior will possible change in future. Then we won't use the internal Sqlite3 trace handler but a self written one which gets called every time a SQL statement is executed.

Call Chaining

The Sqlite3 Interface makes it possible, to deploy several techniques to write short and efficient code.

All methods which doesn't fetch data from the database or creates new objects, return self, like methods in smalltalk.

So you can easily build nice chains of method calls: 

sql  = "INSERT INTO test VALUES (?, ?)"
id   = 5
data = [[That's a matter of taste, if your prefer "'" or '"'.]]

db:prepare(sql):bind(id, data):exec()

LuaSQL Frontend

There is also a LuaSQL compatible frontend provided. To use the LuaSQL compatible frontend, open it with: 

require "luasql-sqlite3"

The LuaSQL Frontend doesn't use any utility functions from the LuaSQL framework of the Keppler Project because the framework from the Keppler Project assumes that every LuaSQL compatible database layer is written in C which is not true for this Sqlite3 wrapper. But this detail doesn't matter. You could use the LuaSQL frontend with or without any other LuaSQL Keppler Project packages. The LuaSQL frontend is fully compatible and integrates nicely.

For a complete description of the LuaSQL interface, please take a look at http://www.keplerproject.org/luasql/manual.html.

Currently LuaSQL-Sqlite3 implements no extensions, but this will change in the near future too.

libluasqlite3 Wrapper

These parts needs to be written:

  1. Open Wrapper
  2. Return values and Error Handling
  3. API Conventions
  4. Type Converting

Please be patient...


Back to Lua-Sqlite3
Last update: 2006-05-10
Copyright (c) 2004, 2005, 2006 Michael Roth