Documentation Source Text

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

chng {2010 August 19 (3.7.1)} {
chng {2010 August 23 (3.7.1)} {
<li> Added new commands [SQLITE_DBSTATUS_SCHEMA_USED] and
     [SQLITE_DBSTATUS_STMT_USED] to the [sqlite3_db_status()] interface, in
     order to report out the amount of memory used to hold the schema and
     prepared statements of a connection.
<li> Increase the maximum size of a database pages from 32KiB to 64KiB.
<li> Use the [LIKE optimization] even if the right-hand side string contains
     no wildcards.
<li> Added the [SQLITE_FCNTL_CHUNK_SIZE] verb to the [sqlite3_file_control()]
     interface for both unix and windows, to cause database files to grow in
     large chunks in order to reduce disk fragmentation.
<li> Fixed a bug in the query planner that caused performance regresssions
     relative to 3.6.23.1 on some complex joins.
<li> Fixed a typo in the OS/2 backend.
<li> Refactored the pager module.
<li> The SQLITE_MAX_PAGE_SIZE compile-time option is now silently ignored.
     The maximum page size is hard-coded at 65536 bytes.
}

chng {2010 August 04 (3.7.0.1)} {
<li> Fix a potential database corruption bug that can occur if version 3.7.0
     and version 3.6.23.1 alternately write to the same database file.
     <a href="http://www.sqlite.org/src/info/51ae9cad317a1">
     Ticket &#91;51ae9cad317a1&#93;</a>
<li> Fix a performance regression related to the query planner enhancements
     of version 3.7.0.
}

chng {2010 July 22 (3.7.0)} {
<li> Added support for [WAL | write-ahead logging].
<li> Query planner enhancement - automatic transient indices are created
     when doing so reduces the estimated query time.
<li> Query planner enhancement - the ORDER BY becomes a no-op if the query

<li> [SQLITE_MAX_COLUMN]  </li>
<li> [SQLITE_MAX_COMPOUND_SELECT]  </li>
<li> [SQLITE_MAX_EXPR_DEPTH]  </li>
<li> [SQLITE_MAX_FUNCTION_ARG]  </li>
<li> [SQLITE_MAX_LENGTH]  </li>
<li> [SQLITE_MAX_LIKE_PATTERN_LENGTH]  </li>
<li> [SQLITE_MAX_PAGE_COUNT]  </li>
<li> [SQLITE_MAX_PAGE_SIZE]  </li>
<li> [SQLITE_MAX_SQL_LENGTH]  </li>
<li> [SQLITE_MAX_VARIABLE_NUMBER]  </li>
</ul>

<a name="controlfeatures"></a>
<h2>1.3 Options To Control Operating Characteristics</h2>

<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_1.html">Version 3.7.1</a>
of SQLite is recommended for all new development.  Upgrading from
version 3.7.0, 3.6.23, 3.6.22, 3.6.13, or 3.6.12 is optional.
version 3.6.23, 3.6.22, 3.6.13, or 3.6.12 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

<p>
The maximum number of attached databases can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_ATTACHED],size) interface.
</p>
}

limititem {Maximum Database Page Size} SQLITE_MAX_PAGE_SIZE {
<p>
An SQLite database file is organized as pages.  The size of each
page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE.
The default value for SQLITE_MAX_PAGE_SIZE is 32768.  The current
implementation will not support a larger value.
</p>

<p>
It used to be the case that SQLite would allocate some stack
structures whose size was proportional to the maximum page size.
For this reason, SQLite would sometimes be compiled with a smaller
maximum page size on embedded devices with limited stack memory.  But
more recent versions of SQLite put these large structures on the
heap, not on the stack, so reducing the maximum page size is no
longer necessary on embedded devices.  There is no longer any
real reason to lower the maximum page size.
</p>
}

