Documentation Source Text

Check-in [b503d1f516]
Login

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

Overview
Comment:Documentation typos.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b503d1f516715b0bea4637cb34e04a89ab572f9d
User & Date: drh 2011-06-20 21:48:16.611
Context
2011-06-20
23:51
More typo fixes. (check-in: 8f9d1225b6 user: drh tags: trunk)
21:48
Documentation typos. (check-in: b503d1f516 user: drh tags: trunk)
20:27
Update the compile-time options. (check-in: 46855636fa user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/34to35.in.
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399

PARAGRAPH {
  The differences between an [SQLITE_OPEN_TEMP_DB] database and an
  [SQLITE_OPEN_TRANSIENT_DB] database is this:  The [SQLITE_OPEN_TEMP_DB]
  is used for explicitly declared and named TEMP tables (using the
  CREATE TEMP TABLE syntax) or for named tables in a temporary database
  that is created by opening a database with a filename that is an empty
  string.  An [SQLITE_OPEN_TRANSIENT_DB] holds an database table that
  SQLite creates automatically in order to evaluate a subquery or
  ORDER BY or GROUP BY clause.  Both TEMP_DB and TRANSIENT_DB databases
  are private and are deleted automatically.  TEMP_DB databases last
  for the duration of the database connection.  TRANSIENT_DB databases
  last only for the duration of a single SQL statement.
}








|







385
386
387
388
389
390
391
392
393
394
395
396
397
398
399

PARAGRAPH {
  The differences between an [SQLITE_OPEN_TEMP_DB] database and an
  [SQLITE_OPEN_TRANSIENT_DB] database is this:  The [SQLITE_OPEN_TEMP_DB]
  is used for explicitly declared and named TEMP tables (using the
  CREATE TEMP TABLE syntax) or for named tables in a temporary database
  that is created by opening a database with a filename that is an empty
  string.  An [SQLITE_OPEN_TRANSIENT_DB] holds a database table that
  SQLite creates automatically in order to evaluate a subquery or
  ORDER BY or GROUP BY clause.  Both TEMP_DB and TRANSIENT_DB databases
  are private and are deleted automatically.  TEMP_DB databases last
  for the duration of the database connection.  TRANSIENT_DB databases
  last only for the duration of a single SQL statement.
}

Changes to pages/35to36.in.
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
}

INDENTED {
  <strong>Key Point:</strong>  The incompatible
  changes in the SQLite operating-system interface for version 3.6.0
  only affect the rare applications that make use of the 
  [sqlite3_vfs | virtual file system] interface or that
  supply a application-defined [sqlite3_mutex_enter | mutex implementation]
  or that make use of other obscure compile-time options.  The
  changes introduced by SQLite version 3.6.0 will have zero impact on the
  vast majority of SQLite applications that use the built-in interfaces
  to Unix, Windows, and OS/2 and that use the standard build configuration.
}

HEADING 2 {Changes In The Way The IN Operator Handles NULLs}







|







142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
}

INDENTED {
  <strong>Key Point:</strong>  The incompatible
  changes in the SQLite operating-system interface for version 3.6.0
  only affect the rare applications that make use of the 
  [sqlite3_vfs | virtual file system] interface or that
  supply an application-defined [sqlite3_mutex_enter | mutex implementation]
  or that make use of other obscure compile-time options.  The
  changes introduced by SQLite version 3.6.0 will have zero impact on the
  vast majority of SQLite applications that use the built-in interfaces
  to Unix, Windows, and OS/2 and that use the standard build configuration.
}

HEADING 2 {Changes In The Way The IN Operator Handles NULLs}
Changes to pages/c_interface.in.
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
<dd><p>This value is returned if the operating system informs SQLite
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to an 
previously undetected logic error in of SQLite. This value is also
returned if a disk I/O error occurs in such a way that SQLite is forced
to leave the database file in a corrupted state.  The latter should only
happen due to a hardware or operating system malfunction.
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is







|







251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
<dd><p>This value is returned if the operating system informs SQLite
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to a
previously undetected logic error in of SQLite. This value is also
returned if a disk I/O error occurs in such a way that SQLite is forced
to leave the database file in a corrupted state.  The latter should only
happen due to a hardware or operating system malfunction.
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is
Changes to pages/changes.in.
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
    smaller.  The code is now also a little bit faster.</li>
<li>sqlite_exec() is now implemented as a wrapper around sqlite_compile()
    and sqlite_step().</li>
<li>The built-in min() and max() functions now honor the difference between
    NUMERIC and TEXT datatypes.  Formerly, min() and max() always assumed
    their arguments were of type NUMERIC.</li>
<li>New HH:MM:SS modifier to the built-in date/time functions.</li>
<li>Experimental sqlite_last_statement_changes() API added.  Fixed the
    the last_insert_rowid() function so that it works correctly with
    triggers.</li>
<li>Add functions prototypes for the database encryption API.</li>
<li>Fix several nuisance bugs.</li>
}

chng {2004 February 8 (2.8.12)} {







|







1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
    smaller.  The code is now also a little bit faster.</li>
<li>sqlite_exec() is now implemented as a wrapper around sqlite_compile()
    and sqlite_step().</li>
<li>The built-in min() and max() functions now honor the difference between
    NUMERIC and TEXT datatypes.  Formerly, min() and max() always assumed
    their arguments were of type NUMERIC.</li>
<li>New HH:MM:SS modifier to the built-in date/time functions.</li>
<li>Experimental sqlite_last_statement_changes() API added.  Fixed
    the last_insert_rowid() function so that it works correctly with
    triggers.</li>
<li>Add functions prototypes for the database encryption API.</li>
<li>Fix several nuisance bugs.</li>
}

