prosqlite.pl -- proSQLite: a Prolog interface to the SQLite database system.

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 but it should also work on YAP Prolog.

The easiest way to install on SWI is via the package manager. Simply do:

     ?- pack_install( prosqlite ).

And you are good to go.

There are good/full examples in the sources, directory examples/. For instance test by :

     ?- [predicated].
     ?- predicated.

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.

Thanks 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).

author
- Nicos Angelopoulos
- Sander Canisius
version
- 1.0, 2014/12/24
- 1.1, 2016/10/9 changed to sqlite3_close() and fixed alias bug
- 1.2, 2016/11/22 added safe_column_names/2
- 1.4, 2018/3/18 fixed blobs support (see examples/two.pl), and logic for already opened file
- 1.6, 2020/5/29 recompiled for SWI 8.2
- 1.7, 2022/4/30 print message if new db file cannot be created
- 1.8, 2022/5/29 fixed major bug of deleting existing files introduced in 1.7 + minor doc + aarch64-linux binary
See also
- Sander Canisius, Nicos Angelopoulos and Lodewyk Wessels. proSQLite: Prolog file based databases via an SQLite interface. In the proceedings of Practical Aspects of Declarative languages (PADL 2013), (2013, Rome, Italy).
- Sander Canisius, Nicos Angelopoulos and Lodewyk Wessels. Exploring file based databases via an Sqlite interface. In the ICLP Workshop on Logic-based methods in Programming Environments, p. 2-9, (2012, Budapest, Hungary).
- http://stoics.org.uk/~nicos/pbs/padl2013-prosqlite.pdf
- http://stoics.org.uk/~nicos/sware/prosqlite
- http://stoics.org.uk/~nicos/sware/db_facts
- http://www.sqlite.org/
- files in examples/ directory
- also available as a SWI pack http://www.swi-prolog.org/pack/list
license
- MIT
To be done
- set pragmas
 sqlite_version(-Version, -Date)
The current version and its publication date.

Version is a Mj:Mn:Fx term and date is a date(Y,M,D) term.

 ? sqlite_version(V, D).
 V = 1:8:0,
 D = date(2022, 5, 29).
author
- nicos angelopoulos
 sqlite_binary_version(-Version, -Date)
The current version of the binaries. If the installed binaries are not compiled from the sources, then this might be different (=older) that the sqlite Porlog source version returned by sqlite_version/2. Version is a Mj:Mn:Fx term, and date is a date(Y,M,D) term.
 sqlite_citation(-Atom, -Bibterm)
Succeeds once for each publication related to this library. Atom is the atom representation suitable for printing while Bibterm is a bibtex(Type,Key,Pairs) term of the same publication. Produces all related publications on backtracking.
 sqlite_connect(+File, ?Alias)
Open a connection to an sqlite File. If Alias is a variable, an opaque atom is generated and unified to it. The opened db connection to file can be accessed via Alias.
  sqlite_connect('uniprot.sqlite', uniprot).
 sqlite_connect(+File, ?Connection, +Options)
Open a connection to an sqlite File. If Connection is unbound then if (a) 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 sinlge term or a list of terms from the following:
alias(Atom)
identify the connection as Alias (no default, interplays with Connection)
as_predicates(AsPred=false)
if true, create hook predicates that map each sqlite table to a prolog predicate. These are created in module user (see at_module()). The user should make sure the predicate is not previously defined.
at_module(AtMod=user)
the module at which the predicates will be asserted at (if as_predicates(true)) is also given)
arity(Arity=arity)
Arity denotes the arity of access clauses to be added in the prolog database that correspond to SQLite tables. The default is 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.

exists(Exists=true)
do not throw an error if file does not exist and Exists is false.
ext(Ext=sqlite)
database files are assumed to have an sqlite extension. To ovewrite this give a different Ext or '' for no extension.
table_as(Table, Pname, Arity)
map the table to predicate with name Pname. Arity should be defined for this representaition as per arity() option.
verbose(Verb=false)
Iff Verb==true print messages- currently about file used.

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.

author
- nicos angelopoulos
version
- 0.2 2018/03/17, fixed logic for existing connection to a file (existing alias is returned)
- 0.3 2022/04/30, add permissions error message, if new sqlite file cannot be created
See also
- examples/predicated.pl .
- pack(prosqlite/examples/two.pl)
 sqlite_disconnect(+Alias)
Terminate the connection to a SQLite database file.
  sqlite_disconnect(uniprot).
 sqlite_current_connection(-Connection)
Return or interrogate the name of open connection handles.
 sqlite_default_connection(-Connection)
Return or interrogate the name of the default connection. This is the last connection opened.
 sqlite_query(+Sql, -Row)
Post an Sql query to default connection and get row result in Row.
 sqlite_query(+Connection, +Sql, -Row)
Post an Sql query to Sqlite Connection and get row result in Row.
 sqlite_format_query(+Connection, +FAs, -Row)
Post a format style Sql query to Sqlite Connection and get row result in Row. FAs is a - pair structure : Format-Arguments.
   sqlite_format_query(uniprot, 'PRAGMA table_info(~w)'-Table, row(_, Column, _, _, _, _))
 sqlite_current_table(+Connection, -Table)
Return or interrogate tables in the Sqlite database associated with Connection.
 sqlite_current_table(+Connection, ?Table, -Facet)
Facet is a property of Table found at Connection. Currently only arity(Arity) is delivered.
 sqlite_table_column(+Connection, ?Table, -Column)
Return or interrogate tables and columns in the Sqlite database associated with Connection.
 sqlite_table_column(+Connection, ?Table, ?Column, -Facet)
Facet is one of:
 sqlite_pragma(+Alias, +Pragma, -Row)
Interrogate SQLite Pragmas. Currently only reading is supported. Pragma can be an atom or a - separated pair, as in table_info-TableName.
     sqlite_pragma( phone_db, encoding, Row).
 sqlite_table_count(+Connection, +Table, -Count)
True if Count is the number of rows in Sqlite Connection associated Table.
 sqlite_date_sql_atom(Date, Sql)
Convert between a Prolog date/3 term and an Sql atom. The conversion is bidirectional.