MySQLのテーブル定義をMarkdown形式に出力するシェル

※ペライチアドベントカレンダー (12/5)の記事になります！

背景

今所属している株式会社ペライチ では
esaというサービスでドキュメント管理をしています。
Qiitaと同じくMarkdown形式で記載します。
DB定義のページの更新を楽にしたく、ちょっとシェルを組んでみました。

サンプルDB

CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL COMMENT '部署No',
  `dept_name` varchar(40) NOT NULL COMMENT '部署名',
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部署テーブル';

CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL COMMENT '社員No',
  `dept_no` char(4) NOT NULL COMMENT '部署No',
  `from_date` date NOT NULL COMMENT '開始日',
  `to_date` date NOT NULL COMMENT '終了日',
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配属テーブル';

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL COMMENT '社員No',
  `birth_date` date NOT NULL COMMENT '生年月日',
  `first_name` varchar(14) NOT NULL COMMENT '姓',
  `last_name` varchar(16) NOT NULL COMMENT '名',
  `gender` enum('M','F') NOT NULL COMMENT '性別',
  `hire_date` date NOT NULL COMMENT '雇用日',
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='社員テーブル';

CREATE TABLE `schema_migrations` (
  `version` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL公式サンプル
https://dev.mysql.com/doc/index-other.html
employee dataのテーブルデータを一部拝借しました。

シェルスクリプト

ファイル名はご自由に。
仮にdb_ddl_markdown.shとします。

#!/bin/sh

# MySQL接続情報
DB_USER='root'
DB_PASS='root'
DB_HOST='127.0.0.1'
DB_PORT='3306'
DB_SCHEMA='employees'
MYSQL_CONNECT="mysql --default-character-set=utf8 -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT} ${DB_SCHEMA}"
# 出力不要のテーブル
IGNORE_TABLES=(schema_migrations others)

# Markdown形式で出力
echo "## DB構成"
echo
{
for TABLE in $(sh -c "${MYSQL_CONNECT} -N -e 'show tables'");do
    for IGNORE in "${IGNORE_TABLES[@]}"; do
        if [[ "$IGNORE" = "$TABLE" ]]; then
            continue 2
        fi
    done

    echo "### ${TABLE}"
    echo
    COLUMNS=$(sh -c "${MYSQL_CONNECT} -t -e 'show full columns from ${TABLE}'")
    echo "${COLUMNS}" | sed -e '1d;$d;s/+/|/g' | awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'
    echo
done
} 2> /dev/null

出力結果

$ sh db_ddl_markdown.sh

## DB構成

### departments

| Field     | Type        | Null | Key | Default | Extra | Comment   |
|-----------|-------------|------|-----|---------|-------|-----------|
| dept_no   | char(4)     | NO   | PRI | NULL    |       | 部署No    |
| dept_name | varchar(40) | NO   | UNI | NULL    |       | 部署名    |

### dept_emp

| Field     | Type    | Null | Key | Default | Extra | Comment   |
|-----------|---------|------|-----|---------|-------|-----------|
| emp_no    | int(11) | NO   | PRI | NULL    |       | 社員No    |
| dept_no   | char(4) | NO   | PRI | NULL    |       | 部署No    |
| from_date | date    | NO   |     | NULL    |       | 開始日    |
| to_date   | date    | NO   |     | NULL    |       | 終了日    |

### employees

| Field      | Type          | Null | Key | Default | Extra | Comment      |
|------------|---------------|------|-----|---------|-------|--------------|
| emp_no     | int(11)       | NO   | PRI | NULL    |       | 社員No       |
| birth_date | date          | NO   |     | NULL    |       | 生年月日     |
| first_name | varchar(14)   | NO   |     | NULL    |       | 姓           |
| last_name  | varchar(16)   | NO   |     | NULL    |       | 名           |
| gender     | enum('M','F') | NO   |     | NULL    |       | 性別         |
| hire_date  | date          | NO   |     | NULL    |       | 雇用日       |

解説

MYSQL_CONNECT="mysql --default-character-set=utf8 -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT} ${DB_SCHEMA}"

まずはベースのMySQL接続コマンドを用意します。
サンプルなので愚直にユーザ名やパスワードをオプションに入れてますが
--defaults-extra-fileオプションを使ってソースコードには書かない方がいいでしょうね。

---

for TABLE in $(sh -c "${MYSQL_CONNECT} -N -e 'show tables'");do

-eオプションに実行させるSQLを書き、先のMySQL接続コマンドとつなげてコマンドの文字列を作成
sh -cで文字列の内容を実行します。

-Nオプションでカラム名無しでデータだけ出力されます。この部分だけの出力内容はこんな感じ。

departments
dept_emp
employees
schema_migrations

ここでは罫線は入ってきませんので、実行結果を
for 変数名 in $(...) とループさせてテーブルごとに処理していきます。

---

    for IGNORE in "${IGNORE_TABLES[@]}"; do
        if [[ "$IGNORE" = "$TABLE" ]]; then
            continue 2
        fi
    done

フレームワークで使うマイグレーションやログ用のテーブルなど、出力不要なテーブルだった場合、処理をスキップします
設定部分は IGNORE_TABLES=(schema_migrations others) です。

---

    COLUMNS=$(sh -c "${MYSQL_CONNECT} -t -e 'show full columns from ${TABLE}'")

テーブルごとにカラム情報を取得します。
descでもいいのでは？と思うかもしれませんが、カラムのCOMMENTの内容を撮りたいので show full columns fromで行います。
-tオプションで今度は罫線も含めて結果を得ます。

---

    echo "${COLUMNS}" | sed -e '1d;$d;s/+/|/g' | awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'

${COLUMNS}にカラム情報が入っているので Markdown形式に加工していきます。
echo "${COLUMNS}"だとテキストはまだ下記の感じです。
これをパイプ|で次のコマンドへと渡します。

+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+
| Field     | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment   |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+
| dept_no   | char(4)     | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references | 部署No    |
| dept_name | varchar(40) | utf8mb4_general_ci | NO   | UNI | NULL    |       | select,insert,update,references | 部署名    |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+

sed -e '1d;$d;s/+/|/g'
sedはテキスト変換によく使われるコマンド。
呪文めいてますが、、 1d;は1行目削除、$d;は最後の行を削除、s/+/|/gは+を|に変換するパラメータです。この段階を通るとテキストの内容はこうなります。

| Field     | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment   |
|-----------|-------------|--------------------|------|-----|---------|-------|---------------------------------|-----------|
| dept_no   | char(4)     | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references | 部署No    |
| dept_name | varchar(40) | utf8mb4_general_ci | NO   | UNI | NULL    |       | select,insert,update,references | 部署名    |

Markdown形式のテーブルの書き方になりましたね。
ただ、情報量が多いので絞りましょう。
awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'
awkは先のsedと同じくテキスト処理でよく使われますが、
プログラミング言語としても使えて、演算やループなど凝ったことができます。
今回のは -Fオプションで区切り文字を指定し、printで列番号を指定して出力する、よく使われる書き方です。
|区切りだと一番左がパイプの外が1番目（空値)、Fieldが2番目になります。

