CLSQL - Part 1
Sunday, June 6, 2004
After testing ACL's MySql support the other day, I received some email that suggested that I try out CLSQL as well. I knew that there were a number of different Lisp database libraries out there but I had never gotten around to trying out any single one of them seriously. Based on the recommendations I had received, I decided to have a look at CLSQL, and, on closer examination, it did seem to have a lot going for it:
- It supports most major CL and hardware platforms:
- Allegro CL on Linux, Solaris and MS Windows
- LispWorks on Linux and MS Windows
- CMUCL on Linux and Solaris
- SBCL on Linux
- OpenMCL on Linux (PowerPC) and Mac OS X
- SCL on Linux
- It supports most major databases using high-performance native APIs:
- MySQL - C API
- PostgreSQL - C API and Socket server
- Oracle - OCI API
- ODBC - C API
- SQLite - C API
- Allegro ODBC Interface - AODBC Library
- Although written by Kevin Rosenberg, it is a consolidation of ideas and work done by Kevin, Pierre Mai (author of MaiSQL), onShore Development (authors of UnCommonSQL), and Xanalys (authors of CommonSQL).
- It supports both a functional and an object-oriented
interface:
- The functional interface allows you to embed SQL expressions in Lisp (much like the ACL MySQL examples that I showed the other day.
- The OO interface provides a way to map CLOS objects into databases and includes functions for inserting new objects, querying objects, and removing objects.
- It is possible (and common) to mix the functional and OO interfaces in code.
- The CLSQL API is based on the CommonSQL package from Xanalys LispWorks so the documentation (and examples) that Xanalys produces applies (to a large extent) to CLSQL as well. Full conformance to the CommonSQL API is meant to be available in the next (3.0) release of CLSQL; however, my initial testing showed a high level of conformance to the CommonSQL API in the current release.
- Last (but not least), the same API works across all SQL implementations on all CL platforms. This makes it relatively simple to prototype an application on a free db (like SQLite or MySQL) and subsequently move the code to a different database if customer requirements so dictate.
- Download CLSQL v2.11.5 (earlier versions had some problems with MS Windows) and UFFI v1.4.20. You will also need asdf to build the packages.
- Download and install the database products that you want to use with CLSQL. I tested with MySQL and SQLite.
- The zlib.dll/zlib.lib files that come with MySQL cause problems when used with CLSQL. Download this alternative version of zlib instead and unzip the files into the C:/bin directory.
- Download Kevin's rtest utility so that you can run the database test suite to test the databases.
- Create a CLSQL configuration file named .clsql-test.config and put it in your HOME directory. This file will tell CLSQL where to find your test database for each of the database products that you have installed. There is an example of what this file needs to look like in the CLSQL examples directory.
- For each database product that you have installed, create the empty database that you named in the configuration file. This database will be populated by the test suite.
- You will need to run something similar to the following in order to build all the required packages:
;; Load packages:
(defvar *lisp-dirs* "c:/usr/home/lisp/" "Root location of CL library installs")
(load (concatenate 'string *lisp-dirs* "asdf/asdf.lisp"))
(setf asdf:*central-registry*
'(*default-pathname-defaults*
(concatenate 'string *lisp-dirs* "uffi-1.4.20/")
(concatenate 'string *lisp-dirs* "cl-rt-20030428b/")
(concatenate 'string *lisp-dirs* "ptester-2.1.2/")
(concatenate 'string *lisp-dirs* "clsql-2.11.5/")))
;; Run the test suite:
(asdf:oos 'asdf:test-op 'clsql)
After setting up and running CLSQL as shown above, I got the following test results (only
summaries are presented):
****************************************************************************** *** CLSQL Test Suite begun at 2004-06-06 19:02:25 *** International Allegro CL Enterprise Edition *** 7.0.beta [Windows] (Jun 4, 2004 9:49) on x86 *** Database MYSQL backend. ****************************************************************************** Doing 202 pending tests of 202 tests total. [snipped detail] No tests failed. Tests skipped: FDDL/VIEW/1 views not supported FDDL/VIEW/3 views not supported FDDL/VIEW/4 views not supported FDML/QUERY/5 not supported by mysql FDML/QUERY/7 not supported by mysql FDML/QUERY/8 not supported by mysql FDML/SELECT/1 fancy math not supported FDML/SELECT/5 subqueries not supported FDML/SELECT/10 subqueries not supported FDML/SELECT/11 boolean where not supported FDML/SELECT/22 not supported by mysql FDML/SELECT/26 string table aliases not supported on all mysql versions FDML/SELECT/32 subqueries not supported FDML/SELECT/33 subqueries not supported OODML/SELECT/5 boolean where not supported
****************************************************************************** *** CLSQL Test Suite begun at 2004-06-06 19:03:04 *** International Allegro CL Enterprise Edition *** 7.0.beta [Windows] (Jun 4, 2004 9:49) on x86 *** Database SQLITE backend. ****************************************************************************** Doing 210 pending tests of 210 tests total. [snipped detail] No tests failed. Tests skipped: FDDL/VIEW/4 not supported by sqlite FDML/SELECT/1 fancy math not supported FDML/SELECT/10 not supported by sqlite FDML/SELECT/11 boolean where not supported FDML/SELECT/21 not supported by sqlite FDML/SELECT/32 not supported by sqlite FDML/SELECT/33 not supported by sqlite OODML/SELECT/5 boolean where not supported
No tests failed for either MySQL or SQLite. There were a number of
skipped tests for both databases - this is indicative of a feature
that is not supported by that particular database. Surprisingly, SQLite had fewer skipped tests than MySQL.
Part 2 will discuss some of my experiences using CLSQL.
