Prolog to SQL translator

Author(s): C. Draxler. Adapted by M. Hermenegildo and I. Caballero.

Version: 1.11#222 (2004/5/24, 13:8:7 CEST)

Version of last change: 1.11#60 (2003/11/27, 21:23:0 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:

%jcf%:- use_module(library('persdb_sql/pl2sql')).
:- use_module(library('persdb_mysql/pl2sql')).
%jcf%
:- use_module(library(strings)).

:- multifile [relation/3,attribute/4].
:- data [relation/3,attribute/4].

relation(product,3,'PRODUCT').
attribute(1,'PRODUCT','ID',int).
attribute(2,'PRODUCT','QUANTITY',int).
attribute(3,'PRODUCT','NAME',string).

main :- 
     pl2sqlstring( f(L,K), 
          ((product(L,N,a); product(L,N,b)),
           \+ product(2,3,b), 
           L + 2 > avg(Y, Z^product(Z,Y,a)),
           K is N + max(X, product(X,2,b))
           ), T),
     write_string(T).
     
%%     printqueries(T).

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.

• Usage and interface (pl2sql)
• Documentation on exports (pl2sql)
• Documentation on multifiles (pl2sql)
• Documentation on internals (pl2sql)
• Known bugs and planned improvements (pl2sql)

Usage and interface (pl2sql)

Documentation on exports (pl2sql)

PREDICATE: pl2sqlstring/3:

Usage: pl2sqlstring(ProjectionTerm, DatabaseGoal, SQLQueryString)

• Description: This is the top level predicate which translates complex Prolog goals into the corresponding SQL code. The query code is prepared in such a way that the result is projected onto the term 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.
• Call and exit should be compatible with: 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)
• The following properties should hold at call time: ProjectionTerm is currently a term which is not a free variable. (term_typing:nonvar/1) DatabaseGoal is currently a term which is not a free variable. (term_typing:nonvar/1) SQLQueryString is a free variable. (term_typing:var/1)

REGTYPE: querybody/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:

• Atomic goals, which must have been defined via 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.
• Database comparison goals, whose main functor must be a database comparison operator (see pl2sql: comparison/2) and whose arguments must be database arithmetic expressions.
• Database calls to is/2. The left side of such a call may be either unbound, in which case it is bound to the result of evaluating the right side, or bound in which case an equality condition is tested. The right side must be a database arithmetic expression.

The binding of variables follows Prolog rules:

• variables are bound by positive base goals and on the left side of the is/2 predicate.
• Comparison operations, negated goals, and right sides of the 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:

• Numeric constants (i.e., integers, reals, etc.).
• Bound variables, i.e., variables which will be bound during execution through occurrence within a positive database goal, or by a preceding arithmetic function.
• Database arithmetic functions, which are a subset of those typically accepted within is/2 (see pl2sql: arithmetic_functor/2).
• Database aggregation functions, each of which has two arguments: a variable indicating the argument over which the function is to be computed, and a goal argument which must contain in at least one argument position the variable (e.g. 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)

• Description: DBGoal is a database query goal.

REGTYPE: projterm/1:

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:

• The functor of ProjectionTerm may not be one of the built-in predicates, i.e. ',', ';', etc. are not allowed.
• Only variables and constants are allowed as arguments, i.e., no structured terms may appear.

Usage: projterm(DBProjTerm)

• Description: DBProjTerm is a database projection term.

REGTYPE: sqlstring/1:

sqlstring(S) :-
        string(S).

Usage: sqlstring(S)

• Description: S is a string containing SQL code.

PREDICATE: pl2sqlterm/3:

Usage: pl2sqlterm(ProjectionTerm, DatabaseGoal, SQLQueryTerm)

• Description: Similar to pl2sqlstring/3 except that SQLQueryTerm is a representation of the SQL query as a Prolog term.
• Call and exit should be compatible with: ProjectionTerm is a database projection term. (pl2sql:projterm/1) DatabaseGoal is a database query goal. (pl2sql:querybody/1) SQLQueryTerm is a list of sqlterms. (basic_props:list/2)
• The following properties should hold at call time: ProjectionTerm is currently a term which is not a free variable. (term_typing:nonvar/1) DatabaseGoal is currently a term which is not a free variable. (term_typing:nonvar/1) SQLQueryTerm is a free variable. (term_typing:var/1)

PREDICATE: sqlterm2string/2:

Usage: sqlterm2string(Queries, QueryString)

• Description: QueryString is a string representation of the list of queries in Prolog-term format in Queries.
• Call and exit should be compatible with: Queries is a list of sqlterms. (basic_props:list/2) QueryString is a string containing SQL code. (pl2sql:sqlstring/1)
• The following properties should hold at call time: Queries is currently a term which is not a free variable. (term_typing:nonvar/1) QueryString is a free variable. (term_typing:var/1)

(UNDOC_REEXPORT): sqltype/1:

Imported from sqltypes (see the corresponding documentation for details).

Documentation on multifiles (pl2sql)

PREDICATE: sql__relation/3:

The predicate is multifile.

The predicate is of type data.

Usage: sql__relation(PredName, Arity, TableName)

• Description: This predicate, together with 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.
• Call and exit should be compatible with: PredName is an atom. (basic_props:atm/1) Arity is an integer. (basic_props:int/1) TableName is an atom. (basic_props:atm/1)

PREDICATE: sql__attribute/4:

The predicate is multifile.

The predicate is of type data.

Usage: sql__attribute(ANumber, TblName, AName, AType)

