Documentation Source Text

     line RELEASE {optx SAVEPOINT} /savepoint-name
  }
  create-index-stmt {
    stack
       {line CREATE {opt UNIQUE} INDEX {opt IF NOT EXISTS}}
       {line {optx /database-name .} /index-name
             ON /table-name ( {loop indexed-column ,} )}
       {line {optx WHERE /expr}}
  }
  indexed-column {
      line /column-name {opt COLLATE /collation-name} {or nil ASC DESC} 
  }
  create-table-stmt {
    stack
       {line CREATE {or {} TEMP TEMPORARY} TABLE {opt IF NOT EXISTS}}
................................................................................
  delete-stmt-limited {
    stack
        {line {opt with-clause} DELETE FROM qualified-table-name}
        {optx WHERE expr}
        {optx
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT /expr {optx {or OFFSET ,} /expr}}
            }
        }
  }
  detach-stmt {
    line DETACH {optx DATABASE} /database-name
  }
  drop-index-stmt {
................................................................................
          }
  }
  select-stmt {
    stack
       {line {opt with-clause}
             {loop simple-query-stmt {nil compound-operator nil}}}
       {optx ORDER BY {loop ordering-term ,}}
       {optx LIMIT /expr {optx {or OFFSET ,} /expr}}
  }
  simple-query-stmt {
    stack
       {line SELECT {or nil DISTINCT ALL} {loop result-column ,}}
       {optx FROM join-source}
       {optx WHERE expr}
       {optx GROUP BY {loop expr ,} {optx HAVING expr}}
................................................................................
                                     {line OR FAIL}
                                     {line OR IGNORE}}
              qualified-table-name}
        {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}}
        {optx
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT /expr {optx {or OFFSET ,} /expr}}
            }
        }
  }
  qualified-table-name {
     line {optx /database-name .} /table-name
          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }

     line RELEASE {optx SAVEPOINT} /savepoint-name
  }
  create-index-stmt {
    stack
       {line CREATE {opt UNIQUE} INDEX {opt IF NOT EXISTS}}
       {line {optx /database-name .} /index-name
             ON /table-name ( {loop indexed-column ,} )}
       {line {optx WHERE expr}}
  }
  indexed-column {
      line /column-name {opt COLLATE /collation-name} {or nil ASC DESC} 
  }
  create-table-stmt {
    stack
       {line CREATE {or {} TEMP TEMPORARY} TABLE {opt IF NOT EXISTS}}
................................................................................
  delete-stmt-limited {
    stack
        {line {opt with-clause} DELETE FROM qualified-table-name}
        {optx WHERE expr}
        {optx
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT expr {optx {or OFFSET ,} expr}}
            }
        }
  }
  detach-stmt {
    line DETACH {optx DATABASE} /database-name
  }
  drop-index-stmt {
................................................................................
          }
  }
  select-stmt {
    stack
       {line {opt with-clause}
             {loop simple-query-stmt {nil compound-operator nil}}}
       {optx ORDER BY {loop ordering-term ,}}
       {optx LIMIT expr {optx {or OFFSET ,} expr}}
  }
  simple-query-stmt {
    stack
       {line SELECT {or nil DISTINCT ALL} {loop result-column ,}}
       {optx FROM join-source}
       {optx WHERE expr}
       {optx GROUP BY {loop expr ,} {optx HAVING expr}}
................................................................................
                                     {line OR FAIL}
                                     {line OR IGNORE}}
              qualified-table-name}
        {line SET {loop {line /column-name = expr} ,} {optx WHERE expr}}
        {optx
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT expr {optx {or OFFSET ,} expr}}
            }
        }
  }
  qualified-table-name {
     line {optx /database-name .} /table-name
          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }

set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(compound-operator) {{} select-stmt}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {indexed-column sql-stmt}
set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint create-trigger-stmt delete-stmt delete-stmt-limited insert-stmt join-constraint ordering-term result-column simple-query-stmt table-constraint update-stmt update-stmt-limited}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-constraint) {expr join-source}
set syntax_linkage(join-op) {{} join-source}
set syntax_linkage(join-source) {{join-constraint join-op simple-join-source} {simple-join-source simple-query-stmt}}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
................................................................................
set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}}
set syntax_linkage(raise-function) {{} expr}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr simple-query-stmt}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-stmt) {{compound-operator ordering-term simple-query-stmt with-clause} {create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt simple-join-source sql-stmt}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-join-source) {{join-source select-stmt} join-source}
set syntax_linkage(simple-query-stmt) {{expr join-source result-column} select-stmt}
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{} {delete-stmt delete-stmt-limited insert-stmt select-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt pragma-stmt pragma-value reindex-stmt select-stmt simple-query-stmt result-column join-source simple-join-source join-op join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}

