clsql-mysql and SBCL with unicode

Hi!

I've started to use CLSQL with SBCL 0.9.0, which have unicode support
already.  Also, I'm using MySQL and, therefore, clsql-mysql.

All of these packages are updated (as of today) on my debian unstable
box.

However, when I try to execute any SQL command with non-ASCII chars in
it there is an error.  I've investigated this error and I found two
problems: one specific to SBCL and the other, I believe, would affect
any implementation.

My current locale is Latin-1.  However, as far as I could tell, the
UFFI for SBCL with the sb-unicode feature transforms all :cstring UFFI
foreign-types to sb-alien:utf8-string, independently of the current
locale.

If I undertand the SBCL FFI correctly, this means that a foreign
function declared with an argument of type :cstring will transform a
Lisp string into an UTF-8 encoded c-string when it is called.
Many of the MySQL C API functions receive strings...

However, as my locale is Latin-1, the connection to the database is in
Latin-1, rather than UTF-8.  Therefore, SBCL sends an UTF-8 encoded
string to the database, which interprets it as a Latin-1 encoded string.
The result, is a garbled string in the database.

This does not affect an implementation whose FFI obeys to the current
locale.  For instance, Allegro works ok, because the conversion from
the Lisp string to the C string uses the current locale.

To fix this problem I see two possible alternatives:

 1. In clsql-mysql issue a "SET NAMES utf8" MySQL command after the
    database connection is established, which instructs the MySQL
    server to use UTF-8.  I believe that something such as the
    following would work:

   #+(and sbcl sb-unicode)
   (defmethod database-connect :around (connection-spec
                                        (database-type (eql :mysql)))
     (let ((db (call-next-method)))
       (database-execute-command "SET NAMES utf8" db)
       db))


 2. The other alternative I considered (but did not test) is to
    change UFFI so that, for SBCL, the :cstring is transformed into
    (* sb-alien:byte) and the uffi:with-string macro and similars
    would use sb-ext:string-to-octets to convert Lisp strings into
    sequences of bytes that correspond to the current locale.
    However, I am not sufficiently knowledgeable of SBCL and its FFI
    to be sure that this is the right approach...
    I can try this path and contribute to a fixed UFFI if this is the
    right way, but before I spend more time with this I would like to
    hear opinions from the experts on these topics...


Now, after we are sure that the Lisp is talking the same language to
the database, there is another problem.  The methods in mysql-sql.lisp
use the foreign function mysql-real-query, which receives a pointer to
a mysql structure, the string with the query and a long with the
length of the query.  A typical use is the following:

    (uffi:with-cstring (query-native query-expression)
      (if (zerop (mysql-real-query mysql-ptr query-native 
                                   (length query-expression)))

The problem with this is that the length of the Lisp string
query-expression might not be the length in bytes of the
query-native.  Indeed, if the string is UTF-8 encoded, the Lisp string
length is smaller than the byte length when we have non-ASCII chars.

The result of this is that the MySQL server will consider just part of
the SQL command, which may be (and is, in most of my cases)
syntactically wrong.

I believe that this problem affects all Lisps when the current locale
is UTF-8, although I've not tested it.

The solution for this?

Either use mysql-query instead of mysql-real-query, or compute the
correct length in bytes of the resulting foreign string.

I don't know if there is a simple, portable, way to compute the
correct length in bytes in UFFI...

Therefore, although the MySQL documentation recommends the
mysql_real_query because this way the command may have binary data
with NULs in it, I think that the best solution for clsql-mysql would
be to use mysql-query, instead.

What do you think?

Sorry for the long post...
--
João Cachopo