GraphViz::DBI で ER図を吐き出す

この記事は, Perl Advent Calendar 2019 の20日目の記事です。 19日は、doikojiさんの、「WINI & cal: perlベースの新しい簡易マークアップ言語WINIで来年のカレンダーを作りましょう！ 」でした。

この記事は、もともとWanoグループのAdvent Calendar に書くつもりでしたが、Perl の Advent Calendarに空きがあったので、 会社の方はRustの記事を載せました。よろしければ、そちらもどうぞ。

qiita.com

閑話休題。

最近、DocBase、Growi 、Gitlab の Wiki (設定すれば)などPlantUMLでの表示をサポートするものが増えてきています。 自分の手でGraphVizのER図を書くのも可能ですが、正直、だいぶ面倒ですし、テーブル数が多いとやる気が出ません。

PerlのGraphViz::DBIでさくっと書いてみましょう。

ですが、僕の担当している、とあるシステムはテーブル数が1000近くある関係で、はきだされたものをそのままPlantUMLに渡すと、長すぎて壊れてしまいました。 そらそうですね。

これは同種のテーブルが負荷対策の関係で分けたりしているのが原因で非常に多くなっちゃってるのですが、もし同様なケースでしたら、似たようなテーブルはグルーピングするなりの処理をしたり、 ER図をいくつかのグループに分割するなどしたほうが良いですね(例えば、カテゴリだけをグルーピングするとか、特定機能に関わるテーブルだけをグルーピングするなど)。

下記のようにグルーピングの定義をしてやると良いと思います。 この時に、似たようなテーブルがいくつかある場合は、1つのテーブルのみをグループの中に入れてやれば良いでしょう。

tie my %GROUP_TABLES, 'Tie::IxHash';
%GROUP_TABLES = (
    # グルーピング => 表示したいテーブルの正規表現を書く(※正規表現は、`schema`.`table_name` にマッチするようにする)
    'グループ1'       => qr{},
    'グループ2'       => qr{},
  );

出力時に下記のようにすれば、各グループ毎にER図を出力できます。

foreach my $group (keys %GROUP_TABLES) {
    my $filter = $GROUP_TABLES{$group};
    my $g = __PACKAGE__->new($dbh);
    $g->{tables} = [ grep {$_ =~ $filter} $g->get_dbh->tables];
    my $txt = $g->graph_tables->as_text;

　# これは、表示を簡略化するためなので、グルーピングとは関係ないです。 
   # `schema`.`table_name` => table_name だけにしています
    $txt =~ s{`$SCHEMA`\.`(.+?)`}{$1}g;
    print "## $group\n\n";
    print "```plantuml\n\@startuml\n", $txt, "\n\@enduml\n```\n\n";
}

他にも、削除フラグとか更新日時とか、ほとんどのテーブルにあるけど、特に表示したくないものもあるでしょうから、

my %IGNORE_COLUMNS = (
  updated_at => 1,
  delete_flag => 1,
  created_at => 1,
);

のように定義しておいて、graph_tables をオーバーライドして、調整するとよいかもしれません(後のコードを参照)。

また、GraphViz::DBI の外部キーの実装は、下記のようになっています。

sub is_foreign_key {
    # if the field name is of the form "<table>_id" and
    # "<table>" is an actual table in the database, treat
    # this as a foreign key.
    # This is my convention; override it to suit your needs.

    my ($self, $table, $field) = @_;
    return if $field =~ /$table[_-]id/i;
    return unless $field =~ /^(.*)[_-]id$/i;
    my $candidate = $1;
    return unless $self->is_table($candidate);
    return $candidate;
}

コメントを訳すと、

フィールド名が、"<table>_id"で、"<table>"が実際にDBに存在する
テーブルの場合、これを外部キーとします。
これは、自分の慣例なので、必要に応じてオーバーライドしてください。

とした、ざっくりな感じなので、コメントの通り継承してオーバーライドしました。

my %SPECIAL_FOREIGN_KEY = (
   '特殊な命名の外部キー1' => "`$SCHEMA`.`テーブル`", # 外部キーの参照しているテーブル
   '特殊な命名の外部キー2' => "`$SCHEMA`.`テーブル`",
   # ...
);

