Documentation Source Text

  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    hd_fragment version_$label
  }
  hd_puts "<dt><b>$date</b></dt>"
  hd_resolve "<dd><p><ul>$desc</ul></p>"
  hd_puts "</dd>"
  if {[regexp {\((3\.\d+\.\d+)\)} $date all vers]} {
    set tag [string map {. _} $vers]
    file mkdir $DEST/releaselog
    set filename releaselog/$tag.html
    hd_open_aux $filename
    hd_header "SQLite Release $vers On $date"
    hd_keywords "Version $vers" "version $vers"
    hd_enable_main 0

  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    hd_fragment version_$label
  }
  hd_puts "<dt><b>$date</b></dt>"
  hd_resolve "<dd><p><ul>$desc</ul></p>"
  hd_puts "</dd>"
  if {[regexp {\((3\.\d+\.\d+)[ a-z]*\)} $date all vers]} {
    set tag [string map {. _} $vers]
    file mkdir $DEST/releaselog
    set filename releaselog/$tag.html
    hd_open_aux $filename
    hd_header "SQLite Release $vers On $date"
    hd_keywords "Version $vers" "version $vers"
    hd_enable_main 0

  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;
    char zSql[] = "SELECT .....";

    do {
      /* Compile the statement from SQL. Assume success. */
      sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);

      while( SQLITE_ROW==sqlite3_step(pStmt) ){
        /* Do something with the row of available data */

  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;
    char zSql&#91;] = "SELECT .....";

    do {
      /* Compile the statement from SQL. Assume success. */
      sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);

      while( SQLITE_ROW==sqlite3_step(pStmt) ){
        /* Do something with the row of available data */

proc Keyword {name} {
  return "<font color=\"#2c2cf0\">$name</font>"
}
proc Example {text} {
  hd_puts "<blockquote><pre>$text</pre></blockquote>"
}

proc Section {name label} {
  global DOC
  hd_close_main
  hd_open_main lang_$label.html
  hd_header "SQLite Query Language: $name" $DOC/pages/lang.in

  hd_puts {<a href="lang.html"><h2>SQL As Understood By SQLite</h2></a>}
  hd_puts "<h3>$name</h3>"
}

###############################################################################
Section {ALTER TABLE} altertable

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> <alteration>
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
................................................................................

<p>SQLite's version of the ALTER TABLE command allows the user to 
rename or add a new column to an existing table. It is not possible
to remove a column from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>[database-name.]table-name</i> to <i>new-table-name</i>. This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.
</p>


<p>The ADD [COLUMN] syntax is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
<i>Column-def</i> may take any of the forms permissable in a CREATE TABLE 
statement, with the following restrictions:
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
................................................................................

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database
is <a href="lang_vacuum.html">VACUUM</a>ed.</p>

<tcl>
##############################################################################
Section {ANALYZE} analyze

Syntax {sql-statement} {
  ANALYZE
}
Syntax {sql-statement} {
  ANALYZE <database-name>
}
................................................................................
additional tables with the same name pattern except with the "1"
changed to a different digit.  The <b>sqlite_stat1</b> table cannot
be <a href="lang_droptable.html">DROP</a>ped,
but all the content can be <a href="lang_delete.html">DELETE</a>d which has the
same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
</tcl>

<p>The ATTACH DATABASE statement adds another database 
................................................................................
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>

<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
................................................................................
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the transaction.  An application can tell which
course of action SQLite took by using the
<a href="c3ref/get_autocommit.html">sqlite3_get_autocommit()</a>
C-language interface.</p>

<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.  If the 
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>

................................................................................
might cause automatic transaction rollback.  Future versions of
SQLite might change the error response.  In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section comment comment

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}
</tcl>

................................................................................
including inside expressions, and in the middle of other SQL statements.
C comments do not nest.  SQL comments inside a C comment will be ignored.
</p>


<tcl>
##############################################################################
Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}
................................................................................

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [<database-name> .] <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {
................................................................................
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] <trigger-name> [ BEFORE | AFTER ]
<database-event> ON [<database-name> .] <table-name>
<trigger-action>
}

................................................................................

<p>Triggers are removed using the 
<a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 
................................................................................
<a href="lang_createtrigger.html">TRIGGER</a> on the view to accomplish 
the same thing.  Views are removed 
with the <a href="lang_dropview.html">DROP VIEW</a> 
command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]
}
</tcl>

<p>A virtual table is an interface to an external storage or computation
................................................................................

<p>A virtual table is destroyed using the ordinary
<a href="lang_droptable.html">DROP TABLE</a> statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
................................................................................

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
................................................................................
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added
................................................................................
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the <a href=
................................................................................
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
<a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the <a href=
"lang_createview.html">CREATE VIEW</a> statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
</tcl>

