Documentation Source Text

chng {2011 June 28 (3.7.7)} {
<li> Add support for [URI filenames]
<li> Add the [sqlite3_vtab_config()] interface in
     support of [ON CONFLICT] clauses with [virtual tables].
<li> Add the [xSavepoint], [xRelease] and [xRollbackTo] methods in
     [virtual tables] in support of [SAVEPOINT] for virtual tables.


<li> Avoid unnecessary reparsing of the database schema.

<li> Allow [WAL | WAL-mode] databases to be opened read-only as long as
     there is an existing read/write connection.
<li> Added support for [short filenames].
}

chng {2011 May 19 (3.7.6.3)} {
<li> Fix a problem with [WAL mode] which could cause transactions to

chng {2011 June 28 (3.7.7)} {
<li> Add support for [URI filenames]
<li> Add the [sqlite3_vtab_config()] interface in
     support of [ON CONFLICT] clauses with [virtual tables].
<li> Add the [xSavepoint], [xRelease] and [xRollbackTo] methods in
     [virtual tables] in support of [SAVEPOINT] for virtual tables.
<li> Update the built-in [FTS3|FTS3/FTS4] and [RTREE] virtual tables to support
     [ON CONFLICT] clauses and [REPLACE].
<li> Avoid unnecessary reparsing of the database schema.
<li> Added support for the [FTS4 prefix option] and the [FTS4 order option].
<li> Allow [WAL | WAL-mode] databases to be opened read-only as long as
     there is an existing read/write connection.
<li> Added support for [short filenames].
}

chng {2011 May 19 (3.7.6.3)} {
<li> Fix a problem with [WAL mode] which could cause transactions to

  by FTS tables, and how they may be utilized by specifying a more
  complex query expression as the right-hand operand of a MATCH operator.

<p>
  FTS tables support three basic query types:

<ul>

  <li><p><b>Token or token prefix queries</b>. 
    An FTS table may be queried for all documents that contain a specified
    term (the [simple fts queries|simple case] described above), or for
    all documents that contain a term with a specified prefix. As we have
    seen, the query expression for a specific term is simply the term itself.
    The query expression used to search for a term prefix is the prefix
    itself with a '*' character appended to it. For example:
................................................................................

<ul>
  <li><p><b>NEAR queries</b>. 
    A NEAR query is a query that returns documents that contain a two or
    more nominated terms or phrases within a specified proximity of each 
    other (by default with 10 or less intervening terms). A NEAR query is 
    specified by putting the keyword "NEAR" between two phrase, term or 
    term prefix queries. To specify a proximity other than the default,
    an operator of the form "NEAR/<i>&lt;N&gt;</i>" may be used, where
    <i>&lt;N&gt;</i> is the maximum number of intervening terms allowed.
    For example:
</ul>

<codeblock>
  <i>-- Virtual table declaration.</i>
................................................................................
    
  <tr><td>uncompress<td>
    This option is used to specify the uncompress function. It is an error to
    specify an uncompress function without also specifying a compress
    function. [fts4 compress option|See below] for details.

  <tr><td>order<td>

    The "order" option may be set to either "DESC" or "ASC" (in upper or
    lower case). If it is set to "DESC", then FTS4 stores its data in such
    a way as to optimize returning results in descending order by docid.
    If it is set to "ASC" (the default), then the data structures are 
    optimized for returning results in ascending order by docid. In other
    words, if many of the queries run against the FTS4 table use "ORDER BY
    docid DESC", then it may improve performance to add the "order=desc" 
    option to the CREATE VIRTUAL TABLE statement.

  <tr><td>prefix<td>
    This option may be set to a comma-separated list of positive non-zero 
    integers. For each integer N in the list, a separate index is created
    in the database file to optimize [FTS MATCH|term prefix] queries where
    the query term is N bytes in length, not including the '*' character,
    when encoded using UTF-8. [fts4 prefix option|See below] for details.

</table>

<p>
  When using FTS4, specifying a column name that contains an "=" character
................................................................................
<codeblock>
  <i>-- Create an FTS4 table that stores data in compressed form. This</i>
  <i>-- assumes that the scalar functions zip() and unzip() have been (or</i>
  <i>-- will be) added to the database handle.</i>
  CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);
</codeblock>


<h2 tags="fts4 prefix option">Prefix</h2>

<p>
  The FTS4 prefix option causes FTS to index term prefixes of specified lengths
  in the same way that it always indexes complete terms. The prefix option must be set 
  to a comma separated list of positive non-zero integers. For each value N in the 
  list, prefixes of length N bytes (when encoded using UTF-8) are indexed. FTS4 uses
  term prefix indexes to speed up term prefix queries. The cost, of course, is that

  indexing term prefixes as well as complete terms increases the database size and
  slows down write operations on the FTS4 table.

<p>
  Prefix indexes may be used to optimize term prefix queries in two cases. If the
  query is for a prefix of N bytes, then a prefix index created with "prefix=N" 
  provides the best optimization. Or, if no "prefix=N" index is available, a

  "prefix=N+1" index imay be used instead. Using a "prefix=N+1" index is less
  efficient than a "prefix=N" index, but is better than no prefix index at all.

<codeblock>
  <i>-- Create an FTS4 table with indexes to optimize 2 and 4 byte term prefix queries.</i>
  CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");

  <i>-- The following two queries are both optimized using the prefix indexes.</i>
  SELECT * FROM t1 WHERE t1 MATCH 'ab*';
  SELECT * FROM t1 WHERE t1 MATCH 'abcd*';

  <i>-- The following two queries are both partially optimized using the prefix</i>

  by FTS tables, and how they may be utilized by specifying a more
  complex query expression as the right-hand operand of a MATCH operator.

<p>
  FTS tables support three basic query types:

<ul>
  <tcl>hd_fragment termprefix {prefix query} {prefix queries}</tcl>
  <li><p><b>Token or token prefix queries</b>. 
    An FTS table may be queried for all documents that contain a specified
    term (the [simple fts queries|simple case] described above), or for
    all documents that contain a term with a specified prefix. As we have
    seen, the query expression for a specific term is simply the term itself.
    The query expression used to search for a term prefix is the prefix
    itself with a '*' character appended to it. For example:
................................................................................

<ul>
  <li><p><b>NEAR queries</b>. 
    A NEAR query is a query that returns documents that contain a two or
    more nominated terms or phrases within a specified proximity of each 
    other (by default with 10 or less intervening terms). A NEAR query is 
    specified by putting the keyword "NEAR" between two phrase, term or 
    [prefix queries]. To specify a proximity other than the default,
    an operator of the form "NEAR/<i>&lt;N&gt;</i>" may be used, where
    <i>&lt;N&gt;</i> is the maximum number of intervening terms allowed.
    For example:
</ul>

<codeblock>
  <i>-- Virtual table declaration.</i>
................................................................................
    
  <tr><td>uncompress<td>
    This option is used to specify the uncompress function. It is an error to
    specify an uncompress function without also specifying a compress
    function. [fts4 compress option|See below] for details.

  <tr><td>order<td>
    <tcl>hd_fragment fts4order {FTS4 order option}</tcl>
    ^The "order" option may be set to either "DESC" or "ASC" (in upper or
    lower case). ^If it is set to "DESC", then FTS4 stores its data in such
    a way as to optimize returning results in descending order by docid.
    ^If it is set to "ASC" (the default), then the data structures are 
    optimized for returning results in ascending order by docid.  ^In other
    words, if many of the queries run against the FTS4 table use "ORDER BY
    docid DESC", then it may improve performance to add the "order=desc" 
    option to the CREATE VIRTUAL TABLE statement.

  <tr><td>prefix<td>
    This option may be set to a comma-separated list of positive non-zero 
    integers. For each integer N in the list, a separate index is created
    in the database file to optimize [prefix queries] where
    the query term is N bytes in length, not including the '*' character,
    when encoded using UTF-8. [fts4 prefix option|See below] for details.

</table>

<p>
  When using FTS4, specifying a column name that contains an "=" character
................................................................................
<codeblock>
  <i>-- Create an FTS4 table that stores data in compressed form. This</i>
  <i>-- assumes that the scalar functions zip() and unzip() have been (or</i>
  <i>-- will be) added to the database handle.</i>
  CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);
