lang.tcl

一个小型的嵌入式数据库

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.84 2005/02/19 12:44:16 drh Exp $}
source common.tcl

if {[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 SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
adding a few features of its own.  This document attempts to
describe precisely what parts of the SQL language SQLite does
and 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 in
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code and
the 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}
}] {
  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 in
the 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} altertable

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> RENAME TO <new-table-name>
}

puts {
<p>SQLite's version of the ALTER TABLE command allows the user to 
rename an existing table. The table identified by 
<i>[database-name.]table-name</i> is renamed 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>
}

Section {ATTACH DATABASE} attach

Syntax {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 you
can modify the schema of the attached database.  This is a new
feature of SQLite version 3.0.  In SQLite 2.8, schema changes
to 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 contains
tables 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' table
of 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 main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might 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 if
the main database were ":memory:".
</p>

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


Section {BEGIN TRANSACTION} transaction

Syntax {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 with
rollback 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 command
other than SELECT) will automatically start a transaction if
one is not already in effect.  Automatically started transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN
command.  Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict 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 acquired
on the database until the database is first accessed.  Thus with a
deferred transaction, the BEGIN statement itself does nothing.  Locks
are not acquired until the first read or write operation.  The first read
operation against a database creates a SHARED lock and the first
write operation creates a RESERVED lock.   Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then RESERVED locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  After a BEGIN IMMEDIATE, you are guaranteed that
no other thread or process will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  Other processes can continue
to read from the database, however.  An exclusive transaction causes
EXCLUSIVE locks to be acquired on all databases.  After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will
be able to read or write the database until the transaction is
complete.
</p>

<p>
A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks
is available <a href="lockingv3.html">separately</a>.
</p>

<p>
The default behavior for SQLite version 3.0.8 is a
deferred transaction.  For SQLite version 3.0.0 through 3.0.7,
deferred is the only kind of transaction available.  For SQLite
version 2.8 and earlier, all transactions are exclusive.
</p>

<p>
The COMMIT command does not actually perform a commit until all
pending SQL commands finish.  Thus if two or more SELECT statements
are in the middle of processing and a COMMIT is executed, the commit
will not actually occur until all SELECT statements finish.
</p>

<p>
An attempt to execute COMMIT might result in an SQLITE_BUSY return code.
This indicates that another thread or process had a read lock on the database
that prevented the database from being updated.  When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>
}


Section comment comment

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

puts {
<p> Comments aren't SQL commands, but can occur in SQL queries. They are 
treated as whitespace by the parser.  They can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
</p>

<p> SQL comments only extend to the end of the current line.</p>

<p> C comments can span any number of lines.  If there is no terminating
delimiter, they extend to the end of the input.  This is not treated as
an error.  A new SQL statement can begin on a line after a multiline
comment ends.  C comments can be embedded anywhere whitespace can occur,
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>
}


Section COPY copy

Syntax {sql-statement} {
COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}

puts {
<p>The COPY command is available in SQLite version 2.8 and earlier.
The COPY command has been removed from SQLite version 3.0 due to
complications in trying to support it in a mixed UTF-8/16 environment.
In version 3.0, the <a href="sqlite.html">command-line shell</a>
contains a new command <b>.import</b> that can be used as a substitute
for COPY.
</p>

<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from
PostgreSQL into SQLite.</p>

<p>The table-name is the name of an existing table which is to