AdSense

網頁

2022/5/24

Liquibase sql-format rollback

Liquibase rollback回滾資料庫異動到tag的紀錄。


Rollback是將資料庫狀態回復到指定tag紀錄的狀態。

範例環境:

  • macOS BigSur version 10.15.5
  • Liquibase 4.10.0


事前要求

參考「Liquibase tag資料庫狀態」標記tag。


Tag

H2 Shell查詢DATABASECHANGELOG資料表最後一筆紀錄有tag v1。

sql> select * from databasechangelog;
ID | AUTHOR   | FILENAME      | DATEEXECUTED               | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID
1  | employee | changelog.sql | 2022-05-24 16:43:01.221643 | 1             | EXECUTED | 8:28bb5eee313e523c6f750d4473e7bb0b | sql         |          | null | 4.10.0    | null     | null   | 3381780263
2  | employee | changelog.sql | 2022-05-24 16:51:02.397504 | 2             | EXECUTED | 8:b1bb25282c1a42ee4b411b34e605247f | sql         |          | null | 4.10.0    | null     | null   | 3382261332
3  | employee | changelog.sql | 2022-05-24 16:51:02.403449 | 3             | EXECUTED | 8:a0a2e29df3bb3d319f3f3c7d8dd907bd | sql         |          | v1   | 4.10.0    | null     | null   | 3382261332
(3 rows, 56 ms)


Changelogs

changelog.sql最下面加上一筆changeset來新增資料,並設定rollback SQL作為執行回滾時的script(反向操作),因為SQL format的changelog無法自動產生回滾SQL。

--changeset employee:4
INSERT INTO employee (name, age) VALUES ('Tony', '42');
--rollback DELETE FROM employee WHERE name = 'Tony';

修改後的changelog.sql

changelog.sql

--liquibase formatted sql

--changeset employee:1
CREATE TABLE employee (
    id int PRIMARY KEY auto_increment NOT NULL,
    name varchar(50) UNIQUE NOT NULL,
    age int,
    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

--changeset employee:2
INSERT INTO employee (name, age) VALUES ('John', '33');

--changeset employee:3
INSERT INTO employee (name, age) VALUES ('Mary', '28');

--changeset employee:4
INSERT INTO employee (name, age) VALUES ('Tony', '42');
--rollback DELETE FROM employee WHERE name = 'Tony';


新增資料

liquibase.properties目錄命令列輸入liquibase update執行changelog的script更新資料庫狀態。

在H2 Shell輸入SELECT * FROM employee可看到新增的一筆資料。

sql> SELECT * FROM employee;
ID | NAME | AGE | CREATE_TIME
1  | John | 33  | 2022-05-24 16:51:02.361003
2  | Mary | 28  | 2022-05-24 16:51:02.401038
3  | Tony | 42  | 2022-05-24 21:58:43.72119
(3 rows, 3 ms)

查詢DATABASECHANGELOG可看到多了一筆異動紀錄。

sql> SELECT * FROM databasechangelog;
ID | AUTHOR   | FILENAME      | DATEEXECUTED               | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID
1  | employee | changelog.sql | 2022-05-24 16:43:01.221643 | 1             | EXECUTED | 8:28bb5eee313e523c6f750d4473e7bb0b | sql         |          | null | 4.10.0    | null     | null   | 3381780263
2  | employee | changelog.sql | 2022-05-24 16:51:02.397504 | 2             | EXECUTED | 8:b1bb25282c1a42ee4b411b34e605247f | sql         |          | null | 4.10.0    | null     | null   | 3382261332
3  | employee | changelog.sql | 2022-05-24 16:51:02.403449 | 3             | EXECUTED | 8:a0a2e29df3bb3d319f3f3c7d8dd907bd | sql         |          | v1   | 4.10.0    | null     | null   | 3382261332
4  | employee | changelog.sql | 2022-05-24 21:58:43.754635 | 4             | EXECUTED | 8:149b431c32ee741dc7cdff83164a1f87 | sql         |          | null | 4.10.0    | null     | null   | 3400722342
(4 rows, 5 ms)


Rollback

liquibase.properties目錄命令列輸入liquibase rollback v1將資料庫狀態回復到tag v1的狀態。

$ liquibase rollback v1
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 22:04:41 (version 4.10.0 #2501 built at 2022-05-04 14:27+0000)
Liquibase Version: 4.10.0
Liquibase Community 4.10.0 by Liquibase
Do you want to see this operation's report in Liquibase Hub, which improves team collaboration?
If so, enter your email. If not, enter [N] to no longer be prompted, or [S] to skip for now, but ask again next time [S]:
S
Skipping auto-registration
Rolling Back Changeset: changelog.sql::4::employee
Liquibase command 'rollback' was executed successfully

查詢EMPLOYEE資料表可看到--changeset employee:4新增的資料已消失,回復到tag v1紀錄的狀態。

sql> SELECT * FROM employee;
ID | NAME | AGE | CREATE_TIME
1  | John | 33  | 2022-05-24 16:51:02.361003
2  | Mary | 28  | 2022-05-24 16:51:02.401038
(2 rows, 3 ms)

查詢DATABASECHANGELOG的紀錄回復到tag v1為最後一筆。

sql> SELECT * FROM databasechangelog;
ID | AUTHOR   | FILENAME      | DATEEXECUTED               | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID
1  | employee | changelog.sql | 2022-05-24 16:43:01.221643 | 1             | EXECUTED | 8:28bb5eee313e523c6f750d4473e7bb0b | sql         |          | null | 4.10.0    | null     | null   | 3381780263
2  | employee | changelog.sql | 2022-05-24 16:51:02.397504 | 2             | EXECUTED | 8:b1bb25282c1a42ee4b411b34e605247f | sql         |          | null | 4.10.0    | null     | null   | 3382261332
3  | employee | changelog.sql | 2022-05-24 16:51:02.403449 | 3             | EXECUTED | 8:a0a2e29df3bb3d319f3f3c7d8dd907bd | sql         |          | v1   | 4.10.0    | null     | null   | 3382261332
(3 rows, 2 ms)


未回滾

再次執行liquibase update重新新增資料。

查詢EMPLOYEE可看到再次新增的資料但ID為4而非3,因為ID值是auto_increment自動遞增,但rollback SQL並沒有重置此設定。

sql> SELECT * FROM employee;
ID | NAME | AGE | CREATE_TIME
1  | John | 33  | 2022-05-24 16:51:02.361003
2  | Mary | 28  | 2022-05-24 16:51:02.401038
4  | Tony | 42  | 2022-05-24 22:19:21.21548
(3 rows, 4 ms)

沒有留言:

AdSense