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.

  1. Use the ODBC "Control Panel" applet to create a data-source corresponding to the file "4-AmazoniaAgrobiodiversity.mdb". I called my data-source Agrobiodiversity.

  2. 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.

  3. Load Common SQL into the lisp image:

    (require "odbc")
  4. 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"
  5. 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.

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:

  1. the symbol's package is explicitly qualified;
  2. the symbol belongs to one of the packages which are used by default, namely: COMMON-LISP, LISPWORKS, HARLEQUIN-COMMON-LISP;
  3. if neither of the above applies then the symbol belongs to and is exported from the SQL package.

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 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 >

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-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.

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 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]]])

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 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.

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 :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 >

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 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 >

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.

  1. 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.

  2. Unpack both archives, under the same root directory (I used cygwin gunzip and tar, and unpacked under "d:/p4/user/ndl/lisp/ilc2002/test/").

  3. In "clocc/clocc.lisp" change the value of *clocc-root* to "d:/p4/user/ndl/lisp/ilc2002/test/clocc/"

  4. Now I can load the mk:defsystem utility:

    (load "d:/p4/user/ndl/lisp/ilc2002/test/clocc/clocc.lisp")
    (load "clocc:src;defsystem;defsystem")
    
  5. Permit LispWorks to redefine the SQL package:

    (setf *PACKAGES-FOR-WARN-ON-REDEFINITION*
          (remove "SQL" *PACKAGES-FOR-WARN-ON-REDEFINITION*
                  :test 'equal))
    
  6. 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/**/*.*")))
    
  7. 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.

  8. (load "d:/p4/user/ndl/lisp/ilc2002/test/uncommonsql/MaiSQL.system")
    (mk:oos "MaiSQL" :load)
    
  9. (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 $

Ravenbrook / ILC 2002

Company     Contact     Privacy Policy     Terms of Use