((( [logo]Racket )))Need Help?
AboutDownloadDocumentationPLaneTCommunityLearning
Home > jaymccarthy > sqlite.plt >  package version 3.1
doc.txt

SQLite: An interface to SQLite databases

_SQLite: An interface to SQLite databases_
==========================================

By Jay McCarthy (jay at kenyamountain dot com)
 & Noel Welsh (noelwelsh at yahoo 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.

Then Noel came along and made it work.


_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" 3)))

Types:

> exn:sqlite

This exception is thrown whenever the library encounters an
error.  The message field gives more information about the
error.

Functions and Syntax:

> (open db-path) :: path -> db

Opens the SQLite database at ``db-path''.

> (close db) :: db -> void

Closes the database, db

> (exec db sql callback) :: db string ((list-of vector) (list-of vector)) -> integer) -> void

Executes the sql with the given db, calling the callback for
each row of the results.  The callback is passed two
vectors, one of the column names and one of the column
values.  The callback returns an integer status code.  If
the status code is anything other than zero execution halts
with an exception. If the sql does not return results, the
callback will not be called.

> (exec/ignore db sql) :: db string -> void

A wrapper around exec that provides a meaningless callback.

> (insert db sql) :: db string -> integer

Executes the sql with the db.  The SQL is assumed to be an
insert statement, and the result is the ID of the last row
inserted.  This is useful when using AUTOINCREMENT or
INTEGER PRIMARY KEY fields as the database will choose a
unique value for this field

If the SQL is not an insertion statement it is still
executed, the results if any are discarded, and the returned
value is unspecified.

> (select db sql) :: db string -> (list-of vector)

Executes the sql with the given, collating the results in to
a list where each element is a vector of the columns values
as strings.  The first vector contains the column names.  If
the statement returns no results an empty list is returned.

> (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.  Make
sure you free the statement after use using finalize.  A
statement can be reused by calling reset.

> (load-params statement . params) :: statement list-of-values -> void

Loads the params into the statement

> (step statement) :: statement -> (U vector #f)

Steps the statement to the next result, returning the column
values as a vector, or false if the statement does not
return values or there are no more values.  Unlike select
values are converted to the appropriate Scheme type:

  null -> #f
  integer -> integer
  float -> inexact
  string or text -> string
  blob -> bytes

> (step* statement) :: statement -> (list-of vector)

Runs steps until it is done collecting the results in a
list.  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 -> void

Loads the params in the statement, then runs the
statement. (If the statement returns results, they are not
available.) (Use for UPDATE and INSERT)

> (reset statement) :: statement -> void

Resets a statement for re-execution.

> (finalize statement) :: statement -> void

Releases the resources held by a statement.

> (with-transaction* db lock-type action) :: db (U 'none 'deferred 'immediate 'exclusive) (('a -> 'a) -> 'a) -> 'a

Runs the action in a transaction in the given database with
the given lock type, returning the result of the action.
The action is passed a function of one argument which aborts
the transaction when called.  If the transaction is aborted
the result of the with-transaction* expression is the value
passed to the abort function.  If control leaves the action
via an exception or other continuation jump (i.e. without
action exiting normally) the transaction is aborted.

Note that SQLite does not currently support nested
transactions.

> (with-transaction (db fail) body ...) :: syntax

Executes the body within a transaction on the given db, with
the fail symbol bound to a procedure of one argument that
escapes from the body and fails the transaction.  The result
of this expression is the result of body, or the value
passed to fail if the transaction is aborted.

> (with-transaction/lock (db lock-type fail) body ...) :: syntax

Like with-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 rows were changed by the most
recently completed INSERT, UPDATE, or DELETE statement.

> (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 ending in *-test.ss

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.

If you encounter unexpected errors with the message "SQLite
Error: The database file is locked" check you haven't got
any un-finalized statements around.

TODO
====
From: Noel Welsh
Date: Oct 3, 2005 5:47 AM

There are a few other things worth doing:

 - rework select and exec to convert types like step does

 - write a stress test suite

 - fix my tests to make them independent.  Currently if
one test breaks all following tests will break.

 - make the tests run faster.  Particularly on OS X the
hard disk thrashes a lot.  Perhaps wrapping things in a
transaction would stop all this disk activity.

History
=======

v3.1 :: March 2nd, 2005 (Jay McCarthy
	* Added int64 support
	* Correct relative path bug

v3.0 :: October 3rd, 2005 (Noel Welsh)
        * Added comprehensive test suite and fixed bugs
        * Added insert function
        * Improved documentation
        * Improved error handling
        * Reworked transaction support

v2.0 :: August 17th, 2005
        * Adding better error handling
        * Supporting paths rather strings for db paths
        * Returning void, rather than status-codes where errors are now caught by the library
        * Adding 'reset'

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