SQLite: An interface to SQLite databases

_SQLite: An interface to SQLite databases_
==========================================
By Jay McCarthy (jay at kenyamountain dot com)
Keywords: _database_, _io_, _sqlite_
Introduction
============
I noticed many people asking for SQLite support and I wanted to learn the new FFI. So I took an afternoon and wrote this library.
_sqlite-ffi.ss_
---------------
This file contains the actual FFI code.
If you have a problem loading the sqlite library, you need to change one of the first lines to the commented out line. This seems to be a problem with the FFI library.
If you are changing _sqlite.ss_ you will probably need to look at this, but otherwise you won't.
_sqlite.ss_
-----------
This is the main file for the library. Require it like this:
> (require (planet "sqlite.ss" ("jaymccarthy" "sqlite.plt" 1)))
It provides the follow procedures:
> (open db-path) :: string -> db
Opens the SQLite database at ``db-path''.
> (close db) :: db -> integer
Closes the database, db, returning a status code.
> (exec db sql callback) :: db string (list-of-strings list-of-strings -> integer) -> integer
Executes the sql with the given db, calling the callback with two lists, one of the column names and one of the column values, returning a status code. If the sql does not return results, the callback will not be called. The callback should return 0 for success.
> (exec/ignore db sql) :: db string -> integer
A wrapper around exec that provides a meaningless callback.
> (select db sql) :: db string -> list-of-lists-of-strings
Executes the sql with the given, collating the results in to a list where each element is a list of the columns values.
> (prepare db sql) :: db string -> statement
Compiles the sql into a statement object for the given db. The sql may contain ``?'' to mark a parameter.
> (load-params statement . params) :: statement list-of-strings -> integer
Loads the params into the statement, returning a status code.
> (step statement) :: statement -> list-of-strings
Steps the statement to the next result, returning the column values as a list. (If the statement does not return results, this returns the empty list.) (Use for SELECT)
> (step* statement) :: statment -> list-of-list-of-strings
Runs steps until it is done. (Use this rather than select or exec when you want to use a placeholder (?) in the query and have SQLite do the quoting for you.)
> (run statement . params) :: statement list-of-strings -> integer
Loads the params in the statement, then runs the statement, returning a status code. (If the statement returns reults, they are not available.) (Use for UPDATE and INSERT)
> (finalize statement) :: statement -> integer
Finalizes a statement, and returns a status code.
> (transaction db fail body ...) :: syntax
Executes the body within a transaction on the given db, with the fail symbol bound to a procedure that escapes from the body and fails the transaction.
> (transaction/lock db lock-type fail body ...) :: syntax
Like transaction, except that the transaction is locked as lock-type. Refer to the SQLite locking documentation [1] for explanation of different modes.
> (errmsg db) :: db -> string
Returns the message for the last error with the database.
> (changes-count db) :: db -> integer
Returns a count of how many changes have been made to the database in the current session.
> (total-changes-count db) :: db -> integer
Returns a count of how many changes have been made to the database since its creation.
Examples
========
See the code in the tests/ directory.
Notes and Limitations
=====================
I'm fairly certain that the memory problems are gone, but let me know if you find them.
I'd like to port the s-expr -> SQL portion of Schematics' SchemeQL project to be a general library that can be used with this library as well.
History
=======
v1.5 :: April 7th, 2005
* Added step*
v1.4 :: March 28th, 2005
* Fixing some contracts
* Added 'errmsg'
* Fixing transaction behaviour ("END" is actually a synonym for "COMMIT")
* Remove sqlite-oo.ss, it was a bad idea.
v1.3 :: March 25th, 2005
* Fixed require lines (Jacob Matthews)
* Fixed possible leak with lambdas passed directly to the FFI (Eli)
* Fixed position of finalizers (Eli)
v1.2 :: March 25th, 2005
* Added documentation
* Added sqlite-oo.ss module
v1.1 :: March 24th, 2005
* Added support for SELECT statements
v1.0 :: March 24th, 2005
* Added support for transactions
* Added basic finalizers
v0.0 :: March 24th, 2005
* Initial release
References
==========
1. Locking and Concurrency in SQLite Version 3
http://www.sqlite.org/lockingv3.html