Debian Codegraph @

test/bestindex5.test

raw
1 # 2017 September 10
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 # Test the virtual table interface. In particular the xBestIndex
12 # method.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix bestindex5
18
19 ifcapable !vtab {
20 finish_test
21 return
22 }
23
24 #-------------------------------------------------------------------------
25 # Virtual table callback for a virtual table named $tbl.
26 #
27 proc vtab_cmd {method args} {
28
29 set binops(ne) !=
30 set binops(eq) =
31 set binops(isnot) "IS NOT"
32 set binops(is) "IS"
33
34 set unops(isnotnull) "IS NOT NULL"
35 set unops(isnull) "IS NULL"
36
37 set cols(0) a
38 set cols(1) b
39 set cols(2) c
40
41 switch -- $method {
42 xConnect {
43 return "CREATE TABLE t1(a, b, c)"
44 }
45
46 xBestIndex {
47 set hdl [lindex $args 0]
48 set clist [$hdl constraints]
49 set orderby [$hdl orderby]
50 set mask [$hdl mask]
51
52 set cost 1000000.0
53 set ret [list]
54 set str [list]
55
56 set v 0
57 for {set i 0} {$i < [llength $clist]} {incr i} {
58 array unset C
59 array set C [lindex $clist $i]
60 if {$C(usable)} {
61 if {[info exists binops($C(op))]} {
62 lappend ret omit $i
63 lappend str "$cols($C(column)) $binops($C(op)) %$v%"
64 incr v
65 set cost [expr $cost / 2]
66 }
67 if {[info exists unops($C(op))]} {
68 lappend ret omit $i
69 lappend str "$cols($C(column)) $unops($C(op))"
70 incr v
71 set cost [expr $cost / 2]
72 }
73 }
74 }
75
76 lappend ret idxstr [join $str " AND "]
77 lappend ret cost $cost
78 return $ret
79 }
80
81 xFilter {
82 set q [lindex $args 1]
83 set a [lindex $args 2]
84 for {set v 0} {$v < [llength $a]} {incr v} {
85 set val [lindex $a $v]
86 set q [string map [list %$v% '$val'] $q]
87 }
88 if {$q==""} { set q 1 }
89 lappend ::xFilterQueries "WHERE $q"
90 return [list sql "SELECT rowid, * FROM t1x WHERE $q"]
91 }
92 }
93 return ""
94 }
95
96 proc vtab_simple {method args} {
97 switch -- $method {
98 xConnect {
99 return "CREATE TABLE t2(x)"
100 }
101 xBestIndex {
102 return [list cost 999999.0]
103 }
104 xFilter {
105 return [list sql "SELECT rowid, * FROM t2x"]
106 }
107 }
108 return ""
109 }
110
111 register_tcl_module db
112
113 proc do_vtab_query_test {tn query result} {
114 set ::xFilterQueries [list]
115 uplevel [list
116 do_test $tn [string map [list %QUERY% $query] {
117 set r [execsql {%QUERY%}]
118 set r [concat $::xFilterQueries $r]
119 set r
120 }] [list {*}$result]
121 ]
122 }
123
124 do_execsql_test 1.0 {
125 CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd');
126 CREATE TABLE t1x(a INTEGER, b TEXT, c REAL);
127 INSERT INTO t1x VALUES(1, 2, 3);
128 INSERT INTO t1x VALUES(4, 5, 6);
129 INSERT INTO t1x VALUES(7, 8, 9);
130
131 CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple');
132 CREATE TABLE t2x(x INTEGER);
133 INSERT INTO t2x VALUES(1);
134 }
135
136 do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } {
137 "WHERE a != 'hello'"
138 1 2 3.0 4 5 6.0 7 8 9.0
139 }
140
141 do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } {
142 "WHERE b != '8'"
143 1 2 3.0 4 5 6.0
144 }
145 do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } {
146 "WHERE b != '8'"
147 1 2 3.0 4 5 6.0
148 }
149
150 do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } {
151 "WHERE c IS NOT '3'"
152 4 5 6.0 7 8 9.0
153 }
154 do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } {
155 "WHERE c IS NOT '3'"
156 4 5 6.0 7 8 9.0
157 }
158
159 do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } {
160 "WHERE a != '1'"
161 4 5 6.0 1 7 8 9.0 1
162 }
163 do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } {
164 "WHERE a != '1'"
165 4 5 6.0 1 7 8 9.0 1
166 }
167
168 do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } {
169 "WHERE a IS NOT NULL"
170 1 2 3.0 4 5 6.0 7 8 9.0
171 }
172 do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } {
173 "WHERE a IS NOT ''"
174 1 2 3.0 4 5 6.0 7 8 9.0
175 }
176
177 do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } {
178 "WHERE a IS NULL"
179 }
180
181 do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } {
182 "WHERE a IS ''"
183 }
184
185 do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } {
186 "WHERE a IS '1' AND b IS '2'"
187 1 2 3.0
188 }
189 do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
190 {WHERE b IS '5' AND a IS '4'}
191 4 5 6.0
192 }
193
194 #---------------------------------------------------------------------
195 do_execsql_test 2.0.0 {
196 DELETE FROM t1x;
197 INSERT INTO t1x VALUES('a', 'b', 'c');
198 }
199 do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c}
200 do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {}
201
202 do_execsql_test 2.1.0 {
203 DELETE FROM t1x;
204 INSERT INTO t1x VALUES(7, 8, 9);
205 }
206 do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0}
207 do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {}
208 do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
209 do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
210
211
212 do_execsql_test 2.2.1 {
213 CREATE TABLE t3(a INTEGER, b TEXT);
214 INSERT INTO t3 VALUES(45, 46);
215 }
216 do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); }
217 do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); }
218 do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46}
219 do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46}
220
221 #---------------------------------------------------------------------
222 # Test the != operator on a virtual table with column affinities.
223 #
224 proc vtab_simple_integer {method args} {
225 switch -- $method {
226 xConnect {
227 return "CREATE TABLE t4(x INTEGER)"
228 }
229 xBestIndex {
230 return [list cost 999999.0]
231 }
232 xFilter {
233 return [list sql "SELECT rowid, * FROM t4x"]
234 }
235 }
236 return ""
237 }
238
239 do_execsql_test 3.0 {
240 CREATE TABLE t4x(a INTEGER);
241 INSERT INTO t4x VALUES(245);
242 CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer');
243 }
244 do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245}
245 do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245}
246 do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {}
247 do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {}
248
249 do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {}
250
251
252 finish_test