Поддерживаемый синтаксис SQL

SELECT

Инструкция SELECT используется для запроса информации в базе данных. В качестве ответа на SELECT возвращается 0 или больше строк данных, где каждая строка содержит фиксированное число столбцов. Число столбцов в результате определяется по списку имен столбцов или выражений в результате, который находится между SELECT и необязательными ключевыми словами FROM.

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

В качестве результата может использоваться произвольное выражение. Если результирующим выражением является символ «*», то все столбцы всех таблиц заменяются одним этим выражением. Если выражение содержит имя таблицы после «.*»,то результатом будут все столбцы в этой таблице.

Ключевое слово DISTINCT возвращает подмножество строк, каждая из которых отлична от остальных. Значения NULL не считаются отличными друг от друга. По умолчанию возвращаются все строки результата, этого также можно достичь при помощи ключевого слова ALL.

Запрос выполняется по одной или нескольким таблицам, имена которых следуют за ключевым словом FROM. Если несколько имен таблиц разделены запятыми, то при выполнении запроса используется перекрестное объединение различных таблиц. Чтобы определить способ объединения таблиц, также можно использовать синтаксис JOIN. Единственными поддерживаемым типом внешнего объединения является LEFT OUTER JOIN. Выражение предложения ON в join-args должно разрешаться в логическое значение. Подзапрос в круглых скобках может использоваться как таблица в предложении FROM. Предложение FROM можно полностью опустить. В таком случае возвращается одна строка, содержащая значения результирующего списка выражений.

Предложение WHERE служит для ограничения числа строк, извлекаемых с помощью запроса. Выражения предложения WHERE должны разрешаться в логическое значение. Фильтрация с помощью предложения WHERE выполняется перед группировкой, потому выражения WHERE не могут включать агрегатные функции.

Предложения GROUP BY служат для объединения одной или нескольких строк результата в одну строку вывода. Предложение GROUP BY особенно полезно, когда результат содержит агрегатные функции. Выражения в приложении GROUP BY могут не входить в список выражений, содержащийся в инструкции SELECT.

Предложение HAVING похоже на WHERE тем, что оно ограничивает число строк, возвращаемых инструкцией. Однако предложение HAVING применяется после выполнения группировки, определенной предложением GROUP BY. По этой причине выражение HAVING может применяться к значениям, которые включают агрегатные функции. Выражения предложения HAVING могут не входить в список SELECT. Как и WHERE, выражение HAVING должно разрешаться в логическое значение.

Предложение ORDER BY обеспечивает сортировку строк вывода. Аргумент sort-expr-list, передаваемый в предложении ORDER BY, представляет собой список выражений, используемых в качестве ключа для сортировки. Выражения могут не входить в результат простой инструкции SELECT, но в составной инструкции SELECT (в которой используется один из операторов compound-op) каждое выражение сортировки должно точно соответствовать одному из столбцов результата. За каждым аргументом sort может следовать необязательное предложение порядка сортировки sort-order, которое содержит ключевое слово COLLATE и имя функции сортировки, используемое для упорядочения текста, и/или ключевое слово ASC или DESC, указывающее порядок сортировки (по возрастанию или по убыванию). Аргумент sort-order может быть опущен, в этом случае будет использоваться порядок сортировки по умолчанию (по возрастанию). Определение предложения COLLATE и функций сортировки см. в разделе «COLLATE».

Предложение LIMIT определяет верхний предел для числа строк, возвращаемых в результате. Отрицательное значение LIMIT указывает на то, что верхний предел не установлен. Необязательное предложение OFFSET после LIMIT указывает, сколько строк следует пропустить в начале набора результатов. В составном запросе SELECT предложение LIMIT может следовать только за последней инструкцией SELECT, при этом ограничение применяется ко всему запросу. Обратите внимание, что если в предложении LIMIT используется ключевое слово OFFSET, то ограничением является первое целое число, а смещением — второе. Если вместо ключевого слова OFFSET стоит запятая, то смещением является первое число, а ограничением — второе. Такое кажущееся противоречие является намеренным: оно позволяет обеспечить максимальную совместимость с устаревшими системами баз данных SQL.

Составная инструкция SELECT образуется из двух или нескольких простых инструкций SELECT, объединенных с помощью одного из операторов UNION, UNION ALL, INTERSECT или EXCEPT. Во всех инструкциях SELECT, которые входят в составную инструкцию SELECT, должно быть указано одинаковое число столбцов результата. Может использоваться только одно предложение ORDER BY, оно должно следовать за последней инструкцией SELECT (и перед единственным предложением LIMIT, если оно имеется). Операторы UNION и UNION ALL объединяют результаты предшествующей и последующей инструкций SELECT в одну таблицу. Разница между ними заключается в том, что при использовании UNION все строки результата являются уникальными, а при использовании UNION ALL могут встречаться повторения. Оператор INTERSECT возвращает результаты, совпавшие в предшествующей и последующей инструкциях SELECT. Оператор EXCEPT возвращает результат предшествующей инструкции SELECT после удаления из него результата последующей инструкции SELECT. Когда объединяется три и более инструкции SELECT, группировка выполняется начиная с первой и кончая последней.

Определения допустимых выражений см. в разделе «Выражения».

