SQLite: An interface to SQLite databases
_SQLite: An interface to SQLite databases_
By Jay McCarthy (jay at kenyamountain dot com)
Keywords: _database_, _io_, _sqlite_
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.
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.
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  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.
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.
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.
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
1. Locking and Concurrency in SQLite Version 3