Tests for time printing and retrieving

These tests will create a table and verify that the date inserted is
the date retrieved and that when we update the database the value we
retrieve is the same as the one we inserted.

Some backends support timezones and some do not, also some support
usecs and some dont.

Things the following tests try to prove correct
 * Reading and writing usec and usec-less times
 * reading and writing timezones (Z=utc) when appropriate (eg: postgresql-socket)
 * reading and writing localtimes when appropriate (eg: ODBC)
 * reading and writing through both the oodml and fdml layers

All these tests fail right now. All tests may not be correct yet.

diff --git a/tests/test-init.lisp b/tests/test-init.lisp
index 8bfa2dafd08acda7a0a1e1a952e8139228ae3217..bd8fc99a86a45f3f4d12e780e083fc2715646843 100644 (file)
--- a/tests/test-init.lisp
+++ b/tests/test-init.lisp
@@ -277,6 +277,17 @@
                              :oodml/update-instance/6 :oodml/update-instance/7
                              :oodml/db-auto-sync/3 :oodml/db-auto-sync/4))
           (push (cons test ":auto-increment not supported.") skip-tests))
+         ((and (not (member *test-database-underlying-type*
+                           '(:postgresql :postgresql-socket)))
+               (clsql-sys:in test
+                             :time/pg/fdml/usec :time/pg/oodml/no-usec :time/pg/oodml/usec))
+          (push (cons test "cant run the same date/time tests everywhere that we do on postgres because there are not standard datetime database types.")
+                skip-tests))
+         ((and (member *test-database-underlying-type* '(:mysql))
+               (clsql-sys:in test :time/cross-platform/usec/no-tz :time/cross-platform/usec/tz))
+          (push (cons test "Mysql does not support fractional seconds (on timestamp columns), see http://forge.mysql.com/worklog/task.php?id=946 perhaps?")
+                skip-tests))
+
           (t
            (push test-form test-forms)))))
       (values (nreverse test-forms) (nreverse skip-tests))))

