STRICT Таблицы

1. Введение

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

Некоторые разработчики ценят свободу, которую предоставляют гибкие правила типизации 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. Столбцы, которые являются частью первичного ключа, неявно имеют ограничение NOT NULL. Однако, даже если первичный ключ имеет неявное ограничение 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 версии 3.37.0 (2021-11-27) и более поздними. Если вы попытаетесь открыть базу данных, содержащую ключевое слово STRICT в более ранней версии SQLite, оно не будет распознано, и будет сообщено об ошибке (за исключением случаев, указанных ниже). Но помимо дополнительного ключевого слова STRICT, основной формат файла базы данных идентичен.

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

5. Другие параметры таблицы

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

• STRICT
• WITHOUT ROWID

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