Sqlite3

Description

Author

Requirements

Documentation

The API of SQLite changed significantly from version 2.x to 3.x. These are new bindings to the modified API, which are reasonably complete -- most procedures that take callback arguments are missing, though.

For in-depth information on the functionality of the routines and general information you should consult the SQLite documentation as well as this manual.

Exceptions

Unless otherwise indicated, all procedures and methods in this egg may throw an exception of the kind (exn sqlite3) if something goes wrong. This exception will contain a status property indicating the return value of the operation that failed:

error

SQL error or missing database

internal

An internal logic error in SQLite

permission

Access permission denied

abort

Callback routine requested an abort

busy

The database file is locked

locked

A table in the database is locked

no-memory

A malloc() failed

read-only

Attempt to write a readonly database

interrupt

Operation terminated by sqlite-interrupt()

io-error

Some kind of disk I/O error occurred

corrupt

The database disk image is malformed

not-found

(Internal Only) Table or record not found

full

Insertion failed because database is full

cant-open

Unable to open the database file

protocol

Database lock protocol error

empty

(Internal Only) Database table is empty

schema

The database schema changed

too-big

Too much data for one row of a table

constraint

Abort due to contraint violation

mismatch

Data type mismatch

misuse

Library used incorrectly

no-lfs

Uses OS features not supported on host

authorization

Authorization denied

done

step! has finished executing, so no further data is ready

Abstract data types

[procedure](database? OBJECT) => BOOLEAN

Checks whether a value represents an SQLite database.

[procedure](error-database LOCATION OBJECT [ARGUMENT-NAME]) => VOID

Raises a type error saying that a database was expected instead of the given value.

[procedure](check-database LOCATION OBJECT [ARGUMENT-NAME]) => VOID

Raises a type error like error-database does, unless the given value satisfies database?

[procedure](statement? OBJECT) => BOOLEAN

Checks whether the value v represents an SQL statement.

[procedure](error-statement LOCATION OBJECT [ARGUMENT-NAME]) => VOID

Raises a type error saying that a statement was expected instead of the given value.

[procedure](check-statement LOCATION OBJECT [ARGUMENT-NAME]) => VOID

Raises a type error like error-statement does, unless the given value satisfies statement?

Managing databases

[procedure](open-database PATH) => DATABASE

Opens the indicated database file and returns a database object for it.

The given path is subject to the same special expansion as paths passed to open-input-file and similar procedures.

[procedure](define-collation DATABASE NAME [PROC]) => VOID

If a procedure is given, registers a new collation sequence identified by name for use in the context of database handle db. If no procedure is passed, the collation sequence with the given name is removed.

PROC should have the signature (PROC STRING STRING) => FIXNUM. It should return a negative number if the first argument sorts before the second, a positive number if the second sorts before the first and zero if they are equal.

As PROC will be called in a callback context from within step!, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from it. Attempts to do so will result in a 0 return value and warning messages.

Registers a new SQL function identified by NAME for use in the context of the given database handle. If STEP-PROC and SEED are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.

N is the number of parameters the new SQL function takes or -1 to allow any number of arguments.

PROC should have the signature (PROC . PARAMS) => OBJECT. It is called with the N parameters given to the SQL function converted into Scheme objects like by column-data. The return value is converted into an SQLite data object like by bind!. A return value satisfying sql-null? corresponds to NULL in SQLite.

STEP-PROC should have the signature (STEP-PROC SEED PARAMS) => SEED. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to define-function; for subsequent calls it is the last value returned by STEP-PROC and after completion of FINAL-PROC it will be the initial value again.

FINAL-PROC should have the signature (FINAL-PROC SEED) => OBJECT and transforms the last seed value into the value to be returned from the aggregate function. If it is not explicitly specified, STEP-PROC defaults to the identity function.

As PROC, STEP-PROC and FINAL-PROC will be called in a callback context from within step!, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in NULL return values and warning messages.

[procedure](set-busy-handler! DATABASE PROC) => VOID

Installs the supplied procedure as the application's busy handler, or removes it if #f. When the database returns a busy error code, the egg will invoke this handler repeatedly until it returns #f. The handler will be called with arguments DATABASE and COUNT (number of times invoked for the same operation).

As PROC is not called in a callback context, it is legal to invoke captured continuations, and it is safe in the presence of multiple threads. In general, this handler should give up at some point to avoid possible deadlock.

For an example handler, see the code of make-busy-timeout.

[procedure](make-busy-timeout MS) => PROC

Returns a handler suitable for use with set-busy-handler!. It polls in increasing intervals until the timeout in milliseconds is reached. The handler is non-blocking.

Every statement must be finalized to free its resources and discard it before the database itself can be finalized. However, if FINALIZE-STATEMENTS? is not #f, finalizing the database triggers automatic finalization of all statements first. FINALIZE-STATEMENTS? defaults to #f.

Note that both the SQLite3 egg and the SQLite3 library itself try to detect the use of already finalized statement or database handles in API calls, but the detection is not always possible and you might crash the program by using an already finalized handle.

Managing statements

[procedure](prepare DATABASE SQL) => STATEMENT, SQL

Compiles the first SQL statement in SQL and returns a statement and the tail of the SQL code, which was not compiled (or an empty string).

[procedure](source-sql STATEMENT) => STRING

Retrieves the SQL source code of a statement.

[procedure](column-count STATEMENT) => CARDINAL-INTEGER

Can be applied to any statement and returns the number of columns it will return as results.

