HN 日本語サマリー

← 一覧へ戻る
プログラミング

SQLiteでStrictテーブルを推奨する

Prefer Strict Tables in SQLite (evanhahn.com)

308 pointsby ingve156 コメント

要約

SQLiteの「Strictテーブル」機能は、データ型の一貫性を強制し、数値列にテキストを入れるといった一般的なエラーを防ぐのに役立つ。これにより、テーブル作成時の型エラーや、挿入・更新時の型不一致を防ぐことができる。既存のテーブルをStrictにすることはできないが、新規テーブル作成時には積極的に利用することで、データ整合性を高めることができる。

全文翻訳

SQLiteではSTRICTテーブルを推奨する

要するに、SQLiteのSTRICTテーブルを推奨するのは、数値カラムにテキストを挿入するようなデータ型の問題を回避できるからです。

SQLiteには、私が過小評価されていると思う機能があります。それがSTRICTテーブルです。STRICTテーブルは厳格な型付けを強制し、整数カラムにテキストを挿入するような間違いを防ぎます。私はこの機能を気に入っており、その利用を促進するためにこの記事を書きました!

STRICTテーブルを作成するには、定義の末尾にSTRICTを追加します。以下のようにします。

-CREATE TABLE people (name TEXT);
+CREATE TABLE people (name TEXT) STRICT;

これだけです!しかし、これは何をするのでしょうか?

STRICTテーブルの利点

広義には、STRICTテーブルは他のSQLエンジンが行っているような、厳格な型付けを強制するのに役立ちます。

INSERT/UPDATE時の型不一致を防ぐ

最も重要なのは、STRICTテーブルは間違った型のデータをカラムに挿入するのを防いでくれることです。例えば、SQLiteは通常、INTEGERカラムにテキストを挿入することを許可しますが、STRICTテーブルではそれができません。

-- 非STRICTテーブルでは、どこにでも何でも挿入できます。
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- => 問題なく動作します

-- STRICTテーブルではそれが許可されないため、私はこれを好みます。
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- => エラー: INTEGERカラムにTEXT値を格納できません

個人的には、整数カラムにテキストを挿入したり、その逆を行ったりするのは間違いだと思います。私はSQLiteにこのような間違いを犯させたくありません!

同じ検証はUPDATEに対しても行われます。

注目すべきは、値がロスレスで変換できる場合、それは引き続き受け入れられるということです。例えば、文字列'123'は整数に完全に変換できるため、許可されます。これらの2行は、STRICTテーブルであっても同等です。

INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);
テーブル作成時の不正なカラム型を防ぐ

デフォルトでは、不正な型のカラムを作成できます。例えば、これらはすべて有効なSQLiteデータ型ではありませんが、受け入れられます。

-- SQLiteはこれらの型をサポートしていませんが、すべて受け入れられます。
CREATE TABLE tbl (name GARBAGE);
CREATE TABLE tbl (name DATETIME);
CREATE TABLE tbl (name JSON);
CREATE TABLE tbl (name UUID);
CREATE TABLE tbl (name BLOBB);

これらは開発者が意図したものではないと思います。いくつかはタイプミスであり、いくつかはSQLiteがサポートするデータ型の誤解であり、いくつかはひどい間違いです。

これらのステートメントのいずれかにSTRICTを追加すると、エラーになります。私の意見では、それが正しい動作です!

-- これらすべてがエラーになりますが、私はこれを好みます。
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;

許可されるのはINT、INTEGER、REAL、TEXT、BLOB、ANYのみです。

STRICTテーブルはカラム型も必須とするため、CREATE TABLE tbl (name)のようなことはできません。

ANY型で柔軟性を維持

それでもカラムに柔軟性が必要な場合は、ANYデータ型を使用できます。名前が示すように、これはSTRICTテーブルであっても、あらゆるものを許可します。

CREATE TABLE tbl (value ANY) STRICT;

-- カラムがANYであるため、これらすべてが有効です:
INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

私はこれに使う場面を見つけていませんが、あなたは見つけるかもしれません!

STRICTテーブルの欠点

私はSTRICTテーブルを好みますが、いくつかの欠点を共有しなければなりません。すべてが良いわけではありません!

既存のテーブルをSTRICT化できない

