Documentation Source Text

<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

<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

    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

    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

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

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

  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.

  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.

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

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