</codeblock>

<tcl>hd_fragment fts4prefix {FTS4 prefix option}</tcl>
<h2 tags="fts4 prefix option">Prefix</h2>

<p>
  ^The FTS4 prefix option causes FTS to index term prefixes of specified lengths
  in the same way that it always indexes complete terms.  ^The prefix option
  must be set to a comma separated list of positive non-zero integers. 
  ^For each value N in the list, prefixes of length N bytes (when encoded 
  using UTF-8) are indexed.  ^FTS4 uses term prefix indexes to speed up
  [prefix queries]. The cost, of course, is that indexing term prefixes as
  well as complete terms increases the database size and slows down write 
  operations on the FTS4 table.

<p>
  Prefix indexes may be used to optimize [prefix queries] in two cases.
  If the query is for a prefix of N bytes, then a prefix index created
  with "prefix=N" provides the best optimization. Or, if no "prefix=N"
  index is available, a "prefix=N+1" index imay be used instead. 
  Using a "prefix=N+1" index is less
  efficient than a "prefix=N" index, but is better than no prefix index at all.

<codeblock>
  <i>-- Create an FTS4 table with indexes to optimize 2 and 4 byte prefix queries.</i>
  CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");

  <i>-- The following two queries are both optimized using the prefix indexes.</i>
  SELECT * FROM t1 WHERE t1 MATCH 'ab*';
  SELECT * FROM t1 WHERE t1 MATCH 'abcd*';

  <i>-- The following two queries are both partially optimized using the prefix</i>

       is set at application start-time.</p></li>
