This library follows the design and borrows code from the ODBC library of SWI-Prolog http://www.swi-prolog.org/pldoc/packasqlite_connectge/odbc.html .
The SQLite system is a powerful zero-configuration management systme that interacts with single-file databases that are cross-platform compatible binaries.
ProSQLite provides three layers of interaction with SQLite databases. At the lower level is the querying via SQL statements. A second layer allows the interogation of the database dictionary, and the final level facilitates the viewing of database tables as predicates. See the publication pointed to by sqlite_citation/2, for further details. If you use prosqlite in your research, please consider citing this publication.
The library has been developed and tested on SWI 6.3.2 and latest version was tested on 9.3.9.
The easiest way to install on SWI is via the package manager. Simply do:
?- pack_install(prosqlite).
You will also need the sqlite3{.so,.dll} in your path. If you have difficulties in MS platforms try dropping sqlite3.dll in installed prosqlite/lib/x86_64/ directory (or prosqlite/lib/i386-win32 if you are on 32 bits). Alternative place it in SWI's bin/ directory.
There are good/full examples in the sources, directory examples/. For instance test by :
?- [predicated].
?- predicated.
As of v2.0 errors and null values are better supported:
% assuming db is open in SQLiteBrowser, with unsaved changes: ?- sqlite_query(db1, "update ex_t set exid=99 where exf3='v13';", AffC ). ERROR: Unhandled exception: SQLite code: 5, with short message: database is locked ?- ex_t(A,B,C), write(A:B:C), nl, fail. 1:v12:v13 2: : $null$ 3: $null$ : w false. ?- ex_t(A,B,'$null$') | . exf3- $null$ A = 2, B = '' ;
There is a sister package, db_facts (also installable via the manager). Db_facts, allow interaction with the underlying database via Prolog terms, That library can also be used as a common compatibility layer for the ODBC and proSQLite libraries of SWI-Prolog, as it works on both type of connections.
ProSQLite is debug/1 aware: call debug(sqlite) to see what is sent to
the sqlite engine.
There are MS wins DLLs included in the sources and recent version of the SWI package manager will install these properly.
There is a battery of predicates about versions. Some of these can be used to debug issues between compiled and loaded versions of SQLite, although we never had any issues and seems they have good backward compatibility.
Predicates
sqlite_version(V,D)
date(Y,M,D)) the version was publishedsqlite_binary_version(Vb,Db)
sqlite_build_version(Bv)
sqlite_library_c_version(Cv)
sqlite3_libversion() c function callThanks to Samer Abdallah for 2 fixes. One on mapping blobs to strings and second for handling UTF text correctly.
Thanks to Christian Gimenez for suggesting replacing sqlite3_close() with sqlite3_close_().
The former returns the unhandled SQLITE_BUSY if there unfinalzed statements.
_v2 is designed for garbage collected languages, see http://sqlite.org/c3ref/close.html.
Thanks to Wolfram Diestel for spotting a bug in opening 2 dbs with distinct aliases.
Thanks to Steve Moyle for contributing safe_column_names/2 (Nov 2016).
Thanks to John B Thiel (JBThiel) for opening a number of issues which led to v2.0.
Version is a Mj:Mn:Fx term and date is a date(Y,M,D) term.
? sqlite_version(V, D). V = 2:0:0, D = date(2024, 8, 15).
This is provided as the c code is not changed as often as the prolog part of the pack. These changes require re-compiling the binaries of the pack distribution.
?- sqlite_binary_version(V,D). V = 1:3:0, D = date(2018, 3, 17). ?- sqlite_version(V1,D1). V1 = 1:8:2, D1 = date(2024, 7, 6).
?- sqlite_library_c_version(V). V = '3.45.1'.
The Atom is bound to the SQLITE_VERSION macro from the SQLite sources at compile time.
?- sqlite_build_version(LibCV).
LibCV = '3.45.1'.
?- sqlite_library_c_version(V).
V = '3.45.1'.
?- sqlite_connect('/tmp/testo.sqlite', testo, exists(false)).
true.
?- sqlite_library_version(V).
V = '3.45.1'.
?- sqlite_disconnect.
ERROR: Unknown procedure: sqlite_disconnect/0
ERROR: However, there are definitions for:
ERROR: prosqlite:sqlite_disconnect/1
ERROR: prosqlite:sqlite_disconnect/2
false.
?- sqlite_disconnect_all.
ERROR: Unknown procedure: sqlite_disconnect_all/0 (DWIM could not correct goal)
^ Exception: (4) setup_call_cleanup('$toplevel':notrace(call_repl_loop_hook(begin, 0)), '$toplevel':'$query_loop'(0), '$toplevel':notrace(call_repl_loop_hook(end, 0))) ? Unknown option (h for help)
^ Exception: (4) setup_call_cleanup('$toplevel':notrace(call_repl_loop_hook(begin, 0)), '$toplevel':'$query_loop'(0), '$toplevel':notrace(call_repl_loop_hook(end, 0))) ? Unknown option (h for help)
^ Exception: (4) setup_call_cleanup('$toplevel':notrace(call_repl_loop_hook(begin, 0)), '$toplevel':'$query_loop'(0), '$toplevel':notrace(call_repl_loop_hook(end, 0))) ? abort
% Execution Aborted
?- sqlite_disconnet(testo).
Correct to: "sqlite_disconnect(testo)"? yes
true.
This version uses the query interface so it expects a default connection to have been established.
?- sqlite_library_version(V).
false.
?- sqlite_connect('/tmp/testo.sqlite', testo, exists(false)).
?- sqlite_library_version(V).
V = '3.37.2'.
?- sqlite_disconnect(testo).
bibtex(Type,Key,Pairs) term of the same publication.
Produces all related publications on backtracking.
sqlite_connect('uniprot.sqlite', uniprot).
alias(Alias) option is given,
Connection is bound to Alias, else (b) an opaque atom is generated. If Connection is ground,
the opened can be accessed with Connection as a handle.
Options is a single term or a list of terms from the following:
at_module()).
The user should make sure the predicate is not previously defined.as_predicates(true)) is also given)arity, which asserts a
predicate matching the arity of the table.
both adds two predicates, one matching the arity and a single argument one.
The later can be interrogated with something like
?- phones( [name=naku, telephone=T] ).
unary only adds the unary version, and palette adds a suite of predicates
with arities from 1 to N, where N is the number of columns.
These can be interrogated by :
?- phones( name=Name ). ?- phones( name=naku, telephone=T ). ?- phones( [name=naku, telephone=T] ).
Predicated tables can be used to insert values to the database by virtue of all their columns are give ground values.
arity() option.When unary predicates are defined the columns can be interrogated/accessed by list pairs of the form Column=Value. Column-Value and Column:Value are also recognised.
So for example, for table phones with columns Name, Address and Phone, prosqlite will add
phones(_,_,_)
as a response to as_predicates, and
phones(_)
if Arity is unary.
The latter can be interrogated by
phones( ['Name'=naku','Phone'=Phone] ).
which will return the phone number(s) associated with individual named by naku.
Options is a single term or a list of terms from the following:
as_predicates(true)|+ was used when created Alias connection.)
Set to =|retractall to only retract predicated definitions, by default these are abolished.
prolog_flag/2 key sqlite_remove_predicates can set a new default value.Examples
?-
sqlite_disconnect(uniprot).
?- sqlite_connect( kword, kwordo, as_predicates(true) ).
true.
?- kwordo(A,B,C).
A = 1,
B = f11,
C = f21 ;
A = 2,
B = f12,
C = groupaa ;
false.
?- sqlite_disconnect( kwordo ).
true.
?- kwordo(A,B,C).
ERROR: Unknown procedure: kwordo/3 (DWIM could not correct goal)
?- sqlite_connect( kword, kwordo, as_predicates(true) ).
true.
?- kwordo(A,B,C).
A = 1,
B = f11,
C = f21 ;
A = 2,
B = f12,
C = groupaa ;
false.
?- sqlite_disconnect( kwordo, remove_predicates(retractall) ).
true.
?- kwordo(A,B,C).
false.
sqlite_format_query(uniprot, 'PRAGMA table_info(~w)'-Table, row(_, Column, _, _, _, _))
arity(Arity) is
delivered.position(Nth0) position of the Column in the table, first being 0.data_type(Dtype) the data type for the columnnullable(Null) can this column be set to the null valuedefautl(Default) the default value for theprimary_key(Key) is this column part of the primary key ?table_info-TableName.
sqlite_pragma( phone_db, encoding, Row).