PowerShellでSQL生成構文からカラム名とコメントを抜き出す

こんなマニアックな需要があるのでしょうか。あ、あったので作りました (震え声正直SQLエディタを選べばコピペでﾎﾟｲｯてイケそうですが、その選択が出来なかったので。今回は、show create table "MyTable" で取得、あるいはGUI上でも表示される、CREATE TABLE構文を利用しています。では見てみましょう。

欲しいデータは何？

今回は、こういったデータが必要となりました。

テーブル名
テーブルコメント(あれば)
カラム名
カラムコメント

合わせて出力フォーマットも希望がありました。エクセルで管理するため、こういうイメージでcsvで欲しいとのことです。

テーブル名
テーブルコメント
カラムコメント1	カラムコメント2	カラムコメント3	カラムコメント4	カラムコメント5	カラムコメント6
カラム名1	カラム名2	カラム名3	カラム名4	カラム名5	カラム名6

SQLを見てみる

まずはどう取りたいのか、SQLを見てみましょう。 table_master.txt

CREATE TABLE `test_table` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, COMMENT 'IDだよ！',
	`result` INT(10) NOT NULL DEFAULT '0' COMMENT '結果。0 = 未、1 = 勝利、2 = 敗退',
	`created` DATETIME NOT NULL,
	`modified` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `	result` (`result`)
)
COMMENT='テストだよ！'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0;

PowerShellでどう読むか

