Ruby / Rails関連

2025.06.19

SQLite on Railsシリーズ（12）SQLiteのテーブルスキーマやメタデータを一括で取得する（翻訳）

hachi8833

シェア
ツイート
ブックマーク
LINE

概要

原著者の許諾を得て翻訳・公開いたします。

英語記事: Table Schema and Metadata | Fractaled Mind
原文公開日: 2023/11/13
原著者: Stephen Margheim -- フルスタックRails開発者であり、RailsのSQLite強化作業の中心人物です。Rails 8+SQLiteによる学習動画サイトHigh Leverage Railsを主催しています。

参考: Rails 8はSQLiteで大幅に強化された「個人が扱えるフレームワーク」（翻訳）｜YassLab 株式会社

日本語タイトルは内容に即したものにしました。

SQLite on Railsシリーズ（12）SQLiteのテーブルスキーマやメタデータを一括で取得する（翻訳）

SQLiteデータベース内にある特定のテーブルに関する情報をすべて取得するにはどうすればよいでしょうか。情報はさまざまなソースに分散しており、構造もさまざまなので、1つのレポートにまとめるのは簡単ではありません。本記事では、必要な情報をすべて取得する方法を説明します。

テーブルに関するさまざまな情報を取得するために以下の4つのPRAGMAステートメントを利用できます。

PRAGMA table_list: このプラグマは、スキーマ内のテーブルとビューに関する情報を返します。
PRAGMA table_xinfo: このプラグマは、名前付きテーブル内のカラム（生成カラムや非表示カラムも含む）情報を1行ずつ返します。。
PRAGMA index_list: このプラグマは、指定されたテーブルに関連付けられた各インデックスの情報を1行ずつ返します。
PRAGMA foreign_key_list: このプラグマは、指定されたテーブルの外部キー制約ごとの情報を1行ずつ返します。

また、sqlite_schemaテーブルをクエリすることで、テーブルのCREATE TABLEステートメントを取得することも可能です。

これら5つの情報源を合わせると、テーブルについて必要な情報をすべて得られます。問題は、このすべての情報を1件のクエリで収集できないことです。5つのクエリを個別に実行して結果を結合する必要があります。

さらに、CREATE TABLEステートメントでしか推測できない情報もあります。たとえば、テーブルの主キーがAUTOINCREMENTかどうかを知るには、CREATE TABLEステートメントを確認する必要があります。これはPRAGMA table_xinfoステートメントではわかりません。

そこで、このすべての情報を収集してテーブルの構造とメタデータを一通り見渡せる、適切に構造化されたハッシュを返すメソッドを記述することにしました。
Railsアプリケーションの場合、このメソッドをApplicationRecordクラスに配置しておけば、任意のモデルで呼び出して必要な情報を取得できます。

訳注

以下のコードは、table_sqlの引数部におけるヒアドキュメント<<~SQL〜SQLより下のシンタックスハイライトが効かなくなっています。

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  def self.schema
    table_info = connection.execute("PRAGMA table_list(#{table_name});").first
    columns_info = connection.execute("PRAGMA table_xinfo(#{table_name});")
    index_info = connection.execute("PRAGMA index_list(#{table_name});")
    fk_info = connection.execute("PRAGMA foreign_key_list(#{table_name});")
    table_sql = connection.exec_query(<<~SQL, 'SQL', [table_name]).first
      SELECT sql
      FROM (
        SELECT * FROM main.sqlite_schema UNION ALL
        SELECT * FROM temp.sqlite_schema
      )
      WHERE type = 'table'
        AND name = ?;
    SQL

    column_names = columns_info.map { |column| column["name"] }

    collate_regex = /COLLATE\s+(\w+).*/i
    primary_key_autoincrement_regex = /PRIMARY KEY AUTOINCREMENT/i
    unquoted_open_parens_regex = /\((?![^'"]*['"][^'"]*$)/
    final_close_parens_regex = /\);*\z/
    column_separator_regex = /,(?=\s(?:CONSTRAINT|"(?:#{Regexp.union(column_names).source})"))/i

    column_defs = table_sql["sql"]
      .partition(unquoted_open_parens_regex)
      .last
      .sub(final_close_parens_regex, "")
      .split(column_separator_regex)
      .map do |definition|
        definition = definition.strip
        key = definition.partition(" ").first.gsub(/^"*|"*$/, "")
        [key, definition]
      end
      .to_h

    {
      schema: table_info["schema"],
      name: table_info["name"],
      sql: table_sql["sql"],
      without_rowid: table_info["wr"] == 1,
      strict: table_info["strict"] == 1,
      columns: columns_info.map do |column_info|
        column_string = column_defs[column_info["name"]]

        { name: column_info["name"],
          type: column_info["type"],
          sql: column_string,
          nullable: column_info["notnull"] == 0,
          default: column_info["dflt_value"],
          primary_key: column_info["pk"],
          kind: case column_info["hidden"]
                when 0 then :normal
                when 1 then :virtual
                when 2 then :dynamic
                when 3 then :stored
                end,
          collation: ($1 if collate_regex =~ column_string),
          autoincrement: column_string.match?(primary_key_autoincrement_regex) }
      end,
      indexes: index_info.map do |index_info|
        { name: index_info["name"],
          unique: index_info["unique"] == 1,
          origin: case index_info["origin"]
                  when "c" then :create_index
                  when "u" then :unique_constraint
                  when "pk" then :primary_key_constraint
                  end,
          partial: index_info["partial"] == 1 }
      end,
      foreign_keys: fk_info.map do |fk_info|
        { table: fk_info["table"],
          from: fk_info["from"],
          to: fk_info["to"],
          on_update: fk_info["on_update"],
          on_delete: fk_info["on_delete"],
          match: fk_info["match"] }
      end
    }
  end
