(("Vladamir" "Lenin" "lenin@soviet.org")))
+;; Computed values are not always classified as numeric by psqlodbc
(deftest :fdml/query/1
- (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')")
- (("10")))
+ (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))
+ count))
+ 10)
(deftest :fdml/query/2
- (clsql:query
- "SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEE WHERE (EMPLID <= 5) ORDER BY LAST_NAME")
+ (multiple-value-bind (rows field-names)
+ (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")
- ("Josef" "Stalin") ("Leon" "Trotsky")))
+ ("Josef" "Stalin") ("Leon" "Trotsky"))
+ ("FIRST_NAME" "LAST_NAME"))
+(deftest :fdml/query/3
+ (caar (clsql:query "SELECT EMPLID FROM EMPLOYEE WHERE LAST_NAME = 'Andropov'" :field-names nil))
+ 6)
+
+(deftest :fdml/query/4
+ (typep (caar (clsql:query "SELECT HEIGHT FROM EMPLOYEE WHERE LAST_NAME = 'Andropov'" :field-names nil))
+ 'float)
+ t)
(deftest :fdml/execute-command/1
(values
;; compare min, max and average hieghts in inches (they're quite short
-;; these guys!) -- only works with pgsql
+;; these guys!)
(deftest :fdml/select/1
- (if (member *test-database-type* '(:postgresql-socket :postgresql))
- (let ((max (clsql:select [function "floor"
- [/ [* [max [height]] 100] 2.54]]
- :from [employee]
- :flatp t))
- (min (clsql:select [function "floor"
- [/ [* [min [height]] 100] 2.54]]
- :from [employee]
- :flatp t))
- (avg (clsql:select [function "floor"
- [avg [/ [* [height] 100] 2.54]]]
- :from [employee]
- :flatp t)))
- (apply #'< (mapcar #'parse-integer (append min avg max))))
- t)
- t)
+ (let ((max (clsql:select [function "floor"
+ [/ [* [max [height]] 100] 2.54]]
+ :from [employee]
+ :result-types nil
+ :flatp t))
+ (min (clsql:select [function "floor"
+ [/ [* [min [height]] 100] 2.54]]
+ :from [employee]
+ :result-types nil
+ :flatp t))
+ (avg (clsql:select [function "floor"
+ [avg [/ [* [height] 100] 2.54]]]
+ :from [employee]
+ :result-types nil
+ :flatp t)))
+ (apply #'< (mapcar #'(lambda (s) (parse-integer s :junk-allowed t))
+ (append min avg max))))
+ t)
(deftest :fdml/select/2
- (clsql:select [first-name] :from [employee] :flatp t :distinct t
- :order-by [first-name])
- ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
- "Yuri"))
+ (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/3
(clsql:select [first-name] [count [*]] :from [employee]
- :group-by [first-name]
- :order-by [first-name])
- (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
- ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1")))
+ :result-types nil
+ :group-by [first-name]
+ :order-by [first-name]
+ :field-names nil)
+ (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
+ ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1")))
(deftest :fdml/select/4
- (clsql:select [last-name] :from [employee] :where [like [email] "%org"]
- :order-by [last-name]
- :flatp t)
- ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
- "Stalin" "Trotsky" "Yeltsin"))
+ (clsql:select [last-name] :from [employee]
+ :where [like [email] "%org"]
+ :order-by [last-name]
+ :field-names nil
+ :result-types nil
+ :flatp t)
+ ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
+ "Stalin" "Trotsky" "Yeltsin"))
(deftest :fdml/select/5
- (clsql:select [email] :from [employee] :flatp t
- :where [in [employee emplid]
- [select [managerid] :from [employee]]])
+ (clsql:select [email] :from [employee] :flatp t :result-types nil
+ :where [in [employee emplid]
+ [select [managerid] :from [employee]]]
+ :field-names nil)
("lenin@soviet.org"))
(deftest :fdml/select/6
- (if (member *test-database-type* '(:postgresql-socket :postgresql))
- (mapcar #'parse-integer
- (clsql:select [function "trunc" [height]] :from [employee]
- :flatp t))
- (mapcar #'(lambda (s) (truncate (parse-integer s :junk-allowed t)))
- (clsql:select [height] :from [employee] :flatp t)))
- (1 1 1 1 1 1 1 1 1 1))
+ (if (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
+ :field-names nil
+ :flatp t))
+ (mapcar #'(lambda (s) (truncate (parse-integer s :junk-allowed t)))
+ (clsql:select [height] :from [employee] :flatp t
+ :field-names nil :result-types nil)))
+ (1 1 1 1 1 1 1 1 1 1))
(deftest :fdml/select/7
- (clsql:select [max [emplid]] :from [employee] :flatp t)
+ (clsql:select [max [emplid]] :from [employee] :flatp t
+ :field-names nil :result-types nil)
("10"))
(deftest :fdml/select/8
- (clsql:select [min [emplid]] :from [employee] :flatp t)
+ (clsql:select [min [emplid]] :from [employee] :flatp t
+ :field-names nil :result-types nil)
("1"))
(deftest :fdml/select/9
- (subseq (car (clsql:select [avg [emplid]] :from [employee] :flatp t)) 0 3)
+ (subseq
+ (car
+ (clsql:select [avg [emplid]] :from [employee] :flatp t
+ :field-names nil :result-types nil))
+ 0 3)
"5.5")
(deftest :fdml/select/10
(clsql:select [last-name] :from [employee]
- :where [not [in [emplid]
- [select [managerid] :from [company]]]]
- :flatp t
- :order-by [last-name])
- ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Putin" "Stalin"
- "Trotsky" "Yeltsin"))
+ :where [not [in [emplid]
+ [select [managerid] :from [company]]]]
+ :result-types nil
+ :field-names nil
+ :flatp t
+ :order-by [last-name])
+ ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Putin" "Stalin"
+ "Trotsky" "Yeltsin"))
(deftest :fdml/select/11
(clsql:select [last-name] :from [employee] :where [married] :flatp t
- :order-by [emplid])
+ :field-names nil :order-by [emplid] :result-types nil)
("Lenin" "Stalin" "Trotsky"))
(deftest :fdml/select/12
(let ((v 1))
- (clsql:select [last-name] :from [employee] :where [= [emplid] v]))
+ (clsql:select [last-name] :from [employee] :where [= [emplid] v]
+ :field-names nil :result-types nil))
(("Lenin")))
+(deftest :fdml/select/13
+ (multiple-value-bind (results field-names)
+ (clsql:select [emplid] [last-name] [married] :from [employee]
+ :where [= [emplid] 1])
+ (values results (mapcar #'string-downcase field-names)))
+ ((1 "Lenin" "t"))
+ ("emplid" "last_name" "married"))
+
+(deftest :fdml/select/14
+ (floatp (car (clsql:select [height] :from [employee] :where [= [emplid] 1]
+ :flatp t)))
+ t)
+
+(deftest :fdml/select/15
+ (clsql:select [married] :from [employee]
+ :where [= [emplid] 4]
+ :field-names nil)
+ (("f")))
+
;(deftest :fdml/select/11
; (clsql:select [emplid] :from [employee]
; :where [= [emplid] [any [select [companyid] :from [company]]]]
:flatp t)
results)
(apply #'values (nreverse results)))
- nil :COMMITTED nil ("lenin-nospam@soviet.org") :COMMITTED
+ nil :committed nil ("lenin-nospam@soviet.org") :committed
nil ("lenin@soviet.org"))
;; runs a valid update and an invalid one within a transaction and checks