The sqlutil library

The sqlutil library simplifies some of the tedious and repetitive operations when working with the luasql bindings. See the Lua SQLite Tutorial for examples on how to use this library.

The library, which can be found in mako.zip, is loaded as follows:

local su=require"sqlutil"

The sqlutil functions:

su.open(name, options)

Function su.open() creates a database environment object by calling luasql.sqlite() and and then opens the database file by calling env:connect().

Function su.open() returns two values, the database environment object and a database file connection object.

name
The database name without a path. The complete path and name is computed by the sqlutil library. The open function tries to open/create the following path and name in descending order:
  1. 'Configuration file'.dbdir is an optional parameter you can set in the Mako Server configuration file.
  2. The configuration directory, if the configuration file is specified with the -c option.
  3. The current directory.
  4. The Mako Server's installation directory (where the executable is stored).

The complete database name is calculated as follows: (path from 1-4 above)/data/(name).sqlite.db

options

Options can be one of the two strings 'READONLY' or 'NOCREATE'. READONLY opens the connection in read only mode and NOCREATE prevents creation of an SQL database.

su.dir([path])

Get the database directory (if no argument) or set the database directory, thus overriding the path calculated in step 1-4 in su.open().

su.exist()

Returns true if the database file exists, otherwise false is returned.

su.find(conn,query)
su.findt(conn, query,table)

Search the database using the provided SQL query and return the first entry found. The first argument is either a connection object or a function that returns env,conn. The database is also automatically closed if the argument is a function.

Example:

local a,b,c = su.find(conn,"a,b,c FROM table WHERE a='10' LIMIT 1")

Function su.findt is similar to su.find, except that the return values are saved in the provided table instead of being returned.

su.select(conn,sql,func)

Execute the provided SQL query and call the provided callback function "func". The first argument is either a connection object or a function that returns env,conn. The database is also automatically closed if the argument is a function.