You are looking at historical revision 21448 of this page. It may differ significantly from its current revision.

sql-de-lite

sql-de-lite is an experimental interface to SQLite 3 for Chicken 4. The API is subject to change.

sql-de-lite has the following advantages and disadvantages over the sqlite3 extension:

Installation

Starting with sql-de-lite 0.4.0, the SQLite 3 library is included with the egg. It will be automatically built if the system library is missing or too old; otherwise, the system library is used. You can also override the egg's choice of internal or external library.

In most cases, the following will do the right thing:

chicken-install sql-de-lite

The goal of this automatic detection is ensure that you can just depend on this extension without requiring a separate library install step from your users.

Built-in library

SQLite 3.7.0.1 is included in the egg and will be linked in statically if the system library is missing or older than 3.7.0.1. The built-in library is compiled with FTS3 (full text search) enabled.

Additionally, a copy of the SQLite3 command shell is installed with your Chicken binaries as chicken-sqlite3.

To force the use of the built-in library, pass the sql-de-lite-internal-lib feature to chicken-install. This requires Chicken 4.5.1 or later.

chicken-install -D sql-de-lite-internal-lib sql-de-lite

External library

A SQLite library installed in the default system library location will be used instead of the built-in library, if present and recent enough.

To force the use of an external library, pass the sql-de-lite-external-lib feature to chicken-install. This requires Chicken 4.5.1 or later.

chicken-install -D sql-de-lite-external-lib sql-de-lite

If you force an external library to be used, the build will fail unless the library is present and recent enough not to cause link errors. The extension assumes you know what you doing, and does not enforce a minimum library version. It is probably necessary to use 3.6.x or later, and the latest stable is always recommended.

Non-standard library location

You can point the build process at a library in a non-standard location--for example, under your home directory--by setting CSC_OPTIONS appropriately.

export CSC_OPTIONS="-I$HOME/local/include -L$HOME/local/lib"
chicken-install sql-de-lite

Your own built-in library, static

If you want to use your own built-in library, retrieve the egg source, download the SQLite amalgamation zip file or tarball, then extract it into sqlite3/ inside the egg directory.

chicken-install -r sql-de-lite
cd sql-de-lite
curl -O http://www.sqlite.org/sqlite-amalgamation-3_7_0_1.zip
unzip sqlite-amalgamation-3_7_0_1.zip -d sqlite3
chicken-install          # build and install the egg

All we really need is shell.c, sqlite3.c, and sqlite3.h from the amalgamation. Autoconf is not used.

Currently, there is no way to override the compilation options to SQLite3 from chicken-install. If you need to do so, edit the sql-de-lite.setup file.

Low-level interface

The low-level interface maps closely to the underlying library API. It may be used in tandem with or instead of the high-level API as desired.

Opening the database

[procedure] (open-database filename)

Opens filename, a sqlite3 database. If no database exists, one is created transparently. filename may also be one of the following symbols:

Returns a #<sqlite-database> object.

Closing the database

[procedure] (close-database db)

Closes the database connection db.

The prepared statement cache is flushed and any transient statements are finalized before closing.

Preparing a SQL statement

[procedure] (prepare db sql)

Looks up a prepared statement in the statement cache. If not found, it prepares a new statement and caches it, returning the statement. If found, the statement is reset if need be, and returned. An exception is thrown if a statement we pulled from cache is currently running --- in other words, has been stepped at least once and has not yet returned SQLITE_DONE. (NB: warn and reset would also be acceptable behavior.)

Returns a #<sqlite-statement> object.

[procedure] (prepare-transient db sql)

Same as prepare, but bypasses the cache completely. This procedure is subject to removal.

[parameter] (prepared-cache-size n) [default: 100]

Sets the capacity of the prepared statement cache, in statements. Set to 0 to disable the cache; this is not recommended.

When the cache reaches capacity and a new statement is prepared, the least recently used statement is finalized and drops off the cache.

This setting takes effect only upon initiating a new connection, and the statement cache is unique per connection.

Stepping the statement

[procedure] (step statement)

Steps statement and returns one of the following:

#f is only ever returned if raising exceptions is disabled. Completion of execution is still considered a "success" and so the true value 'done is returned, rather than #f.

Upon database error, the statement is reset.

Resetting a statement

[procedure] (reset statement)

Resets statement to the beginning of its program, returning the statement.

Finalizing a statement

[procedure] (finalize statement)

Finalize statement. Finalizing a finalized statement or a cached statement is a no-op. Finalizing a statement on a closed database is also a no-op. Cached statements are finalized as they expire, and all other statements known to the SQLite library are automatically finalized when the database is closed, so it is rarely necessary to call this directly.