set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(compound-operator) {{} select-stmt}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt}
set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited insert-stmt join-constraint ordering-term result-column select-stmt simple-query-stmt table-constraint update-stmt update-stmt-limited}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-constraint) {expr join-source}
set syntax_linkage(join-op) {{} join-source}
set syntax_linkage(join-source) {{join-constraint join-op simple-join-source} {simple-join-source simple-query-stmt}}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
................................................................................
set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}}
set syntax_linkage(raise-function) {{} expr}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr simple-query-stmt}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-stmt) {{compound-operator expr ordering-term simple-query-stmt with-clause} {create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt simple-join-source sql-stmt}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-join-source) {{join-source select-stmt} join-source}
set syntax_linkage(simple-query-stmt) {{expr join-source result-column} select-stmt}
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{} {delete-stmt delete-stmt-limited insert-stmt select-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt pragma-stmt pragma-value reindex-stmt select-stmt simple-query-stmt result-column join-source simple-join-source join-op join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}

  hd_puts {<p><a href="lang.html">[Top]</a></p>}
  hd_puts "<h2>$name</h2>"
}

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

BubbleDiagram alter-table-stmt 1
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table. It is not possible
to rename a column, remove a column, or add or remove constraints 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>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier.</p>

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

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in [internal tables] of the database where the query optimizer can
access the information and use it to help make better query planning choices.
^If no arguments are given, all attached databases are
................................................................................

<p> 

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current [database connection]. 
^The filename for the database to be attached is the value of
the expression that occurs before the AS keyword.
^The filename of the database follows the same semantics as the
................................................................................
[SQLITE_LIMIT_ATTACHED], to the number of databases that can be
simultaneously attached to a single database connection.</p>

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

BubbleDiagram begin-stmt
BubbleDiagram commit-stmt
BubbleDiagram rollback-stmt
</tcl>

<p>
^No changes can be made to the database except within a transaction.
^Any command that changes the database (basically, any SQL command
other than [SELECT]) will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
................................................................................
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section {SAVEPOINT} savepoint {SAVEPOINT RELEASE}

BubbleDiagram savepoint-stmt
BubbleDiagram release-stmt
BubbleDiagram rollback-stmt
</tcl>

<p> ^SAVEPOINTs are a method of creating transactions, similar to
[BEGIN] and [COMMIT], except that the SAVEPOINT and RELEASE commands
are named and may be nested.</p>

<p> ^The SAVEPOINT command starts a new transaction with a name.
................................................................................
match any SAVEPOINT on the stack, then the ROLLBACK command fails with an
error and leaves the state of the database unchanged.</p>

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

BubbleDiagram comment-syntax
</tcl>

<p>^Comments are not SQL commands, but can occur within the text of
SQL queries passed to [sqlite3_prepare_v2()] and related interfaces.
^Comments are treated as whitespace by the parser.
^Comments can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
................................................................................
</p>


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

BubbleDiagram create-index-stmt 1
BubbleDiagram indexed-column
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
If the optional WHERE clause is included, then the index is a "[partial index]".
................................................................................
<p>^Indexes are removed with the [DROP INDEX] command.</p>


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

BubbleDiagram create-table-stmt 1
BubbleDiagram column-def
BubbleDiagram type-name
BubbleDiagram column-constraint
BubbleDiagram table-constraint
BubbleDiagram foreign-key-clause
</tcl>

<p>The "CREATE TABLE" command is used to create a new table in an SQLite 
database. A CREATE TABLE command specifies the following attributes of the
new table:

<ul>
................................................................................
<p>^(The [parent key] of a [foreign key constraint] is not allowed to
use the rowid.  The parent key must used named columns only.)^</p>

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

BubbleDiagram create-trigger-stmt 1
</tcl>

<p>^The CREATE TRIGGER statement is used to add triggers to the 
database schema. ^Triggers are database operations 
that are automatically performed when a specified database event
occurs.  </p>

................................................................................
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.</p>

<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view *views}

BubbleDiagram create-view-stmt 1
</tcl>

<p>^The CREATE VIEW command assigns a name to a pre-packaged 
[SELECT] statement. 
^Once the view is created, it can be used in the FROM clause
of another [SELECT] in place of a table name.
</p>
................................................................................
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

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

