X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=tests%2Ftest-fdml.lisp;h=51cdd02ef417cb9c549d341dac085b5fbddff387;hb=f67c4e2a4e5b8371a1b7c1629828999ff909f538;hp=053dbc45f7c02a0a9ee0304663ed1b3d7f96443a;hpb=809eb398e2acc83bc526b0dc174a300d000b938e;p=clsql.git diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp index 053dbc4..51cdd02 100644 --- a/tests/test-fdml.lisp +++ b/tests/test-fdml.lisp @@ -3,7 +3,6 @@ ;;;; File: test-fdml.lisp ;;;; Author: Marcus Pearce , Kevin Rosenberg ;;;; Created: 30/03/2004 -;;;; Updated: $Id$ ;;;; ;;;; Tests for the CLSQL Functional Data Manipulation Language ;;;; (FDML). @@ -17,8 +16,7 @@ (in-package #:clsql-tests) -#.(clsql:locally-enable-sql-reader-syntax) - +(clsql-sys:file-enable-sql-reader-syntax) ;;started defining an independent dataset that doesn't depend on the view-classes ;; but there is a *lot* of stuff in the file assuming that dataset. ;; (def-dataset *ds-fdml* @@ -57,9 +55,7 @@ (deftest :fdml/query/1 (with-dataset *ds-employees* (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)) - (nth-value 0 (truncate count))))) + (%get-int count))) 10) (deftest :fdml/query/2 @@ -88,7 +84,7 @@ (let ((res (clsql:query (clsql:sql [select [first-name] [sum [emplid]] :from [employee]] [group-by [first-name]] [order-by [sum [emplid]]]) :field-names nil :result-types nil))) - (mapcar (lambda (p) (list (car p) (truncate (read-from-string (second p))))) + (mapcar (lambda (p) (list (car p) (%get-int (second p)))) res))) (("Josef" 2) ("Leon" 3) ("Nikita" 4) ("Leonid" 5) ("Yuri" 6) ("Konstantin" 7) ("Mikhail" 8) ("Boris" 9) ("Vladimir" 11))) @@ -99,8 +95,9 @@ [select [groupid] :from [company]]]) :field-names nil :result-types nil :flatp t ))) - (values (every #'stringp res) - (sort (mapcar #'(lambda (f) (truncate (read-from-string f))) res) + (values (or (eql *test-database-type* :postgresql-socket3) + (every #'stringp res)) + (sort (mapcar #'%get-int res) #'<=)))) t (1 2 3 4 5 6 7 8 9 10)) @@ -109,8 +106,9 @@ (let ((res (car (clsql:query (clsql:sql [intersect [select [emplid] :from [employee]] [select [groupid] :from [company]]]) :field-names nil :result-types nil :flatp t)))) - (values (stringp res) - (nth-value 0 (truncate (read-from-string res)))))) + (values (or (stringp res) + (eql *test-database-type* :postgresql-socket3)) + (nth-value 0 (%get-int res))))) t 1) (deftest :fdml/query/8 @@ -118,25 +116,12 @@ (let ((res (clsql:query (clsql:sql [except [select [emplid] :from [employee]] [select [groupid] :from [company]]]) :field-names nil :result-types nil :flatp t))) - (values (every #'stringp res) - (sort (mapcar #'(lambda (f) (truncate (read-from-string f))) res) + (values (or (every #'stringp res) + (eql *test-database-type* :postgresql-socket3)) + (sort (mapcar #'%get-int res) #'<=)))) t (2 3 4 5 6 7 8 9 10)) - -(deftest :fdml/execute-command/1 - (with-dataset *ds-employees* - (values - (clsql:table-exists-p [foo] :owner *test-database-user*) - (progn - (clsql:execute-command "create table foo (bar integer)") - (clsql:table-exists-p [foo] :owner *test-database-user*)) - (progn - (clsql:execute-command "drop table foo") - (clsql:table-exists-p [foo] :owner *test-database-user*)))) - nil t nil) - - ;; compare min, max and average hieghts in inches (they're quite short ;; these guys!) (deftest :fdml/select/1 @@ -156,8 +141,7 @@ :from [employee] :result-types nil :flatp t))) - (apply #'< (mapcar #'(lambda (s) (parse-integer s :junk-allowed t)) - (append min avg max))))) + (apply #'< (mapcar #'%get-int (append min avg max))))) t) (deftest :fdml/select/2 @@ -176,7 +160,7 @@ :group-by [first-name] :order-by [first-name] :field-names nil))) - (mapcar (lambda (p) (list (car p) (truncate (read-from-string (second p))))) + (mapcar (lambda (p) (list (car p) (%get-int (second p)))) res))) (("Boris" 1) ("Josef" 1) ("Konstantin" 1) ("Leon" 1) ("Leonid" 1) ("Mikhail" 1) ("Nikita" 1) ("Vladimir" 2) ("Yuri" 1))) @@ -203,12 +187,12 @@ (deftest :fdml/select/6 (with-dataset *ds-employees* (if (clsql-sys:db-type-has-fancy-math? *test-database-underlying-type*) - (mapcar #'(lambda (s) (parse-integer s :junk-allowed t)) + (mapcar #'%get-int (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))) + (mapcar #'%get-int (clsql:select [height] :from [employee] :flatp t :field-names nil :result-types nil)))) (1 1 1 1 1 1 1 1 1 1)) @@ -218,27 +202,26 @@ (let ((result (car (clsql:select [max [emplid]] :from [employee] :flatp t :field-names nil :result-types nil)))) (values - (stringp result) - (nth-value 0 (truncate (read-from-string result)))))) - t 10) + (nth-value 0 (%get-int result))))) + 10) (deftest :fdml/select/8 (with-dataset *ds-employees* (let ((result (car (clsql:select [min [emplid]] :from [employee] :flatp t :field-names nil :result-types nil)))) (values - (stringp result) - (nth-value 0 (truncate (read-from-string result)))))) - t 1) + (nth-value 0 (%get-int result))))) + 1) (deftest :fdml/select/9 - (with-dataset *ds-employees* - (subseq - (car - (clsql:select [avg [emplid]] :from [employee] :flatp t - :field-names nil :result-types nil)) - 0 3)) - "5.5") + (with-dataset *ds-employees* + (let ((val (car (clsql:select + [avg [emplid]] :from [employee] :flatp t + :field-names nil :result-types nil)))) + (typecase val + (string (subseq val 0 3)) + (number (format nil "~,1F" val))))) + "5.5") (deftest :fdml/select/10 (with-dataset *ds-employees* @@ -311,18 +294,21 @@ (("1" "Lenin"))) (deftest :fdml/select/19 - (with-dataset *ds-employees* - (clsql:select [emplid] :from [employee] :order-by [emplid] - :where [between [* [emplid] 10] [* 5 10] [* 10 10]] - :field-names nil :result-types nil :flatp t)) - ("5" "6" "7" "8" "9" "10")) + (with-dataset *ds-employees* + (mapcar + #'%get-int + (clsql:select [emplid] :from [employee] :order-by [emplid] + :where [between [* [emplid] 10] [* 5 10] [* 10 10]] + :field-names nil :result-types nil :flatp t))) + (5 6 7 8 9 10)) (deftest :fdml/select/20 (with-dataset *ds-employees* + (mapcar #'%get-int (clsql:select [emplid] :from [employee] :order-by [emplid] :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]] - :field-names nil :result-types nil :flatp t)) - ("1" "2" "3" "4")) + :field-names nil :result-types nil :flatp t))) + (1 2 3 4)) (deftest :fdml/select/21 (with-dataset *ds-employees* @@ -342,11 +328,12 @@ "Boris Yeltsin" "Vladimir Putin")) (deftest :fdml/select/23 - (with-dataset *ds-employees* - (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)] - :flatp t :order-by [emplid] :field-names nil - :result-types nil)) - ("1" "2" "3" "4")) + (with-dataset *ds-employees* + (mapcar #'%get-int + (clsql:select [emplid] :from [employee] :where [in [emplid] '(1 2 3 4)] + :flatp t :order-by [emplid] :field-names nil + :result-types nil))) + (1 2 3 4)) (deftest :fdml/select/24 (with-dataset *ds-employees* @@ -380,19 +367,20 @@ (deftest :fdml/select/27 (with-dataset *ds-employees* (mapcar - (lambda (f) (truncate (read-from-string f))) + #'%get-int (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 - (with-dataset *ds-employees* - (mapcar - (lambda (f) (truncate (read-from-string (car f)))) - (loop for column in `([*] [emplid]) collect - (clsql:select [count column] :from [employee] - :flatp t :result-types nil :field-names nil)))) - (10 10)) + (with-dataset *ds-employees* + (loop for column in `([*] [emplid]) + collect + (%get-int + (car + (clsql:select [count column] :from [employee] + :flatp t :result-types nil :field-names nil))))) + (10 10)) (deftest :fdml/select/29 (with-dataset *ds-employees* @@ -428,10 +416,12 @@ (deftest :fdml/select/32 (with-dataset *ds-employees* - (clsql:select [emplid] :from [employee] - :where [= [emplid] [any [select [companyid] :from [company]]]] - :flatp t :result-types nil :field-names nil)) - ("1")) + (mapcar + #'%get-int + (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 (with-dataset *ds-employees* @@ -542,7 +532,7 @@ being each tuple in [select [addressid] :from [addr] :order-by [addressid]] collect addressid)) - (1 2)) + (1 2 3)) (deftest :fdml/loop/3 (with-dataset *ds-employees* @@ -550,15 +540,16 @@ being each tuple in [select [addressid] :from [addr] :order-by [addressid]] collect addressid)) - (1 2)) + (1 2 3)) ;; inserts a record using all values only and then deletes it (deftest :fdml/insert/1 (with-dataset *ds-employees* (let ((now (get-universal-time))) - (clsql:insert-records :into [employee] - :values `(11 1 "Yuri" "Gagarin" "gagarin@soviet.org" - 1 1 1.85 t ,(clsql:utime->time now) ,now)) + (clsql:insert-records + :into [employee] + :values `(11 1 "clsql-sys::astronaut" "Yuri" "Gagarin" "gagarin@soviet.org" + 1 1 1.85 t ,(clsql:utime->time now) ,now)) (values (clsql:select [first-name] [last-name] [email] :from [employee] :where [= [emplid] 11]) @@ -663,8 +654,6 @@ (("Yuri" "Gagarin" "gagarin@soviet.org")) (("Vladimir" "Lenin" "lenin@soviet.org"))) - - ;; starts a transaction deletes a record and then rolls back the deletion (deftest :fdml/transaction/1 (with-dataset *ds-employees* @@ -789,4 +778,3 @@ )) -#.(clsql:restore-sql-reader-syntax-state)