SQLite is a software library that implements an easy to use,
self-contained, serverless, zero-configuration, transactional SQL
database engine. SQLite is not intended to be an enterprise database
engine like Oracle or PostgreSQL. Instead, SQLite strives to be small,
fast, reliable, and above all simple. See Appropriate Uses For SQLite.

Sql3 is a wrapper around SQLite’s C interface that provides Pure
programmers access to almost all of SQLite’s features, including many
that are not available through Pure’s generic ODBC interface.

The examples subdirectory of pure-Sql3 contains several files that
further illustrate basic usage as well as some of Sql3’s more
sophisticated features. These include readme.pure, a short file that
contains the examples included herein. If you are using emacs pure-mode
you can load readme.pure into a buffer and execute the examples line by
line (pressing C-c C-c) (as well as experiment as you go).

SQLite’s home page provides excellent documentation regarding its SQL
dialect as well as its C interface. Comments in this document
regarding SQLite are not meant to be a substitute for the actual
documentation and should not be relied upon, other than as general
observations which may or may not be accurate. The best way to use
Sql3 is to get familiar with SQLite and its C interface and go
directly to the SQLite Site Map for authoritative answers to any
specific questions that you might have.

The SQLite library includes a really nice command-line utility named
sqlite3 (or sqlite3.exe on Windows) that allows the user to manually
enter and execute SQL statements against a SQLite database (and much
more).

This tool is an invaluable aid when working with SQLite in general and
with Sql3 in the Pure interpreter in particular. For example, after
entering the Pure statements from the Simple Example above, you could
start a new terminal, cd to pure-sql3, type “sqlite3 readme.db” at the
prompt, and see the effect the Pure statements had on the database:

Sql3 is free software: you can redistribute it and/or modify it
under the terms of the New BSD License, often referred to as the 3
clause BSD license. Sql3 is distributed in the hope that it will be
useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Please see the COPYING file for the actual license applicable to Sql3.

Unless you already have them on your machine, download SQLite and
sqlite3 from the SQLite website and install as indicated. To install
Sql3, cd to the pure-sql3 directory, run make, and then run sudomakeinstall (on Linux).

From a client’s perspective, the most important of SQLite’s data structures
are the database connection object “sqlite3” and the prepared statement object
“sqlite3_stmt”. These are opaque data structures that are made available to
users of SQLite’s C interface via pointers, sqlite3* and sqlite3_stmt*. At
appropriate times, Sql3 creates “cooked” versions of these pointers that can
be used (with care) to call native C functions exposed by SQLite’s C
interface.

Sql3 introduces two new data types, “db_ptr” and “stmt_ptr” which refer to the
cooked versions of sqlite3* and sqlite3_stmt*, respectively. These two
new data types are defined using :func: type, and therefore can be used as
type tags in rule patterns or as the first parameter passed to in the typep
function. It follows that all db_ptrs are sqlite3* pointers and all stmt_ptrs
are sqlite3_stmt* pointers. Thus, using dbp and sp1 from the introductory
example:

Generally speaking, the first step in accessing a database is to
obtain a db_ptr that references a database connection object. Once the
db_ptr is obtained, it can be used to construct prepared statements
for updating and querying the underlying database. The last step is
usually to close the database connection (although this is will be done
automatically by Sql3 when the db_ptr goes out of scope).

opens a SQLite database file whose name is given by the file_path argument
and returns a db_ptr for the associated database connection object created
by SQLite.

Example:

> let dbp2 = open "abc.db"; dbp2;#<pointer 0x992dff8>

If the filename is ”:memory:” a private, temporary in-memory database
is created for the connection.

The basic access modes are:

SQLITE_OPEN_READONLY - the database is opened in read-only mode. If the
database does not already exist, an error is returned.

SQLITE_OPEN_READWRITE - the database is opened for reading and writing if
possible, or reading only if the file is write protected by the operating
system. In either case the database must already exist, otherwise an
error is returned.

SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE - the database is opened for
reading and writing, and is creates it if it does not already exist. This
is the default value that is used if the flags argument is omitted.

These flags can be combined with SQLITE_OPEN_NOMUTEX
SQLITE_OPEN_FULLMUTEX SQLITE_OPEN_SHAREDCACHE SQLITE_OPEN_PRIVATECACHE
to control SQLite’s threading and shared cache features. All of these
flags are exported by Sql3.

If SQLite cannot open the connection, it still returns a pointer to a
database connection object that must be closed. In this case, open
automatically closes the the connection object and then throws an
exception. E.g.,:

