modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/reindex.test (about)

     1  # 2004 November 5
     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  # 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  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # There is nothing to test if REINDEX is disable for this build.
    20  #
    21  ifcapable {!reindex} {
    22    finish_test
    23    return
    24  }
    25  
    26  # Basic sanity checks.
    27  #
    28  do_test reindex-1.1 {
    29    execsql {
    30      CREATE TABLE t1(a,b);
    31      INSERT INTO t1 VALUES(1,2);
    32      INSERT INTO t1 VALUES(3,4);
    33      CREATE INDEX i1 ON t1(a);
    34      REINDEX;
    35    }
    36  } {}
    37  integrity_check reindex-1.2
    38  do_test reindex-1.3 {
    39    execsql {
    40      REINDEX t1;
    41    }
    42  } {}
    43  integrity_check reindex-1.4
    44  do_test reindex-1.5 {
    45    execsql {
    46      REINDEX i1;
    47    }
    48  } {}
    49  integrity_check reindex-1.6
    50  do_test reindex-1.7 {
    51    execsql {
    52      REINDEX main.t1;
    53    }
    54  } {}
    55  do_test reindex-1.8 {
    56    execsql {
    57      REINDEX main.i1;
    58    }
    59  } {}
    60  do_test reindex-1.9 {
    61    catchsql {
    62      REINDEX bogus
    63    }
    64  } {1 {unable to identify the object to be reindexed}}
    65  
    66  # Set up a table for testing that includes several different collating
    67  # sequences including some that we can modify.
    68  #
    69  do_test reindex-2.1 {
    70    proc c1 {a b} {
    71      return [expr {-[string compare $a $b]}]
    72    }
    73    proc c2 {a b} {
    74      return [expr {-[string compare [string tolower $a] [string tolower $b]]}]
    75    }
    76    db collate c1 c1
    77    db collate c2 c2
    78    execsql {
    79      CREATE TABLE t2(
    80        a TEXT PRIMARY KEY COLLATE c1,
    81        b TEXT UNIQUE COLLATE c2,
    82        c TEXT COLLATE nocase,
    83        d TEST COLLATE binary
    84      );
    85      INSERT INTO t2 VALUES('abc','abc','abc','abc');
    86      INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD');
    87      INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd');
    88      INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE');
    89      SELECT a FROM t2 ORDER BY a;
    90    }
    91  } {bcd abc BCDE ABCD}
    92  do_test reindex-2.2 {
    93    execsql {
    94      SELECT b FROM t2 ORDER BY b;
    95    }
    96  } {BCDE bcd ABCD abc}
    97  do_test reindex-2.3 {
    98    execsql {
    99      SELECT c FROM t2 ORDER BY c;
   100    }
   101  } {abc ABCD bcd BCDE}
   102  do_test reindex-2.4 {
   103    execsql {
   104      SELECT d FROM t2 ORDER BY d;
   105    }
   106  } {ABCD BCDE abc bcd}
   107  
   108  # Change a collating sequence function.  Verify that REINDEX rebuilds
   109  # the index.
   110  #
   111  do_test reindex-2.5 {
   112    proc c1 {a b} {
   113      return [string compare $a $b]
   114    }
   115    execsql {
   116      SELECT a FROM t2 ORDER BY a;
   117    }
   118  } {bcd abc BCDE ABCD}
   119  ifcapable {integrityck} {
   120    do_test reindex-2.5.1 {
   121      string equal ok [execsql {PRAGMA integrity_check}]
   122    } {0}
   123  }
   124  do_test reindex-2.6 {
   125    execsql {
   126      REINDEX c2;
   127      SELECT a FROM t2 ORDER BY a;
   128    }
   129  } {bcd abc BCDE ABCD}
   130  do_test reindex-2.7 {
   131    execsql {
   132      REINDEX t1;
   133      SELECT a FROM t2 ORDER BY a;
   134    }
   135  } {bcd abc BCDE ABCD}
   136  do_test reindex-2.8 {
   137    execsql {
   138      REINDEX c1;
   139      SELECT a FROM t2 ORDER BY a;
   140    }
   141  } {ABCD BCDE abc bcd}
   142  integrity_check reindex-2.8.1
   143  
   144  # Try to REINDEX an index for which the collation sequence is not available.
   145  #
   146  do_test reindex-3.1 {
   147    sqlite3 db2 test.db
   148    catchsql {
   149      REINDEX c1;
   150    } db2
   151  } {1 {no such collation sequence: c1}}
   152  do_test reindex-3.2 {
   153    proc need_collate {collation} {
   154      db2 collate c1 c1
   155    }
   156    db2 collation_needed need_collate
   157    catchsql {
   158      REINDEX c1;
   159    } db2
   160  } {0 {}}
   161  do_test reindex-3.3 {
   162    catchsql {
   163      REINDEX;
   164    } db2
   165  } {1 {no such collation sequence: c2}}
   166  
   167  do_test reindex-3.99 {
   168    db2 close
   169  } {}
   170  
   171  finish_test