limititem {Maximum Number Of Pages In A Database File} \
          SQLITE_MAX_PAGE_COUNT {
<p>
SQLite is able to limit the size of a database file to prevent
the database file from growing too large and consuming too much
disk space.
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to

  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 "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

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 new release of SQLite that features
  a new transaction control mechanism usingn a [write-ahead log] or [WAL].
  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

  but because they could arise in an application after deployment, it is
  recommended that all applications upgrade SQLite to version 3.6.22.

  This release also includes other minor bug fixes and performance
  enhancements, especially in the [FTS3] extension.
}

newsitem {2009-Dec-07} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}

newsitem {2009-Nov-04} {Version 3.6.20} {
  SQLite [version 3.6.20] is a general maintenance release.  The
  query planner has been enhanced to work better with bound parameters
  in LIKE and GLOB operators and in range constraints and various minor
  bugs have been fixed.  Upgrading from 3.6.19 is optional.
}

newsitem {2009-Oct-14} {Version 3.6.19} {
  SQLite [version 3.6.19] adds native support for
  [foreign key constraints], including deferred constraints and
  cascading deletes.  Enforcement of foreign keys is disabled by
  default for backwards compatibility and must be turned on using
  the [foreign_keys pragma].

  Version 3.6.19 also adds support for the
  [IS operator | IS and IS NOT operators].  Formerly, SQLite (as most
  other SQL database engines) supported IS NULL and IS NOT NULL.  The
  IS and IS NOT operators are generalizations that allow the right-hand
  side to be an arbitrary expression.  IS and IS NOT work the same as
  == (equals) and != (not equals) except that with IS and IS NOT the
  NULL values compare equal to one another.
}

