pragma.html

sqlite的帮助文档

    is set, such columns are always named <table-name/alias>
    <column-name> 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>

<a name="pragma_fullfsync"></a>

<li><p><b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>0 | 1</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>

<a name="pragma_incremental_vacuum"></a>

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

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

<a name="pragma_journal_mode"></a>

<li><p><b>PRAGMA journal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | OFF</i>
       <br>PRAGMA <i>database</i>.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 <a href="c3ref/sqlite3.html">database connection</a>.</p>

    <p>The first two forms of this pragma query the current journaling
    mode.  In the first form, the default journal_mode is returned.
    The default journaling mode is the mode used by databases added
    to the connection by subsequent <a href="lang_attach.html">ATTACH</a> statements.  The second
    form returns the current journaling mode for a specific database.</p>

    <p>The last two forms change the journaling mode.  The 4th form
    changes the journaling mode for a specific database connection.
    Use "main" for the main database (the database that was opened by
    the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
    <a href="c3ref/open.html">sqlite3_open_v2()</a> interface call) and use "temp" for database
    that holds TEMP tables.  The 3rd form changes the journaling mode
    on all databases and it changes the default journaling mode that
    will be used for new databases added by subsequent <a href="lang_attach.html">ATTACH</a>
    commands.</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 documented 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 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.</p>

    <p>The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  This saves disk I/O but that 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 go corrupt.</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 <a href="lang_transaction.html">ROLLBACK</a> command
    no longer works. If the application crashes
    in the middle of a transaction when the OFF journaling mode is
    set, then the database file will very likely go corrupt.</p>
</li>

<a name="pragma_journal_size_limit"></a>

<li><p><b>
    PRAGMA journal_size_limit<br>
    PRAGMA journal_size_limit = </b><i>N</i> <b>;</b>

  <p>If a database connection is operating in either "exclusive mode"
  (PRAGMA locking_mode=exclusive) or "persistent journal mode"
  (PRAGMA journal_mode=persist) then under certain circumstances
  after committing a transaction the journal file may remain in
  the file-system. This increases efficiency but also consumes
  space in the file-system. After a large transaction (e.g. a VACUUM),
  it may consume a very large amount of space.

  <p>This pragma may be used to limit the size of journal files left
  in the file-system after transactions are committed on a per database
  basis. Each time a transaction is committed, SQLite compares the
  size of the journal file left in the file-system to the size limit
  configured using this pragma. If the journal file is larger than the
  limit allows for, 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.
  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 limit value is -1 (no limit), which
  may be overridden by defining the preprocessor macro
  SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT at compile time.

  <p>If no &lt;database&gt; is specified, the pragma operates on 
  the main database.
  There is no way to operate on all attached databases using a single
  PRAGMA statement, nor is there a way to set the limit to use for databases
  that will be attached in the future.
</li>


<a name="pragma_legacy_file_format"></a>

<li><p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>ON | OFF</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>

    <p>When the 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>This flag only affects newly created databases.  It has no
    effect on databases that already exist.</p>

    <p>The default file format is set by the
    <a href="compile.html#default_file_format">SQLITE_DEFAULT_FILE_FORMAT</a> compile-time option.</p>
</li>

<a name="pragma_locking_mode"></a>

<li><p><b>PRAGMA locking_mode;
       <br>PRAGMA 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), 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 be released until the next time
    the database file is accessed.</p>

    <p>There are two reasons to set the locking-mode to EXCLUSIVE. One
    is if the application actually wants to prevent other processes from
    accessing the database file. The other is that a small number of
    filesystem operations are saved by optimizations enabled in this
    mode. This may be significant in embedded environments.</p>

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

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

    <p>Then the locking mode applies only to the named database.  If no
    database name qualifier preceeds the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent <a href="lang_attach.html">ATTACH</a> commands.</p>

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

<a name="pragma_page_size"></a>

<li><p><b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database. The page size
    may only be set if the database has not yet been created. The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to <a href="limits.html#max_page_size">SQLITE_MAX_PAGE_SIZE</a>.
    The maximum value for <a href="limits.html#max_page_size">SQLITE_MAX_PAGE_SIZE</a> is 32768.
    </p>

    <p>When a new database is created, SQLite assigned a default page size
    based on information received from the xSectorSize and 
    xDeviceCharacteristics methods of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object
    of the newly created database file.  The page_size pragma will only
    cause an immediate change in the
    page size if it is issued while the database is still empty, prior 
    to the first CREATE TABLE statement.  As of <a href="releaselog/3_5_8.html">version 3.5.8</a>, if
    the page_size pragma is used to specify a new page size just prior to
    running the <a href="lang_vacuum.html">VACUUM</a> command then <a href="lang_vacuum.html">VACUUM</a> will change the page
    size to the new value.</p>

    <p>If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,
    then the default page size is chosen to be the largest page size
    less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic
    write is enabled according to the
    xDeviceCharacteristics method of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object for
    the database file.  If the SQLITE_ENABLE_ATOMIC_WRITE option is
    disabled or if xDeviceCharacteristics reports no suitable atomic
    write page sizes, then the default page size is the larger of 
    SQLITE_DEFALT_PAGE_SIZE
    and the sector size as reported by the xSectorSize method of the
    <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object, but not more than 
    SQLITE_MAX_DEFAULT_PAGE_SIZE.  The normal configuration for SQLite
    running on workstations is for atomic write to be
    disabled, for the maximum page size to be set to 32768, for
    SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
    maximum default page size to be set to 8192.  The default xSectorSize
    method on workstation implementations always reports a sector size
    of 512 bytes.  Hence, 
    the default page size chosen by SQLite is usually 1024 bytes.</p>
</li>

<a name="pragma_max_page_count"></a>

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

<a name="pragma_read_uncommitted"></a>

<li><p><b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>0 | 1</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 <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> API.
    Cache sharing is disabled by default.
    </p>
</li>

<a name="pragma_short_column_names"></a>

<li><p><b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>0 | 1</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 <a href="lang_select.html">SELECT</a> 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 <a href="lang_select.html">SELECT</a> statement 
    joins two or more tables together, or simply &lt;column-name&gt; if