# (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:
```scheme
(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.
```scheme
(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`.
```scheme
(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.
```scheme
(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.
```scheme
; 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.
```scheme
; 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:
```scheme
(("Donald Duck" "donald@disney.net") ("Mickey Mouse" "mickey@disney.net"))
```
## 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 + 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** 
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.
**sqlite-database-type-tag** 
Symbol representing the `sqlite-database` type. The `type-for` procedure of library `(lispkit type)` returns this symbol for all sqlite database objects.
**(make-database)** \
\&#xNAN;**(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*****)** \
\&#xNAN;**(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-type-tag**
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*****)**
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*****)** \
\&#xNAN;**(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*.
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://www.lisppad.app/libraries/lispkit/lispkit-sqlite.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.