最初から厳格性を使用するのが最善だと思いますが、常に可能とは限りません。

残念ながら、テーブルをALTERしてSTRICT化する方法はないと思います。データを非STRICTテーブルからSTRICTテーブルにコピーする必要があると思います。以下のような方法です。

-- 1. 同じスキーマで新しいSTRICTテーブルを作成
CREATE TABLE new_people (name TEXT) STRICT;

-- 2. データをコピー(型が間違っていると危険!)
INSERT INTO new_people SELECT * FROM people;

-- 3. 古いテーブルを置き換える
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;

注意点として、非STRICTテーブルに無効なデータが含まれている場合、これはトリッキーになる可能性があります!例えば、古いデータに整数カラムにテキストが誤って含まれている場合、移行中にエラーが発生します。データをクリーンアップするか、キャストする必要があるでしょう。

コードベースで、すべての新しいテーブルをSTRICTにするというルールを設けることができます。それは有用かもしれません—少なくとも一部のテーブルは有効になります!しかし、テーブル間で検証が一貫しない可能性もあり、すべてのテーブルで弱い検証を行うよりも驚くべき結果になるかもしれません。これがあなたに適しているかどうかは、あなた次第です。

SQLiteの開発者は私に同意しない

SQLiteには「柔軟な型の利点」というページがあり、SQLiteの柔軟な動作は実際には良いと主張しています。

静的型付けと動的型付けの論争に踏み込むのはためらわれますが、ほとんどの場合、私は同意しません。私は個人的に、予期しないデータ型が微妙な頭痛の種を引き起こす多くのバグに遭遇しました。これらの間違いは、もっと大きな音で爆発してほしいと思います。しかし、SQLiteの開発者は私のSTRICTテーブルへの好みを共有していないようだということは注目に値します!

彼らは、柔軟なテーブルの良い用途として、「純粋なキー・バリュー・ストア」や、さまざまな型の「雑多な属性を格納する場所」などを挙げています。また、たとえば、乱雑なCSVを直接インポートしていて、データを失いたくない場合など、無効なデータを保持したい場合もあると述べています。私は依然としてSTRICTテーブルを好みますが、非STRICTテーブルにもいくつかの合理的なケースがあることを認めます。

(SQLiteソースコードには、非STRICTテーブルを「レガシー」と呼ぶコメントも少なくとも1つありますが、公式ドキュメントよりも信頼性は低いと考えています。)

SQLite 3.37.0以降でのみ利用可能

SQLiteは、2021年11月にリリースされたバージョン3.37.0でSTRICTテーブルを導入しました。古いバージョンのSQLiteを使用している場合、STRICTテーブルは使用できません。

古いバージョンのSQLiteは、STRICTテーブルを含むデータベースを読み込めないことに注意してください。例えば、最新バージョンのSQLiteでSTRICTテーブルを作成し、その後SQLite 3.36.0(STRICTテーブルが追加される前)でそのデータベースを読み込もうとすると、エラーが発生します。たとえSTRICTテーブルが既にデータベース内に存在していてもです。

パフォーマンスは?

STRICTテーブルは、少し余分な作業を行う必要があるため、理論的には遅くなる可能性があります。例えば、挿入または更新時にデータ型をチェックします。

しかし、実際にはこれは問題にならないと思います。私は、100カラムを持つテーブルに数百万行を挿入するハッキーなスクリプトを作成しましたが、試した複数のマシンで明白な違いはありませんでした。ディスク上のファイルサイズも同じでした。これは徹底的にテストしたわけではありませんが、何か見落としたことがあるかもしれませんが、STRICTテーブルがパフォーマンス上の問題を引き起こすとは思いません。

実際、カラムの親和性の不一致を誤って行うことがなくなるため、パフォーマンスが向上すると期待する人もいるかもしれません。しかし、これもテストしていません。

結論:私はSTRICTテーブルが好きです!

個人的には、STRICTテーブルの長所は短所を上回ると考えています。

私は一般的に、型が厳格に強制されることを好みます。これにより、ある種のミスが squashed され、良好なデータ整合性が強制されます。これらは万能薬ではありませんが、通常は追加が容易で、大きな効果があります。

もしあなたが過小評価されていると思うSQLiteの機能があれば、ぜひ教えてください。