Description

Bindings to version 3.x of the SQLite API.

Author

Thomas Chust

Usage

(require-extension sqlite3)

Download

sqlite3.egg

Requires

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:

Symbol Meaning
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 sqlite3:step! has finished executing, so no further data is ready

Classes

class: <sqlite3:database>
class: <sqlite3:statement>

These classes are derived from <c++-object>. They hold a pointer to the underlying C-structure in their this slot.

<sqlite3:statement> also has a database slot pointing to the database object it belongs to.

Managing databases

procedure: (sqlite3:open (path <string>)) => <sqlite3:database>

Opens the indicated database file and returns a <sqlite3:database> object for it.

The given path is subject to the same special expansion as paths passed to

open-input-file

and similar procedures.

method: (sqlite3:define-collation (db <sqlite3:database>) (name <string>)) => <void>
method: (sqlite3:define-collation (db <sqlite3:database>) (name <string>) (proc <procedure-class>)) => <void>

If proc 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 (a <string>) (b <string>)) => <exact>. It should return a negative number if a sorts before b, a positive number if b sorts before a and zero if a and b are equal.

As proc will be called in a callback context from within sqlite3: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.

method: (sqlite3:define-function (db <sqlite3:database>) (name <string>) (n <exact>) (proc <procedure-class>)) => <void>
method: (sqlite3:define-function (db <sqlite3:database>) (name <string>) (n <exact>) (step-proc <procedure-class>) (seed <top>) #!optional ((final-proc <procedure-class>) identity)) => <void>

If proc is given, registers a new SQL function identified by name for use in the context of database handle db. If step-proc and final-proc 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) => <top>. It is called with the n parameters given to the SQL function converted into Scheme objects like by sqlite3:column-data. The return value is converted into an SQLite3 data object like by sqlite3:bind!. A return value of (void) corresponds to NULL in SQLite3.

step-proc should have the signature (step-proc (seed <top>) . params) => <top>. 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 sqlite3: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 <top>)) => <top> and transforms the last seed value into the value to be returned from the aggregate function.

As proc, step-proc and final-proc will be called in a callback context from within sqlite3: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: (sqlite3:set-busy-timeout! (db <sqlite3:database>) #!optional ((ms <exact>) 0)) => <void>

Installs a busy handler that waits at least the specified amount of milliseconds for locks on the given database. If (<= ms 0) though, all busy handlers for the database are uninstalled.

procedure: (sqlite3:set-busy-handler! (db <sqlite3:database>) (handler <procedure-class>)) => <void>

Installs the supplied procedure as the library's busy handler, or removes it if #f. When the database is busy, SQLite will invoke this handler repeatedly until it returns #f. The handler will be called with arguments db (database) and count (number of times invoked for the same operation).

As handler will be called in a callback context from within the library, safety measures are installed to avoid throwing any exceptions or invoking continuations. Attempts to do such things will result in #f return values and warning messages.

Notably, you may not use this procedure in the presence of multiple threads unless you can ensure no other thread will invoke a C to Scheme callback while this handler is executing. In other words, callback entrance and exit must occur in order. Use set-safe-busy-handler! in this case.

Example:
(sqlite3:set-busy-handler! db
 (lambda (db count)
   (cond ((< count 5)
          (sleep 1)
          #t)
         (else #f)))))
procedure: (sqlite3:set-safe-busy-handler! (db <sqlite3:database>) (handler <procedure-class>)) => <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 db (database), count (number of times invoked for the same operation), and last (the previous value the handler returned for this operation).

As handler 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 default-safe-busy-handler.

procedure: (sqlite3:default-safe-busy-handler (ms <exact>)) => <procedure-class>

Returns a handler suitable for use with set-safe-busy-handler!. The behavior is identical to the default SQLite busy handler installed via set-busy-timeout!—it polls in increasing intervals until the timeout in milliseconds is reached—but this version is non-blocking.

Example:
(define open-db
  (let ((handler (sqlite3:default-safe-busy-handler 2000)))
    (lambda (db-name)
      (let ((db (sqlite3:open db-name)))
        (sqlite3:set-safe-busy-handler! db handler)
        db))))
procedure: (sqlite3:interrupt! (db <sqlite3:database>)) => <void>

Cancels any running database operation as soon as possible.

This function is always successful and never throws an exception.

procedure: (sqlite3:auto-committing? (db <sqlite3:database>)) => <bool>

Checks whether the database is currently in auto committing mode, i.e. no transaction is currently active.

This function always returns a state and never throws an exception.

procedure: (sqlite3:changes (db <sqlite3:database>) #!optional ((total <bool>) #f)) => <number>

Returns the number of rows changed by the last statement (if (not total)) or since the database was opened (if total).

This function always returns a count and never throws an exception.

procedure: (sqlite3:last-insert-rowid (db <sqlite3:database>)) => <number>

Returns the row ID of the last row inserted in db.

This function always returns a number and never throws an exception.

method: (sqlite3:finalize! (db <sqlite3:database>)) => <void>

Closes the given database.

Managing statements

procedure: (sqlite3:prepare (db <sqlite3:database>) (sql <string>)) => <sqlite3:statement>, <string>

Compiles the first SQL statement in sql and returns a <sqlite3:statement> and the rest of sql, which was not compiled (or an empty string).

procedure: (sqlite3:repair! (stmt <sqlite3:statement>)) => <void>

Recompiles the SQL statement used to create stmt, transfers all existing bindings from the old statement handle to the new one and destructively modifies stmt to point to the new statement handle.

If the operation is successful, the old handle is finalized, in case of error, the new handle is finalized and the old one stays untouched.

Usually you should not have to call this routine by hand. It is invoked by sqlite3:step! to automagically repair a stale statement handle after a database schema change.

procedure: (sqlite3:column-count (stmt <sqlite3:statement>)) => <exact>

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: (sqlite3:column-name (stmt <sqlite3:statement>) (i <exact>)) => <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: (sqlite3:column-declared-type (stmt <sqlite3:statement>) (i <exact>)) => <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: (sqlite3:bind-parameter-count (stmt <sqlite3:statement>)) => <exact>

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: (sqlite3:bind-parameter-index (stmt <sqlite3:statement>) (name <string>)) => <exact>

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: (sqlite3:bind-parameter-name (stmt <sqlite3:statement>) (i <exact>)) => <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.

method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>)) => <void>
method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>) (v <boolean>)) => <void>
method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>) (v <exact>)) => <void>
method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>) (v <number>)) => <void>
method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>) (v <string>)) => <void>
method: (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>) (v <blob>)) => <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
none null
<boolean> integer: #t = 1, #f = 0
<exact> integer
<number> float
<string> text
<blob> blob

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

