- 2. Introduction
- 3. Elementary interactions
- 4. Functional interface
- 5. OO interface
- 6. Moving on
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
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.
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.
The database which I have chosen for examples in this tutorial is the Amazonia Agrobiodiversity Database, freely available from:
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:
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:
You can now connect to the data-source:
The required argument to
connectis 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
Note: if the data-source requires a name or password then the form of the first argument is:
This database will now be used, by default, in all sql operations.
There's not a lot else you need to know about
The opened database is represented by a lisp object which is (a) returned
and (b) stored into the variable
Almost every function in Common SQL takes a
argument. The default value of this argument is given by the current
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
As an example, consider the function
which closes an existing connection to a data-source. To disconnect
from the current
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
:database keyword, unless I state otherwise.
Other functions worth noting in this area are
For reasons of brevity, I am going to drop the
prefix. In the lisp sessions I used for testing the examples for this
tutorial, I simply used the
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:
- the symbol's package is explicitly qualified;
- the symbol belongs to one of the packages which are used by default,
- if neither of the above applies then the symbol belongs to and
is exported from the
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
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 >
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
queryreturns (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
This takes the same arguments as
query plus additional
keywords for specifying an output
:stream, and the
: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
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
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
Alternatively you can use the functions
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
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
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 results
Traffic appears on
*standard-output*. To redirect this
output, see add-sql-stream
and friends. To halt the flow, call
with the same
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 >
select is harder to learn to use than
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
When you come to writing applications which use Common SQL, you should read the documentation on
locally-enable-sql-reader-syntaxand its relatives. These are a little more subtle than
None of the reader-syntax control functions take a
:databaseargument. 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
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
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
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 >
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
is bound to an SQL identifier and its value is then substituted as
the argument to
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
argument it substitutes for one of lisp's SQL identifiers without
The comparison operators appear in conjunction with arguments
and (for aggregate values)
:having to the function
This is a good place to introduce
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?
What 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 >
Which 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 >
Who 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.
Let'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
belong unambiguously to one table each, but
would be ambiguous and so must be qualified. We do this by prepending
the table name, as in
[TreeData SpeciesID]. Note
: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
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
"join" which are established in the
clause. Note that - in contrast to singleton values
:from - we are always obliged to wrap the
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.
We 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
[select] operator, which takes most of the same
arguments as the function
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 >
[exists]need a list as their argument.
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
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
:where clause in
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
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
do-query is like
and not like
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-view-- but note that Access doesn't implement DROP VIEW, so you should use
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
"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
: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.
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
defclass - a
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
By default the base table has the same name as the class. You can instead use the
:base-tableclass option to set the name of the table corresponding to your class.
There is no
:databaseargument - 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
: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
:joinslots to link
:keyattributes between this and other tables.
:column- use this for
:keyslots to name the database attribute. If
:columnis not given then it defaults to the slot name. Note: set
:columnto 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:
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-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-keybut referring to the foreign class. An object from a join class will only be included in the
:joinslot only if corresponding values are
See the documentation for further
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
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
say that this slot will hold a list of instances of
|SpeciesID| match our
Note that the
:base-table class option
is not inherited from
species-list and has to be specified
(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
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
:fromkeyword 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
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 >
[slot-value ...] operator
: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
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,
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:
select or by
again. If you do not, then the slot may be unbound or contain stale
values. For example, suppose we redefine view-class
by adding the following slot:
(|Date| :type universal-time)
(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
slotof the record corresponding to
(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 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
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
(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
#+(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) 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
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-25||NDL||First draft complete.|
|2002-09-27||NDL||Updates based on reading first draft.|
|2002-10-14||NDL||Review complete, ready for distribution.|
Copyright © 2002 by LispWorks Ltd
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 LispWorks Ltd may revoke this permission at any time and you shall immediately stop your activities related to this permission upon notice from LispWorks Ltd. 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 LispWorks Ltd. LispWorks Ltd 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 LispWorks Ltd. Microsoft is a registered trademark of Microsoft Corporation. Other brand or product names are the registered trademarks or trademarks of their respective holders.