| # 2010 September 24 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # |
| # This file implements tests to verify that the "testable statements" in |
| # the lang_reindex.html document are correct. |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| proc do_reindex_tests {args} { |
| uplevel do_select_tests $args |
| } |
| |
| do_execsql_test e_reindex-0.0 { |
| CREATE TABLE t1(a, b); |
| CREATE INDEX i1 ON t1(a, b); |
| CREATE INDEX i2 ON t1(b, a); |
| } {} |
| |
| # EVIDENCE-OF: R-57021-15304 -- syntax diagram reindex-stmt |
| # |
| do_reindex_tests e_reindex-0.1 { |
| 1 "REINDEX" {} |
| 2 "REINDEX nocase" {} |
| 3 "REINDEX binary" {} |
| 4 "REINDEX t1" {} |
| 5 "REINDEX main.t1" {} |
| 4 "REINDEX i1" {} |
| 5 "REINDEX main.i1" {} |
| } |
| |
| # EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and |
| # recreate indices from scratch. |
| # |
| # Test this by corrupting some database indexes, running REINDEX, and |
| # observing that the corruption is gone. |
| # |
| do_execsql_test e_reindex-1.1 { |
| INSERT INTO t1 VALUES(1, 2); |
| INSERT INTO t1 VALUES(3, 4); |
| INSERT INTO t1 VALUES(5, 6); |
| PRAGMA writable_schema = 1; |
| UPDATE sqlite_master SET sql = '-- ' || sql WHERE type = 'index'; |
| } {} |
| |
| db close |
| sqlite3 db test.db |
| do_execsql_test e_reindex-1.2 { |
| DELETE FROM t1 WHERE a = 3; |
| INSERT INTO t1 VALUES(7, 8); |
| INSERT INTO t1 VALUES(9, 10); |
| PRAGMA writable_schema = 1; |
| UPDATE sqlite_master SET sql = substr(sql, 4) WHERE type = 'index'; |
| } {} |
| |
| db close |
| sqlite3 db test.db |
| do_execsql_test e_reindex-1.3 { |
| PRAGMA integrity_check; |
| } [list \ |
| {rowid 4 missing from index i2} \ |
| {rowid 4 missing from index i1} \ |
| {rowid 5 missing from index i2} \ |
| {rowid 5 missing from index i1} \ |
| {wrong # of entries in index i2} \ |
| {wrong # of entries in index i1} |
| ] |
| |
| do_execsql_test e_reindex-1.4 { |
| REINDEX; |
| PRAGMA integrity_check; |
| } {ok} |
| |
| #------------------------------------------------------------------------- |
| # The remaining tests in this file focus on testing that the REINDEX |
| # command reindexes the correct subset of the indexes in the database. |
| # They all use the following dataset. |
| # |
| db close |
| forcedelete test.db2 |
| forcedelete test.db |
| sqlite3 db test.db |
| |
| proc sort_by_length {lhs rhs} { |
| set res [expr {[string length $lhs] - [string length $rhs]}] |
| if {$res!=0} {return $res} |
| return [string compare $lhs $rhs] |
| } |
| array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8} |
| proc sort_by_value {lhs rhs} { |
| global V |
| set res [expr {$V($lhs) - $V(rhs)}] |
| if {$res!=0} {return $res} |
| return [string compare $lhs $rhs] |
| } |
| |
| db collate collA sort_by_length |
| db collate collB sort_by_value |
| |
| set BY(length) {one six two five four eight seven three} |
| set BY(value) {one two three four five six seven eight} |
| |
| do_execsql_test e_reindex-2.0 { |
| ATTACH 'test.db2' AS aux; |
| |
| CREATE TABLE t1(x); |
| CREATE INDEX i1_a ON t1(x COLLATE collA); |
| CREATE INDEX i1_b ON t1(x COLLATE collB); |
| INSERT INTO t1 VALUES('one'); |
| INSERT INTO t1 VALUES('two'); |
| INSERT INTO t1 VALUES('three'); |
| INSERT INTO t1 VALUES('four'); |
| INSERT INTO t1 VALUES('five'); |
| INSERT INTO t1 VALUES('six'); |
| INSERT INTO t1 VALUES('seven'); |
| INSERT INTO t1 VALUES('eight'); |
| |
| CREATE TABLE t2(x); |
| CREATE INDEX i2_a ON t2(x COLLATE collA); |
| CREATE INDEX i2_b ON t2(x COLLATE collB); |
| INSERT INTO t2 SELECT x FROM t1; |
| |
| CREATE TABLE aux.t1(x); |
| CREATE INDEX aux.i1_a ON t1(x COLLATE collA); |
| CREATE INDEX aux.i1_b ON t1(x COLLATE collB); |
| INSERT INTO aux.t1 SELECT x FROM main.t1; |
| |
| } {} |
| |
| proc test_index {tn tbl collation expected} { |
| set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation" |
| uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)] |
| } |
| |
| proc set_collations {a b} { |
| db collate collA "sort_by_$a" |
| db collate collB "sort_by_$b" |
| } |
| |
| test_index 1.1 t1 collA length |
| test_index 1.2 t1 collB value |
| test_index 1.3 t2 collA length |
| test_index 1.4 t2 collB value |
| test_index 1.5 aux.t1 collA length |
| test_index 1.6 aux.t1 collB value |
| |
| |
| # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a |
| # collation-sequence or database object identifier, then all indices in |
| # all attached databases are rebuilt. |
| # |
| set_collations value length |
| do_execsql_test e_reindex-2.2.1 "REINDEX" {} |
| test_index 2.2 t1 collA value |
| test_index 2.3 t1 collB length |
| test_index 2.4 t2 collA value |
| test_index 2.5 t2 collB length |
| test_index 2.6 aux.t1 collA value |
| test_index 2.7 aux.t1 collB length |
| |
| # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a |
| # collation-sequence name, then all indices in all attached databases |
| # that use the named collation sequences are recreated. |
| # |
| set_collations length value |
| do_execsql_test e_reindex-2.3.1 "REINDEX collA" {} |
| test_index 3.2 t1 collA length |
| test_index 3.3 t1 collB length |
| test_index 3.4 t2 collA length |
| test_index 3.5 t2 collB length |
| test_index 3.6 aux.t1 collA length |
| test_index 3.7 aux.t1 collB length |
| do_execsql_test e_reindex-2.3.8 "REINDEX collB" {} |
| test_index 3.9 t1 collA length |
| test_index 3.10 t1 collB value |
| test_index 3.11 t2 collA length |
| test_index 3.12 t2 collB value |
| test_index 3.13 aux.t1 collA length |
| test_index 3.14 aux.t1 collB value |
| |
| # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX |
| # identifies a specific database table, then all indices attached to the |
| # database table are rebuilt. |
| # |
| set_collations value length |
| do_execsql_test e_reindex-2.4.1 "REINDEX t1" {} |
| test_index 4.2 t1 collA value |
| test_index 4.3 t1 collB length |
| test_index 4.4 t2 collA length |
| test_index 4.5 t2 collB value |
| test_index 4.6 aux.t1 collA length |
| test_index 4.7 aux.t1 collB value |
| do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {} |
| test_index 4.9 t1 collA value |
| test_index 4.10 t1 collB length |
| test_index 4.11 t2 collA length |
| test_index 4.12 t2 collB value |
| test_index 4.13 aux.t1 collA value |
| test_index 4.14 aux.t1 collB length |
| do_execsql_test e_reindex-2.4.15 "REINDEX t2" {} |
| test_index 4.16 t1 collA value |
| test_index 4.17 t1 collB length |
| test_index 4.18 t2 collA value |
| test_index 4.19 t2 collB length |
| test_index 4.20 aux.t1 collA value |
| test_index 4.21 aux.t1 collB length |
| |
| # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index, |
| # then just that index is recreated. |
| # |
| set_collations length value |
| do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {} |
| test_index 5.2 t1 collA length |
| test_index 5.3 t1 collB length |
| test_index 5.4 t2 collA value |
| test_index 5.5 t2 collB length |
| test_index 5.6 aux.t1 collA value |
| test_index 5.7 aux.t1 collB length |
| do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {} |
| test_index 5.9 t1 collA length |
| test_index 5.10 t1 collB length |
| test_index 5.11 t2 collA value |
| test_index 5.12 t2 collB value |
| test_index 5.13 aux.t1 collA value |
| test_index 5.14 aux.t1 collB length |
| do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {} |
| test_index 5.16 t1 collA length |
| test_index 5.17 t1 collB length |
| test_index 5.18 t2 collA value |
| test_index 5.19 t2 collB value |
| test_index 5.20 aux.t1 collA value |
| test_index 5.21 aux.t1 collB value |
| do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {} |
| test_index 5.23 t1 collA length |
| test_index 5.24 t1 collB value |
| test_index 5.25 t2 collA value |
| test_index 5.26 t2 collB value |
| test_index 5.27 aux.t1 collA value |
| test_index 5.28 aux.t1 collB value |
| do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {} |
| test_index 5.30 t1 collA length |
| test_index 5.31 t1 collB value |
| test_index 5.32 t2 collA length |
| test_index 5.33 t2 collB value |
| test_index 5.34 aux.t1 collA value |
| test_index 5.35 aux.t1 collB value |
| do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {} |
| test_index 5.37 t1 collA length |
| test_index 5.38 t1 collB value |
| test_index 5.39 t2 collA length |
| test_index 5.40 t2 collB value |
| test_index 5.41 aux.t1 collA length |
| test_index 5.42 aux.t1 collB value |
| |
| # EVIDENCE-OF: R-15639-02023 If no database-name is specified and there |
| # exists both a table or index and a collation sequence of the specified |
| # name, SQLite interprets this as a request to rebuild the indices that |
| # use the named collation sequence. |
| # |
| set_collations value length |
| do_execsql_test e_reindex-2.6.0 { |
| CREATE TABLE collA(x); |
| CREATE INDEX icolla_a ON collA(x COLLATE collA); |
| CREATE INDEX icolla_b ON collA(x COLLATE collB); |
| |
| INSERT INTO collA SELECT x FROM t1; |
| } {} |
| |
| test_index 6.1 collA collA value |
| test_index 6.2 collA collB length |
| |
| set_collations length value |
| do_execsql_test e_reindex-2.6.3 "REINDEX collA" {} |
| test_index 6.4 collA collA length |
| test_index 6.5 collA collB length |
| do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {} |
| test_index 6.4 collA collA length |
| test_index 6.5 collA collB value |
| |
| set_collations value length |
| do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {} |
| test_index 6.7 collA collA value |
| test_index 6.8 collA collB length |
| |
| finish_test |
