| 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 |
{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 |
|
| 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 |