diff --git a/tests/test-time.lisp b/tests/test-time.lisp
index 9b4b85780dacada03d4ea2b717e516036deedf28..f373669ff65e93e9ccb1c66a3de75b8ad14e5ff4 100644 (file)
--- a/tests/test-time.lisp
+++ b/tests/test-time.lisp
@@ -6,6 +6,29 @@
 ;;; Test time functions (time.lisp)
 
 (in-package #:clsql-tests)
+#.(clsql-sys:locally-enable-sql-reader-syntax)
+
+(def-dataset *ds-datetest*
+  (:setup (lambda () (clsql-sys:create-view-from-class 'datetest)))
+  (:cleanup "DROP TABLE datetest"))
+
+(def-dataset *cross-platform-datetest*
+  (:setup "CREATE TABLE DATETEST (TESTTIME TIMESTAMP)")
+  (:cleanup "DROP TABLE DATETEST"))
+
+(def-view-class datetest ()
+  ((testtimetz :COLUMN "testtimetz" :TYPE
+              clsql-sys:wall-time :DB-KIND :BASE
+              :DB-CONSTRAINTS COMMON-LISP:NIL
+              :ACCESSOR testtimetz :INITARG
+              :testtimetz :INITFORM COMMON-LISP:NIL
+              :DB-TYPE "timestamptz")
+   (testtime :COLUMN "testtime" :TYPE
+            clsql-sys:wall-time :DB-KIND :BASE
+            :DB-CONSTRAINTS COMMON-LISP:NIL
+            :ACCESSOR testtime :INITARG
+            :testtime :INITFORM COMMON-LISP:NIL
+            :DB-TYPE "timestamp")))
 
 (setq *rt-time*
       '(
@@ -213,4 +236,153 @@
       (clsql:time= add-time roll-time))
   t)
 
+
+(deftest :time/14-usec
+    ;;make sure when we print and parse we get the same time.
+    (let* ((time (clsql-sys:make-time :year 2010 :month 1 :day 4
+                                     :hour 14 :minute 15 :second 44 :usec 3))
+          (string-time (clsql-sys:format-time nil time :format :iso))
+          (time2 (clsql-sys:parse-timestring string-time)))
+      (format-time nil time2 :format :iso))
+  #.(format-time nil (clsql-sys:make-time :year 2010 :month 1 :day 4
+                                     :hour 14 :minute 15 :second 44 :usec 3)
+     :format :iso)
+  t)
+
+
+;;; The cross platform dataset uses the 'timestamp' column type which is
+;;; in sql-92, for all that means.
+
+(deftest :time/cross-platform/no-usec/no-tz
+    (with-dataset *cross-platform-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29")))
+       (clsql-sys:insert-records :into [datetest]
+                                 :attributes '([testtime])
+                                 :values (list time))
+       (let ((testtime
+              (first (clsql:select [testtime]
+                                   :from [datetest]
+                                   :limit 1 :flatp T
+                                   :where [= [testtime] time] ))))
+         (format-time nil (parse-timestring testtime) :format :iso)
+         )))
+  #.(format-time nil (parse-timestring "2008-09-09T14:37:29") :format :iso))
+
+(deftest :time/cross-platform/no-usec/tz
+    (with-dataset *cross-platform-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29-04:00")))
+       (clsql-sys:insert-records :into [datetest]
+                                 :attributes '([testtime])
+                                 :values (list time))
+       (let ((testtime
+              (first (clsql:select [testtime]
+                                   :from [datetest]
+                                   :limit 1 :flatp T
+                                   :where [= [testtime] time] ))))
+         (format-time nil (parse-timestring testtime) :format :iso)
+         )))
+  #.(format-time nil (parse-timestring "2008-09-09T14:37:29-04:00") :format :iso))
+
+(deftest :time/cross-platform/usec/no-tz
+    (with-dataset *cross-platform-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29.000213")))
+       (clsql-sys:insert-records :into [datetest]
+                                 :attributes '([testtime])
+                                 :values (list time))
+       (let ((testtime
+              (first (clsql:select [testtime]
+                                   :from [datetest]
+                                   :limit 1 :flatp T
+                                   :where [= [testtime] time] ))))
+         (format-time nil (parse-timestring testtime) :format :iso)
+         )))
+  #.(format-time nil (parse-timestring "2008-09-09T14:37:29.000213") :format :iso))
+
+(deftest :time/cross-platform/usec/tz
+    (with-dataset *cross-platform-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29.000213-04:00")))
+       (clsql-sys:insert-records :into [datetest]
+                                 :attributes '([testtime])
+                                 :values (list time))
+       (let ((testtime
+              (first (clsql:select [testtime]
+                                   :from [datetest]
+                                   :limit 1 :flatp T
+                                   :where [= [testtime] time] ))))
+         (format-time nil (parse-timestring testtime) :format :iso)
+         )))
+  #.(format-time nil (parse-timestring "2008-09-09T14:37:29.000213-04:00") :format :iso))
+
+
+
+;;; All odbc databases use local times exclusively (they do not send timezone info)
+;;; Postgresql can use timezones, except when being used over odbc.  This test when
+;;; run through both postgres socket and postgres odbc should test a fairly
+;;; broad swath of available problem space
+;;;
+;;; Things the following tests try to prove correct
+;;;  * Reading and writing usec and usec-less times
+;;;  * reading and writing timezones (Z=utc) when appropriate (eg: postgresql-socket)
+;;;  * reading and writing localtimes when appropriate (eg: ODBC)
+;;;  * reading and writing through both the oodml and fdml layers
+
+
+
+(deftest :time/pg/fdml/usec
+    (with-dataset *ds-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29.000213-04:00")))
+       (clsql-sys:insert-records :into [datetest]
+                                 :attributes '([testtimetz] [testtime])
+                                 :values (list time time))
+       (destructuring-bind (testtimetz testtime)
+           (first (clsql:select [testtimetz] [testtime]
+                                :from [datetest]
+                                :limit 1 :flatp T
+                                :where [= [testtime] time] ))
+         (assert (time= (parse-timestring testtimetz) time) (testtimetz time)
+                 "Timetz of o: ~s should be time:~s" testtimetz time)
+         (assert (time= (parse-timestring testtime) time) (testtime time)
+                 "Time of o: ~s should be time:~s" testtime time))))
+  nil)
+
+(deftest :time/pg/oodml/no-usec
+    (with-dataset *ds-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29-04:00")))
+       (clsql-sys:update-records-from-instance
+        (make-instance 'datetest :testtimetz time :testtime time))
+       (let ((o (first (clsql:select
+                        'datetest
+                        :limit 1 :flatp T
+                        :where [= [testtime] time] ))))
+         (assert o (o) "o shouldnt be null here (we should have just inserted)")
+         (update-records-from-instance o)
+         (update-instance-from-records o)
+         (assert (time= (testtime o) time) (time o)
+                 "Time of o: ~s should be time:~s" (testtime o) time)
+         (assert (time= (testtimetz o) time) (time o)
+                 "Timetz of o: ~s should be time:~s" (testtime o) time))))
+  nil)
+
+(deftest :time/pg/oodml/usec
+    (with-dataset *ds-datetest*
+      (let ((time (parse-timestring "2008-09-09T14:37:29.000278-04:00")))
+       (clsql-sys:update-records-from-instance
+        (make-instance 'datetest :testtimetz time :testtime time))
+       (let ((o (first (clsql:select
+                        'datetest
+                        :limit 1 :flatp T
+                        :where [= [testtime] time] ))))
+         (assert o (o) "o shouldnt be null here (we should have just inserted)")
+         (update-records-from-instance o)
+         (update-instance-from-records o)
+         (assert (time= (testtime o) time) (time o)
+                 "Time of o: ~s should be time:~s" (testtime o) time)
+         (assert (time= (testtimetz o) time) (time o)
+                 "Timetz of o: ~s should be time:~s" (testtime o) time)
+         )))
+  nil)
+
 ))
+
+
+#.(clsql-sys:locally-disable-sql-reader-syntax)