Начиная с версии AIR 2.5, оператор SQL CAST поддерживается при считывании данных BLOB для их преобразования в объекты ActionScript ByteArray. Например, следующий код считывает необработанные данные, которые не сохранены в формате AMF, и сохраняет их в объекте ByteArray:

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

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

Первая форма (с ключевым словом VALUES) создает новую строку в существующей таблице. Если аргумент column-list не определен, то число значений должно соответствовать числу столбцов в таблице. Если аргумент column-list определен, то число значений должно соответствовать числу указанных столбцов. Столбцы таблицы, которые не отображаются в списке столбцов, заполняются значением по умолчанию, определенным при создании таблицы, или значением NULL, если значение по умолчанию не определено.

Вторая форма инструкции INSERT получает свои данные из инструкции SELECT. Если аргумент column-list не определен, число столбцов, полученное в результате SELECT, должно точно соответствовать числу столбцов в таблице; в противном случае оно должно соответствовать числу столбцов, перечисленных в аргументе column-list. Для каждой строки результата SELECT создается новая запись в таблице. Инструкция SELECT может быть простой или составной. Определения разрешенных инструкций SELECT см. в разделе «SELECT».

Необязательный аргумент conflict-algorithm позволяет определить альтернативный алгоритм разрешения конфликтов между ограничениями, который должен использоваться при выполнении данной команды. Описание и определение алгоритмов конфликтов см. в разделе «Специальные инструкции и предложения».

Две формы REPLACE INTO инструкции равнозначны использованию стандартной формы INSERT [OR conflict-algorithm] с алгоритмом решения конфликтов REPLACE (то есть формы INSERT OR REPLACE...).

Две формы REPLACE INTO инструкции равнозначны использованию стандартной формы INSERT [OR conflict-algorithm] с алгоритмом решения конфликтов REPLACE (то есть формы INSERT OR REPLACE...).

UPDATE

Команда update изменяет существующие записи в таблице.

sql-statement  ::=  UPDATE [database-name.] table-name SET column1=value1, column2=value2,... [WHERE expr]

Команда состоит из ключевого слова UPDATE, а за ним следует имя таблицы, записи в которой требуется обновить. После ключевого слова SET укажите название столбца и значение, на которое будет изменено значение в столбце, в виде списка с разделением запятой. Выражение предложения WHERE указывает строку или строки, записи в которых будут обновлены.

DELETE

sql-statement  ::=  DELETE FROM [database-name.] table-name [WHERE expr]

Команда состоит из ключевых слов DELETE FROM, а за ними следует имя таблицы, из которой требуется удалить записи.

Если отсутствует предложение WHERE, удаляются все строки таблицы. Если имеется предложение WHERE, то удаляются только строки, соответствующие выражению. Выражение предложения WHERE должно разрешаться в логическое значение. Определения допустимых выражений см. в разделе «Выражения».

CREATE TABLE

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]*

Каждое определение столбца содержит имя столбца, за которым следует тип данных и одно или несколько необязательных ограничений. Тип данных ограничивает данные, которые могут храниться в данном столбце. При попытке сохранить в столбце значение другого типа среда выполнения по возможности преобразует значение в соответствующий тип и выдает ошибку, если это не удается. Дополнительные сведения см. в разделе «Поддержка типов данных».

Ограничение столбца NOT NULL указывает, что столбец не может содержать значения NULL.

Ограничение UNIQUE создает индекс для указанных столбцов. Этот индекс должен содержать уникальные ключи: любые две строки не должны содержать повторяющихся значений или комбинаций значений в указанных столбцах. Инструкция CREATE TABLE может иметь несколько ограничений UNIQUE, включая несколько столбцов, в определении которых есть ограничение UNIQUE, и/или несколько ограничений UNIQUE на уровне таблицы.

Ограничение CHECK определяет выражение, которое должно вычисляться и быть верным (true), чтобы можно было вставить или обновить данные строки. Выражение CHECK должно разрешаться в логическое значение.

Предложение COLLATE в определении столбца указывает, какую функцию сортировки текста следует использовать при сравнении текстовых записей в столбце. По умолчанию используется функция сортировки BINARY. Сведения о предложении COLLATE и функциях сортировки см. в разделе «COLLATE».

Ограничение DEFAULT определяет значение по умолчанию, которое должно использоваться при выполнении инструкции INSERT. В качестве значения может использоваться NULL, строковая константа или число. В качестве значения по умолчанию может также использоваться одно специальных из ключевых слов (без учета регистра): CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP. Если используется значение NULL, строковая константа или число, оно буквально вставляется в столбец каждый раз, когда в инструкции INSERT не определено значение для столбца. Если используется значение CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP, то в столбец вставляется текущая дата и/или время UTC. Для CURRENT_TIME используется формат HH:MM:SS (ЧЧ:ММ:СС). Для CURRENT_DATE используется формат YYYY-MM-DD (ГГГГ-ММ-ДД). Для CURRENT_TIMESTAMP используется формат YYYY-MM-DD HH:MM:SS (ГГГГ-ММ-ДД ЧЧ:ММ:СС.)