<p>The EXPLAIN command modifier is a non-standard extension.  The
................................................................................
used to execute the command had the EXPLAIN keyword not been present.
For additional information about virtual machine instructions see
the <a href="arch.html">architecture description</a> or the documentation
on <a href="opcode.html">available opcodes</a> for the virtual machine.</p>

<tcl>
##############################################################################
Section expression expr

Syntax {expr} {
<expr> <binary-op> <expr> |
<expr> [NOT] <like-op> <expr> [ESCAPE <expr>] |
<unary-op> <expr> |
( <expr> ) |
<column-name> |
................................................................................
</pre></blockquote>

<p>The COLLATE operator can be thought of as a unary postfix
operator.  The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>


<p>The unary operator [Operator +] is a no-op.  It can be applied
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>

<p>Note that there are two variations of the equals and not equals
operators.  Equals can be either

<tcl>
hd_puts "[Operator =] or [Operator ==].
The non-equals operator can be either
................................................................................
<p>
A literal value can also be the token "NULL".
</p>

<p>
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
<a href="c3ref/bind_blob.html">sqlite3_bind</a> API.
Parameters can take several forms:
</p

<blockquote>
<table class="pdf_functions">
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
................................................................................
any text at all.  This syntax is the form of a variable name in the Tcl
programming language.</td>
</tr>
</table>
</blockquote>

<p>Parameters that are not assigned values using
<a href="c3ref/bind_blob.html">sqlite3_bind</a> are treated
as NULL.</p>

<a name="like"></a>
<p>The LIKE operator does a pattern matching comparison. The operand
to the right contains the pattern, the left hand operand contains the
string to match against the pattern. 

<tcl>hd_puts "A percent symbol [Operator %] in the pattern matches any
sequence of zero or more characters in the string.  An underscore
[Operator _] in the pattern matches any single character in the
................................................................................
operator is implemented by calling the user function <a href="#likeFunc">
like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it adds
a third parameter to the function call. If the functionality of LIKE can be
overridden by defining an alternative implementation of the
like() SQL function.</p>
</p>

<a name="glob"></a>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the user function <a href="#globFunc">
glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overriding
that function.</p>

<a name="regexp"></a>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<a name="match"></a>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises and exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<p>A column name can be any of the names defined in the CREATE TABLE
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the "row key") associated with every 
row of every table.
The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an UPDATE or INSERT statement.
"SELECT * ..." does not return the row key.</p>

<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the SELECT are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the SELECT expression refer to value in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the SELECT expression
does contain variables from the outer query, then the SELECT is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

................................................................................
their result across all rows of the result set.</p>

<a name="corefunctions"></a>
<b>Core Functions</b>

<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="c3ref/create_function.html">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

................................................................................
</tr>

<tr>
<td valign="top" align="right">
<a name="globFunc"></a>
glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The
<a href="c3ref/create_function.html">sqlite3_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#globFunc">GLOB</a> operator.</td>

</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>
................................................................................

<tr>
<td valign="top" align="right">
<a name="likeFunc"></a>
like(<i>X</i>,<i>Y</i>)<br>
like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">
This function is used to implement the "<b>X LIKE Y [ESCAPE Z]</b>"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. The 
<a href="c3ref/create_function.html">
sqlite_create_function()</a> interface can be used to override this
function and thereby change the operation of the <a
href= "#like">LIKE</a> operator. When doing this, it may be important
................................................................................
<p>This function will fail if the extension attempts to modify
or delete a SQL function or collating sequence.  The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement.  To load an extension that
changes or deletes functions or collating sequences, use the
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a>
C-language API.</p>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
................................................................................

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of VACUUM uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer
................................................................................

<tr>
<td valign="top" align="right">zeroblob(<i>N</i>)</td>
<td valign="top"><a name="zeroblob"></a>
Return a BLOB consisting of N bytes of 0x00.  SQLite
manages these zeroblobs very efficiently.  Zeroblobs can be used to
reserve space for a BLOB that is later written using 
<a href="c3ref/blob_open.html">incremental BLOB I/O</a>.</td>
</tr>

</table>

<b>Date And Time Functions</b>

<p>Date and time functions are documented in the 
................................................................................

<a name="aggregatefunctions"></a>
<b>Aggregate Functions</b>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
<a href="c3ref/create_function.html">sqlite3_create_function()</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
................................................................................
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>
</tr>
</table>

<tcl>
##############################################################################
Section INSERT insert

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement>
}
</tcl>

................................................................................
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>
................................................................................

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {
  REINDEX [<database-name> .] <table/index-name>
}
................................................................................

<p>The REINDEX command is used to delete and recreate indices from scratch.
This is useful when the definition of a collation sequence has changed.
</p>

<p>In the first form, all indices in all attached databases that use the
named collation sequence are recreated. In the second form, if 
<i>[database-name.]table/index-name</i> identifies a table, then all indices

associated with the table are rebuilt. If an index is identified, then only
this specific index is deleted and recreated.
</p>