BubbleDiagram create-virtual-table-stmt 1
</tcl>

<p>A [virtual table] is an interface to an external storage or computation
engine that appears to be a table but does not actually store information
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
................................................................................
[DROP TABLE] statement.  There is no
DROP VIRTUAL TABLE statement.</p>

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

BubbleDiagram delete-stmt 1
BubbleDiagram qualified-table-name
</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
................................................................................
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

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

BubbleDiagram detach-stmt 1
</tcl>

<p>^This statement detaches an additional database connection previously 
attached using the [ATTACH] statement.  
^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
................................................................................
<p>^This statement will fail if SQLite is in the middle of a transaction.</p>


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

BubbleDiagram drop-index-stmt 1
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate [CREATE INDEX] command.</p>

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

BubbleDiagram drop-table-stmt 1
</tcl>

<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
................................................................................
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}

BubbleDiagram drop-trigger-stmt 1
</tcl>

<p>^The DROP TRIGGER statement removes a trigger created by the 
[CREATE TRIGGER] statement. ^Once removed, the trigger definition is no
longer present in the sqlite_master (or sqlite_temp_master) table and is
not fired by any subsequent INSERT, UPDATE or DELETE statements.

................................................................................
<p>^Note that triggers are automatically dropped when the associated table is
dropped.

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

BubbleDiagram drop-view-stmt 1
</tcl>

<p>^The DROP VIEW statement removes a view created by the [CREATE VIEW] 
  statement. ^The view definition is removed from the database schema, but 
  no actual data in the underlying base tables is modified.

<p>^(The view to drop is identified by the view-name and optional 
................................................................................
The EXPLAIN QUERY PLAN command is described in 
[explain query plan|more detail here].

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

BubbleDiagram expr 1
BubbleDiagram literal-value
BubbleDiagram signed-number
BubbleDiagram raise-function
</tcl>

<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does
not talk about a standalone command but about "expressions" which are 
subcomponents of most other commands.</p>

................................................................................
</tcl>
</table>

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

BubbleDiagram insert-stmt 1
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If no column-list is specified then the number
................................................................................
triggers.
<p>

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

BubbleDiagram conflict-clause
</tcl>

<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

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

BubbleDiagram reindex-stmt 1
</tcl>

<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>^If the REINDEX keyword is not followed by a collation-sequence or database 
................................................................................
This alias is provided for compatibility other SQL database engines.  See the 
[INSERT] command documentation for additional information.</p>  

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

BubbleDiagram select-stmt 1
BubbleDiagram simple-query-stmt
BubbleDiagram result-column
BubbleDiagram join-source
BubbleDiagram simple-join-source
BubbleDiagram join-op
BubbleDiagram join-constraint
BubbleDiagram ordering-term
BubbleDiagram compound-operator
</tcl>


<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  

................................................................................
the two expressions is the OFFSET and the first the LIMIT. This is intentional
- it maximizes compatibility with other SQL database systems.

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

BubbleDiagram update-stmt 1
BubbleDiagram qualified-table-name
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
................................................................................
rows fall within the LIMIT. The order in which rows are modified is arbitrary
and is not influenced by the ORDER BY clause.)^

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

BubbleDiagram vacuum-stmt 1
</tcl>

<p>
  The VACUUM command rebuilds the entire database. There are several 
  reasons an application might do this:

<ul>

  hd_puts {<p><a href="lang.html">[Top]</a></p>}
  hd_puts "<h2>$name</h2>"
}

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

RecursiveBubbleDiagram alter-table-stmt
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing 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>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier.</p>

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

RecursiveBubbleDiagram analyze-stmt
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in [internal tables] of the database where the query optimizer can
access the information and use it to help make better query planning choices.
^If no arguments are given, all attached databases are
................................................................................

<p> 

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

RecursiveBubbleDiagram attach-stmt
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current [database connection]. 
^The filename for the database to be attached is the value of
the expression that occurs before the AS keyword.
^The filename of the database follows the same semantics as the
................................................................................
[SQLITE_LIMIT_ATTACHED], to the number of databases that can be
simultaneously attached to a single database connection.</p>

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

RecursiveBubbleDiagram begin-stmt commit-stmt rollback-stmt


</tcl>

<p>
^No changes can be made to the database except within a transaction.
^Any command that changes the database (basically, any SQL command
other than [SELECT]) will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
................................................................................
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section {SAVEPOINT} savepoint {SAVEPOINT RELEASE}

RecursiveBubbleDiagram savepoint-stmt release-stmt rollback-stmt