При указании ограничения PRIMARY KEY обычно просто создается индекс UNIQUE для соответствующих столбцов. Однако если ограничение PRIMARY KEY применяется только к одному столбцу с типом данных INTEGER (или если указан любой синоним, такой как int), то этот столбец используется базой данных как действительный первичный ключ для таблицы. Это означает, что данный столбец может содержать только уникальные целочисленные значения. (Обратите внимание, что во многих случаях внедрения SQLite только тип данных столбца INTEGER определяет функцию столбца как внутреннего первичного ключа, но в Adobe AIR это поведение также определяется синонимами INTEGER, такими как int.)

Если в таблице нет столбца INTEGER PRIMARY KEY, целочисленный ключ автоматически создается при вставке каждой строки. Первичный ключ для строки можно всегда получить с помощью одного из специальных имен: ROWID, OID или _ROWID_. Эти имена можно использовать независимо от того, объявляются они явно как INTEGER PRIMARY KEY или генерируются как внутреннее значение. Однако если в таблице явно объявлен столбец INTEGER PRIMARY KEY, имя столбца в данных результата соответствует действительному имени, а не специальному.

Столбец INTEGER PRIMARY KEY также может содержать ключевое слово AUTOINCREMENT. При использовании ключевого слова AUTOINCREMENT база данных автоматически генерирует и вставляет последовательно увеличиваемый целочисленный ключ в столбце INTEGER PRIMARY KEY, когда выполняется инструкция INSERT, в которой не указано явного значения для столбца.

В инструкции CREATE TABLE может быть только одно ограничение PRIMARY KEY. Оно может входить в состав определения одного столбца или быть единственным ограничением PRIMARY KEY на уровне таблицы. К столбцу первичного ключа применяется неявное ограничение NOT NULL.

Необязательный аргумент conflict-clause, следующий за ограничением, позволяет указать для данного ограничения альтернативный алгоритм решения конфликтов между ограничениями по умолчанию. По умолчанию используется значение ABORT. Для всех ограничений в одной таблице можно определить разные алгоритмы решения конфликтов по умолчанию. Если в инструкции INSERT или UPDATE указан другой алгоритм решения конфликтов, то он используется вместо алгоритма, определенного в инструкции CREATE TABLE. Дополнительные сведения см. в подразделе ON CONFLICT раздела «Специальные инструкции и предложения».

Дополнительные ограничения, такие как FOREIGN KEY, не приводят к появлению ошибки, но среда выполнения их игнорирует.

Если между ключевыми словами CREATE и TABLE встречается слово TEMP или TEMPORARY, то создаваемая таблица будет отображаться только в рамках текущего подключения к базе данных (экземпляра SQLConnection). Она автоматически удаляется после закрытия подключения к базе данных. Все индексы, созданные для временной таблицы, также являются временными. Временные таблицы и индексы хранятся в отдельном файле, а не в главном файле базы данных.

Если указан необязательный префикс имени базы данных database-name, то таблица создается в именованной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода attach() с указанием имени базы данных). Нельзя одновременно использовать префикс database-name и ключевое слово TEMP, если префикс database-name не является temp. Если имя базы данных не указано и не используется ключевое слово TEMP, то таблица создается в главной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода open() или openAsync()).

Не существует произвольных пределов для числа столбцов или ограничений в таблице. Также не установлено произвольных пределов для объема данных в строке.

Форма CREATE TABLE AS определяет таблицу как набор результатов запроса. Имена столбцов таблицы соответствуют именам столбцов в результате.

Если присутствует необязательное предложение IF NOT EXISTS и существует другая таблица с тем же именем, то база данных игнорирует команду CREATE TABLE.

Таблицу можно удалить с помощью инструкции DROP TABLE, а с помощью инструкции ALTER TABLE можно внести ряд изменений.

ALTER TABLE

Команда ALTER TABLE дает пользователю возможность переименовать или добавить столбец в существующей таблице. Удалить столбец из таблицы невозможно.

sql-statement ::= ALTER TABLE [database-name.] table-name alteration 
alteration    ::= RENAME TO new-table-name 
alteration    ::= ADD [COLUMN] column-def

Синтаксис RENAME TO можно использовать, чтобы присвоить таблице с именем, определенном параметром [database-name.] в аргументе table-name, новое имя, переданное в аргументе new-table-name. Эту команду нельзя использовать для перемещения таблицы из одной подключенной базы данных в другую. Она предназначена только для переименования таблицы в рамках одной базы данных.

Если в переименовываемой таблице есть триггеры или индексы, они останутся подключенными к таблице после ее переименования. Однако, если существуют определения или инструкции видов, которые выполняются триггерами, связанными с переименовываемой таблицей, они не изменяются автоматически для использования нового имени таблицы. Если переименованная таблица имеет связанные виды или триггеры, необходимо вручную удалить и воссоздать эти определения видов или триггеры, используя новое имя таблицы.

Синтаксис ADD [COLUMN] служит для добавления нового столбца в существующую таблицу. Новый столбец всегда добавляется в конце списка существующих столбцов. Предложение column-def может принимать любые формы, допустимые в инструкции CREATE TABLE, с учетом следующих ограничений.

• Столбец не должен иметь ограничения PRIMARY KEY или UNIQUE.

• Столбец не должен иметь значения по умолчанию CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP.

