SQL AS UNDERSTOOD BY SQLITE

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

85 views

8/3/10 Travis Horn
SQL AS UNDERSTOOD BY SQLITE
SQLite 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 precisely what parts of the SQL language SQLite does and does not support. A list of
SQL keywords is also provided. The SQL language syntax is described by syntax diagrams.
The following syntax documentation topics are available:
CONTENTS

Aggregate Functions .................................................................................................................................................................................. 4
ALTER TABLE .............................................................................................................................................................................................. 5
ANALYZE ..................................................................................................................................................................................................... 6
ATTACH DATABASE .................................................................................................................................................................................... 7
BEGIN TRANSACTION ................................................................................................................................................................................. 8
Response To Errors Within A Transaction .............................................................................................................................................. 9
comment .................................................................................................................................................................................................. 10
Core Functions ......................................................................................................................................................................................... 11
CREATE INDEX .......................................................................................................................................................................................... 15
CREATE TABLE .......................................................................................................................................................................................... 16
ROWIDs and the INTEGER PRIMARY KEY ............................................................................................................................................. 19
CREATE TRIGGER ...................................................................................................................................................................................... 20
Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers ............................................................................ 21
INSTEAD OF trigger .............................................................................................................................................................................. 21
Examples .............................................................................................................................................................................................. 22
Cautions On The Use Of BEFORE triggers ............................................................................................................................................ 22
The RAISE() function ............................................................................................................................................................................ 22
CREATE VIEW ........................................................................................................................................................................................... 22
CREATE VIRTUAL TABLE ........................................................................................................................................................................... 24
Date And Time Functions ......................................................................................................................................................................... 25
Time Strings ......................................................................................................................................................................................... 25
Modifiers .............................................................................................................................................................................................. 26
Examples .............................................................................................................................................................................................. 27
Caveats And Bugs ................................................................................................................................................................................. 27
DELETE ..................................................................................................................................................................................................... 29
Restrictions on DELETE Statements Within CREATE TRIGGER ............................................................................................................. 29
The Truncate Optimization .................................................................................................................................................................. 29
Use Of LIMIT ......................................................................................................................................................................................... 29
DETACH DATABASE .................................................................................................................................................................................. 31
DROP INDEX ............................................................................................................................................................................................. 31
DROP TABLE ............................................................................................................................................................................................. 31
DROP TRIGGER ......................................................................................................................................................................................... 32
DROP VIEW .............................................................................................................................................................................................. 32
EXPLAIN .................................................................................................................................................................................................... 33
expression ................................................................................................................................................................................................ 35
Operators ............................................................................................................................................................................................. 36
Literal Values ........................................................................................................................................................................................ 37
Parameters ........................................................................................................................................................................................... 37
8/3/10 Travis Horn
The LIKE and GLOB operators .............................................................................................................................................................. 37
The BETWEEN operator ....................................................................................................................................................................... 38
Table Column Names ........................................................................................................................................................................... 39
Subqueries ........................................................................................................................................................................................... 39
CAST expressions ................................................................................................................................................................................. 39
Functions .............................................................................................................................................................................................. 40
INDEXED BY .............................................................................................................................................................................................. 41
See Also: ............................................................................................................................................................................................... 41
INSERT ...................................................................................................................................................................................................... 42
SQLite Keywords ...................................................................................................................................................................................... 43
ON CONFLICT clause ................................................................................................................................................................................ 45
ROLLBACK............................................................................................................................................................................................. 45
ABORT .................................................................................................................................................................................................. 45
FAIL ...................................................................................................................................................................................................... 45
IGNORE................................................................................................................................................................................................. 45
REPLACE ............................................................................................................................................................................................... 45
PRAGMA Statements ............................................................................................................................................................................... 47
PRAGMA command syntax .................................................................................................................................................................. 47
List of PRAGMAs .................................................................................................................................................................................. 47
REINDEX ................................................................................................................................................................................................... 58
SAVEPOINT ............................................................................................................................................................................................... 60
Transaction Nesting Rules .................................................................................................................................................................... 61
REPLACE ................................................................................................................................................................................................... 61
SELECT ...................................................................................................................................................................................................... 62
UPDATE .................................................................................................................................................................................................... 64
Restrictions on UPDATE Statements Within CREATE TRIGGER ............................................................................................................ 65
Optional LIMIT and ORDER BY Clauses ................................................................................................................................................ 65
VACUUM .................................................................................................................................................................................................. 67


8/3/10 Travis Horn
The routines sqlite3_prepare_v2(), sqlite3_prepare(), sqlite3_prepare16(), sqlite3_exec(), and sqlite3_get_table() accept an SQL
statement list (sql-stmt-list) which is a semicolon-separated list of statements.
sql-stmt-list:

Each SQL statement in the statement list is an instance of the following:
sql-stmt:


8/3/10 Travis Horn
AGGREGATE FUNCTIONS