<p>If no <i>database-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, then indices associated
with the collation sequence only are reconstructed. This ambiguity may be
dispelled by always specifying a <i>database-name</i> when reindexing a
specific table or index.

<tcl>
###############################################################################
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>
}
</tcl>

................................................................................
This alias is provided for
compatibility with MySQL.  See the 
<a href="lang_insert.html">INSERT</a> command documentation for additional
information.</p>  

<tcl>
###############################################################################
Section SELECT select

Syntax {sql-statement} {
SELECT [ALL | DISTINCT] <result> [FROM <table-list>]
[WHERE <expr>]
[GROUP BY <expr-list>]
[HAVING <expr>]
[<compound-op> <select>]*
................................................................................
left and right SELECTs.  EXCEPT takes the result of left SELECT after
removing the results of the right SELECT.  When three or more SELECTs
are connected into a compound, they group from left to right.</p>


<tcl>
##############################################################################
Section UPDATE update

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>
................................................................................
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM
}
</tcl>

<p>The VACUUM command is an SQLite extension modeled after a similar
................................................................................
   VIEW*
   VIRTUAL*
   WHEN
   WHERE
}]

hd_puts {<DIV class="pdf_section">}
Section {SQLite Keywords} keywords 
hd_puts {</DIV>}
</tcl>

<p>The SQL standard specifies a huge number of keywords which may not
be used as the names of tables, indices, columns, databases, user-defined
functions, collations, virtual table modules, or any other named object.
The list of keywords is so long that few people can remember them all.
................................................................................
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
        string literal.</td></tr>
<tr>	<td valign="top"><b>[keyword]</b></td><td></td>
	<td>A keyword enclosed in square brackets is always understood as
        an identifier.  This is not standard SQL.  This quoting mechanism
        is used by MS Access and SQL Server and is included in SQLite for
        compatibility.</td></tr>
</table>
</blockquote>
</p>

proc Keyword {name} {
  return "<font color=\"#2c2cf0\">$name</font>"
}
proc Example {text} {
  hd_puts "<blockquote><pre>$text</pre></blockquote>"
}

proc Section {name label keywords} {
  global DOC
  hd_close_main
  hd_open_main lang_$label.html
  hd_header "SQLite Query Language: $name" $DOC/pages/lang.in
  eval hd_keywords $keywords
  hd_puts {<a href="lang.html"><h2>SQL As Understood By SQLite</h2></a>}
  hd_puts "<h3>$name</h3>"
}

###############################################################################
Section {ALTER TABLE} altertable {{ALTER TABLE} {ALTER}}

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> <alteration>
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
................................................................................

<p>SQLite's version of the ALTER TABLE command allows the user to 
rename or add a new column to an existing table. It is not possible
to remove a column from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>. This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.
</p>

<p>The ADD &#91;COLUMN&#93; syntax
is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
<i>Column-def</i> may take any of the forms permissable in a [CREATE TABLE]
statement, with the following restrictions:
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
................................................................................

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database
is <a href="lang_vacuum.html">VACUUM</a>ed.</p>

<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

Syntax {sql-statement} {
  ANALYZE
}
Syntax {sql-statement} {
  ANALYZE <database-name>
}
................................................................................
additional tables with the same name pattern except with the "1"
changed to a different digit.  The <b>sqlite_stat1</b> table cannot
be <a href="lang_droptable.html">DROP</a>ped,
but all the content can be <a href="lang_delete.html">DELETE</a>d which has the
same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach ATTACH

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
</tcl>

<p>The ATTACH DATABASE statement adds another database 
................................................................................
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>

<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction {BEGIN COMMIT ROLLBACK}

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
................................................................................
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the transaction.  An application can tell which
course of action SQLite took by using the

[sqlite3_get_autocommit()] C-language interface.</p>

<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.  If the 
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>

................................................................................
might cause automatic transaction rollback.  Future versions of
SQLite might change the error response.  In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section comment comment {comment comments}

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}
</tcl>

................................................................................
including inside expressions, and in the middle of other SQL statements.
C comments do not nest.  SQL comments inside a C comment will be ignored.
</p>


<tcl>
##############################################################################
Section {CREATE INDEX} createindex {{CREATE INDEX}}

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}
................................................................................

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable} {{CREATE TABLE}}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [<database-name> .] <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {
................................................................................
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] <trigger-name> [ BEFORE | AFTER ]
<database-event> ON [<database-name> .] <table-name>
<trigger-action>
}

................................................................................

<p>Triggers are removed using the 
<a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW}}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 
................................................................................
<a href="lang_createtrigger.html">TRIGGER</a> on the view to accomplish 
the same thing.  Views are removed 
with the <a href="lang_dropview.html">DROP VIEW</a> 
command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL_TABLE}}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]
}
</tcl>

<p>A virtual table is an interface to an external storage or computation
................................................................................

