CL-SQLITE

 

Abstract

CL-SQLITE package is an interface to the SQLite embedded relational database engine.

The code is in public domain so you can basically do with it whatever you want.

This documentation describes only the CL-SQLITE package, not the SQLite database itself. SQLite documentation is available at http://sqlite.org/docs.html

CL-SQLITE together with this documentation can be downloaded from http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz.

CL-SQLITE source code is available in Git repository at git://repo.or.cz/cl-sqlite.git (gitweb) and at git://github.com/dmitryvk/cl-sqlite.git (gitweb).

Contents

1. Installation
2. Example
3. Usage
4. The SQLITE dictionary
   1. bind-parameter
   2. clear-statement-bindings
   3. connect
   4. disconnect
   5. execute-non-query
   6. execute-non-query/named
   7. execute-one-row-m-v
   8. execute-one-row-m-v/named
   9. execute-single
   10. execute-single/named
   11. execute-to-list
   12. execute-to-list/named
   13. finalize-statement
   14. last-insert-rowid
   15. prepare-statement
   16. reset-statement
   17. sqlite-error
   18. sqlite-constraint-error
   19. sqlite-error-code
   20. sqlite-error-db-handle
   21. sqlite-error-message
   22. sqlite-error-sql
   23. sqlite-handle
   24. sqlite-statement
   25. statement-bind-parameter-names
   26. statement-column-names
   27. statement-column-value
   28. step-statement
   29. with-transaction
   30. with-open-database
5. Support
6. Changelog
7. Acknowledgements

Installation

The package can be downloaded from http://common-lisp.net/project/cl-sqlite/releases/cl-sqlite-0.2.tar.gz. CL-SQLITE package has the following dependencies:

• CFFI
• iterate

SQLITE has a system definition for ASDF. Compile and load it in the usual way.

