lang.tcl

一个小型的嵌入式数据库

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

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

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

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

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

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

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

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A negative LIMIT indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.
In a compound query, the LIMIT clause may only appear on the
final SELECT statement.
The limit is applied to the entire query not
to the individual SELECT statement to which it is attached.
Note that if the OFFSET keyword is used in the LIMIT clause, then the
limit is the first number and the offset is the second number.  If a
comma is used instead of the OFFSET keyword, then the offset is the
first number and the limit is the second number.  This seeming
contradition is intentional - it maximizes compatibility with legacy
SQL database systems.
</p>

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


Section UPDATE update

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

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

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


Section VACUUM vacuum

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

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

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

<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space.  This makes the database 
file larger than it needs to be, but can speed up inserts.  In time 
inserts and deletes can leave the database file structure fragmented, 
which slows down disk access to the database contents.

The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure. It is not possible to perform the same
process on an attached database file.</p>

<p>This command will fail if there is an active transaction.  This 
command has no effect on an in-memory database.</p>

<p>As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
}

# A list of keywords.  A asterisk occurs after the keyword if it is on
# the fallback list.
#
set keyword_list [lsort {
   ABORT*
   AFTER*
   ALL
   ALTER
   AND
   AS
   ASC*
   ATTACH*
   AUTOINCREMENT
   BEFORE*
   BEGIN*
   BETWEEN
   BY
   CASCADE*
   CASE
   CHECK
   COLLATE
   COMMIT
   CONFLICT*
   CONSTRAINT
   CREATE
   CROSS
   CURRENT_DATE*
   CURRENT_TIME*
   CURRENT_TIMESTAMP*
   DATABASE*
   DEFAULT
   DEFERRED*
   DEFERRABLE
   DELETE
   DESC*
   DETACH*
   DISTINCT
   DROP
   END*
   EACH*
   ELSE
   ESCAPE
   EXCEPT
   EXCLUSIVE*
   EXPLAIN*
   FAIL*
   FOR*
   FOREIGN
   FROM
   FULL
   GLOB*
   GROUP
   HAVING
   IGNORE*
   IMMEDIATE*
   IN
   INDEX
   INITIALLY*
   INNER
   INSERT
   INSTEAD*
   INTERSECT
   INTO
   IS
   ISNULL
   JOIN
   KEY*
   LEFT
   LIKE*
   LIMIT
   MATCH*
   NATURAL
   NOT
   NOTNULL
   NULL
   OF*
   OFFSET*
   ON
   OR
   ORDER
   OUTER
   PRAGMA*
   PRIMARY
   RAISE*
   REFERENCES
   REINDEX*
   RENAME*
   REPLACE*
   RESTRICT*
   RIGHT
   ROLLBACK
   ROW*
   SELECT
   SET
   STATEMENT*
   TABLE
   TEMP*
   TEMPORARY*
   THEN
   TO
   TRANSACTION
   TRIGGER*
   UNION
   UNIQUE
   UPDATE
   USING
   VACUUM*
   VALUES
   VIEW*
   WHEN
   WHERE
}]



Section {SQLite keywords} keywords

puts {
<p>The SQL standard specifies a huge number of keywords which may not
be used as the names of tables, indices, columns, or databases.  The
list is so long that few people can remember them all.  For most SQL
code, your safest bet is to never use any English language word as the
name of a user-defined object.</p>

<p>If you want to use a keyword as a name, you need to quote it.  There
are three ways of quoting keywords in SQLite:</p>

<p>
<blockquote>
<table>
<tr>	<td valign="top"><b>'keyword'</b></td><td width="20"></td>
	<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>

<p>Quoted keywords are unaesthetic.
To help you avoid them, SQLite allows many keywords to be used unquoted
as the names of databases, tables, indices, triggers, views, and/or columns.
In the list of keywords that follows, those that can be used as identifiers
are shown in an italic font.  Keywords that must be quoted in order to be
used as identifiers are shown in bold.</p>

<p>
SQLite adds new keywords from time to time when it take on new features.
So to prevent you code from being broken by future enhancements, you should
normally quote any indentifier that is an English language word, even if
you do not have to.
</p>

<p>
The following are the keywords currently recognized by SQLite:
</p>

<blockquote>
<table width="100%">
<tr>
<td align="left" valign="top" width="20%">
}

set n [llength $keyword_list]
set nCol 5
set nRow [expr {($n+$nCol-1)/$nCol}]
set i 0
foreach word $keyword_list {
  if {[string index $word end]=="*"} {
    set word [string range $word 0 end-1]
    set font i
  } else {
    set font b
  }
  if {$i==$nRow} {
    puts "</td><td valign=\"top\" align=\"left\" widt