The aggregate functions shown below are available by default. Additional aggregate functions written in C may be added using the
sqlite3_create_function() API.
In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT. In such cases,
duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will
return the number of distinct values of column X instead of the total number of non-null values in column X.
avg(X) The avg() function returns the average value of all non-NULL X within a group. String and BLOB values that do
not look like numbers are interpreted as 0. The result of avg() is always a floating point value as long as at there
is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are
no non-NULL inputs.
count(X)
count(*) The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*)
function (with no arguments) returns the total number of rows in the group.
group_concat(X)
group_concat(X,Y) The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If
parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the
separator if Y is omitted. The order of the concatenated elements is arbitrary.
max(X) The max() aggregate function returns the maximum value of all values in the group. The maximum value is the
value that would be returned last in an ORDER BY on the same column. Aggregate max() returns NULL if and
only if there are no non-NULL values in the group.
min(X) The min() aggregate function returns the minimum non-NULL value of all values in the group. The minimum
value is the first non-NULL value that would appear in an ORDER BY of the column. Aggregate min() returns
NULL if and only if there are no non-NULL values in the group.
sum(X)
total(X) The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there are no non-
NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the
sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that
way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided
as a convenient way to work around this design problem in the SQL language.
The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs
are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which
might be an approximation to the true sum.
Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow
occurs at any point during the computation. Total() never throws an integer overflow.

8/3/10 Travis Horn
ALTER TABLE

alter-table-stmt:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a
new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.
The RENAME TO syntax is used to rename the table identified by [database-name.]table-name to new-table-name. This command
cannot be used to move a table between attached databases, only to rename a table within the same database.
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.
If foreign key constraints are enabled when a table is renamed, then any REFERENCES clauses in any table (either the table being
renamed or some other table) that refer to the table being renamed are modified to refer to the renamed table by its new name.
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. The column-def rule defines the characteristics of the new column. The new column may take any of the forms
permissible in a CREATE TABLE statement, with the following restrictions:
The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default
value of NULL.
Note also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table. This can
result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate
CHECK constraints as they are added.
The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command
runs as quickly on a table with 10 million rows as it does on a table with 1 row.
After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier.

8/3/10 Travis Horn
ANALYZE

analyze-stmt:

The ANALYZE command gathers statistics about indices and stores them in a special tables in the database where the query
optimizer can use them to help make better index choices. If no arguments are given, all indices in all attached databases are
analyzed. If a database name is given as the argument, all indices in that one database are analyzed. If the argument is a table name,
then only indices associated with that one table are analyzed.
The default implementation stores all statistics in a single table named sqlite_stat1. If SQLite is compiled with the
SQLITE_ENABLE_STAT2 option, then additional histogram data is collected and stored in sqlite_stat2. Future enhancements may
create additional tables with the same name pattern except with the "1" or "2" changed to a different digit.
The DROP TABLE command does not work on the sqlite_stat1 or sqlite_stat2 tables, but all the content of those tables can be
queried using SELECT and can be deleted, augmented, or modified using the DELETE, INSERT, and UPDATE commands. Appropriate
care should be used when changing the content of the statistics tables as invalid content can cause SQLite to select inefficient query
plans. Generally speaking, one should not modify the content of the statistics tables by any mechanism other than invoking the
ANALYZE command.
Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database
changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to
update the statistics.

8/3/10 Travis Horn
ATTACH DATABASE

attach-stmt:

The ATTACH DATABASE statement adds another database file to the current database connection. The database-names 'main' and
'temp' refer to the main database and the database used for temporary tables. The main and temp databases cannot be attached or
detached.
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. If the system is not running in shared cache mode, it is also
permissible to attach the same database file multiple times.
Tables in an attached database can be referred to using the syntax database-name.table-name. If the name of the table is unique
across all attached databases and the main and temp databases, then the database-name prefix is not required. When a database is
attached, all tables which don't have duplicate names become the default table of that name. Any tables of that name attached
afterwards require the database prefix. If the default table of a given name is detached, then the last table of that name attached
becomes the new default.
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.
There is a compile-time limit of SQLITE_MAX_ATTACHED attached database files.

8/3/10 Travis Horn
BEGIN TRANSACTION

begin-stmt:

commit-stmt:

rollback-stmt:

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 when the last query finishes.
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 ON CONFLICT clause for additional information about the ROLLBACK
conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. The
"TO SAVEPOINT name" clause of the ROLLBACK command shown in the syntax diagram above is only applicable to SAVEPOINT
transactions. An attempt to invoke the BEGIN command within a transaction will fail with an error, regardless of whether the
transaction was started by SAVEPOINT or a prior BEGIN. The COMMIT command and the ROLLBACK command without the TO clause
work the same on SAVEPOINT transactions as they do with transactions started by BEGIN.
Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. 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 to the filesystem. 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, no other database
connection 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, no other database connection except for read_uncommitted connections will be able to read the database and no other
connection without exception will be able to write the database until the transaction is complete.
8/3/10 Travis Horn
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically
when the last active statement finishes. A statement finishes when its prepared statement is reset or finalized. An open sqlite3_blob
used for incremental BLOB I/O counts as an unfinished statement. The sqlite3_blob finishes when it is closed.
The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write
operations, the COMMIT command will fail with an error code SQLITE_BUSY.
An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if another thread or process has a shared 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.
The ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. Both read-only and read/write queries will
cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because
bad things will happen if the in-memory image of the database is changed out from under an active query.
If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is
undefined.
RESPONSE TO ERRORS WITHIN A TRANSACTION

