r9737: Add column/table contraints example to documentation for CREATE-TABLE.

[clsql.git] / tests / test-fdml.lisp

diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp
index 5c95fcd19c2aeb9a65b9ed4da5a37f9d139dae17..344e6c5ce49965282dfd844bdf05bce0cef8a3f9 100644 (file)
--- a/tests/test-fdml.lisp
+++ b/tests/test-fdml.lisp
@@ -1,7 +1,7 @@
 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
 ;;;; ======================================================================
 ;;;; File:    test-fdml.lisp
 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
 ;;;; ======================================================================
 ;;;; File:    test-fdml.lisp

-;;;; Author:  Marcus Pearce <m.t.pearce@city.ac.uk>
+;;;; Author:  Marcus Pearce <m.t.pearce@city.ac.uk>, Kevin Rosenberg

 ;;;; Created: 30/03/2004
 ;;;; Updated: $Id$
 ;;;;
 ;;;; Created: 30/03/2004
 ;;;; Updated: $Id$
 ;;;;


@@ -24,10 +24,10 @@
        
 ;; inserts a record using all values only and then deletes it 
 (deftest :fdml/insert/1
        
 ;; inserts a record using all values only and then deletes it 
 (deftest :fdml/insert/1

-    (progn
+    (let ((now (get-universal-time)))

       (clsql:insert-records :into [employee] 
       (clsql:insert-records :into [employee] 

-                           :values `(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
-                                     1 1 1.85 t ,(clsql:get-time)))
+       :values `(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
+                   1 1 1.85 t ,(clsql:utime->time now) ,now))

       (values 
        (clsql:select [first-name] [last-name] [email]
                     :from [employee] :where [= [emplid] 11])
       (values 
        (clsql:select [first-name] [last-name] [email]
                     :from [employee] :where [= [emplid] 11])


@@ -133,7 +133,7 @@
     (let ((count (caar (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')" :field-names nil))))
       (if (stringp count)
          (nth-value 0 (parse-integer count))
     (let ((count (caar (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')" :field-names nil))))
       (if (stringp count)
          (nth-value 0 (parse-integer count))

-       count))
+         (nth-value 0 (truncate count))))

   10)
 
 (deftest :fdml/query/2
   10)
 
 (deftest :fdml/query/2


@@ -155,29 +155,38 @@
   t)
   
 (deftest :fdml/query/5
   t)
   
 (deftest :fdml/query/5

- (clsql:query (clsql:sql [select [first-name] [sum [emplid]] :from [employee]] 
-                       [group-by [first-name]] [order-by [sum [emplid]]])
-              :field-names nil :result-types nil)
- (("Josef" "2") ("Leon" "3") ("Nikita" "4") ("Leonid" "5") ("Yuri" "6")
-  ("Konstantin" "7") ("Mikhail" "8") ("Boris" "9") ("Vladamir" "11")))
+    (let ((res (clsql:query (clsql:sql [select [first-name] [sum [emplid]] :from [employee]] 
+                                      [group-by [first-name]] [order-by [sum [emplid]]])
+                           :field-names nil :result-types nil)))
+      (mapcar (lambda (p) (list (car p) (truncate (read-from-string (second p)))))
+             res))
+  (("Josef" 2) ("Leon" 3) ("Nikita" 4) ("Leonid" 5) ("Yuri" 6)
+  ("Konstantin" 7) ("Mikhail" 8) ("Boris" 9) ("Vladamir" 11)))

 
 (deftest :fdml/query/6
 
 (deftest :fdml/query/6

- (clsql:query (clsql:sql [union [select [emplid] :from [employee]] 
-                        [select [groupid] :from [company]]])
-              :field-names nil :result-types nil :flatp t)
- ("1" "2" "3" "4" "5" "6" "7" "8" "9" "10"))
+    (let ((res (clsql:query (clsql:sql [union [select [emplid] :from [employee]] 
+                                      [select [groupid] :from [company]]])
+                           :field-names nil :result-types nil :flatp t)))
+      (values (every #'stringp res)
+             (mapcar #'(lambda (f) (truncate (read-from-string f))) res)))
+  t (1 2 3 4 5 6 7 8 9 10))

 
 (deftest :fdml/query/7
 
 (deftest :fdml/query/7

- (clsql:query (clsql:sql [intersect [select [emplid] :from [employee]] 
-                        [select [groupid] :from [company]]])
-              :field-names nil :result-types nil :flatp t)
- ("1"))
+    (let ((res (car (clsql:query (clsql:sql [intersect [select [emplid] :from [employee]] 
+                                           [select [groupid] :from [company]]])
+                                :field-names nil :result-types nil :flatp t))))
+      (values (stringp res)
+             (nth-value 0 (truncate (read-from-string res)))))
+  t 1)

 
 (deftest :fdml/query/8
 
 (deftest :fdml/query/8

- (clsql:query (clsql:sql [except [select [emplid] :from [employee]] 
-                        [select [groupid] :from [company]]])
-              :field-names nil :result-types nil :flatp t)
- ("2" "3" "4" "5" "6" "7" "8" "9" "10")) 
+    (let ((res (clsql:query (clsql:sql [except [select [emplid] :from [employee]] 
+                                      [select [groupid] :from [company]]])
+                           :field-names nil :result-types nil :flatp t)))
+      (values (every #'stringp res)
+             (mapcar #'(lambda (f) (truncate (read-from-string f))) res)))
+  t (2 3 4 5 6 7 8 9 10))
+

 
 (deftest :fdml/execute-command/1
     (values
 
 (deftest :fdml/execute-command/1
     (values


@@ -222,13 +231,15 @@
   "Yuri"))
 
 (deftest :fdml/select/3
   "Yuri"))
 
 (deftest :fdml/select/3

-    (clsql:select [first-name] [count [*]] :from [employee]
-                         :result-types nil 
-                         :group-by [first-name]
-                         :order-by [first-name]
-                         :field-names nil)
- (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
-  ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1")))
+    (let ((res (clsql:select [first-name] [count [*]] :from [employee]
+                            :result-types nil 
+                            :group-by [first-name]
+                            :order-by [first-name]
+                            :field-names nil)))
+      (mapcar (lambda (p) (list (car p) (truncate (read-from-string (second p)))))
+             res))
+  (("Boris" 1) ("Josef" 1) ("Konstantin" 1) ("Leon" 1) ("Leonid" 1)
+   ("Mikhail" 1) ("Nikita" 1) ("Vladamir" 2) ("Yuri" 1)))

 
 (deftest :fdml/select/4
     (clsql:select [last-name] :from [employee] 
 
 (deftest :fdml/select/4
     (clsql:select [last-name] :from [employee] 


@@ -248,7 +259,7 @@
   ("lenin@soviet.org"))
 
 (deftest :fdml/select/6
   ("lenin@soviet.org"))
 
 (deftest :fdml/select/6

-    (if (db-type-has-fancy-math? *test-database-underlying-type*)
+    (if (clsql-sys:db-type-has-fancy-math? *test-database-underlying-type*)

         (mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
                (clsql:select [function "trunc" [height]] :from [employee]
                              :result-types nil 
         (mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
                (clsql:select [function "trunc" [height]] :from [employee]
                              :result-types nil 


@@ -260,14 +271,20 @@
  (1 1 1 1 1 1 1 1 1 1))
 
 (deftest :fdml/select/7
  (1 1 1 1 1 1 1 1 1 1))
 
 (deftest :fdml/select/7

-    (clsql:select [max [emplid]] :from [employee] :flatp t 
-                 :field-names nil :result-types nil)
-  ("10"))
+    (let ((result (car (clsql:select [max [emplid]] :from [employee] :flatp t 
+                                    :field-names nil :result-types nil))))
+      (values 
+       (stringp result)
+       (nth-value 0 (truncate (read-from-string result)))))
+  t 10)

 
 (deftest :fdml/select/8
 
 (deftest :fdml/select/8

-    (clsql:select [min [emplid]] :from [employee] :flatp t 
-                 :field-names nil :result-types nil)
-  ("1"))
+    (let ((result (car (clsql:select [min [emplid]] :from [employee] :flatp t 
+                                    :field-names nil :result-types nil))))
+      (values
+       (stringp result)
+       (nth-value 0 (truncate (read-from-string result)))))
+  t 1)

 
 (deftest :fdml/select/9
     (subseq 
 
 (deftest :fdml/select/9
     (subseq 


@@ -351,12 +368,12 @@
  ("1" "2" "3" "4"))
 
 (deftest :fdml/select/21 
  ("1" "2" "3" "4"))
 
 (deftest :fdml/select/21 

-  (clsql:select [substr [first-name] 1 4] :from [employee] 
+  (clsql:select [substring [first-name] 1 4] :from [employee] 

                 :flatp t :order-by [emplid] :field-names nil)
  ("Vlad" "Jose" "Leon" "Niki" "Leon" "Yuri" "Kons" "Mikh" "Bori" "Vlad"))
 
 (deftest :fdml/select/22 
                 :flatp t :order-by [emplid] :field-names nil)
  ("Vlad" "Jose" "Leon" "Niki" "Leon" "Yuri" "Kons" "Mikh" "Bori" "Vlad"))
 
 (deftest :fdml/select/22 

-  (clsql:select [\|\| [first-name] " " [last-name]] :from [employee]
+  (clsql:select [|| [first-name] " " [last-name]] :from [employee]

                 :flatp t :order-by [emplid] :field-names nil)
  ("Vladamir Lenin" "Josef Stalin" "Leon Trotsky" "Nikita Kruschev"
  "Leonid Brezhnev" "Yuri Andropov" "Konstantin Chernenko" "Mikhail Gorbachev"
                 :flatp t :order-by [emplid] :field-names nil)
  ("Vladamir Lenin" "Josef Stalin" "Leon Trotsky" "Nikita Kruschev"
  "Leonid Brezhnev" "Yuri Andropov" "Konstantin Chernenko" "Mikhail Gorbachev"


@@ -374,11 +391,92 @@
  ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
   "Yuri"))
 
  ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
   "Yuri"))
 

-;(deftest :fdml/select/11
-;    (clsql:select [emplid] :from [employee]
-;                :where [= [emplid] [any [select [companyid] :from [company]]]]
-;                :flatp t)
-;  ("1"))
+(deftest :fdml/select/25
+ (clsql:select [first-name] :from (clsql-sys:convert-to-db-default-case "employee" *default-database*)
+  :flatp t :distinct t
+  :field-names nil 
+  :result-types nil 
+  :order-by [first-name])
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
+  "Yuri"))
+
+(deftest :fdml/select/26
+ (clsql:select ["table" first-name] ["table" last-name] 
+  :from '([employee "table"] [employee "join"])
+  :where [and [= ["table" first-name] 
+                 ["join" first-name]]
+              [not [= ["table" emplid] 
+                      ["join" emplid]]]]
+  :order-by '(["table" last-name])
+  :result-types nil :field-names nil)
+ (("Vladamir" "Lenin") ("Vladamir" "Putin")))
+
+(deftest :fdml/select/27 
+    (mapcar
+     (lambda (f) (truncate (read-from-string f)))
+     (clsql:select [coalesce [managerid] 10] :from [employee] :order-by [emplid]
+                  :field-names nil :result-types nil :flatp t))
+  (10 1 1 1 1 1 1 1 1 1))
+  
+(deftest :fdml/select/28 
+    (mapcar
+     (lambda (f) (truncate (read-from-string (car f))))
+     (loop for column in `([*] [emplid]) collect         
+          (clsql:select [count column] :from [employee] 
+                        :flatp t :result-types nil :field-names nil)))
+ (10 10))
+
+(deftest :fdml/select/29 
+ (clsql:select [first-name] [last-name] :from [employee] 
+                       :result-types nil :field-names nil 
+                       :order-by '(([first-name] :asc) ([last-name] :desc)))
+ (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
+  ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
+  ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
+  ("Yuri" "Andropov")))
+
+(deftest :fdml/select/30 
+ (clsql:select [first-name] [last-name] :from [employee] 
+                       :result-types nil :field-names nil 
+                       :order-by '(([first-name] :asc) ([last-name] :asc)))
+ (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
+  ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
+  ("Nikita" "Kruschev") ("Vladamir" "Lenin") ("Vladamir" "Putin")
+  ("Yuri" "Andropov")))
+
+(deftest :fdml/select/31
+ (clsql:select [last-name] :from [employee]                   
+              :set-operation [union [select [first-name] :from [employee]
+                                            :order-by [last-name]]]
+              :flatp t
+              :result-types nil 
+              :field-names nil)
+ ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
+ "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
+ "Trotsky" "Vladamir" "Yeltsin" "Yuri"))
+
+(deftest :fdml/select/32
+    (clsql:select [emplid] :from [employee]
+                :where [= [emplid] [any [select [companyid] :from [company]]]]
+                :flatp t :result-types nil :field-names nil)
+  ("1"))
+
+(deftest :fdml/select/33
+ (clsql:select [last-name] :from [employee] 
+              :where [> [emplid] [all [select [groupid] :from [employee]]]]
+              :order-by [last-name] 
+              :flatp t :result-types nil :field-names nil)
+("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Putin" "Stalin"
+ "Trotsky" "Yeltsin"))
+
+(deftest :fdml/select/34
+  (loop for x from 1 below 5
+   collect
+   (car
+    (clsql:select [last-name] :from [employee] 
+                 :where [= [emplid] x]
+                 :flatp t :result-types nil :field-names nil)))
+  ("Lenin" "Stalin" "Trotsky" "Kruschev"))

 
 (deftest :fdml/do-query/1
     (let ((result '()))
 
 (deftest :fdml/do-query/1
     (let ((result '()))


@@ -402,6 +500,21 @@
                             :order-by [last-name]])
   #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
     "Stalin" "Trotsky" "Yeltsin"))
                             :order-by [last-name]])
   #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
     "Stalin" "Trotsky" "Yeltsin"))

+
+(deftest :fdml/map-query/3 
+ (clsql:map-query 'list #'identity
+                  [select [last-name] :from [employee] :order-by [last-name]])
+ (("Andropov") ("Brezhnev") ("Chernenko") ("Gorbachev") ("Kruschev") ("Lenin")
+  ("Putin") ("Stalin") ("Trotsky") ("Yeltsin")))
+
+(deftest :fdml/map-query/4 
+ (clsql:map-query 'list #'identity
+                  [select [first-name] [last-name] :from [employee] 
+                          :order-by [last-name]])
+ (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
+  ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
+  ("Vladamir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky") 
+  ("Boris" "Yeltsin")))

   
 (deftest :fdml/loop/1
     (loop for (forename surname)
   
 (deftest :fdml/loop/1
     (loop for (forename surname)


@@ -513,8 +626,7 @@
                          :flatp t)
             results)
       (apply #'values (nreverse results)))
                          :flatp t)
             results)
       (apply #'values (nreverse results)))

-  nil :committed nil ("lenin-nospam@soviet.org") :committed
-  nil ("lenin@soviet.org"))
+  nil nil nil ("lenin-nospam@soviet.org") nil nil ("lenin@soviet.org"))

 
 ;; runs a valid update and an invalid one within a transaction and checks
 ;; that the valid update is rolled back when the invalid one fails. 
 
 ;; runs a valid update and an invalid one within a transaction and checks
 ;; that the valid update is rolled back when the invalid one fails. 


@@ -533,7 +645,7 @@
                                  :av-pairs
                                  '((emale "lenin-nospam@soviet.org"))
                                  :where [= [emplid] 1]))
                                  :av-pairs
                                  '((emale "lenin-nospam@soviet.org"))
                                  :where [= [emplid] 1]))

-        (clsql:clsql-error ()
+        (clsql:sql-database-error ()

           (progn
             ;; check status 
             (push (clsql:in-transaction-p) results)
           (progn
             ;; check status 
             (push (clsql:in-transaction-p) results)


@@ -544,6 +656,7 @@
             (apply #'values (nreverse results))))))
   nil nil ("lenin@soviet.org"))
 
             (apply #'values (nreverse results))))))
   nil nil ("lenin@soviet.org"))
 

+

 ))
 
 #.(clsql:restore-sql-reader-syntax-state)
 ))
 
 #.(clsql:restore-sql-reader-syntax-state)