r9373: Treat [*] as a column identifier.

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

diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp
index 0286b2d807a0034efcb6831b7f1a9d98f291f00c..adbb8479c90b52820e92ace8d6d409b61f4e6b41 100644 (file)
--- a/tests/test-fdml.lisp
+++ b/tests/test-fdml.lisp
@@ -356,7 +356,7 @@
  ("Vlad" "Jose" "Leon" "Niki" "Leon" "Yuri" "Kons" "Mikh" "Bori" "Vlad"))
 
 (deftest :fdml/select/22 
  ("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 +374,78 @@
  ("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 "employee" :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 
+ (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 
+ (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/do-query/1
     (let ((result '()))
 
 (deftest :fdml/do-query/1
     (let ((result '()))


@@ -402,6 +469,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)