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

SQL persistent database interface

Author(s): I. Caballero, D. Cabeza, J.M. Gómez, M. Hermenegildo, J. F. Morales, and M. Carro, clip@dia.fi.upm.es, http://www.clip.dia.fi.upm.es/, The CLIP Group, Facultad de Informática, Universidad Politécnica de Madrid.

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

Version of last change: 1.11#61 (2003/11/27, 21:23:22 CET)

The purpose of this library is to implement an instance of the generic concept of persistent predicates, where external relational databases are used for storage (see the documentation of the persdb library and [CHGT98,Par97] for details). To this end, this library exports SQL persistent versions of the assertz_fact/1, retract_fact/1 and retractall_fact/1 builtin predicates. Persistent predicates also allow concurrent updates from several programs, since each update is atomic.

The notion of persistence provides a very natural and transparent way to access database relations from a Prolog program. Stub definitions are provided for such predicates which access the database when the predicate is called (using the db_client library). A Prolog to SQL translator is used to generate the required SQL code dynamically (see library pl2sql).

This library also provides facilities for reflecting more complex views of the database relations as Prolog predicates. Such views can be constructed as conjunctions, disjunctions, projections, etc. of database relations. Also, SQL-like aggregation operations are supported.

Implementation of the Database Interface

The architecture of the low-level implementation of the database interface was defined with two goals in mind:

In order to allow the flexibility mentioned above, a client-sever architecture was chosen. At the server side, a MySQL server connects to the databases using the MySQL. At the client side, a MySQL client interface connects to this server. The server daemon (mysqld) should be running in the server machine; check your MySQL documentation on how to do that.

After the connection is established a client can send commands to the mediator server which will pass them to the corresponding database server, and then the data will traverse in the opposite direction. These messages include logging on and off from the database, sending SQL queries, and receiving the responses.

The low level implementation of the current library is accomplished by providing abstraction levels over the MySQL interface library. These layers of abstraction implement the persistent predicate view, build the appropriate commands for the database using a translator of Prolog goals to SQL commands, issue such commands using the mediator send/receive procedures, parse the responses, and present such responses to the Prolog engine via backtracking.

Example(s)

:- module(_, _, [persdb_mysql, functions]).

% Some contributions from Guy-Noel Mathieu

:- use_module(library(write)).
:- use_module(library(format)).

:- use_module(user_and_password).

sql_persistent_location(people, db(people, User, Password, HP)):-
        mysql_host_and_port(HP),
        mysql_user(User),
        mysql_password(Password).

:- sql_persistent(
        people(string, string, int),   %% Prolog predicate and types
        people(name, sex, age),        %% Table name and attributes
        people).                       %% Database local id

% Low level MySQL interface.
:- use_module(library('persdb_mysql/mysql_client')).

main :-
        nl,
        display('Creating database'), nl,nl,
        create_people_db,
        nl,
        display('Inserting people'), nl,nl,
        insert_people,
        nl,
        display('Showing people'), nl,nl,
        show_people,
        display('Removing John'), nl,nl,
        remove_people(john,_Y,_Z),
        display('Showing people, after removing John'), nl,nl,
        show_people,
        remove_people(_X,female,_Z),
        display('Showing people, after removing female'), nl,nl,
        show_people.

% Create a database and a table of people.  Still needs to be ironed out.

