wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2009 August 06 |
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 regression tests for SQLite library. This file |
||
13 | # implements tests for the extra functionality provided by the ANALYZE |
||
14 | # command when the library is compiled with SQLITE_ENABLE_STAT2 defined. |
||
15 | # |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | ifcapable !stat2 { |
||
21 | finish_test |
||
22 | return |
||
23 | } |
||
24 | |||
25 | set testprefix analyze2 |
||
26 | |||
27 | # Do not use a codec for tests in this file, as the database file is |
||
28 | # manipulated directly using tcl scripts (using the [hexio_write] command). |
||
29 | # |
||
30 | do_not_use_codec |
||
31 | |||
32 | #-------------------------------------------------------------------- |
||
33 | # Test organization: |
||
34 | # |
||
35 | # analyze2-1.*: Tests to verify that ANALYZE creates and populates the |
||
36 | # sqlite_stat2 table as expected. |
||
37 | # |
||
38 | # analyze2-2.*: Test that when a table has two indexes on it and either |
||
39 | # index may be used for the scan, the index suggested by |
||
40 | # the contents of sqlite_stat2 table is prefered. |
||
41 | # |
||
42 | # analyze2-3.*: Similar to the previous block of tests, but using tables |
||
43 | # that contain a mixture of NULL, numeric, text and blob |
||
44 | # values. |
||
45 | # |
||
46 | # analyze2-4.*: Check that when an indexed column uses a collation other |
||
47 | # than BINARY, the collation is taken into account when |
||
48 | # using the contents of sqlite_stat2 to estimate the cost |
||
49 | # of a range scan. |
||
50 | # |
||
51 | # analyze2-5.*: Check that collation sequences are used as described above |
||
52 | # even when the only available version of the collation |
||
53 | # function require UTF-16 encoded arguments. |
||
54 | # |
||
55 | # analyze2-6.*: Check that the library behaves correctly when one of the |
||
56 | # sqlite_stat2 or sqlite_stat1 tables are missing. |
||
57 | # |
||
58 | # analyze2-7.*: Check that in a shared-schema situation, nothing goes |
||
59 | # wrong if sqlite_stat2 data is read by one connection, |
||
60 | # and freed by another. |
||
61 | # |
||
62 | |||
63 | proc eqp {sql {db db}} { |
||
64 | uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db |
||
65 | } |
||
66 | |||
67 | do_test analyze2-1.1 { |
||
68 | execsql { CREATE TABLE t1(x PRIMARY KEY) } |
||
69 | for {set i [expr 0]} {$i < 1000} {incr i} { |
||
70 | execsql { INSERT INTO t1 VALUES($i) } |
||
71 | } |
||
72 | execsql { |
||
73 | ANALYZE; |
||
74 | SELECT * FROM sqlite_stat2; |
||
75 | } |
||
76 | } [list t1 sqlite_autoindex_t1_1 0 50 \ |
||
77 | t1 sqlite_autoindex_t1_1 1 149 \ |
||
78 | t1 sqlite_autoindex_t1_1 2 249 \ |
||
79 | t1 sqlite_autoindex_t1_1 3 349 \ |
||
80 | t1 sqlite_autoindex_t1_1 4 449 \ |
||
81 | t1 sqlite_autoindex_t1_1 5 549 \ |
||
82 | t1 sqlite_autoindex_t1_1 6 649 \ |
||
83 | t1 sqlite_autoindex_t1_1 7 749 \ |
||
84 | t1 sqlite_autoindex_t1_1 8 849 \ |
||
85 | t1 sqlite_autoindex_t1_1 9 949 \ |
||
86 | ] |
||
87 | |||
88 | do_test analyze2-1.2 { |
||
89 | execsql { |
||
90 | DELETE FROM t1 WHERe x>9; |
||
91 | ANALYZE; |
||
92 | SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2; |
||
93 | } |
||
94 | } {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}} |
||
95 | do_test analyze2-1.3 { |
||
96 | execsql { |
||
97 | DELETE FROM t1 WHERE x>8; |
||
98 | ANALYZE; |
||
99 | SELECT * FROM sqlite_stat2; |
||
100 | } |
||
101 | } {} |
||
102 | do_test analyze2-1.4 { |
||
103 | execsql { |
||
104 | DELETE FROM t1; |
||
105 | ANALYZE; |
||
106 | SELECT * FROM sqlite_stat2; |
||
107 | } |
||
108 | } {} |
||
109 | |||
110 | do_test analyze2-2.1 { |
||
111 | execsql { |
||
112 | BEGIN; |
||
113 | DROP TABLE t1; |
||
114 | CREATE TABLE t1(x, y); |
||
115 | CREATE INDEX t1_x ON t1(x); |
||
116 | CREATE INDEX t1_y ON t1(y); |
||
117 | } |
||
118 | for {set i [expr 0]} {$i < 1000} {incr i} { |
||
119 | execsql { INSERT INTO t1 VALUES($i, $i) } |
||
120 | } |
||
121 | execsql COMMIT |
||
122 | execsql ANALYZE |
||
123 | } {} |
||
124 | do_eqp_test 2.2 { |
||
125 | SELECT * FROM t1 WHERE x>500 AND y>700 |
||
126 | } { |
||
127 | |||
128 | } |
||
129 | do_eqp_test 2.3 { |
||
130 | SELECT * FROM t1 WHERE x>700 AND y>500 |
||
131 | } { |
||
132 | |||
133 | } |
||
134 | do_eqp_test 2.3 { |
||
135 | SELECT * FROM t1 WHERE y>700 AND x>500 |
||
136 | } { |
||
137 | |||
138 | } |
||
139 | do_eqp_test 2.4 { |
||
140 | SELECT * FROM t1 WHERE y>500 AND x>700 |
||
141 | } { |
||
142 | |||
143 | } |
||
144 | do_eqp_test 2.5 { |
||
145 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700 |
||
146 | } { |
||
147 | |||
148 | } |
||
149 | do_eqp_test 2.6 { |
||
150 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700 |
||
151 | } { |
||
152 | |||
153 | } |
||
154 | do_eqp_test 2.7 { |
||
155 | SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300 |
||
156 | } { |
||
157 | |||
158 | } |
||
159 | do_eqp_test 2.8 { |
||
160 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300 |
||
161 | } { |
||
162 | |||
163 | } |
||
164 | do_eqp_test 2.9 { |
||
165 | SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300 |
||
166 | } { |
||
167 | |||
168 | } |
||
169 | do_eqp_test 2.10 { |
||
170 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100 |
||
171 | } { |
||
172 | |||
173 | } |
||
174 | |||
175 | do_test analyze2-3.1 { |
||
176 | set alphabet [list a b c d e f g h i j] |
||
177 | execsql BEGIN |
||
178 | for {set i 0} {$i < 1000} {incr i} { |
||
179 | set str [lindex $alphabet [expr ($i/100)%10]] |
||
180 | append str [lindex $alphabet [expr ($i/ 10)%10]] |
||
181 | append str [lindex $alphabet [expr ($i/ 1)%10]] |
||
182 | execsql { INSERT INTO t1 VALUES($str, $str) } |
||
183 | } |
||
184 | execsql COMMIT |
||
185 | execsql ANALYZE |
||
186 | execsql { |
||
187 | SELECT tbl,idx,group_concat(sample,' ') |
||
188 | FROM sqlite_stat2 |
||
189 | WHERE idx = 't1_x' |
||
190 | GROUP BY tbl,idx |
||
191 | } |
||
192 | } {t1 t1_x {100 299 499 699 899 ajj cjj ejj gjj ijj}} |
||
193 | do_test analyze2-3.2 { |
||
194 | execsql { |
||
195 | SELECT tbl,idx,group_concat(sample,' ') |
||
196 | FROM sqlite_stat2 |
||
197 | WHERE idx = 't1_y' |
||
198 | GROUP BY tbl,idx |
||
199 | } |
||
200 | } {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}} |
||
201 | |||
202 | do_eqp_test 3.3 { |
||
203 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b' |
||
204 | } { |
||
205 | |||
206 | } |
||
207 | do_eqp_test 3.4 { |
||
208 | SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h' |
||
209 | } { |
||
210 | |||
211 | } |
||
212 | do_eqp_test 3.5 { |
||
213 | SELECT * FROM t1 WHERE x<'a' AND y>'h' |
||
214 | } { |
||
215 | |||
216 | } |
||
217 | do_eqp_test 3.6 { |
||
218 | SELECT * FROM t1 WHERE x<444 AND y>'h' |
||
219 | } { |
||
220 | |||
221 | } |
||
222 | do_eqp_test 3.7 { |
||
223 | SELECT * FROM t1 WHERE x<221 AND y>'g' |
||
224 | } { |
||
225 | |||
226 | } |
||
227 | |||
228 | do_test analyze2-4.1 { |
||
229 | execsql { CREATE TABLE t3(a COLLATE nocase, b) } |
||
230 | execsql { CREATE INDEX t3a ON t3(a) } |
||
231 | execsql { CREATE INDEX t3b ON t3(b) } |
||
232 | set alphabet [list A b C d E f G h I j] |
||
233 | execsql BEGIN |
||
234 | for {set i 0} {$i < 1000} {incr i} { |
||
235 | set str [lindex $alphabet [expr ($i/100)%10]] |
||
236 | append str [lindex $alphabet [expr ($i/ 10)%10]] |
||
237 | append str [lindex $alphabet [expr ($i/ 1)%10]] |
||
238 | execsql { INSERT INTO t3 VALUES($str, $str) } |
||
239 | } |
||
240 | execsql COMMIT |
||
241 | execsql ANALYZE |
||
242 | } {} |
||
243 | do_test analyze2-4.2 { |
||
244 | execsql { |
||
245 | PRAGMA automatic_index=OFF; |
||
246 | SELECT tbl,idx,group_concat(sample,' ') |
||
247 | FROM sqlite_stat2 |
||
248 | WHERE idx = 't3a' |
||
249 | GROUP BY tbl,idx; |
||
250 | PRAGMA automatic_index=ON; |
||
251 | } |
||
252 | } {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}} |
||
253 | do_test analyze2-4.3 { |
||
254 | execsql { |
||
255 | SELECT tbl,idx,group_concat(sample,' ') |
||
256 | FROM sqlite_stat2 |
||
257 | WHERE idx = 't3b' |
||
258 | GROUP BY tbl,idx |
||
259 | } |
||
260 | } {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}} |
||
261 | |||
262 | do_eqp_test 4.4 { |
||
263 | SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C' |
||
264 | } { |
||
265 | |||
266 | } |
||
267 | do_eqp_test 4.5 { |
||
268 | SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c' |
||
269 | } { |
||
270 | |||
271 | } |
||
272 | |||
273 | ifcapable utf16 { |
||
274 | proc test_collate {enc lhs rhs} { |
||
275 | # puts $enc |
||
276 | return [string compare $lhs $rhs] |
||
277 | } |
||
278 | do_test analyze2-5.1 { |
||
279 | add_test_collate db 0 0 1 |
||
280 | execsql { CREATE TABLE t4(x COLLATE test_collate) } |
||
281 | execsql { CREATE INDEX t4x ON t4(x) } |
||
282 | set alphabet [list a b c d e f g h i j] |
||
283 | execsql BEGIN |
||
284 | for {set i 0} {$i < 1000} {incr i} { |
||
285 | set str [lindex $alphabet [expr ($i/100)%10]] |
||
286 | append str [lindex $alphabet [expr ($i/ 10)%10]] |
||
287 | append str [lindex $alphabet [expr ($i/ 1)%10]] |
||
288 | execsql { INSERT INTO t4 VALUES($str) } |
||
289 | } |
||
290 | execsql COMMIT |
||
291 | execsql ANALYZE |
||
292 | } {} |
||
293 | do_test analyze2-5.2 { |
||
294 | execsql { |
||
295 | SELECT tbl,idx,group_concat(sample,' ') |
||
296 | FROM sqlite_stat2 |
||
297 | WHERE tbl = 't4' |
||
298 | GROUP BY tbl,idx |
||
299 | } |
||
300 | } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}} |
||
301 | do_eqp_test 5.3 { |
||
302 | SELECT * FROM t4 WHERE x>'ccc' |
||
303 | } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}} |
||
304 | do_eqp_test 5.4 { |
||
305 | SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg' |
||
306 | } { |
||
307 | |||
308 | |||
309 | } |
||
310 | do_eqp_test 5.5 { |
||
311 | SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc' |
||
312 | } { |
||
313 | |||
314 | |||
315 | } |
||
316 | } |
||
317 | |||
318 | #-------------------------------------------------------------------- |
||
319 | # These tests, analyze2-6.*, verify that the library behaves correctly |
||
320 | # when one of the sqlite_stat1 and sqlite_stat2 tables is missing. |
||
321 | # |
||
322 | # If the sqlite_stat1 table is not present, then the sqlite_stat2 |
||
323 | # table is not read. However, if it is the sqlite_stat2 table that |
||
324 | # is missing, the data in the sqlite_stat1 table is still used. |
||
325 | # |
||
326 | # Tests analyze2-6.1.* test the libary when the sqlite_stat2 table |
||
327 | # is missing. Tests analyze2-6.2.* test the library when sqlite_stat1 |
||
328 | # is not present. |
||
329 | # |
||
330 | do_test analyze2-6.0 { |
||
331 | execsql { |
||
332 | DROP TABLE IF EXISTS t4; |
||
333 | CREATE TABLE t5(a, b); CREATE INDEX t5i ON t5(a, b); |
||
334 | CREATE TABLE t6(a, b); CREATE INDEX t6i ON t6(a, b); |
||
335 | } |
||
336 | for {set ii 0} {$ii < 20} {incr ii} { |
||
337 | execsql { |
||
338 | INSERT INTO t5 VALUES($ii, $ii); |
||
339 | INSERT INTO t6 VALUES($ii/10, $ii/10); |
||
340 | } |
||
341 | } |
||
342 | execsql { |
||
343 | CREATE TABLE master AS |
||
344 | SELECT * FROM sqlite_master WHERE name LIKE 'sqlite_stat%' |
||
345 | } |
||
346 | } {} |
||
347 | |||
348 | do_test analyze2-6.1.1 { |
||
349 | eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
350 | t5.a = 1 AND |
||
351 | t6.a = 1 AND t6.b = 1 |
||
352 | } |
||
353 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
354 | do_test analyze2-6.1.2 { |
||
355 | db cache flush |
||
356 | execsql ANALYZE |
||
357 | eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
358 | t5.a = 1 AND |
||
359 | t6.a = 1 AND t6.b = 1 |
||
360 | } |
||
361 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
362 | do_test analyze2-6.1.3 { |
||
363 | sqlite3 db test.db |
||
364 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
365 | t5.a = 1 AND |
||
366 | t6.a = 1 AND t6.b = 1 |
||
367 | } |
||
368 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
369 | do_test analyze2-6.1.4 { |
||
370 | execsql { |
||
371 | PRAGMA writable_schema = 1; |
||
372 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2'; |
||
373 | } |
||
374 | sqlite3 db test.db |
||
375 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
376 | t5.a = 1 AND |
||
377 | t6.a = 1 AND t6.b = 1 |
||
378 | } |
||
379 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
380 | do_test analyze2-6.1.5 { |
||
381 | execsql { |
||
382 | PRAGMA writable_schema = 1; |
||
383 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1'; |
||
384 | } |
||
385 | sqlite3 db test.db |
||
386 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
387 | t5.a = 1 AND |
||
388 | t6.a = 1 AND t6.b = 1 |
||
389 | } |
||
390 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
391 | do_test analyze2-6.1.6 { |
||
392 | execsql { |
||
393 | PRAGMA writable_schema = 1; |
||
394 | INSERT INTO sqlite_master SELECT * FROM master; |
||
395 | } |
||
396 | sqlite3 db test.db |
||
397 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
398 | t5.a = 1 AND |
||
399 | t6.a = 1 AND t6.b = 1 |
||
400 | } |
||
401 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
402 | |||
403 | do_test analyze2-6.2.1 { |
||
404 | execsql { |
||
405 | DELETE FROM sqlite_stat1; |
||
406 | DELETE FROM sqlite_stat2; |
||
407 | } |
||
408 | sqlite3 db test.db |
||
409 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
410 | t5.a>1 AND t5.a<15 AND |
||
411 | t6.a>1 |
||
412 | } |
||
413 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
414 | do_test analyze2-6.2.2 { |
||
415 | db cache flush |
||
416 | execsql ANALYZE |
||
417 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
418 | t5.a>1 AND t5.a<15 AND |
||
419 | t6.a>1 |
||
420 | } |
||
421 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
422 | do_test analyze2-6.2.3 { |
||
423 | sqlite3 db test.db |
||
424 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
425 | t5.a>1 AND t5.a<15 AND |
||
426 | t6.a>1 |
||
427 | } |
||
428 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
429 | do_test analyze2-6.2.4 { |
||
430 | execsql { |
||
431 | PRAGMA writable_schema = 1; |
||
432 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1'; |
||
433 | } |
||
434 | sqlite3 db test.db |
||
435 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
436 | t5.a>1 AND t5.a<15 AND |
||
437 | t6.a>1 |
||
438 | } |
||
439 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
440 | do_test analyze2-6.2.5 { |
||
441 | execsql { |
||
442 | PRAGMA writable_schema = 1; |
||
443 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2'; |
||
444 | } |
||
445 | sqlite3 db test.db |
||
446 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
447 | t5.a>1 AND t5.a<15 AND |
||
448 | t6.a>1 |
||
449 | } |
||
450 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
451 | do_test analyze2-6.2.6 { |
||
452 | execsql { |
||
453 | PRAGMA writable_schema = 1; |
||
454 | INSERT INTO sqlite_master SELECT * FROM master; |
||
455 | } |
||
456 | sqlite3 db test.db |
||
457 | execsql ANALYZE |
||
458 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
459 | t5.a>1 AND t5.a<15 AND |
||
460 | t6.a>1 |
||
461 | } |
||
462 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
463 | |||
464 | #-------------------------------------------------------------------- |
||
465 | # These tests, analyze2-7.*, test that the sqlite_stat2 functionality |
||
466 | # works in shared-cache mode. Note that these tests reuse the database |
||
467 | # created for the analyze2-6.* tests. |
||
468 | # |
||
469 | ifcapable shared_cache { |
||
470 | db close |
||
471 | set ::enable_shared_cache [sqlite3_enable_shared_cache 1] |
||
472 | |||
473 | proc incr_schema_cookie {zDb} { |
||
474 | foreach iOffset {24 40} { |
||
475 | set cookie [hexio_get_int [hexio_read $zDb $iOffset 4]] |
||
476 | incr cookie |
||
477 | hexio_write $zDb $iOffset [hexio_render_int32 $cookie] |
||
478 | } |
||
479 | } |
||
480 | |||
481 | do_test analyze2-7.1 { |
||
482 | sqlite3 db1 test.db |
||
483 | sqlite3 db2 test.db |
||
484 | db1 cache size 0 |
||
485 | db2 cache size 0 |
||
486 | execsql { SELECT count(*) FROM t5 } db1 |
||
487 | } {20} |
||
488 | do_test analyze2-7.2 { |
||
489 | incr_schema_cookie test.db |
||
490 | execsql { SELECT count(*) FROM t5 } db2 |
||
491 | } {20} |
||
492 | do_test analyze2-7.3 { |
||
493 | incr_schema_cookie test.db |
||
494 | execsql { SELECT count(*) FROM t5 } db1 |
||
495 | } {20} |
||
496 | do_test analyze2-7.4 { |
||
497 | incr_schema_cookie test.db |
||
498 | execsql { SELECT count(*) FROM t5 } db2 |
||
499 | } {20} |
||
500 | |||
501 | do_test analyze2-7.5 { |
||
502 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
503 | t5.a>1 AND t5.a<15 AND |
||
504 | t6.a>1 |
||
505 | } db1 |
||
506 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
507 | do_test analyze2-7.6 { |
||
508 | incr_schema_cookie test.db |
||
509 | execsql { SELECT * FROM sqlite_master } db2 |
||
510 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
511 | t5.a>1 AND t5.a<15 AND |
||
512 | t6.a>1 |
||
513 | } db2 |
||
514 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
515 | do_test analyze2-7.7 { |
||
516 | incr_schema_cookie test.db |
||
517 | execsql { SELECT * FROM sqlite_master } db1 |
||
518 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
519 | t5.a>1 AND t5.a<15 AND |
||
520 | t6.a>1 |
||
521 | } db1 |
||
522 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
523 | |||
524 | do_test analyze2-7.8 { |
||
525 | execsql { DELETE FROM sqlite_stat2 } db2 |
||
526 | execsql { SELECT * FROM sqlite_master } db1 |
||
527 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
528 | t5.a>1 AND t5.a<15 AND |
||
529 | t6.a>1 |
||
530 | } db1 |
||
531 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
532 | do_test analyze2-7.9 { |
||
533 | execsql { SELECT * FROM sqlite_master } db2 |
||
534 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
535 | t5.a>1 AND t5.a<15 AND |
||
536 | t6.a>1 |
||
537 | } db2 |
||
538 | } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
539 | |||
540 | do_test analyze2-7.10 { |
||
541 | incr_schema_cookie test.db |
||
542 | execsql { SELECT * FROM sqlite_master } db1 |
||
543 | eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND |
||
544 | t5.a>1 AND t5.a<15 AND |
||
545 | t6.a>1 |
||
546 | } db1 |
||
547 | } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} |
||
548 | |||
549 | db1 close |
||
550 | db2 close |
||
551 | sqlite3_enable_shared_cache $::enable_shared_cache |
||
552 | } |
||
553 | |||
554 | finish_test |