wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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-57021-15304 -- 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 | 4 "REINDEX i1" {} |
||
38 | 5 "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 |