• Если указано ограничение NOT NULL, столбец не может иметь значения по умолчанию NULL.

На время выполнения инструкции ALTER TABLE не влияет объем данных в таблице.

DROP TABLE

sql-statement  ::=  DROP TABLE [IF EXISTS] [database-name.] table-name

По умолчанию инструкция DROP TABLE не уменьшает размер файла базы данных. Пустое место в базе данных сохраняется и используется при последующих операциях INSERT. Чтобы удалить свободное место из базы данных, используйте метод SQLConnection.clean(). Если при создании базы данных параметру autoClean задается значение true, место освобождается автоматически.

Необязательное предложение IF EXISTS подавляет появление ошибки, которая обычно выдается, если указанная таблица не существует.

CREATE INDEX

Команда CREATE INDEX содержит ключевые слова CREATE INDEX, за которыми следуют имя нового индекса, ключевое слово ON, имя созданной ранее таблицы, которую требуется проиндексировать, и заключенный в круглые скобки список имен столбцов в таблице, значения которых используются для ключа индекса.

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]

После каждого имени столбца может следовать ключевое слово ASC или DESC, которое указывает порядок сортировки, но во время выполнения этот порядок игнорируется. Сортировка всегда осуществляется в порядке возрастания.

Предложение COLLATE после каждого имени столбца определяет последовательность сортировки, которая используется для текстовых значений в этом столбце. По умолчанию используется последовательность сортировки, определенная для данного столбца в инструкции CREATE TABLE. Если последовательность сортировки не указана, используется значение BINARY. Определение предложения COLLATE и функций сортировки см. в разделе «COLLATE».

Не существует произвольных ограничений для числа индексов, которые можно подключить к одной таблице. Также не установлено ограничений для числа столбцов в индексе.

DROP INDEX

Инструкция DROP INDEX удаляет индекс, добавленный с помощью инструкции CREATE INDEX. Указанный индекс полностью удаляется из файла базы данных. Восстановить индекс можно только путем повторного ввода соответствующей команды CREATE INDEX.

sql-statement ::= DROP INDEX [IF EXISTS] [database-name.] index-name

По умолчанию инструкция DROP INDEX не уменьшает размер файла базы данных. Пустое место в базе данных сохраняется и используется при последующих операциях INSERT. Чтобы удалить свободное место из базы данных, используйте метод SQLConnection.clean(). Если при создании базы данных параметру autoClean было задано значение true, место освобождается автоматически.

CREATE VIEW

Команда CREATE VIEW присваивает имя предварительно определенной инструкции SELECT. Это новое имя можно использовать в предложении FROM другой инструкции SELECT вместо имени таблицы. Виды часто применяются для упрощения запросов путем объединения сложных (и часто вызываемых) наборов данных в структуру, которую можно использовать в других операциях.

sql-statement ::= CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.] view-name AS select-statement

Если между ключевыми словами CREATE и VIEW встречается слово TEMP или TEMPORARY, то создаваемый вид будет отображаться только для текущего экземпляра SQLConnection, с помощью которого была открыта база данных. Он автоматически удаляется после закрытия базы данных.

Если указан префикс [database-name], вид создается в названной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода attach() с указанием имени базы данных). Нельзя одновременно использовать префикс [database-name] и ключевое слово TEMP, если префикс [database-name] не является временным. Если имя базы данных не указано и не используется ключевое слово TEMP, то вид создается в главной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода open() или openAsync()).

Виды доступны только для чтения. Для вида нельзя использовать инструкцию DELETE, INSERT или UPDATE, если определен хотя бы один триггер связанного типа (INSTEAD OF DELETE, INSTEAD OF INSERT, INSTEAD OF UPDATE). Сведения о создании триггера для вида см. в разделе «CREATE TRIGGER».

Вид удаляется из базы данных с помощью инструкции DROP VIEW.

DROP VIEW

Инструкция DROP VIEW удаляет вид, созданный с помощью инструкции CREATE VIEW.

sql-statement ::= DROP VIEW [IF EXISTS] view-name

Удаляется вид, имя которого указано в аргументе view-name. Вид удаляется из базы данных, но данные в самих таблицах остаются без изменений.

CREATE TRIGGER

Инструкция CREATE TRIGGER используется для добавления триггеров в схему базы данных. Триггер представляет собой операцию базы данных (trigger-action), которая автоматически выполняется в случае определенного события базы данных (database-event).

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]*

Триггер можно определить так, чтобы он запускался каждый раз, когда выполняется инструкция DELETE, INSERT или UPDATE для определенной таблицы базы данных, или каждый раз, когда обновляется инструкция UPDATE одного или нескольких указанных столбцов таблицы. Триггеры являются постоянными, если при их создании не используется ключевое слово TEMP или TEMPORARY. В таком случае триггер удаляется, когда закрывается подключение к главной базе данных, открытое экземпляром SQLConnection. Если не заданы временные рамки (BEFORE или AFTER), по умолчанию используется триггер BEFORE.

Поддерживаются только триггеры FOR EACH ROW, поэтому синтаксис FOR EACH ROW не является обязательным. При использовании триггера FOR EACH ROW инструкции trigger-step выполняются для каждой строки базы данных, вставляемой, обновляемой или удаляемой с помощью инструкции, запускающей триггер, если выражение предложения WHEN является верным.