if the database connection referenced by the db_ptr dbp is open,
close it using sqlite3_close; otherwise do nothing.

Before calling sqlite3_close, close finalizes all prepared
statements associated with the connection being closed. Sql3 will
detect and throw a db_error if an attempt is subsequently made to
execute a statement associated with the closed database connection.

If a db_ptr goes out of scope, Sql3 will automatically call sqlite3_close
to close the referenced database connection, but only if the connection has
not already been closed by close. Thus, for example, it is not
necessary to use a catch statement to ensure that Sqlite3 resources are
properly finalized when a db_ptr is passed into code that could throw an
exception.

When debugging, this activity can be observed by editing sql3.pure,
changing “const SHOW_OPEN_CLOSE = 0;” to “const SHOW_OPEN_CLOSE = 1;”
and running sudo make install in the pure-sql3 directory. This will
cause a message to be printed whenever a db_ptr or stmt_ptr is created
or finalized.

N.B. You should never call the native C interface function``sqlite3_close``
with a db_ptr. If the referenced database connection is closed by such a call,
a subsequent call to close on this db_ptr (including the call that
will automatically occur when the db_ptr goes out of scope) will cause a seg
fault.

Using the C interface, the basic procedure is to prepare a statement using
sqlite3_prepare_v2, bind its parameters using sqlite3_bind, step it
using sqlite3_step one or more times until it is done and then finalize it
using sqlite3_finalize. Each time sqlite3_step returns SQLITE_ROW, use
sqlite3_column to fetch the row’s values. Here sqlite3_bind and
sqlite3_column represent families of bind and column functions, rather
than actual functions, with one member for each of the basic data types
recognized by SQLite. Thus, for example, sqlite_bind_double is the
function one would use to bind a prepared statement with an argument of type
double.

constructs a prepared statement object and returns a stmt_ptr that
references it. dbp must be a db_ptr or the rule will not
match. sql_statement is the SQL statement that will be executed
when the prepared statement is passed to exec.

Basically, prep just passes dbp and sql_statement on to
sqlite3_prepare_v2 and returns a sentry guarded version of the
sqlite3_stmt* it receives back from sqlite3_prepare_v2. SQL
statements passed to prep (and sqlite3_prepare_v2) can have
argument placeholders, indicated by ”?”, ”?nnn”, ”:AAA”, etc, in which
case the argument placeholders must be bound to values using
sqlite_bind before the prepared statement is passed to
sqlite3_step. Hence the binding_string, which is used by Sql3
to determine how to bind the prepared statement’s argument
placeholders, if any. The binding string also tells Sql3 how to fetch
values in the sqlite3_column phase of the basic prepare, bind,
step, fetch, finalize cycle dictated by the SQlite C interface.

In the following two examples, the “c” and “i” in the binding strings
indicate that (a) a string and an int will be used to bind sp1,(b)
an int will be used to bind sp2 and (c) sp2, when executed,
will return a result set in the form of a list of sublists each of
which contains a string and an int.

In general, the characters in the type string before the ”:”, if any,
indicate the types in the result set. Those that occur after the ”:”,
if any, indicate the types of the arguments used to bind the prepared
statement object. If the type string does not contain a ”:”, the
characters in the type string, if any, are the types of binding
arguments.

Sql3 provides the following set of “core” binding types:

Type

Pure Argument

SQLite Type

b

(int, pointer)

blob

c

string

text (utf8)

d

double

float

i

int

int

k

int or bigint

int64

l

bigint

blob

n

Sql3::SQLNULL

NULL

x

expression

blob

v

variant

variant

The “b” or blob type is different from the rest in that the Pure
argument is specified as a pair. The first element of the pair
indicates the length in bytes of the object to be stored and the
second element indicates its location in memory. The “c” type
stands for string (as in “char*”), “d” stands for double and
“i” stands for int. The “k” type stands for “key” and maps
Pure ints and bigints (within the range of int64) to int64 values in
the database. This type is useful when dealing with SQLite’s “integer
primary keys” and “rowids” both of which are int64. The “l” type,
in contrast applies to all bigints (and not to ints) and it maps
bigints onto blobs, which are generally meaningless in SQL math
expressions. The “n” type can only appear on the binding side of
a type string. The “v” type stands for any of “b”, “c”, “d”, “i”
or “n”, based on the type of the binding argument. A “v” type will be
fetched from SQLite according to the native SQLite column type of the
corresponding column. The “x” type is used to store and
reconstruct Pure expressions as binary objects, using the
val and blob functions provided by the Pure
prelude.