end

このメソッドの優れた点は、扱いやすいハッシュを返すことです。次のようなスキーマの場合:

CREATE TABLE IF NOT EXISTS artists (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE
);
CREATE TABLE IF NOT EXISTS albums (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT COLLATE NOCASE UNIQUE,
  release_date DATE,
  artist_id INTEGER,
  FOREIGN KEY(artist_id) REFERENCES artists(id)
);
CREATE TABLE IF NOT EXISTS songs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  album_id INTEGER,
  FOREIGN KEY(album_id) REFERENCES albums(id)
);

albumsテーブルの出力は以下のようになります。


  name: "albums",
  sql: "CREATE TABLE albums (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT COLLATE NOCASE UNIQUE,
    release_date DATE,
    artist_id INTEGER,
    FOREIGN KEY(artist_id) REFERENCES artists(id)
  )",
  without_rowid: false,
  strict: false,
  columns: [
    { name: "id",
      type: "INTEGER",
      sql: "id INTEGER PRIMARY KEY AUTOINCREMENT",
      nullable: true,
      default: nil,
      primary_key: 1,
      kind: :normal,
      collation: nil,
      autoincrement: true },
    { name: "title",
      type: "TEXT",
      sql: "title TEXT COLLATE NOCASE UNIQUE",
      nullable: true,
      default: nil,
      primary_key: 0,
      kind: :normal,
      collation: "NOCASE",
      autoincrement: false },
    { name: "release_date",
      type: "DATE",
      sql: "release_date DATE",
      nullable: true,
      default: nil,
      primary_key: 0,
      kind: :normal,
      collation: nil,
      autoincrement: false },
    { name: "artist_id",
      type: "INTEGER",
      sql: "artist_id INTEGER",
      nullable: true,
      default: nil,
      primary_key: 0,
      kind: :normal,
      collation: nil,
      autoincrement: false }
  ],
  indexes: [
    { name: "sqlite_autoindex_albums_1",
      unique: true,
      origin: :unique_constraint,
      partial: false }
  ],
  foreign_keys: [
    { table: "artists",
      from: "artist_id",
      to: "id",
      on_update: "NO ACTION",
      on_delete: "NO ACTION",
      match: "NONE" }
  ]
}

SQLiteが認識しているテーブルやカラム、インデックス、外部キーに関するすべての情報を取得していることがわかります。情報は大量ですが、すべて役に立ちます。たとえば、カラムが主キーであるかどうかを知りたい場合は、以下のようにします。

schema[:columns].any? { |column| !column[:primary_key].zero? }

SQLiteは複合主キーをサポートしているので、schemaのprimary_key値には整数が使われます。したがって、songsテーブルの定義を以下のように調整すると、