[procedure] (resurrect statement)

Resurrects a previously finalized statement s or, if still alive, just resets it. Returns s, which is also modified in place.

Resurrection is accomplished simply by re-preparing the original SQL associated with the statement. Afterwards, the statement may be used normally.

Binding parameters

[procedure] (bind statement index value)

Bind parameter at index of statement to value, and returns statement. The variable index may be an integer (the first parameter is 1, not 0) or a string for a named parameter --- for example, "$key", ":key" or "@key". For named parameters, the $, : or @ must be included in the string. A reference to an invalid index will throw an exception.

[procedure] (bind-parameters statement . parameters)

Convenience function which binds parameters to indices 1 .. n, in order. The number of parameters must match the statement's bind-parameter-count, or an error will be signaled.

[procedure] (bind-parameter-count statement)

Returns the number of bound parameter slots in this prepared statement. Technically, this returns the highest-numbered parameter, so the count may not really be a "count" if you bound a explicitly numbered parameter "?nnn". This extension does not play well with explicitly numbered parameters, anyway.

Obtaining result data

In this section's examples, we assume a simple database has been created with the following commands:

CREATE TABLE cache(key text, val text);
INSERT INTO cache(key,val) VALUES('foo', 'bar'); 
INSERT INTO cache(key,val) VALUES('baz', 'quux');

and a SELECT statement has been prepared and stepped once:

(define s (prepare db "SELECT rowid, * from cache;"))
(step s)  ; => row

and s is implicitly reset between examples.

[procedure] (column-name statement index)

Return the name of the specified result set column as a symbol. The statement need not have been stepped to retrieve column names or column count.

(column-name s 1)
; => key
[procedure] (column-names statement)

Convenience function which returns a list of all column names for the result set, in order.

(column-names s)
; => (rowid key val)
[procedure] (column-count statement)

Return the number of columns in the result set returned by the prepared statement.

(column-count s)
; => 3
[procedure] (column-type statement index)

Returns the type of the indexed column in the current row. SQLite is dynamically typed and the column types are unique to each row.

Symbol Database type
integer SQLITE_INTEGER
float SQLITE_FLOAT
text SQLITE_TEXT
blob SQLITE_BLOB
null SQLITE_NULL
(map (lambda (i) (column-type s i))
     (list 0 1 2))
; => (integer text text)
[procedure] (column-data statement index)

Returns the data from the indexed column in the current row.

Column type Scheme type
integer Exact or inexact number
float Inexact number
text String
blob Blob
null '()
(map (lambda (i) (column-data s i))
     (list 0 1 2))
; => (1 "foo" "bar")

integer values are retrieved with sqlite3_column_int64. On a 32-bit machine, values outside the signed 31-bit fixnum range are returned as inexact numbers. On a 64-bit machine, values outside the signed 63-bit fixnum range are returned as inexact numbers. Note that inexact numbers are 64-bit floating point values, and can only accurately represent 53 bits of an integer.

[procedure] (row-data statement)

Retrieve a list of column data from the current row. If the last execution of step returned done, a NULL value will be returned for every column.

(row-data s)
; => (1 "foo" "bar")
[procedure] (row-alist statement)

Retrieve an alist mapping column names to column data for the current row.

(row-alist s)
; => ((rowid . 1) (key . "foo") (val . "bar"))
[procedure] (change-count db)

Returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement, not including triggers, as in sqlite3_changes.

[procedure] (total-change-count db)

Returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the database connection was opened, including triggers, as in sqlite3_total_changes.

[procedure] (last-insert-rowid db)

Get the ROWID of the last successful INSERT, as in sqlite3_last_insert_rowid.

High-level interface

call-with-database

[procedure] (call-with-database filename proc)

Opens a database, calls proc with the database object and then closes the database on return. If an error occurs in proc, the database is closed immediately.

Statements

[procedure] (sql db sql-str)

Creates a statement object associated with the database connection db and the SQL sql-str. Preparation of the statement is deferred until needed. This is a normal statement in every respect except that it must be resurrected before it can be used.

Query

[procedure] (query proc statement . args)

Resurrects s, binds args to s and performs a query*. This is the usual way to perform a query unless you need to bind arguments manually, need more control or are using the low-level interface, in which case you can use query* if desired.

You typically call query or query* with one of the provided result fetching procedures; naturally, you can also pass your own procedure to perform whichever operations you would like.

[procedure] (query* proc statement)

Calls (proc s) and resets the statement immediately afterward, to avoid locking the database. If an exception occurs during proc, the statement will still be reset. The statement is not reset before execution.

The entire purpose of query* is to ensure a statement is reset after it is executed. If a statement were left in a running state --- for example, if an uncaught exception occurs during proc, or you simply do not exhaust its the result set --- then the database will be locked for writing until the statement is finalized.

