Documentation Source Text

  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from all columns of every index and store that data
  in the [sqlite_stat4] table.  The query planner will then use the
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance is mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histograph data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.

  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by millions of users."</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  Details
  can be found in the discussions on the 
  <a href="atomiccommit.html">atomic commit</a> and 
  <a href="lockingv3.html">locking</a> support in SQLite
  as well as in the mailing list archives.</p>

}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
  when building from canonically source files.  They do <u>not</u> work
  when you build from the SQLite [amalgamation] or from the pre-processed
  source files.

  <p>It is possible to build a special [amalgamation] that will work with
  a predetermined set of SQLITE_OMIT_... options.  Instructions for doing
  so can be found with the [omitfeatures | SQLITE_OMIT_... documentation].
}

faq {
  My WHERE clause expression <tt>column1="column1"</tt> does not work.
  It causes every row of the table to be returned, not just the rows
  where column1 has the value "column1".
} {
  Use single-quotes, not double-quotes, around string literals in SQL.
  This is what the SQL standard requires.  Your WHERE clause expression
  should read: <tt>column1='column2'</tt>

  <p>SQL uses double-quotes around identifiers (column or table names) that
  contains special characters or which are keywords.  So double-quotes are
  a way of escaping identifier names.  Hence, when you say
  <tt>column1="column1"</tt> that is equivalent to 
  <tt>column1=column1</tt> which is obviously always true.
}

18 and 19 are intended to allow for enhancements of the file format
in future versions of SQLite.  In current versions of SQLite, both of
these values are 1 for rollback journalling modes and 2 for [WAL]
journalling mode.  If a version of SQLite coded to the current
file format specification encounters a database file where the read
version is 1 or 2 but the write version is greater than 2, then the database
file must be treated as read-only.  If a database file with a read version
greater than 2 is encounter, then that database cannot be read or written.</p>

<h4>1.2.4 Reserved bytes per page</h4>

<p>SQLite has the ability to set aside a small number of extra bytes at
the end of every page for use by extensions.  These extra bytes are
used, for example, by the SQLite Encryption Extension to store a nonce
and/or cryptographic checksum associated with each page.  ^The 

allowed to be less than 480.  In other words, if the page size is 512,
then the reserved space size cannot exceed 32.)^</p>

<h4>1.2.5 Payload fractions</h4>

<p>^The maximum and minimum embedded payload fractions and the leaf
payload fraction values must be 64, 32, and 32.  These values were
originally intended to as tunable parameters that could be used to
modify the storage format of the b-tree algorithm.  However, that
functionality is not supported and there are no current plans to add
support in the future.  Hence, these three bytes are fixed at the
values specified.</p>

<h4>1.2.6 File change counter</h4>

and will be unable to work around the POSIX advisory locking quirks.
A <tt>close()</tt> operation on one connection might unknowingly 
clear the locks on a different database connection, leading to database
corruption.</p>

<p>The scenario above sounds far-fetched.
But the SQLite developers are aware of at 
least one commercial product that was release
with exactly this bug.  The vendor came to the SQLite developers seeking
help in tracking down some infrequent database corruption issues they were
seeing on Linux and Mac.  The problem was eventually traced to the
fact that the application was linking against two separate copies of SQLite.
The solution was to change the application build procedures to link against
just one copy of SQLite instead of two.</p>

be select for use in an application that has to run on an NFS filesystem
that does not support POSIX advisory locking.</p>

<p>It is important that all connections to the same database file use 
the same locking protocol.
If one application is using POSIX advisory locks and another application
is using dot-file locking, then the two applications will not see each
others locks and will not be able to coordinate database access, possibly
leading to database corruption.</p>

<tcl>hd_fragment unlink {unlink corruption} {unlinked database files}</tcl>
<h3>2.4 Unlinking or renaming a database file while in use</h3>

<p>If two processes have open connections to the same database file and
one process closes its connection, unlinks the file, then creates a new

