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

@inabanet(プログラマー かけだしの)

memo

Last updated at Posted at 2020-10-04

const.py

# Excelファイルパス
excelFilePath = "./"
# Excelファイル名
excelFileName = "テーブル定義書.xlsx"
# Migrationファイル出力先
laravelMigrationFilePath = excelFilePath
# Migrationファイル名
laravelMigrationTimeStamp = "0000_00_00_000000"
# ファイル文字コード
fileEncode = "UTF-8"
# 改行コード
fileIndention = "\n"
# テーブル定義書除外シート一覧
exceptionSheetName = ['テーブル一覧','テーブル定義']
# テーブル情報
indexTableData = 4
# テーブル名
indexTableNmae = 6
# テーブルID
indexTableId = 36
# カラム情報開始行
indexColumnRow = 7

# ***************************************
# テーブル定義情報
# ***************************************
# 論理名
indexLogicalName = 2
# 物理名
indexPhysicalName = 14
# 型
indexShape = 26
# 符号付
indexUnsigned = 29
# ０埋め
indexZeroFill = 31
# カラム長さ
indexLength = 33
# カラム長さ（小数）
indexFew = 35
# PK
indexPrimaryKey = 40
# FK
indexForeignKey = 42
# UK
indexUniqueKey = 44
# AI
indexAutoIncrement = 46
# NN
indexNotNull = 48
# DF
indexDefault = 50
# IDX
indexIdx = 52

common.py

import re
import const

# スネークケース To キャメルケース変換
def snakeCaseToCamelCase(strValue):
    return re.sub("_(.)",lambda x:x.group(1).upper(),strValue)

def spaceInsert(spaceCount):
    strScript = ""
    for num in range(spaceCount):
        strScript = strScript + " "
    return strScript

def bracketsStart():
    return "{" + const.fileIndention

def bracketsEnd():
    return "}" + const.fileIndention

def strSpaceInsert(spaceCount, strValue):
    return spaceInsert(spaceCount) + strValue

def strNone(strValue):
    if strValue is None:
        return ""
    return strValue

docTableDefinition.py

import common
import const

def columnDefinition(rowData):
    addrs = []
    addrs.append(common.strNone(rowData[const.indexLogicalName].value))
    addrs.append(common.strNone(rowData[const.indexPhysicalName].value))
    addrs.append(common.strNone(rowData[const.indexShape].value))

    indexUnsignedFlg = str(False)
    if rowData[const.indexUnsigned].value is not None:
        indexUnsignedFlg = str(True)
    addrs.append(indexUnsignedFlg)

    indexZeroFillFlg = str(False)
    if rowData[const.indexZeroFill].value is not None:
        indexZeroFillFlg = str(True)
    addrs.append(indexZeroFillFlg)

    addrs.append(str(common.strNone(rowData[const.indexLength].value)))
    addrs.append(str(common.strNone(rowData[const.indexFew].value)))

    indexPrimaryKeyFlg = str(False)
    if rowData[const.indexPrimaryKey].value is not None:
        indexPrimaryKeyFlg = str(True)
    addrs.append(indexPrimaryKeyFlg)

    indexForeignKeyFlg = str(False)
    if rowData[const.indexForeignKey].value is not None:
        indexForeignKeyFlg = str(True)
    addrs.append(indexForeignKeyFlg)

    indexUniqueKeyFlg = str(False)
    if rowData[const.indexUniqueKey].value is not None:
        indexUniqueKeyFlg = str(True)
    addrs.append(indexUniqueKeyFlg)

    indexAutoIncrementFlg = str(False)
    if rowData[const.indexAutoIncrement].value is not None:
        indexAutoIncrementFlg = str(True)
    addrs.append(indexAutoIncrementFlg)

    indexNotNullFlg = str(False)
    if rowData[const.indexNotNull].value is not None:
        indexNotNullFlg = str(True)
    addrs.append(indexNotNullFlg)

    indexDefaultFlg = str(False)
    if rowData[const.indexDefault].value is not None:
        indexDefaultFlg = str(True)
    addrs.append(indexDefaultFlg)

    return addrs

laravelMigrations.py

import const
import common

# Laravel Migration Class 名変換
def snakeCaseToCamelCaseForLaravelMigrationClassName(strValue):
    laravelMigrationClassName = common.snakeCaseToCamelCase(strValue)
    return laravelMigrationClassName[0].upper() + laravelMigrationClassName[1:]