Если используется предложение WHEN, инструкции SQL, указанные как trigger-step, выполняются только для строк, для которых верно предложение WHEN. Если предложение WHEN не определено, инструкции SQL выполняются для всех строк.

В теле триггера (предложение trigger-action) значения до и после изменения (pre-change и post-change) затрагиваемой таблицы можно получить с помощью специальных имен таблиц OLD и NEW. Структура таблиц OLD и NEW соответствует структуре таблицы, в которой создан триггер. Таблица OLD содержит строки, измененные или удаленные инструкцией, запустившей триггер, в том состоянии, в котором они находились до выполнения операций инструкции. Таблица NEW содержит строки, измененные или созданные инструкцией, запустившей триггер, в том состоянии, в котором они находятся после выполнения операций инструкции. И предложение WHEN и инструкции trigger-step могут получать значения из вставленной, удаленной или обновленной строки, используя ссылки формы NEW.column-name и OLD.column-name, где column-name — это имя столбца из таблицы, с которой связан триггер. Доступность ссылок таблиц OLD и NEW зависит от типа события database-event, обрабатываемого триггером.

• INSERT: ссылки NEW являются действительными.

• UPDATE: ссылки NEW и OLD являются действительными.

• DELETE: ссылки OLD являются действительными.

Указанное время (BEFORE, AFTER или INSTEAD OF) определяет, когда выполняются инструкции trigger-step относительно вставки, изменения или удаления связанной строки. Предложение ON CONFLICT может быть включено в состав инструкции UPDATE или INSERT в trigger-step. Однако если предложение ON CONFLICT указано в составе инструкции, которая запускает триггер, то используется именно эта политика решения конфликтов.

Помимо триггеров таблицы, можно создать триггер INSTEAD OF для вида. Если для вида определен один или несколько триггеров INSTEAD OF INSERT, INSTEAD OF DELETE или INSTEAD OF UPDATE, то выполнение соответствующего типа инструкции (INSERT, DELETE, или UPDATE) для вида не считается ошибкой. В таком случае выполнение инструкции INSERT, DELETE или UPDATE для вида запускает соответствующие триггеры. Так как триггер относится к типу INSTEAD OF, таблицы, используемые в качестве основы вида, не изменяются инструкцией, которая запускает триггер. Однако триггеры можно использовать для выполнения операций модификации базовых таблиц.

При создании триггера для таблицы со столбцом INTEGER PRIMARY KEY важно помнить следующее. Если триггер типа BEFORE изменяет столбец INTEGER PRIMARY KEY строки, которая должна обновляться инструкцией, запускающий триггер, обновление не происходит. Чтобы обойти эту проблему, необходимо создать таблицу со столбцом PRIMARY KEY вместо INTEGER PRIMARY KEY.

Триггер можно удалить с помощью инструкции DROP TRIGGER. При удалении таблицы или вида автоматически удаляются и все связанные триггеры.

Функция RAISE ()

Специальную функцию SQL RAISE() можно использовать в инструкции триггера trigger-step. Эта функция имеет следующий синтаксис:

raise-function  ::=  RAISE ( ABORT, error-message ) | 
                     RAISE ( FAIL, error-message ) | 
                     RAISE ( ROLLBACK, error-message ) | 
                     RAISE ( IGNORE )

Когда одна из первых трех форм вызывается в процессе выполнения триггера, выполняется указанное действие обработки ON CONFLICT (ABORT, FAIL или ROLLBACK), а выполнение текущей инструкции прерывается. Действие ROLLBACK рассматривается как сбой выполнения инструкции, поэтому экземпляр SQLStatement, для которого выполнялся метод execute(), отправляет событие ошибки (SQLErrorEvent.ERROR). Объект SQLError в свойстве error отправленного объекта события имеет свойство details, которое содержит сообщение ошибки error-message, переданное в функции RAISE().

Когда вызывается функция RAISE(IGNORE), прекращается выполнение оставшейся части текущего триггера, инструкции, запустившей его выполнение, а также любых последующих триггеров. Откат изменений базы данных не выполняется. Если инструкция, запустившая выполнение триггера, является частью другого триггера, то выполнение этого триггера возобновляется с начала следующего шага. Дополнительные сведения об алгоритмах решения конфликтов см. в разделе «ON CONFLICT (алгоритмы конфликтов)».

DROP TRIGGER

Инструкция DROP TRIGGER удаляет триггер, созданный с помощью инструкции CREATE TRIGGER.

sql-statement  ::=  DROP TRIGGER [IF EXISTS] [database-name.] trigger-name

Триггер удаляется из базы данных. Обратите внимание, что триггеры автоматически удаляются при удалении связанной с ними таблицы.

COLLATE

Предложение COLLATE используется в инструкциях SELECT, CREATE TABLE и CREATE INDEX для определения алгоритма сравнения, который должен использоваться при сравнении или сортировке значений.

sql-statement   ::=  COLLATE collation-name 
collation-name  ::=  BINARY | NOCASE

По умолчанию для столбцов используется тип сортировки BINARY. Когда сортировка BINARY используется со значениями класса хранения TEXT, двоичная сортировка выполняется путем сравнения байтов в памяти, которые представляют значение независимо от кодировки текста.

