pragma.tcl

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

    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 effected 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 8192. The upper limit may be modified by setting
    the value of macro SQLITE_MAX_PAGE_SIZE during compilation.  The
    maximum upper bound is 32768.
    </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="capi3ref.html#sqlite3_enable_shared_cache">
    sqlite3_enable_shared_cache()</a> API and is only available between
    connections running the same thread.  Cache sharing is off 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 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>

<a name="pragma_synchronous"></a>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = FULL; </b>(2)<b>
       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA synchronous = OFF; </b>(0)</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 slow.  
    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>In SQLite version 2, the default value is NORMAL. For version 3, the
    default was changed to FULL.
    </p>
</li>


<a name="pragma_temp_store"></a>
<li><p><b>PRAGMA temp_store;
       <br>PRAGMA temp_store = DEFAULT;</b> (0)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)<b>
       <br>PRAGMA temp_store = MEMORY;</b> (2)</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
    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 memory.
    When temp_store is FILE (1) temporary tables and indices are stored
    in a file.  The <a href="#pragma_temp_store_directory">
    temp_store_directory</a> pragma can be used to specify the directory
    containing this file.
    <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
    TEMP_STORE to override this pragma setting.  The following table summarizes
    the interaction of the TEMP_STORE preprocessor macro and the
    temp_store pragma:</p>

    <blockquote>
    <table cellpadding="2" border="1">
    <tr><th valign="bottom">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>
    </table>
    </blockquote>
    </li>
    <br>

<a name="pragma_temp_store_directory"></a>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = 'directory-name';</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
    practice, temp_store_directory should be set immediately after the 
    database is opened.  </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.  For
    Unix/Linux/OSX, the default is the is the first writable directory found
    in the list of: <b>/var/tmp, /usr/tmp, /tmp,</b> and <b>
    <i>current-directory</i></b>.  For Windows NT, the default 
    directory is determined by Windows, generally
    <b>C:\Documents and Settings\<i>user-name</i>\Local Settings\Temp\</b>. 
    Temporary files created by SQLite are unlinked immediately after
    opening, so that the operating system can automatically delete the
    files when the SQLite process exits.  Thus, temporary files are not
    normally visible through <i>ls</i> or <i>dir</i> commands.</p>
 
    </li>
</ul>
}

Section {Pragmas to query the database schema} schema

puts {
<ul>
<a name="pragma_database_list"></a>
<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the database was attached with.  The first row will be for 
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<a name="pragma_foreign_key_list"></a>
<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>

<a name="pragma_freelist_count"></a>
<li><p><b>PRAGMA [database].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>

<a name="pragma_index_info"></a>
<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>

<a name="pragma_index_list"></a>
<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>

<a name="pragma_table_info"></a>
<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>
}

Section {Pragmas to query/modify version values} version

puts {

<ul>
<a name="pragma_schema_version"></a>
<a name="pragma_user_version"></a>
<li><p><b>PRAGMA [database.]schema_version; 
       <br>PRAGMA [database.]schema_version = </b><i>integer </i><b>;
       <br>PRAGMA [database.]user_version;
       <br>PRAGMA [database.]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 schema-version is usually only manipulated internally by SQLite.  
       It is incremented by SQLite whenever the database schema is modified 
       (by creating or dropping a table or index). The schema version is 
       used by SQLite each time a query is executed to ensure that the 
       internal cache of the schema used when compiling the SQL query matches 
       the schema of the database against which the compiled query is actually 
       executed.  Subverting this mechanism by using "PRAGMA schema_version" 
       to modify the schema-version is potentially dangerous and may lead 
       to program crashes or database corruption. Use with caution!</p>
  
<p>    The user-version is not used internally by SQLite. It may be used by
       applications for any purpose.</p>
</li>
</ul>
}

Section {Pragmas to debug the library} debug

puts {
<ul>
<a name="pragma_integrity_check"></a>
<li><p><b>PRAGMA integrity_check;
    <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, malformed records, and
    corrupt indices.
    If any problems are found, then strings are returned (as multiple
    rows with a single column per row) which describe
    the problems.  At most <i>integer</i> errors will be reported
    before the analysis quits.  The default value for <i>integer</i>
    is 100.  If no errors are found, a single row with the value "ok" is
    returned.</p></li>

<a name="pragma_parser_trace"></a>
<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
    <br>PRAGMA parser_trace = OFF;</b> (0)</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 without the NDEBUG macro.
    </p></li>

<a name="pragma_vdbe_trace"></a>
<li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b>
    <br>PRAGMA vdbe_trace = OFF;</b> (0)</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>

<a name="pragma_vdbe_listing"></a>
<li><p><b>PRAGMA vdbe_listing = ON; </b>(1)<b>
    <br>PRAGMA vdbe_listing = OFF;</b> (0)</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.  This is like automatically
    executing an EXPLAIN prior to each statement.  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>

}