Java
Bash
データベース
設計

ER図を書くのに疲れたら


はじめに

新規開発/保守開発 にかかわらず、システム開発時にデータ構造を把握することは非常に大事です。

データ構造の把握に有意義なものとして ER図(E-R Diagram) があげられます。


ER図作成で悩むこと


ドキュメントとプログラムは 「乖離」 する

非常に有益な ER図 ですが、ドキュメントの類に漏れず 実体(プログラム)との乖離 が発生します。

「乖離」 を避けるために、定期的に同期を撮ろうとしますが、全てのメンバが常に鮮明な意識で開発に挑めるわけではありません。ドキュメントは荒廃していきます。


ドキュメントとデータベースも 「乖離」 する

ドキュメントとプログラム だけでなく、 ドキュメントとデータベース も乖離します。

すでにサービスとして可動しているデータベースの定義(テーブル、インデックス、ビュー)ドキュメント が乖離しているということです。


サービスインしているものを「生みの親」とすることで対処

ドキュメントプログラム の乖離への対処として昔からあるのが、サービスインしているものからドキュメントを起こす( リバース )というものがあります。

プログラムドキュメント と内容を反映します。

同じように、 データベースドキュメント と内容を反映できれば嬉しいですね。


マウスでこねこねしたくないですよね。

僕はコマンドラインが好きです。

できることならマウスを使わずに仕事を終えたいと思っています。

僕の気持ちとは裏腹にER図のような視覚的なドキュメントはマウスの操作を強要してきます。


複数人で開発している場合は、マルチプラットフォーム対応が必要

複数人で開発している場合は、 マルチプラットフォーム に対応したツールが好ましいです。


Windowsでしか動かせないツールだったりすると、負荷が一部のメンバに集中してしまいますね。


ER図生成ツールに求めることは3つ

ER図生成ツールに求めたのは3点です。


  1. 既存のDBからリバースできる

  2. CUIで実行できる

  3. 複数の環境で動作する(Windows / Mac / Unix系OS )

暫く探し回って見つけたツールが SchemaSpy というツールでした。

使い方を以下に簡単にまとめます。


SchemaSpy を使ってみる


実行環境について

SchemaSpy はJavaでできているため、 JRE が必要です。


対応DBについて

DBとの接続は JDBC - Wikipedia を使用しており、JDBCドライバが提供されているDBであればどんなものでも接続可能です。

以下のようなDBに対応しています。

Type
Description

db2
IBM DB2 with 'app' Driver

db2net
IBM DB2 with 'net' Driver

udbt4
DB2 UDB Type 4 Driver

db2zos
DB2 for z/OS

derby
Derby (JavaDB) Embedded Server

derbynet
Derby (JavaDB) Network Server

firebird
Firebird

hsqldb
HSQLDB Server

informix
Informix

maxdb
MaxDB

mssql
Microsoft SQL Server

mssql05
Microsoft SQL Server 2005

mssql-jtds
Microsoft SQL Server with jTDS Driver

mssql05-jtds
Microsoft SQL Server 2005 with jTDS Driver

mysql
MySQL

ora
Oracle with OCI8 Driver

orathin
Oracle with Thin Driver

pgsql
PostgreSQL

sqlite
SQLite

sybase
Sybase Server with JDBC3 Driver

sybase2
Sybase Server with JDBC2 Driver

teradata
Teradata (requires -connprops)


セットアップ

検証のために仮想環境を用意します。 Vagrant を起動します。

$ echo '''\

VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
config.vm.box = "centos7.1"
config.ssh.insert_key = false
config.vm.network "private_network", ip: "192.168.33.41"
config.vm.provider "virtualbox" do |vb|
vb.customize ["modifyvm", :id, "--memory", "1024"]
end
end
'''
> Vagrantfile

$ vagrant up

起動が終わったら、 仮想環境にログインします。

$ vagrant ssh

必要なアプリケーションをインストールします。

# JDKとGraphvizをインストール

