In the following we describe the direct database evaluation functionality of the DLVDB system. We will also give a necessary introduction of the database mapping directives and show how to prepare and configure dlv to be interfaced with a database system (e.g PostgreSQL or Oracle) in order to evaluate logic programs directly on the db. The present version of this functionality supports normal stratified logic programs plus aggregate functions, with input data (the facts) stored in possibly distributed databases.
The Auxiliary Directives are a set of dlv system directives. They must be written in a separate file that must have a “typ” extension. The Auxiliary Directives are composed by three sections. In the first section (Init-Section), we define the database connection to the working database. The working database is the DBMS on which the logic program must be evaluated. In the second section (Table-Definition), we define the “database table”/”logic atom” mappings. This mapping is needed in order to define the correspondences between atoms of the logic programs and tables of the distributed databases. In the last section (Final-Section), we define the output directive for storing some (or all) of the results of the program execution.
BNF Syntax of Auxiliary Directives
USEDB databaseName:username:password [System-Like]?.
LIKE [POSTGRES | ORACLE | DB2 | SQLSERVER | MYSQL]
Table-Definition (EDB definition)::=
USE tableName [( attribute [, attribute]*)]?
MAPTO predName [( type [, type] )]? [ALLOW_APPEND(1)]?.
Table-Definition (IDB definition)::=
CREATE tableName [(attribute [, attribute]*)]?
MAPTO predName [( type [, type] )]?
Table-Definition (Query definition)::=
OUTPUT [Write-Option]? predName
Write-Option :: =
(1) ALLOW_APPEND clause is allowed only if the table is on the working database.
In the following, we describe how to compose Auxiliary Directives. The
USEDB command (line 1.) is needed to define the connection properties with the working database. A possible use of this directive, when the database name is MyDatabase (e.g. on Postgres DBMS) and the access is granted to the user scott with password tiger, is:
USEDB MyDatabase:scott:tiger LIKE POSTGRES.
USEDB is the only non optional directive.
LIKE directive is optional, but it is highly recommended if the working database is among the listed one. Indeed, this directive allows our system both to use SQL dialect of the specified system (guaranteeing full compatibility with DLVDB) and to optimize the interaction of the system with the working database (to possibly enhance performances). If the LIKE directive is not used, standard SQL and ODBC functionalities are exploited (in particular, full compatibility with POSTGRES is guaranteed).
Note that, currently, the list of DBMS fully compatible as working database for DLVDB is:
- POSTGRESQL 8.0 and higher
- MySQL 5.0
- ORACLE 10g
- SQL Server 2005
- DB2 UDB Version 8.2
For instance, suppose we have a table such as
my_table(lname varchar(20), fname char(20)) on
remote_database and our logic program defines the atom
my_atom(X, Y). If we need to specify a mapping between my_table and my_atom, we have to define the following Auxiliary Directive:
USE my_table (lname, fname) FROM remote_database:dlv:db MAPTO my_atom (varchar(20), char(20)).
On the other hand, when a
[AS ("SQL-Statement")] (3.) clause is specified, the
tablename is expected to be updated by means of the tuples resulting from execution of the
"SQL-Statement". That is, the arity of the table
tablename and the predicate
predName must be compliant with both arity and types of the result set obtained.
The IDB definition is similar to the EDB definition. With the
CREATE directive, the system creates an IDB table tableName and defines the mapping with the logic atom predName. This table will be created into the working database. The directive at line 8., if present, defines that the table is not deleted at the end of the evaluation. For example, if we need to map a logic IDB my_idb_predicate with a database table, we must define the following Auxiliary Directive:
CREATE my_idb_table (preferred_attribute_name_1, preferred_attribute_name_1) MAPTO my_idb_predicate (varchar(20), char(20)).
Noted that the just created table will be deleted after the execution end (this could be useful for intermediate or support tables). However, if we need this table to be persistent (e.g., because it store useful information we want to further analyze), than the correct definition is:
(preferred_attribute_name_1, preferred_attribute_name_1) MAPTO
my_idb_predicate (varchar(20), char(20)) KEEP_AFTER_EXECUTION.
Use directive is intended to map a predicate whose extension cannot change during processing. That is, the associated table is read-only. On the other hand, a
Create directive is conceived to create a new table on the working database in order to store new data inferred for the related predicate. In case we need both accessing tuples within a table and adding new tuples derived during the computation, we have to explicitly allow for “append” operations. In particular, we have to specify an
USE ... ALLOW_APPEND directive. For instance :
USE my_table (lname, fname) FROM working_database:dlv:db MAPTO my_atom (varchar(20), char(20)) ALLOW_APPEND.
QUERY option, allows to define the name of the query table (i.e the table storing the results of the query possibly present in the logic program). In the Final-Section we introduce the
OUTPUT directive. This directive is necessary in order to redirect the output of the program computation. With line 10., the working database in which the program stores the computation, is specified into the database DatabaseName. If we need to store only a specified predName (noted that predName is mapped to a table) then, we must use the directive 11. Specifically, if we need to store the content of the predicate my_predicate after the execution to the database remote_database (grant access to user pippo with password pluto), and I need to rename the table to my_new_table and I need to APPEND the new data to the data possibly already present in that table, I must use the following Mapping Directive:
OUTPUT APPEND my_predicate AS my_new_table IN remote_database:pippo:pluto.
The command line needed to evaluate the logic program in mass-memory (on a DBMS) is:
dl -DBSupport -ORdr- program.dlv directives.typ
where, dl is an executable version of DLVDB, program.dlv is a text file containing the logic program and directives.typ is a text file containing the auxialry directives.
Note that, if the program query has some constants, you should prefer to activate the automatic magic-sets optimization, as:
dl -DBSupport -ORdr- -ODMS program.dlv directives.typ