AryaWu/sqlite

1# 2004 November 52#3# The author disclaims copyright to this source code.  In place of4# 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# This file implements regression tests for SQLite library.12# This file implements tests for the REINDEX command.13#14# $Id: reindex.test,v 1.4 2008/07/12 14:52:20 drh Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix reindex19 20# There is nothing to test if REINDEX is disable for this build.21#22ifcapable {!reindex} {23  finish_test24  return25}26 27# Basic sanity checks.28#29do_test reindex-1.1 {30  execsql {31    CREATE TABLE t1(a,b);32    INSERT INTO t1 VALUES(1,2);33    INSERT INTO t1 VALUES(3,4);34    CREATE INDEX i1 ON t1(a);35    REINDEX;36  }37} {}38integrity_check reindex-1.239do_test reindex-1.3 {40  execsql {41    REINDEX t1;42  }43} {}44integrity_check reindex-1.445do_test reindex-1.5 {46  execsql {47    REINDEX i1;48  }49} {}50integrity_check reindex-1.651do_test reindex-1.7 {52  execsql {53    REINDEX main.t1;54  }55} {}56do_test reindex-1.8 {57  execsql {58    REINDEX main.i1;59  }60} {}61do_test reindex-1.9 {62  catchsql {63    REINDEX bogus64  }65} {1 {unable to identify the object to be reindexed}}66 67# Set up a table for testing that includes several different collating68# sequences including some that we can modify.69#70do_test reindex-2.1 {71  proc c1 {a b} {72    return [expr {-[string compare $a $b]}]73  }74  proc c2 {a b} {75    return [expr {-[string compare [string tolower $a] [string tolower $b]]}]76  }77  db collate c1 c178  db collate c2 c279  execsql {80    CREATE TABLE t2(81      a TEXT PRIMARY KEY COLLATE c1,82      b TEXT UNIQUE COLLATE c2,83      c TEXT COLLATE nocase,84      d TEST COLLATE binary85    );86    INSERT INTO t2 VALUES('abc','abc','abc','abc');87    INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD');88    INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd');89    INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE');90    SELECT a FROM t2 ORDER BY a;91  }92} {bcd abc BCDE ABCD}93do_test reindex-2.2 {94  execsql {95    SELECT b FROM t2 ORDER BY b;96  }97} {BCDE bcd ABCD abc}98do_test reindex-2.3 {99  execsql {100    SELECT c FROM t2 ORDER BY c;101  }102} {abc ABCD bcd BCDE}103do_test reindex-2.4 {104  execsql {105    SELECT d FROM t2 ORDER BY d;106  }107} {ABCD BCDE abc bcd}108 109# Change a collating sequence function.  Verify that REINDEX rebuilds110# the index.111#112do_test reindex-2.5 {113  proc c1 {a b} {114    return [string compare $a $b]115  }116  execsql {117    SELECT a FROM t2 ORDER BY a;118  }119} {bcd abc BCDE ABCD}120ifcapable {integrityck} {121  do_test reindex-2.5.1 {122    string equal ok [execsql {PRAGMA integrity_check}]123  } {0}124}125do_test reindex-2.6 {126  execsql {127    REINDEX c2;128    SELECT a FROM t2 ORDER BY a;129  }130} {bcd abc BCDE ABCD}131do_test reindex-2.7 {132  execsql {133    REINDEX t1;134    SELECT a FROM t2 ORDER BY a;135  }136} {bcd abc BCDE ABCD}137do_test reindex-2.8 {138  execsql {139    REINDEX c1;140    SELECT a FROM t2 ORDER BY a;141  }142} {ABCD BCDE abc bcd}143integrity_check reindex-2.8.1144 145# Try to REINDEX an index for which the collation sequence is not available.146#147do_test reindex-3.1 {148  sqlite3 db2 test.db149  catchsql {150    REINDEX c1;151  } db2152} {1 {no such collation sequence: c1}}153do_test reindex-3.2 {154  proc need_collate {collation} {155    db2 collate c1 c1156  }157  db2 collation_needed need_collate158  catchsql {159    REINDEX c1;160  } db2161} {0 {}}162do_test reindex-3.3 {163  catchsql {164    REINDEX;165  } db2166} {1 {no such collation sequence: c2}}167 168do_test reindex-3.99 {169  db2 close170} {}171 172#-------------------------------------------------------------------------173foreach {tn wo} {1 "" 2 "WITHOUT ROWID"} {174  reset_db175  eval [string map [list %without_rowid% $wo] {176    do_execsql_test 4.$tn.0 {177      CREATE TABLE t0 (178        c0 INTEGER PRIMARY KEY DESC, 179        c1 UNIQUE DEFAULT NULL180      ) %without_rowid% ;181      INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);182      SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1;183    } {1 2 3 4 5}184    185    do_execsql_test 4.$tn.1 {186      REINDEX;187    }188    189    do_execsql_test 4.$tn.2 {190      SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1;191    } {1 2 3 4 5}192 193    do_execsql_test 4.$tn.3 {194      SELECT c0 FROM t0 WHERE c1 IS NULL AND c0 IN (1,2,3,4,5);195    } {1 2 3 4 5}196 197    do_execsql_test 4.$tn.4 {198      PRAGMA integrity_check;199    } {ok}200  }]201}202 203 204 205finish_test206