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# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19proc do_reindex_tests {args} { 20 uplevel do_select_tests $args 21} 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# EVIDENCE-OF: R-57021-15304 -- syntax diagram reindex-stmt 30# 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 4 "REINDEX i1" {} 38 5 "REINDEX main.i1" {} 39} 40 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# 47do_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} {} 54 55db close 56sqlite3 db test.db 57do_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} {} 64 65db close 66sqlite3 db test.db 67do_execsql_test e_reindex-1.3 { 68 PRAGMA integrity_check; 69} [list \ 70 {rowid 4 missing from index i2} \ 71 {rowid 4 missing from index i1} \ 72 {rowid 5 missing from index i2} \ 73 {rowid 5 missing from index i1} \ 74 {wrong # of entries in index i2} \ 75 {wrong # of entries in index i1} 76] 77 78do_execsql_test e_reindex-1.4 { 79 REINDEX; 80 PRAGMA integrity_check; 81} {ok} 82 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# 88db close 89forcedelete test.db2 90forcedelete test.db 91sqlite3 db test.db 92 93proc 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} 98array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8} 99proc 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} 105 106db collate collA sort_by_length 107db collate collB sort_by_value 108 109set BY(length) {one six two five four eight seven three} 110set BY(value) {one two three four five six seven eight} 111 112do_execsql_test e_reindex-2.0 { 113 ATTACH 'test.db2' AS aux; 114 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'); 126 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; 131 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; 136 137} {} 138 139proc 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} 143 144proc set_collations {a b} { 145 db collate collA "sort_by_$a" 146 db collate collB "sort_by_$b" 147} 148 149test_index 1.1 t1 collA length 150test_index 1.2 t1 collB value 151test_index 1.3 t2 collA length 152test_index 1.4 t2 collB value 153test_index 1.5 aux.t1 collA length 154test_index 1.6 aux.t1 collB value 155 156 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# 161set_collations value length 162do_execsql_test e_reindex-2.2.1 "REINDEX" {} 163test_index 2.2 t1 collA value 164test_index 2.3 t1 collB length 165test_index 2.4 t2 collA value 166test_index 2.5 t2 collB length 167test_index 2.6 aux.t1 collA value 168test_index 2.7 aux.t1 collB length 169 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# 174set_collations length value 175do_execsql_test e_reindex-2.3.1 "REINDEX collA" {} 176test_index 3.2 t1 collA length 177test_index 3.3 t1 collB length 178test_index 3.4 t2 collA length 179test_index 3.5 t2 collB length 180test_index 3.6 aux.t1 collA length 181test_index 3.7 aux.t1 collB length 182do_execsql_test e_reindex-2.3.8 "REINDEX collB" {} 183test_index 3.9 t1 collA length 184test_index 3.10 t1 collB value 185test_index 3.11 t2 collA length 186test_index 3.12 t2 collB value 187test_index 3.13 aux.t1 collA length 188test_index 3.14 aux.t1 collB value 189 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# 194set_collations value length 195do_execsql_test e_reindex-2.4.1 "REINDEX t1" {} 196test_index 4.2 t1 collA value 197test_index 4.3 t1 collB length 198test_index 4.4 t2 collA length 199test_index 4.5 t2 collB value 200test_index 4.6 aux.t1 collA length 201test_index 4.7 aux.t1 collB value 202do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {} 203test_index 4.9 t1 collA value 204test_index 4.10 t1 collB length 205test_index 4.11 t2 collA length 206test_index 4.12 t2 collB value 207test_index 4.13 aux.t1 collA value 208test_index 4.14 aux.t1 collB length 209do_execsql_test e_reindex-2.4.15 "REINDEX t2" {} 210test_index 4.16 t1 collA value 211test_index 4.17 t1 collB length 212test_index 4.18 t2 collA value 213test_index 4.19 t2 collB length 214test_index 4.20 aux.t1 collA value 215test_index 4.21 aux.t1 collB length 216 217# EVIDENCE-OF: R-58823-28748 If it identifies a specific database index, 218# then just that index is recreated. 219# 220set_collations length value 221do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {} 222test_index 5.2 t1 collA length 223test_index 5.3 t1 collB length 224test_index 5.4 t2 collA value 225test_index 5.5 t2 collB length 226test_index 5.6 aux.t1 collA value 227test_index 5.7 aux.t1 collB length 228do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {} 229test_index 5.9 t1 collA length 230test_index 5.10 t1 collB length 231test_index 5.11 t2 collA value 232test_index 5.12 t2 collB value 233test_index 5.13 aux.t1 collA value 234test_index 5.14 aux.t1 collB length 235do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {} 236test_index 5.16 t1 collA length 237test_index 5.17 t1 collB length 238test_index 5.18 t2 collA value 239test_index 5.19 t2 collB value 240test_index 5.20 aux.t1 collA value 241test_index 5.21 aux.t1 collB value 242do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {} 243test_index 5.23 t1 collA length 244test_index 5.24 t1 collB value 245test_index 5.25 t2 collA value 246test_index 5.26 t2 collB value 247test_index 5.27 aux.t1 collA value 248test_index 5.28 aux.t1 collB value 249do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {} 250test_index 5.30 t1 collA length 251test_index 5.31 t1 collB value 252test_index 5.32 t2 collA length 253test_index 5.33 t2 collB value 254test_index 5.34 aux.t1 collA value 255test_index 5.35 aux.t1 collB value 256do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {} 257test_index 5.37 t1 collA length 258test_index 5.38 t1 collB value 259test_index 5.39 t2 collA length 260test_index 5.40 t2 collB value 261test_index 5.41 aux.t1 collA length 262test_index 5.42 aux.t1 collB value 263 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# 269set_collations value length 270do_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); 274 275 INSERT INTO collA SELECT x FROM t1; 276} {} 277 278test_index 6.1 collA collA value 279test_index 6.2 collA collB length 280 281set_collations length value 282do_execsql_test e_reindex-2.6.3 "REINDEX collA" {} 283test_index 6.4 collA collA length 284test_index 6.5 collA collB length 285do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {} 286test_index 6.4 collA collA length 287test_index 6.5 collA collB value 288 289set_collations value length 290do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {} 291test_index 6.7 collA collA value 292test_index 6.8 collA collB length 293 294finish_test 295