r10922: 03 May 2006 Kevin Rosenberg <kevin@rosenberg.net>

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

diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp
index eac258f278200a4f5db41fc19428c5655a97dbc7..0fbcb17a9756b6695f46851bc20ae2b09d1679e9 100644 (file)
--- a/tests/test-fdml.lisp
+++ b/tests/test-fdml.lisp
@@ -96,14 +96,14 @@
                     :where [= [emplid] 1])
        (progn
          (clsql:update-records [employee] 
-                              :av-pairs'((first_name "Vladamir")
+                              :av-pairs'((first_name "Vladimir")
                                          (last_name "Lenin")
                                          (email "lenin@soviet.org"))
                               :where [= [emplid] 1])
          (clsql:select [first-name] [last-name] [email] :from [employee]
                       :where [= [emplid] 1]))))
   (("Yuri" "Gagarin" "gagarin@soviet.org"))
-  (("Vladamir" "Lenin" "lenin@soviet.org")))
+  (("Vladimir" "Lenin" "lenin@soviet.org")))
 
 ;; updates a record using av-pairs and then deletes it
 (deftest :fdml/update/2
@@ -118,14 +118,14 @@
                     :where [= [emplid] 1])
        (progn
          (clsql:update-records [employee]
-                              :av-pairs'((first_name "Vladamir")
+                              :av-pairs'((first_name "Vladimir")
                                          (last_name "Lenin")
                                          (email "lenin@soviet.org"))
                               :where [= [emplid] 1])
          (clsql:select [first-name] [last-name] [email]
                       :from [employee] :where [= [emplid] 1]))))
   (("Yuri" "Gagarin" "gagarin@soviet.org"))
-  (("Vladamir" "Lenin" "lenin@soviet.org")))
+  (("Vladimir" "Lenin" "lenin@soviet.org")))
 
 
 ;; Computed values are not always classified as numeric by psqlodbc
@@ -141,7 +141,7 @@
        (clsql:query
         "SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEE WHERE (EMPLID <= 5) ORDER BY LAST_NAME")
       (values rows (mapcar 'string-upcase field-names)))
-  (("Leonid" "Brezhnev") ("Nikita" "Kruschev") ("Vladamir" "Lenin")
+  (("Leonid" "Brezhnev") ("Nikita" "Kruschev") ("Vladimir" "Lenin")
    ("Josef" "Stalin") ("Leon" "Trotsky"))
   ("FIRST_NAME" "LAST_NAME"))
 
@@ -161,7 +161,7 @@
       (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)))
+  ("Konstantin" 7) ("Mikhail" 8) ("Boris" 9) ("Vladimir" 11)))
 
 (deftest :fdml/query/6
     (let ((res (clsql:query (clsql:sql [union [select [emplid] :from [employee]] 
@@ -227,7 +227,7 @@
                             :field-names nil 
                             :result-types nil 
                             :order-by [first-name])
- ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
   "Yuri"))
 
 (deftest :fdml/select/3
@@ -239,7 +239,7 @@
       (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)))
+   ("Mikhail" 1) ("Nikita" 1) ("Vladimir" 2) ("Yuri" 1)))
 
 (deftest :fdml/select/4
     (clsql:select [last-name] :from [employee] 
@@ -368,16 +368,19 @@
  ("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 
-  (clsql:select [|| [first-name] " " [last-name]] :from [employee]
-                :flatp t :order-by [emplid] :field-names nil)
- ("Vladamir Lenin" "Josef Stalin" "Leon Trotsky" "Nikita Kruschev"
+   (case *test-database-underlying-type*
+     (:mssql (clsql:select [+ [first-name] " " [last-name]] :from [employee]
+                           :flatp t :order-by [emplid] :field-names nil))
+     (t (clsql:select [|| [first-name] " " [last-name]] :from [employee]
+                      :flatp t :order-by [emplid] :field-names nil)))
+ ("Vladimir Lenin" "Josef Stalin" "Leon Trotsky" "Nikita Kruschev"
  "Leonid Brezhnev" "Yuri Andropov" "Konstantin Chernenko" "Mikhail Gorbachev"
- "Boris Yeltsin" "Vladamir Putin"))
+ "Boris Yeltsin" "Vladimir Putin"))
 
 (deftest :fdml/select/23
  (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)]
@@ -388,7 +391,7 @@
 (deftest :fdml/select/24
  (clsql:select [distinct [first-name]] :from [employee] :flatp t
                :order-by [first-name] :field-names nil :result-types nil)
- ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
   "Yuri"))
 
 (deftest :fdml/select/25
@@ -397,7 +400,7 @@
   :field-names nil 
   :result-types nil 
   :order-by [first-name])
- ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
+ ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
   "Yuri"))
 
 (deftest :fdml/select/26
@@ -409,7 +412,7 @@
                       ["join" emplid]]]]
   :order-by '(["table" last-name])
   :result-types nil :field-names nil)
