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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# There is nothing to test if REINDEX is disable for this build.
20#
21ifcapable {!reindex} {
22  finish_test
23  return
24}
25
26# Basic sanity checks.
27#
28do_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} {}
37integrity_check reindex-1.2
38do_test reindex-1.3 {
39  execsql {
40    REINDEX t1;
41  }
42} {}
43integrity_check reindex-1.4
44do_test reindex-1.5 {
45  execsql {
46    REINDEX i1;
47  }
48} {}
49integrity_check reindex-1.6
50do_test reindex-1.7 {
51  execsql {
52    REINDEX main.t1;
53  }
54} {}
55do_test reindex-1.8 {
56  execsql {
57    REINDEX main.i1;
58  }
59} {}
60do_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#
69do_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}
92do_test reindex-2.2 {
93  execsql {
94    SELECT b FROM t2 ORDER BY b;
95  }
96} {BCDE bcd ABCD abc}
97do_test reindex-2.3 {
98  execsql {
99    SELECT c FROM t2 ORDER BY c;
100  }
101} {abc ABCD bcd BCDE}
102do_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#
111do_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}
119ifcapable {integrityck} {
120  do_test reindex-2.5.1 {
121    string equal ok [execsql {PRAGMA integrity_check}]
122  } {0}
123}
124do_test reindex-2.6 {
125  execsql {
126    REINDEX c2;
127    SELECT a FROM t2 ORDER BY a;
128  }
129} {bcd abc BCDE ABCD}
130do_test reindex-2.7 {
131  execsql {
132    REINDEX t1;
133    SELECT a FROM t2 ORDER BY a;
134  }
135} {bcd abc BCDE ABCD}
136do_test reindex-2.8 {
137  execsql {
138    REINDEX c1;
139    SELECT a FROM t2 ORDER BY a;
140  }
141} {ABCD BCDE abc bcd}
142integrity_check reindex-2.8.1
143
144# Try to REINDEX an index for which the collation sequence is not available.
145#
146do_test reindex-3.1 {
147  sqlite3 db2 test.db
148  catchsql {
149    REINDEX c1;
150  } db2
151} {1 {no such collation sequence: c1}}
152do_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 {}}
161do_test reindex-3.3 {
162  catchsql {
163    REINDEX;
164  } db2
165} {1 {no such collation sequence: c2}}
166
167do_test reindex-3.99 {
168  db2 close
169} {}
170
171finish_test
172