Annotation of embedaddon/sqlite3/test/e_reindex.test, revision 1.1.1.1
1.1 misho 1: # 2010 September 24
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: #
12: # This file implements tests to verify that the "testable statements" in
13: # the lang_reindex.html document are correct.
14: #
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: proc do_reindex_tests {args} {
20: uplevel do_select_tests $args
21: }
22:
23: do_execsql_test e_reindex-0.0 {
24: CREATE TABLE t1(a, b);
25: CREATE INDEX i1 ON t1(a, b);
26: CREATE INDEX i2 ON t1(b, a);
27: } {}
28:
29: # EVIDENCE-OF: R-51477-38549 -- syntax diagram reindex-stmt
30: #
31: do_reindex_tests e_reindex-0.1 {
32: 1 "REINDEX" {}
33: 2 "REINDEX nocase" {}
34: 3 "REINDEX binary" {}
35: 4 "REINDEX t1" {}
36: 5 "REINDEX main.t1" {}
37: 6 "REINDEX i1" {}
38: 7 "REINDEX main.i1" {}
39: }
40:
41: # EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
42: # recreate indices from scratch.
43: #
44: # Test this by corrupting some database indexes, running REINDEX, and
45: # observing that the corruption is gone.
46: #
47: do_execsql_test e_reindex-1.1 {
48: INSERT INTO t1 VALUES(1, 2);
49: INSERT INTO t1 VALUES(3, 4);
50: INSERT INTO t1 VALUES(5, 6);
51: PRAGMA writable_schema = 1;
52: UPDATE sqlite_master SET sql = '-- ' || sql WHERE type = 'index';
53: } {}
54:
55: db close
56: sqlite3 db test.db
57: do_execsql_test e_reindex-1.2 {
58: DELETE FROM t1 WHERE a = 3;
59: INSERT INTO t1 VALUES(7, 8);
60: INSERT INTO t1 VALUES(9, 10);
61: PRAGMA writable_schema = 1;
62: UPDATE sqlite_master SET sql = substr(sql, 4) WHERE type = 'index';
63: } {}
64:
65: db close
66: sqlite3 db test.db
67: do_execsql_test e_reindex-1.3 {
68: PRAGMA integrity_check;
69: } [list \
70: {rowid 4 missing from index i2} \
71: {rowid 4 missing from index i1} \
72: {rowid 5 missing from index i2} \
73: {rowid 5 missing from index i1} \
74: {wrong # of entries in index i2} \
75: {wrong # of entries in index i1}
76: ]
77:
78: do_execsql_test e_reindex-1.4 {
79: REINDEX;
80: PRAGMA integrity_check;
81: } {ok}
82:
83: #-------------------------------------------------------------------------
84: # The remaining tests in this file focus on testing that the REINDEX
85: # command reindexes the correct subset of the indexes in the database.
86: # They all use the following dataset.
87: #
88: db close
89: forcedelete test.db2
90: forcedelete test.db
91: sqlite3 db test.db
92:
93: proc sort_by_length {lhs rhs} {
94: set res [expr {[string length $lhs] - [string length $rhs]}]
95: if {$res!=0} {return $res}
96: return [string compare $lhs $rhs]
97: }
98: array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
99: proc sort_by_value {lhs rhs} {
100: global V
101: set res [expr {$V($lhs) - $V(rhs)}]
102: if {$res!=0} {return $res}
103: return [string compare $lhs $rhs]
104: }
105:
106: db collate collA sort_by_length
107: db collate collB sort_by_value
108:
109: set BY(length) {one six two five four eight seven three}
110: set BY(value) {one two three four five six seven eight}
111:
112: do_execsql_test e_reindex-2.0 {
113: ATTACH 'test.db2' AS aux;
114:
115: CREATE TABLE t1(x);
116: CREATE INDEX i1_a ON t1(x COLLATE collA);
117: CREATE INDEX i1_b ON t1(x COLLATE collB);
118: INSERT INTO t1 VALUES('one');
119: INSERT INTO t1 VALUES('two');
120: INSERT INTO t1 VALUES('three');
121: INSERT INTO t1 VALUES('four');
122: INSERT INTO t1 VALUES('five');
123: INSERT INTO t1 VALUES('six');
124: INSERT INTO t1 VALUES('seven');
125: INSERT INTO t1 VALUES('eight');
126:
127: CREATE TABLE t2(x);
128: CREATE INDEX i2_a ON t2(x COLLATE collA);
129: CREATE INDEX i2_b ON t2(x COLLATE collB);
130: INSERT INTO t2 SELECT x FROM t1;
131:
132: CREATE TABLE aux.t1(x);
133: CREATE INDEX aux.i1_a ON t1(x COLLATE collA);
134: CREATE INDEX aux.i1_b ON t1(x COLLATE collB);
135: INSERT INTO aux.t1 SELECT x FROM main.t1;
136:
137: } {}
138:
139: proc test_index {tn tbl collation expected} {
140: set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"
141: uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]
142: }
143:
144: proc set_collations {a b} {
145: db collate collA "sort_by_$a"
146: db collate collB "sort_by_$b"
147: }
148:
149: test_index 1.1 t1 collA length
150: test_index 1.2 t1 collB value
151: test_index 1.3 t2 collA length
152: test_index 1.4 t2 collB value
153: test_index 1.5 aux.t1 collA length
154: test_index 1.6 aux.t1 collB value
155:
156:
157: # EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a
158: # collation-sequence or database object identifier, then all indices in
159: # all attached databases are rebuilt.
160: #
161: set_collations value length
162: do_execsql_test e_reindex-2.2.1 "REINDEX" {}
163: test_index 2.2 t1 collA value
164: test_index 2.3 t1 collB length
165: test_index 2.4 t2 collA value
166: test_index 2.5 t2 collB length
167: test_index 2.6 aux.t1 collA value
168: test_index 2.7 aux.t1 collB length
169:
170: # EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a
171: # collation-sequence name, then all indices in all attached databases
172: # that use the named collation sequences are recreated.
173: #
174: set_collations length value
175: do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
176: test_index 3.2 t1 collA length
177: test_index 3.3 t1 collB length
178: test_index 3.4 t2 collA length
179: test_index 3.5 t2 collB length
180: test_index 3.6 aux.t1 collA length
181: test_index 3.7 aux.t1 collB length
182: do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
183: test_index 3.9 t1 collA length
184: test_index 3.10 t1 collB value
185: test_index 3.11 t2 collA length
186: test_index 3.12 t2 collB value
187: test_index 3.13 aux.t1 collA length
188: test_index 3.14 aux.t1 collB value
189:
190: # EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX
191: # identifies a specific database table, then all indices attached to the
192: # database table are rebuilt.
193: #
194: set_collations value length
195: do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
196: test_index 4.2 t1 collA value
197: test_index 4.3 t1 collB length
198: test_index 4.4 t2 collA length
199: test_index 4.5 t2 collB value
200: test_index 4.6 aux.t1 collA length
201: test_index 4.7 aux.t1 collB value
202: do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
203: test_index 4.9 t1 collA value
204: test_index 4.10 t1 collB length
205: test_index 4.11 t2 collA length
206: test_index 4.12 t2 collB value
207: test_index 4.13 aux.t1 collA value
208: test_index 4.14 aux.t1 collB length
209: do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
210: test_index 4.16 t1 collA value
211: test_index 4.17 t1 collB length
212: test_index 4.18 t2 collA value
213: test_index 4.19 t2 collB length
214: test_index 4.20 aux.t1 collA value
215: test_index 4.21 aux.t1 collB length
216:
217: # EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,
218: # then just that index is recreated.
219: #
220: set_collations length value
221: do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
222: test_index 5.2 t1 collA length
223: test_index 5.3 t1 collB length
224: test_index 5.4 t2 collA value
225: test_index 5.5 t2 collB length
226: test_index 5.6 aux.t1 collA value
227: test_index 5.7 aux.t1 collB length
228: do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
229: test_index 5.9 t1 collA length
230: test_index 5.10 t1 collB length
231: test_index 5.11 t2 collA value
232: test_index 5.12 t2 collB value
233: test_index 5.13 aux.t1 collA value
234: test_index 5.14 aux.t1 collB length
235: do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
236: test_index 5.16 t1 collA length
237: test_index 5.17 t1 collB length
238: test_index 5.18 t2 collA value
239: test_index 5.19 t2 collB value
240: test_index 5.20 aux.t1 collA value
241: test_index 5.21 aux.t1 collB value
242: do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
243: test_index 5.23 t1 collA length
244: test_index 5.24 t1 collB value
245: test_index 5.25 t2 collA value
246: test_index 5.26 t2 collB value
247: test_index 5.27 aux.t1 collA value
248: test_index 5.28 aux.t1 collB value
249: do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
250: test_index 5.30 t1 collA length
251: test_index 5.31 t1 collB value
252: test_index 5.32 t2 collA length
253: test_index 5.33 t2 collB value
254: test_index 5.34 aux.t1 collA value
255: test_index 5.35 aux.t1 collB value
256: do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
257: test_index 5.37 t1 collA length
258: test_index 5.38 t1 collB value
259: test_index 5.39 t2 collA length
260: test_index 5.40 t2 collB value
261: test_index 5.41 aux.t1 collA length
262: test_index 5.42 aux.t1 collB value
263:
264: # EVIDENCE-OF: R-15639-02023 If no database-name is specified and there
265: # exists both a table or index and a collation sequence of the specified
266: # name, SQLite interprets this as a request to rebuild the indices that
267: # use the named collation sequence.
268: #
269: set_collations value length
270: do_execsql_test e_reindex-2.6.0 {
271: CREATE TABLE collA(x);
272: CREATE INDEX icolla_a ON collA(x COLLATE collA);
273: CREATE INDEX icolla_b ON collA(x COLLATE collB);
274:
275: INSERT INTO collA SELECT x FROM t1;
276: } {}
277:
278: test_index 6.1 collA collA value
279: test_index 6.2 collA collB length
280:
281: set_collations length value
282: do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
283: test_index 6.4 collA collA length
284: test_index 6.5 collA collB length
285: do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
286: test_index 6.4 collA collA length
287: test_index 6.5 collA collB value
288:
289: set_collations value length
290: do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
291: test_index 6.7 collA collA value
292: test_index 6.8 collA collB length
293:
294: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_reindex.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test