lang.tcl

sqlite数据库源码

       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in
    the database.  When temp_store is DEFAULT (0), the compile-time value
    of the symbol TEMP_STORE is used for the temporary database.  
    When temp_store is MEMORY (2), an in-memory database is used.  
    When temp_store is FILE (1), a temporary database file on disk will be used.
    It is possible for the library compile-time symbol TEMP_STORE to override 
    this setting.  The following table summarizes this:</p>

<table cellpadding="2">
<tr><th>TEMP_STORE</th><th>temp_store</th><th>temp database location</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>

    <p>This pragma changes the temp_store mode for whenever the database
    is opened in the future.  The temp_store mode for the current session
    is unchanged.  Use the 
    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the
    temp_store mode for the current session.</p></li>

<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks = ON; </b>(1)<b>
       <br>PRAGMA empty_result_callbacks = OFF;</b> (0)</p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p></li>

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

<li><p><b>PRAGMA full_column_names = ON; </b>(1)<b>
       <br>PRAGMA full_column_names = OFF;</b> (0)</p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>

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

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

<li><p><b>PRAGMA integrity_check;</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 a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.</p></li>

<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_show_datatypes"></a>
<li><p><b>PRAGMA show_datatypes = ON; </b>(1)<b>
    <br>PRAGMA show_datatypes = OFF;</b> (0)</p>
    <p>When turned on, the SHOW_DATATYPES pragma causes extra entries containing
    the names of <a href="datatypes.html">datatypes</a> of columns to be
    appended to the 4th ("columnNames") argument to <b>sqlite_exec()</b>
    callbacks.  When
    turned off, the 4th argument to callbacks contains only the column names.
    The datatype for table columns is taken from the CREATE TABLE statement
    that defines the table.  Columns with an unspecified datatype have a
    datatype of "NUMERIC" and the results of expression have a datatype of
    either "TEXT" or "NUMERIC" depending on the expression.
    The following chart illustrates the difference for the query
    "SELECT 'xyzzy', 5, NULL AS empty ":</p>

    <blockquote><table border=0>
    <tr><th>show_datatypes=OFF</th><th width=30></th>
        <th>show_datatypes=ON</th></tr>
    <tr><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = 0;
    </td><td></td><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = "TEXT";<br>
       azCol[4] = "NUMERIC";<br>
       azCol[5] = "TEXT";<br>
       azCol[6] = 0;
    </td></table></blockquote></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 affecting
    the database for the duration of the current database connection.
    The synchronous flag reverts to its default value when the database
    is closed and reopened.  For additional information on the synchronous
    flag, see the description of the <a href="#pragma_default_synchronous">
    <b>default_synchronous</b></a> pragma.</p>
    </li>

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

<a name="pragma_temp_store"></a>
<li><p><b>PRAGMA temp_store;
       <br>PRAGMA temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "temp_store" flag affecting
    the database for the duration of the current database connection.
    The temp_store flag reverts to its default value when the database
    is closed and reopened.  For additional information on the temp_store
    flag, see the description of the <a href="#pragma_default_temp_store">
    <b>default_temp_store</b></a> pragma.  Note that it is possible for 
    the library compile-time options to override this setting. </p>

    <p>When the temp_store setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.
    </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>
</ul>

<p>No error message is generated if an unknown pragma is issued.
Unknown pragmas are ignored.</p>
}


Section REPLACE replace

Syntax {sql-statement} {
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="#insert">INSERT</a> command.  This alias is provided for
compatibility with MySQL.  See the 
<a href="#insert">INSERT</a> command documentation for additional
information.</p>  
}


Section SELECT select

Syntax {sql-statement} {
SELECT [ALL | DISTINCT] <result> [FROM <table-list>]
[WHERE <expr>]
[GROUP BY <expr-list>]
[HAVING <expr>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
[LIMIT <integer> [LP OFFSET | , RP <integer>]]
} {result} {
<result-column> [, <result-column>]*
} {result-column} {
STAR | <table-name> . STAR | <expr> [ [AS] <string> ]
} {table-list} {
<table> [<join-op> <table> <join-args>]*
} {table} {
<table-name> [AS <alias>] |
( <select> ) [AS <alias>]
} {join-op} {
, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
} {join-args} {
[ON <expr>] [USING ( <id-list> )]
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
ASC | DESC
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}

puts {
<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.  If the expression is the name of}
puts "a table followed by [Operator .*] then the result is all columns"
puts {in that one table.</p>

<p>The DISTINCT keyword causes a subset of result rows to be returned, 
in which each result row is different.  NULL values are not treated as 
distinct from eachother.  The default behavior is that all result rows 
be returned, which can be made explicit with the keyword ALL.</p>

<p>The query is executed against one or more tables specified after
the FROM keyword.  If multiple tables names are separated by commas,
then the query is against the cross join of the various tables.
The full SQL-92 join syntax can also be used to specify joins.
A sub-query
in parentheses may be substituted for any table name in the FROM clause.
The entire FROM clause may be omitted, in which case the result is a
single row consisting of the values of the expression list.
</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.</p>

<p>The GROUP BY clauses causes one or more rows of the result to
be combined into a single row of output.  This is especially useful
when the result contains aggregate functions.  The expressions in
the GROUP BY clause do <em>not</em> have to be expressions that
appear in the result.  The HAVING clause is similar to WHERE except
that HAVING applies after grouping has occurred.  The HAVING expression
may refer to values, even aggregate functions, that are not in the result.</p>

<p>The ORDER BY clause causes the output rows to be sorted.  
The argument to ORDER BY is a list of expressions that are used as the
key for the sort.  The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  Each
sort expression may be optionally followed by ASC or DESC to specify
the sort order.</p>

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A negative LIMIT indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.
In a compound query, the LIMIT clause may only appear on the
final SELECT statement.
The limit is applied to the entire query not
to the individual SELECT statement to which it is attached.
</p>

<p>A compound SELECT is formed from two or more simple SELECTs connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In
a compound SELECT, all the constituent SELECTs must specify the
same number of result columns.  There may be only a single ORDER BY
clause at the end of the compound SELECT.  The UNION and UNION ALL
operators combine the results of the SELECTs to the right and left into
a single big table.  The difference is that in UNION all result rows
are distinct where in UNION ALL there may be duplicates.
The INTERSECT operator takes the intersection of the results of the
left and right SELECTs.  EXCEPT takes the result of left SELECT after
removing the results of the right SELECT.  When three are more SELECTs
are connected into a compound, they group from left to right.</p>
}


Section UPDATE update

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>
}

puts {
<p>The UPDATE statement is used to change the value of columns in 
selected rows of a table.  Each assignment in an UPDATE specifies
a column name to the left of the equals sign and an arbitrary expression
to the right.  The expressions may use the values of other columns.
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>
}


Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

puts {
<p>The VACUUM command is an SQLite extension modelled after a similar
command found in PostgreSQL.  If VACUUM is invoked with the name of a
table or index then it is suppose to clean up the named table or index.
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.</p>

<p>
VACUUM became a no-op when the GDBM backend was removed from
SQLITE in version 2.0.0.
VACUUM was reimplimented in version 2.8.1.
The index or table name argument is now ignored.
</p>

<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space.  This makes the database 
file larger than it ne