1: # 2005 December 21
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 descending indices.
13: #
14: # $Id: descidx1.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
15: #
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Do not use a codec for tests in this file, as the database file is
21: # manipulated directly using tcl scripts (using the [hexio_write] command).
22: #
23: do_not_use_codec
24:
25: db eval {PRAGMA legacy_file_format=OFF}
26:
27: # This procedure sets the value of the file-format in file 'test.db'
28: # to $newval. Also, the schema cookie is incremented.
29: #
30: proc set_file_format {newval} {
31: hexio_write test.db 44 [hexio_render_int32 $newval]
32: set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
33: incr schemacookie
34: hexio_write test.db 40 [hexio_render_int32 $schemacookie]
35: return {}
36: }
37:
38: # This procedure returns the value of the file-format in file 'test.db'.
39: #
40: proc get_file_format {{fname test.db}} {
41: return [hexio_get_int [hexio_read $fname 44 4]]
42: }
43:
44:
45: # Verify that the file format starts as 4.
46: #
47: do_test descidx1-1.1 {
48: execsql {
49: CREATE TABLE t1(a,b);
50: CREATE INDEX i1 ON t1(b ASC);
51: }
52: get_file_format
53: } {4}
54: do_test descidx1-1.2 {
55: execsql {
56: CREATE INDEX i2 ON t1(a DESC);
57: }
58: get_file_format
59: } {4}
60:
61: # Put some information in the table and verify that the descending
62: # index actually works.
63: #
64: do_test descidx1-2.1 {
65: execsql {
66: INSERT INTO t1 VALUES(1,1);
67: INSERT INTO t1 VALUES(2,2);
68: INSERT INTO t1 SELECT a+2, a+2 FROM t1;
69: INSERT INTO t1 SELECT a+4, a+4 FROM t1;
70: SELECT b FROM t1 WHERE a>3 AND a<7;
71: }
72: } {6 5 4}
73: do_test descidx1-2.2 {
74: execsql {
75: SELECT a FROM t1 WHERE b>3 AND b<7;
76: }
77: } {4 5 6}
78: do_test descidx1-2.3 {
79: execsql {
80: SELECT b FROM t1 WHERE a>=3 AND a<7;
81: }
82: } {6 5 4 3}
83: do_test descidx1-2.4 {
84: execsql {
85: SELECT b FROM t1 WHERE a>3 AND a<=7;
86: }
87: } {7 6 5 4}
88: do_test descidx1-2.5 {
89: execsql {
90: SELECT b FROM t1 WHERE a>=3 AND a<=7;
91: }
92: } {7 6 5 4 3}
93: do_test descidx1-2.6 {
94: execsql {
95: SELECT a FROM t1 WHERE b>=3 AND b<=7;
96: }
97: } {3 4 5 6 7}
98:
99: # This procedure executes the SQL. Then it checks to see if the OP_Sort
100: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
101: # to the result. If no OP_Sort happened, then "nosort" is appended.
102: #
103: # This procedure is used to check to make sure sorting is or is not
104: # occurring as expected.
105: #
106: proc cksort {sql} {
107: set ::sqlite_sort_count 0
108: set data [execsql $sql]
109: if {$::sqlite_sort_count} {set x sort} {set x nosort}
110: lappend data $x
111: return $data
112: }
113:
114: # Test sorting using a descending index.
115: #
116: do_test descidx1-3.1 {
117: cksort {SELECT a FROM t1 ORDER BY a}
118: } {1 2 3 4 5 6 7 8 nosort}
119: do_test descidx1-3.2 {
120: cksort {SELECT a FROM t1 ORDER BY a ASC}
121: } {1 2 3 4 5 6 7 8 nosort}
122: do_test descidx1-3.3 {
123: cksort {SELECT a FROM t1 ORDER BY a DESC}
124: } {8 7 6 5 4 3 2 1 nosort}
125: do_test descidx1-3.4 {
126: cksort {SELECT b FROM t1 ORDER BY a}
127: } {1 2 3 4 5 6 7 8 nosort}
128: do_test descidx1-3.5 {
129: cksort {SELECT b FROM t1 ORDER BY a ASC}
130: } {1 2 3 4 5 6 7 8 nosort}
131: do_test descidx1-3.6 {
132: cksort {SELECT b FROM t1 ORDER BY a DESC}
133: } {8 7 6 5 4 3 2 1 nosort}
134: do_test descidx1-3.7 {
135: cksort {SELECT a FROM t1 ORDER BY b}
136: } {1 2 3 4 5 6 7 8 nosort}
137: do_test descidx1-3.8 {
138: cksort {SELECT a FROM t1 ORDER BY b ASC}
139: } {1 2 3 4 5 6 7 8 nosort}
140: do_test descidx1-3.9 {
141: cksort {SELECT a FROM t1 ORDER BY b DESC}
142: } {8 7 6 5 4 3 2 1 nosort}
143: do_test descidx1-3.10 {
144: cksort {SELECT b FROM t1 ORDER BY b}
145: } {1 2 3 4 5 6 7 8 nosort}
146: do_test descidx1-3.11 {
147: cksort {SELECT b FROM t1 ORDER BY b ASC}
148: } {1 2 3 4 5 6 7 8 nosort}
149: do_test descidx1-3.12 {
150: cksort {SELECT b FROM t1 ORDER BY b DESC}
151: } {8 7 6 5 4 3 2 1 nosort}
152:
153: do_test descidx1-3.21 {
154: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
155: } {4 5 6 7 nosort}
156: do_test descidx1-3.22 {
157: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
158: } {4 5 6 7 nosort}
159: do_test descidx1-3.23 {
160: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
161: } {7 6 5 4 nosort}
162: do_test descidx1-3.24 {
163: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
164: } {4 5 6 7 nosort}
165: do_test descidx1-3.25 {
166: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
167: } {4 5 6 7 nosort}
168: do_test descidx1-3.26 {
169: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
170: } {7 6 5 4 nosort}
171:
172: # Create a table with indices that are descending on some terms and
173: # ascending on others.
174: #
175: ifcapable bloblit {
176: do_test descidx1-4.1 {
177: execsql {
178: CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
179: CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
180: CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
181: INSERT INTO t2 VALUES(1,'one',x'31',1.0);
182: INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
183: INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
184: INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
185: INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
186: INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
187: INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
188: INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
189: INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
190: SELECT count(*) FROM t2;
191: }
192: } {9}
193: do_test descidx1-4.2 {
194: execsql {
195: SELECT d FROM t2 ORDER BY a;
196: }
197: } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
198: do_test descidx1-4.3 {
199: execsql {
200: SELECT d FROM t2 WHERE a>=2;
201: }
202: } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
203: do_test descidx1-4.4 {
204: execsql {
205: SELECT d FROM t2 WHERE a>2;
206: }
207: } {3.0 4.0 5.0 6.0}
208: do_test descidx1-4.5 {
209: execsql {
210: SELECT d FROM t2 WHERE a=2 AND b>'two';
211: }
212: } {2.2}
213: do_test descidx1-4.6 {
214: execsql {
215: SELECT d FROM t2 WHERE a=2 AND b>='two';
216: }
217: } {2.2 2.0 2.1}
218: do_test descidx1-4.7 {
219: execsql {
220: SELECT d FROM t2 WHERE a=2 AND b<'two';
221: }
222: } {}
223: do_test descidx1-4.8 {
224: execsql {
225: SELECT d FROM t2 WHERE a=2 AND b<='two';
226: }
227: } {2.0 2.1}
228: }
229:
230: do_test descidx1-5.1 {
231: execsql {
232: CREATE TABLE t3(a,b,c,d);
233: CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
234: INSERT INTO t3 VALUES(0,0,0,0);
235: INSERT INTO t3 VALUES(0,0,0,1);
236: INSERT INTO t3 VALUES(0,0,1,0);
237: INSERT INTO t3 VALUES(0,0,1,1);
238: INSERT INTO t3 VALUES(0,1,0,0);
239: INSERT INTO t3 VALUES(0,1,0,1);
240: INSERT INTO t3 VALUES(0,1,1,0);
241: INSERT INTO t3 VALUES(0,1,1,1);
242: INSERT INTO t3 VALUES(1,0,0,0);
243: INSERT INTO t3 VALUES(1,0,0,1);
244: INSERT INTO t3 VALUES(1,0,1,0);
245: INSERT INTO t3 VALUES(1,0,1,1);
246: INSERT INTO t3 VALUES(1,1,0,0);
247: INSERT INTO t3 VALUES(1,1,0,1);
248: INSERT INTO t3 VALUES(1,1,1,0);
249: INSERT INTO t3 VALUES(1,1,1,1);
250: SELECT count(*) FROM t3;
251: }
252: } {16}
253: do_test descidx1-5.2 {
254: cksort {
255: SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
256: }
257: } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
258: do_test descidx1-5.3 {
259: cksort {
260: SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
261: }
262: } {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
263: do_test descidx1-5.4 {
264: cksort {
265: SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
266: }
267: } {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
268: do_test descidx1-5.5 {
269: cksort {
270: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
271: }
272: } {101 100 111 110 001 000 011 010 nosort}
273: do_test descidx1-5.6 {
274: cksort {
275: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
276: }
277: } {010 011 000 001 110 111 100 101 nosort}
278: do_test descidx1-5.7 {
279: cksort {
280: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
281: }
282: } {011 010 001 000 111 110 101 100 sort}
283: do_test descidx1-5.8 {
284: cksort {
285: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
286: }
287: } {000 001 010 011 100 101 110 111 sort}
288: do_test descidx1-5.9 {
289: cksort {
290: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
291: }
292: } {110 111 100 101 010 011 000 001 sort}
293:
294: # Test the legacy_file_format pragma here because we have access to
295: # the get_file_format command.
296: #
297: ifcapable legacyformat {
298: do_test descidx1-6.1 {
299: db close
300: forcedelete test.db test.db-journal
301: sqlite3 db test.db
302: execsql {PRAGMA legacy_file_format}
303: } {1}
304: } else {
305: do_test descidx1-6.1 {
306: db close
307: forcedelete test.db test.db-journal
308: sqlite3 db test.db
309: execsql {PRAGMA legacy_file_format}
310: } {0}
311: }
312: do_test descidx1-6.2 {
313: execsql {PRAGMA legacy_file_format=YES}
314: execsql {PRAGMA legacy_file_format}
315: } {1}
316: do_test descidx1-6.3 {
317: execsql {
318: CREATE TABLE t1(a,b,c);
319: }
320: get_file_format
321: } {1}
322: ifcapable vacuum {
323: # Verify that the file format is preserved across a vacuum.
324: do_test descidx1-6.3.1 {
325: execsql {VACUUM}
326: get_file_format
327: } {1}
328: }
329: do_test descidx1-6.4 {
330: db close
331: forcedelete test.db test.db-journal
332: sqlite3 db test.db
333: execsql {PRAGMA legacy_file_format=NO}
334: execsql {PRAGMA legacy_file_format}
335: } {0}
336: do_test descidx1-6.5 {
337: execsql {
338: CREATE TABLE t1(a,b,c);
339: CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);
340: INSERT INTO t1 VALUES(1,2,3);
341: INSERT INTO t1 VALUES(1,1,0);
342: INSERT INTO t1 VALUES(1,2,1);
343: INSERT INTO t1 VALUES(1,3,4);
344: }
345: get_file_format
346: } {4}
347: ifcapable vacuum {
348: # Verify that the file format is preserved across a vacuum.
349: do_test descidx1-6.6 {
350: execsql {VACUUM}
351: get_file_format
352: } {4}
353: do_test descidx1-6.7 {
354: execsql {
355: PRAGMA legacy_file_format=ON;
356: VACUUM;
357: }
358: get_file_format
359: } {4}
360: }
361:
362:
363:
364: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to descidx1.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