SQLiteではSTRICTテーブルを優先して使うべき

 (evanhahn.com)
1 ポイント 投稿者 GN⁺ 3 시간 전 | 1件のコメント | WhatsAppで共有
  • テーブル定義の末尾に STRICT を追加すると、整数カラムに任意のテキストが入るような型エラーを早期に防ぎ、データ整合性を高められる
  • 挿入・更新時に型を検査しつつ、'123' のように損失なく変換可能な値は許容され、カラム型は INT, INTEGER, REAL, TEXT, BLOB, ANY に制限される
  • 複数の型を入れる必要があるカラムには ANY を指定でき、厳格な検証と柔軟な保存を1つのテーブル内で併用できる
  • 既存テーブルをそのままSTRICTに変更することはできず、新しいテーブルの作成とデータのコピーが必要で、既存の不正なデータは整理または型変換しなければならない
  • STRICTテーブルは SQLite 3.37.0以降 でのみサポートされ、理論上は検査コストがあるものの、非公式な実験では目立った性能差やファイルサイズ差は確認されなかった

STRICTテーブルの作成

  • SQLiteの STRICTテーブル は、他のSQLエンジンに近い、より厳密な型検査を適用する
  • CREATE TABLE 定義の最後に STRICT を付ければよい
CREATE TABLE people (name TEXT) STRICT;

挿入と更新時の型検査

  • 通常のSQLiteテーブルでは INTEGER カラムにも 'garbage' のようなテキストを保存できるが、STRICTテーブルでは 型不一致 がエラーとして扱われる
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- 正常に処理される

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- エラー: cannot store TEXT value in INTEGER column
  • 同じ検証は UPDATE にも適用され、保存後に値を変更する場合でも不正な型を防ぐ
  • ただし、値が損失なく変換できるならSTRICTテーブルでも許容される
    • 文字列 '123' は整数 123 に完全に変換できるため、以下の2つの挿入は同じように処理される
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

テーブル定義段階での型制限

  • 通常のテーブルでは、SQLiteがサポートしていない型名でもカラム宣言に使用できる
    • GARBAGE, DATETIME, JSON, UUID, 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 のみ
  • すべてのカラムに型指定が必要なため、CREATE TABLE tbl (name) のように型を省略することはできない

ANYで柔軟性を維持する

  • 型が一定でないデータを保存する必要があるなら、ANY カラムを使える
  • STRICTテーブル内でも ANY カラムには整数、テキスト、実数、BLOBなどすべての型を保存できる
CREATE TABLE tbl (value ANY) STRICT;

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テーブルを ALTER でそのままSTRICTテーブルに変える方法はないため、最初から厳格に作成するほうが簡単
  • 既存テーブルを移行するには、新しいSTRICTテーブルの作成 → データのコピー → 既存テーブルの置き換え という手順が必要になる
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • 既存データで、整数カラムにテキストが入っているなど型が不正な場合、コピー中にエラーが起こる可能性がある
    • 移行前にデータを整理するか、CAST で型変換する必要があるかもしれない
  • 新しいテーブルにだけSTRICTを適用する運用も可能だが、テーブルごとに 検証の厳しさ が異なり、すべてのテーブルが緩い場合より動作を予測しにくくなることがある

柔軟な型が適している場合

  • SQLite開発チームは 柔軟な型の利点 を別途まとめており、デフォルト動作が有用なケースを示している
  • 非STRICTテーブルが合理的になりうる用途は次のとおり
    • 複数の型をそのまま格納する 純粋なキー・バリューストア
    • 型の異なる各種属性を保管するための領域
    • 汚れたCSVを直接取り込み、無効な値も失わずに保持する必要がある場合
  • しかし、想定外の型は微妙なバグを引き起こすことがあるため、一般的なテーブルでは静かに許容するより即座にエラーにしたほうが有利
  • SQLiteのソースには非STRICTテーブルを legacyと呼ぶコメント もあるが、公式ドキュメント以上に信頼できる根拠とみなすのは難しい

