Artful Code

One of the more significant difficulties I face when developing Lisp applications is the apparent lack of generally compatable database libraries for the various lisp dialects. CLSQL is a nice solution, but it does not get along well on OSX, my development OS, or with CLISP, which is the primary lisp dialect I have available on my host.

My database of choice (and the one available to me on my host) is MySQL. Unfortunately, this is the piece of CLSQL that I can’t seem to get running on OSX. In every dialect I’ve tried (sbcl, cmucl, clisp, openmcl, ad infinitum), CFFI/UFFI cannot find the libmysqlclient library- even when it has been pushed onto the library search path… but that is a rant for a different blog (or Usenet).

CFFI is generally available via ASDF and plays well with GNU CLISP. It has a fairly simple, intuitive syntax for making C routines available to lisp (as a side note, I have not encountered anything as easy as newLisp’s C library importer, although this is made easier in that newLisp is implemented in C).

Using CFFI, we can fairly easily create mappings for the most important MySQL C api functions. This entry will walk through the process of importing the library; future entries will go one to defining a few of the most basic routines for our lisp image.

Let’s begin by defining our mysql wrapper package.

(defpackage :mysql-api
  (:use :common-lisp :cffi))
(in-package :mysql-api)

To load the library itself, we use the function, define-foreign-library, in the CFFI package. The first argument to define-foreign-library is the symbol we wish to point to the library. Next, we create list similar in syntax to cond, where we define the names of our libraries based on, for example, the host OS. Since I happen to know that CFFI does find the library path even when explicitly specified, we can conveniently specify a list of full paths to try for each OS. The full list of platform symbols available here.

(define-foreign-library libmysqlclient
  (:darwin (:or "/usr/local/mysql/lib/libmysqlclient.dylib"
                "/sw/lib/libmysqlclient.dylib"
                "/usr/local/lib/libmysqlclient.dylib"))
  (t (:default "libmysqlclient")))

The last line is a catch-all, and will format the filename based on the OS (i.e., libmysqlclient.so for unix, .dll for Windows). Although I know my library is located in /usr/local/mysql/lib, I have included various other commonly used paths for MySQL libraries in OSX.

Then, to load the library:

(use-foreign-library libmysqlclient)

This will return a pointer to a foreign address (for the C-challenged, a pointer is a variable to points to a value’s address in memory; it is used to access a value by reference, rather than by value) or, in the event that it did not work, it will throw up an error. Generally, the error will be caused by CFFI not being able to find the library. If this is the case, verify that your account has access to the file and that the file is included in one of the paths above.

Next, we will begin defining our C routines in lisp.