<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and
<tt>FlushFileBuffers()</tt> under windows.  We call this flush of
pending writes a "sync".</p>

<p>Actually, if one is only concerned with atomic and consistent writes and
is willing to forego durable writes, the sync operation does not need
to wait until the content is completely stored on persistent media.  Instead,
the sync operation can be thought of as an I/O barrier.  As long as all
writes that occur before the sync are completed before any write that happens

historical bugs in SQLite (now fixed) that could cause database corruption.
And there may be yet a few more that remain undiscovered.  Because of the
extensive testing and widespread use of SQLite, bugs that result in
database corruption tend to be very obscure.  The likelihood
of an application encountering an SQLite bug is small.  To illustrate this,
an account is given below 
of all database-corruption bugs found in SQLite during the
two-year period from 2009-04-01 to 2013-04-15.
This account should give the reader an intuitive sense of the
kinds of bugs in SQLite that manage to slip through testing procedures
and make it into a release.</p>


<h3>7.1 False corruption reports due to database shrinkage</h3>

<p>If a database is written by SQLite version 3.7.0 or later and then
written again by SQLite version 3.6.23 or earlier in such a way as to
make the size of the database file decrease, then the next time that
SQLite version 3.7.0 access the database file, it might report that the
database file is corrupt.  The database file is not really corrupt, however.
Version 3.7.0 was simply begin overly zealous in its corruption detection.</p>

<p>The problem was fixed on 2011-02-20.  The fix first appears in
SQLite version 3.7.6.</p>

<h3>7.2 Corruption follow switches between rollback and WAL modes</h3>

<p>Repeatedly switching an SQLite database in and out of [WAL | WAL mode]
and running the [VACUUM] command in between switches, in one process or
thread, can cause another process or thread that has the database file
open to miss the fact that the database has changed.  That second process
or thread might then try to modify the database using a stale cache and
cause database corruption.</p>

has already completed, allowing that process to continue using the
database file without running recovery first.  If that process writes
to the file, then the file might go corrupt.  This race condition
had apparently existing in all prior versions of SQLite for Windows going
back to 2004.  But the race was very tight.  Practically speaking, you
need a fast multi-core machine in which you launch two processes to run
recovery at the same moment on two separate cores.  This defect was
on windows systems only and did not effect the posix OS interface.</p>

<h3>The RAISE() function</h3>

<p>^(A special SQL function RAISE() may be used within a trigger-program,)^
with the following syntax</p> 

<tcl>BubbleDiagram raise-function</tcl>


<p>^When one of the first three forms is called during trigger-program
execution, the specified [ON CONFLICT] processing is performed
(either ABORT, FAIL or ROLLBACK) and the current query terminates.
An error code of [SQLITE_CONSTRAINT] is returned to the application,
along with the specified error message.</p>

<p>^When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would of been executed are abandoned. ^No database
changes are rolled back.  ^If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
</p>

<tcl>hd_fragment temptrig {TEMP triggers on non-TEMP tables}</tcl>
<h3>TEMP Triggers on Non-TEMP Tables</h3>