• Description: This predicate maps the argument positions of a Prolog predicate to the SQL attributes of its corresponding table. The types of the arguments need to be specified, and this information is used for consistency checking during the translation and for output formatting. A minimal type system is provided to this end. The allowable types are given by 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.
• Call and exit should be compatible with: 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)

Documentation on internals (pl2sql)

PREDICATE: query_generation/3:

Usage: query_generation(ListOfConjunctions, ProjectionTerm, ListOfQueries)

• Description: For each Conjunction in 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:
  • the functor of a goal that is not a comparison operation is translated to a relation name with a range variable,
  • negated goals are translated to NOT EXISTS-subqueries with * projection,
  • comparison operations are translated to comparison operations in the WHERE-clause,
  • aggregate function terms are translated to aggregate function (sub)queries.
  The arguments of a goal are translated as follows:
  • variables of a goal are translated to qualified attributes,
  • variables occurring in several goals are translated to equality comparisons (equi join) in the WHERE-clause,
  • constant arguments are translated to equality comparisons in the WHERE-clause.
  Arithmetic functions are treated specially ( translate_arithmetic_function/5). See also querybody/1 for details on the syntax accepted and restrictions.
• The following properties should hold at call time: ListOfConjunctions is currently a term which is not a free variable. (term_typing:nonvar/1) ProjectionTerm is currently a term which is not a free variable. (term_typing:nonvar/1) ListOfQueries is a free variable. (term_typing:var/1)

PREDICATE: translate_conjunction/5:

Usage: translate_conjunction(Conjunction, SQLFrom, SQLWhere, Dict, NewDict)

• Description: Translates a conjunction of goals (represented as a list of goals preceeded by existentially quantified variables) to FROM-clauses and WHERE-clauses of an SQL query. A dictionary containing the associated SQL table and attribute names is built up as an accumulator pair (arguments Dict and NewDict).

PREDICATE: translate_goal/5:

Usage: translate_goal(Goal, SQLFrom, SQLWhere, Dict, NewDict)

• Description: Translates:
  • a positive database goal to the associated FROM- and WHERE clause of an SQL query,
  • a negated database goal to a negated existential subquery,
  • an arithmetic goal to an arithmetic expression or an aggregate function query,
  • a comparison goal to a comparison expression, and
  • a negated comparison goal to a comparison expression with the opposite comparison operator.

PREDICATE: translate_arithmetic_function/5:

Usage: translate_arithmetic_function(Result, Expression, SQLWhere, Dict, NewDict)

• Description: Arithmetic functions (left side of is/2 operator is bound to value of expression on right side) may be called with either:
  • 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.

PREDICATE: translate_comparison/5:

Usage: translate_comparison(LeftArg, RightArg, CompOp, Dict, SQLComparison)

• Description: Translates the left and right arguments of a comparison term into the appropriate comparison operation in SQL. The result type of each argument expression is checked for type compatibility.

PREDICATE: aggregate_function/3:

Usage: aggregate_function(AggregateFunctionTerm, Dict, AggregateFunctionQuery)

• Description: Supports the Prolog aggregate function terms listed in aggregate_functor/2 within arithmetic expressions. Aggregate functions are translated to the corresponding SQL built-in aggregate functions.

PREDICATE: comparison/2:

Usage: comparison(PrologOperator, SQLOperator)

• Description: Defines the mapping between Prolog operators and SQL operators:

  comparison(=,=).
  comparison(<,<).
  comparison(>,>).
  comparison(@<,<).
  comparison(@>,>).
  

• Call and exit should be compatible with: PrologOperator is an atom. (basic_props:atm/1) SQLOperator is an atom. (basic_props:atm/1)

PREDICATE: negated_comparison/2:

Usage: negated_comparison(PrologOperator, SQLOperator)

• Description: Defines the mapping between Prolog operators and the complementary SQL operators:

  negated_comparison(=,<>).
  negated_comparison(\==,=).
  negated_comparison(>,=<).
  negated_comparison(=<,>).
  negated_comparison(<,>=).
  negated_comparison(>=,<).
  

• Call and exit should be compatible with: PrologOperator is an atom. (basic_props:atm/1) SQLOperator is an atom. (basic_props:atm/1)

PREDICATE: arithmetic_functor/2:

Usage: arithmetic_functor(PrologFunctor, SQLFunction)

• Description: Defines the admissible arithmetic functions on the Prolog side and their correspondence on the SQL side:

  arithmetic_functor(+,+).
  arithmetic_functor(-,-).
  arithmetic_functor(*,*).
  arithmetic_functor(/,/).
  

• Call and exit should be compatible with: PrologFunctor is an atom. (basic_props:atm/1) SQLFunction is an atom. (basic_props:atm/1)

PREDICATE: aggregate_functor/2:

Usage: aggregate_functor(PrologFunctor, SQLFunction)

• Description: Defines the admissible aggregate functions on the Prolog side and their correspondence on the SQL side:

  aggregate_functor(avg,'AVG').
  aggregate_functor(min,'MIN').
  aggregate_functor(max,'MAX').
  aggregate_functor(sum,'SUM').
  aggregate_functor(count,'COUNT').
  

• Call and exit should be compatible with: PrologFunctor is an atom. (basic_props:atm/1) SQLFunction is an atom. (basic_props:atm/1)

Known bugs and planned improvements (pl2sql)

• Need to separate db predicate names by module.

Go to the first, previous, next, last section, table of contents.