wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 October 6
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the LIMIT ... OFFSET ... clause
13 # of UPDATE and DELETE statements.
14 #
15 # $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 proc create_test_data {size} {
21 # Build some test data
22 #
23 execsql {
24 DROP TABLE IF EXISTS t1;
25 CREATE TABLE t1(x int, y int);
26 BEGIN;
27 }
28 for {set i 1} {$i<=$size} {incr i} {
29 for {set j 1} {$j<=$size} {incr j} {
30 execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])"
31 }
32 }
33 execsql {
34 COMMIT;
35 }
36 return {}
37 }
38  
39 ifcapable {update_delete_limit} {
40  
41 # check syntax error support
42 do_test wherelimit-0.1 {
43 catchsql {DELETE FROM t1 ORDER BY x}
44 } {1 {ORDER BY without LIMIT on DELETE}}
45 do_test wherelimit-0.2 {
46 catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
47 } {1 {ORDER BY without LIMIT on DELETE}}
48 do_test wherelimit-0.3 {
49 catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
50 } {1 {ORDER BY without LIMIT on UPDATE}}
51  
52 # no AS on table sources
53 do_test wherelimit-0.4 {
54 catchsql {DELETE FROM t1 AS a WHERE x=1}
55 } {1 {near "AS": syntax error}}
56 do_test wherelimit-0.5 {
57 catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
58 } {1 {near "AS": syntax error}}
59  
60 # OFFSET w/o LIMIT
61 do_test wherelimit-0.6 {
62 catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2}
63 } {1 {near "OFFSET": syntax error}}
64 do_test wherelimit-0.7 {
65 catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2}
66 } {1 {near "OFFSET": syntax error}}
67  
68  
69 # check deletes w/o where clauses but with limit/offsets
70 create_test_data 5
71 do_test wherelimit-1.0 {
72 execsql {SELECT count(*) FROM t1}
73 } {25}
74 do_test wherelimit-1.1 {
75 execsql {DELETE FROM t1}
76 execsql {SELECT count(*) FROM t1}
77 } {0}
78 create_test_data 5
79 do_test wherelimit-1.2 {
80 execsql {DELETE FROM t1 LIMIT 5}
81 execsql {SELECT count(*) FROM t1}
82 } {20}
83 do_test wherelimit-1.3 {
84 # limit 5
85 execsql {DELETE FROM t1 ORDER BY x LIMIT 5}
86 execsql {SELECT count(*) FROM t1}
87 } {15}
88 do_test wherelimit-1.4 {
89 # limit 5, offset 2
90 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
91 execsql {SELECT count(*) FROM t1}
92 } {10}
93 do_test wherelimit-1.5 {
94 # limit 5, offset -2
95 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2}
96 execsql {SELECT count(*) FROM t1}
97 } {5}
98 do_test wherelimit-1.6 {
99 # limit -5 (no limit), offset 2
100 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5}
101 execsql {SELECT count(*) FROM t1}
102 } {2}
103 do_test wherelimit-1.7 {
104 # limit 5, offset -2 (no offset)
105 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5}
106 execsql {SELECT count(*) FROM t1}
107 } {0}
108 create_test_data 5
109 do_test wherelimit-1.8 {
110 # limit -5 (no limit), offset -2 (no offset)
111 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5}
112 execsql {SELECT count(*) FROM t1}
113 } {0}
114 create_test_data 3
115 do_test wherelimit-1.9 {
116 # limit 5, offset 2
117 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5}
118 execsql {SELECT count(*) FROM t1}
119 } {4}
120 do_test wherelimit-1.10 {
121 # limit 5, offset 5
122 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
123 execsql {SELECT count(*) FROM t1}
124 } {4}
125 do_test wherelimit-1.11 {
126 # limit 50, offset 30
127 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
128 execsql {SELECT count(*) FROM t1}
129 } {4}
130 do_test wherelimit-1.12 {
131 # limit 50, offset 30
132 execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50}
133 execsql {SELECT count(*) FROM t1}
134 } {4}
135 do_test wherelimit-1.13 {
136 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
137 execsql {SELECT count(*) FROM t1}
138 } {4}
139  
140  
141 create_test_data 6
142 do_test wherelimit-2.0 {
143 execsql {SELECT count(*) FROM t1}
144 } {36}
145 do_test wherelimit-2.1 {
146 execsql {DELETE FROM t1 WHERE x=1}
147 execsql {SELECT count(*) FROM t1}
148 } {30}
149 create_test_data 6
150 do_test wherelimit-2.2 {
151 execsql {DELETE FROM t1 WHERE x=1 LIMIT 5}
152 execsql {SELECT count(*) FROM t1}
153 } {31}
154 do_test wherelimit-2.3 {
155 # limit 5
156 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5}
157 execsql {SELECT count(*) FROM t1}
158 } {30}
159 do_test wherelimit-2.4 {
160 # limit 5, offset 2
161 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
162 execsql {SELECT count(*) FROM t1}
163 } {26}
164 do_test wherelimit-2.5 {
165 # limit 5, offset -2
166 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
167 execsql {SELECT count(*) FROM t1}
168 } {24}
169 do_test wherelimit-2.6 {
170 # limit -5 (no limit), offset 2
171 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5}
172 execsql {SELECT count(*) FROM t1}
173 } {20}
174 do_test wherelimit-2.7 {
175 # limit 5, offset -2 (no offset)
176 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5}
177 execsql {SELECT count(*) FROM t1}
178 } {18}
179 do_test wherelimit-2.8 {
180 # limit -5 (no limit), offset -2 (no offset)
181 execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5}
182 execsql {SELECT count(*) FROM t1}
183 } {12}
184 create_test_data 6
185 do_test wherelimit-2.9 {
186 # limit 5, offset 2
187 execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5}
188 execsql {SELECT count(*) FROM t1}
189 } {32}
190 do_test wherelimit-2.10 {
191 # limit 5, offset 5
192 execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
193 execsql {SELECT count(*) FROM t1}
194 } {31}
195 do_test wherelimit-2.11 {
196 # limit 50, offset 30
197 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
198 execsql {SELECT count(*) FROM t1}
199 } {31}
200 do_test wherelimit-2.12 {
201 # limit 50, offset 30
202 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50}
203 execsql {SELECT count(*) FROM t1}
204 } {31}
205 do_test wherelimit-2.13 {
206 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
207 execsql {SELECT count(*) FROM t1}
208 } {31}
209  
210  
211 create_test_data 6
212 do_test wherelimit-3.0 {
213 execsql {SELECT count(*) FROM t1}
214 } {36}
215 do_test wherelimit-3.1 {
216 execsql {UPDATE t1 SET y=1 WHERE x=1}
217 execsql {SELECT count(*) FROM t1 WHERE y=1}
218 } {11}
219 create_test_data 6
220 do_test wherelimit-3.2 {
221 execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5}
222 execsql {SELECT count(*) FROM t1 WHERE y=1}
223 } {10}
224 do_test wherelimit-3.3 {
225 # limit 5
226 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5}
227 execsql {SELECT count(*) FROM t1 WHERE y=2}
228 } {9}
229 create_test_data 6
230 do_test wherelimit-3.4 {
231 # limit 5, offset 2
232 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
233 execsql {SELECT count(*) FROM t1 WHERE y=1}
234 } {6}
235 do_test wherelimit-3.5 {
236 # limit 5, offset -2
237 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
238 execsql {SELECT count(*) FROM t1 WHERE y=1}
239 } {5}
240 do_test wherelimit-3.6 {
241 # limit -5 (no limit), offset 2
242 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5}
243 execsql {SELECT count(*) FROM t1 WHERE y=3}
244 } {8}
245 do_test wherelimit-3.7 {
246 # limit 5, offset -2 (no offset)
247 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5}
248 execsql {SELECT count(*) FROM t1 WHERE y=3}
249 } {10}
250  
251 do_test wherelimit-3.8 {
252 # limit -5 (no limit), offset -2 (no offset)
253 execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5}
254 execsql {SELECT count(*) FROM t1 WHERE y=4}
255 } {9}
256 create_test_data 6
257 do_test wherelimit-3.9 {
258 # limit 5, offset 2
259 execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5}
260 execsql {SELECT count(*) FROM t1 WHERE y=4}
261 } {9}
262 do_test wherelimit-3.10 {
263 # limit 5, offset 5
264 execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
265 execsql {SELECT count(*) FROM t1 WHERE y=1}
266 } {6}
267 do_test wherelimit-3.11 {
268 # limit 50, offset 30
269 execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
270 execsql {SELECT count(*) FROM t1 WHERE y=1}
271 } {6}
272 do_test wherelimit-3.12 {
273 # limit 50, offset 30
274 execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
275 execsql {SELECT count(*) FROM t1 WHERE y=1}
276 } {6}
277 do_test wherelimit-3.13 {
278 execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
279 execsql {SELECT count(*) FROM t1 WHERE y=1}
280 } {6}
281  
282 }
283  
284 finish_test