chng {2004 February 8 (2.8.12)} {
Changes to pages/custombuild.in.
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
API to make sure they did not accidentally get linked against a version of
the SQLite library that has its mutexes disabled.  Single-threaded
applications will, of course, work correctly regardless of whether or
not SQLite is configured to be threadsafe, though they will be a little
bit faster when using versions of SQLite with mutexes disabled.</p>

<p>SQLite mutexes can also be disabled at run-time using the
the [sqlite3_config()] interface.  To completely disable all mutexing,
the application can invoke:</p>

<blockquote><pre>
sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
</pre></blockquote>

<p>Disabling mutexes at run-time is not as effective as disabling them







|







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
API to make sure they did not accidentally get linked against a version of
the SQLite library that has its mutexes disabled.  Single-threaded
applications will, of course, work correctly regardless of whether or
not SQLite is configured to be threadsafe, though they will be a little
bit faster when using versions of SQLite with mutexes disabled.</p>

<p>SQLite mutexes can also be disabled at run-time using the
[sqlite3_config()] interface.  To completely disable all mutexing,
the application can invoke:</p>

<blockquote><pre>
sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
</pre></blockquote>

<p>Disabling mutexes at run-time is not as effective as disabling them
Changes to pages/datatypes.in.
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer.  Any
attempt to insert non-integer data will result in an error.
</p>

<p>
INTEGER PRIMARY KEY columns can be used to implement the equivalent
of AUTOINCREMENT.  If you try to insert a NULL into an INTEGER PRIMARY
KEY column, the column will actually be filled with a integer that is
one greater than the largest key already in the table.  Or if the
largest key is 2147483647, then the column will be filled with a
random integer.  Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer.  You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
</p>







|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer.  Any
attempt to insert non-integer data will result in an error.
</p>

<p>
INTEGER PRIMARY KEY columns can be used to implement the equivalent
of AUTOINCREMENT.  If you try to insert a NULL into an INTEGER PRIMARY
KEY column, the column will actually be filled with an integer that is
one greater than the largest key already in the table.  Or if the
largest key is 2147483647, then the column will be filled with a
random integer.  Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer.  You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
</p>
Changes to pages/fileformat2.in.
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
byte are used to hold overflow content.</p>

<h3>1.7 Pointer Map or Ptrmap Pages</h3>

<p>Pointer map or ptrmap pages are extra pages inserted into the database
to make the operation of [auto_vacuum] and [incremental_vacuum] modes
more efficient.  Other page types in the database typically have pointers
from parent to child.  For example, a interior b-tree page contains pointers
to its child b-tree pages and an overflow chain has a pointer
from earlier to later links in the chain.  A ptrmap page contains linkage
information going in the opposite direction, from child to parent.</p>

<p>^Ptrmap pages must exist in any database file which has a non-zero
largest root b-tree page value at offset 52 in the database header.
^If the largest root b-tree page value is zero, then the database must not
contain ptrmap pages.</p>

<p>^In a database with ptrmap pages, the first ptrmap page is page 2.
A ptrmap page consists of an array of 5-byte entries.  Let J be the
number of 5-byte entries that will fit in the usable space of a page.
(In other words, J=U/5.)  ^The first ptrmap page will contain back pointer
information for pages 3 through J+2, inclusive.  ^The second pointer map
page will be on page J+3 and that ptrmap page will provide back pointer
information for pages J+4 through 2*J+3 inclusive.  An so forth for
the entire database file.</p>

<p>^(In a database that uses ptrmap pages, all pages at locations identified
by the computation in the previous paragraph must be ptrmap page and no
other page may be a ptrmap page.  Except, if the byte-lock page happens to
fall on the same page number as a ptrmap page, then the ptrmap is moved
to the following page for that one case.)^</p>







|















|







744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
byte are used to hold overflow content.</p>

<h3>1.7 Pointer Map or Ptrmap Pages</h3>

<p>Pointer map or ptrmap pages are extra pages inserted into the database
to make the operation of [auto_vacuum] and [incremental_vacuum] modes
more efficient.  Other page types in the database typically have pointers
from parent to child.  For example, an interior b-tree page contains pointers
to its child b-tree pages and an overflow chain has a pointer
from earlier to later links in the chain.  A ptrmap page contains linkage
information going in the opposite direction, from child to parent.</p>

<p>^Ptrmap pages must exist in any database file which has a non-zero
largest root b-tree page value at offset 52 in the database header.
^If the largest root b-tree page value is zero, then the database must not
contain ptrmap pages.</p>

<p>^In a database with ptrmap pages, the first ptrmap page is page 2.
A ptrmap page consists of an array of 5-byte entries.  Let J be the
number of 5-byte entries that will fit in the usable space of a page.
(In other words, J=U/5.)  ^The first ptrmap page will contain back pointer
information for pages 3 through J+2, inclusive.  ^The second pointer map
page will be on page J+3 and that ptrmap page will provide back pointer
information for pages J+4 through 2*J+3 inclusive.  And so forth for
the entire database file.</p>

<p>^(In a database that uses ptrmap pages, all pages at locations identified
by the computation in the previous paragraph must be ptrmap page and no
other page may be a ptrmap page.  Except, if the byte-lock page happens to
fall on the same page number as a ptrmap page, then the ptrmap is moved
to the following page for that one case.)^</p>
Changes to pages/fileio.in.
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477

    ASSUMPTION A21004
      If a system failure occurs during a "delete file" operation,
      it is assumed that following system recovery the file-system will 
      either contain the file being deleted in the state it was in before
      the operation was attempted, or not contain the file at all. It is 
      assumed that it is not possible for the file to have become corrupted
      purely as a result of a failure occuring during a "delete file" 
      operation.

    <p>
      The effects of a <b>truncate file</b> operation are not assumed to
      be made persistent until after the corresponding file has been
      <i>synced</i>.








|







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477

    ASSUMPTION A21004
      If a system failure occurs during a "delete file" operation,
      it is assumed that following system recovery the file-system will 
      either contain the file being deleted in the state it was in before
      the operation was attempted, or not contain the file at all. It is 
      assumed that it is not possible for the file to have become corrupted
      purely as a result of a failure occurfing during a "delete file" 
      operation.

    <p>
      The effects of a <b>truncate file</b> operation are not assumed to
      be made persistent until after the corresponding file has been
      <i>synced</i>.

Changes to pages/fts3.in.
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
  operator is a string consisting of a single term. In this case, the MATCH
  expression evaluates to true for all documents that contain one or more 
  instances of the specified word ("sqlite", "search" or "database", depending 
  on which example you look at). Specifying a single term as the right-hand
  operand of the MATCH operator results in the simplest and most common type 
  of full-text query possible. However more complicated queries are possible,
  including phrase searches, term-prefix searches and searches for documents 
  containing combinations of terms occuring within a defined proximity of each
  other. The various ways in which the full-text index may be queried are
  [FTS MATCH|described below].

<p>
  Normally, full-text queries are case-insensitive. However, this is
  is dependent on the specific [tokenizer] used by the FTS table
  being queried. Refer to the section on [tokenizer|tokenizers] for details.

<p>
  The paragraph above notes that a MATCH operator with a simple term as the
  right-hand operand evaluates to true for all documents that contain the
  specified term. In this context, the "document" may refer to either the 







|




|







317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
  operator is a string consisting of a single term. In this case, the MATCH
  expression evaluates to true for all documents that contain one or more 
  instances of the specified word ("sqlite", "search" or "database", depending 
  on which example you look at). Specifying a single term as the right-hand
  operand of the MATCH operator results in the simplest and most common type 
  of full-text query possible. However more complicated queries are possible,
  including phrase searches, term-prefix searches and searches for documents 
  containing combinations of terms occurring within a defined proximity of each
  other. The various ways in which the full-text index may be queried are
  [FTS MATCH|described below].

<p>
  Normally, full-text queries are case-insensitive. However, this
  is dependent on the specific [tokenizer] used by the FTS table
  being queried. Refer to the section on [tokenizer|tokenizers] for details.

<p>
  The paragraph above notes that a MATCH operator with a simple term as the
  right-hand operand evaluates to true for all documents that contain the
  specified term. In this context, the "document" may refer to either the 
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
<codeblock>
  -DSQLITE_ENABLE_FTS3
  -DSQLITE_ENABLE_FTS3_PARENTHESIS
</codeblock>

<p>
  Note that enabling FTS3 also makes FTS4 available.  There is not a separate
  SQLITE_ENABLE_FTS4 compile-time option.  An build of SQLite either supports
  both FTS3 and FTS4 or it supports neither.

<p>
  If using the amalgamation autoconf based build system, setting the CPPFLAGS
  environment variable while running the 'configure' script is an easy
  way to set these macros. For example, the following command:








|







449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
<codeblock>
  -DSQLITE_ENABLE_FTS3
  -DSQLITE_ENABLE_FTS3_PARENTHESIS
</codeblock>

<p>
  Note that enabling FTS3 also makes FTS4 available.  There is not a separate
  SQLITE_ENABLE_FTS4 compile-time option.  A build of SQLite either supports
  both FTS3 and FTS4 or it supports neither.

<p>
  If using the amalgamation autoconf based build system, setting the CPPFLAGS
  environment variable while running the 'configure' script is an easy
  way to set these macros. For example, the following command:

771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
<p>
  FTS query set operations using the standard query syntax are similar, but
  not identical, to set operations with the enhanced query syntax. There
  are four differences, as follows:

<ol>
  <li value=1><p> Only the implicit version of the AND operator is supported.
    Specifying the string "AND" as part of an standard query syntax query is
    interpreted as a term query for the set of documents containing the term 
    "and".
</ol>

<ol>
  <li value=2><p> Parenthesis are not supported.
</ol>







|







771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
<p>
  FTS query set operations using the standard query syntax are similar, but
  not identical, to set operations with the enhanced query syntax. There
  are four differences, as follows:

<ol>
  <li value=1><p> Only the implicit version of the AND operator is supported.
    Specifying the string "AND" as part of a standard query syntax query is
    interpreted as a term query for the set of documents containing the term 
    "and".
</ol>

<ol>
  <li value=2><p> Parenthesis are not supported.
</ol>
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
  a larger number of "extra" phrase matches are favoured. The start of
  the selected text fragment may be moved a few tokens forward or backward
  to attempt to concentrate the phrase matches toward the center of the
  fragment.

<p>
  Assuming <i>N</i> is a positive value, if no fragments can be found that
  contain an phrase match corresponding to each matchable phrase, the snippet
  function attempts to find two fragments of approximately <i>N</i>/2 tokens
  that between them contain at least one phrase match for each matchable phrase
  matched by the current row. If this fails, attempts are made to find three
  fragments of <i>N</i>/3 tokens each and finally four <i>N</i>/4 token
  fragments. If a set of four fragments cannot be found that encompasses the
  required phrase matches, the four fragments of <i>N</i>/4 tokens that provide
  the best coverage are selected.







|







998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
  a larger number of "extra" phrase matches are favoured. The start of
  the selected text fragment may be moved a few tokens forward or backward
  to attempt to concentrate the phrase matches toward the center of the
  fragment.

<p>
  Assuming <i>N</i> is a positive value, if no fragments can be found that
  contain a phrase match corresponding to each matchable phrase, the snippet
  function attempts to find two fragments of approximately <i>N</i>/2 tokens
  that between them contain at least one phrase match for each matchable phrase
  matched by the current row. If this fails, attempts are made to find three
  fragments of <i>N</i>/3 tokens each and finally four <i>N</i>/4 token
  fragments. If a set of four fragments cannot be found that encompasses the
  required phrase matches, the four fragments of <i>N</i>/4 tokens that provide
  the best coverage are selected.
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082

<p>
  The matchinfo function is called with either one or two arguments. As for
  all auxiliary functions, the first argument must be the special 
  [FTS hidden column]. The second argument, if it is specified, must be a text value
  comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's' and 'x'.
  If no second argument is explicitly supplied, it defaults to "pcx". The
  second argument is refered to as the "format string" below.

<p>
  Characters in the matchinfo format string are processed from left to right. 
  Each character in the format string causes one or more 32-bit unsigned
  integer values to be added to the returned array. The "values" column in
  the following table contains the number of integer values appended to the
  output buffer for each supported format string character. In the formula







|







1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082

<p>
  The matchinfo function is called with either one or two arguments. As for
  all auxiliary functions, the first argument must be the special 
  [FTS hidden column]. The second argument, if it is specified, must be a text value
  comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's' and 'x'.
  If no second argument is explicitly supplied, it defaults to "pcx". The
  second argument is referred to as the "format string" below.

<p>
  Characters in the matchinfo format string are processed from left to right. 
  Each character in the format string causes one or more 32-bit unsigned
  integer values to be added to the returned array. The "values" column in
  the following table contains the number of integer values appended to the
  output buffer for each supported format string character. In the formula
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
      </ul>
      The first set of three values corresponds to the left-most column
      of the table (column 0) and the left-most matchable phrase in the
      query (phrase 0). If the table has more than one column, the second
      set of three values in the output array correspond to phrase 0 and
      column 1. Followed by phrase 0, column 2 and so on for all columns of
      the table. And so on for phrase 1, column 0, then phrase 1, column 1
      etc. In other words, the data for occurences of phrase <i>p</i> in
      column <i>c</i> may be found using the following formula:
<pre>
          hits_this_row  = array&#91;3 * (c + p*cols) + 0&#93;
          hits_all_rows  = array&#91;3 * (c + p*cols) + 1&#93;
          docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
</pre>








|







1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
      </ul>
      The first set of three values corresponds to the left-most column
      of the table (column 0) and the left-most matchable phrase in the
      query (phrase 0). If the table has more than one column, the second
      set of three values in the output array correspond to phrase 0 and
      column 1. Followed by phrase 0, column 2 and so on for all columns of
      the table. And so on for phrase 1, column 0, then phrase 1, column 1
      etc. In other words, the data for occurrences of phrase <i>p</i> in
      column <i>c</i> may be found using the following formula:
<pre>
          hits_this_row  = array&#91;3 * (c + p*cols) + 0&#93;
          hits_all_rows  = array&#91;3 * (c + p*cols) + 1&#93;
          docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
</pre>

1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
  b-tree and merge the results), but it has been found that in practice this
  overhead is often negligible.
  