$ sudo yum install -y \
'graphviz*' \
java-1.8.0-openjdk-devel \
;

# /usr/local/src にJarファイルを2つ配置します。
$ sudo mkdir -p /usr/local/src

$ cd /usr/local/src/

# 1. SchemaSpy 本体
$ sudo wget http://jaist.dl.sourceforge.net/project/schemaspy/schemaspy/SchemaSpy%205.0.0/schemaSpy_5.0.0.jar

# 2. PostgreSQL に接続するためのJDBCドライバ
$ sudo wget https://jdbc.postgresql.org/download/postgresql-9.4.1212.jar


ER図を生成するための起動スクリプトを作成

コマンドライン実行時の引数が多いため、 schemaspy という名前で起動スクリプトを作成し、毎回固定の引数をラップしておきます。

今回はローカルの "ebis" というデータベースにアクセスし、ER図を生成するように設定しました。


  • 外部キーがある場合、外部キーを元にテーブル間のリレーションを判定します

  • 外部キーがない場合、 "-noimplied" というオプションを削除し実行すれば、カラム名からリレーションを自動判定して付与します


    • が、この機能はどうも 主キーカラムと同名のカラムを探し、リレーションとしてつなげる ロジックであり、意図しないリレーションが作成されることが有ります。(idというカラムは全テーブルにある、等)




schemaspy

#!/usr/bin/env bash

# 先ほどJARファイルを配置したディレクトリ
JDBC_DRIVER_DIR=/usr/local/src
JDBC_DRIVER_PATH=${JDBC_DRIVER_PATH:-$(echo ${JDBC_DRIVER_DIR}/*.jar| sed "s/ /:/g")}

# ここでは "PostgreSQL" に特化させました
DATABASE_TYPE=${DATABASE_TYPE:-pgsql}

# 接続対象DBホスト
DB_HOST=${DB_HOST:-localhost}
# 接続対象DB名
DB_NAME=${DB_NAME:-ebis}
# 接続対象DBユーザ
DB_USER=${DB_USER:-postgres}
# 接続対象DBパスワード
DB_PASS=${DB_PASS:-}

[ ! -d "./${DB_NAME}" ] && mkdir -p "./${DB_NAME}"

# -hq :ハイクオリティモード。出力結果がきれいになる!
# -noimplied :「外部キーがない既存テーブル間の関連を推測し、ER図に反映してくれる」機能をOFFにする
java \
-jar "/usr/local/src/schemaSpy_5.0.0.jar" \
-hq \
-noimplied \
-o "./${DB_NAME}" \
-charset utf-8 \
-dp "${JDBC_DRIVER_PATH}" \
-t "${DATABASE_TYPE}" \
-host "${DB_HOST}" \
-s "public" \
-db "${DB_NAME}" \
-u "${DB_USER}" \
-p "${DB_PASS}" \
;


# 実行権限を付与

$ chmod u+x schemaspy


実行

スクリプトを起動します。

$ ./schemaspy

"ebis" というディレクトリが作られており、DB定義情報とER図が生成されていることがわかると思います。

(残念ですが、アドエビスのDB情報はここではお見せできません。)


出力サンプル(eccube3)

EC-CUBE/ec-cube セットアップ直後のDBへ繋いでER図を生成してみました。


  • テーブルのボックスごとにクリックできるようになっています。


    • クリックするとタイプやNotNullなど詳細が確認できます。

    • 直接リレーションがつながっているテーブルのみにしぼってER図が表示されます。テーブル数が多いアプリケーションではノイズを覗いて視覚化できとても良いです。



eccube3-er.png


QuickStartスクリプト

シェルスクリプト化しました。

Redhad環境であれば、インストールされていないものを事前に自動的にインストールしたのち実行してくれます。

GitHub - genzouw/schemaspy-cli: schemaspy commandline interface


システムの概要をつかむのに非常に便利です。

SchemaSpy よりもよいツールをご存じの方、教えてください。

以上