0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

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

Last updated at Posted at 2022-06-18

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

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 = [];                // 1テーブル
        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);
    }

    /**
     * テーブルの1行を描画
     *
     * @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');
    }
}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?