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

11 Responses to “ cl-mysql ”

  1. david says:

    “CLSQL is installable via asdf. Simply enter the following into your REPL:”

    I think you mean cl-mysql. :-).
    Good job.

  2. Steve Knight says:

    Eeek! Thanks Dave. Corrected now.

    Steve

  3. Could I convince you to work on Allegro next? Am willing to guinea pig for working code :) I tried the current point release with the usual (asdf-install:install ‘cl-mysql) but it died after much ado.

    –hsm

  4. Steve Knight says:

    Done. See compatability matrix for details of other CL implementations

  5. Sergey says:

    Hey, nice work.
    Prepared statement support still on schedule?
    I want to use cl-mysql now, but just don’t know how to prevent code injection problem (if so far all requesting from cl side goes as strings)

  6. Steve Knight says:

    Thanks, I’ve emailled you about this. But the short answer is that it’s still work in progress. But you can do what you want by creating stored procedures and executing those instead of prepared statements …

    Steve

  7. Jason says:

    Hello,

    When a query result contains a UTF-8 character, I get the following error:

    Illegal :UTF-8 character starting at position 4.
    [Condition of type BABEL-ENCODINGS:INVALID-UTF8-CONTINUATION-BYTE]

    Is this a bug in cl-mysql? If not, how can I avoid it?

    Thanks,
    Jason

  8. Steve Knight says:

    Update: In a private email Jason let me know that executing

    set names 'utf8'

    Works around the issue.

  9. Daniel Collin says:

    Thanks a lot for your work on this!

    In my opinion CLSQL has always been a bit bulky and hard to get working correctly so what I did was to write my own smallish wrapper above the mysql C API.

    Your lib seems to be very nice though and fit my needs perfectly.

    I have only done some initial testing but it seems to work just fine under Mac OS X which I use as primary system.

    Cheers!

  10. Steve Knight says:

    You’re welcome! I have to admit that I haven’t done a lot to it recently. I was trying to get parameterized SQL working and support for a few more Common Lisp variants. Perhaps I’ll dust it off …

    Steve

  11. Tapiwa says:

    Great job with the library, I do prefer the thin abstraction. I couldn’t find your email so figured I will just post here.

    I made a few changes to the WITH-ROWS macros to fix a blocking connection issue that kept popping up if I had an error in my code because the connection ends up never being released.

    Can you also modify the error signalling functions to release the connection if an error is signalled due to an error in the SQL syntax, it seems the connection is never cleaned up and remains blocking because of that.

    (defmacro with-gensyms (syms &body body)
    “WITH-GENSYMS takes a list of symbols SYM and a list of body forms.
    It returns a LET clause with each symbol in SYM bound to a gensymbol and the body forms as the body of the let clause.”
    `(let ,(mapcar #’(lambda (s) `(,s (gensym))) syms)
    ,@body))

    ;; This macro replaces the one provided by CL-MYSQL and offers two improvements.
    ;; The CL-MYSQL does not release a connection if an error is signalled in the body code,
    ;; If VAR-LIST is a list of variables, a destructuring-bind will be used, if its a symbol
    ;; it will just be bound to the list returned by CL-MYSQL:NEXT-ROW.
    (defmacro with-rows ((var-row query &key (database ‘cl-mysql::*last-database*) (var-result (gensym)) (type-map ‘cl-mysql::*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.”
    (with-gensyms (connection row)
    `(let ((,connection (cl-mysql:query ,query :type-map ,type-map :database ,database :store nil)) (,var-result 0))
    (unwind-protect
    (loop
    :while (cl-mysql:next-result-set ,connection :dont-release t)
    :do
    (loop
    :for ,row = (cl-mysql:next-row ,connection :type-map ,type-map)
    :while ,row
    :do
    ,(if (listp var-row)
    `(destructuring-bind ,var-row ,row
    ,@body)
    `(let ((,var-row ,row))
    ,@body)))
    (incf ,var-result))
    (cl-mysql-system:release ,connection)))))

Trackbacks & Pingbacks:

Leave a Comment