Annotation of embedaddon/sqlite3/test/descidx2.test, revision 1.1.1.1
1.1 misho 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: descidx2.test,v 1.5 2008/03/19 00:21:31 drh 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:
26: db eval {PRAGMA legacy_file_format=OFF}
27:
28: # This procedure sets the value of the file-format in file 'test.db'
29: # to $newval. Also, the schema cookie is incremented.
30: #
31: proc set_file_format {newval} {
32: hexio_write test.db 44 [hexio_render_int32 $newval]
33: set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
34: incr schemacookie
35: hexio_write test.db 40 [hexio_render_int32 $schemacookie]
36: return {}
37: }
38:
39: # This procedure returns the value of the file-format in file 'test.db'.
40: #
41: proc get_file_format {{fname test.db}} {
42: return [hexio_get_int [hexio_read $fname 44 4]]
43: }
44:
45:
46: # Verify that the file format starts as 4
47: #
48: do_test descidx2-1.1 {
49: execsql {
50: CREATE TABLE t1(a,b);
51: CREATE INDEX i1 ON t1(b ASC);
52: }
53: get_file_format
54: } {4}
55: do_test descidx2-1.2 {
56: execsql {
57: CREATE INDEX i2 ON t1(a DESC);
58: }
59: get_file_format
60: } {4}
61:
62: # Before adding any information to the database, set the file format
63: # back to three. Then close and reopen the database. With the file
64: # format set to three, SQLite should ignore the DESC argument on the
65: # index.
66: #
67: do_test descidx2-2.0 {
68: set_file_format 3
69: db close
70: sqlite3 db test.db
71: get_file_format
72: } {3}
73:
74: # Put some information in the table and verify that the DESC
75: # on the index is ignored.
76: #
77: do_test descidx2-2.1 {
78: execsql {
79: INSERT INTO t1 VALUES(1,1);
80: INSERT INTO t1 VALUES(2,2);
81: INSERT INTO t1 SELECT a+2, a+2 FROM t1;
82: INSERT INTO t1 SELECT a+4, a+4 FROM t1;
83: SELECT b FROM t1 WHERE a>3 AND a<7;
84: }
85: } {4 5 6}
86: do_test descidx2-2.2 {
87: execsql {
88: SELECT a FROM t1 WHERE b>3 AND b<7;
89: }
90: } {4 5 6}
91: do_test descidx2-2.3 {
92: execsql {
93: SELECT b FROM t1 WHERE a>=3 AND a<7;
94: }
95: } {3 4 5 6}
96: do_test descidx2-2.4 {
97: execsql {
98: SELECT b FROM t1 WHERE a>3 AND a<=7;
99: }
100: } {4 5 6 7}
101: do_test descidx2-2.5 {
102: execsql {
103: SELECT b FROM t1 WHERE a>=3 AND a<=7;
104: }
105: } {3 4 5 6 7}
106: do_test descidx2-2.6 {
107: execsql {
108: SELECT a FROM t1 WHERE b>=3 AND b<=7;
109: }
110: } {3 4 5 6 7}
111:
112: # This procedure executes the SQL. Then it checks to see if the OP_Sort
113: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
114: # to the result. If no OP_Sort happened, then "nosort" is appended.
115: #
116: # This procedure is used to check to make sure sorting is or is not
117: # occurring as expected.
118: #
119: proc cksort {sql} {
120: set ::sqlite_sort_count 0
121: set data [execsql $sql]
122: if {$::sqlite_sort_count} {set x sort} {set x nosort}
123: lappend data $x
124: return $data
125: }
126:
127: # Test sorting using a descending index.
128: #
129: do_test descidx2-3.1 {
130: cksort {SELECT a FROM t1 ORDER BY a}
131: } {1 2 3 4 5 6 7 8 nosort}
132: do_test descidx2-3.2 {
133: cksort {SELECT a FROM t1 ORDER BY a ASC}
134: } {1 2 3 4 5 6 7 8 nosort}
135: do_test descidx2-3.3 {
136: cksort {SELECT a FROM t1 ORDER BY a DESC}
137: } {8 7 6 5 4 3 2 1 nosort}
138: do_test descidx2-3.4 {
139: cksort {SELECT b FROM t1 ORDER BY a}
140: } {1 2 3 4 5 6 7 8 nosort}
141: do_test descidx2-3.5 {
142: cksort {SELECT b FROM t1 ORDER BY a ASC}
143: } {1 2 3 4 5 6 7 8 nosort}
144: do_test descidx2-3.6 {
145: cksort {SELECT b FROM t1 ORDER BY a DESC}
146: } {8 7 6 5 4 3 2 1 nosort}
147: do_test descidx2-3.7 {
148: cksort {SELECT a FROM t1 ORDER BY b}
149: } {1 2 3 4 5 6 7 8 nosort}
150: do_test descidx2-3.8 {
151: cksort {SELECT a FROM t1 ORDER BY b ASC}
152: } {1 2 3 4 5 6 7 8 nosort}
153: do_test descidx2-3.9 {
154: cksort {SELECT a FROM t1 ORDER BY b DESC}
155: } {8 7 6 5 4 3 2 1 nosort}
156: do_test descidx2-3.10 {
157: cksort {SELECT b FROM t1 ORDER BY b}
158: } {1 2 3 4 5 6 7 8 nosort}
159: do_test descidx2-3.11 {
160: cksort {SELECT b FROM t1 ORDER BY b ASC}
161: } {1 2 3 4 5 6 7 8 nosort}
162: do_test descidx2-3.12 {
163: cksort {SELECT b FROM t1 ORDER BY b DESC}
164: } {8 7 6 5 4 3 2 1 nosort}
165:
166: do_test descidx2-3.21 {
167: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
168: } {4 5 6 7 nosort}
169: do_test descidx2-3.22 {
170: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
171: } {4 5 6 7 nosort}
172: do_test descidx2-3.23 {
173: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
174: } {7 6 5 4 nosort}
175: do_test descidx2-3.24 {
176: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
177: } {4 5 6 7 nosort}
178: do_test descidx2-3.25 {
179: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
180: } {4 5 6 7 nosort}
181: do_test descidx2-3.26 {
182: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
183: } {7 6 5 4 nosort}
184:
185: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to descidx2.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