MySQLのテーブル定義をGitでバージョン管理する。しかもWindowsで

いやそんな必要ないだろうって言われると思いますが、思いつきでやってみたので書きます。

経緯

ウチの環境ではひとつのDBサーバーを複数名の開発者でいじっており、データベース・テーブル定義の変更は特に体系立てて記録していません。
「このカラム追加って俺がやったのは覚えてるんだけど、いつやったんだっけ？」みたいなことがたまにありました。
これではまずい（かもしれない）と思ったものの、MySQL側でそれを自動で記録する方法が用意されていません。（私の知る限りでは&&標準では）
phpMyAdminなどのUIを使えばそのログで記録できますが、かならずそのUIを使うとは限りません。

そんなもやもやしているころ、PHPのファイルのバージョン管理をするためにGitを導入した（というかSubversionから乗り換えた）んですが、これがなかなか便利なものだったのでこれを流用できないかと考えてみたんです。

結果、ちょっと無理くりなところもあるし荒削りなところもあるんですが、ウチで必要な状態には持ち込めたのでヨシとします。

要件

• Windows ( そういう決まりなので・・・べつにWindowsが好きでやっているわけでは・・ ）
• テーブル定義が変更された内容がわかること。
• テーブル定義が変更された日付がわかること。
• タスクスケジューラとバッチファイルなどで自動化されること。

概要

mysqldumpでデータを書き出さない形でSQL文のファイルを作り、
gitコマンドでそのファイルをaddしてcommitします。
これを毎日同じ時刻に繰り返すバッチを作ります。

手順1 mysqldump

mysqldumpのオプションは --all-databases と --no-data を使います。
こんな感じです。

> mysqldump --user=name --password=pass --all-databases --no-data > D:\log\mysql\schema.git\schema.sql

データベースごとに別ファイルにしたい場合はその分のコマンドを実行する必要があり、さらにデータベースが追加/削除された場合のロジックを書くのが面倒なのでやめます。
出力先はGitのリポジトリになることを想定して決めます。

手順2 AUTO_INCREMENT値の削除

データを書き出さないようにしても、レコード増に影響を受けてAUTO_INCREMENTの値が変わり、dumpされたファイルに差ができてしまいます。
今回の目的ではその違いは無視したいところだったので、これを無視できる形に変える必要があります。

知恵袋的なところにも同じ相談があって

% mysqldump -d ... | sed -e 's/AUTO_INCREMENT=[0-9]*//'

すればおｋって回答が書いてあるんですが、Windowsなので使えません。

そこで検討されるひとつめの方法は mysqldump に --skip-create-optionsオプションをつけること。
すると「AUTO_INCREMENT=xxx」が出力されなくなるのですが、一緒に「ENGINE=xxx」の定義も出力されなくなります。
残念ながらウチの環境ではこれをストレージエンジンを変更する可能性があり、「ENGINE=xxx」が出力されないのは私の意図から外れてしまいます。

さらに毎回変わる箇所が他にもありました。
dumpされたファイルの最後に「Dump completed on YYYY-MM-DD HH:mm:SS」の形式でdumpが完了した時刻が残ってしまうのです。

ということで「--skip-create-optionsオプション」案はボツに。


そこで二つ目の方法、出力されたあとでdumpされたファイルの中から無視したい箇所をVBSで置換すること。
コマンドプロンプトだと正規表現がろくに使えないのでVBSにしたんですが、これが予想より面倒で、あとから考えるとPHPで加工するほうが楽だったかも。
ってかバッチとかVBSじゃなくてPowerShellでやれよって話ですよね。ごめんなさい。PowerShell未修得なんです。

VBS

VBSってなんだよっていう方がもしかしたらいらっしゃるかもしれないので説明すると、
VBScriptの略で、VB風の書式で書けるライトウェイトでバッチファイルよりはいろいろできるスクリプトです。
・・・などと大雑把過ぎる説明をしましたが、そこを知らないとこのあと（特にWindowsに耐性のない方に）はキツいので、興味本位で読んでいる方は手順3まで読み飛ばしてください。

さてdumpされたファイルをVBSで開こうとするならそれはテキストファイルなので FileSystemObjectのOpenTextFileメソッドを使おうとします。
で、一行ずつさらっていって正規表現(RegExpオブジェクト)でマッチしたら置換してゆこうと。
この方法は間違ってはいないのですが、ファイル内にマルチバイト文字があると困ったことになります。

いまどきテーブル名に日本語を使っている方はあまりいらっしゃらないと思いますが（・・ってかやめましょうね）、テーブルコメントやカラムコメントに日本語を使うことは多いでしょう。
で、多くの場合これはUTF-8で書かれていることでしょう。
ところがFileSystemObjectではcp932以外の文字コードの操作ができないので結果が文字化けして死亡します。
おおvbsよ、しんでしまうとはなさけない。

ということでGoogle先生にお願いしたらこんな記事を教えてくれました。


参考：
拝啓、シーシュポス [VBScript]UTF-8でファイル入出力（ADODB.Streamオブジェクト）


どうやら「ADODB.Stream」というのを使えばUTF-8もあつかえるようなのです。
ということでいろいろ省略して出来上がったソースを。

'------------------------------------------------------
' replaceDumpedSql()
' mysqldumpしたファイルの中から不要な部分を削る
'
' @param readFilePath dumpされたSQLファイルパス
' @param writeFilePath 置換後のSQLファイルパス
' @return bool
'------------------------------------------------------
Function replaceDumpedSql( readFilePath, writeFilePath )

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    '「AUTO_INCREMENT=xx」を置き換えるための正規表現
    Set RegexpAI  = New RegExp
    RegexpAI.Pattern = "AUTO_INCREMENT=([0-9]*)"
    RegexpAIAfter    = "AUTO_INCREMENT=?"

    '「Dump completed on xx」を消去するための正規表現
    Set RegexpDC  = New RegExp
    RegexpDC.Pattern = "Dump completed on .*"
    RegexpDCAfter    = ""

    '入力ファイルのストリーム
    Set inStream = CreateObject("ADODB.Stream")
    'type 1:バイナリデータ 2:テキストデータ
    inStream.type = 2
    inStream.charset = "UTF-8"
    inStream.open
    inStream.LoadFromFile readFilePath

    '出力ファイルのストリーム
    Set outStream =CreateObject("ADODB.Stream")
    outStream.type = 2
    outStream.charset = "UTF-8"
    outStream.open 

    '1行ずつ読み込む
    Do While inStream.EOS = False

        'ReadText第1引数 -1:全部読み込む -2:一行読み込む
        line = inStream.ReadText(-2)
        
        'AUTO_INCREMENT=xxを置き換え
        If RegexpAI.Test( line ) Then
            line = RegexpAI.Replace(line, RegexpAIAfter)
        End If
        
        'Dump completed on を消去
        If RegexpDC.Test( line ) Then
            line = RegexpDC.Replace(line, RegexpDCAfter)
        End If

        '出力ファイルに書きこむ
        'WriteText第2引数 0:文字列のみ書き込む 1:文字列＋改行を書き込む
        outStream.WriteText line, 1
    Loop

    '入力ストリームを閉じる
    inStream.Close
    Set inStream = Nothing

    '出力ファイルが同名の場合、削除しておく。
    If readFilePath = writeFilePath Then
        objFSO.DeleteFile readFilePath, True
    End If

    '出力ストリームをファイルへの書き込み
    'SaveToFile第2引数 1:ファイルがない場合はファイル作成 2:ファイルがある場合は上書き
    replaceDumpedSql = outStream.SaveToFile writeFilePath, 2

    '出力ストリームを閉じる
    outStream.Close
    Set outStream = Nothing

End Function

'実行
replaceDumpedSql "D:\log\mysql\schema.git\schema.sql", "D:\log\mysql\schema.git\schema.sql"

手順3 Gitコマンド

cdコマンドでGitのリポジトリに移動し、「git add .」でaddして、「git commit -m "コメント"」でcommitします。
コメントには日付を入れました。

バッチファイル

というわけで出来上がったバッチファイルはこちら。

: 定義をdumpする
mysqldump --user=name --password=pass --all-databases --no-data > D:\log\mysql\schema.git\schema.sql

: AUTO_INCREMENT値等を消す
CALL replaceDumpedSql.vbs

: 日付から/を取り除いた文字列を作る
SET dt=%DATE:/=%

: リポジトリに移動する
CD /D D:\log\mysql\schema.git

: Gitコマンド
git add .
git commit -m "backup-%dt%"

手順4 タスクスケジューラで登録

開発者が触らない時刻にこれを実行するようタスクに登録しておきます。

結果を見てみる

git bashの画面で差を読み取れるほどCUIに目が慣れていないので、EclipseのEGitを使います。
見たいときにサーバー上のgitリポジトリからpullしてきて、compareしてみるとこんな感じです。

列がひとつ追加されたのが見えますね。
AUTO_INCREMENT=の後ろが?になっているのもご確認いただけるかと。

難点

• 変更された日付はわかりますが、時刻はわかりません。
  (え?日中にスキーマ定義変えるの?)
• 誰が変更を行ったのかが見えません。Viewは定義者の名前が残りますが。
• これが実行されるタイミングで開発者がいじっている可能性がなくはないです。

はい、以上自己満足の世界でした！