: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
: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
(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"))
(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]]
: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
(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]
("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"
+ ("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)]
(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
: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
["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
: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
: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
: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]
: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/do-query/1
(let ((result '()))
(clsql:do-query ((name) [select [last-name] :from [employee]
[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
[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
: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.