Overview

cl-mysql is a Common Lisp implementation of a MySQL wrapper. This functionality is largely a subset of what is already available in the CLSQL library. However, as already mentioned super APIs generally have the problem of hiding the light switches. So why should you choose cl-mysql over clsql?

Well, the answer is you’d need a few good reasons to do that because CLSQL probably has a larger developer base and has been tested on more platforms. Indeed, you should definitely not choose cl-mysql if you intend your application to connect to more than one database or if you can ever forsee switching database for your application in the future. As it turns out, most of the applications that I write don’t have this limitation. Conversely, in my opinion the current version of CLSQL (4.0.4) does indeed have some limitations:

• Multiple statements/result sets/stored procedures – CLSQL is written against a version of MySQL that does not support multiple statements/result sets and as such you need to patch CLSQL to make it work. The patch is fairly simple but I’m unable to contact the developers to get it applied. As it turns out if you write a stored procedure to return a single result set you must still enable and handle CLIENT_MULTI_RESULTS.
• Return types – CLSQL returns most things as string, except for some float/integer numerics. If you are making use of things like spatial extensions, blobs, bit, binary or varbinary CLSQL will stringify them. Which might be what you want but also might not. CLSQL does not attempt to decode times, dates and large numerics. CLSQL has some bugs relating to unsigned integers.
• Simpler deployment – CLSQL requires some shared/dynamic libraries to run. These shared libraries offer 64 bit support. However because of the way CLSQL loads into the Lisp image you can not easily, in SBCL for example, load the CLSQL and save-lisp-and-die and deploy the core. It’s possible to do but you must deploy these shared libraries in the same paths that they were built in when CLSQL was installed or find a way to relocate the libraries to a new path. I don’t want to install in the same path as my dev system and I have tried relocating, whatever way you do it it’s pretty ugly.

Documentation

cl-mysql is installable via asdf and git.

For the latest point release from asdf simply enter the following into your REPL:

