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)
21 (deftest :syntax/generic/1
25 (deftest :syntax/generic/2
29 (deftest :syntax/generic/3
33 (deftest :syntax/generic/4
34 (clsql:sql '("ten" 10 ten))
37 (deftest :syntax/generic/5
38 (clsql:sql ["SELECT FOO,BAR FROM BAZ"])
39 "SELECT FOO,BAR FROM BAZ")
42 (deftest :syntax/ident/1
46 (deftest :syntax/ident/2
50 ;; not sure about this one
51 (deftest :syntax/ident/3
52 (clsql:sql ["foo" bar])
55 ;(deftest :syntax/ident/4
56 ; (clsql:sql [foo "bar"])
59 (deftest :syntax/ident/5
60 (clsql:sql [foo :integer])
63 (deftest :syntax/ident/6
64 (clsql:sql [foo bar :integer])
67 ;; not sure about this one
68 (deftest :syntax/ident/7
69 (clsql:sql ["foo" bar :integer])
73 (deftest :syntax/value/1
74 (clsql:sql [any '(3 4)])
77 (deftest :syntax/value/2
82 (deftest :syntax/relational/1
83 (clsql:sql [> [baz] [beep]])
86 (deftest :syntax/relational/2
88 (clsql:sql [> [foo] x]))
92 (deftest :syntax/function/1
93 (clsql:sql [function "COS" [age]])
96 (deftest :syntax/function/2
97 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
98 "TO_DATE('02/06/99','mm/DD/RR')")
100 (deftest :syntax/query/1
101 (clsql:sql [select [person_id] [surname] :from [person]])
102 "SELECT PERSON_ID,SURNAME FROM PERSON")
104 (deftest :syntax/query/2
105 (clsql:sql [select [foo] [bar *]
107 :where [or [= [foo] 3]
109 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
111 (deftest :syntax/query/3
112 (clsql:sql [select [foo bar] [baz]
113 :from '([foo] [quux])
114 :where [or [> [baz] 3]
115 [like [foo bar] "SU%"]]])
116 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
118 (deftest :syntax/query/4
119 (clsql:sql [select [count [*]] :from [emp]])
120 "SELECT COUNT(*) FROM EMP")
123 (deftest :syntax/expression1
127 (clsql:sql-expression :table 'foo :attribute 'bar)
128 (clsql:sql-expression :attribute 'baz)
130 (clsql:sql-expression :table 'foo)
131 (clsql:sql-expression :table 'quux))
133 (clsql:sql-operation 'or
136 (clsql:sql-expression :attribute 'baz)
140 (clsql:sql-expression :table 'foo
143 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
145 (deftest :syntax/expression/2
147 (apply (clsql:sql-operator 'and)
148 (loop for table in '(thistime nexttime sometime never)
152 (clsql:sql-expression :table table
154 (clsql:sql-operation '* [hip] [hop])
157 [like (clsql:sql-expression :table table
159 (clsql:sql table)])))
160 "(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'))")
162 #.(clsql:restore-sql-reader-syntax-state)