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 sqlite_version/1, 26 sqlite_open/3, 27 sqlite_close/1, 28 sqlite_prepare/3, 29 sqlite_bind/2, 30 sqlite_reset/1, 31 sqlite_sql/2, 32 sqlite_expanded_sql/2, 33 sqlite_column_names/2, 34 sqlite_finalize/1, 35 sqlite_do/1, 36 sqlite_one/2, 37 sqlite_many/4, 38 sqlite_row/2 ]).
78:- use_foreign_library(foreign(swiplite)). 79:- use_module(library(dcg/basics)). 80 81:- multifile prolog:error_message//1. 82 83prologerror_message(sqlite_error(Caller, Code, Str, Message)) --> 84 [ '[~s] (~d) ~s - ~s'-[Caller, Code, Str, Message] ]. 85prologerror_message(swiplite_error(Caller, Message)) --> 86 [ '[~s] ~s'-[Caller, Message] ].
92sqlite_version(V) :- 93 setup_call_cleanup(sqlite_open('', DB, [memory(true)]), 94 setup_call_cleanup(sqlite_prepare(DB, "select sqlite_version()", S), 95 sqlite_one(S, row(V0)), 96 sqlite_finalize(S)), 97 sqlite_close(DB)), 98 atom_string(V, V0). 99 100:- predicate_options(sqlite_open/3, 3, 101 [ mode(oneof([read,write,create])), 102 memory(boolean), 103 threaded(oneof([single,multi,serialized])) 104 ]).
The options are used to set the flags argument in the call to
sqlite3_open_v2().
The following options are recognized:
| 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 |
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.
The term in Bind_values must be named "bv" (bind values).
Use an empty list [] to set a variable to NULL.
?- sqlite_prepare(DB, "Select ?1, ?2", S),
sqlite_bind(S, bv('a', [])),
sqlite_expanded_sql(S, E).
E = "Select 'a', NULL".
Each term in the Bind_values argument is used to set the variable with the same index in the SQL statement; both start counting at 1.
In addition to using the empty list to represent SQL NULL:
double.SELECT statement
For a SELECT statement, the result is a flat term
cols(column_1, column_2, ...).
If the prepared statement does not have a result set with columns
in it, Column_names is unified with cols().
For example, CREATE or INSERT statements must be
evaluated using sqlite_do/1, while a SELECT needs
either sqlite_one/1 or sqlite_many/4.
Statement is reset automatically upon success.
SELECT statement with exactly one row in the result set
Statement is reset automatically upon success.
When N is a free variable, fetch all rows of the result set and unify N with the number of rows.
Otherwise, fetch up to N result rows in R.
R and T form a difference list. When there are no more results
in the result set, T is unified with the empty list [].
A statement evaluated with sqlite_many/4 must be explictly
reset using sqlite_reset/1 after all rows in the result set have
been fetched. Until it is reset, consecutive calls will unify N
with 0 and both R and T with the empty list [].
If the result set is empty, fail.
Prolog bindings for SQLite
This module provides partial access to the C-language interface of SQLite.
It exposes the database connection object
sqlite3and the prepared statement objectsqlite3_stmt, along with some of the essential functions using these objects. Please refer to the SQLite documentation and the implementation inc/swiplite.cwhen 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 ofSELECTqueries 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
*_v2versions 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. */