AryaWu/sqlite
0
1# 2010 September 242#3# The author disclaims copyright to this source code. In place of4# 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#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19proc do_reindex_tests {args} {20 uplevel do_select_tests $args21}22 23do_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} {}28 29# -- syntax diagram reindex-stmt30#31do_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}40 41# EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and42# recreate indices from scratch.43#44# Test this by corrupting some database indexes, running REINDEX, and45# observing that the corruption is gone.46#47sqlite3_db_config db DEFENSIVE 048do_execsql_test e_reindex-1.1 {49 INSERT INTO t1 VALUES(1, 2);50 INSERT INTO t1 VALUES(3, 4);51 INSERT INTO t1 VALUES(5, 6);52 53 CREATE TABLE saved(a,b,c,d,e);54 INSERT INTO saved SELECT * FROM sqlite_master WHERE type = 'index';55 PRAGMA writable_schema = 1;56 DELETE FROM sqlite_master WHERE type = 'index';57} {}58 59db close60sqlite3 db test.db61sqlite3_db_config db DEFENSIVE 062do_execsql_test e_reindex-1.2 {63 DELETE FROM t1 WHERE a = 3;64 INSERT INTO t1 VALUES(7, 8);65 INSERT INTO t1 VALUES(9, 10);66 PRAGMA writable_schema = 1;67 INSERT INTO sqlite_master SELECT * FROM saved;68 DROP TABLE saved;69} {}70 71db close72sqlite3 db test.db73do_execsql_test e_reindex-1.3 {74 PRAGMA integrity_check;75} [list \76 {wrong # of entries in index i2} \77 {wrong # of entries in index i1} \78 {row 3 missing from index i2} \79 {row 3 missing from index i1} \80 {row 4 missing from index i2} \81 {row 4 missing from index i1} 82]83 84do_execsql_test e_reindex-1.4 {85 REINDEX;86 PRAGMA integrity_check;87} {ok}88 89#-------------------------------------------------------------------------90# The remaining tests in this file focus on testing that the REINDEX 91# command reindexes the correct subset of the indexes in the database.92# They all use the following dataset.93#94db close95forcedelete test.db296forcedelete test.db97sqlite3 db test.db98 99proc sort_by_length {lhs rhs} {100 set res [expr {[string length $lhs] - [string length $rhs]}]101 if {$res!=0} {return $res}102 return [string compare $lhs $rhs]103}104array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}105proc sort_by_value {lhs rhs} {106 global V107 set res [expr {$V($lhs) - $V($rhs)}]108 if {$res!=0} {return $res}109 return [string compare $lhs $rhs]110}111 112db collate collA sort_by_length113db collate collB sort_by_value114 115set BY(length) {one six two five four eight seven three}116set BY(value) {one two three four five six seven eight}117 118do_execsql_test e_reindex-2.0 {119 ATTACH 'test.db2' AS aux;120 121 CREATE TABLE t1(x);122 CREATE INDEX i1_a ON t1(x COLLATE collA);123 CREATE INDEX i1_b ON t1(x COLLATE collB);124 INSERT INTO t1 VALUES('one');125 INSERT INTO t1 VALUES('two');126 INSERT INTO t1 VALUES('three');127 INSERT INTO t1 VALUES('four');128 INSERT INTO t1 VALUES('five');129 INSERT INTO t1 VALUES('six');130 INSERT INTO t1 VALUES('seven');131 INSERT INTO t1 VALUES('eight');132 133 CREATE TABLE t2(x);134 CREATE INDEX i2_a ON t2(x COLLATE collA);135 CREATE INDEX i2_b ON t2(x COLLATE collB);136 INSERT INTO t2 SELECT x FROM t1;137 138 CREATE TABLE aux.t1(x);139 CREATE INDEX aux.i1_a ON t1(x COLLATE collA);140 CREATE INDEX aux.i1_b ON t1(x COLLATE collB);141 INSERT INTO aux.t1 SELECT x FROM main.t1;142 143} {}144 145proc test_index {tn tbl collation expected} {146 set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"147 uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]148}149 150proc set_collations {a b} {151 db collate collA "sort_by_$a"152 db collate collB "sort_by_$b"153}154 155test_index 1.1 t1 collA length156test_index 1.2 t1 collB value157test_index 1.3 t2 collA length158test_index 1.4 t2 collB value159test_index 1.5 aux.t1 collA length160test_index 1.6 aux.t1 collB value161 162 163# EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a164# collation-sequence or database object identifier, then all indices in165# all attached databases are rebuilt.166#167set_collations value length168do_execsql_test e_reindex-2.2.1 "REINDEX" {}169test_index 2.2 t1 collA value170test_index 2.3 t1 collB length171test_index 2.4 t2 collA value172test_index 2.5 t2 collB length173test_index 2.6 aux.t1 collA value174test_index 2.7 aux.t1 collB length175 176# EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a177# collation-sequence name, then all indices in all attached databases178# that use the named collation sequences are recreated.179#180set_collations length value181do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}182test_index 3.2 t1 collA length183test_index 3.3 t1 collB length184test_index 3.4 t2 collA length185test_index 3.5 t2 collB length186test_index 3.6 aux.t1 collA length187test_index 3.7 aux.t1 collB length188do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}189test_index 3.9 t1 collA length190test_index 3.10 t1 collB value191test_index 3.11 t2 collA length192test_index 3.12 t2 collB value193test_index 3.13 aux.t1 collA length194test_index 3.14 aux.t1 collB value195 196# EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX197# identifies a specific database table, then all indices attached to the198# database table are rebuilt.199#200set_collations value length201do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}202test_index 4.2 t1 collA value203test_index 4.3 t1 collB length204test_index 4.4 t2 collA length205test_index 4.5 t2 collB value206test_index 4.6 aux.t1 collA length207test_index 4.7 aux.t1 collB value208do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}209test_index 4.9 t1 collA value210test_index 4.10 t1 collB length211test_index 4.11 t2 collA length212test_index 4.12 t2 collB value213test_index 4.13 aux.t1 collA value214test_index 4.14 aux.t1 collB length215do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}216test_index 4.16 t1 collA value217test_index 4.17 t1 collB length218test_index 4.18 t2 collA value219test_index 4.19 t2 collB length220test_index 4.20 aux.t1 collA value221test_index 4.21 aux.t1 collB length222 223# EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,224# then just that index is recreated.225#226set_collations length value227do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}228test_index 5.2 t1 collA length229test_index 5.3 t1 collB length230test_index 5.4 t2 collA value231test_index 5.5 t2 collB length232test_index 5.6 aux.t1 collA value233test_index 5.7 aux.t1 collB length234do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}235test_index 5.9 t1 collA length236test_index 5.10 t1 collB length237test_index 5.11 t2 collA value238test_index 5.12 t2 collB value239test_index 5.13 aux.t1 collA value240test_index 5.14 aux.t1 collB length241do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}242test_index 5.16 t1 collA length243test_index 5.17 t1 collB length244test_index 5.18 t2 collA value245test_index 5.19 t2 collB value246test_index 5.20 aux.t1 collA value247test_index 5.21 aux.t1 collB value248do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}249test_index 5.23 t1 collA length250test_index 5.24 t1 collB value251test_index 5.25 t2 collA value252test_index 5.26 t2 collB value253test_index 5.27 aux.t1 collA value254test_index 5.28 aux.t1 collB value255do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}256test_index 5.30 t1 collA length257test_index 5.31 t1 collB value258test_index 5.32 t2 collA length259test_index 5.33 t2 collB value260test_index 5.34 aux.t1 collA value261test_index 5.35 aux.t1 collB value262do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}263test_index 5.37 t1 collA length264test_index 5.38 t1 collB value265test_index 5.39 t2 collA length266test_index 5.40 t2 collB value267test_index 5.41 aux.t1 collA length268test_index 5.42 aux.t1 collB value269 270# EVIDENCE-OF: R-35892-30289 For a command of the form "REINDEX name", a271# match against collation-name takes precedence over a match against272# index-name or table-name.273#274set_collations value length275do_execsql_test e_reindex-2.6.0 {276 CREATE TABLE collA(x);277 CREATE INDEX icolla_a ON collA(x COLLATE collA);278 CREATE INDEX icolla_b ON collA(x COLLATE collB);279 280 INSERT INTO collA SELECT x FROM t1;281} {}282 283test_index 6.1 collA collA value284test_index 6.2 collA collB length285 286set_collations length value287do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}288test_index 6.4 collA collA length289test_index 6.5 collA collB length290do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}291test_index 6.4 collA collA length292test_index 6.5 collA collB value293 294set_collations value length295do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}296test_index 6.7 collA collA value297test_index 6.8 collA collB length298 299finish_test300