[glob(<i>Y</i>,<i>X</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>^The REGEXP operator is a special syntax for the regexp()
user function.  ^No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  ^If a [application-defined SQL function] named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>^The MATCH operator is a special syntax for the match()
application-defined function.  ^The default match() function implementation
raises an exception and is not really useful for anything.
^But extensions can override the match() function with more
helpful logic.</p>

    </li>
<li>Drop the EXCLUSIVE and PENDING locks from the database file.
    </li>
</ol>

<p>As soon as the PENDING lock is released from the database file, other
processes can begin reading the database again.  In the current implementation,
the RESERVED lock is also released, but that is not essential.  Future
versions of SQLite might provide a "CHECKPOINT" SQL command that will
commit all changes made so far within a transaction but retain the
RESERVED lock so that additional changes can be made without given
any other process an opportunity to write.</p>

<p>If a transaction involves multiple databases, then a more complex
commit sequence is used, as follows:</p>

<ol>
<li value="4">
   Make sure all individual database files have an EXCLUSIVE lock and a

errors.
</p></li>

<li><p>
<b>Memory usage limits.</b>
The [sqlite3_soft_heap_limit64()] mechanism allows the application to
set a memory usage limit that SQLite strives to stay below.  SQLite
will attempt to reuse memory from its caches rather than allocation new
memory as it approaches the soft limit.
</p></li>

<li><p>
<b>Zero-malloc option</b>
The application can provide SQLite with several buffers of bulk memory
at startup and SQLite will then use those provided buffers for all of

<p>Memsys6 was added in SQLite [version 3.6.1].
It is very experimental.  Its future is uncertain and it may be removed
in a subsequent release.  Update:  Memsys6 was removed as of 
[version 3.6.5].</p>

<p>Other experimental memory allocators might be added in future releases
of SQLite.  One many anticipate that these will be called memsys7, memsys8,
and so forth.</p>

<a name="appalloc"></a>
<h4>3.1.5 Application-defined memory allocators</h4>

<p>New memory allocators do not have to be part of the SQLite source tree
nor included in the sqlite3.c [amalgamation].  Individual applications can

  <ol>
  <li>^The left-hand side of the LIKE or GLOB operator must be the name
      of an indexed column with [affinity | TEXT affinity].</li>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>^The build-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>

  
<p>    ^The application_id PRAGMA is used to query or set the 32-bit
       unsigned big-endian "Application ID" integer located at offset
       68 into the [database header].  Applications that use SQLite as their
       [application file-format] should set the Application ID integer to
       a unique integer so that utilities such as 
       [http://www.darwinsys.com/file/ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list
       assigned application IDs can be seen by consulting the
       [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt
        |magic.txt] file in the SQLite source repository.
}

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;

There are no API changes nor modifications
to compilation procedures.</p>

<p>But as with any query planner change, upgrading to the NGQP does carry
a small risk of introducing performance regressions.  The problem here is
not that the NGQP is incorrect or buggy or inferior to the legacy query
planner.  Given reliable information about the selectivity of indices, 
the NGQP should always pick a plan than is as good or better than before.
The problem is that some applications may be using low-quality and
low-selectivity indices without having run [ANALYZE].  The older query
planners look at many fewer possible implementations for each query and 
so they may have stumbled over a good plan by stupid luck.  The NGQP, on 
the other hand, looks at many more query plan possibilities, and it may 
chose a different query plan that
works better in theory, assuming good indices, but which gives a performance

as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other
words, journal mode PERSIST exhibits the same behavior as is seen
in EXCLUSIVE locking mode. The
OFF journal mode causes SQLite to the rollback journal.


The OFF journal mode disables the atomic
commit and rollback capabilities of SQLite.  The ROLLBACK command
is not available when OFF journal mode is set.  And if a crash or power
loss occurs in the middle of a transaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.
The MEMORY journal mode causes the rollback journal to be stored in

<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.8 Transient Indices</h3>

<p>

compared to the previous to see if it starts a new "group".
The ordering by GROUP BY terms is done in exactly the same way
as the ordering by ORDER BY terms.  A preexisting index is used
if possible, but if no suitable index is available, a transient
index is created.
</p>

<p>
The previous two paragraphs describe the implementation of SQLite
as of version 3.5.8.  There are known problems with this approach
for very large results sets - result sets that are larger than the
available disk cache.  Future versions of SQLite will likely address
this deficiency by completely reworking the sort algorithm for 
cases when no suitable preexisting sort index is available.  The
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>

    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<tt>-shm</tt> shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic
    by default, is still something that application developers need to
    be mindful of.
<li>WAL works best with smaller transactions.  WAL does
    not work well for very large transactions.  For transactions larger than