Последовательность сортировки NOCASE применяется только к значениям класса хранения TEXT. При использовании сортировки NOCASE выполняется сравнение без учета регистра.

Последовательность сортировки не используется для классов хранения типа NULL, BLOB, INTEGER или REAL.

Чтобы использовать для столбца другой тип сортировки вместо BINARY, предложение COLLATE необходимо включить в определение столбца в инструкции CREATE TABLE. При любом сравнении двух значений TEXT используется последовательность сортировки для определения результатов сравнения в соответствии со следующими правилами.

EXPLAIN

Модификатор команды EXPLAIN является нестандартным расширением SQL.

sql-statement  ::=  EXPLAIN sql-statement

Если ключевое слово EXPLAIN стоит перед любой другой инструкцией SQL, то вместо фактического выполнения команды результат сообщает последовательность инструкций виртуальной машины, которые бы использовались для выполнения команды, если бы не было ключевого слова EXPLAIN. EXPLAIN представляет собой расширенную функцию, с помощью которой разработчики могут изменять текст инструкции SQL, чтобы оптимизировать производительность или отладить некорректно работающую инструкцию.

ON CONFLICT (алгоритмы конфликтов)

Предложение ON CONFLICT не является отдельной командой SQL. Это нестандартное предложение, которое может встречаться во многих других командах SQL.

conflict-clause     ::=  ON CONFLICT conflict-algorithm 
conflict-clause     ::=  OR conflict-algorithm 
conflict-algorithm  ::=  ROLLBACK | 
                         ABORT | 
                         FAIL | 
                         IGNORE | 
                         REPLACE

Первая форма предложения ON CONFLICT, в которой используются ключевые слова ON CONFLICT, применяется в инструкции CREATE TABLE. Для инструкции INSERT или UPDATE используется вторая форма, где ключевые слова ON CONFLICT меняются на OR, чтобы придать синтаксису более естественный вид. Например, вместо INSERT ON CONFLICT IGNORE инструкция принимает вид INSERT OR IGNORE. Хотя используются разные ключевые слова, обе формы имеют один и тот же смысл предложения.

Предложение ON CONFLICT определяет алгоритм, которые используется для разрешения конфликтов ограничений. Существует пять алгоритмов: ROLLBACK, ABORT, FAIL, IGNORE и REPLACE. По умолчанию используется алгоритм ABORT. Ниже приводится объяснение пяти алгоритмов конфликтов.

ROLLBACK

В случае нарушения ограничения сразу выполняется откат (ROLLBACK), что завершает текущую транзакцию. Выполнение команды прерывается, и экземпляр SQLStatement отправляет событие ошибки. Если нет активных транзакций (кроме подразумеваемой транзакции, которая создается для каждой команды), этот алгоритм ведет себя так же, как ABORT.

ABORT

В случае нарушения ограничения, команда отменяет все свои внесенные ранее изменения, и экземпляр SQLStatement отправляет событие ошибки. Откат (ROLLBACK) не выполняется, поэтому изменения, выполненные предшествующими командами в рамках транзакции, сохраняются. Алгоритм ABORT используется как поведение по умолчанию.

FAIL

В случае нарушения ограничения выполнение команды прекращается и экземпляр SQLStatement отправляет событие ошибки. Однако все изменения, которые внесла инструкция в базу данных до нарушения, сохраняются и не отменяются. Например, если при выполнении инструкции UPDATE встречается нарушение ограничения в строке 100, которую требуется обновить, то изменения в первых 99 строках сохраняются, а строка 100 и следующие за ней остаются без изменений.

IGNORE

В случае нарушения ограничения та строка, которая содержит это нарушение, не вставляется или не изменяется. Данная строка игнорируется, и выполнение команды продолжается. Другие строки до и после той, которая содержит нарушение ограничения, вставляются или обновляются в обычном порядке. Ошибка при этом не возникает.

REPLACE

Когда возникает нарушение ограничения UNIQUE, перед вставкой или обновлением текущей строки удаляются существующие строки, вызвавшие это нарушение. Следовательно, вставка или обновление происходит всегда, и выполнение команды продолжается. Ошибка при этом не возникает. Если происходит нарушение ограничения NOT NULL, значение NULL заменяется значением по умолчанию, установленным для данного столбца. Если для столбца не задано значение по умолчанию, используется алгоритм ABORT. Если происходит нарушение ограничения CHECK, используется алгоритм IGNORE. Когда эта стратегия решения конфликтов удаляет строку, чтобы реализовать ограничение, триггер удаления в этой строке не запускается.

Алгоритм, определенный в предложении OR инструкции INSERT или UPDATE, переопределяет любой алгоритм, указанный в инструкции CREATE TABLE. Если алгоритм не указан в инструкции CREATE TABLE или в выполняемой инструкции INSERT или UPDATE, используется алгоритм ABORT.

REINDEX

Команда REINDEX служит для удаления и воссоздания одного или нескольких индексов. Эта команда полезна, когда происходит изменение определения последовательности сортировки.

sql-statement  ::=  REINDEX collation-name 
sql-statement  ::=  REINDEX [database-name .] ( table-name | index-name )

