Refactored find-all and build-object to be more readable, shorter and

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

diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp
index 053dbc45f7c02a0a9ee0304663ed1b3d7f96443a..dd8336be95ed0d0df92477a873b2eaceea2824ea 100644 (file)
--- a/tests/test-fdml.lisp
+++ b/tests/test-fdml.lisp
@@ -3,7 +3,6 @@
 ;;;; File:    test-fdml.lisp
 ;;;; Author:  Marcus Pearce <m.t.pearce@city.ac.uk>, Kevin Rosenberg
 ;;;; Created: 30/03/2004
 ;;;; File:    test-fdml.lisp
 ;;;; Author:  Marcus Pearce <m.t.pearce@city.ac.uk>, Kevin Rosenberg
 ;;;; Created: 30/03/2004

-;;;; Updated: $Id$

 ;;;;
 ;;;; Tests for the CLSQL Functional Data Manipulation Language
 ;;;; (FDML).
 ;;;;
 ;;;; Tests for the CLSQL Functional Data Manipulation Language
 ;;;; (FDML).


@@ -17,8 +16,7 @@
 
 (in-package #:clsql-tests)
 
 
 (in-package #:clsql-tests)
 

-#.(clsql:locally-enable-sql-reader-syntax)
-
+(clsql-sys:file-enable-sql-reader-syntax)

 ;;started defining an independent dataset that doesn't depend on the view-classes
 ;; but there is a *lot* of stuff in the file assuming that dataset.
 ;; (def-dataset *ds-fdml*
 ;;started defining an independent dataset that doesn't depend on the view-classes
 ;; but there is a *lot* of stuff in the file assuming that dataset.
 ;; (def-dataset *ds-fdml*


@@ -57,9 +55,7 @@
 (deftest :fdml/query/1
     (with-dataset *ds-employees*
       (let ((count (caar (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')" :field-names nil))))
 (deftest :fdml/query/1
     (with-dataset *ds-employees*
       (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))
-           (nth-value 0 (truncate count)))))
+        (%get-int count)))

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


@@ -88,7 +84,7 @@
       (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)))
       (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)))))
+       (mapcar (lambda (p) (list (car p) (%get-int (second p))))

                res)))
   (("Josef" 2) ("Leon" 3) ("Nikita" 4) ("Leonid" 5) ("Yuri" 6)
    ("Konstantin" 7) ("Mikhail" 8) ("Boris" 9) ("Vladimir" 11)))
                res)))
   (("Josef" 2) ("Leon" 3) ("Nikita" 4) ("Leonid" 5) ("Yuri" 6)
    ("Konstantin" 7) ("Mikhail" 8) ("Boris" 9) ("Vladimir" 11)))


@@ -99,8 +95,9 @@
                                                [select [groupid] :from [company]]])
                              :field-names nil :result-types nil :flatp t
                              )))
                                                [select [groupid] :from [company]]])
                              :field-names nil :result-types nil :flatp t
                              )))

