Zenn
👻

Laravelで実データベースからテーブル定義書を作成するためのステップ

2021/06/30に公開
Laravel
#
テーブル定義
tech

実データベースから簡易的なテーブル定義書を出力することに成功しました。

ただ、 Laravel-Excelの使い方〜とかまで行くとちょっとご面倒なので、テーブル定義書を作るために必要な情報を取得するところまで解説します。

DB::select("SHOW TABLES");

このコマンドで、現在使用しているデータベースのテーブル一覧を見ることができます。

ただ面倒なことにちょっと変な感じで出力されます。


>>> DB::select("SHOW TABLES");                                                                                                                                                  
=> [
     {#3516
       +"Tables_in_questionnaire_system": "admins",
     },                                                                   
     {#3515
       +"Tables_in_questionnaire_system": "failed_jobs",
     },                                                                   
     {#3514                                                               
       +"Tables_in_questionnaire_system": "users",            
     }, 
]

オブジェクトの配列で返ってくるので、単純にテーブル名の配列がほしいのです。
なので、無理やり変換してあげます

$tables = DB::select("SHOW TABLES");

$tableNameArray = array_reduce(
    array_map(function ($table) {
	return array_values((array) $table);
    }, $tables),
'array_merge', []);

これでtableNameArrayに単純なテーブル名の配列が入ります

[
    "admins",
    "failed_jobs",
    "users",
]

DB::select("show full columns from {テーブル名}");

テーブル内にあるカラム一覧とそれぞれの定義を取得します。

DB::select("show full columns from users");

    {#3525
      +"Field": "id",
      +"Type": "bigint(20) unsigned",
      +"Collation": null,
      +"Null": "NO",
      +"Key": "PRI",
      +"Default": null,
      +"Extra": "auto_increment",
      +"Privileges": "select,insert,update,references",
      +"Comment": "",
    },
    {#3525
      +"Field": "email",
      +"Type": "varchar(191)",
      +"Collation": utf8mb4_unicode_ci,
      +"Null": "YES",
      +"Key": "",
      +"Default": null,
      +"Extra": "",
      +"Privileges": "select,insert,update,references",
      +"Comment": "",
    },
    {#3525
      +"Field": "created_at",
      +"Type": "timestamp",
      +"Collation": null,
      +"Null": "YES",
      +"Key": "",
      +"Default": null,
      +"Extra": "",
      +"Privileges": "select,insert,update,references",
      +"Comment": "",
    },
    {#3525
      +"Field": "updated_at",
      +"Type": "timestamp",
      +"Collation": null,
      +"Null": "YES",
      +"Key": "",
      +"Default": null,
      +"Extra": "",
      +"Privileges": "select,insert,update,references",
      +"Comment": "",
    },

各カラムと定義が出力されました。

foreachかなんかでループを回して、 $column->Field とか $column->Type とかするとそれぞれの値が取得できます。

外部キーの場合は Keyのところに MUL が入っています。
MULって何？って思った人への参考記事: 【MySQL】Keyのところに出る「MUL」の付け方

ついでにリレーション先のテーブル名、カラム名も出力したいよ！

企業テーブル - ユーザーテーブル で紐付いている場合、 usersテーブル内の corporate_id という列を用いて、 リレーション元のテーブル名、カラム名も取得したい場合はこうします。

$tableName = "users";
$columnName = "corporate_id";
$foreighData = DB::table('information_schema.KEY_COLUMN_USAGE')
                ->where("TABLE_SCHEMA", config('database.connections.mysql.database'))
                ->where("CONSTRAINT_NAME", "LIKE", "%foreign%")
                ->where("TABLE_NAME", $tableName)
                ->where("COLUMN_NAME", $columnName) // テーブル内の外部キー指定しているカラム名（例：corporate_id）
                ->first();
		
=> {#3568
     +"CONSTRAINT_CATALOG": "def",
     +"CONSTRAINT_SCHEMA": "{データベース名}",
     +"CONSTRAINT_NAME": "users_corporate_id_foreign",
     +"TABLE_CATALOG": "def",
     +"TABLE_SCHEMA": "{データベース名}",
     +"TABLE_NAME": "users",
     +"COLUMN_NAME": "corporate_id",
     +"ORDINAL_POSITION": 1,
     +"POSITION_IN_UNIQUE_CONSTRAINT": 1,
     +"REFERENCED_TABLE_SCHEMA": "{データベース名}",
     +"REFERENCED_TABLE_NAME": "corporates", // ← これがリレーション元のテーブル名
     +"REFERENCED_COLUMN_NAME": "id", // ← これがリレーションを貼っているkeyになる
   }

リレーション元のテーブル名やキーをゲットしました。

$foreighData->REFERENCED_TABLE_NAME; // corporates
$foreighData->REFERENCED_COLUMN_NAME; // id

必要なデータは揃ったのであとはExcel形式にして保存するだけ

Laravel-Excelの解説までいくとちょっと大変なので、ここまでとします。

「データベースにある全てのテーブル名」「テーブル内の全カラムとその定義」「リレーション先のテーブル名・キー名」を取得できました。

テーブル名の配列をループ
テーブル内のカラムと定義取得する
カラムをループする
カラム名・定義を取得する
外部キー(KeyがMULになっている)であればリレーション先のテーブル名・キーを取得する

とかで、テーブル定義書を出力できます。

カラムの日本語名もほしいんだけど...

そうなんです、唯一テーブル内にない情報として、カラムの日本語名です。

id, name, gender, age, corporate_id...という英語名のカラム名だけでなく、ユーザーID, 名前、性別、企業ID...のような日本語名もほしいですよね！

ただ、これはもうそもそもテーブル内に日本語名を入れる部分がそもそもないので、方法としては以下の2パターンになるかなと思います。

パターン① 出力するテーブル定義書のカラムの日本語名の部分を空欄にしておき、あとで頑張って手打ちにする
パターン② マイグレーションでカラムを作るときにカラムに日本語のカラム名をコメントに残しておく。すると、 DB::select("show full columns from {テーブル名}") の部分のCommentで取得できるようになります。

一見パターン②の方が楽そうですが、本来commentは備考とかを入れるところなので、本来の使い方としてはちょっと間違っているのと、日本語のカラム名を入れてしまったら本当に備考をいれたいときに入れられないというのが発生してしまいます。好みの問題なのでお好きにどうぞ！

終わりに

というわけで、簡易的なテーブル定義書を出力してみました。
最後まで解説すればいいのですが、ちょっとご面倒な部分もあり途中までにしています。

要望があればちゃんと記事化したいと思いますので、要望がございましたらコメントください。

ではまた！

Discussion