SQL As Understood By SQLite

}
puts "

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

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

The optional ON CONFLICT clause at the end of a BEGIN statement
can be used to changed the default conflict resolution algorithm.
The normal default is ABORT. If an alternative is specified by
the ON CONFLICT clause of a BEGIN, then that alternative is used
as the default for all commands within the transaction. The default
algorithm is overridden by ON CONFLICT clauses on individual
constraints within the CREATE TABLE or CREATE INDEX statements
and by the OR clauses on COPY, INSERT, and UPDATE commands.

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 pg_dump so that data can be easily transferred from
PostgreSQL into SQLite.

The table-name is the name of an existing table which is to
be filled with data. The filename is a string or identifier that
names a file from which data will be read. The filename can be
the STDIN to read data from standard input.

Each line of the input file is converted into a single record
in the table. Columns are separated by tabs. If a tab occurs as
data within a column, then that tab is preceded by a baskslash "\"
character. A baskslash in the data appears as two backslashes in
a row. The optional USING DELIMITERS clause can specify a delimiter
other than tab.

If a column consists of the character "\N", that column is filled
with the value NULL.

The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use for this one command.
See the section titled
ON CONFLICT for additional information.

When the input data source is STDIN, the input can be terminated
by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".

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 the sort order is ignored in the current
implementation.

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.

If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed. Any attempt to insert a duplicate entry
will result in a rollback and an error message.

The optional conflict-clause allows the specification of al alternative
default constraint conflict resolution algorithm for this index.
This only makes sense if the UNIQUE keyword is used since otherwise
there are not constraints on the index. The default algorithm is
ABORT. If a COPY, INSERT, or UPDATE statement specifies a particular
conflict resolution algorithm, that algorithm is used in place of
the default algorithm specified here.
See the section titled
ON CONFLICT for additional information.

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.