Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the rtree documentation with more detail about the rules for column names and the affinity of columns in the rtree virtual table. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
8199b59a39c017c7caffc49b05895005 |
User & Date: | drh 2019-12-06 13:08:36 |
Context
2019-12-07
| ||
12:14 | Update the rtree documentation to report that type affinity is ignored for auxiliary columns. (check-in: 6958f518ec user: drh tags: trunk) | |
2019-12-06
| ||
13:08 | Enhance the rtree documentation with more detail about the rules for column names and the affinity of columns in the rtree virtual table. (check-in: 8199b59a39 user: drh tags: trunk) | |
02:35 | Enhance the rtree auxiliary column documentation to point out that constraints such as NOT NULL on auxiliary columns are silently ignored. SQLite ticket https://www.sqlite.org/src/info/8bf76328ac940d52 (check-in: c3ab325994 user: drh tags: trunk) | |
Changes
Changes to pages/rtree.in.
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
|
CREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX, -- Minimum and maximum X coordinate minY, maxY -- Minimum and maximum Y coordinate ); </codeblock> <h2>Populating An R*Tree Index</h2> <p> ^The usual [INSERT], [UPDATE], and [DELETE] commands work on an R*Tree index just like on regular tables. ^(So to insert some data into our sample R*Tree index, we can do something like this: </p> ................................................................................ AND minX>=-81.0 AND maxX<=-79.6 AND minY>=35.0 AND maxY>=36.2; </codeblock>)^ <h3>Limitations</h3> <p> Constraints on auxiliary columns such as NOT NULL, UNIQUE, REFERENCES, or CHECK are silently ignored in the current implementation. However, future versions of SQLite might start enforcing one or more of those constraint types. Avoid using constraints on auxiliary columns to prevent future compatibility problems. <tcl>hd_fragment {intrtree} {integer-valued r-trees}</tcl> <h1>Integer-Valued R-Trees</h1> |
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
...
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
|
CREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX, -- Minimum and maximum X coordinate minY, maxY -- Minimum and maximum Y coordinate ); </codeblock> <h3>Column naming details</h3> <p> ^(In the argments to "rtree" in the CREATE VIRTUAL TABLE statement, the names of the columns are taken from the first token of each argument. All subsequent tokens within each argument are silently ignored.)^ This means, for example, that if you try to give a column a [type affinity] or add a constraint such as UNIQUE or NOT NULL or DEFAULT to a column, those extra tokens are accepted as valid, but they do not change the behavior of the rtree. ^(In an RTREE virtual table, the first column always has a [type affinity] of INTEGER and all other data columns have a [type affinity] of NUMERIC.)^ <p> Recommended practice is to omit any extra tokens in the rtree specification. Let each argument to "rtree" be a single ordinary label that is the name of the corresponding column, and omit all other tokens from the argument list. <p> Slightly different rules apply to to <a href="#auxcol">auxiliary columns</a>. The [type affinity] for an auxiliary column specified in the CREATE VIRTUAL TABLE statement is honored, but other constraints on still ignored. <h2>Populating An R*Tree Index</h2> <p> ^The usual [INSERT], [UPDATE], and [DELETE] commands work on an R*Tree index just like on regular tables. ^(So to insert some data into our sample R*Tree index, we can do something like this: </p> ................................................................................ AND minX>=-81.0 AND maxX<=-79.6 AND minY>=35.0 AND maxY>=36.2; </codeblock>)^ <h3>Limitations</h3> <p> The [type affinity] of auxiliary columns is honored. However, constraints on auxiliary columns such as NOT NULL, UNIQUE, REFERENCES, or CHECK are silently ignored in the current implementation. However, future versions of SQLite might start enforcing one or more of those constraint types. Avoid using constraints on auxiliary columns to prevent future compatibility problems. <tcl>hd_fragment {intrtree} {integer-valued r-trees}</tcl> <h1>Integer-Valued R-Trees</h1> |