000001  # 2010 September 24
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_reindex.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  proc do_reindex_tests {args} {
000020    uplevel do_select_tests $args
000021  }
000022  
000023  do_execsql_test e_reindex-0.0 {
000024    CREATE TABLE t1(a, b);
000025    CREATE INDEX i1 ON t1(a, b);
000026    CREATE INDEX i2 ON t1(b, a);
000027  } {}
000028  
000029  #  -- syntax diagram reindex-stmt
000030  #
000031  do_reindex_tests e_reindex-0.1 {
000032    1   "REINDEX"           {}
000033    2   "REINDEX nocase"    {}
000034    3   "REINDEX binary"    {}
000035    4   "REINDEX t1"        {}
000036    5   "REINDEX main.t1"   {}
000037    6   "REINDEX i1"        {}
000038    7   "REINDEX main.i1"   {}
000039  }
000040  
000041  # EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
000042  # recreate indices from scratch.
000043  #
000044  #    Test this by corrupting some database indexes, running REINDEX, and
000045  #    observing that the corruption is gone.
000046  #
000047  sqlite3_db_config db DEFENSIVE 0
000048  do_execsql_test e_reindex-1.1 {
000049    INSERT INTO t1 VALUES(1, 2);
000050    INSERT INTO t1 VALUES(3, 4);
000051    INSERT INTO t1 VALUES(5, 6);
000052  
000053    CREATE TABLE saved(a,b,c,d,e);
000054    INSERT INTO saved SELECT * FROM sqlite_master WHERE type = 'index';
000055    PRAGMA writable_schema = 1;
000056    DELETE FROM sqlite_master WHERE type = 'index';
000057  } {}
000058  
000059  db close
000060  sqlite3 db test.db
000061  sqlite3_db_config db DEFENSIVE 0
000062  do_execsql_test e_reindex-1.2 {
000063    DELETE FROM t1 WHERE a = 3;
000064    INSERT INTO t1 VALUES(7, 8);
000065    INSERT INTO t1 VALUES(9, 10);
000066    PRAGMA writable_schema = 1;
000067    INSERT INTO sqlite_master SELECT * FROM saved;
000068    DROP TABLE saved;
000069  } {}
000070  
000071  db close
000072  sqlite3 db test.db
000073  do_execsql_test e_reindex-1.3 {
000074    PRAGMA integrity_check;
000075  } [list \
000076    {row 3 missing from index i2} \
000077    {row 3 missing from index i1} \
000078    {row 4 missing from index i2} \
000079    {row 4 missing from index i1} \
000080    {wrong # of entries in index i2} \
000081    {wrong # of entries in index i1}
000082  ]
000083  
000084  do_execsql_test e_reindex-1.4 {
000085    REINDEX;
000086    PRAGMA integrity_check;
000087  } {ok}
000088  
000089  #-------------------------------------------------------------------------
000090  # The remaining tests in this file focus on testing that the REINDEX 
000091  # command reindexes the correct subset of the indexes in the database.
000092  # They all use the following dataset.
000093  #
000094  db close
000095  forcedelete test.db2
000096  forcedelete test.db
000097  sqlite3 db test.db
000098  
000099  proc sort_by_length {lhs rhs} {
000100    set res [expr {[string length $lhs] - [string length $rhs]}]
000101    if {$res!=0} {return $res}
000102    return [string compare $lhs $rhs]
000103  }
000104  array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
000105  proc sort_by_value {lhs rhs} {
000106    global V
000107    set res [expr {$V($lhs) - $V($rhs)}]
000108    if {$res!=0} {return $res}
000109    return [string compare $lhs $rhs]
000110  }
000111  
000112  db collate collA sort_by_length
000113  db collate collB sort_by_value
000114  
000115  set BY(length) {one six two five four eight seven three}
000116  set BY(value)  {one two three four five six seven eight}
000117  
000118  do_execsql_test e_reindex-2.0 {
000119    ATTACH 'test.db2' AS aux;
000120  
000121    CREATE TABLE t1(x);
000122    CREATE INDEX i1_a ON t1(x COLLATE collA);
000123    CREATE INDEX i1_b ON t1(x COLLATE collB);
000124    INSERT INTO t1 VALUES('one');
000125    INSERT INTO t1 VALUES('two');
000126    INSERT INTO t1 VALUES('three');
000127    INSERT INTO t1 VALUES('four');
000128    INSERT INTO t1 VALUES('five');
000129    INSERT INTO t1 VALUES('six');
000130    INSERT INTO t1 VALUES('seven');
000131    INSERT INTO t1 VALUES('eight');
000132  
000133    CREATE TABLE t2(x);
000134    CREATE INDEX i2_a ON t2(x COLLATE collA);
000135    CREATE INDEX i2_b ON t2(x COLLATE collB);
000136    INSERT INTO t2 SELECT x FROM t1;
000137  
000138    CREATE TABLE aux.t1(x);
000139    CREATE INDEX aux.i1_a ON t1(x COLLATE collA);
000140    CREATE INDEX aux.i1_b ON t1(x COLLATE collB);
000141    INSERT INTO aux.t1 SELECT x FROM main.t1;
000142  
000143  } {}
000144  
000145  proc test_index {tn tbl collation expected} {
000146    set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"
000147    uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]
000148  }
000149  
000150  proc set_collations {a b} {
000151    db collate collA "sort_by_$a"
000152    db collate collB "sort_by_$b"
000153  }
000154  
000155  test_index 1.1   t1     collA   length
000156  test_index 1.2   t1     collB   value
000157  test_index 1.3   t2     collA   length
000158  test_index 1.4   t2     collB   value
000159  test_index 1.5   aux.t1 collA   length
000160  test_index 1.6   aux.t1 collB   value
000161  
000162  
000163  # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a
000164  # collation-sequence or database object identifier, then all indices in
000165  # all attached databases are rebuilt.
000166  #
000167  set_collations value length
000168  do_execsql_test e_reindex-2.2.1 "REINDEX" {}
000169  test_index 2.2   t1     collA   value
000170  test_index 2.3   t1     collB   length
000171  test_index 2.4   t2     collA   value
000172  test_index 2.5   t2     collB   length
000173  test_index 2.6   aux.t1 collA   value
000174  test_index 2.7   aux.t1 collB   length
000175  
000176  # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a
000177  # collation-sequence name, then all indices in all attached databases
000178  # that use the named collation sequences are recreated.
000179  #
000180  set_collations length value
000181  do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
000182  test_index 3.2   t1     collA   length
000183  test_index 3.3   t1     collB   length
000184  test_index 3.4   t2     collA   length
000185  test_index 3.5   t2     collB   length
000186  test_index 3.6   aux.t1 collA   length
000187  test_index 3.7   aux.t1 collB   length
000188  do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
000189  test_index 3.9   t1     collA   length
000190  test_index 3.10  t1     collB   value
000191  test_index 3.11  t2     collA   length
000192  test_index 3.12  t2     collB   value
000193  test_index 3.13  aux.t1 collA   length
000194  test_index 3.14  aux.t1 collB   value
000195  
000196  # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX
000197  # identifies a specific database table, then all indices attached to the
000198  # database table are rebuilt.
000199  #
000200  set_collations value length
000201  do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
000202  test_index 4.2   t1     collA   value
000203  test_index 4.3   t1     collB   length
000204  test_index 4.4   t2     collA   length
000205  test_index 4.5   t2     collB   value
000206  test_index 4.6   aux.t1 collA   length
000207  test_index 4.7   aux.t1 collB   value
000208  do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
000209  test_index 4.9   t1     collA   value
000210  test_index 4.10  t1     collB   length
000211  test_index 4.11  t2     collA   length
000212  test_index 4.12  t2     collB   value
000213  test_index 4.13  aux.t1 collA   value
000214  test_index 4.14  aux.t1 collB   length
000215  do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
000216  test_index 4.16  t1     collA   value
000217  test_index 4.17  t1     collB   length
000218  test_index 4.18  t2     collA   value
000219  test_index 4.19  t2     collB   length
000220  test_index 4.20  aux.t1 collA   value
000221  test_index 4.21  aux.t1 collB   length
000222  
000223  # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,
000224  # then just that index is recreated.
000225  #
000226  set_collations length value
000227  do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
000228  test_index 5.2   t1     collA   length
000229  test_index 5.3   t1     collB   length
000230  test_index 5.4   t2     collA   value
000231  test_index 5.5   t2     collB   length
000232  test_index 5.6   aux.t1 collA   value
000233  test_index 5.7   aux.t1 collB   length
000234  do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
000235  test_index 5.9   t1     collA   length
000236  test_index 5.10  t1     collB   length
000237  test_index 5.11  t2     collA   value
000238  test_index 5.12  t2     collB   value
000239  test_index 5.13  aux.t1 collA   value
000240  test_index 5.14  aux.t1 collB   length
000241  do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
000242  test_index 5.16  t1     collA   length
000243  test_index 5.17  t1     collB   length
000244  test_index 5.18  t2     collA   value
000245  test_index 5.19  t2     collB   value
000246  test_index 5.20  aux.t1 collA   value
000247  test_index 5.21  aux.t1 collB   value
000248  do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
000249  test_index 5.23  t1     collA   length
000250  test_index 5.24  t1     collB   value
000251  test_index 5.25  t2     collA   value
000252  test_index 5.26  t2     collB   value
000253  test_index 5.27  aux.t1 collA   value
000254  test_index 5.28  aux.t1 collB   value
000255  do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
000256  test_index 5.30  t1     collA   length
000257  test_index 5.31  t1     collB   value
000258  test_index 5.32  t2     collA   length
000259  test_index 5.33  t2     collB   value
000260  test_index 5.34  aux.t1 collA   value
000261  test_index 5.35  aux.t1 collB   value
000262  do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
000263  test_index 5.37  t1     collA   length
000264  test_index 5.38  t1     collB   value
000265  test_index 5.39  t2     collA   length
000266  test_index 5.40  t2     collB   value
000267  test_index 5.41  aux.t1 collA   length
000268  test_index 5.42  aux.t1 collB   value
000269  
000270  # EVIDENCE-OF: R-35892-30289 For a command of the form "REINDEX name", a
000271  # match against collation-name takes precedence over a match against
000272  # index-name or table-name.
000273  #
000274  set_collations value length
000275  do_execsql_test e_reindex-2.6.0 {
000276    CREATE TABLE collA(x);
000277    CREATE INDEX icolla_a ON collA(x COLLATE collA);
000278    CREATE INDEX icolla_b ON collA(x COLLATE collB);
000279  
000280    INSERT INTO collA SELECT x FROM t1;
000281  } {}
000282  
000283  test_index 6.1   collA  collA   value
000284  test_index 6.2   collA  collB   length
000285  
000286  set_collations length value
000287  do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
000288  test_index 6.4   collA  collA   length
000289  test_index 6.5   collA  collB   length
000290  do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
000291  test_index 6.4   collA  collA   length
000292  test_index 6.5   collA  collB   value
000293  
000294  set_collations value length
000295  do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
000296  test_index 6.7   collA  collA   value
000297  test_index 6.8   collA  collB   length
000298  
000299  finish_test