Access to Relational Databases

Overview

PrologJ allows a Prolog program to access any relational database, provided a JDBC driver is available for it. Three kinds of Prolog predicate can be created for this purpose. A table predicate associates a Prolog predicate with a single table in the database, so that operations performed on the predicate (asserta/1, assertz/1, call/1, clause/2, retract/1 and retractall/1) are executed as operations on the database table (insert, select, or delete). A query predicate associates a Prolog predicate with a SQL query (that may involve multiple tables), so that calling the Prolog predicate leads to executing the query and (generally) binding variables appearing in the query to values retrieved from the database. An update predicate associates a Prolog predicate with a SQL update statement, so that calling the Prolog predicate leads to updating the database, possibly with some of the values being specified by parameters of the Prolog predicate.

Relational database access predicates are created by options to an extended version of open/4, which specifies a database to connect to rather than a stream to open.

Connecting to a Relational Database

Two builtin-in predicates have been extended to allow code written in Prolog to initiate and terminate a connection to a relational database using JDBC, and to define Prolog predicates to access it.

• open(URL, database, Connection, Options)
  or
  open(URL, database(Driver), Connection, Options)
   
  These variants of open/4 - distinguished from the ISO standard version by the second argument - can be used to establish a connection at runtime to a relational database and/or to create Prolog predicates that access a database. Any number of connections to any number of databases may be open at any time.
   
  • URL is either an atom that is a valid JDBC database URL, or a variable. If Connection is a variable, this argument must be instantiated, and a new connection is opened to the specified database. If Connection is instantiated, then this parameter must not be instantiated; instead, it is unified with the URL of an existing database connection, after possibly creating additional predicates that access the database.
     
  • If the second parameter is of the form database(Driver), then Driver must be an atom whose name is the fully-qualified name of a JDBC driver class that is to be loaded.
     
    Example: open(..., database('com.mysql.jdbc.Driver'), ...) would load the JDBC driver for mysql databases.
     
    If the second parameter is of the form database, then an already-loaded driver is used. Thus, normally only the first open/4 for a given DBMS specifies a driver (though it is not an error to specify a driver that is already loaded).
     
  • The Connection argument is either an uninstantiated variable or a variable that has previously been instantiated to a database connection object created by a previous call to open/4. If it is uninstantiated, then URL must be a valid URL, and a new database connection is established to the specified database. The Connection parameter is then instantiated to this connection. If Connection is instantiated, additional predicates may be defined for an existing connection.
     
  • Options must be a list of options, similar in format to the options used with the ISO standard form of open/4. Each option on the list must be one of the following:
     
    • A valid option as defined by the database driver. In this case, the term must be a compound term of arity 1, whose functor is the option name defined by the driver, and whose argument is an atom whose name is an appropriate value for the option. Although the valid options differ for different drivers, almost all drivers will support at least the following two options:
       
      • user
      • password
       
      
    • A term of the form catalog/1, where the argument is an atom whose name is the name of a valid catalog in the database specified by the connection, if the database supports distinct catalogs within a database. This option is not needed with a database that does not support distinct catalogs, or if it desired to use the default catalog for the database
       
    • A term having one of the following forms, which is used to create and bind a Prolog predicate to the database. (See the discussion of creating predicates below.)
       
      • table/2
      • query/2
      • update/2
         
  • In any case, when opening a new connection to a database, certain information must be supplied either via the URL, or via the option list, or by the user at run-time via a dialog box. The driver determines which values are required; typically they include the host for the database and the user to connect as, and the password for this user. If any required value is not specified via the URL or the option list, then a dialog box will be displayed at run-time, requiring the user to enter the appropriate values. (This is the recommended way to handle the password in any case, since it avoids embedding a password in the code.)
     
    
    If all required values are supplied via the URL and/or options, then no dialog box is displayed.
     
  • Error Cases (in addition to the standard errors for open/4, including options lists):
     

    Both URL and Connection are variables	instantiation_error
    Both URL and Connection are other than variables	type_error(variable, Connection)
    URL is neither a variable nor an atom	type_error(atom, URL)
    URL is an atom that names a database that cannot be successfully connected to	system_error(relational_database_access, URL)
    The second argument is of the form database(Driver) and Driver is a variable	instantiation_error
    The second argument is of the form database(Driver) and Driver is neither a variable nor an atom	type_error(atom, Driver)
    The second argument is of the form database(Driver) and Driver is an atom that names a class that cannot be found	system_error(class_not_found, Driver)
    Connection is neither a variable nor a database connection object	type_error(variable, Connection)
    Options is a list which has an element E that has the form of an option, but is not one of catalog/1, table/2, query/2, or update/2 or an option defined by the database driver	domain_error(database_option, E)

     
