Documentation Source Text

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

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

<p>
A pragma can take a single argument or it can have no argument at all.
In many pragmas, the argument is a boolean.  The boolean can be one of:
</p>

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

<p>Keyword arguments can optionally appear in quotes.  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, the number is returned, not
the keyword.</p>

<p>A pragma may have an optional database name before the pragma name.
The database name is the name of an [ATTACH]-ed database.  Or it can be
"main" or "temp" for the main and the TEMP databases.  If the optional
database name is omitted, "main" is assumed.  In some pragmas, the database
name is meaningless and is simply ignored.</p>

<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>boolean</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 

    <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 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_methods | Application Defined Page Cache]. The default

    page cache implemention that is built into SQLite does honor the 
    suggestion if it can, but alternative page caches implementations
    set by the application at run-time may choose to ignore this suggestion.
    The default suggested cache size is 2000.</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.  Use
    the [default_cache_size]
    pragma to check the cache size permanently.</p></li>

<tcl>Subsection case_sensitive_like</tcl>
<li><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 can change
    this behavior.  When case_sensitive_like is enabled,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p>
    </li>

<tcl>Subsection count_changes</tcl>
<li><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.</p>

    <p>The use of this pragma is discouraged.  A better way to get the
    row change count is to use the [sqlite3_changes()] or
    [sqlite3_total_changes()] interfaces.</p>

<tcl>Subsection default_cache_size</tcl>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA 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.

    </p></li>


<tcl>Subsection empty_result_callbacks</tcl>
<li><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()] call 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>

    <p>This pragma is legacy.  It was created long ago in the early
    days of SQLite before the prepared statement interface was available.
    Do not use this pragma.  It is likely to go away in a future release</p>
   
    

<tcl>Subsection encoding</tcl>
<li><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>Databases created by the [ATTACH] command always use the same encoding
    as the main database.</p>
</li>

<tcl>Subsection full_column_names</tcl>
<li><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 affects
    the way SQLite names columns of data returned by [SELECT] statements
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;&lt;column-name&gt; if the [SELECT] statement joins 
    two or
    more tables together, or simply &lt;column-name&gt; if the [SELECT]
    statement queries a single table. When the full-column-names flag
    is set, such columns are always named &lt;table-name/alias&gt;
    &lt;column-name&gt; regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<tcl>Subsection fullfsync</tcl>
<li><p><b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
    <p>Query or change the fullfsync flag. This flag affects
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  The default value is off.  As of this
    writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.
    </p>
</li>

    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>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA journal_mode;

       <br>PRAGMA journal_mode


              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | 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 two forms of this pragma query the current journaling
    mode.  In the first form, the default journal_mode is returned.

  PRAGMA statement, nor is there a way to set the limit to use for databases
  that will be attached in the future.
</li>


<tcl>Subsection legacy_file_format</tcl>
<li><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 older versions of SQLite.</p>

    form attempts to modify the maximum page count.  The maximum page
    count cannot be reduced below the current database size.
    </p>
</li>

<tcl>Subsection read_uncommitted</tcl>
<li><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>

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

<tcl>Subsection reverse_unordered_selects</tcl>
<li><p><b>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b></p>
    <p>When enabled, this PRAGMA causes [SELECT] statements without a
    an ORDER BY clause to emit their results in the reverse order of what
    they normally would.  This can help debug applications that are
    making invalid assumptions about the result order.<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

    identified and fixed early, reducing problems
    that might be caused by linking against a different version of SQLite.
    </p>
</li>

<tcl>Subsection short_column_names</tcl>
<li><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
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;&lt;column-name&gt; if the [SELECT] statement 
    joins two or more tables together, or simply &lt;column-name&gt; if 
    the [SELECT] statement queries a single table. When the short-column-names 
    flag is set, such columns are always named &lt;column-name&gt; 
    regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<tcl>Subsection synchronous</tcl>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = </b>
          <i>0 | OFF | 1 | NORMAL | 2 | FULL</i><b>;</b></p>

    <p>Query or change the setting of the "synchronous" flag.  
    The first (query) form will return the setting as an 
    integer.  When synchronous is FULL (2), the SQLite database engine will
    pause at critical moments to make sure that data has actually been 
    written to the disk surface before continuing.  This ensures that if
    the operating system crashes or if there is a power failure, the database
    will be uncorrupted after rebooting.  FULL synchronous is very 
    safe, but it is also slower. 
    When synchronous is NORMAL, the SQLite database
    engine will still pause 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.
    With synchronous OFF (0), SQLite continues without pausing
    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 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, some
    operations are as much as 50 or more times faster with synchronous OFF.
    </p>

    <p>The default setting is synchronous=FULL.
    </p>
</li>


<tcl>Subsection temp_store</tcl>
<li><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

    </table>
    </blockquote>
    </li>
    <br>

<tcl>Subsection temp_store_directory</tcl>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
    <p>Query or change the setting of the "temp_store_directory" - the
    directory where files used for storing [temporary tables] and indices
    are kept.  This setting lasts for the duration of the current connection
    only and resets to its default value for each new connection opened.

    <p>When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.  In

<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>

<tcl>Subsection freelist_count</tcl>
<li><p><b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file. Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 
    command with a large value of N will shrink the database file by this
    number of pages. </p></li>

<tcl>Subsection index_info</tcl>
<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p></li>

<tcl>Subsection index_list</tcl>
<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p></li>

<tcl>Subsection page_count</tcl>
<li><p><b>PRAGMA page_count;</b></p>
    <p>Return the total number of pages in the database file.</p></li>

<tcl>Subsection table_info</tcl>
<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p></li>
</ul>

<tcl>Section {Pragmas to query/modify version values} version</tcl>

<ul>
<tcl>Subsection schema_version user_version</tcl>
<li><p><b>PRAGMA schema_version; 
       <br>PRAGMA schema_version = </b><i>integer </i><b>;
       <br>PRAGMA user_version;
       <br>PRAGMA user_version = </b><i>integer </i><b>;</b>

  
<p>    The pragmas schema_version and user_version are used to set or get
       the value of the schema-version and user-version, respectively. Both
       the schema-version and the user-version are 32-bit signed integers
       stored in the database header.</p>
  

    <p>The pragma is like [integrity_check] except that it does not verify
    that index content matches table content.  By skipping the verification
    of index content, quick_check is able to run much faster than
    integrity_check.  Otherwise the two pragmas are the same.
    </p></li>

<tcl>Subsection parser_trace</tcl>
<li><p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>

    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled with the SQLITE_DEBUG
    compile-time option.
    </p></li>

<tcl>Subsection vdbe_trace</tcl>
<li><p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>

    <p>Turn tracing of the virtual database engine inside of the
    SQLite library on and off.  This is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>

<tcl>Subsection vdbe_listing</tcl>
<li><p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>

    <p>Turn listings of virtual machine programs on and off.
    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 is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>
</ul>