From: Marcus Pearce Date: Sun, 16 May 2004 11:17:49 +0000 (+0000) Subject: r9373: Treat [*] as a column identifier. X-Git-Tag: v3.8.6~447 X-Git-Url: http://git.kpe.io/?p=clsql.git;a=commitdiff_plain;h=692ff4990d0cd04685531f524801b16d21cfbb49 r9373: Treat [*] as a column identifier. --- diff --git a/ChangeLog b/ChangeLog index e800941..9eba9b9 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,13 @@ +16 May 2004 Marcus Pearce (m.t.pearce@city.ac.uk) + * sql/syntax.lisp: added condition to the reader macro to treat [*] + as a column identifier (rather than an operation) for CommonSQL + compatibility. + * tests/test-fdml.lisp: add tests for ORDER-BY and SET-OPERATION + keword args to SELECT, [*] as column identifier, new MAP-QUERY + behaviour and the ANY and ALL operators in subqueries. + * tests/test-init.lisp: add set-operation and subquery tests to + appropriate skip lists. + 15 May 2004 Kevin Rosenberg (kevin@rosenberg.net) * Version 2.10.18 * sql/db-interface.lisp: Add new db-type-has-union? diff --git a/sql/syntax.lisp b/sql/syntax.lisp index 7a619f0..6d771da 100644 --- a/sql/syntax.lisp +++ b/sql/syntax.lisp @@ -86,6 +86,8 @@ syntax is disabled." (let ((sqllist (read-delimited-list #\] stream t))) (cond ((string= (write-to-string (car sqllist)) "||") (cons (sql-operator 'concat) (cdr sqllist))) + ((and (= (length sqllist) 1) (eql (car sqllist) '*)) + (apply #'generate-sql-reference sqllist)) ((sql-operator (car sqllist)) (cons (sql-operator (car sqllist)) (cdr sqllist))) (t (apply #'generate-sql-reference sqllist))))) diff --git a/tests/test-fdml.lisp b/tests/test-fdml.lisp index f764e73..adbb847 100644 --- a/tests/test-fdml.lisp +++ b/tests/test-fdml.lisp @@ -398,11 +398,54 @@ :field-names nil :result-types nil :flatp t) ("10" "1" "1" "1" "1" "1" "1" "1" "1" "1")) -;(deftest :fdml/select/11 -; (clsql:select [emplid] :from [employee] -; :where [= [emplid] [any [select [companyid] :from [company]]]] -; :flatp t) -; ("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/do-query/1 (let ((result '())) @@ -426,6 +469,21 @@ :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) diff --git a/tests/test-init.lisp b/tests/test-init.lisp index 70d54d7..1ac0207 100644 --- a/tests/test-init.lisp +++ b/tests/test-init.lisp @@ -541,7 +541,8 @@ (clsql-sys:in test :fdml/select/11 :oodml/select/5)) (push (cons test "boolean where not supported") skip-tests)) ((and (null (clsql-sys:db-type-has-subqueries? db-underlying-type)) - (clsql-sys:in test :fdml/select/5 :fdml/select/10)) + (clsql-sys:in test :fdml/select/5 :fdml/select/10 + :fdml/select/32 :fdml/select/33)) (push (cons test "subqueries not supported") skip-tests)) ((and (null (clsql-sys:db-type-transaction-capable? db-underlying-type *default-database*)) @@ -552,14 +553,15 @@ (push (cons test "fancy math not supported") skip-tests)) ((and (eql *test-database-type* :sqlite) (clsql-sys:in test :fddl/view/4 :fdml/select/10 - :fdml/select/21)) + :fdml/select/21 :fdml/select/32 + :fdml/select/33)) (push (cons test "not supported by sqlite") skip-tests)) ((and (eql *test-database-underlying-type* :mysql) (clsql-sys:in test :fdml/select/22 :fdml/query/5 :fdml/query/7 :fdml/query/8)) (push (cons test "not supported by mysql") skip-tests)) ((and (null (clsql-sys:db-type-has-union? db-underlying-type)) - (clsql-sys:in test :fdml/query/6)) + (clsql-sys:in test :fdml/query/6 :fdml/select/31)) (push (cons test "union not supported") skip-tests)) (t (push test-form test-forms)))))