## Run this Tcl script to generate the lang-*.html files.#set rcsid {$Id: lang.tcl,v 1.111 2006/03/15 16:26:10 drh Exp $}source common.tclif {[llength $argv]>0} {  set outputdir [lindex $argv 0]} else {  set outputdir ""}header {Query Language Understood by SQLite}puts {<h1>SQL As Understood By SQLite</h1><p>The SQLite library understands most of the standard SQLlanguage.  But it does <a href="omitted.html">omit some features</a>while at the same timeadding a few features of its own.  This document attempts todescribe precisely what parts of the SQL language SQLite doesand does not support.  A list of <a href="lang_keywords.html">keywords</a> is also provided.</p><p>In all of the syntax diagrams that follow, literal text is shown inbold blue.  Non-terminal symbols are shown in italic red.  Operatorsthat are part of the syntactic markup itself are shown in black roman.</p><p>This document is just an overview of the SQL syntax implementedby SQLite.  Many low-level productions are omitted.  For detailed informationon the language that SQLite understands, refer to the source code andthe grammar file "parse.y".</p><p>SQLite implements the follow syntax:</p><p><ul>}proc slink {label} {  if {[string match *.html $label]} {    return $label  }  if {[string length $::outputdir]==0} {    return #$label  } else {     return lang_$label.html  }}foreach {section} [lsort -index 0 -dictionary {  {{CREATE TABLE} createtable}  {{CREATE INDEX} createindex}  {VACUUM vacuum}  {{DROP TABLE} droptable}  {{DROP INDEX} dropindex}  {INSERT insert}  {REPLACE replace}  {DELETE delete}  {UPDATE update}  {SELECT select}  {comment comment}  {COPY copy}  {EXPLAIN explain}  {expression expr}  {{BEGIN TRANSACTION} transaction}  {{COMMIT TRANSACTION} transaction}  {{END TRANSACTION} transaction}  {{ROLLBACK TRANSACTION} transaction}  {PRAGMA pragma.html}  {{ON CONFLICT clause} conflict}  {{CREATE VIEW} createview}  {{DROP VIEW} dropview}  {{CREATE TRIGGER} createtrigger}  {{DROP TRIGGER} droptrigger}  {{ATTACH DATABASE} attach}  {{DETACH DATABASE} detach}  {REINDEX reindex}  {{ALTER TABLE} altertable}  {{ANALYZE} analyze}}] {  foreach {s_title s_tag} $section {}  puts "<li><a href=\"[slink $s_tag]\">$s_title</a></li>"}puts {</ul></p><p>Details on the implementation of each command are provided inthe sequel.</p>}proc Operator {name} {  return "<font color=\"#2c2cf0\"><big>$name</big></font>"}proc Nonterminal {name} {  return "<i><font color=\"#ff3434\">$name</font></i>"}proc Keyword {name} {  return "<font color=\"#2c2cf0\">$name</font>"}proc Example {text} {  puts "<blockquote><pre>$text</pre></blockquote>"}proc Section {name label} {  global outputdir  if {[string length $outputdir]!=0} {    if {[llength [info commands puts_standard]]>0} {      footer $::rcsid    }    if {[string length $label]>0} {      rename puts puts_standard      proc puts {str} {        regsub -all {href="#([a-z]+)"} $str {href="lang_\1.html"} str        puts_standard $::section_file $str      }      rename footer footer_standard      proc footer {id} {        footer_standard $id        rename footer ""        rename puts ""        rename puts_standard puts        rename footer_standard footer      }       set ::section_file [open [file join $outputdir lang_$label.html] w]      header "Query Language Understood by SQLite: $name"      puts "<h1>SQL As Understood By SQLite</h1>"      puts "<a href=\"lang.html\">\[Contents\]</a>"      puts "<h2>$name</h2>"      return     }  }  puts "\n<hr />"  if {$label!=""} {    puts "<a name=\"$label\"></a>"  }  puts "<h1>$name</h1>\n"}Section {ALTER TABLE} altertableSyntax {sql-statement} {ALTER TABLE [<database-name> .] <table-name> <alteration>} {alteration} {RENAME TO <new-table-name>} {alteration} {ADD [COLUMN] <column-def>}puts {<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 possibleto 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 remainattached to the table after it has been renamed. However, if there areany view definitions, or statements executed by triggers that refer tothe table being renamed, these are not automatically modified to use the newtable name. If this is required, the triggers or view definitions must bedropped 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.</ul><p>The execution time of the ALTER TABLE command is independent ofthe amount of data in the table.  The ALTER TABLE command runs as quicklyon a table with 10 million rows as it does on a table with 1 row.</p><p>After ADD COLUMN has been run on a database, that database will notbe readable by SQLite version 3.1.3 and earlier until the databaseis <a href="lang_vacuum.html">VACUUM</a>ed.</p>}Section {ANALYZE} analyzeSyntax {sql-statement} {  ANALYZE}Syntax {sql-statement} {  ANALYZE <database-name>}Syntax {sql-statement} {  ANALYZE [<database-name> .] <table-name>}puts {<p>The ANALYZE command gathers statistics about indices and stores themin a special tables in the database where the query optimizer can usethem to help make better index choices.If no arguments are given, all indices in all attached databases areanalyzed.  If a database name is given as the argument, all indicesin that one database are analyzed.  If the argument is a table name,then only indices associated with that one table are analyzed.</p><p>The initial implementation stores all statistics in a singletable named <b>sqlite_stat1</b>.  Future enhancements may createadditional tables with the same name pattern except with the "1"changed to a different digit.  The <b>sqlite_stat1</b> table cannotbe <a href="#droptable">DROP</a>ped,but it all the content can be <a href="#delete">DELETE</a>d which has thesame effect.</p>}Section {ATTACH DATABASE} attachSyntax {sql-statement} {ATTACH [DATABASE] <database-filename> AS <database-name>}puts {<p>The ATTACH DATABASE statement adds a preexisting database file to the current database connection.  If the filename contains punctuation characters it must be quoted.  The names 'main' and 'temp' refer to the main database and the database used for temporary tables.  These cannot be detached.  Attached databases are removed using the <a href="#detach">DETACH DATABASE</a> statement.</p><p>You can read from and write to an attached database and youcan modify the schema of the attached database.  This is a newfeature of SQLite version 3.0.  In SQLite 2.8, schema changesto attached databases were not allowed.</p><p>You cannot create a new table with the same name as a table in an attached database, but you can attach a database which containstables whose names are duplicates of tables in the main database.  It is also permissible to attach the same database file multiple times.</p><p>Tables in an attached database can be referred to using the syntax <i>database-name.table-name</i>.  If an attached table doesn't have a duplicate table name in the main database, it doesn't require a database name prefix.  When a database is attached, all of its tables which don't have duplicate names become the 'default' tableof that name.  Any tables of that name attached afterwards require the table prefix. If the 'default' table of a given name is detached, then the last table of that name attached becomes the new default.</p><p>Transactions involving multiple attached databases are atomic,assuming that the main database is not ":memory:".  If the maindatabase is ":memory:" then transactions continue to be atomic within each individualdatabase file. But if the host computer crashes in the middleof a COMMIT where two or more database files are updated,some of those files might get the changes where othersmight not.Atomic commit of attached databases is a new feature of SQLite version 3.0.In SQLite version 2.8, all commits to attached databases behaved as ifthe main database were ":memory:".</p><p>There is a compile-time limit of 10 attached database files.</p>}Section {BEGIN TRANSACTION} transactionSyntax {sql-statement} {BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]}Syntax {sql-statement} {END [TRANSACTION [<name>]]}Syntax {sql-statement} {COMMIT [TRANSACTION [<name>]]}Syntax {sql-statement} {ROLLBACK [TRANSACTION [<name>]]}puts {<p>Beginning in version 2.0, SQLite supports transactions withrollback and atomic commit.</p><p>The optional transaction name is ignored. SQLite currently does not allow nested transactions.</p><p>No changes can be made to the database except within a transaction.Any command that changes the database (basically, any SQL commandother than SELECT) will automatically start a transaction ifone is not already in effect.  Automatically started transactionsare committed at the conclusion of the command.</p><p>Transactions can be started manually using the BEGINcommand.  Such transactions usually persist until the nextCOMMIT or ROLLBACK command.  But a transaction will also ROLLBACK if the database is closed or if an error occursand the ROLLBACK conflict resolution algorithm is specified.See the documentation on the <a href="#conflict">ON CONFLICT</a>clause for additional information about the ROLLBACKconflict resolution algorithm.</p><p>In SQLite version 3.0.8 and later, transactions can be deferred,immediate, or exclusive.  Deferred means that no locks are acquiredon the database until the database is first accessed.  Thus with adeferred transaction, the BEGIN statement itself does nothing.  Locksare not acquired until the first read or write operation.  The first readoperation against a database creates a SHARED lock and the firstwrite operation creates a RESERVED lock.   Because the acquisition oflocks is deferred until they are needed, it is possible that anotherthread or process could create a separate transaction and write tothe database after the BEGIN on the current thread has executed.If the transaction is immediate, then RESERVED locksare acquired on all databases as soon as the BEGIN command isexecuted, without waiting for thedatabase to be used.  After a BEGIN IMMEDIATE, you are guaranteed thatno other thread or process will be able to write to the database ordo a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  Other processes can continueto read from the database, however.  An exclusive transaction causesEXCLUSIVE locks to be acquired on all databases.  After a BEGINEXCLUSIVE, you are guaranteed that no other thread or process willbe able to read or write the database until the transaction iscomplete.</p>