[sqlite] sqliteの内部テーブル

sqliteにはスキーマ、データベースファイル、テーブルのメタ情報を管理するための内部テーブルが用意されています。どんなテーブルが用意されていて、どのように活用できるかをまとめておきます。

テーブル一覧：

• sqlite_master
• sqlite_temp_master
• sqlite_sequence
• sqlite_stat1
• sqlite_stat2
• sqlite_stat3

参考："The SQLite Database File Format"の2.5 Storage Of The SQL Database Schema章

sqlite_master

sqlite_masterテーブルは、データベースファイル毎に一つだけ生成されます。このテーブルの中には、該当データベースファイルの中に生成された、以下の4種類のオブジェクトの情報が格納される。

• テーブル
• インデックス
• ビュー
• トリガー

スキーマ：

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

• type
• オブジェクトのタイプ。"table", "index", "trigger", "view"のいずれかが格納される。
• name
• オブジェクトの名前。typeが"table"もしくは"view"の場合はtbl_nameと一致する。
• tbl_name
• オブジェクトが所属するテーブルの名前。
• rootpage
• テーブル及びインデックスのroot b-tree pageの番号。ビュー、トリガー、バーチャルテーブルの場合は0もしくはNULLが格納される。
• sql
• オブジェクトを生成する際に実行されたSQL文（CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE TRIGGER等）が格納される。

このテーブルの利用例：

  SELECT name FROM sqlite_master
  WHERE type='table'
  ORDER BY name;

例えば上記のSQLを実行すると、DBファイル中のテーブルの一覧がテーブル名でソートした状態で出力される。

sqlite_temp_master

sqlite_temp_masterテーブルは、テンポラリデータベースにおいて、sqlite_masterテーブルの代わりに生成される。名前が異なる以外はsqlite_masterと仕様は同じ。

sqlite_sequence

sqlite_sequenceテーブルは、データベースファイル毎に一つだけ生成される。データベース内に存在しているテーブルのINTEGER PRIMARY KEYの最大値を保持する。この最大値はAUTOINCREMENTの動作で利用される。

スキーマ：

CREATE TABLE sqlite_sequence(name,seq);

sqlite_stat1

ANALYZEコマンドで生成される内部テーブル。テーブルやインデックスの補助的な情報が格納され、query plannerが効率的なクエリを見つけるために利用される。 アプリケーションからupdate, delete from, insert into, dropを実行できるが、createとalter tableは実行できない。

スキーマ：

CREATE TABLE sqlite_stat1(tbl,idx,stat);

sqlite_stat2

SQLiteのバージョンが3.6.18と3.7.8の間で、かつ、SQLITE_ENABLE_STAT2オプションでコンパイルされている場合にのみ利用される。インデックス中のキーの分散に関する補助的な情報が格納される。

スキーマ：

CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);

sqlite_stat3

SQLiteのバージョンが3.7.9以降で、かつ、SQLITE_ENABLE_STAT3オプションでコンパイルされている場合にのみ生成・利用される。インデックス中のキーの分散に関する情報、および、query plannerがよりよいアルゴリズムを生成するための情報が格納される。

スキーマ：

CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);