Documentation Source Text
Artifact Content
Not logged in

Artifact d19593c9c8f427195d423957919b062ed8917e42f940766cc73cbf99e837eee3:

<title>Pragma statements supported by SQLite</title>
<h1 align="center">PRAGMA Statements</h1>

proc Section {name {label {}} {keywords {}}} {
  hd_puts "\n<hr />"
  if {$label!=""} {
    hd_fragment $label
    if {$keywords!=""} {
      eval hd_keywords $keywords
  hd_puts "<h2>$name</h2>\n"
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content
  regsub -all {<warning>} $content \
      {<span style='background-color: #ffff60;'>} content
  regsub -all {</warning>} $content </span> content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
proc LegacyDisclaimer {} {
  return {
    <p style='background-color: #ffd0d0;'>
    <b>This pragma is deprecated</b> and exists
    for backwards compatibility only.  New applications
    should avoid using this pragma.  Older applications should discontinue
    use of this pragma at the earliest opportunity.  This pragma may be omitted
    from the build when SQLite is compiled using [SQLITE_OMIT_DEPRECATED].
proc DebugDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    This pragma is intended for use when debugging SQLite itself.  It
    is only available when the [SQLITE_DEBUG] compile-time option
    is used.</p>
proc DangerDisclaimer {} {
  return {
    <p><span style='background-color: #ffff60;'>
    Misuse of this pragma can result in [cfgerrors|database corruption].
proc TestDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    The intended use of this pragma is only for testing and validation of
    SQLite.  This pragma is subject to change without notice and is not
    recommended for use by application programs.</p>
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
# Debugging pragmas
proc DebugPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [DebugDisclaimer]] $content]
  global PragmaDebug
  foreach x $namelist {set PragmaDebug($x) 1}
# Testing pragmas
proc TestPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [TestDisclaimer]] $content]
  global PragmaTest
  foreach x $namelist {set PragmaTest($x) 1}
proc DangerousPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [DangerDisclaimer]] $content]
  global PragmaTest
  foreach x $namelist {set PragmaTest($x) 1}


<p>The PRAGMA statement is an 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:
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatibility.
<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
    not compatible with any other SQL database engine.

<p>The C-language API for SQLite provides the [SQLITE_FCNTL_PRAGMA]
[sqlite3_file_control | file control] which gives [VFS] implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>

Section {PRAGMA command syntax} syntax {PRAGMA}

RecursiveBubbleDiagram pragma-stmt pragma-value

^A pragma can take either zero or one argument.  ^The argument is may be either
in parentheses or it may be separated from the pragma name by an equal sign.
^The two syntaxes yield identical results.
^(In many pragmas, the argument is a boolean.  The boolean can be one of:

<b>1 yes true on<br>0 no false off</b>

<p>^Keyword arguments can optionally appear in quotes.  
(Example:  <tt>'yes' &#91;FALSE&#93;</tt>.) Some pragmas
takes a string literal as their argument.  When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>

<p>^A pragma may have an optional <yyterm>schema-name</yyterm>
before the pragma name.
^The <yyterm>schema-name</yyterm> is the name of an [ATTACH]-ed database 
or "main" or "temp" for the main and the TEMP databases.  ^If the optional
schema name is omitted, "main" is assumed.  ^In some pragmas, the schema
name is meaningless and is simply ignored.  In the documentation below,
pragmas for which the schema name is meaningful are shown with a
"<i>schema.</i>" prefix.</p>

Section {PRAGMA functions} pragfunc {{PRAGMA functions} {PRAGMA function}}

PRAGMAs that return results and that have no side-effects can be 
accessed from ordinary [SELECT] statements as [table-valued functions].
For each participating PRAGMA, the corresponding table-valued function
has the same name as the PRAGMA with a 7-character "pragma_" prefix.
The PRAGMA argument and schema, if any, are passed as arguments to
the table-valued function.

<p>For example, information about the columns in an index can be
read using the [index_info pragma] as follows:

PRAGMA index_info('idx52');

<p>Or, the same content can be read using:

SELECT * FROM pragma_index_info('idx52');

<p>The advantage of the table-valued function format is that the query
can return just a subset of the PRAGMA columns, can include a WHERE clause,
can use aggregate functions, and the table-valued function can be just
one of several data sources in a join.
For example, to get a list of all indexed columns in a schema, one
could query:

SELECT DISTINCT || '.' || AS 'indexed-columns'
  FROM sqlite_master AS m,
       pragma_index_list( AS il,
       pragma_index_info( AS ii
 WHERE m.type='table'

Additional notes:
Table-valued functions exist only for built-in PRAGMAs, not for PRAGMAs
defined using the [SQLITE_FCNTL_PRAGMA] file control.
Table-valued functions exist only for PRAGMAs that return results and
that have no side-effects.
This feature could be used to implement
[ | information schema]
by first creating a separate schema using
[ATTACH] ':memory:' AS 'information_schema';
Then creating
[VIEW|VIEWs] in that schema that implement the official information schema
tables using table-valued PRAGMA functions.
This feature is experimental and is subject to change.  Further documentation
will become available if and when the table-valued functions for PRAGMAs
feature becomes officially supported.
The table-valued functions for PRAGMA feature was added
in SQLite version 3.16.0 ([dateof:3.16.0]).  Prior versions of SQLite
cannot use this feature.

<tcl>Pragma {application_id} {
    <p><b>PRAGMA DB.application_id;
     <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b>
<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 
       [ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list of
       assigned application IDs can be seen by consulting the
        |magic.txt] file in the SQLite source repository.

<p>   See also the [user_version pragma].

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;
     <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>

    <p>Query, set, or clear the [automatic indexing] capability.)^
    <p>[Automatic indexing] is enabled by default as of 
    [version 3.7.17] ([dateof:3.7.17]),
    but this might change in future releases of SQLite.

Pragma {auto_vacuum} {
    <p><b>PRAGMA DB.auto_vacuum;<br>
          PRAGMA DB.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 referrer.  ^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, changing from
    "none" to "full" or "incremental" can only occur when the database 
    is new (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>^When the auto_vacuum pragma is invoked with no arguments, it
    returns the current auto_vacuum mode.</p>

Pragma busy_timeout {
    <p>^(<b>PRAGMA busy_timeout;
         <br>PRAGMA busy_timeout = </b><i>milliseconds</i><b>;</b></p>
    <p>Query or change the setting of the
    [sqlite3_busy_timeout | busy timeout].)^
    This pragma is an alternative to the [sqlite3_busy_timeout()] C-language
    interface which is made available as a pragma for use with language
    bindings that do not provide direct access to [sqlite3_busy_timeout()].

    <p>Each database connection can only have a single
    [sqlite3_busy_handler|busy handler].  This PRAGMA sets the busy handler
    for the process, possibly overwriting any previously set busy handler.

Pragma cache_spill {
    <p>^(<b>PRAGMA cache_spill;
         <br>PRAGMA cache_spill=</b><i>boolean</i><b>;
         <br>PRAGMA DB.cache_spill=<i>N</i>;</b>)^</p>

    <p>^(The cache_spill pragma enables or disables the ability of the pager
    to spill dirty cache pages to the database file in the middle of a 
    transaction.)^  ^(Cache_spill is enabled by default)^ and most applications
    should leave it that way as cache spilling is usually advantageous.
    However, a cache spill has the side-effect of acquiring an
    [EXCLUSIVE lock] on the database file.  Hence, some applications that
    have large long-running transactions may want to disable cache spilling
    in order to prevent the application from acquiring an exclusive lock
    on the database until the moment that the transaction [COMMIT]s.
    <p>^(The "PRAGMA cache_spill=<i>N</i>" form of this pragma sets a minimum
    cache size threshold required for spilling to occur.)^ ^(The number of pages
    in cache must exceed both the cache_spill threshold and the maximum cache
    size set by the [PRAGMA cache_size] statement in order for spilling to
    <p>^(The "PRAGMA cache_spill=<i>boolean</i>" form of this pragma applies
    across all databases attached to the database connection.)^  ^(But the
    "PRAGMA cache_spill=<i>N</i>" form of this statement only applies to
    the "main" schema or whatever other schema is specified as part of the

Pragma cache_size {
    <p>^(<b>PRAGMA DB.cache_size;
       <br>PRAGMA DB.cache_size = </b><i>pages</i><b>;
       <br>PRAGMA DB.cache_size = -</b><i>kibibytes</i><b>;</b></p>
    <p>Query or change the suggested maximum number of database disk pages
    that SQLite will hold in memory at once per open database file.)^  Whether
    or not this suggestion is honored is at the discretion of the
    [sqlite3_pcache_methods2 | Application Defined Page Cache].
    The default page cache that is built into SQLite honors the request,
    however alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^The default suggested cache size is -2000, which means the cache size
    is limited to 2048000 bytes of memory.
    ^The default suggested cache size can be altered using the
    [SQLITE_DEFAULT_CACHE_SIZE] compile-time options.
    ^The TEMP database has a default suggested cache size of 0 pages.</p>

    <p>^If the argument N is positive then the suggested cache size is set 
    to N. ^If the argument N is negative, then the
    number of cache pages is adjusted to use approximately abs(N*1024) bytes
    of memory.
    <i>Backwards compatibility note:</i>
    The behavior of cache_size with a negative N
    was different in prior to [version 3.7.10] ([dateof:3.7.10]).  In
    version 3.7.9 and earlier, the number of pages in the cache was set
    to the absolute value of N.</p>

    <p>^When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  ^The cache size reverts
    to the default value when the database is closed and reopened.</p>


Pragma case_sensitive_like {
    <p><b>PRAGMA case_sensitive_like = </b><i>boolean</i><b>;</b></p>
    <p>^(The default behavior of the [LIKE] operator is to ignore case
    for ASCII characters. Hence, by default <b>'a' LIKE 'A'</b> is
    true.)^  ^The case_sensitive_like pragma installs a new application-defined
    LIKE function that is either case sensitive or insensitive depending
    on the value of the case_sensitive_like pragma.
    ^When case_sensitive_like is disabled, the default LIKE behavior is
    expressed.  ^(When case_sensitive_like is enabled, case becomes
    significant.  So, for example,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.)^</p>

    <p>^This pragma uses [sqlite3_create_function()] to overload the
    LIKE and GLOB functions, which may override previous implementations
    of LIKE and GLOB registered by the application.  ^(This pragma
    only changes the behavior of the SQL [LIKE] operator.  It does not
    change the behavior of the [sqlite3_strlike()] C-language interface,
    which is always case insensitive.)^</p>

Pragma cell_size_check {
    <p>^(<b>PRAGMA cell_size_check
       <br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p>
    <p>The cell_size_check pragma enables or disables additional sanity
    checking on database b-tree pages as they are initially read from disk.)^
    With cell size checking enabled, database corruption is detected earlier
    and is less likely to "spread".  However, there is a small performance
    hit for doing the extra checks and so cell size checking is turned off
    by default.

Pragma checkpoint_fullfsync {
    <p>^(<b>PRAGMA checkpoint_fullfsync
       <br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag for [checkpoint] operations.)^
    ^If this flag is set, then the F_FULLFSYNC syncing method is used
    during checkpoint operations on systems that support F_FULLFSYNC. 
    ^The default value of the checkpoint_fullfsync flag
    is off.  Only Mac OS-X supports F_FULLFSYNC.</p>

    <p>^If the [fullfsync] flag is set, then the F_FULLFSYNC syncing
    method is used for all sync operations and the checkpoint_fullfsync
    setting is irrelevant.</p>

LegacyPragma count_changes {
    <p><b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>

    <p>Query or change the count-changes flag. Normally, when the
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers, or any changes made automatically
    by [foreign key actions].</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  When an INSERT, UPDATE, or
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.


Pragma data_version {
    <p><b>PRAGMA DB.data_version;</b></p>
    <p>^The "PRAGMA data_version" command provides an indication that the
    database file has been modified.
    Interactive programs that hold database content in memory or that
    display database content on-screen can use the PRAGMA data_version
    command to determine if they need to flush and reload their memory
    or update the screen display.</p>

    <p>^The integer values returned by two
    invocations of "PRAGMA data_version" from the same connection
    will be different if changes were committed to the database 
    by any other connection in the interim.
    ^The "PRAGMA data_version" value is unchanged for commits made
    on the same database connection.
    ^The behavior of "PRAGMA data_version" is the same for all database
    connections, including database connections in separate processes
    and [shared cache] database connections.

    <p>^The "PRAGMA data_version" value is a local property of each
    database connection and so values returned by two concurrent invocations
    of "PRAGMA data_version" on separate database connections are 
    often different even though the underlying database is identical.
    It is only meaningful to compare the "PRAGMA data_version" values
    returned by the same database connection at two different points in

Pragma defer_foreign_keys {
    <p><b>PRAGMA defer_foreign_keys
       <br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p>
    <p>^When the defer_foreign_keys [PRAGMA] is on,
    enforcement of all [foreign key constraints] is delayed until the
    outermost transaction is committed.  ^The defer_foreign_keys pragma
    defaults to OFF so that foreign key constraints are only deferred if
    they are created as "DEFERRABLE INITIALLY DEFERRED".  ^(The 
    defer_foreign_keys pragma is automatically switched off at each
    COMMIT or ROLLBACK.  Hence, the defer_foreign_keys pragma must be
    separately enabled for each transaction.)^  This pragma is
    only meaningful if foreign key constraints are enabled, of course.</p>

    <p>The [sqlite3_db_status](db,[SQLITE_DBSTATUS_DEFERRED_FKS],...)
    C-language interface can be used during a transaction to determine 
    if there are deferred and unresolved foreign key constraints.</p>

LegacyPragma default_cache_size {
    ^(<b>PRAGMA DB.default_cache_size;
       <br>PRAGMA DB.default_cache_size
            = </b><i>Number-of-pages</i><b>;</b></p>

    <p>This pragma queries or sets the suggested maximum number of pages
    of disk cache that will be allocated per open database file.)^
    ^The difference between this pragma and [cache_size] is that the
    value set here persists across database connections.
    ^The value of the default cache size is stored in the 4-byte
    big-endian integer located at offset 48 in the header of the
    database file.


LegacyPragma empty_result_callbacks {
    <p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p>

    <p>Query or change the empty-result-callbacks flag.</p>

    <p>The empty-result-callbacks flag affects the [sqlite3_exec()] API only.
    Normally, when the empty-result-callbacks flag is cleared, the
    callback function supplied to the [sqlite3_exec()] is not invoked
    for commands that return zero rows of data.  When empty-result-callbacks
    is set in this situation, the callback function is invoked exactly once,
    with the third parameter set to 0 (NULL). This is to enable programs  
    that use the [sqlite3_exec()] API to retrieve column-names even when
    a query returns no data.</p>


Pragma encoding {
   <p>^(<b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
       <br>PRAGMA encoding = "UTF-16le";
       <br>PRAGMA encoding = "UTF-16be";</b>)^</p>
    <p>^In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
    main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16
    encoding) or "UTF-16be" (big-endian UTF-16 encoding).  ^If the main
    database has not already been created, then the value returned is the
    text encoding that will be used to create the main database, if 
    it is created by this session.</p>

    <p>^The second through fifth forms of this pragma
    set the encoding that the main database will be created with if
    it is created by this session. ^The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  ^It is not
    possible to change the text encoding of a database after it has been
    created and any attempt to do so will be silently ignored.</p>

    <p>^Once an encoding has been set for a database, it cannot be changed.</p>

    <p>^Databases created by the [ATTACH] command always use the same encoding
    as the main database.  ^An attempt to [ATTACH] a database with a different
    text encoding from the "main" database will fail.</p>

Pragma foreign_keys {
     <p>^(<b>PRAGMA foreign_keys;
       <br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the enforcement of [foreign key constraints].)^

    <p>^This pragma is a no-op within a transaction; foreign key constraint
       enforcement may only be enabled or disabled when there is no pending
       [BEGIN] or [SAVEPOINT].

    <p>^Changing the foreign_keys setting affects the execution of
       all statements prepared
       using the database connection, including those prepared before the
       setting was changed. ^Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error
       after the foreign_keys setting is changed.

    <p>^(As of SQLite [version 3.6.19], the default setting for foreign
       key enforcement is OFF.)^  However, that might change in a future
       release of SQLite.  The default setting for foreign key enforcement
       can be specified at compile-time using the [SQLITE_DEFAULT_FOREIGN_KEYS]
       preprocessor macro.  To minimize future problems, applications should
       set the foreign key enforcement flag as required by the application
       and not depend on the default setting.

LegacyPragma full_column_names {
    <p><b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>

    <p>Query or change the full_column_names flag. This flag together 
    with the [short_column_names] flag determine
    the way SQLite assigns names to result columns of [SELECT] statements.
    Result columns are named by applying the following rules in order:
    <li><p>If there is an AS clause on the result, then the name of
        the column is the right-hand side of the AS clause.</p></li>
    <li><p>If the result is a general expression, not a just the name of
        a source table column,
        then the name of the result is a copy of the expression text.</p></li>
    <li><p>If the [short_column_names] pragma is ON, then the name of the
        result is the name of the source table column without the 
        source table name prefix:  COLUMN.</p></li>
    <li><p>If both pragmas [short_column_names] and [full_column_names]
        are OFF then case (2) applies.
    <li><p>The name of the result column is a combination of the source table
        and source column name:  TABLE.COLUMN</p></li>


Pragma fullfsync {
    <p>^(<b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag.)^ ^This flag
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  ^The default value of the fullfsync flag
    is off.  Only Mac OS X supports F_FULLFSYNC.</p>

    <p>See also [checkpoint_fullfsync].</p>

Pragma incremental_vacuum {
    ^(<p><b>PRAGMA DB.incremental_vacuum</b><i>(N)</i><b>;<br>
      PRAGMA DB.incremental_vacuum;</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 the "(<i>N</i>)" argument is omitted, then the entire
    freelist is cleared.</p>

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

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

    <p>^The first form of this pragma queries the current journaling
    mode for <i>database</i>.  ^When <i>database</i> is omitted, the
    "main" database is queried.</p>

    <p>^The second form changes the journaling mode for "<i>database</i>"
    or for all attached databases if "<i>database</i>" is omitted.
    ^The new journal mode is returned.  ^If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>^The DELETE journaling mode is the normal behavior.  ^In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the document titled <a href="atomiccommit.html">
    Atomic Commit In SQLite</a> for additional detail.)</p>

    <p>^The TRUNCATE journaling mode commits transactions by truncating
    the rollback journal to zero-length instead of deleting it.  On many
    systems, truncating a file is much faster than deleting the file since
    the containing directory does not need to be changed.</p>

    <p>^(The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.)^  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting or truncating a file is much more expensive than overwriting
    the first block of a file with zeros.  See also:
    [PRAGMA journal_size_limit] and [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT].</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely 
    [cfgerrors|go corrupt].</p>

    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.  ^The WAL journaling mode
    is persistent; after being set it stays in effect
    across multiple database connections and after closing and
    reopening the database.  A database in WAL journaling mode
    can only be accessed by SQLite [version 3.7.0] ([dateof:3.7.0])
    or later.</p>

    <p>^The OFF journaling mode disables the rollback journal completely.
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.
    ^If the application crashes
    in the middle of a transaction when the OFF journaling mode is
    set, then the database file will very likely
    [cfgerrors|go corrupt].</p>

    <p>^Note that the journal_mode for an [in-memory database]
    is either MEMORY or OFF and can not be changed to a different value.
    ^An attempt to change the journal_mode of an [in-memory database] to
    any setting other than MEMORY or OFF is ignored.  ^Note also that
    the journal_mode cannot be changed while a transaction is active.</p>

Pragma journal_size_limit {
    PRAGMA DB.journal_size_limit<br>
    PRAGMA DB.journal_size_limit = </b><i>N</i> <b>;</b>

  <p>^If a database connection is operating in
  [locking_mode | exclusive locking mode] or in
  [journal_mode | persistent journal mode] 
  (PRAGMA journal_mode=persist) then
  after committing a transaction the [rollback journal] file may remain in
  the file-system. This increases performance for subsequent transactions
  since overwriting an existing file is faster than append to a file,
  but it also consumes
  file-system space. After a large transaction (e.g. a [VACUUM]),
  the rollback journal file may consume a very large amount of space.

  <p>Similarly, in [WAL mode], the write-ahead log file is not truncated
  following a [checkpoint].  Instead, SQLite reuses the existing file
  for subsequent WAL entries since overwriting is faster than appending.

  <p>^The journal_size_limit pragma may be used to limit the size of 
  rollback-journal and WAL files left
  in the file-system after transactions or checkpoints.
  ^Each time a transaction is committed or a WAL file resets, SQLite 
  compares the size of the rollback journal file or WAL file left in 
  the file-system to the size limit
  set by this pragma and if the journal or WAL file is larger 
  it is truncated to the limit.

  <p>^The second form of the pragma listed above is used to set a new limit
  in bytes for the specified database.  ^A negative number implies no limit.
  ^To always truncate rollback journals and WAL files to their minimum size, 
  set the journal_size_limit to zero.
  ^Both the first and second forms of the pragma listed above return a single
  result row containing a single integer column - the value of the journal
  size limit in bytes. ^The default journal size limit is -1 (no limit).  The
  [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT] preprocessor macro can be used to change
  the default journal size limit at compile-time.</p>

  <p>^This pragma only operates on the single database specified prior
  to the pragma name (or on the "main" database if no database is specified.)
  There is no way to change the journal size limit on all attached databases
  using a single PRAGMA statement.  The size limit must be set separately for
  each attached database.

Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.)^  ^(When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of
    SQLite going back to 3.0.0.)^  ^(When the flag is off, new databases
    are created using the latest file format which might not be
    readable or writable by versions of SQLite prior to 3.3.0.)^</p>

    <p>^When the legacy_file_format pragma is issued with no argument,
    it returns the setting of the flag.  ^This pragma does <u>not</u> tell
    which file format the current database is using; it tells what format
    will be used by any newly created databases.</p>

    <p>^The legacy_file_format pragma is initialized to OFF when an existing
    database in the newer file format is first opened.</p>

    <p>^The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>

Pragma locking_mode {
    <p>^(<b>PRAGMA DB.locking_mode;
    <br>PRAGMA DB.locking_mode
                = <i>NORMAL | EXCLUSIVE</i></b>)^</p>
    <p>^This pragma sets or queries the database connection locking-mode. 
    ^The locking-mode is either NORMAL or EXCLUSIVE.

    <p>^In NORMAL locking-mode (the default unless overridden at compile-time
    using [SQLITE_DEFAULT_LOCKING_MODE]), a database connection
    unlocks the database file at the conclusion of each read or
    write transaction. ^When the locking-mode is set to EXCLUSIVE, the
    database connection never releases file-locks. ^The first time the
    database is read in EXCLUSIVE mode, a shared lock is obtained and 
    held. ^The first time the database is written, an exclusive lock is
    obtained and held.</p>

    <p>^Database locks obtained by a connection in EXCLUSIVE mode may be
    released either by closing the database connection, or by setting the
    locking-mode back to NORMAL using this pragma and then accessing the
    database file (for read or write). ^Simply setting the locking-mode to
    NORMAL is not enough - locks are not released until the next time
    the database file is accessed.</p>

    <p>There are three reasons to set the locking-mode to EXCLUSIVE.
    <li>^The application wants to prevent other processes from
        accessing the database file.
    <li>^The number of system calls for filesystem operations is reduced,
        possibly resulting in a small performance increase.
    <li>^[WAL] databases can be accessed in EXCLUSIVE mode without the
        use of shared memory. 
        ([WAL without shared memory | Additional information])

    <p>^(When the locking_mode pragma specifies a particular database,
    for example:</p>

PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;

    <p>Then the locking mode applies only to the named database.)^  ^If no
    database name qualifier precedes the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent [ATTACH] commands.</p>

   <p>^The "temp" database (in which TEMP tables and indices are stored)
   and [in-memory databases]
   always uses exclusive locking mode.  ^The locking mode of temp and
   [in-memory databases] cannot
   be changed.  ^All other databases use the normal locking mode by default
   and are affected by this pragma.</p>

   <p>^If the locking mode is EXCLUSIVE when first entering
   [WAL | WAL journal mode], then the locking mode cannot be changed to
   NORMAL until after exiting WAL journal mode. 
   ^If the locking mode is NORMAL when first entering WAL
   journal mode, then the locking mode can be changed between NORMAL and
   EXCLUSIVE and back again at any time and without needing to exit
   WAL journal mode.</p>

Pragma mmap_size {
    <p>^(<br><b>PRAGMA DB.mmap_size;
         <br>PRAGMA DB.mmap_size=</b><i>N</i></p>

    <p>Query or change the maximum number of bytes that are set
       aside for memory-mapped I/O on a single database.)^  ^The first form
       (without an argument) queries the current limit.  ^The second
       form (with a numeric argument) sets the limit for the specified
       database, or for all databases if the optional database name is
       omitted.  ^In the second form, if the database name is omitted, the
       limit that is set becomes the default limit for all databases that
       are added to the [database connection] by subsequent [ATTACH]

    <p>^The argument N is the maximum number of bytes of the database file
       that will be accessed using memory-mapped I/O.  ^If N is zero then
       memory mapped I/O is disabled.  ^If N is negative, then the limit
       reverts to the default value determined by the most recent
       [sqlite3_config]([SQLITE_CONFIG_MMAP_SIZE]), or to the compile
       time default determined by [SQLITE_DEFAULT_MMAP_SIZE] if not
       start-time limit has been set.</p>

    <p>The [PRAGMA mmap_size] statement will never increase the amount
       of address space used for memory-mapped I/O above the
       hard limit set by the [SQLITE_MAX_MMAP_SIZE] compile-time option,
       nor the hard limit set start-time by the second argument to

    <p>The size of the memory-mapped I/O region cannot be changed while
       the memory-mapped I/O region is in active use, to avoid unmapping
       memory out from under running SQL statements.  For this reason,
       the mmap_size pragma may be a no-op if the prior mmap_size is non-zero
       and there are other SQL statements running concurrently on the same
       [database connection].</p>

Pragma page_size {
   <p>^(<b>PRAGMA DB.page_size;
       <br>PRAGMA DB.page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page
    size must be a power of two between 512 and 65536 inclusive.

    <p>^When a new database is created, SQLite assigns a page size to
    the database based on platform and filesystem.  For many years,
    the default page size was almost always 1024 bytes, but beginning
    with SQLite [version 3.12.0] ([dateof:3.12.0]), 
    the default page size increased to 4096.
    The default page size is recommended for most applications.

    <p>^(Specifying a new page size does not change the page size
    immediately.  Instead, the new page size is remembered and is used
    to set the page size when the database is first created, if it does
    not already exist when the page_size pragma is issued, or at the
    next [VACUUM] command that is run on the same database connection
    while not in [WAL mode].)^</p>

    <p>^The [SQLITE_DEFAULT_PAGE_SIZE] compile-time option can be used
    to change the default page size assigned to new databases.

Pragma max_page_count {
    <p>^(<b>PRAGMA DB.max_page_count;
        <br>PRAGMA DB.max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
    form attempts to modify the maximum page count.  ^The maximum page
    count cannot be reduced below the current database size.

Pragma optimize {
  <p>^(<b>PRAGMA optimize;
       <br>PRAGMA optimize(</b><i>MASK</i><b>);
       <br>PRAGMA </b><i>schema</i><b>.optimize;
       <br>PRAGMA </b><i>schema</i><b>.optimize(</b><i>MASK</i><b>);</b></p>
  <p>Attempt to optimize the database.)^  ^All schemas are optimized in the 
  first two forms, and only the specified schema is optimized in the latter
  <p>To achieve the best long-term query performance without the need to
  do a detailed engineering analysis of the application schema and SQL,
  it is recommended that applications run "PRAGMA optimize" (with no arguments)
  just before closing each [database connection].  Long-running applications
  might also benefit from setting a timer to run "PRAGMA optimize" every
  few hours.
  <p>This pragma is usually a no-op or nearly so and is very fast.
  However if SQLite feels
  that performing database optimizations (such as running [ANALYZE]
  or creating new indexes) will improve the performance of future queries, then
  some database I/O may be done.  Applications that want to limit the amount
  of work performed can set a timer that will invoke
  [sqlite3_interrupt()] if the pragma goes on for too long.
  <p>The details of optimizations performed by this pragma are expected
  to change and improve over time.  Applications should anticipate that
  this pragma will perform new optimizations in future releases.</p>

  <p>The optional MASK argument is a bitmask of optimizations to perform:
  <li value='1'><p>
         Debugging mode.  Do not actually perform any optimizations
         but instead return one line of text for each optimization
         that would have been done.  Off by default.
  <li value='2'><p>
         Run [ANALYZE] on tables that might benefit.  On by default.
         See below for additional information.
  <li value='4'><p>
         <em>(Not yet implemented)</em>
         Record usage and performance 
         information from the current session in the
         database file so that it will be available to "optimize"
         pragmas run by future database connections.
  <li value='8'><p>
         <em>(Not yet implemented)</em>
         Create indexes that might have been helpful to recent queries.
  <p>The default MASK is and always shall be 0xfffe.  The 0xfffe mask means
  perform all of the optimizations listed above except Debug Mode.  If new
  optimizations are added in the future that should be off by default, those
  new optimizations will be given a mask of 0x10000 or larger.</p>

  <p>To see all optimizations that would have been done without actually
  doing them, run "PRAGMA optimize(-1)".  To use only the ANALYZE
  optimization, run "PRAGMA optimize(0x02)".</p>
  <p><b>Determination Of When To Run Analyze</b></p>
  <p> In the current implementation, a table is analyzed if and only if 
      all of the following are true:
    MASK bit 0x02 is set.
    The query planner used [sqlite_stat1]-style statistics for one or
    more indexes of the table at some point during the lifetime of
    the current connection.
    One or more indexes of the table are currently unanalyzed <em>or</em>
    the number of rows in the table has increased by 25 times or more
    since the last time ANALYZE was run.
  <p> The rules for when tables are analyzed are likely to change in
      future releases.

Pragma query_only {
    <p><b>PRAGMA query_only;
      <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>

    <p>The query_only pragma prevents all changes to database files when

Pragma read_uncommitted {
    <p>^(<b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear READ UNCOMMITTED isolation.)^ ^The default isolation
    level for SQLite is SERIALIZABLE.  ^Any process or thread can select
    READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except
    between connections that share a common page and schema cache.
    Cache sharing is enabled using the [sqlite3_enable_shared_cache()] API.
    Cache sharing is disabled by default.

    <p>See [SQLite Shared-Cache Mode] for additional information.</p>

Pragma recursive_triggers {
    <p>^(<b>PRAGMA recursive_triggers;
       <br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the recursive trigger capability.)^

    <p>^Changing the recursive_triggers setting affects the execution of
       all statements prepared
       using the database connection, including those prepared before the
       setting was changed. ^Any existing statements prepared using the legacy 
       [sqlite3_prepare()] interface may fail with an [SQLITE_SCHEMA] error
       after the recursive_triggers setting is changed.

    <p>Prior to SQLite [version 3.6.18] ([dateof:3.6.18]), 
    recursive triggers were not supported.
    The behavior of SQLite was always as if this pragma was
    set to OFF.  Support for recursive triggers was added in version 3.6.18
    but was initially turned OFF by default, for compatibility.  Recursive
    triggers may be turned on by default in future versions of SQLite.

    <p>^(The depth of recursion for triggers has a hard upper limit set by
    the [SQLITE_MAX_TRIGGER_DEPTH] compile-time option and a run-time
    limit set by [sqlite3_limit](db,[SQLITE_LIMIT_TRIGGER_DEPTH],...).)^</p>

Pragma reverse_unordered_selects {
    <p>^(<b>PRAGMA reverse_unordered_selects;
       <br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b>)^</p>
    <p>^When enabled, this PRAGMA causes many [SELECT] statements without
    an ORDER BY clause to emit their results in the reverse order from what
    they normally would.  This can help debug applications that are
    making invalid assumptions about the result order.  
    ^The reverse_unordered_selects pragma works for most SELECT statements,
    however the query planner may sometimes choose an algorithm that is
    not easily reversed, in which case the output will appear in the same
    order regardless of the reverse_unordered_selects setting.
    <p>SQLite makes no
    guarantees about the order of results if a SELECT omits the ORDER BY
    clause.  Even so, the order of results does not change from one
    run to the next, and so many applications mistakenly come to depend
    on the arbitrary output order whatever that order happens to be.  However, 
    sometimes new versions of SQLite will contain optimizer enhancements
    that will cause the output order of queries without ORDER BY clauses
    to shift.  When that happens, applications that depend on a certain
    output order might malfunction.  By running the application multiple
    times with this pragma both disabled and enabled, cases where the
    application makes faulty assumptions about output order can be
    identified and fixed early, reducing problems
    that might be caused by linking against a different version of SQLite.

Pragma secure_delete {
    <p>^(<b>PRAGMA DB.secure_delete;
     <br>PRAGMA DB.secure_delete = </b><i>boolean</i>|<b>FAST</b></p>
    <p>Query or change the secure-delete setting.)^ ^When secure_delete is
    on, SQLite overwrites deleted content with zeros.  ^The default
    setting for secure_delete is determined by the [SQLITE_SECURE_DELETE]
    compile-time option and is normally off.  The off setting for
    secure_delete improves performance by reducing the number of CPU cycles
    and the amount of disk I/O.  Applications that wish to avoid leaving
    forensic traces after content is deleted or updated should enable the
    secure_delete pragma prior to performing the delete or update, or else
    run [VACUUM] after the delete or update.

    <p>The "fast" setting for secure_delete (added circa [dateof:3.20.0])
    is an intermediate setting in between "on" and "off".
    When secure_delete is set to "fast",
    SQLite will overwrite deleted content with zeros only if doing so
    does not increase the amount of I/O.  In other words, the "fast"
    setting uses more CPU cycles but does not use more I/O.
    This has the effect of purging all old content from [B-tree|b-tree pages],
    but leaving forensic traces on [freelist|freelist pages].

    ^When there are [ATTACH | attached databases] and no database
    is specified in the pragma, all databases have their secure-delete
    setting altered.
    ^The secure-delete setting for newly attached databases is the setting
    of the main database at the time the ATTACH command is evaluated.

    ^When multiple database connections share the same cache, changing
    the secure-delete flag on one database connection changes it for them

LegacyPragma short_column_names {
    <p><b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>

    <p>Query or change the short-column-names flag. This flag affects
    the way SQLite names columns of data returned by [SELECT] statements.
    See the [full_column_names] pragma for full details.


Pragma shrink_memory {
    <p><b>PRAGMA shrink_memory</b></p>

    <p>^This pragma causes the database connection on which it is invoked
    to free up as much memory as it can, by calling

Pragma soft_heap_limit {
    <p><b>PRAGMA soft_heap_limit<br>
          PRAGMA soft_heap_limit=</b><i>N</i></p>

    <p>^This pragma invokes the [sqlite3_soft_heap_limit64()] interface with
    the argument N, if N is specified and is a non-negative integer.
    ^The soft_heap_limit pragma always returns the same integer
    that would be returned by the [sqlite3_soft_heap_limit64](-1) C-language

Pragma synchronous {
    <p>^(<b>PRAGMA DB.synchronous;
        <br>PRAGMA DB.synchronous = </b>
          <i>0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA</i><b>;</b></p>

    <p>Query or change the setting of the "synchronous" flag.)^
    ^The first (query) form will return the synchronous setting as an 
    integer.  The second form changes the synchronous setting.
    The meanings of the various synchronous settings are as follows:</p>
    <dt><b>EXTRA</b> (3)</dt>
    ^EXTRA synchronous is like FULL with the addition that the directory
    containing a [rollback journal] is synced after that journal is unlinked
    to commit a transaction in DELETE mode.  EXTRA provides additional
    durability if the commit is followed closely by a power loss.</dd>
    <dt><b>FULL</b> (2)</dt>
    ^When synchronous is FULL (2), the SQLite database engine will
    use the xSync method of the [VFS] to ensure that all content is safely
    written to the disk surface prior to continuing.
    This ensures that an operating system crash or power failure will
    not corrupt the database.
    FULL synchronous is very safe, but it is also slower.  FULL is the
    most commonly used synchronous setting when not in [WAL mode].</dd>
    <dt><b>NORMAL</b> (1)</dt>
    ^When synchronous is NORMAL (1), the SQLite database
    engine will still sync at the most critical moments, but less often
    than in FULL mode.  There is a very small (though non-zero) chance that
    a power failure at just the wrong time could corrupt the database in
    NORMAL mode.  But in practice, you are more likely to suffer
    a catastrophic disk failure or some other unrecoverable hardware
    fault.  Many applications choose NORMAL when in [WAL mode].</dd>
    <dt><b>OFF</b> (0)</dt>
    ^With synchronous OFF (0), SQLite continues without syncing
    as soon as it has handed data off to the operating system.
    If the application running SQLite crashes, the data will be safe, but
    the database [cfgerrors|might become corrupted] if the operating system
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, commits can be orders of
    magnitude faster with synchronous OFF.
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
    synchronized before each [checkpoint] and the database file is
    synchronized after each completed [checkpoint] and the WAL file
    header is synchronized when a WAL file begins to be reused after
    a checkpoint, but no sync operations occur during most transactions.
    ^With synchronous=FULL in WAL mode, an additional
    sync operation of the WAL file happens after each transaction commit.
    The extra WAL sync following each transaction help ensure that 
    transactions are durable across a power loss.  Transactions are
    consistent with or without the extra syncs provided by
    If durability is not a concern, then synchronous=NORMAL is normally
    all one needs in WAL mode.</p>

    <p>^The default setting is usually synchronous=FULL.
    The [SQLITE_EXTRA_DURABLE] compile-time option changes the
    default to synchronous=EXTRA.</p>

    <p>The TEMP schema always has synchronous=OFF since the content of
    of TEMP is ephemeral and is not expected to survive a power outage.
    Attempts to change the synchronous setting for TEMP are
    silently ignored.

    <p>See also the [fullfsync] and [checkpoint_fullfsync] pragmas.</p>

Pragma temp_store {
    <p>^(<b>PRAGMA temp_store;
        <br>PRAGMA temp_store = </b>
            <i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>

    <p>Query or change the setting of the "<b>temp_store</b>" parameter.)^
    ^When temp_store is DEFAULT (0), the compile-time C preprocessor macro
    [SQLITE_TEMP_STORE] is used to determine where temporary tables and indices
    are stored.  ^When
    temp_store is MEMORY (2) [temporary tables] and indices are kept in
    as if they were pure [in-memory databases] memory.
    ^When temp_store is FILE (1) [temporary tables] and indices are stored
    in a file.  ^The [temp_store_directory] pragma can be used to specify
    the directory containing temporary files when
    <b>FILE</b> is specified.  ^When the temp_store setting is changed,
    all existing temporary tables, indices, triggers, and views are
    immediately deleted.</p>

    <p>^It is possible for the library compile-time C preprocessor symbol
    [SQLITE_TEMP_STORE] to override this pragma setting.
    ^(The following table summarizes
    the interaction of the [SQLITE_TEMP_STORE] preprocessor macro and the
    temp_store pragma:</p>

    <table cellpadding="2" border="1">
    <tr><th valign="bottom">[SQLITE_TEMP_STORE]</th>
        <th valign="bottom">PRAGMA<br>temp_store</th>
        <th>Storage used for<br>TEMP tables and indices</th></tr>
    <tr><td align="center">0</td>
        <td align="center"><em>any</em></td>
        <td align="center">file</td></tr>
    <tr><td align="center">1</td>
        <td align="center">0</td>
        <td align="center">file</td></tr>
    <tr><td align="center">1</td>
        <td align="center">1</td>
        <td align="center">file</td></tr>
    <tr><td align="center">1</td>
        <td align="center">2</td>
        <td align="center">memory</td></tr>
    <tr><td align="center">2</td>
        <td align="center">0</td>
        <td align="center">memory</td></tr>
    <tr><td align="center">2</td>
        <td align="center">1</td>
        <td align="center">file</td></tr>
    <tr><td align="center">2</td>
        <td align="center">2</td>
        <td align="center">memory</td></tr>
    <tr><td align="center">3</td>
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>

LegacyPragma temp_store_directory {
    <p><b>PRAGMA temp_store_directory;
      <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_temp_directory] global
    variable, which many operating-system interface backends use to
    determine where to store [temporary tables] and indices.</p>

    <p>When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers in the database connection that
    issued the pragma are immediately deleted.  In
    practice, temp_store_directory should be set immediately after the first
    database connection for a process is opened.  If the temp_store_directory
    is changed for one database connection while other database connections
    are open in the same process, then the behavior is undefined and
    probably undesirable.</p>

    <p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
    Never change the temp_store_directory setting if another thread
    within the application is running any SQLite interface at the same time.
    Doing so results in undefined behavior.  Changing the temp_store_directory
    setting writes to the [sqlite3_temp_directory] global
    variable and that global variable is not protected by a mutex.</p>

    <p>The value <i>directory-name</i> should be enclosed in single quotes.
    To revert the directory to the default, set the <i>directory-name</i> to
    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.  An
    error is raised if <i>directory-name</i> is not found or is not
    writable. </p>

    <p>The default directory for temporary files depends on the OS.  Some
    OS interfaces may choose to ignore this variable and place temporary
    files in some other directory different from the directory specified
    here.  In that sense, this pragma is only advisory.</p>

LegacyPragma data_store_directory {
    <p><b>PRAGMA data_store_directory;
      <br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the value of the [sqlite3_data_directory] global
    variable, which windows operating-system interface backends use to
    determine where to store database files specified using a relative

    <p>Changing the data_store_directory setting is <u>not</u> threadsafe.
    Never change the data_store_directory setting if another thread
    within the application is running any SQLite interface at the same time.
    Doing so results in undefined behavior.  Changing the data_store_directory
    setting writes to the [sqlite3_data_directory] global
    variable and that global variable is not protected by a mutex.</p>

    <p>This facility is provided for WinRT which does not have an OS
    mechanism for reading or changing the current working directory.
    The use of this pragma in any other context is discouraged and may
    be disallowed in future releases.</p>


Pragma threads {
    <p><b>PRAGMA threads;
      <br>PRAGMA threads = </b><i>N</i><b>;</b></p>
    <p>Query or change the value of the 
    [sqlite3_limit](db,[SQLITE_LIMIT_WORKER_THREADS],...) limit for
    the current database connection.  This limit sets an upper bound
    on the number of auxiliary threads that a [prepared statement] is
    allowed to launch to assist with a query.  The default limit is 0
    unless it is changed using the [SQLITE_DEFAULT_WORKER_THREADS]
    compile-time option.  When the limit is zero, that means no
    auxiliary threads will be launched.</p>

    <p>This pragma is a thin wrapper around the
    [sqlite3_limit](db,[SQLITE_LIMIT_WORKER_THREADS],...) interface.

Pragma collation_list {
    <p>^(<b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p>)^

Pragma database_list {
    <p>^(<b>PRAGMA database_list;</b></p>
    <p>This pragma works like a query to return one row for each database
    attached to the current database connection.)^
    ^(The second column is the "main" for the main database file, "temp"
    for the database file used to store TEMP objects, or the name of the
    ATTACHed database for other database files.)^
    ^(The third column is the name of the database file itself, or an empty
    string if the database is not associated with a file.)^</p>

Pragma foreign_key_list {
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>

    <p>This pragma returns one row for each [foreign key constraint]
    created by a REFERENCES clause in the CREATE TABLE statement of
    table "<i>table-name</i>".)^

Pragma foreign_key_check {
    <p>^(<b>PRAGMA DB.foreign_key_check;
        <br>PRAGMA DB.foreign_key_check(</b><i>table-name</i><b>);</b>)^</b></p>

    <p>^(The foreign_key_check pragma checks the database, or the table
    called "<i>table-name</i>", for 
    [foreign key constraints] that are violated and returns one row of
    output for each violation.)^  ^There are four columns in each result row.
    ^The first column is the name of the table that contains the REFERENCES
    clause.  ^The second column is the [rowid] of the row that
    contains the invalid REFERENCES clause, or NULL if the child table is a
    [WITHOUT ROWID] table.  ^The third column is the name
    of the table that is referred to. ^The fourth column is the index of
    the specific foreign key constraint that failed.  ^The fourth column
    in the output of the foreign_key_check pragma is the same integer as
    the first column in the output of the [foreign_key_list pragma].
    ^(When a "<i>table-name</i>" is specified, the only foreign key constraints
    checked are those created by REFERENCES clauses in the
    CREATE TABLE statement for <i>table-name</i>.)^</p>

Pragma freelist_count {
    <p>^(<b>PRAGMA DB.freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^</p>

Pragma index_info {
    <p>^(<b>PRAGMA DB.index_info(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns one row for each key column in the named index.)^
    A key column is a column that is actually named in the [CREATE INDEX]
    index statement or [UNIQUE constraint] or [PRIMARY KEY constraint] that
    created the index.  Index entries also usually contain auxiliary
    columns that point back to the table row being indexed.  ^The auxiliary
    index-columns are not shown by the index_info pragma, but they are
    listed by the [index_xinfo pragma].</p>

    <p>Output columns from the index_info pragma are as follows:
    <li>^(The rank of the column within the index.  (0 means left-most.))^
    <li>^The rank of the column within the table being indexed.
    <li>^The name of the column being indexed.

Pragma index_xinfo {
    <p>^(<b>PRAGMA DB.index_xinfo(</b><i>index-name</i><b>);</b></p>
    <p>This pragma returns information about every column in an index.)^
    ^(Unlike this [index_info pragma], this pragma returns information about
    every column in the index, not just the key columns.)^
    (A key column is a column that is actually named in the [CREATE INDEX]
    index statement or [UNIQUE constraint] or [PRIMARY KEY constraint] that
    created the index.  Auxiliary columns are additional columns needed to
    locate the table entry that corresponds to each index entry.)

    <p>Output columns from the index_xinfo pragma are as follows:
    <li>^(The rank of the column within the index. (0 means left-most.
         Key columns come before auxiliary columns.))^
    <li>^The rank of the column within the table being indexed, or -1 if
         the index-column is the [rowid] of the table being indexed.
    <li>^The name of the column being indexed, or NULL if the index-column
         is the [rowid] of the table being indexed.
    <li>^1 if the index-column is sorted in reverse (DESC) order by the
         index and 0 otherwise.
    <li>^The name for the [collating sequence]
         used to compare values in the index-column.
    <li>^1 if the index-column is a key column and 0 if the index-column
         is an auxiliary column.

Pragma index_list {
    <p>^(<b>PRAGMA DB.index_list(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each index associated with the
    given table.)^
    <p>Output columns from the index_list pragma are as follows:
    <li>^(A sequence number assigned to each index for internal tracking
    <li>^(The name of the index.)^
    <li>^("1" if the index is UNIQUE and "0" if not.)^
    <li>^("c" if the index was created by a [CREATE INDEX] statement,
          "u" if the index was created by a [UNIQUE constraint], or
          "pk" if the index was created by a [PRIMARY KEY constraint].)^
    <li>^("1" if the index is a [partial index] and "0" if not.)^

TestPragma stats {
    <p>^(<b>PRAGMA stats;</b>)^ </p>
    <p>This pragma returns auxiliary information about tables and
    indices.  The returned information is used during testing to help
    verify that the query planner is operating correctly.  The format
    and meaning of this pragma will likely change from one release
    to the next. Because of its volatility, the behavior and output
    format of this pragma are deliberately undocumented.</p>


Pragma page_count {
    <p>^(<b>PRAGMA DB.page_count;</b></p>
    <p>Return the total number of pages in the database file.</p>)^

Pragma table_info {
    <p>^(<b>PRAGMA DB.table_info(</b><i>table-name</i><b>);</b></p>
    <p>This pragma returns one row for each column in the named table.)^
    ^Columns in the result set include the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.  ^The "pk" column in the result set is zero
    for columns that are not part of the primary key, and is the index of
    the column in the primary key for columns that are part of the primary
    <p>^The table named in the table_info pragma can also be a view.</p>

DangerousPragma {schema_version} {
    <p><b>PRAGMA DB.schema_version; 
      <br>PRAGMA DB.schema_version = </b><i>integer </i>;

<p>   ^The schema_version pragma will to get or set
       the value of the schema-version integer at offset 40 in the
       [database header]. 

<p>    ^SQLite automatically increments the schema-version whenever the
       schema changes. ^As each SQL statement runs, the schema version is
       checked to ensure that the schema has not changed since the SQL
       statement was [sqlite3_prepare|prepared].
       ^Subverting this mechanism by using "PRAGMA schema_version"
       may cause SQL statement to run using an obsolete schema,
       which can lead to incorrect answers and/or
       [cfgerrors|database corruption].


<p>    ^For the purposes of this pragma, the [VACUUM] command is considered
       a schema change, since [VACUUM] will usual alter the "rootpage"
       values for entries in the [sqlite_master table].

<p>    See also the [application_id pragma] and [user_version pragma].

Pragma {user_version} {
      <p><b>PRAGMA DB.user_version;
      <br>PRAGMA DB.user_version = </b><i>integer </i><b>;</b>

<p>    ^The user_version pragma will to get or set
       the value of the user-version integer at offset 60 in the
       [database header].  The user-version is an integer that is
       available to applications to use however they want.  SQLite
       makes no use of the user-version itself.

<p>    See also the [application_id pragma] and [schema_version pragma].

Pragma compile_options {
    <p><b>PRAGMA compile_options;</b></p>
    <p>^This pragma returns the names of [compile-time options] used when
    building SQLite, one option per row.  ^The "SQLITE_" prefix is omitted
    from the returned option names.  See also the
    [sqlite3_compileoption_get()] C/C++ interface and the
    [sqlite_compileoption_get()] SQL functions.</p>

Pragma integrity_check {
    <p><b>PRAGMA DB.integrity_check;
      <br>PRAGMA DB.integrity_check(</b><i>N</i><b>)</b></p>
    <p>^This pragma does an integrity check of the entire database.  ^The
    integrity_check pragma
    looks for out-of-order records, missing pages, malformed records, missing
    index entries, and UNIQUE, CHECK, and NOT NULL constraint errors.
    ^If the integrity_check pragma finds problems, strings are returned
    (as multiple rows with a single column per row) which describe
    the problems.  ^Pragma integrity_check will return at most <i>N</i>
    errors before the analysis quits, with N defaulting
    to 100.  ^If pragma integrity_check finds no errors, a
    single row with the value 'ok' is returned.</p>

    <p>PRAGMA integrity_check does not find
    [foreign key constraint|FOREIGN KEY] errors.  
    Use the [PRAGMA foreign_key_check] command for to find errors in
    FOREIGN KEY constraints.</p>

    <p>See also the [PRAGMA quick_check] command which does most of the
    checking of PRAGMA integrity_check but runs much faster.</p>

Pragma quick_check {
    <p><b>PRAGMA DB.quick_check;
      <br>PRAGMA DB.quick_check(</b><i>N</i><b>)</b></p>
    <p>^The pragma is like [integrity_check] except that it does not verify
    UNIQUE constraints and does not verify
    that index content matches table content.  By skipping UNIQUE
    and index consistency checks, quick_check is able to run much faster than
    integrity_check.  ^Otherwise the two pragmas are the same.

DebugPragma parser_trace {
    <p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the parser_trace pragma can be used to turn on tracing
    for the SQL parser used internally by SQLite.
    This feature is used for debugging SQLite itself.</p>


DebugPragma vdbe_trace {
    <p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_trace pragma can be used to cause virtual machine
    opcodes to be printed on standard output as they are evaluated.
    This feature is used for debugging SQLite.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 


DebugPragma vdbe_listing {
    <p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_listing pragma can be used to cause a complete
    listing of the virtual machine opcodes to appear on standard output
    as each statement is evaluated.
    With listing is on, the entire content of a program is printed
    just prior to beginning execution.  The statement
    executes normally after the listing is printed.
    This feature is used for debugging SQLite itself.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 


DebugPragma vdbe_debug {
    <p><b>PRAGMA vdbe_debug = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_debug pragma is a shorthand for three other
    debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace.
    This feature is used for debugging SQLite itself.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 

DebugPragma vdbe_addoptrace {
    <p><b>PRAGMA vdbe_addoptrace = </b><i>boolean</i><b>;</b></p>

    <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
    option, then the vdbe_addoptrace pragma can be used to cause a complete
    VDBE opcodes to be displayed as they are created during code generation.
    This feature is used for debugging SQLite itself.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 


Pragma wal_checkpoint {
    <p><b>PRAGMA DB.wal_checkpoint;</b><br>
       <b>PRAGMA DB.wal_checkpoint(PASSIVE);</b><br>
       <b>PRAGMA DB.wal_checkpoint(FULL);</b><br>
       <b>PRAGMA DB.wal_checkpoint(RESTART);</b><br>
       <b>PRAGMA DB.wal_checkpoint(TRUNCATE);</b>

    <p>^If the [write-ahead log] is enabled (via the [journal_mode pragma]),
    this pragma causes a [checkpoint] operation to run on database
    <i>database</i>, or on all attached databases if <i>database</i>
    is omitted.  ^If [write-ahead log] mode is disabled, this pragma is a
    harmless no-op.</p>

    <p>^Invoking this
    pragma without an argument is equivalent to calling the
    [sqlite3_wal_checkpoint()] C interface.</p>
    ^(Invoking this pragma with an argument is equivalent to calling the
    [sqlite3_wal_checkpoint_v2()] C interface with a 
    [SQLITE_CHECKPOINT_PASSIVE | 3rd parameter]
    corresponding to the argument:)^

      Checkpoint as many frames as possible without waiting for any database 
      readers or writers to finish. Sync the db file if all frames in the log
      are checkpointed.)^ ^(This mode is the same as calling the
      [sqlite3_wal_checkpoint()] C interface.)^ ^(The
      [sqlite3_busy_handler|busy-handler callback] is never invoked in
      this mode.)^
      This mode blocks 
      (invokes the [sqlite3_busy_handler|busy-handler callback])
      until there is no
      database writer and all readers are reading from the most recent database
      snapshot. It then checkpoints all frames in the log file and syncs the
      database file.)^ ^(FULL blocks concurrent writers while it is
      running, but readers can proceed.)^
      This mode works the same way as FULL with the addition that after 
      checkpointing the log file it blocks (calls the 
      [sqlite3_busy_handler|busy-handler callback])
      until all readers are finished with the log file.)^ This ensures 
      that the next client to write to the database file restarts the log file 
      from the beginning. ^(RESTART blocks concurrent writers while it is
      running, but allowed readers to proceed.)^
      This mode works the same way as RESTART with the 
      addition that the WAL file is truncated to zero bytes upon successful

    <p>^The wal_checkpoint pragma returns a single row with three
    integer columns.  ^The first column is usually 0 but will be
    1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing,
    for example because another thread or process was actively
    using the database.  ^In other words, the first column is 0 if the
    equivalent call to [sqlite3_wal_checkpoint_v2()] would have returned
    [SQLITE_OK] or 1 if the equivalent call would have returned [SQLITE_BUSY].
    ^The second column is the number of modified pages that have been
    written to the write-ahead log file.
    ^The third column is the number of pages in the write-ahead log file
    that have been successfully moved back into the database file at
    the conclusion of the checkpoint.
   ^The second and third column are -1 if there is no
    write-ahead log, for example if this pragma is invoked on a database
    connection that is not in [WAL mode].</p>

Pragma wal_autocheckpoint {
    <p><b>PRAGMA wal_autocheckpoint;<br>
          PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>

    <p>^This pragma queries or sets the [write-ahead log] 
    [checkpointing | auto-checkpoint] interval.
    ^When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    ^Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.
    ^All automatic checkpoints are [sqlite3_wal_checkpoint_v2|PASSIVE].</p>

    <p>^Autocheckpointing is enabled by default with an interval


Pragma ignore_check_constraints {
    <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>

    <p>This pragma enables or disables the enforcement of CHECK constraints.)^
    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, the SQLITE_MASTER tables in which database
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning></p>

Section {List Of PRAGMAs} {toc} {{pragma list}}
set lx {}
foreach prag [array names PragmaRef] {
  set ref $PragmaRef($prag)
  if {[info exists PragmaLegacy($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 3]
  } elseif {[info exists PragmaDebug($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 4]
  } elseif {[info exists PragmaTest($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 5]
  } else {
    lappend lx [list "PRAGMA $ref" $prag 0]
hd_list_of_links {} 250 [lsort -index 1 $lx]
<li>Pragmas whose names are <s>struck through</s>
are deprecated. Do not use them. They exist
for historical compatibility.
<li>These pragmas are used for debugging SQLite and
are only available when SQLite is compiled using [SQLITE_DEBUG].
<li>These pragmas are used for testing SQLite and are not recommended
for use in application programs.</ol></p>
foreach prag [lsort [array names PragmaBody]] {
  hd_fragment pragma_$prag

  hd_puts "<h _id=pragma_$prag style=\"display:none\"> PRAGMA "
  hd_puts [join $PragmaKeys($prag) ", "]
  hd_puts "</h>"

  foreach x $PragmaKeys($prag) {
    hd_keywords *$x "PRAGMA $x" "$x pragma"
  hd_puts "<hr>"
  hd_resolve $PragmaBody($prag)