23.13 Using SQLite

This section describes particular issues in Common SQL with SQLite databases.

23.13.1 Connecting to SQLite

See 23.2.8 Connecting to SQLite. for information about SQLite-specific connection-spec and sqlite-keywords arguments to connect.

23.13.2 Types of retrieved fields in queries

By default, when doing queries (select, query, map-query, do-query, simple-do-query, loop with each record and print-query) the LispWorks checks the data type of each field it reads in each row, and fetches the data accordingly (using the C functions sqlite3_column_* like sqlite3_column_int in the SQLite3 library). Values of SQLite data types NULL, INTEGER, REAL and TEXT are mapped to Lisp objects of type null, integer, double-float and string respectively ("mapped" means returned from select or query, printed in print-query, or passed to your code in the other APIs). A value of data type BLOB is mapped to an array with element type (unsigned-byte 8) containing all of the bytes of the BLOB.

You can force the value to a specific type by specifying the type explicitly. This is done by specifying the type with the identifier, either using the symbolic SQL syntax (see 23.5.1.1 Enclosing database identifiers) or using sql-expression. For select and query you can also use the keyword argument :result-types.

The types that LispWorks recognizes for SQLite are the common types: :integer (alias :int), :string, :double-float and :binary. These match the SQLite data types INTEGER, TEXT, REAL and BLOB respectively. When these keywords are used, LispWorks asks SQLite for a value of the corresponding data type, and converts it to the matching Lisp object type as above. Note that the value can also be nil, if the the value is null or cannot be converted to the requested Lisp object type.

Other possible values for the type are:

Note that SQLite does not support any kind of date data type.

When the value that is stored in the database does not match the value that it is asked for, the SQLite library converts the value to the required type, so you always get a value of the correct type, but not necessarily a useful value. See the documentation for SQLite for details: https://www.sqlite.org/c3ref/column_blob.html "Result Values From a Query".

23.13.3 Tables containing a uniform type per column

SQLite allows the fields in each row to contain any supported type, rather than being constrained to the type specified for the column in the table definition.

When you connect to a database, you can use the SQLite-specific keyword :uniform-type-per-column in sqlite-keywords with value t to tell LispWorks that all of the values of a column returned by a query have the same data type.

When you do that, for fields where you do not specify the type explicitly, the LispWorks checks the type of the field in the first result row, and then uses it for the rest of the rows.

23.13.4 Reading from blobs using a handle (sqlite-raw-blob) and modifying blobs (sqlite-blob)

When the type of a field in a query is specified as :blob, the SQLite BLOB is mapped to an object of type sqlite-raw-blob. You can then read data from the SQLite BLOB using any the functions copy-from-sqlite-raw-blob, replace-from-sqlite-raw-blob or sqlite-raw-blob-ref. The function sqlite-raw-blob-length can be used to find the size of the BLOB (in bytes).

The sqlite-raw-blob is valid only within the dynamic extent of the function that is called from the Common SQL interface. If you try to read from a sqlite-raw-blob outside this dynamic context, an error of type sql-user-error will be signaled. You can use sqlite-raw-blob-valid-p to check if a blob is valid.

Using sqlite-raw-blob makes it more convenient to read the data when a BLOB contains elements larger than bytes, and makes it more efficient when you retrieve large BLOBs (a few kilobytes or more) but need only a small part of the data.

SQLite allows reading and writing of BLOBs (fields with type BLOB or TEXT) directly, which you can do using the sqlite-blob interface. The functions sqlite-open-blob and sqlite-close-blob are used to open and close a BLOB field, or the macro with-sqlite-blob can be used to do both. Once you have opened a BLOB, you call replace-from-sqlite-blob or replace-into-sqlite-blob to copy data to or from it. Note that the sqlite-blob is not thread-safe, so you must do all of the operations in a "single thread" context (either all in one thread, or serialized by a lock).

sqlite-raw-blob corresponds to the result of the C function sqlite3_column_blob (and sqlie3_column_bytes to obtain the size). sqlite-blob corresponds to the C structure sqlite3_blob.

23.13.5 Values in Insert and Update.

When modifying a table in SQLite, either directly by using insert-records or update-records, or by executing a prepared-statement statement with bind-variables, the values that are passed are treated as follows:

In a prepared-statement, if the variable-type is :string, then the value is converted to a string.

The value is passed to the SQLite library as a SQLite data type based on the type of the value as follows:

A binary array is an array with an integer or float element type. bmp-string and base-string are also binary arrays in some contexts, but they are treated as strings in this case (text-string is not a binary array).

In addition, the value can be a list, which in treated as follows:

• If the first element of the list is a binary array, including bmp-string or base-string, then the list must be of form (array), (array start) or (array start end) and the bytes between start and end in array are inserted as a BLOB. If start is omitted, it defaults to 0. If end is omitted, it defaults to the length of array.

  Note that start and end are denoted in elements rather than bytes, so the number of the bytes in the BLOB is (* (- end start) bytes-per-element). Note also that, for arrays of more than one byte per element, the contents of the BLOB will depend on the byte order of the host machine.

  Apart from allowing insertion of parts of arrays, this syntax also allows you to insert the character codes in a bmp-string and base-string as a BLOB, by passing the string as a list of one element.

• If the first element of the list is the keyword :zeroblob, then the second element is treated as a size, which must be a positive integer smaller than 231, that is of type (integer 0 #x7fffffff). LispWorks inserts a zero blob of this size (using the C function sqlite3_bind_zeroblob).

Any value that does not match the description above, including integers out of range and lists that do not match the patterns described, cause an error (of type sql-user-error) to be signaled.

23.13.6 Accessing ATTACHed databases

ATTACHed databases in SQLite, that is databases that were attached using the SQLite ATTACH statement, are identified by their schema names. You can specify the schema name in the "[...]" syntax, for example, if you attach a file called "another-database" as follows:

(execute-command "ATTACH another-database as AttachedDB")

then you can read the contents of a table SomeTable inside "another-database" using AttachedDB as the "schema" name:

(select [*] :from [AttachedDB SomeTable])

The keyword :owner in Common SQL function specifies the schema to which the table(s) belong, for example, after the ATTACH above, you can obtain the list of tables inside another-database by using:

(sql:list-tables :owner "AttachedDB")

and use AttachedDB as the "owner" in sql-expression:

(select [*] :from (sql-expression :owner "AttachedDB" 
                                  :table "SomeTable"))

See https://www.sqlite.org/lang_attach.html "ATTACH DATABASE" for details about attaching in SQLite.