Author(s): C. Draxler. Adapted by M. Hermenegildo and I. Caballero.
Version: 1.10#7 (2006/4/26, 19:22:13 CEST)
Version of last change: 1.9#112 (2003/11/27, 20:54:19 CET)
This library performs translation of Prolog queries into
SQL. The code is an adaptation for Ciao of the
Prolog to SQL compiler written by Christoph Draxler, CIS Centre for Information and Speech Processing, Ludwig-Maximilians-University Munich, draxler@cis.uni-muenchen.de
, Version 1.1. Many thanks to Christoph for allowing us to include this adaptation of his code with Ciao.
The translator needs to know the correspondence between Prolog predicates and the
SQL tables in the database. To this end this module exports two multifile predicates,
sql__relation/3
and
sql__attribute/4
. See the description of these predicates for details on how such correspondance is specified.
The main entry points to the translator are
pl2sqlstring/3
and
pl2sqlterm/3
. Details on the types of queries allowed can be found in the description of these predicates.
Example: the following program would print out a term representing the SQL query corresponding to the given Prolog query:
Note: while the translator can be used directly in programs, it is more convenient to use a higher-level abstraction:
persistent predicates (implemented in the
persdb
library). The notion of persistent predicates provides a completely transparent interface between Prolog and relational databases. When using this library, the Prolog to SQL translation is called automatically as needed.
pl2sql
):- use_module(library(pl2sql)).
pl2sql
)
Usage: pl2sqlstring(+ProjectionTerm, +DatabaseGoal, -SQLQueryString)
ProjectionTerm
(also in a similar way to the first argument of
setof/3
)). See the predicate
translate_projection/3
for restrictions on this term.
SQLQueryString
contains the code of the
SQL query, ready to be sent to an
SQL server.
+ProjectionTerm
is a database projection term.
(pl2sql:projterm/1
)
+DatabaseGoal
is a database query goal.
(pl2sql:querybody/1
)
-SQLQueryString
is a string containing SQL code.
(pl2sql:sqlstring/1
)
DBGoal
is a goal meant to be executed in the external database. It can be a complex term containing
conjunctions,
disjunctions, and
negations, of:
sql__relation/3
and
sql__attribute/4
and reside in the (same) database. Their arguments must be either ground or free variables. If they are ground, they must be bound to constants of the type declared for that argument. If an argument is a free variable, it may share with (i.e., be the same variable as) other free variables in other goal arguments.
pl2sql
:
comparison/2
) and whose arguments must be database arithmetic expressions.
The binding of variables follows Prolog rules:
is/2
predicate.
is/2
predicate do not return variable bindings and may even require all arguments to be bound for a safe evaluation.
Database arithmetic expressions may contain:
is/2
(see
pl2sql
:
arithmetic_functor/2
).
avg(Seats, plane(Type, Seats))
). The goal argument may only be a conjunction of (positive or negative) base goals. See
pl2sql
:
aggregate_functor/2
for the admissible aggregate functions.
In addition, variables
can be existentially quantified using
^/2
(in a similar way to how it is done in
setof/3
).
Note that it is assumed that the arithmetic operators in Prolog and SQL are the same, i.e., +
is addition in Prolog and in SQL, etc.
Usage: querybody(DBGoal)
DBGoal
is a database query goal.
DBProjTerm
is a term onto which the result of a database query code is (in a similar way to the first argument of
setof/3
)).
A ProjectionTerm
must meet the following restrictions:
ProjectionTerm
may not be one of the built-in predicates, i.e. ',', ';', etc. are not allowed.
Usage: projterm(DBProjTerm)
DBProjTerm
is a database projection term.
sqlstring(S) :- string(S).
Usage: sqlstring(S)
S
is a string containing SQL code.
Usage: pl2sqlterm(+ProjectionTerm, +DatabaseGoal, -SQLQueryTerm)
pl2sqlstring/3
except that SQLQueryTerm
is a representation of the SQL query as a Prolog term.
+ProjectionTerm
is a database projection term.
(pl2sql:projterm/1
)
+DatabaseGoal
is a database query goal.
(pl2sql:querybody/1
)
-SQLQueryTerm
is a list of sqlterm
s.
(basic_props:list/2
)
Usage: sqlterm2string(+Queries, -QueryString)
QueryString
is a string representation of the list of queries in Prolog-term format in Queries
.
+Queries
is a list of sqlterm
s.
(basic_props:list/2
)
-QueryString
is a string containing SQL code.
(pl2sql:sqlstring/1
)
Imported from
sqltypes
(see the corresponding documentation for details).
pl2sql
)The predicate is multifile.
The predicate is of type data.
Usage: sql__relation(PredName, Arity, TableName)
sql__attribute/4
, defines the correspondence between Prolog predicates and the
SQL tables in the database. These two relations constitute an extensible meta-database which maps
Prolog predicate names to
SQL table names, and
Prolog predicate argument positions to
SQL attributes.
PredName
is the chosen Prolog name for an SQL table. Arity
is the number of arguments of the predicate. TableName
is the name of the SQL table in the Database Management System.
PredName
is an atom.
(basic_props:atm/1
)
Arity
is an integer.
(basic_props:int/1
)
TableName
is an atom.
(basic_props:atm/1
)
The predicate is multifile.
The predicate is of type data.
Usage: sql__attribute(ANumber, TblName, AName, AType)
sqltype/1
.
ANumber
is the argument number in the Prolog relation. TblName
is the name of the SQL table in the Database Management System. AName
is the name of the corresponding attribute in the table. AType
is the (translator) data type of the attribute.
ANumber
is an integer.
(basic_props:int/1
)
TblName
is an atom.
(basic_props:atm/1
)
AName
is an atom.
(basic_props:atm/1
)
AType
is an SQL data type supported by the translator.
(sqltypes:sqltype/1
)
pl2sql
)
Usage: query_generation(+ListOfConjunctions, +ProjectionTerm, -ListOfQueries)
ListOfConjunctions
, translate the pair (ProjectionTerm, Conjunction)
to an SQL query and connect each such query through a
UNION-operator to result in the ListOfQueries
.
A Conjunction consists of positive or negative subgoals. Each subgoal is translated as follows:
variables of a goal
are translated to
qualified attributes,
translate_arithmetic_function/5
). See also
querybody/1
for details on the syntax accepted and restrictions.
Usage: translate_conjunction(Conjunction, SQLFrom, SQLWhere, Dict, NewDict)
Dict
and NewDict
).
Usage: translate_goal(Goal, SQLFrom, SQLWhere, Dict, NewDict)
Usage: translate_arithmetic_function(Result, Expression, SQLWhere, Dict, NewDict)
Result
unbound: then Result
is bound to the value of the evaluation of Expression
,
Result
bound: then an equality condition is returned between the value of Result
and the value of the evaluation of Expression
.
Only the equality test shows up in the WHERE clause of an SQLquery.
Usage: translate_comparison(LeftArg, RightArg, CompOp, Dict, SQLComparison)
Usage: aggregate_function(AggregateFunctionTerm, Dict, AggregateFunctionQuery)
Usage: comparison(PrologOperator, SQLOperator)
comparison(=,=). comparison(<,<). comparison(>,>). comparison(@<,<). comparison(@>,>).
PrologOperator
is an atom.
(basic_props:atm/1
)
SQLOperator
is an atom.
(basic_props:atm/1
)
Usage: negated_comparison(PrologOperator, SQLOperator)
negated_comparison(=,<>). negated_comparison(\==,=). negated_comparison(>,=<). negated_comparison(=<,>). negated_comparison(<,>=). negated_comparison(>=,<).
PrologOperator
is an atom.
(basic_props:atm/1
)
SQLOperator
is an atom.
(basic_props:atm/1
)
Usage: arithmetic_functor(PrologFunctor, SQLFunction)
arithmetic_functor(+,+). arithmetic_functor(-,-). arithmetic_functor(*,*). arithmetic_functor(/,/).
PrologFunctor
is an atom.
(basic_props:atm/1
)
SQLFunction
is an atom.
(basic_props:atm/1
)
Usage: aggregate_functor(PrologFunctor, SQLFunction)
aggregate_functor(avg,'AVG'). aggregate_functor(min,'MIN'). aggregate_functor(max,'MAX'). aggregate_functor(sum,'SUM'). aggregate_functor(count,'COUNT').
PrologFunctor
is an atom.
(basic_props:atm/1
)
SQLFunction
is an atom.
(basic_props:atm/1
)
pl2sql
)Go to the first, previous, next, last section, table of contents.