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).
sqlite_version( 1:4:0, date(2018,3,18) ). % proper blob support & opened_already with associated portray_message/2 fix
bibtex(Type,Key,Pairs)term of the same publication. Produces all related publications on backtracking.
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 in all transactions
as_predicates(AsPred)if true, create hook predicates that map
at_module(AtMod)the module at which the predicates will be asserted at (if
as_predicates(true)) is also given). Default is
table_as(Table,Pname,Arity)map the table to predicate with name Pname. Arity should be defined for this representaition as per
arity(arity)Arity denotes the arity of access clauses to be added in the prolog database that
arity, which asserts a predicate matching the arity of the table.
bothadds two predicates, one matching the arity and a single argument one. The later can be interrogated with something like
?- phones( [name=naku, telephone=T] ).
unaryonly adds the unary version, and
paletteadds 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] ).
exists(Boolean)do not throw an error if file does not exist and
ext(Ext)database files are assumed to have an sqlite extension at their end. To ovewrite this give Ext ('' for no extension).
verbose(Verb)Iff Verb==true printa message about which file is used- from within C (false).
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
as a response to as_predicates, and
if Arity is
The latter can be interrogated by
phones( ['Name'=naku','Phone'=Phone] ).
which will return the phone
number(s) associated with individual named by
sqlite_format_query(uniprot, 'PRAGMA table_info(~w)'-Table, row(_, Column, _, _, _, _))
position(Nth0)position of the Column in the table, first being 0.
data_type(Dtype)the data type for the column
nullable(Null)can this column be set to the null value
defautl(Default)the default value for the
primary_key(Key)is this column part of the primary key ?
sqlite_pragma( phone_db, encoding, Row).