Annotation of embedaddon/sqlite3/test/e_reindex.test, revision 1.1
1.1 ! misho 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: # EVIDENCE-OF: R-51477-38549 -- 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: do_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:
! 55: db close
! 56: sqlite3 db test.db
! 57: do_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:
! 65: db close
! 66: sqlite3 db test.db
! 67: do_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:
! 78: do_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: #
! 88: db close
! 89: forcedelete test.db2
! 90: forcedelete test.db
! 91: sqlite3 db test.db
! 92:
! 93: proc 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: }
! 98: array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
! 99: proc 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:
! 106: db collate collA sort_by_length
! 107: db collate collB sort_by_value
! 108:
! 109: set BY(length) {one six two five four eight seven three}
! 110: set BY(value) {one two three four five six seven eight}
! 111:
! 112: do_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:
! 139: proc 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:
! 144: proc set_collations {a b} {
! 145: db collate collA "sort_by_$a"
! 146: db collate collB "sort_by_$b"
! 147: }
! 148:
! 149: test_index 1.1 t1 collA length
! 150: test_index 1.2 t1 collB value
! 151: test_index 1.3 t2 collA length
! 152: test_index 1.4 t2 collB value
! 153: test_index 1.5 aux.t1 collA length
! 154: test_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: #
! 161: set_collations value length
! 162: do_execsql_test e_reindex-2.2.1 "REINDEX" {}
! 163: test_index 2.2 t1 collA value
! 164: test_index 2.3 t1 collB length
! 165: test_index 2.4 t2 collA value
! 166: test_index 2.5 t2 collB length
! 167: test_index 2.6 aux.t1 collA value
! 168: test_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: #
! 174: set_collations length value
! 175: do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
! 176: test_index 3.2 t1 collA length
! 177: test_index 3.3 t1 collB length
! 178: test_index 3.4 t2 collA length
! 179: test_index 3.5 t2 collB length
! 180: test_index 3.6 aux.t1 collA length
! 181: test_index 3.7 aux.t1 collB length
! 182: do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
! 183: test_index 3.9 t1 collA length
! 184: test_index 3.10 t1 collB value
! 185: test_index 3.11 t2 collA length
! 186: test_index 3.12 t2 collB value
! 187: test_index 3.13 aux.t1 collA length
! 188: test_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: #
! 194: set_collations value length
! 195: do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
! 196: test_index 4.2 t1 collA value
! 197: test_index 4.3 t1 collB length
! 198: test_index 4.4 t2 collA length
! 199: test_index 4.5 t2 collB value
! 200: test_index 4.6 aux.t1 collA length
! 201: test_index 4.7 aux.t1 collB value
! 202: do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
! 203: test_index 4.9 t1 collA value
! 204: test_index 4.10 t1 collB length
! 205: test_index 4.11 t2 collA length
! 206: test_index 4.12 t2 collB value
! 207: test_index 4.13 aux.t1 collA value
! 208: test_index 4.14 aux.t1 collB length
! 209: do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
! 210: test_index 4.16 t1 collA value
! 211: test_index 4.17 t1 collB length
! 212: test_index 4.18 t2 collA value
! 213: test_index 4.19 t2 collB length
! 214: test_index 4.20 aux.t1 collA value
! 215: test_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: #
! 220: set_collations length value
! 221: do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
! 222: test_index 5.2 t1 collA length
! 223: test_index 5.3 t1 collB length
! 224: test_index 5.4 t2 collA value
! 225: test_index 5.5 t2 collB length
! 226: test_index 5.6 aux.t1 collA value
! 227: test_index 5.7 aux.t1 collB length
! 228: do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
! 229: test_index 5.9 t1 collA length
! 230: test_index 5.10 t1 collB length
! 231: test_index 5.11 t2 collA value
! 232: test_index 5.12 t2 collB value
! 233: test_index 5.13 aux.t1 collA value
! 234: test_index 5.14 aux.t1 collB length
! 235: do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
! 236: test_index 5.16 t1 collA length
! 237: test_index 5.17 t1 collB length
! 238: test_index 5.18 t2 collA value
! 239: test_index 5.19 t2 collB value
! 240: test_index 5.20 aux.t1 collA value
! 241: test_index 5.21 aux.t1 collB value
! 242: do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
! 243: test_index 5.23 t1 collA length
! 244: test_index 5.24 t1 collB value
! 245: test_index 5.25 t2 collA value
! 246: test_index 5.26 t2 collB value
! 247: test_index 5.27 aux.t1 collA value
! 248: test_index 5.28 aux.t1 collB value
! 249: do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
! 250: test_index 5.30 t1 collA length
! 251: test_index 5.31 t1 collB value
! 252: test_index 5.32 t2 collA length
! 253: test_index 5.33 t2 collB value
! 254: test_index 5.34 aux.t1 collA value
! 255: test_index 5.35 aux.t1 collB value
! 256: do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
! 257: test_index 5.37 t1 collA length
! 258: test_index 5.38 t1 collB value
! 259: test_index 5.39 t2 collA length
! 260: test_index 5.40 t2 collB value
! 261: test_index 5.41 aux.t1 collA length
! 262: test_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: #
! 269: set_collations value length
! 270: do_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:
! 278: test_index 6.1 collA collA value
! 279: test_index 6.2 collA collB length
! 280:
! 281: set_collations length value
! 282: do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
! 283: test_index 6.4 collA collA length
! 284: test_index 6.5 collA collB length
! 285: do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
! 286: test_index 6.4 collA collA length
! 287: test_index 6.5 collA collB value
! 288:
! 289: set_collations value length
! 290: do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
! 291: test_index 6.7 collA collA value
! 292: test_index 6.8 collA collB length
! 293:
! 294: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_reindex.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test