procedure: (sqlite3:bind-parameters! (stmt <sqlite3:statement>) . params) => <void>

Binds the statement's free parameters.

Allows <void> as a parameter.

procedure: (sqlite3:step! (stmt <sqlite3:statement>)) => <boolean>

Single-steps the execution of stmt 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: (sqlite3:column-type (stmt <sqlite3:statement>) (i <exact>)) => <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: (sqlite3:column-data (stmt <sqlite3:statement>) (i <exact>)) => <void | exact | number | string | blob>

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

See sqlite3:bind! for the mapping between Scheme and SQLite data types. Columns of type null are returned as <void>. Also keep in mind that CHICKEN's <exact> datatype can only hold a subset of the values an SQLite integer can store. Large integer values may therefore be returned as floating point numbers from the database, but they will still be of class <integer>.

This procedure always succeeds and never throws an exception.

procedure: (sqlite3:reset! (stmt <sqlite3:statement>)) => <void>

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

method: (sqlite3:finalize! (stmt <sqlite3:statement>)) => <void>

Must be applied to every statement to free its resources and discard it.

sqlite3:close will not be able to close a database that has associated unfinalized statements.

Simple statement interface

procedure: (sqlite3:call-with-temporary-statements (proc <procedure-class>) (db <sqlite3:database>) . sqls) => <top>

Compiles the SQL sources in sqls into statements in the context of db, 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.

method: (sqlite3:exec (stmt <sqlite3:statement>) . params) => <void>
method: (sqlite3:exec (db <sqlite3:database>) (sql <string>) . params) => <void>

(Compiles the given SQL), resets the statement, binds the statement's free parameters and executes the statement ignoring possible results from it.

Allows <void> as a parameter.

method: (sqlite3:update (stmt <sqlite3:statement>) . params) => <exact>
method: (sqlite3:update (db <sqlite3:database>) (sql <string>) . params) => <exact>

(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 sqlite3:changes to the affected database after the execution of the statement instead.

Allows <void> as a parameter.

method: (sqlite3:first-result (stmt <sqlite3:statement>) . params) => <void | exact | number | string | blob>
method: (sqlite3:first-result (db <sqlite3:database>) (sql <string>) . params) => <void | exact | number | string | blob>

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

Allows <void> as a parameter.

method: (sqlite3:first-row (stmt <sqlite3:statement>) . params) => <list>
method: (sqlite3:first-row (db <sqlite3:database>) (sql <string>) . params) => <list>

(Compiles the given SQL), resets the statement, binds the statement's free parameters and single-steps the statement once returning all columns in the first result row as a list.

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

Allows <void> as a parameter.

method: (sqlite3:fold-row (proc <procedure-class>) (stmt <sqlite3:statement>) initial . params) => <list>
method: (sqlite3:fold-row (proc <procedure-class>) (db <sqlite3:database>) (sql <string>) initial . params) => <list>

(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 and the column values. The result of the application becomes the new folded value.

Allows <void> as a parameter.

method: (sqlite3:for-each-row (proc <procedure-class>) (stmt <sqlite3:statement>) . params) => <void>
method: (sqlite3:for-each-row (proc <procedure-class>) (db <sqlite3:database>) (sql <string>) . params) => <void>

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

Allows <void> as a parameter.

method: (sqlite3:map-row (proc <procedure-class>) (stmt <sqlite3:statement>) . params) => <list>
method: (sqlite3:map-row (proc <procedure-class>) (db <sqlite3:database>) (sql <string>) . params) => <list>

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

Allows <void> as a parameter.

Utility functions

procedure: (sqlite3:with-transaction (db <sqlite3:database>) (thunk <procedure-class>) #!optional ((type <symbol>) 'deferred)) => <void>

Runs thunk within the scope of a transaction on the database db.

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, immediate or exclusive.

procedure: (sqlite3:complete? (sql <string>)) => <boolean>

Checks whether sql comprises at least one complete SQL statement.

procedure: (sqlite3: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: (sqlite3:library-version) => <string>

Returns a string identifying the version of SQLite in use.

procedure: (sqlite3:boolean-value v) => <boolean>

Returns a Scheme boolean for the usual SQLite column boolean values.

Truth values are Y, y, YES, yes, Yes, and (not (zero? v)).

Anything else is considered false.

Version

License

Copyright (c) 2005, 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.