-       (values (every #'stringp res)
-               (sort (mapcar #'(lambda (f) (truncate (read-from-string f))) res)
+       (values (or (eql *test-database-type* :postgresql-socket3)
+                    (every #'stringp res))
+               (sort (mapcar #'%get-int res)

                      #'<=))))
   t (1 2 3 4 5 6 7 8 9 10))
 
                      #'<=))))
   t (1 2 3 4 5 6 7 8 9 10))
 


@@ -109,8 +106,9 @@
       (let ((res (car (clsql:query (clsql:sql [intersect [select [emplid] :from [employee]]
                                                         [select [groupid] :from [company]]])
                                   :field-names nil :result-types nil :flatp t))))
       (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))))))
+       (values (or (stringp res)
+                    (eql *test-database-type* :postgresql-socket3))
+               (nth-value 0 (%get-int res)))))

   t 1)
 
 (deftest :fdml/query/8
   t 1)
 
 (deftest :fdml/query/8


@@ -118,25 +116,12 @@
       (let ((res (clsql:query (clsql:sql [except [select [emplid] :from [employee]]
                                                 [select [groupid] :from [company]]])
                              :field-names nil :result-types nil :flatp t)))
       (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)
-               (sort (mapcar #'(lambda (f) (truncate (read-from-string f))) res)
+       (values (or (every #'stringp res)
+                    (eql *test-database-type* :postgresql-socket3))
+               (sort (mapcar #'%get-int res)

                      #'<=))))
   t (2 3 4 5 6 7 8 9 10))
 
                      #'<=))))
   t (2 3 4 5 6 7 8 9 10))
 

-
-(deftest :fdml/execute-command/1
-    (with-dataset *ds-employees*
-      (values
-       (clsql:table-exists-p [foo] :owner *test-database-user*)
-       (progn
-         (clsql:execute-command "create table foo (bar integer)")
-         (clsql:table-exists-p [foo] :owner *test-database-user*))
-       (progn
-         (clsql:execute-command "drop table foo")
-         (clsql:table-exists-p [foo] :owner *test-database-user*))))
-  nil t nil)
-
-

 ;; compare min, max and average hieghts in inches (they're quite short
 ;; these guys!)
 (deftest :fdml/select/1
 ;; compare min, max and average hieghts in inches (they're quite short
 ;; these guys!)
 (deftest :fdml/select/1


@@ -156,8 +141,7 @@
                               :from [employee]
                               :result-types nil
                               :flatp t)))
                               :from [employee]
                               :result-types nil
                               :flatp t)))

-       (apply #'< (mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
-                          (append min avg max)))))
+       (apply #'< (mapcar #'%get-int (append min avg max)))))

   t)
 
 (deftest :fdml/select/2
   t)
 
 (deftest :fdml/select/2


@@ -176,7 +160,7 @@
                               :group-by [first-name]
                               :order-by [first-name]
                               :field-names nil)))
                               :group-by [first-name]
                               :order-by [first-name]
                               :field-names nil)))

-       (mapcar (lambda (p) (list (car p) (truncate (read-from-string (second p)))))
+       (mapcar (lambda (p) (list (car p) (%get-int (second p))))

                res)))
   (("Boris" 1) ("Josef" 1) ("Konstantin" 1) ("Leon" 1) ("Leonid" 1)
    ("Mikhail" 1) ("Nikita" 1) ("Vladimir" 2) ("Yuri" 1)))
                res)))
   (("Boris" 1) ("Josef" 1) ("Konstantin" 1) ("Leon" 1) ("Leonid" 1)
    ("Mikhail" 1) ("Nikita" 1) ("Vladimir" 2) ("Yuri" 1)))


@@ -203,12 +187,12 @@
 (deftest :fdml/select/6
     (with-dataset *ds-employees*
       (if (clsql-sys:db-type-has-fancy-math? *test-database-underlying-type*)
 (deftest :fdml/select/6
     (with-dataset *ds-employees*
       (if (clsql-sys:db-type-has-fancy-math? *test-database-underlying-type*)

-         (mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
+         (mapcar #'%get-int

                  (clsql:select [function "trunc" [height]] :from [employee]
                                :result-types nil
                                :field-names nil
                                :flatp t))
                  (clsql:select [function "trunc" [height]] :from [employee]
                                :result-types nil
                                :field-names nil
                                :flatp t))

-         (mapcar #'(lambda (s) (truncate (parse-integer s :junk-allowed t)))
+         (mapcar #'%get-int

                  (clsql:select [height] :from [employee] :flatp t
                                :field-names nil :result-types nil))))
   (1 1 1 1 1 1 1 1 1 1))
                  (clsql:select [height] :from [employee] :flatp t
                                :field-names nil :result-types nil))))
   (1 1 1 1 1 1 1 1 1 1))


