Thursday, November 03, 2005

I'm releasing version 1.0 of my clisp SQLite bindings for SQLite 2.8. If you've never used SQLite before, it's an embeddable SQL (duh) database. Check out SQLite. You can download my clisp binding at http://ucsub.colorado.edu/~williasr/sqlite-2.8-1.0.lisp. An ASDF/ASDF-Install-able version is Real Soon Now (tm). The bindings offer two levels of support: a low-level binding which is directly tied to the FFI (package SQLITE.RAW) and a more friendly, lisp-like interface built atop it (package SQLITE). Here's a glimpse of how it works:
[1]> (use-package :sqlite)
=> T
[2]> (setf my-db (sqlite-open #"~/tmp/test.db"))
=> #<SQLITE::DATABASE #P"/home/scottw/tmp/test.db" :OPEN>
[3]> (sql my-database "malformed sql")
*** - sql error 1: "near \"malformed\": syntax error. Offending query:
        "malformed sql"
Break 1 [4]> :r1
[5]> (sql my-database "create table bar (a, b)")
=> NIL
[6]> (sql my-database "insert into bar values (1, 2)")
=> NIL
[7]> (sql my-database "insert into bar values ('a', 'x')")
=> NIL
[8]> (sql my-database "select * from bar")
=> (("1" "2") ("a" "x"))
[9]> (sqlite-close my-database)
=> #<SQLITE::DATABASE #P"/home/scottw/tmp/test.db" :CLOSED>
[10]> (with-open-db (db #"~/tmp/test.db")
        (with-query (db (a b) "select a, b from bar")
          (format nil "Processing a row with a=~s, b=~s.~%" a b)))
Processing a row with a="1", b="2".
Processing a row with a="a", b="x".
=> T
So far as I've used it, I find with-query to be one of the most efficient and useful of the sql forms supplied with the binding. I optimized it for relatively high throughput (though for some reason, clisp creates 3 strings for every column in the row, odd), so it's fast in tight loops where your lisp must process a lot of data (it can handle a million 3-column rows in less than a second on my machine). Since the body of with-query is executed once for each row as the row is returned, it avoids building up potentially large lists, the way the plain SQL call is likely to. I find this form so particularly useful because it binds the columns from each result row to the specified variables, making them much more accessible. There are a few outstanding issues. The first is the lack of a with-transaction. Inserts into the database are much more efficient when wrapped in a transaction, but I haven't yet worked out how to wrap the body of with-transaction so that "rollback" is called if the body is left abnormally, and "commit" is called otherwise. I suspect some trickery with unwind-protect and catch is going to be necessary, though it's not obvious to me how to do it, so I'm delaying a while. Next, I plan to explore the MOP by building a Class::DBI-style object mapper. CLSQL already does this, but a) it doesn't compile out of the box with clisp, and b) I want to make my own.

0 Comments:

Post a Comment

<< Home