既存のテーブル用のマイグレーションファイルをSequelで作成する

今まできちんとテーブル構造の更新管理をしていなかったとして...
=======================================

というかこういうのもう201X年代には起き得ないと思うのだけど、
なんかまあつまらんし書いてみる。

sqlファイルを生でmysql database < hoge.sql みたいな形で食わせることを
続けていて、マイグレーションをマトモにできていないのでは？と気がついたとき、
その時、[Sequel](http://sequel.rubyforge.org/)を使えば救われる。

## Sequelを使う準備

環境を汚したくないのでbundlerを使う。
今回利用するRDBMSはmysqlなので、mysql2アダプタもインストールする。

```sh
mkdir -p ~/Sandbox/Sequel
touch Gemfile
vi Gemfile
```

```ruby:Gemfile
source 'https://rubygems.org/'

gem 'sequel'
gem 'mysql2'
```

```sh
bundle install --path vendor/bundle
```

以上で準備完了。

#マイグレーションファイルの生成

以下のRDBMS、DB、接続先などを仮定する。
(そのためのテーブルも作ってみた)

|項目|内容|
|:---|:---|
|RDBMS|MySQL|
|ホスト|localhost|
|対象DB|sequel_test|
|接続可能なユーザー名|sequel_test|
|設定してるパスワード|sequel_pass|

以上の条件で、sequelにてマイグレーション用のスクリプトを作ってもらうには、
以下の形でコマンドにschemeの指定をする。

```sh
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 ファイルに書きだされている内容は以下となる。(ちょっと長い)

```ruby
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に書き換えておく。理由は後述する。


```ruby: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
```


```ruby: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
```

```ruby: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
```

```ruby: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のテーブルをすべて削除して
実行しよう。

```sql
> drop database sequel_test
> create database sequel_test
```

```sh
mkdir -p migrate
mv *.rb migrate

bundle exec sequel -m migrate "mysql2://sequel_test:sequel_pass@localhost/sequel_test" -E 
```

-E はログをstdoutに出力させるオプション。
コマンドを実行すれば、実行結果がターミナルに表示される。

```sh
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とすれば良い。

```sh
bundle exec sequel -m migrate "mysql2://sequel_test:sequel_pass@localhost/sequel_test" -l sql.log
```

マイグレーションした結果を都度ファイルに吐き出し、管理すると良いかもしれない。
マイグレーションの最終状態(Version)はschema_infoテーブルのversionカラムで管理されている。

![schema_info](https://dl.dropboxusercontent.com/u/614755/qiita/sequel-test/screen-01.png)

# 既存環境に対して対応したいがどうすれば

schema_infoテーブルを作成して管理すれば大丈夫。

## schema_infoテーブルのversionカラムをいじってみる

先ほどの例の場合は、Migrate version 4 まで実行されている状態なので、
新たに以下のマイグレーションファイルを作成する。
userテーブルにcountryカラムを追加するALTER文を実行する
マイグレーションファイルを作成しよう。

```ruby:005_alter_user.rb
Sequel.migration do
  transaction
  up do
    add_column(:user, :country, String)
  end
end
```

マイグレーションファイルを作成したら、
DBにあるschema_infoテーブルのversionを5にしておく。

```sql:schema_info
UPDATE schema_info SET version = 5;
```

この状態でマイグレーションを実行してみよう。


```sh
> 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に戻そう。


```sql:schema_info
UPDATE schema_info SET version = 4;
```

これでマイグレーションを実行すれば、005_alter_user.rbに書いた
マイグレーションの内容が実行される。

### 既存の環境にマイグレーションファイルを使った管理方法を導入する場合は

以下の流れになる


1. sequel -d でDBの内容をマイグレーションファイルとして出力させる
2. (1)で作成されたマイグレーションファイルを適当に分割する
3. schema_infoテーブルを作成する
4. schema_infoテーブルのversionカラムを(2)で分割したファイル数分進めておく
5. 以降は問題なくマイグレーションできる。

schema_infoのテーブル構造は以下。

```sql
CREATE TABLE `schema_info` (
  `version` int(11) NOT NULL DEFAULT '0'
);
```

今後は、[Sequel documentのmigrationの項](http://sequel.rubyforge.org/rdoc/files/doc/migration_rdoc.html)にある便利メソッドを利用してテーブル定義を作成・変更していくことで、
マイグレーション管理が気楽にできるようになる。

# 接続先の管理をする

接続先の指定を毎度コマンド実行時に引数として渡す、
というのはだるいのでYAMLファイルに書いて以下のように使おう。

設定するオプションについてはこちらにある[General connection options](http://sequel.rubyforge.org/rdoc/files/doc/opening_databases_rdoc.html)に載っている。

```yaml
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として保存して、

```sh
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する形になっている。
心に良くないので以下のような感じに書き直している。

```ruby
down do
   drop_table :users unless Padrino.env == :production
end
```