Annotation of embedaddon/sqlite3/test/collate3.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test