;;;; sqlite3-test.scm

(use testbase testbase-output-human)
(use sqlite3)

;;;

#| TestBase
|#

;;;

;; This is needed due to a testbase bug!

(define-for-syntax (SQL-QUERY . lines)
  (apply string-append (intersperse lines "\n")) )

;;;

(define *mak-source-table-sql*
  (SQL-QUERY
    "CREATE TABLE IF NOT EXISTS source ("
    "  oid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,"
    "  dir TEXT,"
    "  fil TEXT NOT NULL,"
    "  ext TEXT,"
    "  UNIQUE (dir, fil, ext) ON CONFLICT ROLLBACK"
    ");"))

(define *ins-source-entry-sql*
  (SQL-QUERY
    "INSERT INTO source (oid, dir, fil, ext)"
    "  VALUES (NULL, ?001, ?002, ?003);"))

(define *sel-maximum-dir-length-sql*
  (SQL-QUERY
    "SELECT Max(length(source.dir)) FROM source;"))

(define *db-name* "sqlit3test.db")

;;;

;;

(define-test string-queries-test "String Queries"
  (initial
    (define db)
  )

  (setup (delete-file* *db-name*))
  (teardown (delete-file* *db-name*))

  (expect-set! db (sqlite3:open *db-name*))

  (expect-success (sqlite3:exec db *mak-source-table-sql*))

  (expect-success (sqlite3:exec db *ins-source-entry-sql* "directory1" "file1" "ext1"))
  (expect-success (sqlite3:exec db *ins-source-entry-sql* "directory10" "file10" "ext10"))
  (expect-success (sqlite3:exec db *ins-source-entry-sql* "directory1000" "file1000" "ext1000"))
  (expect-success (sqlite3:exec db *ins-source-entry-sql* "" "" ""))

  (expect-eqv 13 (sqlite3:first-result db *sel-maximum-dir-length-sql*))

  (expect-success (sqlite3:finalize! db))
)

;;

(define-test statement-queries-test "Statement Queries"
  (initial
    (define dbpn "sqlit3test.db")
    (define db)
    (define mak-source-table)
    (define ins-source-entry)
    (define sel-maximum-dir-length)
  )

  (setup (delete-file* dbpn))
  (teardown (delete-file* dbpn))

  (expect-set! db (sqlite3:open dbpn))

  (expect-set! mak-source-table (sqlite3:prepare db *mak-source-table-sql*))

  (expect-success (sqlite3:exec mak-source-table))

  (expect-set! ins-source-entry (sqlite3:prepare db *ins-source-entry-sql*))
  (expect-set! sel-maximum-dir-length (sqlite3:prepare db *sel-maximum-dir-length-sql*))

  (expect-success (sqlite3:exec ins-source-entry "directory1" "file1" "ext1"))
  (expect-success (sqlite3:exec ins-source-entry "directory10" "file10" "ext10"))
  (expect-success (sqlite3:exec ins-source-entry "directory1000" "file1000" "ext1000"))
  (expect-success (sqlite3:exec ins-source-entry "" "" ""))

  (expect-eqv 13 (sqlite3:first-result sel-maximum-dir-length))

  (expect-success (sqlite3:finalize! mak-source-table))
  (expect-success (sqlite3:finalize! ins-source-entry))
  (expect-success (sqlite3:finalize! sel-maximum-dir-length))

  (expect-success (sqlite3:finalize! db))
)

;;;

(test::for-each (cut test::styler-set! <> test::output-style-human))
(run-test "Test sqlite3")

(test::forget!)