sub is_foreign_key {
    my ($self, $table, $field) = @_;

    my $candidate;
    if (not $candidate = $SPECIAL_FOREIGN_KEY{$field}) {
    # TABLE_NAME_ID は TABLE_NAME の外部キーとみなす
        return unless $field =~ /^(.*)[_-]id$/i;
        $candidate = "`$SCHEMA`.`$1`";
    }
    return unless $self->is_table($candidate);
    return $candidate;
}

僕も同じような慣例なので、例外的なものを追加するくらいでOKでした。

全体像は、こんな感じになります。

use strict;
use DBI;
use parent 'GraphViz::DBI';

my $SCHEMA = "スキーマ";
my $dbh = DBI->connect("dbi:mysql:$SCHEMA;host=DB_HOST", 'user', 'password');

use Tie::IxHash;

tie my %GROUP_TABLES, 'Tie::IxHash';
%GROUP_TABLES = (
    # グルーピング => 表示したいテーブルの正規表現を書く
    'グループ1'       => qr{},
    'グループ2'       => qr{},
  );

my %SPECIAL_FOREIGN_KEYS = (
   '特殊な命名の外部キー1' => "`$SCHEMA`.`テーブル`", # 外部キーの参照しているテーブル
   '特殊な命名の外部キー2' => "`$SCHEMA`.`テーブル`",
   # ...
);

my %IGNORE_COLUMNS = (
  created_at => 1,
  delete_flag => 1,
  updated_at => 1,
);


sub is_foreign_key {
    my ($self, $table, $field) = @_;

    my $candidate;
    if (not $candidate = $SPECIAL_FOREIGN_KEYS{$field}) {
    # TABLE_NAME_ID は TABLE_NAME の外部キーとみなす
        return unless $field =~ /^(.*)[_-]id$/i;
        $candidate = "`$SCHEMA`.`$1`";
    }
    return unless $self->is_table($candidate);
    return $candidate;
}
sub graph_tables {
    my $self = shift;

    my %table = map { $_ => 1 } $self->get_tables;

    for my $table ($self->get_tables) {
        my $sth = $self->get_dbh->prepare(
            "select * from $table where 1 = 0");
        $sth->execute;
        my @fields = @{ $sth->{NAME} };
        $sth->finish;

        my $label = "{$table|";

        for my $field (@fields) {
            next if $IGNORE_COLUMNS{$field};
            $label .= $field.'\l';
            if (my $dep = $self->is_foreign_key($table, $field)) {
                $self->{g}->add_edge({ from => $table, to => $dep });
            }
        }

        $self->{g}->add_node({ name => $table,
                               shape => 'record',
                               label => "$label}",
                           });

    }
    return $self->{g};
}

foreach my $group (keys %GROUP_TABLES) {
    my $filter = $GROUP_TABLES{$group};
    my $g = __PACKAGE__->new($dbh);
    $g->{tables} = [ grep {$_ =~ $filter} $g->get_dbh->tables];
    my $txt = $g->graph_tables->as_text;
    $txt =~ s{`$SCHEMA`\.`(.+?)`}{$1}g;
    print "## $group\n\n";
    print "```plantuml\n\@startuml\n", $txt, "\n\@enduml\n```\n\n";
    # Growiならこっち
    # print "\@startuml\n", $txt, "\n\@enduml\n\n";
}

こんな感じでやると、下記のようなER図になります(自分で作ってたサービスのDBです)。

PlantUMLで表示

ちなみに、GraphViz::DBIは、13年前の更新が最後ですが、最新のPerlでも動くと思いますよ(5.8.9 でも、5.25.1でも動いたので)。

これを定期的に回して、Wikiのページも自動的に更新とかしてやれば、良いかもしれません。 ER図をDBの情報を読み取って吐き出すツールは普通にありますが、テーブル数が多いと、使い物になりませんので、こんな感じで特定部分をフィルタリングして出すのも割と有用だと思いますよ。

21日のPerl Advent Calendar 2019はMorichanさんによる「PerlだけでWebサイトを作る - Qiita」です。