xref: /sqlite-3.40.0/test/reindex.test (revision c12655df) 
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
18set testprefix reindex
19
20# There is nothing to test if REINDEX is disable for this build.
21#
22ifcapable {!reindex} {
23  finish_test
24  return
25}
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.2
39do_test reindex-1.3 {
40  execsql {
41    REINDEX t1;
42  }
43} {}
44integrity_check reindex-1.4
45do_test reindex-1.5 {
46  execsql {
47    REINDEX i1;
48  }
49} {}
50integrity_check reindex-1.6
51do_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 bogus
64  }
65} {1 {unable to identify the object to be reindexed}}
66
67# Set up a table for testing that includes several different collating
68# 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 c1
78  db collate c2 c2
79  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 binary
85    );
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 rebuilds
110# 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.1
144
145# Try to REINDEX an index for which the collation sequence is not available.
146#
147do_test reindex-3.1 {
148  sqlite3 db2 test.db
149  catchsql {
150    REINDEX c1;
151  } db2
152} {1 {no such collation sequence: c1}}
153do_test reindex-3.2 {
154  proc need_collate {collation} {
155    db2 collate c1 c1
156  }
157  db2 collation_needed need_collate
158  catchsql {
159    REINDEX c1;
160  } db2
161} {0 {}}
162do_test reindex-3.3 {
163  catchsql {
164    REINDEX;
165  } db2
166} {1 {no such collation sequence: c2}}
167
168do_test reindex-3.99 {
169  db2 close
170} {}
171
172#-------------------------------------------------------------------------
173foreach {tn wo} {1 "" 2 "WITHOUT ROWID"} {
174  reset_db
175  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 NULL
180      ) %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_test
206