Documentation Source Text

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

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

}

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}

}

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}

<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

<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

    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)} {

    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)} {

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

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

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>

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>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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
................................................................................
  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
................................................................................
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>
................................................................................
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
................................................................................
  ^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>
}
................................................................................
</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.
................................................................................
<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

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

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


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

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
................................................................................
  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
................................................................................
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>
................................................................................
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
................................................................................
  ^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>
}
................................................................................
</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.
................................................................................
<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

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

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


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

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

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

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

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

  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 {
................................................................................
  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 {
................................................................................
  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,

  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 {
................................................................................
  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 {
................................................................................
  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,

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

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

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

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

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".
}

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".
}

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

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

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>

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

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>

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

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>

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

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>

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

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

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