SQLite

どのようなものか

SQLiteは、それを使うプログラムの内部でライブラリーとして動くSQLデー
タベースエンジンです。サーバープロセスもネットワークポートも管理者も
必要なく、テーブル、インデックス、トリガー、ビューを、ディスク上の一
つの通常のファイルにまとめて保存します。`sqlite3`という名前も登場し
ますが、これはコマンドラインシェルの名前であり、Cインターフェースの
全関数に付く接頭辞です。プロジェクトが配布する一つのCソースファイル
は、`sqlite3.c`という名前です。プロジェクト自身は、自己完結型、サー
バーレス、設定不要、トランザクション対応と要約し、Oracleよりも
`fopen()`と比較するよう求めています。置き換える対象は、自作のデータ
ファイルであり、データベースサーバーではありません。

プロジェクトは2000年5月9日に始まり、コードはパブリックドメインにある
ため、製品に組み込んで出荷する前にライセンスの許諾を得る必要はありま
せん。ファイル形式も、この選択の利点の一つです。32ビットのマシンで
作ったデータベースを64ビットのマシンへ、あるいはビッグエンディアンと
リトルエンディアンのプロセッサー間でコピーし、そのまま開けます。開発
者は2050年までSQLiteを支える意向を示し、米国議会図書館は、そのファイ
ルをデータ保存用の推奨形式に挙げています。すべての機能を有効にして
も、ライブラリーは900KiB未満に収まることがあります。そのため、サー
バーに加え、携帯電話、自動車、時計、デスクトップアプリケーションにも
組み込まれています。

内部には、結合、サブクエリ、ビュー、トリガーを備えた本格的なSQLエン
ジンがあります。トランザクションは、書き込みの途中で電源が切れても、
原子性、一貫性、分離性、耐久性を保ちます。サーバー型データベースとの
違いを生む設計上の選択は二つあります。一つ目は同時実行です。同時に読
み取る接続数に制限はありませんが、どの瞬間でも書き込めるのは一つだけ
で、ロックはファイル全体に及びます。二つ目は型の扱いです。列に宣言し
た型は、SQLiteがアフィニティーと呼ぶ、優先的に使う型を示しており、実
際の型は個々の値に属します。そのため、`INTEGER`と宣言した列でも、数
値に見えない文字列を受け付けます。

ほかの選択肢との違いは明確です。PostgreSQL、MySQL、SQL
Serverは、多くのマシンがネットワーク越しに利用する共有サービスです。
SQLiteは、一台のホスト上の一つのアプリケーションが使う専用の保存領域
です。CSVやJSONファイルと比べると、クエリ言語、インデックス、制約、
トランザクションが加わります。その代わりに依存ライブラリーが一つ増え
ますが、多くの言語はすでにそれを同梱しています。

なぜ重要なのか

多くのソフトウェアにとって、データベースを巡る難問は、どれだけ速いか
ではなく、誰が運用するかです。サーバーには、インストール、アップグ
レード、セキュリティー対策、監視、バックアップが必要で、そのすべてを
行う人に費用を払わなければなりません。SQLiteは、この運用業務一式を取
り除きます。データベースを開くことはファイルを開くことであり、バック
アップはそのファイルのコピーです。使用中ならバックアップAPIを通じて
コピーします。テストスイートでは、各テストに、それぞれ新しいデータ
ベースを用意できます。

ファイルでできることも変わります。独自形式を考え、そのパーサーを書
き、保存途中でクラッシュしないよう願う代わりに、プログラムの状態を
SQLiteに保存すれば、原子的な保存を追加実装なしで得られます。プロジェ
クトも、この用途を明示しています。アプリケーションのファイル形式、シ
ステム間でデータを渡す入れ物、そして別の形で届いたデータを並べ替えた
り集計したりする一時的なデータベースです。システム間の受け渡しでは、
受け取り側はパーサーを書く代わりにSQLを実行します。

判断すべきことは限られており、コードを一行も書く前に確認できます。書
き込むプロセスはいくつで、何台のマシンで動くのでしょうか。一台のホス
ト上の一つのプログラムが、短時間ずつ書き込むのはSQLiteの得意分野であ
り、アクセス量が少ない、あるいは中程度の多くのWebサイトが、SQLiteで
無理なく稼働しています。複数のサーバーが同じデータに書き込む場合や、
多数の利用者が絶えず書き込む負荷には、クライアントサーバー型データ
ベースが向いています。一つのファイルに最大281テラバイトを保存できる
ため、容量が判断を左右することはまれです。決め手は、書き込みを行う側
の数です。

得意分野の中でも、考慮すべき制約があります。`ALTER
TABLE`が対応するのは、テーブル名や列名の変更、列の追加や削除だけで
す。そのため、それ以外の大半の構造変更では、新しいテーブルを作り、行
をコピーし、名前を入れ替える必要があります。外部キーは、接続ごとに有
効化するまで無効です。また、拒否するようテーブルを宣言しなければ、緩
やかな型の扱いにより、不適切なデータが気付かれないまま入ります。

実際の活用例

記録されている開発段階では、一つのSQLiteファイルにある23のテーブル
が、CV、推薦者一覧、ポートフォリオ、カバーレターを生成するシステムの
正本でした。実績や企業から、プロフィールや推薦者情報までを格納してい
ました。一つのコマンドで、空のSQLiteファイルからそのデータベースを構
築し、19の手順を順番に実行していました。各手順を安全に二度実行できる
ため、既存のデータベースを再構築しても行が重複しませんでした。6種類
の出力形式は、その一つのファイルを読む、合計10,580行の41のPythonモ
ジュールから生成され、実行時の依存ライブラリーはちょうど二つでした。
特定の読者向けの文書は、コピーを編集するのではなく、プロフィールの指
定に従って実績を選ぶクエリで作っていました。代償は明確で、このシステ
ムが文書を作る唯一の手段になっていたことです。