<h2>Variable Length Integer (varint) Format</h2>

<p>
  Integer values stored as part of segment b-tree nodes are encoded using the
  FTS varint format. This encoding is similar, but <b>not identical</b>, to the
  the <a href="fileformat.html#varint_format">SQLite varint format</a>.

<p>
  An encoded FTS varint consumes between one and ten bytes of space. The
  number of bytes required is determined by the sign and magnitude of the
  integer value encoded. More accurately, the number of bytes used to store
  the encoded integer depends on the position of the most significant set bit







|







1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
  b-tree and merge the results), but it has been found that in practice this
  overhead is often negligible.
  
<h2>Variable Length Integer (varint) Format</h2>

<p>
  Integer values stored as part of segment b-tree nodes are encoded using the
  FTS varint format. This encoding is similar, but <b>not identical</b>, to
  the <a href="fileformat.html#varint_format">SQLite varint format</a>.

<p>
  An encoded FTS varint consumes between one and ten bytes of space. The
  number of bytes required is determined by the sign and magnitude of the
  integer value encoded. More accurately, the number of bytes used to store
  the encoded integer depends on the position of the most significant set bit
Changes to pages/howtocorrupt.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<title>How To Corrupt An SQLite Database File</title>
<tcl>hd_keywords {how to corrupt}</tcl>

