The View Level Interface

The view level interface can be used for the definition of rules whose bodies includes only imported database predicates (by using the relation level interface) described above and aggregate predicates (defined below). When they are invoked, rules are translated into complex database queries, which are then executed taking advantage of the query processing ability of the DBMS's.

One can use the view level interface through the predicate odbc_query/2:

| ?- odbc_query('RuleName'(ARG1, ..., ARGn), DatabaseGoal).

All arguments are standard Prolog terms. ARG1, ARG2, ..., ARGn defines the attributes to be retrieved from the database, while DatabaseGoal defines the selection restrictions and join conditions.

The compiler is a simple extension of [4] which generates SQL queries with bind variables and handles NULL values as described in Section 7.3.7. It allows negation, the expression of arithmetic functions, and higher-order constructs such as grouping, sorting, and aggregate functions.

Database goals are translated according to the following rules from [4]:

• Disjunctive goals translate to distinct SQL queries connected through the UNION operator.
• Goal conjunctions translate to joins.
• Negated goals translate to negated EXISTS subqueries.
• Variables with single occurrences in the body are not translated.
• Free variables translate to grouping attributes.
• Shared variables in goals translate to equi-join conditions.
• Constants translate to equality comparisons of an attribute and the constant value.
• Nulls are translated to IS NULL conditions.

For more examples and implementation details see [4].

In the following, we show the definition of a simple join view between the two database predicates Room and Floor.

Assuming the declarations:

| ?- odbc_import('Room'('RoomNo','CostPerDay','Capacity','FId'),room).

| ?- odbc_import('Floor'('FId','','FName'),floor).

use

| ?- odbc_query(rule1(RoomNo,FName),
                  (room(RoomNo,_,_,FId),floor(FId,_,FName))).
yes

| ?- rule1(RoomNo,FloorName).

Prolog/SQL compiler generates the SQL statement:

SELECT rel1.RoomNo , rel2.FName FROM Room rel1 , Floor rel2 
WHERE rel2.FId = rel1.FId;

RoomNo = 101
FloorName = First Floor

Backtracking can then be used to retrieve the next row of the view.

| ?- rule1('101','NULL'(_)).

generates the SQL statement:

SELECT rel1.RoomNo, NULL
FROM Room rel1 , Floor rel2
WHERE rel1.RoomId = ? AND rel2.FId = rel1.FId AND rel2.FName IS NULL;

The view interface also supports aggregate functions predicates such as sum, avg, count, min and max. For example

| ?- odbc_import('Doctor'('DId', 'FId', 'DName','PhoneNo','ChargePerMin'),doctor).

yes
| ?- odbc_query(avgchargepermin(X),
                (X is avg(ChargePerMin, A1 ^ A2 ^ A3 ^ A4 ^ 
                          doctor(A1,A2, A3,A4,ChargePerMin)))).

yes
| ?- avgchargepermin(X).

SELECT AVG(rel1.ChargePerMin)
FROM doctor rel1;

X = 1.64

yes

A more complicated example:

| ?- odbc_query(nonsense(A,B,C,D,E),
                (doctor(A, B, C, D, E), 
                 not floor('First Floor', B), 
                 not (A = 'd001'), 
                 E > avg(ChargePerMin, A1 ^ A2 ^ A3 ^ A4 ^
                         (doctor(A1, A2, A3, A4, ChargePerMin))))).

| ?- nonsense(A,'4',C,D,E).

SELECT rel1.DId , rel1.FId , rel1.DName , rel1.PhoneNo , rel1.ChargePerMin
FROM doctor rel1
WHERE rel1.FId = ? AND NOT EXISTS
(SELECT *
FROM Floor rel2
WHERE rel2.FName = 'First Floor' and rel2.FId = rel1.FId
) AND rel1.Did <> 'd001' AND rel1.ChargePerMin >
(SELECT AVG(rel3.ChargePerMin)
FROM Doctor rel3
);


A = d004
C = Tom Wilson
D = 516-252-100
E = 2.5

All database rules defined by odbc_query can be queried with any mode.

Note that at each call to a database relation or rule, the communication takes place through bind variables. The corresponding restrictive SQL query is generated, and if this is the first call with that adornment, it is cached. A second call with same adornment would try to use the same database cursor if still available, without parsing the respective SQL statement. Otherwise, it would find an unused cursor and retrieve the results. In this way efficient access methods for relations and database rules can be maintained throughout the session.

Also the relation level interface can be used to define and access simple project views of single tables. For example:

| ?- odbc_import('Room'('RoomNo','Capacity'),roomview).

defines roomview/2.