1/* Copyright (C) 2025 Boris Vassilev <boris.vassilev@gmail.com> 2 3Permission is hereby granted, free of charge, to any person 4obtaining a copy of this software and associated documentation 5files (the "Software"), to deal in the Software without 6restriction, including without limitation the rights to use, 7copy, modify, merge, publish, distribute, sublicense, and/or sell 8copies of the Software, and to permit persons to whom the 9Software is furnished to do so, subject to the following 10conditions: 11 12The above copyright notice and this permission notice shall be 13included in all copies or substantial portions of the Software. 14 15THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 16EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 17OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 18NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 19HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 20WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 21FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 22OTHER DEALINGS IN THE SOFTWARE. 23*/ 24:- module(sqlite, [ 25 sql_command/2, 26 sql_query/3, 27 sql_query_all/3, 28 sqlite_initialize/0, 29 sqlite_shutdown/0, 30 sqlite_version/1, 31 sqlite_schema/2, 32 sqlite_open/3, 33 sqlite_close/1, 34 sqlite_prepare/3, 35 sqlite_prepare/4, 36 sqlite_bind/2, 37 sqlite_reset/1, 38 sqlite_sql/2, 39 sqlite_expanded_sql/2, 40 sqlite_column_names/2, 41 sqlite_finalize/1, 42 sqlite_do/1, 43 sqlite_one/2, 44 sqlite_many/4, 45 sqlite_row/2, 46 sqlite_status/4, 47 sqlite_db_status/5, 48 sqlite_stmt_status/4 ] ).

Prolog bindings for SQLite

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.

88:- use_foreign_library(foreign(swiplite)). 89:- use_module(library(dcg/basics)). 90 91:- multifile prolog:error_message//1. 92 93prolog:error_message(sqlite_error(Caller, Code, Str, Message)) --> 94 [ '[~s] (~d) ~s - ~s'-[Caller, Code, Str, Message] ]. 95prolog:error_message(swiplite_error(Caller, Message)) --> 96 [ '[~s] ~s'-[Caller, Message] ].

102sqlite_version(V) :- 103 setup_call_cleanup(sqlite_open('', DB, [memory(true)]), 104 sql_query_all(DB, "select sqlite_version()", [row(V0)]), 105 sqlite_close(DB)), 106 atom_string(V, V0).

120sql_command(DB, SQL) :- 121 setup_call_cleanup(sqlite_prepare(DB, SQL, S, [bind_parameter_count(0)]), 122 sqlite_do(S), 123 sqlite_finalize(S)). 124 125/* 126sql_command(DB, SQL, Bind_vars, Generator) :- 127 setup_call_cleanup(sqlite_prepare(DB, SQL, S), 128 sql_transaction(DB, 129 forall(call(Generator, Bind_vars), 130 ( sqlite_bind(S, Bind_vars), 131 sqlite_do(S) 132 ))), 133 sqlite_finalize(S)). 134*/

179sqlite_schema(Connection, Schema) :- 180 setup_call_cleanup(sqlite_prepare(Connection, 181 "Select sql from sqlite_schema where sql is not null", S), 182 sqlite_many(S, _, Schema, []), 183 sqlite_finalize(S)). 184 185 186:- predicate_options(sqlite_open/3, 3, 187 [ mode(oneof([read,write,create])), 188 memory(boolean), 189 threaded(oneof([single,multi,serialized])) 190 ]).

293/* sqlite_prepare( 294 ++Connection:blob, ++SQL:text, 295 -Statement:blob, -N_bind:integer, -Rest:string) is det 296 297Compile Statement from the text in SQL using the database in Connection 298 299The UTF-8 encoded text in SQL is parsed up to the first nul, or 300up to the end of the first SQL statement. SQL parameters are 301initially all set to =|NULL|=. Anonymous variables are not allowed. 302If *|?|*_|NNN|_ parameters are used, they must be numbered 303starting from 1, without any gaps. 304 305@arg Connection A database connection obtained with sqlite_open/3 306@arg SQL The UTF8-encoded text of the SQL as an atom, string, 307 or list of codes 308@arg N_bind The number of bind parameters in the statement 309@arg Rest A UTF8-encoded string containing the trailing content of SQL 310 311@see sqlite_bind/2 312@see [SQL statement parameters in SQLite](https://www.sqlite.org/lang_expr.html#varparam) 313@see [`sqlite3_bind_parameter_count()`](https://www.sqlite.org/c3ref/bind_parameter_count.html) 314*/

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

`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

`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

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

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

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

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

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

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

`Statement`	- A statement compiled with sqlite_prepare/3
`Bind_values`	- A flat term with functor `bv/<number of parameters>`
`Statement`	- A blob with the compiled statement

`Statement`	- A statement compiled with sqlite_prepare/3
`Expanded_SQL`	- A string with the expanded statement

`Statement`	- A statement compiled with sqlite_prepare/3
`Column_names`	- A flat term with functor `cols/<number of columns>`

`Statement`	- A `SELECT` statement compiled with sqlite_prepare/3
`Result`	- A flat term with functor `row/<number of columns>`

`Statement`	- A `SELECT` statement compiled with sqlite_prepare/3
`N`	- Number of rows
`R`	- Rows of the result set
`T`	- Tail of `R`

`Op`	- SQLite run-time status parameter
`Current`	- is the current value of the parameter
`Highwater`	- is the highest recorder value
`Reset`	- When `true`, the `Highwater` value is reset after it is returned

`DB`	- A database connection obtained with sqlite_open/3
`Op`	- SQLite status parameter for database connections
`Current`	- is the current value of the parameter
`Highwater`	- is the highest recorder value
`Reset`	- When `true`, the `Highwater` value is reset after it is returned

`DB`	- A prepared statement obtained with sqlite_prepare/3
`Op`	- SQLite status parameter for prepared statement
`Counter`	- is the current value of the corresponding counter
`Reset`	- When `true`, the counter is reset after it is returned