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 ;; not sure about this one
53 (deftest :syntax/ident/3
54 (clsql:sql ["foo" bar])
57 ;(deftest :syntax/ident/4
58 ; (clsql:sql [foo "bar"])
61 (deftest :syntax/ident/5
62 (clsql:sql [foo :integer])
65 (deftest :syntax/ident/6
66 (clsql:sql [foo bar :integer])
69 ;; not sure about this one
70 (deftest :syntax/ident/7
71 (clsql:sql ["foo" bar :integer])
75 (deftest :syntax/value/1
76 (clsql:sql [any '(3 4)])
79 (deftest :syntax/value/2
84 (deftest :syntax/relational/1
85 (clsql:sql [> [baz] [beep]])
88 (deftest :syntax/relational/2
90 (clsql:sql [> [foo] x]))
94 (deftest :syntax/function/1
95 (clsql:sql [function "COS" [age]])
98 (deftest :syntax/function/2
99 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
100 "TO_DATE('02/06/99','mm/DD/RR')")
102 (deftest :syntax/query/1
103 (clsql:sql [select [person_id] [surname] :from [person]])
104 "SELECT PERSON_ID,SURNAME FROM PERSON")
106 (deftest :syntax/query/2
107 (clsql:sql [select [foo] [bar *]
109 :where [or [= [foo] 3]
111 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
113 (deftest :syntax/query/3
114 (clsql:sql [select [foo bar] [baz]
115 :from '([foo] [quux])
116 :where [or [> [baz] 3]
117 [like [foo bar] "SU%"]]])
118 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
120 (deftest :syntax/query/4
121 (clsql:sql [select [count [*]] :from [emp]])
122 "SELECT COUNT(*) FROM EMP")
125 (deftest :syntax/expression1
129 (clsql:sql-expression :table 'foo :attribute 'bar)
130 (clsql:sql-expression :attribute 'baz)
132 (clsql:sql-expression :table 'foo)
133 (clsql:sql-expression :table 'quux))
135 (clsql:sql-operation 'or
138 (clsql:sql-expression :attribute 'baz)
142 (clsql:sql-expression :table 'foo
145 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
147 (deftest :syntax/expression/2
149 (apply (clsql:sql-operator 'and)
150 (loop for table in '(thistime nexttime sometime never)
154 (clsql:sql-expression :table table
156 (clsql:sql-operation '* [hip] [hop])
159 [like (clsql:sql-expression :table table
161 (clsql:sql table)])))
162 "(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'))")
166 #.(clsql:restore-sql-reader-syntax-state)