X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-syntax.lisp;h=01a76123d413021926d6e4cd199b56ef6fb0e765;hp=cd8d2ba1e4d08604800fe5c20d745b4f1620e472;hb=b50166ae0ba2bc09a9094c0e675ec92010b7293e;hpb=aad71482a312cf287e2f6e3e926cf671cd382ec7 diff --git a/tests/test-syntax.lisp b/tests/test-syntax.lisp index cd8d2ba..01a7612 100644 --- a/tests/test-syntax.lisp +++ b/tests/test-syntax.lisp @@ -1,25 +1,25 @@ ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ====================================================================== -;;;; File: test-syntax.lisp -;;;; Author: Marcus Pearce -;;;; Created: 30/03/2004 -;;;; Updated: $Id$ -;;;; ====================================================================== +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION ;;;; -;;;; Description ========================================================== -;;;; ====================================================================== +;;;; Name: clsql.asd +;;;; Purpose: Tests for the CLSQL Symbolic SQL syntax. +;;;; Authors: Marcus Pearce and Kevin M. Rosenberg +;;;; Created: March 2004 ;;;; -;;;; Tests for the CLSQL Symbolic SQL syntax. -;;;; -;;;; ====================================================================== +;;;; 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) +(clsql-sys:file-enable-sql-reader-syntax) + (setq *rt-syntax* '( - + (deftest :syntax/generic/1 (clsql:sql "foo") "'foo'") @@ -40,6 +40,9 @@ (clsql:sql ["SELECT FOO,BAR FROM BAZ"]) "SELECT FOO,BAR FROM BAZ") +(deftest :syntax/generic/6 + (clsql:sql "What's up Doc?") + "'What''s up Doc?'") (deftest :syntax/ident/1 (clsql:sql [foo]) @@ -50,29 +53,46 @@ "FOO.BAR") (deftest :syntax/ident/3 - (clsql:sql ["foo" bar]) - "FOO.BAR") - -;(deftest :syntax/ident/4 -; (clsql:sql [foo "bar"]) -; "FOO \"bar\"") - -(deftest :syntax/ident/5 (clsql:sql [foo :integer]) "FOO") -(deftest :syntax/ident/6 +(deftest :syntax/ident/4 (clsql:sql [foo bar :integer]) "FOO.BAR") +(deftest :syntax/ident/5 + (clsql:sql [foo "bar"]) + "FOO \"bar\"") + +(deftest :syntax/ident/6 + (clsql:sql ["foo" bar]) + "\"foo\".BAR") + (deftest :syntax/ident/7 (clsql:sql ["foo" bar :integer]) + "\"foo\".BAR") + + +(deftest :syntax/attribute/1 + (clsql:sql (clsql:sql-expression :table 'foo :attribute 'bar)) "FOO.BAR") +(deftest :syntax/attribute/2 + (clsql:sql (clsql:sql-expression :table 'foo :attribute "bar")) + "FOO.\"bar\"") + +(deftest :syntax/attribute/3 + (clsql:sql (clsql:sql-expression :table "foo" :attribute 'bar)) + "\"foo\".BAR") + +(deftest :syntax/attribute/4 + (clsql:sql (clsql:sql-expression :table "foo" :attribute "bar")) + "\"foo\".\"bar\"") + (deftest :syntax/subquery/1 (clsql:sql [any '(3 4)]) - "(ANY ((3,4)))") + "ANY((3,4))") (deftest :syntax/subquery/2 (clsql:sql [in [foo] '(foo bar baz)]) @@ -80,18 +100,18 @@ (deftest :syntax/subquery/3 (clsql:sql [all '(foo bar baz)]) - "(ALL ((FOO,BAR,BAZ)))") + "ALL((FOO,BAR,BAZ))") (deftest :syntax/subquery/4 (clsql:sql [exists '(foo bar baz)]) - "(EXISTS ((FOO,BAR,BAZ)))") + "EXISTS((FOO,BAR,BAZ))") (deftest :syntax/subquery/5 (clsql:sql [some '(foo bar baz)]) - "(SOME ((FOO,BAR,BAZ)))") + "SOME((FOO,BAR,BAZ))") -(deftest :syntax/aggregate/1 +(deftest :syntax/aggregate/1 (clsql:sql [max [+ [foo] [* 1000 [bar]]]]) "MAX((FOO + (1000 * BAR)))") @@ -112,27 +132,56 @@ "COUNT(FOO)") -(deftest :syntax/logical/1 - (clsql:sql [and [foo] [bar]]) - "(FOO AND BAR)") - -(deftest :syntax/logical/2 - (clsql:sql [or [foo] [bar]]) +(deftest :syntax/logical/1 + (values (clsql:sql [and [foo] [bar]]) + (clsql:sql [or [foo] [bar]])) + "(FOO AND BAR)" "(FOO OR BAR)") -(deftest :syntax/logical/3 +(deftest :syntax/logical/2 (clsql:sql [not [foo]]) "(NOT (FOO))") +;;; Test how we apply logical operators when we have different numbers of children +;;; This is useful if we wish to (apply #'sql-and some-list) without having to do +;;; alot of length checking +(deftest :syntax/logical/3 + (values (clsql:sql [and ]) + (clsql:sql [and [foo]]) + (clsql:sql [and [not [foo]]]) + (clsql:sql [and [foo] [bar] [baz]])) + "" + "FOO" + "(NOT (FOO))" + "(FOO AND BAR AND BAZ)") + +(deftest :syntax/logical/4 + (clsql:sql [and [= [foo] [bar]]]) + "(FOO = BAR)") + +(deftest :syntax/logical/5 + (clsql:sql [and [= [foo] [bar]] + [= [bar] [bast]] + [= [block] [blech]]]) + "((FOO = BAR) AND (BAR = BAST) AND (BLOCK = BLECH))") + +(deftest :syntax/logical/6 + (clsql:sql + (apply #'sql-and + (list [= [foo] [bar]] + [and ] + [and [= [bar] [bast]]]))) + "((FOO = BAR) AND (BAR = BAST))") + -(deftest :syntax/null/1 +(deftest :syntax/null/1 (clsql:sql [null [foo]]) "(FOO IS NULL)") (deftest :syntax/null/2 (clsql:sql [not [null [foo]]]) "(NOT ((FOO IS NULL)))") - + (deftest :syntax/null/3 (clsql:sql [null]) "NULL") @@ -141,6 +190,18 @@ (clsql:sql [not [null]]) "(NOT (NULL))") +(deftest :syntax/null/5 + (clsql:sql [is [foo.bar] [null]]) + "(FOO.BAR IS NULL)") + +(deftest :syntax/null/6 + (clsql:sql [is [foo.bar] [not-null]]) + "(FOO.BAR IS NOT NULL)") + +(deftest :syntax/null/7 + (clsql:sql [not-null [foo.bar]]) + "(FOO.BAR IS NOT NULL)") + (deftest :syntax/relational/1 @@ -169,16 +230,16 @@ "(BAZ <> BEEP)") -(deftest :syntax/between/1 +(deftest :syntax/between/1 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]) "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)") -(deftest :syntax/between/2 +(deftest :syntax/between/2 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]]) "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))") -(deftest :syntax/arithmetic/1 +(deftest :syntax/arithmetic/1 (clsql:sql [+ [foo bar] [baz]]) "(FOO.BAR + BAZ)") @@ -203,17 +264,25 @@ "(2 * 3)") -(deftest :syntax/substr/1 +(deftest :syntax/substr/1 (clsql:sql [substr [hello] 1 4]) + "SUBSTR(HELLO,1,4)") + +(deftest :syntax/substring/1 + (clsql:sql [substring [hello] 1 4]) "SUBSTRING(HELLO,1,4)") -(deftest :syntax/concat/1 +(deftest :syntax/concat/1 (clsql:sql [|| [foo] [bar] [baz]]) "(FOO || BAR || BAZ)") +(deftest :syntax/concat/2 + (clsql:sql [concat [foo] [bar]]) + "CONCAT(FOO,BAR)") + -(deftest :syntax/pattern/1 +(deftest :syntax/pattern/1 (clsql:sql [like [foo] "%v"]) "(FOO LIKE '%v')") @@ -222,7 +291,7 @@ "(NOT ((FOO LIKE '%v')))") -(deftest :syntax/distinct/1 +(deftest :syntax/distinct/1 (clsql:sql [distinct [foo bar :string]]) "DISTINCT FOO.BAR") @@ -231,20 +300,42 @@ "DISTINCT FOO, BAR") -(deftest :syntax/order-by/1 +(deftest :syntax/order-by/1 (clsql:sql [order-by [foo]]) "ORDER BY FOO") -(deftest :syntax/group-by/1 +(deftest :syntax/group-by/1 (clsql:sql [group-by [foo]]) "GROUP BY FOO") +(deftest :syntax/group-by/2 + (clsql:sql + (clsql-sys::make-query [foo] [bar] [count [foo]] + :from [table] + :group-by '([foo] [bar]) + :order-by '([foo] [bar]))) + "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR") + -(deftest :syntax/sets/1 +(deftest :syntax/coalesce/1 + (clsql:sql [coalesce [foo] [bar] "not specified"]) + "COALESCE(FOO,BAR,'not specified')") + +(deftest :syntax/coalesce/2 + (clsql:sql [nvl [foo] "not specified"]) + "COALESCE(FOO,'not specified')") + +(deftest :syntax/nvl/1 + (clsql:sql [nvl [foo] "not specified"]) + "COALESCE(FOO,'not specified')") + + + +(deftest :syntax/sets/1 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]]) "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR") -(deftest :syntax/sets/2 +(deftest :syntax/sets/2 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]]) "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR") @@ -252,6 +343,10 @@ (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]]) "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR") +(deftest :syntax/sets/4 + (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]]) + "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR") + (deftest :syntax/function/1 (clsql:sql [function "COS" [age]]) @@ -266,7 +361,7 @@ (clsql:sql [select [person_id] [surname] :from [person]]) "SELECT PERSON_ID,SURNAME FROM PERSON") -(deftest :syntax/query/2 +(deftest :syntax/query/2 (clsql:sql [select [foo] [bar *] :from '([baz] [bar]) :where [or [= [foo] 3] @@ -283,19 +378,19 @@ (deftest :syntax/query/4 (clsql:sql [select [count [*]] :from [emp]]) "SELECT COUNT(*) FROM EMP") - -(deftest :syntax/expression1 + +(deftest :syntax/expression/1 (clsql:sql (clsql:sql-operation 'select (clsql:sql-expression :table 'foo :attribute 'bar) (clsql:sql-expression :attribute 'baz) - :from (list + :from (list (clsql:sql-expression :table 'foo) (clsql:sql-expression :table 'quux)) :where - (clsql:sql-operation 'or + (clsql:sql-operation 'or (clsql:sql-operation '> (clsql:sql-expression :attribute 'baz) @@ -306,7 +401,7 @@ :attribute 'bar) "SU%")))) "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))") - + (deftest :syntax/expression/2 (clsql:sql (apply (clsql:sql-operator 'and) @@ -322,8 +417,49 @@ [like (clsql:sql-expression :table table :attribute 'baz) (clsql:sql table)]))) - "(BETWEEN(THISTIME.BAR,(HIP * HOP),42) AND (THISTIME.BAZ LIKE 'THISTIME') AND BETWEEN(NEXTTIME.BAR,(HIP * HOP),43) AND (NEXTTIME.BAZ LIKE 'NEXTTIME') AND BETWEEN(SOMETIME.BAR,(HIP * HOP),44) AND (SOMETIME.BAZ LIKE 'SOMETIME') AND BETWEEN(NEVER.BAR,(HIP * HOP),45) AND (NEVER.BAZ LIKE 'NEVER'))") - -)) - -#.(clsql:restore-sql-reader-syntax-state) + "(BETWEEN(THISTIME.BAR,(HIP * HOP),42) AND (THISTIME.BAZ LIKE 'THISTIME') AND BETWEEN(NEXTTIME.BAR,(HIP * HOP),43) AND (NEXTTIME.BAZ LIKE 'NEXTTIME') AND BETWEEN(SOMETIME.BAR,(HIP * HOP),44) AND (SOMETIME.BAZ LIKE 'SOMETIME') AND BETWEEN(NEVER.BAR,(HIP * HOP),45) AND (NEVER.BAZ LIKE 'NEVER'))" + ) + +(deftest :syntax/subqueries/query + (clsql:sql + (clsql:sql-operation 'select [*] + :from [foo] + :where [in [id] [select [id] :from [bar]]])) + "SELECT * FROM FOO WHERE (ID IN (SELECT ID FROM BAR))") + +(deftest :syntax/subqueries/delete + (clsql:sql + (make-instance 'clsql-sys::sql-delete + :from [foo] + :where [in [id] [select [id] :from [bar]]])) + "DELETE FROM FOO WHERE (ID IN (SELECT ID FROM BAR))") + +(deftest :syntax/subqueries/update + (clsql:sql + (make-instance 'clsql-sys::sql-update + :attributes (list [id]) + :values '(0) + :table [foo] + :where [in [id] [select [id] :from [bar]]])) + "UPDATE FOO SET ID = 0 WHERE (ID IN (SELECT ID FROM BAR))") + + )) + +(let ((tests '(((:foo) "FOO") + ((:foo-bar) "FOO_BAR") + (("foo") "\"foo\"") + (('|foo bar|) "\"foo bar\"") + ((:foo :table-alias :bar) "FOO BAR" ) + ((:foo_bar :table-alias :bar-bast) "FOO_BAR BAR_BAST") + (("foo" :table-alias "Bar") "\"foo\" \"Bar\"") + (('|foo bar| :table-alias :bast) "\"foo bar\" BAST")))) + + (push + `(deftest :syntax/sql-ident-table + (values ,@(mapcar + #'(lambda (args) + `(clsql:sql (make-instance 'clsql-sys:sql-ident-table + :name ,@args))) + (mapcar #'first tests))) + ,@(mapcar #'second tests)) + *rt-syntax*))