SQLite - PRAGMA: 初心者向けの包括ガイド

こんにちは、データベース愛好家の皆さん！今日は、SQLiteのPRAGMAの世界に興味深く飛び込んでみましょう。PRAGMAという言葉を聞いたことがない方も安心してください。このチュートリアルが終わるまでに、あなたはプロのように使えるようになるでしょう！

PRAGMAとは？

まず、基本から始めましょう。SQLiteでは、PRAGMAは特殊なコマンドで、SQLiteライブラリの動作を変更するか、内部状態を照会することができます。PRAGMAをデータベースのcontropanelのように考えましょう。データベースの設定を調整したり、データベースの動作に関する情報を取得したりすることができます。

では、SQLiteで最もよく使われるPRAGMAをいくつか見てみましょう。

auto_vacuum Pragma

auto_vacuum PRAGMAは、データベースの整理好きなルームメイトのようなものです。データを削除するときに、データベースファイルを自動的に整理します。

PRAGMA auto_vacuum = FULL;

このコマンドは、auto_vacuumモードをFULLに設定し、SQLiteはデータベースファイルをできるだけ小さく保つために頑張ります。

cache_size Pragma

cache_size PRAGMAは、データベースに大きな脳を与えるようなものです。SQLiteがメモリに保持するページ数を決定し、アクセスを速くします。

PRAGMA cache_size = 10000;

これはキャッシュサイズを10,000ページに設定します。キャッシュが多ければパフォーマンスが向上しますが、メモリ使用量も増えます。

case_sensitive_like Pragma

このPRAGMAは、LIKE演算子が大文字小文字を区別するかどうかを決定します。データベースが眼鏡をかけて読むか、かけないで読むかの違いです。

PRAGMA case_sensitive_like = TRUE;

この設定では、'Apple' LIKE 'app%'はfalseになり、'Apple' LIKE 'App%'はtrueになります。

count_changes Pragma

count_changes PRAGMAは、データベース操作のスコアキーパーのようなものです。INSERT、UPDATE、DELETE文によって変更された行数を教えてくれます。

PRAGMA count_changes = ON;

これを設定した後、例えばUPDATE文を実行すると、SQLiteはどれだけの行が影響を受けたかを教えてくれます。

database_list Pragma

このPRAGMAは、データベースのディレクトリのようなものです。メインデータベースと接続している他のデータベース全てをリストアップします。

PRAGMA database_list;

これは接続している全てのデータベース、そのファイルパス、内部名を表示します。

encoding Pragma

encoding PRAGMAは、データベースが使用するテキストエンコーディングを設定または照会します。データベースがどの言語を話すかを選ぶようなものです。

PRAGMA encoding = "UTF-8";

これはデータベースエンコーディングをUTF-8に設定します。これは複数の言語をサポートするための一般的な選択です。

freelist_count Pragma

このPRAGMAは、データベースファイル内の未使用ページ数を教えてくれます。ストレージユニットの空きスペースを確認するようなものです。

PRAGMA freelist_count;

これはデータベースファイル内の未使用ページ数を返します。

index_infoとindex_list Pragmas

これらのPRAGMAは、データベースのインデックスの図書館司書のようなものです。インデックスの構造と内容に関する情報を提供します。

PRAGMA index_list(table_name);
PRAGMA index_info(index_name);

最初のコマンドはテーブル上の全てのインデックスをリストアップし、二つ目のコマンドは特定のインデックスに関する詳細情報を提供します。

journal_mode Pragma

journal_mode PRAGMAは、SQLiteが取引をどのように追跡するかを制御します。異なるタイプのバックアップシステムを選ぶようなものです。

PRAGMA journal_mode = WAL;

これはジャーナルモードをWrite-Ahead Logging（WAL）に設定し、多くの場合パフォーマンスを向上させることができます。

max_page_countとpage_count Pragmas

これらのPRAGMAは、データベースファイルのページ数を扱います。本のページ数を設定し、確認するようなものです。

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

最初のコマンドは最大ページ数を設定し、二つ目のコマンドは現在のページ数を教えてくれます。

page_size Pragma

このPRAGMAは、データベースのページサイズを設定または照会します。本のページサイズを決めるようなものです。