If certain kinds of errors occur within a transaction, the transaction may or may not be rolled back automatically. The errors that
cause the behavior include:
SQLITE_FULL: database or disk full
SQLITE_IOERR: disk I/O error
SQLITE_BUSY: database in use by another process
SQLITE_NOMEM: out or memory
SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements
within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and
the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction. An application can
tell which course of action SQLite took by using the sqlite3_get_autocommit() C-language interface.
It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction
has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm
is caused by this.
Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite
might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the
errors above to force an unconditional rollback.

8/3/10 Travis Horn
COMMENT

comment-syntax:

Comments are not SQL commands, but can occur within the text of SQL queries passed to sqlite3_prepare_v2() and related
interfaces. Comments are treated as whitespace by the parser. Comments can begin anywhere whitespace can be found, including
inside expressions that span multiple lines.
SQL comments begin with two consecutive "-" characters (ASCII 0x2d) and extend up to and including the next newline character
(ASCII 0x0a) or until the end of input, whichever comes first.
C-style comments begin with "/*" and extend up to and including the next "*/" character pair or until the end of input, whichever
comes first. C-style comments can span multiple lines.
Comments can appear anywhere whitespace can occur, including inside expressions and in the middle of other SQL statements.
Comments do not nest.

8/3/10 Travis Horn
CORE FUNCTIONS

The core functions shown below are available by default. Date & Time functions and aggregate functions are documented
separately. An application may define additional functions written in C and added to the database engine using the
sqlite3_create_function() API.
abs(X) The abs(X) function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL.
Abs(X) return 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -
9223372036854775807 then abs(X) throws an integer overflow error since there is no equivalent positive 64-
bit two complement value.
changes() The changes() function returns the number of database rows that were changed or inserted or deleted by the
most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers.
The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the
same rules for counting changes.
coalesce(X,Y,...) The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL.
Coalesce() must be at least 2 arguments.
glob(X,Y) The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the X and Y arguments are
reversed in the glob() function relative to the infix GLOB operator. If the sqlite3_create_function() interface is
used to override the glob(X,Y) function with an alternative implementation then the GLOB operator will invoke
the alternative implementation.
ifnull(X,Y) The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull()
must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
hex(X) The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal
rendering of the content of that blob.
last_insert_rowid() The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which
invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid()
C/C++ interface function.
length(X) The length(X) function returns the length of X in characters if X is a string, or in bytes if X is a blob. If X is NULL
then length(X) is NULL. If X is numeric then length(X) returns the length of a string representation of X.
like(X,Y)
like(X,Y,Z) The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is
present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments
only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The
sqlite3_create_function() interface can be used to override the like() function and thereby change the
operation of the LIKE operator. When overriding the like() function, it may be important to override both the
two and three argument versions of the like() function. Otherwise, different code may be called to implement
the LIKE operator depending on whether or not an ESCAPE clause was specified.


load_extension(X)
8/3/10 Travis Horn
load_extension(X,Y) The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry
point Y. The result of load_extension() is always a NULL. If Y is omitted then the default entry point of
sqlite3_extension_init is used. The load_extension() function raises an exception if the extension fails to load or
initialize correctly.
The load_extension() function will fail if the extension attempts to modify or delete a SQL function or collating
sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing
functions or collating sequences because those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement. To load an extension that changes or deletes functions or collating
sequences, use the sqlite3_load_extension() C-language API.
lower(X) The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default
built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load
the ICU extension.
ltrim(X)
ltrim(X,Y) The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the
left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X.
max(X,Y,...) The multi-argument max() function returns the argument with the maximum value, or return NULL if any
argument is NULL. The multi-argument max() function searches its arguments from left to right for an argument
that defines a collating function and uses that collating function for all string comparisons. If none of the
arguments to max() define a collating function, then the BINARY collating function is used. Note that max() is a
simple function when it has 2 or more arguments but operates as an aggregate function if given only a single
argument.
min(X,Y,...) The multi-argument min() function returns the argument with the minimum value. The multi-argument min()
function searches its arguments from left to right for an argument that defines a collating function and uses
that collating function for all string comparisons. If none of the arguments to min() define a collating function,
then the BINARY collating function is used. Note that min() is a simple function when it has 2 or more
arguments but operates as an aggregate function if given only a single argument.
nullif(X,Y) The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are
the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a
collating function and uses that collating function for all string comparisons. If neither argument to nullif()
defines a collating function then the BINARY is used.
quote(X) The quote(X) function returns a string which is the value of its argument suitable for inclusion into another SQL
statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are
encoded as hexadecimal literals.
random() The random() function returns a pseudo-random integer between -9223372036854775808 and
+9223372036854775807.
randomblob(N) The randomblob(N) function return an N-byte blob containing pseudo-random bytes. If N is less than 1 then a
1-byte random blob is returned.

Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower()
like this:
8/3/10 Travis Horn
hex(randomblob(16))
lower(hex(randomblob(16)))
replace(X,Y,Z) The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in
string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X
unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.
round(X)
round(X,Y) The round(X,Y) function returns a string representation of the floating-point value X rounded to Y digits to the
right of the decimal point. If the Y argument is omitted, the X value is truncated to an integer.
rtrim(X)
rtrim(X,Y) The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the
right side of X. If the Y argument is omitted, rtrim(X) removes spaces from the right side of X.
soundex(X) The soundex(X) function returns a string that is the soundex encoding of the string X. The string "?000" is
returned if the argument is NULL or contains no ASCII alphabetic characters. This function is omitted from
SQLite by default. It is only available if the SQLITE_SOUNDEX compile-time option is used when SQLite is built.
sqlite_compileoption_get(N) The sqlite_compileoption_get() SQL function is a wrapper around the sqlite3_compileoption_get()
C/C++ function. This routine returns the N-th compile-time option used to build SQLite or NULL if N
is out of range. See also the compile_options pragma.
sqlite_compileoption_used(X) The sqlite_compileoption_used() SQL function is a wrapper around the
sqlite3_compileoption_used() C/C++ function. When the argument X to
sqlite_compileoption_used(X) is a string which is the name of a compile-time option, this routine
returns true (1) or false (0) depending on whether or not that option was used during the build.
sqlite_source_id() The sqlite_source_id() function returns a string that identifies the specific version of the source code that was
used to build the SQLite library. The string returned by sqlite_source_id() begins with the date and time that
the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This
function is an SQL wrapper around the sqlite3_sourceid() C interface.
sqlite_version() The sqlite_version() function returns the version string for the SQLite library that is running. This function is an
SQL wrapper around the sqlite3_libversion() C-interface.
substr(X,Y,Z)
substr(X,Y) The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z
characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning
with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring
is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the
Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a
BLOB then the indices refer to bytes.
total_changes() The total_changes() function returns the number of row changes caused by INSERT, UPDATE or DELETE
statements since the current database connection was opened. This function is a wrapper around the
sqlite3_total_changes() C/C++ interface.
trim(X)
trim(X,Y) The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both
ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X.
8/3/10 Travis Horn
typeof(X) The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real",
"text", or "blob".
upper(X) The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to
their upper-case equivalent.
zeroblob(N) The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very
efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O.
This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface.

