;; =========================== ;; Script: sqlite3-protocol.r ;; Downloaded from: www.REBOL.org ;; =========================== REBOL [ Title: "Sqlite 3.X rebol wrapper as protocol handler" File: %sqlite3-protocol.r Author: ["Cal Dixon" "Juan-Carlos Miranda"] Date: 22-SEP-2004 Version: 0.1.2 Purpose: "Sqlite wrapper as a Rebol protocol handler." Comment: "This is mostly Juan-Carlos's sqlite3.r script with some extra code wrapped around it" License: "Public Domain" Changes: { 22/03/2005 - v0.1.2 Added trace function management thanks to the hidden callback! feature. 22/09/2004 - v0.1.1 Changed a few things in the sqlite-exec function and wrapped in all in a protocol (Cal) 21/09/2004 - v0.0.2 Added blob management using enbased strings to make my life easier, maybe not the best solution though. Added the refinement /names to sqlite-exec that allows to get results as objects (I have no use for this but who knows ...) I should consider finding another name but well, i am a lazy boy. :-) I also added a little example that is just that, an example ... 25/08/2004 - v0.0.1 Initial revision. It's a dirty piece of software, but it should work. } To-Do: { - Add management of user functions. I was already able to do some working stuff, But not yet satisfying enough to introduce it. } library: [ level: 'intermediate platform: [linux windows] type: [tool] domain: [database external-library] tested-under: [view 1.2.47.4.2 linux view 1.2.10.3.1 windows] support: none license: public-domain see-also: %sqlite3.r ] ] make root-protocol [ SQLITE_OK: 0 ;; Successful result SQLITE_ROW: 100 ;; sqlite3/step has another row ready SQLITE_DONE: 101 ;; sqlite3/step has finished executing ;; sqlite datatypes SQLITE_INTEGER: 1 SQLITE_FLOAT: 2 SQLITE_TEXT: 3 SQLITE_BLOB: 4 SQLITE_NULL: 5 sqlite3: context [ ;;; Just set this to your sql shared lib or dll path. sql: load/library either exists? %libsqlite3.so [%libsqlite3.so][%sqlite3.dll] open: make routine! [ name [string!] db-handle [struct! [[integer!]]] return: [integer!] ] sql "sqlite3_open" close: make routine! [ db [integer!] return: [integer!] ] sql "sqlite3_close" error-msg: make routine! [ db [integer!] return: [string!] ] sql "sqlite3_errmsg" prepare: make routine! [ db [integer!] dbq [string!] len [integer!] stmt [struct! [[integer!]]] dummy [struct! [[integer!]]] return: [integer!] ] sql "sqlite3_prepare" step: make routine! [ stmt [integer!] return: [integer!] ] sql "sqlite3_step" finalize: make routine! [ stmt [integer!] return: [integer!] ] sql "sqlite3_finalize" reset: make routine! [ stmt [integer!] return: [integer!] ] sql "sqlite3_reset" bind_int: make routine! [ stmt [integer!] idx [integer!] val [integer!] return: [integer!] ] sql "sqlite3_bind_int" bind_double: make routine! [ stmt [integer!] idx [integer!] val [decimal!] return: [integer!] ] sql "sqlite3_bind_int" bind_text: make routine! [ stmt [integer!] idx [integer!] val [string!] len [integer!] fn [integer!] return: [integer!] ] sql "sqlite3_bind_text" bind_blob: make routine! [ stmt [integer!] idx [integer!] val [string!] len [integer!] fn [integer!] return: [integer!] ] sql "sqlite3_bind_blob" data_count: make routine! [ stmt [integer!] return: [integer!] ] sql "sqlite3_data_count" column_count: make routine! [ stmt [integer!] return: [integer!] ] sql "sqlite3_column_count" column_name: make routine! [ stmt [integer!] idx [integer!] return: [string!] ] sql "sqlite3_column_name" column_type: make routine! [ stmt [integer!] idx [integer!] return: [integer!] ] sql "sqlite3_column_type" column_bytes: make routine! [ stmt [integer!] idx [integer!] return: [integer!] ] sql "sqlite3_column_bytes" column_int: make routine! [ stmt [integer!] idx [integer!] return: [integer!] ] sql "sqlite3_column_int" column_double: make routine! [ stmt [integer!] idx [integer!] return: [decimal!] ] sql "sqlite3_column_double" column_text: make routine! [ stmt [integer!] idx [integer!] return: [string!] ] sql "sqlite3_column_text" column_blob: make routine! [ stmt [integer!] idx [integer!] return: [string!] ] sql "sqlite3_column_blob" trace: make routine! [ db [integer!] clb [callback! [int string!]] ptr [integer!] ] sql "sqlite3_trace" ] sqlite-open: func [ "Create/Open sqlite database." name [file!] "database filename" /local tmp ][ either SQLITE_OK = sqlite3/open to-string name tmp: make struct! [p [integer!]] none [ tmp/p ][ sqlite3/error-msg tmp/p ] ] sqlite-close: func [ "Close database." db [integer!] "database handle" ][ if SQLITE_OK <> sqlite3/close db [ sqlite3/error-msg db ] ] sqlite-error: func [ "Display the error raised by SQLite." db [integer!] "database handle" ][ make error! sqlite3/error-msg db ] set 'set-tracing func [ "Set a trace function for the given SQLITE database" db [port!] "SQLITE database port" tracing-function [function!] {tracing function to install. Parameters should always be an integer! and a string! Be careful as this is not checked.} ][ sqlite3/trace db/locals/dbid :tracing-function 0 ] sqlite-exec: func [ {Execute the specified SQL query. Returns results as a block (empty if no result is to be expected.} db [integer!] "database handle" query [series!] "SQL query" cols [block!] "column names" /local i j val stmt ret col result colcount ][ result: make block! 100 either string? query [ if not find query ";" [ system/words/insert tail query ";" ] ][ if not find query/1 ";" [ system/words/insert tail query/1 ";" ] ] query: compose [(query)] either SQLITE_OK = sqlite3/prepare db first query length? first query stmt: make struct! [p [integer!]] none make struct! [[integer!]] none [ stmt: stmt/p repeat i length? next query [ ret: switch type? val: pick next query i reduce [ integer! [ sqlite3/bind_int stmt i val] decimal! [ sqlite3/bind_double stmt i val] string! [ sqlite3/bind_text stmt i val length? val 0] binary! [ sqlite3/bind_blob stmt i val: enbase val length? val 0] ] if SQLITE_OK <> ret [ sqlite-error db ] ] colcount: sqlite3/column_count stmt repeat j colcount [ system/words/insert tail cols form sqlite3/column_name stmt -1 + j ] while [SQLITE_ROW = ret: sqlite3/step stmt] [ col: make block! colcount j: 0 repeat i colcount [ system/words/insert tail col switch sqlite3/column_type stmt j reduce [ SQLITE_INTEGER [sqlite3/column_int stmt j] SQLITE_FLOAT [sqlite3/column_double stmt j] SQLITE_TEXT [sqlite3/column_text stmt j] SQLITE_BLOB [debase sqlite3/column_blob stmt j] ] j: i ] system/words/insert/only tail result col ] if SQLITE_DONE <> ret [ sqlite-error db ] sqlite3/finalize stmt ][ sqlite-error db ] result ] port-flags: system/standard/port-flags/pass-thru dbid: none sqlresult: none open: func [port][ port/locals: context [dbid: none sqlresult: none cols: system/words/copy []] port/locals/dbid: sqlite-open to-file port/target port/state/flags: port/state/flags or port-flags ] close: func [port][ sqlite-close port/locals/dbid ] insert: func [port data][ port/locals/sqlresult: sqlite-exec port/locals/dbid data port/locals/cols data ] copy: func [port][ port/locals/sqlresult ] net-utils/net-install sqlite self 0 ] example: [ print "Starting Test:" db: open sqlite://localhost/sqltest.db db-tracing: func [ctx str-out] [ print join "DB Trace >> " str-out ] set-tracing db :db-tracing insert db "CREATE TABLE t1 (a int , b text , c text)" repeat i 125 [ insert db rejoin [{INSERT INTO t1 VALUES (} i {,"cool1","cool1")}] ] insert db "SELECT * FROM t1" res: copy db insert db "DROP TABLE t1" close db probe length? res probe res/1 halt ]