Debian Codegraph @

ext/rtree/rtreedoc2.test

raw
1 # 2021 September 13
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 # The focus of this file is testing the r-tree extension.
13 #
14
15 if {![info exists testdir]} {
16 set testdir [file join [file dirname [info script]] .. .. test]
17 }
18 source [file join [file dirname [info script]] rtree_util.tcl]
19 source $testdir/tester.tcl
20 set testprefix rtreedoc2
21
22 ifcapable !rtree {
23 finish_test
24 return
25 }
26
27 #-------------------------------------------------------------------------
28 #-------------------------------------------------------------------------
29 # Section 6 of documentation.
30 #-------------------------------------------------------------------------
31 #-------------------------------------------------------------------------
32 set testprefix rtreedoc2-1
33
34 # EVIDENCE-OF: R-35254-48865 A call to one of the above APIs creates a
35 # new SQL function named by the second parameter (zQueryFunc or zGeom).
36 #
37 # [register_circle_geom db] registers new geometry callback "Qcircle"
38 # and legacy implementation "circle". Test that these do actually appear.
39 #
40 do_execsql_test 1.1.0 {
41 SELECT * FROM pragma_function_list WHERE name IN('circle', 'qcircle');
42 } {
43 }
44 do_test 1.1 {
45 register_circle_geom db
46 } {SQLITE_OK}
47 do_execsql_test 1.1.2 {
48 SELECT * FROM pragma_function_list WHERE name = 'circle' AND enc='utf8';
49 } {
50 circle 0 s utf8 -1 0
51 }
52 do_execsql_test 1.1.3 {
53 SELECT * FROM pragma_function_list WHERE name = 'qcircle' AND enc='utf8';
54 } {
55 qcircle 0 s utf8 -1 0
56 }
57
58 do_execsql_test 1.2.0 { SELECT circle(1, 2, 3); } {{}}
59 do_execsql_test 1.2.1 { SELECT qcircle(1, 2, 3); } {{}}
60
61 # EVIDENCE-OF: R-61427-46983
62 do_execsql_test 1.3.0 {
63 CREATE VIRTUAL TABLE demo_index USING rtree(id, x1,x2, y1,y2);
64 INSERT INTO demo_index VALUES(10, 45,45, 24,24);
65 INSERT INTO demo_index VALUES(20, 50,50, 28,28);
66 INSERT INTO demo_index VALUES(30, 43,43, 22,22);
67 }
68 do_execsql_test 1.3.1 {
69 SELECT id FROM demo_index WHERE id MATCH circle(45.3, 22.9, 5.0)
70 } {10 30}
71
72 # EVIDENCE-OF: R-16907-50223 The SQL syntax for custom queries is the
73 # same regardless of which interface, sqlite3_rtree_geometry_callback()
74 # or sqlite3_rtree_query_callback(), is used to register the SQL
75 # function.
76 do_execsql_test 1.3.2 {
77 SELECT id FROM demo_index WHERE id MATCH qcircle(45.3, 22.9, 5.0, 1)
78 } {10 30}
79
80
81 # EVIDENCE-OF: R-59634-51678 When that SQL function appears on the
82 # right-hand side of the MATCH operator and the left-hand side of the
83 # MATCH operator is any column in the R*Tree virtual table, then the
84 # callback defined by the third argument (xQueryFunc or xGeom) is
85 # invoked to determine if a particular object or subtree overlaps the
86 # desired region.
87 proc box_geom {args} {
88 lappend ::box_geom [concat [lindex $args 0] [lrange $args 2 end-1]]
89 return ""
90 }
91 register_box_geom db box_geom
92 set box_geom [list]
93 do_execsql_test 1.3.2 {
94 SELECT id FROM demo_index WHERE id MATCH box(43,46, 21,25);
95 } {10 30}
96 do_test 1.3.3 {
97 set ::box_geom
98 } [list {*}{
99 {box {43.0 46.0 21.0 25.0} {45.0 45.0 24.0 24.0}}
100 {box {43.0 46.0 21.0 25.0} {50.0 50.0 28.0 28.0}}
101 {box {43.0 46.0 21.0 25.0} {43.0 43.0 22.0 22.0}}
102 }]
103
104 #-------------------------------------------------------------------------
105 #-------------------------------------------------------------------------
106 # Section 6 of documentation.
107 #-------------------------------------------------------------------------
108 #-------------------------------------------------------------------------
109 set testprefix rtreedoc2-2
110
111 # EVIDENCE-OF: R-02424-24769 The second argument is the number of
112 # coordinates in each r-tree entry, and is always the same for any given
113 # R*Tree.
114 #
115 # EVIDENCE-OF: R-40260-16838 The number of coordinates is 2 for a
116 # 1-dimensional R*Tree, 4 for a 2-dimensional R*Tree, 6 for a
117 # 3-dimensional R*Tree, and so forth.
118 #
119 # The second argument referred to above is the length of the list passed
120 # as the 3rd parameter to the Tcl script.
121 #
122 do_execsql_test 1.0 {
123 CREATE VIRTUAL TABLE rt1 USING rtree(id, x1,x2);
124 CREATE VIRTUAL TABLE rt2 USING rtree(id, x1,x2, y1,y2);
125 CREATE VIRTUAL TABLE rt3 USING rtree(id, x1,x2, y1,y2, z1,z2);
126
127 INSERT INTO rt1 DEFAULT VALUES;
128 INSERT INTO rt2 DEFAULT VALUES;
129 INSERT INTO rt3 DEFAULT VALUES;
130 }
131 foreach {tn tbl nCoord} {
132 1 rt1 2
133 2 rt2 4
134 3 rt3 6
135 } {
136 set ::box_geom [list]
137 do_catchsql_test 1.$tn.1 "
138 SELECT id FROM $tbl WHERE id MATCH box();
139 " {1 {SQL logic error}}
140
141 do_test 1.$tn.2 {
142 llength [lindex $::box_geom 0 2]
143 } $nCoord
144 }
145
146 # EVIDENCE-OF: R-28051-48608 If xGeom returns anything other than
147 # SQLITE_OK, then the r-tree query will abort with an error.
148 proc box_geom {args} {
149 error "an error!"
150 }
151 do_catchsql_test 2.0 {
152 SELECT * FROM rt2 WHERE id MATCH box(22,23, 24,25);
153 } {1 {SQL logic error}}
154
155 do_execsql_test 3.0 {
156 INSERT INTO rt1 VALUES(10, 10, 10);
157 INSERT INTO rt1 VALUES(11, 11, 11);
158 INSERT INTO rt1 VALUES(12, 12, 12);
159 INSERT INTO rt1 VALUES(13, 13, 13);
160 INSERT INTO rt1 VALUES(14, 14, 14);
161 }
162
163 # EVIDENCE-OF: R-53759-57366 The exact same sqlite3_rtree_geometry
164 # structure is used for every callback for same MATCH operator in the
165 # same query.
166 proc box_geom {args} {
167 lappend ::ptr_list [lindex $args 4]
168 return 0
169 }
170 set ::ptr_list [list]
171 do_execsql_test 3.1 {
172 SELECT * FROM rt1 WHERE id MATCH box(1,1);
173 }
174 do_test 3.2 {
175 set val [lindex $::ptr_list 0]
176 foreach p $::ptr_list {
177 if {$p!=$val} {error "pointer mismatch"}
178 }
179 } {}
180
181 # EVIDENCE-OF: R-60247-35692 The contents of the sqlite3_rtree_geometry
182 # structure are initialized by SQLite but are not subsequently modified.
183 proc box_geom {args} {
184 lappend ::box_geom [concat [lindex $args 0] [lrange $args 2 end-1]]
185 if {[llength $::box_geom]==3} {
186 return "zero"
187 }
188 return ""
189 }
190 set ::box_geom [list]
191 do_catchsql_test 3.2 {
192 SELECT * FROM rt1 WHERE id MATCH box(1,1);
193 } {1 {SQL logic error}}
194 do_test 3.3 {
195 set ::box_geom
196 } [list {*}{
197 {box {1.0 1.0} {0.0 0.0}}
198 {box {1.0 1.0} {10.0 10.0}}
199 {box {1.0 1.0} {11.0 11.0}}
200 {box 0.0 {12.0 12.0}}
201 }]
202
203 # EVIDENCE-OF: R-31246-29731 The pContext member of the
204 # sqlite3_rtree_geometry structure is always set to a copy of the
205 # pContext argument passed to sqlite3_rtree_geometry_callback() when the
206 # callback is registered.
207 reset_db
208 do_execsql_test 4.0 {
209 CREATE VIRTUAL TABLE r1 USING rtree(id, minX,maxX, minY,maxY);
210 WITH s(i) AS (
211 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<120
212 )
213 INSERT INTO r1 SELECT i,i,i+1, 200,201 FROM s;
214 }
215 set ctx [register_box_geom db box_geom]
216 set ::box_geom [list]
217 proc box_geom {args} {
218 lappend ::box_geom [lindex $args 1]
219 return ""
220 }
221 do_execsql_test 4.1 {
222 SELECT count(*) FROM r1 WHERE id MATCH box(0,150,199,201)
223 } 120
224 do_test 4.2 {
225 foreach g $::box_geom {
226 if {$g!=$ctx} {error "pointer mismatch"}
227 }
228 } {}
229
230 # EVIDENCE-OF: R-09904-19077 The aParam[] array (size nParam) contains
231 # the parameter values passed to the SQL function on the right-hand side
232 # of the MATCH operator.
233 proc box_geom {args} {
234 set ::box_geom [lindex $args 2]
235 }
236 foreach {tn q vals} {
237 1 "SELECT count(*) FROM r1 WHERE id MATCH box(1,2,3)" {1.0 2.0 3.0}
238 2 "SELECT count(*) FROM r1 WHERE id MATCH box(10001)" {10001.0}
239 3 "SELECT count(*) FROM r1 WHERE id MATCH box(-10001)" {-10001.0}
240 } {
241 do_catchsql_test 5.$tn.1 $q {1 {SQL logic error}}
242 do_test 5.$tn.2 { set ::box_geom } $vals
243 }
244
245 do_execsql_test 5.0 {
246 CREATE VIRTUAL TABLE myrtree USING rtree(id, x1,x2);
247 INSERT INTO myrtree VALUES(1, 1, 1);
248 INSERT INTO myrtree VALUES(2, 2, 2);
249 INSERT INTO myrtree VALUES(3, 3, 3);
250 }
251
252 # EVIDENCE-OF: R-44448-00687 The pUser and xDelUser members of the
253 # sqlite3_rtree_geometry structure are initially set to NULL.
254 set ::box_geom_calls 0
255 proc box_geom {args} {
256 incr ::box_geom_calls
257 return user_is_zero
258 }
259 do_execsql_test 5.1.1 {
260 SELECT * FROM myrtree WHERE id MATCH box(4, 5);
261 }
262 do_test 5.1.2 { set ::box_geom_calls } 3
263
264
265 # EVIDENCE-OF: R-55837-00155 The pUser variable may be set by the
266 # callback implementation to any arbitrary value that may be useful to
267 # subsequent invocations of the callback within the same query (for
268 # example, a pointer to a complicated data structure used to test for
269 # region intersection).
270 #
271 # EVIDENCE-OF: R-34745-08839 If the xDelUser variable is set to a
272 # non-NULL value, then after the query has finished running SQLite
273 # automatically invokes it with the value of the pUser variable as the
274 # only argument.
275 #
276 set ::box_geom_calls 0
277 proc box_geom {args} {
278 incr ::box_geom_calls
279 switch -- $::box_geom_calls {
280 1 {
281 return user_is_zero
282 }
283 2 {
284 return [list user box_geom_finalizer]
285 }
286 }
287 return ""
288 }
289 proc box_geom_finalizer {} {
290 set ::box_geom_finalizer "::box_geom_calls is $::box_geom_calls"
291 }
292 do_execsql_test 5.1.1 {
293 SELECT * FROM myrtree WHERE id MATCH box(4, 5);
294 }
295 do_test 5.1.2 { set ::box_geom_calls } 3
296 do_test 5.1.3 {
297 set ::box_geom_finalizer
298 } {::box_geom_calls is 3}
299
300
301 # EVIDENCE-OF: R-28176-28813 The xGeom callback always does a
302 # depth-first search of the r-tree.
303 #
304 # For a breadth first search, final test case would return "B L" only.
305 #
306 do_execsql_test 6.0 {
307 CREATE VIRTUAL TABLE xyz USING rtree(x, x1,x2, y1,y2);
308 WITH s(i) AS (
309 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<15
310 )
311 INSERT INTO xyz SELECT NULL, one.i,one.i+1, two.i,two.i+1 FROM s one, s two;
312 }
313 do_execsql_test 6.1 {
314 SELECT count(*) FROM xyz_node
315 } {10}
316 proc box_geom {args} {
317 set coords [lindex $args 3]
318 set area [expr {
319 ([lindex $coords 1]-[lindex $coords 0]) *
320 ([lindex $coords 3]-[lindex $coords 2])
321 }]
322 if {$area==1} {
323 lappend ::box_geom_calls L
324 } else {
325 lappend ::box_geom_calls B
326 }
327 }
328 set ::box_geom_calls [list]
329 do_execsql_test 6.2 {
330 SELECT count(*) FROM xyz WHERE x MATCH box(0,20,0,20)
331 } 225
332 do_test 6.3 {
333 set prev ""
334 set box_calls [list]
335 foreach c $::box_geom_calls {
336 if {$c!=$prev} {
337 lappend ::box_calls $c
338 set prev $c
339 }
340 }
341 set ::box_calls
342 } {B L B L B L B L B L B L B L B L B L}
343
344
345 finish_test
346