バージョン互換性

  • STRICTテーブルは 2021年11月リリースの SQLite 3.37.0 で導入された
  • それ以前のSQLiteではSTRICTテーブルは使えない
  • 旧バージョンはSTRICTテーブルを含むデータベースも読み取れない
    • 最新のSQLiteでSTRICTテーブルを作成したあと、SQLite 3.36.0で同じデータベースを開くとエラーになる

パフォーマンスと保存容量

  • STRICTテーブルは挿入・更新時に データ型を追加で検査 するため、理論上は遅くなる可能性がある
  • 100カラムを持つテーブルに数百万行を挿入した非公式実験では、複数のコンピュータで 目立った性能差 は見られなかった
  • ディスク上のデータベースファイルサイズも同じだったが、厳密なベンチマークではないため、見落とされた差がある可能性はある
  • カラムアフィニティに合わない値が誤って保存されるのを防ぐことで、むしろ性能が向上する可能性もあるが、これは別途検証されていない

適用時に考えるべき点

  • STRICTテーブルはすべてのデータ問題を解決するわけではないが、型に関するミスを減らし、データ整合性を強化する
  • 多くの場合、テーブル定義に STRICT を追加するだけで済むため導入は簡単
  • 一般的なテーブルでは、厳格な型検証による利点が、移行の負担、旧バージョンとの非互換性、制限された柔軟性を上回る

関連記事