CREATE TABLE IF NOT EXISTS songs (
  id INTEGER,
  title TEXT,
  album_id INTEGER,
  PRIMARY KEY(id, album_id),
  FOREIGN KEY(album_id) REFERENCES albums(id)
);

以下のようなスキーマが得られます。

{ schema: "main",
    name: "songs",
    sql: "CREATE TABLE songs (
    id INTEGER,
    title TEXT,
    album_id INTEGER,
    PRIMARY KEY(id, album_id),
    FOREIGN KEY(album_id) REFERENCES albums(id)
  )",
  without_rowid: false,
  strict: false,
  columns: [
    { name: "id",
      type: "INTEGER",
      sql: "id INTEGER",
      nullable: true,
      default: nil,
      primary_key: 1,
      kind: :normal,
      collation: nil,
      autoincrement: false },
    { name: "title",
      type: "TEXT",
      sql: "title TEXT",
      nullable: true,
      default: nil,
      primary_key: 0,
      kind: :normal,
      collation: nil,
      autoincrement: false },
    { name: "album_id",
      type: "INTEGER",
      sql: "album_id INTEGER",
      nullable: true,
      default: nil,
      primary_key: 2,
      kind: :normal,
      collation: nil,
      autoincrement: false }
  ],
  indexes: [
    { name: "sqlite_autoindex_songs_1",
      unique: true,
      origin: :primary_key_constraint,
      partial: false }
  ],
  foreign_keys: [
    { table: "albums",
      from: "album_id",
      to: "id",
      on_update: "NO ACTION",
      on_delete: "NO ACTION",
      match: "NONE" }
  ]
}

ここで、idカラムとalbum_idカラムは両方とも主キーの一部であり、各カラムのprimary_key値が主キー内のカラムの位置であることがわかります。この情報は、複合主キー内のカラムの順序を知りたい場合に便利です。

SQLiteデータベース内のテーブルに関するメタデータの完全なセットには、さまざまな用途があります。たとえば、別のデータベースのスキーマを生成したり、別のデータベースのスキーマを更新するためのマイグレーションを生成したりするのに使えます。スキーマの中間表現があれば、それも自由に利用できます。

興味深い用途を思いついた方は、Twitterの@fractaledmind でお知らせください。

SQLite on Railsシリーズ（01）Gitブランチごとにデータベースを切り替える（翻訳）

SQLite on Railsシリーズ（02）SQLiteをチューニングで強化する（翻訳）

シェア
ツイート
ブックマーク
LINE

Twitter: @hachi8833、GitHub: @hachi8833 コボラー、ITコンサル、ローカライズ業界、Rails開発を経てTechRachoの編集・記事作成を担当。これまでにRuby on Rails チュートリアル第2版のコンテンツ監修、Railsガイドのコンテンツ作成を担当。かと思うと、正規表現の粋を尽くした日本語エラーチェックサービス enno.jpを運営。実は最近Go言語が好きで、Goで書かれたRubyライクなGoby言語のメンテナーでもある。仕事に関係ないすっとこブログ「あけてくれ」は2000年頃から多少の中断をはさんで継続、現在はnote.comに移転。Amazonウィッシュリスト: https://bit.ly/32aAmiI

SQLite on Railsシリーズ（12）SQLiteのテーブルスキーマやメタデータを一括で取得する（翻訳）

SQLite on Railsシリーズ（12）SQLiteのテーブルスキーマやメタデータを一括で取得する（翻訳）

関連記事

開発人生25年で学んだ7つのソフトウェア原則（翻訳）

RubyにマージされたZJITの概要を理解する（翻訳）

Rails診察カルテ: アプリで何か変わったことをしてませんか？（翻訳）

Ruby: SorbetにRBSのインラインコメント機能が追加された（翻訳）

週刊Railsウォッチ: 書籍『Rails Scales!』、RubyにNamespaceがマージほか（20250515）

Rails 8.1: 生SQLやArelのブーリアン型カラムをTRUEやFALSEと比較できるようになった（翻訳）

Rails: Active Supportのdeep_dupの適切な使い所を理解する（翻訳）

Railsスケーリング（2）: アムダールの法則で高速化とコンカレンシーの関係を理解する（翻訳）

開発人生25年で学んだ7つのソフトウェア原則（翻訳）

Lorca: ノードで様々な値とアバターの髪色を連動させたい: 実践編

関連記事

CONTACT