むむ………` (バッククォート)が使われてますね……PowerShellではエスケープ文字なので、この時点でヒアストリングや変数への直接の代入ができないことが確定しました。カラム名は`` で、カラムコメントは''で判断がつききそうです。 CREATE TABLEやCOMMENT= で、テーブルの情報も絞れます。ということで、さっくり正規表現で行きましょう。

コード

GitHubに公開してあります。

PowerShellUtil / Get-ColumnCommentSQL

さっくり。しかし間に合わせとはいえ、これは流用が効きにくい……。 Get-ColumnCommentSQL.ps1

#Requires -Version 2.0

param
(
    [Parameter(
    HelpMessage = @"
    Input path of CREATE TABLE sql file. If blank then './tablemaster.txt' will use.
    Sample text requires to be pastes.

CREATE TABLE `test_table` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, COMMENT 'IDだよ！',
	`result` INT(10) NOT NULL DEFAULT '0' COMMENT '結果。0 = 未、1 = 勝利、2 = 敗退',
	`created` DATETIME NOT NULL,
	`modified` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `	result` (`result`)
)
COMMENT='テストだよ！'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0;

"@,
    Position = 0,
    Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true
    )]
    [ValidateNotNullOrEmpty()]
    [ValidateScript({Test-Path $_.FullName})]
    [IO.FileInfo[]]
    $InputPath = ".\table_master.txt",

    [Parameter(
    HelpMessage = @"
    Output path of converted csv file. If blank then If blank then 'tablename.csv' will create.
    Sample converted csv.

test_table
テストだよ！
IDだよ！, 結果。0 = 未、1 = 勝利、2 = 敗退, , , ,
id, result, created, modified, id,

"@,
    Position = 1,
    Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true
    )]
    [ValidateNotNullOrEmpty()]
    $OutputPath
    )

function Get-ColumnCommentSQL{

    [CmdletBinding(
        SupportsShouldProcess = $false,
        ConfirmImpact = "none",
        DefaultParameterSetName = ""
    )]
    param
    (
        [Parameter(
        HelpMessage = @"
        Input path of CREATE TABLE sql file. If blank then './tablemaster.txt' will use.
        Sample text requires to be pastes.

CREATE TABLE `test_table` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, COMMENT 'IDだよ！',
	`result` INT(10) NOT NULL DEFAULT '0' COMMENT '結果。0 = 未、1 = 勝利、2 = 敗退',
	`created` DATETIME NOT NULL,
	`modified` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `	result` (`result`)
)
COMMENT='テストだよ！'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0;
"@,
        Position = 0,
        Mandatory = $false,
        ValueFromPipeline = $true,
        ValueFromPipelineByPropertyName = $true
        )]
        [ValidateNotNullOrEmpty()]
        [ValidateScript({Test-Path $_.FullName})]
        [IO.FileInfo[]]
        $InputPath = ".\table_master.txt",

        [Parameter(
        HelpMessage = @"
        Output path of converted csv file. If blank then If blank then 'tablename.csv' will create.
        Sample converted csv.

test_table
テストだよ！
IDだよ！, 結果。0 = 未、1 = 勝利、2 = 敗退, , , ,
id, result, created, modified, id,
"@,
        Position = 1,
        Mandatory = $false,
        ValueFromPipeline = $true,
        ValueFromPipelineByPropertyName = $true
        )]
        [ValidateNotNullOrEmpty()]
        $OutputPath
     )

    begin
    {
        # initialize String Array
        [string[]]$hoge = [string[]]$moge = [string[]]$fuga = [string[]]$foo = $null

        # Check previous csv files are existing or not.
        if (Test-Path .\*.csv)
        {
            Remove-Item -Path ./*.csv -Confirm -Force
        }

    }

    process
    {

        # Get Table Name
        $TableName = Get-Content -Path $InputPath -Encoding Default `
            | ?{
                $_ -cmatch "^CREATE TABLE [``](?<TableName>.*)[``].*"
                } `
            | %{
                $Matches.TableName
            }

        # Get Table Comments
        $TableComment = Get-Content -Path $InputPath -Encoding Default `
            | ?{
                $_ -cmatch "^COMMENT='(?<TableComment>.*)'"
                } `
            | %{
                $Matches.TableComment
            }


        # Get Column Name and Comments
        $ColumnResult = Get-Content -Path $InputPath -Encoding Default `
            | ?{ $_ -match "[' ']*.*," } `
            | %{
                    $_ -match "[``].*[``].*'(?<ColumnComment>.*)'" > $null
                    $ColumnComment = $Matches.ColumnComment
                    $_ -match "[``](?<Column>.*)[``].*" > $null
                    $Column = $Matches.Column

                    [PSCustomObject]@{
                    Column = $Column
                    ColumnComment = $Columncomment
                    }
                }
        }

    end
    {

        # add conmma to each columns
        $hoge += $ColumnResult | %{[string]$_.ColumnComment + ","}
        $moge += $ColumnResult | %{[string]$_.Column + ","}

        #region output parameters definition
        $AppendOutputOptions = @{
            Append = $true
            Encoding = "default"
            NoClonner = $true
            Force = $true
        }

        $NoAppendOutputOptions = @{
            Append = $false
            Encoding = "default"
            NoClonner = $true
            Force = $true
        }
        #endregion


        #region Export files to csv (Could not use Export-Csv as using "" to pickup ArrayStrings)
        switch ($true){
        {$OutputPath -ne $null} {
            "$TableName" | Out-File -FilePath $OutputPath -Encoding default -NoClobber -Force
            "$TableComment" | Out-File -FilePath $OutputPath -Encoding default -NoClobber -Force -Append
            "$hoge" | Out-File -FilePath $OutputPath -Encoding default -NoClobber -Force -Append
            "$moge" | Out-File -FilePath $OutputPath -Encoding default -NoClobber -Force -Append
            }

        default {
            "$TableName" | Out-File -FilePath "$($TableName).csv" -Encoding default -NoClobber -Force
            "$TableComment" | Out-File -FilePath "$($TableName).csv" -Encoding default -NoClobber -Force -Append
            "$hoge" | Out-File -FilePath "$($TableName).csv" -Encoding default -NoClobber -Force -Append
            "$moge" | Out-File -FilePath "$($TableName).csv" -Encoding default -NoClobber -Force -Append
            }
        }
        #endregion

    }
}

Get-ColumnCommentSQL

Moduleでの追加は中々伝わらないので、バッチ連動にしています。 run_Get-ColumnCommentSQL.bat

@echo off
echo 管理者で実行すること！！ (ExecutionPolicy - 権限ののエラーが出ます)

powershell.exe -ExecutionPolicy RemoteSigned -File .\Get-ColumnCommentSQL.ps1

pause

読み取り対象のSQLファイルは、ps1やバッチファイルと同一パスに table_master.txt として設置します。実行すると、 テーブル名.csv　が生成されます。この時、*.csvが存在した場合は、削除するか聞かれますので、消してください。生成データのサンプルです。 test_table.csv

test_table
テストだよ！
IDだよ！, 結果。0 = 未、1 = 勝利、2 = 敗退, , , ,
id, result, created, modified, id,

……あ、今更ながらPowerGUIでコンパイルすればよかったのでは……いや、でもスクリプトパスが狂うし…

tech.guitarrapc.cóm

Technical updates

PowerShellでSQL生成構文からカラム名とコメントを抜き出す

欲しいデータは何？

SQLを見てみる

PowerShellでどう読むか

コード