Axiom — SQLite Module

The @sqlite module is just a single class @sqlite, which represents a SQLite database.

The version of SQLite compiled into Axiom is 3.33.0, and has the following extensions enabled:

CSV — For easily querying CSV files using SQL
FTS5 — For full-text search
JSON1 — For dealing with JSON within SQL
R-Tree — For geospatial indexing
Spellfix1 — For implementing spellcheck/autosuggest

Methods

:new(path) #

Create or open a SQLite database at path.

Parameters

path — the file path of the database (optional, default is ":memory:")

Return

a SQLite database object

Notes

If path is ":memory:" (default), an in-memory database will be created.

Example

db = sqlite('data.db')
print db.schema() #print the schema
print db.query('SELECT * FROM entries WHERE rowid = ?',[2]) #print row with rowid=2

:query(sql,args,headers) #

Query the database.

Parameters

sql — a string containing SQL
args — an array or object of arguments, see notes (optional, default is null for no arguments)
headers — whether or not prepend a row of headers (optional, default is false)

Return

an error string if the query failed
the query result (usually a 2D array), if the query succeeded

Notes

If args are passed as an array, question marks (?) should be used as placeholders within sql.

If args are passed as an object, a colon followed by the key (like :name) should be used as placeholders within sql.

If args is not an array or object, its value will be bound to the first question mark (?) within sql.

Headers only appear if there are 1 or more rows in the result. If there are 0 rows in the result, headers will not appear even if headers is true.

:last_insert_rowid() #

The the rowid of the last inserted row.

Notes

Every row in every table in SQLite has a implicit nonzero integer autoincrement field called 'rowid', which can be used to uniquely identify a row within a table.

:tables(glob) #

Get all tables matching glob.

Parameters

glob — a glob to filter the list of tables by (optional, default is to return all tables)

Return

an array of strings, which are the names of the tables

:schema() #

Get the schema of the database.

Return

a 2d array containing a single column of strings, which are the statements used to create the database

Notes

Subsequent modifications ('ALTER TABLE...') are reflected within the schema.

:indices() #

Get just the indices within the database.

Return

a 2d array containing a single column of strings, which are the statements used to create the indices

Functions

:version() #

Get the version of SQLite used.

Return

a string containing the SQLite version

Notes

Should return the following value:

'3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0alt2'