Documentation Source Text

using the [journal_mode pragma]
as described in <a href="#section_7_6">section 7.6</a> below.</p>

<tcl>hd_fragment freelistjrnl</tcl>
<h3>7.3 Do Not Journal Freelist Pages</h3>

<p>When information is deleted from an SQLite database, the pages used
to hold the deleted information are added to a "freelist".  Subsequent
inserts will draw pages off of this freelist rather than expanding the
database file.</p>

<p>Some freelist pages contain critical data; specifically the locations
of other freelist pages.  But most freelist pages contain nothing useful.
These latter freelist pages are called "leaf" pages.  We are free to
modify the content of a leaf freelist page in the database without

using the [journal_mode pragma]
as described in <a href="#section_7_6">section 7.6</a> below.</p>

<tcl>hd_fragment freelistjrnl</tcl>
<h3>7.3 Do Not Journal Freelist Pages</h3>

<p>When information is deleted from an SQLite database, the pages used
to hold the deleted information are added to a "[freelist]".  Subsequent
inserts will draw pages off of this freelist rather than expanding the
database file.</p>

<p>Some freelist pages contain critical data; specifically the locations
of other freelist pages.  But most freelist pages contain nothing useful.
These latter freelist pages are called "leaf" pages.  We are free to
modify the content of a leaf freelist page in the database without

    hd_enable_main 0
    hd_puts "<h2>SQLite Release $vers On $date</h2>"
    hd_puts "<p>Changes associated with this release include the following:</p>"
    regsub -all {<a href="(?!http:)} $desc {<a href="../} desc
    hd_resolve "<p><ul>$desc</ul></p>"
    hd_puts {
      <p>A <a href="../changes.html">complete list of SQLite releases</a>
      in a single pages is also available.  A detailed history of every
      check-in is available at
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 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.

    hd_enable_main 0
    hd_puts "<h2>SQLite Release $vers On $date</h2>"
    hd_puts "<p>Changes associated with this release include the following:</p>"
    regsub -all {<a href="(?!http:)} $desc {<a href="../} desc
    hd_resolve "<p><ul>$desc</ul></p>"
    hd_puts {
      <p>A <a href="../changes.html">complete list of SQLite releases</a>
      in a single page is also available.  A detailed history of every
      check-in is available at
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2010 August 24 (3.7.2)} {
<li> Fix an <a href="http://www.sqlite.org/src/info/5e10420e8d">
     old and very obscure bug</a> that can lead to corruption of the
     database [free-page list] when [incremental_vacuum] is used.
}

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.

by the SQLite core, though operating-system specific VFS implementations may
choose to read or write bytes on the lock-byte page according to the 
needs and proclivities of the underlying system.  The unix and win32
VFS implementations that come built into SQLite do not write to the
lock-byte page, but we are aware of third-party VFS implementations for
other operating systems that do sometimes write to the lock-byte page.</p>


<h3>1.4 The Freelist</h3>

<p>A database file might contain one or more pages that are not in
active use.  Unused pages can come about, for example, when information
is deleted from the database.  Unused pages are stored on the freelist
and are reused when additional pages are required.</p>

................................................................................
be at least 120 entries in length.  ^The first integer in the array 
is the page number of the next freelist trunk page in the list or zero 
if this is the last freelist trunk page.  ^The second integer in the array
is the number of leaf page pointers to follow.  Call the second integer L.
^If L is greater than zero then integers with array indexes between 2 and
L+1 inclusive contain page numbers for freelist leaf pages.</p>

<p>Freelist leaf pages contain no information.  ^SQLite avoid reading or
writing freelist leaf pages in order to reduce disk I/O.</p>

<p>A bug in SQLite versions prior to 3.6.0 caused the database to be
reported as corrupt if any last 6 entries in the freelist trunk page 
array contained non-zero values.  Newer versions of SQLite do not have
this problem.  ^However, newer versions of SQLite still avoid using the 
last six entries in the freelist trunk page array in order that database

by the SQLite core, though operating-system specific VFS implementations may
choose to read or write bytes on the lock-byte page according to the 
needs and proclivities of the underlying system.  The unix and win32
VFS implementations that come built into SQLite do not write to the
lock-byte page, but we are aware of third-party VFS implementations for
other operating systems that do sometimes write to the lock-byte page.</p>

<tcl>hd_fragment {freelist} {freelist} {free-page list}</tcl>
<h3>1.4 The Freelist</h3>

<p>A database file might contain one or more pages that are not in
active use.  Unused pages can come about, for example, when information
is deleted from the database.  Unused pages are stored on the freelist
and are reused when additional pages are required.</p>

................................................................................
be at least 120 entries in length.  ^The first integer in the array 
is the page number of the next freelist trunk page in the list or zero 
if this is the last freelist trunk page.  ^The second integer in the array
is the number of leaf page pointers to follow.  Call the second integer L.
^If L is greater than zero then integers with array indexes between 2 and
L+1 inclusive contain page numbers for freelist leaf pages.</p>

<p>Freelist leaf pages contain no information.  ^SQLite avoids reading or
writing freelist leaf pages in order to reduce disk I/O.</p>

<p>A bug in SQLite versions prior to 3.6.0 caused the database to be
reported as corrupt if any last 6 entries in the freelist trunk page 
array contained non-zero values.  Newer versions of SQLite do not have
this problem.  ^However, newer versions of SQLite still avoid using the 
last six entries in the freelist trunk page array in order that database

</td>
<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.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>
<li> <a href="faq.html">Frequently Asked Questions</a> </li>

</td>
<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_2.html">Version 3.7.2</a>
of SQLite is recommended for all new development.

Upgrading from all prior SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>
<li> <a href="faq.html">Frequently Asked Questions</a> </li>

  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.

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

    <p>Query or set the auto-vacuum status in the database.</p>

    <p>^The default setting for auto-vacuum is 0 or "none",
    unless the [SQLITE_DEFAULT_AUTOVACUUM] compile-time option is used.
    ^The "none" setting means that auto-vacuum is disabled.
    ^When auto-vacuum is disabled and data is deleted data from a database,
    the database file remains the same size.  ^Unused database file 
    pages are added to a "freelist" and reused for subsequent inserts.  So
    no database file space is lost.  However, the database file does not
    shrink.  ^In this mode the [VACUUM]
    command can be used to rebuild the entire database file and
    thus reclaim unused disk space.</p>

    <p>^When the auto-vacuum mode is 1  or "full", the freelist pages are
    moved to the end of the database file and the database file is truncated
................................................................................
    is off.  Only Mac OS X supports F_FULLFSYNC.
    </p>
}

