Documentation Source Text

      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2012 January 16 (3.7.10)} {
<li>The default [schema format number] is changed from 1 to 4.
    This means that, unless
    the [PRAGMA legacy_file_format | PRAGMA legacy_file_format=ON] statement is
    run, newly created database files will be unreadable by version of SQLite
    prior to 3.3.0 (2006-01-10).  It also means that the [descending indices]
    are enabled by default.
<li>The sqlite3_pcache_methods structure and the [SQLITE_CONFIG_PCACHE]
    and [SQLITE_CONFIG_GETPCACHE] configuration parameters are deprecated.
    They are replaced by a new [sqlite3_pcache_methods2] structure and
    [SQLITE_CONFIG_PCACHE2] and [SQLITE_CONFIG_GETPCACHE2] configuration
    parameters.
<li>Added the [powersafe overwrite] property to the VFS interface.  Provide
    the [SQLITE_IOCAP_POWERSAFE_OVERWRITE] I/O capability, the
    [SQLITE_POWERSAFE_OVERWRITE] compile-time option, and the
    "psow=BOOLEAN" query parameter for [URI filenames].
<li>Added the [sqlite3_db_release_memory()] interface and the
    [shrink_memory pragma].
<li>Added the [sqlite3_db_filename()] interface.
<li>Added the [sqlite3_stmt_busy()] interface.
<li>Added the [sqlite3_uri_boolean()] and [sqlite3_uri_int64()] interfaces.
<li>If the argument to [PRAGMA cache_size] is negative N, that means to use
    approximately -1024*N bytes of memory for the page cache regardless of
    the page size.
<li>Enhanced the default memory allocator to make use of _msize() on windows,
    malloc_size() on Mac, and malloc_usable_size() on Linux.
<li>Enhanced the query planner to support index queries with range constraints
    on the rowid.

    using the xSetSystemCall interface.
<li>Updated the "unix-dotfile" [VFS] to use locking directories with mkdir()
    and rmdir() instead of locking files with open() and unlink().
<li>Enhancements to the test_quota.c extension to support stdio-like interfaces
    with quotas.
<li>Change the unix [VFS] to be tolerant of read() system calls that return 
    less then the full number of requested bytes.
<li>Change both unix and windows [VFSes] to report a sector size of 4096
    instead of the old default of 512.
<li>In the [TCL Interface], add the -uri option to the "sqlite3" TCL command
    used for creating new database connection objects.
<li>Added the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control option with the
    [SQLITE_ENABLE_TREE_EXPLAIN] compile-time option to enable the
    [command-line shell] to display ASCII-art parse trees of SQL statements
    that it processes, for debugging and analysis.
<li><b>Bug fix:</b>
    Add an additional xSync when restarting a WAL in order to prevent an
    exceedingly unlikely but theoretically possible
    database corruption following power-loss.
    Ticket [http://www.sqlite.org/src/info/ff5be73dee | ff5be73dee].
<li><b>Bug fix:</b>
    Change the VDBE so that all registers are initialized to Invalid

  is normally off by default, but if this compile-time parameter is
  set to 1, enforcement of foreign key constraints will be on by default.
}

COMPILE_OPTION {SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=<i>&lt;bytes&gt;</i>} {
  This option sets the size limit on [rollback journal] files in
  [journal_mode pragma | persistent journal mode] and
  [locking_mode | exclusive locking mode] and on the size of the
  write-ahead log file in [WAL mode]. When this 
  compile-time option is omitted there is no upper bound on the
  size of the rollback journals or write-ahead logs.  
  The journal file size limit
  can be changed at run-time using the [journal_size_limit pragma].
}

  This option adds additional logic to the [ANALYZE] command and to
  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 each index and store that data
  in the <b>sqlite_stat3</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
}

COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control
  in the SQLite core.  When the [command-line shell] is also compiled with
  this option, the ".explain" dot-command enables a mode that uses the
  [SQLITE_TESTCTRL_EXPLAIN_STMT] interface to display an ASCII-art diagram
  of the parse tree for each SQL query statement that is run in the shell.
  This mechanism is useful for debugging the SQLite parser and code
  generator.  This whole mechanism is highly experimental and could change
  drastically or be eliminated in future releases of SQLite.
}

COMPILE_OPTION {SQLITE_ENABLE_UPDATE_DELETE_LIMIT} {
  This option enables an optional ORDER BY and LIMIT clause on 
  [UPDATE] and [DELETE] statements.

  <p>If this option is defined, then it must also be 
  defined when using the 'lemon' tool to generate a parse.c

If there are no ATTACHed databases (or if none of the ATTACHed database
is participating in the current transaction) no master journal is
created and the normal rollback journal contains an empty string
in the place normally reserved for recording the name of the master
journal.</p>

<tcl>hd_fragment hotjrnl {hot journal}</tcl>
<p>A rollback journal is said to be <em>hot</em>
if it needs to be rolled back
in order to restore the integrity of its database.  
A hot journal is created when a process is in the middle of a database
update and a program or operating system crash or power failure prevents 
the update from completing.
Hot journals are an exception condition. 
Hot journals exist to recover from crashes and power failures.

  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2012-January-16} {Version 3.7.10} {
  SQLite [version 3.7.10] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases is recommended.

  The [SQLITE_CONFIG_PCACHE] mechanism has been replaced with
  [SQLITE_CONFIG_PCACHE2].  If you do not know what this mechanism
  is (it is an extreme corner-case and is seldom used) then this 
  change will not effect you in the least.

  The default [schema format number] for new database files has changed
  from 1 to 4.  SQLite has been able to generate and read database files
  using schema format 4 for six years.  But up unto now, the default
  schema format has been 1 so that older versions of SQLite could read
  and write databases generated by newer versions of SQLite.  But those
  older versions of SQLite have become so scarce now that it seems 
  reasonable to make the new format the default.

  SQLite is changing some of the assumptions it makes above the behavior
  of disk drives and flash memory devices during a sudden power loss.
  This change is completely transparent to applications.
  Read about the [powersafe overwrite] property for additional information.

  Lots of new interfaces have been added in this release:
  <ul>
  <li> [sqlite3_db_release_memory()]
  <li> [PRAGMA shrink_memory]
  <li> [sqlite3_db_filename()]
  <li> [sqlite3_stmt_busy()]
  <li> [sqlite3_uri_boolean()]
  <li> [sqlite3_uri_int64()]
  </ul>

  The [PRAGMA cache_size] statement has been enhanced.  Formerly, you would
  use this statement to tell SQLite how many pages of the database files it
  should hold in its cache at once.  The total memory requirement would
  depend on the database page size. Now, if you give [PRAGMA cache_size]
  a negative value -N, it will allocate roughly N 
  [http://en.wikipedia.org/wiki/Kibibyte | kibibytes] of memory to cache,
  divided up according to page size.  This enhancement allows programs to
  more easily control their memory usage.

  There have been several obscure bug fixes.  One noteworthy bug,
  ticket [http://www.sqlite.org/src/info/ff5be73dee | ff5be73dee],
  could in theory result in a corrupt database file if a power loss
  occurred at just the wrong moment on an unusually cantankerous disk
  drive.  But that is mostly a theoretical concern and is very unlikely
  to happen in practice.  The bug was found during laboratory testing
  and has never been observed to occur in the wild.
}

newsitem {2011-November-01} {Version 3.7.9} {
  SQLite [version 3.7.9] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, and 3.7.8 is optional.
  Upgrading from other versions is recommended.

  The [SQLITE_ENABLE_STAT2] compile-time option is now a no-op.  The enhanced

  Every version of SQLite is better tested than the previous, and 3.7.8
  is no exception to this rule.  Version 3.7.8 has been used internally by
  the SQLite team for mission critical functions and has performed flawlessly.
  And, of course, it passes our rigorous [testing] procedures with no
  problems detected.  Version 3.7.8 is recommended for all new development.
}



























































































































































































































































































</tcl>

<a href="oldnews.html">Old news...</a>

<?xml version="1.0" encoding="Windows-1252"?>
<title>SQLite Older News</title>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} $date _ tag
  hd_puts "<a name=\"$tag\"></a>"
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2011-June-28} {Version 3.7.7.1} {
  SQLite [version 3.7.7.1] adds a one-line bug fix to 3.7.7 to fix
  [http://www.sqlite.org/src/info/25ee812710 | a problem]
  causing [PRAGMA case_sensitive_like] statements compiled using the legacy
  [sqlite3_prepare()] interface to fail with an [SQLITE_SCHEMA] error. Because
  [sqlite3_exec()] uses sqlite3_prepare() internally, the problem also affects
  sqlite3_exec().

  Upgrading from 3.7.7 is only required for applications that use "PRAGMA
  case_sensitive_like" and the sqlite3_prepare() (or sqlite3_exec()) interface.
}

newsitem {2011-June-24} {Version 3.7.7} {
  SQLite [version 3.7.7] is a regularly scheduled bi-monthly maintenance
  release.  Upgrading from version 3.7.6.3 is optional.  Upgrading from all
  prior releases is recommended.

  This release adds support for naming database files using [URI filenames].
  URI filenames are disabled by default (for backwards compatibility) but
  applications are encouraged to enable them since incompatibilities are
  likely to be exceedingly rare and the feature is useful.  See the 
  [URI filenames | URI filename documentation] for details.

  Most of the other enhancements in this release involve 
  [virtual tables].  The virtual table interface has been enhanced to
  support [SAVEPOINT] and [ON CONFLICT] clause processing, and the built-in 
  [RTREE] and [FTS3 | FTS3/FTS4] have been augmented to take advantage of 
  the new capability.  This means, for example, that it is now possible
  to use the [REPLACE] command on [FTS3 | FTS3/FTS4] and [RTREE] tables.

  The [FTS4] full-text index extension has been enhanced to support
  the [FTS4 prefix option] and the [FTS4 order option].  These two enhancements
  are provided in support of search-as-you-type interfaces where search
  results begin to appear after the first keystroke in the "search" box
  and are refined with each subsequent keystroke.  The way this is done is
  to do a separate full-text search after each key stroke, and add the
  "*" wildcard at the end of the word currently being typed.  So, for
  example, if the text typed so far is "fast da" and the next character
  typed is "t", then the application does a full-text search of the
  pattern "fast dat*" and displays the results.  Such capability has
  always existed.  What is new is that the [FTS4 prefix option] allows
  the search to be very fast (a matter of milliseconds) even for difficult
  cases such as "t*" or "th*".

  There has been a fair amount of work done on the FTS4 module for this
  release.  But the core SQLite code has changed little and the previous
  release has not given any problems, so we expect this to be a very
  stable release.
}

newsitem {2011-May-19} {Version 3.7.6.3} {
  SQLite [version 3.7.6.3] is a patch release that fixes a 
  [http://www.sqlite.org/src/info/2d1a5c67df | single bug]
  associated with [WAL mode].  The bug has been in SQLite ever since WAL
  was added, but the problem is very obscure and so nobody has noticed
  before now.  Nevertheless, all users are encouraged to upgrade to
  version 3.7.6.3 or later.

  The bug is this:
  If the [cache_size] is set very small (less than 10) and SQLite comes
  under memory pressure and if a multi-statement transaction is started
  in which the last statement prior to COMMIT is a SELECT statement and if
  a [checkpoint] occurs right after the transaction commit, then
  it might happen that the transaction will be silently rolled back instead
  of being committed.

  The default setting for [cache_size] is 2000. So in most situations, this
  bug will never appear.  But sometimes programmers set [cache_size] to
  very small values on gadgets and other low-memory devices in order to
  save memory space.  Such applications are vulnerable.
  Note that this bug does <u>not</u> cause database corruption.  It is
  as if [ROLLBACK] were being run instead of [COMMIT] in some cases.

  <b>Bug Details</b>

  Transactions commit in WAL mode by adding a record onto the end of
  the WAL (the write-ahead log) that contains a "commit" flag.  So to
  commit a transaction, SQLite takes all the pages that have changed
  during that transaction, appends them to the WAL, and sets the commit
  flag on the last page.  Now, if SQLite comes under memory pressure, it
  might try to free up memory space by writing changed pages to the WAL
  prior to the commit.  We call this "spilling" the cache to WAL.  There 
  is nothing wrong with spilling cache to WAL.  But if the
  memory pressure is severe, it might be that by the time [COMMIT] is run,
  all changed pages for the transaction have already been spilled to WAL
  and there are no pages left to be written to WAL.
  And with no unwritten pages, there was nothing to put the commit flag
  on.  And without a commit flag, the transaction would end up being
  rolled back.

  The fix to this problem was that if all changed pages has already
  been written to the WAL when the commit was started, then page 1 of
  the database will be written to the WAL again, so that there will always
  be a page available on which to set the commit flag.
}

newsitem {2011-April-17} {Version 3.7.6.2} {
  SQLite [version 3.7.6.2] adds a one-line bug fix to 3.7.6.1 that enables
  pthreads to work correctly on NetBSD.  The problem was a faulty function
  signature for the open system call.  The problem does not appear to have
  any adverse impact on any system other than NetBSD.

  Upgrading from version 3.7.6.1 is only needed on NetBSD.
}

newsitem {2011-April-13} {Version 3.7.6.1} {
  SQLite [version 3.7.6.1] fixes a single bug in 3.7.6 that can cause a
  segfault if [SQLITE_FCNTL_SIZE_HINT] is used on a unix build that has
  SQLITE_ENABLE_LOCKING_MODE set to 0 and is compiled with
  HAVE_POSIX_FALLOCATE.

  Upgrading from 3.7.6 is only needed for users effected by the 
  configuration-specific bug described above.  There are no other changes
  to the code.
}

newsitem {2011-April-12} {Version 3.7.6} {
  SQLite [version 3.7.6] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from version 3.7.5 is optional.  Upgrading
  releases prior to 3.7.5 is recommended.
}

newsitem {2011-February-01} {Version 3.7.5} {
  SQLite [version 3.7.5] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Due to the discovery and fix of 
  [http://www.sqlite.org/src/tktview?name=5d863f876e | an obscure bug]
  that could cause database corruption, upgrading from all prior
  releases of SQLite is recommended.  This bug was found during code
  review and has not been observed in the wild.

  This release adds new [SQLITE_DBSTATUS_LOOKASIDE_HIT | opcodes] for the
  [sqlite3_db_status()] interface that allow more precise measurement of
  how the [lookaside memory allocator] is performing, which can be useful
  for tuning in applications with very tight memory constraints.

  The [sqlite3_vsnprintf()] interface was added.  This routine is simply
  a varargs version of the long-standing [sqlite3_snprintf()] interface.

  The output from [sqlite3_trace()] interface has been enhanced to work
  better (and faster) in systems that use recursive extensions such as
  [FTS3] or [RTREE].

  Testing with Valgrind shows that this release of SQLite is about 1%
  or 2% faster than the previous release for most operations.

  A fork of the popular ADO.NET adaptor for SQLite known as System.Data.SQLite
  is now available on [http://System.Data.SQLite.org/].  The originator
  of System.Data.SQLite, Robert Simpson, is aware of this fork, has
  expressed his approval, and has commit privileges on the new Fossil
  repository.  The SQLite development team intends to maintain
  System.Data.SQLite moving forward.
}

newsitem {2010-December-08} {Version 3.7.4} {
  SQLite [version 3.7.4] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from [version 3.7.2] and [version 3.7.3]
  is optional.  Upgrading from all other SQLite releases is recommended.

  This release features [full-text search] enhancements.  The older 
  [FTS3] virtual table is still fully supported, and should also run
  faster.  In addition, the new [FTS4] virtual table is added.  FTS4
  follows the same syntax as FTS3 but holds additional metadata which
  facilitates some performance improvements and more advanced 
  [matchinfo()] output.  Look for further full-text search enhancements
  in subsequent releases.

  Also in this release, the [EXPLAIN QUERY PLAN] output has been enhanced
  and new documentation is provided so that application developers can
  more easily understand how SQLite is performing their queries.

  Thanks to an account from the folks at [http://www.devio.us/], OpenBSD
  has been added to the list of platforms upon which we 
  [tested | test SQLite] prior to every release.  That list of platforms
  now includes:

  <ul>
  <li> Linux x86 &amp; x86_64
  <li> MacOS 10.5 &amp; 10.6
  <li> MacOS 10.2 PowerPC
  <li> WinXP and Win7
  <li> Android 2.2
  <li> OpenBSD 4.7
  </ul>

  The previous release of SQLite ([version 3.7.3]) has proven to be very
  robust.  The only serious issue discovered was
  [http://www.sqlite.org/src/info/80ba201079 | ticket 80ba201079] that
  describes an incorrect query result that can occur under very 
  unusual circumstances.  The ticket description contains details of the
  problem.  Suffice it to say here that the problem is very obscure and
  is unlikely to effect most applications and so upgrading is optional.
  The problem is fixed, of course, in this release.
}

newsitem {2010-October-08}  {Version 3.7.3} {
  SQLite [version 3.7.3] is a regularly scheduled bi-monthly maintenance
  release of SQLite.  Upgrading from [version 3.7.2] is optional.
  Upgrading from all other releases is recommended.

  This release adds two new interfaces (really just variations on existing
  interfaces).  The [sqlite3_create_function_v2()] interface adds a 
  destructor for the application-data pointer.  The new 
  [sqlite3_soft_heap_limit64()] interface allows the soft heap limit to
  be set to a value greater than 2<sup>31</sup>. 

  The [RTREE] extension has been enhanced with the ability to have an
  [custom r-tree queries | application-defined query region].  This might
  be used, for example, to locate all objects within 
  the field of view of a camera.

  The 3.7.3 release also includes some performance enhancements, including
  query planner improvements, documentation updates,
  and fixes to some very obscure bugs.
}

newsitem {2010-August-24} {Version 3.7.2} {
  SQLite [version 3.7.2] fixes a long-standing bug that can cause the
  database [free-page list] to go corrupt if [incremental_vacuum] is used
  multiple times to
  partially reduce the size of a database file that contains many hundreds
  of unused database pages.  The original bug reports together with links
  to the patch that fixes it can be seen 
  <a href="http://www.sqlite.org/src/info/5e10420e8d">here</a>.

  This bug has been in the code for at least a year and possibly longer.
  The bug has nothing to do with the versions 3.7.1 or 3.7.0 or any other
  recent release.  The fact that the bug was discovered (and fixed)
  within hours of the 3.7.1 release is purely a coincidence.

  The bug is impossible to hit without using [incremental_vacuum] and is
  very difficult to hit even with incremental_vacuum.  And the kind of
  corruption that the bug causes can usually be fixed 
  simply by running [VACUUM].  Nevertheless, because the bug can result
  in database corruption, it is recommended that all SQLite users upgrade
  to version 3.7.2 or later.
}

newsitem {2010-August-23} {Version 3.7.1} {
  SQLite [version 3.7.1] is a stabilization release for the 3.7.x series.
  Other than the filesize-in-header bug that was fixed in [version 3.7.0.1],
  no major problems have been seen in 3.7.0.  Some minor corner-case
  performance regressions have been fixed.  A typo in the OS/2 interface
  has been repaired.

  A biggest part of the 3.7.1 release is a cleanup and refactoring of
  the pager module within SQLite.  This refactoring should have no
  application-visible effects.  The purpose was to reorganize the code
  in ways that make it easier to prove correctness.

  The 3.7.1 release adds new experimental methods for obtained more 
  detailed memory usage information and for controlling database file
  fragmentation.  And the query planner now does a better job of
  optimizing the [LIKE] and [GLOB] operators.

  This release increases the maximum size of database pages from 32KiB to
  64KiB.  A database with 64KiB pages will not be readable or writable by
  older versions of SQLite.  Note that further increases in page size
  are not feasible since the [file format] uses 16-bit offsets to structures
  within each page.
}

newsitem {2010-August-04} {Version 3.7.0.1} {
  SQLite [version 3.7.0.1] is a patch release to fix a bug in the new
  filesize-in-header feature of the [file format | SQLite file format]
  that could cause database corruption if the same database file is
  written alternately with version 3.7.0 and version 3.6.23.1 or earlier.
  A performance regression was also fixed in this release.
}

newsitem {2010-July-22} {Version 3.7.0} {
  SQLite [version 3.7.0] is a major release of SQLite that features
  a new transaction control mechanism using a [write-ahead log] or [WAL].
  The traditional rollback-journal is still used as the default so there
  should be no visible change for legacy programs.  But newer programs
  can take advantage of improved performance and concurrency by enabling
  the WAL journaling mode.

  SQLite version 3.7.0 also contains some query planner enhancements and
  a few obscure bug fixes, but the only really big change is the addition
  of WAL mode.
}

newsitem {2010-Mar-30} {Version 3.6.23.1} {
  SQLite [version 3.6.23.1] is a patch release to fix a bug in the
  offsets() function of [FTS3] at the request of the Mozilla.  
}

newsitem {2010-Mar-09} {Version 3.6.23} {

<p>Consider what would happen if disk sector writes are interrupted
by a power loss.  If an application writes two or three bytes in the middle
of some file, the operating system will implement this by first reading
the entire sector containing those bytes, making the change to the
sector in memory, then writing the entire sector back to the disk.  If a power
loss occurs during the writeback and the sector was not completely written,
then on the next read after reboot, error correcting codes
in the sector will probably detect irreparable damage and the disk 
controller will read out the sector as all zeros or all ones.  Thus
values will have changed outside of the range of the two or three bytes 
that were written at the application level - a violation of the powersafe
overwrite property.

<h2>SQLite Assumptions About Powersafe Overwrite</h2>

<p>All versions of SQLite up to and including version 3.7.9 assume that
the filesystem does <u>not</u> provide powersafe overwrite.  SQLite 
has traditionally assumed that when any one byte of a file changes, all
other bytes within the same sector of that byte have the potential of
being corrupted on a power loss.  When writing, SQLite has made sure
to journal all bytes in the same sector of any modifications
and it pads journal files out to the next sector boundary so that
subsequent appends to that journal cannot damage prior records.
SQLite understands the sector size to be the value returned by the
xSectorSize method in the [VFS].  The SQLite team has often referred

seems reasonable that the controller will finish writing
whatever sector it is current working on when the imminent power loss 
is first detected, prior to parking the heads, as long as doing so
does not take too long, which it should not with
small and dense sectors.  Hence it seems reasonable
to assume powersafe overwrite for modern disks.  Indeed, BerkeleyDB has
made this assumption for decades, we are told.  Caution is advised
though. As Roger Binns noted on the SQLite developers mailing list:
"'poorly written' should be the main assumption about drive firmware."

<tcl>hd_fragment tornpage {torn page}</tcl>
<h2>Torn Pages</h2>

<p>A torn page occurs when a database page is larger than a disk sector,
the database page is written to disk, but a power loss occurs prior to