;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
;;;; ======================================================================
;;;; File: test-fdml.lisp
-;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>
+;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>, Kevin Rosenberg
;;;; Created: 30/03/2004
;;;; Updated: $Id$
;;;;
("lenin@soviet.org"))
(deftest :fdml/select/6
- (if (db-type-has-fancy-math? *test-database-underlying-type*)
+ (if (clsql-sys:db-type-has-fancy-math? *test-database-underlying-type*)
(mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
(clsql:select [function "trunc" [height]] :from [employee]
:result-types nil
("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"
("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 (clsql-sys:convert-to-db-default-case "employee" *default-database*)
+ :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/select/34
+ (loop for x from 1 below 5
+ collect
+ (car
+ (clsql:select [last-name] :from [employee]
+ :where [= [emplid] x]
+ :flatp t :result-types nil :field-names nil)))
+ ("Lenin" "Stalin" "Trotsky" "Kruschev"))
(deftest :fdml/do-query/1
(let ((result '()))
: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)
:av-pairs
'((emale "lenin-nospam@soviet.org"))
:where [= [emplid] 1]))
- (clsql:clsql-error ()
+ (clsql:sql-database-error ()
(progn
;; check status
(push (clsql:in-transaction-p) results)