• close(Connection)
   
  
  • Connection is a variable that is instantiated to a database connection object created by a call to open/4 as described above. When a connection is closed, the Prolog predicates that were bound to the database are abolished, and no new Prolog predicates can be created using it.
     
  • There are no error cases different than the standard error cases for close/1.

Table Predicates

A table predicate represents an association between a Prolog predicate and a single table in the database. Both the predicate and the table must have the same arity; they may - but need not necessarily - have the same name. Arguments of the Prolog predicate are associated with columns in the table based on the order of the columns in the database.

Operations performed on a table predicate result in performing operations on the database table. The following example will be used in describing the various operations.
 
Suppose there is a table in the database with the following scheme:
 
person(last, first, mi, address, city, state, zip)

 
Suppose further that a table predicate is created by:
 
table(person/7, person).
 

• Calling the predicate (directly, or as an argument of a predicate like call/1) will result in performing a select operation on the table. If one or more arguments of the predicate call are instantiated, then the select operation will have a where clause, and will access only rows in the table having the specified value in the corresponding column. (An instantiated argument must be of a Prolog type that is compatible with the declared type of the column in the table.) Uninstantiated arguments of the predicate call will be unified with the value in the table row, converted to an appropriate Prolog type. The predicate will succeed once for each matching row in the table (possibly zero times if there are no matching rows). Example:
   
  Suppose the following goal is called: person(L, F, M, A, 'Boston', 'MA', Z)
   
  This will result in the execution of the following SQL statement:
  

  	select last, first, mi, address, zip
  		from person
  		where city = 'Boston' and state = 'MA';
  	

  The goal will succeed once for each matching row in the table. Each time it succeeds, L will be unified with the person's last name; F with the first name, M with the middle initial, A with the address, and Z with the zip.
   
  Error cases:
   

  One of the arguments of the call is instantiated to a value that is not compatible with the declared type of the corresponding column

  The error that is listed for the specific violation in the discussion of type conversion errors

   
