1. Table of contents
2. Introduction
This document was written on behalf of LispWorks Ltd 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/.
2.1. 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 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, available online at the LispWorks website (https://www.lispworks.com/).
2.2. Example database
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.
2.3. Getting started
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
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
:if-existskeyword 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:databasearguments 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.
2.4. SQL Package
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:
- the symbol's package is explicitly qualified;
- the symbol belongs to one of the packages which are used by default,
namely:
COMMON-LISP,LISPWORKS,HARLEQUIN-COMMON-LISP; - if neither of the above applies then the symbol belongs to and
is exported from the
SQLpackage.
3. Elementary interactions
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.
3.1. Queries
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
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 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 >
3.2. Updates
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 >
3.3. Meta queries
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.
3.4. Monitoring SQL traffic
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 results
Traffic 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.
4. Functional interface
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.
4.1. Select and the [...] syntax
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-syntaxand its relatives. These are a little more subtle than(enable-sql-reader-syntax). -
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.
4.1.1. Arithmetic operators:
+ - * /
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 >
4.1.2. Aggregates: 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.
4.1.3. Comparisons: <
<= = > >= between
The 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?
4.1.4. Strings: like
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 >
4.1.5. null
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 >
4.1.6. Removing duplicates:
distinct
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.
4.1.7. Logical: and
or not
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 [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.
4.1.8. Subselects: in
select all any exists
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
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 >
4.2. Updates etc
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.
4.3. Iteration
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.
4.4. Table maintenance
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 usedrop-tableinstead.
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]]])
5. OO interface
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.
5.1. Managing view classes
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 fromstandard-db-object. -
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:keyattribute. -
: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:baseand: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:def-view-classtypeSQL 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:joinslot:-
: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 areequal.
See the documentation for further
:db-infokeywords. -
-
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.
5.2. Selecting on view classes
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
: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: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 >
5.3. Updating via view classes
Four functions are provided for modifying a record from an instance:
-
(update-record-from-slot instance slot)sets the attributeslotof the record corresponding toinstance; -
(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 toinstance.
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 >
6. Moving on
6.1. UncommonSQL
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
SQLpackage:(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
:lispworksfeature, 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!
6.2. Limitations
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 LispWorks, 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).
A. References
| [eGenix 2001] | "mxODBC - An ODBC Interface for Python"; eGenix; 2001. |
| [PLEC] | "Amazonia Agrobiodiversity Database"; Environment and Sustainable Development Programme; United Nations University. |
B. Document History
| 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 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.
$Id: reference:sql-tutorial:index.html,v 1.2 2003/01/08 15:47:51
davef Exp $