8/3/10 Travis Horn
CREATE INDEX

create-index-stmt:

indexed-column:

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. The sort order may or may not be
ignored depending on the database file format. The "legacy" file format ignores index sort order. The descending index file format
takes index sort order into account. Only copies of SQLite newer than version 3.3.0 (released on 2006-01-10) are able to understand
the newer descending index file format and so for compatibility with older versions of SQLite, the legacy file format is generated by
default. Use the legacy_file_format pragma to modify this behavior and generate databases that use the newer file format. Future
versions of SQLite may begin to generate the newer file format by default.
The COLLATE clause optionally following each column name defines a collating sequence used for text entries in that column. The
default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating
sequence is otherwise defined, the built-in BINARY collating sequence is used.
There are no arbitrary limits on the number of indices that can be attached to a single table. The number of columns in an index is
limited to the value set by sqlite3_limit(SQLITE_LIMIT_COLUMN,...).
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 an error. For the purposes of unique indices, all NULL values are considered to different from all other
NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the
standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL
Server follow the other interpretation of the standard.
If the optional IF NOT EXISTS clause is present and another index with the same name already exists, then this command becomes a
no-op.
Indexes are removed with the DROP INDEX command.

8/3/10 Travis Horn
CREATE TABLE

create-table-stmt:

column-def:

type-name:

column-constraint:


8/3/10 Travis Horn
table-constraint:

foreign-key-clause:

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. Tables names that begin with "sqlite_" are reserved for use by SQLite itself and cannot
normally appear in a CREATE TABLE statement.
Each column definition is the name of the column optionally followed by the datatype for that column, then one or more optional
column constraints. SQLite uses dynamic typing; the datatype for the column does not restrict what data may be put in that column.
The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different
from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple
entries with the value of NULL. The COLLATE clause specifies what text collating function to use when comparing text entries for the
column. The built-in BINARY collating function is used by default.

8/3/10 Travis Horn
The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number,
or a constant expression enclosed in parentheses. The default value may also be one of the special case-independent keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is inserted into the
column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME,
CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME,
the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
The PRIMARY KEY attribute normally creates a UNIQUE index on the column or columns that are specified as the PRIMARY KEY. The
only exception to this behavior is special INTEGER PRIMARY KEY column, described below. According to the SQL standard, PRIMARY
KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows
NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but
by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem.
So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we
may change SQLite to conform to the SQL standard in future and should design new programs accordingly.
SQLite uses dynamic typing instead of static typing. Except for the special case of INTEGER PRIMARY KEY, SQLite will allow values of
any type to be stored in any column regardless of the declared datatype of that column. The declared datatype is a type affinity that
SQLite attempts to comply with, but the operation will proceed even if compliance is not possible.
If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible
within that same database connection and is automatically deleted when the database connection is closed. Any indices created on
a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.
If a <database-name> is specified, then the table is created in the named database. It is an error to specify both a <database-name>
and the TEMP keyword, unless the <database-name> is "temp". If no database name is specified and the TEMP keyword is not
present then the table is created in the main database.
The optional conflict clause following each constraint allows the specification of an alternative default constraint conflict resolution
algorithm for that constraint. If no conflict clause is specified, the ABORT algorithm is used. Different constraints within the same
table may have different conflict resolution algorithms. If an INSERT or UPDATE statement specifies a conflict resolution algorithm,
then the algorithm specified on the INSERT or UPDATE statement overrides the algorithm specified in the CREATE TABLE statement.
See the section titled ON CONFLICT for additional information.
CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.
The number of columns in a table is limited by the SQLITE_MAX_COLUMN compile-time parameter. A single row of a table cannot
store more than SQLITE_MAX_LENGTH bytes of data. Both of these limits can be lowered at runtime using the sqlite3_limit() C/C++
interface.
The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the
columns in the result.
If the optional IF NOT EXISTS clause is present and another table with the same name already exists, then this command becomes a
no-op.
Tables are removed using the DROP TABLE statement.

8/3/10 Travis Horn

ROWIDS AND THE INTEGER PRIMARY KEY

Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is usually called the
"rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Rows are stored in rowid order. The rowid
value can be accessed using one of the special names "ROWID", "OID", or "_ROWID_" assuming those names are no used by other
conventional table columns.
If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an
alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value. The rowid is not able to hold floating
point values, strings, BLOBs, or NULLs.
An INTEGER PRIMARY KEY column is an alias for the 64-bit signed integer rowid.
An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way
that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately.
The special behavior of INTEGER PRIMARY KEY is only available if the type name is exactly "INTEGER" in any mixture of upper and
lower case. Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key
column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid. The special
behavior of INTEGER PRIMARY KEY is only available if the primary key is a single column. Multi-column primary keys do not become
aliases for the rowid. The AUTOINCREMENT keyword only works on a column that is an alias for the rowid.
Note that searches against a rowid are generally about twice as fast as searches against any other PRIMARY KEY or indexed value.
Goofy behavior alert: The following three declarations all cause the column "x" be an alias for the rowid:
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
But, in contrast, the following declaration does not result in "x" being an alias for the rowid:
CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
This asymmetrical behavior is unfortunate and is really due to a bug in the parser in early versions of SQLite. But fixing the bug would
result in very serious backwards incompatibilities. The SQLite developers feel that goofy behavior in an corner case is far better than
a compatibility break, so the original behavior is retained.

8/3/10 Travis Horn
CREATE TRIGGER

create-trigger-stmt:

The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are
automatically performed when a specified database event occurs.
A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an
UPDATE occurs on one or more specified columns of a table.
At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH
ROW is optional. FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN
clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.
Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references
of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the
trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:
INSERT NEW references are valid
UPDATE NEW and OLD references are valid
8/3/10 Travis Horn
DELETE OLD references are valid
If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no
WHEN clause is supplied, the SQL statements are executed for all rows.
The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or
removal of the associated row.
An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON
CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement
is used instead.
Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped. However if the
trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.
Triggers are removed using the DROP TRIGGER statement.
SYNTAX RESTRICTIONS ON UPDATE, DELETE, AND INSERT STATEMENTS WITHIN TRIGGERS

The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT
statements. The following restrictions apply:
The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In
other words, one must use just "tablename" not "database.tablename" when specifying the table. The table to be modified
must exist in the same database as the table or view to which the trigger is attached.
The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.
The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.
The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not
normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the
SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level
UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.
INSTEAD OF TRIGGER

Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement. If one or
more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or
UPDATE statement on the view, respectively. Instead, executing an INSERT, DELETE or UPDATE on the view causes the associated
triggers to fire. The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).
Note that the sqlite3_changes() and sqlite3_total_changes() interfaces do not count INSTEAD OF trigger firings, but the
count_changes pragma does count INSTEAD OF trigger firing.

8/3/10 Travis Horn

EXAMPLES

Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the
following trigger ensures that all associated orders are redirected when a customer changes his or her address:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
CAUTIONS ON THE USE OF BEFORE TRIGGERS

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result
of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is
undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.
The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.
Because of the behaviors described above, programmers are encouraged to prefer AFTER triggers over BEFORE triggers.
THE RAISE() FUNCTION

A special SQL function RAISE() may be used within a trigger-program, with the following syntax
raise-function:

When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed
(either ABORT, FAIL or ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the
application, along with the specified error message.
When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to
execute and any subsequent trigger programs that would of been executed are abandoned. No database changes are rolled back. If
the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes
execution at the beginning of the next step.
CREATE VIEW

create-view-stmt:
8/3/10 Travis Horn

The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the
FROM clause of another SELECT in place of a table name.
If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the
process that opened the database and is automatically deleted when the database is closed.
If a <database-name> is specified, then the view is created in the named database. It is an error to specify both a <database-name>
and the TEMP keyword on a VIEW, unless the <database-name> is "temp". If no database name is specified, and the TEMP keyword
is not present, the VIEW is created in the main database.
You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF
trigger on the view to accomplish the same thing. Views are removed with the DROP VIEW command.

8/3/10 Travis Horn
CREATE VIRTUAL TABLE

create-virtual-table-stmt:

A virtual table is an interface to an external storage or computation engine that appears to be a table but does not actually store
information in the database file.
In general, you can do anything with a virtual table that can be done with an ordinary table, except that you cannot create indices or
triggers on a virtual table. Some virtual table implementations might impose additional restrictions. For example, many virtual tables
are read-only.
The <module-name> is the name of an object that implements the virtual table. The <module-name> must be registered with the
SQLite database connection using sqlite3_create_module() or sqlite3_create_module_v2() prior to issuing the CREATE VIRTUAL
TABLE statement. The module takes zero or more comma-separated arguments. The arguments can be just about any text as long as
it has balanced parentheses. The argument syntax is sufficiently general that the arguments can be made to appear as column
definitions in a traditional CREATE TABLE statement. SQLite passes the module arguments directly to the xCreate and xConnect
methods of the module implementation without any interpretation. It is the responsibility of the module implementation to parse
and interpret its own arguments.
A virtual table is destroyed using the ordinary DROP TABLE statement. There is no DROP VIRTUAL TABLE statement.

8/3/10 Travis Horn
DATE AND TIME FUNCTI ONS

SQLite supports five date and time functions as follows:
1. date(timestring, modifier, modifier, ...)
2. time(timestring, modifier, modifier, ...)
3. datetime(timestring, modifier, modifier, ...)
4. julianday(timestring, modifier, modifier, ...)
5. strftime(format, timestring, modifier, modifier, ...)
All five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The
strftime() function also takes a format string as its first argument.
The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format:
YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The
julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic
Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument.
The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new
substitutions, %f and %J. The following is a complete list of valid strftime() substitutions:
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

Notice that all other date and time functions can be expressed in terms of strftime():

Function Equivalent strftime()
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

TIME STRINGS


A time string can be in any of the following formats:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
8/3/10 Travis Horn
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10
that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as
obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used. Format 12 is the
Julian day number expressed as a floating point value.

MODIFIERS


The time string can be followed by zero or more modifiers that alter the date and time string. Each modifier is a transformation that
is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as
follows.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding
timestring and modifiers. Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the
±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially
yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date
is February 29 of a leap year and the modifier is ±N years where N is not a multiple of four.

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and
so forth.

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes
the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds
since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of
seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is
undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works
for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (Unix times of -62167219200 through 10675199167).

8/3/10 Travis Horn
The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so
that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" is the opposite of
"localtime". "utc" assumes that the string to its left is in the local time zone and adjusts that string to be in UTC. If the prior string is
not in localtime, then the result of "utc" is undefined.

EXAMPLES


Compute the current date.

SELECT date('now');

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a Unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a Unix timestamp 1092941466, and compensate for your local time zone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current Unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independent.

SELECT julianday('now') - julianday('1776-07-04');

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the Unix epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

CAVEATS AND BUGS


The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this
implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. The localtime_r() C
function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into
an equivalent year within this range, do the calculation, then map the year back.

8/3/10 Travis Horn
These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (Julian day numbers 1721059.5 through
5373484.5). For dates outside that range, the results of these functions are undefined.

Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical
DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the
new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules
were also changed.

All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in
duration.

8/3/10 Travis Horn
DELETE

delete-stmt:

qualified-table-name:

The DELETE command is used to remove records from a table. The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
Without a WHERE clause, all rows of the table are removed. If a WHERE clause is supplied, then only those rows that match the
expression are removed.
RESTRICTIONS ON DELETE STATEMENTS WITHIN CREATE TRIGGER

There are additional syntax restrictions on DELETE statements that occur within the body of a CREATE TRIGGER statement. The
table-name must be unqualified. In other words, the database-name. prefix on the table name is not allowed within triggers. The
table from which to delete must be in the same database as the table to which the trigger is attached.
The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE statements within triggers.
The LIMIT clause (described below) is unsupported within triggers.
THE TRUNCATE OPTIMIZATION

When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to
erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the
delete run much faster. Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and
sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem
has been fixed as of version 3.6.5.
The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the
SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.
The truncate optimization can also be disabled at runtime using the sqlite3_set_authorizer() interface. If an authorizer callback
returns SQLITE_IGNORE for an SQLITE_DELETE action code, then the DELETE operation will proceed but the truncate optimization
will be bypassed and rows will be deleted one by one.
USE OF LIMIT

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE
statement is extended by the addition of optional ORDER BY and LIMIT clauses:
delete-stmt-limited:
8/3/10 Travis Horn

The optional LIMIT clause can be used to limit the number of rows deleted, and thereby limit the size of the transaction. The ORDER
BY clause on a DELETE statement is used only to determine which rows fall within the LIMIT. The order in which rows are deleted is
arbitrary and is not determined by the ORDER BY clause.
The presence of a LIMIT clause defeats the truncate optimization causing all rows being deleted to be visited.

8/3/10 Travis Horn
DETACH DATABASE

detach-stmt:

This statement detaches an additional database connection previously attached using the ATTACH statement. It is possible to have
the same database file attached multiple times using different names, and detaching one connection to a file will leave the others
intact.
This statement will fail if SQLite is in the middle of a transaction.
DROP INDEX

drop-index-stmt:

The DROP INDEX statement removes an index added with the CREATE INDEX statement. The index is completely removed from the
disk. The only way to recover the index is to reenter the appropriate CREATE INDEX command.
DROP TABLE

drop-table-stmt:

The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name. The
dropped table is completely removed from the database schema and the disk file. The table cannot be recovered. All indices and
triggers associated with the table are also deleted.
The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.
If foreign key constraints are enabled, a DROP TABLE command performs an implicit DELETE FROM <tbl> command before removing
the table from the database schema. Any triggers attached to the table are dropped from the database schema before the implicit
DELETE FROM <tbl> is executed, so this cannot cause any triggers to fire. By contrast, an implicit DELETE FROM <tbl> does cause any
configured foreign key actions to take place. If the implicit DELETE FROM <tbl> executed as part of a DROP TABLE command violates
any immediate foreign key constraints, an error is returned and the table is not dropped. If the implicit DELETE FROM <tbl> causes
any deferred foreign key constraints to be violated, and the violations still exist when the transaction is committed, an error is
returned at the time of commit.

8/3/10 Travis Horn

DROP TRIGGER

drop-trigger-stmt:

The DROP TRIGGER statement removes a trigger created by the CREATE TRIGGER statement. The trigger is deleted from the
database schema. Note that triggers are automatically dropped when the associated table is dropped.
DROP VIEW

drop-view-stmt:

The DROP VIEW statement removes a view created by the CREATE VIEW statement. The name specified is the view name. It is
removed from the database schema, but no actual data in the underlying base tables is modified.

8/3/10 Travis Horn

EXPLAIN

sql-stmt:

An SQL statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN". Either modification causes
the SQL statement to behave as a query and to return information about how the SQL statement would have operated if the
EXPLAIN keyword or phrase had been omitted.
When the EXPLAIN keyword appears by itself it causes the statement to behave as a query that returns the sequence of virtual
machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. When the EXPLAIN
QUERY PLAN phrase appears, the statement returns high-level information about what indices would have been used.
8/3/10 Travis Horn
The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the
output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY
PLAN since their exact behavior is undocumented, unspecified, and variable.

8/3/10 Travis Horn
EXPRESSION

expr:


8/3/10 Travis Horn
literal-value:

signed-number:

raise-function:

This section is different from the others. Most other sections of this document talks about a particular SQL command. This section
does not talk about a standalone command but about "expressions" which are subcomponents of most other commands.
OPERATORS

SQLite understands the following binary operators, in order from highest to lowest precedence:
||
* / %
+ -
<< >> & |
< <= > >=
= == != <> IS IN LIKE GLOB MATCH REGEXP
AND
OR
Supported unary prefix operators are these:
- + ~ NOT
The COLLATE operator can be thought of as a unary postfix operator. The COLLATE operator has the highest precedence. It always
binds more tightly than any prefix unary operator or any binary operator.
8/3/10 Travis Horn
The unary operator + is a no-op. It can be applied to strings, numbers, or blobs and it always gives as its result the value of the
operand.
Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can
be either != or <>. The || operator is "concatenate" - it joins together the two strings of its operands. The operator % outputs the
remainder of its left operand modulo its right operand.
The result of any binary operator is a numeric value, except for the || concatenation operator which gives a string result.
The IS and IS NOT operators work like = and != except that NULL values compare equal to one another. IS and IS NOT have the same
precedence as =.
LITERAL VALUES

A literal value is a constant of some kind. Literal values may be integers, floating point numbers, strings, BLOBs, or NULLs. Scientific
notation is supported for floating point literal values. The "." character is always used as the decimal point even if the locale setting
specifies "," for this role - the use of "," for the decimal point would result in syntactic ambiguity. A string constant is formed by
enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in
Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string
literals containing hexadecimal data and preceded by a single "x" or "X" character. For example:
X'53514C697465'
A literal value can also be the token "NULL".
PARAMETERS

A "variable" or "parameter" token specifies a placeholder in the expression for a literal value that is filled in at runtime using the
sqlite3_bind() family of C/C++ interfaces. Parameters can take several forms:
?NNN A question mark followed by a number NNN holds a spot for the NNN-th parameter. NNN must be between 1
and SQLITE_MAX_VARIABLE_NUMBER.
? A question mark that is not followed by a number holds a spot for the next unused parameter.
:AAAA A colon followed by an identifier name holds a spot for a named parameter with the name AAAA. Named
parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best
to avoid mixing named and numbered parameters.
@AAAA An "at" sign works exactly like a colon.
$AAAA A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name AAAA. The
identifier name in this case can include one or more occurrences of "::" and a suffix enclosed in "(...)" containing
any text at all. This syntax is the form of a variable name in the Tcl programming language. The presence of this
syntax results from the fact that SQLite is really a Tcl extension that has escaped into the wild.

Parameters that are not assigned values using sqlite3_bind() are treated as NULL.
THE LIKE AND GLOB OPERATORS

8/3/10 Travis Horn
The LIKE operator does a pattern matching comparison. The operand to the right of the LIKE operator contains the pattern and the
left hand operand contains the string to match against the pattern. A percent symbol ("%") in the LIKE pattern matches any
sequence of zero or more characters in the string. An underscore ("_") in the LIKE pattern matches any single character in the string.
Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only
understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for Unicode characters
that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)
If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a
single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape
character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal
percent symbol, underscore, or a single escape character, respectively.
The infix LIKE operator is implemented by calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
The LIKE operator can be made case sensitive using the case_sensitive_like pragma.
The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE.
Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented
by calling the function glob(Y,X) and can be modified by overriding that function.
The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of
the REGEXP operator will normally result in an error message. If a application-defined SQL function named "regexp" is added at run-
time, that function will be called in order to implement the REGEXP operator.
The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation
raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.
THE BETWEEN OPERATOR

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z"
except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the
precedence as operators == and != and LIKE and groups left to right.
The CASE expression
A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages. WHEN expressions are evaluated from left
to right until one is found that is true, at which point the corresponding THEN term becomes the result. If no WHEN expression is
true then the ELSE clause determines the result or the result is NULL if there is no ELSE clause.
The optional expression that occurs in between the CASE keyword and the first WHEN keyword is the "base" expression. There are
two basic forms of a CASE expression: those with and without a base expression. In a CASE without a base expression, each WHEN
expression is evaluated as a boolean and the overall result is determined by first WHEN expression that is true. In a CASE with a base
expression, the base expression is evaluated just once and the result is compared against each WHEN expression until a match is
found. When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling
rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.
Assuming the subexpressions have no side-effects, the following two expressions are equivalent:
CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
8/3/10 Travis Horn
The only difference between the two CASE expressions shown above is that the x expression is evaluated exactly once in the first
example but might be evaluated multiple times in the second.
A NULL result is considered false when evaluating WHEN terms. If the base expression is NULL then the result of the CASE is the
result of the ELSE expression if it exists, or NULL if the ELSE clause is omitted.
TABLE COLUMN NAMES

A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers:
"ROWID", "OID", or "_ROWID_". These special identifiers all describe the unique integer key (the rowid) associated with every row
of every table. The special identifiers only refer to the row key if the CREATE TABLE statement does not define a real column with
the same name. The rowid can be used anywhere a regular column can be used.
SUBQUERIES

SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the
operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in
its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns
in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is
empty. If no terms in the SELECT expression refer to values in the containing query, then the expression is evaluated once prior to
any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query,
then the SELECT is reevaluated every time it is needed.
When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the SELECT result contains no NULLs and if
the left operand matches any of the values in the SELECT result. The IN operator may be preceded by the NOT keyword to invert the
sense of the test.
When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored.
If the SELECT yields no rows, then the value of the SELECT is NULL.
CAST EXPRESSIONS

A CAST expression changes the datatype of the into the type specified by <type>. <type> can be any non-empty type name that is
valid for the type in a column definition of a CREATE TABLE statement. The <type> name is interpreted according to the rules for
determining column affinity.
An explicit cast is strong than affinity; with the CAST expression the datatype conversion is forced even if it is lossy and irreversible.
A cast of a REAL value into an INTEGER will truncate the fractional part of the REAL. If an REAL is too large to be represented
as an INTEGER then the result of the cast is the largest negative integer: -9223372036854775808.
A cast of a TEXT value into an INTEGER or REAL will read a prefix of the TEXT value that looks like an integer or real value,
respectively, and ignore the rest. When casting from TEXT into INTEGER or REAL, leading spaces in the TEXT value are
ignored. A cast of a TEXT value that looks nothing like a number into INTEGER or REAL results in 0 or 0.0, respectively.
Casting a value into NUMERIC first does a forced conversion into REAL but will then further converts the result into INTEGER
if and only if the conversion from REAL to INTEGER is lossless and reversible. This is the only context in SQLite where the
NUMERIC and INTEGER affinities behave differently.
Casting a value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of
the database connection.
8/3/10 Travis Horn
Casting a value to a <type> with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first
casting the value to TEXT in the encoding of the database connection, then interpreting the resulting byte sequence as a
BLOB instead of as TEXT.
Casting a BLOB value into any type other than a BLOB consists of first interpreting the byte sequence of the BLOB as a TEXT
string in the database encoding then continuing as if the value where originally of type TEXT.
Any cast of a NULL value yields a NULL result.
Note that the result from casting any non-BLOB value into a BLOB and the result from casting any BLOB value into a non-BLOB value
will be different depending on whether the database encoding is UTF-8, UTF-16be, or UTF-16le.
FUNCTIONS

