SQLite3 Driver for Common Lisp (Lispworks)

Foreign language interface to the SQLite Version 3 database in Common Lisp for Lispworks. It requires no additional lisp libraries, and all the required code is included in one file. The code depends on the sqlite3 external dynamic library and works with any Lispworks version 8.0 or higher.

Download

sqlite3.lisp  (8JUL2023, 13KB)

Examples of usage

Example 1
---------

(load "sqlite3.lisp")
(sqlite:init "c:/lib/sqlite3.dll")
(setq db (sqlite:connect "c:/db/test.db"))
(sqlite:query db "select 1+1")
(sqlite:pquery db "select datetime('now') as datetime")
(sqlite:disconnect db)


Example 2
---------

(load "sqlite3.lisp")
(sqlite:init)

(defvar *db* nil)

(defconstant +industrialists+
  '(("Bruce Wayne" 188)
    ("Anthony Stark" 185)))

(defun main ()
  (sqlite:with-connection (*db* "test.db")
    (create-table)
    (populate-table)
    (view-table)))

(defun create-table ()
  (sqlite:query *db*
     "CREATE TABLE industrialist ("
     "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
     "  name VARCHAR(128),"
     "  height INTEGER)"))

(defun populate-table ()
  (dolist (item +industrialists+)
    (sqlite:query *db*
       "INSERT INTO industrialist (name, height)"
       "VALUES (" (sqlite:quote-string (car item)) "," (cadr item) ")")))

(defun view-table ()
  (dolist (item (query-table))
    (format t "~a: ~a~%" (getf item :name) (getf item :height))))

(defun query-table ()
  (sqlite:pquery *db*
     "SELECT name, height FROM industrialist"))

(main)

Requirement

The sqlite3 dynamic library is required to use the code. See the official sqlite3 site for more information on how to download and install the library.

Before using the code you must initialize the sqlite3 dynamic library with the init function. See the documentation for the init function in the section "Programming interface" of this file.

Limitations

There are no checks in the code for sql query statements — know what you are querying and what you expect to retrieve. If your query returns a set of one million results, the code will generate a list with one million elements, which might slow or crash your computer. Again, be judicious in what you query.

The code doesn't support the BLOB data type in the databases.

Programming interface

SQLITE3.lisp defines SQLITE package and exports some symbols as a public interface to work with sqlite3 databases. The programming interface consists of functions for establishing connection with a database, querying it and some helper utils and macros.

Symbols available in the SQLITE package:
init, connect, disconnect, query, pquery, last-insert-id, escape-string, quote-string, with-connection

[function]
init &optional filename

Initialize the sqlite3 dynamic library.

filename  a string with a filepath to an external sqlite3 dynamic library. If no filepath is given then the function uses default names for the sqlite3 dynamic libarary based on the computer platform. The list of directories where the dynamic library is searched is based on the rules described in the documentation for the function fli:register-module. See "LispWorks Foreign Language Interface User Guide and Reference Manual" for more information.


[function]
connect filename => handler

Opens to the sqlite database.

filename  a string that represents a filename for the database. If there is no such a filename then creates a new database.

handler  a strucure that holds the information about an opened connection


[function]
disconnect handler

Closes the sqlite database.

handler  a structure returned by the connection function


[function]
query handler &rest args => result

Query and retrieve information from the database as lists.

handler  a structure returned by the connection function
args  any number of query arguments of any type that would be combined together and send to the sqlite database as an sql statement. All args are first converted to strings and the resultant strings are combined together appending a space chracter between the strings. For example for args (1 "2" :keyword) the resultant sql statemnt string would be " 1 2 :KEYWORD".

result  the list of result rows as lists. Each element of a result row list is a string.


[function]
pquery handler &rest args => result

Query and retrieve information from the database as property lists.

handler  a structure returned by the connection function
args  any number of query arguments of any type that would be combined together and send to the sqlite database as an sql statement. See the query function for more information.

result  the list of result rows as property lists. In a property list the key is the name of a column as a keyword and the property is a string.


[function]
last-insert-id handler => result

Returns the id generated for an auto_increment column by the previous query (usually insert).

handler  a structure returned by the connection function

result  a number


[function]
escape-string string => result

Escapes special characters ', " and \ in a string.

string  an unescaped string

result  an escaped string


[function]
quote-string string => result

Escapes special characters ', " and \ in a string and adds the ' character at the beginning and the end of the escaped string.

string  an unescaped string

result  an escaped and quoted string


[macro]
with-connection (var filename) &body body => result

Connects to the database given by filename and binds information about the opened connection to var. Executes body using this binding. At the end closes the connection to the database.

var  a symbol
filename  a string that represents a filename for the database. If there is no such a filename then creates a new database.

result  the result of evaluating body


[condition type]
sqlite3-error

Signalled if the sqlite database returns an error.

License

Copyright (c) 2013-2023, Art Obrezan
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
  2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
  3. Use in source and binary forms are not permitted in projects under GNU General Public Licenses and its derivatives.

THIS SOFTWARE IS PROVIDED BY ART OBREZAN ''AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL ART OBREZAN BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.