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: |
f3d8866e421408b1adcb8aab0ba28df6 |
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
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 | <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. | | | 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 | <codeblock> x >= 10 x <= 100 y < 999 </codeblock> | | > > > > > > > > > > > | 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 >= 10 x <= 100 y < 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 | <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 | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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. |
︙ | ︙ |