<h1 align=center>How To Corrupt An SQLite Database File</h1>

<p>An SQLite database is highly resistant to corruption.
If an application crash, or an operating-system crash, or a even
a power failure occurs in the middle of a transaction, the partially
written transaction should be automatically rolled back the next time
the database file is accessed.  The recovery process is fully
automatic and does not require any action on the part of the user
or the application.
</p>







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
<title>How To Corrupt An SQLite Database File</title>
<tcl>hd_keywords {how to corrupt}</tcl>

<h1 align=center>How To Corrupt An SQLite Database File</h1>

<p>An SQLite database is highly resistant to corruption.
If an application crash, or an operating-system crash, or even
a power failure occurs in the middle of a transaction, the partially
written transaction should be automatically rolled back the next time
the database file is accessed.  The recovery process is fully
automatic and does not require any action on the part of the user
or the application.
</p>

232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
due to a disk drive failure.  It is very rare, but disks will occasionally
flip a bit in the middle of a sector.</p>

<p>We are told that in some flash memory controllers the wear-leveling logic
can cause random filesystem damage if power is interrupted during a write.
This can manifest, for example, as random changes in the middle of a file
that was not even open at the time of the power loss.  So, for example,
a device would be writing content into a MP3 file in flash memory when a
power loss occurs, and that could result in an SQLite database being
corrupted even though the database as not even in use at the time of the
power loss.</p>

<h2>5.0 Memory corruption</h2>

<p>SQLite is a C-library that runs in the same address space as the 







|







232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
due to a disk drive failure.  It is very rare, but disks will occasionally
flip a bit in the middle of a sector.</p>

<p>We are told that in some flash memory controllers the wear-leveling logic
can cause random filesystem damage if power is interrupted during a write.
This can manifest, for example, as random changes in the middle of a file
that was not even open at the time of the power loss.  So, for example,
a device would be writing content into an MP3 file in flash memory when a
power loss occurs, and that could result in an SQLite database being
corrupted even though the database as not even in use at the time of the
power loss.</p>

<h2>5.0 Memory corruption</h2>

<p>SQLite is a C-library that runs in the same address space as the 
Changes to pages/lang.in.
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
finishes when it is [sqlite3_blob_close() | closed].)^
</p>

<p>
^The explicit COMMIT command runs immediately, even if there are
pending [SELECT] statements.  ^However, if there are pending
write operations, the COMMIT command
will fail with a error code [SQLITE_BUSY].
</p>

<p>
^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code
if an another thread or process has a [shared lock] on the database
that prevented the database from being updated.  ^When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later







|







356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
finishes when it is [sqlite3_blob_close() | closed].)^
</p>

<p>
^The explicit COMMIT command runs immediately, even if there are
pending [SELECT] statements.  ^However, if there are pending
write operations, the COMMIT command
will fail with an error code [SQLITE_BUSY].
</p>

<p>
^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code
if an another thread or process has a [shared lock] on the database
that prevented the database from being updated.  ^When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.

<p>If a table has a single column primary key, and the declared type of that
  column is "INTEGER", then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviours
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must feature a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows







|







782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.

<p>If a table has a single column primary key, and the declared type of that
  column is "INTEGER", then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviors
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must feature a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
the rowid.

