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" bar])
56 ;(deftest :syntax/ident/4
57 ; (clsql:sql [foo "bar"])
60 (deftest :syntax/ident/5
61 (clsql:sql [foo :integer])
64 (deftest :syntax/ident/6
65 (clsql:sql [foo bar :integer])
68 (deftest :syntax/ident/7
69 (clsql:sql ["foo" bar :integer])
73 (deftest :syntax/subquery/1
74 (clsql:sql [any '(3 4)])
77 (deftest :syntax/subquery/2
78 (clsql:sql [in [foo] '(foo bar baz)])
79 "(FOO IN (FOO,BAR,BAZ))")
81 (deftest :syntax/subquery/3
82 (clsql:sql [all '(foo bar baz)])
83 "(ALL ((FOO,BAR,BAZ)))")
85 (deftest :syntax/subquery/4
86 (clsql:sql [exists '(foo bar baz)])
87 "(EXISTS ((FOO,BAR,BAZ)))")
89 (deftest :syntax/subquery/5
90 (clsql:sql [some '(foo bar baz)])
91 "(SOME ((FOO,BAR,BAZ)))")
94 (deftest :syntax/aggregate/1
95 (clsql:sql [max [+ [foo] [* 1000 [bar]]]])
96 "MAX((FOO + (1000 * BAR)))")
98 (deftest :syntax/aggregate/2
99 (clsql:sql [avg [+ [foo] [* 1000 [bar]]]])
100 "AVG((FOO + (1000 * BAR)))")
102 (deftest :syntax/aggregate/3
103 (clsql:sql [min [+ [foo] [* 1000 [bar]]]])
104 "MIN((FOO + (1000 * BAR)))")
106 (deftest :syntax/aggregate/4
107 (clsql:sql [sum [foo] [bar]])
110 (deftest :syntax/aggregate/5
111 (clsql:sql [count [foo]])
115 (deftest :syntax/logical/1
116 (clsql:sql [and [foo] [bar]])
119 (deftest :syntax/logical/2
120 (clsql:sql [or [foo] [bar]])
123 (deftest :syntax/logical/3
124 (clsql:sql [not [foo]])
128 (deftest :syntax/null/1
129 (clsql:sql [null [foo]])
132 (deftest :syntax/null/2
133 (clsql:sql [not [null [foo]]])
134 "(NOT ((FOO IS NULL)))")
136 (deftest :syntax/null/3
140 (deftest :syntax/null/4
141 (clsql:sql [not [null]])
146 (deftest :syntax/relational/1
147 (clsql:sql [> [baz] [beep]])
150 (deftest :syntax/relational/2
152 (clsql:sql [> [foo] x]))
155 (deftest :syntax/relational/3
156 (clsql:sql [>= [baz] [beep]])
159 (deftest :syntax/relational/4
160 (clsql:sql [< [baz] [beep]])
163 (deftest :syntax/relational/5
164 (clsql:sql [= [baz] [beep]])
167 (deftest :syntax/relational/6
168 (clsql:sql [<> [baz] [beep]])
172 (deftest :syntax/between/1
173 (clsql:sql [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]])
174 "(FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)")
176 (deftest :syntax/between/2
177 (clsql:sql [not [between [- [foo] 1] [* [bar] 5] [/ [baz] 9]]])
178 "(NOT ((FOO - 1) BETWEEN (BAR * 5) AND (BAZ / 9)))")
181 (deftest :syntax/arithmetic/1
182 (clsql:sql [+ [foo bar] [baz]])
185 (deftest :syntax/arithmetic/2
186 (clsql:sql [- [foo bar] [baz]])
189 (deftest :syntax/arithmetic/3
190 (clsql:sql [/ [foo bar] [baz]])
193 (deftest :syntax/arithmetic/4
194 (clsql:sql [* [foo bar] [baz]])
197 (deftest :syntax/arithmetic/5
198 (clsql:sql [- [foo bar]])
201 (deftest :syntax/arithmetic/6
206 (deftest :syntax/substr/1
207 (clsql:sql [substr [hello] 1 4])
208 "SUBSTRING(HELLO,1,4)")
211 (deftest :syntax/concat/1
212 (clsql:sql [\|\| [foo] [bar] [baz]])
213 "(FOO || BAR || BAZ)")
216 (deftest :syntax/pattern/1
217 (clsql:sql [like [foo] "%v"])
220 (deftest :syntax/pattern/2
221 (clsql:sql [not [like [foo] "%v"]])
222 "(NOT ((FOO LIKE '%v')))")
225 (deftest :syntax/distinct/1
226 (clsql:sql [distinct [foo bar :string]])
229 (deftest :syntax/distinct/2
230 (clsql:sql [distinct [foo :string] [bar :integer]])
234 (deftest :syntax/order-by/1
235 (clsql:sql [order-by [foo]])
238 (deftest :syntax/group-by/1
239 (clsql:sql [group-by [foo]])
243 (deftest :syntax/sets/1
244 (clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
245 "SELECT FOO FROM BAR UNION SELECT BAZ FROM BAR")
247 (deftest :syntax/sets/2
248 (clsql:sql [intersect [select [foo] :from [bar]] [select [baz] :from [bar]]])
249 "SELECT FOO FROM BAR INTERSECT SELECT BAZ FROM BAR")
251 (deftest :syntax/sets/3
252 (clsql:sql [except [select [foo] :from [bar]] [select [baz] :from [bar]]])
253 "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
256 (deftest :syntax/function/1
257 (clsql:sql [function "COS" [age]])
260 (deftest :syntax/function/2
261 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
262 "TO_DATE('02/06/99','mm/DD/RR')")
265 (deftest :syntax/query/1
266 (clsql:sql [select [person_id] [surname] :from [person]])
267 "SELECT PERSON_ID,SURNAME FROM PERSON")
269 (deftest :syntax/query/2
270 (clsql:sql [select [foo] [bar *]
272 :where [or [= [foo] 3]
274 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
276 (deftest :syntax/query/3
277 (clsql:sql [select [foo bar] [baz]
278 :from '([foo] [quux])
279 :where [or [> [baz] 3]
280 [like [foo bar] "SU%"]]])
281 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
283 (deftest :syntax/query/4
284 (clsql:sql [select [count [*]] :from [emp]])
285 "SELECT COUNT(*) FROM EMP")
288 (deftest :syntax/expression1
292 (clsql:sql-expression :table 'foo :attribute 'bar)
293 (clsql:sql-expression :attribute 'baz)
295 (clsql:sql-expression :table 'foo)
296 (clsql:sql-expression :table 'quux))
298 (clsql:sql-operation 'or
301 (clsql:sql-expression :attribute 'baz)
305 (clsql:sql-expression :table 'foo
308 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
310 (deftest :syntax/expression/2
312 (apply (clsql:sql-operator 'and)
313 (loop for table in '(thistime nexttime sometime never)
317 (clsql:sql-expression :table table
319 (clsql:sql-operation '* [hip] [hop])
322 [like (clsql:sql-expression :table table
324 (clsql:sql table)])))
325 "(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'))")
329 #.(clsql:restore-sql-reader-syntax-state)