This procedure always succeeds and never throws an exception.

[procedure](column-name STATEMENT I) => STRING

Can be applied to any statement and returns the name of the column number I (counting from 0) as a string or #f if the column has no name.

This procedure always succeeds and never throws an exception.

[procedure](column-declared-type STATEMENT I) => STRING

Can be applied to any statement and returns the declared type (as given in the CREATE statement) of the column number I (counting from 0) as a string or #f if the column has no declared type.

This procedure always succeeds and never throws an exception.

[procedure](bind-parameter-count STATEMENT) => CARDINAL-INTEGER

Can be applied to any statement and returns the number of free parameters that can be bound in the statement.

This procedure always succeeds and never throws an exception.

[procedure](bind-parameter-index STATEMENT NAME) => CARDINAL-INTEGER

Can be applied to any statement and returns the index of the bindable parameter called NAME or #f if no such parameter exists.

This procedure always succeeds and never throws an exception.

[procedure](bind-parameter-name STATEMENT I) => STRING

Can be applied to any statement and returns the name of the bindable parameter number I (counting from 0) or #f if no such parameter exists or the parameter has no name.

This procedure always succeeds and never throws an exception.

[procedure](bind! STATEMENT I OBJECT) => VOID

Can be applied to any statement to bind its free parameter number I(counting from 0) to the given value. Scheme types of the value map to SQLite types as follows:

Scheme type

SQLite type

boolean?

integer: #t = 1, #f = 0

fixnum?

integer

real?

float

string?

text

blob?

blob

sql-null?

null

Unless there is internal trouble in SQLite, this method should always succeeds and never throw an exception. For invalid parameter indices the method just silently does nothing.

[procedure](bind-parameters! STATEMENT . PARAMETERS) => VOID

Resets the statement and binds all its free parameters.

In addition to just listing the values to bind to the statement's parameters in sequence, you may specify parameters prefixed by keywords that are resolved to parameter indices by prefixing their names with ":" and resolving them using bind-parameter-index.

[procedure](step! STATEMENT) => BOOLEAN

Single-steps the execution of STATEMENT and returns #t if a result row was produced, #f if no further results are available as the statement has been stepped through. This procedure must be called at least once before any results can be retrieved from the statement.

[procedure](column-type STATEMENT I) => SYMBOL

Can be applied to a statement that has just been stepped (otherwise it returns #f) and returns the SQLite type of the result column number I (counting from 0) as a symbol.

The return value can be one of the symbols null, integer, float, text or blob.

This procedure always succeeds and never throws an exception.

[procedure](column-data STATEMENT I) => OBJECT

Can be applied to a statement that has just been stepped. Consults column-type and column-declared-type to determine the type of the indicated column and to return its data as an appropriate Scheme object:

SQLite type

Scheme type

integer, declared "bool""

boolean?

integer

integer?

float

real?

text

string?

blob

blob?

null

sql-null?

The declared type of a column is considered to be boolean if the type declaration contains the character sequence "bool" anywhere, ignoring case.

This procedure always succeeds and never throws an exception.

[procedure](reset! STATEMENT) => VOID

Can be applied to any statement and resets it such that execution using step! will perform all operations of the statement again.

Simple statement interface

Compiles the SQL sources into statements in the context of DATABASE, applies PROC to these statements and returns PROC's result. The statements are created and finalized in dynamic-wind entry and exit blocks around the application of PROC.

(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the specified statement ignoring possible results from it, returning the result of applying change-count to the affected database after the execution of the statement instead.

(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning the value of the first column in the first result row. Resets the statement again just before returning.

If the given statement does not yield any results, an (exn sqlite3) is thrown with the status-property set to done.

(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and PROC is applied to the current folded value, set to INIT in the first step, and the column values. The result of the application becomes the new folded value.

(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and PROC is applied to them. The results of this application are discarded.

(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes it step by step. After each step, the column values of the current result row are retrieved and PROC is applied to them. The results of these applications are collected into a list.

Utility functions

[procedure](with-transaction DATABASE THUNK [TYPE]) => OBJECT

Runs THUNK within the scope of a transaction on the database and returns the return value from THUNK.

The transaction is committed upon exit from THUNK if THUNK returns a true value. If THUNK returns a false value or throws an exception, the transaction is rolled back.

The TYPE" of the transaction can be specified as one of the symbols deferred (the default), immediate or exclusive.

[procedure](sql-complete? SQL) => BOOLEAN

Checks whether SQL comprises at least one complete SQL statement.

[procedure](enable-shared-cache! BOOLEAN) => VOID

Enables (or disables) the sharing of the database cache and schema data structures between connections to the same database.

[procedure](database-version) => STRING

Returns a string identifying the version of SQLite in use.

[procedure](database-memory-used) => CARDINAL-INTEGER

Returns the amount of memory currently in use by the database engine.

[procedure](database-memory-highwater [RESET?]) => CARDINAL-INTEGER

Returns the maximum amount of memory that was in use by the database engine since the counter was last reset or since the program started. Resets the counter if RESET? is not #f. RESET? defaults to #f.

License

Copyright (c) 2005-2009, Thomas Chust <chust@web.de>. All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
Redistributions of source code must retain the above copyright notice,
this list of conditions and the following disclaimer. Redistributions in
binary form must reproduce the above copyright notice, this list of
conditions and the following disclaimer in the documentation and/or
other materials provided with the distribution. Neither the name of the
author nor the names of its contributors may be used to endorse or
promote products derived from this software without specific prior
written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.