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])
209 "SUBSTRING(HELLO,1,4)")
212 (deftest :syntax/concat/1
213 (clsql:sql [|| [foo] [bar] [baz]])
214 "(FOO || BAR || BAZ)")
217 (deftest :syntax/pattern/1
218 (clsql:sql [like [foo] "%v"])
221 (deftest :syntax/pattern/2
222 (clsql:sql [not [like [foo] "%v"]])
223 "(NOT ((FOO LIKE '%v')))")
226 (deftest :syntax/distinct/1
227 (clsql:sql [distinct [foo bar :string]])
230 (deftest :syntax/distinct/2
231 (clsql:sql [distinct [foo :string] [bar :integer]])
235 (deftest :syntax/order-by/1
236 (clsql:sql [order-by [foo]])
239 (deftest :syntax/group-by/1
240 (clsql:sql [group-by [foo]])
244 (deftest :syntax/coalesce/1
245 (clsql:sql [coalesce [foo] [bar] "not specified"])
246 "COALESCE(FOO,BAR,'not specified')")
248 (deftest :syntax/coalesce/2
249 (clsql:sql [nvl [foo] "not specified"])
250 "COALESCE(FOO,'not specified')")
253 (deftest :syntax/sets/1
254 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
255 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
257 (deftest :syntax/sets/2
258 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
259 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
261 (deftest :syntax/sets/3
262 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
263 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
265 (deftest :syntax/sets/4
266 (clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
267 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
270 (deftest :syntax/function/1
271 (clsql:sql [function "COS" [age]])
274 (deftest :syntax/function/2
275 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
276 "TO_DATE('02/06/99','mm/DD/RR')")
279 (deftest :syntax/query/1
280 (clsql:sql [select [person_id] [surname] :from [person]])
281 "SELECT PERSON_ID,SURNAME FROM PERSON")
283 (deftest :syntax/query/2
284 (clsql:sql [select [foo] [bar *]
286 :where [or [= [foo] 3]
288 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
290 (deftest :syntax/query/3
291 (clsql:sql [select [foo bar] [baz]
292 :from '([foo] [quux])
293 :where [or [> [baz] 3]
294 [like [foo bar] "SU%"]]])
295 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
297 (deftest :syntax/query/4
298 (clsql:sql [select [count [*]] :from [emp]])
299 "SELECT COUNT(*) FROM EMP")
302 (deftest :syntax/expression1
306 (clsql:sql-expression :table 'foo :attribute 'bar)
307 (clsql:sql-expression :attribute 'baz)
309 (clsql:sql-expression :table 'foo)
310 (clsql:sql-expression :table 'quux))
312 (clsql:sql-operation 'or
315 (clsql:sql-expression :attribute 'baz)
319 (clsql:sql-expression :table 'foo
322 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
324 (deftest :syntax/expression/2
326 (apply (clsql:sql-operator 'and)
327 (loop for table in '(thistime nexttime sometime never)
331 (clsql:sql-expression :table table
333 (clsql:sql-operation '* [hip] [hop])
336 [like (clsql:sql-expression :table table
338 (clsql:sql table)])))
339 "(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'))")
343 #.(clsql:restore-sql-reader-syntax-state)