<p>A virtual table is destroyed using the ordinary
<a href="lang_droptable.html">DROP TABLE</a> statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete {DELETE DELETEs}

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
................................................................................

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach DETACH

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
................................................................................
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added
................................................................................
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the <a href=
................................................................................
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
<a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview {{DROP VIEW}}

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the <a href=
"lang_createview.html">CREATE VIEW</a> statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain EXPLAIN

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
</tcl>

<p>The EXPLAIN command modifier is a non-standard extension.  The
................................................................................
used to execute the command had the EXPLAIN keyword not been present.
For additional information about virtual machine instructions see
the <a href="arch.html">architecture description</a> or the documentation
on <a href="opcode.html">available opcodes</a> for the virtual machine.</p>

<tcl>
##############################################################################
Section expression expr {expression {expression syntax}}

Syntax {expr} {
<expr> <binary-op> <expr> |
<expr> [NOT] <like-op> <expr> [ESCAPE <expr>] |
<unary-op> <expr> |
( <expr> ) |
<column-name> |
................................................................................
</pre></blockquote>

<p>The COLLATE operator can be thought of as a unary postfix
operator.  The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>

<tcl>hd_puts "
<p>The unary operator [Operator +] is a no-op.  It can be applied
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>"</tcl>

<p>Note that there are two variations of the equals and not equals
operators.  Equals can be either

<tcl>
hd_puts "[Operator =] or [Operator ==].
The non-equals operator can be either
................................................................................
<p>
A literal value can also be the token "NULL".
</p>

<p>
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
[sqlite3_bind_blob() | sqlite3_bind()] APIs.
Parameters can take several forms:
</p

<blockquote>
<table class="pdf_functions">
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
................................................................................
any text at all.  This syntax is the form of a variable name in the Tcl
programming language.</td>
</tr>
</table>
</blockquote>

<p>Parameters that are not assigned values using
[sqlite3_bind_blob() | sqlite3_bind()] are treated
as NULL.</p>

<tcl>hd_fragment like LIKE ESCAPE</tcl>
<p>The LIKE operator does a pattern matching comparison. The operand
to the right contains the pattern, the left hand operand contains the
string to match against the pattern. 

<tcl>hd_puts "A percent symbol [Operator %] in the pattern matches any
sequence of zero or more characters in the string.  An underscore
[Operator _] in the pattern matches any single character in the
................................................................................
operator is implemented by calling the user function <a href="#likeFunc">
like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it adds
a third parameter to the function call. If the functionality of LIKE can be
overridden by defining an alternative implementation of the
like() SQL function.</p>
</p>

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the user function <a href="#globFunc">
glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises and exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<p>A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the "row key") associated with every 
row of every table.
The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an [UPDATE] or [INSERT] statement.
"SELECT * ..." does not return the row key.</p>

<p>[SELECT] statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the [SELECT] expression refer to value in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

................................................................................
their result across all rows of the result set.</p>

<a name="corefunctions"></a>
<b>Core Functions</b>

<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>


<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

................................................................................
</tr>

<tr>
<td valign="top" align="right">
<a name="globFunc"></a>
glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The [sqlite3_create_function()]

interface can
be used to override this function and thereby change the operation

of the [GLOB] operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>
................................................................................

<tr>
<td valign="top" align="right">
<a name="likeFunc"></a>
like(<i>X</i>,<i>Y</i>)<br>
like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">
This function is used to implement the "<b>X LIKE Y &#91;ESCAPE Z&#93;</b>"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. The 
<a href="c3ref/create_function.html">
sqlite_create_function()</a> interface can be used to override this
function and thereby change the operation of the <a
href= "#like">LIKE</a> operator. When doing this, it may be important
................................................................................
<p>This function will fail if the extension attempts to modify
or delete a SQL function or collating sequence.  The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement.  To load an extension that
changes or deletes functions or collating sequences, use the

[sqlite3_load_extension()] C-language API.</p>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
................................................................................

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of [VACUUM] uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer
................................................................................

<tr>
<td valign="top" align="right">zeroblob(<i>N</i>)</td>
<td valign="top"><a name="zeroblob"></a>
Return a BLOB consisting of N bytes of 0x00.  SQLite
manages these zeroblobs very efficiently.  Zeroblobs can be used to
reserve space for a BLOB that is later written using 
[sqlite3_blob_open() | incremental BLOB I/O].</td>
</tr>

</table>

<b>Date And Time Functions</b>

<p>Date and time functions are documented in the 
................................................................................

<a name="aggregatefunctions"></a>
<b>Aggregate Functions</b>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
................................................................................
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>
</tr>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement>
}
</tcl>

................................................................................
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause}}

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>
................................................................................

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex REINDEX

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {
  REINDEX [<database-name> .] <table/index-name>
}
................................................................................

<p>The REINDEX command is used to delete and recreate indices from scratch.
This is useful when the definition of a collation sequence has changed.
</p>

