Gambit MySQL client 0.2

After a few weeks wrestling with the MySQL protocol, I've finally managed to produce a reasonable attempt at a client for Gambit (and probably other Scheme's).

This is a pure Scheme implementation rather than using Gambit's C interface to glue the existing MySQL C API into Gambit. The reason for this is that Gambit's power, and in my view fantastic potential as a web platform, comes from it's lightweight threads which allows Gambit to process thousands of concurrent requests in a single OS thread. This is all acheived by an internal schedular, continuations and very careful use of non-blocking calls on all I/O functions. Using the C API effectively takes the OS thread out of Gambit and into the C code and therefore all of those lovely threads stop executing until your call is completed - seriously bad news for a web application.

This library is now around 80% complete and supports the following features :-

• Multiple concurrent connections to MySQL,
• Authentication using both the old and new authentication methods,
• Switching Schema's,
• Dynamic queries, updates, deletes etc.
  
• Prepared statement creation,
• Executing prepared statements (queries and updates) with bind variables for most datatypes,
• Closing prepared statements,
• Closing connections.

Additionally, I've also introduced a SQL Abstraction Layer. This is equivalent to JDBC in Java which defines a few generic SQL types and provides a common API across database vendors. The idea behind this is that if someone else provides another database client, they can use the same abstraction layer and then any programs can switch between databases without having to rewrite their code (hopefully).

There are a few limitations with the driver, i.e. features that aren't implemented yet :-

• Not all datatypes are supported yet in prepared statements e.g. floats, doubles, sets, enums and one or two others. Some of these can easily be avoided e.g. by using DECIMAL instead of FLOAT or DOUBLE.
• BLOB's and large character fields are not yet supported in either dynamic or prepared statements.
• Large result sets requiring supplementary fetches aren't supported as I haven't implemented the additional fetch function.

Hopefully I'll add these things over the coming weeks and months. There are likely to be a few bugs in there, so if you find one or you are desperate for a feature that hasn't been implemented yet then please email me.

The library is in the Gambit Dumping Grounds. Here's an example of how you use it :-

(load "sql")
(load "mysql")

(with-connection (sql-connect "localhost" 3306 "test" "fred" "fred")

;;(mysql-execute "drop table test")
(mysql-execute "create table test (id integer PRIMARY KEY AUTO_INCREMENT, name varchar(50), inserted datetime, amount decimal(10,2) DEFAULT -4.5)")
(pp (sql-execute "select * from test"))
      

;; Prepared Statements
(let ((stmt1 (sql-prepare-statement "insert into test (name, amount, inserted) values (?, ?, sysdate())"))
      (stmt2 (sql-prepare-statement "select id, name, amount, inserted from test where amount > ? order by id")))

  (pp (sql-execute-prepared-statement stmt1 '("Fred" 10.5)))
  (pp (sql-execute-prepared-statement stmt1 '("Joe" 5.5)))
  (sql-close-statement stmt1)


  (pp (sql-execute-prepared-statement stmt2 '(4.5)))
  (sql-close-statement stmt2))

;; Close connection
(sql-close-connection)))

Result sets are returned as a list of lists i.e. a list of column values for each row in a list for the result set. The first row in the list is the column headings. For dynamic statements, the column data is always a string regardless of the SQL type because this is the way that MySQL sends it and I haven't implemented any type conversions yet. Prepared statements return binary types so I've mapped some of these e.g. strings and numbers into their respective Scheme types.

I would strongly recommend using prepared statements rather than dynamic statements for performance reasons and also for their natural resistance to SQL injection attacks in web applications.

Once the library is finished, I'll create a persistence layer that builds on top of the prepared statements to give a framework similar to ActiveRecord in Rails or Hibernate in Java.