(lispkit sqlite)

SQLite is a lightweight, embedded, relational open-source database management system. It is simple to use, requires zero configuration, is not based on a server, and manages databases directly in files.

Library (lispkit sqlite) provides functionality for creating, managing, and querying SQLite databases in LispKit. (lispkit sqlite) is a low-level library that wraps the classial C API for SQLite3. Just like in the C API, the actual SQL statements are represented as strings and compiled into statement objects that are used for executing the statements.

Introduction

Library (lispkit sqlite) exports procedure open-database for creating new databases and connecting to existing ones. The following code will create a new database from scratch in file ~/Desktop/TestDatabase.sqlite if that file does not exist. If the file exists, open-database will return a database object for accessing the database:

(import (lispkit sqlite))
(define db (open-database "~/Desktop/TestDatabase.sqlite"))

A new table can be created in database db with the help of an SQL CREATE TABLE statement. SQL statements are defined as strings and compiled into statement objects via procedure prepare-statement. Procedure process-statement is used to execute statement objects.

(define stmt0
  (prepare-statement db
    (string-append
      "CREATE TABLE Contacts (id INTEGER PRIMARY KEY,"
      "                       name TEXT NOT NULL,"
      "                       email TEXT NOT NULL UNIQUE,"
      "                       phone TEXT);")))
(process-statement stmt0)

Entries can be inserted into the new table Contacts with a corresponding SQL statement as shown in the following listing. First, a new SQL statement is being compiled. This SQL statement contains parameters. These are placeholders that are defined via ?. They can be bound to concrete values before the statement is executed using procedures bind-parameter and bind-parameters.

The SQL statement below has 4 parameters, indexed starting 1. The code below binds these parameters one by one via bind-parameter to concrete values before the statement is executed via process-statement.

(define stmt1 (prepare-statement db "INSERT INTO Contacts VALUES (?, ?, ?, ?);"))
(bind-parameter stmt1 1 1000)
(bind-parameter stmt1 2 "Mickey Mouse")
(bind-parameter stmt1 3 "[e-mail]")
(bind-parameter stmt1 4 "[phone]")
(process-statement stmt1)

SQL statements can be reused many times. Typically, this is done by utilizing procedure reset-statement. If the previous execution was successful, though, this is not strictly necessary and a reset is done automatically. The code below re-applies the same statement a second time, this time using procedure bind-parameters to bind all parameters in one go.

