Documentation Source Text

Check-in [6958f518ec]
Login

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

Overview
Comment:Update the rtree documentation to report that type affinity is ignored for auxiliary columns.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 6958f518ec48ab28acb4b8fb09163bd002d0b0135c2080a5b36fb8bd4c64ac51
User & Date: drh 2019-12-07 12:14:24.999
Context
2019-12-21
15:22
Enhance CLI documentation to talk more about reading ZIP archives as database files. (check-in: e92fb43396 user: drh tags: trunk)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/rtree.in.
︙︙
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

[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>








<
<
<
<
<








140
141
142
143
144
145
146





147
148
149
150
151
152
153

[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.






<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>
︙︙
392
393
394
395
396
397
398

399
400
401
402

403
404
405
406
407
408
409
410

   AND minX&gt;=-81.0 AND maxX&lt;=-79.6
   AND minY&gt;=35.0 AND maxY&gt;=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>

<p>
The default virtual table ("rtree") normally stores coordinates as
single-precision (4-byte) floating point numbers.  If integer coordinates








>
|
|
|
|
>
|








387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407

   AND minX&gt;=-81.0 AND maxX&lt;=-79.6
   AND minY&gt;=35.0 AND maxY&gt;=36.2;
</codeblock>)^

<h3>Limitations</h3>

<p>
For auxiliary columns, only the name of the column matters.
The [type affinity] is ignored.
Constraints such as NOT NULL, UNIQUE, REFERENCES, or CHECK
are also ignored.  However, future versions
of SQLite might start paying attention to the type affinity and
constraints, so users of auxiliary columns are advised to leave
both blank, to avoid future compatibility problems.

<tcl>hd_fragment {intrtree} {integer-valued r-trees}</tcl>
<h1>Integer-Valued R-Trees</h1>

<p>
The default virtual table ("rtree") normally stores coordinates as
single-precision (4-byte) floating point numbers.  If integer coordinates
︙︙