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)
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")
44 (deftest :syntax/ident/1
48 (deftest :syntax/ident/2
52 (deftest :syntax/ident/3
53 (clsql:sql [foo :integer])
56 (deftest :syntax/ident/4
57 (clsql:sql [foo bar :integer])
60 (deftest :syntax/ident/5
61 (clsql:sql [foo "bar"])
64 (deftest :syntax/ident/6
65 (clsql:sql ["foo" bar])
68 (deftest :syntax/ident/7
69 (clsql:sql ["foo" bar :integer])
74 (deftest :syntax/subquery/1
75 (clsql:sql [any '(3 4)])
78 (deftest :syntax/subquery/2
79 (clsql:sql [in [foo] '(foo bar baz)])
80 "(FOO IN (FOO,BAR,BAZ))")
82 (deftest :syntax/subquery/3
83 (clsql:sql [all '(foo bar baz)])
86 (deftest :syntax/subquery/4
87 (clsql:sql [exists '(foo bar baz)])
88 "EXISTS((FOO,BAR,BAZ))")
90 (deftest :syntax/subquery/5
91 (clsql:sql [some '(foo bar baz)])
92 "SOME((FOO,BAR,BAZ))")
95 (deftest :syntax/aggregate/1
96 (clsql:sql [max [+ [foo] [* 1000 [bar]]]])
97 "MAX((FOO + (1000 * BAR)))")
99 (deftest :syntax/aggregate/2
100 (clsql:sql [avg [+ [foo] [* 1000 [bar]]]])
101 "AVG((FOO + (1000 * BAR)))")
103 (deftest :syntax/aggregate/3
104 (clsql:sql [min [+ [foo] [* 1000 [bar]]]])
105 "MIN((FOO + (1000 * BAR)))")
107 (deftest :syntax/aggregate/4
108 (clsql:sql [sum [foo] [bar]])
111 (deftest :syntax/aggregate/5
112 (clsql:sql [count [foo]])
116 (deftest :syntax/logical/1
117 (clsql:sql [and [foo] [bar]])
120 (deftest :syntax/logical/2
121 (clsql:sql [or [foo] [bar]])
124 (deftest :syntax/logical/3
125 (clsql:sql [not [foo]])
129 (deftest :syntax/null/1
130 (clsql:sql [null [foo]])
133 (deftest :syntax/null/2
134 (clsql:sql [not [null [foo]]])
135 "(NOT ((FOO IS NULL)))")
137 (deftest :syntax/null/3
141 (deftest :syntax/null/4
142 (clsql:sql [not [null]])
147 (deftest :syntax/relational/1
148 (clsql:sql [> [baz] [beep]])
151 (deftest :syntax/relational/2
153 (clsql:sql [> [foo] x]))
156 (deftest :syntax/relational/3
157 (clsql:sql [>= [baz] [beep]])
160 (deftest :syntax/relational/4
161 (clsql:sql [< [baz] [beep]])
164 (deftest :syntax/relational/5
165 (clsql:sql [= [baz] [beep]])
168 (deftest :syntax/relational/6
169 (clsql:sql [<> [baz] [beep]])
173 (deftest :syntax/between/1
174 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
175 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
177 (deftest :syntax/between/2
178 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
179 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
182 (deftest :syntax/arithmetic/1
183 (clsql:sql [+ [foo bar] [baz]])
186 (deftest :syntax/arithmetic/2
187 (clsql:sql [- [foo bar] [baz]])
190 (deftest :syntax/arithmetic/3
191 (clsql:sql [/ [foo bar] [baz]])
194 (deftest :syntax/arithmetic/4
195 (clsql:sql [* [foo bar] [baz]])
198 (deftest :syntax/arithmetic/5
199 (clsql:sql [- [foo bar]])
202 (deftest :syntax/arithmetic/6
207 (deftest :syntax/substr/1
208 (clsql:sql [substr [hello] 1 4])
211 (deftest :syntax/substring/1
212 (clsql:sql [substring [hello] 1 4])
213 "SUBSTRING(HELLO,1,4)")
216 (deftest :syntax/concat/1
217 (clsql:sql [|| [foo] [bar] [baz]])
218 "(FOO || BAR || BAZ)")
220 (deftest :syntax/concat/2
221 (clsql:sql [concat [foo] [bar]])
225 (deftest :syntax/pattern/1
226 (clsql:sql [like [foo] "%v"])
229 (deftest :syntax/pattern/2
230 (clsql:sql [not [like [foo] "%v"]])
231 "(NOT ((FOO LIKE '%v')))")
234 (deftest :syntax/distinct/1
235 (clsql:sql [distinct [foo bar :string]])
238 (deftest :syntax/distinct/2
239 (clsql:sql [distinct [foo :string] [bar :integer]])
243 (deftest :syntax/order-by/1
244 (clsql:sql [order-by [foo]])
247 (deftest :syntax/group-by/1
248 (clsql:sql [group-by [foo]])
252 (deftest :syntax/coalesce/1
253 (clsql:sql [coalesce [foo] [bar] "not specified"])
254 "COALESCE(FOO,BAR,'not specified')")
256 (deftest :syntax/coalesce/2
257 (clsql:sql [nvl [foo] "not specified"])
258 "COALESCE(FOO,'not specified')")
260 (deftest :syntax/nvl/1
261 (clsql:sql [nvl [foo] "not specified"])
262 "COALESCE(FOO,'not specified')")
266 (deftest :syntax/sets/1
267 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
268 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
270 (deftest :syntax/sets/2
271 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
272 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
274 (deftest :syntax/sets/3
275 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
276 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
278 (deftest :syntax/sets/4
279 (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
280 "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
283 (deftest :syntax/function/1
284 (clsql:sql [function "COS" [age]])
287 (deftest :syntax/function/2
288 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
289 "TO_DATE('02/06/99','mm/DD/RR')")
292 (deftest :syntax/query/1
293 (clsql:sql [select [person_id] [surname] :from [person]])
294 "SELECT PERSON_ID,SURNAME FROM PERSON")
296 (deftest :syntax/query/2
297 (clsql:sql [select [foo] [bar *]
299 :where [or [= [foo] 3]
301 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
303 (deftest :syntax/query/3
304 (clsql:sql [select [foo bar] [baz]
305 :from '([foo] [quux])
306 :where [or [> [baz] 3]
307 [like [foo bar] "SU%"]]])
308 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
310 (deftest :syntax/query/4
311 (clsql:sql [select [count [*]] :from [emp]])
312 "SELECT COUNT(*) FROM EMP")
315 (deftest :syntax/expression1
319 (clsql:sql-expression :table 'foo :attribute 'bar)
320 (clsql:sql-expression :attribute 'baz)
322 (clsql:sql-expression :table 'foo)
323 (clsql:sql-expression :table 'quux))
325 (clsql:sql-operation 'or
328 (clsql:sql-expression :attribute 'baz)
332 (clsql:sql-expression :table 'foo
335 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
337 (deftest :syntax/expression/2
339 (apply (clsql:sql-operator 'and)
340 (loop for table in '(thistime nexttime sometime never)
344 (clsql:sql-expression :table table
346 (clsql:sql-operation '* [hip] [hop])
349 [like (clsql:sql-expression :table table
351 (clsql:sql table)])))
352 "(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'))")
356 #.(clsql:restore-sql-reader-syntax-state)