1: #
2: # 2001 September 15
3: #
4: # The author disclaims copyright to this source code. In place of
5: # a legal notice, here is a blessing:
6: #
7: # May you do good and not evil.
8: # May you find forgiveness for yourself and forgive others.
9: # May you share freely, never taking more than you give.
10: #
11: #***********************************************************************
12: # This file implements regression tests for SQLite library. The
13: # focus of this script is page cache subsystem.
14: #
15: # $Id: collate1.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: #
21: # Tests are roughly organised as follows:
22: #
23: # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.
24: # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.
25: # collate1-3.* - ORDER BY using a default collation type. Also that an
26: # explict collate type overrides a default collate type.
27: # collate1-4.* - ORDER BY using a data type.
28: #
29:
30: #
31: # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal
32: # number, then it is converted to one before the comparison is performed.
33: # Numbers are less than other strings. If neither argument is a number,
34: # [string compare] is used.
35: #
36: db collate HEX hex_collate
37: proc hex_collate {lhs rhs} {
38: set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]
39: set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]
40: if {$lhs_ishex && $rhs_ishex} {
41: set lhsx [scan $lhs %x]
42: set rhsx [scan $rhs %x]
43: if {$lhs < $rhs} {return -1}
44: if {$lhs == $rhs} {return 0}
45: if {$lhs > $rhs} {return 1}
46: }
47: if {$lhs_ishex} {
48: return -1;
49: }
50: if {$rhs_ishex} {
51: return 1;
52: }
53: return [string compare $lhs $rhs]
54: }
55: db function hex {format 0x%X}
56:
57: # Mimic the SQLite 2 collation type NUMERIC.
58: db collate numeric numeric_collate
59: proc numeric_collate {lhs rhs} {
60: if {$lhs == $rhs} {return 0}
61: return [expr ($lhs>$rhs)?1:-1]
62: }
63:
64: do_test collate1-1.0 {
65: execsql {
66: CREATE TABLE collate1t1(c1, c2);
67: INSERT INTO collate1t1 VALUES(45, hex(45));
68: INSERT INTO collate1t1 VALUES(NULL, NULL);
69: INSERT INTO collate1t1 VALUES(281, hex(281));
70: }
71: } {}
72: do_test collate1-1.1 {
73: execsql {
74: SELECT c2 FROM collate1t1 ORDER BY 1;
75: }
76: } {{} 0x119 0x2D}
77: do_test collate1-1.2 {
78: execsql {
79: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;
80: }
81: } {{} 0x2D 0x119}
82: do_test collate1-1.3 {
83: execsql {
84: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;
85: }
86: } {0x119 0x2D {}}
87: do_test collate1-1.4 {
88: execsql {
89: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;
90: }
91: } {{} 0x2D 0x119}
92: do_test collate1-1.5 {
93: execsql {
94: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1
95: }
96: } {{} 0x2D 0x119}
97: do_test collate1-1.6 {
98: execsql {
99: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC
100: }
101: } {{} 0x2D 0x119}
102: do_test collate1-1.7 {
103: execsql {
104: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC
105: }
106: } {0x119 0x2D {}}
107: do_test collate1-1.99 {
108: execsql {
109: DROP TABLE collate1t1;
110: }
111: } {}
112:
113: do_test collate1-2.0 {
114: execsql {
115: CREATE TABLE collate1t1(c1, c2);
116: INSERT INTO collate1t1 VALUES('5', '0x11');
117: INSERT INTO collate1t1 VALUES('5', '0xA');
118: INSERT INTO collate1t1 VALUES(NULL, NULL);
119: INSERT INTO collate1t1 VALUES('7', '0xA');
120: INSERT INTO collate1t1 VALUES('11', '0x11');
121: INSERT INTO collate1t1 VALUES('11', '0x101');
122: }
123: } {}
124: do_test collate1-2.2 {
125: execsql {
126: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
127: }
128: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
129: do_test collate1-2.3 {
130: execsql {
131: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;
132: }
133: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
134: do_test collate1-2.4 {
135: execsql {
136: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
137: }
138: } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
139: do_test collate1-2.5 {
140: execsql {
141: SELECT c1, c2 FROM collate1t1
142: ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;
143: }
144: } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
145: do_test collate1-2.6 {
146: execsql {
147: SELECT c1, c2 FROM collate1t1
148: ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
149: }
150: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
151: do_test collate1-2.12.1 {
152: execsql {
153: SELECT c1 COLLATE numeric, c2 FROM collate1t1
154: ORDER BY 1, 2 COLLATE hex;
155: }
156: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
157: do_test collate1-2.12.2 {
158: execsql {
159: SELECT c1 COLLATE hex, c2 FROM collate1t1
160: ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
161: }
162: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
163: do_test collate1-2.12.3 {
164: execsql {
165: SELECT c1, c2 COLLATE hex FROM collate1t1
166: ORDER BY 1 COLLATE numeric, 2;
167: }
168: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
169: do_test collate1-2.12.4 {
170: execsql {
171: SELECT c1 COLLATE numeric, c2 COLLATE hex
172: FROM collate1t1
173: ORDER BY 1, 2;
174: }
175: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
176: do_test collate1-2.13 {
177: execsql {
178: SELECT c1 COLLATE binary, c2 COLLATE hex
179: FROM collate1t1
180: ORDER BY 1, 2;
181: }
182: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
183: do_test collate1-2.14 {
184: execsql {
185: SELECT c1, c2
186: FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
187: }
188: } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
189: do_test collate1-2.15 {
190: execsql {
191: SELECT c1 COLLATE binary, c2 COLLATE hex
192: FROM collate1t1
193: ORDER BY 1 DESC, 2 DESC;
194: }
195: } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
196: do_test collate1-2.16 {
197: execsql {
198: SELECT c1 COLLATE hex, c2 COLLATE binary
199: FROM collate1t1
200: ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
201: }
202: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
203: do_test collate1-2.99 {
204: execsql {
205: DROP TABLE collate1t1;
206: }
207: } {}
208:
209: #
210: # These tests ensure that the default collation type for a column is used
211: # by an ORDER BY clause correctly. The focus is all the different ways
212: # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.
213: #
214: do_test collate1-3.0 {
215: execsql {
216: CREATE TABLE collate1t1(a COLLATE hex, b);
217: INSERT INTO collate1t1 VALUES( '0x5', 5 );
218: INSERT INTO collate1t1 VALUES( '1', 1 );
219: INSERT INTO collate1t1 VALUES( '0x45', 69 );
220: INSERT INTO collate1t1 VALUES( NULL, NULL );
221: SELECT * FROM collate1t1 ORDER BY a;
222: }
223: } {{} {} 1 1 0x5 5 0x45 69}
224:
225: do_test collate1-3.1 {
226: execsql {
227: SELECT * FROM collate1t1 ORDER BY 1;
228: }
229: } {{} {} 1 1 0x5 5 0x45 69}
230: do_test collate1-3.2 {
231: execsql {
232: SELECT * FROM collate1t1 ORDER BY collate1t1.a;
233: }
234: } {{} {} 1 1 0x5 5 0x45 69}
235: do_test collate1-3.3 {
236: execsql {
237: SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;
238: }
239: } {{} {} 1 1 0x5 5 0x45 69}
240: do_test collate1-3.4 {
241: execsql {
242: SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;
243: }
244: } {{} {} 1 1 0x5 5 0x45 69}
245: do_test collate1-3.5 {
246: execsql {
247: SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;
248: }
249: } {{} {} 0x45 69 0x5 5 1 1}
250: do_test collate1-3.5.1 {
251: execsql {
252: SELECT a COLLATE binary as c1, b as c2
253: FROM collate1t1 ORDER BY c1;
254: }
255: } {{} {} 0x45 69 0x5 5 1 1}
256: do_test collate1-3.6 {
257: execsql {
258: DROP TABLE collate1t1;
259: }
260: } {}
261:
262: # Update for SQLite version 3. The collate1-4.* test cases were written
263: # before manifest types were introduced. The following test cases still
264: # work, due to the 'affinity' mechanism, but they don't prove anything
265: # about collation sequences.
266: #
267: do_test collate1-4.0 {
268: execsql {
269: CREATE TABLE collate1t1(c1 numeric, c2 text);
270: INSERT INTO collate1t1 VALUES(1, 1);
271: INSERT INTO collate1t1 VALUES(12, 12);
272: INSERT INTO collate1t1 VALUES(NULL, NULL);
273: INSERT INTO collate1t1 VALUES(101, 101);
274: }
275: } {}
276: do_test collate1-4.1 {
277: execsql {
278: SELECT c1 FROM collate1t1 ORDER BY 1;
279: }
280: } {{} 1 12 101}
281: do_test collate1-4.2 {
282: execsql {
283: SELECT c2 FROM collate1t1 ORDER BY 1;
284: }
285: } {{} 1 101 12}
286: do_test collate1-4.3 {
287: execsql {
288: SELECT c2+0 FROM collate1t1 ORDER BY 1;
289: }
290: } {{} 1 12 101}
291: do_test collate1-4.4 {
292: execsql {
293: SELECT c1||'' FROM collate1t1 ORDER BY 1;
294: }
295: } {{} 1 101 12}
296: do_test collate1-4.4.1 {
297: execsql {
298: SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;
299: }
300: } {{} 1 12 101}
301: do_test collate1-4.5 {
302: execsql {
303: DROP TABLE collate1t1;
304: }
305: } {}
306:
307: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate1.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