create_people_db :-
        mysql_user(User),
        mysql_password(Password),
        mysql_host_and_port(HP),
        mysql_connect(HP, '', User, Password, DbConnection),
        
        write(~mysql_query(DbConnection,
                "drop database if exists people")), nl,
        write(~mysql_query(DbConnection, "create database people")), nl,
        write(~mysql_query(DbConnection, "use people")), nl,
        write(~mysql_query(DbConnection,
                "create table people(name char(16) not null, 
sex text, age int, primary key(name))")), nl,
        mysql_disconnect(DbConnection).

% Inserts people into the 'people' table.

male(john, 15).
male(peter, 24).
male(ralph, 24).
male(bart, 50).
female(kirsten, 24).
female(mary, 17).
female(jona, 12).
female(maija, 34).

%% Tuples are inserted as in the local Prolog dynamic database
insert_people :-
        (
            male(N, A),
            display('Inserting '), 
            display(male(N, A)), 
            nl,
            dbassertz_fact(people(N, male, A)), 
            fail 
        ; 
            true
        ),
        (
            female(N, A), 
            display('Inserting '), 
            display(female(N, A)), 
            nl,
            dbassertz_fact(people(N, female, A)),
            fail 
        ; 
            true
        ).

 %% Removes people from the 'people' table.

 %% Still not working in MySQL due to differences in SQL: working on it.

remove_people(A, B, C) :-
        dbretractall_fact(people(A, B, C)).
 
remove_people_2(A, B, C) :-
        dbretract_fact(people(A, B, C)),
        display('Removed row '), display(people(A, B, C)), nl,
        fail.
remove_people_2(_, _, _) :-
         display('No more rows'), nl.

show_people :-
        people(Name, Sex, Age),
        display(people(Name, Sex, Age)),
        nl,
        fail.
show_people :-
        display('No more rows'), nl.

Usage and interface (persdbrt_mysql)

Documentation on exports (persdbrt_mysql)

PREDICATE: init_sql_persdb/0:

Usage:

PREDICATE: dbassertz_fact/1:

Usage: dbassertz_fact(Fact)

PREDICATE: dbretract_fact/1:

Usage: dbretract_fact(Fact)

PREDICATE: dbcurrent_fact/1:

Usage: dbcurrent_fact(Fact)

PREDICATE: dbretractall_fact/1:

Usage: dbretractall_fact(Fact)

PREDICATE: make_sql_persistent/3:

Meta-predicate with arguments: make_sql_persistent(addmodule,?,?).

Usage: make_sql_persistent(PrologPredTypes, TableAttributes, Keyword)

PREDICATE: dbfindall/4:

Meta-predicate with arguments: dbfindall(?,?,goal,?).

Usage: dbfindall(DBId, Pattern, ComplexGoal, Results)

PREDICATE: dbcall/2:

Usage: dbcall(DBId, ComplexGoal)

PREDICATE: sql_query/3:

Usage: sql_query(DBId, SQLString, AnswerTableTerm)

PREDICATE: sql_get_tables/2:

Usage 1: sql_get_tables(Location, Tables)

Usage 2: sql_get_tables(DbConnection, Tables)

PREDICATE: sql_table_types/3:

Usage 1: sql_table_types(Location, Table, AttrTypes)

Usage 2: sql_table_types(DbConnection, Table, AttrTypes)

REGTYPE: socketname/1:

Usage: socketname(IPP)

REGTYPE: dbname/1:

Usage: dbname(DBId)

REGTYPE: user/1:

Usage: user(User)

REGTYPE: passwd/1:

Usage: passwd(Passwd)

REGTYPE: projterm/1:

Usage: projterm(DBProjTerm)

REGTYPE: querybody/1:

Usage: querybody(DBGoal)

(UNDOC_REEXPORT): sqltype/1:

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

Documentation on multifiles (persdbrt_mysql)

PREDICATE: sql_persistent_location/2:

Relates names of locations (the Keywords) with descriptions of such locations (Locations).

The predicate is multifile.

The predicate is of type data.

Usage: sql_persistent_location(Keyword, DBLocation)

Documentation on internals (persdbrt_mysql)

REGTYPE: tuple/1: 
tuple(T) :-
        list(T,atm).
tuple(T) :-
        list(T,atm).

Usage: tuple(T)

REGTYPE: dbconnection/1:

Usage: dbconnection(H)

DECLARATION: sql_persistent/3:

Usage: :- sql_persistent(PrologPredTypes, TableAttributes, Keyword).

PREDICATE: db_query/4:

Usage: db_query(DBId, ProjTerm, Goal, ResultTerm)

PREDICATE: db_query_one_tuple/4:

Usage: db_query_one_tuple(DBId, ProjTerm, Goal, ResultTerm)

PREDICATE: sql_query_one_tuple/3:

Usage: sql_query_one_tuple(DBId, SQLString, ResultTuple)

Known bugs and planned improvements (persdbrt_mysql)

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