</tcl>

<p> ^SAVEPOINTs are a method of creating transactions, similar to
[BEGIN] and [COMMIT], except that the SAVEPOINT and RELEASE commands
are named and may be nested.</p>

<p> ^The SAVEPOINT command starts a new transaction with a name.
................................................................................
match any SAVEPOINT on the stack, then the ROLLBACK command fails with an
error and leaves the state of the database unchanged.</p>

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

RecursiveBubbleDiagram comment-syntax
</tcl>

<p>^Comments are not SQL commands, but can occur within the text of
SQL queries passed to [sqlite3_prepare_v2()] and related interfaces.
^Comments are treated as whitespace by the parser.
^Comments can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
................................................................................
</p>


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

RecursiveBubbleDiagram create-index-stmt

</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
If the optional WHERE clause is included, then the index is a "[partial index]".
................................................................................
<p>^Indexes are removed with the [DROP INDEX] command.</p>


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

RecursiveBubbleDiagram create-table-stmt





</tcl>

<p>The "CREATE TABLE" command is used to create a new table in an SQLite 
database. A CREATE TABLE command specifies the following attributes of the
new table:

<ul>
................................................................................
<p>^(The [parent key] of a [foreign key constraint] is not allowed to
use the rowid.  The parent key must used named columns only.)^</p>

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

RecursiveBubbleDiagram create-trigger-stmt
</tcl>

<p>^The CREATE TRIGGER statement is used to add triggers to the 
database schema. ^Triggers are database operations 
that are automatically performed when a specified database event
occurs.  </p>

................................................................................
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.</p>

<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view *views}

RecursiveBubbleDiagram create-view-stmt
</tcl>

<p>^The CREATE VIEW command assigns a name to a pre-packaged 
[SELECT] statement. 
^Once the view is created, it can be used in the FROM clause
of another [SELECT] in place of a table name.
</p>
................................................................................
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

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

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>

<p>A [virtual table] is an interface to an external storage or computation
engine that appears to be a table but does not actually store information
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
................................................................................
[DROP TABLE] statement.  There is no
DROP VIRTUAL TABLE statement.</p>

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

RecursiveBubbleDiagram delete-stmt

</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
................................................................................
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

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

RecursiveBubbleDiagram detach-stmt
</tcl>

<p>^This statement detaches an additional database connection previously 
attached using the [ATTACH] statement.  
^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
................................................................................
<p>^This statement will fail if SQLite is in the middle of a transaction.</p>


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

RecursiveBubbleDiagram drop-index-stmt
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate [CREATE INDEX] command.</p>

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

RecursiveBubbleDiagram drop-table-stmt
</tcl>

<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
................................................................................
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}

RecursiveBubbleDiagram drop-trigger-stmt
</tcl>

<p>^The DROP TRIGGER statement removes a trigger created by the 
[CREATE TRIGGER] statement. ^Once removed, the trigger definition is no
longer present in the sqlite_master (or sqlite_temp_master) table and is
not fired by any subsequent INSERT, UPDATE or DELETE statements.

................................................................................
<p>^Note that triggers are automatically dropped when the associated table is
dropped.

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

RecursiveBubbleDiagram drop-view-stmt
</tcl>

<p>^The DROP VIEW statement removes a view created by the [CREATE VIEW] 
  statement. ^The view definition is removed from the database schema, but 
  no actual data in the underlying base tables is modified.

<p>^(The view to drop is identified by the view-name and optional 
................................................................................
The EXPLAIN QUERY PLAN command is described in 
[explain query plan|more detail here].

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

RecursiveBubbleDiagram expr 1



</tcl>

<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does
not talk about a standalone command but about "expressions" which are 
subcomponents of most other commands.</p>

................................................................................
</tcl>
</table>

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

RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If no column-list is specified then the number
................................................................................
triggers.
<p>

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

RecursiveBubbleDiagram conflict-clause
</tcl>

<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

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

RecursiveBubbleDiagram reindex-stmt
</tcl>

<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>^If the REINDEX keyword is not followed by a collation-sequence or database 
................................................................................
This alias is provided for compatibility other SQL database engines.  See the 
[INSERT] command documentation for additional information.</p>  

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

RecursiveBubbleDiagram select-stmt simple-query-stmt








</tcl>


<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  

................................................................................
the two expressions is the OFFSET and the first the LIMIT. This is intentional
- it maximizes compatibility with other SQL database systems.

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

RecursiveBubbleDiagram update-stmt

</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
................................................................................
rows fall within the LIMIT. The order in which rows are modified is arbitrary
and is not influenced by the ORDER BY clause.)^

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