This package does not include SQLite library. It should be installed and loadable with regular FFI mechanisms. On Linux and Mac OS X SQLite is probably already installed (if it's not installed, use native package manager to install it). On Windows PATH environment variable should contain path to sqlite3.dll.

Example

(use-package :sqlite)
(use-package :iter)

(defvar *db* (connect ":memory:")) ;;Connect to the sqlite database. :memory: is the temporary in-memory database

(execute-non-query *db* "create table users (id integer primary key, user_name text not null, age integer null)") ;;Create the table

(execute-non-query *db* "insert into users (user_name, age) values (?, ?)" "joe" 18)
(execute-non-query/named *db* "insert into users (user_name, age) values (:user_name, :user_age)" 
                         ":user_name" "dvk" ":user_age" 22)
(execute-non-query *db* "insert into users (user_name, age) values (?, ?)" "qwe" 30)
(execute-non-query *db* "insert into users (user_name, age) values (?, ?)" nil nil) ;; ERROR: constraint failed

(execute-single *db* "select id from users where user_name = ?" "dvk")
;; => 2
(execute-one-row-m-v *db* "select id, user_name, age from users where user_name = ?" "joe")
;; => (values 1 "joe" 18)

(execute-to-list *db* "select id, user_name, age from users")
;; => ((1 "joe" 18) (2 "dvk" 22) (3 "qwe" 30))

;; Use iterate
(iter (for (id user-name age) in-sqlite-query "select id, user_name, age from users where age < ?" on-database *db* with-parameters (25))
      (collect (list id user-name age)))
;; => ((1 "joe" 18) (2 "dvk" 22))

;; Use iterate with named parameters
(iter (for (id user-name age) in-sqlite-query/named "select id, user_name, age from users where age < :age"
      on-database *db* with-parameters (":age" 25))
      (collect (list id user-name age)))
;; => ((1 "joe" 18) (2 "dvk" 22))

;; Use prepared statements directly
(loop
   with statement = (prepare-statement *db* "select id, user_name, age from users where age < ?")
   initially (bind-parameter statement 1 25)
   while (step-statement statement)
   collect (list (statement-column-value statement 0) (statement-column-value statement 1) (statement-column-value statement 2))
   finally (finalize-statement statement))
;; => ((1 "joe" 18) (2 "dvk" 22))

;; Use prepared statements with named parameters
(loop
   with statement = (prepare-statement *db* "select id, user_name, age from users where age < :age")
   initially (bind-parameter statement ":age" 25)
   while (step-statement statement)
   collect (list (statement-column-value statement 0) (statement-column-value statement 1) (statement-column-value statement 2))
   finally (finalize-statement statement))
;; => ((1 "joe" 18) (2 "dvk" 22))

(disconnect *db*) ;;Disconnect

Usage

Two functions and a macro are used to manage connections to the database:

• Function connect connects to the database
• Function disconnect disconnects from the database
• Macro with-open-database opens the database and ensures that it is properly closed after the code is run

To make queries to the database the following functions are provided:

• execute-non-query (execute-non-query/named) executes the query and returns nothing
• execute-single (execute-single/named) returns the first column of the first row of the result
• execute-one-row-m-v (execute-one-row-m-v/named) returns the first row of the result as multiple values
• execute-to-list (execute-to-list/named) returns all rows as the list of lists

Macro with-transaction is used to execute code within transaction.

Support for ITERATE is provided. Use the following clause:

(for (vars) in-sqlite-query sql on-database db &optional with-parameters (&rest parameters))

Additionally, it is possible to use the prepared statements API of sqlite. Create the prepared statement with prepare-statement, bind its parameters with bind-parameter, step through it with step-statement, retrieve the results with statement-column-value, and finally reset it to be used again with reset-statement or dispose of it with finalize-statement.

Positional and named parameters in queries are supported. Positional parameters are denoted by question mark in SQL code, and named parameters are denoted by prefixing color (:), at sign (@) or dollar sign ($) before parameter name.

Following types are supported:

• Integer. Integers are stored as 64-bit integers.
• Float. Stored as double. Single-float, double-float and rational may be passed as a parameter, and double-float will be returned.
• String. Stored as an UTF-8 string.
• Vector of bytes. Stored as a blob.
• Null. Passed as NIL to and from database.

The SQLITE dictionary

[Function]
bind-parameter statement parameter value

Sets the parameter in statement to the value.
parameter is an index (parameters are numbered from one) or the name of a parameter.
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

[Function]
clear-statement-bindings statement

Binds all parameters of the statement to NULL.

[Function]
connect database-path &key busy-timeout => sqlite-handle

Connect to the sqlite database at the given database-path (database-path is a string or a pathname). If database-path equal to ":memory:" is given, a new in-memory database is created. 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.

[Function]
disconnect handle

Disconnects the given handle from the database. All further operations on the handle and on prepared statements (including freeing handle or statements) are invalid and will lead to memory corruption.

[Function]
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.

[Function]
execute-non-query/named db sql &rest parameters

Executes the query sql to the database db with given parameters. Returns nothing. Parameters are alternating names and values.
Example:

(execute-non-query/named db "insert into users (user_name, real_name) values (:user_name, :real_name)"
                         ":user_name" "joe" ":real_name" "Joe the User")

See bind-parameter for the list of supported parameter types.

[Function]
execute-one-row-m-v db sql &rest parameters => (values result*)

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.

[Function]
execute-one-row-m-v/named db sql &rest parameters => (values result*)

Executes the query sql to the database db with given parameters. Returns the first row as multiple values. Parameters are alternating names and values.
Example:

(execute-one-row-m-v/named 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.

[Function]
execute-single db sql &rest parameters => result

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.

[Function]
execute-single/named db sql &rest parameters => result

Executes the query sql to the database db with given parameters. Returns the first column of the first row as single value. Parameters are alternating names and values.
Example:

(execute-single/named db "select user_name from users where id = :id" ":id" 1)
=>
"joe"

See bind-parameter for the list of supported parameter types.

[Function]
execute-to-list db sql &rest parameters => results

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.

[Function]
execute-to-list/named db sql &rest parameters => results

Executes the query sql to the database db with given parameters. Returns the results as list of lists. Parameters are alternating names and values.
Example:

(execute-to-list db "select id, user_name, real_name from users where user_name = :name" ":name" "joe")
=>
((1 "joe" "Joe the User")
 (2 "joe" "Another Joe")) 

See bind-parameter for the list of supported parameter types.

[Function]
finalize-statement statement

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

[Function]
last-insert-rowid db => result

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

[Function]
prepare-statement db sql => sqlite-statement

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:

(prepare-statement db "select name from users where id = ?")

[Function]
reset-statement statement

Resets the statement and prepares it to be called again. Note that bind parameter values are not cleared; use clear-statement-bindings for that.

[Condition]
sqlite-error

Error condition used by the library.

[Condition]
sqlite-constraint-error

A subclass of sqlite-error used to distinguish constraint violation errors.

[Accessor]
sqlite-error-code sqlite-error => keyword or null

Returns the SQLite error code represeting the error.

[Accessor]
sqlite-error-db-handle sqlite-error => sqlite-handle or null

Returns the SQLite database connection that caused the error.

[Accessor]
sqlite-error-message sqlite-error => string or null

Returns the SQLite error message corresponding to the error code.

[Accessor]
sqlite-error-sql sqlite-error => string or null

Returns the SQL statement source string that caused the error.

[Standard class]
sqlite-handle

Class that encapsulates the connection to the database.

[Standard class]
sqlite-statement

Class that represents the prepared statement.

[Accessor]
statement-bind-parameter-names statement => list of strings

Returns the names of the bind parameters of the prepared statement. If a parameter does not have a name, the corresponding list item is NIL.

[Accessor]
statement-column-names statement => list of strings

Returns the names of columns in the result set of the prepared statement.

[Function]
statement-column-value statement column-number => result

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

[Function]
step-statement statement => boolean

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.

[Macro]
with-transaction db &body body

Wraps the body inside the transaction. If body evaluates without error, transaction is commited. If evaluation of body is interrupted, transaction is rolled back.

[Macro]
with-open-database (db path &key busy-timeout) &body body

Executes the body with db being bound to the database handle for database located at path. Database is open before the body is run and it is ensured that database is closed after the evaluation of body finished or interrupted.
See CONNECT for meaning of busy-timeout parameter.

Support

Changelog

• 23 Jan 2009 0.1 Initial version
• 03 Mar 2009 0.1.1 Fixed bug with access to recently freed memory during statement preparation
• 22 Mar 2009 0.1.2 disconnect function now ensures that all non-finalized statements are finalized before closing the database (otherwise errors are signaled when database is being closed).
• 28 Apr 2009 0.1.3 Added support for passing all values of type REAL (including RATIONAL) as query parameter. cl-sqlite is made available as git repository.
• 10 May 2009 0.1.4 Added test suite (based on FiveAM testing framework); changed foreign library definition to work on Mac OS X (thanks to Patrick Stein) and removed the dependency on sqlite3_next_stmt function that appeared only in sqlite 3.6.0 (making cl-sqlite work with older sqlite versions)
• 13 June 2009 0.1.5 Allow passing pathnames to CONNECT function.
• 24 Oct 2009 0.1.6 Add busy-timeout argument to CONNECT. Fix library defininitions for running on Microsoft Windows.
• 14 Nov 2010 0.2 Added support for named parameters. Made statement reset and connection close more safe by clearing statements' bindings and unbinding slot of connection object. Added error condition for SQLite errors. Changes are courtesy of Alexander Gavrilov.

Acknowledgements

This documentation was prepared with DOCUMENTATION-TEMPLATE.

$Header: /usr/local/cvsrep/documentation-template/output.lisp,v 1.14 2008/05/29 08:23:37 edi Exp $