Users can define custom binding types and pass them as a third
parameter to open. The resulting db_ptr can be used with the
custom binding types to construct prepared statements using prep.

Extra care is required when executing prepared statements that take a
blob argument because it must be a pair. In order to preserve the
tuple as a pair, binding arguments that include a blob should passed
to exec as a list. If passed as a member of a larger tuple, it will be
treated as two arguments due to the nature of tuples.

SQLite allows multiple processes to concurrently read a single
database, but when any process wants to write, it locks the entire
database file for the duration of its update.

When the native SQLite C interface function sqlite3_step (used by
exec) tries to access a file that is locked by another process, it treats
the database as “busy” and returns the SQLITE_BUSY error code. If this happens
in a call to exec or lexec, a db_busy exception will
be thrown.

You can adjust SQLite’s behavior using sqlite3_busy_handler or
sqlite3_busy_timeout.

If the statement is a COMMIT or occurs outside of an explicit
transaction, then you can retry the statement. If the statement is not
a COMMIT and occurs within a explicit transaction then you should
rollback the transaction before continuing.

No changes can be made to a SQLite database file except within a
transaction. Transactions can be started manually by executing a BEGIN
statement (i.e., exec dbp “BEGIN”). Manually started transactions persist
until the next COMMIT or ROLLBACK statement is executed. Transactions are also
ended if an error occurs before the transaction is manually ended using a
COMMIT or ROLLBACK statement. This behavior provides the means make a series
of changes “atomically.”

By default, SQLite operates in autocommit mode. In autocommit mode,
any SQL statement that changes the database (basically, anything other
than SELECT) will automatically start a transaction if one is not
already in effect. As opposed to manually started transactions,
automatically started transactions are committed as soon as the
execution of the related statement completes.

The upshot of this, in Sql3 terms, is that unless a transaction is
started manually, the database will be updated each time exec is
called. For a long series of updates or inserts this a can be very
slow. The way to avoid this problem is to manually begin and end
transactions manually.

Sql3 provides the following convenience functions all of which simply
call exec with the appropriate statement. For example begindbp is exactly the same as execdbp"BEGIN".

finalize the prepared statement referenced by sp, which must be a
stmt_ptr previously returned by prep.

Often there is no need to call finalize for a given stmt_ptr
because it will be automatically called when the stmt_ptr goes out of
scope.

If the stmt_ptr is associated with a database connection that has been
closed (which would have caused an exception to be thrown), an attempt
to finalize it, including the automatic finalization can occur when
stmt_ptr goes out of scope, will cause an exception to be thrown.

Multiple calls to finalize are fine. In contrast, the corresponding
native C interface function, sqlite3_finalize will cause a seg
fault if called with a pointer to a finalized prepared statement
object. This is the main reason why you should never call
sqlite3_finalize with a stmt_ptr. If the prepared statement
referenced by the stmt_ptr is finalized by such a call, a subsequent
call to finalize with the stmt_ptr (including the call that will
automatically occur when the stmt_ptr goes out of scope) will cause a
seg fault.

When a Sql3 function detects an error it throws an exception of the form
“db_errorecmsg” where ec is an error code and msg is the
corresponding error message. If ec>0, the error was detected by SQLite
itself, and ec and msg are those returned by SQLite. If ec==0, the error
was detected by Sql3 and msg is a Sql3 specific description of the
error. E.g.,

Sql3 functions exec and lexec throw exceptions of the form
“db_busydbp”, where dbp is a db_ptr, if they are prevented from
executing successfully because the database referenced by dbp is locked
(See Executing Against a Busy Database).

An extremely powerful (albeit complex) feature of the SQLite C
interface is the ability to add new SQL scalar or aggregate
functions. The new functions can be used in SQL statements the in same
way as SQLites’s prepackaged functions. Sql3 hides the complexity and
seamlessly integrates all of this functionality, :), into Pure via
create_function. This function is used to register both scalar SQL
functions and aggregate SQL functions with SQlite.

registers a new SQL scalar function of nargs arguments that can be
called, as name, in SQL statements prepared with respect to dbp, a
db_ptr. When the SQL function is called in a SQL statement, control is
passed to pure_fun, a function written in Pure. If nargs is
(-1), the SQL function name is variadic, and the arguments will be
passed to pure_fun as a single list.