Query procedures
[procedure] (fetch s)

Fetch the next row of the result set. This is the equivalent to performing a step followed by a row-data call, and works with both the high- and low-level interfaces. If the statement has finished executing, fetch returns '(). These query procedures do not reset the statement before or afterward; one may do so using reset or query.

(fetch s)
; => (1 "foo" "bar")
(fetch s)
; => (2 "baz" "quux")
(fetch s)
; => ()
(fetch s)
; => error
(query fetch s)
; => (1 "foo" "bar")
(query fetch s)
; => (1 "foo" "bar")
(fetch s)
; => (1 "foo" "bar")
[procedure] (fetch-alist s)

Equivalent to step followed by row-alist.

(query fetch-alist s)
; ((rowid . 1) (key . "foo") (val . "bar"))
[procedure] (fetch-all s)

Calls fetch until it returns '(), and collects the result into a list.

(query fetch-all s)
; => ((1 "foo" "bar")
      (2 "baz" "quux"))
[procedure] (for-each-row proc)
[procedure] (for-each-row* proc)

Returns a procedure suitable for passing to query, taking one argument, a statement object.

The procedure will call fetch once for each row and call your callback as (proc row), discarding the results.

(query (for-each-row
         (lambda (x) (print "row: " x)))
       s)
; row: (1 foo bar)
; row: (2 baz quux)
; => undefined

for-each-row* behaves like for-each-row, but your callback is invoked with one argument for each column value. For example, these produce equivalent results:

(query (for-each-row (match-lambda ((name sql)
                        (print "table: " name " sql: " sql ";"))))
       (sql db "select name, sql from sqlite_master;"))  
(query (for-each-row* (lambda (name sql)
                        (print "table: " name " sql: " sql ";")))
       (sql db "select name, sql from sqlite_master;"))
[procedure] (map-rows proc)
[procedure] (map-rows* proc)

Return a procedure suitable for passing to query, taking one argument, a statement object.

The procedure will call fetch once for each row and call (proc row), collecting the results into a list, in order.

(query (map-rows car) s)
; => (1 2)

Another example; these two produce equivalent results:

(query (map-rows car) (sql db "select name, sql from sqlite_master;"))
(map car (query fetch-all (sql db "select name, sql from sqlite_master;")))

map-rows* behaves like map-rows, but your callback is invoked with one argument for each column value.

[procedure] (fold-rows kons knil)
[procedure] (fold-rows* kons knil)

Calls (kons x xs) once for each row, where x is the current row data and xs is the seed (previous return value from kons). The initial seed is knil.

