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 9bacbe52024dc7abf5e35e57fbe08ed28d06e4f6..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*


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


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

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