- (("Vladamir" "Lenin") ("Vladamir" "Putin")))
+ (("Vladimir" "Lenin") ("Vladimir" "Putin")))
 
 (deftest :fdml/select/27 
     (mapcar
@@ -432,7 +435,7 @@
                        :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")
+  ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
   ("Yuri" "Andropov")))
 
 (deftest :fdml/select/30 
@@ -441,7 +444,7 @@
                        :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")
+  ("Nikita" "Kruschev") ("Vladimir" "Lenin") ("Vladimir" "Putin")
   ("Yuri" "Andropov")))
 
 (deftest :fdml/select/31
@@ -453,7 +456,7 @@
               :field-names nil)
  ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
  "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
- "Trotsky" "Vladamir" "Yeltsin" "Yuri"))
+ "Trotsky" "Vladimir" "Yeltsin" "Yuri"))
 
 (deftest :fdml/select/32
     (clsql:select [emplid] :from [employee]
@@ -478,6 +481,37 @@
                  :flatp t :result-types nil :field-names nil)))
   ("Lenin" "Stalin" "Trotsky" "Kruschev"))
 
+;; test escaping of single quotes 
+(deftest :fdml/select/35 
+    (clsql:select "What's up doc?" :from [employee] :flatp t :field-names nil)
+  ("What's up doc?" "What's up doc?" "What's up doc?" "What's up doc?"
+   "What's up doc?" "What's up doc?" "What's up doc?" "What's up doc?"
+   "What's up doc?" "What's up doc?"))
+
+;; test proper treatment of backslash (depending on backend) 
+(deftest :fdml/select/36
+    (clsql:select "foo\\bar\\baz" :from [employee] :flatp t :field-names nil)
+ ("foo\\bar\\baz" "foo\\bar\\baz" "foo\\bar\\baz" "foo\\bar\\baz" 
+  "foo\\bar\\baz"  "foo\\bar\\baz" "foo\\bar\\baz" "foo\\bar\\baz" 
+  "foo\\bar\\baz" "foo\\bar\\baz"))
+
+(deftest :fdml/select/37
+    (clsql:select [emplid] :from [employee] 
+                  :order-by [emplid]
+                  :limit 5 
+                  :field-names nil
+                  :flatp t)
+  (1 2 3 4 5))
+
+(deftest :fdml/select/38
+    (clsql:select [emplid] :from [employee] 
+                  :order-by [emplid]
+                  :limit 5 
+                  :offset 3
+                  :field-names nil
+                  :flatp t)
+  (4 5 6 7 8))
+
 (deftest :fdml/do-query/1
     (let ((result '()))
     (clsql:do-query ((name) [select [last-name] :from [employee]
@@ -512,8 +546,8 @@
                   [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") 
+  ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladimir" "Lenin")
+  ("Vladimir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky") 
   ("Boris" "Yeltsin")))
   
 (deftest :fdml/loop/1
@@ -522,7 +556,7 @@
       [select [first-name] [last-name] :from [employee] :order-by [last-name]]
       collect (concatenate 'string forename " " surname))
   ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
-                   "Nikita Kruschev" "Vladamir Lenin" "Vladamir Putin"
+                   "Nikita Kruschev" "Vladimir Lenin" "Vladimir Putin"
    "Josef Stalin" "Leon Trotsky" "Boris Yeltsin"))
 
 (deftest :fdml/loop/2
@@ -626,8 +660,7 @@
                          :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.