Liam Healy - March 20th, 2007

10:03 pm - Math extension functions in SQLite3 using CLSQL

Inconveniently, nature has made the semimajor axis of an orbit proportional to the -2/3 power of the mean motion, so when retrieving element sets from an SQL database, it is very useful to have a POWER function, as Oracle does. Since I have now moved to SQLite3, this function is no longer available. The topic of adding mathematical functions like power comes up repeatedly every few weeks on the SQLite mailing list. Unhelpfully, someone always replies "it's easy" with no further information. It is not easy. Fortunately, Mikey C offered his C extension functions
which include most common math functions --- not just power, but trigonometric functions, logarithm, etc.

Here is my procedure for using these functions:

1. Download Mickey C's source zip
   
2. Unzip it, and optionally move the needed files btree.h, config.h, func_ext.c, hash.h, map.c, map.h, opcodes.h, os.h, pager.h, parse.h, sqliteInt.h, vdbe.h, vdbeInt.h into a separate directory
   
3. Compile
   

   gcc -DHAVE_ISBLANK -DHAVE_LOG10 -DHAVE_COSH -DHAVE_SINH -DHAVE_TANH \
    -DHAVE_ACOSH -DHAVE_ASINH -DHAVE_ATANH \
    -fPIC func_ext.c map.c -shared -o libsqlitefunctions.so
   

   
   

Now these functions can be used through CLSQL.

(in-package :sqlite3)

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

(uffi:load-foreign-library 
 (uffi:find-foreign-library "libsqlitefunctions" #p"/path/to/library/")
 :module "sqlitefunctions"
 :supporting-libraries '()
 :force-load t)

(def-sqlite3-function
    "sqlite3RegisterExtraFunctions"
    ((db sqlite3-db))
 :returning :void)

(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)
    (sqlite3registerextrafunctions db-ptr)))

After the database is connected, (enable-sqlite3-extension-functions) gets the ball rolling. Not just the mathematical extension functions, but there are string functions as well. And good old POWER:

(clsql:query 
    "SELECT (POWER((398600.4415 /
    POWER((7.27220521664304e-5*MEAN_MOTION), 2.0)), 0.3333333333333333)
    * (1.0 - ECCENTRICITY)) - 6378.1363 from nscels where catdate = '2007-03-16 06:16:48'
     AND satnum = 24937"
   :database *nscels-database*)

(Yes, I know that a query like this is easily computed in Lisp with #'expt. My real application is a WHERE clause that constrains perigee altitude between certain values.)