今まできちんとテーブル構造の更新管理をしていなかったとして...
というかこういうのもう201X年代には起き得ないと思うのだけど、
なんかまあつまらんし書いてみる。
sqlファイルを生でmysql database < hoge.sql みたいな形で食わせることを
続けていて、マイグレーションをマトモにできていないのでは？と気がついたとき、
その時、Sequelを使えば救われる。
Sequelを使う準備
環境を汚したくないのでbundlerを使う。
今回利用するRDBMSはmysqlなので、mysql2アダプタもインストールする。
mkdir -p ~/Sandbox/Sequel
touch Gemfile
vi Gemfile
Gemfile
source 'https://rubygems.org/'
gem 'sequel'
gem 'mysql2'
bundle install --path vendor/bundle
以上で準備完了。
マイグレーションファイルの生成
以下のRDBMS、DB、接続先などを仮定する。
(そのためのテーブルも作ってみた)
| 項目 | 内容 |
|---|---|
| RDBMS | MySQL |
| ホスト | localhost |
| 対象DB | sequel_test |
| 接続可能なユーザー名 | sequel_test |
| 設定してるパスワード | sequel_pass |
以上の条件で、sequelにてマイグレーション用のスクリプトを作ってもらうには、
以下の形でコマンドにschemeの指定をする。
bundle exec sequel -d "mysql2://sequel_test:sequel_pass@localhost/sequel_test" > 001_create_table.rb
001_create_table.rbには、既存テーブルすべてのCREATE TABLEの情報が書き込まれている。
ちなみに用意してるテーブルは以下。
| Tables_in_sequel_test |
|---|
| favorite_programming_language |
| programming_language |
| user |
| user_status |
そして 001_create_table.rb ファイルに書きだされている内容は以下となる。(ちょっと長い)
Sequel.migration do
change do
create_table(:programming_language) do
primary_key :id
String :name, :text=>true, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
end
create_table(:user) do
primary_key :id
String :name, :text=>true, :null=>false
String :mail, :text=>true, :null=>false
Bignum :age, :null=>false
String :sex, :size=>3, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
check Sequel::SQL::BooleanExpression.new(:>=, Sequel::SQL::Identifier.new(:age), 0)
end
create_table(:user_status) do
primary_key :id
String :single_word, :text=>true, :null=>false
String :icon_path, :text=>true, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
end
create_table(:favorite_programming_language, :ignore_index_errors=>true) do
primary_key :id
foreign_key :user_id, :user, :null=>false, :key=>[:id]
foreign_key :programming_language_id, :programming_language, :null=>false, :key=>[:id]
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
index [:programming_language_id], :name=>:programming_language_id
index [:user_id], :name=>:user_id
end
end
end
マイグレーション用に利用できるよう整形・分割する
…CREATE TABLEについてなるべくファイル名=テーブル名という形で対応させとかないと
テンパったときどこにテーブルの作成情報が…?
みたいなことになるので適当な形にファイルを分割する。
| ファイル名 |
|---|
| 001_create_user.rb |
| 002_create_user_status.rb |
| 003_programming_language.rb |
| 004_favorite_programming_language.rb |
Sequel.migration ブロック内のcreate_table(:hoge~)を分割すればよい。
各ファイルへの分割のついでにtransactionを利用する形にしたいので、changeブロックの前に
transaction methodを書いておく。
change methodについてもupに書き換えておく。理由は後述する。
001_create_user.rb
Sequel.migration do
transaction
up do
create_table(:user) do
primary_key :id
String :name, :text=>true, :null=>false
String :mail, :text=>true, :null=>false
Bignum :age, :null=>false
String :sex, :size=>3, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
check Sequel::SQL::BooleanExpression.new(:>=, Sequel::SQL::Identifier.new(:age), 0)
end
end
end
002_create_user_status.rb
Sequel.migration do
transaction
up do
create_table(:user_status) do
primary_key :id
String :single_word, :text=>true, :null=>false
String :icon_path, :text=>true, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
end
end
end
003_programming_language.rb
Sequel.migration do
transaction
up do
create_table(:programming_language) do
primary_key :id
String :name, :text=>true, :null=>false
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
end
end
end
004_favorite_programming_language.rb
Sequel.migration do
transaction
up do
create_table(:favorite_programming_language, :ignore_index_errors=>true) do
primary_key :id
foreign_key :user_id, :user, :null=>false, :key=>[:id]
foreign_key :programming_language_id, :programming_language, :null=>false, :key=>[:id]
DateTime :created_at, :null=>false
DateTime :updated_at, :null=>false
index [:programming_language_id], :name=>:programming_language_id
index [:user_id], :name=>:user_id
end
end
end
これでマイグレーションファイルの分割は完了。
試しにマイグレーションさせてみよう
各マイグレーションファイルをmigrateディレクトリ以下に移動させ、
またsequel_test databaseのテーブルをすべて削除して
実行しよう。
> drop database sequel_test
> create database sequel_test
mkdir -p migrate
mv *.rb migrate
bundle exec sequel -m migrate "mysql2://sequel_test:sequel_pass@localhost/sequel_test" -E
-E はログをstdoutに出力させるオプション。
コマンドを実行すれば、実行結果がターミナルに表示される。
I, [2013-05-07T17:55:56.628975 #38795] INFO -- : (0.000082s) SET @@wait_timeout = 2147483
I, [2013-05-07T17:55:56.629120 #38795] INFO -- : (0.000058s) SET SQL_AUTO_IS_NULL=0
E, [2013-05-07T17:55:56.633328 #38795] ERROR -- : Mysql2::Error: Table 'sequel_test.schema_info' doesn't exist: SELECT NULL FROM `schema_info` LIMIT 1
I, [2013-05-07T17:55:56.663415 #38795] INFO -- : (0.029742s) CREATE TABLE `schema_info` (`version` integer NOT NULL DEFAULT 0)
I, [2013-05-07T17:55:56.664203 #38795] INFO -- : (0.000552s) SELECT 1 AS `one` FROM `schema_info` LIMIT 1
I, [2013-05-07T17:55:56.664787 #38795] INFO -- : (0.000309s) INSERT INTO `schema_info` (`version`) VALUES (0)
I, [2013-05-07T17:55:56.665311 #38795] INFO -- : (0.000205s) SELECT COUNT(*) AS `count` FROM `schema_info` LIMIT 1
I, [2013-05-07T17:55:56.666038 #38795] INFO -- : (0.000404s) SELECT `version` FROM `schema_info` LIMIT 1
I, [2013-05-07T17:55:56.668580 #38795] INFO -- : Begin applying migration version 1, direction: up
I, [2013-05-07T17:55:56.668800 #38795] INFO -- : (0.000092s) BEGIN
I, [2013-05-07T17:55:56.691327 #38795] INFO -- : (0.022101s) CREATE TABLE `user` (`id` integer PRIMARY KEY AUTO_INCREMENT, `name` text NOT NULL, `mail` text NOT NULL, `age` bigint NOT NULL, `sex` varchar(3) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, CHECK (`age` >= 0))
I, [2013-05-07T17:55:56.692072 #38795] INFO -- : (0.000490s) UPDATE `schema_info` SET `version` = 1
I, [2013-05-07T17:55:56.692285 #38795] INFO -- : (0.000104s) COMMIT
I, [2013-05-07T17:55:56.692380 #38795] INFO -- : Finished applying migration version 1, direction: up, took 0.023792 seconds
I, [2013-05-07T17:55:56.692430 #38795] INFO -- : Begin applying migration version 2, direction: up
I, [2013-05-07T17:55:56.692590 #38795] INFO -- : (0.000067s) BEGIN
I, [2013-05-07T17:55:56.707656 #38795] INFO -- : (0.014621s) CREATE TABLE `user_status` (`id` integer PRIMARY KEY AUTO_INCREMENT, `single_word` text NOT NULL, `icon_path` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL)
I, [2013-05-07T17:55:56.708648 #38795] INFO -- : (0.000715s) UPDATE `schema_info` SET `version` = 2
I, [2013-05-07T17:55:56.708839 #38795] INFO -- : (0.000089s) COMMIT
I, [2013-05-07T17:55:56.708934 #38795] INFO -- : Finished applying migration version 2, direction: up, took 0.016488 seconds
I, [2013-05-07T17:55:56.708986 #38795] INFO -- : Begin applying migration version 3, direction: up
I, [2013-05-07T17:55:56.709154 #38795] INFO -- : (0.000069s) BEGIN
I, [2013-05-07T17:55:56.730464 #38795] INFO -- : (0.020901s) CREATE TABLE `programming_language` (`id` integer PRIMARY KEY AUTO_INCREMENT, `name` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL)
I, [2013-05-07T17:55:56.731162 #38795] INFO -- : (0.000446s) UPDATE `schema_info` SET `version` = 3
I, [2013-05-07T17:55:56.731317 #38795] INFO -- : (0.000068s) COMMIT
I, [2013-05-07T17:55:56.731417 #38795] INFO -- : Finished applying migration version 3, direction: up, took 0.022422 seconds
I, [2013-05-07T17:55:56.731472 #38795] INFO -- : Begin applying migration version 4, direction: up
I, [2013-05-07T17:55:56.731642 #38795] INFO -- : (0.000078s) BEGIN
I, [2013-05-07T17:55:56.733453 #38795] INFO -- : (0.001409s) DESCRIBE `user`
I, [2013-05-07T17:55:56.735134 #38795] INFO -- : (0.001076s) DESCRIBE `programming_language`
I, [2013-05-07T17:55:56.753417 #38795] INFO -- : (0.017887s) CREATE TABLE `favorite_programming_language` (`id` integer PRIMARY KEY AUTO_INCREMENT, `user_id` integer NOT NULL, `programming_language_id` integer NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, FOREIGN KEY (`user_id`) REFERENCES `user`(`id`), FOREIGN KEY (`programming_language_id`) REFERENCES `programming_language`(`id`))
I, [2013-05-07T17:55:56.770097 #38795] INFO -- : (0.016466s) CREATE INDEX `programming_language_id` ON `favorite_programming_language` (`programming_language_id`)
I, [2013-05-07T17:55:56.791652 #38795] INFO -- : (0.021293s) CREATE INDEX `user_id` ON `favorite_programming_language` (`user_id`)
I, [2013-05-07T17:55:56.792281 #38795] INFO -- : (0.000385s) UPDATE `schema_info` SET `version` = 4
I, [2013-05-07T17:55:56.792411 #38795] INFO -- : (0.000061s) COMMIT
I, [2013-05-07T17:55:56.792528 #38795] INFO -- : Finished applying migration version 4, direction: up, took 0.061013 seconds
もしログをファイルに吐き出したいなら、コマンドオプションを-l filenameとすれば良い。
bundle exec sequel -m migrate "mysql2://sequel_test:sequel_pass@localhost/sequel_test" -l sql.log
マイグレーションした結果を都度ファイルに吐き出し、管理すると良いかもしれない。
マイグレーションの最終状態(Version)はschema_infoテーブルのversionカラムで管理されている。
既存環境に対して対応したいがどうすれば
schema_infoテーブルを作成して管理すれば大丈夫。
schema_infoテーブルのversionカラムをいじってみる
先ほどの例の場合は、Migrate version 4 まで実行されている状態なので、
新たに以下のマイグレーションファイルを作成する。
userテーブルにcountryカラムを追加するALTER文を実行する
マイグレーションファイルを作成しよう。
005_alter_user.rb
Sequel.migration do
transaction
up do
add_column(:user, :country, String)
end
end
マイグレーションファイルを作成したら、
DBにあるschema_infoテーブルのversionを5にしておく。
schema_info
UPDATE schema_info SET version = 5;
この状態でマイグレーションを実行してみよう。
> bundle exec sequel -m migrate "mysql2://sequel_test:sequel_pass@localhost/sequel_test" -E
I, [2013-05-07T19:47:01.493942 #39351] INFO -- : (0.000112s) SET @@wait_timeout = 2147483
I, [2013-05-07T19:47:01.494106 #39351] INFO -- : (0.000066s) SET SQL_AUTO_IS_NULL=0
I, [2013-05-07T19:47:01.497190 #39351] INFO -- : (0.000315s) SELECT NULL FROM `schema_info` LIMIT 1
I, [2013-05-07T19:47:01.497499 #39351] INFO -- : (0.000143s) SELECT * FROM `schema_info` LIMIT 1
I, [2013-05-07T19:47:01.497834 #39351] INFO -- : (0.000137s) SELECT 1 AS `one` FROM `schema_info` LIMIT 1
I, [2013-05-07T19:47:01.498142 #39351] INFO -- : (0.000120s) SELECT COUNT(*) AS `count` FROM `schema_info` LIMIT 1
I, [2013-05-07T19:47:01.498404 #39351] INFO -- : (0.000136s) SELECT `version` FROM `schema_info` LIMIT 1
マイグレーションは実行されない。
既にマイグレーションバージョン 5 を実行したことになっているからだ。
ので、schema_infoのversionカラムに設定した値を4に戻そう。
schema_info
UPDATE schema_info SET version = 4;
これでマイグレーションを実行すれば、005_alter_user.rbに書いた
マイグレーションの内容が実行される。
既存の環境にマイグレーションファイルを使った管理方法を導入する場合は
以下の流れになる
- sequel -d でDBの内容をマイグレーションファイルとして出力させる
- (1)で作成されたマイグレーションファイルを適当に分割する
- schema_infoテーブルを作成する
- schema_infoテーブルのversionカラムを(2)で分割したファイル数分進めておく
- 以降は問題なくマイグレーションできる。
schema_infoのテーブル構造は以下。
CREATE TABLE `schema_info` (
`version` int(11) NOT NULL DEFAULT '0'
);
今後は、Sequel documentのmigrationの項にある便利メソッドを利用してテーブル定義を作成・変更していくことで、
マイグレーション管理が気楽にできるようになる。
接続先の管理をする
接続先の指定を毎度コマンド実行時に引数として渡す、
というのはだるいのでYAMLファイルに書いて以下のように使おう。
設定するオプションについてはこちらにあるGeneral connection optionsに載っている。
development:
adapter: "mysql2"
host: "localhost"
user: "sequel_test"
password: "sequel_pass"
database: "sequel_test"
production:
adapter: "mysql2"
host: "example.com"
user: "sequel_test"
password: "sequel_pass"
database: "sequel_production"
こういう形でYAMLファイルを定義して、
config/database.ymlとして保存して、
bundle exec sequel -e development config/database.yaml -m migrate -E
で良い。-eが環境を示していて、最初のHashのキーを指すようになってるので、
環境別の設定が書ける。
他
change method -> up methodとする理由
change methodを定義した形でsequelはマイグレーションファイルを吐き出すのだけど、
upとdown methodを利用したほうが良いと思う。
というのも-M 0などとマイグレーションバージョンを指定して
downgradeした場合、change methodを利用していた場合はdrop tableしてしまうため。
up methodに書いていけばdowngrade時にschema_infoだけ更新される。
Padrinoでのscaffold...
Padrino + Sequelな構成の時、scaffoldを使ったModel作成時に
作成されるmigrationスクリプト(db/migrate以下)がdownメソッドのブロック内にて
必ずテーブルをdropする形になっている。
心に良くないので以下のような感じに書き直している。
down do
drop_table :users unless Padrino.env == :production
end