(reset-statement stmt1) ; not strictly needed here
(bind-parameters stmt1 '(1001 "Donald Duck" "[e-mail]" "[phone]"))
(process-statement stmt1)

The following code shows how to query for the total number of distinct phone numbers in table Contacts. The first invokation of procedure process-statement returns #f, indicating that there is a result. column-count returns 1, which is the column containing the distinct count. The count is extracted from the statement via column-value. The second invokation of process-statement now returns #t as there are no further query results.

; Count the number of distinct phone numbers.
(define stmt2 (prepare-statement db "SELECT COUNT(DISTINCT phone) FROM Contacts;"))
(process-statement stmt2) ; returns `#f`, i.e. there is a result
(display (column-count stmt2))
(newline)
(display (column-value stmt2 0))
(newline)
(process-statement stmt2) ; returns `#t`, i.e. there is no further result

The final example code below shows how to iterate effectively over a result table that has more than one result row.

; Show all names and email addresses from the `Contacts` table.
(define stmt3 (prepare-statement db "SELECT name, email FROM Contacts;"))
(do ((res '() (cons (row-values stmt3) res)))
    ((process-statement stmt3) res))

Executing this code returns the following list:

(("Donald Duck" "[e-mail]") ("Mickey Mouse" "[e-mail]"))

API

SQLite version retrieval

(sqlite-version)

The sqlite-version procedure returns a string that specifies the version of the SQLite framework in use in the format "X.Y.Z", where X is the major version number (e.g. 3 for SQLite3), Y is the minor version number, and Z is a release number.

(sqlite-version-number)

The sqlite-version-number procedure returns a fixnum with the value X1000000 + Y1000 + Z where X is the major version number (e.g. 3 for SQLite3), Y is the minor version number, and Z is a release number.

Database options

The following fixnum constants are used to specify how databases are opened or created via make-database and open-database. They can be combined by using an inclusive or function such as fxior. For instance, (fxior sqlite-readwrite sqlite-create) combines the two options sqlite-create and sqlite-readwrite.

sqlite-readonly

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database is opened in read-only mode. If the database does not exist already, an exception is thrown.

sqlite-readwrite

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database is opened for reading and writing if possible, or reading only if the file cannot be written at the operating system-level. If the database does not exist already, an exception is thrown.

sqlite-create

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. This option needs to be combined with either sqlite-readwrite or sqlite-readonly. It will lead to the creation of a new database in case there is no database at the specified path.

sqlite-default

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database is opened for reading and writing if possible, or reading only if the file cannot be written at the operating system-level. If the database does not exist already, a new database is being created.

sqlite-fullmutex

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database will use the "serialized" threading mode. In this mode, multiple threads can safely attempt to use the same database connection at the same time without the need for synchronization.

sqlite-sharedcache

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database is opened with shared cache enabled.

sqlite-privatecache

This is a fixnum value for specifying an option how databases are opened or created via make-database and open-database. With this option, the database is opened with shared cache disabled.

Database objects

SQLite database objects are either created in memory with procedure make-database or they are created on disk by calling procedure open-database. open-database can also be used for opening an existing database. SQLite stores databases in regular files on disk.

(make-database) (make-database options)

Creates a new temporary in-memory database whose characteristics are described by options. options is a fixnum value. If no options are specified, sqlite-default (= create a new read/write database in memory) is used as the default. Options are represented as fixnum values. Combinations of options are created by performing a bitwise inclusive or of several option values, e.g. via (fxior opt1 opt2). The following option values are predefined and can be used with make-database:

• sqlite-default: A new in-memory database is created and opened for reading and writing.

• sqlite-fullmutex: The database will use the "serialized" threading mode. In this mode, multiple threads can safely attempt to use the same database connection at the same time without the need for synchronization.

• sqlite-sharedcache: The database is opened with shared cache enabled.

• sqlite-privatecache: The database is opened with shared cache disabled.

(open-database path) (open-database path options)

Opens a database at file path path whose characteristics are described by options. options is a fixnum value. If no options are specified, sqlite-default (= create a new read/write database if there is not database at path) is used as the default. Options are represented as fixnum values. Combinations of options are created by performing a bitwise inclusive or of several option values, e.g. via (fxior opt1 opt2). The following option values are predefined and can be used with open-database:

• sqlite-readonly: The database is opened in read-only mode. If the database does not exist already, an exception is thrown.

• sqlite-readwrite: The database is opened for reading and writing if possible, or reading only if the file cannot be written at the operating system-level. If the database does not exist already, an exception is thrown.

• sqlite-create: This option needs to be combined with either sqlite-readwrite or sqlite-readonly. It will lead to the creation of a new database in case there is no database at the specified path.

• sqlite-default: The database is opened for reading and writing if possible, or reading only if the file cannot be written at the operating system-level. If the database does not exist already, a new database is being created.

• sqlite-fullmutex: The database will use the "serialized" threading mode. In this mode, multiple threads can safely attempt to use the same database connection at the same time without the need for synchronization.

• sqlite-sharedcache: The database is opened with shared cache enabled.

• sqlite-privatecache: The database is opened with shared cache disabled.

(close-database db)

Closes database db and deallocates all memory related to the database. If a transaction is open at this point, the transaction is automatically rolled back.

(sqlite-database? obj)

Returns #t if obj is a database object. Otherwise, predicate sqlite-database? returns #f.

(database-path db)

Returns the file path as a string at which the database db is being persisted. For in-memory databases, this procedure returns #f.

(database-last-row-id db)

Each entry in a database table (except for WITHOUT ROWID tables) has a unique fixnum key called the row id. Procedure database-last-row-id returns the row id of the most recent successful insert into a table of database db. Inserts into WITHOUT ROWID tables are not recorded. If no successful inserts into row id tables have ever occurred for an open database, then database-last-row-id returns zero.

(database-last-changes db)

database-last-changes returns the number of rows modified, inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement on the database db. Executing any other type of SQL statement does not modify the value returned by database-last-changes.

(database-total-changes db)

Procedure database-total-changes returns the total number of rows inserted, modified or deleted by all INSERT, UPDATE, or DELETE statements completed since the database db was opened. Executing any other type of SQL statement does not affect the value returned by database-total-changes.

SQL statements

SQL statements are created with procedure prepare-statement. This procedure returns a statement object which encapsulates a compiled SQL query. The compiled SQL query can be executed by repeatedly calling procedure process-statement. As long as process-statement returns #f, a new result row can be extracted from the statement object with procedures such as column-count, column-name, column-type, column-value, row-names, row-types, row-values, and row-alist. As soon as process-statement returns #t, processing is complete. With procedure reset-statement, a statement object can be reset such that it can be executed again.

(sqlite-statement? obj)

Returns #t if obj is a statement object. Otherwise, predicate sqlite-statement? returns #f.

(prepare-statement db str)

To execute an SQL statement, it must first be compiled into bytecode which then gets executed, potentially multiple times, in a second step. prepare-statement compiles an SQL statement contained in string str for execution in database db. It returns a statement object which encapsulates the compiled query. If compilation fails, an execption is thrown.

(parameter-count stmt)

Returns the number of parameters contained in statement object stmt. If stmt contains N parameters, they can be referenced by the indices 1 to N.

(parameter-index stmt name)

Returns the index of named parameter name in statement object stmt. name is a string. The result is a positive fixnum if the named parameter exists, or #f if there is no parameter with name name.

(parameter-name stmt idx)

Returns the name of the named parameter at index idx in statement object stmt as a string. If such a parameter does not exist, parameter-name returns #f. idx is a positive fixnum.

(bind-parameter stmt idx val)

Binds parameter at index idx to value val in statement object stmt.

(bind-parameters stmt vals) (bind-parameters stmt vals idx)

Binds the parameters starting at index idx to values in list vals. If idx is not given, 1 is used as a default. bind-parameters returns the tail of the list that could not be bound to parameters. idx is a positive fixnum.

(process-statement stmt)

Procedure process-statement starts or proceeds executing statement stmt. The result of the execution step is accessible via the statement object stmt and can be inspected by procedures such as column-count, column-name, column-type, column-value, row-names, row-types, row-values, and row-alist. process-statement returns #f as long as the execution is ongoing and a new resulting table row is available for inspection. When #t is returned, execution is complete.

(reset-statement stmt)

Resets the statement object stmt so that it can be processed another time.

(column-count stmt)

column-count returns the number of columns of the result of processing statement stmt. If stmt does not yield data as a result, column-count returns 0.

(column-name stmt idx)

column-name returns the name of column idx of the result of executing statement stmt. idx is a fixnum identifying the column by its 0-based index. column-name returns #f if column idx does not exist.

(column-type stmt idx)

column-type returns the type of the value at column idx of the result of executing statement stmt. idx is a fixnum identifying the column by its 0-based index. column-type returns #f if column idx does not exist. Types are represented by symbols. The following types are supported:

• sqlite-integer: Values are fixnums

• sqlite-float: Values are flonums

• sqlite-text: Values are strings

• sqlite-blob: Values are bytevectors

• sqlite-null: There is no value (void is the only supported value)

(column-value stmt idx)

column-value returns the value at column idx of the result of executing statement stmt. idx is a fixnum identifying the column by its 0-based index. column-value returns #f if column idx does not exist.

(row-names stmt)

Returns a list of all column names of the result of executing statement stmt.

(row-types stmt)

Returns a list of all column types of the result of executing statement stmt. Types are represented by symbols. The following types are supported:

• sqlite-integer: Values are fixnums

• sqlite-float: Values are flonums

• sqlite-text: Values are strings

• sqlite-blob: Values are bytevectors

• sqlite-null: There is no value (void is the only supported value)

(row-values stmt)

Returns a list of all column values of the result of executing statement stmt.

(row-alist stmt)

Returns an association list associating column names with column values of the result of executing statement stmt.