source: project/release/4/sqlite3/trunk/sqlite3.scm @ 15347

;;;; sqlite3.scm
;;;; :tabSize=2:indentSize=2:noTabs=true:
;;;; bindings to the SQLite3 database library

;; Notes
;;
;; - Due to 'usual-integrations' the 'string-length' procedure is rewritten
;; to the "core" implementation. So even if the utf-8 module is "imported"
;; into the top-level a byte length will result.

(declare
  (usual-integrations)
  (fixnum-arithmetic)
  (no-procedure-checks-for-usual-bindings)
  (unused
    ; Stop annoying messages
    sqlite3#chicken_sqlite3_function_stub
    sqlite3#chicken_sqlite3_collation_stub
    sqlite3#chicken_sqlite3_final_stub
    sqlite3#chicken_sqlite3_step_stub
    ; These may have to be changed if definitions are added,
    ; removed or reordered:
    sqlite3#g166 sqlite3#g366 sqlite3#g484 sqlite3#g570)
  (bound-to-procedure
    ##sys#expand-home-path
    sqlite3#sqlite3_errmsg))

#>#include <sqlite3.h><#

(module sqlite3
  ( ;; type predicates and checks
    database?
    error-database
    check-database
    statement?
    error-statement
    check-statement
    ;; procedures
    open-database
    define-collation
    define-function
    set-busy-handler!
    make-busy-timeout
    interrupt!
    auto-committing?
    change-count
    last-insert-rowid
    finalize!
    prepare
    repair!
    reset!
    bind-parameter-count
    bind-parameter-index
    bind-parameter-name
    bind!
    bind-parameters!
    step!
    column-count
    column-type
    column-declared-type
    column-name
    column-data
    call-with-temporary-statements
    execute
    update
    first-result
    first-row
    fold-row
    for-each-row
    map-row
    with-transaction
    sql-complete?
    database-version
    database-memory-used
    database-memory-highwater
    enable-shared-cache!
  )

(import scheme chicken foreign)

(use
  srfi-1 srfi-13 srfi-18 srfi-69
  data-structures extras lolevel
  type-errors type-checks synch miscmacros matchable sql-null)

;;; Foreign types & values

;; Enumeration and constant definitions

(define-syntax %define-enum-type
  (syntax-rules ()
    [(%define-enum-type (sname cname) (sv cv) ...)
    (define-foreign-type sname
      (enum cname)
      (lambda (v)
        (case v
          [(sv) (foreign-value cv int)]
          ...
          [else
            (error-argument-type 'sname v "enumeration value")]))
            (lambda (v)
        (select v
          [((foreign-value cv int)) 'sv]
          ...
          [else
            (error-argument-type 'sname v "enumeration index")])))]))

(%define-enum-type (sqlite3:status "sqlite3_status")
  (#f                                                           "SQLITE_OK")      ; Successful result
  (error                                "SQLITE_ERROR")   ; SQL error or missing database
  (internal                                     "SQLITE_INTERNAL")        ; NOT USED. Internal logic error in SQLite
  (permission                           "SQLITE_PERM")    ; Access permission denied
  (abort                                "SQLITE_ABORT")   ; Callback routine requested an abort
  (busy                                 "SQLITE_BUSY")    ; The database file is locked
  (locked                               "SQLITE_LOCKED")          ; A table in the database is locked
  (no-memory                    "SQLITE_NOMEM")   ; A malloc() failed
  (read-only                    "SQLITE_READONLY")        ; Attempt to write a readonly database
  (interrupt                    "SQLITE_INTERRUPT")       ; Operation terminated by sqlite3_interrupt()
  (io-error                     "SQLITE_IOERR")   ; Some kind of disk I/O error occurred
  (corrupt                      "SQLITE_CORRUPT")         ; The database disk image is malformed
  (not-found                    "SQLITE_NOTFOUND")        ; NOT USED. Table or record not found
  (full                                 "SQLITE_FULL")    ; Insertion failed because database is full
  (cant-open                    "SQLITE_CANTOPEN")        ; Unable to open the database file
  (protocol                     "SQLITE_PROTOCOL")        ; NOT USED. Database lock protocol error
  (empty                                "SQLITE_EMPTY")   ; Database is empty
  (schema                               "SQLITE_SCHEMA")          ; The database schema changed
  (too-big                      "SQLITE_TOOBIG")          ; String or BLOB exceeds size limit
  (constraint                   "SQLITE_CONSTRAINT")      ; Abort due to contraint violation
  (mismatch                     "SQLITE_MISMATCH")        ; Data type mismatch
  (misuse                               "SQLITE_MISUSE")          ; Library used incorrectly
  (no-lfs                               "SQLITE_NOLFS")   ; Uses OS features not supported on host
  (authorization                "SQLITE_AUTH")    ; Authorization denied
  (format                               "SQLITE_FORMAT")          ; Auxiliary database format error
  (range                                "SQLITE_RANGE")   ; 2nd parameter to sqlite3_bind out of range
  (not-a-database               "SQLITE_NOTADB")          ; File opened that is not a database file
  (row                                  "SQLITE_ROW")             ; sqlite3_step() has another row ready
  (done                                 "SQLITE_DONE"))   ; sqlite3_step() has finished executing

(%define-enum-type (sqlite3:type "sqlite3_type")
  (integer      "SQLITE_INTEGER")
  (float                "SQLITE_FLOAT")
  (text                 "SQLITE_TEXT")
  (blob                 "SQLITE_BLOB")
  (null                 "SQLITE_NULL"))

;; Auxiliary types

(define-foreign-type sqlite3:context
  (c-pointer "sqlite3_context"))

(define-foreign-type sqlite3:value
  (c-pointer "sqlite3_value"))

;; Types for databases and statements

(define-record-type sqlite3:database
  (make-database ptr busy-handler)
  database?
  (ptr database-ptr database-ptr-set!)
  (busy-handler database-busy-handler database-busy-handler-set!))

(define-record-printer (sqlite3:database db out)
  (display
    (if (database-ptr db)
      "#<sqlite3:database>"
      "#<sqlite3:database zombie>")
    out))

(define-check+error-type database)

(define-foreign-type sqlite3:database
  (nonnull-c-pointer "sqlite3")
  database-ptr
  (cut make-database <> #f))

(define-record-type sqlite3:statement
  (make-statement ptr database sql)
  statement?
  (ptr statement-ptr statement-ptr-set!)
  (database statement-database)
  (sql statement-sql))

(define-record-printer (sqlite3:statement stmt out)
  (display
    (if (statement-ptr stmt)
      (sprintf "#<sqlite3:statement sql=~s>" (statement-sql stmt))
      "#<sqlite3:statement zombie>")
    out))

(define-check+error-type statement)

(define-foreign-type sqlite3:statement
  (nonnull-c-pointer "sqlite3_stmt")
  statement-ptr
  (cut make-statement <> #f #f))

;;; Helpers

;; Conditions

(define (make-exn-condition loc msg . args)
  (make-property-condition 'exn 'location loc 'message msg 'arguments args))

(define (make-sqlite3-condition sta)
  (make-property-condition 'sqlite3 'status sta))

(define (make-sqlite3-error-condition loc msg sta . args)
  (make-composite-condition
    (apply make-exn-condition loc msg args)
    (make-sqlite3-condition sta)))

(define (make-no-data-condition loc stmt params)
  (make-sqlite3-error-condition loc
    "the statement returned no data"
    'done
    stmt params))

;; Errors
(define ((abort-sqlite3-error loc db . args) sta)
  (abort
    (apply make-sqlite3-error-condition
      loc
      (if db (sqlite3_errmsg db) (symbol->string sta))
      sta
      args)))

(define (print-error msg obj)
  (print-error-message obj (current-error-port) (string-append "Error: " msg)))

;; Tree dictionary

(define (make-hash-table-tree/synch id . args)
  (make-object/synch (apply make-hash-table args) id))

(define (hash-table-tree-set! ht-tree keys value)
  (if (null? (cdr keys))
    (hash-table-set! ht-tree (car keys) value)
    (hash-table-update! ht-tree
      (car keys)
      (cute hash-table-tree-set! <> (cdr keys) value)
      (cut make-hash-table)))
  ht-tree)

(define (hash-table-tree-delete! ht-tree keys)
  (if (null? (cdr keys))
    (hash-table-delete! ht-tree (car keys))
    (hash-table-update! ht-tree
      (car keys)
      (cute hash-table-tree-delete! <> (cdr keys))
      (cut make-hash-table)))
  ht-tree)

(define (hash-table-tree-ref
          ht-tree keys
          #!optional
          (thunk (cut abort
            (make-composite-condition
              (make-exn-condition 'hash-table-tree-ref
                "hash-table-tree does not contain path"
                ht-tree keys)
              (make-property-condition 'access)))))
  (let/cc return
    (let loop ([ht ht-tree] [keys keys])
      (if (null? keys)
  ht
  (loop (hash-table-ref ht (car keys) (cut return (thunk)))
    (cdr keys))))))

(define (hash-table-tree-ref/default ht-tree keys default)
  (hash-table-tree-ref ht-tree keys (lambda () default)))

(define (hash-table-tree-clear! htt id elt-clear)
  (cond [(hash-table-ref/default htt id #f)
    => (cute hash-table-walk <> elt-clear)])
  (hash-table-delete! htt id))

;; SQL collation sequence interface

(define *collations* (make-hash-table-tree/synch 'sqlite3:collations))

(define-external (chicken_sqlite3_collation_stub
                  (scheme-object qn) (int la)
                  (c-pointer da) (int lb)
                  (c-pointer db)) int
  (let/cc return
    (let ([r #f])
      (dynamic-wind
        noop
        (lambda ()
          (handle-exceptions exn
            (print-error "in collation function" exn)
            (let ([a (make-string la)]
                  [b (make-string lb)])
              (move-memory! da a la)
              (move-memory! db b lb)
              (set! r
                ((vector-ref
                  (call-with/synch *collations*
                    (cute hash-table-tree-ref <> qn))
                  1)
                  a b)))))
        (lambda ()
          (if (fixnum? r)
            (return r)
            (begin
              (print-error "in collation function: invalid return value" (->string r))
              (return 0))))))))

(define sqlite3_create_collation
  (foreign-lambda* sqlite3:status
    ((sqlite3:database db) (c-string name) (scheme-object qn))
#<<EOS
    if (qn == C_SCHEME_FALSE)
    return(sqlite3_create_collation(db, name, SQLITE_UTF8, NULL, NULL));
    else
    return(sqlite3_create_collation(db, name, SQLITE_UTF8,
            (void *)qn,
            (int (*)(void *,
                int, const void *,
                int, const void *))
            &chicken_sqlite3_collation_stub));
EOS
    ))

(define (define-collation db name #!optional proc)
  (check-database 'define-collation db)
  (check-string 'define-collation name)
  (if proc
    (begin
      (check-procedure 'define-collation proc)
      (let ([qn (object-evict (list (pointer->address (database-ptr db)) name))])
  (cond
    [(sqlite3_create_collation db name qn)
      => (lambda (s)
    (object-release qn)
    ((abort-sqlite3-error 'define-collation db name proc) s))]
    [else
      (call-with/synch *collations*
        (cute hash-table-tree-set! <> qn (vector qn proc)))])))
    (cond
      [(sqlite3_create_collation db name #f)
        => (abort-sqlite3-error 'define-collation db name)]
      [else
        (let ([qn (list (pointer->address (database-ptr db)) name)])
          (call-with/synch *collations*
            (lambda (col)
              (cond [(hash-table-tree-ref/default col qn #f)
                => (lambda (info)
                    (hash-table-tree-delete! col qn)
                    (object-release (vector-ref info 0)))]))))])))

;;; SQL function interface

(define *functions* (make-hash-table-tree/synch 'sqlite3:functions))

(define *seeds* (make-hash-table-tree/synch 'sqlite3:seeds))

(define (parameter-data n args)
  (let loop ([i 0])
    (if (<= n i)
      '()
      (cons (case ((foreign-lambda* sqlite3:type
          (((c-pointer sqlite3:value) args) (int i))
          "return(sqlite3_value_type(args[i]));")
        args i)
        [(integer)
          ((foreign-lambda* integer
              (((c-pointer sqlite3:value) args) (int i))
              "return(sqlite3_value_double(args[i]));")
            args i)]
        [(float)
          ((foreign-lambda* double
              (((c-pointer sqlite3:value) args) (int i))
              "return(sqlite3_value_double(args[i]));")
            args i)]
        [(text)
          ((foreign-primitive scheme-object
              (((c-pointer sqlite3:value) args) (int i))
              "int n = sqlite3_value_bytes(args[i]);"
              "C_word *s = C_alloc(C_SIZEOF_STRING(n));"
              "return(C_string(&s, n, (char *)sqlite3_value_text(args[i])));")
            args i)]
        [(blob)
          ((foreign-primitive scheme-object
              (((c-pointer sqlite3:value) args) (int i))
              "int n = sqlite3_value_bytes(args[i]);"
              "C_word *s = C_alloc(C_SIZEOF_STRING(n));"
              "return(C_bytevector(&s, n, (char *)sqlite3_value_blob(args[i])));")
            args i)]
        [else
          (sql-null)])
  (loop (add1 i))))))

(define (set-result! ctx v)
  (cond
    [(blob? v)
      ((foreign-lambda* void
          ((sqlite3:context ctx) (scheme-pointer v) (int n))
          "sqlite3_result_blob(ctx, v, n, SQLITE_TRANSIENT);")
        ctx v (blob-size v))]
    [(or (and (fixnum? v) v) (and (boolean? v) (if v 1 0)))
      => (lambda (v)
          ((foreign-lambda void "sqlite3_result_int" sqlite3:context int)
            ctx v))]
    [(real? v)
      ((foreign-lambda void "sqlite3_result_double" sqlite3:context double)
        ctx v)]
    [(string? v)
      ((foreign-lambda* void
          ((sqlite3:context ctx) (scheme-pointer v) (int n))
          "sqlite3_result_text(ctx, v, n, SQLITE_TRANSIENT);")
        ctx v (string-length v))]
    [(sql-null? v)
      ((foreign-lambda void "sqlite3_result_null" sqlite3:context)
        ctx)]
    [else
      (error-argument-type 'set-result! v "blob, number, boolean, string or sql-null")]))

(define sqlite3_user_data
  (foreign-lambda scheme-object "sqlite3_user_data" sqlite3:context))

(define-external (chicken_sqlite3_function_stub
      (c-pointer ctx) (int n) (c-pointer args)) void
  (let/cc return
    (dynamic-wind
      noop
      (lambda ()
        (handle-exceptions exn
          (print-error "in SQL function" exn)
          (set-result!
            ctx
            (apply (vector-ref
                      (call-with/synch *functions*
                        (cute hash-table-tree-ref <> (sqlite3_user_data ctx)))
                      1)
              (parameter-data n args)))))
      (lambda ()
        (return (void))))))

(define sqlite3_aggregate_context
  (foreign-lambda* integer ((sqlite3:context ctx))
    "return((int)sqlite3_aggregate_context(ctx, 1));"))

(define-external (chicken_sqlite3_step_stub
      (c-pointer ctx) (int n) (c-pointer args)) void
  (let/cc return
    (dynamic-wind
      noop
      (lambda ()
        (handle-exceptions exn
          (print-error "in step of SQL function" exn)
          (let ([info (call-with/synch *functions*
                        (cute hash-table-tree-ref <> (sqlite3_user_data ctx)))])
            (call-with/synch *seeds*
              (cute hash-table-update!/default
                <>
                (sqlite3_aggregate_context ctx)
                (lambda (seed)
                  (apply (vector-ref info 1) seed (parameter-data n args)))
                (vector-ref info 2))))))
      (lambda ()
        (return (void))))))

(define-external (chicken_sqlite3_final_stub (c-pointer ctx))
  void
  (let/cc return
    (let ([agc (sqlite3_aggregate_context ctx)])
      (dynamic-wind
        noop
        (lambda ()
          (handle-exceptions exn
            (print-error "in final of SQL function" exn)
            (let ([info (call-with/synch *functions*
            (cute hash-table-tree-ref <> (sqlite3_user_data ctx)))])
              (cond
          [((vector-ref info 3)
            (call-with/synch *seeds*
              (cute hash-table-ref/default <> agc (vector-ref info 2))))
            => (cute set-result! ctx <>)]
          [else
            (set-result! ctx (sql-null))]))))
        (lambda ()
          (call-with/synch *seeds*
            (cute hash-table-delete! <> agc))
          (return (void)))))))

(define define-function
  (case-lambda
    [(db name n proc)
      (check-database 'define-function db)
      (check-string 'define-function name)
      (check-cardinal-number 'define-function n)
      (check-procedure 'define-function proc)
      (let ([qn (object-evict (list (pointer->address (database-ptr db)) name))])
        (cond
          [((foreign-lambda* sqlite3:status
              ((sqlite3:database db)
                (c-string name) (int n) (scheme-object qn))
#<<EOS
            return(sqlite3_create_function(db, name, n, SQLITE_UTF8,
                    (void *)qn,
                    (void (*)(sqlite3_context *, int,
                        sqlite3_value **))
                    &chicken_sqlite3_function_stub,
                    NULL,
                    NULL));
EOS
      )
      db name n qn)
      => (lambda (s)
          (object-release qn)
          ((abort-sqlite3-error 'define-function db name n proc) s))]
    [else
      (call-with/synch *functions*
        (cute hash-table-tree-set! <> qn (vector qn proc)))]))]
    [(db name n step-proc seed . final-proc)
      (check-database 'define-function db)
      (check-string 'define-function name)
      (check-cardinal-number 'define-function n)
      (let ([final-proc (optional final-proc identity)])
        (check-procedure 'define-function step-proc)
        (check-procedure 'define-function final-proc)
        (let ([qn (object-evict (list (pointer->address (database-ptr db)) name))])
          (cond
            [((foreign-lambda* sqlite3:status
                ((sqlite3:database db)
                  (c-string name) (int n) (scheme-object qn))
#<<EOS
                return(sqlite3_create_function(db, name, n, SQLITE_UTF8,
                          (void *)qn,
                          NULL,
                          (void (*)(sqlite3_context *,
                              int, sqlite3_value **))
                          &chicken_sqlite3_step_stub,
                          (void (*)(sqlite3_context *))
                          &chicken_sqlite3_final_stub));
EOS
              )
              db name n qn)
              => (lambda (s)
                  (object-release qn)
                  ((abort-sqlite3-error
                      'define-function db name n step-proc seed final-proc) s))]
            [else
              (call-with/synch *functions*
                (cute hash-table-tree-set! <> qn (vector qn step-proc seed final-proc)))])))]))

;;; Database interface

;; Get any error message
(define sqlite3_errmsg
  (foreign-lambda c-string "sqlite3_errmsg" sqlite3:database))

;; Open a database
(define (open-database path)
  (check-string 'open-database path)
  (let-location ([db c-pointer])
    (cond
      [((foreign-lambda sqlite3:status "sqlite3_open"
          nonnull-c-string (c-pointer sqlite3:database))
        (##sys#expand-home-path path) #$db)
        => (abort-sqlite3-error 'open-database #f path)]
      [else
        (make-database db #f)])))

;; Set application busy handler.  Does not use a callback, so it is safe
;; to yield.  Handler is called with DB, COUNT and LAST (the last value
;; it returned).  Return true value to continue trying, or #f to stop.
(define (set-busy-handler! db handler)
  (check-database 'set-busy-handler! db)
  (database-busy-handler-set! db handler))

;; Returns a closure suitable for use with set-busy-handler!.  Identical
;; to sqlite's default busy handler, but does not block.
(define (make-busy-timeout timeout)
  (define (thread-sleep!/ms ms)
    (thread-sleep!
      (milliseconds->time (+ ms (current-milliseconds)))))
  (let* ([delays '#(1 2 5 10 15 20 25 25 25 50 50 100)]
   [totals '#(0 1 3  8 18 33 53 78 103 128 178 228)]
   [ndelay (vector-length delays)])
    (lambda (db count)
      (let* ([delay (vector-ref delays (min count (- ndelay 1)))]
       [prior (if (< count ndelay)
         (vector-ref totals count)
         (+ (vector-ref totals (- ndelay 1))
           (* delay (- count (- ndelay 1)))))])
  (let ([delay (if (> (+ prior delay) timeout)
                (- timeout prior)
                delay)])
    (cond
      [(<= delay 0) #f]
      [else
        (thread-sleep!/ms delay)
        #t]))))))

;; Cancel any running database operation as soon as possible
(define (interrupt! db)
  (check-database 'interrupt! db)
  ((foreign-lambda void "sqlite3_interrupt" sqlite3:database) db))

;; Check whether the database is in autocommit mode
(define (auto-committing? db)
  (check-database 'auto-committing? db)
  ((foreign-lambda bool "sqlite3_get_autocommit" sqlite3:database) db))

;; Get the number of changes made to the database
(define (change-count db #!optional (total #f))
  (check-database 'change-count db)
  (if total
    ((foreign-lambda number "sqlite3_total_changes" sqlite3:database) db)
    ((foreign-lambda number "sqlite3_changes" sqlite3:database) db)))

;; Get the row ID of the last inserted row
(define (last-insert-rowid db)
  (check-database 'last-insert-rowid db)
  ((foreign-lambda number "sqlite3_last_insert_rowid" sqlite3:database) db))

;; Close a database or statement handle
(define finalize!
  (match-lambda
    [(? database? db)
      (cond
        [(not (database-ptr db))
          (void)]
        [((foreign-safe-lambda sqlite3:status "sqlite3_close" sqlite3:database) db)
          => (abort-sqlite3-error 'finalize! db db)]
        [else
          (let ([id (pointer->address (database-ptr db))]
              [release-qns (lambda (_ info) (object-release (vector-ref info 0)))])
            (call-with/synch *collations*
              (cute hash-table-tree-clear! <> id release-qns))
            (call-with/synch *functions*
              (cute hash-table-tree-clear! <> id release-qns)))
          (database-ptr-set! db #f)
          (database-busy-handler-set! db #f)])]
    [(? statement? stmt)
      (cond
        [(not (statement-ptr stmt))
          (void)]
        [((foreign-lambda sqlite3:status "sqlite3_finalize" sqlite3:statement) stmt)
          => (abort-sqlite3-error 'finalize! (statement-database stmt) stmt)]
        [else
          (statement-ptr-set! stmt #f)])]))

;;; Statement interface

;; Create a new statement
(define (prepare db sql)
  (check-database 'prepare db)
  (check-string 'prepare sql)
  (let retry ([retries 0])
    (let-location ([stmt c-pointer] [tail c-string])
      (cond
        [((foreign-safe-lambda sqlite3:status "sqlite3_prepare"
            sqlite3:database scheme-pointer int
            (c-pointer sqlite3:statement)
            (c-pointer (const c-string)))
          db (string-append sql "\x00") (string-length sql) #$stmt #$tail)
          => (lambda (err)
              (case err
                [(busy)
                  (let ([h (database-busy-handler db)])
                    (cond
                      [(and h (h db retries))
                        (retry (add1 retries))]
                      [else
                        ((abort-sqlite3-error 'prepare db db sql) err)]))]
                [else
                  ((abort-sqlite3-error 'prepare db db sql) err)]))]
        [else
          (values
            (make-statement stmt db sql)
            tail)]))))

;; Recompile an existing statement and transfer all bindings
(define (repair! stmt)
  (check-statement 'repair! stmt)
  (let ([fresh (prepare
    (statement-database stmt)
    (statement-sql stmt))])
    (dynamic-wind
      noop
      (lambda ()
        (let ([old (statement-ptr stmt)]
              [new (statement-ptr fresh)])
          (cond
            [((foreign-lambda sqlite3:status "sqlite3_transfer_bindings"
                c-pointer c-pointer)
              old new)
              => (abort-sqlite3-error 'repair! (statement-database stmt) stmt)]
            [else
              (statement-ptr-set! stmt new)
              (statement-ptr-set! fresh old)])))
      (lambda ()
        (finalize! fresh)))))

;; Reset an existing statement to process it again
(define sqlite3_reset
  (foreign-lambda sqlite3:status "sqlite3_reset" sqlite3:statement))

(define (reset! stmt)
  (check-statement 'reset! stmt)
  (cond [(sqlite3_reset stmt)
    => (abort-sqlite3-error 'reset! (statement-database stmt) stmt)]))

;; Get number of bindable parameters
(define (bind-parameter-count stmt)
  (check-statement 'bind-parameter-count stmt)
  ((foreign-lambda int "sqlite3_bind_parameter_count" sqlite3:statement) stmt))

;; Get index of a bindable parameter or #f if no parameter with the
;; given name exists
(define (bind-parameter-index stmt name)
  (check-statement 'bind-parameter-index stmt)
  (let ([i ((foreign-lambda int "sqlite3_bind_parameter_index"
              sqlite3:statement nonnull-c-string)
            stmt name)])
    (if (zero? i)
      #f
      (sub1 i))))

;; Get the name of a bindable parameter
(define (bind-parameter-name stmt i)
  (check-statement 'bind-parameter-name stmt)
  ((foreign-lambda c-string "sqlite3_bind_parameter_name" sqlite3:statement int)
    stmt (add1 i)))

;; Bind data as parameters to an existing statement

(define (bind! stmt i v)
  (check-statement 'bind! stmt)
  (check-cardinal-integer 'bind! i)
  (cond
    [(blob? v)
      (cond [((foreign-lambda* sqlite3:status
                ((sqlite3:statement stmt) (int i) (scheme-pointer v) (int n))
                "return(sqlite3_bind_blob(stmt, i, v, n, SQLITE_TRANSIENT));")
              stmt (add1 i) v (blob-size v))
        => (abort-sqlite3-error 'bind! (statement-database stmt) stmt i v)])]
    [(or (and (fixnum? v) v) (and (boolean? v) (if v 1 0)))
      => (lambda (v)
          (cond [((foreign-lambda sqlite3:status "sqlite3_bind_int"
                    sqlite3:statement int int)
                  stmt (add1 i) v)
            => (abort-sqlite3-error 'bind! (statement-database stmt) stmt i v)]))]
    [(real? v)
      (cond [((foreign-lambda sqlite3:status "sqlite3_bind_double"
                sqlite3:statement int double)
              stmt (add1 i) v)
        => (abort-sqlite3-error 'bind! (statement-database stmt) stmt i v)])]
    [(string? v)
      (cond [((foreign-lambda* sqlite3:status
                ((sqlite3:statement stmt) (int i) (scheme-pointer v) (int n))
                "return(sqlite3_bind_text(stmt, i, v, n, SQLITE_TRANSIENT));")
              stmt (add1 i) v (string-length v))
        => (abort-sqlite3-error 'bind! (statement-database stmt) stmt i v)])]
    [(sql-null? v)
      (cond [((foreign-lambda sqlite3:status "sqlite3_bind_null" sqlite3:statement int)
              stmt (add1 i))
        => (abort-sqlite3-error 'bind! (statement-database stmt) stmt i)])]
    [else
      (error-argument-type 'bind! v "blob, number, boolean, string or sql-null")]))

; Helper

(define (%bind-parameters! loc stmt params)
  (reset! stmt)
  (let ([cnt (bind-parameter-count stmt)]
  [vs (make-hash-table)])
    (let loop ([i 0] [params params])
      (match params
        [((? keyword? k) v . rest)
          (cond
            [(bind-parameter-index stmt (string-append ":" (keyword->string k)))
              => (lambda (j)
             (hash-table-set! vs j v)
             (loop i rest))]
            [else
              (error-argument-type loc k "value or keyword matching a bind parameter name")])]
        [(v . rest)
          (hash-table-set! vs i v)
          (loop (add1 i) rest)]
        [()
          (void)]))
    (if (= (hash-table-size vs) cnt)
      (unless (zero? cnt)
        (hash-table-walk vs (cut bind! stmt <> <>)))
      (abort
        (make-composite-condition
          (make-exn-condition
            loc
            (conc "bad parameter count - received " (hash-table-size vs) " but expected " cnt))
          (make-property-condition 'arity)
          (make-sqlite3-condition 'error))))))

(define (bind-parameters! stmt . params)
  (%bind-parameters! 'bind-parameters! stmt params))

;; Single-step a prepared statement, return #t if data is available,
;; #f otherwise
(define sqlite3_step
  (foreign-safe-lambda sqlite3:status "sqlite3_step" sqlite3:statement))
(define (step! stmt)
  (check-statement 'step! stmt)
  (let ([db (statement-database stmt)])
    (let retry ([retries 0])
      (define (busy-retry s)
  (let ([h (database-busy-handler db)])
    (cond
      [(and h (h db retries))
        (retry (add1 retries))]
      [else
        ((abort-sqlite3-error 'step! db stmt) s)])))
      (let ([s (sqlite3_step stmt)])
  (case s
    [(row)
      #t]
    [(done)
      #f]
    [(error)
      (let ([s (sqlite3_reset stmt)])
        (case s
    [(schema)
      (repair! stmt)
      (retry retries)]
    [(busy)             ; Passed thru sometimes (e.g. ATTACH).
      (busy-retry s)]
    [else
      ((abort-sqlite3-error 'step! db stmt) s)]))]
    [(busy)
      (busy-retry s)]
    [else
      ((abort-sqlite3-error 'step! db stmt) s)])))))

;; Retrieve information from a prepared/stepped statement
(define (column-count stmt)
  (check-statement 'column-count stmt)
  ((foreign-lambda int "sqlite3_column_count" sqlite3:statement) stmt))

(define (column-type stmt i)
  (check-statement 'column-type stmt)
  ((foreign-lambda sqlite3:type "sqlite3_column_type" sqlite3:statement int) stmt i))

(define (column-declared-type stmt i)
  (check-statement 'column-declared-type stmt)
  ((foreign-lambda c-string "sqlite3_column_decltype" sqlite3:statement int) stmt i))

(define (column-name stmt i)
  (check-statement 'column-name stmt)
  ((foreign-lambda c-string "sqlite3_column_name" sqlite3:statement int) stmt i))

;; Retrieve data from a stepped statement
(define (column-data stmt i)
  (case (column-type stmt i)
    [(integer)
      (if (and-let* ([type (column-declared-type stmt i)])
            (string-contains-ci type "bool"))
        ((foreign-lambda bool "sqlite3_column_int" sqlite3:statement int) stmt i)
        ((foreign-lambda integer "sqlite3_column_double" sqlite3:statement int) stmt i))]
    [(float)
      ((foreign-lambda double "sqlite3_column_double" sqlite3:statement int) stmt i)]
    [(text)
      ((foreign-primitive scheme-object ((sqlite3:statement stmt) (int i))
          "int n = sqlite3_column_bytes(stmt, i);"
          "C_word *s = C_alloc(C_SIZEOF_STRING(n));"
          "return(C_string(&s, n, (char *)sqlite3_column_text(stmt, i)));")
        stmt i)]
    [(blob)
      ((foreign-primitive scheme-object ((sqlite3:statement stmt) (int i))
          "int n = sqlite3_column_bytes(stmt, i);"
          "C_word *s = C_alloc(C_SIZEOF_STRING(n));"
          "return(C_bytevector(&s, n, (char *)sqlite3_column_blob(stmt, i)));")
        stmt i)]
    [else
      (sql-null)]))

;;; Easy statement interface

;; Compile a statement and call a procedure on it, then finalize the
;; statement in a dynamic-wind exit block if it hasn't been finalized yet.
(define (call-with-temporary-statements proc db . sqls)
  (check-database 'call-with-temporary-statements db)
  (let ([stmts #f] [exn #f])
    (dynamic-wind
      (lambda ()
        (unless stmts
          (set! stmts (map (cute prepare db <>) sqls))))
      (lambda ()
        (handle-exceptions e (set! exn e)
          (apply proc stmts)))
      (lambda ()
        (and-let* ([s stmts])
          (set! stmts #f)
          (for-each finalize! s)) ;; leaks if error occurs before last stmt
        (and-let* ([e exn])
          (set! exn #f)
          (signal e))))))

(define-syntax %define/statement+params
  (syntax-rules ()
    [(%define/statement+params ((name loc) (init ...) (stmt params))
       body ...)
      (define name
        (let ([impl (lambda (init ... stmt params) body ...)])
          (lambda (init ... db-or-stmt . params)
            (cond
              [(database? db-or-stmt)
                (call-with-temporary-statements
                  (cute impl init ... <> (cdr params))
                  db-or-stmt (car params))]
              [(statement? db-or-stmt)
                (impl init ... db-or-stmt params)]
              [else
                (error-argument-type loc db-or-stmt "database or statement")]))))]
    [(%define/statement+params (name (init ...) (stmt params))
       body ...)
      (%define/statement+params ((name 'name) (init ...) (stmt params))
        body ...)]
    [(%define/statement+params (name stmt params)
       body ...)
      (%define/statement+params ((name 'name) () (stmt params))
        body ...)]))

;; Step through a statement and ignore possible results
(define (%execute loc stmt params)
  (%bind-parameters! loc stmt params)
  (while (step! stmt))
  (void))

(%define/statement+params (execute stmt params)
  (%execute 'execute stmt params))

;; Step through a statement, ignore possible results and return the
;; count of changes performed by this statement
(%define/statement+params (update stmt params)
  (%execute 'update stmt params)
  (change-count (statement-database stmt)))

;; Return only the first column of the first result row produced by this
;; statement

(%define/statement+params (first-result stmt params)
  (%bind-parameters! 'first-result stmt params)
  (if (step! stmt)
    (let ([r (column-data stmt 0)])
      (reset! stmt)
      r)
    (abort (make-no-data-condition 'first-result stmt params))))

;; Return only the first result row produced by this statement as a list

(%define/statement+params (first-row stmt params)
  (%bind-parameters! 'first-row stmt params)
  (if (step! stmt)
    (map (cute column-data stmt <>)
      (iota (column-count stmt)))
    (abort (make-no-data-condition 'first-row stmt params))))

;; Apply a procedure to the values of the result columns for each result row
;; while executing the statement and accumulating results.

(%define/statement+params ((%fold-row loc) (loc proc init) (stmt params))
  (%bind-parameters! loc stmt params)
  (let ([cl (iota (column-count stmt))])
    (let loop ([acc init])
      (if (step! stmt)
  (loop (apply proc acc (map (cute column-data stmt <>) cl)))
  acc))))

(define (fold-row proc init db-or-stmt . params)
  (check-procedure 'fold-row proc)
  (apply %fold-row 'fold-row proc init db-or-stmt params))

;; Apply a procedure to the values of the result columns for each result row
;; while executing the statement and discard the results

(define (for-each-row proc db-or-stmt . params)
  (check-procedure 'for-each-row proc)
  (apply %fold-row
    'for-each-row
    (lambda (acc . columns)
      (apply proc columns))
    (void)
    db-or-stmt params))

;; Apply a procedure to the values of the result columns for each result row
;; while executing the statement and accumulate the results in a list

(define (map-row proc db-or-stmt . params)
  (check-procedure 'map-row proc)
  (reverse!
    (apply %fold-row
      'map-row
      (lambda (acc . columns)
        (cons (apply proc columns) acc))
      '()
      db-or-stmt params)))

;;; Utility procedures

;; Run a thunk within a database transaction, commit if return value is
;; true, rollback if return value is false or the thunk is interrupted by
;; an exception
(define (with-transaction db thunk #!optional (type 'deferred))
  (check-database 'with-transaction db)
  (check-procedure 'with-transaction thunk)
  (unless (memq type '(deferred immediate exclusive))
    (abort
      (make-composite-condition
        (make-exn-condition 'with-transaction
          "bad argument: expected deferred, immediate or exclusive"
          type)
        (make-property-condition 'type))))
  (let ([success? #f] [exn #f])
    (dynamic-wind
      (lambda ()
        (execute db
          (string-append "BEGIN " (symbol->string type) " TRANSACTION;")))
      (lambda ()
        (handle-exceptions e (begin
                               (print-error "with-transaction" exn)
                               (set! exn e))
          (set! success? (thunk))
          success?))
      (lambda ()
        (execute db
          (if success?
            "COMMIT TRANSACTION;"
            "ROLLBACK TRANSACTION;"))
        (and-let* ([e exn])
          (set! exn #f)
          (signal e))))))

;; Check if the given string is a valid SQL statement
(define sql-complete?
  (foreign-lambda bool "sqlite3_complete" nonnull-c-string))

;; Return a descriptive version string
(define database-version
  (foreign-lambda c-string "sqlite3_libversion"))

;; Return the amount of memory currently allocated by the database
(define database-memory-used
  (foreign-lambda integer "sqlite3_memory_used"))

;; Return the maximum amount of memory allocated by the database since
;; the counter was last reset
(define (database-memory-highwater #!optional reset?)
  ((foreign-lambda integer "sqlite3_memory_highwater" bool) reset?))

;; Enables (disables) the sharing of the database cache and schema data
;; structures between connections to the same database.
(define (enable-shared-cache! enable?)
  (cond
    [((foreign-lambda sqlite3:status "sqlite3_enable_shared_cache" bool) enable?)
      => (abort-sqlite3-error 'enable-shared-cache! #f)]))

)