<li><p>The [SQLITE_OPEN_URI] bit is OR-ed in with the set bits passed in
       as the 3rd parameter to the [sqlite3_open_v2()] interface.</p></li>
</ol>)^

<p>
^If URI filenames are recognized when the database connection is originally
opened, then URI filenames will also be recognized on [ATTACH] statemenets.
^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 to begin with "<tt>file:</tt>", for
most applications it is safe to enable URI processing even if URI filenames
are not currently being used.
</p>

<h1>3.0 URI Format</h1>

<p>
According to [http://tools.ietf.org/html/rfc3986 | RFC 3986], a URI consists
of a scheme, an authority, a path, a query string, and a fragment.  The
................................................................................
popular web-browsers such as 
[http://www.mozilla.com/en-US/firefox/new/ | Firefox], 
[http://www.google.com/chrome/ | Chrome], 
[http://www.apple.com/safari/ | Safari], 
[http://windows.microsoft.com/en-US/internet-explorer/products/ie/home | Internet Explorer], and
[http://www.opera.com/ | Opera],
and command-line programs such as 
[http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/start.mspx | Windows "start"] and the
[http://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man1/open.1.html | "open"] command on Mac OS-X.
A succinct summary of the URI parsing rules follows:
</p>

<ul>
<li> ^(The scheme of the URI must be "<tt>file:</tt>".  Any other scheme
     results in the input being treated as an ordinary filename.)^
<li> ^(The authority may be omitted, may be blank, or may be

       is set at application start-time.</p></li>
<li><p>The [SQLITE_OPEN_URI] bit is OR-ed in with the set bits passed in
       as the 3rd parameter to the [sqlite3_open_v2()] interface.</p></li>
</ol>)^

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

<h1>3.0 URI Format</h1>

<p>
According to [http://tools.ietf.org/html/rfc3986 | RFC 3986], a URI consists
of a scheme, an authority, a path, a query string, and a fragment.  The
................................................................................
popular web-browsers such as 
[http://www.mozilla.com/en-US/firefox/new/ | Firefox], 
[http://www.google.com/chrome/ | Chrome], 
[http://www.apple.com/safari/ | Safari], 
[http://windows.microsoft.com/en-US/internet-explorer/products/ie/home | Internet Explorer], and
[http://www.opera.com/ | Opera],
and command-line programs such as 
[http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/start.mspx | Windows "start"] and the Mac OS-X
[http://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man1/open.1.html | "open"] command.
A succinct summary of the URI parsing rules follows:
</p>

<ul>
<li> ^(The scheme of the URI must be "<tt>file:</tt>".  Any other scheme
     results in the input being treated as an ordinary filename.)^
<li> ^(The authority may be omitted, may be blank, or may be