読者です 読者をやめる 読者になる 読者になる

Database Migration Pluginがすごい ~ロールバック編~

前回の続き

Database Migration Pluginのすごいところはロールバックが出来る事。ロールバックを行うコマンドは主に3つ種類がある。

  • 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のタグまでロールバックしていることがわかる。

ロールバックはこんなもんで。