| blob | 4b86787a05feac9af305a5aff2f833cc18771ac1 |
1 # 2010 September 24
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 #
12 # This file implements tests to verify that the "testable statements" in
13 # the lang_reindex.html document are correct.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 proc do_reindex_tests {args} {
20 uplevel do_select_tests $args
21 }
23 do_execsql_test e_reindex-0.0 {
24 CREATE TABLE t1(a, b);
25 CREATE INDEX i1 ON t1(a, b);
26 CREATE INDEX i2 ON t1(b, a);
27 } {}
29 # -- syntax diagram reindex-stmt
30 #
31 do_reindex_tests e_reindex-0.1 {
32 1 "REINDEX" {}
33 2 "REINDEX nocase" {}
34 3 "REINDEX binary" {}
35 4 "REINDEX t1" {}
36 5 "REINDEX main.t1" {}
37 6 "REINDEX i1" {}
38 7 "REINDEX main.i1" {}
39 }
41 # EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
42 # recreate indices from scratch.
43 #
44 # Test this by corrupting some database indexes, running REINDEX, and
45 # observing that the corruption is gone.
46 #
47 do_execsql_test e_reindex-1.1 {
48 INSERT INTO t1 VALUES(1, 2);
49 INSERT INTO t1 VALUES(3, 4);
50 INSERT INTO t1 VALUES(5, 6);
51 PRAGMA writable_schema = 1;
52 UPDATE sqlite_master SET sql = '-- ' || sql WHERE type = 'index';
53 } {}
55 db close
56 sqlite3 db test.db
57 do_execsql_test e_reindex-1.2 {
58 DELETE FROM t1 WHERE a = 3;
59 INSERT INTO t1 VALUES(7, 8);
60 INSERT INTO t1 VALUES(9, 10);
61 PRAGMA writable_schema = 1;
62 UPDATE sqlite_master SET sql = substr(sql, 4) WHERE type = 'index';
63 } {}
65 db close
66 sqlite3 db test.db
67 do_execsql_test e_reindex-1.3 {
68 PRAGMA integrity_check;
69 } [list \
70 {row 3 missing from index i2} \
71 {row 3 missing from index i1} \
72 {row 4 missing from index i2} \
73 {row 4 missing from index i1} \
74 {wrong # of entries in index i2} \
75 {wrong # of entries in index i1}
76 ]
78 do_execsql_test e_reindex-1.4 {
79 REINDEX;
80 PRAGMA integrity_check;
81 } {ok}
83 #-------------------------------------------------------------------------
84 # The remaining tests in this file focus on testing that the REINDEX
85 # command reindexes the correct subset of the indexes in the database.
86 # They all use the following dataset.
87 #
88 db close
89 forcedelete test.db2
90 forcedelete test.db
91 sqlite3 db test.db
93 proc sort_by_length {lhs rhs} {
94 set res [expr {[string length $lhs] - [string length $rhs]}]
95 if {$res!=0} {return $res}
96 return [string compare $lhs $rhs]
97 }
98 array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
99 proc sort_by_value {lhs rhs} {
100 global V
101 set res [expr {$V($lhs) - $V($rhs)}]
102 if {$res!=0} {return $res}
103 return [string compare $lhs $rhs]
104 }
106 db collate collA sort_by_length
107 db collate collB sort_by_value
109 set BY(length) {one six two five four eight seven three}
110 set BY(value) {one two three four five six seven eight}
112 do_execsql_test e_reindex-2.0 {
113 ATTACH 'test.db2' AS aux;
115 CREATE TABLE t1(x);
116 CREATE INDEX i1_a ON t1(x COLLATE collA);
117 CREATE INDEX i1_b ON t1(x COLLATE collB);
118 INSERT INTO t1 VALUES('one');
119 INSERT INTO t1 VALUES('two');
120 INSERT INTO t1 VALUES('three');
121 INSERT INTO t1 VALUES('four');
122 INSERT INTO t1 VALUES('five');
123 INSERT INTO t1 VALUES('six');
124 INSERT INTO t1 VALUES('seven');
125 INSERT INTO t1 VALUES('eight');
127 CREATE TABLE t2(x);
128 CREATE INDEX i2_a ON t2(x COLLATE collA);
129 CREATE INDEX i2_b ON t2(x COLLATE collB);
130 INSERT INTO t2 SELECT x FROM t1;
132 CREATE TABLE aux.t1(x);
133 CREATE INDEX aux.i1_a ON t1(x COLLATE collA);
134 CREATE INDEX aux.i1_b ON t1(x COLLATE collB);
135 INSERT INTO aux.t1 SELECT x FROM main.t1;
137 } {}
139 proc test_index {tn tbl collation expected} {
140 set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"
141 uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]
142 }
144 proc set_collations {a b} {
145 db collate collA "sort_by_$a"
146 db collate collB "sort_by_$b"
147 }
149 test_index 1.1 t1 collA length
150 test_index 1.2 t1 collB value
151 test_index 1.3 t2 collA length
152 test_index 1.4 t2 collB value
153 test_index 1.5 aux.t1 collA length
154 test_index 1.6 aux.t1 collB value
157 # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a
158 # collation-sequence or database object identifier, then all indices in
159 # all attached databases are rebuilt.
160 #
161 set_collations value length
162 do_execsql_test e_reindex-2.2.1 "REINDEX" {}
163 test_index 2.2 t1 collA value
164 test_index 2.3 t1 collB length
165 test_index 2.4 t2 collA value
166 test_index 2.5 t2 collB length
167 test_index 2.6 aux.t1 collA value
168 test_index 2.7 aux.t1 collB length
170 # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a
171 # collation-sequence name, then all indices in all attached databases
172 # that use the named collation sequences are recreated.
173 #
174 set_collations length value
175 do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
176 test_index 3.2 t1 collA length
177 test_index 3.3 t1 collB length
178 test_index 3.4 t2 collA length
179 test_index 3.5 t2 collB length
180 test_index 3.6 aux.t1 collA length
181 test_index 3.7 aux.t1 collB length
182 do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
183 test_index 3.9 t1 collA length
184 test_index 3.10 t1 collB value
185 test_index 3.11 t2 collA length
186 test_index 3.12 t2 collB value
187 test_index 3.13 aux.t1 collA length
188 test_index 3.14 aux.t1 collB value
190 # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX
191 # identifies a specific database table, then all indices attached to the
192 # database table are rebuilt.
193 #
194 set_collations value length
195 do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
196 test_index 4.2 t1 collA value
197 test_index 4.3 t1 collB length
198 test_index 4.4 t2 collA length
199 test_index 4.5 t2 collB value
200 test_index 4.6 aux.t1 collA length
201 test_index 4.7 aux.t1 collB value
202 do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
203 test_index 4.9 t1 collA value
204 test_index 4.10 t1 collB length
205 test_index 4.11 t2 collA length
206 test_index 4.12 t2 collB value
207 test_index 4.13 aux.t1 collA value
208 test_index 4.14 aux.t1 collB length
209 do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
210 test_index 4.16 t1 collA value
211 test_index 4.17 t1 collB length
212 test_index 4.18 t2 collA value
213 test_index 4.19 t2 collB length
214 test_index 4.20 aux.t1 collA value
215 test_index 4.21 aux.t1 collB length
217 # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,
218 # then just that index is recreated.
219 #
220 set_collations length value
221 do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
222 test_index 5.2 t1 collA length
223 test_index 5.3 t1 collB length
224 test_index 5.4 t2 collA value
225 test_index 5.5 t2 collB length
226 test_index 5.6 aux.t1 collA value
227 test_index 5.7 aux.t1 collB length
228 do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
229 test_index 5.9 t1 collA length
230 test_index 5.10 t1 collB length
231 test_index 5.11 t2 collA value
232 test_index 5.12 t2 collB value
233 test_index 5.13 aux.t1 collA value
234 test_index 5.14 aux.t1 collB length
235 do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
236 test_index 5.16 t1 collA length
237 test_index 5.17 t1 collB length
238 test_index 5.18 t2 collA value
239 test_index 5.19 t2 collB value
240 test_index 5.20 aux.t1 collA value
241 test_index 5.21 aux.t1 collB value
242 do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
243 test_index 5.23 t1 collA length
244 test_index 5.24 t1 collB value
245 test_index 5.25 t2 collA value
246 test_index 5.26 t2 collB value
247 test_index 5.27 aux.t1 collA value
248 test_index 5.28 aux.t1 collB value
249 do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
250 test_index 5.30 t1 collA length
251 test_index 5.31 t1 collB value
252 test_index 5.32 t2 collA length
253 test_index 5.33 t2 collB value
254 test_index 5.34 aux.t1 collA value
255 test_index 5.35 aux.t1 collB value
256 do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
257 test_index 5.37 t1 collA length
258 test_index 5.38 t1 collB value
259 test_index 5.39 t2 collA length
260 test_index 5.40 t2 collB value
261 test_index 5.41 aux.t1 collA length
262 test_index 5.42 aux.t1 collB value
264 # EVIDENCE-OF: R-15639-02023 If no database-name is specified and there
265 # exists both a table or index and a collation sequence of the specified
266 # name, SQLite interprets this as a request to rebuild the indices that
267 # use the named collation sequence.
268 #
269 set_collations value length
270 do_execsql_test e_reindex-2.6.0 {
271 CREATE TABLE collA(x);
272 CREATE INDEX icolla_a ON collA(x COLLATE collA);
273 CREATE INDEX icolla_b ON collA(x COLLATE collB);
275 INSERT INTO collA SELECT x FROM t1;
276 } {}
278 test_index 6.1 collA collA value
279 test_index 6.2 collA collB length
281 set_collations length value
282 do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
283 test_index 6.4 collA collA length
284 test_index 6.5 collA collB length
285 do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
286 test_index 6.4 collA collA length
287 test_index 6.5 collA collB value
289 set_collations value length
290 do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
291 test_index 6.7 collA collA value
292 test_index 6.8 collA collB length
294 finish_test