記録されているエクスポート段階では、このWebサイト自身のポートフォリ
オも同じデータベースから生成していたため、それがビルドの入力でもあり
ました。エクスポーターはSQLiteファイルを読み取り専用で開き、495ペー
ジを書き出しました。対象は3言語で、Pythonの標準ライブラリーだけを使
い、生成システムの環境がなくてもサイトをビルドできるよう、出力をコ
ミットしていました。ページのアドレスは、実績ごと、アドレスごとに一行
ずつ保存したため、文を言い換えてもページが移動しなくなりました。この
作業では、次の節で説明する落とし穴も見つかりました。文字列の列に対す
る期間の比較が、32行すべてに一致してしまったのです。

結果を確認するだけという最小限の使い方は、最も典型的な利用法でもあり
ます。電力の生の計測値を地域ごとの毎時消費量に変換するパイプライン
は、結果をCSV、または確認用の軽量なSQLiteデータベースに書き出しまし
た。Python版は1日分を34ms、30日分を180msで処理しました。こうして使う
データベースには設定作業がまったく不要で、`sqlite3`シェルさえあれ
ば、誰でもファイルを開いてクエリを実行できます。

問題が起きるところ

SQLiteの問題の多くは、小さなPostgreSQLだと考えて扱うことで生まれま
す。仕組みも、制約の現れ方も異なるエンジンです。

年のフィルターが、すべての行に一致する。
SQLiteが比較前に値を変換するのは、いずれかの側にアフィニティーがある
場合だけです。`substr()`のような関数の結果にはアフィニティーがなく、
数値リテラル単体にもありません。そのため、SQLiteは文字列`'2019'`と整
数`2025`をそのまま比較します。そして、SQLiteの並び順では、どの数値
も、どの文字列よりも前に来ます。

```sql
CREATE TABLE stint (period TEXT, hours TEXT);
INSERT INTO stint VALUES
  ('2019-03', '9'), ('2024-11', '10'), ('2026-01', '42');

SELECT count(*) FROM stint
WHERE substr(period, 1, 4) >= 2025;                   -- 3

SELECT count(*) FROM stint
WHERE CAST(substr(period, 1, 4) AS INTEGER) >= 2025;  -- 1
```

文字列と数値が混在する箇所では、明示的に型変換します。新しいテーブル
では、意図した型を宣言し、バージョン3.37.0から使える`STRICT`キーワー
ドを加えます。型を厳格に検査する`INTEGER`列でも`'42'`は受け付け、数
値として保存しますが、`'ten'`はそのまま保存せず、エラーとして拒否し
ます。

書き込みが「database is locked」で失敗し始める。
一つの接続が書き込みロックを持っており、二つ目の書き込み側は、待つ
か、直ちに断念します。一般的な原因は、長いトランザクションや、既定の
ロールバックジャーナル方式で読み取り側が書き込み側を待たせていること
です。読み取り側と書き込み側が互いを妨げなくなる先行書き込みログ方式
へ切り替え、書き込みトランザクションを短く保ち、ロック待ちのタイムア
ウトを設定します。書き込み側がすぐエラーになる代わりに、少し待てるよ
うにするためです。本当に多数の書き込み側が絶えず動くなら、サーバー型
へ移るべき兆候です。

共有ドライブ上でファイルが破損する。
SQLiteはOSのファイルロックに依存しますが、多くのネットワークファイル
システムでは、そのロックの実装に不具合があり、二台のマシンがファイル
の同じ部分へ同時に書き込むことがあります。先行書き込みログ方式は、
ネットワークファイルシステムではまったく動きません。データベースを使
うすべてのプロセスが、一台のホスト上でメモリーを共有する必要があるた
めです。データベースは、書き込む一つのプログラムから見てローカルの
ディスクに置き、ほかのマシンがデータを必要とするなら、その手前にサー
バーを置きます。

参照先の親が消えても、行が残る。 外部キー制約はスキーマに宣言されて
いても、接続ごとに`PRAGMA foreign_keys =
ON`を実行するまで無効です。そのため、このプラグマを実行していない接
続で削除すると、警告もなく親のない行が残ります。アプリケーションが接
続を開く処理を一か所にまとめ、そこで必ずプラグマを設定し、有効になっ
ていることをテストします。

参考資料

  * SQLiteについて —
プロセス内で動き、サーバーを必要とせず、パブリックドメインであり、一
つのファイルを異なる環境で使えることを説明する、プロジェクト自身の概
要です。

  * SQLiteのデータ型 — 上の落とし穴の背景となる、ストレージクラス、
型アフィニティー、比較の規則を説明しています。

  * STRICTテーブル — 列に宣言した型をテーブルに強制させる方法です。

  * 先行書き込みログ — 読み取り側と書き込み側を並行して動かす仕組み
と、一台のホストに限定される理由です。

  * SQLiteに適した用途 — 適する場面、クライアントサーバー型がより適
する場面、確認項目の一覧です。

  * SQLiteの外部キー対応 — 制約が既定で無効である理由と、有効化する
方法です。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/sqlite/	engineer.company	70