<p> The exception mentioned above is that ^if the declaration of a column with
declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
become an alias for the rowid and is not classified as an integer primary key.
This quirk is not by design. It is due to a bug in early versions of SQLite.
But fixing the bug could result in very serious backwards incompatibilities.
The SQLite developers feel that goofy behavior in an corner case is far better
than a compatibility break, so the original behavior is retained. This means
that ^(the following three table declarations all cause the column "x" to be an
alias for the rowid (an integer primary key):

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>







|







884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
the rowid.

<p> The exception mentioned above is that ^if the declaration of a column with
declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
become an alias for the rowid and is not classified as an integer primary key.
This quirk is not by design. It is due to a bug in early versions of SQLite.
But fixing the bug could result in very serious backwards incompatibilities.
The SQLite developers feel that goofy behavior in a corner case is far better
than a compatibility break, so the original behavior is retained. This means
that ^(the following three table declarations all cause the column "x" to be an
alias for the rowid (an integer primary key):

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
remaining rows are deleted.

<p>^If the DELETE statement has no ORDER BY clause, then all rows that
would be deleted in the absence of the LIMIT clause are assembled in an
arbitrary order before applying the LIMIT and OFFSET clauses to determine 
the subset that are actually deleted.

<p>^(The ORDER BY clause on an DELETE statement is used only to determine which
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
and is not influenced by the ORDER BY clause.)^

<tcl>hd_fragment truncateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>^When the WHERE is omitted from a DELETE statement and the table







|







1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
remaining rows are deleted.

<p>^If the DELETE statement has no ORDER BY clause, then all rows that
would be deleted in the absence of the LIMIT clause are assembled in an
arbitrary order before applying the LIMIT and OFFSET clauses to determine 
the subset that are actually deleted.

<p>^(The ORDER BY clause on a DELETE statement is used only to determine which
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
and is not influenced by the ORDER BY clause.)^

<tcl>hd_fragment truncateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>^When the WHERE is omitted from a DELETE statement and the table
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
  ^The load_extension(X,Y) function loads SQLite extensions out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  ^The load_extension() function
  raises an exception if the extension fails to load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete a SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>
}







|







2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
  ^The load_extension(X,Y) function loads SQLite extensions out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  ^The load_extension() function
  raises an exception if the extension fails to load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete an SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>
}
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates a single new row in
an existing table. ^If no column-list is specified then the number of
values must be the same as the number of columns in the table. ^In this case
the result of evaluting the left-most expression in the VALUES list is 
inserted into the left-most column of the new row, and so on. ^If a
column-list is specified, then the number of values must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the default
column value (specified as part of the CREATE TABLE statement), or with NULL if
no default value is specified.







|







2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates a single new row in
an existing table. ^If no column-list is specified then the number of
values must be the same as the number of columns in the table. ^In this case
the result of evaluating the left-most expression in the VALUES list is 
inserted into the left-most column of the new row, and so on. ^If a
column-list is specified, then the number of values must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the default
column value (specified as part of the CREATE TABLE statement), or with NULL if
no default value is specified.
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that
are deleted by the REPLACE conflict resolution strategy.  ^Nor does
REPLACE increment the [sqlite3_changes | change counter].
The exceptional behaviors defined in this paragraph might change 
in a future release.</p>
</dl>

<p>^The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
^If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex REINDEX








|







2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that
are deleted by the REPLACE conflict resolution strategy.  ^Nor does
REPLACE increment the [sqlite3_changes | change counter].
The exceptional behaviors defined in this paragraph might change 
in a future release.</p>
</dl>

<p>^The algorithm specified in the OR clause of an INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
^If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex REINDEX

3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
(columns are numbered from left to right starting with 1).

<li><p>^If the ORDER BY expression is an identifier that corresponds to
the alias of one of the output columns, then the expression is considered
an alias for that column.

<li><p>^Otherwise, if the ORDER BY expression is any other expression, it 
is evaluated and the the returned value used to order the output rows. ^If
the SELECT statement is a simple SELECT, then an ORDER BY may contain any
arbitrary expressions. ^However, if the SELECT is a compound SELECT, then
ORDER BY expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column.
</ol>

<p>^For the purposes of sorting rows, values are compared in the same way







|







3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
(columns are numbered from left to right starting with 1).

<li><p>^If the ORDER BY expression is an identifier that corresponds to
the alias of one of the output columns, then the expression is considered
an alias for that column.

<li><p>^Otherwise, if the ORDER BY expression is any other expression, it 
is evaluated and the returned value used to order the output rows. ^If
the SELECT statement is a simple SELECT, then an ORDER BY may contain any
arbitrary expressions. ^However, if the SELECT is a compound SELECT, then
ORDER BY expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column.
</ol>

<p>^For the purposes of sorting rows, values are compared in the same way
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.

<p>^The optional conflict-clause allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.







|







3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurrence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.

<p>^The optional conflict-clause allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422


<tcl>
##############################################################################
Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}

</tcl>
<p>^The INDEXED BY phrase is a SQL extension found only in SQLite which can
be used to verify that the correct indices are being used on a [DELETE],
[SELECT], or [UPDATE] statement.
^The INDEXED BY phrase always follows the name of a table that SQLite will
be reading.  The INDEXED BY phrase can be seen in the following syntax
diagrams:</p>

<tcl>







|







3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422


<tcl>
##############################################################################
Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}

</tcl>
<p>^The INDEXED BY phrase is an SQL extension found only in SQLite which can
be used to verify that the correct indices are being used on a [DELETE],
[SELECT], or [UPDATE] statement.
^The INDEXED BY phrase always follows the name of a table that SQLite will
be reading.  The INDEXED BY phrase can be seen in the following syntax
diagrams:</p>

<tcl>
Changes to pages/lockingv3.in.
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
    </ul>
</li>
</ul>

<tcl>HEADING 2 {Dealing with hot journals} hot_journals</tcl>

<p>
Before reading from a a database file, SQLite always checks to see if that
database file has a hot journal.  If the file does have a hot journal, then
the journal is rolled back before the file is read.  In this way, we ensure
that the database file is in a consistent state before it is read.
</p>

<p>When a process wants to read from a database file, it followed
the following sequence of steps:







|







224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
    </ul>
</li>
</ul>

<tcl>HEADING 2 {Dealing with hot journals} hot_journals</tcl>

<p>
Before reading from a database file, SQLite always checks to see if that
database file has a hot journal.  If the file does have a hot journal, then
the journal is rolled back before the file is read.  In this way, we ensure
that the database file is in a consistent state before it is read.
</p>

