VBAでテーブル仕様書からDDL文を自動生成する

概要
1. このページでは
  1. テーブル仕様書とは
  2. 今回の記事における限定・制約
詳細
補足
1. 今後の展開
2. 業務支援について

概要

このページでは

Excelのテーブル仕様書から、DDL文をVBAで自動生成するツールを作成します。

テーブル仕様書とは

ウォーターフォール型で進めるプロジェクト設計では、データベース設計を行う際に、テーブル仕様書なるものを記述します。（アジャイル開発などでも必要に応じて設計書に起こしたりします。）
テーブル仕様書には、テーブルの目的や要件と、構成する列の属性などを定義しておき、実際のテーブルを作成する際のインプット資料とします。

開発工程では、テーブル仕様書を作成した後、その内容からDDL文（Create Table文）を起こして実際のテーブルを作成しますが、今回はこれをVBAで自動生成するツールを作ります。簡単なサンプルになりますが、拡張すれば実業務でも使えるものです。

今回の記事における限定・制約

本来のテーブル仕様書には、プロジェクト名やら作成・確認者や作成・更新履歴などの書き込み欄が存在しますが、今回は紛らわしくなるので、最低限の項目だけで話をすすめます。また、内部データ型も今回は限定して作成します。　
いずれも仕組みが理解出来れば簡単に拡張出来ると思います。
なお、自動生成するDDL文はOracleの形式とします。

詳細

テーブル仕様の概要

イメージ

Excel文書ファイルでテーブル仕様書を作成します。
すべて同じレイアウトにすることで様々な自動生成処理を実現することが出来ます。

今回はこのExcel文書ファイルからDDL文を自動生成するツールを作成します。

テーブルの情報

・テーブル名
・テーブルID
・要件

構成する列の情報

・No
・日本語名
・カラム名（列ID）
・データ型（内部データ型）
・長さ　　（桁数）
・PK　　　（主キーならY）
・初期値
・NULL許容（NOT NULLならN）
・備考

取扱う内部データ項目

内部データ項目の形式は、DDL文の自動生成についてわかりやすくシンプルな説明とするため以下の3つだけにします。（これを理解出来れば他の内部データ項目の拡張も容易です。）

・DATE　　　固定長日付/時刻値
・NUMBER　　固定数または浮動小数点数
・VARCHAR2　可変長文字列(2000バイト以下)

作成するDDL文

上記のテーブル仕様書からDDL文（Create Table）を自動生成します。
ブック内に複数のテーブル仕様書シートがあればすべて対象とします。

・テーブル仕様書には日本語名や備考が記述されているので、それらをコメントとしてDDL文に添えるのがよいが今回は省略します。（続編で実現予定）

・今回はサンプルなので、以下の考慮をしていません。

　テーブル仕様書以外のシートを含んだ場合の考慮をしません。
　一般的には何らかのルールを設けて、自動生成対象のシートかどうか判断可能なレイアウトにします。例えば今回の例ですとシート名とテーブルIDが等しければ自動生成の対象とする、などとしてもいいかもしれません。（厳格なことを言えばそれではNGだと思いますが）

　セルの値の入力チェックをしていません。
　日本語名の必須チェックや長さの数値チェックなど沢山のチェックが考えられますが、今回は実装の対象外としています。いざチェックを入れようとした場合、事前チェック用のツールを作った方がいい結果が得られるかもしれません。（細かなチェックと生成が絡み合ってわかりにくくなるので）

DDLサンプル

実際に今回のツールで生成したものです。

CREATE TABLE customer (
    cid varchar2(8) NOT NULL, 
    cname varchar2(300) NOT NULL, 
    founded date , 
    capital number(10) DEFAULT 0, 
    address varchar2(500), 
    telno varchar2(100), 
    note varchar2(1000), 
 PRIMARY KEY (cid)
)

実現方法・説明

VBAソースコード

メイン（main）

'
' テーブル仕様書Excel文書ファイルからDDL文を自動生成
'
Sub main()
  
    Dim bookname As String
    bookname = "makedll01.xlsm"
  
    Dim wb As Workbook
    Dim ws As Worksheet

    'Excel文書ファイルをブックオブジェクトとして設定
    Set wb = Workbooks(bookname)
    For Each ws In wb.Worksheets
        'テーブル毎の処理（DDL文の生成）
        sheet_proc ws
    Next
    
    MsgBox "おわり"

End Sub

メインの説明