PRAGMA page_size = 4096;

これはページサイズを4096バイトに設定します。これは一般的な選択です。

parser_traceとsql_trace Pragmas

これらのPRAGMAは、データベースのデバッグモードをオンにするようなものです。SQLiteがコマンドをどのように処理しているかについて詳細な情報を提供します。

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

これらは、特定のクエリが期待通りに動作しない理由を理解する際に非常に役立ちます。

recursive_triggers Pragma

このPRAGMAは、トリガーが他のトリガーを再帰的に発火させるかどうかを制御します。データベース内でドミノ効果を許可するようなものです。

PRAGMA recursive_triggers = ON;

この設定では、トリガーが他のトリガーを発火させ、それが別のトリガーを発火させるなどすることができます。

schema_versionとuser_version Pragmas

これらのPRAGMAは、データベーススキーマのバージョン番号を設定または照会します。データベース構造のバージョンタグのようなものです。

PRAGMA schema_version;
PRAGMA user_version = 1;

最初のコマンドは現在のスキーマバージョンを照会し、二つ目のコマンドはユーザーバージョンを1に設定します。

secure_delete Pragma

このPRAGMAは、削除されたデータがどのように上書きされるかを制御します。書類をシュレッダーにかけるとか、ゴミ箱に捨てるかの違いです。

PRAGMA secure_delete = ON;

この設定では、SQLiteは削除されたデータをゼロで上書きし、復元を難しくします。

synchronous Pragma

このPRAGMAは、SQLiteがデータをディスクにどのように慎重に書き込むかを制御します。安全性と速度のバランスを取るようなものです。

PRAGMA synchronous = NORMAL;

これは中程度の安全性を設定し、完全な安全性（FULL）と最大の速度（OFF）の間をバランスよく取ります。

temp_storeとtemp_store_directory Pragmas

これらのPRAGMAは、SQLiteが一時データをどこに、どのように保存するかを制御します。作業中に在哪里に Scratchパペを置くかを選ぶようなものです。

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/path/to/directory';

最初のコマンドは一時データをメモリに保存し、二つ目のコマンドは一時ファイルのためのディレクトリを指定します。

writable_schema Pragma

このPRAGMAは、sqlite_masterテーブルを直接編集できるかどうかを制御します。データベース構造のマスターキーのようなものです。

PRAGMA writable_schema = ON;

この設定は非常に注意が必要です。正しく行わないと危険なため、データベースの根本的な構造を変更することができます。

以下に、私たちが議論したすべてのPRAGMAをまとめた表があります：

PRAGMA	説明
auto_vacuum	データベースの自動真空化を制御
cache_size	メモリに保持するページ数を設定
case_sensitive_like	LIKE演算子の大文字小文字区別を制御
count_changes	操作による変更された行数を報告
database_list	接続しているデータベースをリストアップ
encoding	データベースのテキストエンコーディングを設定または照会
freelist_count	データベースファイルの未使用ページ数を報告
index_info	特定のインデックスに関する情報を提供
index_list	テーブル上の全てのインデックスをリストアップ
journal_mode	取引の追跡方法を制御
max_page_count	データベースの最大ページ数を設定
page_count	データベースの現在のページ数を報告
page_size	データベースのページサイズを設定または照会
parser_trace	詳細なパース情報を有効に
recursive_triggers	トリガーが再帰的に発火させるかを制御
schema_version	スキーマバージョン番号を設定または照会
secure_delete	削除されたデータの上書き方法を制御
sql_trace	詳細なSQL実行情報を有効に
synchronous	データのディスクへの書き込み方法を制御
temp_store	一時データの保存場所を制御
temp_store_directory	一時ファイルのためのディレクトリを設定
user_version	ユーザー定義のバージョン番号を設定または照会
writable_schema	sqlite_masterテーブルを直接編集できるかを制御

以上で、SQLiteのPRAGMAに関する基本的な知識が身についたことでしょう。これらの強力なツールはデータベースの動作とパフォーマンスに大きな影響を与える可能性があるため、本番環境での使用前に十分にテストを行ってください。快適なコーディングを！

Credits: Image by storyset