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

こんなマニアックな需要があるのでしょうか。正直SQLエディタを選べばコピー&ペーストでポイッてイケそうですが、その選択が出来なかったので作りました。

今回は、show create table "MyTable"で取得、あるいはGUI上でも表示されるCREATE TABLE構文を利用しています。

欲しいデータは何？

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

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

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

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

SQLを見てみる

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

-- table_master.sql
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

間に合わせとはいえ、これは流用が効きにくい。

#Requires -Version 2.0
# Get-ColumnCommentSQL.ps1

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,

tech.guitarrapc.cóm

Technical updates

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

欲しいデータは何？

SQLを見てみる

PowerShellでどう読むか

コード