Debian Codegraph @

ext/expert/expert1.test

raw
1 # 2009 Nov 11
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 # TESTRUNNER: shell
12 #
13 # The focus of this file is testing the CLI shell tool. Specifically,
14 # the ".recommend" command.
15 #
16 #
17
18 # Test plan:
19 #
20 #
21 if {![info exists testdir]} {
22 set testdir [file join [file dirname [info script]] .. .. test]
23 }
24 source $testdir/tester.tcl
25 set testprefix expert1
26
27 if {[info commands sqlite3_expert_new]==""} {
28 finish_test
29 return
30 }
31
32
33 set CLI [test_binary_name sqlite3]
34 set CMD [test_binary_name sqlite3_expert]
35
36 proc squish {txt} {
37 regsub -all {[[:space:]]+} $txt { }
38 }
39
40 proc do_setup_rec_test {tn setup sql res} {
41 reset_db
42 if {[info exists ::set_main_db_name]} {
43 dbconfig_maindbname_icecube db
44 }
45 db eval $setup
46 uplevel [list do_rec_test $tn $sql $res]
47 }
48
49 foreach {tn setup} {
50 1 {
51 if {![file executable $CMD]} { continue }
52
53 proc do_rec_test {tn sql res} {
54 set res [squish [string trim $res]]
55 set tst [subst -nocommands {
56 squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]]
57 }]
58 uplevel [list do_test $tn $tst $res]
59 }
60 }
61 2 {
62 if {[info commands sqlite3_expert_new]==""} { continue }
63
64 proc do_rec_test {tn sql res} {
65 set expert [sqlite3_expert_new db]
66 $expert sql $sql
67 $expert analyze
68
69 set result [list]
70 for {set i 0} {$i < [$expert count]} {incr i} {
71 set idx [string trim [$expert report $i indexes]]
72 if {$idx==""} {set idx "(no new indexes)"}
73 lappend result $idx
74 lappend result [string trim [$expert report $i plan]]
75 }
76
77 $expert destroy
78
79 set tst [subst -nocommands {set {} [squish [join {$result}]]}]
80 uplevel [list do_test $tn $tst [string trim [squish $res]]]
81 }
82 }
83 3 {
84 if {[info commands sqlite3_expert_new]==""} { continue }
85 set ::set_main_db_name 1
86 }
87 4 {
88 if {![file executable $CLI]} { continue }
89
90 proc do_rec_test {tn sql res} {
91 set res [squish [string trim $res]]
92 set tst [subst -nocommands {
93 squish [string trim [exec $::CLI -noinit test.db ".expert" {$sql;}]]
94 }]
95 uplevel [list do_test $tn $tst $res]
96 }
97 }
98 } {
99
100 eval $setup
101
102
103 do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
104 SELECT * FROM t1
105 } {
106 (no new indexes)
107 SCAN t1
108 }
109
110 do_setup_rec_test $tn.2 {
111 CREATE TABLE t1(a, b, c);
112 } {
113 SELECT * FROM t1 WHERE b>?;
114 } {
115 CREATE INDEX t1_idx_00000062 ON t1(b);
116 SEARCH t1 USING INDEX t1_idx_00000062 (b>?)
117 }
118
119 do_setup_rec_test $tn.3 {
120 CREATE TABLE t1(a, b, c);
121 } {
122 SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
123 } {
124 CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
125 SEARCH t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
126 }
127
128 do_setup_rec_test $tn.4 {
129 CREATE TABLE t1(a, b, c);
130 } {
131 SELECT a FROM t1 ORDER BY b;
132 } {
133 CREATE INDEX t1_idx_00000062 ON t1(b);
134 SCAN t1 USING INDEX t1_idx_00000062
135 }
136
137 do_setup_rec_test $tn.5 {
138 CREATE TABLE t1(a, b, c);
139 } {
140 SELECT a FROM t1 WHERE a=? ORDER BY b;
141 } {
142 CREATE INDEX t1_idx_000123a7 ON t1(a, b);
143 SEARCH t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
144 }
145
146 if 0 {
147 do_setup_rec_test $tn.6 {
148 CREATE TABLE t1(a, b, c);
149 } {
150 SELECT min(a) FROM t1
151 } {
152 CREATE INDEX t1_idx_00000061 ON t1(a);
153 SEARCH t1 USING COVERING INDEX t1_idx_00000061
154 }
155 }
156
157 do_setup_rec_test $tn.7 {
158 CREATE TABLE t1(a, b, c);
159 } {
160 SELECT * FROM t1 ORDER BY a, b, c;
161 } {
162 CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
163 SCAN t1 USING COVERING INDEX t1_idx_033e95fe
164 }
165
166 #do_setup_rec_test $tn.1.8 {
167 # CREATE TABLE t1(a, b, c);
168 #} {
169 # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
170 #} {
171 # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
172 # 0|0|0|SCAN t1 USING COVERING INDEX t1_idx_5be6e222
173 #}
174
175 do_setup_rec_test $tn.8.1 {
176 CREATE TABLE t1(a COLLATE NOCase, b, c);
177 } {
178 SELECT * FROM t1 WHERE a=?
179 } {
180 CREATE INDEX t1_idx_00000061 ON t1(a);
181 SEARCH t1 USING INDEX t1_idx_00000061 (a=?)
182 }
183 do_setup_rec_test $tn.8.2 {
184 CREATE TABLE t1(a, b COLLATE nocase, c);
185 } {
186 SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
187 } {
188 CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
189 SCAN t1 USING COVERING INDEX t1_idx_5cb97285
190 }
191
192
193 # Tables with names that require quotes.
194 #
195 do_setup_rec_test $tn.9.1 {
196 CREATE TABLE "t t"(a, b, c);
197 } {
198 SELECT * FROM "t t" WHERE a=?
199 } {
200 CREATE INDEX "t t_idx_00000061" ON "t t"(a);
201 SEARCH t t USING INDEX t t_idx_00000061 (a=?)
202 }
203
204 do_setup_rec_test $tn.9.2 {
205 CREATE TABLE "t t"(a, b, c);
206 } {
207 SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
208 } {
209 CREATE INDEX "t t_idx_00000062" ON "t t"(b);
210 SEARCH t t USING INDEX t t_idx_00000062 (b>? AND b<?)
211 }
212
213 # Columns with names that require quotes.
214 #
215 do_setup_rec_test $tn.10.1 {
216 CREATE TABLE t3(a, "b b", c);
217 } {
218 SELECT * FROM t3 WHERE "b b" = ?
219 } {
220 CREATE INDEX t3_idx_00050c52 ON t3('b b');
221 SEARCH t3 USING INDEX t3_idx_00050c52 (b b=?)
222 }
223
224 do_setup_rec_test $tn.10.2 {
225 CREATE TABLE t3(a, "b b", c);
226 } {
227 SELECT * FROM t3 ORDER BY "b b"
228 } {
229 CREATE INDEX t3_idx_00050c52 ON t3('b b');
230 SCAN t3 USING INDEX t3_idx_00050c52
231 }
232
233 # Transitive constraints
234 #
235 do_setup_rec_test $tn.11.1 {
236 CREATE TABLE t5(a, b);
237 CREATE TABLE t6(c, d);
238 } {
239 SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
240 } {
241 CREATE INDEX t5_idx_000123a7 ON t5(a, b);
242 CREATE INDEX t6_idx_00000063 ON t6(c);
243 SEARCH t6 USING INDEX t6_idx_00000063 (c=?)
244 SEARCH t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
245 }
246
247 # OR terms.
248 #
249 do_setup_rec_test $tn.12.1 {
250 CREATE TABLE t7(a, b);
251 } {
252 SELECT * FROM t7 WHERE a=? OR b=?
253 } {
254 CREATE INDEX t7_idx_00000062 ON t7(b);
255 CREATE INDEX t7_idx_00000061 ON t7(a);
256 MULTI-INDEX OR
257 INDEX 1
258 SEARCH t7 USING INDEX t7_idx_00000061 (a=?)
259 INDEX 2
260 SEARCH t7 USING INDEX t7_idx_00000062 (b=?)
261 }
262
263 # rowid terms.
264 #
265 do_setup_rec_test $tn.13.1 {
266 CREATE TABLE t8(a, b);
267 } {
268 SELECT * FROM t8 WHERE rowid=?
269 } {
270 (no new indexes)
271 SEARCH t8 USING INTEGER PRIMARY KEY (rowid=?)
272 }
273 do_setup_rec_test $tn.13.2 {
274 CREATE TABLE t8(a, b);
275 } {
276 SELECT * FROM t8 ORDER BY rowid
277 } {
278 (no new indexes)
279 SCAN t8
280 }
281 do_setup_rec_test $tn.13.3 {
282 CREATE TABLE t8(a, b);
283 } {
284 SELECT * FROM t8 WHERE a=? ORDER BY rowid
285 } {
286 CREATE INDEX t8_idx_00000061 ON t8(a);
287 SEARCH t8 USING INDEX t8_idx_00000061 (a=?)
288 }
289
290 # Triggers
291 #
292 do_setup_rec_test $tn.14 {
293 CREATE TABLE t9(a, b, c);
294 CREATE TABLE t10(a, b, c);
295 CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
296 UPDATE t10 SET a=new.a WHERE b = new.b;
297 END;
298 } {
299 INSERT INTO t9 VALUES(?, ?, ?);
300 } {
301 CREATE INDEX t10_idx_00000062 ON t10(b);
302 SEARCH t10 USING INDEX t10_idx_00000062 (b=?)
303 }
304
305 do_setup_rec_test $tn.15 {
306 CREATE TABLE t1(a, b);
307 CREATE TABLE t2(c, d);
308
309 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
310 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
311
312 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
313 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
314 } {
315 SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
316 } {
317 CREATE INDEX t2_idx_00000064 ON t2(d);
318 SEARCH t2 USING INDEX t2_idx_00000064 (d=?)
319 SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
320 }
321
322 do_setup_rec_test $tn.16 {
323 CREATE TABLE t1(a, b);
324 } {
325 SELECT * FROM t1 WHERE b IS NOT NULL;
326 } {
327 (no new indexes)
328 SCAN t1
329 }
330
331 do_setup_rec_test $tn.17.1 {
332 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
333 } {
334 SELECT * FROM example WHERE a=?
335 } {
336 (no new indexes)
337 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=?)
338 }
339 do_setup_rec_test $tn.17.2 {
340 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
341 } {
342 SELECT * FROM example WHERE b=?
343 } {
344 CREATE INDEX example_idx_00000042 ON example(B);
345 SEARCH example USING INDEX example_idx_00000042 (B=?)
346 }
347 do_setup_rec_test $tn.17.3 {
348 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
349 } {
350 SELECT * FROM example WHERE a=? AND b=?
351 } {
352 (no new indexes)
353 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B=?)
354 }
355 do_setup_rec_test $tn.17.4 {
356 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
357 } {
358 SELECT * FROM example WHERE a=? AND b>?
359 } {
360 (no new indexes)
361 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B>?)
362 }
363 do_setup_rec_test $tn.17.5 {
364 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
365 } {
366 SELECT * FROM example WHERE a>? AND b=?
367 } {
368 CREATE INDEX example_idx_0000cb3f ON example(B, A);
369 SEARCH example USING INDEX example_idx_0000cb3f (B=? AND A>?)
370 }
371
372 do_setup_rec_test $tn.18.0 {
373 CREATE TABLE SomeObject (
374 a INTEGER PRIMARY KEY,
375 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL
376 );
377 } {
378 SELECT x FROM SomeObject;
379 } {
380 (no new indexes)
381 SCAN SomeObject
382 }
383 do_setup_rec_test $tn.18.1 {
384 CREATE TABLE SomeObject (
385 a INTEGER PRIMARY KEY,
386 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL
387 );
388 } {
389 SELECT * FROM SomeObject WHERE x=?;
390 } {
391 CREATE INDEX SomeObject_idx_00000078 ON SomeObject(x);
392 SEARCH SomeObject USING COVERING INDEX SomeObject_idx_00000078 (x=?)
393 }
394
395
396 do_setup_rec_test $tn.19.0 {
397 CREATE TABLE t1("index");
398 } {
399 SELECT * FROM t1 ORDER BY "index";
400 } {
401 CREATE INDEX t1_idx_01a7214e ON t1('index');
402 SCAN t1 USING COVERING INDEX t1_idx_01a7214e
403 }
404
405 ifcapable fts5 {
406 do_setup_rec_test $tn.20.0 {
407 CREATE VIRTUAL TABLE ft USING fts5(a);
408 CREATE TABLE t1(x, y);
409 } {
410 SELECT * FROM ft, t1 WHERE a=x
411 } {
412 CREATE INDEX t1_idx_00000078 ON t1(x);
413 SCAN ft VIRTUAL TABLE INDEX 0:
414 SEARCH t1 USING INDEX t1_idx_00000078 (x=?)
415 }
416 }
417
418 }
419
420 proc do_candidates_test {tn sql res} {
421 set res [squish [string trim $res]]
422
423 set expert [sqlite3_expert_new db]
424 $expert sql $sql
425 $expert analyze
426
427 set candidates [squish [string trim [$expert report 0 candidates]]]
428 $expert destroy
429
430 uplevel [list do_test $tn [list set {} $candidates] $res]
431 }
432
433
434 reset_db
435 do_execsql_test 5.0 {
436 CREATE TABLE t1(a, b);
437 CREATE TABLE t2(c, d);
438
439 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
440 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
441
442 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
443 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
444
445 CREATE INDEX i1 ON t1( lower(a) );
446 }
447 do_candidates_test 5.1 {
448 SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?)
449 } {
450 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
451 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
452 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
453 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
454 }
455
456 do_candidates_test 5.2 {
457 SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=?
458 } {
459 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17
460 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
461 }
462
463 do_execsql_test 5.3 {
464 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
465 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
466 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16
467
468 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
469 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
470 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
471
472 ANALYZE;
473 SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
474 } {
475 t1 i1 {100 50}
476 t1 t1_idx_00000061 {100 50}
477 t1 t1_idx_00000062 {100 20}
478 t1 t1_idx_000123a7 {100 50 17}
479 t2 t2_idx_00000063 {100 20}
480 t2 t2_idx_00000064 {100 5}
481 t2 t2_idx_0001295b {100 20 5}
482 }
483
484 do_catchsql_test 5.4 {
485 SELECT sqlite_expert_rem(123, 123);
486 } {1 {no such function: sqlite_expert_rem}}
487 do_catchsql_test 5.5 {
488 SELECT sqlite_expert_sample();
489 } {1 {no such function: sqlite_expert_sample}}
490
491 if 0 {
492 do_test expert1-6.0 {
493 catchcmd :memory: {
494 .expert
495 select base64('');
496 .expert
497 select name from pragma_collation_list order by name collate uint;
498 }
499 } {0 {(no new indexes)
500
501 SCAN CONSTANT ROW
502
503 (no new indexes)
504
505 SCAN pragma_collation_list VIRTUAL TABLE INDEX 0:
506 USE TEMP B-TREE FOR ORDER BY
507 }}
508 }
509
510 do_execsql_test 6.0 {
511 CREATE TABLE x1(a, b, c, d);
512 CREATE INDEX x1ab ON x1(a, lower(b));
513 CREATE INDEX x1dcba ON x1(d, b+c, a);
514 }
515
516 do_candidates_test 6.1 {
517 SELECT * FROM x1 WHERE b=? ORDER BY a;
518 } {
519 CREATE INDEX x1_idx_0001267f ON x1(b, a);
520 CREATE INDEX x1_idx_00000062 ON x1(b);
521 }
522
523 #-------------------------------------------------------------------------
524 ifcapable fts5 {
525 reset_db
526 do_execsql_test 7.0 {
527 CREATE VIRTUAL TABLE ft USING fts5(a);
528 CREATE TABLE t1(x, y);
529 }
530
531 do_candidates_test 7.1 {
532 SELECT * FROM ft, t1 WHERE a=x
533 } {
534 CREATE INDEX t1_idx_00000078 ON t1(x);
535 }
536
537 register_tcl_module db
538 proc vtab_command {method args} {
539 global G
540
541 switch -- $method {
542 xConnect {
543 return "CREATE TABLE t1(a, b, c);"
544 }
545
546 xBestIndex {
547 return [list]
548 }
549
550 xFilter {
551 return [list sql "SELECT rowid, * FROM t0"]
552 }
553 }
554
555 return {}
556 }
557
558 do_execsql_test 7.2 {
559 CREATE TABLE t0(a, b, c);
560 INSERT INTO t0 VALUES(1, 2, 3), (11, 22, 33);
561 CREATE VIRTUAL TABLE t2 USING tcl(vtab_command);
562 }
563
564 do_execsql_test 7.3 {
565 SELECT * FROM t2
566 } {
567 1 2 3
568 11 22 33
569 }
570
571 do_candidates_test 7.4 {
572 SELECT * FROM ft, t1 WHERE a=x
573 } {
574 CREATE INDEX t1_idx_00000078 ON t1(x);
575 }
576
577 do_test 7.5 {
578 set expert [sqlite3_expert_new db]
579 list [catch { $expert sql "SELECT * FROM ft, t2 WHERE b=1" } msg] $msg
580 } {1 {no such table: t2}}
581 $expert destroy
582
583 reset_db
584 do_execsql_test 7.6 {
585 BEGIN TRANSACTION;
586 CREATE TABLE IF NOT EXISTS 'bfts_idx_data'(id INTEGER PRIMARY KEY, block BLOB);
587 CREATE TABLE IF NOT EXISTS 'fts_idx_data'(id INTEGER PRIMARY KEY, block BLOB);
588 INSERT INTO fts_idx_data VALUES(1,X'');
589 INSERT INTO fts_idx_data VALUES(10,X'00000000ff000001000000');
590 CREATE TABLE IF NOT EXISTS 'fts_idx_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
591 CREATE TABLE IF NOT EXISTS 'fts_idx_docsize'(id INTEGER PRIMARY KEY, sz BLOB, origin INTEGER);
592 CREATE TABLE IF NOT EXISTS 'fts_idx_config'(k PRIMARY KEY, v) WITHOUT ROWID;
593 INSERT INTO fts_idx_config VALUES('version',4);
594 PRAGMA writable_schema=ON;
595 INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','fts_idx','fts_idx',0,'CREATE VIRTUAL TABLE fts_idx USING fts5(Title, Description, Channel, Tags, content='''', contentless_delete=1)');
596
597 CREATE TABLE f(x BLOB, y);
598 COMMIT;
599 PRAGMA writable_schema = RESET;
600 }
601
602 do_candidates_test 7.4 {
603 SELECT * FROM fts_idx, f WHERE x = fts_idx.Channel
604 } {
605 CREATE INDEX f_idx_00000078 ON f(x);
606 }
607 }
608
609 finish_test