Debian Codegraph @

test/reindex.test

raw
1 # 2004 November 5
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.
12 # This file implements tests for the REINDEX command.
13 #
14 # $Id: reindex.test,v 1.4 2008/07/12 14:52:20 drh Exp $
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix reindex
19
20 # There is nothing to test if REINDEX is disable for this build.
21 #
22 ifcapable {!reindex} {
23 finish_test
24 return
25 }
26
27 # Basic sanity checks.
28 #
29 do_test reindex-1.1 {
30 execsql {
31 CREATE TABLE t1(a,b);
32 INSERT INTO t1 VALUES(1,2);
33 INSERT INTO t1 VALUES(3,4);
34 CREATE INDEX i1 ON t1(a);
35 REINDEX;
36 }
37 } {}
38 integrity_check reindex-1.2
39 do_test reindex-1.3 {
40 execsql {
41 REINDEX t1;
42 }
43 } {}
44 integrity_check reindex-1.4
45 do_test reindex-1.5 {
46 execsql {
47 REINDEX i1;
48 }
49 } {}
50 integrity_check reindex-1.6
51 do_test reindex-1.7 {
52 execsql {
53 REINDEX main.t1;
54 }
55 } {}
56 do_test reindex-1.8 {
57 execsql {
58 REINDEX main.i1;
59 }
60 } {}
61 do_test reindex-1.9 {
62 catchsql {
63 REINDEX bogus
64 }
65 } {1 {unable to identify the object to be reindexed}}
66
67 # Set up a table for testing that includes several different collating
68 # sequences including some that we can modify.
69 #
70 do_test reindex-2.1 {
71 proc c1 {a b} {
72 return [expr {-[string compare $a $b]}]
73 }
74 proc c2 {a b} {
75 return [expr {-[string compare [string tolower $a] [string tolower $b]]}]
76 }
77 db collate c1 c1
78 db collate c2 c2
79 execsql {
80 CREATE TABLE t2(
81 a TEXT PRIMARY KEY COLLATE c1,
82 b TEXT UNIQUE COLLATE c2,
83 c TEXT COLLATE nocase,
84 d TEST COLLATE binary
85 );
86 INSERT INTO t2 VALUES('abc','abc','abc','abc');
87 INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD');
88 INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd');
89 INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE');
90 SELECT a FROM t2 ORDER BY a;
91 }
92 } {bcd abc BCDE ABCD}
93 do_test reindex-2.2 {
94 execsql {
95 SELECT b FROM t2 ORDER BY b;
96 }
97 } {BCDE bcd ABCD abc}
98 do_test reindex-2.3 {
99 execsql {
100 SELECT c FROM t2 ORDER BY c;
101 }
102 } {abc ABCD bcd BCDE}
103 do_test reindex-2.4 {
104 execsql {
105 SELECT d FROM t2 ORDER BY d;
106 }
107 } {ABCD BCDE abc bcd}
108
109 # Change a collating sequence function. Verify that REINDEX rebuilds
110 # the index.
111 #
112 do_test reindex-2.5 {
113 proc c1 {a b} {
114 return [string compare $a $b]
115 }
116 execsql {
117 SELECT a FROM t2 ORDER BY a;
118 }
119 } {bcd abc BCDE ABCD}
120 ifcapable {integrityck} {
121 do_test reindex-2.5.1 {
122 string equal ok [execsql {PRAGMA integrity_check}]
123 } {0}
124 }
125 do_test reindex-2.6 {
126 execsql {
127 REINDEX c2;
128 SELECT a FROM t2 ORDER BY a;
129 }
130 } {bcd abc BCDE ABCD}
131 do_test reindex-2.7 {
132 execsql {
133 REINDEX t1;
134 SELECT a FROM t2 ORDER BY a;
135 }
136 } {bcd abc BCDE ABCD}
137 do_test reindex-2.8 {
138 execsql {
139 REINDEX c1;
140 SELECT a FROM t2 ORDER BY a;
141 }
142 } {ABCD BCDE abc bcd}
143 integrity_check reindex-2.8.1
144
145 # Try to REINDEX an index for which the collation sequence is not available.
146 #
147 do_test reindex-3.1 {
148 sqlite3 db2 test.db
149 catchsql {
150 REINDEX c1;
151 } db2
152 } {1 {no such collation sequence: c1}}
153 do_test reindex-3.2 {
154 proc need_collate {collation} {
155 db2 collate c1 c1
156 }
157 db2 collation_needed need_collate
158 catchsql {
159 REINDEX c1;
160 } db2
161 } {0 {}}
162 do_test reindex-3.3 {
163 catchsql {
164 REINDEX;
165 } db2
166 } {1 {no such collation sequence: c2}}
167
168 do_test reindex-3.99 {
169 db2 close
170 } {}
171
172 #-------------------------------------------------------------------------
173 foreach {tn wo} {1 "" 2 "WITHOUT ROWID"} {
174 reset_db
175 eval [string map [list %without_rowid% $wo] {
176 do_execsql_test 4.$tn.0 {
177 CREATE TABLE t0 (
178 c0 INTEGER PRIMARY KEY DESC,
179 c1 UNIQUE DEFAULT NULL
180 ) %without_rowid% ;
181 INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
182 SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1;
183 } {1 2 3 4 5}
184
185 do_execsql_test 4.$tn.1 {
186 REINDEX;
187 }
188
189 do_execsql_test 4.$tn.2 {
190 SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1;
191 } {1 2 3 4 5}
192
193 do_execsql_test 4.$tn.3 {
194 SELECT c0 FROM t0 WHERE c1 IS NULL AND c0 IN (1,2,3,4,5);
195 } {1 2 3 4 5}
196
197 do_execsql_test 4.$tn.4 {
198 PRAGMA integrity_check;
199 } {ok}
200 }]
201 }
202
203
204
205 finish_test