Documentation Source Text

chng {2013-08-29 (3.8.0)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
<li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].

<li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    subdirectory of the source tree.
<li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
<li>Add the [sqlite3_cancel_auto_extension(X)] interface.
<li>A running SELECT statement that lacks a FROM clause (or any other statement that
    never reads or writes from any database file) will not prevent a read
    transaction from closing.

chng {2013-08-29 (3.8.0)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
<li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
<li>Added the [cache_spill pragma].
<li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    subdirectory of the source tree.
<li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
<li>Add the [sqlite3_cancel_auto_extension(X)] interface.
<li>A running SELECT statement that lacks a FROM clause (or any other statement that
    never reads or writes from any database file) will not prevent a read
    transaction from closing.

  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
  return {

    <p><u>Do not use this pragma!</u> This pragma is deprecated 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].
    </p>
  }
}
proc DebugDisclaimer {} {
  return {

    <p>This pragma is intended for use when debugging SQLite itself.  It
    is only contained in the build when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
................................................................................
         <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()].
}
















Pragma cache_size {
    <p>^(<b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>pages</i><b>;
       <br>PRAGMA 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
................................................................................
    setting is irrelevant.</p>
}

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

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


}

LegacyPragma default_cache_size {
    ^(<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
................................................................................
    DISCLAIMER
}

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

    DISCLAIMER

    <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";
................................................................................
}


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

    DISCLAIMER

    <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:
    <ol>
    <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>
................................................................................
        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.
        </p></li>
    <li><p>The name of the result column is a combination of the source table
        and source column name:  TABLE.COLUMN</p></li>
    </ol>


}

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
................................................................................
    </p>
}

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

    DISCLAIMER

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


}

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

    DISCLAIMER

    <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
................................................................................
    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
    pathname.</p>

    DISCLAIMER

    <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 collation_list {
    <p>^(<b>PRAGMA collation_list;</b></p>
    <p>Return a list of the collating sequences defined for the current
    database connection.</p>)^
}
................................................................................
    integrity_check.  Otherwise the two pragmas are the same.
    </p>
}

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

    DISCLAIMER

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

    DISCLAIMER

    <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 
    information.</p>


}

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

    DISCLAIMER

    <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 
    information.</p>


}

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

    DISCLAIMER

    <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 
    information.</p>


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

    DISCLAIMER

    <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 
    information.</p>


}

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

  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].
    </p>
  }
}
proc DebugDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    This pragma is intended for use when debugging SQLite itself.  It
    is only contained in the build when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
................................................................................
         <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()].
}

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

    <p>^(The cache_spill pragme 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 unusally 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.
}

Pragma cache_size {
    <p>^(<b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>pages</i><b>;
       <br>PRAGMA 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
................................................................................
    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
................................................................................
    <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.

    DISCLAIMER
}

LegacyPragma default_cache_size {
    ^(<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
................................................................................
    DISCLAIMER
}

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>

    DISCLAIMER
}   
    

Pragma encoding {
   <p>^(<b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
................................................................................
}


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:
    <ol>
    <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>
................................................................................
        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.
        </p></li>
    <li><p>The name of the result column is a combination of the source table
        and source column name:  TABLE.COLUMN</p></li>
    </ol>

    DISCLAIMER
}

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
................................................................................
    </p>
}

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

    DISCLAIMER
}

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

    DISCLAIMER
}

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
    pathname.</p>



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

    DISCLAIMER
}

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>)^
}
................................................................................
    integrity_check.  Otherwise the two pragmas are the same.
    </p>
}

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>

    DISCLAIMER
}

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 
    information.</p>

    DISCLAIMER
}

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 
    information.</p>

    DISCLAIMER
}

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 
    information.</p>

    DISCLAIMER
}
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 
    information.</p>

    DISCLAIMER
}

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