Author(s): I. Caballero, D. Cabeza, J.M. Gómez, M. Hermenegildo, J. F. Morales, and M. Carro, clip@dia.fi.upm.es
, http://www.clip.dia.fi.upm.es/
, The CLIP Group, Facultad de Informática, Universidad Politécnica de Madrid.
Version: 1.11#222 (2004/5/24, 13:8:7 CEST)
Version of last change: 1.11#61 (2003/11/27, 21:23:22 CET)
The purpose of this library is to implement an instance of the generic concept of
persistent predicates, where external
relational databases are used for storage (see the documentation of the
persdb
library and [CHGT98,Par97] for details). To this end, this library exports SQL persistent versions of the
assertz_fact/1
,
retract_fact/1
and
retractall_fact/1
builtin predicates. Persistent predicates also allow
concurrent updates from several programs, since each update is atomic.
The notion of persistence provides a very natural and transparent way to access database relations from a Prolog program. Stub definitions are provided for such predicates which access the database when the predicate is called (using the
db_client
library). A
Prolog to SQL translator is used to generate the required
SQL code dynamically (see library
pl2sql
).
This library also provides facilities for reflecting more complex views of the database relations as Prolog predicates. Such views can be constructed as conjunctions, disjunctions, projections, etc. of database relations. Also, SQL-like aggregation operations are supported.
The architecture of the low-level implementation of the database interface was defined with two goals in mind:
In order to allow the flexibility mentioned above, a client-sever architecture was chosen. At the server side, a MySQL server connects to the databases using the MySQL. At the client side, a MySQL client interface connects to this server. The server daemon (mysqld) should be running in the server machine; check your MySQL documentation on how to do that.
After the connection is established a client can send commands to the mediator server which will pass them to the corresponding database server, and then the data will traverse in the opposite direction. These messages include logging on and off from the database, sending SQL queries, and receiving the responses.
The low level implementation of the current library is accomplished by providing abstraction levels over the MySQL interface library. These layers of abstraction implement the persistent predicate view, build the appropriate commands for the database using a translator of Prolog goals to SQL commands, issue such commands using the mediator send/receive procedures, parse the responses, and present such responses to the Prolog engine via backtracking.
:- module(_, _, [persdb_mysql, functions]). % Some contributions from Guy-Noel Mathieu :- use_module(library(write)). :- use_module(library(format)). :- use_module(user_and_password). sql_persistent_location(people, db(people, User, Password, HP)):- mysql_host_and_port(HP), mysql_user(User), mysql_password(Password). :- sql_persistent( people(string, string, int), %% Prolog predicate and types people(name, sex, age), %% Table name and attributes people). %% Database local id % Low level MySQL interface. :- use_module(library('persdb_mysql/mysql_client')). main :- nl, display('Creating database'), nl,nl, create_people_db, nl, display('Inserting people'), nl,nl, insert_people, nl, display('Showing people'), nl,nl, show_people, display('Removing John'), nl,nl, remove_people(john,_Y,_Z), display('Showing people, after removing John'), nl,nl, show_people, remove_people(_X,female,_Z), display('Showing people, after removing female'), nl,nl, show_people. % Create a database and a table of people. Still needs to be ironed out. create_people_db :- mysql_user(User), mysql_password(Password), mysql_host_and_port(HP), mysql_connect(HP, '', User, Password, DbConnection), write(~mysql_query(DbConnection, "drop database if exists people")), nl, write(~mysql_query(DbConnection, "create database people")), nl, write(~mysql_query(DbConnection, "use people")), nl, write(~mysql_query(DbConnection, "create table people(name char(16) not null, sex text, age int, primary key(name))")), nl, mysql_disconnect(DbConnection). % Inserts people into the 'people' table. male(john, 15). male(peter, 24). male(ralph, 24). male(bart, 50). female(kirsten, 24). female(mary, 17). female(jona, 12). female(maija, 34). %% Tuples are inserted as in the local Prolog dynamic database insert_people :- ( male(N, A), display('Inserting '), display(male(N, A)), nl, dbassertz_fact(people(N, male, A)), fail ; true ), ( female(N, A), display('Inserting '), display(female(N, A)), nl, dbassertz_fact(people(N, female, A)), fail ; true ). %% Removes people from the 'people' table. %% Still not working in MySQL due to differences in SQL: working on it. remove_people(A, B, C) :- dbretractall_fact(people(A, B, C)). remove_people_2(A, B, C) :- dbretract_fact(people(A, B, C)), display('Removed row '), display(people(A, B, C)), nl, fail. remove_people_2(_, _, _) :- display('No more rows'), nl. show_people :- people(Name, Sex, Age), display(people(Name, Sex, Age)), nl, fail. show_people :- display('No more rows'), nl.
persdbrt_mysql
)use_package/1
declaration:
:- module(bar, [main/1], [persdb_mysql]).or
:- module(bar, [main/1]). :- include(library(persdb_mysql)).
:- use_package([persdb_mysql]).or
:- include(library(persdb_mysql)).
persdbtr_mysql.pl
and persdbrt_mysql.pl
) and includes some needed declarations.
persdbrt_mysql
)Usage:
Usage: dbassertz_fact(Fact)
assertz_fact/1
: the current instance of Fact
is interpreted as a fact (i.e., a relation tuple) and is added to the end of the definition of the corresponding predicate. If any integrity constraint violation is done (database stored predicates), an error will be displayed. The predicate concerned must be statically (
sql_persistent/3
) or dinamically (
make_sql_persistent/3
) declared. Any uninstantiated variables in the Fact
will be replaced by new, private variables. Note: assertion of facts with uninstantiated variables not implemented at this time.
Fact
is a fact (a term whose main functor is not ':-'/2
).
(persdbrt_mysql:fact/1
)
Fact
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: dbretract_fact(Fact)
retract_fact/1
: deletes on backtracking all the facts which unify with Fact
. The predicate concerned must be statically (
sql_persistent/3
) or dinamically (
make_sql_persistent/3
) declared.
Fact
is a fact (a term whose main functor is not ':-'/2
).
(persdbrt_mysql:fact/1
)
Fact
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: dbcurrent_fact(Fact)
current_fact/1
: the fact Fact
exists in the current database. The predicate concerned must be declared
sql_persistent/3
. Provides on backtracking all the facts (tuples) which unify with Fact
.
Fact
is a fact (a term whose main functor is not ':-'/2
).
(persdbrt_mysql:fact/1
)
Fact
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: dbretractall_fact(Fact)
retractall_fact/1
: when called deletes all the facts which unify with Fact
. The predicate concerned must be statically (
sql_persistent/3
) or dinamically (
make_sql_persistent/3
) declared.
Fact
is a fact (a term whose main functor is not ':-'/2
).
(persdbrt_mysql:fact/1
)
Fact
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Meta-predicate with arguments: make_sql_persistent(addmodule,?,?)
.
Usage: make_sql_persistent(PrologPredTypes, TableAttributes, Keyword)
sql_persistent/3
declaration.
PrologPredTypes
is a structure describing a Prolog predicate name with its types.
(persdbrt_mysql:prologPredTypes/1
)
TableAttributes
is a structure describing a table name and some attributes.
(persdbrt_mysql:tableAttributes/1
)
Keyword
is the name of a persistent storage location.
(persdbrt_mysql:persLocId/1
)
Meta-predicate with arguments: dbfindall(?,?,goal,?)
.
Usage: dbfindall(DBId, Pattern, ComplexGoal, Results)
findall/3
, but Goal
is executed in database DBId
. Certain restrictions and extensions apply to both Pattern
and ComplexGoal
stemming from the Prolog to SQL translation involved (see the corresponding type definitions for details).
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
Pattern
is a database projection term.
(pl2sql:projterm/1
)
ComplexGoal
is a database query goal.
(pl2sql:querybody/1
)
Results
is a list.
(basic_props:list/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Pattern
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
ComplexGoal
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Results
is a free variable.
(term_typing:var/1
)
Usage: dbcall(DBId, ComplexGoal)
ComplexGoal
to database DBId
for evaluation. ComplexGoal
must be a call to a persistent predicate which resides in database DBId
.
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
ComplexGoal
is a database query goal.
(pl2sql:querybody/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
ComplexGoal
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: sql_query(DBId, SQLString, AnswerTableTerm)
ResultTerm
is the response from database DBId
to the
SQL query in SQLString
to database DBId
. AnswerTableTerm
can express a set of tuples, an error answer or a 'ok' response (see
answertableterm/1
for details). At the moment,
sql_query/3
log in and out for each query. This should be changed to log in only the first time and log out on exit and/or via a timer in the standard way.
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
SQLString
is a string containing SQL code.
(pl2sql:sqlstring/1
)
AnswerTableTerm
is a response from the ODBC database interface.
(persdbrt_mysql:answertableterm/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
SQLString
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage 1: sql_get_tables(Location, Tables)
Tables
contains the tables available in Location
.
Location
is a structure describing a database.
(persdbrt_mysql:database_desc/1
)
Tables
is a list of atm
s.
(basic_props:list/2
)
Location
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Tables
is a free variable.
(term_typing:var/1
)
Usage 2: sql_get_tables(DbConnection, Tables)
Tables
contains the tables available in DbConnection
.
DbConnection
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
Tables
is a list of atm
s.
(basic_props:list/2
)
DbConnection
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Tables
is a free variable.
(term_typing:var/1
)
Usage 1: sql_table_types(Location, Table, AttrTypes)
AttrTypes
are the attributes and types of Table
in Location
.
Location
is a structure describing a database.
(persdbrt_mysql:database_desc/1
)
Table
is an atom.
(basic_props:atm/1
)
AttrTypes
is a list.
(basic_props:list/1
)
Location
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Table
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
AttrTypes
is a free variable.
(term_typing:var/1
)
Usage 2: sql_table_types(DbConnection, Table, AttrTypes)
AttrTypes
are the attributes and types of Table
in DbConnection
.
DbConnection
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
Table
is an atom.
(basic_props:atm/1
)
AttrTypes
is a list.
(basic_props:list/1
)
DbConnection
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Table
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
AttrTypes
is a free variable.
(term_typing:var/1
)
Usage: socketname(IPP)
IPP
is a structure describing a complete TCP/IP port address.
Usage: dbname(DBId)
DBId
is the identifier of an database.
Usage: user(User)
User
is a user name in the database.
Usage: passwd(Passwd)
Passwd
is the password for the user name in the database.
Usage: projterm(DBProjTerm)
DBProjTerm
is a database projection term.
Usage: querybody(DBGoal)
DBGoal
is a database query goal.
Imported from
sqltypes
(see the corresponding documentation for details).
persdbrt_mysql
)
Relates names of locations (the Keyword
s) with descriptions of such locations (Location
s).
The predicate is multifile.
The predicate is of type data.
Usage: sql_persistent_location(Keyword, DBLocation)
DBLocation
is a relational database, in which case the predicate is stored as tuples in the database.
Keyword
is the name of a persistent storage location.
(persdbrt_mysql:persLocId/1
)
DBLocation
is a structure describing a database.
(persdbrt_mysql:database_desc/1
)
persdbrt_mysql
)tuple(T) :- list(T,atm). tuple(T) :- list(T,atm).
Usage: tuple(T)
T
is a tuple of values from the ODBC database interface.
Usage: dbconnection(H)
H
a unique identifier of a database session connection.
Usage: :- sql_persistent(PrologPredTypes, TableAttributes, Keyword)
.
PrologPredTypes
as SQL persistent. Keyword
is the
name of a location where the
persistent storage for the predicate is kept, which in this case must be an external relational database. The description of this database is given through the
sql_persistent_location
predicate, which must contain a fact in which the first argument unifies with Keyword
. TableAttributes
provides the
table name and
attributes in the database corresponding respectively to the predicate name and arguments of the (virtual) Prolog predicate.
Although a predicate may be persistent, other usual clauses can be defined in the source code. When querying a persistent predicate with non-persistent clauses, persistent and non-persisten clauses will be evaluated in turn; the order of evaluation is the usual Prolog order, considering that persistent clauses are defined in the program point where the
sql_persistent/3
declaration is.
Example:
:- sql_persistent(product( integer, integer, string, string ), product( quantity, id, name, size ), radiowebdb). sql_persistent_location(radiowebdb, db('SQL Anywhere 5.0 Sample', user, pass, 'r2d5.dia.fi.upm.es':2020)).
PrologPredTypes
is a structure describing a Prolog predicate name with its types.
(persdbrt_mysql:prologPredTypes/1
)
TableAttributes
is a structure describing a table name and some attributes.
(persdbrt_mysql:tableAttributes/1
)
Keyword
is the name of a persistent storage location.
(persdbrt_mysql:persLocId/1
)
Usage: db_query(DBId, ProjTerm, Goal, ResultTerm)
ResultTerm
contains all the
tuples which are the response from database DBId
to the Prolog query Goal
, projected onto ProjTerm
. Uses
pl2sqlstring/3
for the
Prolog to SQL translation and
sql_query/3
for posing the actual query.
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
ProjTerm
is a database projection term.
(pl2sql:projterm/1
)
Goal
is a database query goal.
(pl2sql:querybody/1
)
ResultTerm
is a tuple of values from the ODBC database interface.
(persdbrt_mysql:tuple/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
ProjTerm
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Goal
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: db_query_one_tuple(DBId, ProjTerm, Goal, ResultTerm)
ResultTerm
is one of the
tuples which are the response from database DBId
to the Prolog query Goal
, projected onto ProjTerm
. Uses
pl2sqlstring/3
for the
Prolog to SQL translation and
sql_query_one_tuple/3
for posing the actual query. After last tuple has been reached, a null tuple is unified with ResultTerm, and the connection to the database finishes.
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
ProjTerm
is a database projection term.
(pl2sql:projterm/1
)
Goal
is a database query goal.
(pl2sql:querybody/1
)
ResultTerm
is a predicate containing a tuple.
(persdbrt_mysql:answertupleterm/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
ProjTerm
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Goal
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
Usage: sql_query_one_tuple(DBId, SQLString, ResultTuple)
ResultTuple
contains an element from the set of tuples which represents the response in DBId
to the
SQL query SQLString
. If the connection is kept, succesive calls return consecutive tuples, until the last tuple is reached. Then a null tuple is unified with ResultTuple
and the connection is finished (calls to
mysql_disconnect/1
).
DBId
a unique identifier of a database session connection.
(mysql_client:dbconnection/1
)
SQLString
is a string containing SQL code.
(pl2sql:sqlstring/1
)
ResultTuple
is a tuple of values from the ODBC database interface.
(persdbrt_mysql:tuple/1
)
DBId
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
SQLString
is currently a term which is not a free variable.
(term_typing:nonvar/1
)
persdbrt_mysql
)Go to the first, previous, next, last section, table of contents.