SchemaSpyとSQLiteでデータベースのメタ情報データベースを作る

groovy sql

なんだかよくわからんタイトルになってしまった。


要するにだ。SchemaSpyが吐き出すレポートは便利なんだけど，閲覧するくらいしか役に立たないんで，いっそのことデータベースに格納してしまえ，という話。


テーブルやカラムのメタ情報が手元にあれば，コードやデータを自動生成させる元ネタになってうれしいんだが，DESC文はSQL*Plusからじゃないと使えないし，JDBC経由でメタ情報抜き出すのしんどいで結構やる気がそがれる。で，よくよく考えてみれば，SchemaSpyがそうゆうこと（メタ情報の抽出）やってるんだから，それを丸ごと拝借してしまえと思い，勢いこんなスクリプトを組んでみたよ。
＃とりあえず，OracleXEのデモデータベースを相手にしてる。

import groovy.sql.Sql
import net.sourceforge.schemaspy.SchemaSpy
import net.sourceforge.schemaspy.model.Database

new File('./dbinfo.db').delete()
def sqlite = Sql.newInstance("jdbc:sqlite:dbinfo.db", "org.sqlite.JDBC")
sqlite.execute """
create table table_info (
  pkey integer primary key,
  name text,
  comments text
)
"""
sqlite.execute """
create table column_info (
  pkey integer primary key,
  fkey integer,
  name, type, length, decimal_digits, details_size, is_nullable,
  is_auto_updated, is_unique, is_primary, comments
)
"""

def dbname = 'jdbc:oracle:thin:@localhost:1521:xe'
def props = new Properties()
props.selectTableCommentsSql = 'select table_name, comments from all_tab_comments where owner=:owner'
props.selectColumnCommentsSql = 'select table_name, column_name, comments from all_col_comments where owner=:owner'
def pattern = ~".*"

def oracle = Sql.newInstance(dbname, 'hr', 'hr', 'oracle.jdbc.OracleDriver')

SchemaSpy spy = new SchemaSpy(oracle.connection, oracle.connection.metaData, dbname, 'HR', '', props, pattern, 1)
spy.database.tables.eachWithIndex {table, idx1 ->
  println "$idx1, $table.name, $table.comments"
  sqlite.execute("""
insert into table_info (pkey, name, comments)
values (${idx1}, ${table.name}, ${table.comments ?: ''})
""")
  table.columns.eachWithIndex {column, idx2 ->
    def key = idx1 * 100 + idx2
    println "$key, $column.name, $column.type, $column.length, $column.primary, $column.comments"
    sqlite.execute("""
insert into column_info (
  pkey, fkey, name, type, length, decimal_digits, details_size,
  is_nullable, is_auto_updated, is_unique, is_primary, comments
) values (
  ${key}, ${idx1}, ${column.name}, ${column.type}, ${column.length}, ${column.decimalDigits}, ${column.detailedSize},
  ${column.nullable}, ${column.autoUpdated}, ${column.unique}, ${column.primary}, ${column.comments ?: ''}
)
""")
  }
}

このスクリプトを動かすポイントは以下の通り。

  • SchemaSpyやSQLite，JDBCドライバとかは，まとめて $GROOVY_HOME/lib に入れておく。
  • Oracle以外の設定は，SchemaSpyの "net/sourceforge/schemaspy/dbTypes/" にあるプロパティを参考にする（上の設定も手抜きしてるから，Index情報とか引いてこないよ）。

にゃっはっは。この手のアイデア勝負ネタがカチっとハマるのは実に楽しいねぇ。
あとは煮るなり焼くなり好きにするよー。:-D