<p>When a process wants to read from a database file, it followed
the following sequence of steps:
Changes to pages/oldnews.in.
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
  An analysis of the problem suggests that the bug might be able to cause
  database corruption, however focused efforts to find a real-world test
  cases that actually causes database corruption have so far been unsuccessful.
  Hence, the likelihood of this bug causing problems is low.  Nevertheless,
  we have decided to do an emergency branch release out of an abundance of
  caution.

  The [version 3.6.6.2] release also fixes a obscure memory leak that
  can occur following a disk I/O error.
}

newsitem {2008-Nov-22} {Version 3.6.6.1} {
  This release fixes a bug that was introduced into SQLite [version 3.6.4]
  and that can cause database corruption in obscure cases.  This bug has
  never been seen in the wild; it was first detected by internal stress







|







233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
  An analysis of the problem suggests that the bug might be able to cause
  database corruption, however focused efforts to find a real-world test
  cases that actually causes database corruption have so far been unsuccessful.
  Hence, the likelihood of this bug causing problems is low.  Nevertheless,
  we have decided to do an emergency branch release out of an abundance of
  caution.

  The [version 3.6.6.2] release also fixes an obscure memory leak that
  can occur following a disk I/O error.
}

newsitem {2008-Nov-22} {Version 3.6.6.1} {
  This release fixes a bug that was introduced into SQLite [version 3.6.4]
  and that can cause database corruption in obscure cases.  This bug has
  never been seen in the wild; it was first detected by internal stress
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
  and will avoid an entire class of stack overflow bugs that have caused
  problems in the past.  Even though this change is large, extensive testing
  has found zero errors in the new virtual machine and so we believe this
  to be a very stable release.
}

newsitem {2007-Dec-14} {Version 3.5.4} {
  Version 3.5.4 fixes an long-standing but obscure bug in UPDATE and
  DELETE which might cause database corruption.  (See ticket #2832.)
  Upgrading is recommended for all users.

  This release also brings the processing of ORDER BY statements into
  compliance with standard SQL.  This could, in theory, cause problems
  for existing applications that depend on the older, buggy behavior.
  See ticket #2822 for additional information.







|







374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
  and will avoid an entire class of stack overflow bugs that have caused
  problems in the past.  Even though this change is large, extensive testing
  has found zero errors in the new virtual machine and so we believe this
  to be a very stable release.
}

newsitem {2007-Dec-14} {Version 3.5.4} {
  Version 3.5.4 fixes a long-standing but obscure bug in UPDATE and
  DELETE which might cause database corruption.  (See ticket #2832.)
  Upgrading is recommended for all users.

  This release also brings the processing of ORDER BY statements into
  compliance with standard SQL.  This could, in theory, cause problems
  for existing applications that depend on the older, buggy behavior.
  See ticket #2822 for additional information.
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
  This release fixes many minor bugs and documentation typos and
  provides some minor new features and performance enhancements.
  Upgrade only if you are having problems or need one of the new features.
}

newsitem {2006-Feb-11} {Version 3.3.4} {
  This release fixes several bugs, including a 
  a blunder that might cause a deadlock on multithreaded systems.
  Anyone using SQLite in a multithreaded environment should probably upgrade.
}

newsitem {2006-Jan-31} {Version 3.3.3 stable} {
  There have been no major problems discovered in version 3.3.2, so
  we hereby declare the new APIs and language features to be stable
  and supported.







|







602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
  This release fixes many minor bugs and documentation typos and
  provides some minor new features and performance enhancements.
  Upgrade only if you are having problems or need one of the new features.
}

newsitem {2006-Feb-11} {Version 3.3.4} {
  This release fixes several bugs, including a 
  blunder that might cause a deadlock on multithreaded systems.
  Anyone using SQLite in a multithreaded environment should probably upgrade.
}

newsitem {2006-Jan-31} {Version 3.3.3 stable} {
  There have been no major problems discovered in version 3.3.2, so
  we hereby declare the new APIs and language features to be stable
  and supported.
Changes to pages/optoverview.in.
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
  PRAGMA case_sensitive_like=ON;
}
PARAGRAPH {
  Then the LIKE operator pays attention to case and the example above would
  evaluate to false.)^  ^Note that case insensitivity only applies to
  latin1 characters - basically the upper and lower case letters of English
  in the lower 127 byte codes of ASCII.  ^International character sets
  are case sensitive in SQLite unless a application-defined
  [collating sequence] and [like | like() SQL function] are provided that
  take non-ASCII characters into account.
  ^But if an application-defined collating sequence and/or like() SQL
  function are provided, the LIKE optimization described here will never
  be taken.
}
PARAGRAPH {







|







345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
  PRAGMA case_sensitive_like=ON;
}
PARAGRAPH {
  Then the LIKE operator pays attention to case and the example above would
  evaluate to false.)^  ^Note that case insensitivity only applies to
  latin1 characters - basically the upper and lower case letters of English
  in the lower 127 byte codes of ASCII.  ^International character sets
  are case sensitive in SQLite unless an application-defined
  [collating sequence] and [like | like() SQL function] are provided that
  take non-ASCII characters into account.
  ^But if an application-defined collating sequence and/or like() SQL
  function are provided, the LIKE optimization described here will never
  be taken.
}
PARAGRAPH {
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
  optimization described above.
}
  

HEADING 1 {Joins} joins

PARAGRAPH {
  ^The ON and USING clauses of a inner join are converted into additional
  terms of the WHERE clause prior to WHERE clause analysis described
  above in paragraph 1.0.  ^(Thus with SQLite, there is no computational
  advantage to use the newer SQL92 join syntax
  over the older SQL89 comma-join syntax.  They both end up accomplishing
  exactly the same thing on inner joins.)^
}
PARAGRAPH {







|







437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
  optimization described above.
}
  

HEADING 1 {Joins} joins