Первая форма воссоздает все индексы во всех подключенных базах данных, которые используют названную последовательность сортировки. Вторая форма перестраивает все индексы, связанные с таблицей, имя которой указано в аргументе table-name. Если в аргументе index-name задано имя индекса, удаляется и воссоздается только указанный индекс.

КОММЕНТАРИИ

Комментарии не являются командами SQL, но они могут встречаться в запросах SQL. Среда выполнения рассматривает их как пробел. Комментарии могут начинаться в любом месте, где может быть пробел, в том числе внутри многострочных выражений.

comment             ::=  single-line-comment | 
                         block-comment 
single-line-comment ::=  -- single-line 
block-comment       ::=  /* multiple-lines or block [*/]

Однострочный комментарий обозначается двумя дефисами. Однострочный комментарий может продолжаться только до конца текущей строки.

Блоки комментариев могут быть многострочными или встраиваться в одну строку. Если отсутствует конечный ограничитель, блок комментариев продолжается до конца ввода. Такая ситуация не рассматривается как ошибка. Новая инструкция SQL может начаться в строке после окончания блока комментариев. Блоки комментариев могут встраиваться в любом месте, где может находиться пробел, в том числе внутри выражений, и в середине других выражений SQL. Блоки комментариев не могут быть вложенными. Однострочные комментарии внутри блока игнорируются.

ВЫРАЖЕНИЯ

Выражения — это подкоманды внутри других блоков SQL. Ниже описывается допустимый синтаксис для выражений внутри инструкции SQL.

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

Выражением является любая комбинация значений и операторов, которые могут разрешаться в одно значение. Выражения можно разделить на два общих типа в соответствии с тем, разрешаются они в логическое значение (true или false) или в нелогическое значение.

В нескольких распространенных ситуациях (включая предложение WHERE, предложение HAVING, выражение ON в предложении JOIN и выражение CHECK) выражение должно разрешаться в логическое значение. Этому условию отвечают следующие типы выражений:

• ISNULL

• NOTNULL

• IN () �

• EXISTS ()

• LIKE

• GLOB

• Определенные функции

• Определенные операторы (в частности, операторы сравнения)

Значения литерала

Числовое значение литерала записывается как целое число или число с плавающей запятой. Поддерживается экспоненциальное представление. В качестве разделителя десятичной дроби всегда используется символ . (точка).

Строковый литерал обозначается заключением строки в одинарные кавычки '. Чтобы включить в строку одинарную кавычку, поставьте две одинарные кавычки подряд, например: ''.

Логический литерал определяется по значению true (истина) или false (ложь). Логические значения литерала используются вместе с логическим типом данных столбца.

Литерал большого двоичного объекта (BLOB) представляет собой строковый литерал, содержащий шестнадцатеричные данные и один символ x или X в начале, например X'53514697465'.

В качестве значения литерала также может использоваться маркер NULL.

Имя столбца

В качестве имени столбца могут использоваться любые имена, определенные в инструкции CREATE TABLE, или один из следующих специальных идентификаторов: ROWID, OID или _ROWID_. Все эти специальные идентификаторы описывают уникальный произвольный целочисленный ключ («ключ строки»), связанный с каждой строкой в каждой таблице. Специальные идентификаторы ссылаются на ключ строки, только если в инструкции CREATE TABLE не определен реальный столбец с тем же именем. Ключи строк ведут себя как столбцы, доступные только для чтения. Ключ строки можно использовать везде, где используются обычные столбцы, с тем исключением, что значение ключа строки нельзя изменить в инструкции UPDATE или INSERT. Инструкция таблицы SELECT * FROM не включает ключ строки в набор результатов.

Инструкция SELECT

Инструкция SELECT может встречаться в выражении как правый операнд оператора IN, как скалярное количество (одно значение результата) или как операнд оператора EXISTS. При использовании в качестве скалярного количества или операнда оператора IN инструкция SELECT может иметь в результате только один столбец. Допускается использование составной инструкции SELECT (соединенной с помощью таких ключевых слов, как UNION или EXCEPT). При использовании оператора EXISTS столбцы в наборе результатов инструкции SELECT игнорируются и выражение возвращает значение TRUE, если существует одна или несколько строк, или FALSE, если набор результатов пустой. Если в выражении SELECT ни один термин не ссылается на значение в запросе-контейнере, то выражение вычисляется один раз перед любой другой обработкой, при необходимости результат можно использовать повторно. Если выражение SELECT содержит переменные из внешнего запроса, который называется связанным подзапросом, оно повторно вычисляется при каждом использовании.

Когда SELECT является правым операндом оператора IN, оператор IN возвращает значение TRUE, если результат левого операнда равен любому значению в наборе результатов инструкции SELECT. Перед оператором IN может стоять ключевое слово NOT, которое меняет смысл проверки на обратный.

Когда инструкция SELECT встречается в выражении, но не в правой части оператора IN, в выражении используется первая строка результата SELECT. Если инструкция SELECT возвращает несколько строк результатов, все строки, следующие за первой, игнорируются. Если инструкция SELECT не возвращает строк, SELECT имеет значение NULL.

Выражение CAST

