;; == testing ==============================================================
;;
;;
;; These tests assume that a table named "test" is defined in the
;; system catalog, and that the user identified in
;; CALL-WITH-TEST-CONNECTION has the rights to access that table. 

(defpackage :pg-tests
  (:use :cl :pg))
(in-package :pg-tests)

;; !!! CHANGE THE VALUES HERE !!!
(defun call-with-test-connection (function)
  (with-pg-connection (conn "test" "emarsden" :host "localhost")
    (funcall function conn)))

(defmacro with-test-connection ((conn) &body body)
  `(call-with-test-connection
     (lambda (,conn) ,@body)))

(defun test-insert ()
  (with-test-connection (conn)
    (let ((res nil)
          (count 0)
          (created t))
      (unwind-protect
           (progn
             (pg-exec conn "CREATE TABLE count_test(key int, val int)")
             (loop :for i :from 1 :to 100
                   :for sql = (format nil "INSERT INTO count_test VALUES(~s, ~s)"
                                      i (* i i))
                   :do (pg-exec conn sql))
             (setq created t)
             (setq res (pg-exec conn "SELECT count(val) FROM count_test"))
             (assert (eql 100 (first (pg-result res :tuple 0))))
             (setq res (pg-exec conn "SELECT sum(key) FROM count_test"))
             (assert (eql 5050 (first (pg-result res :tuple 0))))
             ;; this iterator does the equivalent of the sum(key) SQL statement
             ;; above, but on the client side.
             (pg-for-each conn "SELECT key FROM count_test"
                          (lambda (tuple) (incf count (first tuple))))
             (assert (= 5050 count)))
        (when created
          (pg-exec conn "DROP TABLE count_test"))))))

(defun test-date (&aux created)
  (with-test-connection (conn)
    (unwind-protect
         (progn
           (pg-exec conn "CREATE TABLE pgltest (a timestamp, b abstime, c time, d date)")
           (pg-exec conn "INSERT INTO pgltest VALUES "
                    "(current_timestamp, 'now', 'now', 'now')")
           (setq created t)
           (let* ((res (pg-exec conn "SELECT * FROM pgltest"))
                  (parsed (first (pg-result res :tuples))))
             (format t "attributes ~a~%" (pg-result res :attributes))
             (format t "Timestamp = ~s~%abstime = ~s~%time = ~s~%date = ~s~%"
                     (first parsed)
                     (second parsed)
                     (third parsed)
                     (fourth parsed))))
      (when created
        (pg-exec conn "DROP TABLE pgltest")))))

(defun test-lo ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let* ((oid (pglo-create conn))
           (fd (pglo-open conn oid)))
      (sleep 1)
      (pglo-tell conn fd)
      (sleep 1)
      (pglo-unlink conn oid)))))

;; test of large-object interface
(defun test-lo-read ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let* ((oid (pglo-create conn "rw"))
           (fd (pglo-open conn oid "rw")))
      (pglo-write conn fd "Hi there mate")
      (pglo-lseek conn fd 3 0)           ; SEEK_SET = 0
      (assert (= 3 (pglo-tell conn fd)))
      ;; this should print "there mate"
      (format *debug-io* "Read ~s from lo~%" (pglo-read conn fd 10))
      (pglo-close conn fd)
      (pglo-unlink conn oid)))))

#+cmu
(defun test-lo-import ()
  (with-test-connection (conn)
   (with-pg-transaction conn
    (let ((oid (pglo-import conn "/etc/group")))
      (pglo-export conn oid "/tmp/group")
      (cond ((zerop
              (ext:process-exit-code
               (ext:run-program "diff" (list "/tmp/group" "/etc/group"))))
             (format *debug-io* "pglo-import test succeeded~%")
             (unix:unix-unlink "/tmp/group"))
            (t
             (format *debug-io* "pglo-import test failed: check differences
between files /etc/group and /tmp/group")))
      (pglo-unlink conn oid)))))

(defun test-simple ()
  (let ((*pg-disable-type-coercion* t))
    (with-test-connection (conn)
     (format t "backend ~a~%" (pg-backend-version conn)))))

(defun test-notifications ()
  (with-test-connection (conn)
    (let (res)
      (setq res (pg-exec conn "LISTEN pg_test_listen"))
      (format t "LISTEN -> ~S~%" (pg-result res :status))
      (assert (null (pg::pgcon-notices conn)))
      (pg-exec conn "SELECT * FROM pg_type")
      (assert (null (pg::pgcon-notices conn)))
      (setq res (pg-exec conn "NOTIFY pg_test_listen"))
      (format t "NOTIFY -> ~S~%" (pg-result res :status))
      (format t "In TEST-NOTIFICATIONS notices are ~S~%"
              (pg::pgcon-notices conn)))))

(defun test ()
  (with-test-connection (conn)
   (format t "Running pg.lisp tests against backend ~a~%" (pg-backend-version conn))
   ;; client encoding supported since PostgreSQL v7.1
   (format t "Client encoding is ~A~%" (pg-client-encoding conn))
   (format t "Date style is ~A~%" (pg-date-style conn))
   (let ((r2 (pg-exec conn "CREATE TABLE pgltest (a int, b float, c money)"))
         (r3 (pg-exec conn "INSERT INTO pgltest VALUES (3, -1234.5e67, '$123.45')"))
         (r4 (pg-exec conn "DROP TABLE pgltest")))
     (format t "~%==============================================~%")
     (format t "status of CREATE is ~s~%" (pg-result r2 :status))
     (format t "status of INSERT is ~s~%" (pg-result r3 :status))
     (format t "oid of INSERT is ~s~%" (pg-result r3 :oid))
     (format t "status of DROP is ~s~%" (pg-result r4 :status))
     (format t "==============================================~%")))
  (test-simple)
  (test-insert)
  (test-date)
  (test-lo)
  (test-lo-read)
  (test-notifications))


;; EOF