SQLite: An interface to SQLite databases

Jay McCarthy <jay at plt-scheme dot org>

SQLite gives you access to SQLite database from PLT Scheme.

1 API

(require (planet jaymccarthy/sqlite))

1.1 Structs and Contracts

(exn:sqlite? x) → boolean?

x : any/c

Identifiers if x represents an SQLite exception.

(db? x) → boolean?

x : any/c

Identifies if x represents an SQLite database.

(statement? x) → boolean?

x : any/c

Identifies if x represents an SQLite statement.

sqlite-datum/c : contract?

Equivalent to (or/c integer? number? string? bytes? false/c).

1.2 Database Operations

(open db-path) → db?

db-path : (or/c path-string? (symbols ':memory: ':temp:))

Opens the SQLite database at db-path.

If ':memory: or ':temp: are passed they correspond to the string arguments ":memory:" and "" to SQLite’s open function. These correspond to a private, temporary in-memory database and a private, temporary on-disk database.

(close db) → void

db : db?

Closes the database referred to by db.

(errmsg db) → string?

db : db?

Returns the message for the last error with the database.

(last-insert-rowid db) → integer?

db : db?

Returns the identifier of the last inserted row.

(changes-count db) → integer?

db : db?

Returns a count of how many rows were changed by the most recently completed INSERT, UPDATE, or DELETE statement.

(total-changes-count db) → integer?

db : db?

Returns a count of how many changes have been made to the database since its creation.

1.3 Statement Operations

(prepare db sql) → statement?

db : db?

sql : string?

Compiles sql into a statement object for the given db. The query may contain “?” to mark a parameter. Make sure you free the statement after use with finalize. A statement can be reused by calling reset.

(load-params stmt param ...) → void

stmt : statement?

param : sqlite-datum/c

Loads params into stmt, filling in the `?’s.

(step stmt) → (or/c (vectorof sqlite-datum/c) false/c)

stmt : statement?

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

A NULL becomes #f. An INTEGER becomes an integer. A FLOAT becomes an inexact number. A STRING or TEXT becomes a string. A BLOB becomes a bytes.

(step* stmt) → (listof (vectorof sqlite-datum/c))

stmt : statement?

Runs step 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 stmt param ...) → void

stmt : statement?

param : sqlite-datum/c

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 stmt) → void

stmt : statement?

Resets a statement for re-execution.

(finalize stmt) → void

stmt : statement?

Releases the resources held by a statement.

1.4 High-level SQL Operations

(exec db sql callback param ...) → void

db : db?

sql : string?

callback : ((vectorof string?) (vectorof sqlite-datum/c) . -> . integer?)

param : sqlite-datum/c

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

(exec/ignore db sql param ...) → void

db : db?

sql : string?

param : sqlite-datum/c

A wrapper around exec that provides a void callback.

(insert db sql param ...) → integer?

db : db?

sql : string?

param : sqlite-datum/c

Executes sql, after loading the params, with the db. The query 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 param ...) → (listof (vectorof sqlite-datum/c))

db : db?

sql : string?

param : sqlite-datum/c

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

1.5 Transaction Operations

(with-transaction* db lock-type action) → any/c

db : db?

lock-type : (symbols 'none 'deferred 'immediate 'exclusive)

action : ((-> void) . -> . any/c)

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

Refer to the SQLite documentation for the meaning of the lock-types.

(with-transaction (db fail) body ...)

Equivalent to: (with-transaction* db 'none (lambda (fail) body ...)).

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

Equivalent to: (with-transaction* db lock-type (lambda (fail) body ...)).

2 Notes

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.

Noel Welsh wrote the first tests.