<p>In the first form, all indices in all attached databases that use the
named collation sequence are recreated. In the second form, if 
<i>&#91;database-name.&#93;table/index-name</i> identifies a table,
then all indices
associated with the table are rebuilt. If an index is identified, then only
this specific index is deleted and recreated.
</p>

<p>If no <i>database-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, then indices associated
with the collation sequence only are reconstructed. This ambiguity may be
dispelled by always specifying a <i>database-name</i> when reindexing a
specific table or index.

<tcl>
###############################################################################
Section REPLACE 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>
}
</tcl>

................................................................................
This alias is provided for
compatibility with MySQL.  See the 
<a href="lang_insert.html">INSERT</a> command documentation for additional
information.</p>  

<tcl>
###############################################################################
Section SELECT select {SELECT query}

Syntax {sql-statement} {
SELECT [ALL | DISTINCT] <result> [FROM <table-list>]
[WHERE <expr>]
[GROUP BY <expr-list>]
[HAVING <expr>]
[<compound-op> <select>]*
................................................................................
left and right SELECTs.  EXCEPT takes the result of left SELECT after
removing the results of the right SELECT.  When three or more SELECTs
are connected into a compound, they group from left to right.</p>


<tcl>
##############################################################################
Section UPDATE update {UPDATE UPDATEs}

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>
................................................................................
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

Syntax {sql-statement} {
VACUUM
}
</tcl>

<p>The VACUUM command is an SQLite extension modeled after a similar
................................................................................
   VIEW*
   VIRTUAL*
   WHEN
   WHERE
}]

hd_puts {<DIV class="pdf_section">}
Section {SQLite Keywords} keywords {{SQL keyword} {SQL keywords}}
hd_puts {</DIV>}
</tcl>

<p>The SQL standard specifies a huge number of keywords which may not
be used as the names of tables, indices, columns, databases, user-defined
functions, collations, virtual table modules, or any other named object.
The list of keywords is so long that few people can remember them all.
................................................................................
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
        string literal.</td></tr>
<tr>	<td valign="top"><b>&#91;keyword&#93;</b></td><td></td>
	<td>A keyword enclosed in square brackets is always understood as
        an identifier.  This is not standard SQL.  This quoting mechanism
        is used by MS Access and SQL Server and is included in SQLite for
        compatibility.</td></tr>
</table>
</blockquote>
</p>

multiple copies of SQLite.  SQLite is used on servers, but
it is also used on desktop PC, and in cellphones, and PDAs,
and MP3-players, and set-top boxes.</p>

<h3>Estimates</h3>

<p>At the end of 2006, there were 100 million websites on the internet.
<a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">[1]</a>
Let us use that number as a proxy for the number of deployed
SQL database engines other than SQLite.  Not every website
runs an SQL database engine.  Larger websites run multiple
database engines.  But the vast majority of smaller websites
(the long tail) share
a database engine with several other websites,
if they use database engine at all
................................................................................
for the number of non-SQLite SQL database engines currently
deployed.</p>

<p>Now let's consider where SQLite is used:</p>

<ul>
<li>125 million copies of Mozilla Firefox 
<a href="http://arstechnica.com/news.ars/post/20071202-mozilla-coo-over-125-million-people-use-firefox.html">[2]</a></li>
<li>20 million Mac computers, each of which contains multiple
copies of SQLite</li>
<li>20 million websites run PHP which has SQLite built in.  
<a href="http://www.php.net/usage.php">[3]</a>  We have no
way of estimating what fraction of those sites actively use
SQLite, but we think it is a significant fraction.<li>
<li>300 million downloads of the <a href="http://www.skype.com/">Skype</a>
client software and 100 million registered users
<a href="http://skypejournal.com/blog/archives/2006/04/how_many_users_does_skype_have_today.php">[4]</a>.  All recent versions of the Skype
client use SQLite internally.</li>
<li>20 million Symbian smartphones shipped in Q3 2007
<a href="http://www.symbian.com/news/pr/2007/pr20079552.html">[5]</a>
Newer versions of the SymbianOS have SQLite built in.  It is unclear
exactly how many Symbian phones actually contain SQLite, so we will
use a single quarter's sales as a lower bound.</li>
<li>Millions and millions of copies of
<a href="http://www.mcafee.com/">McAfee</a> anti-virus software all
use SQLite internally.</li>
<li>Millions of iPhones use SQLite</li>

multiple copies of SQLite.  SQLite is used on servers, but
it is also used on desktop PC, and in cellphones, and PDAs,
and MP3-players, and set-top boxes.</p>

<h3>Estimates</h3>

<p>At the end of 2006, there were 100 million websites on the internet.
<a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">&#91;1]</a>
Let us use that number as a proxy for the number of deployed
SQL database engines other than SQLite.  Not every website
runs an SQL database engine.  Larger websites run multiple
database engines.  But the vast majority of smaller websites
(the long tail) share
a database engine with several other websites,
if they use database engine at all
................................................................................
for the number of non-SQLite SQL database engines currently
deployed.</p>

