1. Введение

SQLite стремится быть гибким относительно типа данных содержания, которое это хранит. Например, если у столбца таблицы есть тип "INTEGER", то SQLite пытается преобразовать что-либо вставленное в ту колонку в целое число. Так попытка вставить последовательность '123' приведет к целому числу 123. Но если содержание не может быть без потерь преобразовано в целое число, например, если вход 'xyz', то исходная строка вставляется вместо этого. Посмотрите здесь подробности.

Некоторые разработчики ценят свободу, что гибкие правила типизации SQLITE обеспечивают и используют ту свободу. Но другие разработчики ошеломлены скандальной ломкой правил в SQLITE и предпочитают традиционную систему типов, найденную во всех других системах базы данных SQL и в стандарте SQL. Для этой последней группы SQLite поддерживает строгий способ типизации с версии 3.37.0 (2021-11-27), который позволен отдельно для каждой таблицы.

2. Таблицы STRICT

В CREATE TABLE, если ключевое слово выбора таблицы "STRICT" добавляется после закрывающей ")", строгие правила типизации относятся к той таблице. Ключевое слово STRICT вызывает следующие различия:

1. Каждое определение столбца должно определить тип данных для той колонки. Свобода определить колонку без типа данных удалена.

2. Тип данных должен быть одним из следующего:

   • INT
   • INTEGER
   • REAL
   • TEXT
   • BLOB
   • ANY

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

3. Содержанием, вставленным в колонку с типом данных кроме ANY, должен быть NULL (предполагая, что нет никакого ограничения NOT NULL на колонке) или определенный тип. SQLite пытается привести данные в соответствующий тип, используя обычные правила близости, как PostgreSQL, MySQL, SQL Server и Oracle. Если значение не может быть без потерь преобразовано в указанный тип данных, то ошибка SQLITE_CONSTRAINT_DATATYPE поднята.

4. Колонки с типом данных ANY могут принять любой вид данных (они отклонят NULL, если у них будет ограничение NOT NULL). Никакое приведение типа не происходит для колонки типа ANY в таблице STRICT.

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

6. 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 уже нет.
• Дисковый формат для табличных данных тот же.

3. Тип данных ANY

Способность принять любой тип данных в отдельном столбце, оказалось, была удивительно полезна за эти годы. Чтобы продолжить поддерживать эту способность, даже в таблицах STRICT, новое имя типа данных ANY вводится. Когда тип данных колонки "ANY", это означает, что любой вид данных, целые числа, значения с плавающей точкой, последовательности или blob, могут быть вставлены в таблицу, и тип данных будет сохранен точно. Насколько мы знаем, SQLite это единственный движок базы данных SQL, который поддерживает эту продвинутую способность.

Поведение ANY немного отличается в таблице STRICT и в обычной. В STRICT колонке типа ANY всегда сохраняет данные точно, как это получено. Для обычной нестрогой таблицы колонки типа ANY попытается преобразовать последовательности, которые похожи на числа в числовое значение, и, если это успешно, сохранят числовое значение, а не исходную строку. Например:

CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'

CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123

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

Ключевое слово STRICT в конце CREATE TABLE появилось в SQLite version 3.37.0 (2021-11-27) и позже. При попытке открыть базу данных, содержащую ключевое слово STRICT в более ранней версии SQLite, она не признает ключевого слова и сообщит об ошибке (за исключением отмеченного ниже). Но кроме дополнительного ключевого слова STRICT, основной формат файла базы данных идентичен.

Таким образом, в целом, файл базы данных, который содержит одну или несколько таблиц STRICT, может быть прочитан и написан только версией SQLite version 3.37.0 или позже. Однако, база данных, созданная SQLite 3.37.0 или позже, может все еще быть прочитана и написана более ранними версиями SQLite до версию 3.0.0 (2004-06-18), пока база данных не содержит таблиц STRICT или других особенностей, которые были введены после более старой версии SQLite.

Ключевое слово STRICT может все еще использоваться в качестве идентификатора. Это рассматривают как ключевое слово только в определенной части синтаксиса, и sqlite3_keyword_check(..) не признает его регулярным ключевым словом.

4.1. Доступ к таблицам 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, и вы открываете базу данных с таблицами STRICT в CLI и даете команду ".dump" прежде, чем сделать что-либо еще, вы будете в состоянии читать и писать таблицы STRICT без принудительного присвоения типов. Это могло, потенциально, испортить базу данных, позволив неправильные типы в таблицах STRICT. Повторное открытие базы данных с более новой версией SQLite и управление "PRAGMA quick_check" обнаружит и сообщит о таких повреждениях.

5. Другие варианты таблицы

Парсер SQLite принимает список разделенных запятой значений параметров таблицы после заключительной круглой скобки в CREATE TABLE. На момент (2021-08-23) признаны только две опции:

• STRICT
• WITHOUT ROWID

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