Database Migration Pluginがすごい ~ロールバック編~
Database Migration Pluginのすごいところはロールバックが出来る事。ロールバックを行うコマンドは主に３つ種類がある。
- dbm-rollback-count
- dbm-rollback-to-date
- dbm-rollback
それぞれ個別に見ていく。
dbm-rollback-count
dbm-rollback-countは指定した数分の変更履歴をロールバックするコマンド。例えばdatabasechangelogの状態が以下のようになっていたとする。
devDb=> select * from databasechangelog; id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase -----------------+---------------------+-------------------+-------------------------------+---------------+----------+------------------------------------+----------------------------+----------+-----+----------- 1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5 1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | | 2.0.5 1351333217527-1 | yamkazu (generated) | add-author.groovy | 2012-10-27 19:27:06.174495+09 | 3 | EXECUTED | 3:500d2782c8ddcc7d7b89b0b29b2d7342 | Create Table | | | 2.0.5 1351333217527-2 | yamkazu (generated) | add-author.groovy | 2012-10-27 19:27:06.190441+09 | 4 | EXECUTED | 3:d4dcaaa9285f777d2a05f33e275488b7 | Add Column | | | 2.0.5 1351333217527-3 | yamkazu (generated) | add-author.groovy | 2012-10-27 19:27:06.200821+09 | 5 | EXECUTED | 3:77e6c768bd70db135e30edfb2ef6788e | Add Foreign Key Constraint | | | 2.0.5
ここでbookだけがあった状態に戻したいといった場合、最新の3つのchangesetを打ち消す必要がある。ここでdbm-rollback-count-sqlというコマンドを実行してみる。
これはロールバック系のコマンドだけでなく他のコマンドにも用意されて、xxx-sqlの形になっている。これを使用すると、xxxのコマンドを実行した時に実際にどんなSQLが実行されるのか確認出来る。また、grailsコマンドが使用できないような環境にシステムがあるばあはこのSQLを持って行く事もできる。
grails> dbm-rollback-count-sql 3 | Starting dbm-rollback-count-sql for database test @ jdbc:postgresql://localhost:5432/devDb -- ********************************************************************* -- Rollback 3 Change(s) Script -- ********************************************************************* -- Change Log: changelog.groovy -- Ran at: 12/10/28 17:09 -- Against: test@jdbc:postgresql://localhost:5432/devDb -- Liquibase version: 2.0.5 -- ********************************************************************* -- Lock Database -- Rolling Back ChangeSet: add-author.groovy::1351333217527-3::yamkazu (generated)::(Checksum: 3:77e6c768bd70db135e30edfb2ef6788e) ALTER TABLE book DROP CONSTRAINT FK2E3AE9B2F6003C; DELETE FROM databasechangelog WHERE ID='1351333217527-3' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-2::yamkazu (generated)::(Checksum: 3:d4dcaaa9285f777d2a05f33e275488b7) ALTER TABLE book DROP COLUMN author_id; DELETE FROM databasechangelog WHERE ID='1351333217527-2' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-1::yamkazu (generated)::(Checksum: 3:500d2782c8ddcc7d7b89b0b29b2d7342) DROP TABLE author; DELETE FROM databasechangelog WHERE ID='1351333217527-1' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; | Finished dbm-rollback-count-sql
changesetをぞれぞれ評価していって、そのchangesetの変更を取り返すSQLが出力さているのがわかる。問題無さそうなので実際に実行。
grails> dbm-rollback-count 3 | Finished dbm-rollback-count
DBを確認してみる。
devDb=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+-------
public | book | table | test
public | databasechangelog | table | test
public | databasechangeloglock | table | test
public | hibernate_sequence | sequence | test
(4 rows)
devDb=> select * from databasechangelog;
id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase
-----------------+---------------------+-----------------+-------------------------------+---------------+----------+------------------------------------+-----------------+----------+-----+-----------
1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5
1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | | 2.0.5
(2 rows)
ちゃんとロールバックされている。
dbm-rollback-to-date
次はdbm-rollback-to-dateで指定した時間までロールバックするというもの。DBの状態が以下のようになっていたとする。dateexecutedの日時に対して判断される。
devDb=> select * from databasechangelog; id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase -----------------+---------------------+-------------------+-------------------------------+---------------+----------+------------------------------------+----------------------------+----------+-----+----------- 1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5 1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | | 2.0.5 1351333217527-1 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:17:07.623519+09 | 3 | EXECUTED | 3:500d2782c8ddcc7d7b89b0b29b2d7342 | Create Table | | | 2.0.5 1351333217527-2 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:17:07.639352+09 | 4 | EXECUTED | 3:d4dcaaa9285f777d2a05f33e275488b7 | Add Column | | | 2.0.5 1351333217527-3 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:17:07.648655+09 | 5 | EXECUTED | 3:77e6c768bd70db135e30edfb2ef6788e | Add Foreign Key Constraint | | | 2.0.5
ここでauthorが追加される前の状態まで戻りたいとする。日付と時刻はそれぞれ、yyyy-MM-dd、 HH:mm:ssで指定する。時刻はオプションで指定しなかった場合は00:00:00が指定されたものと同じとなる。
先ほどと同じようにdbm-rollback-to-date-sqlで事前確認
grails> dbm-rollback-to-date-sql 2012-10-28 Starting dbm-rollback-to-date-sql for database test @ jdbc:postgresql://localhost:5432/devDb-- *********************************************************************-- Rollback to Sun Oct 28 00:00:00 JST 2012 Script -- ********************************************************************* -- Change Log: changelog.groovy -- Ran at: 12/10/28 17:24-- Against: test@jdbc:postgresql://localhost:5432/devDb-- Liquibase version: 2.0.5-- *********************************************************************-- Lock Database-- Rolling Back ChangeSet: add-author.groovy::1351333217527-3::yamkazu (generated)::(Checksum: 3:77e6c768bd70db135e30edfb2ef6788e) ALTER TABLE book DROP CONSTRAINT FK2E3AE9B2F6003C; DELETE FROM databasechangelog WHERE ID='1351333217527-3' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-2::yamkazu (generated)::(Checksum: 3:d4dcaaa9285f777d2a05f33e275488b7) ALTER TABLE book DROP COLUMN author_id; DELETE FROM databasechangelog WHERE ID='1351333217527-2' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-1::yamkazu (generated)::(Checksum: 3:500d2782c8ddcc7d7b89b0b29b2d7342) DROP TABLE author; DELETE FROM databasechangelog WHERE ID='1351333217527-1' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; | Finished dbm-rollback-to-date-sql
問題無さそう。では実行。
grails> dbm-rollback-to-date 2012-10-28 | Finished dbm-rollback-to-date
DBを確認。
devDb=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+-------
public | book | table | test
public | databasechangelog | table | test
public | databasechangeloglock | table | test
public | hibernate_sequence | sequence | test
(4 rows)
devDb=> select * from databasechangelog;
id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase
-----------------+---------------------+-----------------+-------------------------------+---------------+----------+------------------------------------+-----------------+----------+-----+-----------
1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5
1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | | 2.0.5
(2 rows)
うまくいっている。
dbm-rollback
最後にdbm-rollback。これは指定したタグまでロールバックするというもの。そもそもタグとは何か。
タグはdbm-tagコマンドで設定できる。現在のDB状態に名前が付けられる。DB状態が以下のようになっていたとする。
devDb=> select * from databasechangelog; id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase -----------------+---------------------+-----------------+-------------------------------+---------------+----------+------------------------------------+-----------------+----------+-----+----------- 1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5 1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | | 2.0.5
この状態でdbm-tagを実行する。現状のDBの状態にv1.0という名前のタグを付けてみる。
grails> dbm-tag v1.0 | Finished dbm-tag
DBを再確認。
devDb=> select * from databasechangelog; id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase -----------------+---------------------+-----------------+-------------------------------+---------------+----------+------------------------------------+-----------------+----------+------+----------- 1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5 1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | v1.0 | 2.0.5 (2 rows)
tagのところにv1.0が入っている。この後DBの変更があって以下になったとする。
devDb=> select * from databasechangelog; id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase -----------------+---------------------+-------------------+-------------------------------+---------------+----------+------------------------------------+----------------------------+----------+------+----------- 1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5 1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | v1.0 | 2.0.5 1351333217527-1 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:42:39.667878+09 | 3 | EXECUTED | 3:500d2782c8ddcc7d7b89b0b29b2d7342 | Create Table | | | 2.0.5 1351333217527-2 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:42:39.686335+09 | 4 | EXECUTED | 3:d4dcaaa9285f777d2a05f33e275488b7 | Add Column | | | 2.0.5 1351333217527-3 | yamkazu (generated) | add-author.groovy | 2012-10-28 17:42:39.699466+09 | 5 | EXECUTED | 3:77e6c768bd70db135e30edfb2ef6788e | Add Foreign Key Constraint | | | 2.0.5
ここでv1.0の状態までロールバックしたいとする。まずはsqlから確認。
grails> dbm-rollback-sql v1.0 | Starting dbm-rollback-sql for database test @ jdbc:postgresql://localhost:5432/devDb -- ********************************************************************* -- Rollback to 'v1.0' Script -- ********************************************************************* -- Change Log: changelog.groovy -- Ran at: 12/10/28 17:43 -- Against: test@jdbc:postgresql://localhost:5432/devDb -- Liquibase version: 2.0.5 -- ********************************************************************* -- Lock Database -- Rolling Back ChangeSet: add-author.groovy::1351333217527-3::yamkazu (generated)::(Checksum: 3:77e6c768bd70db135e30edfb2ef6788e) ALTER TABLE book DROP CONSTRAINT FK2E3AE9B2F6003C; DELETE FROM databasechangelog WHERE ID='1351333217527-3' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-2::yamkazu (generated)::(Checksum: 3:d4dcaaa9285f777d2a05f33e275488b7) ALTER TABLE book DROP COLUMN author_id; DELETE FROM databasechangelog WHERE ID='1351333217527-2' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; -- Rolling Back ChangeSet: add-author.groovy::1351333217527-1::yamkazu (generated)::(Checksum: 3:500d2782c8ddcc7d7b89b0b29b2d7342) DROP TABLE author; DELETE FROM databasechangelog WHERE ID='1351333217527-1' AND AUTHOR='yamkazu (generated)' AND FILENAME='add-author.groovy'; | Finished dbm-rollback-sql
問題無さそうなので実行。
grails> dbm-rollback v1.0 | Finished dbm-rollback
DBの確認。
devDb=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+-------
public | book | table | test
public | databasechangelog | table | test
public | databasechangeloglock | table | test
public | hibernate_sequence | sequence | test
(4 rows)
devDb=> select * from databasechangelog;
id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase
-----------------+---------------------+-----------------+-------------------------------+---------------+----------+------------------------------------+-----------------+----------+------+-----------
1351331869483-1 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.890237+09 | 1 | EXECUTED | 3:378572087c807b0eae512c8e1bac00b7 | Create Table | | | 2.0.5
1351331869483-2 | yamkazu (generated) | add-book.groovy | 2012-10-27 19:07:44.907147+09 | 2 | EXECUTED | 3:1f8c97c0685b3c4f68b4ac2954ed9919 | Create Sequence | | v1.0 | 2.0.5
(2 rows)
v1.0のタグまでロールバックしていることがわかる。
ロールバックはこんなもんで。