<p>Now let's consider where SQLite is used:</p>

<ul>
<li>125 million copies of Mozilla Firefox 
<a href="http://arstechnica.com/news.ars/post/20071202-mozilla-coo-over-125-million-people-use-firefox.html">&#91;2]</a></li>
<li>20 million Mac computers, each of which contains multiple
copies of SQLite</li>
<li>20 million websites run PHP which has SQLite built in.  
<a href="http://www.php.net/usage.php">&#91;3]</a>  We have no
way of estimating what fraction of those sites actively use
SQLite, but we think it is a significant fraction.<li>
<li>300 million downloads of the <a href="http://www.skype.com/">Skype</a>
client software and 100 million registered users
<a href="http://skypejournal.com/blog/archives/2006/04/how_many_users_does_skype_have_today.php">&#91;4]</a>.  All recent versions of the Skype
client use SQLite internally.</li>
<li>20 million Symbian smartphones shipped in Q3 2007
<a href="http://www.symbian.com/news/pr/2007/pr20079552.html">&#91;5]</a>
Newer versions of the SymbianOS have SQLite built in.  It is unclear
exactly how many Symbian phones actually contain SQLite, so we will
use a single quarter's sales as a lower bound.</li>
<li>Millions and millions of copies of
<a href="http://www.mcafee.com/">McAfee</a> anti-virus software all
use SQLite internally.</li>
<li>Millions of iPhones use SQLite</li>

<title>Pragma statements supported by SQLite</title>

<tcl>
proc Section {name {label {}}} {
  hd_puts "\n<hr />"
  if {$label!=""} {
    hd_puts "<a name=\"$label\"></a>"



  }
  hd_puts "<h1>$name</h1>\n"
}





</tcl>

<p>The <a href="#syntax">PRAGMA command</a> is a special command used to 

modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The PRAGMA command is issued using the same
interface as other SQLite commands (e.g. SELECT, INSERT) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. Use with caution!
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language sqlite3_prepare(), 
    sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper 
    interface), the pragma may be applied to the library during the 
    sqlite3_prepare() call.
<li>The pragma command is unlikely to be compatible with any other SQL
    engine.
</ul>

<p>The available pragmas fall into four basic categories:</p>
<ul>
<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
................................................................................
<li>Pragmas used to <a href="#version">query or modify the databases two 
    version values</a>, the schema-version and the user-version.
<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
    database files are not corrupted.
</ul>

<tcl>
Section {PRAGMA command syntax} syntax

Syntax {sql-statement} {
PRAGMA <name> [= <value>] |
PRAGMA <function>(<arg>)
}
</tcl>

................................................................................
are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
insensitive, and do not require quotes.  An unrecognized string will be 
treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
is returned it is as an integer.</p>

<tcl>Section {Pragmas to modify library operation} modify</tcl>


<ul>
<a name="pragma_auto_vacuum"></a>

<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
            <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <p>Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, (that is to say when auto_vacuum is 0 or "none")
    when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are added to a "freelist" are reused for subsequent inserts.  The
    database file does not shrink.
    In this mode the <a href="lang_vacuum.html">VACUUM</a>
    command can be used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is 1 (full), the freelist pages are
    moved to the end of the file and the file is truncated to remove
    the freelist pages at every commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    <a href="lang_vacuum.html">VACUUM</a> command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 (incremental) then the additional
    information needed to do autovacuuming is stored in the database file
    but autovacuuming does not occur automatically at each commit as it
    does with auto_vacuum==full.  In incremental mode, the separate
    <a href="#pragma_incremental_vacuum">incremental_vacuum</a> pragma must
    be invoked to cause the vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at will.  However, the connection cannot be changed
    in and out of the "none" mode after any table has been created in the
    database.
    </p></li>

<a name="pragma_cache_size"></a>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
    The default cache size is 2000.  If you are doing UPDATEs or DELETEs
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</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 <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
    pragma to check the cache size permanently.</p></li>

<a name="pragma_case_sensitive_like"></a>
<li><p><b>PRAGMA case_sensitive_like;
       <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p>
    <p>The default behavior of the LIKE operator is to ignore case
    for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is
    true.  The case_sensitive_like pragma can be turned on to 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>

<a name="pragma_count_changes"></a>
<li><p><b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b><i>0 | 1</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>

<a name="pragma_default_cache_size"></a>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the
    <a href="#pragma_cache_size"><b>cache_size</b></a> 
    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    every time you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;</b></p>
    <p>This pragma was available in version 2.8 but was removed in version
    3.0.  It is a dangerous pragma whose use is discouraged.  To help
    dissuide users of version 2.8 from employing this pragma, the documentation
    will not tell you what it does.</p></li>