Выражение CAST меняет тип данных указанного значения на заданный. В качестве типа может быть указано любое непустое имя, которое является действительным для типа в определении столбца в инструкции CREATE TABLE. Дополнительные сведения см. в разделе «Поддержка типов данных».

Дополнительные элементы выражений

Следующие элементы SQL также можно использовать в выражениях.

• Встроенные функции: агрегатные функции, скалярные функции, функции форматирования даты и времени

• Операторы

• Параметры

Агрегатные функции

Агрегатные функции выполняют операции со значениями из нескольких строк. Эти функции главным образом используются в инструкциях SELECT вместе с предложением GROUP BY.

В любой предшествующей агрегатной функции, которая принимает один аргумент, перед этим аргументом может стоять ключевое слово DISTINCT. В таком случае повторяющиеся элементы отфильтровываются перед передачей результата следующей агрегатной функции. Например, вызов функции COUNT(DISTINCT x) возвращает число разных значений в столбце X, а не общее число ненулевых значений.

Скалярные функции

Скалярные функции выполняют операции со значениями только одной строки.

Функции форматирования даты и времени

Функции форматирования даты и времени представляют собой группу скалярных функций, которые служат для создания форматированных данных даты и времени. Обратите внимание, что эти функции выполняют операции со значениями, содержащими строки и числа, и возвращают соответствующие результаты. Эти функции не предназначены для использования с типом данных DATE. Если их использовать для данных в столбце, для которого объявлен тип данных DATE, они работают некорректно.

Форматы времени

Символ T в этих форматах является символом литерала «T», который разделяет дату и время. Форматы, которые содержат только время, предполагают дату 2001-01-01.

Модификаторы

Общие операторы

В блоках SQL разрешается использовать следующие двоичные операторы, перечисленные в порядке приоритета от самого высокого до самого низкого.

*    /    % 
+    - 
<< >> &     | 
< >=   > >= 
=    ==   !=   <> IN 
AND 
OR

Ниже перечислены поддерживаемые унарные префиксные операторы.

 !    ~    NOT

Оператор COLLATE можно рассматривать как унарный постфиксный оператор. Оператор COLLATE имеет самый высокий приоритет. Он всегда обеспечивает более крепкую привязку, чем любой другой унарный префиксный оператор или двоичный оператор.

Обратите внимание, что существует два варианта операторов равенства и неравенства. Равенство может быть выражено как = или ==. В качестве оператора неравенства может использоваться != или <>.

Оператор || является оператором сцепления строк, он объединяет две строки операндов.

Оператор % выводит остаток левого операнда по модулю правого операнда.

Результат любого двоичного оператора является числовым значением, за исключением оператора сцепления ||, результатом которого является строка.

Операторы SQL

LIKE

Оператор LIKE выполняет сравнение для сопоставления с шаблоном.

expr     ::=  (column-name | expr) LIKE pattern 
pattern  ::=  '[ string | % | _ ]'

Операнд справа от оператора LIKE содержит шаблон, а левый операнд содержит строку, которую требуется сравнить с шаблоном. Символ процента (%) в шаблоне является символом подстановки. Он заменяет любую последовательность из нуля или более символов в строке. Символ подчеркивания (_) в шаблоне соответствует любому одному символу в строке. Любой другой символ соответствует самому себе или своему эквиваленту нижнего или верхнего регистра, то есть сопоставление выполняется без учета регистра. (Примечание. Ядро базы данных распознает верхний и нижний регистр только для 7-битовых латинских символов. По этой причине оператор LIKE учитывает регистр для 8-битовых символов iso8859 и для символов UTF-8. Например, выражение 'a' LIKE 'A' возвращает значение TRUE, а 'æ' LIKE 'Æ' возвращает FALSE). Чувствительность к регистру латинских символов можно изменить с помощью свойства SQLConnection.caseSensitiveLike.

Если присутствует необязательное предложение ESCAPE, то выражение, следующее за ключевым словом ESCAPE, должно содержать строку, состоящую из одного символа. Этот символ можно использовать в шаблоне LIKE для сопоставления символов процента или прочерка литерала. Символ маскировки, за которым следует символ процента, прочерка или он сам, соответствует символу процента, прочерка или маскировки литерала в строке.

GLOB

Оператор GLOB похож на LIKE, но использует синтаксис глобализации файлов Unix для подстановочных знаков. В отличие от LIKE, оператор GLOB учитывает регистр.

IN

Оператор IN вычисляет, равен ли левый операнд одному из значений в правом операнде (из набора значений в круглых скобках).

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]*

Правый операнд может содержать разделенные запятыми значения литералов или результат инструкции SELECT. Объяснения и ограничения использования инструкции SELECT в качестве правого операнда оператора IN см. в подразделе «Инструкция SELECT» раздела «Выражения».

BETWEEN...AND

Оператор BETWEEN...AND равносилен использованию двух выражений с операторами >= и <=. Например, выражение x BETWEEN y AND z эквивалентно выражению x >= y AND x <= z.

NOT

Оператор NOT выполняет функцию отрицания. Перед операторами GLOB, LIKE и IN может стоять ключевое слово NOT, которое меняет смысл проверки на обратный (другими словами, требуется проверить, что значение не соответствует указанному шаблону).