Pack swiplite -- prolog/sqlite.pl

This module provides partial access to the C-language interface of SQLite.

It exposes the database connection object sqlite3 and the prepared statement object sqlite3_stmt, along with some of the essential functions using these objects. Please refer to the SQLite documentation and the implementation in c/swiplite.c when using this library. To make it easier to find the relevant docs, I have tried to consistently provide links.

Most of the predicates in this module are as close as possible in naming and semantics to the corresponding functions in the C interface. One exception is sqlite_bind/2, which converts values from Prolog terms to corresponding SQLite column datatype. Similarly, sqlite_do/1, sqlite_one/2, and sqlite_many/4 wrap the necessary calls to sqlite3_step() and convert the results of SELECT queries to Prolog terms.

The database connection and prepared statement objects are represented in SWI-Prolog as blobs. They are garbage collected, but finalizing a statement or closing a database connection (and, alternatively, not doing it) have reprecussions, especially for long-running programs. The code in this library uses exclusively the *_v2 versions of the SQLite C interface. In particular:

The sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.

sqlite_version(-Version:atom) is det

Unify Version with the version of SQLite currently in use

sql_command(++Connection:blob, ++SQL:text) is det

Execute the command in SQL.

This assumes that the command is not a SELECT query and it does not return any rows.

Arguments:

`Connection`	- A database connection obtained with sqlite_open/3
`SQL`	- The text of the command to be executed

To be done

- Allow the user to supply a generator for bind values

sql_query(++Connection:blob, ++SQL:text, -Row:row) is semidet

Get rows from the result set of the statement in SQL on backtracking.

If the result set is empty, fail.

Arguments:

`Connection`	- A database connection obtained with sqlite_open/3
`SQL`	- The text of the command to be executed
`Row`	- Unified with `row(Col1, Col2, ...)` on backtracking

sql_query_all(++Connection:blob, ++SQL:text, -Rows:list(row)) is semidet

Get all rows from the result set of the statement in SQL as a list in Rows.

Arguments:

`Connection`	- A database connection obtained with sqlite_open/3
`SQL`	- The text of the command to be executed
`Rows`	- The list of rows with all rows in the result set

sqlite_schema(++Connection:blob, -Schema:text) is det

Query the DB schema for that connection.

This is a convenience predicate returning the sql column of the table sqlite_schema.

Arguments:

`Connection`	- A database connection obtained with sqlite_open/3
`Schema`	- A list of objects in the current schema

See also

- The Schema Table

- PRAGMA table_list

- PRAGMA table_info

- PRAGMA table_xinfo

sqlite_open(++File:text, -Connection:blob, ++Options:list) is det

Open Connection to the database in File using Options

The options are used to set the flags argument in the call to sqlite3_open_v2(). The following options are recognized:

mode Mode

Determines how the database is opened:

Value	Corresponding flags
`read` (default)	`SQLITE_OPEN_READONLY`
`write`	`SQLITE_OPEN_READWRITE`
`create`	`SQLITE_OPEN_READWRITE \| SQLITE_OPEN_CREATE`

memory(Bool)

Open as an in-memory database:

Value	Corresponding flags
`false` (default)	(empty)
`true`	`SQLITE_OPEN_MEMORY`

threaded(Threaded)

Threading mode for this database connection:

Value	Corresponding flags
`single` (default)	(empty)
`multi`	`SQLITE_OPEN_NOMUTEX`
`serialized`=	`SQLITE_OPEN_FULLMUTEX`

foreign_keys(Bool)

Enable foreign keys for this connection. Defaults to true and will enable foreign keys for this connection and throw an error if foreign keys are not supported. Setting this option to false is required if the used version of SQLite does not support foreign keys.

Value	Behaviour
`true` (default)	Enable foreign keys
`false`	Do not check for foreign keys

Arguments:

`File`	- Relative path to the database file. Interpreted as UTF-8 string.
`Connection`	- A blob with the database connection.
`Options`	- A list of options

See also

- sqlite3_open_v2()

- Using SQLite in multi-threaded applications

To be done

- Support all available SQLITE_OPEN_* flags.

sqlite_close(++Connection:blob) is det

Close a Connection opened with sqlite_open/3

Arguments:

Connection

- A database connection obtained with sqlite_open/3

See also

- sqlite3_close_v2()

sqlite_prepare(++Connection:blob, ++SQL:text, -Statement:blob, ++Options) is det

Compile Statement from the text in SQL using the database in Connection, using the provided options

The UTF-8 encoded text in SQL is parsed up to the first nul, or up to the end of the first SQL statement. SQL parameters are initially all set to NULL. Anonymous variables are not allowed. If ?NNN parameters are used, they must be numbered starting from 1, without any gaps.

Two options are supported. If the option bind_parameter_count(Value) is provided, Value is unified with the number of bind variables in the prepared statement.

If the option rest(SQL_rest) is provided, the trailing content of SQL is unified with SQL_rest as a string.

Arguments: