The following SQL syntax listings are supported by the
Adobe AIR SQL database engine. The listings are divided into explanations
of different statement and clause types, expressions, built-in functions,
and operators. The following topics are covered:
-
General
SQL syntax
-
Data manipulation statements (SELECT, INSERT, UPDATE, and
DELETE)
-
Data definition statements (CREATE, ALTER, and DROP statements
for tables, indices, views, and triggers)
-
Special statements and clauses
-
Built-in functions (Aggregate, scalar, and date/time formatting
functions)
-
Operators
-
Parameters
-
Unsupported SQL features
-
Additional SQL features
General SQL syntax
In addition to the specific syntax for various statements
and expressions, the following are general rules of SQL syntax:
-
Case sensitivity
-
SQL statements, including object names, are not case sensitive. Nevertheless,
SQL statements are frequently written with SQL keywords written in
uppercase, and this document uses that convention. While SQL syntax
is not case sensitive, literal text values in SQL are case sensitive,
and comparison and sorting operations can be case sensitive, as
specified by the collation sequence defined for a column or operation.
For more information see COLLATE.
-
White space
-
A white-space character (such as space, tab, new line, and
so forth) must be used to separate individual words in an SQL statement.
However, white space is optional between words and symbols. The
type and quantity of white-space characters in a SQL statement is
not significant. You can use white space, such as indenting and
line breaks, to format your SQL statements for easy readability,
without affecting the meaning of the statement.
Data manipulation statements
Data manipulation statements are the most commonly used
SQL statements. These statements are used to retrieve, add, modify,
and remove data from database tables. The following data manipulation
statements are supported: SELECT, INSERT, UPDATE, and DELETE.
SELECT
The SELECT statement is used to
query the database. The result of a SELECT is zero or more rows
of data where each row has a fixed number of columns. The number
of columns in the result is specified by the result column name
or expression list between the SELECT and optional FROM keywords.
sql-statement ::= SELECT [ALL | DISTINCT] result
[FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select-statement]*
[ORDER BY sort-expr-list]
[LIMIT integer [( OFFSET | , ) integer]]
result ::= result-column [, result-column]*
result-column ::= * | table-name . * | expr [[AS] string]
table-list ::= table [ join-op table join-args ]*
table ::= table-name [AS alias] |
( select ) [AS alias]
join-op ::= , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join-args ::= [ON expr] [USING ( id-list )]
compound-op ::= UNION | UNION ALL | INTERSECT | EXCEPT
sort-expr-list ::= expr [sort-order] [, expr [sort-order]]*
sort-order ::= [COLLATE collation-name] [ASC | DESC]
collation-name ::= BINARY | NOCASE
Any arbitrary
expression can be used as a result. If a result expression is *
then all columns of all tables are substituted for that one expression.
If the expression is the name of a table followed by .* then the
result is all columns in that one table.
The DISTINCT keyword
causes a subset of result rows to be returned, in which each result
row is different. NULL values are not treated as distinct from each other.
The default behavior is that all result rows are returned, which
can be made explicit with the keyword ALL.
The query is executed
against one or more tables specified after the FROM keyword. If
multiple table names are separated by commas, then the query uses the
cross join of the various tables. The JOIN syntax can also be used
to specify how tables are joined. The only type of outer join that
is supported is LEFT OUTER JOIN. The ON clause expression in join-args
must resolve to a boolean value. A subquery in parentheses may be
used as a table in the FROM clause. The entire FROM clause may be
omitted, in which case the result is a single row consisting of
the values of the result expression list.
The WHERE clause
is used to limit the number of rows the query retrieves. WHERE clause
expressions must resolve to a boolean value. WHERE clause filtering
is performed before any grouping, so WHERE clause expressions may
not include aggregate functions.
The GROUP BY clause causes
one or more rows of the result to be combined into a single row
of output. A GROUP BY clause is especially useful when the result contains
aggregate functions. The expressions in the GROUP BY clause do not have
to be expressions that appear in the SELECT expression list.
The
HAVING clause is like WHERE in that it limits the rows returned
by the statement. However, the HAVING clause applies after any grouping
specified by a GROUP BY clause has occurred. Consequently, the HAVING
expression may refer to values that include aggregate functions.
A HAVING clause expression is not required to appear in the SELECT
list. Like a WHERE expression, a HAVING expression must resolve
to a boolean value.
The ORDER BY clause causes the output
rows to be sorted. The sort-expr-list argument to the ORDER BY clause
is a list of expressions that are used as the key for the sort.
The expressions do not have to be part of the result for a simple SELECT,
but in a compound SELECT (a SELECT using one of the compound-op operators)
each sort expression must exactly match one of the result columns. Each
sort expression may be optionally followed by a sort-order clause
consisting of the COLLATE keyword and the name of a collation function
used for ordering text and/or the keyword ASC or DESC to specify
the sort order (ascending or descending). The sort-order can be
omitted and the default (ascending order) is used. For a definition
of the COLLATE clause and collation functions, see COLLATE.
The
LIMIT clause places an upper bound on the number of rows returned
in the result. A negative LIMIT indicates no upper bound. The optional
OFFSET following LIMIT specifies how many rows to skip at the beginning
of the result set. In a compound SELECT query, the LIMIT clause
may only appear after the final SELECT statement, and the limit
is applied to the entire query. Note that if the OFFSET keyword
is used in the LIMIT clause, then the limit is the first integer
and the offset is the second integer. If a comma is used instead
of the OFFSET keyword, then the offset is the first number and the
limit is the second number. This seeming contradiction is intentional
— it maximizes compatibility with legacy SQL database systems.
A
compound SELECT is formed from two or more simple SELECT statements connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.
In a compound SELECT, all the constituent SELECT statements must
specify the same number of result columns. There can only be a single
ORDER BY clause after the final SELECT statement (and before the
single LIMIT clause, if one is specified). The UNION and UNION ALL
operators combine the results of the preceding and following SELECT
statements into a single table. The difference is that in UNION, all
result rows are distinct, but in UNION ALL, there may be duplicates.
The INTERSECT operator takes the intersection of the results of
the preceding and following SELECT statements. EXCEPT takes the
result of preceding SELECT after removing the results of the following
SELECT. When three or more SELECT statements are connected into
a compound, they group from first to last.
For a definition
of permitted expressions, see Expressions.
Starting with
AIR 2.5, the SQL CAST operator is supported when reading to convert
BLOB data to ActionScript ByteArray objects. For example, the following code
reads raw data that is not stored in the AMF format and stores it
in a ByteArray object:
stmt.text = "SELECT CAST(data AS ByteArray) AS data FROM pictures;";
stmt.execute();
var result:SQLResult = stmt.getResult();
var bytes:ByteArray = result.data[0].data;
INSERT
The INSERT statement comes in two
basic forms and is used to populate tables with data.
sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name.] table-name [(column-list)] VALUES (value-list) |
INSERT [OR conflict-algorithm] INTO [database-name.] table-name [(column-list)] select-statement
REPLACE INTO [database-name.] table-name [(column-list)] VALUES (value-list) |
REPLACE INTO [database-name.] table-name [(column-list)] select-statement
The
first form (with the VALUES keyword) creates a single new row in
an existing table. If no column-list is specified then the number
of values must be the same as the number of columns in the table.
If a column-list is specified, then the number of values must match
the number of specified columns. Columns of the table that do not
appear in the column list are filled with the default value defined
when the table is created, or with NULL if no default value is defined.
The
second form of the INSERT statement takes its data from a SELECT
statement. The number of columns in the result of the SELECT must
exactly match the number of columns in the table if column-list
is not specified, or it must match the number of columns named in
the column-list. A new entry is made in the table for every row
of the SELECT result. The SELECT may be simple or compound. For
a definition of allowable SELECT statements, see SELECT.
The
optional conflict-algorithm allows the specification of an alternative constraint
conflict resolution algorithm to use during this one command. For
an explanation and definition of conflict algorithms, see
Special statements and clauses
.
The two REPLACE INTO forms
of the statement are equivalent to using the standard INSERT [OR
conflict-algorithm] form with the REPLACE conflict algorithm (i.e.
the INSERT OR REPLACE... form).
The two REPLACE INTO forms
of the statement are equivalent to using the standard INSERT [OR
conflict-algorithm] form with the REPLACE conflict algorithm (i.e.
the INSERT OR REPLACE... form).
UPDATE
The update command changes the
existing records in a table.
sql-statement ::= UPDATE [database-name.] table-name SET column1=value1, column2=value2,... [WHERE expr]
The
command consists of the UPDATE keyword followed by the name of the table
in which you want to update the records. After the SET keyword,
provide the name of the column and the value to which the column
to be changed as a comma-separated list. The WHERE clause expression
provides the row or rows in which the records are updated.
DELETE
The delete command is used to remove
records from a table.
sql-statement ::= DELETE FROM [database-name.] table-name [WHERE expr]
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. The WHERE clause expression must resolve to a boolean value.
For a definition of permitted expressions, see Expressions.
Data definition statements
Data definition statements are used to create, modify,
and remove database objects such as tables, views, indices, and
triggers. The following data definition statements are supported:
-
Tables:
-
CREATE TABLE
-
ALTER TABLE
-
DROP TABLE
-
-
-
Triggers:
-
CREATE TRIGGERS
-
DROP TRIGGERS
CREATE TABLE
A CREATE TABLE statement
consists of the keywords CREATE TABLE followed by the name of the
new table, then (in parentheses) a list of column definitions and constraints.
The table name can be either an identifier or a string.
sql-statement ::= CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name.] table-name
( column-def [, column-def]* [, constraint]* )
sql-statement ::= CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement
column-def ::= name [type] [[CONSTRAINT name] column-constraint]*
type ::= typename | typename ( number ) | typename ( number , number )
column-constraint ::= NOT NULL [ conflict-clause ] |
PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] |
UNIQUE [conflict-clause] |
CHECK ( expr ) |
DEFAULT default-value |
COLLATE collation-name
constraint ::= PRIMARY KEY ( column-list ) [conflict-clause] |
UNIQUE ( column-list ) [conflict-clause] |
CHECK ( expr )
conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
default-value ::= NULL | string | number | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP
sort-order ::= ASC | DESC
collation-name ::= BINARY | NOCASE
column-list ::= column-name [, column-name]*
Each
column definition is the name of the column followed by the data
type for that column, then one or more optional column constraints.
The data type for the column restricts what data may be stored in
that column. If an attempt is made to store a value in a column
with a different data type, the runtime converts the value to the
appropriate type if possible, or raises an error. See the Data type support
section for additional information.
The NOT NULL column constraint
indicates that the column cannot contain NULL values.
A UNIQUE
constraint causes an index to be created on the specified column
or columns. This index must contain unique keys—no two rows may
contain duplicate values or combinations of values for the specified
column or columns. A CREATE TABLE statement can have multiple UNIQUE
constraints, including multiple columns with a UNIQUE constraint
in the column's definition and/or multiple table-level UNIQUE constraints.
A
CHECK constraint defines an expression that is evaluated and must
be true in order for a row's data to be inserted or updated. The
CHECK expression must resolve to a boolean value.
A COLLATE
clause in a column definition specifies what text collation function
to use when comparing text entries for the column. The BINARY collating
function is used by default. For details on the COLLATE clause and
collation functions, see COLLATE.
The DEFAULT constraint specifies
a default value to use when doing an INSERT. The value may be NULL,
a string constant, or a number. 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
a number, it is literally inserted into the column whenever an INSERT statement
does not specify a value for the column. If the value is CURRENT_TIME, CURRENT_DATE
or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted
into the column. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE,
the format is YYYY-MM-DD. The format for CURRENT_TIMESTAMP is YYYY-MM-DD
HH:MM:SS.
Specifying a PRIMARY KEY normally just creates a
UNIQUE index on the corresponding column or columns. However, if
the PRIMARY KEY constraint is on a single column that has the data
type INTEGER (or one of its synonyms such as int) then that column
is used by the database as the actual primary key for the table. This
means that the column may only hold unique integer values. (Note
that in many SQLite implementations, only the column type INTEGER
causes the column to serve as the internal primary key, but in Adobe
AIR synonyms for INTEGER such as int also specify that behavior.)
If
a table does not have an INTEGER PRIMARY KEY column, an integer
key is automatically generated when a row is inserted. The primary
key for a row can always be accessed using one of the special names
ROWID, OID, or _ROWID_. These names can be used regardless of whether
it is an explicitly declared INTEGER PRIMARY KEY or an internal
generated value. However, if the table has an explicit INTEGER PRIMARY
KEY, the name of the column in the result data is the actual column
name rather than the special name.
An INTEGER PRIMARY KEY
column can also include the keyword AUTOINCREMENT. When the AUTOINCREMENT
keyword is used, the database automatically generates and inserts
a sequentially incremented integer key in the INTEGER PRIMARY KEY
column when it executes an INSERT statement that doesn't specify
an explicit value for the column.
There can only be one PRIMARY
KEY constraint in a CREATE TABLE statement. It can either be part
of one column's definition or one single table-level PRIMARY KEY
constraint. A primary key column is implicitly NOT NULL.
The
optional conflict-clause following many constraints allows the specification of
an alternative default constraint conflict resolution algorithm
for that constraint. The default is ABORT. Different constraints
within the same table may have different default conflict resolution
algorithms. If an INSERT or UPDATE statement specifies a different
conflict resolution algorithm, that algorithm is used in place of
the algorithm specified in the CREATE TABLE statement. See the ON
CONFLICT section of
Special statements and clauses
for additional information.
Additional
constraints, such as FOREIGN KEY constraints, do not result in an
error but the runtime ignores them.
If the TEMP or TEMPORARY
keyword occurs between CREATE and TABLE then the table that is created
is only visible within the same database connection (SQLConnection
instance). It 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 the optional database-name prefix
is specified, then the table is created in a named database (a database
that was connected to the SQLConnection instance by calling the
attach() method with the specified database name). It is an error
to specify both a database-name prefix and the TEMP keyword, unless
the database-name prefix is temp. If no database name is specified,
and the TEMP keyword is not present, the table is created in the
main database (the database that was connected to the SQLConnection
instance using the open() or openAsync()method).
There are
no arbitrary limits on the number of columns or on the number of constraints
in a table. There is also no arbitrary limit on the amount of data
in a row.
The CREATE TABLE AS form defines the table as 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 the database ignores the CREATE TABLE command.
A table
can be removed using the DROP TABLE statement, and limited changes can
be made using the ALTER TABLE statement.
ALTER TABLE
The ALTER TABLE command
allows the user to rename or add a new column to an existing table.
It is not possible to remove a column from a table.
sql-statement ::= ALTER TABLE [database-name.] table-name alteration
alteration ::= RENAME TO new-table-name
alteration ::= ADD [COLUMN] column-def
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 they 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, they
are not automatically modified to use the new table name. If a renamed
table has associated views or triggers, you must manually drop and
recreate the triggers or view definitions using the new table 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 clause 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, the column must have
a default value other than NULL.
The execution
time of the ALTER TABLE statement is not affected by the amount of
data in the table.
DROP TABLE
The DROP TABLE statement removes
a table added with a CREATE TABLE statement. The table with the
specified table-name is the table that's dropped. It is completely
removed from the database and the disk file. The table cannot be recovered.
All indices associated with the table are also deleted.
sql-statement ::= DROP TABLE [IF EXISTS] [database-name.] table-name
By
default the DROP TABLE statement does not reduce the size of the
database file. Empty space in the database is retained and used
in subsequent INSERT operations. To remove free space in the database
use the SQLConnection.clean() method. If the autoClean parameter
is set to true when the database is initially created, the space
is freed automatically.
The optional IF EXISTS clause suppresses
the error that would normally result if the table does not exist.
CREATE INDEX
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 whose values are used for the index key.
sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name.] index-name
ON table-name ( column-name [, column-name]* )
column-name ::= name [COLLATE collation-name] [ASC | DESC]
Each
column name can be followed by ASC or DESC keywords to indicate
sort order, but the sort order designation is ignored by the runtime.
Sorting is always done in ascending order.
The COLLATE clause
following each column name defines a collating sequence used for
text values in that column. The default collation sequence is the
collation sequence defined for that column in the CREATE TABLE statement.
If no collation sequence is specified, the BINARY collation sequence
is used. For a definition of the COLLATE clause and collation functions
see COLLATE.
There are no arbitrary limits on the number of
indices that can be attached to a single table. There are also no
limits on the number of columns in an index.
DROP INDEX
The drop index statement removes
an index added with the CREATE INDEX statement. The specified index
is completely removed from the database file. The only way to recover
the index is to reenter the appropriate CREATE INDEX command.
sql-statement ::= DROP INDEX [IF EXISTS] [database-name.] index-name
By
default the DROP INDEX statement does not reduce the size of the
database file. Empty space in the database is retained and used
in subsequent INSERT operations. To remove free space in the database
use the SQLConnection.clean() method. If the autoClean parameter
is set to true when the database is initially created, the space
is freed automatically.
CREATE VIEW
The CREATE VIEW command assigns
a name to a pre-defined SELECT statement. This new name can then
be used in a FROM clause of another SELECT statement in place of
a table name. Views are commonly used to simplify queries by combining
a complex (and frequently used) set of data into a structure that
can be used in other operations.
sql-statement ::= CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.] view-name AS select-statement
If
the TEMP or TEMPORARY keyword occurs in between CREATE and VIEW
then the view that is created is only visible to the SQLConnection
instance that opened the database and is automatically deleted when
the database is closed.
If a [database-name] is specified
the view is created in the named database (a database that was connected
to the SQLConnection instance using the attach() method, with the
specified name argument. 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, the view
is created in the main database (the database that was connected
to the SQLConnection instance using the open() or openAsync() method).
Views
are read only. A DELETE, INSERT, or UPDATE statement cannot be used
on a view, unless at least one trigger of the associated type (INSTEAD
OF DELETE, INSTEAD OF INSERT, INSTEAD OF UPDATE) is defined. For
information on creating a trigger for a view, see CREATE TRIGGER.
A
view is removed from a database using the DROP VIEW statement.
DROP VIEW
The DROP VIEW statement removes
a view created by a CREATE VIEW statement.
sql-statement ::= DROP VIEW [IF EXISTS] view-name
The
specified view-name is the name of the view to drop. It is removed
from the database, but no data in the underlying tables is modified.
CREATE TRIGGER
The create trigger statement
is used to add triggers to the database schema. A trigger is a database
operation (the trigger-action) that is automatically performed when
a specified database event (the database-event) occurs.
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name
[BEFORE | AFTER] database-event
ON table-name
trigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name
INSTEAD OF database-event
ON view-name
trigger-action
database-event ::= DELETE |
INSERT |
UPDATE |
UPDATE OF column-list
trigger-action ::= [FOR EACH ROW] [WHEN expr]
BEGIN
trigger-step ;
[ trigger-step ; ]*
END
trigger-step ::= update-statement |
insert-statement |
delete-statement |
select-statement
column-list ::= column-name [, column-name]*
A
trigger is specified to fire whenever a DELETE, INSERT, or UPDATE
of a particular database table occurs, or whenever an UPDATE of
one or more specified columns of a table are updated. Triggers are
permanent unless the TEMP or TEMPORARY keyword is used. In that
case the trigger is removed when the SQLConnection instance's main
database connection is closed. If no timing is specified (BEFORE or
AFTER) the trigger defaults to BEFORE.
Only FOR EACH ROW triggers
are supported, so the FOR EACH ROW text is optional. With a FOR
EACH ROW trigger, the trigger-step statements are executed for each
database row being inserted, updated or deleted by the statement causing
the trigger to fire, if the WHEN clause expression evaluates to
true.
If a WHEN clause is supplied, the SQL statements specified
as trigger-steps 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.
Within the body of a trigger, (the trigger-action
clause) the pre-change and post-change values of the affected table
are available using the special table names OLD and NEW. The structure
of the OLD and NEW tables matches the structure of the table on
which the trigger is created. The OLD table contains any rows that are
modified or deleted by the triggering statement, in their state
before the triggering statement's operations. The NEW table contains
any rows that are modified or created by the triggering statement,
in their state after the triggering statement's operations. Both
the WHEN clause and the trigger-step statements can access values
from 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 with which the trigger is
associated. The availability of the OLD and NEW table references
depends on the type of database-event the trigger handles:
-
INSERT – NEW references are valid
-
UPDATE – NEW and OLD references are valid
-
DELETE – OLD references are valid
The specified
timing (BEFORE, AFTER, or INSTEAD OF) determines when the trigger-step
statements are 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 statement in a trigger-step. However,
if an ON CONFLICT clause is specified as part of the statement causing
the trigger to fire, then that conflict handling policy is used
instead.
In addition to table triggers, an INSTEAD OF trigger
can be created on a view. If one or more INSTEAD OF INSERT, INSTEAD
OF DELETE, or INSTEAD OF UPDATE triggers are defined on a view,
it is not considered an error to execute the associated type of
statement (INSERT, DELETE, or UPDATE) on the view. In that case,
executing an INSERT, DELETE or UPDATE on the view causes the associated triggers
to fire. Because the trigger is an INSTEAD OF trigger, the tables
underlying the view are not modified by the statement that causes
the trigger to fire. However, the triggers can be used to perform
modifying operations on the underlying tables.
There is an
important issue to keep in mind when creating a trigger on a table with
an INTEGER PRIMARY KEY column. If a BEFORE trigger modifies the
INTEGER PRIMARY KEY column of a row that is to be updated by the
statement that causes the trigger to fire, the update doesn't occur.
A workaround is to create the table with a PRIMARY KEY column instead
of an INTEGER PRIMARY KEY column.
A trigger can be removed
using the DROP TRIGGER statement. When a table or view is dropped,
all triggers associated with that table or view are automatically dropped
as well.
RAISE () function
A special SQL function
RAISE() can be used in a trigger-step statement of a trigger. This
function has the following syntax:
raise-function ::= RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )
When one of
the first three forms is called during trigger execution, the specified ON
CONFLICT processing action (ABORT, FAIL, or ROLLBACK) is performed
and the current statement's execution ends. The ROLLBACK is considered
a statement execution failure, so the SQLStatement instance whose
execute() method was being carried out dispatches an error (SQLErrorEvent.ERROR)
event. The SQLError object in the dispatched event object's error
property has its details property set to the error-message specified
in the RAISE() function.
When RAISE(IGNORE) is called, the
remainder of the current trigger, the statement that caused the
trigger to execute, and any subsequent triggers that would have
been executed are abandoned. No database changes are rolled back. If
the statement that caused the trigger to execute is itself part
of a trigger, that trigger program resumes execution at the beginning
of the next step. For more information about the conflict resolution
algorithms, see the section ON CONFLICT (conflict algorithms).
DROP TRIGGER
The DROP TRIGGER statement
removes a trigger created by the CREATE TRIGGER statement.
sql-statement ::= DROP TRIGGER [IF EXISTS] [database-name.] trigger-name
The
trigger is deleted from the database. Note that triggers are automatically dropped
when their associated table is dropped.
Special statements and clauses
This section describes several clauses that are extensions
to SQL provided by the runtime, as well as two language elements
that can be used in many statements, comments and expressions.
COLLATE
The COLLATE clause is used in
SELECT, CREATE TABLE, and CREATE INDEX statements to specify the
comparison algorithm that is used when comparing or sorting values.
sql-statement ::= COLLATE collation-name
collation-name ::= BINARY | NOCASE
The default collation
type for columns is BINARY. When BINARY collation is used with values
of the TEXT storage class, binary collation is performed by comparing the
bytes in memory that represent the value regardless of the text
encoding.
The NOCASE collation sequence is only applied for
values of the TEXT storage class. When used, the NOCASE collation
performs a case-insensitive comparison.
No collation sequence
is used for storage classes of type NULL, BLOB, INTEGER, or REAL.
To
use a collation type other than BINARY with a column, a COLLATE
clause must be specified as part of the column definition in the
CREATE TABLE statement. Whenever two TEXT values are compared, a
collation sequence is used to determine the results of the comparison
according to the following rules:
-
For binary comparison
operators, if either operand is a column, then the default collation
type of the column determines the collation sequence that is used
for the comparison. If both operands are columns, then the collation
type for the left operand determines the collation sequence used.
If neither operand is a column, then the BINARY collation sequence
is used.
-
The BETWEEN...AND operator is equivalent to using two expressions
with the >= and <= operators. For example, the expression
x BETWEEN y AND z is equivalent to x >= y AND x <= z. Consequently,
the BETWEEN...AND operator follows the preceding rule to determine
the collation sequence.
-
The IN operator behaves like the =operator for the purposes
of determining the collation sequence to use. For example, the collation
sequence used for the expressionx IN (y, z) is the default collation
type of x if x is a column. Otherwise, BINARY collation is used.
-
An ORDER BY clause that is part of a SELECT statement may
be explicitly assigned a collation sequence to be used for the sort
operation. In that case the explicit collation sequence is always
used. Otherwise, if the expression sorted by an ORDER BYclause is
a column, the default collation type of the column is used to determine
sort order. If the expression is not a column, the BINARY collation
sequence is used.
EXPLAIN
The EXPLAIN command modifier is
a non-standard extension to SQL.
sql-statement ::= EXPLAIN sql-statement
If
the EXPLAIN keyword appears before any other SQL statement, then
instead of actually executing the command, the result reports the
sequence of virtual machine instructions it would have used to execute
the command, had the EXPLAIN keyword not been present. The EXPLAIN
feature is an advanced feature and allows developers to change SQL
statement text in an attempt to optimize performance or debug a
statement that doesn't appear to be working properly.
ON CONFLICT (conflict algorithms)
The
ON CONFLICT clause is not a separate SQL command. It is a non-standard clause
that can appear in many other SQL commands.
conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-clause ::= OR conflict-algorithm
conflict-algorithm ::= ROLLBACK |
ABORT |
FAIL |
IGNORE |
REPLACE
The first form of
the ON CONFLICT clause, using the keywords ON CONFLICT, is used
in a CREATE TABLE statement. For an INSERT or UPDATE statement,
the second form is used, with ON CONFLICT replaced by OR to make
the syntax seem more natural. For example, instead of INSERT ON
CONFLICT IGNORE, the statement becomes INSERT OR IGNORE. Although
the keywords are different, the meaning of the clause is the same
in either form.
The ON CONFLICT clause specifies the algorithm
that is used to resolve constraint conflicts. The five algorithms
are ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm
is ABORT. The following is an explanation of the five conflict algorithms:
-
ROLLBACK
-
When a constraint violation occurs, an immediate ROLLBACK
occurs, ending the current transaction. The command aborts and the
SQLStatement instance dispatches an error event. If no transaction
is active (other than the implied transaction that is created on
every command) then this algorithm works the same as ABORT.
-
ABORT
-
When a constraint violation occurs, the command backs out
any prior changes it might have made and the SQLStatement instance
dispatches an error event. No ROLLBACK is executed, so changes from
prior commands within a transaction are preserved. ABORT is the
default behavior.
-
FAIL
-
When a constraint violation occurs, the command aborts and
the SQLStatement dispatches an error event. However, any changes
to the database that the statement made before encountering the
constraint violation are preserved and are not backed out. For example,
if an UPDATE statement encounters a constraint violation on the
100th row that it attempts to update, then the first 99 row changes
are preserved but changes to rows 100 and beyond don’t occur.
-
IGNORE
-
When a constraint violation occurs, the one row that contains
the constraint violation is not inserted or changed. Aside from
this row being ignored, the command continues executing normally.
Other rows before and after the row that contained the constraint
violation continue to be inserted or updated normally. No error
is returned.
-
REPLACE
-
When a UNIQUE constraint violation occurs, the pre-existing
rows that are causing the constraint violation are removed before
inserting or updating the current row. Consequently, the insert
or update always occurs, and the command continues executing normally.
No error is returned. If a NOT NULL constraint violation occurs,
the NULL value is replaced by the default value for that column. If
the column has no default value, then the ABORT algorithm is used.
If a CHECK constraint violation occurs then the IGNORE algorithm
is used. When this conflict resolution strategy deletes rows in
order to satisfy a constraint, it does not invoke delete triggers
on those rows.
The algorithm specified
in the OR clause of an INSERT or UPDATE statement overrides any
algorithm specified in a CREATE TABLE statement. If no algorithm
is specified in the CREATE TABLE statement or the executing INSERT
or UPDATE statement, the ABORT algorithm is used.
REINDEX
The REINDEX command is used to
delete and re-create one or more indices. This command is useful
when the definition of a collation sequence has changed.
sql-statement ::= REINDEX collation-name
sql-statement ::= REINDEX [database-name .] ( table-name | index-name )
In
the first form, all indices in all attached databases that use the
named collation sequence are recreated. In the second form, when
a table-name is specified, all indices associated with the table
are rebuilt. If an index-name is given, only the specified index
is deleted and recreated.
COMMENTS
Comments aren't SQL commands,
but they can occur in SQL queries. They are treated as white space
by the runtime. They can begin anywhere white space can be found,
including inside expressions that span multiple lines.
comment ::= single-line-comment |
block-comment
single-line-comment ::= -- single-line
block-comment ::= /* multiple-lines or block [*/]
A
single-line comment is indicated by two dashes. A single line comment
only extends to the end of the current line.
Block comments
can span any number of lines, or be embedded within a single line.
If there is no terminating delimiter, a block comment extends to
the end of the input. This situation is not treated as an error.
A new SQL statement can begin on a line after a block comment ends.
Block comments can be embedded anywhere white space can occur, including
inside expressions, and in the middle of other SQL statements. Block
comments do not nest. Single-line comments inside a block comment
are ignored.
EXPRESSIONS
Expressions are subcommands
within other SQL blocks. The following describes the valid syntax
for an expression within a SQL statement:
expr ::= expr binary-op expr |
expr [NOT] like-op expr [ESCAPE expr] |
unary-op expr |
( expr ) |
column-name |
table-name.column-name |
database-name.table-name.column-name |
literal-value |
parameter |
function-name( expr-list | * ) |
expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name.] table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END |
CAST ( expr AS type ) |
expr COLLATE collation-name
like-op ::= LIKE | GLOB
binary-op ::= see Operators
unary-op ::= see Operators
parameter ::= :param-name | @param-name | ?
value-list ::= literal-value [, literal-value]*
literal-value ::= literal-string | literal-number | literal-boolean | literal-blob | literal-null
literal-string ::= 'string value'
literal-number ::= integer | number
literal-boolean ::= true | false
literal-blob ::= X'string of hexadecimal data'
literal-null ::= NULL
An expression is any combination
of values and operators that can be resolved to a single value.
Expressions can be divided into two general types, according to whether
they resolve to a boolean (true or false) value or whether they
resolve to a non-boolean value.
In several common situations,
including in a WHERE clause, a HAVING clause, the ON expression
in a JOIN clause, and a CHECK expression, the expression must resolve
to a boolean value. The following types of expressions meet this condition:
Literal values
A literal numeric value
is written as an integer number or a floating point number. Scientific
notation is supported. The . (period) character is always used as
the decimal point.
A string literal is indicated by enclosing
the string in single quotes '. To include a single quote within
a string, put two single quotes in a row like this example: ''.
A
boolean literal is indicated by the value true or false. Literal
boolean values are used with the Boolean column data type.
A
BLOB literal is a string literal containing hexadecimal data and
proceeded by a single x or X character, such as X'53514697465'.
A
literal value can also be the token NULL.
Column name
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 random integer key (the "row key") 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. Row keys behave as read-only columns.
A row key can be used anywhere a regular column can be used, except
that you cannot change the value of a row key in an UPDATE or INSERT
statement. The SELECT * FROM table statement does not include the
row key in its result set.
SELECT statement
A SELECT statement can
appear in an expression as either the right-hand operand of the
IN operator, as a scalar quantity (a single result value), or as
the operand of an EXISTS operator. When used as a scalar quantity
or the operand of an IN operator, the SELECT can only have a single
column in its result. A compound SELECT statement (connected with
keywords like UNION or EXCEPT) is 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 the value in the containing query, then the expression is evaluated
once before any other processing and the result is reused as necessary.
If the SELECT expression does contain variables from the outer query,
known as a correlated subquery, then the SELECT is re-evaluated
every time it is needed.
When a SELECT is the right operand
of the IN operator, the IN operator returns TRUE if the result of
the left operand is equal to any of the values in the SELECT statement's
result set. 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 expression
A CAST expression changes
the data type of the value specified to the one given. The type
specified can be any non-empty type name that is valid for the type
in a column definition of a CREATE TABLE statement. See Data type
support for details.
Additional expression elements
The following
SQL elements can also be used in expressions:
Built-in functions
The built-in functions fall into three main categories:
-
Aggregate functions
-
Scalar functions
-
Date and time functions
In addition to these functions, there is a special function RAISE()
that is used to provide notification of an error in the execution
of a trigger. This function can only be used within the body of
a CREATE TRIGGER statement. For information on the RAISE() function,
see CREATE TRIGGER > RAISE().
Like all keywords in SQL, function names are not case sensitive.
Aggregate functions
Aggregate functions
perform operations on values from multiple rows. These functions
are primarily used in SELECT statements in conjunction with the GROUP
BY clause.
AVG(X)
|
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 even if all inputs are integers.
|
COUNT(X) COUNT(*)
|
The first form returns a count of the number
of times that X is not NULL in a group. The second form (with the
* argument) returns the total number of rows in the group.
|
MAX(X)
|
Returns the maximum value of all values
in the group. The usual sort order is used to determine the maximum.
|
MIN(X)
|
Returns the minimum non-NULL value of all
values in the group. The usual sort order is used to determine the
minimum. If all values in the group are NULL, NULL is returned.
|
SUM(X)
TOTAL(X)
|
Returns the numeric sum of all non-NULL
values in the group. If all of the values are NULL then SUM() returns
NULL, and TOTAL() returns 0.0. 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 not an
integer and not NULL then SUM() returns a floating point value.
This value might be an approximation to the true sum.
|
In any of the preceding aggregate functions
that take a single argument, that argument can be preceded by the
keyword DISTINCT. In that case, duplicate elements are filtered
before being passed into the aggregate function. For example, the
function call COUNT(DISTINCT x) returns the number of distinct values
of column X instead of the total number of non-NULL values in column
x.
Scalar functions
Scalar functions operate
on values one row at a time.
ABS(X)
|
Returns the absolute value of argument X.
|
COALESCE(X, Y, ...)
|
Returns a copy of the first non-NULL argument.
If all arguments are NULL then NULL is returned. There must be at
least two arguments.
|
GLOB(X, Y)
|
This function is used to implement the X
GLOB Y syntax.
|
IFNULL(X, Y)
|
Returns a copy of the first non-NULL argument.
If both arguments are NULL then NULL is returned. This function
behaves the same as COALESCE().
|
HEX(X)
|
The argument is interpreted as a value of
the BLOB storage type. The result is a hexadecimal rendering of
the content of that value.
|
LAST_INSERT_ROWID()
|
Returns the row identifier
(generated primary key) of the last row inserted to the database
through the current SQLConnection. This value is the same as the value
returned by the
SQLConnection.lastInsertRowID
property.
|
LENGTH(X)
|
Returns the string length of X in characters.
|
LIKE(X, Y [, Z])
|
This function is used to implement the X
LIKE Y [ESCAPE Z] syntax of SQL. If the optional ESCAPE clause is
present, then the function is invoked with three arguments. Otherwise,
it is invoked with two arguments only.
|
LOWER(X)
|
Returns a copy of string X with all characters
converted to lower case.
|
LTRIM(X) LTRIM(X, Y)
|
Returns a string formed by removing spaces
from the left side of X. If a Y argument is specified, the function
removes any of the characters in Y from the left side of X.
|
MAX(X, Y, ...)
|
Returns the argument with the maximum value.
Arguments may be strings in addition to numbers. The maximum value
is determined by the defined sort order. Note that MAX() is a simple function
when it has 2 or more arguments but is an aggregate function when
it has a single argument.
|
MIN(X, Y, ...)
|
Returns the argument with the minimum value.
Arguments may be strings in addition to numbers. The minimum value
is determined by the defined sort order. Note that MIN() is a simple function
when it has 2 or more arguments but is an aggregate function when
it has a single argument.
|
NULLIF(X, Y)
|
Returns the first argument if the arguments
are different, otherwise returns NULL.
|
QUOTE(X)
|
This routine 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. BLOB storage classes are encoded as hexadecimal literals.
The function is useful when writing triggers to implement undo/redo
functionality.
|
RANDOM(*)
|
Returns a pseudo-random integer between
-9223372036854775808 and 9223372036854775807. This random value
is not crypto-strong.
|
RANDOMBLOB(N)
|
Returns an N-byte BLOB containing pseudo-random
bytes. N should be a positive integer. This random value is not
crypto-strong. If the value of N is negative a single byte is returned.
|
ROUND(X) ROUND(X, Y)
|
Rounds off the number X to Y digits to the
right of the decimal point. If the Y argument is omitted, 0 is used.
|
RTRIM(X) RTRIM(X, Y)
|
Returns a string formed by removing spaces
from the right side of X. If a Y argument is specified, the function
removes any of the characters in Y from the right side of X.
|
SUBSTR(X, Y, Z)
|
Returns a substring of input string X that
begins with the Y-th character and which is Z characters long. The
left-most character of X is index position 1. If Y is negative the
first character of the substring is found by counting from the right
rather than the left.
|
TRIM(X) TRIM(X, Y)
|
Returns a string formed by removing spaces
from the right side of X. If a Y argument is specified, the function
removes any of the characters in Y from the right side of X.
|
TYPEOF(X)
|
Returns the type of the expression X. The
possible return values are 'null', 'integer', 'real', 'text', and
'blob'. For more information on data types see Data type support.
|
UPPER(X)
|
Returns a copy of input string X converted
to all upper-case letters.
|
ZEROBLOB(N)
|
Returns a BLOB containing N bytes of 0x00.
|
Date and time formatting functions
The
date and time formatting functions are a group of scalar functions
that are used to create formatted date and time data. Note that
these functions operate on and return string and number values.
These functions are not intended to be used with the DATE data type.
If you use these functions on data in a column whose declared data
type is DATE, they do not behave as expected.
DATE(T, ...)
|
The DATE() function returns a string containing
the date in this format: YYYY-MM-DD. The first parameter (T) specifies
a time string of the format found under Time formats. Any number
of modifiers can be specified after the time string. The modifiers
can be found under Modifiers.
|
TIME(T, ...)
|
The TIME() function returns a string containing
the time as HH:MM:SS. The first parameter (T) specifies a time string
of the format found under Time formats. Any number of modifiers
can be specified after the time string. The modifiers can be found
under Modifiers.
|
DATETIME(T, ...)
|
The DATETIME() function returns a string
containing the date and time in YYYY-MM-DD HH:MM:SS format. The
first parameter (T) specifies a time string of the format found
under Time formats. Any number of modifiers can be specified after
the time string. The modifiers can be found under Modifiers.
|
JULIANDAY(T, ...)
|
The JULIANDAY() function returns a number
indicating the number of days since noon in Greenwich on November
24, 4714 B.C. and the provided date. The first parameter (T) specifies a
time string of the format found under Time formats. Any number of
modifiers can be specified after the time string. The modifiers
can be found under Modifiers.
|
STRFTIME(F, T, ...)
|
The STRFTIME() routine returns the date
formatted according to the format string specified as the first
argument F. The format string supports the following substitutions:
%d
- day of month
%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 (sunday = 0)
%W
- week of year 00-53
%Y - year 0000-9999
%% - %
The
second parameter (T) specifies a time string of the format found
under Time formats. Any number of modifiers can be specified after
the time string. The modifiers can be found under Modifiers.
|
Time formats
A time string can be in any
of the following formats:
YYYY-MM-DD
|
2007-06-15
|
YYYY-MM-DD HH:MM
|
2007-06-15 07:30
|
YYYY-MM-DD HH:MM:SS
|
2007-06-15 07:30:59
|
YYYY-MM-DD HH:MM:SS.SSS
|
2007-06-15 07:30:59.152
|
YYYY-MM-DDTHH:MM
|
2007-06-15T07:30
|
YYYY-MM-DDTHH:MM:SS
|
2007-06-15T07:30:59
|
YYYY-MM-DDTHH:MM:SS.SSS
|
2007-06-15T07:30:59.152
|
HH:MM
|
07:30 (date is 2000-01-01)
|
HH:MM:SS
|
07:30:59 (date is 2000-01-01)
|
HH:MM:SS.SSS
|
07:30:59:152 (date is 2000-01-01)
|
now
|
Current date and time in Universal Coordinated
Time.
|
DDDD.DDDD
|
Julian day number as a floating-point number.
|
The character T in these formats is
a literal character "T" separating the date and the time. Formats
that only include a time assume the date 2001-01-01.
Modifiers
The time string can be followed
by zero or more modifiers that alter the date or alter the interpretation
of the date. The available modifiers are as follows:
NNN days
|
Number of days to add to the time.
|
NNN hours
|
Number of hours to add to the time.
|
NNN minutes
|
Number of minutes to add to the time.
|
NNN.NNNN seconds
|
Number of seconds and milliseconds to add
to the time.
|
NNN months
|
Number of months to add to the time.
|
NNN years
|
Number of years to add to the time.
|
start of month
|
Shift time backwards to the start of the
month.
|
start of year
|
Shift time backwards to the start of the
year.
|
start of day
|
Shift time backwards to the start of the
day.
|
weekday N
|
Forwards the time to the specified weekday.
(0 = Sunday, 1 = Monday, and so forth).
|
localtime
|
Converts the date to local time.
|
utc
|
Converts the date to Universal Coordinated
Time.
|
Operators
SQL supports a large selection of operators, including
common operators that exist in most programming languages, as well
as several operators that are unique to SQL.
Common operators
The following binary
operators are allowed in a SQL block and are listed in order from
highest to lowest precedence:
* / %
+ -
<< >> & |
< >= > >=
= == != <> IN
AND
OR
Supported unary prefix operators are:
! ~ 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.
Note
that there are two variations of the equals and not equals operators.
Equals can be either = or ==. The not-equals operator can be either
!= or <>.
The || operator is the string concatenation
operator—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.
SQL operators
LIKE
The LIKE
operator does a pattern matching comparison.
expr ::= (column-name | expr) LIKE pattern
pattern ::= '[ string | % | _ ]'
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 pattern is a wildcard character—it matches any
sequence of zero or more characters in the string. An underscore
(_) in the pattern matches any single character in the string. Any
other character matches itself or its lower/upper case equivalent,
that is, matches are performed in a case-insensitive manner. (Note: the
database engine only understands upper/lower case for 7-bit Latin characters.
Consequently, the LIKE operator is case sensitive for 8-bit iso8859 characters
or UTF-8 characters. For example, the expression 'a' LIKE 'A' is
TRUE but 'æ' LIKE 'Æ' is FALSE). Case sensitivity for Latin characters
can be changed using the SQLConnection.caseSensitiveLike property.
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 match
literal percent or underscore characters. The escape character followed
by a percent symbol, underscore or itself matches a literal percent
symbol, underscore or escape character in the string, respectively.
GLOB
The
GLOB operator is similar to LIKE but uses the Unix file globbing
syntax for its wildcards. Unlike LIKE, GLOB is case sensitive.
IN
The
IN operator calculates whether its left operand is equal to one
of the values in its right operand (a set of values in parentheses).
in-expr ::= expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name.] table-name
value-list ::= literal-value [, literal-value]*
The
right operand can be a set of comma-separated literal values, or
it can be the result of a SELECT statement. See SELECT statements
in expressions for an explanation and limitations on using a SELECT
statement as the right-hand operand of the IN operator.
BETWEEN...AND
The
BETWEEN...AND operator is equivalent to using two expressions with
the >= and <= operators. For example, the expression x BETWEEN
y AND z is equivalent to x >= y AND x <= z.
NOT
The
NOT operator is a negation operator. The GLOB, LIKE, and IN operators
may be preceded by the NOT keyword to invert the sense of the test
(in other words, to check that a value does not match the indicated
pattern).
Parameters
A parameter specifies a placeholder in the expression for
a literal value that is filled in at runtime by assigning a value
to the SQLStatement.parameters associative array. Parameters can
take three forms:
?
|
A question mark indicates an indexed parameter.
Parameters are assigned numerical (zero-based) index values according
to their order in the statement.
|
:AAAA
|
A colon followed by an identifier name holds
a spot for a named parameter with the name AAAA. Named parameters
are also numbered according to their order in the SQL statement.
To avoid confusion, it is best to avoid mixing named and numbered
parameters.
|
@AAAA
|
An "at sign" is equivalent to a colon.
|
Unsupported SQL features
The following is a list of the standard SQL elements that
are not supported in Adobe AIR:
-
FOREIGN KEY constraints
-
FOREIGN KEY constraints are parsed but are not enforced.
-
Triggers
-
FOR EACH STATEMENT triggers are not supported (all triggers
must be FOR EACH ROW). INSTEAD OF triggers are not supported on
tables (INSTEAD OF triggers are only allowed on views). Recursive
triggers—triggers that trigger themselves—are not supported.
-
ALTER TABLE
-
Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE
command are supported. Other kinds of ALTER TABLE operations such
as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are ignored.
-
Nested transactions
-
Only a single active transaction is allowed.
-
RIGHT and FULL OUTER JOIN
-
RIGHT OUTER JOIN or FULL OUTER JOIN are not supported.
-
Updateable VIEW
-
A view is read only. You may not execute a DELETE, INSERT,
or UPDATE statement on a view. An INSTEAD OF trigger that fires
on an attempt to DELETE, INSERT, or UPDATE a view is supported and
can be used to update supporting tables in the body of the trigger.
-
GRANT and REVOKE
-
A database is an ordinary disk file; the only access permissions
that can be applied are the normal file access permissions of the
underlying operating system. The GRANT and REVOKE commands commonly
found on client/server RDBMSes are not implemented.
The following SQL elements and SQLite features are supported
in some SQLite implementations, but are not supported in Adobe AIR.
Most of this functionality is available through methods of the SQLConnection
class:
-
Transaction-related SQL elements (BEGIN,
END, COMMIT, ROLLBACK)
-
This functionality is available through the transaction-related
methods of the SQLConnection class: SQLConnection.begin(), SQLConnection.commit(),
and SQLConnection.rollback().
-
ANALYZE
-
This functionality is available through the SQLConnection.analyze() method.
-
ATTACH
-
This functionality is available through the SQLConnection.attach() method.
-
COPY
-
This statement is not supported.
-
CREATE VIRTUAL TABLE
-
This statement is not supported.
-
DETACH
-
This functionality is available through the SQLConnection.detach() method.
-
PRAGMA
-
This statement is not supported.
-
VACUUM
-
This functionality is available through the SQLConnection.compact() method.
-
System table access is not available
-
The system tables including sqlite_master and other tables
with the "sqlite_" prefix are not available in SQL statements. The
runtime includes a schema API that provides an object-oriented way
to access schema data. For more information see the SQLConnection.loadSchema()
method.
-
Regular-expression functions (MATCH() and REGEX())
-
These functions are not available in SQL statements.
The following functionality differs between many SQLite implementations
and Adobe AIR:
-
Indexed statement parameters
-
In many implementations indexed statement parameters are
one-based. However, in Adobe AIR indexed statement parameters are
zero-based (that is, the first parameter is given the index 0, the
second parameter is given the index 1, and so forth.
-
INTEGER PRIMARY KEY column definitions
-
In many implementations, only columns that are defined exactly
as INTEGER PRIMARY KEY are used as the actual primary key column
for a table. In those implementations, using another data type that
is usually a synonym for INTEGER (such as int) does not cause the column
to be used as the internal primary key. However, in Adobe AIR, the
int data type (and other INTEGER synonyms) are considered exactly
equivalent to INTEGER. Consequently, a column defined as int PRIMARY
KEY is used as the internal primary key for a table. For more information,
see the sections CREATE TABLE and Column affinity.
Additional SQL features
The following column affinity types are not supported by
default in SQLite, but are supported in Adobe AIR (Note that, like
all keywords in SQL, these data type names are not case-sensitive):
-
Boolean
-
corresponding to the Boolean class.
-
Date
-
corresponding to the Date class.
-
int
-
corresponding to the int class (equivalent to the INTEGER
column affinity).
-
Number
-
corresponding to the Number class (equivalent to the REAL
column affinity).
-
Object
-
corresponding to the Object class or any subclass that can
be serialized and deserialized using AMF3. (This includes most classes
including custom classes, but excludes some classes including display
objects and objects that include display objects as properties.)
-
String
-
corresponding to the String class (equivalent to the TEXT
column affinity).
-
XML
-
corresponding to the ActionScript (E4X) XML class.
-
XMLList
-
corresponding to the ActionScript (E4X) XMLList class.
The following literal values are not supported by default in
SQLite, but are supported in Adobe AIR:
-
true
-
used to represent the literal boolean value true, for working
with BOOLEAN columns.
-
false
-
used to represent the literal boolean value false, for working
with BOOLEAN columns.
|
|
|