@@ -218,27 +202,26 @@
       (let ((result (car (clsql:select [max [emplid]] :from [employee] :flatp t
                                       :field-names nil :result-types nil))))
        (values
       (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)
+         (nth-value 0 (%get-int result)))))
+  10)

 
 (deftest :fdml/select/8
     (with-dataset *ds-employees*
       (let ((result (car (clsql:select [min [emplid]] :from [employee] :flatp t
                                       :field-names nil :result-types nil))))
        (values
 
 (deftest :fdml/select/8
     (with-dataset *ds-employees*
       (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)
+         (nth-value 0 (%get-int result)))))
+  1)

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

-    (with-dataset *ds-employees*
-      (subseq
-       (car
-       (clsql:select [avg [emplid]] :from [employee] :flatp t
-                     :field-names nil :result-types nil))
-       0 3))
-  "5.5")
+ (with-dataset *ds-employees*
+   (let ((val (car (clsql:select
+                       [avg [emplid]] :from [employee] :flatp t
+                       :field-names nil :result-types nil))))
+     (typecase val
+       (string (subseq val 0 3))
+       (number (format nil "~,1F" val)))))
+ "5.5")

 
 (deftest :fdml/select/10
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/10
     (with-dataset *ds-employees*


@@ -311,18 +294,21 @@
   (("1" "Lenin")))
 
 (deftest :fdml/select/19
   (("1" "Lenin")))
 
 (deftest :fdml/select/19

-    (with-dataset *ds-employees*
-      (clsql:select [emplid] :from [employee] :order-by [emplid]
-                   :where [between [* [emplid] 10] [* 5 10] [* 10 10]]
-                   :field-names nil :result-types nil :flatp t))
-  ("5" "6" "7" "8" "9" "10"))
+ (with-dataset *ds-employees*
+   (mapcar
+    #'%get-int
+    (clsql:select [emplid] :from [employee] :order-by [emplid]
+                  :where [between [* [emplid] 10] [* 5 10] [* 10 10]]
+                  :field-names nil :result-types nil :flatp t)))
+ (5 6 7 8 9 10))

 
 (deftest :fdml/select/20
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/20
     (with-dataset *ds-employees*

+      (mapcar #'%get-int

       (clsql:select [emplid] :from [employee] :order-by [emplid]
                    :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
       (clsql:select [emplid] :from [employee] :order-by [emplid]
                    :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]

-                   :field-names nil :result-types nil :flatp t))
-  ("1" "2" "3" "4"))
+                   :field-names nil :result-types nil :flatp t)))
+  (1 2 3 4))

 
 (deftest :fdml/select/21
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/21
     (with-dataset *ds-employees*


@@ -342,11 +328,12 @@
    "Boris Yeltsin" "Vladimir Putin"))
 
 (deftest :fdml/select/23
    "Boris Yeltsin" "Vladimir Putin"))
 
 (deftest :fdml/select/23

-    (with-dataset *ds-employees*
-      (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)]
-                   :flatp t :order-by [emplid] :field-names nil
-                   :result-types nil))
-  ("1" "2" "3" "4"))
+ (with-dataset *ds-employees*
+   (mapcar #'%get-int
+           (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)]
+                         :flatp t :order-by [emplid] :field-names nil
+                         :result-types nil)))
+ (1 2 3 4))

 
 (deftest :fdml/select/24
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/24
     (with-dataset *ds-employees*


@@ -380,19 +367,20 @@
 (deftest :fdml/select/27
     (with-dataset *ds-employees*
       (mapcar
 (deftest :fdml/select/27
     (with-dataset *ds-employees*
       (mapcar

-       (lambda (f) (truncate (read-from-string f)))
+       #'%get-int

        (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
        (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

-    (with-dataset *ds-employees*
-      (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))
+ (with-dataset *ds-employees*
+   (loop for column in `([*] [emplid])
+         collect
+         (%get-int
+          (car
+           (clsql:select [count column] :from [employee]
+                         :flatp t :result-types nil :field-names nil)))))
+ (10 10))

 
 (deftest :fdml/select/29
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/29
     (with-dataset *ds-employees*


@@ -428,10 +416,12 @@
 
 (deftest :fdml/select/32
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/32
     (with-dataset *ds-employees*

-      (clsql:select [emplid] :from [employee]
-                   :where [= [emplid] [any [select [companyid] :from [company]]]]
-                   :flatp t :result-types nil :field-names nil))
-  ("1"))
+      (mapcar
+       #'%get-int
+       (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
     (with-dataset *ds-employees*
 
 (deftest :fdml/select/33
     (with-dataset *ds-employees*


@@ -663,8 +653,6 @@
   (("Yuri" "Gagarin" "gagarin@soviet.org"))
   (("Vladimir" "Lenin" "lenin@soviet.org")))
 
   (("Yuri" "Gagarin" "gagarin@soviet.org"))
   (("Vladimir" "Lenin" "lenin@soviet.org")))
 

-
-

 ;; starts a transaction deletes a record and then rolls back the deletion
 (deftest :fdml/transaction/1
     (with-dataset *ds-employees*
 ;; starts a transaction deletes a record and then rolls back the deletion
 (deftest :fdml/transaction/1
     (with-dataset *ds-employees*


@@ -789,4 +777,3 @@
 
 ))
 
 
 ))
 

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