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]])
193 (deftest :syntax/null/5
194 (clsql:sql [is [foo.bar] [null]])
197 (deftest :syntax/null/6
198 (clsql:sql [is [foo.bar] [not-null]])
199 "(FOO.BAR IS NOT NULL)")
201 (deftest :syntax/null/7
202 (clsql:sql [not-null [foo.bar]])
203 "(FOO.BAR IS NOT NULL)")
207 (deftest :syntax/relational/1
208 (clsql:sql [> [baz] [beep]])
211 (deftest :syntax/relational/2
213 (clsql:sql [> [foo] x]))
216 (deftest :syntax/relational/3
217 (clsql:sql [>= [baz] [beep]])
220 (deftest :syntax/relational/4
221 (clsql:sql [< [baz] [beep]])
224 (deftest :syntax/relational/5
225 (clsql:sql [= [baz] [beep]])
228 (deftest :syntax/relational/6
229 (clsql:sql [<> [baz] [beep]])
233 (deftest :syntax/between/1
234 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
235 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
237 (deftest :syntax/between/2
238 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
239 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
242 (deftest :syntax/arithmetic/1
243 (clsql:sql [+ [foo bar] [baz]])
246 (deftest :syntax/arithmetic/2
247 (clsql:sql [- [foo bar] [baz]])
250 (deftest :syntax/arithmetic/3
251 (clsql:sql [/ [foo bar] [baz]])
254 (deftest :syntax/arithmetic/4
255 (clsql:sql [* [foo bar] [baz]])
258 (deftest :syntax/arithmetic/5
259 (clsql:sql [- [foo bar]])
262 (deftest :syntax/arithmetic/6
267 (deftest :syntax/substr/1
268 (clsql:sql [substr [hello] 1 4])
271 (deftest :syntax/substring/1
272 (clsql:sql [substring [hello] 1 4])
273 "SUBSTRING(HELLO,1,4)")
276 (deftest :syntax/concat/1
277 (clsql:sql [|| [foo] [bar] [baz]])
278 "(FOO || BAR || BAZ)")
280 (deftest :syntax/concat/2
281 (clsql:sql [concat [foo] [bar]])
285 (deftest :syntax/pattern/1
286 (clsql:sql [like [foo] "%v"])
289 (deftest :syntax/pattern/2
290 (clsql:sql [not [like [foo] "%v"]])
291 "(NOT ((FOO LIKE '%v')))")
294 (deftest :syntax/distinct/1
295 (clsql:sql [distinct [foo bar :string]])
298 (deftest :syntax/distinct/2
299 (clsql:sql [distinct [foo :string] [bar :integer]])
303 (deftest :syntax/order-by/1
304 (clsql:sql [order-by [foo]])
307 (deftest :syntax/group-by/1
308 (clsql:sql [group-by [foo]])
311 (deftest :syntax/group-by/2
313 (clsql-sys::make-query [foo] [bar] [count [foo]]
315 :group-by '([foo] [bar])
316 :order-by '([foo] [bar])))
317 "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR")
320 (deftest :syntax/coalesce/1
321 (clsql:sql [coalesce [foo] [bar] "not specified"])
322 "COALESCE(FOO,BAR,'not specified')")
324 (deftest :syntax/coalesce/2
325 (clsql:sql [nvl [foo] "not specified"])
326 "COALESCE(FOO,'not specified')")
328 (deftest :syntax/nvl/1
329 (clsql:sql [nvl [foo] "not specified"])
330 "COALESCE(FOO,'not specified')")
334 (deftest :syntax/sets/1
335 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
336 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
338 (deftest :syntax/sets/2
339 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
340 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
342 (deftest :syntax/sets/3
343 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
344 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
346 (deftest :syntax/sets/4
347 (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
348 "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
351 (deftest :syntax/function/1
352 (clsql:sql [function "COS" [age]])
355 (deftest :syntax/function/2
356 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
357 "TO_DATE('02/06/99','mm/DD/RR')")
360 (deftest :syntax/query/1
361 (clsql:sql [select [person_id] [surname] :from [person]])
362 "SELECT PERSON_ID,SURNAME FROM PERSON")
364 (deftest :syntax/query/2
365 (clsql:sql [select [foo] [bar *]
367 :where [or [= [foo] 3]
369 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
371 (deftest :syntax/query/3
372 (clsql:sql [select [foo bar] [baz]
373 :from '([foo] [quux])
374 :where [or [> [baz] 3]
375 [like [foo bar] "SU%"]]])
376 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
378 (deftest :syntax/query/4
379 (clsql:sql [select [count [*]] :from [emp]])
380 "SELECT COUNT(*) FROM EMP")
383 (deftest :syntax/expression/1
387 (clsql:sql-expression :table 'foo :attribute 'bar)
388 (clsql:sql-expression :attribute 'baz)
390 (clsql:sql-expression :table 'foo)
391 (clsql:sql-expression :table 'quux))
393 (clsql:sql-operation 'or
396 (clsql:sql-expression :attribute 'baz)
400 (clsql:sql-expression :table 'foo
403 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
405 (deftest :syntax/expression/2
407 (apply (clsql:sql-operator 'and)
408 (loop for table in '(thistime nexttime sometime never)
412 (clsql:sql-expression :table table
414 (clsql:sql-operation '* [hip] [hop])
417 [like (clsql:sql-expression :table table
419 (clsql:sql table)])))
420 "(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'))")
424 (defun test-output-sql/sql-ident-table ()
425 (let ((tests `((,(make-instance 'sql-ident-table :name :foo) "FOO")
426 (,(make-instance 'sql-ident-table :name :foo-bar) "FOO_BAR")
427 (,(make-instance 'sql-ident-table :name "foo") "\"foo\"")
428 (,(make-instance 'sql-ident-table :name '|foo bar|) "\"foo bar\"")
429 (,(make-instance 'sql-ident-table :name :foo :table-alias :bar) "FOO BAR" )
430 (,(make-instance 'sql-ident-table :name :foo_bar :table-alias :bar-bast) "FOO_BAR BAR_BAST")
431 (,(make-instance 'sql-ident-table :name "foo" :table-alias "Bar") "\"foo\" \"Bar\"")
432 (,(make-instance 'sql-ident-table :name '|foo bar| :table-alias :bast) "\"foo bar\" BAST"))))
433 (loop for (test expected-result) in tests
434 for test-out = (with-output-to-string (*sql-stream*) (output-sql test nil))
435 do (assert (string-equal test-out expected-result)
436 (test test-out expected-result)
437 "Test:~s didnt match ~S"
438 test-out expected-result))))