Debian Codegraph @

test/bestindex6.test

raw
1 # 2018-09-09
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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix bestindex6
16
17 ifcapable !vtab {
18 finish_test
19 return
20 }
21
22 register_tcl_module db
23
24 proc vtab_command {src method args} {
25 switch -- $method {
26 xConnect {
27 return [db one {SELECT sql FROM sqlite_master where name = $src}]
28 }
29
30 xBestIndex {
31 set hdl [lindex $args 0]
32 set clist [$hdl constraints]
33 set orderby [$hdl orderby]
34 set mask [$hdl mask]
35
36 set wlist 1
37
38 set iCons 0
39 set ret [list]
40 foreach cons $clist {
41 catch { array unset C }
42 array set C $cons
43
44 if {$C(usable)} {
45 set col [db one {
46 SELECT name FROM pragma_table_info($src) WHERE cid=$C(column)
47 }]
48 switch $C(op) {
49 isnull {
50 lappend wlist "$col IS NULL"
51 lappend ret omit $iCons
52 }
53 eq {
54 lappend wlist "$col = %$iCons%"
55 lappend ret omit $iCons
56 }
57 }
58 }
59 incr iCons
60 }
61 #puts "xBestIndex: $ret"
62 lappend ret idxStr [join $wlist " AND "]
63 return $ret
64 }
65
66 xFilter {
67 foreach {idxnum idxstr aa} $args {}
68 set map [list]
69 for {set iCons 0} {$iCons < [llength $aa]} {incr iCons} {
70 lappend map %$iCons% [lindex $aa $iCons]
71 }
72 set ret [list sql \
73 "SELECT rowid, * FROM $src WHERE [string map $map $idxstr]"
74 ]
75 # puts "xFilter: $ret"
76 return $ret
77 }
78
79 }
80
81 return {}
82 }
83
84 do_execsql_test 1.0 {
85 CREATE TABLE t1(id int, value text);
86 CREATE TABLE t2(ctx int, id int, value text);
87
88 INSERT INTO t1 VALUES(1,'try');
89 INSERT INTO t2 VALUES(1,1,'good');
90 INSERT INTO t2 VALUES(2,2,'evil');
91
92 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
93 CREATE VIRTUAL TABLE vt2 USING tcl(vtab_command t2);
94 }
95
96 do_execsql_test 1.1 {
97 select * from t2 left join t1 on t1.id=t2.ctx where t1.value is null;
98 } {2 2 evil {} {}}
99
100 do_execsql_test 1.2 {
101 select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is null;
102 } {2 2 evil {} {}}
103
104 unset -nocomplain xxx
105 do_execsql_test 1.3 {
106 select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is $xxx;
107 } {2 2 evil {} {}}
108
109 do_execsql_test 1.4 {
110 select * from t2 left join vt1 on vt1.id=t2.ctx where vt1.value = 3
111 } {}
112
113 finish_test