Гибкая типизация SQLite и её ограничения
SQLite предлагает гибкость в типах данных. Например, столбец с типом «INTEGER» может автоматически преобразовать строки вроде '123' в целое число, однако если ввести нераспознаваемое значение, такое как 'xyz', будет вставлена исходная строка. Подробности можно найти в документе «Datatypes In SQLite»
Некоторые разработчики выделяют преимущества гибкой типизации SQLite, активно применяя её в своих проектах. Однако многие предпочитают строгую типизацию, как в других SQL-системах. Для этих пользователей с версии 3.37.0 (27 ноября 2021 года) SQLite предоставляет режим строгой типизации (STRICT), который можно включать индивидуально для каждой таблицы
По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы
STRICT-таблицы в SQLite: синтаксис и правила
В операторе CREATE TABLE, если вы добавите слово-опцию STRICT в конце после закрывающей скобки ), к этой таблице начнут применяться правила строгой типизации. Ключевое слово STRICT создаёт несколько отличий:
В каждом определении столбца должен быть указан тип данных для этого столбца. Возможность указывать столбец без типа данных упраздняется
Тип данных должен быть одним из следующих:
INTINTEGERREALTEXTBLOBANY
Никакие другие имена типов данных не допускаются, хотя в будущих версиях SQLite могут быть добавлены новые типы
Содержимое, вставляемое в столбец с типом данных, отличным от ANY, должно быть либо NULL (если нет ограничения NOT NULL), либо соответствовать указанному типу. SQLite будет пытаться привести данные к нужному типу согласно стандартным правилам аффинности, как это делается в PostgreSQL, MySQL, SQL Server и Oracle. Если значение невозможно преобразовать без потерь, возникнет ошибка SQLITE_CONSTRAINT_DATATYPE
Столбцы с типом данных ANY могут принимать данные любого вида (за исключением того, что они будут отклонять значения NULL при наличии ограничения NOT NULL, разумеется). Для столбца типа ANY в STRICT-таблице приведение типов не выполняется
Столбцы, входящие в состав PRIMARY KEY, неявно имеют ограничение NOT NULL. Однако, несмотря на то что PRIMARY KEY имеет неявное ограничение NOT NULL, при вставке значения NULL в столбец INTEGER PRIMARY KEY это значение автоматически преобразуется в уникальное целое число по тем же правилам, что и для INTEGER PRIMARY KEY в обычных, нестрогих таблицах
Команды PRAGMA integrity_check и PRAGMA quick_check проверяют тип содержимого всех столбцов в STRICT-таблицах и сообщают об ошибках при обнаружении несоответствий
Всё остальное в STRICT-таблице работает так же, как и в обычной нестрогой таблице:
- Ограничения
CHECKработают так же - Ограничения
NOT NULLработают так же - Ограничения
FOREIGN KEYработают так же - Ограничения
UNIQUEработают так же - Предложения
DEFAULTработают так же - Предложения
COLLATEработают так же - Вычисляемые столбцы работают так же
- Предложения
ON CONFLICTработают так же - Индексы работают так же
AUTOINCREMENTработает так же- Столбец
INTEGER PRIMARY KEYявляется псевдонимом дляrowid, а столбецINT PRIMARY KEY— нет - Формат хранения данных таблицы на диске одинаков
Тип данных ANY
Возможность хранить данные любого типа в одном столбце действительно полезна. Чтобы сохранить эту возможность в STRICT-таблицах, введён новый тип данных ANY, который позволяет вставлять целые числа, числа с плавающей точкой, строки или двоичные данные (BLOB), сохраняя их в точности так, как они были вставлены. Именно это отличает SQLite от других реляционных СУБД в подходе к типам данных
В STRICT-таблице столбец типа ANY сохраняет данные именно в том виде, в каком они были получены. В то время как в обычной нестрогой таблице столбец типа ANY может преобразовывать строки, похожие на числа, в числовые значения и сохранять их, а не исходные строки
Обратная совместимость
Ключевое слово STRICT в конце оператора CREATE TABLE распознаётся только в SQLite версии 3.37.0 (27 ноября 2021 года) и более поздних. Если попытаться открыть базу данных с ключевым словом STRICT в старых версиях SQLite, она не распознает его и выдаст ошибку (за исключением случаев, описанных ниже)
Таким образом, файл базы данных с одной или несколькими STRICT-таблицами может быть прочитан и записан только в SQLite версии 3.37.0 или новее. Тем не менее, база данных, созданная в версии 3.37.0 или более поздней, по-прежнему доступна для чтения и записи в более ранних версиях SQLite (до 3.0.0, 18 июня 2004 года), при условии что она не содержит STRICT-таблиц или других новшеств, добавленных после более старых версий SQLite
Ключевое слово STRICT по-прежнему может использоваться как идентификатор. Оно трактуется как ключевое слово только в определённой части синтаксиса, и sqlite3_keyword_check(..) не распознаёт его как обычное ключевое слово
Доступ к
STRICT-таблицам в более ранних версиях SQLite
Из-за особенности синтаксического анализатора SQL-языка версии SQLite до 3.37.0 всё же могут читать и записывать STRICT-таблицы, если сразу после открытия файла базы данных, до выполнения каких-либо других операций, требующих знания схемы, установить PRAGMA writable_schema=ON. Одна из особенностей PRAGMA writable_schema=ON состоит в том, что она отключает ошибки в анализаторе схемы
Это сделано намеренно, поскольку одной из главных причин существования PRAGMA writable_schema=ON является обеспечение восстановления файлов баз данных с повреждёнными схемами
Таким образом, при включённом writable_schema=ON, когда анализатор схемы достигает ключевого слова STRICT, он говорит себе: «Я не знаю, что с этим делать, но всё до этой точки выглядит как допустимое определение таблицы, поэтому я просто воспользуюсь тем, что у меня есть». Следовательно, ключевое слово STRICT фактически игнорируется
Поскольку в остальном формат файла для STRICT-таблиц не изменяется, всё остальное будет работать в штатном режиме. Разумеется, жёсткая проверка типов выполняться не будет, поскольку более ранние версии SQLite не умеют этого делать
Команда .dump в CLI (интерфейс командной строки) устанавливает PRAGMA writable_schema=ON, поскольку .dump предназначена для извлечения максимального объёма содержимого даже из повреждённого файла базы данных
Поэтому, если вы используете более старую версию SQLite, открываете в CLI базу данных со STRICT-таблицами и выполняете команду .dump прежде всего остального, вы сможете читать и записывать данные в STRICT-таблицы без жёсткой проверки типов. Это потенциально может привести к повреждению базы данных, допустив некорректные типы в STRICT-таблицы
Повторное открытие базы данных в более новой версии SQLite и выполнение PRAGMA quick_check позволит обнаружить и сообщить обо всех подобных повреждениях
Другие опции таблицы
Синтаксический анализатор SQLite принимает разделённый запятыми список опций таблицы после последней закрывающей скобки в операторе CREATE TABLE. На момент написания этого текста (2021-08-23) распознаются только две опции:
STRICTWITHOUT ROWID
Если опций несколько, они могут быть указаны в любом порядке. Для простоты текущий анализатор принимает дублирующиеся опции без предупреждений, однако это может измениться в будущих версиях, поэтому приложениям не следует на это полагаться
Типичные ошибки при вставке данных в STRICT-таблицы SQLite
На практике я замечаю, что разработчики, переходящие с обычных таблиц SQLite на STRICT-режим, чаще всего сталкиваются с несколькими повторяющимися проблемами. Я разберу каждую из них отдельно, чтобы на нашем опыте можно было избежать этих ловушек
Использование нестандартных имён типов. SQLite в обычном режиме принимает произвольные имена типов вроде VARCHAR(255), BOOLEAN или DATETIME и сопоставляет их с аффинностью через набор эвристик. В STRICT-таблице такие имена вызовут ошибку — допустимы только INT, INTEGER, REAL, TEXT, BLOB и ANY
Ожидание автоматического приведения строк к числам. В обычной таблице вставка строки '42' в столбец INTEGER молча преобразует её в число. В STRICT-таблице это приведение тоже происходит, но только если преобразование возможно без потерь. Строка '42abc' уже вызовет SQLITE_CONSTRAINT_DATATYPE
Путаница между INT и INTEGER в контексте PRIMARY KEY. Это различие существовало и до появления STRICT-режима, но в STRICT-таблицах оно особенно важно: только INTEGER PRIMARY KEY является псевдонимом для rowid, тогда как INT PRIMARY KEY — нет
Открытие STRICT-базы данных старой версией SQLite без writable_schema=ON. Результатом будет ошибка разбора схемы. Если совместимость со старыми версиями критична, стоит заранее проверить версию SQLite в целевой среде
Ответы на эти вопросы могут быть для вас полезными
Можно ли смешивать STRICT-таблицы и обычные таблицы в одной базе данных?
Да. Режим STRICT включается отдельно для каждой таблицы с помощью ключевого слова в операторе CREATE TABLE. Остальные таблицы в той же базе данных продолжают работать по обычным правилам гибкой типизации
Что произойдёт, если попытаться вставить значение неподходящего типа в STRICT-таблицу?
SQLite сначала попытается привести значение к нужному типу по стандартным правилам аффинности. Если приведение невозможно без потерь, операция завершится с ошибкой SQLITE_CONSTRAINT_DATATYPE
Чем тип ANY в STRICT-таблице отличается от ANY в обычной таблице?
В STRICT-таблице столбец ANY всегда сохраняет данные точно в том виде, в каком они получены. В обычной таблице столбец ANY пытается преобразовать строки, похожие на числа, в числовое значение и сохраняет результат преобразования, а не исходную строку
Как проверить, что STRICT-таблица не содержит данных с некорректными типами?
Для этого используются команды PRAGMA integrity_check и PRAGMA quick_check — они проверяют типы содержимого всех столбцов в STRICT-таблицах и сообщают об обнаруженных несоответствиях
Можно ли использовать слово STRICT как имя столбца или таблицы?
Да. Ключевое слово STRICT распознаётся как специальное только в определённой позиции синтаксиса CREATE TABLE и не является зарезервированным ключевым словом в общем смысле. Функция sqlite3_keyword_check(..) не возвращает его как обычное ключевое слово
