テーブル構造をYaml形式に出力してみた

この記事は GRIPHONE Advent Calendar 2020 14日目の記事です。

こんにちは。サーバーサイドエンジニアの斎藤です。

今回はデータ分析で実際のDBにあるテーブルのスキーマを利用したい、という要望があり、ゲームに使用している全テーブルのスキーマをYaml形式で出力してみました。

実装

当プロジェクトではphp言語を使用し、フレームワークとしてLumenを利用しています。

メインの実装はこちら

   /**
     * テーブルスキーマを生成
     */
    public function generate()
    {
        $connections = config('database.connections');
        foreach ($connections as $name => $connection) {
            $key = sprintf('Tables_in_%s', $connection['database']);
            foreach (DB::connection($name)->select('show tables') as $row) {
                $title = $row->{$key};
                $tables = DB::connection($name)->select(sprintf('show full columns from %s', $title));
                $yaml = $this->arrayToYaml($tables, $title);
                $this->writeFile($title, $yaml);
            }
        }
    }

要するにDB毎に show tables のクエリでテーブルリストを取得し、テーブル毎に詳細なテーブル情報を取得するクエリ show full columns from [テーブル名] を投げて詳細なテーブル情報を取得する簡単な作りになっています。

実際に下記のようなテーブル構造の場合、

こんな感じのYamlファイルが出力されます。

title: test
properties:
    -
        Field: id
        Type: 'int(10) unsigned'
        Collation: null
        'Null': 'NO'
        Key: PRI
        Default: null
        Extra: auto_increment
        Privileges: 'select,insert,update,references'
        Comment: ''

Nullの表示がおかしいし、CollationやPrivilegesも特に必要ないので、ちょこちょこっと調整します。

    /**
     * @param array  $array
     * @param string $title
     * @return string
     */
    protected function arrayToYaml(array $array, string $title): string
    {
        $outputArray = [];
        $outputArray['title'] = $title;
        foreach ($array as $key => $column) {
            foreach ($column as $k => $v) {
                if (in_array($k, ['Privileges', 'Collation'])) continue;
                $outputArray['properties'][$key][$this->getColumn($k)] = $v;
            }
        }
        return Yaml::dump($outputArray, 10);
    }

    /**
     * @param $k
     * @return string
     */
    protected function getColumn($k): string
    {
        if ($k == 'Null') $k = 'is_null';
        return strtolower($k);
    }

修正後の出力はこちら

title: test
properties:
    -
        field: id
        type: 'int(10) unsigned'
        is_null: 'NO'
        key: PRI
        default: null
        extra: auto_increment
        comment: ''
    -
        field: user_id
        type: 'bigint(20) unsigned'
        is_null: 'NO'
        key: UNI
        default: null
        extra: ''
        comment: userID
    -
        field: type
        type: 'tinyint(2) unsigned'
        is_null: 'NO'
        key: ''
        default: '0'
        extra: ''
        comment: type
    -
        field: name
        type: varchar(128)
        is_null: 'YES'
        key: ''
        default: null
        extra: ''
        comment: name

まだ改修の余地はあるけれどだいぶ読みやすくなったかと思います。

さいごに

今回はYaml出力のみですが、使い方によってはいろいろ応用が効くと思います。

例えば、プロジェクトに途中から参加することになり、テーブル定義が存在しなかったりメンテナンスされていないケースは多くあると思います。

そういった時に全テーブルスキーマを取得して資料化すれば、システム理解も進むのではないかと思います。

実装自体も簡単ですので、参考にしてもらえると幸いです。