Common Lisp Package: SQLITE

README:

FUNCTION

Public

BIND-PARAMETER (STATEMENT PARAMETER VALUE)

Sets the PARAMETER-th parameter in STATEMENT to the VALUE. PARAMETER may be parameter index (starting from 1) or parameters name. Supported types: * NULL. Passed as NULL * INTEGER. Passed as an 64-bit integer * STRING. Passed as a string * FLOAT. Passed as a double * (VECTOR (UNSIGNED-BYTE 8)) and VECTOR that contains integers in range [0,256). Passed as a BLOB

CLEAR-STATEMENT-BINDINGS (STATEMENT)

Sets all binding values to NULL.

CONNECT (DATABASE-PATH &KEY BUSY-TIMEOUT)

Connect to the sqlite database at the given DATABASE-PATH. Returns the SQLITE-HANDLE connected to the database. Use DISCONNECT to disconnect. Operations will wait for locked databases for up to BUSY-TIMEOUT milliseconds; if BUSY-TIMEOUT is NIL, then operations on locked databases will fail immediately.

DISCONNECT (HANDLE)

Disconnects the given HANDLE from the database. All further operations on the handle are invalid.

EXECUTE-NON-QUERY (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns nothing. Example: (execute-non-query db "insert into users (user_name, real_name) values (?, ?)" "joe" "Joe the User") See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-NON-QUERY/NAMED (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns nothing. PARAMETERS is a list of alternating parameter names and values. Example: (execute-non-query db "insert into users (user_name, real_name) values (:name, :real_name)" ":name" "joe" ":real_name" "Joe the User") See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-ONE-ROW-M-V (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the first row as multiple values. Example: (execute-one-row-m-v db "select id, user_name, real_name from users where id = ?" 1) => (values 1 "joe" "Joe the User") See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-ONE-ROW-M-V/NAMED (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the first row as multiple values. PARAMETERS is a list of alternating parameters names and values. Example: (execute-one-row-m-v db "select id, user_name, real_name from users where id = :id" ":id" 1) => (values 1 "joe" "Joe the User") See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-SINGLE (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the first column of the first row as single value. Example: (execute-single db "select user_name from users where id = ?" 1) => "joe" See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-SINGLE/NAMED (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the first column of the first row as single value. PARAMETERS is a list of alternating parameters names and values. Example: (execute-single db "select user_name from users where id = :id" ":id" 1) => "joe" See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-TO-LIST (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the results as list of lists. Example: (execute-to-list db "select id, user_name, real_name from users where user_name = ?" "joe") => ((1 "joe" "Joe the User") (2 "joe" "Another Joe")) See BIND-PARAMETER for the list of supported parameter types.

EXECUTE-TO-LIST/NAMED (DB SQL &REST PARAMETERS)

Executes the query SQL to the database DB with given PARAMETERS. Returns the results as list of lists. PARAMETERS is a list of alternating parameters names and values. Example: (execute-to-list db "select id, user_name, real_name from users where user_name = :user_name" ":user_name" "joe") => ((1 "joe" "Joe the User") (2 "joe" "Another Joe")) See BIND-PARAMETER for the list of supported parameter types.

FINALIZE-STATEMENT (STATEMENT)

Finalizes the statement and signals that associated resources may be released. Note: does not immediately release resources because statements are cached.

LAST-INSERT-ROWID (DB)

Returns the auto-generated ID of the last inserted row on the database connection DB.

PREPARE-STATEMENT (DB SQL)

Prepare the statement to the DB that will execute the commands that are in SQL. Returns the SQLITE-STATEMENT. SQL must contain exactly one statement. SQL may have some positional (not named) parameters specified with question marks. Example: select name from users where id = ?

RESET-STATEMENT (STATEMENT)

Resets the STATEMENT and prepare it to be called again.

SET-BUSY-TIMEOUT (DB MILLISECONDS)

Sets the maximum amount of time to wait for a locked database.

STATEMENT-COLUMN-VALUE (STATEMENT COLUMN-NUMBER)

Returns the COLUMN-NUMBER-th column's value of the current row of the STATEMENT. Columns are numbered from zero. Returns: * NIL for NULL * INTEGER for integers * DOUBLE-FLOAT for floats * STRING for text * (SIMPLE-ARRAY (UNSIGNED-BYTE 8)) for BLOBs

STEP-STATEMENT (STATEMENT)

Steps to the next row of the resultset of STATEMENT. Returns T is successfully advanced to the next row and NIL if there are no more rows.

Undocumented

SQLITE-ERROR (ERROR-CODE MESSAGE &KEY STATEMENT (DB-HANDLE (IF STATEMENT (DB STATEMENT))) (SQL-TEXT (IF STATEMENT (SQL STATEMENT))))

Private

Undocumented

REALLY-FINALIZE-STATEMENT (STATEMENT)

STATEMENT-PARAMETER-INDEX (STATEMENT PARAMETER-NAME)

MACRO

Public

WITH-TRANSACTION (DB &BODY BODY)

Wraps the BODY inside the transaction.

Undocumented

WITH-OPEN-DATABASE ((DB PATH &KEY BUSY-TIMEOUT) &BODY BODY)

Private

Undocumented

CLAUSE-FOR-IN-SQLITE-QUERY-ON-DATABASE-1 (&KEY ((FOR VARS) NIL) ((IN-SQLITE-QUERY QUERY-EXPRESSION) NIL) ((ON-DATABASE DB) NIL) ((WITH-PARAMETERS PARAMETERS) NIL) GENERATE)

CLAUSE-FOR-IN-SQLITE-QUERY/NAMED-ON-DATABASE-2 (&KEY ((FOR VARS) NIL) ((IN-SQLITE-QUERY/NAMED QUERY-EXPRESSION) NIL) ((ON-DATABASE DB) NIL) ((WITH-PARAMETERS PARAMETERS) NIL) GENERATE)

CLAUSE-FOR-ON-SQLITE-STATEMENT-3 (&KEY ((FOR VARS) NIL) ((ON-SQLITE-STATEMENT STATEMENT) NIL) GENERATE)

WITH-PREPARED-STATEMENT (STATEMENT-VAR (DB SQL PARAMETERS-VAR) &BODY BODY)

WITH-PREPARED-STATEMENT/NAMED (STATEMENT-VAR (DB SQL PARAMETERS-VAR) &BODY BODY)

GENERIC-FUNCTION

Public

Undocumented

SQLITE-ERROR-CODE (CONDITION)

SQLITE-ERROR-DB-HANDLE (CONDITION)

SQLITE-ERROR-MESSAGE (CONDITION)

SQLITE-ERROR-SQL (CONDITION)

Private

Undocumented

SQLITE-ERROR-STATEMENT (CONDITION)

SLOT-ACCESSOR

Public

Undocumented

STATEMENT-BIND-PARAMETER-NAMES (OBJECT)

STATEMENT-COLUMN-NAMES (OBJECT)

Private

Undocumented

CACHE (OBJECT)

SETFCACHE (NEW-VALUE OBJECT)

DATABASE-PATH (OBJECT)

SETFDATABASE-PATH (NEW-VALUE OBJECT)

DB (OBJECT)

HANDLE (OBJECT)

SETFHANDLE (NEW-VALUE OBJECT)

PARAMETERS-COUNT (OBJECT)

SETFPARAMETERS-COUNT (NEW-VALUE OBJECT)

PARAMETERS-NAMES (OBJECT)

SETFPARAMETERS-NAMES (NEW-VALUE OBJECT)

RESULTSET-COLUMNS-COUNT (OBJECT)

SETFRESULTSET-COLUMNS-COUNT (NEW-VALUE OBJECT)

RESULTSET-COLUMNS-NAMES (OBJECT)

SETFRESULTSET-COLUMNS-NAMES (NEW-VALUE OBJECT)

SQL (OBJECT)

SQLITE-HANDLE-STATEMENTS (OBJECT)

SETFSQLITE-HANDLE-STATEMENTS (NEW-VALUE OBJECT)

CLASS

Public

SQLITE-HANDLE

Class that encapsulates the connection to the database. Use connect and disconnect.

SQLITE-STATEMENT

Class that represents the prepared statement.

CONDITION

Public

Undocumented

SQLITE-CONSTRAINT-ERROR

SQLITE-ERROR (ERROR-CODE MESSAGE &KEY STATEMENT (DB-HANDLE (IF STATEMENT (DB STATEMENT))) (SQL-TEXT (IF STATEMENT (SQL STATEMENT))))