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