000001 # 2010 September 24
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests to verify that the "testable statements" in
000013 # the lang_reindex.html document are correct.
000014 #
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 proc do_reindex_tests {args} {
000020 uplevel do_select_tests $args
000021 }
000022
000023 do_execsql_test e_reindex-0.0 {
000024 CREATE TABLE t1(a, b);
000025 CREATE INDEX i1 ON t1(a, b);
000026 CREATE INDEX i2 ON t1(b, a);
000027 } {}
000028
000029 # -- syntax diagram reindex-stmt
000030 #
000031 do_reindex_tests e_reindex-0.1 {
000032 1 "REINDEX" {}
000033 2 "REINDEX nocase" {}
000034 3 "REINDEX binary" {}
000035 4 "REINDEX t1" {}
000036 5 "REINDEX main.t1" {}
000037 6 "REINDEX i1" {}
000038 7 "REINDEX main.i1" {}
000039 }
000040
000041 # EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
000042 # recreate indices from scratch.
000043 #
000044 # Test this by corrupting some database indexes, running REINDEX, and
000045 # observing that the corruption is gone.
000046 #
000047 sqlite3_db_config db DEFENSIVE 0
000048 do_execsql_test e_reindex-1.1 {
000049 INSERT INTO t1 VALUES(1, 2);
000050 INSERT INTO t1 VALUES(3, 4);
000051 INSERT INTO t1 VALUES(5, 6);
000052
000053 CREATE TABLE saved(a,b,c,d,e);
000054 INSERT INTO saved SELECT * FROM sqlite_master WHERE type = 'index';
000055 PRAGMA writable_schema = 1;
000056 DELETE FROM sqlite_master WHERE type = 'index';
000057 } {}
000058
000059 db close
000060 sqlite3 db test.db
000061 sqlite3_db_config db DEFENSIVE 0
000062 do_execsql_test e_reindex-1.2 {
000063 DELETE FROM t1 WHERE a = 3;
000064 INSERT INTO t1 VALUES(7, 8);
000065 INSERT INTO t1 VALUES(9, 10);
000066 PRAGMA writable_schema = 1;
000067 INSERT INTO sqlite_master SELECT * FROM saved;
000068 DROP TABLE saved;
000069 } {}
000070
000071 db close
000072 sqlite3 db test.db
000073 do_execsql_test e_reindex-1.3 {
000074 PRAGMA integrity_check;
000075 } [list \
000076 {row 3 missing from index i2} \
000077 {row 3 missing from index i1} \
000078 {row 4 missing from index i2} \
000079 {row 4 missing from index i1} \
000080 {wrong # of entries in index i2} \
000081 {wrong # of entries in index i1}
000082 ]
000083
000084 do_execsql_test e_reindex-1.4 {
000085 REINDEX;
000086 PRAGMA integrity_check;
000087 } {ok}
000088
000089 #-------------------------------------------------------------------------
000090 # The remaining tests in this file focus on testing that the REINDEX
000091 # command reindexes the correct subset of the indexes in the database.
000092 # They all use the following dataset.
000093 #
000094 db close
000095 forcedelete test.db2
000096 forcedelete test.db
000097 sqlite3 db test.db
000098
000099 proc sort_by_length {lhs rhs} {
000100 set res [expr {[string length $lhs] - [string length $rhs]}]
000101 if {$res!=0} {return $res}
000102 return [string compare $lhs $rhs]
000103 }
000104 array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
000105 proc sort_by_value {lhs rhs} {
000106 global V
000107 set res [expr {$V($lhs) - $V($rhs)}]
000108 if {$res!=0} {return $res}
000109 return [string compare $lhs $rhs]
000110 }
000111
000112 db collate collA sort_by_length
000113 db collate collB sort_by_value
000114
000115 set BY(length) {one six two five four eight seven three}
000116 set BY(value) {one two three four five six seven eight}
000117
000118 do_execsql_test e_reindex-2.0 {
000119 ATTACH 'test.db2' AS aux;
000120
000121 CREATE TABLE t1(x);
000122 CREATE INDEX i1_a ON t1(x COLLATE collA);
000123 CREATE INDEX i1_b ON t1(x COLLATE collB);
000124 INSERT INTO t1 VALUES('one');
000125 INSERT INTO t1 VALUES('two');
000126 INSERT INTO t1 VALUES('three');
000127 INSERT INTO t1 VALUES('four');
000128 INSERT INTO t1 VALUES('five');
000129 INSERT INTO t1 VALUES('six');
000130 INSERT INTO t1 VALUES('seven');
000131 INSERT INTO t1 VALUES('eight');
000132
000133 CREATE TABLE t2(x);
000134 CREATE INDEX i2_a ON t2(x COLLATE collA);
000135 CREATE INDEX i2_b ON t2(x COLLATE collB);
000136 INSERT INTO t2 SELECT x FROM t1;
000137
000138 CREATE TABLE aux.t1(x);
000139 CREATE INDEX aux.i1_a ON t1(x COLLATE collA);
000140 CREATE INDEX aux.i1_b ON t1(x COLLATE collB);
000141 INSERT INTO aux.t1 SELECT x FROM main.t1;
000142
000143 } {}
000144
000145 proc test_index {tn tbl collation expected} {
000146 set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"
000147 uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]
000148 }
000149
000150 proc set_collations {a b} {
000151 db collate collA "sort_by_$a"
000152 db collate collB "sort_by_$b"
000153 }
000154
000155 test_index 1.1 t1 collA length
000156 test_index 1.2 t1 collB value
000157 test_index 1.3 t2 collA length
000158 test_index 1.4 t2 collB value
000159 test_index 1.5 aux.t1 collA length
000160 test_index 1.6 aux.t1 collB value
000161
000162
000163 # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a
000164 # collation-sequence or database object identifier, then all indices in
000165 # all attached databases are rebuilt.
000166 #
000167 set_collations value length
000168 do_execsql_test e_reindex-2.2.1 "REINDEX" {}
000169 test_index 2.2 t1 collA value
000170 test_index 2.3 t1 collB length
000171 test_index 2.4 t2 collA value
000172 test_index 2.5 t2 collB length
000173 test_index 2.6 aux.t1 collA value
000174 test_index 2.7 aux.t1 collB length
000175
000176 # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a
000177 # collation-sequence name, then all indices in all attached databases
000178 # that use the named collation sequences are recreated.
000179 #
000180 set_collations length value
000181 do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
000182 test_index 3.2 t1 collA length
000183 test_index 3.3 t1 collB length
000184 test_index 3.4 t2 collA length
000185 test_index 3.5 t2 collB length
000186 test_index 3.6 aux.t1 collA length
000187 test_index 3.7 aux.t1 collB length
000188 do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
000189 test_index 3.9 t1 collA length
000190 test_index 3.10 t1 collB value
000191 test_index 3.11 t2 collA length
000192 test_index 3.12 t2 collB value
000193 test_index 3.13 aux.t1 collA length
000194 test_index 3.14 aux.t1 collB value
000195
000196 # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX
000197 # identifies a specific database table, then all indices attached to the
000198 # database table are rebuilt.
000199 #
000200 set_collations value length
000201 do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
000202 test_index 4.2 t1 collA value
000203 test_index 4.3 t1 collB length
000204 test_index 4.4 t2 collA length
000205 test_index 4.5 t2 collB value
000206 test_index 4.6 aux.t1 collA length
000207 test_index 4.7 aux.t1 collB value
000208 do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
000209 test_index 4.9 t1 collA value
000210 test_index 4.10 t1 collB length
000211 test_index 4.11 t2 collA length
000212 test_index 4.12 t2 collB value
000213 test_index 4.13 aux.t1 collA value
000214 test_index 4.14 aux.t1 collB length
000215 do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
000216 test_index 4.16 t1 collA value
000217 test_index 4.17 t1 collB length
000218 test_index 4.18 t2 collA value
000219 test_index 4.19 t2 collB length
000220 test_index 4.20 aux.t1 collA value
000221 test_index 4.21 aux.t1 collB length
000222
000223 # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,
000224 # then just that index is recreated.
000225 #
000226 set_collations length value
000227 do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
000228 test_index 5.2 t1 collA length
000229 test_index 5.3 t1 collB length
000230 test_index 5.4 t2 collA value
000231 test_index 5.5 t2 collB length
000232 test_index 5.6 aux.t1 collA value
000233 test_index 5.7 aux.t1 collB length
000234 do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
000235 test_index 5.9 t1 collA length
000236 test_index 5.10 t1 collB length
000237 test_index 5.11 t2 collA value
000238 test_index 5.12 t2 collB value
000239 test_index 5.13 aux.t1 collA value
000240 test_index 5.14 aux.t1 collB length
000241 do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
000242 test_index 5.16 t1 collA length
000243 test_index 5.17 t1 collB length
000244 test_index 5.18 t2 collA value
000245 test_index 5.19 t2 collB value
000246 test_index 5.20 aux.t1 collA value
000247 test_index 5.21 aux.t1 collB value
000248 do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
000249 test_index 5.23 t1 collA length
000250 test_index 5.24 t1 collB value
000251 test_index 5.25 t2 collA value
000252 test_index 5.26 t2 collB value
000253 test_index 5.27 aux.t1 collA value
000254 test_index 5.28 aux.t1 collB value
000255 do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
000256 test_index 5.30 t1 collA length
000257 test_index 5.31 t1 collB value
000258 test_index 5.32 t2 collA length
000259 test_index 5.33 t2 collB value
000260 test_index 5.34 aux.t1 collA value
000261 test_index 5.35 aux.t1 collB value
000262 do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
000263 test_index 5.37 t1 collA length
000264 test_index 5.38 t1 collB value
000265 test_index 5.39 t2 collA length
000266 test_index 5.40 t2 collB value
000267 test_index 5.41 aux.t1 collA length
000268 test_index 5.42 aux.t1 collB value
000269
000270 # EVIDENCE-OF: R-35892-30289 For a command of the form "REINDEX name", a
000271 # match against collation-name takes precedence over a match against
000272 # index-name or table-name.
000273 #
000274 set_collations value length
000275 do_execsql_test e_reindex-2.6.0 {
000276 CREATE TABLE collA(x);
000277 CREATE INDEX icolla_a ON collA(x COLLATE collA);
000278 CREATE INDEX icolla_b ON collA(x COLLATE collB);
000279
000280 INSERT INTO collA SELECT x FROM t1;
000281 } {}
000282
000283 test_index 6.1 collA collA value
000284 test_index 6.2 collA collB length
000285
000286 set_collations length value
000287 do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
000288 test_index 6.4 collA collA length
000289 test_index 6.5 collA collB length
000290 do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
000291 test_index 6.4 collA collA length
000292 test_index 6.5 collA collB value
000293
000294 set_collations value length
000295 do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
000296 test_index 6.7 collA collA value
000297 test_index 6.8 collA collB length
000298
000299 finish_test