Subscribed unsubscribe Subscribe Subscribe

SQL::Translator::DiffでDBスキーマに追従させる方法

#5「GitDDLまじイノベーティブ」 tech.kayac.com Advent Calendar 2012 | tech.kayac.com - KAYAC engineers' blog
が便利そうだなーと思って。
でもGitと絡めなくても、Webアプリにおいて「現在の環境で使用するデータベース」と「有るべきスキーマの状態を示すDDLファイル」の差分を取って埋めることができればそれだけで十分使える気がする、と思って一つの運用方法を考えてみた。

もちろんGitDDL使っても良いのだけど、SQL::Translatorを使うだけでもある程度は、ということで。

Amon2プロジェクトの例で。

初期設定

$ amon2-setup.pl MyApp

とかで雛形プロジェクトを作ると、sqlディレクトリが作られて、そこにDDLを保存する雰囲気になる。そのままsql/mysql.sqlを使っていくことにする。
config/development.plで例えば

return +{
    'DBI' => [
        'dbi:mysql:dbname=example', 'root', '', +{
            AutoCommit          => 1,
            PrintError          => 0,
            RaiseError          => 1,
            ShowErrorStatement  => 1,
            AutoInactiveDestroy => 1,
            mysql_enable_utf8    => 1,
            mysql_auto_reconnect => 0,
        },
    ],
};

のように設定し、アプリではこの設定を使ってMySQLのexampleデータベースに接続して動くようにする。

テーブル作成

exampleデータベースで例えば次のようにテーブルを作成。

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `item_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL::Translatorを使ってschema dump

上記で作ったDB定義をそのままmysqldump -dしてsql/mysql.sqlに突っ込んでもいいのだけど、敢えてPerlスクリプトからdumpしてみる。
script/dump_schema.plを以下のように作成。

#!/usr/bin/env perl
use strict;
use warnings;

use File::Basename;
use File::Spec;
use lib File::Spec->catdir(dirname(__FILE__), '..', 'lib');
use MyApp;

use DBI;
use SQL::Translator;

# DBIからschema生成
my $translator = do {
    my $config = MyApp->config;
    my $dbh    = DBI->connect(@{ $config->{DBI} });
    SQL::Translator->new(
        parser      => 'DBI',
        parser_args => +{ dbh => $dbh },
    );
};
my $schema = $translator->translate;
# AUTO_INCREMENT情報を削る
for my $table ($schema->get_tables) {
    my @options = $table->options;
    if (my ($idx) = grep { $options[$_]->{AUTO_INCREMENT} } 0..$#options) {
        splice $table->options, $idx, 1;
    }
}
# MySQL向けのSQLを生成
$translator->producer('MySQL');
my $sql = $translator->translate;
print $sql;

# sql/mysql.sql ファイルに書き込み
my $file = File::Spec->catdir(MyApp->base_dir, 'sql', 'mysql.sql');
open my $fh, '>', $file or die $!;
print $fh $sql;
close $fh;

これを実行すると、mysqldumpしたのと同様に以下のようなSQLが吐かれる。

--
-- Created by SQL::Translator::Producer::MySQL
-- Created on Fri Dec 14 15:40:27 2012
--
SET foreign_key_checks=0;

--
-- Table: `item`
--
CREATE TABLE `item` (
  `id` integer(10) unsigned NOT NULL auto_increment,
  `user_id` integer(10) unsigned NOT NULL,
  `name` varchar(255) NULL DEFAULT NULL,
  INDEX `user_id` (`user_id`),
  PRIMARY KEY (`id`),
  CONSTRAINT `item_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

