# 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 "mickey@disney.net")
    (bind-parameter stmt1 4 "+1 101-123-456")
    (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" "donald@disney.net" "+1 101-123-456"))
    (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" "donald@disney.net") ("Mickey Mouse" "mickey@disney.net"))

## API

### SQLite version retrieval

(sqlite-version) [procedure]

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) [procedure]

The `sqlite-version-number` procedure returns a fixnum with the value X 1000000 + _Y_1000 + 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 [constant]

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 [constant]

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 [constant]

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 [constant]

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 [constant]

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 [constant]

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 [constant]

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.

sqlite-database-type-tag [object]

Symbol representing the `sqlite-database` type. The `type-for` procedure of library `(lispkit type)` returns this symbol for all sqlite database objects.

(make-database) [procedure]
(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) [procedure]
(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) [procedure]

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) [procedure]

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

(database-path db) [procedure]

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) [procedure]

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) [procedure]

`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]

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-type-tag [object]

Symbol representing the `sqlite-statement` type. The `type-for` procedure of library `(lispkit type)` returns this symbol for all sqlite statement objects.

(sqlite-statement? obj) [procedure]

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

(prepare-statement db str) [procedure]

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) [procedure]

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) [procedure]

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) [procedure]

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) [procedure]

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

(bind-parameters stmt vals) [procedure]
(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]

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) [procedure]

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

(column-count stmt) [procedure]

`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) [procedure]

`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) [procedure]

`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) [procedure]

`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) [procedure]

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

(row-types stmt) [procedure]

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) [procedure]

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

(row-alist stmt) [procedure]

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