Excel文書ファイル（ブック）に含まれるテーブル仕様書（シート）毎に、DDL文を生成する処理（処理名：sheet_proc）を呼び出すロジックになっています。
この処理により１ブックに作成したテーブル仕様書分のDDL文が自動生成されることになります。

なお、ブックに含まれる複数シートの処理については、
Excelブックに含まれる複数シートの一括処理
に説明したページがありますのでよろしければご覧ください。

シート毎の処理（sheet_proc）


' テーブルレイアウト生成（シート毎の処理）
'
'   引数はテーブル仕様書のシートオブジェクト
'
Sub sheet_proc(ws As Worksheet)

    'DDLファイル生成用
    Dim f As Integer            ' ファイル番号
    Dim fname As String         ' ファイル名
    
    'Excelシート読み込み（テーブル情報）
    Dim tname As String         ' テーブル名
    Dim tid As String           ' テーブルID
    Dim tnote As String         ' 要件
        
    'Excelシート読み込み（カラム情報）
    Dim itemno As String        ' No
    Dim itemname As String      '日本語名
    Dim itemid As String        'カラム名
    Dim itempk As String        'プライマリーキー
    Dim itemtype As String      'データ型
    Dim itemleng As String      '長さ
    Dim itemval As String       '初期値
    Dim itemnull As String      'NULL
    Dim itemnote As String      '備考
    
    Dim pktbl(100) As String    'プライマリーキー保存エリア
    Dim pkcount As Integer      'プライマリーキー保存数
    
    Dim linectr As Integer
    Dim workstr As String       'SQL編集用領域
    
    pkcount = 0     　　　　　　' プライマリーキー保存数のクリア
    linectr = 6         　　　　' カラム開始行のセット（この行から処理をする）
            
    '処理中のシートからテーブル情報を読み取る
    tname = ws.Cells(1, 2)
    tid = ws.Cells(2, 2)
    tnote = ws.Cells(3, 2)
    
    'ファイル名の設定（テーブル名付与）
    fname = "CreateTable_" & tid & ".sql"
    
    'ファイルオープン
    '（開いているシートのテーブルDDL文創成）
    f = FreeFile
    Open fname For Output As #f
    
    'Create Table文の自動生成
    Print #f, "CREATE TABLE " & tid & " ("
    
    'Noの列に値がある間繰り返す
    While ws.Cells(linectr, 1) <> ""
        
        DoEvents
        
        'カラムの情報を読み取る
        itemno = Trim(ws.Cells(linectr, 1))
        itemname = Trim(ws.Cells(linectr, 2))
        itemid = Trim(ws.Cells(linectr, 3))
        itempk = Trim(ws.Cells(linectr, 4))
        itemtype = Trim(ws.Cells(linectr, 5))
        itemleng = Trim(ws.Cells(linectr, 6))
        itemval = Trim(ws.Cells(linectr, 7))
        itemnull = Trim(ws.Cells(linectr, 8))
        itemnote = Trim(ws.Cells(linectr, 9))
        
        'Create Table文の自動生成
        workstr = ""
        'カラム名編集
        workstr = workstr & itemid & " "
            
        'プライマリーキー保存number
        If itempk = "Y" Then
            pkcount = pkcount + 1
            pktbl(pkcount) = itemid
        End If
        
        'データ型編集
        Select Case itemtype
            Case "文字"
                workstr = workstr & "varchar2("
                workstr = workstr & itemleng & ")"
            Case "数値"
                workstr = workstr & "number("
                workstr = workstr & itemleng & ")"
            Case "日付"
                workstr = workstr & "date "
        End Select
                
        '初期値編集（記入時のみ）
        If Len(itemval) > 0 Then
            workstr = workstr & " DEFAULT " & itemval
        End If
        
        'NULL編集（記入時のみ）
        If Len(itemnull) > 0 Then
            If itemnull = "N" Then
                workstr = workstr & " NOT NULL"
            End If
        End If
        
        workstr = workstr & ", "
        Print #f, Space(4); workstr
            
        linectr = linectr + 1
    
    Wend
    
    'プライマリーキー情報の編集
    Dim i As Integer
    If pkcount > 0 Then
        Print #f, " PRIMARY KEY (";
        For i = 1 To pkcount
            Print #f, pktbl(i);
            If i < pkcount Then
                Print #f, ",";
            Else
                Print #f, ")"
            End If
        Next
    End If
    
    Print #f, ")"
    Close #f

End Sub