RecursiveBubbleDiagram vacuum-stmt
</tcl>

<p>
  The VACUUM command rebuilds the entire database. There are several 
  reasons an application might do this:

<ul>

<h2>2.0 Creating Partial Indexes</h2>

<p>
^Create a partial index by adding a WHERE clause to the end of an 
ordinary [CREATE INDEX] statement.
</p>

<tcl>BubbleDiagram create-index-stmt</tcl>

<p>
Any index that includes the WHERE clause at the end is considered to be
a partial index.  Indexes that omit the WHERE clause (or indexes that
are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
statements) are ordinary full indexes.
</p>

<h2>2.0 Creating Partial Indexes</h2>

<p>
^Create a partial index by adding a WHERE clause to the end of an 
ordinary [CREATE INDEX] statement.
</p>

<tcl>RecursiveBubbleDiagram create-index-stmt</tcl>

<p>
Any index that includes the WHERE clause at the end is considered to be
a partial index.  Indexes that omit the WHERE clause (or indexes that
are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
statements) are ordinary full indexes.
</p>

[sqlite3_file_control | file control] which gives [VFS] implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>

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

BubbleDiagram pragma-stmt
BubbleDiagram pragma-value
</tcl>

<p>
^A pragma can take either zero or one argument.  ^The argument is may be either
in parentheses or it may be separated from the pragma name by an equal sign.
^The two syntaxes yield identical results.
^(In many pragmas, the argument is a boolean.  The boolean can be one of:

[sqlite3_file_control | file control] which gives [VFS] implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>

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

RecursiveBubbleDiagram pragma-stmt pragma-value

</tcl>

<p>
^A pragma can take either zero or one argument.  ^The argument is may be either
in parentheses or it may be separated from the pragma name by an equal sign.
^The two syntaxes yield identical results.
^(In many pragmas, the argument is a boolean.  The boolean can be one of:

# characters from each line.  It also does variable substitution in
# the namespace of its calling procedure.
#
proc putsin4 {fd text} {
  regsub -all "\n    " $text \n text
  puts $fd [uplevel 1 [list subst -noback -nocom $text]]
}










# A procedure to write the common header found on every HTML file on
# the SQLite website.
#
#####################
# NOTE:  This code is copied and reused in matrix.tcl.  When making
# changes to this implementation, be sure to also change matrix.tcl.
................................................................................
        var q = document.getElementById("q");
        if( q.value == "" ) { 
          q.value = gMsg
          q.style.color = "#044a64"
          q.style.fontStyle = "italic"
        }
      }












    </script>}
  }
  if {1} {
    putsin4 $fd {<td>
        <div style="padding:0 1em 0px 0;white-space:nowrap">
        <form name=f method="GET" action="http://www.sqlite.org/search">
          <input id=q name=q type=text
................................................................................
#
proc BubbleDiagram {name {anonymous_flag 0}} {
  global hd

  #if {!$anonymous_flag} {
  #  hd_resolve "<h4>\[$name:\]</h4>"
  #}
  hd_resolve "<h4>\[$name:\]</h4>"
  set alt "alt=\"syntax diagram $name\""
  if {$hd(enable-main)} {
    puts $hd(main) "<blockquote>\
        <img $alt src=\"$hd(rootpath-main)images/syntax/$name.gif\"></img>\
        </blockquote>"
  }
  if {$hd(enable-aux)} {
    puts $hd(aux) "<blockquote>\
        <img $alt src=\"$hd(rootpath-aux)images/syntax/$name.gif\"></img>\
        </blockquote>"
  }
}



































































# Record a requirement.  This procedure is active only for the first
# pass.  This procedure becomes a no-op for the second pass.  During
# the second pass, requirements listing report generators can use the
# data accumulated during the first pass to construct their reports.
#
# If the "verbatim" argument is true, then the requirement text is

# characters from each line.  It also does variable substitution in
# the namespace of its calling procedure.
#
proc putsin4 {fd text} {
  regsub -all "\n    " $text \n text
  puts $fd [uplevel 1 [list subst -noback -nocom $text]]
}

# Return a globally unique object id
#
set hd_id_counter 0
proc hd_id {} {
  global hd_id_counter
  incr hd_id_counter
  return x$hd_id_counter
}

# A procedure to write the common header found on every HTML file on
# the SQLite website.
#
#####################
# NOTE:  This code is copied and reused in matrix.tcl.  When making
# changes to this implementation, be sure to also change matrix.tcl.
................................................................................
        var q = document.getElementById("q");
        if( q.value == "" ) { 
          q.value = gMsg
          q.style.color = "#044a64"
          q.style.fontStyle = "italic"
        }
      }
      function hideorshow(btn,obj){
        var x = document.getElementById(obj);
        var b = document.getElementById(btn);
        if( x.style.display!='none' ){
          x.style.display = 'none';
          b.innerHTML='show';
        }else{
          x.style.display = '';
          b.innerHTML='hide';
        }
        return false;
      }
    </script>}
  }
  if {1} {
    putsin4 $fd {<td>
        <div style="padding:0 1em 0px 0;white-space:nowrap">
        <form name=f method="GET" action="http://www.sqlite.org/search">
          <input id=q name=q type=text
................................................................................
#
proc BubbleDiagram {name {anonymous_flag 0}} {
  global hd

  #if {!$anonymous_flag} {
  #  hd_resolve "<h4>\[$name:\]</h4>"
  #}
  hd_resolve "<p><b>\[$name:\]</b></p>"
  set alt "alt=\"syntax diagram $name\""
  if {$hd(enable-main)} {
    puts $hd(main) "<blockquote>\n\
        <img $alt src=\"$hd(rootpath-main)images/syntax/$name.gif\"></img>\n\
        </blockquote>"
  }
  if {$hd(enable-aux)} {
    puts $hd(aux) "<blockquote>\n\
        <img $alt src=\"$hd(rootpath-aux)images/syntax/$name.gif\"></img>\n\
        </blockquote>"
  }
}
proc HiddenBubbleDiagram {name} {
  global hd
  set alt "alt=\"syntax diagram $name\""
  hd_resolve "<p><b>\[$name:\]</b> "
  if {$hd(enable-main)} {
    set a [hd_id]
    set b [hd_id]
    puts $hd(main) \
     "<button id='$a' onclick='hideorshow(\"$a\",\"$b\")'>show</button>\
      </p>\n\
      <blockquote id='$b' style='display:none;'>\n\
      <img $alt src=\"$hd(rootpath-main)images/syntax/$name.gif\"></img>\n\
      </blockquote>"
  }
  if {$hd(enable-aux)} {
    set a [hd_id]
    set b [hd_id]
    puts $hd(aux) \
     "<button id='$a' onclick='hideorshow(\"$a\",\"$b\")'>show</button>\
      </p>\n\
      <blockquote id='$b' style='display:none;'>\n\
      <img $alt src=\"$hd(rootpath-aux)images/syntax/$name.gif\"></img>\n\
      </blockquote>"
  }
}
proc RecursiveBubbleDiagram_helper {class name openflag exclude} {
  global hd syntax_linkage
  set alt "alt=\"syntax diagram $name\""
  hd_resolve "<p><b>\[$name:\]</b>\n"
  set a [hd_id]
  set b [hd_id]
  if {$openflag} {
    puts $hd($class) \
      "<button id='$a' onclick='hideorshow(\"$a\",\"$b\")'>hide</button></p>\n\
       <blockquote id='$b'>\n\
       <img $alt src=\"$hd(rootpath-$class)images/syntax/$name.gif\" />"
  } else {
    puts $hd($class) \
      "<button id='$a' onclick='hideorshow(\"$a\",\"$b\")'>show</button></p>\n\
       <blockquote id='$b' style='display:none;'>\n\
       <img $alt src=\"$hd(rootpath-$class)images/syntax/$name.gif\" />"
  }
  if {[info exists syntax_linkage($name)]} {
    foreach {cx px} $syntax_linkage($name) break
    foreach c $cx {
      if {[lsearch $exclude $c]>=0} continue
      RecursiveBubbleDiagram_helper $class $c 0 [concat $exclude $c]
    }  
  }
  puts $hd($class) "</blockquote>"
}
proc RecursiveBubbleDiagram {args} {
  global hd
  foreach name $args {
    if {$hd(enable-main)} {
      RecursiveBubbleDiagram_helper main $name 1 $args
    }
    if {$hd(enable-aux)} {
      RecursiveBubbleDiagram_helper aux $name 1 $args
    }
  }
}



# Insert a See Also line for related bubble

# Record a requirement.  This procedure is active only for the first
# pass.  This procedure becomes a no-op for the second pass.  During
# the second pass, requirements listing report generators can use the
# data accumulated during the first pass to construct their reports.
#
# If the "verbatim" argument is true, then the requirement text is