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 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)

> (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.

> (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.

_sqlite-oo.ss_
--------------

This module provides classes that offer the same API as _sqlite.ss_. Below I will only highlight what is different.

This module is required as such:

> (require (planet "sqlite-oo.ss" ("jaymccarthy" "sqlite.plt" 1)))

> (new db% (path string)) :: string -> db%

The db% class requires a path to the database.

> (send db% statement%) :: -> class

Returns the class that should be used to create statement objects.

> (new statement% (sql string)) :: string -> statement%

The statement% class requires the sql the statement is for.

Examples
========

See the code in the tests/ directory.

Notes and Limitations
=====================

I've noticed an occasional crash of DrScheme in between evaluations of the test code after a bug had been triggered in the previous invocation. I would not be surprised if there are memory problems in the code, as I have not written another FFI interface.

History
=======

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
	* Seems to crash less

v0.0 :: March 24th, 2005
	* Initial release

References
==========

1. Locking and Concurrency in SQLite Version 3
   http://www.sqlite.org/lockingv3.html