Debian Codegraph @

test/bestindexE.test

raw
1 # 2025-09-02
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix bestindexE
17
18 ifcapable !vtab {
19 finish_test
20 return
21 }
22
23 register_tcl_module db
24
25 proc pretty_constraint {lCol cons} {
26 array set C $cons
27
28 set ret ""
29 if {$C(usable)} {
30 set OP(eq) =
31 set ret "[lindex $lCol $C(column)]$OP($C(op))?"
32 }
33
34 return $ret
35 }
36
37 proc vtab_command {zName lCol method args} {
38 switch -- $method {
39 xConnect {
40 return "CREATE TABLE $zName ([join $lCol ,]) "
41 }
42
43 xBestIndex {
44 set hdl [lindex $args 0]
45 set conslist [$hdl constraints]
46
47 set ret [list]
48 foreach cons $conslist {
49 set pretty [pretty_constraint $lCol $cons]
50 if {$pretty != ""} { lappend ret $pretty }
51 }
52
53 lappend ::xBestIndex "$zName: [join $ret { AND }]"
54
55 return "cost 1000 rows 1000 idxnum 555"
56 }
57 }
58
59 return {}
60 }
61
62 proc do_bestindex_test {tn sql lCons} {
63 set ::xBestIndex [list]
64 do_execsql_test $tn.1 $sql
65 uplevel [list do_test $tn.2 [list set ::xBestIndex] [list {*}$lCons]]
66 }
67
68 proc create_vtab {tname clist} {
69 set cmd [list vtab_command $tname $clist]
70 execsql "
71 CREATE VIRTUAL TABLE $tname USING tcl('$cmd')
72 "
73 }
74
75 do_test 1.0 {
76 create_vtab x1 {a b c}
77 } {}
78
79 do_bestindex_test 1.1 {
80 SELECT * FROM x1 WHERE a=?
81 } {{x1: a=?}}
82
83 do_bestindex_test 1.2 {
84 SELECT * FROM x1 WHERE a=? AND b=?
85 } {{x1: a=? AND b=?}}
86
87 #--------------------------------------------------------------------------
88 reset_db
89 register_tcl_module db
90
91 do_test 2.0 {
92 create_vtab Delivery {id customer}
93 create_vtab ReturnDelivery {id customer}
94 create_vtab Customer {oid name}
95 } {}
96
97 do_bestindex_test 2.1 {
98 SELECT Delivery.ID, Customer.Name
99 FROM Delivery LEFT JOIN
100 Customer ON Delivery.Customer = Customer.OID
101 } {
102 {Delivery: }
103 {Customer: oid=?}
104 }
105
106 do_bestindex_test 2.2 {
107 SELECT * FROM
108 (
109 SELECT Delivery.ID, Customer.Name
110 FROM Delivery LEFT JOIN
111 Customer ON Delivery.Customer = Customer.OID
112
113 UNION
114
115 SELECT ReturnDelivery.ID, Customer.Name
116 FROM ReturnDelivery LEFT JOIN
117 Customer ON ReturnDelivery.Customer = Customer.OID
118 )
119 WHERE ID = 1
120 } {
121 {Delivery: id=?}
122 {Customer: oid=?}
123 {ReturnDelivery: id=?}
124 {Customer: oid=?}
125 }
126
127 #--------------------------------------------------------------------------
128 reset_db
129 register_tcl_module db eponymous_cmd
130
131 proc eponymous_cmd {method args} {
132 switch -- $method {
133 xConnect {
134 db eval { SELECT * FROM sqlite_schema }
135 return "CREATE TABLE t1 (a, b)"
136 }
137
138 xBestIndex {
139 return "idxnum 555"
140 }
141
142 xFilter {
143 return [list sql {SELECT 123, 'A', 'B'}]
144 }
145
146 xUpdate {
147 return 123
148 }
149
150 }
151
152 return {}
153 }
154
155 do_execsql_test 3.1.0 {
156 PRAGMA table_info = tcl
157 } {
158 0 a {} 0 {} 0 1 b {} 0 {} 0
159 }
160 do_execsql_test 3.1.1 {
161 SELECT rowid, * FROM tcl
162 } {123 A B}
163 do_execsql_test 3.1.2 {
164 INSERT INTO tcl VALUES('i', 'ii') RETURNING *;
165 } {i ii}
166 do_test 3.1.3 {
167 db last_insert_rowid
168 } {123}
169
170 do_execsql_test 3.1.4 {
171 CREATE TABLE x1(x);
172 }
173
174 db close
175 sqlite3 db test.db
176 register_tcl_module db eponymous_cmd
177 sqlite3 db2 test.db
178
179 # Load the schema into connection [db]
180 do_execsql_test 3.2.1 { SELECT * FROM x1 }
181
182 # Modify the schema on disk
183 do_execsql_test -db db2 3.2.2 { CREATE TABLE x2(x) }
184
185 # Insert with RETURNING trigger on eponymous table.
186 do_execsql_test 3.2.3 {
187 INSERT INTO tcl VALUES('i', 'ii') RETURNING *;
188 } {i ii}
189
190 finish_test
191