--
-- Table: `user`
--
CREATE TABLE `user` (
  `id` integer(10) unsigned NOT NULL auto_increment,
  `name` char(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

SET foreign_key_checks=1;

スキーマ情報にAUTO_INCREMENTオプション要らないので無理矢理削っている。。
SQL::Translator は、ファイルやDBIなどの入力を受け取ってschemaを保持し、producerを指定することで、そのschemaを様々な形で出力することが出来る。便利。
ここではDBIから読み取ったschemaをMySQLの形式で吐き出しただけ。

スキーマ変更

ここで、共同開発者がDB変更してdumpがpushされたとかでsql/mysql.sqlが以下のように変更されたとする。

CREATE TABLE `hoge` (
  `id` integer(11) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

CREATE TABLE `item` (
  `id` integer(10) unsigned NOT NULL auto_increment,
  `user_id` integer(10) unsigned NOT NULL,
  `name` varchar(255) NULL DEFAULT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  INDEX `user_id` (`user_id`),
  PRIMARY KEY (`id`),
  CONSTRAINT `item_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

CREATE TABLE `user` (
  `id` integer(10) unsigned NOT NULL auto_increment,
  `name` char(16) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

そうすると「DDLファイル」と「実際の手元のDB」でschemaが異なることになる。

SQL::Translator::Diffで差分抽出

GitDDLでも中で使っている、SQL::Translator::Diff。 これが2つのschemaの差異を算出してくれる。
script/diff_schema.plを以下のように作成。

#!/usr/bin/env perl
use strict;
use warnings;

use File::Basename;
use File::Spec;
use lib File::Spec->catdir(dirname(__FILE__), '..', 'lib');
use MyApp;

use DBI;
use SQL::Translator;
use SQL::Translator::Diff;

my $config = MyApp->config;
my $dbh    = DBI->connect(@{ $config->{DBI} });
my $source = do {
    my $schema = SQL::Translator->new(
        parser      => 'DBI',
        parser_args => +{ dbh => $dbh },
    )->translate;
};
my $target = do {
    SQL::Translator->new(
        parser   => 'MySQL',
        filename => File::Spec->catdir(MyApp->base_dir, 'sql', 'mysql.sql'),
    )->translate;
};
# AUTO_INCREMENT情報を削る
for my $schema ($source, $target) {
    for my $table ($schema->get_tables) {
        my @options = $table->options;
        if (my ($idx) = grep { $options[$_]->{AUTO_INCREMENT} } 0..$#options) {
            splice $table->options, $idx, 1;
        }
    }
}

# diffの検出
my $std = SQL::Translator::Diff->new(+{
    output_db     => 'MySQL',
    source_schema => $source,
    target_schema => $target,
    producer_args => +{
        quote_field_names => '`',
    },
});
my $diff = $std->compute_differences->produce_diff_sql;
print $diff;

これで、「今の状態のDB」にどう変更をすれば「schemaファイル」と同じ状態になるか、が求められる。

$ perl script/diff_schema.pl
-- Convert schema '' to '':;

BEGIN;

SET foreign_key_checks=0;

CREATE TABLE `hoge` (
  `id` integer(11) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

SET foreign_key_checks=1;

ALTER TABLE item ADD COLUMN `type` tinyint(1) unsigned NOT NULL;

ALTER TABLE user ADD COLUMN `created_at` datetime NOT NULL;


COMMIT;

ちゃんと差分だけを出してくれる。問題ないようであればこれをそのまま流してやれば良い。オプション付きで自動適用とかにしても良いかもしれない。

use Getopt::Long;

GetOptions(
    'upgrade' => \my $upgrade,
) or die;

...

my $diff = $std->compute_differences->produce_diff_sql;
print $diff;

# DBへの適用
if ($upgrade) {
    for my $statement (split /;/, $diff) {
        next unless $statement =~ /\S+/;
        $dbh->do($statement);
    }
}

これだけで、「現在の状態」と「有るべき状態」を比較して最小限の変更で「有るべき状態」への追従ができる。はず。

まとめ

やっていることはGitDDLとかとたぶん変わらないのだけど、schemaの差分を抽出して適用させる、という操作の部分だけ取り出すとこんな感じになるかと。
スキーマの差分は
https://github.com/onishi/mysqldiff
でも簡単に求められるけど、より細かいところまで差分だしたり、MySQL以外のDBで同じようにやりたいときはSQL::Translator::Diffが使えそうな気がする。

注意

この方法はSQL::Translatorのparserやproducerがどの程度使用しているDB(MySQLならMySQL)の文法をサポートしているか、どの程度信用するか、になるので 頼りきっていると罠にハマる可能性もある。
正しくSQLが生成されているか、正しい差分抽出ができているか、は常に気をつけて使う必要はある。