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)
沒有留言:
張貼留言