(use test)

(use (prefix dbi dbi:))
;;(load "../dbi.scm")
;;(import (prefix dbi dbi:))

(define backend-openers
  `((sqlite3 . ((dbname . ":memory:")))
    ;; Same convention as the postgresql egg's tests: the UNIX user
    ;; running these tests needs access to a database called "test".
    ;; This can be arranged through any combination of settings in
    ;; pg_hba.conf, ~/.pgpass and/or PG* environment variables.  This
    ;; means we don't need to hardcode anything else.  We could omit
    ;; the dbname too, but then we'd run the risk of connecting to the
    ;; user's default database and potentially messing up a real db.
    (pg . ((dbname   . "test")))
    ;; Use ~/.my.cnf to achieve the same with MySQL
    (mysql . ((dbname . "test")))))

(test-begin "dbi for all supported backends")

(for-each
 (lambda (opener)
   (let* ((dbtype (car opener))
          (dbinit (cdr opener))
          (strings-only (memq dbtype '(mysql))))

     (test-begin (symbol->string dbtype))

     ;; Set up a table and insert one row of data
     (define db (handle-exceptions exn #f (dbi:open dbtype dbinit)))

     (if (not db)
         ;; This is a bit of a hack :)
         (test-assert "Skipping tests, egg is not installed or connection cannot be made." (not db))
         (begin

           (test "db-dbtype" dbtype (dbi:db-dbtype db))

           (case dbtype
             ((pg)
              (dbi:exec db (conc "CREATE TEMPORARY TABLE users "
                                 "(id SERIAL, name TEXT);")))
             ((mysql)
              (dbi:exec db (conc "CREATE TEMPORARY TABLE users "
                                 "(id INTEGER PRIMARY KEY AUTO_INCREMENT,"
                                 " name TEXT);")))
             (else
              (dbi:exec db (conc "CREATE TEMPORARY TABLE users "
                                 "(id INTEGER PRIMARY KEY, name TEXT);"))))
           (dbi:exec db "INSERT INTO users (name) VALUES (?)" "Matt")
           (dbi:exec db "INSERT INTO users (name) VALUES (?)" "Peter")
           
           (test-begin "row accessors")
           
           (let ((result '()))
             (dbi:for-each-row 
              (lambda (tuple) (set! result (cons tuple result)))
              db "SELECT * FROM users ORDER BY name")
             
             (test "for-each-row result is the rows which we inserted"
                   (if strings-only
                       '(#("1" "Matt")
                         #("2" "Peter"))
                       '(#(1 "Matt")
                         #(2 "Peter")))
                   (reverse result)))
           
           (test "get-rows returns all the rows"
                 (if strings-only
                     '(#("1" "Matt")
                       #("2" "Peter"))
                     '(#(1 "Matt")
                       #(2 "Peter")))
                 (dbi:get-rows db "SELECT * FROM users"))
           
           (test "get-one-row returns the single row as well"
                 (if strings-only
                     '#("1" "Matt")
                     '#(1 "Matt"))
                 (dbi:get-one-row
                  db "SELECT * FROM users WHERE name = 'Matt'"))
           
           (test "get-one-row with no match returns #f"
                 #f
                 (dbi:get-one-row
                  db "SELECT * FROM users WHERE name = 'John'"))
           
           (test "get-one returns the value from the first row"
                 "Matt"
                 (dbi:get-one db "SELECT name FROM users ORDER BY name"))
           
           (test "get-one with no match returns #f"
                 #f
                 (dbi:get-one
                  db "SELECT name FROM users WHERE name = 'John'"))
           
           (test-end "row accessors")
           
           
           (test-begin "parameter passing")
           
           ;; NOTE: Trailing semicolon required
           (test "get-rows with separate arguments returns correct values"
                 (if strings-only
                     '(#("1" "Matt"))
                     '(#(1 "Matt")))
                 (dbi:get-rows
                  db "SELECT * FROM users WHERE name = ?;" "Matt"))
           
           (test-error "get-rows with too many arguments errors"
                       (dbi:get-rows
                        db "SELECT * FROM users WHERE name = ?;" "Matt" "Peter"))
           
           (test-error "get-rows with too few arguments errors"
                       (dbi:get-rows
                        db "SELECT * FROM users WHERE name = ? OR name = ?;" "Matt"))
           
           (test-end "parameter passing")


           (unless strings-only
             (test-begin "value conversion")
           
             (test "strings"
                   '#("abc" "def")
                   (dbi:get-one-row db "SELECT ?, ?;" "abc" "def"))
           
             (test "integers"
                   '#(1 2 3)
                   (dbi:get-one-row db "SELECT ?, ?, ?;" 1 2 3))
           
             (test "flonums"
                   '#(1.2 2.0 0.123)
                   (dbi:get-one-row db "SELECT ?, ?, ?;" 1.2 2.0 0.123))
           
             ;; This doesn't work, dbi:sqlparam-val->string doesn't know
             ;; db type and sqlite3 doesn't know about booleans, anyway,
             ;; so it would return #(1 0).
             #;(test "booleans"
             '#(#t #f)
             (dbi:get-one-row db "SELECT ? ?;" #t #f))
             ;; TODO: date vectors?
     
             (test-end "value conversion"))


           (test-begin "convenience helpers")
           
           (test "lazy bools"
                 '(0 1 0 1 0 1 1)
                 (map dbi:lazy-bool '(#f #t "0" "1" "FALSE" "TRUE" "hello")))

           (test-end "convenience helpers")

           
           (dbi:close db)))
     (test-end (symbol->string dbtype))))

 backend-openers)

(test-end "dbi for all supported backends")