Both simple and aggregate functions are supported. (For presentation purposes, simple functions are further subdivided into core
functions and date-time functions.) A simple function can be used in any expression. Simple functions return a result immediately
based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across
all rows of the result set.

8/3/10 Travis Horn
INDEXED BY

The INDEXED BY phrase is a SQL extension found only in SQLite which can be used to verify that the correct indices are being used on
a DELETE, SELECT, or UPDATE statement. The INDEXED BY phrase always follows the name of a table that SQLite will be reading. The
INDEXED BY phrase can be seen in the following syntax diagrams:
qualified-table-name:

single-source:

The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table.
If index-name does not exist or cannot be used for the query, then the preparation of the SQL statement fails. The "NOT INDEXED"
clause specifies that no index shall be used when accessing the preceding table, including implied indices create by UNIQUE and
PRIMARY KEY constraints. However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is
specified.
Some SQL database engines provide non-standard "hint" mechanisms which can be used to give the query optimizer clues about
what indices it should use for a particular statement. The INDEX BY clause of SQLite is not a hinting mechanism and it should not be
used as such. The INDEXED BY clause does not give the optimizer hints about which index to use; it gives the optimizer a
requirement of which index to use. If the query optimizer is unable to use the index specified by the INDEX BY clause, then the query
will fail with an error.
The INDEXED BY clause is not intended for use in tuning the performance of a query. The intent of the INDEXED BY clause is to raise
a run-time error if a schema change, such as dropping or creating an index, causes the query plan for a time-sensitive query to
change. The INDEXED BY clause is designed to help detect undesirable query plan changes during regression testing. Developers are
admonished to omit all use of INDEXED BY during application design, implementation, testing, and tuning. If INDEXED BY is to be