wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2001 September 15
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 script is page cache subsystem.
13 #
14 # $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 #
20 # Tests are organised as follows:
21 #
22 # collate3.1.* - Errors related to unknown collation sequences.
23 # collate3.2.* - Errors related to undefined collation sequences.
24 # collate3.3.* - Writing to a table that has an index with an undefined c.s.
25 # collate3.4.* - Misc errors.
26 # collate3.5.* - Collation factory.
27 #
28  
29 #
30 # These tests ensure that when a user executes a statement with an
31 # unknown collation sequence an error is returned.
32 #
33 do_test collate3-1.0 {
34 execsql {
35 CREATE TABLE collate3t1(c1);
36 }
37 } {}
38 do_test collate3-1.1 {
39 catchsql {
40 SELECT * FROM collate3t1 ORDER BY 1 collate garbage;
41 }
42 } {1 {no such collation sequence: garbage}}
43 do_test collate3-1.2 {
44 catchsql {
45 CREATE TABLE collate3t2(c1 collate garbage);
46 }
47 } {1 {no such collation sequence: garbage}}
48 do_test collate3-1.3 {
49 catchsql {
50 CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
51 }
52 } {1 {no such collation sequence: garbage}}
53  
54 execsql {
55 DROP TABLE collate3t1;
56 }
57  
58 #
59 # Create a table with a default collation sequence, then close
60 # and re-open the database without re-registering the collation
61 # sequence. Then make sure the library stops us from using
62 # the collation sequence in:
63 # * an explicitly collated ORDER BY
64 # * an ORDER BY that uses the default collation sequence
65 # * an expression (=)
66 # * a CREATE TABLE statement
67 # * a CREATE INDEX statement that uses a default collation sequence
68 # * a GROUP BY that uses the default collation sequence
69 # * a SELECT DISTINCT that uses the default collation sequence
70 # * Compound SELECTs that uses the default collation sequence
71 # * An ORDER BY on a compound SELECT with an explicit ORDER BY.
72 #
73 do_test collate3-2.0 {
74 db collate string_compare {string compare}
75 execsql {
76 CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);
77 }
78 db close
79 sqlite3 db test.db
80 expr 0
81 } 0
82 do_test collate3-2.1 {
83 catchsql {
84 SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
85 }
86 } {1 {no such collation sequence: string_compare}}
87 do_test collate3-2.2 {
88 catchsql {
89 SELECT * FROM collate3t1 ORDER BY c1;
90 }
91 } {1 {no such collation sequence: string_compare}}
92 do_test collate3-2.3 {
93 catchsql {
94 SELECT * FROM collate3t1 WHERE c1 = 'xxx';
95 }
96 } {1 {no such collation sequence: string_compare}}
97 do_test collate3-2.4 {
98 catchsql {
99 CREATE TABLE collate3t2(c1 COLLATE string_compare);
100 }
101 } {1 {no such collation sequence: string_compare}}
102 do_test collate3-2.5 {
103 catchsql {
104 CREATE INDEX collate3t1_i1 ON collate3t1(c1);
105 }
106 } {1 {no such collation sequence: string_compare}}
107 do_test collate3-2.6 {
108 catchsql {
109 SELECT * FROM collate3t1;
110 }
111 } {0 {}}
112 do_test collate3-2.7.1 {
113 catchsql {
114 SELECT count(*) FROM collate3t1 GROUP BY c1;
115 }
116 } {1 {no such collation sequence: string_compare}}
117 # do_test collate3-2.7.2 {
118 # catchsql {
119 # SELECT * FROM collate3t1 GROUP BY c1;
120 # }
121 # } {1 {GROUP BY may only be used on aggregate queries}}
122 do_test collate3-2.7.2 {
123 catchsql {
124 SELECT * FROM collate3t1 GROUP BY c1;
125 }
126 } {1 {no such collation sequence: string_compare}}
127 do_test collate3-2.8 {
128 catchsql {
129 SELECT DISTINCT c1 FROM collate3t1;
130 }
131 } {1 {no such collation sequence: string_compare}}
132  
133 ifcapable compound {
134 do_test collate3-2.9 {
135 catchsql {
136 SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
137 }
138 } {1 {no such collation sequence: string_compare}}
139 do_test collate3-2.10 {
140 catchsql {
141 SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;
142 }
143 } {1 {no such collation sequence: string_compare}}
144 do_test collate3-2.11 {
145 catchsql {
146 SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;
147 }
148 } {1 {no such collation sequence: string_compare}}
149 do_test collate3-2.12 {
150 catchsql {
151 SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
152 }
153 } {0 {}}
154 do_test collate3-2.13 {
155 catchsql {
156 SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
157 }
158 } {1 {no such collation sequence: string_compare}}
159 do_test collate3-2.14 {
160 catchsql {
161 SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;
162 }
163 } {1 {no such collation sequence: string_compare}}
164 do_test collate3-2.15 {
165 catchsql {
166 SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;
167 }
168 } {1 {no such collation sequence: string_compare}}
169 do_test collate3-2.16 {
170 catchsql {
171 SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;
172 }
173 } {1 {no such collation sequence: string_compare}}
174 do_test collate3-2.17 {
175 catchsql {
176 SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;
177 }
178 } {1 {no such collation sequence: string_compare}}
179 } ;# ifcapable compound
180  
181 #
182 # Create an index that uses a collation sequence then close and
183 # re-open the database without re-registering the collation
184 # sequence. Then check that for the table with the index
185 # * An INSERT fails,
186 # * An UPDATE on the column with the index fails,
187 # * An UPDATE on a different column succeeds.
188 # * A DELETE with a WHERE clause fails
189 # * A DELETE without a WHERE clause succeeds
190 #
191 # Also, ensure that the restrictions tested by collate3-2.* still
192 # apply after the index has been created.
193 #
194 do_test collate3-3.0 {
195 db collate string_compare {string compare}
196 execsql {
197 CREATE INDEX collate3t1_i1 ON collate3t1(c1);
198 INSERT INTO collate3t1 VALUES('xxx', 'yyy');
199 }
200 db close
201 sqlite3 db test.db
202 expr 0
203 } 0
204 db eval {select * from collate3t1}
205 do_test collate3-3.1 {
206 catchsql {
207 INSERT INTO collate3t1 VALUES('xxx', 0);
208 }
209 } {1 {no such collation sequence: string_compare}}
210 do_test collate3-3.2 {
211 catchsql {
212 UPDATE collate3t1 SET c1 = 'xxx';
213 }
214 } {1 {no such collation sequence: string_compare}}
215 do_test collate3-3.3 {
216 catchsql {
217 UPDATE collate3t1 SET c2 = 'xxx';
218 }
219 } {0 {}}
220 do_test collate3-3.4 {
221 catchsql {
222 DELETE FROM collate3t1 WHERE 1;
223 }
224 } {1 {no such collation sequence: string_compare}}
225 do_test collate3-3.5 {
226 catchsql {
227 SELECT * FROM collate3t1;
228 }
229 } {0 {xxx xxx}}
230 do_test collate3-3.6 {
231 catchsql {
232 DELETE FROM collate3t1;
233 }
234 } {0 {}}
235 ifcapable {integrityck} {
236 do_test collate3-3.8 {
237 catchsql {
238 PRAGMA integrity_check
239 }
240 } {1 {no such collation sequence: string_compare}}
241 }
242 do_test collate3-3.9 {
243 catchsql {
244 SELECT * FROM collate3t1;
245 }
246 } {0 {}}
247 do_test collate3-3.10 {
248 catchsql {
249 SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
250 }
251 } {1 {no such collation sequence: string_compare}}
252 do_test collate3-3.11 {
253 catchsql {
254 SELECT * FROM collate3t1 ORDER BY c1;
255 }
256 } {1 {no such collation sequence: string_compare}}
257 do_test collate3-3.12 {
258 catchsql {
259 SELECT * FROM collate3t1 WHERE c1 = 'xxx';
260 }
261 } {1 {no such collation sequence: string_compare}}
262 do_test collate3-3.13 {
263 catchsql {
264 CREATE TABLE collate3t2(c1 COLLATE string_compare);
265 }
266 } {1 {no such collation sequence: string_compare}}
267 do_test collate3-3.14 {
268 catchsql {
269 CREATE INDEX collate3t1_i2 ON collate3t1(c1);
270 }
271 } {1 {no such collation sequence: string_compare}}
272 do_test collate3-3.15 {
273 execsql {
274 DROP TABLE collate3t1;
275 }
276 } {}
277  
278 # Check we can create an index that uses an explicit collation
279 # sequence and then close and re-open the database.
280 do_test collate3-4.6 {
281 db collate user_defined "string compare"
282 execsql {
283 CREATE TABLE collate3t1(a, b);
284 INSERT INTO collate3t1 VALUES('hello', NULL);
285 CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);
286 }
287 } {}
288 do_test collate3-4.7 {
289 db close
290 sqlite3 db test.db
291 catchsql {
292 SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
293 }
294 } {1 {no such collation sequence: user_defined}}
295 do_test collate3-4.8.1 {
296 db collate user_defined "string compare"
297 catchsql {
298 SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
299 }
300 } {0 {hello {}}}
301 do_test collate3-4.8.2 {
302 db close
303 lindex [catch {
304 sqlite3 db test.db
305 }] 0
306 } {0}
307 do_test collate3-4.8.3 {
308 execsql {
309 DROP TABLE collate3t1;
310 }
311 } {}
312  
313 # Compare strings as numbers.
314 proc numeric_compare {lhs rhs} {
315 if {$rhs > $lhs} {
316 set res -1
317 } else {
318 set res [expr ($lhs > $rhs)?1:0]
319 }
320 return $res
321 }
322  
323 # Check we can create a view that uses an explicit collation
324 # sequence and then close and re-open the database.
325 ifcapable view {
326 do_test collate3-4.9 {
327 db collate user_defined numeric_compare
328 execsql {
329 CREATE TABLE collate3t1(a, b);
330 INSERT INTO collate3t1 VALUES('2', NULL);
331 INSERT INTO collate3t1 VALUES('101', NULL);
332 INSERT INTO collate3t1 VALUES('12', NULL);
333 CREATE VIEW collate3v1 AS SELECT * FROM collate3t1
334 ORDER BY 1 COLLATE user_defined;
335 SELECT * FROM collate3v1;
336 }
337 } {2 {} 12 {} 101 {}}
338 do_test collate3-4.10 {
339 db close
340 sqlite3 db test.db
341 catchsql {
342 SELECT * FROM collate3v1;
343 }
344 } {1 {no such collation sequence: user_defined}}
345 do_test collate3-4.11 {
346 db collate user_defined numeric_compare
347 catchsql {
348 SELECT * FROM collate3v1;
349 }
350 } {0 {2 {} 12 {} 101 {}}}
351 do_test collate3-4.12 {
352 execsql {
353 DROP TABLE collate3t1;
354 }
355 } {}
356 } ;# ifcapable view
357  
358 #
359 # Test the collation factory. In the code, the "no such collation sequence"
360 # message is only generated in two places. So these tests just test that
361 # the collation factory can be called once from each of those points.
362 #
363 do_test collate3-5.0 {
364 catchsql {
365 CREATE TABLE collate3t1(a);
366 INSERT INTO collate3t1 VALUES(10);
367 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
368 }
369 } {1 {no such collation sequence: unk}}
370 do_test collate3-5.1 {
371 set ::cfact_cnt 0
372 proc cfact {nm} {
373 db collate $nm {string compare}
374 incr ::cfact_cnt
375 }
376 db collation_needed cfact
377 } {}
378 do_test collate3-5.2 {
379 catchsql {
380 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
381 }
382 } {0 10}
383 do_test collate3-5.3 {
384 set ::cfact_cnt
385 } {1}
386 do_test collate3-5.4 {
387 catchsql {
388 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
389 }
390 } {0 10}
391 do_test collate3-5.5 {
392 set ::cfact_cnt
393 } {1}
394 do_test collate3-5.6 {
395 catchsql {
396 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
397 }
398 } {0 10}
399 do_test collate3-5.7 {
400 execsql {
401 DROP TABLE collate3t1;
402 CREATE TABLE collate3t1(a COLLATE unk);
403 }
404 db close
405 sqlite3 db test.db
406 catchsql {
407 SELECT a FROM collate3t1 ORDER BY 1;
408 }
409 } {1 {no such collation sequence: unk}}
410 do_test collate3-5.8 {
411 set ::cfact_cnt 0
412 proc cfact {nm} {
413 db collate $nm {string compare}
414 incr ::cfact_cnt
415 }
416 db collation_needed cfact
417 catchsql {
418 SELECT a FROM collate3t1 ORDER BY 1;
419 }
420 } {0 {}}
421  
422 do_test collate3-5.9 {
423 execsql {
424 DROP TABLE collate3t1;
425 }
426 } {}
427  
428 finish_test