Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -183,18 +183,22 @@ tclsh $(TOP)/www/changes.tcl >changes.html fileformat.html: $(TOP)/www/fileformat.tcl tclsh $(TOP)/www/fileformat.tcl >fileformat.html +lang.html: $(TOP)/www/lang.tcl + tclsh $(TOP)/www/lang.tcl >lang.html + # Files to be published on the website. # PUBLISH = \ sqlite.tar.gz \ index.html \ sqlite.html \ changes.html \ fileformat.html \ + lang.html \ c_interface.html website: $(PUBLISH) publish: $(PUBLISH) ADDED www/lang.tcl Index: www/lang.tcl ================================================================== --- www/lang.tcl +++ www/lang.tcl @@ -0,0 +1,188 @@ +# +# Run this Tcl script to generate the sqlite.html file. +# +set rcsid {$Id: lang.tcl,v 1.1 2000/06/08 21:53:06 drh Exp $} + +puts { + + Query Language Understood By SQLite + + +

+SQL As Understood By SQLite +

} +puts "

+(This page was last modified on [lrange $rcsid 3 4] GMT) +

" + +puts { +

The SQLite library understands most of the standard SQL +language. But it does omit some features while at the same time +adding a few features of its own. This document attempts to +describe percisely what parts of the SQL language SQLite does +and does not support.

+ +

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.

+ +

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.

+ +

CREATE TABLE

+ +

The basic structure of a CREATE TABLE statement is as follows:

+} + +proc Syntax {args} { + puts {} + foreach {rule body} $args { + puts "" + regsub -all < $body {%LT} body + regsub -all > $body {%GT} body + regsub -all %LT $body {} body + regsub -all %GT $body {} body + regsub -all {[]|[*?]} $body {&} body + regsub -all "\n" [string trim $body] "
\n" body + regsub -all "\n *" $body "\n\\ \\ \\ \\ " body + regsub -all {[|,*()]} $body {&} body + puts "" + } + puts {

" + puts "$rule ::=

$body

} +} + +Syntax {sql-command} { +CREATE TABLE ( + [, ]* + [, ]* +) +} {column-def} { + []* +} {type} { + | + ( ) | + ( , ) +} {column-constraint} { +NOT NULL | +PRIMARY KEY [] | +UNIQUE | +CHECK ( ) +} {constraint} { +PRIMARY KEY ( [, ]* ) | +UNIQUE ( [, ]* ) | +CHECK ( ) +} + +puts { +
A CREATE TABLE statement is basically the keywords "CREATE TABLE" +followed by the name of a new table and a parenthesized list of column +definitions and constraints. The table name can be either an identifier +or a string. The only reserved table name is "sqlite_master" which +is the name of the table that records the database schema.
+ +
Each column definition is the name of the column followed by the +datatype for that column, then one or more optional column constraints. +The datatype for the column is ignored. All information +is stored as null-terminated strings. The constraints are also ignored, +except that the PRIMARY KEY constraint will cause an index to be automatically +created that implements the primary key. The name of the primary +key index will be the table name +with "__primary_key" appended. The index used for a primary key +does not show up in the sqlite_master table, but a GDBM file is +created for that index.
+ +
There are no arbitrary limits on the size of columns, on the number +of columns, or on the number of constraints in a table.
+ +
The exact text +of each CREATE TABLE statement is stored in the sqlite_master +table. Everytime the database is opened, all CREATE TABLE statements +are read from the sqlite_master table and used to regenerate +SQLite's internal representation of the table layout.
+} + +puts {
CREATE INDEX
+} + +Syntax {sql-statement} { +CREATE INDEX +ON ( [, ]* ) +} {column-name} { + [ ASC | DESC ] +} + +puts { +
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. +Each column name can be followed by one of the "ASC" or "DESC" keywords +to indicate sort order, but since GDBM does not implement ordered keys, +these keywords are ignored.
+ +
There are no arbitrary limits on the number of indices that can be +attached to a single table, nor on the number of columns in an index.
+ +
The exact text +of each CREATE INDEX statement is stored in the sqlite_master +table. Everytime the database is opened, all CREATE INDEX statements +are read from the sqlite_master table and used to regenerate +SQLite's internal representation of the index layout.
+ +
DROP TABLE
+} + +Syntax {sql-command} { +DROP TABLE +} + +puts { +
The DROP TABLE statement consists of the keywords "DROP TABLE" followed +by the name of the table. The table named is completely removed from +the disk. The table can not be recovered. All indices associated with +the table are also reversibly deleted.
+ +
DROP INDEX
+} + +Syntax {sql-command} { +DROP INDEX +} + +puts { +
The DROP INDEX statement consists of the keywords "DROP INDEX" followed +by the name of the index. The index named is completely removed from +the disk. The only way to recover the index is to reenter the +appropriate CREATE INDEX command.
+ +
VACUUM
+} + +Syntax {sql-statement} { +VACUUM [] +} + +puts { +
The VACUUM command is an SQLite extension modelled after a similar +command found in PostgreSQL. If VACUUM is invoked with the name of a +table or index, then the gdbm_reorganize() function is called +on the corresponding GDBM file. If VACUUM is invoked with no arguments, +then gdbm_reorganize() is call on every GDBM file in the database.
+ +
It is a good idea to run VACUUM after creating large indices, +especially indices where a single index value refers to many +entries in the data table. Reorganizing these indices will make +the underlying GDBM file much smaller and will help queries to +run much faster.
+ +} + +puts { +
+
+Back to the SQLite Home Page +
+ +}