いい加減テーブル定義書を手動で更新するのなんかやめたらいい

自動化。

~~どうせ誰も見ないって。
`show create table` 見たほうが早いし。~~

`mysqldump --no-data -uroot --database employees`
をリポジトリに入れてバージョン管理しとけば良いと思うけど、
何らかの文書化が必要な場合は毎回差分を修正するより全部出力した方がラク。

カラム名日本語がよければ適当に `as` で別名つけるとよい。

# mysqlの場合

```bash
#!/bin/bash

MYSQL="mysql -B -uroot"
DBNAME="employees"

TBLSQL=`cat << EOD
SELECT
    TABLE_NAME,
    TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = '$DBNAME'
    AND table_name LIKE '%s'
EOD`

COLSQL=`cat << EOD
SELECT
    COLUMN_NAME,
    COLUMN_DEFAULT,
    IS_NULLABLE,
    COLUMN_TYPE,
    COLLATION_NAME,
    COLUMN_KEY,
    EXTRA,
    COLUMN_COMMENT
  FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = '$DBNAME'
    AND table_name LIKE '%s'
EOD`

function textile() {
    while IFS= read line; do
        echo "$line" | tr "\t" "|" | xargs printf "|%s|\n"
    done
}

function csv() {
    while IFS= read line; do
        echo "$line" | tr "\t" ","
    done
}

function tsv() {
    while IFS= read line; do
        echo "$line"
    done
}

function html() {
    IFS=$'\t' read -a HEADER

    echo "<table border='1'>"
    echo "<tr>"
    for x in ${HEADER[*]}; do
        echo "<th>" $x "</th>"
    done
    echo "</tr>"

    while IFS= read line; do
        IFS="|" read -a ROW <<< "$(echo "$line" | tr "\t" "|")"
        echo "<tr>"
        for i in $(seq 0 $((${#HEADER[*]}-1)) );do
            echo "<td>" ${ROW[$i]} "</td>"
        done
        echo "</tr>"
    done
    echo "</table>"
}


echo "SHOW TABLES FROM $DBNAME" | $MYSQL -N | \
while read table; do
    IFS=$'\t' read TABLE_NAME TABLE_COMMENT <<< "$(printf "$TBLSQL" $table | $MYSQL -N)"
    echo "$TABLE_NAME"
    echo "$TABLE_COMMENT"

    printf "$COLSQL" $TABLE_NAME | $MYSQL | html
    echo
done
```

## 追記＠2015/08/31

bashだけとか不親切もいいとこなので

### php版

だがしかし`eval`使った邪悪な方法。

```php
<?php

$dsn = "mysql:host=127.0.0.1;port=3306;charset=utf8";
$user = "root";
$pass = "";
$DBNAME = "employees";

$SQL_TABLE_INFO = <<<EOD
SELECT
    TABLE_NAME,
    TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = '$DBNAME'
EOD;

$SQL_COLUMN_INFO = <<<EOD
SELECT
    COLUMN_NAME,
    COLUMN_DEFAULT,
    IS_NULLABLE,
    COLUMN_TYPE,
    COLLATION_NAME,
    COLUMN_KEY,
    EXTRA,
    COLUMN_COMMENT
  FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = '$DBNAME'
    AND table_name LIKE :tbl
EOD;

function each_tables($pdo, $dbname, $callback) {
	global $SQL_TABLE_INFO;
	global $SQL_COLUMN_INFO;

	$stmt1 = $pdo->query($SQL_TABLE_INFO);

	while(false != ($tblinfo = $stmt1->fetch(PDO::FETCH_ASSOC))) {
		$tblname = $tblinfo["TABLE_NAME"];

		$stmt2 = $pdo->prepare($SQL_COLUMN_INFO);
		$ret2 = $stmt2->execute(array(":tbl" => $tblname));
		if($ret2 === false) {
			throw new Exception($tblname);
		}
		$colinfo = $stmt2->fetchAll(PDO::FETCH_ASSOC);

		$callback($tblinfo, $colinfo);
	}
}


function genHtml($pdo, $dbname) {
	$str = <<<'EOD'
?>
<meta charset="utf-8">

<?php
each_tables($pdo, $dbname, function($tblinfo, $colinfo) {
?>
<fieldset>
	<legend><?= $tblinfo["TABLE_NAME"] ?></legend>
	<p><?= $tblinfo["TABLE_COMMENT"] ?></p>
	<table border="1">
		<tr>
<?php foreach(array_keys($colinfo[0]) as $key): ?>
			<th><?= $key ?></th>
<?php endforeach; ?>
		</tr>

<?php foreach($colinfo as $col): ?>
		<tr>
<?php foreach($col as $key => $val): ?>
			<td><?= $val ?></td>
<?php endforeach; ?>
		</tr>
<?php endforeach; ?>
	</table>
</fieldset>

<?php
});
?>
EOD;

	eval($str);
}

try {
	$pdo = new PDO($dsn, $user, $pass);
	genHtml($pdo, $DBNAME);
} catch(Exception $e) {
	var_dump($e);
	throw $e;
}
```

### `ruby`だとスッキリ

```rb
#!/usr/bin/env ruby

require "mysql2"
require 'mysql2-cs-bind'
require "erb"

host = "127.0.0.1"
user = "root"
pass = ""
dbname = "employees"

SQL_TABLE_INFO = <<EOD
SELECT
    TABLE_NAME,
    TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = '#{dbname}'
EOD

SQL_COLUMN_INFO = <<EOD
SELECT
    COLUMN_NAME,
    COLUMN_DEFAULT,
    IS_NULLABLE,
    COLUMN_TYPE,
    COLLATION_NAME,
    COLUMN_KEY,
    EXTRA,
    COLUMN_COMMENT
  FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = '#{dbname}'
    AND table_name LIKE ?
EOD

cli = Mysql2::Client.new(:host => host, :username => user, :password => pass, :database => dbname)
tables = cli.query(SQL_TABLE_INFO).map do |row|
  [row, cli.xquery(SQL_COLUMN_INFO, row["TABLE_NAME"])]
end

puts ERB.new(DATA.read).result(binding)

__END__
<meta charset="utf-8">

<% tables.each do |tbl, col| %>
<fieldset>
	<legend><%= tbl["TABLE_NAME"] %></legend>
	<p><%= tbl["TABLE_COMMENT"] %></p>
	<table border="1">
		<tr>
<% col.fields.each do |c| %>
			<th><%= c %></th>
<% end %>
		</tr>

<% col.each do |c| %>
		<tr>
<% c.each do |key, value| %>
			<td><%= value %></td>
<% end %>
		</tr>
<% end %>
	</table>
</fieldset>
<% end %>
```