PARAGRAPH {
  ^The ON and USING clauses of an inner join are converted into additional
  terms of the WHERE clause prior to WHERE clause analysis described
  above in paragraph 1.0.  ^(Thus with SQLite, there is no computational
  advantage to use the newer SQL92 join syntax
  over the older SQL89 comma-join syntax.  They both end up accomplishing
  exactly the same thing on inner joins.)^
}
PARAGRAPH {
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
  SQLite provides the ability for advanced programmers to exercise control
  over the query plan chosen by the optimizer. One method for doing this
  is to fudge the [ANALYZE] results in the <b>sqlite_stat1</b> and
  <b>sqlite_stat2</b> tables.  That approach is not recommended except
  for the one scenario described in the following paragraph.
}
PARAGRAPH {
  For an program that uses an SQLite database as its application file
  format, when a new database instances is first created the [ANALYZE]
  command is ineffective because the database contain no data from which
  to gather statistics.  In that case, one could construct a large prototype
  database containing typical data during development and run the 
  [ANALYZE] command on this prototype database to gather statistics,
  then save the prototype statistics as part of the application.
  After deployment, when the application goes to create a new database file,







|







608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
  SQLite provides the ability for advanced programmers to exercise control
  over the query plan chosen by the optimizer. One method for doing this
  is to fudge the [ANALYZE] results in the <b>sqlite_stat1</b> and
  <b>sqlite_stat2</b> tables.  That approach is not recommended except
  for the one scenario described in the following paragraph.
}
PARAGRAPH {
  For a program that uses an SQLite database as its application file
  format, when a new database instances is first created the [ANALYZE]
  command is ineffective because the database contain no data from which
  to gather statistics.  In that case, one could construct a large prototype
  database containing typical data during development and run the 
  [ANALYZE] command on this prototype database to gather statistics,
  then save the prototype statistics as part of the application.
  After deployment, when the application goes to create a new database file,
Changes to pages/pragma.in.
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
  Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content]
  global PragmaDebug
  foreach x $namelist {set PragmaDebug($x) 1}
}

</tcl>

<p>The PRAGMA statement is a SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future







|







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
  Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content]
  global PragmaDebug
  foreach x $namelist {set PragmaDebug($x) 1}
}

</tcl>

<p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
    the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time
    limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</p>
}

Pragma reverse_unordered_selects {
    <p>^(<b>PRAGMA reverse_unordered_selects;
       <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b>)^</p>
    <p>^When enabled, this PRAGMA causes [SELECT] statements without a
    an ORDER BY clause to emit their results in the reverse order of what
    they normally would.  This can help debug applications that are
    making invalid assumptions about the result order.<p>SQLite makes no
    guarantees about the order of results if a SELECT omits the ORDER BY
    clause.  Even so, the order of results does not change from one
    run to the next, and so many applications mistakenly come to depend
    on the arbitrary output order whatever that order happens to be.  However, 







|







680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
    the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time
    limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</p>
}

Pragma reverse_unordered_selects {
    <p>^(<b>PRAGMA reverse_unordered_selects;
       <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b>)^</p>
    <p>^When enabled, this PRAGMA causes [SELECT] statements without
    an ORDER BY clause to emit their results in the reverse order of what
    they normally would.  This can help debug applications that are
    making invalid assumptions about the result order.<p>SQLite makes no
    guarantees about the order of results if a SELECT omits the ORDER BY
    clause.  Even so, the order of results does not change from one
    run to the next, and so many applications mistakenly come to depend
    on the arbitrary output order whatever that order happens to be.  However, 
Changes to pages/requirements.in.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<li>Derived high-level requirements</li>
<li>Low-level requirements</li>
<li>Derived low-level requirements</li>
</ul>

<p>The usual distinction between high-level and low-level requirements is
that high-level requirements describe "what" the system does and the
low-level requirements describe "how" the system does it.  Since the
the requirements denoted here describe the
behavior of SQLite and not its implementation, they are best thought of
as high-level requirements.  Consistent with that view, most of
the requirements numbers begin with the letter "<b>H</b>" (for "high-level"). 
A few of the requirements presented here specify broad objectives that
SQLite strives to achieve.  These broad requirements can be thought of
as system requirements and are number with an initial letter "<b>S</b>".</p>







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<li>Derived high-level requirements</li>
<li>Low-level requirements</li>
<li>Derived low-level requirements</li>
</ul>

<p>The usual distinction between high-level and low-level requirements is
that high-level requirements describe "what" the system does and the
low-level requirements describe "how" the system does it.  Since
the requirements denoted here describe the
behavior of SQLite and not its implementation, they are best thought of
as high-level requirements.  Consistent with that view, most of
the requirements numbers begin with the letter "<b>H</b>" (for "high-level"). 
A few of the requirements presented here specify broad objectives that
SQLite strives to achieve.  These broad requirements can be thought of
as system requirements and are number with an initial letter "<b>S</b>".</p>
Changes to pages/tclsqlite.in.
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
the current connection should be overwritten with new content.  
The default value is <b>main</b> 
(or, in other words, the primary database file).  To repopulate the TEMP tables
use <b>temp</b>.  To overwrite an auxiliary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>source-filename</i> is the name of a existing well-formed SQLite
database file from which the content is extracted.</p>
}

##############################################################################
METHOD version {
  Return the current library version. For example, "3.6.17".
}







|







757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
the current connection should be overwritten with new content.  
The default value is <b>main</b> 
(or, in other words, the primary database file).  To repopulate the TEMP tables
use <b>temp</b>.  To overwrite an auxiliary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>source-filename</i> is the name of an existing well-formed SQLite
database file from which the content is extracted.</p>
}

##############################################################################
METHOD version {
  Return the current library version. For example, "3.6.17".
}
Changes to pages/tempfiles.in.
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369

<p>
More complex queries may or may not be able to employ query
flattening to avoid the temporary table.  Whether or not
the query can be flattened depends on such factors as whether
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and an cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.6 Transient Indices</h3>

<p>







|







355
356
357
358
359
360
361
362
363
364
365
366
367
368
369

<p>
More complex queries may or may not be able to employ query
flattening to avoid the temporary table.  Whether or not
the query can be flattened depends on such factors as whether
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.6 Transient Indices</h3>

<p>
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
new sort algorithm will also use temporary files, but not in the
same way as the current implementation, the temporary files
for the new implementation will probably not be index files.
</p>

<p>
The DISTINCT keyword on an aggregate query is implemented by
creating an transient index in a temporary file and storing
each result row in that index.  As new result rows are computed
a check is made to see if they already exist in the transient
index and if they do the new result row is discarded.
</p>

