0
1

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 3 years have passed since last update.

markdownで書いたDB設計をLaravelのmigrationファイルに変換するPythonスクリプト

Last updated at Posted at 2019-11-19

Laravel用だけどPythonです。
Laravel5.8, Python3.7.4 で動作確認済み。Python2系だと文字化けするかも。

用意

  • in: database.md
    • DB情報をmarkdown形式で書く
  • out: migrations/
    • フォルダを作っておく

作成例)

database.md
## users
- ユーザ情報

| 名前            | データ型     | 照合順序           | 属性     | NULL | デフォルト値 | コメント | その他 |
|-----------------|--------------|--------------------|----------|------|--------------|----------|--------|
| id              | bigint(20)   |                    | UNSIGNED | no   | なし         | ID       | AUTO_INCREMENT |
| email           | varchar(255) | utf8mb4_unicode_ci |          | no   | なし         | メールアドレス | UNIQUE |
| name            | varchar(63)  | utf8mb4_unicode_ci |          | no   | なし         | 名前     |
| password        | varchar(255) | utf8mb4_unicode_ci |          | no   | なし         | ログインパスワード(暗号化) |
| last_logined_at | timestamp    |                    |          | yes  | NULL         | 最終ログイン日時 |
| remember_token  | varchar(100) | utf8mb4_unicode_ci |          | yes  | NULL         | パスワード再発行トークン |
| created_at      | timestamp    |                    |          | yes  | NULL         | 作成日   |
| updated_at      | timestamp    |                    |          | yes  | NULL         | 更新日   |
| deleted_at      | timestamp    |                    |          | yes  | NULL         | 削除日   |
  • テーブル名は見出しh2, スネークケース (## xxx_xxx)
  • カラム情報のヘッダは 名前, データ型, 照合順序, 属性, NULL, デフォルト値, コメント, その他 の順

変換スクリプト

$ python md2migrations.py
md2migrations.py
# coding: utf-8
import re

inFile = 'database.md'
outFolder = 'migrations/'

class Color:
    BLACK     = '\033[30m'
    RED       = '\033[31m'
    GREEN     = '\033[32m'
    YELLOW    = '\033[33m'
    BLUE      = '\033[34m'
    PURPLE    = '\033[35m'
    CYAN      = '\033[36m'
    WHITE     = '\033[37m'
    END       = '\033[0m'
    BOLD      = '\038[1m'
    UNDERLINE = '\033[4m'
    INVISIBLE = '\033[08m'
    REVERCE   = '\033[07m'

class Migrate():

    def __init__(self, tableLine):
        self.tableName = tableLine.replace('##', '').replace(' ', '').replace('\n', '')
        print('reading ' + self.tableName + '...')

        n = self.tableName.find('_')
        if n < 0:
            self.className = self.tableName[0].upper() + self.tableName[1:]
        else:
            self.className = self.tableName[0].upper() + self.tableName[1:n] + self.tableName[n + 1].upper() + self.tableName[n + 2:]

        self.code = """
<?php

use Illuminate\\Support\\Facades\\Schema;
use Illuminate\\Database\\Schema\\Blueprint;
use Illuminate\\Database\\Migrations\\Migration;

class Create""" + self.className + """Table extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('""" + self.tableName + """', function (Blueprint $table) {
"""

    def put(self, line):
        line = line.replace('\n', '')
        allItems = line.split('|')
        items = {}
        items['field'] = allItems[1].replace(' ', '')
        items['type'] = allItems[2].replace(' ', '')
        items['attribute'] = allItems[4].replace(' ', '')
        items['null'] = allItems[5].replace(' ', '')
        items['default'] = allItems[6].replace(' ', '')
        items['comment'] = re.sub('^ *| *$', '', allItems[7])
        items['extra'] = allItems[8].replace(' ', '')

        # ヘッダフィールドをスキップ
        if items['field'] == u'名前':
            return

        # 定型フィールド
        if items['field'] == 'id':
            self.code += "            $table->bigIncrements('id')->comment('" + items['comment'] + "');\n"
            return
        if items['field'] == 'remember_token':
            self.code += "            $table->rememberToken();\n"
            return
        if items['field'] == 'created_at':
            self.code += "            $table->timestamps();\n"
            return
        if items['field'] == 'updated_at':
            return
        if items['field'] == 'deleted_at':
            self.code += "            $table->softDeletes();\n"
            return

        # 定形外フィールド
        self.code += '            $table->'
        # データ型・属性
        if items['type'] == 'datetime':
            self.code += "dateTime('" + items['field'] + "')"
        elif items['type'] == 'timestamp':
            self.code += "timestamp('" + items['field'] + "')"
        elif re.match('bigint', items['type']):
            if re.match('UNSIGNED', items['type']):
                self.code += "unsignedBigInteger('" + items['field'] + "')"
            else:
                self.code += "bigInteger('" + items['field'] + "')"
        elif re.match('tinyint', items['type']):
            if re.match('UNSIGNED', items['type']):
                self.code += "unsignedTinyInteger('" + items['field'] + "')"
            else:
                self.code += "tinyInteger('" + items['field'] + "')"
        elif re.match('varchar', items['type']):
            strLen = re.sub('\\D', '', items['type'])
            self.code += "string('" + items['field'] + "', " + strLen + ")"
        elif items['type'] == 'boolean':
            self.code += "boolean('" + items['field'] + "')"            
        else:
            print(Color.RED + 'ERROR! ' + items['type'] + ' is not compatible!' + Color.END)

        # NULL
        if items['null'] == 'yes':
            self.code += '->nullable()'

        # デフォルト値
        if items['default'] != 'なし':
            self.code += "->default(" + items['default'] + ")"

        # コメント
        if items['comment'] != '':
            self.code += "->comment('" + items['comment'] + "')"

        # その他
        if re.match('UNIQUE', items['extra']):
            self.code += '->unique()'

        self.code += ';\n'

    def write(self):
        self.code += """        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('""" + self.tableName + """');
    }
}
"""
        if self.code.find('$table->') > 0:
            outFile = outFolder + '2019_11_15_000000_create_' + self.tableName + '_table.php'
            with open(outFile, mode='w') as f:
                f.write(self.code)
            print(Color.CYAN + 'wrote to ' + outFile + Color.END)


def main():
    migrate = None
    with open(inFile) as f:
        for line in f:
            if re.match('^## ', line):
                if migrate:
                    migrate.write()
                migrate = Migrate(line)
                continue

            if re.match('^\| ', line):
                migrate.put(line)

    if migrate:
        migrate.write()

    print(Color.GREEN + 'complate' + Color.END)

if __name__ == '__main__':
    main()

出力

reading users...
wrote to migrations/2019_11_15_000000_create_users_table.php
complate
migrations/2019_11_15_000000_create_users_table.php
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id')->comment('ID');
            $table->string('email', 255)->comment('メールアドレス')->unique();
            $table->string('name', 63)->comment('名前');
            $table->string('password', 255)->comment('ログインパスワード(暗号化)');
            $table->timestamp('last_logined_at')->nullable()->default(NULL)->comment('最終ログイン日時');
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}
  • 日時は2019_11_15_000000で固定です
  • 使いやすいように適当に直して使ってください
0
1
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
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?