| Field     | Type        | Null | Key | Default | Extra | Comment   |
|-----------|-------------|------|-----|---------|-------|-----------|
| dept_no   | char(4)     | NO   | PRI | NULL    |       | 部署No    |
| dept_name | varchar(40) | NO   | UNI | NULL    |       | 部署名    |

無事、Markdown形式にできました。
先の出力結果を貼り付けると↓になります。

DB構成

departments

Field	Type	Null	Key	Default	Extra	Comment
dept_no	char(4)	NO	PRI	NULL		部署No
dept_name	varchar(40)	NO	UNI	NULL		部署名

dept_emp

Field	Type	Null	Key	Default	Extra	Comment
emp_no	int(11)	NO	PRI	NULL		社員No
dept_no	char(4)	NO	PRI	NULL		部署No
from_date	date	NO		NULL		開始日
to_date	date	NO		NULL		終了日

employees

Field	Type	Null	Key	Default	Extra	Comment
emp_no	int(11)	NO	PRI	NULL		社員No
birth_date	date	NO		NULL		生年月日
first_name	varchar(14)	NO		NULL		姓
last_name	varchar(16)	NO		NULL		名
gender	enum('M','F')	NO		NULL		性別
hire_date	date	NO		NULL		雇用日

---

} 2> /dev/null

シェルのテクニックの一つ。
{}で複数行を囲んで一括りの処理とします。
mysqlコマンドを実行したときに、サンプルの書き方だとパスワードに関して警告が出るので、
エラー出力を無視するリダイレクトを入れてます。
デバッグ時には外してください。

最後に

シェルはもう少し改良の余地があるなーと思いつつ。。

次は 12/7 ペライチCTO @katsukii さんの「ペライチ開発チームのご紹介」になります！