$Id: //info.ravenbrook.com/user/ndl/lisp/ilc2002/index.html#26 $
This document was written on behalf of Xanalys LLC for presentation during a tutorial session at the International Lisp Conference held in San Fransisco at the end of October 2002.
The intended audience for the tutorial is anybody with a working
knowledge of lisp and at least some knowledge of SQL, who is
interested in seeing how the two can be combined. On the other hand
most of this material should be accessible to people with little or no
SQL experience. (The exception is the material on select
clauses in section 4.1, which will probably
be somewhat daunting for anyone new to SQL.)
The author worked on the LispWorks project at Harlequin for ten years. Since then he has taught lisp to undergraduates, written an open-source search engine, and taken up the stress-free existence of a software consultant.
The examples in this tutorial are available in a separate file examples.lisp. I hope to use the code in present.lisp to squirt them into a lisp listener during the tutorial.
This document is not confidential. It is available on the web, at http://www.ravenbrook.com/doc/2002/09/13/common-sql/.
Common SQL is the name of LispWorks' interface to relational databases. The interface dates back to the very early 1990's, when it was written to support Watson, Harlequin's emerging "intelligent database application". The interface ships as standard with LispWorks on the "commercial unix" platforms, and in the "Enterprise" editions for Windows and Linux.
Common SQL supports database connections via ODBC and - on unix - directly to Oracle.
Common SQL is documented in both the LispWorks User Guide and the Reference Manual. I will talk later about Uncommon SQL: a free, vendor-independent and platform-portable clone of Common SQL. This too is documented; in particular it comes with its very own (shorter!) online tutorial.
To illustrate this tutorial I will use Xanalys' LispWorks for Windows to communicate with a Microsoft(R) Access database via ODBC.
All references from this document to the LispWorks manual set are pointers into the manual pages for LispWorks for Windows 4.2, available online at the Xanalys website (http://www.lispworks.com/).
The database which I have chosen for examples in this tutorial is the Amazonia Agrobiodiversity Database, freely available from:
http://www.unu.edu/env/plec/database/4-AmazoniaAgrobiodiversity.mdb
UNU is the United Nations University; PLEC stands for "People, Land management & Environmental Change". This is a Microsoft Access file; the database is around 1.5MB in size.
There are five main tables in the database. I've extracted the top ten rows of each table into html, as a separate document.
Let's look at the steps that you need to take, starting from scratch, to connect your lisp to the database.
Use the ODBC "Control Panel" applet to create a data-source corresponding to the file "4-AmazoniaAgrobiodiversity.mdb". I called my data-source Agrobiodiversity.
Start LispWorks. Make sure that one of your startup files (e.g. "~/.lispworks" or "siteinit.lisp") calls:
(load-all-patches) otherwise you may find yourself wasting a whole load of time with bugs which were fixed months ago. If you're not at version 4.2.7 or above, upgrade now.
Load Common SQL into the lisp image:
(require "odbc") You can now connect to the data-source:
(sql:connect data-source) The required argument to connect is your data-source
name - whatever you chose in #1 above. For example:
CL-USER 22 > (sql:connect "Agrobiodiversity") #<SQL::ACCESS-ODBC-DATABASE "Agrobiodiversity" 205EE944> CL-USER 23 >
If you really need to maintain more than one connection to a single
data-source, read in the documentation
about the :if-exists keyword argument.
Note: if the data-source requires a name or password then the form of the first argument is:
"data-source/username/password"This database will now be used, by default, in all sql operations.
There's not a lot else you need to know about
sql:connect. The opened database is represented by a lisp
object which is (a) returned and (b) stored into the variable
sql:*default-database*. Almost every function in Common
SQL takes a :database argument. The default value of this
argument is given by the current value of
sql:*default-database*. So:
if you only ever connect to one database at a time, you can just make the connection and forget about it;
if you connect to more than one database, you'll have to keep
track of them yourself and either bind
sql:*default-database* or pass :database
arguments around.
As an example, consider the function sql:disconnect,
which closes an existing connection to a data-source. To disconnect
from the current sql:*default-database*, call
sql:disconnect with no arguments. To disconnect from some
other data-source, call:
(sql:disconnect :database database) From now on, you can assume that every function / macro in the
interface takes a :database keyword, unless I state
otherwise.
Other functions worth noting in this area are
sql:connected-databases and
sql:find-database.
For reasons of brevity, I am going to drop the sql:
prefix. In the lisp sessions I used for testing the examples for this
tutorial, I simply used the SQL package:
CL-USER 2 > (use-package :SQL) T CL-USER 3 >
To make what I'm doing unambiguous, the package of any global lisp symbol referenced in this document can be determined by selecting the first of the following which is applicable to that symbol:
COMMON-LISP, LISPWORKS,
HARLEQUIN-COMMON-LISP;SQL package.The simplest way to interact with the database is to send it raw SQL strings. This approach may not be the most elegant or powerful but it's enough to get you started.
The function to send SQL queries is query. This
function takes an SQL string (and the :database keyword
argument mentioned above) and returns a list of rows matching the
query. Each row might consist of one or more columns, and so the rows
themselves come back as lists: the primary return value of
query is therefore a list of lists.
CL-USER 70 > (query "select SampleAreaLocation, LandUseStage
from SampleAreas where SampleAreaNumber = 1")
(("Mazagao" "Forest"))
("SampleAreaLocation" "LandUseStage")
CL-USER 71 > (query "select count(*) from SpeciesList
where ScientificName like '%sp.'")
((16))
("Expr1000")
CL-USER 72 > (query "select LocalName, ScientificName from SpeciesList
where SpeciesID <= 5")
(("Abacate" "Persea gratissima Gaertn.")
("Abiu" "Ferdinandusa paraensis")
("abiu do sertão" "Pouruma sp.")
("Abiurana" "Pouteria bilocularis (Winkler) Baehni")
("Açaí" "Euterpe Oleraceae Mahrt."))
("LocalName" "ScientificName")
CL-USER 73 >
Note:
that a list of column names is returned as a second value;
that we do not have to terminate the SQL with its standard semicolon - the lisp interface adds one for us;
that apart from the added semicolon lisp makes no changes to our SQL string but sends it literally;
that we do not have to worry in advance about the types which
query returns (within its list of lists): the values
extracted from the database are correctly coerced into lisp objects of
the appropriate type;
that dates are returned as universal times:
CL-USER 180 > (multiple-value-list
(decode-universal-time
(caar (query "select max(Date) from TreeData"))))
(0 0 1 1 8 2000 1 T 0)
CL-USER 181 >
To generate simple reports, use the function
print-query. This takes the same arguments as
query plus additional keywords for specifying an output
:stream, and the :titles,
:formats and column :sizes to use:
CL-USER 81 > (print-query "select LocalName, ScientificName from SpeciesList
where SpeciesID <= 5"
:titles '("LocalName" "ScientificName"))
LocalName ScientificName
Abacate Persea gratissima Gaertn.
Abiu Ferdinandusa paraensis
abiu do sertão Pouruma sp.
Abiurana Pouteria bilocularis (Winkler) Baehni
Açaí Euterpe Oleraceae Mahrt.
CL-USER 82 >
Updating the database is almost as easy as querying it. The only complication is that almost all databases are equipped for transaction handling these days and so we have to respect this, otherwise our updates will never show up in the database.
To send any SQL statement other than a query, use the function
execute-command:
CL-USER 89 > (with-transaction
(execute-command "insert into SpeciesList (LocalName)
values ('Aardvark')")
(execute-command "create table foo (bar integer)"))
NIL
CL-USER 90 >
Note the use of the with-transaction
macro. This ensures that a transaction is committed if its body
finishes successfully, otherwise the database is rolled back. (By
"successful" here, we mean that the body exited without returning,
aborting or throwing.)
Alternatively you can use the functions commit and
rollback to exercise control, in a more procedural style,
over whether and when transactions are written into the database.
CL-USER 137 > (execute-command "delete from SpeciesList
where (LocalName = 'Aardvark')")
CL-USER 138 > (query "select * from SpeciesList
where (LocalName = 'Aardvark')")
NIL
("LocalName" "ScientificName" "SpeciesID")
CL-USER 139 > (rollback)
NIL
CL-USER 140 > (query "select * from SpeciesList
where (LocalName = 'Aardvark')")
(("Aardvark" NIL 228))
("LocalName" "ScientificName" "SpeciesID")
CL-USER 141 >
Common SQL supplies four functions for making simple queries about the database schema.
(list-tables) returns a list of strings naming
every table and view in the database.
(table-exists-p table) is a
predicate for determining whether or not a named table / view
exists.
(list-attributes table) returns a
list of strings naming every column (attribute) in a
given table / view.
(attribute-type attribute table)
returns the type of a given attribute.
So, for example:
CL-USER 164 > (loop for attr in (list-attributes "TreeData")
collect (attribute-type attr "TreeData"))
(:INTEGER :INTEGER :INTEGER :REAL :REAL
(:VARCHAR 50) :DATETIME (:VARCHAR 50))
CL-USER 165 > (print-query "select * from SpeciesList
where (SpeciesID between 6 and 9)"
:titles (list-attributes "SpeciesList"))
LocalName ScientificName SpeciesID
Acapurana Campsiandra laurifolia Benth. 6
Acerola Malpighia glabra 7
Ajuru NIL 8
Ameixa Eugenia cuminii 9
CL-USER 166 >
Note incidentally how the null ScientificName for
"Ajuru" is returned as a nil.
When you're debugging an application it's sometimes handy to
monitor the SQL you've generated and the results which have been
returned from the database. You can use the function
start-sql-recording for this. This takes a
:type argument, which can have one of the following
values, depending on what you want to record:
:commands (default) - the text of SELECT, INSERT, UPDATE
and DELETE commands:results - results returned from SELECT commands:both - both commands and resultsTraffic appears on *standard-output*. To redirect
this output, see add-sql-stream
and friends. To halt the flow, call stop-sql-recording
with the same :type argument.
We now move on to a more elegant and lisp-like way of interacting with the database. Let's start with an example of the syntax which drives this:
CL-USER 194 > (enable-sql-reader-syntax)
CL-USER 195 > (select [Researcher] :from [SampleAreas])
(("Fernando") ("Fernando") ("Marcio") ("Fernando") ("Marcio") ...)
("RESEARCHER")
CL-USER 196 >
Undoubtedly, select is harder to learn to use than
query. On the other hand:
once you know your way about SQL this is a comparatively small step to take - the real unpleasantness lies in the SQL;
the syntax is backend independent;
the syntax allows a natural and powerful intermixing of lisp forms and SQL.
The first thing you have to do with the [...] syntax is switch it on, as #\[ does not become a character macro until it has been explicitly enabled.
To enable the syntax, call
(enable-sql-reader-syntax).
When you come to writing applications which use Common SQL, you
should read the documentation on locally-enable-sql-reader-syntax
and its relatives. These are a little more subtle than
(enable-sql-reader-syntax).
None of the reader-syntax control functions take a
:database argument. Their effects are global across the
lisp image and persist until undone.
The next thing to know is that all the forms you can construct with [...] can also be generated programmatically, if you really want to insist on it. For example:
CL-USER 27 > (apply (sql-operator 'and)
(loop for table in '(thistime nexttime sometime never)
for count from 42
collect
[between (sql-expression :table table
:attribute 'bar)
(sql-operation '* [hip] [hop])
count]
collect
[like (sql-expression :table table
:attribute 'baz)
(sql table)]))
#<SQL-RELATIONAL-EXP "((THISTIME.BAR BETWEEN (HIP * HOP) AND 42) AND
(THISTIME.BAZ LIKE 'THISTIME') AND (NEXTTIME.BAR BETWEEN (HIP * HOP)
AND 43) AND (NEXTTIME.BAZ LIKE 'NEXTTIME') AND (SOMETIME.BAR BETWEEN
(HIP * HOP) AND 44) AND (SOMETIME.BAZ LIKE 'SOMETIME') AND (NEVER.BAR
BETWEEN (HIP * HOP) AND 45) AND (NEVER.BAZ LIKE 'NEVER'))">
CL-USER 28 >
The syntax is deliberately overloaded. The interpretation of a [...] form depends on the first element of the form. If this element is a "reserved operator" (corresponding to one of SQL's reserved operators) then that operator is invoked - at run-time - using any remaining elements as its parameters. Otherwise, the form is taken to represent a database identifier.
We'll meet the reserved operators in the following sections.
The argument list of select is somewhat unusual. You
pass one or more columns, followed by at least one keyword argument
(:from) and maybe more. For example:
CL-USER 196 > (select [FieldType] [Researcher] :from [SampleAreas])
(("Levee" "Fernando") ("Levee" "Fernando") ("Levee" "Marcio") ...)
("FIELDTYPE" "RESEARCHER")
CL-USER 197 >
Further examples of the keyword arguments follow. It's natural to introduce them alongside the operators they work with.
+ - * /In the following example, none of the forms
[PlotNumer], [SampleAreaNumber] and
[PlotDescription] start with reserved operators and so
all three must represent identifiers within the database: naming
attributes, tables, and so on.
CL-USER 81 > (select [+ [PlotNumer] [* 1000 [SampleAreaNumber]]]
:from [PlotDescription] :flatp t)
(1001 1002 1003 1004 1005 ...)
("Expr1000")
CL-USER 82 >
Note here the use of the :flatp
argument to select. This can be used when you generate
output consisting of a single column. Its effect is to strip off the
now superfluous inner lists, so that the primary return value becomes
a straightforward list of values instead of a list of singleton lists
of values.
Note also that the [*] operator is
itself overloaded. As in SQL, it can represent either multiplication
or the "all columns" identifier:
CL-USER 95 > (select [*] :from [SpeciesList])
(("Abacate" "Persea gratissima Gaertn." 1)
("Abiu" "Ferdinandusa paraensis" 2)
("abiu do sertão" "Pouruma sp." 3)
("Abiurana" "Pouteria bilocularis (Winkler) Baehni" 4)
("Açaí" "Euterpe Oleraceae Mahrt." 5)
...)
("LocalName" "ScientificName" "SpeciesID")
CL-USER 96 >
avg count max min sum
CL-USER 82 > (select [max [+ [PlotNumer] [* 1000 [SampleAreaNumber]]]]
:from [PlotDescription] :flatp t)
(33427)
("Expr1000")
CL-USER 83 > (select [avg [+ [PlotNumer] [* 1000 [SampleAreaNumber]]]]
:from [PlotDescription] :flatp t)
(18364.049295774646)
("Expr1000")
CL-USER 84 > (loop for table in (list-tables) repeat 5 ; exclude views
collect (select table [count [*]] :from table))
((("PlotDescription" 426))
(("SampleAreas" 38))
(("SpeciesData" 3440))
(("SpeciesList" 205))
(("TreeData" 9448)))
CL-USER 85 > (loop for column in '([*] [ScientificName]) collect
(select [count column] :from [SpeciesList] :flatp t))
((205) (152))
CL-USER 86 >
Note in the last of this set of examples how pure
lisp and SQL components have been mixed: the variable
column is bound to an SQL identifier and its value is
then substituted as the argument to [count].
Note also, in the previous example, the two uses I
made of string values: as an attribute argument the string is simply
returned (with every row), exactly as in SQL itself; as the
:from argument it substitutes for one of lisp's SQL
identifiers without any problems.
< <= = > >= betweenThe comparison operators appear in conjunction with arguments
:where and (for aggregate values) :having to
the function select. This is a good place to introduce
:distinct and :group-by.
CL-USER 138 > (select [LandUseStage] :from [Sampleareas] :flatp t)
("Forest" "Forest" "Forest" "Forest" "Housegarden" ...)
("LANDUSESTAGE")
CL-USER 139 > (select [LandUseStage] :from [Sampleareas] :flatp t
:where [>= [SampleAreaNumber] 37])
("Field" "Field" "Field")
("LANDUSESTAGE")
CL-USER 140 > (select [LandUseStage] :from [Sampleareas] :flatp t
:distinct t)
("Fallow" "Field" "Forest" "Housegarden")
("LANDUSESTAGE")
CL-USER 141 > (select [LandUseStage] [count [*]] :from [Sampleareas]
:group-by [LandUseStage])
(("Fallow" 12) ("Field" 5) ("Forest" 9) ("Housegarden" 12))
("LANDUSESTAGE" "Expr1001")
CL-USER 142 > (select [LandUseStage] [count [*]] :from [Sampleareas]
:having [between [count [*]] 8 10]
:group-by [LandUseStage])
(("Forest" 9))
("LANDUSESTAGE" "Expr1001")
CL-USER 143 > (select [max [Height]] :from [TreeData] :flatp t
:where [= [Researcher] "Fernando"])
(30.0)
("Expr1000")
CL-USER 144 >
This last query answers the question: how tall was the tallest tree that Fernando found?
likeWhat are the scientific names of species whose common names begin with a 'v'?
CL-USER 154 > (select [ScientificName] :from [SpeciesList] :flatp t
:where [like [LocalName] "v%"])
("Hernandia guianensis Aubl." "Virola surinamensis Warb." NIL NIL)
("SCIENTIFICNAME")
CL-USER 155 >
nullWhich species don't have a scientific name in the database?
CL-USER 163 > (select [LocalName] :from [SpeciesList] :flatp t
:where [null [ScientificName]])
("Ajuru" "acacurana" "Axua" "Biribarana" "cipo" ...)
("LOCALNAME")
CL-USER 164 >
distinctWho researched the sample areas?
CL-USER 80 > (select [distinct [Researcher]] :from [TreeData] :flatp t)
("Fernando" "Marcio" "Viles" "Vilis")
("RESEARCHER")
CL-USER 81 >
Actually, the simple example above could have been coded:
(select [Researcher] :distinct t :from [TreeData] :flatp t)
for the same effect.
and or notLet's introduce a simple join, answering the question: who researched species whose common names begin with a 'v'?
CL-USER 165 > (select [Researcher] :from '([TreeData] [SpeciesList])
:where [and [= [TreeData SpeciesID]
[SpeciesList SpeciesID]]
[like [LocalName] "v%"]]
:distinct t :flatp t)
("Fernando")
("RESEARCHER")
CL-USER 166 >
In this query, identifiers [Researcher] and
[LocalName] belong unambiguously to one table each, but
[SpeciesID] would be ambiguous and so must be
qualified. We do this by prepending the table name, as in
[TreeData SpeciesID]. Note that the
:from value is now a lisp list. (In fact, when there's
only one table in a query you are still free to wrap it into a
list.)
In the next query, in which we locate species with non-unique scientific names, the "Species" tables is joined to itself:
CL-USER 185 > (select ["table" LocalName] ["table" ScientificName]
:from '([SpeciesList "table"] [SpeciesList "join"])
:where [and [= ["table" ScientificName]
["join" ScientificName]]
[not [= ["table" SpeciesID]
["join" SpeciesID]]]]
:order-by '(["table" ScientificName]))
(("Limao bravo" "Citrus sp.")
("Limao caiena" "Citrus sp.")
("capitiu do mato" "Siparuna sp.")
("capitiu brabo" "Siparuna sp.")
("Unknown2" "Unknown")
...)
("LOCALNAME" "SCIENTIFICNAME")
CL-USER 186 >
The two tables called "Species" are distinguished by aliases
"table" and "join" which are established in
the :from clause. Note that - in
contrast to singleton values for :from - we are always
obliged to wrap the :order-by argument into a list. Also,
if we need to reverse the sort order, the argument becomes
((["table" ScientificName] :desc)) - a single sort
criterion, itself a list comprising a field and a keyword denoting
direction.
in select all any existsWe are near the end of our tour of select and the
[...] syntax. Let's revisit two queries using subselects: what are the
scientific names of species whose common names begin with a 'v'? and
who researched species whose common names begin with a 'v'?. We build
the subselect with the [select] operator, which takes
most of the same arguments as the function select:
CL-USER 229 > (select [ScientificName] :from [SpeciesList]
:where [in [LocalName]
[select [LocalName] :from [SpeciesList]
:where [like [LocalName ] "v%"]]]
:flatp t)
("Hernandia guianensis Aubl." "Virola surinamensis Warb." NIL NIL)
("SCIENTIFICNAME")
CL-USER 230 > (select [Researcher] :from '([TreeData] [SpeciesList])
:where [and [= [TreeData SpeciesID]
[SpeciesList SpeciesID]]
[in [LocalName]
[select [LocalName] :from [SpeciesList]
:where [like [LocalName]
"v%"]]]]
:distinct t :flatp t)
("Fernando")
("RESEARCHER")
CL-USER 231 >
Operators [in], [all], [any]
and [exists]need a list as their
argument. [select] returns a list.
Two final examples: which sites were first surveyed "before" any species data had been accumulated? Were any species data accumulated on days when no sites were surveyed?
CL-USER 231 > (select [DemoSite] :from [SampleAreas]
:group-by [DemoSite] :flatp t
:where [<= [Date]
[all [select [Date] :from [SpeciesData]]]])
("Macapa")
("DEMOSITE")
CL-USER 232 > (select [SpeciesID] :from [SpeciesData]
:where [not [exists
[select [*] :from [SampleAreas]
:where [= [SpeciesData Date]
[SampleAreas Date]]]]])
((1) (113) (195))
("SPECIESID")
CL-USER 233 >
Now that we have the pain of select out of the way,
the going gets easier.
CL-USER 321 > (defvar aardvark [= [LocalName] "Aardvark"])
AARDVARK
CL-USER 322 > (values (select [*] :from [SpeciesList] :where aardvark))
NIL
CL-USER 323 > (with-transaction
(insert-records :into [SpeciesList]
:attributes '([LocalName])
:values '("Aardvark")))
NIL
CL-USER 324 > (values (select [*] :from [SpeciesList] :where aardvark))
(("Aardvark" NIL 208))
CL-USER 325 > (with-transaction
(update-records [SpeciesList] :where aardvark
:av-pairs '(([ScientificName]
"Orycteropus afer"))))
NIL
CL-USER 326 > (values (select [*] :from [SpeciesList] :where aardvark))
(("Aardvark" "Orycteropus afer" 208))
CL-USER 327 > (with-transaction
(delete-records :from [SpeciesList] :where aardvark))
NIL
CL-USER 328 > (values (select [*] :from [SpeciesList] :where aardvark))
NIL
CL-USER 329 >
There are two methods of specifying values and attributes to
insert-records and update-records and both
are illustrated in the above examples. If you are supplying values for
every attribute in the table then specify just the
:values argument.
The :where clause in update-records can be as
simple as the above, or as complex as you like.
Common SQL prvoides three simple ways to traverse the rows of a
table: a function corresponding to map, a macro similar
to dolist, and an extension to the loop
macro. Let's assume I've restored the aardvark...
CL-USER 344 > (map-query 'vector
'print
[select [*] :from [SpeciesList]
:where aardvark])
("Aardvark" "Orycteropus afer" 209)
#(("Aardvark" "Orycteropus afer" 209))
CL-USER 345 > (do-query ((local scientific id)
[select [*] :from [SpeciesList]
:where aardvark])
(print (list local scientific id)))
("Aardvark" "Orycteropus afer" 209)
CL-USER 346 > (loop for columns being the records of
[select [*] :from [SpeciesList]
:where aardvark]
do (print columns))
("Aardvark" "Orycteropus afer" 209)
NIL
CL-USER 347 >
Note by the way that the argument decomposition in
do-query is like multiple-value-bind and not
like destructuring-bind.
We've seen how to use the functional interface to query, iterate over, and update the contents of tables. We now turn to three pairs of functions for maintaining those tables.
Actually, one function in each pair is so easy that I'm going to
break logical order and mention them first. They each take one
argument (in addition to the usual :database keyword),
for example: (drop-table [foo]).
drop-table
drop-index
drop-view -- but note that Access
doesn't implement DROP VIEW, so you should use
drop-table instead.
Going the other way involves just a little more detail.
The required arguments for create-table are its name
and a list describing each of the columns. Regrettably, you'll need to
use database types rather than lisp types. Also, you're restricted to
fairly simple table definitions: you can't express such complexities
as FOREIGN KEY or REFERENCES or CHECK. Use (execute-command
"create table ...") instead. An example:
(create-table [foo]
'(([id] number primary key)
([name] (char 255) not null)
([comments] longchar)))
Next we have create-index. This only has one required
argument: a name, but you won't get very far unless you specify the
:on and :attributes keywords too:
(create-index [bar] :on [foo] :attributes '([id] [name]))
You can also set :unique, specifying that the columns
indexed must contain unique values.
Finally, use create-view to add new views to the
database.
(create-view [nullScientificName]
:as [select [*] :from [SpeciesList]
:where [null [ScientificName]]])
Common SQL's object-oriented interface allows you to map CLOS classes onto database views, class slots onto attributes in those views, and instances onto records from the views.
We start with the macro def-view-class. This is an
extended version of defclass - a
def-view-class form looks like an ordinary class
definition but with extra keywords. The macro establishes a Lisp view
of an underlying (base) table and is similar in concept
to SQL VIEWs.
The default superclass is
standard-db-object. If you mix in other superclasses, you
should ensure that your view-class does inherit from
standard-db-object.
By default the base table has the same name as the class. You
can instead use the :base-table class option
to set the name of the table corresponding to your class.
There is no :database argument - the class is not
tied down to any particular database.
The slot options each take a number of additional arguments:
:db-kind - set this to one of the following:
:base (default value) - the slot corresponds
to an ordinary attribute of the database view.
:key - an ordinary attribute of the database
view which also corresponds to part of the unique
key for this view. Every view-class should have at least one
:key attribute.
:virtual - the slot is an ordinary CLOS slot,
not associated with any database attribute.
:join - the slot corresponds to a join. A
slot of this type will contain a list of further view-class
objects. Use :join slots to link
:key attributes between this and other
tables.
:column - use this for :base and
:key slots to name the database attribute. If
:column is not given then it defaults to the slot
name. Note: set :column to the
symbol whose name names the attribute - you should not set this
option to a string.
:type - refers to the database type for this
attribute:
def-view-class type |
SQL type |
|---|---|
| (STRING n) | CHAR(n) |
| INTEGER | INTEGER |
| (INTEGER n) | INTEGER(n) |
| FLOAT | FLOAT |
| (FLOAT n) | FLOAT(n) |
| UNIVERSAL-TIME | TIMESTAMP |
(Recall that dates are held as universal-times, i.e. passed to you applications as integers.)
:db-info - a list of alternating keywords and
values, used to specify details for a :join slot:
:join-class - the name of the class to
join on.
:home-key - the element (or list of
elements) in this class to be a subject for the join. If an
element is a symbol then it names a slot, which must be a
:key. Otherwise it should be given a database
value (i.e. null, string or integer).
:foreign-key - as for
:home-key but referring to the foreign class. An
object from a join class will only be included in the
:join slot only if corresponding values are
equal.
See the documentation
for further :db-info keywords.
For example, suppose I want to investigate the observed heights of trees of particular species. I start by defining a view-class on the TreeData table. If it happens that I am only interested in some of the attributes, then I need only define slots for these:
(def-view-class |TreeData| () ((|TreeTagNumber| :type integer :db-kind :key) (|SpeciesID| :type integer) (|Height| :type float :reader treedata-height) (|Researcher| :type (string 50))))
I have chosen to name the class and attributes exactly as they appear in the database table. I obtained types using the conversion table above and the first of the meta queries examples from earlier on (section 3.3).
I now define a second view-class, this time on SpeciesList. This
time, I might want all the slots in the table. Also, I choose to use
lisp-like names and so have to specify :column and
:base-table options to provide a mapping onto database
names. I intend to use instances of this class for updating the
database, so I ensure that each slot can be initialized with a valid
value:
(def-view-class species-list ()
((local-name :column |LocalName|
:type (string 50)
:initarg :local-name)
(scientific-name :column |ScientificName|
:type (string 50)
:initform nil)
(species-id :db-kind :key
:column |SpeciesID|
:type integer
:initform 0))
(:base-table |SpeciesList|))
Finally, I subclass species-list and add a new slot to
hold join information. The slot-options for heights say
that this slot will hold a list of instances of
|TreeData| whose |SpeciesID| match our
species-id. Note that the
:base-table class option is not inherited from
species-list and has to be specified again.
(def-view-class tree-list (species-list)
((heights :db-kind :join
:db-info (:home-key species-id
:foreign-key |SpeciesID|
:join-class |TreeData|)))
(:base-table |SpeciesList|))
In the next section we'll see how to put these classes to use.
Let's start by querying members of TreeData:
CL-USER 227 > (select '|TreeData|) ((#<db-instance |TreeData| 584735692>) (#<db-instance |TreeData| 584735796>) (#<db-instance |TreeData| 584736052>) (#<db-instance |TreeData| 584736156>) (#<db-instance |TreeData| 584736260>) ...) CL-USER 228 > (describe (caar *)) #<db-instance |TreeData| 584735692> is a |TreeData| TreeTagNumber 1002 SpeciesID 132 Height 12.84000015258789 Researcher "Fernando" DATABASE #<SQL::ACCESS-ODBC-DATABASE "agrobiodiversity" 22D3C5EC> PHYSICAL NIL CL-USER 229 >
Note that the function select is overloaded: it can be
called with either:
one or more columns, and specifying the keyword argument
:from, as in section 4.1,
or
the names of one or more view-classes, in which case the
:from keyword is unnecessary and should be omitted. In
this second case, the return values (inside the list of lists) are
instances of the view-classes. If you name only one class in the call
you might as well specify :flatp.
Now let's see how joins work. First, in the more familiar SQL style:
CL-USER 398 > (select '|TreeData| 'species-list
:where [= [slot-value '|TreeData| '|SpeciesID|]
[slot-value 'species-list 'species-id]])
((#<db-instance |TreeData| 580360436> #<db-instance SPECIES-LIST 580362012>)
(#<db-instance |TreeData| 577210396> #<db-instance SPECIES-LIST 577210348>)
(#<db-instance |TreeData| 577210196> #<db-instance SPECIES-LIST 577210148>)
(#<db-instance |TreeData| 577210044> #<db-instance SPECIES-LIST 580362012>)
(#<db-instance |TreeData| 577209908> #<db-instance SPECIES-LIST 577209860>)
...)
CL-USER 399 >
Note the [slot-value ...] operator
inside the :where clause. Its first argument is one of
the view-class names in this select statement, the second argument
names a slot.
The alternative approach is to use the :join slot in
our tree-list view-class:
CL-USER 254 > (setf tree-1
(car
(select 'tree-list
:flatp t
;; equivalent to [= [|SpeciesID|] 1]...
:where [= [slot-value 'tree-list 'species-id]
1])))
#<db-instance TREE-LIST 543123180>
CL-USER 255 > (inspect *)
#<db-instance TREE-LIST 543123180> is a TREE-LIST
HEIGHTS #<unbound slot>
LOCAL-NAME "Abacate"
SCIENTIFIC-NAME "Persea gratissima Gaertn."
SPECIES-ID 1
DATABASE #<SQL::ACCESS-ODBC-DATABASE "agrobiodiversity" 20609C2C>
PHYSICAL NIL
CL-USER 256 : Inspect 1 > (slot-value tree-1 'heights)
(#<db-instance |TreeData| 543227084>
#<db-instance |TreeData| 543227220>
#<db-instance |TreeData| 543227356>
#<db-instance |TreeData| 543227492>
#<db-instance |TreeData| 543227628>
...)
CL-USER 257 : Inspect 1 > :d ; get inspector to redisplay tree-1
#<db-instance TREE-LIST 544048716> is a TREE-LIST
HEIGHTS (#<db-instance |TreeData| 543227084>
#<db-instance |TreeData| 543227220>
#<db-instance |TreeData| 543227356>
#<db-instance |TreeData| 543227492>
#<db-instance |TreeData| 543227628>
...)
LOCAL-NAME "Abacate"
SCIENTIFIC-NAME "Persea gratissima Gaertn."
SPECIES-ID 1
DATABASE #<SQL::ACCESS-ODBC-DATABASE "agrobiodiversity" 206D99DC>
PHYSICAL NIL
CL-USER 258 : Inspect 1 > (mapcar 'treedata-height
(slot-value tree-1 'heights))
(12.133333206176758
14.666666984558105
15.600000381469727
9.333333015441895
23.33333396911621
...)
CL-USER 259 : Inspect 1 >
Note here that until we specifically invoke a slot
reader (in this case, slot-value) on
heights, the slot is unbound. Note also
that database values associated with join slots are cached in the
database connection. If you redefine the view-class, or if the
database is shared and might have been updated by someone else, then
you must refresh the view, either by passing:
:refresh t
to select or by disconnecting and
connecting again. If you do not, then the slot may be
unbound or contain stale values. For example, suppose we redefine
view-class |TreeData| by adding the following slot:
(|Date| :type universal-time)
Then:
(defun refresh-test (refresh)
(let* ((select-461 [= [TreeTagNumber] 461])
(tree-461 (car (select '|TreeData|
:flatp t
:where select-461
:refresh refresh))))
(when (slot-boundp tree-461 '|Date|)
(list (slot-value tree-461 '|Date|)))))
(refresh-test nil) =& nil
(refresh-test t) =& (3124137600)
Finally, we have at our disposal all the same iteration constructs that we had before (section 4.3). This time the iteration focus is not a record (i.e. a tuple of attributes) but a tuple of instances. For example, returning to the iteration examples we used before:
CL-USER 361 > (do-query ((my-aardvark) [select 'species-list
:where aardvark])
(print my-aardvark))
#<db-instance SPECIES-LIST 574209404>
CL-USER 362 >
Four functions are provided for modifying a record from an instance:
(update-record-from-slot instance
slot) sets the attribute slot
of the record corresponding to
instance;
(update-record-from-slots instance
slots) takes a list of slot names as its second argument;
(update-records-from-instance
instance) sets all the attributes of the
appropriate record;
(delete-instance-records instance)
removes from the database the record corresponding to
instance.
If instance is associated with an
existing database record, then three update-mumble
functions will update that record. If
instance is not associated with a record,
then a new one is created. Examples:
CL-USER 69 > (setf my-aardvark
(make-instance 'species-list :local-name "Aardvark"))
#<db-instance SPECIES-LIST 543237852>
CL-USER 70 > (update-records-from-instance my-aardvark)
#<db-instance SPECIES-LIST 543237852>
CL-USER 71 > (select 'species-list :where aardvark)
((#<db-instance SPECIES-LIST 543237852>))
CL-USER 72 > (setf (slot-value my-Aardvark 'scientific-name)
"Orycteropus Afer")
"Orycteropus Afer"
CL-USER 73 > (update-record-from-slot my-Aardvark 'scientific-name)
#<db-instance SPECIES-LIST 543237852>
CL-USER 74 > (slot-value (car (select 'species-list
:where aardvark
:flatp t))
'scientific-name)
"Orycteropus Afer"
CL-USER 75 >
UncommonSQL is a database integration library for CL, based on MaiSQL, developed and maintained primarily by onShore Development. It is distributed under an MIT/X like license. A package that adds OBDC support for UncommonSQL will be found at http://www.dataheaven.de/.
The following notes document what I had to do to get UncommonSQL working with LispWorks and Access, on my NT machine.
Download CLOCC (the Common Lisp Open Code Collection) from http://clocc.sourceforge.net/, and the ODBC UncommonSQL Module from http://dataheaven.dnsalias.net/~neonsquare/usql-odbc.html.
Unpack both archives, under the same root directory (I used cygwin gunzip and tar, and unpacked under "d:/p4/user/ndl/lisp/ilc2002/test/").
In "clocc/clocc.lisp" change the value of
*clocc-root* to
"d:/p4/user/ndl/lisp/ilc2002/test/clocc/"
Now I can load the mk:defsystem utility:
(load "d:/p4/user/ndl/lisp/ilc2002/test/clocc/clocc.lisp") (load "clocc:src;defsystem;defsystem")
Permit LispWorks to redefine the SQL
package:
(setf *PACKAGES-FOR-WARN-ON-REDEFINITION*
(remove "SQL" *PACKAGES-FOR-WARN-ON-REDEFINITION*
:test 'equal))
Establish the following logical pathname translations:
(setf
(logical-pathname-translations "systems")
'(("maisql;**;*.*"
"d:p4/user/ndl/lisp/ilc2002/test/uncommonsql/**/*.*"))
(logical-pathname-translations "sql")
'(("**;*.*"
"d:p4/user/ndl/lisp/ilc2002/test/uncommonsql/dbms/odbc/**/*.*")))
In
"d:/p4/user/ndl/lisp/ilc2002/test/uncommonsql/dbms/odbc/odbc/odbc-ff-interface.lisp"
add an appropriate :lispworks feature, thus:
#+(and (or :lispworks :allegro) (not :unix)) (setf *foreign-module* "odbc32.dll")
and move the blanket (setf *foreign-module* "libodbc.so")
out of the way.
(load "d:/p4/user/ndl/lisp/ilc2002/test/uncommonsql/MaiSQL.system") (mk:oos "MaiSQL" :load)
(sql:connect '("" "" "agrobiodiversity") :database-type :odbc)
Note the different form of the connection specification!
ODBC is large; only the most commonly trodden paths are supported by Common SQL. If we are restricted to the simple approaches above, we occasionally feel the need to leave these paths, for example to get information about the database and the types it supports. Common SQL acts as a sort of barrier against such explorations:
on the plus side: it protects us from all sorts of nastiness;
on the minus side: it won't let us play nasty.
There is not much point in working alongside a copy of Microsoft's ODBC Programmer's Reference, because you won't be able to use most of it.
An example of the sort of thing which you can do, if you don't mind
experimenting with the results of apropos, is to obtain
the database connection's ODBC version:
CL-USER 170 > (odbc-common:sqlgetinfo (sql::hdbc *default-database*)
odbc-common:sql_odbc_ver)
0
"03.52.0000"
CL-USER 171 >
An example of the sort of thing which you can't do with Common SQL as it stands, although the ODBC Programmer's Reference says it's a valid query, is to determine the maximum permitted length of a character literal in an SQL statement:
CL-USER 171 > (odbc-common:sqlgetinfo (sql::hdbc *default-database*) 108) Error: unknown SQLGetInfo type 108 1 (continue) Return NIL 2 (abort) Return to level 0. 3 Return to top loop level 0. Type :b for backtrace, :c <option number> to proceed, or :? for other options CL-USER 172 : 1 >
In contrast, with the Python ODBC interface [eGenix 2001] we're less insulated from the coalface. We constantly have to mess with types in a way that we wouldn't have to in Common SQL. But if we wanted to determine the maximum character literal length, then we could do so easily:
>>> SQL.MAX_CHAR_LITERAL_LEN 108 >>> agrobiodiversity.getinfo(SQL.MAX_CHAR_LITERAL_LEN)[0] 255 >>>
In fact, it turns out that this query is possible from lisp if
you're prepared to modify the system a little. Given either flagrant
disregard for the LispWorks license agreement, or residence in the
European Community plus a claim to have met the conditions of Article
6 of Council Directive 91/250/EEC, creative use of error
backtraces, apropos and the inspector leads us to the
variable odbc-common::+finfotype-return-types+.
CL-USER 173 > (setf (aref odbc-common::+finfotype-return-types+ 108)
'(:unsigned :short))
(:UNSIGNED :SHORT)
CL-USER 174 > (odbc-common:sqlgetinfo (sql::hdbc *default-database*) 108)
0
255
CL-USER 175 >
An example of the sort of thing I always used to believe that we couldn't do, even with the ODBC Programmer's Reference: find out in advance of getting integrity errors whether the database is case sensitive when comparing character values to determine primary-key uniqueness.
CL-USER 175 > (with-transaction
(when (table-exists-p "foo")
(execute-command "drop table foo"))
(execute-command "create table foo (bar varchar (255),
primary key (bar))")
(execute-command "insert into foo values ('wombat')")
(execute-command "insert into foo values ('Wombat')"))
Error: Sql-Database-Data-Error id 23000[-1605] : [Microsoft][ODBC
Microsoft Access Driver] The changes you requested to the table were
not successful because they would create duplicate values in the
index, primary key, or relationship. Change the data in the field or
fields that contain duplicate data, remove the index, or redefine the
index to permit duplicate entries and try again.
1 (abort) Return to level 0.
2 Return to top loop level 0.
Type :b for backtrace, :c <option number> to proceed,
or :? for other options
CL-USER 176 : 1 >
When I showed a first draft of this tutorial to Xanalys, their support folks came up with the following inside information: in ODBC, you can check if a column is case-sensitive by using SQLColAttribute with SQL_DESC_CASE_SENSITIVE.
(defun column-sensitive-p (column table)
(let ((h (nth-value 1 (odbc-common:SQLAllocHandle
odbc-common:SQL_HANDLE_STMT
(sql::hdbc sql:*default-database*)))))
(unwind-protect
(progn
(odbc-common::sqlprepare h
(format nil "select ~a from ~a"
column table))
(= 1 (nth-value 1
(odbc-common::sqlcolattribute
h 1 odbc-common:sql_desc_case_sensitive))))
(odbc-common:sqlfreehandle odbc-common:SQL_HANDLE_STMT h))))
I don't think there's much to be learned from the lisp. It was a somewhat daft question anyway, because even with supposedly backend-independent SQL interfaces you always end up having to tweak for the target database, in which case questions such as the above can be answered at tweak-time. But there is a (generalised) moral to this story: if you're working on any serious lisp application and you find you've run aground, CONTACT YOUR PRODUCT'S SUPPORT TEAM.
A final note on limitations: we cannot use the Common SQL interface for database administration (creating the database, creating or removing users, granting or revoking privileges, etc).
| [eGenix 2001] | "mxODBC - An ODBC Interface for Python"; eGenix; 2001. |
| [PLEC] | "Amazonia Agrobiodiversity Database"; Environment and Sustainable Development Programme; United Nations University. |
| 2002-09-13 | NDL | Created. |
| 2002-09-25 | NDL | First draft complete. |
| 2002-09-27 | NDL | Updates based on reading first draft. |
| 2002-10-14 | NDL | Review complete, ready for distribution. |
September 2002
Copyright © 2002 by Xanalys LLC
All Rights Reserved.
You are permitted to view, copy, print and distribute this publication, subject to your agreement that: a) your use of the information is for informational, personal, and non-commercial purposes only, b) you will not modify the documents, publications or graphics, c) you will not copy or distribute graphics separate from their accompanying text and you will not quote materials out of their context, d) you will display the above copyright notice and other proprietary notices on every copy you make, and e) you agree that Xanalys LLC may revoke this permission at any time and you shall immediately stop your activities related to this permission upon notice from Xanalys LLC. Use for any other purpose is expressly prohibited by law, and may result in severe civil and criminal penalties. Violators will be prosecuted to the maximum extent possible.
The information in this publication is provided for information only, is subject to change without notice, and should not be construed as a commitment by Xanalys Limited or Xanalys LLC. Xanalys LLC assumes no responsibility or liability for any errors or inaccuracies that may appear in this publication.
The software described in this publication is furnished under license and may only be used or copied in accordance with the terms of that license. LispWorks is a registered trademark of Xanalys LLC. Microsoft is a registered trademark of Microsoft Corporation. Other brand or product names are the registered trademarks or trademarks of their respective holders.