Skip to content

Using SQLite in SIPI

Sipi supports SQLite 3 databases, which can be accessed from Lua scripts. You should use pcall to handle errors that may be returned by SQLite.

Opening an SQLite Database

db = sqlite(path_to_db, access)

This creates a new opaque database object. The parameters are:

  • path_to_db: path to the sqlite3 database file.
  • access: Method of opening the database. Allowed are
    • 'RO': readonly access. The file must exist and the SPIP server must have read access to it.
    • 'RW': read and write access. The file must exist and the SPIP server must have read/write access to it.
    • 'CRW': If the database file does not exist, it will be created and opened with read/write access.

To destroy the database object and free all resources, you can do this:

db = ~db

However, Lua's garbage collection will destroy the database object and free all resources when they are no longer used.

Preparing a Query

The SIPI sqlite interface supports direct queries as well as prepared statements. A direct query is constructed as follows:

qry = db << 'SELECT * FROM image'

Or, if you want to use a prepared query statement:

qry = db << 'INSERT INTO image (id, description) VALUES (?,?)'

The result of the << operator (qry) will then be a query object containing a prepared query. If the query object is not needed anymore, it may be destroyed:

qry = ~qry

Query objects should be destroyed explicitly if not needed any longer.

Executing a Query

Excuting (calling) a query objects gets the next row of data. If there are no more rows, nil is returned. The row is returned as array of values.

row = qry()
while (row) do
    print(row[0], ' -> ', row[1])
    row = qry()
end

Or with a prepared statement:

row = qry('SGV_1960_00315', 'This is an image of a steam engine...')

The second way is used for prepared queries that contain parameters.