2つのデータベースの差分からDDL(CREATE/DROP/ALTER TABLE文)を作成する

#「テーブルの追加と修正をしたから各自開発環境でこのCREATE/ALTER文を実行して！」

データベースのマイグレーションツールを使っていない場合は、こういった作業をしばらくサボって、あとで困ってしまうことがよくあると思います。

そんなときは、
[SQL::Translator (SQLFairy)](http://sqlfairy.sourceforge.net/) 
を使ってさくっとテーブル構造を同期させましょう。

## 準備
### SQL::Translatorのインストール

```bash:
$ sudo yum install perl-SQL-Translator.noarch -y
```

## テーブル構造の同期

### 1. 2つのデータベースのスキーマをバックアップ

```bash:
$ mysqldump -h[NEW_DB] -uUSER DB_NAME -d -p | sed -e 's/AUTO_INCREMENT=[0-9]\+//' > NEW_DB_schema.sql
$ mysqldump -h[OLD_DB] -uUSER DB_NAME -d -p | sed -e 's/AUTO_INCREMENT=[0-9]\+//' > OLD_DB_schema.sql
```

[OLD_DB] = テーブル構造の古いデータベース
[NEW_DB] = テーブル構造の新しいデータベース

※AUTO_INCREMENTは邪魔になるのでsedで削除しています。

### 2. 2つのバックアップファイルから差分DDLを作成

```bash:
$ sqlt-diff OLD_DB_schema.sql=MySQL NEW_DB_schema.sql=MySQL > diff.sql
```

`sql-diff` `古いDBのバックアップファイル=MySQL` `新しいDBのバックアップファイル=MySQL`
のようにコマンド引数を指定します。
コマンド引数の`MySQL`の部分はSQLのパーサとなります。使えるパーサの一覧は`qlit-diff -l`で確認が出来ます。

### 3. DDLを実行し、OLD_DBをNEW_DBのテーブル構造に合わせる

```
$ mysql -hOLD_DB -uUSER DB_NAME -p < diff.sql
```

以上の作業で2つのデータベースのテーブルの構造は一致します。

---

## 実験してみる

### 2つのデータベースにusersテーブルを作成

```mysql:
-- TEST1 新しい構造のテーブル
CREATE DATABASE `TEST1` DEFAULT CHARACTER SET = `utf8`;
USE `TEST1`;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- TEST2 古い構造のテーブル
CREATE DATABASE `TEST2` DEFAULT CHARACTER SET = `utf8`;
USE `TEST2`;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `address` varchar(100) NOT NULL DEFAULT '',
  `note` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

### 2つのデータベースのスキーマをバックアップ

```bash:
$ mysqldump -hHOST -uUSER TEST1 -d -p > TEST1_schema.sql
$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_schema.sql
```

### 差分DDLを作成

```bash:
$ sqlt-diff TEST2_schema.sql=MySQL TEST1_schema.sql=MySQL > diff.sql
```

diff.sqlの中身を確認

```bash:diff.sql
$ more diff.sql 
-- Convert schema 'TEST2_schema.sql' to 'TEST1_schema.sql':;

BEGIN;

ALTER TABLE users DROP COLUMN name,
                  ADD COLUMN first_name varchar(10) NOT NULL DEFAULT '',
                  ADD COLUMN last_name varchar(10) NOT NULL DEFAULT '',
                  CHANGE COLUMN address address varchar(200) NOT NULL DEFAULT '',
                  CHANGE COLUMN note note varchar(100) NULL DEFAULT '';


COMMIT;
```

良い感じ。

### 2つのデータベースのテーブル構造を一致させる（TEST2 ← TEST1)

```bash:
$ mysql -hHOST -uUSER TEST2 -p < diff.sql
```

### テーブル構造が一致しているか確認

```
$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_new_schema.sql
$ diff TEST1_schema.sql TEST2_new_schema.sql
3c3
< -- Host: XXXXX    Database: TEST1
---
> -- Host: XXXXX    Database: TEST2
27,28d26
<   `first_name` varchar(10) NOT NULL DEFAULT '',
<   `last_name` varchar(10) NOT NULL DEFAULT '',
30a29,30
>   `first_name` varchar(10) NOT NULL DEFAULT '',
>   `last_name` varchar(10) NOT NULL DEFAULT '',
44c44
< -- Dump completed on 2016-02-25 XX:XX:XX
---
> -- Dump completed on 2016-02-25 XX:XX:XX
```

**あれ？**

どうやら、カラムの並び順までは考慮されていないようです。

```
$ more TEST2_new_schema.sql 
~~(省略)~~
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
~~(省略)~~
```

気になる場合は、ALTER TABLEでカラム並び順を変更しましょう。（手作業でALTER TABLE文を作成）

```カラムの並び順を変える
USE `TEST2`;
ALTER TABLE `users` MODIFY COLUMN `first_name` VARCHAR(10) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `users` MODIFY COLUMN `last_name` VARCHAR(10) NOT NULL DEFAULT '' AFTER `first_name`;
```

### 再度確認

```
$ mysqldump -hHOST -uUSER TEST2 -d -p > TEST2_new_schema.sql

$ more TEST2_new_schema.sql 
~~(省略)~~
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(10) NOT NULL DEFAULT '',
  `last_name` varchar(10) NOT NULL DEFAULT '',
  `address` varchar(200) NOT NULL DEFAULT '',
  `note` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
~~(省略)~~

$ diff TEST1_schema.sql TEST2_new_schema.sql
3c3
< -- Host: XXXXX    Database: TEST1
---
> -- Host: XXXXX    Database: TEST2
44c44
< -- Dump completed on 2016-02-25 XX:XX:XX
---
> -- Dump completed on 2016-02-25 XX:XX:XX
```

テーブル構造が完全に一致しました。

以上です。