<p>
The UNION operator for compound queries is implemented by creating







|







416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
new sort algorithm will also use temporary files, but not in the
same way as the current implementation, the temporary files
for the new implementation will probably not be index files.
</p>

<p>
The DISTINCT keyword on an aggregate query is implemented by
creating a transient index in a temporary file and storing
each result row in that index.  As new result rows are computed
a check is made to see if they already exist in the transient
index and if they do the new result row is discarded.
</p>

<p>
The UNION operator for compound queries is implemented by creating
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
<p>
The default setting for the [temp_store pragma] is 0,
which means to following the recommendation of [SQLITE_TEMP_STORE] compile-time
parameter.
</p>

<p>
To reiterate, the [SQLITE_TEMP_STORE] compile-time parameter an the 
[temp_store pragma] only
influence the temporary files other than the rollback journal
and the master journal.  The rollback journal and the master
journal are always written to disk regardless of the settings of
the [SQLITE_TEMP_STORE] compile-time parameter and the
[temp_store pragma].
</p>







|







550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
<p>
The default setting for the [temp_store pragma] is 0,
which means to following the recommendation of [SQLITE_TEMP_STORE] compile-time
parameter.
</p>

<p>
To reiterate, the [SQLITE_TEMP_STORE] compile-time parameter and the 
[temp_store pragma] only
influence the temporary files other than the rollback journal
and the master journal.  The rollback journal and the master
journal are always written to disk regardless of the settings of
the [SQLITE_TEMP_STORE] compile-time parameter and the
[temp_store pragma].
</p>
Changes to pages/uri.in.
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
deleted when the database connection closes.)^  ^If the authority section
is present, then the path is always an absolute pathname.  ^If the 
authority section is omitted, then the path is an absolute pathname if it
begins with the "/" character (ASCII code 0x2f) and is a relative
pathname otherwise.  ^(On windows, if the absolute path begins with
"<b>/<i>X</i>:/</b>" where <b><i>X</i></b> is any single ASCII alphabetic
character ("a" through "z" or "A" through "Z") then the "<b><i>X:</i></b>"
is understood to be the drive letter of the the volume containing the file,
not the toplevel directory.)^

<p>An ordinary filename can usually be converted into an equivalent URI 
by the steps shown below.  The one exception is that a relative windows
pathname with a drive letter cannot be converted directly into a URI; it must
be changed into an absolute pathname first.</p>








|







108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
deleted when the database connection closes.)^  ^If the authority section
is present, then the path is always an absolute pathname.  ^If the 
authority section is omitted, then the path is an absolute pathname if it
begins with the "/" character (ASCII code 0x2f) and is a relative
pathname otherwise.  ^(On windows, if the absolute path begins with
"<b>/<i>X</i>:/</b>" where <b><i>X</i></b> is any single ASCII alphabetic
character ("a" through "z" or "A" through "Z") then the "<b><i>X:</i></b>"
is understood to be the drive letter of the volume containing the file,
not the toplevel directory.)^

<p>An ordinary filename can usually be converted into an equivalent URI 
by the steps shown below.  The one exception is that a relative windows
pathname with a drive letter cannot be converted directly into a URI; it must
be changed into an absolute pathname first.</p>

131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<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 exluding 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>








|







131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<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>

Changes to pages/vfs.in.
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
This file implements a shim that can be used to simulate filesystem faults.
This shim is used during testing to verify that SQLite responses sanely
to hardware malfunctions or to other error conditions such as running out
of filesystem space that are difficult to test on a real system.
</ul>

<p>
There are other VFS inplementations both in the core SQLite source code
library and in available extensions.  The list above is not meant to be
exhaustive but merely representative of the kinds of features that can
be realized using the VFS interface.
</p>

<h2>3.0 VFS Implementations</h2>

<p>
A new VFS is implemented by subclassing three objects:
</p>

<ul>
<li>[sqlite3_vfs]
<li>[sqlite3_io_methods]
<li>[sqlite3_file]
</ul>

<p>
An [sqlite3_vfs] object defines the name of the VFS and the core
methods that implement the interface to the operating system, such
as checking for existance of files, deleting files, creating files
and opening and for reading and/or writing, converting filenames
into their canonical form.  The [sqlite3_vfs] object also contains
methods for obtaining randomness from the operating system, for
suspending a process (sleeping) and for finding the current date and
time.
</p>








|




















|







239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
This file implements a shim that can be used to simulate filesystem faults.
This shim is used during testing to verify that SQLite responses sanely
to hardware malfunctions or to other error conditions such as running out
of filesystem space that are difficult to test on a real system.
</ul>

<p>
There are other VFS implementations both in the core SQLite source code
library and in available extensions.  The list above is not meant to be
exhaustive but merely representative of the kinds of features that can
be realized using the VFS interface.
</p>

<h2>3.0 VFS Implementations</h2>

<p>
A new VFS is implemented by subclassing three objects:
</p>

<ul>
<li>[sqlite3_vfs]
<li>[sqlite3_io_methods]
<li>[sqlite3_file]
</ul>

<p>
An [sqlite3_vfs] object defines the name of the VFS and the core
methods that implement the interface to the operating system, such
as checking for existence of files, deleting files, creating files
and opening and for reading and/or writing, converting filenames
into their canonical form.  The [sqlite3_vfs] object also contains
methods for obtaining randomness from the operating system, for
suspending a process (sleeping) and for finding the current date and
time.
</p>

Changes to pages/vtab.in.
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.

<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have a aConstraint[].usable flag which is true.

<p>In addition to WHERE clause constraints, the SQLite core also 
tells the xBestIndex method about the ORDER BY clause. 
(In an aggregate query, the SQLite core might put in GROUP BY clause 
information in place of the ORDER BY clause information, but this fact
should not make any difference to the xBestIndex method.) 
If all terms of the ORDER BY clause are columns in the virtual table, 







|







583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.

<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have an aConstraint[].usable flag which is true.

<p>In addition to WHERE clause constraints, the SQLite core also 
tells the xBestIndex method about the ORDER BY clause. 
(In an aggregate query, the SQLite core might put in GROUP BY clause 
information in place of the ORDER BY clause information, but this fact
should not make any difference to the xBestIndex method.) 
If all terms of the ORDER BY clause are columns in the virtual table,