# Laravel Migration ヘッダー部
def laravelMigrationHeader(strValue):
    strScript = "<?php" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "use Illuminate\Support\Facades\Schema;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Schema\Blueprint;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Migrations\Migration;" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "class " + snakeCaseToCamelCaseForLaravelMigrationClassName(strValue) + " extends Migration" + const.fileIndention
    strScript = strScript + common.bracketsStart()
    return strScript

# Laravel Migration フッター部
def laravelMigrationFooter(strValue):
    return common.bracketsEnd()

# Laravel Migration Up Method ヘッダー部
def laravelMigrationUpHeader(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* マイグレーション実行" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function up()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    return strScript

def laravelMigrationUpSchema(strValue, aryColumns):
    strScript = common.strSpaceInsert(8,"Schema::create('"+ strValue +"', function (Blueprint $table) {" + const.fileIndention)
    for column in aryColumns:
        strScript = strScript + common.strSpaceInsert(12, column + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableEngine() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCharset() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCollation() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(8,"});" + const.fileIndention)
    return strScript
        
# Laravel Migration Up Method フッター部
def laravelMigrationUpFooter(strValue):
    return common.strSpaceInsert(4,common.bracketsEnd())

# Laravel Migration Down Method
def laravelMigrationDown(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* マイグレーションを元に戻す" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function down()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    strScript = strScript + common.strSpaceInsert(8,"Schema::drop('" + strValue + "');" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsEnd())
    return strScript

# Laravel Migration Script
def laravelMigrationScript(strValue, aryColumns):
    strScript = laravelMigrationHeader(strValue)
    strScript = strScript + laravelMigrationUpHeader(strValue)
    strScript = strScript + laravelMigrationUpSchema(strValue, aryColumns)
    strScript = strScript + laravelMigrationUpFooter(strValue)
    strScript = strScript + laravelMigrationDown(strValue)
    strScript = strScript + laravelMigrationFooter(strValue)
    return strScript

def laravelMigrationTableEngine(strValue = "InnoDB"):
    return "$table->engine = '" + strValue + "';"

def laravelMigrationTableCharset(strValue = "utf8mb4"):
    return "$table->charset = '" + strValue + "';"

def laravelMigrationTableCollation(strValue = "utf8mb4_unicode_ci"):
    return "$table->collation = '" + strValue + "';"

def laravelMigrationTableIncrements(strValue, strShape):
    strScript = ""

    if strShape == 'bigint':
        strScript = "$table->bigIncrements('" + strValue + "')"
    elif strShape == 'integer':
        strScript = "$table->increments('" + strValue + "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumIncrements('" + strValue + "')"
    elif strShape == 'smallint':
        strScript = "$table->smallIncrements('" + strValue + "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyIncrements('" + strValue + "')"
    else:
        strScript = "$table->increments('" + strValue + "')"

    return strScript

def laravelMigrationTableString(strValue, strShape, strLength):
    strScript = ""

    if strShape == 'char':
        strScript = "$table->char('" + strValue + "', " + strLength + ")"
    elif strShape == 'varchar':
        strScript = "$table->string('" + strValue + "', " + strLength + ")"
    elif strShape == 'text':
        strScript = "$table->text('" + strValue + "')"
    elif strShape == 'mediumText':
        strScript = "$table->mediumText('" + strValue + "')"
    elif strShape == 'longText':
        strScript = "$table->longText('" + strValue + "')"

    return strScript

def laravelMigrationTableNumerical(strValue, strShape):
    strScript = ""
    if strShape == 'bigint':
        strScript = "$table->bigInteger('" + strValue +  "')"
    elif strShape == 'integer':
        strScript = "$table->integer('" + strValue +  "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumInteger('" + strValue +  "')"
    elif strShape == 'smallint':
        strScript = "$table->smallInteger('" + strValue +  "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyInteger('" + strValue +  "')"
    elif strShape == 'unsignedbigint':
        strScript = "$table->unsignedBigInteger('" + strValue +  "')"
    elif strShape == 'unsignedinteger':
        strScript = "$table->unsignedInteger('" + strValue +  "')"
    elif strShape == 'unsignedmediumint':
        strScript = "$table->unsignedMediumInteger('" + strValue +  "')"
    elif strShape == 'unsignedsmallint':
        strScript = "$table->unsignedSmallInteger('" + strValue +  "')"
    elif strShape == 'unsignedtinyint':
        strScript = "$table->unsignedTinyInteger('" + strValue +  "')"

    return strScript

def laravelMigrationTableComment(strValue, strLogicalName):
    return strValue + "->comment('" + strLogicalName + "');"

laravelMigrationCreateScript.py

import openpyxl
import pprint

import common
import const
import laravelMigrations
import docTableDefinition

wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)

for sheets in wb.sheetnames:
    rows = []
    strTableId = wb[sheets].cell(row=const.indexTableData, column=const.indexTableId).value
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        if row[const.indexLogicalName].value is None:
            break

        # [0] :論理名
        # [1] :物理名
        # [2] :型
        # [3] :符号付
        # [4] :０埋め
        # [5] :カラム長さ
        # [6] :カラム長さ（小数）
        # [7] :PK
        # [8] :FK
        # [9] :UK
        # [10]:AI
        # [11]:NN
        # [12]:DF
        # [13]:IDX

        strScriptData = docTableDefinition.columnDefinition(row)
        strScript = ""

        # Auto Increment
        if strScriptData[10] == "True":
            strScript = laravelMigrations.laravelMigrationTableIncrements(strScriptData[1], strScriptData[2])
        
        # 文字列カラム
        elif strScriptData[2] == "char" \
            or strScriptData[2] == "varchar" \
            or strScriptData[2] == "text" \
            or strScriptData[2] == "mediumText" \
            or strScriptData[2] == "longText" :
            strScript = laravelMigrations.laravelMigrationTableString(strScriptData[1], strScriptData[2], strScriptData[5])

        # 数値カラム
        elif strScriptData[2] == "bigint" \
            or strScriptData[2] == "integer" \
            or strScriptData[2] == "mediumint" \
            or strScriptData[2] == "smallint" \
            or strScriptData[2] == "tinyint" :

            if strScriptData[3] == "True":
                strScriptData[2] = "unsigned" + strScriptData[2]

            strScript = laravelMigrations.laravelMigrationTableNumerical(strScriptData[1], strScriptData[2])


        # Comment
        strScript = laravelMigrations.laravelMigrationTableComment(strScript, strScriptData[0])
        
        #print(strScript)

        rows.append(strScript)
    
    print(laravelMigrations.laravelMigrationScript(strTableId, rows))

#        print(docTableDefinition.columnDefinition(row))
#        rows.append(docTableDefinition.columnDefinition(row))



# for row in rows:
#    print(row)
#    print(",".join(row))

test.py

import openpyxl
import pprint

import const
# import common
import laravelMigrations

# wb = openpyxl.load_workbook("テーブル定義書.xlsx")
wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)
print(wb.sheetnames)
# print(len(wb.sheetnames))
# print(wb.worksheets[0].title)

# print("")

for sheets in wb.sheetnames:
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        addrs = []
        addrs.append(row[const.indexLogicalName].value)
        print(",".join(addrs))
        break


# for row in wb.worksheets[0].iter_rows(min_row=const.indexColumnRow):
#    addrs = []
#    for cell in row:
#        addrs.append(cell.coordinate)
#    print(",".join(addrs))
#    print(row[const.indexLogicalName].value)
#    print(row[const.indexPhysicalName].value)
#    print(row[const.indexShape].value)

#    addrs.append(row[const.indexLogicalName].value)
#   addrs.append(row[const.indexPhysicalName].value)
#    addrs.append(row[const.indexShape].value)
#    indexUnsignedFlg = str(False)
#    if row[const.indexUnsigned].value is not None:
#        indexUnsignedFlg = str(True)
#    addrs.append(indexUnsignedFlg)
#    print(",".join(addrs))
#    break

#    print(wb.worksheets[0].max_row)

# print(common.snakeCaseToCamelCase("aaa_bbb_ccc"))

# print(laravelMigrations.snakeCaseToCamelCaseForLaravelMigrationClassName("aaa_bbb_ccc"))

# print(laravelMigrations.laravelMigrationScript("aaa_bbb_ccc"))
0
0
1

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
Sign upLogin
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?