;;;; File: test-fdml.lisp
;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>
;;;; Created: 30/03/2004
-;;;; Updated: $Id: $
-;;;; ======================================================================
-;;;;
-;;;; Description ==========================================================
-;;;; ======================================================================
+;;;; Updated: $Id$
;;;;
;;;; Tests for the CLSQL Functional Data Manipulation Language
;;;; (FDML).
-;;;;
+;;;;
+;;;; This file is part of CLSQL.
+;;;;
+;;;; CLSQL users are granted the rights to distribute and use this software
+;;;; as governed by the terms of the Lisp Lesser GNU Public License
+;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
;;;; ======================================================================
(in-package #:clsql-tests)
#.(clsql:locally-enable-sql-reader-syntax)
+(setq *rt-fdml*
+ '(
+
;; inserts a record using all values only and then deletes it
(deftest :fdml/insert/1
(progn
(deftest :fdml/query/1
- (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')")
+ (clsql:query "SELECT COUNT(*) FROM EMPLOYEE WHERE (EMAIL LIKE '%org')" :field-names nil)
(("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/execute-command/1
;; 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)
+ (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 #'(lambda (s) (parse-integer s :junk-allowed t))
+ (append min avg max))))
t)
(deftest :fdml/select/2
("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)))
+ (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]
+ :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))
(deftest :fdml/select/7
- (sql:select [max [emplid]] :from [employee] :flatp t)
+ (clsql:select [max [emplid]] :from [employee] :flatp t)
("10"))
(deftest :fdml/select/8
- (sql:select [min [emplid]] :from [employee] :flatp t)
+ (clsql:select [min [emplid]] :from [employee] :flatp t)
("1"))
(deftest :fdml/select/9
- (subseq (car (sql:select [avg [emplid]] :from [employee] :flatp t)) 0 3)
+ (subseq (car (clsql:select [avg [emplid]] :from [employee] :flatp t)) 0 3)
"5.5")
(deftest :fdml/select/10
- (sql:select [last-name] :from [employee]
+ (clsql:select [last-name] :from [employee]
:where [not [in [emplid]
- [select [managerid] :from [company]]]]
+ [select [managerid] :from [company]]]]
:flatp t
:order-by [last-name])
("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Putin" "Stalin"
(("Lenin")))
;(deftest :fdml/select/11
-; (sql:select [emplid] :from [employee]
+; (clsql:select [emplid] :from [employee]
; :where [= [emplid] [any [select [companyid] :from [company]]]]
; :flatp t)
; ("1"))
;; test if we are in a transaction
(push (clsql:in-transaction-p) results)
;;Putin has got to go
- (unless (eql *test-database-type* :mysql)
- (clsql:delete-records :from [employee] :where [= [last-name] "Putin"]))
+ (clsql:delete-records :from [employee] :where [= [last-name] "Putin"])
;;Should be nil
(push
(clsql:select [*] :from [employee] :where [= [last-name] "Putin"])
;; test if we are in a transaction
(push (clsql:in-transaction-p) results)
;;Putin has got to go
- (unless (eql *test-database-type* :mysql)
- (clsql:update-records [employee]
- :av-pairs '((email "putin-nospam@soviet.org"))
- :where [= [last-name] "Putin"]))
+ (clsql:update-records [employee]
+ :av-pairs '((email "putin-nospam@soviet.org"))
+ :where [= [last-name] "Putin"])
;;Should be new value
(push (clsql:select [email] :from [employee]
:where [= [last-name] "Putin"]
: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
(let ((results '()))
;; check status
(push (clsql:in-transaction-p) results)
- (unless (eql *test-database-type* :mysql)
- (handler-case
- (clsql:with-transaction ()
- ;; valid update
- (clsql:update-records [employee]
- :av-pairs '((email "lenin-nospam@soviet.org"))
- :where [= [emplid] 1])
- ;; invalid update which generates an error
+ (handler-case
+ (clsql:with-transaction ()
+ ;; valid update
+ (clsql:update-records [employee]
+ :av-pairs '((email "lenin-nospam@soviet.org"))
+ :where [= [emplid] 1])
+ ;; invalid update which generates an error
(clsql:update-records [employee]
- :av-pairs
- '((emale "lenin-nospam@soviet.org"))
- :where [= [emplid] 1]))
- (clsql:clsql-sql-error ()
+ :av-pairs
+ '((emale "lenin-nospam@soviet.org"))
+ :where [= [emplid] 1]))
+ (clsql:clsql-error ()
(progn
;; check status
(push (clsql:in-transaction-p) results)
(push (clsql:select [email] :from [employee] :where [= [emplid] 1]
:flatp t)
results)
- (apply #'values (nreverse results)))))))
+ (apply #'values (nreverse results))))))
nil nil ("lenin@soviet.org"))
+))
+
#.(clsql:restore-sql-reader-syntax-state)
projects / clsql.git / blobdiff