gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/e_reindex.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 proc do_reindex_tests {args} { 20 uplevel do_select_tests $args 21 } 22 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 } {} 28 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 } 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 # 47 sqlite3_db_config db DEFENSIVE 0 48 do_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 59 db close 60 sqlite3 db test.db 61 sqlite3_db_config db DEFENSIVE 0 62 do_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 71 db close 72 sqlite3 db test.db 73 do_execsql_test e_reindex-1.3 { 74 PRAGMA integrity_check; 75 } [list \ 76 {row 3 missing from index i2} \ 77 {row 3 missing from index i1} \ 78 {row 4 missing from index i2} \ 79 {row 4 missing from index i1} \ 80 {wrong # of entries in index i2} \ 81 {wrong # of entries in index i1} 82 ] 83 84 do_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 # 94 db close 95 forcedelete test.db2 96 forcedelete test.db 97 sqlite3 db test.db 98 99 proc 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 } 104 array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8} 105 proc sort_by_value {lhs rhs} { 106 global V 107 set res [expr {$V($lhs) - $V($rhs)}] 108 if {$res!=0} {return $res} 109 return [string compare $lhs $rhs] 110 } 111 112 db collate collA sort_by_length 113 db collate collB sort_by_value 114 115 set BY(length) {one six two five four eight seven three} 116 set BY(value) {one two three four five six seven eight} 117 118 do_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 145 proc 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 150 proc set_collations {a b} { 151 db collate collA "sort_by_$a" 152 db collate collB "sort_by_$b" 153 } 154 155 test_index 1.1 t1 collA length 156 test_index 1.2 t1 collB value 157 test_index 1.3 t2 collA length 158 test_index 1.4 t2 collB value 159 test_index 1.5 aux.t1 collA length 160 test_index 1.6 aux.t1 collB value 161 162 163 # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a 164 # collation-sequence or database object identifier, then all indices in 165 # all attached databases are rebuilt. 166 # 167 set_collations value length 168 do_execsql_test e_reindex-2.2.1 "REINDEX" {} 169 test_index 2.2 t1 collA value 170 test_index 2.3 t1 collB length 171 test_index 2.4 t2 collA value 172 test_index 2.5 t2 collB length 173 test_index 2.6 aux.t1 collA value 174 test_index 2.7 aux.t1 collB length 175 176 # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a 177 # collation-sequence name, then all indices in all attached databases 178 # that use the named collation sequences are recreated. 179 # 180 set_collations length value 181 do_execsql_test e_reindex-2.3.1 "REINDEX collA" {} 182 test_index 3.2 t1 collA length 183 test_index 3.3 t1 collB length 184 test_index 3.4 t2 collA length 185 test_index 3.5 t2 collB length 186 test_index 3.6 aux.t1 collA length 187 test_index 3.7 aux.t1 collB length 188 do_execsql_test e_reindex-2.3.8 "REINDEX collB" {} 189 test_index 3.9 t1 collA length 190 test_index 3.10 t1 collB value 191 test_index 3.11 t2 collA length 192 test_index 3.12 t2 collB value 193 test_index 3.13 aux.t1 collA length 194 test_index 3.14 aux.t1 collB value 195 196 # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX 197 # identifies a specific database table, then all indices attached to the 198 # database table are rebuilt. 199 # 200 set_collations value length 201 do_execsql_test e_reindex-2.4.1 "REINDEX t1" {} 202 test_index 4.2 t1 collA value 203 test_index 4.3 t1 collB length 204 test_index 4.4 t2 collA length 205 test_index 4.5 t2 collB value 206 test_index 4.6 aux.t1 collA length 207 test_index 4.7 aux.t1 collB value 208 do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {} 209 test_index 4.9 t1 collA value 210 test_index 4.10 t1 collB length 211 test_index 4.11 t2 collA length 212 test_index 4.12 t2 collB value 213 test_index 4.13 aux.t1 collA value 214 test_index 4.14 aux.t1 collB length 215 do_execsql_test e_reindex-2.4.15 "REINDEX t2" {} 216 test_index 4.16 t1 collA value 217 test_index 4.17 t1 collB length 218 test_index 4.18 t2 collA value 219 test_index 4.19 t2 collB length 220 test_index 4.20 aux.t1 collA value 221 test_index 4.21 aux.t1 collB length 222 223 # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index, 224 # then just that index is recreated. 225 # 226 set_collations length value 227 do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {} 228 test_index 5.2 t1 collA length 229 test_index 5.3 t1 collB length 230 test_index 5.4 t2 collA value 231 test_index 5.5 t2 collB length 232 test_index 5.6 aux.t1 collA value 233 test_index 5.7 aux.t1 collB length 234 do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {} 235 test_index 5.9 t1 collA length 236 test_index 5.10 t1 collB length 237 test_index 5.11 t2 collA value 238 test_index 5.12 t2 collB value 239 test_index 5.13 aux.t1 collA value 240 test_index 5.14 aux.t1 collB length 241 do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {} 242 test_index 5.16 t1 collA length 243 test_index 5.17 t1 collB length 244 test_index 5.18 t2 collA value 245 test_index 5.19 t2 collB value 246 test_index 5.20 aux.t1 collA value 247 test_index 5.21 aux.t1 collB value 248 do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {} 249 test_index 5.23 t1 collA length 250 test_index 5.24 t1 collB value 251 test_index 5.25 t2 collA value 252 test_index 5.26 t2 collB value 253 test_index 5.27 aux.t1 collA value 254 test_index 5.28 aux.t1 collB value 255 do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {} 256 test_index 5.30 t1 collA length 257 test_index 5.31 t1 collB value 258 test_index 5.32 t2 collA length 259 test_index 5.33 t2 collB value 260 test_index 5.34 aux.t1 collA value 261 test_index 5.35 aux.t1 collB value 262 do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {} 263 test_index 5.37 t1 collA length 264 test_index 5.38 t1 collB value 265 test_index 5.39 t2 collA length 266 test_index 5.40 t2 collB value 267 test_index 5.41 aux.t1 collA length 268 test_index 5.42 aux.t1 collB value 269 270 # EVIDENCE-OF: R-35892-30289 For a command of the form "REINDEX name", a 271 # match against collation-name takes precedence over a match against 272 # index-name or table-name. 273 # 274 set_collations value length 275 do_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 283 test_index 6.1 collA collA value 284 test_index 6.2 collA collB length 285 286 set_collations length value 287 do_execsql_test e_reindex-2.6.3 "REINDEX collA" {} 288 test_index 6.4 collA collA length 289 test_index 6.5 collA collB length 290 do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {} 291 test_index 6.4 collA collA length 292 test_index 6.5 collA collB value 293 294 set_collations value length 295 do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {} 296 test_index 6.7 collA collA value 297 test_index 6.8 collA collB length 298 299 finish_test