1件のコメント

 
GN⁺ 3 시간 전
Hacker News の意見

  • SQLite にはテーブルを ALTER して厳格モードに変更する機能がないため、非厳格テーブルのデータを厳格テーブルへコピーする必要がある、という点から着想を得て、sqlite-utils 4.1 に双方向の変換機能を追加した
    CLI では uvx sqlite-utils transform data.db mytable --strict、Python では db.table("mytable").transform(strict=True) として使える
    リリースノートは https://sqlite-utils.datasette.io/en/stable/changelog.html#v...、Python API ドキュメントは https://sqlite-utils.datasette.io/en/stable/python-api.html#...、CLI ドキュメントは https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... で確認できる

  • https://sqlite.org/flextypegood.html は、柔軟な型指定がデフォルトである理由と、今後も変わらない可能性が高いことを説明しているが、実際の経験とは合わない
    顧客名を整数型の Customer.creditScore に入れても簡単に見つけて修正できる、という説明とは異なり、壊れた行は復旧が難しく、データが完全に失われている可能性もある
    厳格な型検査は見つけやすいエラーだけを防ぐため、かえってバグの検出と修正を難しくする、という論理にもまったく同意しがたい

    • 信頼性の高いデータベース構成がもたらす安全性と信頼が、むしろデータのバグ修正を難しくするという見方は理解しにくい
      Postgres では可能な限り多くの検査と安全装置を追加することが多く、そうすれば、そもそも発生すべきでないエラーを後から探し回る必要がなくなる
    • MongoDB にも何でも保存できるという似た論理があったが、多くの人は実際に使った後、それがたいてい悪い特性だと気づいた
      これは厳密な工学原則というより、SQLite が作られた時代と作者の強い信念に由来する結果のように見え、多くの批判を受けた後も、どんな根拠を持ち出してでも立場を維持している印象がある
      Postgres の JSON や HSTORE のような型で望む柔軟性は提供できるが、無制限の型指定をデフォルトとして強制するより、必要なときに選べるようにするほうがほとんど常に望ましい

  • STRICT がデフォルトだとよいと思う
    この点を除けば、SQLite の開発者は素晴らしいツールを作ったすごい人だ

    • 外部キーでさえデフォルトでは有効になっておらず、PRAGMA foreign_keys = ON; を使う必要がある
      さらに大きな問題は、厳格テーブルには同等の pragma がなく、すべての CREATE TABLE に非標準の STRICT を付けなければならないことだ
      グローバルな STRICT pragma も検討されたが実装されておらず、関連資料は https://sqlite.org/foreignkeys.html と https://sqlite.org/forum/forumpost/1b9d073a37ca5998 で確認できる
    • SQLite は後方互換性を重視しており、デフォルト値をほとんど変更しない
      SQLite 3.53 向けのソフトウェアが 3.54 にアップグレードされた後、CREATE TABLE が突然厳格テーブルを作成してエラーを出し、全体が壊れる事態を避けるためだ
    • 外部キー制約も互換性を理由にデフォルトで無効化されているなど、似た問題がある
      かつて SQLite は外部キーの構文だけをサポートし、実際の機能は実装していなかったのだろうかと思ってしまう
    • 単なる文字列ではない、きちんとした日付・タイムスタンプ型も必要だ
    • データベースが誤って間違った型を入れることを許す理由はなく、SQLite の型安全性に対する哲学は残念だ
      数千台のデバイスに配布されたコードがブール値列に文字列 '1' と '0' を保存してしまい、それを整理しなければならなかったが、まったく楽しい作業ではなかった
      タイムスタンプ型もないためテキスト列に保存する必要があり、標準の日付・時刻関数でさえ ISO 形式の yyyy-mm-ddTHH:MM:SSZ ではなく、UTC だと暗黙に仮定しなければならない yyyy-mm-dd HH:MM:SS を生成する
      SQLite は本当に素晴らしいプロジェクトだが、一部の設計判断には困惑させられる

  • エンタープライズ向けSQL環境から来た立場では、フィールド型がデフォルトでは強制されないという理由でSQLiteを真剣に見ておらず、スマートフォンアプリのメタデータの基盤になったときも驚いた。
    これは、低レイテンシと単純さを理由にUDPを選んだあと、TCPの信頼性機能の大半をアプリケーション側で自前で作り直す、昔からあるネットワークの話を思い出させる。

    • 自前で実装すればTCPとは違う方式にでき、ときには大きな効果を得られる。QUICとHTTP/3がその例だ。
      しかし、非厳格なSQLiteの上に型チェックを自前実装しても、同じような強みが生まれるようには見えない。
    • 外部キーは2009年以前にはそもそもサポートされておらず、今でもデフォルトでは有効になっていない: https://sqlite.org/foreignkeys.html
    • こうした方式を実際のビジネスとして成功させることもある: https://aeron.io/
    • 型チェックが完全にサポートされていないなら、SQLiteを真剣に見ない姿勢は理解できるが、たいていのデータベースも完璧なデフォルト設定を提供しているわけではない。
      PostgreSQLもデフォルト値は低スペックなシステムを対象にしているため、最適な性能を出すには設定が必要だ。
      またTCPはストリームベースなので、多くのアプリケーションでHOLブロッキングの問題があり、UDP上に信頼性レイヤーを自前で作ったなら、多くの作業を経たあとでもTCPより良い結果を得られることがある。
    • JeepやBroncoに関心があるなら、一般的な自動車評論家を探すことはないだろう。
      彼らはうるさくて操縦性が悪いと評価するが、そもそも別の用途を狙った製品に対して、自分のユースケースだけが重要であるかのように接するからだ。

  • 厳格テーブルにはDateのような一部の型を使えないという欠点があるが、それでもデフォルトであるべきだ。
    複数のアプリケーションがデータベースを共有するなら、宣言された型を信頼できなければならないし、1つが数値列に文字列を保存すれば残りはすべて壊れてしまう。
    一方、SQLiteの主な用途である組み込みデータベースは通常1つのアプリケーションだけが使うため、新しいデータベースを作ってコピーする代わりにスキーマを発展させられるという利点もある。
    その場合、アプリケーションコードは混合型を含め、各列に何を期待すべきかを知っている。

    • SQLiteにはINTEGER、TEXT、BLOB、REAL、NUMERICの5種類のデータ型しかない: https://sqlite.org/datatype3.html
    • SQLiteには日付データ型がなく、任意のSELECTクエリにEXPLAINを呼んで、形式上指定した型名を得る方法もない。
      したがって、DATEやDATETIMEのような名前を使っても、それを推論することすらできない。
    • Dateは実際の型ではなく、単に数値アフィニティを持つ列が作られるだけだ。

  • 厳格モードは列の型表記を制限し、より意味のある名前を使えなくし、データベース型とアプリケーション型をマッピングするときにコードがその名前を活用できなくするため、むしろアプリケーション層のより厳格な型体系を妨げる。
    詳細はhttps://hn.algolia.com/?query=chrismorgan+strict+sqlite&type...にあり、Rustのsqlxのようなクレートでデータベースを扱うなら、厳格モードは避けたほうがよいと思う。

  • データベースとRPCには厳格な型を望むが、SQLiteは用途がやや異なるので、実際に使ってみるとhttps://sqlite.org/flextypegood.htmlをより理解できるかもしれない。
    たとえば、SQLite用ではない任意のスクリプトも偶然動くようにするという目標は、ほかのDBMSでは一般的に考慮されない特性だ。

    • その文書の長所・短所の項目には、核心となる論点が抜けている。
      柔軟な型付けの主な利点は、スキーマを簡単に発展させられることだ。
      組み込みデータベースの要件が変わるたびに新しいデータベースを作ってデータを移行する代わりに、その場でスキーマを変えられるし、1つのアプリケーションだけが読み書きするなら、整数列に文字列が追加されても驚くことではない。
      逆に、複数のアプリケーションがそれぞれのスケジュールで更新されながら同じデータベースを共有するなら、スキーマは契約なので、1つの誤った型が別のアプリケーションを壊す可能性がある。
      あるアプリケーションが作ったテーブルを別のアプリケーションが使うなら、合意したデータ型を厳格に守らなければならない。
    • ほとんどの場合、コードが型構造に従っているかをコンパイル時に確認し、あわせて開発するSQLiteスキーマが静的解析に必要な型情報を提供する。
      ランタイムで再度検査しても実質的な利益はなく、静的解析で文字列を入れると確認されたコードが突然変異して整数を入れることはない。
      SQLiteは、複数のアプリケーションがデータを共有し、第三者を信頼しなければならないPostgresとは違う。
      Postgresのような環境ではランタイム検証が必須だが、SQLiteは基本的に1つのアプリケーションと1つのデータベースのために設計されているため、コンパイル時に評価できる自分のコードだけを信頼すればよい。
      複数のアプリケーションが1つのファイルを共有する非典型的な状況では、厳格テーブルを有効にできる。

  • 動的データ型は単純なキー・バリューストアのような用途には合うかもしれないが、INTEGERがエラーなしに'hello world'を受け入れることと、NONSTRICTのようなキーワードやANY型を明示して初めてそうした値を入れられることのどちらが最小驚きの原則に合うのかを問うべきだ。
    SQLiteユーザーの大多数は、前者が可能だとは予想しないように思う。

    • SQLiteは型のないデータベースを志向しつつも、構文は標準SQLのように見せようとしたからかもしれない。
      他のDBMS向けのコードがSQLiteでも偶然動くことを望む、という説明とも一致する。
      それでも、ユーザーが型を省略したわけでもないのにINTEGERを明示してテキストを入れられるというのは非常に驚きだ。

  • 08123…のような値で始まるUUIDが8進数としてパースされ、一部が数値に誤変換されたことがある。
    混乱し面倒だったが、STRICTを適用してテーブル全体を作り直すことで解決した。

    • それはSQLiteではなく、言語ドライバーが過剰に手助けした結果だ。
      SQLiteには8進数型はない。

  • CREATE TABLE ... STRICT WITHOUT ROWID をデフォルトで使っており、あえて別の形にする理由がわからない