Генерируемые столбцы в SQLite: VIRTUAL и STORED

Что такое генерируемые столбцы в SQLite

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

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.

Синтаксис

С точки зрения синтаксиса генерируемые столбцы обозначаются с помощью ограничения столбца 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, что упрощает запись

Типы генерируемых столбцов: VIRTUAL и STORED

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

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

Возможности

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

Генерируемые столбцы могут иметь ограничения NOT NULL, CHECK и UNIQUE, а также ограничения внешнего ключа — точно так же, как и обычные столбцы

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

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

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

Ограничения

Генерируемые столбцы не могут иметь значение по умолчанию — они не поддерживают предложение DEFAULT. Значение генерируемого столбца всегда определяется выражением, следующим за ключевым словом AS

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

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

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

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

Каждая таблица должна содержать хотя бы один негенерируемый столбец

Выполнить ALTER TABLE ADD COLUMN для столбца STORED невозможно. Однако добавить столбец VIRTUAL таким образом можно

Тип данных и последовательность сортировки (collation) генерируемого столбца определяются только типом данных и предложением COLLATE в определении столбца. Тип данных и последовательность сортировки выражения GENERATED ALWAYS AS не влияют на тип данных и последовательность сортировки самого столбца

Генерируемые столбцы не включаются в список столбцов, предоставляемый оператором PRAGMA table_info. Однако они включаются в вывод более нового оператора PRAGMA table_xinfo.

Разница между VIRTUAL и STORED столбцами в SQLite

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

Ключевые практические различия:

• Хранение: STORED-столбцы занимают место на диске, VIRTUAL — нет
• Момент вычисления: STORED вычисляется при записи строки, VIRTUAL — при каждом чтении
• Индексирование: индекс по STORED-столбцу — обычный индекс; индекс по VIRTUAL-столбцу — индекс по выражению
• ALTER TABLE: добавить через ALTER TABLE ADD COLUMN можно только VIRTUAL-столбец

Типичные ошибки при работе с генерируемыми столбцами

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

Попытка записать значение напрямую. Генерируемый столбец нельзя указывать в INSERT или UPDATE как целевой столбец для записи — это вызовет ошибку. Значение всегда вычисляется автоматически

Использование подзапросов или агрегатных функций в выражении. Выражение генерируемого столбца поддерживает только скалярные детерминированные функции. Попытка использовать COUNT(), SUM() или подзапрос приведёт к ошибке на этапе создания таблицы

Попытка добавить STORED-столбец через ALTER TABLE. Это ограничение нередко застаёт врасплох: ALTER TABLE ADD COLUMN работает только для VIRTUAL-столбцов

Ссылка на ROWID в выражении. Прямая ссылка на ROWID в выражении генерируемого столбца запрещена. Если нужен доступ к идентификатору строки, следует использовать столбец INTEGER PRIMARY KEY

Ожидание столбца в выводе PRAGMA table_info. Генерируемые столбцы не отображаются в PRAGMA table_info — для их просмотра нужно использовать PRAGMA table_xinfo.

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

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

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

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

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

Я рекомендую проверять версию SQLite перед развёртыванием схем с генерируемыми столбцами, особенно если приложение должно работать в средах с разными версиями движка.

Часто задаваемые вопросы

Можно ли использовать генерируемый столбец как первичный ключ? Нет. Генерируемые столбцы не могут быть частью PRIMARY KEY. Это ограничение может быть снято в будущих версиях SQLite для столбцов STORED.

Чем VIRTUAL-столбец отличается от STORED-столбца с точки зрения запросов? С точки зрения результатов запросов они идентичны. Разница только в том, когда и где вычисляется значение: VIRTUAL — при каждом чтении, STORED — один раз при записи строки, с сохранением на диске.

Можно ли добавить генерируемый столбец к существующей таблице через ALTER TABLE? Можно, но только VIRTUAL-столбец. Добавить STORED-столбец через ALTER TABLE ADD COLUMN нельзя.

Как просмотреть генерируемые столбцы таблицы? Через PRAGMA table_xinfo. Стандартный PRAGMA table_info генерируемые столбцы не показывает.

Можно ли индексировать генерируемый столбец? Да. Индекс по STORED-столбцу создаётся как обычный индекс. Индекс по VIRTUAL-столбцу создаётся как индекс по выражению.