newsitem {2009-Sep-11} {Version 3.6.18} {
  Beginning with this release, the SQLite source code is tracked and
  managed using the [http://www.fossil-scm.org/ | Fossil]
  distributed configuration management system.  SQLite was previously
  versioned using CVS.  The entire CVS history has been imported into
  Fossil.  The older CVS repository remains on the website but is
  read-only.

  There are two major enhancements in SQLite version 3.6.18.  The first
  is a series or refinements to the query planner that help SQLite to
  choose better plans for joins where in the past it was selecting suboptimal
  query plans.  The [SQLITE_ENABLE_STAT2] compile-time option has been
  added to cause SQLite to collect histogram data on indices when the
  [ANALYZE] command is run.  The use of histograms improve the query
  planning performance even more.

  The second major enhancement is that SQLite now support recursive
  triggers.  The older non-recursive behavior of triggers is still the
  default behavior.  Recursive triggers are activated using the
  [recursive_triggers pragma].  In addition to allowing triggers to
  call themselves (either directly or indirectly) the new capability
  also fires DELETE triggers on rows that are removed from a table
  as a result of [ON CONFLICT | REPLACE] conflict resolution processing.

  Non-recursive triggers are still the default behavior since this
  is least likely to cause problems for existing applications.  However,
  we anticipate that triggers will become
  recursive by default beginning with release 3.7.0.  At that point,
  applications that want to continue using the older non-recursive
  trigger behavior will need to use the [recursive_triggers pragma]
  to disable recursive triggers.

  This version of SQLite also contains bug fixes, though none of the
  bugs are serious and all are obscure, so upgrading is optional.

  The SQLite core continues to have [test coverage | 100% branch test coverage]
  and so despite the many changes in this release, the developers 
  believe that this version of SQLite is stable and ready for production
  use.
}

newsitem {2009-Aug-10} {Version 3.6.17} {
  This is a monthly maintenance release with a focus of bug fixes,
  performance improvements, and increased test coverage.  This is the
  first release of SQLite since [test coverage | 100% branch test coverage]
  was achieved on the SQLite core.  

  In addition, a new interface [sqlite3_strnicmp()] is provided for the
  convenience of extension writers.

  None of the bugs fixed in this release are serious.  All bugs are
  obscure.  Upgrading is optional.
}

newsitem {2009-July-25} {100% Branch Test Coverage} {
  A subset of the [TH3] test suite was measured by gcov to provide
  [test coverage | 100% branch test coverage] over the SQLite core
  (exclusive of the VFS backend and of extensions such as FTS3 and RTREE)
  when compiled for SuSE 10.1 Linux on x86.  The SQLite developers pledge
  to maintain branch test coverage at 100% in all future releases.
  Ongoing work will strive for 100% branch test coverage on the 
  operating-system backends and extensions as well.
}

newsitem {2009-Jun-27} {Version 3.6.16} {
  SQLite [version 3.6.16] is another general maintenance release containing
  performance and robustness enhancements.  A single notable bug was fixed
  (ticket #3929).  This bug cause cause INSERT or UPDATE statements to fail
  on indexed tables that have AFTER triggers that modify the same table and
  index.
}

newsitem {2009-Jun-15} {Version 3.6.15} {
  SQLite [version 3.6.15] is a general maintenance release containing
  performance and robustness enhancements and fixes for various obscure
  bugs.  
}

newsitem {2009-May-25} {Version 3.6.14.2} {
  SQLite [version 3.6.14.2] fixes an obscure bug in the code generator
  (<a href="http://www.sqlite.org/cvstrac/tktview?tn=3879">ticket #3879</a>)
  section of SQLite which can potentially cause incorrect query results.
  The changes from the prior release consist of only this one bug fix,
  check-in <a href="http://www.sqlite.org/cvstrac/chngview?cn=6676">[6676]</a>
  and a change to the version number text.

  The bug was introduced in version 3.6.14.  It is recommended that
  users of version 3.6.14 and 3.6.14.1 upgrade to this release.  Applications
  are unlikely to hit this bug, but since it is difficult to predict which
  applications might hit it and which might not, we recommend that all
  users of 3.6.14 and 3.5.14.1 upgrade to this release.
}

newsitem {2009-May-19} {Version 3.6.14.1} {
  SQLite [version 3.6.14.1] is a patch release to [version 3.6.14] with
  minimal changes that fixes three bugs.  Upgrading is only necessary 
  for users who are impacted by one or more of those bugs.
}

newsitem {2009-May-07} {Version 3.6.14} {
  SQLite [version 3.6.14] provides new performance enhancements in
  the btree and pager layers and in the query optimizer.  Certain
  workloads can be as much as twice as fast as the previous release,
  though 10% faster is a more typical result.

  Queries against [virtual tables] that contain OR and IN operators
  in the WHERE clause are now able to use indexing.

  A new optional [asynchronous I/O backend] is available for
  unix and windows.  The asynchronous backend gives the illusion of faster
  response time by pushing slow write operations into a background thread.
  The tradeoff for faster response time is that more memory is required
  (to hold the content of the pending writes) and if a power failure or
  program crash occurs, some transactions that appeared to have committed
  might end up being rolled back upon restart.

  This release also contains many minor bug fixes, documentation enhancements,
  new test cases, and cleanups and simplifications to the source code.

  There is no compelling reason to upgrade from versions 3.6.12 or
  3.6.13 if those prior versions are working.  Though many users may
  benefit from the improved performance.
}

</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 "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}


newsitem {2009-Dec-07} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}

newsitem {2009-Nov-04} {Version 3.6.20} {
  SQLite [version 3.6.20] is a general maintenance release.  The
  query planner has been enhanced to work better with bound parameters
  in LIKE and GLOB operators and in range constraints and various minor
  bugs have been fixed.  Upgrading from 3.6.19 is optional.
}

newsitem {2009-Oct-14} {Version 3.6.19} {
  SQLite [version 3.6.19] adds native support for
  [foreign key constraints], including deferred constraints and
  cascading deletes.  Enforcement of foreign keys is disabled by
  default for backwards compatibility and must be turned on using
  the [foreign_keys pragma].

  Version 3.6.19 also adds support for the
  [IS operator | IS and IS NOT operators].  Formerly, SQLite (as most
  other SQL database engines) supported IS NULL and IS NOT NULL.  The
  IS and IS NOT operators are generalizations that allow the right-hand
  side to be an arbitrary expression.  IS and IS NOT work the same as
  == (equals) and != (not equals) except that with IS and IS NOT the
  NULL values compare equal to one another.
}

newsitem {2009-Sep-11} {Version 3.6.18} {
  Beginning with this release, the SQLite source code is tracked and
  managed using the [http://www.fossil-scm.org/ | Fossil]
  distributed configuration management system.  SQLite was previously
  versioned using CVS.  The entire CVS history has been imported into
  Fossil.  The older CVS repository remains on the website but is
  read-only.

  There are two major enhancements in SQLite version 3.6.18.  The first
  is a series or refinements to the query planner that help SQLite to
  choose better plans for joins where in the past it was selecting suboptimal
  query plans.  The [SQLITE_ENABLE_STAT2] compile-time option has been
  added to cause SQLite to collect histogram data on indices when the
  [ANALYZE] command is run.  The use of histograms improve the query
  planning performance even more.

  The second major enhancement is that SQLite now support recursive
  triggers.  The older non-recursive behavior of triggers is still the
  default behavior.  Recursive triggers are activated using the
  [recursive_triggers pragma].  In addition to allowing triggers to
  call themselves (either directly or indirectly) the new capability
  also fires DELETE triggers on rows that are removed from a table
  as a result of [ON CONFLICT | REPLACE] conflict resolution processing.

  Non-recursive triggers are still the default behavior since this
  is least likely to cause problems for existing applications.  However,
  we anticipate that triggers will become
  recursive by default beginning with release 3.7.0.  At that point,
  applications that want to continue using the older non-recursive
  trigger behavior will need to use the [recursive_triggers pragma]
  to disable recursive triggers.

  This version of SQLite also contains bug fixes, though none of the
  bugs are serious and all are obscure, so upgrading is optional.

  The SQLite core continues to have [test coverage | 100% branch test coverage]
  and so despite the many changes in this release, the developers 
  believe that this version of SQLite is stable and ready for production
  use.
}

newsitem {2009-Aug-10} {Version 3.6.17} {
  This is a monthly maintenance release with a focus of bug fixes,
  performance improvements, and increased test coverage.  This is the
  first release of SQLite since [test coverage | 100% branch test coverage]
  was achieved on the SQLite core.  

  In addition, a new interface [sqlite3_strnicmp()] is provided for the
  convenience of extension writers.

  None of the bugs fixed in this release are serious.  All bugs are
  obscure.  Upgrading is optional.
}

newsitem {2009-July-25} {100% Branch Test Coverage} {
  A subset of the [TH3] test suite was measured by gcov to provide
  [test coverage | 100% branch test coverage] over the SQLite core
  (exclusive of the VFS backend and of extensions such as FTS3 and RTREE)
  when compiled for SuSE 10.1 Linux on x86.  The SQLite developers pledge
  to maintain branch test coverage at 100% in all future releases.
  Ongoing work will strive for 100% branch test coverage on the 
  operating-system backends and extensions as well.
}

newsitem {2009-Jun-27} {Version 3.6.16} {
  SQLite [version 3.6.16] is another general maintenance release containing
  performance and robustness enhancements.  A single notable bug was fixed
  (ticket #3929).  This bug cause cause INSERT or UPDATE statements to fail
  on indexed tables that have AFTER triggers that modify the same table and
  index.
}

newsitem {2009-Jun-15} {Version 3.6.15} {
  SQLite [version 3.6.15] is a general maintenance release containing
  performance and robustness enhancements and fixes for various obscure
  bugs.  
}

newsitem {2009-May-25} {Version 3.6.14.2} {
  SQLite [version 3.6.14.2] fixes an obscure bug in the code generator
  (<a href="http://www.sqlite.org/cvstrac/tktview?tn=3879">ticket #3879</a>)
  section of SQLite which can potentially cause incorrect query results.
  The changes from the prior release consist of only this one bug fix,
  check-in <a href="http://www.sqlite.org/cvstrac/chngview?cn=6676">[6676]</a>
  and a change to the version number text.

  The bug was introduced in version 3.6.14.  It is recommended that
  users of version 3.6.14 and 3.6.14.1 upgrade to this release.  Applications
  are unlikely to hit this bug, but since it is difficult to predict which
  applications might hit it and which might not, we recommend that all
  users of 3.6.14 and 3.5.14.1 upgrade to this release.
}

newsitem {2009-May-19} {Version 3.6.14.1} {
  SQLite [version 3.6.14.1] is a patch release to [version 3.6.14] with
  minimal changes that fixes three bugs.  Upgrading is only necessary 
  for users who are impacted by one or more of those bugs.
}

newsitem {2009-May-07} {Version 3.6.14} {
  SQLite [version 3.6.14] provides new performance enhancements in
  the btree and pager layers and in the query optimizer.  Certain
  workloads can be as much as twice as fast as the previous release,
  though 10% faster is a more typical result.

  Queries against [virtual tables] that contain OR and IN operators
  in the WHERE clause are now able to use indexing.

  A new optional [asynchronous I/O backend] is available for
  unix and windows.  The asynchronous backend gives the illusion of faster
  response time by pushing slow write operations into a background thread.
  The tradeoff for faster response time is that more memory is required
  (to hold the content of the pending writes) and if a power failure or
  program crash occurs, some transactions that appeared to have committed
  might end up being rolled back upon restart.

  This release also contains many minor bug fixes, documentation enhancements,
  new test cases, and cleanups and simplifications to the source code.

  There is no compelling reason to upgrade from versions 3.6.12 or
  3.6.13 if those prior versions are working.  Though many users may
  benefit from the improved performance.
}

newsitem {2008-Dec-16} {Version 3.6.7} {
  SQLite [version 3.6.7] contains a major cleanup of the Unix driver,
  and support for the new Proxy Locking mechanism on MacOSX.  Though
  the Unix driver is reorganized, its functionality is the same and so
  applications should not notice a difference.
}

Pragma page_size {
   <p>^(<b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page size
    may only be set if the database has not yet been created. ^The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to [SQLITE_MAX_PAGE_SIZE].
    than or equal to 65536.
    The maximum value for [SQLITE_MAX_PAGE_SIZE] is 32768.
    </p>

    <p>^When a new database is created, SQLite assigned a default page size
    based on information received from the xSectorSize and 
    xDeviceCharacteristics methods of the [sqlite3_io_methods] object
    of the newly created database file.  ^The page_size pragma will only
    cause an immediate change in the

    disabled or if xDeviceCharacteristics reports no suitable atomic
    write page sizes, then the default page size is the larger of 
    SQLITE_DEFALT_PAGE_SIZE
    and the sector size as reported by the xSectorSize method of the
    [sqlite3_io_methods] object, but not more than 
    SQLITE_MAX_DEFAULT_PAGE_SIZE.  ^The normal configuration for SQLite
    running on workstations is for atomic write to be
    disabled, for the maximum page size to be set to 32768, for
    disabled, for the maximum page size to be set to 65536, for
    SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
    maximum default page size to be set to 8192.  ^(The default xSectorSize
    method on workstation implementations always reports a sector size
    of 512 bytes.  Hence, 
    the default page size chosen by SQLite is usually 1024 bytes.)^</p>
}

data is stored in the leaves of the tree instead of in both the leaves and
the intermediate branch nodes.  The use of B+trees for tables allows for
better scalability and the storage of larger data fields without the use of
overflow pages.  Traditional B-trees are still used for indices.</p>

<p>
The new file format also supports variable pages sizes between 512 and
32768 bytes.  The size of a page is stored in the file header so the
65536 bytes.  The size of a page is stored in the file header so the
same library can read databases with different pages sizes, in theory,
though this feature has not yet been implemented in practice.
</p>

<p>
The new file format omits unused fields from its disk images.  For example,
indices use only the key part of a B-tree record and not the data.  So