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])
72 (deftest :syntax/value/1
73 (clsql:sql [any '(3 4)])
76 (deftest :syntax/value/2
81 (deftest :syntax/relational/1
82 (clsql:sql [> [baz] [beep]])
85 (deftest :syntax/relational/2
87 (clsql:sql [> [foo] x]))
91 (deftest :syntax/function/1
92 (clsql:sql [function "COS" [age]])
95 (deftest :syntax/function/2
96 (clsql:sql [function "TO_DATE" "02/06/99" "mm/DD/RR"])
97 "TO_DATE('02/06/99','mm/DD/RR')")
99 (deftest :syntax/query/1
100 (clsql:sql [select [person_id] [surname] :from [person]])
101 "SELECT PERSON_ID,SURNAME FROM PERSON")
103 (deftest :syntax/query/2
104 (clsql:sql [select [foo] [bar *]
106 :where [or [= [foo] 3]
108 "SELECT FOO,BAR.* FROM BAZ,BAR WHERE ((FOO = 3) OR (BAZ.QUUX > 10))")
110 (deftest :syntax/query/3
111 (clsql:sql [select [foo bar] [baz]
112 :from '([foo] [quux])
113 :where [or [> [baz] 3]
114 [like [foo bar] "SU%"]]])
115 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
117 (deftest :syntax/query/4
118 (clsql:sql [select [count [*]] :from [emp]])
119 "SELECT COUNT(*) FROM EMP")
122 (deftest :syntax/expression1
126 (clsql:sql-expression :table 'foo :attribute 'bar)
127 (clsql:sql-expression :attribute 'baz)
129 (clsql:sql-expression :table 'foo)
130 (clsql:sql-expression :table 'quux))
132 (clsql:sql-operation 'or
135 (clsql:sql-expression :attribute 'baz)
139 (clsql:sql-expression :table 'foo
142 "SELECT FOO.BAR,BAZ FROM FOO,QUUX WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))")
144 (deftest :syntax/expression/2
146 (apply (clsql:sql-operator 'and)
147 (loop for table in '(thistime nexttime sometime never)
151 (clsql:sql-expression :table table
153 (clsql:sql-operation '* [hip] [hop])
156 [like (clsql:sql-expression :table table
158 (clsql:sql table)])))
159 "(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'))")
163 #.(clsql:restore-sql-reader-syntax-state)