Documentation Source Text

  foreach x $args {
    hd_keywords $x "PRAGMA $x" "$x pragma"
  }
}

</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
    releases of SQLite. There is no guarantee of backwards compatiblity.
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
    not during the [sqlite3_step()] call as normal SQL statements do.
    Or the pragma might run during sqlite3_step() just like normal
    SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
    or sqlite3_step() depends on the pragma and on the specific release
    of SQLite.
<li>The pragma command is specific to SQLite and is very unlikely 
    to be compatible with any other SQL database engine.
</ul>

<p>The available pragmas fall into four basic categories:</p>
<ul>
<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
    SQLite library in some manner, or to query for the current mode of 
    operation.
<li>Pragmas used to <a href="#schema">query the schema</a> of the current 
    database.
<li>Pragmas used to <a href="#version">query or modify the two 
    version counters stored in the database:</a>
    the schema-version and the user-version.
<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
    database files are not corrupted.
</ul>

<tcl>
Section {PRAGMA command syntax} syntax {PRAGMA}

BubbleDiagram pragma-stmt
BubbleDiagram pragma-value
</tcl>









<tcl>Section {Pragmas to modify library operation} modify</tcl>
</tcl>

<ul>
<tcl>Subsection auto_vacuum</tcl>
<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
            <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <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
    to remove the freelist pages at every transaction commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    [VACUUM] command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 or "incremental" then the additional
    information needed to do auto-vacuuming is stored in the database file
    but auto-vacuuming does not occur automatically at each commit as it
    does with auto_vacuum=full.  In incremental mode, the separate
    [incremental_vacuum] pragma must
    be invoked to cause the auto-vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at any time.  However, the connection can only be changed
    "none" to "full" or "incremental" when the database is empty (no tables
    have yet been created) or by running the [VACUUM] command.  To
    change auto-vacuum modes, first use the auto_vacuum pragma to set
    the new desired mode, then invoke the [VACUUM] command to 
    reorganize the entire database file.  To change from "full" or
    "incremental" back to "none" always requires running [VACUUM] even
    on an empty database.
    </p>

    <p>When the auto_vacuum pragma is invoked with no arguments, it
    returns the current auto_vacuum mode.</p>
    </li>

<tcl>Subsection cache_size</tcl>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
    The default cache size is 2000.  If you are doing [UPDATEs] or [DELETEs]