+;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
+;;;; *************************************************************************
+;;;; FILE IDENTIFICATION
+;;;;
+;;;; Name: sqlite3-api.lisp
+;;;; Purpose: Low-level SQLite3 interface using UFFI
+;;;; Authors: Aurelio Bignoli
+;;;; Created: Oct 2004
+;;;;
+;;;; $Id$
+;;;;
+;;;; This file, part of CLSQL, is Copyright (c) 2004 by Aurelio Bignoli
+;;;;
+;;;; CLSQL users are granted the rights to distribute and use this software
+;;;; as governed by the terms of the Lisp Lesser GNU Public License
+;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
+;;;; *************************************************************************
+
+(in-package #:cl-user)
+
+(defpackage #:sqlite3
+ (:use #:common-lisp #:uffi)
+ (:export
+ ;;; Conditions
+ #:sqlite3-error
+ #:sqlite3-error-code
+ #:sqlite3-error-message
+
+ ;;; API functions.
+ #:sqlite3-open
+ #:sqlite3-close
+
+ #:sqlite3-prepare
+ #:sqlite3-step
+ #:sqlite3-finalize
+
+ #:sqlite3-column-count
+ #:sqlite3-column-name
+ #:sqlite3-column-type
+ #:sqlite3-column-text
+ #:sqlite3-column-bytes
+ #:sqlite3-column-blob
+
+ ;;; Types.
+ #:sqlite3-db
+ #:sqlite3-db-type
+ #:sqlite3-stmt-type
+ #:unsigned-char-ptr-type
+ #:null-stmt
+
+ ;;; Columnt types.
+ #:SQLITE-INTEGER
+ #:SQLITE-FLOAT
+ #:SQLITE-TEXT
+ #:SQLITE-BLOB
+ #:SQLITE-NULL))
+
+(in-package #:sqlite3)
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Return values for sqlite_exec() and sqlite_step()
+;;;;
+(defconstant SQLITE-OK 0 "Successful result")
+(defconstant SQLITE-ERROR 1 "SQL error or missing database")
+(defconstant SQLITE-INTERNAL 2 "An internal logic error in SQLite")
+(defconstant SQLITE-PERM 3 "Access permission denied")
+(defconstant SQLITE-ABORT 4 "Callback routine requested an abort")
+(defconstant SQLITE-BUSY 5 "The database file is locked")
+(defconstant SQLITE-LOCKED 6 "A table in the database is locked")
+(defconstant SQLITE-NOMEM 7 "A malloc() failed")
+(defconstant SQLITE-READONLY 8 "Attempt to write a readonly database")
+(defconstant SQLITE-INTERRUPT 9 "Operation terminated by sqlite3_interrupt()")
+(defconstant SQLITE-IOERR 10 "Some kind of disk I/O error occurred")
+(defconstant SQLITE-CORRUPT 11 "The database disk image is malformed")
+(defconstant SQLITE-NOTFOUND 12 "(Internal Only) Table or record not found")
+(defconstant SQLITE-FULL 13 "Insertion failed because database is full")
+(defconstant SQLITE-CANTOPEN 14 "Unable to open the database file")
+(defconstant SQLITE-PROTOCOL 15 "Database lock protocol error")
+(defconstant SQLITE-EMPTY 16 "Database is empty")
+(defconstant SQLITE-SCHEMA 17 "The database schema changed")
+(defconstant SQLITE-TOOBIG 18 "Too much data for one row of a table")
+(defconstant SQLITE-CONSTRAINT 19 "Abort due to contraint violation")
+(defconstant SQLITE-MISMATCH 20 "Data type mismatch")
+(defconstant SQLITE-MISUSE 21 "Library used incorrectly")
+(defconstant SQLITE-NOLFS 22 "Uses OS features not supported on host")
+(defconstant SQLITE-AUTH 23 "Authorization denied")
+(defconstant SQLITE-FORMAT 24 "Auxiliary database format error")
+(defconstant SQLITE-RANGE 25 "2nd parameter to sqlite3_bind out of range")
+(defconstant SQLITE-NOTADB 26 "File opened that is not a database file")
+(defconstant SQLITE-ROW 100 "sqlite3_step() has another row ready")
+(defconstant SQLITE-DONE 101 "sqlite3_step() has finished executing")
+
+(defparameter error-codes
+ (list
+ (cons SQLITE-OK "not an error")
+ (cons SQLITE-ERROR "SQL logic error or missing database")
+ (cons SQLITE-INTERNAL "internal SQLite implementation flaw")
+ (cons SQLITE-PERM "access permission denied")
+ (cons SQLITE-ABORT "callback requested query abort")
+ (cons SQLITE-BUSY "database is locked")
+ (cons SQLITE-LOCKED "database table is locked")
+ (cons SQLITE-NOMEM "out of memory")
+ (cons SQLITE-READONLY "attempt to write a readonly database")
+ (cons SQLITE-INTERRUPT "interrupted")
+ (cons SQLITE-IOERR "disk I/O error")
+ (cons SQLITE-CORRUPT "database disk image is malformed")
+ (cons SQLITE-NOTFOUND "table or record not found")
+ (cons SQLITE-FULL "database is full")
+ (cons SQLITE-CANTOPEN "unable to open database file")
+ (cons SQLITE-PROTOCOL "database locking protocol failure")
+ (cons SQLITE-EMPTY "table contains no data")
+ (cons SQLITE-SCHEMA "database schema has changed")
+ (cons SQLITE-TOOBIG "too much data for one table row")
+ (cons SQLITE-CONSTRAINT "constraint failed")
+ (cons SQLITE-MISMATCH "datatype mismatch")
+ (cons SQLITE-MISUSE "library routine called out of sequence")
+ (cons SQLITE-NOLFS "kernel lacks large file support")
+ (cons SQLITE-AUTH "authorization denied")
+ (cons SQLITE-FORMAT "auxiliary database format error")
+ (cons SQLITE-RANGE "bind index out of range")
+ (cons SQLITE-NOTADB "file is encrypted or is not a database"))
+ "Association list of error messages.")
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Column types.
+;;;;
+(defconstant SQLITE-INTEGER 1)
+(defconstant SQLITE-FLOAT 2)
+(defconstant SQLITE-TEXT 3)
+(defconstant SQLITE-BLOB 4)
+(defconstant SQLITE-NULL 5)
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Foreign types definitions.
+;;;;
+(def-foreign-type sqlite3-db :pointer-void)
+(def-foreign-type sqlite3-stmt :pointer-void)
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Lisp types definitions.
+;;;;
+(def-type sqlite3-db-type sqlite3-db)
+(def-type sqlite3-db-ptr-type (* sqlite3-db))
+(def-type sqlite3-stmt-type sqlite3-stmt)
+(def-type sqlite3-stmt-ptr-type (* sqlite3-stmt))
+(def-type unsigned-char-ptr-type (* :unsigned-char))
+
+(defparameter null-stmt (make-null-pointer :void))
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;
+;;; Hash tables for db and statement pointers.
+;;;
+(defvar *db-pointers* (make-hash-table))
+(defvar *stmt-pointers* (make-hash-table))
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Conditions.
+;;;;
+(define-condition sqlite3-error ()
+ ((message :initarg :message :reader sqlite3-error-message :initform "")
+ (code :initarg :code :reader sqlite3-error-code))
+ (:report (lambda (condition stream)
+ (format stream "Sqlite3 error [~A]: ~A"
+ (sqlite3-error-code condition)
+ (sqlite3-error-message condition)))))
+
+(defmethod signal-sqlite3-error (db)
+ (let ((condition
+ (make-condition 'sqlite3-error
+ :code (sqlite3-errcode db)
+ :message (convert-from-cstring (sqlite3-errmsg db)))))
+ (unless (signal condition)
+ (invoke-debugger condition))))
+
+(defmethod signal-sqlite3-error ((code number))
+ (let ((condition
+ (make-condition 'sqlite3-error
+ :code code
+ :message (let ((s (cdr (assoc code error-codes))))
+ (if s
+ s
+ "unknown error")))))
+ (unless (signal condition)
+ (invoke-debugger condition))))
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; Library functions.
+;;;;
+(defmacro def-sqlite3-function (name args &key (returning :void))
+ `(def-function ,name ,args
+ :module "sqlite3"
+ :returning ,returning))
+
+(declaim (inline %errcode))
+(def-sqlite3-function
+ "sqlite3_errcode"
+ ((db sqlite3-db))
+ :returning :int)
+
+(declaim (inline %errmsg))
+(def-sqlite3-function
+ "sqlite3_errmsg"
+ ((db sqlite3-db))
+ :returning :cstring)
+
+(declaim (inline %open))
+(def-sqlite3-function
+ ("sqlite3_open" %open)
+ ((dbname :cstring)
+ (db (* sqlite3-db)))
+ :returning :int)
+
+(declaim (inline %close))
+(def-sqlite3-function
+ ("sqlite3_close" %close)
+ ((db sqlite3-db))
+ :returning :int)
+
+(declaim (inline %prepare))
+(def-sqlite3-function
+ ("sqlite3_prepare" %prepare)
+ ((db sqlite3-db)
+ (sql :cstring)
+ (len :int)
+ (stmt (* sqlite3-stmt))
+ (sql-tail (* (* :unsigned-char))))
+ :returning :int)
+
+(declaim (inline %step))
+(def-sqlite3-function
+ ("sqlite3_step" %step)
+ ((stmt sqlite3-stmt))
+ :returning :int)
+
+(declaim (inline %finalize))
+(def-sqlite3-function
+ ("sqlite3_finalize" %finalize)
+ ((stmt sqlite3-stmt))
+ :returning :int)
+
+(declaim (inline sqlite3-column-count))
+(def-sqlite3-function
+ "sqlite3_column_count"
+ ((stmt sqlite3-stmt))
+ :returning :int)
+
+(declaim (inline %column-name))
+(def-sqlite3-function
+ ("sqlite3_column_name" %column-name)
+ ((stmt sqlite3-stmt)
+ (n-col :int))
+ :returning :cstring)
+
+(declaim (inline sqlite3-column-type))
+(def-sqlite3-function
+ "sqlite3_column_type"
+ ((stmt sqlite3-stmt)
+ (n-col :int))
+ :returning :int)
+
+(declaim (inline sqlite3-column-text))
+(def-sqlite3-function
+ "sqlite3_column_text"
+ ((stmt sqlite3-stmt)
+ (n-col :int))
+ :returning (* :unsigned-char))
+
+(declaim (inline sqlite3-column-bytes))
+(def-sqlite3-function
+ "sqlite3_column_bytes"
+ ((stmt sqlite3-stmt)
+ (n-col :int))
+ :returning :int)
+
+(declaim (inline sqlite3-column-blob))
+(def-sqlite3-function
+ "sqlite3_column_blob"
+ ((stmt sqlite3-stmt)
+ (n-col :int))
+ :returning :pointer-void)
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;;;
+;;;; wrapper functions.
+;;;;
+(defun sqlite3-open (db-name &optional (mode 0))
+ (declare (ignore mode) (type string db-name))
+ (let ((dbp (allocate-foreign-object 'sqlite3-db)))
+ (declare (type sqlite3-db-ptr-type dbp))
+ (with-cstring (db-name-native db-name)
+ (let ((result (%open db-name-native dbp)))
+ (if (/= result 0)
+ (progn
+ ;; According to docs, the db must be closed even in case
+ ;; of error.
+ (%close (deref-pointer dbp 'sqlite3-db))
+ (free-foreign-object dbp)
+ (signal-sqlite3-error result))
+ (let ((db (deref-pointer dbp 'sqlite3-db)))
+ (declare (type sqlite3-db-type db))
+ (setf (gethash db *db-pointers*) dbp)
+ db))))))
+
+(declaim (ftype (function (sqlite3-db-type) t) sqlite3-close))
+(defun sqlite3-close (db)
+ (declare (type sqlite3-db-type db))
+ (let ((result (%close db)))
+ (if (/= result 0)
+ (signal-sqlite3-error result)
+ (progn
+ (free-foreign-object (gethash db *db-pointers*))
+ (remhash db *db-pointers*)
+ t))))
+
+(declaim (ftype (function (sqlite3-db-type string) sqlite3-stmt-type) sqlite3-prepare))
+(defun sqlite3-prepare (db sql)
+ (declare (type sqlite3-db-type db))
+ (with-cstring (sql-native sql)
+ (let ((stmtp (allocate-foreign-object 'sqlite3-stmt)))
+ (declare (type sqlite3-stmt-ptr-type stmtp))
+ (with-foreign-object (sql-tail '(* :unsigned-char))
+ (let ((result (%prepare db sql-native -1 stmtp sql-tail)))
+ (if (/= result SQLITE-OK)
+ (progn
+ (unless (null-pointer-p stmtp)
+ ;; There is an error, but a statement has been allocated:
+ ;; finalize it (better safe than sorry).
+ (%finalize (deref-pointer stmtp 'sqlite3-stmt)))
+ (free-foreign-object stmtp)
+ (signal-sqlite3-error db))
+ (let ((stmt (deref-pointer stmtp 'sqlite3-stmt)))
+ (declare (type sqlite3-stmt-type stmt))
+ (setf (gethash stmt *stmt-pointers*) stmtp)
+ stmt)))))))
+
+(declaim (ftype (function (sqlite3-stmt-type) t) sqlite3-step))
+(defun sqlite3-step (stmt)
+ (declare (type sqlite3-stmt-type stmt))
+ (let ((result (%step stmt)))
+ (cond ((= result SQLITE-ROW) t)
+ ((= result SQLITE-DONE) nil)
+ (t (signal-sqlite3-error result)))))
+
+(declaim (ftype (function (sqlite3-stmt-type) t) sqlite3-finalize))
+(defun sqlite3-finalize (stmt)
+ (declare (type sqlite3-stmt-type stmt))
+ (let ((result (%finalize stmt)))
+ (if (/= result SQLITE-OK)
+ (signal-sqlite3-error result)
+ (progn
+ (free-foreign-object (gethash stmt *stmt-pointers*))
+ (remhash stmt *stmt-pointers*)
+ t))))
+
+(declaim (inline sqlite3-column-name))
+(defun sqlite3-column-name (stmt n)
+ (declare (type sqlite3-stmt-type stmt) (type fixnum n))
+ (convert-from-cstring (%column-name stmt n)))