Here is an example of a scalar function that takes two parameters. Note
that any kind of Pure “function” can be passed here; local functions,
global functions, lambdas or partial applications all work.

Multiple SQL functions can be registered with the same name if they
have differing numbers of arguments. Built-in SQL functions may be
overloaded or replaced by new application-defined functions.

Generally, a custom function is permitted to call other Sql3 and
native SQLite C interface functions. However, such calls must not
close the database connection nor finalize or reset the prepared
statement in which the function is running.

registers a new SQL aggregate function of nargs arguments that can be
called, as name in SQL statements prepared with respect to dbp, a
db_ptr. step and final are curried Pure functions and start is
the initial value for the aggregation. The step function is called
repeatedly to accumulate values from the database, starting from the given
start value, and finally the final function is applied to the
accumulated result.

Note that for a single-argument step function, this works exactly as
if the functions were invoked as “final(foldlstepstartvalues)”,
where values is the list of aggregated values from the database.

The db_ptrs returned by open and stmt_ptrs returned by prep
are sentry guarded versions of the actual pointers to the data base
connection objects and prepared statement objects returned by their
corresponding native C interface functions sqlite3_open_v2 and
sqlite3_prepare_v2. This makes it easy to call almost any
external function in SQLite’s C interface directly, passing it the
same db_ptr or stmt_ptr that is passed to Sql3’s functions, such as
prep or exec.

For example, you can override SQLite’s default behavior with respect
to a busy database as follows:

This sets a busy handler that will “sleep and retry” multiple times
until at least 10 milliseconds of sleeping have accumulated. Calling
this routine with an argument less than or equal to zero turns off all
busy handlers.

Another example is to query the number of database rows that were
changed, inserted or deleted by the most recently completed SQL
statement executed on a given database connection:

In order to call a native C function you must first make it accessible
using an extern statement.

Please note also that directly calling a function provided by the
SQLite C interface can be dangerous, as is the case with any call from
Pure code to an external C function. Sql3 users should be especially
careful in this regard because using a db_ptr or a stmt_ptr in calls
to certain native C interface functions, including in particular
sqlite3_close and sqlite3_finalize, will corrupt data held by
the db_ptr or stmt_ptr, leading to undefined behavior. The reason for
this restriction is that Sql3 uses sentries to insure that the
resources associated with a db_ptr or a stmt_ptr are automatically
finalized by SQLite when they go out of scope. In addition, the
sentries carry internal information used by Sql3 for other purposes.

You can add your own binding types for preparing and executing
prepared statements by specifying a third argument to open. The
third argument must be a list of “hash rocket pairs” in which the left
side is a character for the custom binding type and the right side is
a list with three members. The second and third members of the list
are functions that map objects from the new type to one of the Sql3
core types and back, respectively. The first member of the list is the
character for the Sql3 core types referenced by the mapping functions.

The file sql3_user_bind_types.pure in the examples subdirectory shows
how this might be done for a couple of user defined types. The example
script deals with dates and certain Pure expressions as bigints and
native Pure expressions, while the database stores these as utf-8
text. The following snippets show parts of the script that are
relevant to this discussion:

Single-thread. In this mode, all mutexes are disabled and SQLite
is unsafe to use in more than a single thread at once.

Multi-thread. In this mode, SQLite can be safely used by multiple
threads provided that no single database connection is used
simultaneously in two or more threads.

Serialized. In serialized mode, SQLite can be safely used by
multiple threads with no restriction.

SQLite can be compiled with or without support for multithreading and
the default is to support it.

In many cases, single-thread mode might be appropriate if only because
it is measurably faster. This might be the case, for example, if you
are using SQLite as the on-disk file format for a desktop application.

If your version of SQLite was compiled with support for
multithreading, you can switch to single-thread mode at runtime by calling
sqlite3_config() with the verb SQLITE_CONFIG_SINGLETHREAD.

If you must use threads, it is anticipated that Sql3 probably will not
impose an additional burden. Hopefully, you will be fine if you apply
the same precautions to a db_ptr or stmt_ptr that you would apply to
the underlying sqlite* and sqlite_stmt*s if you were not using
Sql3. It is strongly advised however that you look at the underlying
Sql3 code to verify that this will work. Since everything that is
imposed between the raw pointers returned by the SQlite interface and
the corresponding db_ptr and stmt_ptrs is written in Pure, it should
be relatively easy to determine how Sql3 and your multithreading
strategy will interact. See Is SQLite threadsafe? , Opening A New
Database Connection and Test To See If The Library Is Threadsafe.