CL-USER> (asdf-install:install 'cl-mysql)
CL-USER> (asdf:oos 'asdf:test-op 'cl-mysql)

For a source distribution install git.

git clone git://github.com/hackinghat/cl-mysql.git

connect &key host database user password port socket client-flag

Connect will present to MySQL sensible defaults for all the connection items.
The following code will attach you to a MySQL instance running on localhost,
as the current user with no password. It will automatically turn on
compression between client-and-server and also enable multiple-result sets
if possible.

CL-USER> (connect)

If unsuccesful connect will raise a CL-MYSQL-ERROR, otherwise it will place
the connection into a pool, note that all connections are pool-able,
a single connection is simply the special case of a pool with only one
connection.

The pool has two slots, min-connections and max-connections. There will
always be min-connections available in the pool. If you are using all
min-connections and max-connections is greater than min-connections,
the pool will continue to allocate connections until max-connections are
used. After this an attempt to aquire more connections from the pool
should block (if your implementation supports it). When a connection is
released (this is done automatically unless you explicity disable it) the
connection to the server is closed if we have allocated more connections
than min-connections.

The last allocated pool object is placed into a special variable
*last-database* which is defaulted from the higher level API functions.

use name &key database

This is equivalent to running:

(query "USE <name>")

next-result-set ((self connection) &key dont-release store

Position for the the next result set. Returns T if there is a result
set to process and NIL when the last result set has been passed.

sets. Use this method with (query \”SELECT\” :store NIL). Call
next-result-set to position on the first result set then use next-row
to fetch all the individual rows.

Use dont-release if you don’t want cl-mysql to release all the resources
when the last result set has been processed. This might be useful, for
instance, if you need access to the fields through result-set-fields after
the result set has been processed.

You can, if you wish elect to not process each individual row of a result
set by setting :store T. However, you cannot then use next-row because
you must process the entire result set in one go.

CL-USER> (query \"SELECT ...\" :store nil)
   CL-USER> (next-result-set *)
   T
   CL-USER> (next-row **)
   ...

next-row ((self connection) &key (type-map *type-map*))

Retrieve and decode (according to the type map) the next row in the query. This
function will return NIL when the last row has been retrieved.

query&key (type-map *type-map*) database store

For a SELECT query or stored procedure that returns data, query will return
a list of result sets. Each result set will have 1 or more sublists
where the first sublist contains the column names and the remaining lists
represent the rows of the result set. If the query does not return a result
set (for example if the query is an INSERT, UPDATE) the return value is the
number of rows affected. Because cl-mysql supports multiple-statements
you can execute code like the following:

CL-MYSQL-SYSTEM> (query \"CREATE TABLE a(a INT); 
                  INSERT INTO a (a) VALUES (1); DELETE FROM a; DROP TABLE a\")
   ((0) (1) (1) (0))

The type-map, if set will alter the decoding into CL types. If you set
this to nil it will have the effect of disabling all CL type conversions
and return either character or binary data only.

This might be useful for performance reasons, (cl-mysql
is much faster when it doesn’t need to translate types) but it also might
be all you need. Consider for instance if you’re displaying a lot of
numerics on a web-page. If you do not need to convert the data into
floats/integers before displaying them on a page then raw could be useful
here too. cl-mysql attempts to convert all numeric types to their closest
CL representation. For very large numerics, or numerics that have very
high precision this might not be what you want. In this case you could
attempt to write your own conversion routine and inject it into cl-mysql
through the type-map.

The currented supported conversions are as follows (MySQL type -> CL type):

• DECIMAL/NUMERIC -> RATIO
• INT/TINYINT/SMALLINT/MEDIUMINT/BIGINT/YEAR -> INTEGER
• FLOAT/REAL/DOUBLE PRECISION -> DOUBLE-FLOAT
• DATE/DATETIME/TIMESTAMP -> INTEGER (Universal time)
• TIME -> INTEGER (Seconds)
• CHAR/VARCHAR/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT -> STRING
• BIT/BLOB/MEDIUMBLOB/LONGBLOB/TINYBLOB/GEOMETRY -> SIMPLE-ARRAY ‘(UNSIGNED-BYTE 8 )

If :store is T query returns a list of result sets. Each result set is a
list with the first element set to the data and the second elements set to
the column data. Since this structure can be a little awkward to handle
you can use nth-row to manipulate the structure more sanely.

If :store is NIL query returns the allocated connection object. You should
use next-result-set and next-row to step through the results.

disconnect &key database

Unless database is supplied, disconnect will take the top element of the connection stack and close it.

CL-USER> (disconnect &key database)

option option-keyword value &key database

Use this to set a client specific connection option.

CL-USER> (option #:opt-reconnect 1)




client-version &key database

Returns a three part list containing the decoded client version information

server-version &key database

Returns a three part list containing the decoded server version information

escape-string string &key database

Given a string, encode it appropriately. This correctly quotes things like carriage-return, line-feed, apostrophes and double-quotes. More details can be found in the MySQL manual. This function relies on the fact that the character set encoding was set to UTF-8 when the connection is made.

nth-row (result-set-list n &optional nth-result-set)

Return the nth-row of the nth-result set.

with-row

A macro that with the following signature:

with-rows ((var-row query-string
			  &key
			   (var-result (gensym))
			   (database *last-database*)
			   (type-map *type-map*))
		     &body body)

Takes a query-string and iterates over the result sets assigning
var-row to each individual row. If you supply var-result it will
hold the result set sequence number. This macro generates code
that does not store the complete result so should be suitable for
working with very large data sets.

Known bugs

cl-mysql is tested on only a few variations of OS and Lisp (see below for details).

Intended Feature Support

• Support for all major architecture, OS and CL
• Prepared statement support
• Mudballs deployment

Compatability Matrix

cl-mysql should be compatible with MySQL versions 5.0 and 5.1.

	XP	Linux
	32	32
Allegro 8.1
SBCL 1.0.22
CLISP 2.47
ECL 8.1
LispWorks 5.1

Legend:

Not tested
OK
Work in Progress