Documentation Source Text

Check-in [be9ae0b604]
Login

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

Overview
Comment:Updates in preparation for the 3.7.7 release.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be9ae0b6048b0ff794c025f77b620a1f156970b5
User & Date: drh 2011-06-20 19:49:44.344
Context
2011-06-20
20:27
Update the compile-time options. (check-in: 46855636fa user: drh tags: trunk)
19:49
Updates in preparation for the 3.7.7 release. (check-in: be9ae0b604 user: drh tags: trunk)
19:49
Fix the ON CONFLICT documentation to avoid the erroneous implication that you can put an ON CONFLICT clause on a CHECK constraint. (check-in: 11f4999021 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
44
45
46
47
48
49
50


51

52
53
54
55
56
57
58

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







>
>

>







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

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
Changes to pages/fts3.in.
506
507
508
509
510
511
512

513
514
515
516
517
518
519
  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:







>







506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
  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:
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591

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







|







578
579
580
581
582
583
584
585
586
587
588
589
590
591
592

<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>
1347
1348
1349
1350
1351
1352
1353

1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
    
  <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







>
|
|

|
|







|







1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
    
  <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
1429
1430
1431
1432
1433
1434
1435

1436
1437
1438
1439
1440
1441
1442
1443

1444
1445
1446
1447
1448
1449
1450

1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
<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>







>



|
|
|
|
|
>
|
|


|
|
|
>
|



|







1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
<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>
Changes to pages/uri.in.
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
       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







|








|
|
|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
       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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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







|
|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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