1. Введение

Произведенные колонки (также иногда названы "вычисляемыми столбцами") являются колонками таблицы, значения которых это функция других колонок в той же самой строке. Произведенные колонки могут быть прочитаны, но их значения не могут быть непосредственно написаны. Единственный способ изменить значение произведенной колонки состоит в том, чтобы изменить значения других колонок, используемых, чтобы вычислить произведенную колонку.

2. Синтаксис

Синтаксически, произведенные колонки определяются, используя ограничение колонки "GENERATED ALWAYS". Например:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c TEXT,
                d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
                e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED);

У запроса выше есть три обычных колонки: "a" (PRIMARY KEY), "b" и "c", а также две произведенных колонки: "d" и "e".

"GENERATED ALWAYS" в начале ограничения и ключевое слово "VIRTUAL" или "STORED" в конце все дополнительные. Только ключевое слово "AS" и введенное выражение требуются. Если ключевое слово "VIRTUAL" или "STORED" опущено, то по умолчанию VIRTUAL. Следовательно, запрос в качестве примера выше мог быть упрощен до:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c TEXT, d INT AS (a*abs(b)),
                e TEXT AS (substr(c,b,b+1)) STORED);

2.1. VIRTUAL против STORED

Произведенные колонки могут быть VIRTUAL или STORED. Значение колонки VIRTUAL вычисляется, когда прочитано, тогда как значение колонки STORED вычисляется, когда строка написана. Колонки STORED занимают место в файле базы данных, тогда как колонки VIRTUAL используют больше циклов CPU, будучи прочитанными.

С точки зрения SQL, колонки STORED и VIRTUAL почти точно то же самое. Запросы против любого класса произведенной колонки приводят к тем же самым результатам. Единственное функциональное различие в том, что нельзя добавить новые колонки STORED, используя ALTER TABLE ADD COLUMN. Только колонки VIRTUAL могут быть добавлены, используя ALTER TABLE.

2.2. Возможности

1. У произведенных колонок может быть тип данных. SQLite пытается преобразовать результат выражения в тот тип данных, используя те же самые правила близости, как для обычных колонок.

2. У произведенных колонок могут быть ограничения NOT NULL, CHECK, UNIQUE и внешнего ключа, точно так же, как у обычных колонок.

3. Произведенные колонки могут участвовать в индексах, точно так же, как обычные колонки.

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

5. Произведенные колонки могут произойти где угодно в определении таблицы. Произведенные колонки могут быть вкраплены среди обычных колонок. Не необходимо поместить произведенные колонки в конце списка колонок в определении, как показано в примерах выше.

2.3. Ограничения

1. У произведенных колонок не может быть значения по умолчанию (они не могут использовать пункт "DEFAULT"). Значение произведенной колонки всегда значение, определенное выражением, которое следует за ключевым словом "AS".

2. Произведенные колонки не могут использоваться в качестве части PRIMARY KEY. Будущие версии SQLite могли бы снять это ограничение для колонок STORED.

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

4. Выражение произведенной колонки может обратиться к другим произведенным колонкам в той же самой строке, но никакая произведенная колонка не может зависеть от себя, прямо или косвенно.

5. Выражение произведенной колонки не может непосредственно сослаться на ROWID, хотя это может сослаться на столбец INTEGER PRIMARY KEY, который часто является тем же самым.

6. У каждой таблицы должна быть по крайней мере одна непроизведенная колонка.

7. Невозможно ALTER TABLE ADD COLUMN для столбца STORED. Можно добавить колонку VIRTUAL, как бы то ни было.

8. Тип данных и последовательность сопоставления произведенной колонки определяются только типом данных и COLLATE в определении столбца. У типа данных и последовательности сопоставления выражения GENERATED ALWAYS AS нет влияния на тип данных и последовательность сопоставления самой колонки.

9. Произведенные колонки не включены в список колонок, предоставленных PRAGMA table_info. Но они включены в вывод более новой PRAGMA table_xinfo.

3. Совместимость

Произведенная поддержка колонки была добавлена с версии SQLite version 3.31.0 (2020-01-22). Если более ранняя версия SQLite попытается прочитать файл базы данных, который содержит произведенную колонку в ее схеме, то та более ранняя версия будет чувствовать произведенный синтаксис колонки как ошибку и сообщит, что схема базы данных повреждена.

Версия 3.31.0 SQLite может прочитать и написать любую базу данных, созданную любой предыдущей версией SQLite до SQLite 3.0.0 (2004-06-18). Более ранние версии SQLite до 3.31.0 могут прочитать и написать базы данных, созданные версией 3.31.0 SQLite и позже, пока схема базы данных не содержит такие особенности, как произведенные колонки, которые непонятны более ранней версии. Проблемы возникают только, если вы создаете новую базу данных, которая содержит произведенные колонки, используя версию 3.31.0 SQLite или позже, а затем попытаетесь прочитать или написать файл базы данных, используя более раннюю версию SQLite, которая не понимает произведенные колонки.