PostgreSQLのスキーマ抽出

既存のテーブルからDAOを生成するためには

既存のテーブルからDAOを生成するには、そのテーブルのスキーマ情報が必要だ。

テーブル名を与えることで、カラム名と型が取れれば、とりあえず値オブジェクトの生成とINSERT,UPDATE,DELETEまでは生成可能となる。

既存テーブルという前提だからCREATE TABLEは不要だろう。

すると残りは、キーを指定したファインダーメソッドに必要な情報ということになる。

これらは、各データベースマネージャが持つスキーマ情報から得ることができる。

ここでは、PostgreSQLを利用してみよう。

PostgreSQLのメタデータ

PostgresSQLのメタデータはシステムカタログと呼ばれる一連のテーブルに格納されている。

この中で参照する必要があるのは次の4つのテーブルだ。

pg_class

テーブル、インデックスなどの情報

pg_type

データ型の情報

pg_index

インデックスとテーブルの関連

pg_attribute

カラム情報

詳細については開発者ガイドを参照すればよい。

指定されたテーブルのスキーマを取り出すプログラム

いきなり作ってみる。データ型については処理系固有なので直接文字列を使用して参照することにする。

#!/usr/bin/ruby -Ke

require 'postgres'

class TypeMetadata
  def initialize(oid, conn)
    r = conn.query("SELECT DISTINCT typname,typlen FROM pg_type WHERE oid=#{oid}")[0]
    @name = conv(r[0])
  end

  attr_reader :name

 private
  @@convt = { 'int2'=>'smallint', 'int4'=>'integer', 'int8'=>'bigint',
              'float4'=>'real', 'float8'=>'double precision',
              'bpchar'=>'char'
            }
  def conv(type)
    r = @@convt[type]
    return type if r.nil?
    r
  end
end

class << TypeMetadata
  Types = {}
  def get(oid, conn)
    if Types[oid].nil?
      Types[oid] = TypeMetadata.new(oid, conn)
    end
    return Types[oid]
  end
end

class ColumnMetadata
  def initialize(name, type, tlen, vlen, notnull, pkey)
    @name = name
    @type = type
    @length = nil
    @fixlen = nil
    @not_null = (notnull != 'f')
    @primarykey = pkey
    if tlen.to_i >= 0
      @fixlen = tlen.to_i
    else
      len = vlen.to_i - 4
      if len >= 0
        if type.name == 'numeric'
          d = len & 0xffff
          if d > 0
            @length = sprintf('%d,%d', len >> 16, d)
          else
            @length = (len >> 16).to_s
          end
        else
          @length = len.to_s
        end
      end
    end
  end

  attr_reader :name, :length

  def not_null?()
    @not_null
  end

  def primarykey?()
    @primarykey
  end

  def type_name()
    @type.name
  end

  def to_s()
    s = sprintf("#{@name} #{@type.name}#{@fixlen || @length.nil? ? '' : '(' + @length + ')'}")
    if @not_null
      s << ' not null'
    end
    if @primarykey
      s << ' primary key'
    end
    s
  end

end

class TableMetadata
  include Enumerable

  def initialize(name, conn)
    @name = name
    @cols = []
    oid = conn.query("SELECT DISTINCT oid FROM pg_class WHERE relname = '#{name}'")[0][0]
    r = conn.query("SELECT indkey,indisprimary FROM pg_index WHERE indrelid=#{oid}")
    key = []
    r.each do |x|
      p x if $DEBUG
      next if x[1] != 't'
      key = x[0].split
    end
    r = conn.query("SELECT attname,atttypid,attlen,attnum,atttypmod,attnotnull,attisdropped " +
                     "FROM pg_attribute WHERE attrelid=#{oid}")
    r.each do |x|
      p x if $DEBUG
      cn = x[3].to_i
      next if cn < 0
      next if x[6] != 'f'
      @cols << ColumnMetadata.new(x[0], TypeMetadata.get(x[1], conn), x[2], x[4], x[5], key.include?(x[3]))
    end
  end

  def show_schema()
    @cols.each do |x|
      puts x.to_s
    end
  end

  def each
    @cols.each do |x|
      yield(x)
    end
  end

  def columns()
    @cols
  end

end

if __FILE__ == $0
  if ARGV.length == 0
    puts('usage: ruby pgscm.rb table [more talbe...]')
  else
    psc =  PGconn.connect(nil, nil, nil, nil)
    ARGV.each do |tbl|
      tbl = TableMetadata.new(tbl, psc)
      tbl.show_schema
    end
    psc.close
  end
end

ま、こんなもんかな。

注釈

    r = conn.query("SELECT indkey,indisprimary FROM pg_index WHERE indrelid=#{oid}")
    key = []
    r.each do |x|
      p x if $DEBUG
      next if x[1] != 't'
      key = x[0].split
    end

の部分は明らかに臭い。indisprimaryのチェックをWHERE以下に指定すべきに見える。

それはその通りなのだが、この後、ファインダーメソッドを生成することを考えると、ここでは読み飛ばしている非プライマリーキーによるインデックスも生成対象になるわけだ。したがって、本来は、すべてのキーの組み合わせ情報とインデックス名もクラスとして保持することになる。

実際の動作を検証する

runit使えよ。

と思いながらも、こんなテストコードでいいや。目で確認だ。

require 'pgscm'

psc =  PGconn.connect(nil, nil, nil, nil)
begin
  psc.exec('CREATE TABLE pgs_test (' +
	 ' row1 bigint primary key,' +
	 ' row2 boolean,' +
	 ' row3 bytea not null,' +
	 ' row4 character(8),' +
	 ' row5 date,' +
	 ' row6 double precision,' +
	 ' row7 integer,' +
	 ' row8 numeric(8,4),' +
	 ' row9 decimal(12),' +
	 ' row10 real,' +
	 ' row11 smallint,' +
	 ' row12 text,' +
	 ' row13 time,' +
	 ' row14 timestamp,' +
	 ' row15 varchar(253))')
  t = TableMetadata.new('pgs_test', psc)
  t.each do |col|
    puts col
  end
rescue
  puts $!.message
end
psc.exec('drop table pgs_test')
psc.close

それでは動かしてみよう。CREATE TABLEが作成できるような情報が取得できていればOKとみなして良いだろう。

$ ruby pgtest.rb
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'pgs_test_pkey' for table 'pgs_test'
row1 bigint not null primary key
row2 bool
row3 bytea not null
row4 char(8)
row5 date
row6 double precision
row7 integer
row8 numeric(8,4)
row9 numeric(12)
row10 real
row11 smallint
row12 text
row13 time
row14 timestamp
row15 varchar(253)

とこのように、カラム名、型、長さともに取り出すことができた。

使用方法

テーブル名とコネクションオブジェクトからTableMetadataとColumnMetadataのインスタンスが生成できるから、後は必要に応じて処理を行えば良い。