MySQL テーブル構造の md を作成

マイグレーションで積み上げたテーブル構造を可視化する

Laravel でデータ構造を管理するのに困るのは、マイグレーションを積み上げすぎて、現在の構造が把握できないこと。

ということで、次のコマンドでマークダウンファイルが作成できるように artisan コマンドを書いてみた。

php artisan schema:md

• database/schema/mysql-schema.md を出力する。
• mysqldump が使える前提。
• 解析に必要な dump ファイルはテンポラリに作成して破棄しているので、mysql-schema.dump とは独立。

実行すると次のようなマークダウンファイルを作成する。開発中はソースコードと一緒に確認できる。納品時にはマークダウンを pdf や xlsx にするとか。
マイグレーションでは、カラムはもちろんテーブルにもコメント付けを徹底しておく。

# Table Definition of `example`

## companies    （会社）
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|id                             |bigint unsigned   |    |AUTO INCREMENT        |PRI|                          |
|name                           |varchar(255)      |    |''                    |   |会社名                    |
|created_at                     |datetime          | OK |NULL                  |   |                          |
|updated_at                     |datetime          | OK |NULL                  |   |                          |

## migrations
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|id                             |int unsigned      |    |AUTO INCREMENT        |PRI|                          |
|migration                      |varchar(255)      |    |                      |   |                          |
|batch                          |int               |    |                      |   |                          |

## password_resets    （パスワードリセット）
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|email                          |varchar(255)      |    |''                    |MUL|メールアドレス            |
|token                          |varchar(255)      |    |''                    |   |トークン                  |
|created_at                     |datetime          | OK |NULL                  |   |                          |

## project_user    （プロジェクト・ユーザー）
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|project_id                     |int               |    |0                     |PRI|プロジェクト              |
|user_id                        |int               |    |0                     |P,M|ユーザー                  |

## projects    （プロジェクト）
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|id                             |bigint unsigned   |    |AUTO INCREMENT        |PRI|                          |
|name                           |varchar(255)      |    |''                    |   |プロジェクト名            |
|created_at                     |datetime          | OK |NULL                  |   |                          |
|updated_at                     |datetime          | OK |NULL                  |   |                          |

## users    （ユーザー）
|Field                          |Type              |Null|Default               |Key|Comment                   |
|-------------------------------|------------------|----|----------------------|---|--------------------------|
|id                             |bigint unsigned   |    |AUTO INCREMENT        |PRI|                          |
|name                           |varchar(255)      |    |''                    |   |                          |
|email                          |varchar(255)      |    |''                    |UNI|メールアドレス            |
|company_id                     |int               |    |0                     |MUL|会社                      |
|email_verified_at              |datetime          | OK |NULL                  |   |メール確認日時            |
|password                       |varchar(255)      |    |''                    |   |パスワード                |
|remember_token                 |varchar(100)      | OK |NULL                  |   |リメンバートークン        |
|created_at                     |datetime          | OK |NULL                  |   |                          |
|updated_at                     |datetime          | OK |NULL                  |   |                          |

SchemaMdCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;

class SchemaMdCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'schema:md ' .
        ' {--db= : データベース名を指定}' .
        ' {--out=mysql-schema : 出力MDファイル名}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'データベース定義をマークダウン化';

    /**
     * 出力するマークダウンのパス
     *
     * @var string
     */
    protected $markdown;

    /**
     * マークダウンテーブルのカラム幅
     *
     * @var array
     */
    protected $header = [
        'Field'   => 31,
        'Type'     => 18,
        'Null'     => 4,
        'Default'  => 22,
        'Key'      => 3,
        'Comment'  => 26,
    ];


    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        // 出力するマークダウンのパス
        $this->markdown = sprintf('%s/%s.md', database_path('schema'), $this->option('out'));

        // テンポラリファイルにダンプ
        $file = tempnam('/tmp', 'dump');
        $this->dump($file);

        // ダンプからマークダウンを作成
        $this->make($file);

        // テンポラリファイルを削除
        unlink($file);

        return Command::SUCCESS;
    }

    /**
     * 現在のデータベース情報をファイルにダンプする
     *
     * @return void
     */
    private function dump($file)
    {
        // データベース名
        $database = $this->option('db') ?: config('database.connections.mysql.database');

        // mysqldump を実行
        $cmd = sprintf(
            'env MYSQL_PWD=%s mysqldump -u %s --no-data %s > %s',
            config('database.connections.mysql.password'),
            config('database.connections.mysql.username'),
            $database,
            $file
        );
        system($cmd);

        // ダンプの不要記述を削除
        $buff = file_get_contents($file);
        $buff = preg_replace('/AUTO_INCREMENT=[0-9]+ /', '', $buff);
        file_put_contents($file, $buff);
    }

    /**
     * マークダウンの作成
     *
     * @param string $file
     * @return void
     */
    private function make($file)
    {
        if (!file_exists($file)) {
            return Command::FAILURE;
        }
        // ダンプファイルの読み取り
        $rows = file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

        $struct = false;            // テーブル定義の解析中か？
        $tables = [];               // 収集したテーブル定義をまとめる配列
        $table = [];                // １テーブル
        foreach ($rows as $row) {
            // テーブル定義の解析を開始
            if (preg_match('/^CREATE TABLE `(.+?)`/', $row, $m)) {
                $struct = true;
                $table = [
                    'name' => $m[1],
                    'cols' => [],
                ];
                continue;
            }
            // テーブル定義の終了
            if (preg_match('/^\\) ENGINE=/', $row)) {
                if (preg_match("/COMMENT='(.+?)'/", $row, $m)) {
                    $table['comment'] = $m[1];
                }
                $tables[] = $table;
                $struct = false;
                continue;
            }
            // テーブル定義の解析
            if ($struct) {
                $this->makeStruct($row, $table);
            }
        }

        // 収集したテーブル定義をマークダウン化する
        $this->makeMarkdown($tables);

        return 0;
    }

    /**
     * テーブル定義の解析
     *
     * @param string $row   行
     * @param array  $table
     * @return void
     */
    private function makeStruct($row, &$table)
    {
        // varchar 定義
        if (preg_match('/^  `(.+?)` varchar\(([0-9]+)\) CHARACTER SET [^ ]+ COLLATE [^ ]+ (.+)/', $row, $m)) {
            $table['cols'][] = array_merge([
                'name' => $m[1],
                'type' => "varchar({$m[2]})",
            ], $this->makeAttr($m[3]));
            // それ以外
        } elseif (preg_match('/^  `(.+?)` ([^ ]+) (.+)/', $row, $m)) {
            $table['cols'][] = array_merge([
                'name' => $m[1],
                'type' => $m[2],
            ], $this->makeAttr($m[3]));
            // インデックスキー
        } elseif (preg_match('/PRIMARY KEY \(`(.+?)`\)/', $row, $m)) {
            //$table['primary'] = $m[1];
            if (preg_match('/,/', $m[1])) {
                // 複合
                $table['primary'][] = preg_split('/`,`/', $m[1]);
            } else {
                $table['primary'][] = $m[1];
            }
        } elseif (preg_match('/UNIQUE KEY .+ \(`(.+?)`\)/', $row, $m)) {
            if (preg_match('/,/', $m[1])) {
                // 複合
                $table['unique'][] = preg_split('/`,`/', $m[1]);
            } else {
                $table['unique'][] = $m[1];
            }
        } elseif (preg_match('/KEY .+ \(`(.+?)`\)/', $row, $m)) {
            if (preg_match('/,/', $m[1])) {
                // 複合
                $table['index'][] = preg_split('/`,`/', $m[1]);
            } else {
                $table['index'][] = $m[1];
            }
        }
    }

    /**
     * 属性部分の解析
     *
     * @param string $string
     * @return array
     */
    private function makeAttr($string)
    {
        $attr = [];
        if (!preg_match('/NOT NULL/', $string)) {
            $attr['nullable'] = ' OK';
        }
        if (preg_match('/unsigned/', $string)) {
            $attr['unsigned'] = ' unsigned';
        }
        if (preg_match('/DEFAULT ([^ ,]+)/', $string, $m)) {
            $attr['default'] = ($m[1] == "''") ? "''" : trim($m[1], "'");
        }
        if (preg_match('/ON UPDATE ([^ ,]+)/', $string, $m)) {
            $attr['default'] .= ' / ON';
        }
        if (preg_match('/AUTO_INCREMENT/', $string)) {
            $attr['default'] = 'AUTO INCREMENT';
        }
        if (preg_match("/COMMENT '(.+?)'/", $string, $m)) {
            $attr['comment'] = $m[1];
        }
        return $attr;
    }

    /**
     * テーブル配列からマークダウン生成
     *
     * @param array $tables
     * @return void
     */
    private function makeMarkdown($tables)
    {
        $buff = [];
        $buff[] = '# Table Definition of `' . config('database.connections.mysql.database') . '`';

        foreach ($tables as $table) {
            $buff[] = '';
            if (isset($table['comment'])) {
                $buff[] = '## ' . $table['name'] . "    （" . $table['comment'] . '）';
            } else {
                $buff[] = '## ' . $table['name'];
            }
            // テーブルヘッダ
            $buff[] = $this->makeRow(array_keys($this->header));
            // 水平ボーダー
            $buff[] = $this->makeBorder();

            // 各カラム定義
            foreach ($table['cols'] as $col) {
                $buff[] = $this->makeRow([
                    $col['name'],
                    $col['type'] . Arr::get($col, 'unsigned'),
                    Arr::get($col, 'nullable'),
                    Arr::get($col, 'default'),
                    $this->makeKey($col['name'], $table),
                    Arr::get($col, 'comment'),
                ]);
            }
        }

        file_put_contents($this->markdown, join(PHP_EOL, $buff) . PHP_EOL);
    }

    /**
     * テーブルの１行を描画
     *
     * @param string[] $cols   値の配列
     * @param string   $fill   補充文字
     * @param string   $border 縦仕切り
     * @return string
     */
    private function makeRow($cols, $fill = ' ', $border = '|')
    {
        // 値の配列とカラム幅の配列のインデックスを揃える
        $cols = array_values($cols);
        $lengths = array_values($this->header);

        foreach ($cols as $i => $col) {
            // 文字列がカラム幅に短ければ補充文字で補完する
            if (($short  = $lengths[$i] - self::strlen($col)) > 0) {
                $cols[$i] .= str_repeat($fill, $short);
            }
        }
        // 縦仕切りで結合する
        return $border . join($border, $cols) . $border;
    }

    /**
     * テーブル水平ボーダー
     *
     * @return string
     */
    private function makeBorder()
    {
        // カラム数だけ '-' の配列を作る
        $cols = array_fill(0, count($this->header), '-');
        // 補充文字を '-' にして行を作るとボーダーになる
        return $this->makeRow($cols, '-');
    }

    /**
     * インデックス
     *
     * @param string $name
     * @param array $table
     * @return string
     */
    private function makeKey($name, $table)
    {
        if (in_array($name, Arr::get($table, 'primary', []))) {
            return 'PRI';     // プライマリー
        }
        if (in_array($name, Arr::flatten(Arr::get($table, 'primary', []))) &&
            in_array($name, Arr::get($table, 'index', []))) {
            return 'P,M';   // プライマリとインデックス
        }
        if (in_array($name, Arr::flatten(Arr::get($table, 'unique', []))) &&
            in_array($name, Arr::get($table, 'index', []))) {
            return 'U,M';   // 複合ユニークとインデックス
        }
        if (in_array($name, Arr::flatten(Arr::get($table, 'primary', [])))) {
            return 'PRI';     // 複合プライマリー
        }
        if (in_array($name, Arr::get($table, 'index', []))) {
            return 'MUL';     // インデックス
        }
        if (in_array($name, Arr::flatten(Arr::get($table, 'index', [])))) {
            return 'CMU';    // 複合インデックス
        }
        if (in_array($name, Arr::get($table, 'unique', []))) {
            return 'UNI';     // ユニーク
        }
        if (in_array($name, Arr::flatten(Arr::get($table, 'unique', [])))) {
            return 'CUN';    // 複合ユニーク
        }
    }

    /**
     * 半角換算した文字列の長さ
     *
     * @param string $string
     * @return int
     */
    private static function strlen($string)
    {
        return mb_strwidth($string, 'UTF-8');
    }
}