Documentation Source Text

Check-in [f3d8866e42]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhancements to the virtual table documentation to describe the operation of SQLITE_INDEX_CONSTRAINT_FUNCTION.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f3d8866e421408b1adcb8aab0ba28df6b8903a1946fef61f93ea7a36713deee8
User & Date: drh 2018-08-27 20:52:42.877
Context
2018-09-03
10:46
Fix a typo in the testing.html document. (check-in: fb87225a7a user: drh tags: trunk)
2018-08-27
20:52
Enhancements to the virtual table documentation to describe the operation of SQLITE_INDEX_CONSTRAINT_FUNCTION. (check-in: f3d8866e42 user: drh tags: trunk)
17:15
First cut at documentation for the Geopoly module. (check-in: a007133016 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/geopoly.in.
136
137
138
139
140
141
142

143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

173
174
175
176
177
178

179
180
181
182
183
184
185

186
187
188
189
190
191
192

193
194
195
196
197
198
199
<h1>Special Functions</h1>

<p>
The geopoly module defines several new SQL functions that are useful for
dealing with polygons.  All polygon arguments to these functions can be
either the GeoJSON format or the internal binary format.


<h2>The geopoly_overlap(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_overlap(P1,P2) function returns
true if there is any overlap between P1 and P2, or it returns false if P1 and P2
completely disjoint.
If either P1 or P2 is not a polygon, this routine returns NULL.

<p>
The geopoly_overlap(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_overlap() as a boolean function.  Only the
geopoly_overlap(P1,P2) and geopoly_within(P1,P2) functions have this
capability.


<h2>The geopoly_within(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_within(P1,P2) function returns
true if P2 is completely contained within P1, or it returns false if any part of
P2 is outside of P1.  If P1 and P2 are the same polygon, this routine returns true.
If either P1 or P2 is not a polygon, this routine returns NULL.

<p>
The geopoly_within(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_within() as a boolean function.  Only the
geopoly_within(P1,P2) and geopoly_overlap(P1,P2) functions have this
capability.


<h2>The geopoly_area(P) Function</h2>

<p>
If P is a polygon, then geopoly_area(P) returns the area enclosed by
that polygon.  If P is not a polygon, geopoly_area(P) returns NULL.


<h2>The geopoly_blob(P) Function</h2>

<p>
If P is a polygon, then geopoly_blob(P) returns the binary encoding
of that polygon as a BLOB.
If P is not a polygon, geopoly_blob(P) returns NULL.


<h2>The geopoly_json(P) Function</h2>

<p>
If P is a polygon, then geopoly_json(P) returns the GeoJSON representation
of that polygon as a TEPT string.
If P is not a polygon, geopoly_json(P) returns NULL.


<h2>The geopoly_svg(P,...) Function</h2>

<p>
If P is a polygon, then geopoly_svg(P,...) returns a text string which is a
[https://en.wikipedia.org/wiki/Scalable_Vector_Graphics|Scalable Vector Graphics (SVG)]
representation of that polygon.  If there is more one argument, then second
and subsequent arguments are added as attributes to each SVG glyph.  For example:







>















>















>






>







>







>







136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
<h1>Special Functions</h1>

<p>
The geopoly module defines several new SQL functions that are useful for
dealing with polygons.  All polygon arguments to these functions can be
either the GeoJSON format or the internal binary format.

<tcl>hd_fragment goverlap geopoly_overlap</tcl>
<h2>The geopoly_overlap(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_overlap(P1,P2) function returns
true if there is any overlap between P1 and P2, or it returns false if P1 and P2
completely disjoint.
If either P1 or P2 is not a polygon, this routine returns NULL.

<p>
The geopoly_overlap(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_overlap() as a boolean function.  Only the
geopoly_overlap(P1,P2) and geopoly_within(P1,P2) functions have this
capability.

<tcl>hd_fragment gwithin geopoly_within</tcl>
<h2>The geopoly_within(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_within(P1,P2) function returns
true if P2 is completely contained within P1, or it returns false if any part of
P2 is outside of P1.  If P1 and P2 are the same polygon, this routine returns true.
If either P1 or P2 is not a polygon, this routine returns NULL.

<p>
The geopoly_within(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_within() as a boolean function.  Only the
geopoly_within(P1,P2) and geopoly_overlap(P1,P2) functions have this
capability.

<tcl>hd_fragment garea geopoly_area</tcl>
<h2>The geopoly_area(P) Function</h2>

<p>
If P is a polygon, then geopoly_area(P) returns the area enclosed by
that polygon.  If P is not a polygon, geopoly_area(P) returns NULL.

<tcl>hd_fragment gblob geopoly_blob</tcl>
<h2>The geopoly_blob(P) Function</h2>

<p>
If P is a polygon, then geopoly_blob(P) returns the binary encoding
of that polygon as a BLOB.
If P is not a polygon, geopoly_blob(P) returns NULL.

<tcl>hd_fragment gjson geopoly_json</tcl>
<h2>The geopoly_json(P) Function</h2>

<p>
If P is a polygon, then geopoly_json(P) returns the GeoJSON representation
of that polygon as a TEPT string.
If P is not a polygon, geopoly_json(P) returns NULL.

<tcl>hd_fragment gsvg geopoly_svg</tcl>
<h2>The geopoly_svg(P,...) Function</h2>

<p>
If P is a polygon, then geopoly_svg(P,...) returns a text string which is a
[https://en.wikipedia.org/wiki/Scalable_Vector_Graphics|Scalable Vector Graphics (SVG)]
representation of that polygon.  If there is more one argument, then second
and subsequent arguments are added as attributes to each SVG glyph.  For example:
210
211
212
213
214
215
216

217
218
219
220
221
222
223

224
225
226
227
228
229
230

231
232
233
234
235
236
237
with the origin at the lower left, whereas SVG uses a left-handed coordinate
system with the origin at the upper left.  The geopoly_svg() routine makes no
attempt to transform the coordinate system, so the displayed images are shown
in mirror image and rotated.  If that is undesirable, the geopoly_xform() routine
can be used to transform the output from cartesian to SVG coordinates prior to
passing the polygons into geopoly_svg().


<h2>The geopoly_bbox(P) Function</h2>

<p>
If P is a polygon, then geopoly_bbox(P) returns the a new polygon that is
the smallest rectangle completely enclosing P.
If P is not a polygon, geopoly_bbox(P) returns NULL.


<h2>The geopoly_contains_point(P,X,Y) Function</h2>

<p>
If P is a polygon, then geopoly_contains_point(P,X,Y) returns true if and only
if the coordinate X,Y is inside or on the boundary of the polygon P.
If P is not a polygon, geopoly_contains_point(P,X,Y) returns NULL.


<h2>The geopoly_xform(P,A,B,C,D,E,F) Function</h2>

<p>
The geopoly_xform(P,A,B,C,D,E,F) returns a new polygon that is a
linear transformation of the polygon P and where the transformation
is defined by values A,B,C,D,E,F. If P is not a valid polygon, this
routine returns NULL.







>







>







>







216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
with the origin at the lower left, whereas SVG uses a left-handed coordinate
system with the origin at the upper left.  The geopoly_svg() routine makes no
attempt to transform the coordinate system, so the displayed images are shown
in mirror image and rotated.  If that is undesirable, the geopoly_xform() routine
can be used to transform the output from cartesian to SVG coordinates prior to
passing the polygons into geopoly_svg().

<tcl>hd_fragment gbbox geopoly_bbox</tcl>
<h2>The geopoly_bbox(P) Function</h2>

<p>
If P is a polygon, then geopoly_bbox(P) returns the a new polygon that is
the smallest rectangle completely enclosing P.
If P is not a polygon, geopoly_bbox(P) returns NULL.

<tcl>hd_fragment gpoint geopoly_constains_point</tcl>
<h2>The geopoly_contains_point(P,X,Y) Function</h2>

<p>
If P is a polygon, then geopoly_contains_point(P,X,Y) returns true if and only
if the coordinate X,Y is inside or on the boundary of the polygon P.
If P is not a polygon, geopoly_contains_point(P,X,Y) returns NULL.

<tcl>hd_fragment xform geopoly_xform</tcl>
<h2>The geopoly_xform(P,A,B,C,D,E,F) Function</h2>

<p>
The geopoly_xform(P,A,B,C,D,E,F) returns a new polygon that is a
linear transformation of the polygon P and where the transformation
is defined by values A,B,C,D,E,F. If P is not a valid polygon, this
routine returns NULL.
Changes to pages/vtab.in.
678
679
680
681
682
683
684

685
686
687
688
689
690
691
  #define SQLITE_INDEX_CONSTRAINT_GLOB      66  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_REGEXP    67  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_NE        68  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOT     69  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNULL    71  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_IS        72  /* 3.21.0 and later */

  #define SQLITE_INDEX_SCAN_UNIQUE           1  /* Scan visits at most 1 row */
</codeblock>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 







>







678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
  #define SQLITE_INDEX_CONSTRAINT_GLOB      66  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_REGEXP    67  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_NE        68  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOT     69  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNULL    71  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_IS        72  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_FUNCTION 150  /* 3.25.0 and later */
  #define SQLITE_INDEX_SCAN_UNIQUE           1  /* Scan visits at most 1 row */
</codeblock>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741

<p>The main thing that the SQLite core is trying to communicate to 
the virtual table is the constraints that are available to limit 
the number of rows that need to be searched. The aConstraint[] array 
contains one entry for each constraint. There will be exactly 
nConstraint entries in that array.

<p>Each constraint will correspond to a term in the WHERE clause
or in a USING or ON clause that is of the form

<blockquote>
     column  OP  EXPR
</blockquote>

<p>Where "column" is a column in the virtual table, OP is an operator 







|







728
729
730
731
732
733
734
735
736
737
738
739
740
741
742

<p>The main thing that the SQLite core is trying to communicate to 
the virtual table is the constraints that are available to limit 
the number of rows that need to be searched. The aConstraint[] array 
contains one entry for each constraint. There will be exactly 
nConstraint entries in that array.

<p>Each constraint will usually correspond to a term in the WHERE clause
or in a USING or ON clause that is of the form

<blockquote>
     column  OP  EXPR
</blockquote>

<p>Where "column" is a column in the virtual table, OP is an operator 
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778











779
780
781
782
783
784
785

<codeblock>
     x &gt;= 10
     x &lt;= 100
     y &lt; 999
</codeblock>

<p>For each constraint, the aConstraint[].iColumn field indicates which 
column appears on the left-hand side of the constraint.
The first column of the virtual table is column 0. 
The rowid of the virtual table is column -1. 
The aConstraint[].op field indicates which operator is used. 
The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
into operator values.
Columns occur in the order they were defined by the call to
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.












<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have an aConstraint[].usable flag which is true.








|









>
>
>
>
>
>
>
>
>
>
>







763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797

<codeblock>
     x &gt;= 10
     x &lt;= 100
     y &lt; 999
</codeblock>

<p>For such each constraint, the aConstraint[].iColumn field indicates which 
column appears on the left-hand side of the constraint.
The first column of the virtual table is column 0. 
The rowid of the virtual table is column -1. 
The aConstraint[].op field indicates which operator is used. 
The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
into operator values.
Columns occur in the order they were defined by the call to
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.

<p>If the [xFindFunction()] method for the virtual table is defined, and 
if xFindFunction() sometimes returns [SQLITE_INDEX_CONSTRAINT_FUNCTION] or
larger, then the constraints might also be of the form:

<blockquote>
     FUNCTION( column, EXPR)
</blockquote>

<p>In this case the aConstraint[].op value is the same as the value
returned by [xFindFunction()] for FUNCTION.

<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have an aConstraint[].usable flag which is true.

1236
1237
1238
1239
1240
1241
1242
1243






























1244
1245
1246
1247
1248
1249
1250
<p>When a function uses a column from a virtual table as its first 
argument, this method is called to see if the virtual table would 
like to overload the function. The first three parameters are inputs: 
the virtual table, the number of arguments to the function, and the 
name of the function. If no overloading is desired, this method
returns 0. To overload the function, this method writes the new 
function implementation into *pxFunc and writes user data into *ppArg 
and returns 1.































<p>Note that infix functions ([LIKE], [GLOB], [REGEXP], and [MATCH]) reverse 
the order of their arguments. So "like(A,B)" is equivalent to "B like A". 
For the form "B like A" the B term is considered the first argument 
to the function. But for "like(A,B)" the A term is considered the 
first argument.








|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
<p>When a function uses a column from a virtual table as its first 
argument, this method is called to see if the virtual table would 
like to overload the function. The first three parameters are inputs: 
the virtual table, the number of arguments to the function, and the 
name of the function. If no overloading is desired, this method
returns 0. To overload the function, this method writes the new 
function implementation into *pxFunc and writes user data into *ppArg 
and returns either 1 or a number between
[SQLITE_INDEX_CONSTRAINT_FUNCTION] and 255.

<p>Historically, the return value from xFindFunction() was either zero
or one.  Zero means that the function is not overloaded and one means that
it is overload.  The ability to return values of 
[SQLITE_INDEX_CONSTRAINT_FUNCTION] or greater was added in
version 3.25.0 ([dateof:3.25.0]).  If xFindFunction returns
[SQLITE_INDEX_CONSTRAINT_FUNCTION] or greater, than means that the function
takes two arguments and the function
can be used as a boolean in the WHERE clause of a query and that
the virtual table is able to exploit that function to speed up the query
result.  When xFindFunction returns [SQLITE_INDEX_CONSTRAINT_FUNCTION] or 
larger, the value returned becomes the sqlite3_index_info.aConstraint.op
value for one of the constraints passed into [xBestIndex()] and the second
argument becomes the value corresponding to that constraint that is passed
to [xFilter()].  This enables the
xBestIndex()/xFilter implementations to use the function to speed
its search.

<p>The technique of having xFindFunction() return values of
[SQLITE_INDEX_CONSTRAINT_FUNCTION] was initially used in the implementation
of the [Geopoly module].  The xFindFunction() method of that module returns
SQLITE_INDEX_CONSTRAINT_FUNCTION for the [geopoly_overlap()] SQL function
and it returns
SQLITE_INDEX_CONSTRAINT_FUNCTION+1 for the [geopoly_within()] SQL function.
This permits search optimizations for queries such as:

<codeblock>
SELECT * FROM geopolytab WHERE geopoly_overlap(_shape, $query_polygon);
</codeblock>

<p>Note that infix functions ([LIKE], [GLOB], [REGEXP], and [MATCH]) reverse 
the order of their arguments. So "like(A,B)" is equivalent to "B like A". 
For the form "B like A" the B term is considered the first argument 
to the function. But for "like(A,B)" the A term is considered the 
first argument.