ネクタイ締めて SE らしきものをやってた頃はコードよりドキュメントのほうを山のように書いてたもんですが、限りなくプータローに近いフリーの身になってからはそんな苦行も今は昔… と言いつつ、タマに「DB の仕様書下さい」「ダンプした DDL でいいっすよね」「…え?」「え?」てなこともあります。
てな訳で、既存の MySQL データベースをソースに、よくあるテーブル定義書を生成するのが今日のお題。車輪の再発明を避けるべくグーグル先生に相談してみたら SQL::Translator という、様々な DBMS の SQL をこれまた様々なフォーマットに変換する、正にうってつけの CPAN モジュールがあったので、これを使ってみます。全体的には
- mysqldump でテーブル定義を SQL 出力
- SQL::Translator を使って SQL から XML に変換
- XSLT で XML をテーブル仕様書 HTML に変換
という手順をとります。
まずは DB からテーブル構造をダンプ。
$ mysqldump -u hoge -pXXXXXX --no-data sample_db > sample.ddl.sql
ダンプした SQL はこんなん(↓)だとします。テーブルや列には論理名となるコメントが付けてあることを前提にしてます。
CREATE TABLE IF NOT EXISTS `persons` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'レコードID',
`name` varchar(50) NOT NULL COMMENT '氏名',
`address` varchar(100) NOT NULL COMMENT '住所',
`phone` varchar(20) NOT NULL COMMENT '電話番号',
`email` varchar(50) NOT NULL COMMENT 'Eメール',
`created` datetime NOT NULL COMMENT '登録日時',
`modified` datetime NOT NULL COMMENT '最終更新日時',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='住所録' AUTO_INCREMENT=1 ;
で、SQL::Translator を使って SQL を XML に変換する簡単なスクリプト sql2xml.pl (↓)を作って、これを実行。
#!/usr/bin/perl
use strict;
use SQL::Translator;
my @data = <STDIN>;
my $t = SQL::Translator->new(
from => 'MySQL',
to => 'XML-SQLFairy',
data => join("\n", @data),
show_warnings => 1,
);
print $t->translate;
$ perl sql2xml.pl < sample.ddl.sql > sample.ddl.xml
はい、SQL がこんな XML に変換されますた。
xml version="1.0" encoding="UTF-8"
<schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
<extra />
<tables>
<table name="persons" order="1">
<extra />
<fields>
<field name="id" data_type="int" size="10" is_nullable="0" is_auto_increment="1" is_primary_key="1" is_foreign_key="0" order="1">
<extra unsigned="1" />
<comments>レコードID</comments>
</field>
<field name="name" data_type="varchar" size="50" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="2">
<extra />
<comments>氏名</comments>
</field>
<field name="address" data_type="varchar" size="100" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="3">
<extra />
<comments>住所</comments>
</field>
<field name="phone" data_type="varchar" size="20" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="4">
<extra />
<comments>電話番号</comments>
</field>
<field name="email" data_type="varchar" size="50" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="5">
<extra />
<comments>Eメール</comments>
</field>
<field name="created" data_type="datetime" size="0" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="6">
<extra />
<comments>登録日時</comments>
</field>
<field name="modified" data_type="datetime" size="0" is_nullable="0" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="7">
<extra />
<comments>最終更新日時</comments>
</field>
</fields>
<indices></indices>
<constraints>
<constraint name="" type="PRIMARY KEY" fields="id" reference_table="" reference_fields="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1">
<extra />
</constraint>
</constraints>
<comments>
<comment>住所録</comment>
</comments>
</table>
</tables>
<views></views>
<triggers></triggers>
<procedures></procedures>
</schema>
あとはこの XML をお好みにドキュメント変換するだけ。ここでは HTML に XSLT 変換します。XSL (2html.xsl)はこんな感じ(↓)。
xml version="1.0" encoding="utf8"
<xslstylesheet version="1.0" xmlnsxsl="http://www.w3.org/1999/XSL/Transform" xmlnss="http://sqlfairy.sourceforge.net/sqlfairy.xml">
<xsloutput method="html" encoding="utf8" doctype-public="-//W3C//DTD XHTML 1.0 Transitional//EN"/>
<xsltemplate match="/s:schema">
<html lang="ja">
<head>
<meta charset="utf-8"/>
<title>データベース テーブル定義</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css"/>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap-theme.min.css"/>
<style>
table.htable {
margin: 3em auto 1em auto !important;
}
table.htable th {
border-left: 10px solid #e5e5e5 !important;
}
footer {
border-top: 1px solid #e5e5e5;
padding: 0.5em;
}
</style>
</head>
<body>
<div class="container">
<h1 class="page-header">データベース テーブル定義</h1>
<xslapply-templates select="s:tables"/>
</div>
<footer class="text-center">
mariyudu.net
</footer>
</body>
</html>
</xsltemplate>
<xsltemplate match="s:table">
<table class="table table-bordered htable">
<tbody>
<tr class="active">
<th>テーブル名</th>
<td><xslvalue-of select="s:comments"/></td>
</tr>
<tr class="active">
<th>スキーマ</th>
<td><xslvalue-of select="@name"/></td>
</tr>
</tbody>
</table>
<table class="table table-condensed">
<thead>
<tr>
<th class="text-right">#</th>
<th>論理名</th>
<th>物理名</th>
<th>型</th>
<th class="text-right">長さ</th>
<th>主キー</th>
</tr>
</thead>
<tbody>
<xslapply-templates select="s:fields"/>
</tbody>
</table>
</xsltemplate>
<xsltemplate match="s:field">
<tr>
<td class="text-right"><xslvalue-of select="@order"/></td>
<td><xslvalue-of select="s:comments"/></td>
<td><xslvalue-of select="@name"/></td>
<td><xslvalue-of select="@data_type"/></td>
<td class="text-right"><xslif test="@size!=0"><xslvalue-of select="@size"/></xslif></td>
<td><xslif test="@is_primary_key!=0"><span class="glyphicon glyphicon-ok"></span></xslif></td>
</tr>
</xsltemplate>
</xslstylesheet>
$ xsltproc -o sample.ddl.html 2html.xsl sample.ddl.xml
お疲れさま。出来たのがこんな HTML のテーブル定義書です。
ER 図とかは MySQL Workbench が便利かも。ネクタイの頃とはツール類が段違いすぐる… まぁ、ドキュメントなんて古くて新しい悩みですが、巨人の肩に乗っかってカジュアルに片付けたいもんですな。
2015-2-1 付記 : MySQL ではもっとカンタンな方法があることが判ったので続編記事を書きました → 「続・MySQL データベースからテーブル定義書を生成する」
