1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
6 ;;;; Purpose: Tests for the CLSQL Symbolic SQL syntax.
7 ;;;; Authors: Marcus Pearce and Kevin M. Rosenberg
8 ;;;; Created: March 2004
10 ;;;; CLSQL users are granted the rights to distribute and use this software
11 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
12 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
13 ;;;; *************************************************************************
15 (in-package #:clsql-tests)
17 (clsql-sys:file-enable-sql-reader-syntax)
23 (deftest :syntax/generic/1
27 (deftest :syntax/generic/2
31 (deftest :syntax/generic/3
35 (deftest :syntax/generic/4
36 (clsql:sql '("ten" 10 ten))
39 (deftest :syntax/generic/5
40 (clsql:sql ["SELECT FOO,BAR FROM BAZ"])
41 "SELECT FOO,BAR FROM BAZ")
43 (deftest :syntax/generic/6
44 (clsql:sql "What's up Doc?")
47 (deftest :syntax/ident/1
51 (deftest :syntax/ident/2
55 (deftest :syntax/ident/3
56 (clsql:sql [foo :integer])
59 (deftest :syntax/ident/4
60 (clsql:sql [foo bar :integer])
63 (deftest :syntax/ident/5
64 (clsql:sql [foo "bar"])
67 (deftest :syntax/ident/6
68 (clsql:sql ["foo" bar])
71 (deftest :syntax/ident/7
72 (clsql:sql ["foo" bar :integer])
76 (deftest :syntax/attribute/1
77 (clsql:sql (clsql:sql-expression :table 'foo :attribute 'bar))
80 (deftest :syntax/attribute/2
81 (clsql:sql (clsql:sql-expression :table 'foo :attribute "bar"))
84 (deftest :syntax/attribute/3
85 (clsql:sql (clsql:sql-expression :table "foo" :attribute 'bar))
88 (deftest :syntax/attribute/4
89 (clsql:sql (clsql:sql-expression :table "foo" :attribute "bar"))
93 (deftest :syntax/subquery/1
94 (clsql:sql [any '(3 4)])
97 (deftest :syntax/subquery/2
98 (clsql:sql [in [foo] '(foo bar baz)])
99 "(FOO IN (FOO,BAR,BAZ))")
101 (deftest :syntax/subquery/3
102 (clsql:sql [all '(foo bar baz)])
103 "ALL((FOO,BAR,BAZ))")
105 (deftest :syntax/subquery/4
106 (clsql:sql [exists '(foo bar baz)])
107 "EXISTS((FOO,BAR,BAZ))")
109 (deftest :syntax/subquery/5
110 (clsql:sql [some '(foo bar baz)])
111 "SOME((FOO,BAR,BAZ))")
114 (deftest :syntax/aggregate/1
115 (clsql:sql [max [+ [foo] [* 1000 [bar]]]])
116 "MAX((FOO + (1000 * BAR)))")
118 (deftest :syntax/aggregate/2
119 (clsql:sql [avg [+ [foo] [* 1000 [bar]]]])
120 "AVG((FOO + (1000 * BAR)))")
122 (deftest :syntax/aggregate/3
123 (clsql:sql [min [+ [foo] [* 1000 [bar]]]])
124 "MIN((FOO + (1000 * BAR)))")
126 (deftest :syntax/aggregate/4
127 (clsql:sql [sum [foo] [bar]])
130 (deftest :syntax/aggregate/5
131 (clsql:sql [count [foo]])
135 (deftest :syntax/logical/1
136 (values (clsql:sql [and [foo] [bar]])
137 (clsql:sql [or [foo] [bar]]))
141 (deftest :syntax/logical/2
142 (clsql:sql [not [foo]])
145 ;;; Test how we apply logical operators when we have different numbers of children
146 ;;; This is useful if we wish to (apply #'sql-and some-list) without having to do
147 ;;; alot of length checking
148 (deftest :syntax/logical/3
149 (values (clsql:sql [and ])
150 (clsql:sql [and [foo]])
151 (clsql:sql [and [not [foo]]])
152 (clsql:sql [and [foo] [bar] [baz]]))
156 "(FOO AND BAR AND BAZ)")
158 (deftest :syntax/logical/4
159 (clsql:sql [and [= [foo] [bar]]])
162 (deftest :syntax/logical/5
163 (clsql:sql [and [= [foo] [bar]]
165 [= [block] [blech]]])
166 "((FOO = BAR) AND (BAR = BAST) AND (BLOCK = BLECH))")
168 (deftest :syntax/logical/6
171 (list [= [foo] [bar]]
173 [and [= [bar] [bast]]])))
174 "((FOO = BAR) AND (BAR = BAST))")
177 (deftest :syntax/null/1
178 (clsql:sql [null [foo]])
181 (deftest :syntax/null/2
182 (clsql:sql [not [null [foo]]])
183 "(NOT ((FOO IS NULL)))")
185 (deftest :syntax/null/3
189 (deftest :syntax/null/4
190 (clsql:sql [not [null]])
195 (deftest :syntax/relational/1
196 (clsql:sql [> [baz] [beep]])
199 (deftest :syntax/relational/2
201 (clsql:sql [> [foo] x]))
204 (deftest :syntax/relational/3
205 (clsql:sql [>= [baz] [beep]])
208 (deftest :syntax/relational/4
209 (clsql:sql [< [baz] [beep]])
212 (deftest :syntax/relational/5
213 (clsql:sql [= [baz] [beep]])
216 (deftest :syntax/relational/6
217 (clsql:sql [<> [baz] [beep]])
221 (deftest :syntax/between/1
222 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
223 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
225 (deftest :syntax/between/2
226 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
227 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
230 (deftest :syntax/arithmetic/1
231 (clsql:sql [+ [foo bar] [baz]])
234 (deftest :syntax/arithmetic/2
235 (clsql:sql [- [foo bar] [baz]])
238 (deftest :syntax/arithmetic/3
239 (clsql:sql [/ [foo bar] [baz]])
242 (deftest :syntax/arithmetic/4
243 (clsql:sql [* [foo bar] [baz]])
246 (deftest :syntax/arithmetic/5
247 (clsql:sql [- [foo bar]])
250 (deftest :syntax/arithmetic/6
255 (deftest :syntax/substr/1
256 (clsql:sql [substr [hello] 1 4])
259 (deftest :syntax/substring/1
260 (clsql:sql [substring [hello] 1 4])
261 "SUBSTRING(HELLO,1,4)")
264 (deftest :syntax/concat/1
265 (clsql:sql [|| [foo] [bar] [baz]])
266 "(FOO || BAR || BAZ)")
268 (deftest :syntax/concat/2
269 (clsql:sql [concat [foo] [bar]])
273 (deftest :syntax/pattern/1
274 (clsql:sql [like [foo] "%v"])
277 (deftest :syntax/pattern/2
278 (clsql:sql [not [like [foo] "%v"]])
279 "(NOT ((FOO LIKE '%v')))")
282 (deftest :syntax/distinct/1
283 (clsql:sql [distinct [foo bar :string]])
286 (deftest :syntax/distinct/2
287 (clsql:sql [distinct [foo :string] [bar :integer]])
291 (deftest :syntax/order-by/1
292 (clsql:sql [order-by [foo]])
295 (deftest :syntax/group-by/1
296 (clsql:sql [group-by [foo]])
299 (deftest :syntax/group-by/2
301 (clsql-sys::make-query [foo] [bar] [count [foo]]
303 :group-by '([foo] [bar])
304 :order-by '([foo] [bar])))
305 "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR")
308 (deftest :syntax/coalesce/1
309 (clsql:sql [coalesce [foo] [bar] "not specified"])
310 "COALESCE(FOO,BAR,'not specified')")
312 (deftest :syntax/coalesce/2
313 (clsql:sql [nvl [foo] "not specified"])
314 "COALESCE(FOO,'not specified')")
316 (deftest :syntax/nvl/1
317 (clsql:sql [nvl [foo] "not specified"])
318 "COALESCE(FOO,'not specified')")
322 (deftest :syntax/sets/1
323 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
324 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
326 (deftest :syntax/sets/2
327 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
328 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
330 (deftest :syntax/sets/3
331 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
332 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
334 (deftest :syntax/sets/4
335 (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
336 "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
339 (deftest :syntax/function/1
340 (clsql:sql [function "COS" [age]])
343 (deftest :syntax/function/2
344 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
345 "TO_DATE('02/06/99','mm/DD/RR')")
348 (deftest :syntax/query/1
349 (clsql:sql [select [person_id] [surname] :from [person]])
350 "SELECT PERSON_ID,SURNAME FROM PERSON")
352 (deftest :syntax/query/2
353 (clsql:sql [select [foo] [bar *]
355 :where [or [= [foo] 3]
357 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
359 (deftest :syntax/query/3
360 (clsql:sql [select [foo bar] [baz]
361 :from '([foo] [quux])
362 :where [or [> [baz] 3]
363 [like [foo bar] "SU%"]]])
364 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
366 (deftest :syntax/query/4
367 (clsql:sql [select [count [*]] :from [emp]])
368 "SELECT COUNT(*) FROM EMP")
371 (deftest :syntax/expression/1
375 (clsql:sql-expression :table 'foo :attribute 'bar)
376 (clsql:sql-expression :attribute 'baz)
378 (clsql:sql-expression :table 'foo)
379 (clsql:sql-expression :table 'quux))
381 (clsql:sql-operation 'or
384 (clsql:sql-expression :attribute 'baz)
388 (clsql:sql-expression :table 'foo
391 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
393 (deftest :syntax/expression/2
395 (apply (clsql:sql-operator 'and)
396 (loop for table in '(thistime nexttime sometime never)
400 (clsql:sql-expression :table table
402 (clsql:sql-operation '* [hip] [hop])
405 [like (clsql:sql-expression :table table
407 (clsql:sql table)])))
408 "(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'))")
412 (defun test-output-sql/sql-ident-table ()
413 (let ((tests `((,(make-instance 'sql-ident-table :name :foo) "FOO")
414 (,(make-instance 'sql-ident-table :name :foo-bar) "FOO_BAR")
415 (,(make-instance 'sql-ident-table :name "foo") "\"foo\"")
416 (,(make-instance 'sql-ident-table :name '|foo bar|) "\"foo bar\"")
417 (,(make-instance 'sql-ident-table :name :foo :table-alias :bar) "FOO BAR" )
418 (,(make-instance 'sql-ident-table :name :foo_bar :table-alias :bar-bast) "FOO_BAR BAR_BAST")
419 (,(make-instance 'sql-ident-table :name "foo" :table-alias "Bar") "\"foo\" \"Bar\"")
420 (,(make-instance 'sql-ident-table :name '|foo bar| :table-alias :bast) "\"foo bar\" BAST"))))
421 (loop for (test expected-result) in tests
422 for test-out = (with-output-to-string (*sql-stream*) (output-sql test nil))
423 do (assert (string-equal test-out expected-result)
424 (test test-out expected-result)
425 "Test:~s didnt match ~S"
426 test-out expected-result))))