Re: RDBMS backend
Hans Chalupsky <[email protected]> Tue, 21 Nov 2006 20:46:35 -0800
| Newsgroups | gmane.comp.ai.powerloom |
|---|---|
| Message-ID | <[email protected]> |
Kambiz,
we packaged up a pre-release of the PowerLoom RDBMS code. It is part
of the latest PowerLoom snapshot 3.2.10. There is no documentation
yet, so here is a quick example on how to use that with the Java
version of PowerLoom and JDBC.
First, you need a JDBC driver library. For example, for MySQL get
mysql-connector-java-3.1.10-bin.jar, for Oracle ojdbc14.jar should
work. You also need to edit the top-level `powerloom' script to
include the driver library in the class path.
Then start powerloom with the --load-all-extensions option which will
load the optional SDBC and RDBMS subsystems that support database
access (by default these extensions are not loaded):
% ./powerloom --load-all-extensions
Running Java version of PowerLoom...
Initializing STELLA...
Initializing PowerLoom...
Initializing SDBC...
Initializing RDBMS...
Welcome to PowerLoom 3.2.10
Copyright (C) USC Information Sciences Institute, 1997-2006.
PowerLoom is a trademark of the University of Southern California.
PowerLoom comes with ABSOLUTELY NO WARRANTY!
Type `(copyright)' for detailed copyright information.
Type `(help)' for a list of available commands.
Type `(demo)' for a list of example applications.
Type `bye', `exit', `halt', `quit', or `stop', to exit.
PL-USER |=
The first things you need to do is to define a logical database object
which will have all the connection information associated with it.
For example (using a MySQL example database and a JDBC connection string):
PL-USER |= (defdb mydb
:sql-database true
:jdbc-connection-string "jdbc:mysql://blackcat:3306/menagerie?user=scott&password=tiger")
|i|MYDB
For reference, here is what the example database table looks like in
MySQL and what it has in it:
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 rows in set (0.00 sec)
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | m | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | m | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 0000-00-00 | 1997-12-09 |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Bitsy | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy2 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy3 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy4 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy5 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy6 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy7 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
| Bitsy8 | Benny | hamster | m | 1997-02-03 | 1997-04-03 |
+----------+--------+---------+------+------------+------------+
16 rows in set (0.00 sec)
mysql>
Back to PowerLoom. The most direct way to query the database is via
the `query-database' relation which takes an sql query as an argument
and needs one PowerLoom output variable per result column. For example:
PL-USER |= (retrieve all (rdbms/query-database mydb "select count(*) from pet" ?c))
There is 1 solution:
#1: ?C=|16|
PowerLoom doesn't know what result type to expect, so by default it
coerces everything to a logic object, hence, the vertical bars around
the 16. You can tell it the type of the result by typing the
variable, for example:
PL-USER |= (retrieve all (?c integer) (rdbms/query-database mydb "select count(*) from pet" ?c))
There is 1 solution:
#1: ?C=16
Now it coerced the string coming back into an integer. Here is a
query that selects multiple rows (we limit to 5 here):
PL-USER |= (retrieve 5 (rdbms/query-database mydb "select name, owner from pet" ?n ?o))
There are 5 solutions so far:
#1: ?N=|Fluffy|, ?O=|Harold|
#2: ?N=|Claws|, ?O=|Gwen|
#3: ?N=|Buffy|, ?O=|Harold|
#4: ?N=|Fang|, ?O=|Benny|
#5: ?N=|Bowser|, ?O=|Diane|
Again, we can change the result coercion by supplying a variable type:
PL-USER |= (retrieve 5 (?n (?o string)) (rdbms/query-database mydb "select name, owner from pet" ?n ?o))
There are 5 solutions so far:
#1: ?N=|Fluffy|, ?O="Harold"
#2: ?N=|Claws|, ?O="Gwen"
#3: ?N=|Buffy|, ?O="Harold"
#4: ?N=|Fang|, ?O="Benny"
#5: ?N=|Bowser|, ?O="Diane"
The next thing you can do is to define table relations that map onto a
database table. For example:
PL-USER |= (deftable pet mydb "pet" (?name ?owner ?species))
|r|PET
Here we defined a PowerLoom relation `pet' that maps onto the "pet"
table of the database identified by `mydb'. The variable names are
interpreted as column names, so this table represents a 3-column
projection of the pet table. If you type the variables you can
control how the results get coerced (similar to the query before).
You can now use this relation just like any other PowerLoom relation,
for example:
PL-USER |= (retrieve all (pet ?n ?o ?s))
There are 16 solutions:
#1: ?N=|Fluffy|, ?O=|Harold|, ?S=|cat|
#2: ?N=|Claws|, ?O=|Gwen|, ?S=|cat|
#3: ?N=|Buffy|, ?O=|Harold|, ?S=|dog|
#4: ?N=|Fang|, ?O=|Benny|, ?S=|dog|
#5: ?N=|Bowser|, ?O=|Diane|, ?S=|dog|
#6: ?N=|Chirpy|, ?O=|Gwen|, ?S=|bird|
#7: ?N=|Whistler|, ?O=|Gwen|, ?S=|bird|
#8: ?N=|Slim|, ?O=|Benny|, ?S=|snake|
#9: ?N=|Bitsy|, ?O=|Benny|, ?S=|hamster|
#10: ?N=|Bitsy2|, ?O=|Benny|, ?S=|hamster|
#11: ?N=|Bitsy3|, ?O=|Benny|, ?S=|hamster|
#12: ?N=|Bitsy4|, ?O=|Benny|, ?S=|hamster|
#13: ?N=|Bitsy5|, ?O=|Benny|, ?S=|hamster|
#14: ?N=|Bitsy6|, ?O=|Benny|, ?S=|hamster|
#15: ?N=|Bitsy7|, ?O=|Benny|, ?S=|hamster|
#16: ?N=|Bitsy8|, ?O=|Benny|, ?S=|hamster|
Again, PowerLoom coerced every result string to a logic objects. It
did this case-sensitively, that's why they print with vertical bars in
the case-insensitive PL-USER module.
You can bind some of the arguments which will appropriately restrict
the results (these translate into WHERE constraints, so the restriction
is handled on the database side):
PL-USER |= (retrieve all (pet ?n ?o |hamster|))
There are 8 solutions:
#1: ?N=|Bitsy|, ?O=|Benny|
#2: ?N=|Bitsy2|, ?O=|Benny|
#3: ?N=|Bitsy3|, ?O=|Benny|
#4: ?N=|Bitsy4|, ?O=|Benny|
#5: ?N=|Bitsy5|, ?O=|Benny|
#6: ?N=|Bitsy6|, ?O=|Benny|
#7: ?N=|Bitsy7|, ?O=|Benny|
#8: ?N=|Bitsy8|, ?O=|Benny|
You can see the queries PowerLoom generates by turning on logging:
PL-USER |= (set-logging-parameters "sdbc" :level :high)
PL-USER |= (retrieve all (pet ?n ?o |hamster|))
[2006-NOV-21 20:22:42.000 SDBC] get-result-set: sql=SELECT DISTINCT _T.NAME, _T.OWNER, _T.SPECIES FROM pet _T WHERE _T.SPECIES='hamster'
There are 8 solutions:
#1: ?N=|Bitsy|, ?O=|Benny|
#2: ?N=|Bitsy2|, ?O=|Benny|
#3: ?N=|Bitsy3|, ?O=|Benny|
#4: ?N=|Bitsy4|, ?O=|Benny|
#5: ?N=|Bitsy5|, ?O=|Benny|
#6: ?N=|Bitsy6|, ?O=|Benny|
#7: ?N=|Bitsy7|, ?O=|Benny|
#8: ?N=|Bitsy8|, ?O=|Benny|
PL-USER |= (set-logging-parameters "sdbc" :level :none)
The next useful thing to do is to define PowerLoom relations that map
onto an arbitrary SQL query. For example:
PL-USER |= (defquery male-pet (?name ?owner ?species)
:query-pattern
(RDBMS/SQL-QUERY mydb "SELECT name, owner, species
FROM pet
WHERE sex='m'
AND name='?NAME'
AND owner='?OWNER'
AND species='?SPECIES'"))
|r|MALE-PET
The name='?NAME' clauses allow you to splice initial bindings into the
pattern where appropriate. So, these query relations are parametric
SQL patterns (be careful to match the case of the variable names,
despite their lower-case appearance the names have been upcased in the
case-insensitive PL-USER module). For example:
PL-USER |= (retrieve all (male-pet ?n ?o ?s))
There are 14 solutions:
#1: ?N=|Fluffy|, ?O=|Harold|, ?S=|cat|
#2: ?N=|Claws|, ?O=|Gwen|, ?S=|cat|
#3: ?N=|Buffy|, ?O=|Harold|, ?S=|dog|
#4: ?N=|Fang|, ?O=|Benny|, ?S=|dog|
#5: ?N=|Bowser|, ?O=|Diane|, ?S=|dog|
#6: ?N=|Slim|, ?O=|Benny|, ?S=|snake|
#7: ?N=|Bitsy|, ?O=|Benny|, ?S=|hamster|
#8: ?N=|Bitsy2|, ?O=|Benny|, ?S=|hamster|
#9: ?N=|Bitsy3|, ?O=|Benny|, ?S=|hamster|
#10: ?N=|Bitsy4|, ?O=|Benny|, ?S=|hamster|
#11: ?N=|Bitsy5|, ?O=|Benny|, ?S=|hamster|
#12: ?N=|Bitsy6|, ?O=|Benny|, ?S=|hamster|
#13: ?N=|Bitsy7|, ?O=|Benny|, ?S=|hamster|
#14: ?N=|Bitsy8|, ?O=|Benny|, ?S=|hamster|
Since we spliced in initial bindings into the pattern, we can bind
some arguments which will turn into appropriate constraints on the
database side (see the logging output); the open variables translate
into dummy constraints:
PL-USER |= (retrieve all (male-pet ?n ?o |hamster|))
[2006-NOV-21 19:53:59.000 SDBC] get-result-set: sql=SELECT name, owner, species
FROM pet
WHERE sex='m'
AND 1=1
AND 1=1
AND species='hamster'
There are 8 solutions:
#1: ?N=|Bitsy|, ?O=|Benny|
#2: ?N=|Bitsy2|, ?O=|Benny|
#3: ?N=|Bitsy3|, ?O=|Benny|
#4: ?N=|Bitsy4|, ?O=|Benny|
#5: ?N=|Bitsy5|, ?O=|Benny|
#6: ?N=|Bitsy6|, ?O=|Benny|
#7: ?N=|Bitsy7|, ?O=|Benny|
#8: ?N=|Bitsy8|, ?O=|Benny|
You can of course use these database tables and query relations
anywhere you can use a PowerLoom relation. For example, you could
define a concept like this:
PL-USER |= (defconcept dog (?d)
:<= (exists (?o) (pet ?d ?o |dog|)))
|c|DOG
Note that the `dog' concept and the lower-case |dog| coming from the
database are different objects due to the different casing. Now we
can query it or use it in a rule somewhere:
PL-USER |= (retrieve all (dog ?d))
There are 3 solutions:
#1: ?D=|Buffy|
#2: ?D=|Fang|
#3: ?D=|Bowser|
PL-USER |=
One thing to be careful about are NULL values which - when retrieved -
result into NULL variable bindings which result in query and inference
failures. We don't yet have a good way yet to deal with those. One
way around this is to map a wide database table onto many small 2-3
argument table relations that map a key to a few non-NULL column
values. That way you can still, for example, retrieve a person's
first and last name even if their middle-name column is NULL.
Hope this gets you going. The machinery is available in Lisp as well
(look at `load-powerloom.lisp' and set the variable
*load-all-extensions?* to t to load the extension systems). This
should work fairly easily out of the box with Allegro's ODBC
interface, but then ODBC is always a bit of a mystery to set up.
It also works in C++ but we don't yet have dynamic loading of
extensions for that, i.e., you'd have to make a couple of edits to
make files and the main function to get proper linking and
initialization.
Hope this gets you going,
Hans
--------------------------------------------------------------------------
Hans Chalupsky, PhD USC Information Sciences Institute
Project Leader, Loom KR&R Group 4676 Admiralty Way
<[email protected]> Marina del Rey, CA 90292
(310) 448-8745
--------------------------------------------------------------------------
>>>>> Kambiz Darabi <[email protected]> writes:
> Thank you, Hans.
> This sounds very exciting as I want to enrich an existing, database-centric
> application with PL. I roughly thought each database table (or rather
> each class in the class hierarchy of the application) could correspond to a
> concept in PL with rows being instances of the concept.
> I thought I would have to write the part which interfaces to the database
> myself (probably in Java) and I'm very happy to hear that it exists
> already.
> Is there a time frame for the official release? If not, would it be possible
> to try it somehow before the official release?
> Kambiz
> _______________________________________________
> powerloom-forum mailing list
> [email protected]
> http://mailman.isi.edu/mailman/listinfo/powerloom-forum