Just trying to use CLSQL with an existing MySQL database and ran into a problem. I've created my (clsql:def-view-class mytable () ...) classes which correspond to my existing tables. I start SBCL and type in a few expressions:

> (require 'clsql)

NIL

> (clsql:connect '("localhost" "mydatabase" "me" "mypassword" "3306"))

#<CLSQL-MYSQL:MYSQL-DATABASE localhost:3306/mydatabase/me OPEN {B0E00F1}>

> (clsql:select 'mytable)

and, uh oh, it says

While accessing database

#<MYSQL-DATABASE localhost:3306/mydatabase/me OPEN {B278641}>

with expression \"SELECT MYTABLE.FIELD1...\":

Error 1146 / Table 'mydatabase.MYTABLE' doesn't exist

has occurred.

[Condition of type CLSQL-SYS:SQL-DATABASE-DATA-ERROR]" nil)

What's happened is that CLSQL is generating SQL to send to MySQL which includes the table names in uppercase, as this is Lisp's default way of printing the symbols in which the table names are stored. MySQL on UNIX systems (not including MacOS X) defaults to treating table names case sensitively, and as my tables are named in lower case, MySQL reports a 1146 error.

There seem to be two possible solutions, one a MySQL fix, the other a Lisp fix.

The MySQL fix to alter the lower_case_table_names variable to 1 which stores table names in lower case on disk and makes table name comparisons case insensitive. This is what I've gone for. The only slight problem I had to overcome was a few tables that were named in mixed case in my existing database. I had to RENAME TABLE Aaa TO aaa before altering lower_case_table_names.

The Lisp fix I'm not really sure how to do elegantly. It's to do with how Lisp prints symbol names. It should be possible to call

(setf (readtable-case *readtable*) :preserve)

in order to make SBCL print symbol names in the same case that they were initially read and, as a result, make CLSQL generate SQL with the table names in the same case as the original (clsql:def-view-class ...) call. However, I'm not sure what would be a good way to alter *readtable*'s case to :preserve at the relevant times and then switch it back to the default (:upcase) in between. Maybe this counts as a CLSQL bug and a solution could be implemented upstream?