;; @module sqlite3.lsp
;; @description SQLite3 database interface routines
;; @version 1.6 - comments redone for automatic documentation
;; @version 1.7 - D.C. fixed getting types when null values are present
;; @version 1.8 - D.C. made 64-Bit integers work
;; @version 1.9 - new library detection routine
;; @version 2.0 - added documentation for close
;; @version 2.1 - use default functor for query
;; @version 2.2 - detection for NEWLISP64, lib path for OpenBSD, tested for 64-bit
;; @version 2.3 - C.H. added parameter binding for safer SQL (guard against SQL-injection)
;; @version 2.4 - doc changes
;; @version 2.5 - changed sqlite3_bind_blob to sqlite3_bind_text in function bind-parameter
;; @version 2.61 - added function <tt>sql3:colnames</tt>.
;; @version 2.7 - changed deprecated <tt>name</tt> to <tt>term</tt>, <tt>inc</tt> to <tt>++</tt>
;; @version 2.71 - minor doc changes
;; @version 2.72 - add support for CentOS 6 Linux 64Bit
;; @version 2.73 - doc additions
;; @version 2.83- added sqlite3 library path for UBUNTU 12.04/10 64-bit and others
;; @author Lutz Mueller 2004-2013, Dmitri Cherniak 2007, Clemens Hintze 2009
;;
;; <h2>Module for SQLite3 database bindings</h2>
;; To use this module include the following 'load' or 'module' statement at the
;; beginning of the program file:
;; <pre>
;; (load "/usr/share/newlisp/modules/sqlite3.lsp")
;; ; or shorter
;; (module "sqlite3.lsp")
;; </pre>
;; Test the module:
;; <pre>
;; (test-sqlite3)
;; </pre>
;; This function, located at the and of the module file, exercises
;; most of the functions.
;;
;; SQLite version 3.0 introduced a new database format and is incompatible
;; whith the previous 2.1 to 2.8 format. Old SQLite 2.x based databases can
;; be converted using the old and new sqlite client application:
;;
;; sqlite OLD.DB .dump | sqlite3 NEW.DB
;;
;; While in sqlite 2.8 all returned fields where of string type, SQLite3
;; returns, text, integer or float. Blobs are returned as text and NULLs
;; are returned as nil.
;;
;; See also the documentation at @link http://sqlite.org sqlite.org
;;
;; <h2>Requirements:</h2>
;; One of the libraries sqlite3.dll for Win32 or libsqlite3.so for UNIX like
;; operating systems is required from http://www.sqlite.org.
;;
;; SQLite is an <in-process> database. The library contains the whole database
;; system. An extra database server is not required. SQLite also has limited
;; mutiuser capabilities for accessing a common database from several programs
;; at the same time. See the documentation at @link http://sqlite.org sqlite.org
;; for details.
;;
;; The following is a short example how to use SQLite3:
;;
;; @example
;; (sql3:open "MYDB") ; opens/creates a database returns a handle (ignore)
;; ; or 'nil' on failure
;;
;; (sql3:sql "select * from mytable;") ; make a SQL query, return result
;; (sql3 "select * from mytable;") ; use default functor as alias
;;
;; (sql3:error) ; return error text
;;
;; (sql3:close) ; close the database
;; Function calls returning 'nil' signal that an error has occurred. The
;; function 'sql3:error' can then be used to get details about the error
;; as a text string.
;;
;; At the bottom of the source file 'sqlite3.lsp' a test routine called
;; 'test-sqlite3' can be found to test for correct installation of SQLite.
; make this module compatible with version less than 10.1.11
(when (< (sys-info -2) 10111)
(constant (global 'term) name))
(when (< (sys-info -2) 10110)
(constant (global '++) inc))
(context 'sql3)
; fetch-row and keep-type functions depend on this
(set 'NEWLISP64 (not (zero? (& (sys-info -1) 256))))
; set library to path-name of the library on your platform OS
;
(set 'files (list
"/usr/lib/libsqlite3.so" ; SuSE Linux
"/usr/local/lib/libsqlite3.so" ; Linux, BSD, Solaris
"/usr/pkg/lib/libsqlite3.so" ; NetBSD
"/usr/local/lib/libsqlite3.so.13.3" ; OpenBSD 4.6
"/usr/lib/libsqlite3.0.dylib" ; Mac OSX Darwin
"/usr/lib64/libsqlite3.so" ; for 64Bit Fedora CentOS 6 Linux
"/usr/lib/x86_64-linux-gnu/libsqlite3.so" ; for UBUNTU 64-bit
"/usr/lib/x86_64-linux-gnu/libsqlite3.so.0"
"/usr/lib/i386-linux-gnu/libsqlite3.so" ; for UBUNTU 32-bit
"/usr/lib/i386-linux-gnu/libsqlite3.so.0"
"sqlite3.dll" ; Win32 DLL path and current directory
(string (env "PROGRAMFILES") "/sqlite3/sqlite3.dll") ; Win32 SQLite3 std install
))
(set 'library (files (or
(find true (map file? files))
(throw-error "cannot find sqlite3 library"))))
(import library "sqlite3_open" "cdecl")
(import library "sqlite3_close" "cdecl")
(import library "sqlite3_prepare" "cdecl")
(import library "sqlite3_bind_blob" "cdecl")
(import library "sqlite3_bind_double" "cdecl")
(import library "sqlite3_bind_null" "cdecl")
(import library "sqlite3_bind_parameter_count" "cdecl")
(import library "sqlite3_bind_parameter_index" "cdecl")
(import library "sqlite3_bind_parameter_name" "cdecl")
(import library "sqlite3_bind_text" "cdecl")
(import library "sqlite3_bind_text16" "cdecl")
(import library "sqlite3_step" "cdecl")
(import library "sqlite3_column_count" "cdecl")
(import library "sqlite3_column_name" "cdecl")
(import library "sqlite3_column_type" "cdecl")
(import library "sqlite3_column_int64" "cdecl")
(import library "sqlite3_column_double" "cdecl")
(import library "sqlite3_column_text" "cdecl")
(import library "sqlite3_column_blob" "cdecl")
(import library "sqlite3_column_bytes" "cdecl")
(import library "sqlite3_finalize" "cdecl")
(import library "sqlite3_get_table" "cdecl")
(import library "sqlite3_last_insert_rowid" "cdecl")
(import library "sqlite3_changes" "cdecl")
(import library "sqlite3_busy_timeout" "cdecl")
(import library "sqlite3_errmsg" "cdecl")
; gloablly used vars and constants
(define db nil) ; database handle
(define dbp "\000\000\000\000\000\000\000\000") ; ptr to database handle
(define error-message nil) ; error message
(define col-names '()) ; list of column headers
(define col-types '()) ; list of column types
(define pstm "\000\000\000\000\000\000\000\000") ; ptr to compiled sql
(constant 'SQLITE_OK 0)
(constant 'SQLITE_ROW 100)
(constant 'SQLITE_DONE 101)
(constant 'SQLITE_TYPES '(
0
SQLITE_INTEGER
SQLITE_FLOAT
SQLITE_TEXT
SQLITE_BLOB
SQLITE_NULL))
;; @syntax (sql3:open <str-db-name>)
;; @param <str-db-name> The name of the database.
;; @return A database handle (discard), or 'nil' on failure.
;; Opens or creates a database. If the database does exist it gets opened,
;; else a new database with the name given is created.
;; If trying to open a database that already has been opened 'nil' is returned
;; and an error text can be retrieved using 'sql3:error'.
(define (sql3:open db-name)
; only open if not alrady done
(if (not db)
(begin
(set 'result (sqlite3_open db-name dbp))
(if (!= result SQLITE_OK)
(set 'db nil)
(if NEWLISP64
(set 'db (get-long dbp))
(set 'db (get-int dbp)))
))
(begin
(set 'error-message "A database is already open")
nil))
)
;; @syntax (sql3:close)
;; @return Returns 'true' on success;
;; Closes the currently open database.
(define (sql3:close) ;; overwrite the close in MAIN
(if db (begin
(sqlite3_close db)
(set 'db nil)
true)))
;; @syntax (sql3:sql <str-sql> [<sql-args>])
;; @param <str-sql> The SQL statement.
;; @param <sql-args> Parameters for the SQL statement's host variables
;;
;; Executes the SQL statement in <str-sql>. For 'select' statements a table
;; of the result set is returned or '()' for the empty set. For other statements
;; 'true' is returned for a successful outcome. On failure 'nil' is returened
;; and 'sql3:error' can be used to retrieve the error text.
;;
;; If the parameter <sql-args> is given, it has either to be a list of values (if
;; the SQL statement use the '?' type of host variables) or an association list
;; whose every association is formed like (<varname> <value>). The <varname> is
;; the name of the host variable used in the SQL statement e.g. ':name' or '?123'.
;;
;; Strings are bound to host variables as BLOBs. That mean the data will be passed
;; as is, without any further modification.
;;
;; Using host variables is much safer than passing those values via string
;; composition as no SQL quoting problem can occur (SQL injection attack).
;; For example:
;;
;; @example
;; ; traditional usage
;; (sql3:sql "select * from persons where age > 18;")
;;
;; ; safer usage using SQLite parameter binding
;; (sql3:sql "select * from persons where age > ?;" '(18))
;;
;; ; bind parameters from association lists
;; (sql3:sql "select * from persons where name like :name;" '((":name" "Do%")))
;; (sql3:sql "select * from persons where age > :a and name like :n;" '((":n" "Do%") (":a" 18)))
(define (sql sql-str sql-args)
(set 'result nil 'done nil 'error-message nil)
(set 'sqarray '());
(set 'col-names '());
(set 'col-types '());
; set up parameters for sqlite3_prepare() call
(set 'ppstm "\000\000\000\000\000\000\000\000") ; pointer to statement ptr
(set 'pptail "\000\000\000\000\000\000\000\000") ; pointer to statement tail
; compile the sql statment
(if db (set 'result (sqlite3_prepare db sql-str -1 ppstm pptail)))
; set up parameters for sqlite3_step() call
(if NEWLISP64
(set 'pstm (get-long ppstm))
(set 'pstm (get-int ppstm)))
; bind parameters to sql stament if necessary
(if (and (= result SQLITE_OK) sql-args)
(let (argi 0)
(dolist (entry sql-args (!= result SQLITE_OK))
(if (list? entry)
(set 'result (bind-parameter pstm (first entry) (last entry)))
(set 'result (bind-parameter pstm (++ argi) entry))
)))
)
; execute the compiled statement
(if (= result SQLITE_OK)
(while (not done)
;; execute statement until done/101 or
(set 'result (sqlite3_step pstm))
(set 'num-cols (sqlite3_column_count pstm))
(if (empty? col-names) (set 'col-names (get-names pstm num-cols)))
(set 'col-types (get-types pstm num-cols))
(if (= result SQLITE_ROW)
(push (get-values pstm num-cols) sqarray -1)
(set 'done true) ;; received done/101 or error
))
)
; if done/101 finalize
(if (= result SQLITE_DONE)
(begin
(set 'result (sqlite3_finalize pstm))
; for 'select' statements return the array else 'true'
(if (> num-cols 0) sqarray true))
(if (= result 0) true (set-error))))
(define (bind-parameter pstm param value)
(let (idx param)
(unless (integer? param)
(set 'idx (sqlite3_bind_parameter_index pstm
(if (symbol? param) (term param) (string param)))))
(cond
((float? value) (sqlite3_bind_double pstm idx (float value)))
;((string? value) (sqlite3_bind_blob pstm idx value (length value) -1))
((string? value) (sqlite3_bind_text pstm idx value (length value) -1))
((nil? value) (sqlite3_bind_null pstm idx))
(true (sqlite3_bind_text pstm idx (string value) (length (string value)) -1)) )) )
(define (get-values pstm cols)
(set 'row '())
(dotimes (idx cols)
(set 'i (int idx)) ; all loop vars are float
(case (nth idx col-types idx)
; (SQLITE_INTEGER
; (push (sqlite3_column_int pstm i) row -1))
; fixed for 64-bit, thanks Dmitry
(SQLITE_INTEGER
(set 'pstr (sqlite3_column_text pstm i))
(if (= pstr 0)
(push nil row -1)
(push (int (get-string pstr)) row -1)))
(SQLITE_FLOAT
(set 'pstr (sqlite3_column_text pstm i))
(if (= pstr 0)
(push nil row -1)
(push (float (get-string pstr)) row -1)))
(SQLITE_TEXT
(set 'pstr (sqlite3_column_text pstm i))
(if (= pstr 0)
(push "" row -1)
(push (get-string pstr) row -1)))
(SQLITE_BLOB
(set 'pstr (sqlite3_column_blob pstm i))
(set 'len (sqlite3_column_bytes pstm i))
(set 'buff (dup "\000" len))
(if (= pstr 0)
(push "" row -1)
(begin
(cpymem pstr buff len)
(push buff row -1))))
(SQLITE_NULL
(push nil row -1))))
row)
(define (get-names pstm cols)
(set 'row '())
(dotimes (idx cols)
(set 'i (int idx)) ; all loop vars are float
(set 'ps (sqlite3_column_name pstm i))
(if (= ps 0) ;; check for null pointer to result
(push "" row -1)
(push (get-string ps) row -1)))
row)
(define (get-types pstm cols)
(set 'row '())
(dotimes (idx cols)
(set 'i (int idx)) ; all loop vars are float
(push (nth (sqlite3_column_type pstm i) SQLITE_TYPES) row -1))
row)
(define sql3:sql3 sql)
;; @syntax (sql3:colnames)
;; @return A list of column header names.
;; Returns a list of column header names for the last query. This is
;; a function wrapper around the internal variable <tt>sql3:col-names</tt>.
(define (colnames) col-names)
;; @syntax (sql3:rowid)
;; @return The last row id from last 'insert'.
;; Gets the id of the last row inserted.
(define (rowid)
(if db (sqlite3_last_insert_rowid db)))
;; @syntax (sql3:tables)
;; @return A list of tables in the database.
(define (tables)
(if db (begin
(set 'lst (sql "select tbl_name from sqlite_master")) ))
(if lst (set 'lst (first (transpose lst)))))
;; @syntax (sql3:columns <str-tabel-name>)
;; @return A list of column names for a table.
(define (columns aTable)
(if (list? (sql (append "select * from " aTable " where 0;")))
col-names))
;; @syntax (sql3:changes)
;; @return The Number of rows changed/affected by the last SQL statement.
(define (changes)
(if db (sqlite3_changes db)))
;; @syntax (sql3:timeout <num-milli-seconds>)
;; @return 'true' on success or 'nil' on failure.
;; Sets busy timeout in milliseconds.
(define (timeout ms)
(if db (zero? (sqlite3_busy_timeout db (int ms)))))
;; @syntax (sql3:error)
;; @return The error text of the last error occured in 'sql3:sql'.
(define (error) error-message)
(define (set-error)
(set 'result (sqlite3_errmsg db))
(if (= result 0)
(set 'error-message nil)
(set 'error-message (get-string result))
nil
)
)
(context 'MAIN)
; -------------------------------------------------------------------------
;
; test the database routines
;
; if there is an old "SQLITE3-TEST" db from an earlier sqlite 2.8 delete it first
;
(define (test-sqlite3)
(if (sql3:open "SQLITE3-TEST")
(println "database opened/created, ... Ok")
(println "problem opening/creating database"))
(if (sql3:sql "create table fruits (name CHAR(20), qty INT(3), price FLOAT(10), blobtext BLOB);")
(println "created table fruits, ... Ok")
(println "problem creating table fruits"))
(if (sql3:sql "insert into fruits values ('apples', 11, 1.234, X'41424300010101');")
(println "inserted, last row id: " (sql3:rowid) ", ... Ok")
(println "problem inserting row"))
(if (sql3:sql "insert into fruits values ('oranges', 22, 2.345, X'42434400020202');")
(println "inserted, last row id: " (sql3:rowid) ", ... Ok")
(println "problem inserting row"))
(if (sql3:sql "insert into fruits values ('bananas', 33, 3.456, X'44454600030303');")
(println "inserted, last row id: " (sql3:rowid) ", ... Ok")
(println "problem inserting row"))
; Definition of a small helper function for the tests to emulate the X'...' argument
; quoting of SQL
(define (hexstring hexstr)
(join (map (fn (s) (pack "c" (int s 0 16))) (find-all ".." hexstr))))
; Following statement was modified below to show, how to use host variables with
; the SQL INSERT statement.
; (if (sql3:sql "insert into fruits values (:name, :qty, :price, X'47484900040404');"
; '((":name" "grapes") (":qty" 123456789012345678) (":price" 7,89)))
; (println "inserted, last row id: " (sql3:rowid) ", ... Ok")
; (println "problem inserting row"))
(if (sql3:sql "insert into fruits values (?, ?, ?, ?);"
(list "grapes" 123456789012345678 (div 789 100) (hexstring "47484900040404")))
(println "inserted, last row id: " (sql3:rowid) ", ... Ok")
(println "problem inserting row: " (sql3:error)))
(set 'sqarray (sql3:sql "select * from fruits;"))
(if sqarray
(begin
(println "selected rows: ")
(map println sqarray)
(println "column names with sql3:col-names: ")
(map println (sql3:colnames))
(println "... Ok")
)
(println "problem with select"))
(if (= (sql3:sql "select name from fruits where qty < ? order by name;" '(33))
'(("apples") ("oranges")))
(println "select via host parameter (type '?'), ... Ok")
(println "problem with selecting via host parameters (type '?')"))
(if (= (sql3:sql "select name from fruits where qty < :qty order by name;" '((":qty" 33)))
'(("apples") ("oranges")))
(println "select via host parameter (type ':VVV'), ... Ok")
(println "problem with selecting via host parameters (type ':VVV')"))
(if (= (sql3:sql "select name from fruits where qty < ?2 order by name;" '(("?2" 33)))
'(("apples") ("oranges")))
(println "select via host parameter (type '?NNN'), ... Ok")
(println "problem with selecting via host parameters (type '?NNN')"))
(if (= (sql3:sql "select name from fruits where qty < @par order by name;" '(("@par" 33)))
'(("apples") ("oranges")))
(println "select via host parameter (type '@VVV'), ... Ok")
(println "problem with selecting via host parameters (type '@VVV')"))
(if (= (sql3:sql "select name from fruits where qty < $par order by name;" '(("$par" 33)))
'(("apples") ("oranges")))
(println "select via host parameter (type '$VVV'), ... Ok")
(println "problem with selecting via host parameters (type '$VVV')"))
; SQL injection has no chance:
(print "try to drop table fruits via SQL injection attack ... ")
(if (sql3:sql "select * from fruits where name = ?;" '("''; drop table fruits;"))
(println "OUCH! Table was dropped via SQL injection!!!")
(println "no luck, table was safe against SQL injection."))
(if (sql3:sql "delete from fruits where 1;")
(println "deleted, rows affected: " (sql3:changes) ", ... Ok")
(println "problem deleting rows"))
(if (list? (sql3:tables) )
(println "tables: " (sql3:tables) ", ... Ok")
(println "problem in sql3:tables"))
(if (list? (sql3:columns "fruits") )
(println "columns: " (sql3:columns "fruits") ", ... Ok")
(println "problem in sql3:columns"))
(if (sql3 "drop table fruits;")
(println "table fruits dropped, ... Ok")
(println "problem dropping table fruits"))
(sql3:close)
)
; eof ;
syntax highlighting with newLISP and newLISPdoc