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

※[ペライチアドベントカレンダー](https://qiita.com/advent-calendar/2021/peraichi) (12/5)の記事になります！

## 背景

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

## サンプルDB

```sql
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`とします。

```shell
#!/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
```

## 出力結果

```shell
$ sh db_ddl_markdown.sh
```

```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    |       | 雇用日       |
```

## 解説

```shell
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`オプションを使ってソースコードには書かない方がいいでしょうね。

----

```shell
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 $(...)` とループさせてテーブルごとに処理していきます。

----

```shell
    for IGNORE in "${IGNORE_TABLES[@]}"; do
        if [[ "$IGNORE" = "$TABLE" ]]; then
            continue 2
        fi
    done
```
フレームワークで使うマイグレーションやログ用のテーブルなど、出力不要なテーブルだった場合、処理をスキップします
設定部分は `IGNORE_TABLES=(schema_migrations others)` です。

----

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

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

----

```shell
    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    |       | 雇用日       |


---

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


## 最後に

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

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