• asserta(Clause), assertz(Clause)
   
  Asserting a fact into a table predicate will result in inserting a row into the table. Because rows in a database table are not ordered, there is no guarantee as to where in the table the newly-inserted row will be placed (regardless of which built-in predicate is used to assert it). An instantiated argument of Clause will result in that value being inserted into the corresponding column in the table, and therefore must be of a Prolog type that is compatible with the declared type of the column; an uninstantiated argument will result in the corresponding column being NULL. (This is not permitted if the column in question is declared NOT NULL.) Example:
   
  Suppose the following is executed: assertz(person('Zebra', 'Zelda', 'Z', _, _, _, _))
   
  This will result in the execution of a SQL statement that is equivalent to the following (though the actual statement will be a PreparedStatement that uses parameters):
  

  	insert into person(last, first, mi)
  		values('Zebra', 'Zelda', 'Z);
  	

  The row that is inserted into the table will have NULL in the address, city, state, and zip columns.
   
  Error cases (in addition to standard cases for asserta/1 and assertz/1:
   

  Conditions	Error Term
  Clause is of the form Head :- Body where Head has the functor and arity of a table predicate	type_error(fact, Clause)
  One of the arguments of Clause is a variable, and the corresponding column in the table is declared NOT NULL	instantiation_error
  All of the arguments of Clause are variables	instantiation_error
  One of the arguments of Clause is instantiated to a value that is not compatible with the declared type of the corresponding column	The error that is listed for the specific violation in the discussion of type conversion errors

  
   
• clause(Head, Body)
   
  If Head has the functor and arity of a table predicate, and Body unifies with true, then the operation is equivalent to calling the predicate as discussed above.
   
  Error cases (in addition to standard cases for clause/2:
   

  Conditions	Error Term
  One of the arguments of Head is instantiated to a value that is not compatible with the declared type of the corresponding column	The error that is listed for the specific violation in the discussion of type conversion errors
  Body does not unify with the atom true	type_error(fact, Head :- Body)

   
• retract(Clause)
   
  If Clause has the functor and arity of a table predicate, then the operation will result in deleting matching rows from the table. If one or more arguments of the predicate are instantiated, then the delete operation will have a where clause, and will only match rows in the table having the specified value in the corresponding column. (An instantiated argument must be of a Prolog type that is compatible with the declared type of the column in the table.) An uninstantiated argument of the predicate will be unified with the value in the table row that was matched and deleted, converted to an appropriate Prolog type. The predicate will succeed once for each matching row in the table (possibly zero times if there are no matching rows). Example:
   
  Suppose the following is executed: retract(person(_, _, _, _, 'Boston', 'MA', _))
   
  This will result in the execution of the following SQL statement:
  

  	delete from person
  		where city = 'Boston' and state = 'MA';
  	

  with rows being deleted one at a time.
   
  Rows for persons living in Boston, MA will be matched, and each time the predicate succeeds, one row will be deleted.
   
  Note: The SQL delete operation on a table is defined to delete all the matching rows of the table. The Prolog definition of retract/1 requires the ability to delete matching rows one at a time, and to bind variables in the goal to the actual values appearing in each row deleted. This is done using the deleteRow() method defined in the interface ResultSet as part of JDBC. However, various JDBC drivers may implement this with some restrictions, arising from the capabilities of the DBMS. For example, the driver used for mysql in the examples requires that the table have a declared primary key. Should one need to perform this operation using a driver that does not provide the necessary support (or the necessary suppport for the particular table being used), one can use retractall/1 or can define an appropriate delete ... from operation as a update predicate, though, of course, the option of deleting row by row and binding variables to the values in the row deleted will not be available.
   
  Error cases (in addition to standard cases for retract/1:
   

  Conditions	Error Term
  Clause is of the form Head :- Body where Head has the functor and arity of a table predicate	type_error(fact, Clause)
  One of the arguments of Clause is instantiated to a value that is not compatible with the declared type of the corresponding column	The error that is listed for the specific violation in the discussion of type conversion errors

   
• retractall(Clause)
   
  If Clause has the functor and arity of a table predicate, then the operation will result in deleting zero or more rows from the table. If one or more arguments of the predicate are instantiated, then the delete operation will have a where clause, and will delete only rows the in the table having the specified value in the corresponding column. (An instantiated argument must be of a Prolog type that is compatible with the declared type of the column in the table.) Example:
   
  Suppose the following is executed: retractall(person(_, _, _, _, 'Boston', 'MA', _))
   
  This will result in the execution of the following SQL statement:
  

  	delete from person
  		where city = 'Boston' and state = 'MA';
  	

  All rows for persons living in Boston, MA will be deleted from the table.
   
  Note the following:
   
  1. If it is desired to delete all the matching rows in a table, retractall/1 is considerably more efficient that deleting rows one at a time using retract/1, since retractall/1 more closely matches the basic meaning to the SQL delete operation.
      
  2. The operation will succeed even if no rows are deleted. This is consistent with the definition of retractall/1.
      
  3. Since retractall/1 is only defined in the traditional predicate category, if these extensions of retractall/1 are to be used with table predicates, the traditional predicate category must be included in the available predicates along with the relational extension.
      
   
  Error cases (in addition to standard cases for retractall/1:
   

  Conditions	Error Term
  Clause is of the form Head :- Body where Head has the functor and arity of a table predicate	type_error(fact, Clause)
  One of the arguments of Clause is instantiated to a value that is not compatible with the declared type of the corresponding column	The error that is listed for the specific violation in the discussion of type conversion errors

   

Query Predicates

A query predicate represents an association between a Prolog predicate and a SQL query (select) statement, which may (but need not) involve joining multiple tables. If the statement has m parameters (? appears m times in the query) and produces a result having n columns, then the arity of the predicate is m + n. When the predicate is called, the first m arguments correspond to the parameters of the SQL statement, in the order in which they appear, and the final n arguments correspond to the values produced by the statement, in the order in which the columns appear either in the select clause or as columns in the database (if select * or select table.* is used).

The only legal operation on a query predicate is to call it (directly, or as an argument of a predicate like call/1). Each of the initial arguments of the call must be a values that is compatible with the type of the corresponding parameter. (An uninstantiated variable is allowed and will result in the corresponding parameter being set to NULL.) The next n arguments of the call must be will be unified with one of the columns of the result, converted to an appropriate Prolog type. A call to a query predicate succeeds as many times as there are rows in the result of the query which unify with the final arguments of the call.

Example: Suppose the database contains the following tables:

variable(name, type)

b       boolean
c       char
s       short
i       int
j       int
l       long
m       long
f       float
g       float
d       double

storage(type, bytes)

boolean 1
char    2
byte    1
short   2
int     4
long    8
float   4
double  4

Suppose further that a query predicate test/2 is created for the following statement:

select name
    from variable join storage on variable.type = storage.type
    where bytes = ?

Then the following goal will succeed 4 times, with the unifications shown:

test(4, Name).

Name = i;
Name = j;
Name = f;
Name = g.

Error cases:

Conditions	Error Term
One of the initial arguments of the call (that correspond to parameters) is a variable, and the corresponding parameter does not allow its value to be NULL	instantiation_error (See note 1)
One of the initial arguments of the call (that correspond to parameters) is instantiated to a value that is not compatible with the declared type of the corresponding parameter	The error that is listed for the specific violation in the discussion of type conversion errors (See note 1)

Notes:

1. The ability to detect such errors depends on the extent to with the database driver fully supports JDBC 3. With drivers that are not yet at this level of compliance, some parameter problems will not be detected until an attempt is made to set the parameter value, at which point the error thrown will be relational_database_access(predicate indicator).

Update Predicates

An update predicate represents an association between a Prolog predicate and a SQL update statement (often insert, update, or delete, but the statement may be any legal SQL statement that does not produce a result set). The arity of the predicate corresponds to the number of parameters in the statement (the number of times ? appears in the statement), plus one. When the predicate is called, its first arguments correspond to the parameters of the SQL statement, in the order in which they appear, and its last argument is unified with the count of the number of rows affected by the operation.

The only legal operation on an update predicate is to call it (directly, or as an argument of a predicate like call/1). Each of the initial arguments of the call must be a values that is compatible with the type of the corresponding parameter. (An uninstantiated variable is allowed and will result in the corresponding parameter being set to NULL.) In the absence of an error, the final argument is unified with the count of the number of rows affected by the operation, and the call to the update predicate succeeds just when the unification succeeds.

Example: Suppose the database contains the following table:

employee(name, department, salary)

Suppose further that an update predicate raise/2 is created for the following statement:

update employee
    set salary = salary * (1 + ?)
    where department = ?

Then the following goal will give a 10% raise to all employees in the Prolog Programming department: raise(0.10, 'Prolog Programming').

Note well: the success or failure of a call using an update predicate is based on the unification of the final parameter with the count of the number of rows affected, not on whether or not any rows were affected. If it is desired to call an update predicate in such a way as to have the call succeed only if one or more rows are affected, a form like the following can be used:

	update_predicate_name(args ...  AffectedRowCount),
	AffectedRowCount > 0

Error cases:

Conditions	Error Term
One of the initial arguments of the call is a variable, and the corresponding parameter does not allow its value to be NULL	instantiation_error (See note 1)
One of the initial arguments of the call is instantiated to a value that is not compatible with the declared type of the corresponding parameter	The error that is listed for the specific violation in the discussion of type conversion errors (See note 1)

Notes:

1. The ability to detect such errors depends on the extent to with the database driver fully supports JDBC 3. With drivers that are not yet at this level of compliance, some parameter problems will not be detected until an attempt is made to set the parameter value, at which point the error thrown will be relational_database_access(predicate indicator).

Creating Relational Database Predicates

A table, query, or update predicate is created by an appropriate option to an open/4 goal that specifies the database. (This may be either the open/4 that initially creates the connection, or a subsequent goal that references an already existing connection.)

• table(PredicateIndicator, TableName)
   
  An option of this form is used to create a table predicate.
   
  • The PredicateIndicator argument must be a predicate indicator for the Prolog predicate to be created, which must not yet exist. The functor portion of the predicate indicator must be instantiated; the arity portion can be either an uninstantiated variable or an integer. In the former case, the execution of the option will unify the variable with the arity of the table in the database; in the latter case, the arity must exactly match the arity of the table.
     
  • The TableName argument must be an atom which is the name of a table in the database (possibly qualfied by a schema name - e.g. TableName may have the form 'schema.table').
     
  • Error cases:
     

    Conditions	Error Term
    PredicateIndicator is a variable	instantiation error
    PredicateIndicator is neither a variable nor a predicate indicator pattern	type_error(predicate_indicator, PredicateIndicator)
    PredicateIndicator is a predicate indicator pattern whose functor is a variable	instantiation_error
    PredicateIndicator is a predicate indicator pattern whose functor F is a neither a variable nor an atom	type_error(atom, F)
    PredicateIndicator is a predicate indicator pattern whose arity A is neither a variable nor an integer	type_error(integer, A)
    PredicateIndicator is a predicate indicator pattern whose arity A is an integer that is less than zero	domain_error(not_less_than_zero, A)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that is greater than the maximum permitted arity for a compound term	representation_error(max_arity)
    TableName is a variable	instantiation_error
    TableName is neither a variable nor an atom	type_error(atom, TableName)
    TableName specifies a table that does not exist in the database	existence_error(table, TableName)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that does not match the arity of the table in the database	system_error(arity_mismatch)

  
   
• query(PredicateIndicator, Query)
   
  An option of this form is used to create a query predicate.
   
  • The PredicateIndicator argument must be a predicate indicator for the Prolog predicate to be created, which must not yet exist. The functor portion of the predicate indicator must be instantiated; the arity portion can be either an uninstantiated variable or an integer. In the former case, the execution of the option will unify the variable with the sum of the number of parameters appearing in the query plus the number of columns selected by the query; in the latter case, the arity must exactly match this number.
     
  • The Query argument must be an atom which is a valid SQL query statement, possibly containing parameter markers (values of the form ?).
     
  • Error cases:
     

    Conditions	Error Term
    PredicateIndicator is a variable	instantiation error
    PredicateIndicator is neither a variable nor a predicate indicator pattern	type_error(predicate_indicator, PredicateIndicator)
    PredicateIndicator is a predicate indicator pattern whose functor is a variable	instantiation_error
    PredicateIndicator is a predicate indicator pattern whose functor F is a neither a variable nor an atom	type_error(atom, F)
    PredicateIndicator is a predicate indicator pattern whose arity A is neither a variable nor an integer	type_error(integer, A)
    PredicateIndicator is a predicate indicator pattern whose arity A is an integer that is less than zero	domain_error(not_less_than_zero, A)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that is greater than the maximum permitted arity for a compound term	representation_error(max_arity)
    Query is a variable	instantiation_error
    Query is neither a variable nor an atom	type_error(atom, Query)
    Query is not a valid SQL statement	system_error(relational_database_access, Query)
    Query is a valid SQL statement but not a query statement, or it is a query statement that selects zero columns	system_error(not_a_query, Query)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that does not match the sum of the number of parameters appearing in Query plus the number of columns selected by Query	system_error(arity_mismatch)

  
   
• update(PredicateIndicator, Update)
   
  An option of this form is used to create an update predicate.
   
  • The PredicateIndicator argument must be a predicate indicator for the Prolog predicate to be created, which must not yet exist. The functor portion of the predicate indicator must be instantiated; the arity portion can be either an uninstantiated variable or an integer. In the former case, the execution of the option will unify the variable with the count of the number of parameters appearing in the update, plus one (for the returned row count); in the latter case, the arity portion of PredicateIndicator must exactly match this number.
     
  • The Update argument must be an atom which is a valid SQL update statement, possibly containing parameter markers (values of the form ?).
     
  • Error cases:
     

    Conditions	Error Term
    PredicateIndicator is a variable	instantiation error
    PredicateIndicator is neither a variable nor a predicate indicator pattern	type_error(predicate_indicator, PredicateIndicator)
    PredicateIndicator is a predicate indicator pattern whose functor is a variable	instantiation_error
    PredicateIndicator is a predicate indicator pattern whose functor F is a neither a variable nor an atom	type_error(atom, F)
    PredicateIndicator is a predicate indicator pattern whose arity A is neither a variable nor an integer	type_error(integer, A)
    PredicateIndicator is a predicate indicator pattern whose arity A is an integer that is less than zero	domain_error(not_less_than_zero, A)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that is greater than the maximum permitted arity for a compound term	representation_error(max_arity)
    Update is a variable	instantiation_error
    Update is neither a variable nor an atom	type_error(atom, Update)
    Update is not a valid SQL statement	system_error(relational_database_access, Update)
    Update is a valid SQL statement but not an update statement	system_error(not_an_update, Update)
    PredicateIndicator is a predicate indicator pattern whose arity is an integer that does not match the number of parameters appearing in Update	system_error(arity_mismatch)

  
   

Extensions to Built-in Predicates

The Relational Interface extends a number of built-in predicates for use in either creating or using relational database predicates.

• asserta/1, assertz/1 can be used to add rows to a table predicate. It is in error (system_error(unsupported_operation, asserta/1) or system_error(unsupported_operation, assertz/1) as the case may be) to attempt one of these operations on a query or update predicate.
   
• clause/2 can be used to access rows in a table predicate. It is in error (system_error(unsupported_operation, clause/2) to attempt this operation on a query or update predicate.
   
• close/1 can be used to terminate a connection to a relational database.
   
• The operations listing/1 and listing/2 quietly skip over relational database predicates.
   
• open/4 can be used to open a connection to a relational database and or associated one or more Prolog predicates with operations on the database.
   
• retract/1 can be used to retract individual rows from a table predicate. It is in error (system_error(unsupported_operation, retract/1) to attempt this operation on a query or update predicate.
   
• retractall/1 can be used to retract multiple rows from a table predicate. It is in error (system_error(unsupported_operation, retractall/1) to attempt this operation on a query or update predicate.