Documentation Source Text
Check-in [84efe9e646ab955aef9afa50a6e63c7917d555bd]
Not logged in

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

Overview
SHA1 Hash:84efe9e646ab955aef9afa50a6e63c7917d555bd
Date: 2014-05-27 10:31:29
User: drh
Comment:Fix documentation typos reported on the mailing list.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in

28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<li>Added the [sqlite3_rtree_query_callback()] interface to [R-Tree extension]
<li>Added the [SQLITE_IOCAP_IMMUTABLE] bit to the set of bits that can be returned by
    the xDeviceCharacteristics method of a [VFS].
<li>Added new [URI query parameters] "nolock" and "immutable".
<li>Use less memory by not remembering CHECK constraints on read-only
    database connections.
<li>Enable the [or-connected-terms | OR optimization] for [WITHOUT ROWID] tables.
query<p><b>Bug Fixes:</b>
<li>OFFSET clause ignored on queries without a FROM clause.
    Ticket [http://www.sqlite.org/src/info/07d6a0453d | 07d6a0453d]
<li>Assertion fault on queries involving expressions of the form
    "x IN (?)".  Ticket [http://www.sqlite.org/src/info/e39d032577|e39d032577].
<li>Incorrect column datatype reported.
    Ticket [http://www.sqlite.org/src/info/a8a0d2996a | a8a0d2996a]
<li>Duplicate row returned on a query against a table with more than







|







28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<li>Added the [sqlite3_rtree_query_callback()] interface to [R-Tree extension]
<li>Added the [SQLITE_IOCAP_IMMUTABLE] bit to the set of bits that can be returned by
    the xDeviceCharacteristics method of a [VFS].
<li>Added new [URI query parameters] "nolock" and "immutable".
<li>Use less memory by not remembering CHECK constraints on read-only
    database connections.
<li>Enable the [or-connected-terms | OR optimization] for [WITHOUT ROWID] tables.
<p><b>Bug Fixes:</b>
<li>OFFSET clause ignored on queries without a FROM clause.
    Ticket [http://www.sqlite.org/src/info/07d6a0453d | 07d6a0453d]
<li>Assertion fault on queries involving expressions of the form
    "x IN (?)".  Ticket [http://www.sqlite.org/src/info/e39d032577|e39d032577].
<li>Incorrect column datatype reported.
    Ticket [http://www.sqlite.org/src/info/a8a0d2996a | a8a0d2996a]
<li>Duplicate row returned on a query against a table with more than

Changes to pages/fts3.in

630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
....
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
    More than one NEAR operator may appear in a single query. In this case each
    pair of terms or phrases separated by a NEAR operator must appear within the
    specified proximity of each other in the document. Using the same table and
    data as in the block of examples above:
</ul>

<codeblock> 
 <i>-- The following query selects documents that contains an instance of the term </i>
  <i>-- "sqlite" separated by two or fewer terms from an instance of the term "acid",</i>
  <i>-- which is in turn separated by two or fewer terms from an instance of the term</i>
  <i>-- "relational". As it happens, the only document in table docs satisfies this criteria.</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';

  <i>-- This query matches no documents. There is an instance of the term "sqlite" with</i>
  <i>-- sufficient proximity to an instance of "acid" but it is not sufficiently close</i>
  <i>-- to an instance of the term "relational".</i>
  SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
</codeblock>
................................................................................

<codeblock>
    <i>-- Create a table that uses the unicode61 tokenizer, but considers "."</i>
    <i>-- and "=" characters to be part of tokens, and capital "X" characters to</i>
    <i>-- function as separators.</i>
    CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "tokenchars=.=" "separators=X");

    <i>-- Create a tables that considers space characters (codepoint 32) to be</i>
    <i>-- a token character</i>
    CREATE VIRTUAL TABLE txt4 USING fts4(tokenize=unicode61 "tokenchars= ");
</codeblock>

<p>
  If a character specified as part of the argument to "tokenchars=" is considered
  to be a token character by default, it is ignored. This is true even if it has







|


|







 







|







630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
....
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
    More than one NEAR operator may appear in a single query. In this case each
    pair of terms or phrases separated by a NEAR operator must appear within the
    specified proximity of each other in the document. Using the same table and
    data as in the block of examples above:
</ul>

<codeblock> 
  <i>-- The following query selects documents that contains an instance of the term </i>
  <i>-- "sqlite" separated by two or fewer terms from an instance of the term "acid",</i>
  <i>-- which is in turn separated by two or fewer terms from an instance of the term</i>
  <i>-- "relational".</i>
  SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';

  <i>-- This query matches no documents. There is an instance of the term "sqlite" with</i>
  <i>-- sufficient proximity to an instance of "acid" but it is not sufficiently close</i>
  <i>-- to an instance of the term "relational".</i>
  SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
</codeblock>
................................................................................

<codeblock>
    <i>-- Create a table that uses the unicode61 tokenizer, but considers "."</i>
    <i>-- and "=" characters to be part of tokens, and capital "X" characters to</i>
    <i>-- function as separators.</i>
    CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "tokenchars=.=" "separators=X");

    <i>-- Create a table that considers space characters (codepoint 32) to be</i>
    <i>-- a token character</i>
    CREATE VIRTUAL TABLE txt4 USING fts4(tokenize=unicode61 "tokenchars= ");
</codeblock>

<p>
  If a character specified as part of the argument to "tokenchars=" is considered
  to be a token character by default, it is ignored. This is true even if it has

Changes to pages/queryplanner.in

316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
<p>
A multi-column index follows the same pattern as a single-column index;
the indexed columns are added in front of the rowid.  The only difference
is that now multiple columns are added.  The left-most column is the
primary key used for ordering the rows in the index.  The second column is
used to break ties in the left-most column.  If there were a third column,
it would be used to break ties for the first two columns.  And so forth for
as many columns as their are in the index.  Because rowid is guaranteed
to be unique, every row of the index will be unique even if all of the
content columns for two rows are the same.  That case does not happen
in our sample data, but there is one case (fruit='Orange') where there
is a tie on the first column which must be broken by the second column.
</p>

<p>
................................................................................
so that the states will appear in descending order.
</p>

<tcl>hd_fragment {partialsort} {partial sorting by index}</tcl>
<h3>3.2 Partial Sorting Using An Index</h3>

<p>
Sometimes only part of an ORDER BY clause an be satisfied using indexes.
Consider, for example, the following query:
</p>

<tcl>
code {SELECT * FROM fruitforsale ORDER BY fruit, price}
</tcl>








|







 







|







316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
<p>
A multi-column index follows the same pattern as a single-column index;
the indexed columns are added in front of the rowid.  The only difference
is that now multiple columns are added.  The left-most column is the
primary key used for ordering the rows in the index.  The second column is
used to break ties in the left-most column.  If there were a third column,
it would be used to break ties for the first two columns.  And so forth for
all columns in the index.  Because rowid is guaranteed
to be unique, every row of the index will be unique even if all of the
content columns for two rows are the same.  That case does not happen
in our sample data, but there is one case (fruit='Orange') where there
is a tie on the first column which must be broken by the second column.
</p>

<p>
................................................................................
so that the states will appear in descending order.
</p>

<tcl>hd_fragment {partialsort} {partial sorting by index}</tcl>
<h3>3.2 Partial Sorting Using An Index</h3>

<p>
Sometimes only part of an ORDER BY clause can be satisfied using indexes.
Consider, for example, the following query:
</p>

<tcl>
code {SELECT * FROM fruitforsale ORDER BY fruit, price}
</tcl>

Changes to pages/rtree.in

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
...
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
...
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
  void *pContext
);
</pre></blockquote>

<p>The sqlite3_rtree_query_callback() became available with SQLite
[version 3.8.5] and is the preferred interface.
The sqlite3_rtree_geometry_callback() is an older and less flexible
interface that is supported for backwards compatiblity.

<p>^A call to one of the above APIs creates a new SQL function named by the
second parameter (zQueryFunc or zGeom).  ^When that SQL function appears
on the right-hand side of the MATCH operator and the left-hand side of the
MATCH operator is any column in the R*Tree virtual table, then the callback 
defined by the third argument (xQueryFunc or xGeom) is invoked to determine
if a particular object or subtree overlaps the desired region.
................................................................................

<p>The sqlite3_rtree_geometry structure that the first argument to the
xGeom callback points to has a structure shown below.  ^The exact same
sqlite3_rtree_geometry
structure is used for every callback for same MATCH operator in the same
query.  ^The contents of the sqlite3_rtree_geometry
structure are initialized by SQLite but are
not subsequently modifed.  The callback is free to make changes to the
pUser and xDelUser elements of the structure if desired.

<blockquote><pre>
typedef struct sqlite3_rtree_geometry sqlite3_rtree_geometry;
struct sqlite3_rtree_geometry {
  void *pContext;                 /* Copy of pContext passed to s_r_g_c() */
  int nParam;                     /* Size of array aParam */
................................................................................
<p>^The xGeom callback always does a depth-first search of the r-tree.

<tcl>hd_fragment xquery {xQueryFunc R*Tree callback} {sqlite3_rtree_query_callback}
</tcl>
<h3>6.2 The New xQueryFunc Callback</h3>

<p>The newer xQueryFunc callback receives more information from the r-tree
query engine on each call, and it send more information back to the query engine
before it returns.
To help keep the interface manageable, the xQueryFunc callback sends and receives
information from the query engine as fields in the
sqlite3_rtree_query_info structure:

<blockquote><pre>
struct sqlite3_rtree_query_info {
................................................................................
the priority queue at each level.

<h3>6.3 Additional Considerations for Custom Queries</h3>

<p>
^The MATCH operator of a custom R*Tree query function must be a top-level
AND-connected term of the WHERE clause, or else it will not be usable
by the R*Tree query optimizer and the query will not be runable.
^If the MATCH operator is connected to other terms of the WHERE clause 
via an OR operator, for example, the query will fail with an error.

<p>
^Two or more MATCH operators are allowed in the same WHERE clause, as long
as they are connected by AND operators.  However,
the R*Tree query engine only contains a single priority queue.  ^The priority
assigned to each node in the search is the lowest priority returned by any
of the MATCH operators.







|







 







|







 







|







 







|









376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
...
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
...
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
  void *pContext
);
</pre></blockquote>

<p>The sqlite3_rtree_query_callback() became available with SQLite
[version 3.8.5] and is the preferred interface.
The sqlite3_rtree_geometry_callback() is an older and less flexible
interface that is supported for backwards compatibility.

<p>^A call to one of the above APIs creates a new SQL function named by the
second parameter (zQueryFunc or zGeom).  ^When that SQL function appears
on the right-hand side of the MATCH operator and the left-hand side of the
MATCH operator is any column in the R*Tree virtual table, then the callback 
defined by the third argument (xQueryFunc or xGeom) is invoked to determine
if a particular object or subtree overlaps the desired region.
................................................................................

<p>The sqlite3_rtree_geometry structure that the first argument to the
xGeom callback points to has a structure shown below.  ^The exact same
sqlite3_rtree_geometry
structure is used for every callback for same MATCH operator in the same
query.  ^The contents of the sqlite3_rtree_geometry
structure are initialized by SQLite but are
not subsequently modified.  The callback is free to make changes to the
pUser and xDelUser elements of the structure if desired.

<blockquote><pre>
typedef struct sqlite3_rtree_geometry sqlite3_rtree_geometry;
struct sqlite3_rtree_geometry {
  void *pContext;                 /* Copy of pContext passed to s_r_g_c() */
  int nParam;                     /* Size of array aParam */
................................................................................
<p>^The xGeom callback always does a depth-first search of the r-tree.

<tcl>hd_fragment xquery {xQueryFunc R*Tree callback} {sqlite3_rtree_query_callback}
</tcl>
<h3>6.2 The New xQueryFunc Callback</h3>

<p>The newer xQueryFunc callback receives more information from the r-tree
query engine on each call, and it sends more information back to the query engine
before it returns.
To help keep the interface manageable, the xQueryFunc callback sends and receives
information from the query engine as fields in the
sqlite3_rtree_query_info structure:

<blockquote><pre>
struct sqlite3_rtree_query_info {
................................................................................
the priority queue at each level.

<h3>6.3 Additional Considerations for Custom Queries</h3>

<p>
^The MATCH operator of a custom R*Tree query function must be a top-level
AND-connected term of the WHERE clause, or else it will not be usable
by the R*Tree query optimizer and the query will not be runnable.
^If the MATCH operator is connected to other terms of the WHERE clause 
via an OR operator, for example, the query will fail with an error.

<p>
^Two or more MATCH operators are allowed in the same WHERE clause, as long
as they are connected by AND operators.  However,
the R*Tree query engine only contains a single priority queue.  ^The priority
assigned to each node in the search is the lowest priority returned by any
of the MATCH operators.

Changes to pages/uri.in

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
^If URI filenames are recognized when the database connection is originally
opened, then URI filenames will also be recognized on [ATTACH] statements.
^Similarly, if URI filenames are not recognized when the database connection
is first opened, they will not be recognized by [ATTACH].
</p>

<p>
Since SQLite always interprets any filename that does not begins 
with "<tt>file:</tt>"
as an ordinary filename regardless of the URI setting, and because it is
very unusual to have an actual file begin with "<tt>file:</tt>", 
it is safe for most applications to enable URI processing even if URI 
filenames are not currently being used.
</p>

................................................................................
<li>Prepend the "<tt>file:</tt>" scheme.
</ol>

<h2>3.2 Query String</h2>

<p>^A URI filename can optionally be followed by a query string.
^The query string consists of text following the first "<tt>?</tt>"
character but excluding the optional fragment that begins with with
"<tt>#</tt>".  ^The query string is divided into key/value pairs.
We usually refer to these key/value pairs as "query parameters".
^Key/value pairs are separated by a single "<tt>&amp;</tt>" character.
^The key comes first and is separated from the value by a single
"<tt>=</tt>" character.
^Both key and value may contain <b>%HH</b> escape sequences.</p>

................................................................................
^The text of query parameters is appended to the filename argument of
the xOpen method of the [VFS].
^Any %HH escape sequences in the query parameters are resolved prior to
being appended to the xOpen filename.
^A single zero-byte separates the xOpen filename argument from the key of
the first query parameters, each key and value, and each subsequent key
from the prior value.
^The list of query parameters parameters appended to the xOpen filename
is terminated by a single zero-length key.
Note that the value of a query parameter can be an empty string.
</p>

<tcl>hd_fragment coreqp *coreqp {standard query parameters} {URI query parameters} \
    {query parameters with special meaning to SQLite}</tcl>
<h2>3.3 Recognized Query Parameters</h2>







|







 







|







 







|







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
...
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
^If URI filenames are recognized when the database connection is originally
opened, then URI filenames will also be recognized on [ATTACH] statements.
^Similarly, if URI filenames are not recognized when the database connection
is first opened, they will not be recognized by [ATTACH].
</p>

<p>
Since SQLite always interprets any filename that does not begin
with "<tt>file:</tt>"
as an ordinary filename regardless of the URI setting, and because it is
very unusual to have an actual file begin with "<tt>file:</tt>", 
it is safe for most applications to enable URI processing even if URI 
filenames are not currently being used.
</p>

................................................................................
<li>Prepend the "<tt>file:</tt>" scheme.
</ol>

<h2>3.2 Query String</h2>

<p>^A URI filename can optionally be followed by a query string.
^The query string consists of text following the first "<tt>?</tt>"
character but excluding the optional fragment that begins with
"<tt>#</tt>".  ^The query string is divided into key/value pairs.
We usually refer to these key/value pairs as "query parameters".
^Key/value pairs are separated by a single "<tt>&amp;</tt>" character.
^The key comes first and is separated from the value by a single
"<tt>=</tt>" character.
^Both key and value may contain <b>%HH</b> escape sequences.</p>

................................................................................
^The text of query parameters is appended to the filename argument of
the xOpen method of the [VFS].
^Any %HH escape sequences in the query parameters are resolved prior to
being appended to the xOpen filename.
^A single zero-byte separates the xOpen filename argument from the key of
the first query parameters, each key and value, and each subsequent key
from the prior value.
^The list of query parameters appended to the xOpen filename
is terminated by a single zero-length key.
Note that the value of a query parameter can be an empty string.
</p>

<tcl>hd_fragment coreqp *coreqp {standard query parameters} {URI query parameters} \
    {query parameters with special meaning to SQLite}</tcl>
<h2>3.3 Recognized Query Parameters</h2>