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 a46740808dd15b54e68c3712e9e4250fe10d0f24..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,12 +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))
 

-

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


@@ -143,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


@@ -163,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)))


@@ -190,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))


@@ -205,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*


@@ -298,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*


@@ -329,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*


@@ -367,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*


@@ -415,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*


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

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