<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</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>

<a name="pragma_encoding"></a>
<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>In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
................................................................................
    it is created by this session. The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  If the second
    and subsequent forms are used after the database file has already
    been created, they have no effect and are silently ignored.</p>

    <p>Once an encoding has been set for a database, it cannot be changed.</p>

    <p>Databases created by the ATTACH command always use the same encoding
    as the main database.</p>
</li>

<a name="pragma_full_column_names"></a>
<li><p><b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>0 | 1</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>

<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 version 3.4.0 (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 VACUUM 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_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
................................................................................
    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>
</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
................................................................................
    <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 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="c3ref/enable_shared_cache.html">
    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
................................................................................
    </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
................................................................................
        <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.

................................................................................
 
    </li>
</ul>

<tcl>Section {Pragmas to query the database schema} schema</tcl>

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

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

<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>
  
................................................................................
       applications for any purpose.</p>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

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

<title>Pragma statements supported by SQLite</title>

<tcl>
proc Section {name {label {}} {keywords {}}} {
  hd_puts "\n<hr />"
  if {$label!=""} {
    hd_fragment $label
    if {$keywords!=""} {
      eval hd_keywords $keywords
    }
  }
  hd_puts "<h1>$name</h1>\n"
}
proc Subsection {args} {
  set f [lindex $args 0]
  hd_fragment pragma_$f
  eval hd_keywords $args
}
</tcl>


<p>The [PRAGMA] statement is a special SQL statement used to 
modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The [PRAGMA] statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. Use with caution!
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may be applied to the library during the 
    [sqlite3_prepare()] call.
<li>The pragma command is unlikely to be compatible with any other SQL
    engine.
</ul>

<p>The available pragmas fall into four basic categories:</p>
<ul>
<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
................................................................................
<li>Pragmas used to <a href="#version">query or modify the databases two 
    version values</a>, the schema-version and the user-version.
<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
    database files are not corrupted.
</ul>

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

Syntax {sql-statement} {
PRAGMA <name> [= <value>] |
PRAGMA <function>(<arg>)
}
</tcl>

................................................................................
are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
insensitive, and do not require quotes.  An unrecognized string will be 
treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
is returned it is as an integer.</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>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <p>Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, (that is to say when auto_vacuum is 0 or "none")
    when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are added to a "freelist" are reused for subsequent inserts.  The
    database file does not shrink.
    In this mode the [VACUUM]
    command can be used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is 1 (full), the freelist pages are
    moved to the end of the file and the file is truncated to remove
    the freelist pages at every commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    [VACUUM] command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 (incremental) then the additional
    information needed to do autovacuuming is stored in the database file
    but autovacuuming does not occur automatically at each commit as it
    does with auto_vacuum==full.  In incremental mode, the separate
    [incremental_vacuum] pragma must
    be invoked to cause the vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at will.  However, the connection cannot be changed
    in and out of the "none" mode after any table has been created in the
    database.
    </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 maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
    The default cache size is 2000.  If you are doing [UPDATEs] or [DELETEs]
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</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;
       <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p>
    <p>The default behavior of the [LIKE] operator is to ignore case
    for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is
    true.  The case_sensitive_like pragma can be turned on to 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><i>0 | 1</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>

<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>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the [cache_size]

    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    every time you reopen the database.</p></li>









<tcl>Subsection empty_result_callbacks</tcl>
<li><p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</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>

<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>In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
................................................................................
    it is created by this session. The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  If the second
    and subsequent forms are used after the database file has already
    been created, they have no effect and are silently ignored.</p>

    <p>Once an encoding has been set for a database, it cannot be changed.</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>0 | 1</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>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>

<tcl>Subsection incremental_vacuum</tcl>
<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 [version 3.4.0] (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
    [VACUUM] command does.  And
    incremental vacuum may be promoted from a pragma to a separate
    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 legacy_file_format</tcl>
<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
................................................................................
    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>
</li>

<tcl>Subsection locking_mode</tcl>
<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
................................................................................
    <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 [ATTACH] 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>

<tcl>Subsection page_size</tcl>
<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>

<tcl>Subsection max_page_count</tcl>
<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>

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

    [sqlite3_enable_shared_cache()] API and is only available between
    connections running the same thread.  Cache sharing is off by default.
    </p>
</li>

<tcl>Subsection short_column_names</tcl>
<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>

<tcl>Subsection synchronous</tcl>
<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
................................................................................
    </p>
    <p>In SQLite version 2, the default value is NORMAL. For version 3, the
    default was changed to FULL.
    </p>
</li>


<tcl>Subsection temp_store</tcl>
<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
................................................................................
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </table>
    </blockquote>
    </li>
    <br>

<tcl>Subsection temp_store_directory</tcl>
<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.

................................................................................
 
    </li>
</ul>

<tcl>Section {Pragmas to query the database schema} schema</tcl>

<ul>
<tcl>Subsection database_list</tcl>
<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>

<tcl>Subsection foreign_key_list</tcl>
<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 &#91;database&#93;.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 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; Subsection user_version</tcl>

<li><p><b>PRAGMA &#91;database.&#93;schema_version; 
       <br>PRAGMA &#91;database.&#93;schema_version = </b><i>integer </i><b>;

       <br>PRAGMA &#91;database.&#93;user_version;
       <br>PRAGMA &#91;database.&#93;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>
  
................................................................................
       applications for any purpose.</p>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

<ul>
<tcl>Subsection integrity_check</tcl>
<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>

<tcl>Subsection parser_trace</tcl>
<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>

<tcl>Subsection vdbe_trace</tcl>
<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>

<tcl>Subsection vdbe_listing</tcl>
<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>

proc hd_resolve_one {x} {
  set x2 [split $x |]
  set kw [string trim [lindex $x2 0]]
  if {[llength $x2]==1} {
    set content $kw
    regsub -all {[^a-zA-Z0-9_.# -]} $content {} kw
  } else {

    set content [string trim [lindex $x2 1]]
  }
  global hd llink glink
  if {$hd(enable-main)} {
    set fn $hd(fn-main)
    if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} {
      set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid
................................................................................
    set lurl "#$hd(fragment)"
  }
  set fn $hd(fn-main)
  if {[info exists hd(aux)]} {
    set gurl $hd(fn-aux)
  } else {
    set gurl {}



  }
  foreach a $args {
    if {[info exists glink($a)]} {
      puts stderr "WARNING: duplicate keyword \"$a\""
    }
    if {$gurl==""} {
      set glink($a) $lurl
................................................................................
# Start a new fragment in the main file.  Give the new fragment the
# indicated name.  Any keywords defined after this point will refer
# to the fragment, not to the beginning of the file.
#
# Only the main file may have fragments.  Auxiliary files are assumed
# to be small enough that fragments are not helpful.
#
proc hd_fragment {name} {
  global hd
  set hd(fragment) $name
  puts $hd(main) "<a name=\"$name\"></a>"

}

# Write raw output to both the main file and the auxiliary.  Only write
# to files that are enabled.
#
proc hd_puts {text} {
  global hd
................................................................................
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; hd_resolve \173" in
  eval "hd_resolve \173$in\175"
  cd $::HOMEDIR
  hd_close_main
}

proc hd_resolve_one {x} {
  set x2 [split $x |]
  set kw [string trim [lindex $x2 0]]
  if {[llength $x2]==1} {
    set content $kw
    regsub -all {[^a-zA-Z0-9_.# -]} $content {} kw
  } else {
    regsub -all {[^a-zA-Z0-9_.# -]} $kw {} kw
    set content [string trim [lindex $x2 1]]
  }
  global hd llink glink
  if {$hd(enable-main)} {
    set fn $hd(fn-main)
    if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} {
      set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid
................................................................................
    set lurl "#$hd(fragment)"
  }
  set fn $hd(fn-main)
  if {[info exists hd(aux)]} {
    set gurl $hd(fn-aux)
  } else {
    set gurl {}
    if {$hd(fragment)!=""} {
      set lurl $hd(fn-main)#$hd(fragment)
    }
  }
  foreach a $args {
    if {[info exists glink($a)]} {
      puts stderr "WARNING: duplicate keyword \"$a\""
    }
    if {$gurl==""} {
      set glink($a) $lurl
................................................................................
# Start a new fragment in the main file.  Give the new fragment the
# indicated name.  Any keywords defined after this point will refer
# to the fragment, not to the beginning of the file.
#
# Only the main file may have fragments.  Auxiliary files are assumed
# to be small enough that fragments are not helpful.
#
proc hd_fragment {name args} {
  global hd
  set hd(fragment) $name
  puts $hd(main) "<a name=\"$name\"></a>"
  eval hd_keywords $args
}

# Write raw output to both the main file and the auxiliary.  Only write
# to files that are enabled.
#
proc hd_puts {text} {
  global hd
................................................................................
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; hd_resolve \173" in
  eval "hd_resolve \173$in\175"
  cd $::HOMEDIR
  hd_close_main
}

# Generate a document showing the hyperlink keywords and their
# targets.
#
hd_open_main doc_keyword_crossref.html
hd_header {Hyperlink Crossreference} $DOC/wrap.tcl
hd_puts "<ul>"
foreach x [lsort [array names glink]] {
  set y $glink($x)
  hd_puts "<li>$x - <a href=\"$y\">$y</a></li>"
  lappend revglink($y) $x
}
hd_puts "</ul><hr><ul>"
foreach y [lsort [array names revglink]] {
  hd_puts "<li><a href=\"$y\">$y</a> - [lsort $revglink($y)]</li>"
}
hd_puts "</ul>"
hd_close_main