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 (clsql:sql
[and
[foo] [bar]])
140 (deftest :syntax/logical/2
141 (clsql:sql [or [foo] [bar]])
144 (deftest :syntax/logical/3
145 (clsql:sql [not [foo]])
149 (deftest :syntax/null/1
150 (clsql:sql [null [foo]])
153 (deftest :syntax/null/2
154 (clsql:sql [not [null [foo]]])
155 "(NOT ((FOO IS NULL)))")
157 (deftest :syntax/null/3
161 (deftest :syntax/null/4
162 (clsql:sql [not [null]])
167 (deftest :syntax/relational/1
168 (clsql:sql [> [baz] [beep]])
171 (deftest :syntax/relational/2
173 (clsql:sql [> [foo] x]))
176 (deftest :syntax/relational/3
177 (clsql:sql [>= [baz] [beep]])
180 (deftest :syntax/relational/4
181 (clsql:sql [< [baz] [beep]])
184 (deftest :syntax/relational/5
185 (clsql:sql [= [baz] [beep]])
188 (deftest :syntax/relational/6
189 (clsql:sql [<> [baz] [beep]])
193 (deftest :syntax/between/1
194 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
195 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
197 (deftest :syntax/between/2
198 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
199 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
202 (deftest :syntax
/arithmetic
/1
203 (clsql:sql
[+ [foo bar
] [baz]])
206 (deftest :syntax/arithmetic/2
207 (clsql:sql [- [foo bar] [baz]])
210 (deftest :syntax
/arithmetic
/3
211 (clsql:sql
[/ [foo bar
] [baz]])
214 (deftest :syntax/arithmetic/4
215 (clsql:sql [* [foo bar] [baz]])
218 (deftest :syntax
/arithmetic
/5
219 (clsql:sql
[-
[foo bar
]])
222 (deftest :syntax
/arithmetic
/6
227 (deftest :syntax
/substr
/1
228 (clsql:sql
[substr
[hello] 1 4])
231 (deftest :syntax/substring/1
232 (clsql:sql [substring [hello] 1 4])
233 "SUBSTRING(HELLO,1,4)")
236 (deftest :syntax
/concat
/1
237 (clsql:sql
[||
[foo] [bar] [baz]])
238 "(FOO || BAR || BAZ)")
240 (deftest :syntax/concat/2
241 (clsql:sql [concat [foo] [bar]])
245 (deftest :syntax/pattern/1
246 (clsql:sql [like [foo] "%v"])
249 (deftest :syntax/pattern/2
250 (clsql:sql [not [like [foo] "%v"]])
251 "(NOT ((FOO LIKE '%v')))")
254 (deftest :syntax/distinct/1
255 (clsql:sql [distinct [foo bar :string]])
258 (deftest :syntax/distinct/2
259 (clsql:sql [distinct [foo :string] [bar :integer]])
263 (deftest :syntax/order-by/1
264 (clsql:sql [order-by [foo]])
267 (deftest :syntax/group-by/1
268 (clsql:sql [group-by [foo]])
271 (deftest :syntax/group-by/2
273 (clsql-sys::make-query [foo] [bar] [count
[foo]]
275 :group-by '([foo] [bar])
276 :order-by '([foo] [bar])))
277 "SELECT FOO,BAR,COUNT(FOO) FROM TABLE GROUP BY FOO,BAR ORDER BY FOO,BAR")
280 (deftest :syntax
/coalesce
/1
281 (clsql:sql
[coalesce
[foo] [bar] "not specified"])
282 "COALESCE(FOO,BAR,'not specified')")
284 (deftest :syntax/coalesce/2
285 (clsql:sql [nvl [foo] "not specified"])
286 "COALESCE(FOO,'not specified')")
288 (deftest :syntax
/nvl
/1
289 (clsql:sql
[nvl
[foo] "not specified"])
290 "COALESCE(FOO,'not specified')")
294 (deftest :syntax/sets/1
295 (clsql:sql [union [select [foo] :from
[bar]] [select [baz] :from [bar]]])
296 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
298 (deftest :syntax
/sets
/2
299 (clsql:sql
[intersect
[select
[foo] :from [bar]] [select [baz] :from [bar]]])
300 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
302 (deftest :syntax/sets/3
303 (clsql:sql [except [select [foo] :from
[bar]] [select [baz] :from [bar]]])
304 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
306 (deftest :syntax
/sets
/4
307 (clsql:sql
[minus
[select
[foo] :from [bar]] [select [baz] :from [bar]]])
308 "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
311 (deftest :syntax/function/1
312 (clsql:sql [function "COS" [age]])
315 (deftest :syntax/function/2
316 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
317 "TO_DATE('02/06/99','mm/DD/RR')")
320 (deftest :syntax/query/1
321 (clsql:sql [select [person_id] [surname] :from [person]])
322 "SELECT PERSON_ID,SURNAME FROM PERSON")
324 (deftest :syntax/query/2
325 (clsql:sql [select [foo] [bar
*]
327 :where [or [= [foo] 3]
329 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
331 (deftest :syntax/query/3
332 (clsql:sql [select [foo bar] [baz]
333 :from
'([foo] [quux])
334 :where [or [> [baz] 3]
335 [like [foo bar] "SU%"]]])
336 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
338 (deftest :syntax/query/4
339 (clsql:sql [select [count [*]] :from [emp]])
340 "SELECT COUNT(*) FROM EMP")
343 (deftest :syntax/expression/1
347 (clsql:sql-expression :table 'foo :attribute 'bar)
348 (clsql:sql-expression :attribute 'baz)
350 (clsql:sql-expression :table 'foo)
351 (clsql:sql-expression :table 'quux))
353 (clsql:sql-operation 'or
356 (clsql:sql-expression :attribute 'baz)
360 (clsql:sql-expression :table 'foo
363 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
365 (deftest :syntax/expression/2
367 (apply (clsql:sql-operator 'and)
368 (loop for table in '(thistime nexttime sometime never)
372 (clsql:sql-expression :table table
374 (clsql:sql-operation '* [hip] [hop])
377 [like (clsql:sql-expression :table table
379 (clsql:sql table)])))
380 "(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'))")
384 #.(clsql:restore-sql-reader-syntax-state)