1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; ======================================================================
3 ;;;; File: test-syntax.lisp
4 ;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>
5 ;;;; Created: 30/03/2004
7 ;;;; ======================================================================
9 ;;;; Description ==========================================================
10 ;;;; ======================================================================
12 ;;;; Tests for the CLSQL Symbolic SQL syntax.
14 ;;;; ======================================================================
16 (in-package #:clsql-tests)
18 #.(clsql:locally-enable-sql-reader-syntax)
24 (deftest :syntax/generic/1
28 (deftest :syntax/generic/2
32 (deftest :syntax/generic/3
36 (deftest :syntax/generic/4
37 (clsql:sql '("ten" 10 ten))
40 (deftest :syntax/generic/5
41 (clsql:sql ["SELECT FOO,BAR FROM BAZ"])
42 "SELECT FOO,BAR FROM BAZ")
44 (deftest :syntax/generic/6
45 (clsql:sql "What's up Doc?")
48 (deftest :syntax/ident/1
52 (deftest :syntax/ident/2
56 (deftest :syntax/ident/3
57 (clsql:sql [foo :integer])
60 (deftest :syntax/ident/4
61 (clsql:sql [foo bar :integer])
64 (deftest :syntax/ident/5
65 (clsql:sql [foo "bar"])
68 (deftest :syntax/ident/6
69 (clsql:sql ["foo" bar])
72 (deftest :syntax/ident/7
73 (clsql:sql ["foo" bar :integer])
77 (deftest :syntax/attribute/1
78 (clsql:sql (clsql:sql-expression :table 'foo :attribute 'bar))
81 (deftest :syntax/attribute/2
82 (clsql:sql (clsql:sql-expression :table 'foo :attribute "bar"))
85 (deftest :syntax/attribute/3
86 (clsql:sql (clsql:sql-expression :table "foo" :attribute 'bar))
89 (deftest :syntax/attribute/4
90 (clsql:sql (clsql:sql-expression :table "foo" :attribute "bar"))
94 (deftest :syntax/subquery/1
95 (clsql:sql [any '(3 4)])
98 (deftest :syntax/subquery/2
99 (clsql:sql [in [foo] '(foo bar baz)])
100 "(FOO IN (FOO,BAR,BAZ))")
102 (deftest :syntax/subquery/3
103 (clsql:sql [all '(foo bar baz)])
104 "ALL((FOO,BAR,BAZ))")
106 (deftest :syntax/subquery/4
107 (clsql:sql [exists '(foo bar baz)])
108 "EXISTS((FOO,BAR,BAZ))")
110 (deftest :syntax/subquery/5
111 (clsql:sql [some '(foo bar baz)])
112 "SOME((FOO,BAR,BAZ))")
115 (deftest :syntax/aggregate/1
116 (clsql:sql [max [+ [foo] [* 1000 [bar]]]])
117 "MAX((FOO + (1000 * BAR)))")
119 (deftest :syntax/aggregate/2
120 (clsql:sql [avg [+ [foo] [* 1000 [bar]]]])
121 "AVG((FOO + (1000 * BAR)))")
123 (deftest :syntax/aggregate/3
124 (clsql:sql [min [+ [foo] [* 1000 [bar]]]])
125 "MIN((FOO + (1000 * BAR)))")
127 (deftest :syntax/aggregate/4
128 (clsql:sql [sum [foo] [bar]])
131 (deftest :syntax/aggregate/5
132 (clsql:sql [count [foo]])
136 (deftest :syntax/logical/1
137 (values (clsql:sql [and [foo] [bar]])
138 (clsql:sql [or [foo] [bar]]))
142 (deftest :syntax/logical/2
143 (clsql:sql [not [foo]])
146 ;;; Test how we apply logical operators when we have different numbers of children
147 ;;; This is useful if we wish to (apply #'sql-and some-list) without having to do
148 ;;; alot of length checking
149 (deftest :syntax/logical/3
150 (values (clsql:sql [and ])
151 (clsql:sql [and [foo]])
152 (clsql:sql [and [not [foo]]])
153 (clsql:sql [and [foo] [bar] [baz]]))
157 "(FOO AND BAR AND BAZ)")
159 (deftest :syntax/logical/4
160 (clsql:sql [and [= [foo] [bar]]])
163 (deftest :syntax/logical/5
164 (clsql:sql [and [= [foo] [bar]]
166 [= [block] [blech]]])
167 "((FOO = BAR) AND (BAR = BAST) AND (BLOCK = BLECH))")
169 (deftest :syntax/logical/6
172 (list [= [foo] [bar]]
174 [and [= [bar] [bast]]])))
175 "((FOO = BAR) AND (BAR = BAST))")
178 (deftest :syntax/null/1
179 (clsql:sql [null [foo]])
182 (deftest :syntax/null/2
183 (clsql:sql [not [null [foo]]])
184 "(NOT ((FOO IS NULL)))")
186 (deftest :syntax/null/3
190 (deftest :syntax/null/4
191 (clsql:sql [not [null]])
196 (deftest :syntax/relational/1
197 (clsql:sql [> [baz] [beep]])
200 (deftest :syntax/relational/2
202 (clsql:sql [> [foo] x]))
205 (deftest :syntax/relational/3
206 (clsql:sql [>= [baz] [beep]])
209 (deftest :syntax/relational/4
210 (clsql:sql [< [baz] [beep]])
213 (deftest :syntax/relational/5
214 (clsql:sql [= [baz] [beep]])
217 (deftest :syntax/relational/6
218 (clsql:sql [<> [baz] [beep]])
222 (deftest :syntax/between/1
223 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
224 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
226 (deftest :syntax/between/2
227 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
228 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
231 (deftest :syntax/arithmetic/1
232 (clsql:sql [+ [foo bar] [baz]])
235 (deftest :syntax/arithmetic/2
236 (clsql:sql [- [foo bar] [baz]])
239 (deftest :syntax/arithmetic/3
240 (clsql:sql [/ [foo bar] [baz]])
243 (deftest :syntax/arithmetic/4
244 (clsql:sql [* [foo bar] [baz]])
247 (deftest :syntax/arithmetic/5
248 (clsql:sql [- [foo bar]])
251 (deftest :syntax/arithmetic/6
256 (deftest :syntax/substr/1
257 (clsql:sql [substr [hello] 1 4])
260 (deftest :syntax/substring/1
261 (clsql:sql [substring [hello] 1 4])
262 "SUBSTRING(HELLO,1,4)")
265 (deftest :syntax/concat/1
266 (clsql:sql [|| [foo] [bar] [baz]])
267 "(FOO || BAR || BAZ)")
269 (deftest :syntax/concat/2
270 (clsql:sql [concat [foo] [bar]])
274 (deftest :syntax/pattern/1
275 (clsql:sql [like [foo] "%v"])
278 (deftest :syntax/pattern/2
279 (clsql:sql [not [like [foo] "%v"]])
280 "(NOT ((FOO LIKE '%v')))")
283 (deftest :syntax/distinct/1
284 (clsql:sql [distinct [foo bar :string]])
287 (deftest :syntax/distinct/2
288 (clsql:sql [distinct [foo :string] [bar :integer]])
292 (deftest :syntax/order-by/1
293 (clsql:sql [order-by [foo]])
296 (deftest :syntax/group-by/1
297 (clsql:sql [group-by [foo]])
300 (deftest :syntax/group-by/2
302 (clsql-sys::make-query [foo] [bar] [count [foo]]
304 :group-by '([foo] [bar])
305 :order-by '([foo] [bar])))
306 "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR")
309 (deftest :syntax/coalesce/1
310 (clsql:sql [coalesce [foo] [bar] "not specified"])
311 "COALESCE(FOO,BAR,'not specified')")
313 (deftest :syntax/coalesce/2
314 (clsql:sql [nvl [foo] "not specified"])
315 "COALESCE(FOO,'not specified')")
317 (deftest :syntax/nvl/1
318 (clsql:sql [nvl [foo] "not specified"])
319 "COALESCE(FOO,'not specified')")
323 (deftest :syntax/sets/1
324 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
325 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
327 (deftest :syntax/sets/2
328 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
329 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
331 (deftest :syntax/sets/3
332 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
333 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
335 (deftest :syntax/sets/4
336 (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
337 "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
340 (deftest :syntax/function/1
341 (clsql:sql [function "COS" [age]])
344 (deftest :syntax/function/2
345 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
346 "TO_DATE('02/06/99','mm/DD/RR')")
349 (deftest :syntax/query/1
350 (clsql:sql [select [person_id] [surname] :from [person]])
351 "SELECT PERSON_ID,SURNAME FROM PERSON")
353 (deftest :syntax/query/2
354 (clsql:sql [select [foo] [bar *]
356 :where [or [= [foo] 3]
358 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
360 (deftest :syntax/query/3
361 (clsql:sql [select [foo bar] [baz]
362 :from '([foo] [quux])
363 :where [or [> [baz] 3]
364 [like [foo bar] "SU%"]]])
365 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
367 (deftest :syntax/query/4
368 (clsql:sql [select [count [*]] :from [emp]])
369 "SELECT COUNT(*) FROM EMP")
372 (deftest :syntax/expression/1
376 (clsql:sql-expression :table 'foo :attribute 'bar)
377 (clsql:sql-expression :attribute 'baz)
379 (clsql:sql-expression :table 'foo)
380 (clsql:sql-expression :table 'quux))
382 (clsql:sql-operation 'or
385 (clsql:sql-expression :attribute 'baz)
389 (clsql:sql-expression :table 'foo
392 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
394 (deftest :syntax/expression/2
396 (apply (clsql:sql-operator 'and)
397 (loop for table in '(thistime nexttime sometime never)
401 (clsql:sql-expression :table table
403 (clsql:sql-operation '* [hip] [hop])
406 [like (clsql:sql-expression :table table
408 (clsql:sql table)])))
409 "(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'))")
413 #.(clsql:restore-sql-reader-syntax-state)