(query (fold-rows cons '()) s)
; => ((2 "baz" "quux") (1 "foo" "bar"))
;; sum the returned rowids 
(query (fold-rows (lambda (x xs) (+ (car x) xs))
                  0)
       s)
; => 3
;; that was contrived, you should actually do the sum in the database
(car (query fetch (sql db "select sum(rowid) from mytable;")))
; => 3

fold-rows* behaves like fold-rows, but the kons callback is invoked with one column for each argument value, plus the seed as the last argument -- for example, as (kons x y z seed). This turns out to be quite inefficient and makes little sense, so fold-rows* is deprecated as of 0.4.2.

[procedure] (first-column row)

Returns the first column of row, or #f if the row is '().

(first-column (query fetch (sql db "select sum(rowid) from mytable;")))
; => 3

Execute

[procedure] (exec s . args)

Resurrects statement s, binds args to s and performs an exec*.

[procedure] (exec* s)

Executes statement sql, returning the number of changes (if the result set has no columns as in INSERT, DELETE, UPDATE) or the first row (if column data is returned as in SELECT). In the latter case, it is like performing a (query* fetch s), but is more efficient.

Resurrection is omitted, as it would wipe out any bindings. Reset is NOT done beforehand; it is cheap, but the user must reset before a bind anyway.

Note: Reset afterward is not guaranteed; it is done only if a row was returned and fetch did not throw an error, to avoid locking the database. An error in step should not leave the statement open, but an error in retrieving column data will (such as a string > 16MB) -- this is a flaw in the current implementation.

(exec (sql db "INSERT INTO cache(key, val) values(?, ?);")
      "chicken" 4)
; => 1
(exec (sql db "SELECT * FROM cache WHERE key = ?;")
      "chicken")
; => ("chicken" "4")
(first-column (exec (sql db "SELECT val FROM cache;")))
; => "bar"
(first-column (exec (sql db "SELECT val FROM cache;")))
; => "bar"

Transactions

with-transaction

[procedure] (with-transaction db thunk #!optional (type deferred))
[procedure] (with-deferred-transaction db thunk)
[procedure] (with-immediate-transaction db thunk)
[procedure] (with-exclusive-transaction db thunk)

Executes thunk within a BEGIN TRANSACTION block, and returns the value of thunk. The optional type may be one of the symbols deferred, immediate, or exclusive. You may also use the named convenience functions instead of the optional parameter.

The transaction is committed with (commit db) if thunk returns a true value. Escaping or re-entering the dynamic extent of thunk will not commit or rollback the in-progress transaction. However, if an exception occurs during thunk, or thunk returns #f, or the commit fails, the transaction will be rolled back with (rollback db). If this rollback fails, that is a critical error and you should likely abort.

rollback

[procedure] (rollback db)

Rollback current transaction. Unconditionally resets running queries before doing so, as rollback would fail if read or read/write queries are running. Successful rollback returns a true value. Rolling back in autocommit mode also returns a true value.

commit

[procedure] (commit db)

Commit current transaction. This does not rollback running queries, because running read queries are acceptable, and the behavior in the presence of pending write statements is unclear. If the commit fails, you can always rollback, which will reset the pending queries.

Successful commit, or commit in autocommit mode, returns a true value.

autocommit?

[procedure] (autocommit? db)

Returns #t if the database is in autocommit mode, or #f if within a transaction.

Error handling

Currently, a generic exception is raised when a database error occurs; information about the specific error is not available within the exception. However, this can still be obtained using the error-code and error-message interface.

[procedure] (error-code db)

Returns the last database error code as a symbol.

[procedure] (error-message db)

Returns the last database error message as a string.

[parameter] (raise-database-errors BOOLEAN) [default: #t]

Set to #t to raise an exception on database error, #f to return a false value. Note that certain critical errors, such as "misuse of interface" and arity mismatches of bound parameters will raise exceptions regardless. Procedures in this extension that utilize the low-level interface are written to work correctly with both #f return values and errors.

Disabling raising of database errors is intended for experts and this option may be removed.

Busy handling

Busy handling is done outside of the library, instead of inside the library busy handler, because with SRFI-18 threads it is not legal to yield within a callback. The backoff algorithm of sqlite3_busy_timeout is reimplemented.

SQLite can deadlock in certain situations and to avoid this will return SQLITE_BUSY immediately rather than invoking the busy handler. However if there is no busy handler, we cannot tell a retryable SQLITE_BUSY from a deadlock one. To gain deadlock protection we register a simple busy handler which sets a flag indicating this BUSY is retryable. This is done without invoking a callback into Scheme.

set-busy-handler!

[procedure] (set-busy-handler! db proc)

Register the busy handler proc on the open connection db; the handler will be called repeatedly when a prepare or step operation returns SQLITE_BUSY. It is passed the two arguments (db count), which are the associated database connection and the number of times this busy handler has been invoked so far for this operation. The procedure should return #f to stop retrying and have the operation return a BUSY error to the caller, or #t if the busy operation should be retried.

By default, no busy handler is registered. Busy handlers are unique to each connection and must be registered after the connection is open.

(call-with-database 
 (lambda (db)
  (set-busy-handler! db (busy-timeout 10000)) ; 10 second timeout
  ...))

busy-timeout

[procedure] (busy-timeout ms)

Return a procedure suitable for use in set-busy-handler!, implementing a spinning busy timeout using the SQLite3 busy wait algorithm. This handler will wait up to ms milliseconds total before giving up. Other threads may be scheduled while this one is busy-waiting.

Miscellaneous

[procedure] (schema db)

Returns a list of SQL statements making up the database schema.

[procedure] (print-schema db)

Displays the database schema to the current output port; the result is similar to using .schema at the sqlite3 command prompt.

[procedure] (flush-cache! db)

Flush the prepared statement cache,

[procedure] (finalized? statement)

Returns #t if the statement is finalized or has never been prepared.

(finalized? (sql db "select 1;"))     ; => #t
(finalized? (prepare db "select 1;")) ; => #f
[string] library-version

A string representing the SQLite3 library version (e.g. "3.6.11").

Notes

SRFI-18 threads

If you are operating on the same database in multiple threads, you must open a new connection per thread. Reusing the same connection will result in corruption.

Bugs

Assuredly many, among which is the lack of a good high-level interface to named parameters.

About this egg

Version history

0.4.2
Upgrade to SQLite 3.7.3; fix fold-rows* (and deprecate it)
0.4.1
Drop dependency on easyffi
0.4.0
Add integrated SQLite3 library
0.3.0
Initial release

Author

Jim Ursetto

License

The egg is BSD-licensed. The SQLite 3 library is public domain.