Liam Healy - September 29th, 2007

03:45 pm - Extension functions in sqlite3, again

I mentioned before that I cleaned up and posted mathematical and string extension functions for SQLite3. Some things have changed in the interim, which necessitated going through a few revisions. So now the new version is available which makes compilation and use easier. For one thing, the SQLite source is no longer required to compile it. Second, it uses the standard sqlite3_load_extension interface, which should make it easier to use. The interface through CL using CLSQL is now

(in-package :sqlite3)

;;; Add mathematical and string functions to SQL queries using
;;; libsqlitefunctions from
;;; http://www.sqlite.org/contrib/download/extension-functions.c?get=22

(def-sqlite3-function
    "sqlite3_enable_load_extension"
    ((db sqlite3-db) (onoff :int))
 :returning :int)

(def-sqlite3-function
    "sqlite3_load_extension"
    ((db sqlite3-db)
     (filename :cstring)
     (entrypoint :int)
     (errmsg :int))
  :returning :int)

(eval-when  (:compile-toplevel :load-toplevel :execute)
  (export 'enable-sqlite3-extension-functions))

(defun enable-sqlite3-extension-functions (database)
  "Set up the SQLite3 mathematical extension functions.  This
   must be called every time the database is connected
   before any extension function is used."
  (let ((db-ptr (clsql-sqlite3::sqlite3-db database)))
    (sqlite3-enable-load-extension db-ptr 1)
    (unless (zerop
	     (sqlite3-load-extension db-ptr "libsqlitefunctions.so" 0 0))
      (error "Can't load libsqlitefunctions.so."))))