SQLite3で型に厳格なSTRICTテーブルを作る
SQLiteの際立った特徴の一つは動的な型システムを採用していることです。
一般的なリレーショナルデータベースであれば、カラムの型がレコードによって異なるなんてことはありませんが、SQLiteはその限りではありません。
整数型と宣言したカラムにテキスト型のレコードを格納できます。
これはバグではなくて仕様のため、SQLiteのデータ型のドキュメント冒頭で丁寧に解説されていますし("Flexible typing is a feature of SQLite, not a bug.")、"The Advantages Of Flexible Typing"というそのものズバリのドキュメントも存在します。
とはいえ、他のRDBMSに慣れ親しんだ人にも驚きが少ない仕様を提供するためか、型に厳格な STRICT テーブルがバージョン3.37(2021-11-27リリース)から提供されました。
SQLite3 って型があるようで何でもアリなので使えねぇって昔から言われてきたけど 2021 年に最近入った STRICT suffix を付けると厳密になるんですよね。 pic.twitter.com/gqpfKSm8Jl
— mattn (@mattn_jp) February 12, 2023
STRICT tables provide a prescriptive style of data type management, for developers who prefer that kind of thing.
本ブログでは、同機能について簡単に紹介します。
SQLiteの動的な型
SQLiteのスキーマは参考情報でしかなく、同じカラムであってもレコードごとに異なる型を格納可能です。
試しに、整数型で宣言したカラムにテキスト型の値を格納してみましょう。
sqlite> CREATE TABLE t1(a INT);
sqlite> INSERT INTO t1 VALUES(1), ('a');
sqlite> SELECT typeof(a), a FROM t1;
integer|1
text|a
このユルイ型システムをよしとせず、CHECK 制約で型を縛っていた人も多いのではないかと思います。
sqlite> CREATE TABLE t2(a INT CHECK (typeof(a) == 'integer'));
sqlite> INSERT INTO t2 VALUES('a');
Error: CHECK constraint failed: typeof(a) == 'integer'
STRICTテーブルで静的型付け
厳格な型が欲しいユーザーのために、SQLite 3.37(2021-11-27リリース)からは、テーブル定義の最後に STRICT オプションを追加すると、型に厳格なテーブルを作成できるようになりました。
sqlite> CREATE TABLE t3(a INT) STRICT;
sqlite> INSERT INTO t3 VALUES('a');
Runtime error: cannot store TEXT value in INT column t3.a (19)
ユルイ型を使い続ける
STRICT テーブルに対応した SQLite 3.37 以降も動的な型システムを使い続ける方法は2通りあります。
1つ目は、 STRICT 宣言せずにテーブルを作成することです。
デフォルトの挙動に変更はありません。
2つ目は、STRICT テーブルに対して任意の型を受け取る ANY 型でカラム定義することです。
sqlite> CREATE TABLE t1(a ANY) STRICT;
sqlite> INSERT INTO t1 VALUES(1), ('a');
sqlite> SELECT typeof(a), a FROM t1;
integer|1
text|a
ただしANY 型は非STRICT テーブルと互換なわけではありません。
例えば、このANY 型は型強制が行われません。
一方で、非 STRICT テーブルは、型強制されます。
sqlite> CREATE TABLE t2(a INT);
sqlite> INSERT INTO t2 VALUES('01');
sqlite> SELECT typeof(a), a FROM t3;
integer|1
ANY 型の詳細は次のドキュメントをご確認ください。
https://www.sqlite.org/stricttables.html
古いバージョンでSTRICTテーブルを扱う
STRICT テーブルを含むデータベースを STRICT 未対応の古いバージョンで動かすことは考えないほうがいいでしょう。
テーブル定義の末尾のキーワードに対応していないため、諸々の操作が失敗します。
sqlite> .version SQLite 3.36.0 2021-06-18 18:58:49 d24547a13b6b119c43ca2ede05fecaa707068f18c7430d47fc95fb5a2232aapl clang-13.0.0 sqlite> .schema Error: malformed database schema (t1) - near "STRICT": syntax error sqlite> INSERT INTO t1 VALUES(1); Error: malformed database schema (t1) - near "STRICT": syntax error
ただし、データベースファイルをオープンしたあと PRAGMA writable_schema=ON; と宣言すると、動作はします。
このプラグマは、壊れたデータファイルのリカバリなどに使われるものであり、エラーが起きるまで(今回だとSTRICT キーワード直前まで)を正として処理します。
sqlite> .version
SQLite 3.36.0 2021-06-18 18:58:49 d24547a13b6b119c43ca2ede05fecaa707068f18c7430d47fc95fb5a2232aapl
clang-13.0.0
sqlite> PRAGMA writable_schema=ON;
sqlite> .schema
CREATE TABLE t1(a INT) STRICT;
sqlite> INSERT INTO t1(a) VALUES(1), ('a');
sqlite> SELECT typeof(a), a FROM t1;
integer|1
text|a
STRICT で定義したはずのテーブルが STRICT に動作していません。
特殊な事情がない限り、このような使い方はやめたほうがいいでしょう。
SQLite4ではどうなる?
SQLite3 の次世代バージョンであるSQLite4の設計方針は公開されています。
SQLite4: The Design Of SQLite4
SQLite4でも互換なSQLite3の主要機能の3つ目として、動的な型が挙げられています。
Among the features that will remain in common between SQLite3 that SQLite4 are:
...
3 . Dynamic typing is used, rather than the rigid static typing of most other SQL database engines.
STRICT モードはあくまでも要望が多いからオプションとして提供しているものであり、SQLiteの目指す世界観とはずれるようです。
ALTER TABLE ドキュメントの"8. Why ALTER TABLE is such a problem for SQLite"はそのヒントの一つかもしれません。
知らんけど。
最後に
SQLite 3.37(2021-11-27)以降では、テーブル定義を STRICT 宣言するだけで、型に厳格なデータ管理をできます。
一般的なRDMBSのように SQLiteでも型チェックするテーブルを作成したい場合は、STRICT テーブルも検討しましょう。
それでは。