Pragma incremental_vacuum {
    <p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>^The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the freelist.  ^The database file is truncated by
    the same amount.  ^The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum==incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>

    <p>As of [version 3.4.0] (the first version that supports
    incremental_vacuum) this feature is still experimental.  Possible
    future changes include enhancing incremental vacuum to do
    defragmentation and node repacking just as the full-blown
    [VACUUM] command does.  And
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
}

Pragma journal_mode {
    <p>^(<b>PRAGMA journal_mode;
       <br>PRAGMA </b><i>database</i><b>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i>

    <p>Query or set the auto-vacuum status in the database.</p>

    <p>^The default setting for auto-vacuum is 0 or "none",
    unless the [SQLITE_DEFAULT_AUTOVACUUM] compile-time option is used.
    ^The "none" setting means that auto-vacuum is disabled.
    ^When auto-vacuum is disabled and data is deleted data from a database,
    the database file remains the same size.  ^Unused database file 
    pages are added to a "[freelist]" and reused for subsequent inserts.  So
    no database file space is lost.  However, the database file does not
    shrink.  ^In this mode the [VACUUM]
    command can be used to rebuild the entire database file and
    thus reclaim unused disk space.</p>

    <p>^When the auto-vacuum mode is 1  or "full", the freelist pages are
    moved to the end of the database file and the database file is truncated
................................................................................
    is off.  Only Mac OS X supports F_FULLFSYNC.
    </p>
}

Pragma incremental_vacuum {
    <p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>^The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the [freelist].  ^The database file is truncated by
    the same amount.  ^The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>










}

Pragma journal_mode {
    <p>^(<b>PRAGMA journal_mode;
       <br>PRAGMA </b><i>database</i><b>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i>