【Springboot集成Liquibase实现回滚rollback】

Springboot集成Liquibase实现数据库回滚。

Demo Springboot rollback data patch with Liquibase Github Project

此项目只是在Liquibase 4.20.0及以下支持。如果需要在4.20.0以上支持，由于Liqiubase官方重构了rollback方法，我们需要自己实现Liquibase的rollback方法。

Note: this demo only works for liquibase v4.20.0 and below. this is because from Liquibase v4.21.0 and above, Liquibase has refactored the rollback API with different commandScope.

文章的初衷

Reason why I document this

• 在Liquibase官方与Springboot集成的文件中，其只是实现了有限的功能。
• According Liquibase official document, Liquibase provides limited functionalities for Springboot integrates
  Using Liquibase with Spring Boot
  
• 在行业中， “远离数据” 法则越来越受到企业界的认同，同时也是维护系统的安全性，可靠性中发挥不可替代的作用。
• Keep people away from data. is becoming more and more important in our ecosystem. It is one of principle to keep our environment secure and stabled.
• 在企业界日常维护中，我们不可避免地遇到回滚系统应用的版本的时候，与之相对应的，我们也需要回滚相关的配置数据
• In industry, sometime we may need rollback application if any issue detected in production. In the meanwhle we also need to rollback our application data patch made by the particular version.
• 在回滚中，我们需要确保回滚只是回滚了与之相关版本的数据。 也就是要确保操作的幂等性。Liquibase Changset能够很好地确保了更新脚本与回滚脚本的幂等。
• To manage data patch rollback, we have to make sure data consist with previously state after rollback. Liqiubase offers a very well data patching approach which allow us bundle the rollback scripts to each changeset.

Getting Started

数据的向前更新

Data roll forward

在springboot 与Liquibase集成中，每当Springboot启动，如果有任何新的changset, Liquibase都会自动向前更新。
If you are managed to integrates springboot with liquibase, then each time springboot start up, it will auto apply db changesets via liquibase master file: resources/db/changelog/db.changelog-master.xml

在实现Liquibase回滚功能后，在每个数据更新前，我们需要tagDatabase。用以其后需要回滚的操作。

do remeber tag our database before each database changeset.

liquibase db changeset to apply DB tag

<changeSet id="tagdb" author="liangfaan">
    <tagDatabase tag="v2.2.2"/>
</changeSet>

然后我们需要实现我们自己的rollback脚本。

After adding database tag, each liquibase changeset must together with rollback tag except those auto rollback supported by Liquibase Auto rollback tags.

<rollback>
    <delete tableName="item">
        <where>
            house_id=(select id from house where owner='testowner2')
        </where>
    </delete>
    <delete tableName="house">
        <where>
            owner = 'testowner2'
        </where>
    </delete>
</rollback>

在springboot 与Liquiabse向前更新中，我们可以通过以下的设定来限定Liquibase只会更新到某一个tag

to restrict Liquibase roll forward until which tag, we can configure below properties in springboot application.properties.

spring.liquibase.tag=v2.2.4

我们也可以通过以下配置来让Liquibase生成我们需要的rollback script. 在容器部署中，如果我们无法直接访问数据库，个人觉得回滚脚本其实作用不大。这个步骤可以省略。

configure rollback file is let springboot generate rollback file

spring.liquibase.rollbackFile=/Documents/Projects/spring-liquibase-demo/rollback.sql

Liquibase changeset 全文

Full sample of liquibase changeset file

Liquibase Changeset

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xmlns:pro="http://www.liquibase.org/xml/ns/pro"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
    <!--changeset tag-->
    <changeSet id="tagdb" author="liangfaan">
        <tagDatabase tag="v2.2.2"/>
    </changeSet>
    <changeSet id="202010211812" author="Julius Krah">
        <createTable tableName="house">
            <column name="id" type="bigint">
                <constraints primaryKey="true" primaryKeyName="house_id_pk" />
            </column>
            <column name="owner" type="varchar(250)">
                <constraints unique="true" uniqueConstraintName="house_owner_unq" />
            </column>
            <column name="fully_paid" type="boolean" defaultValueBoolean="false"/>
        </createTable>
        <createTable tableName="item">
            <column name="id" type="bigint">
                <constraints primaryKey="true" primaryKeyName="item_id_pk" />
            </column>
            <column name="name" type="varchar(250)" />
            <column name="house_id" type="bigint">
                <constraints nullable="false" notNullConstraintName="item_house_id_nn" />
            </column>
        </createTable>
        <addAutoIncrement tableName="house" columnName="id" columnDataType="bigint" startWith="1" incrementBy="1" />
        <addAutoIncrement tableName="item" columnName="id" columnDataType="bigint" startWith="1" incrementBy="1" />
        <createSequence sequenceName="hibernate_sequence" incrementBy="1" startValue="1" />
        <addForeignKeyConstraint baseTableName="item" baseColumnNames="house_id" constraintName="item_house_id_fk" referencedTableName="house" referencedColumnNames="id" />
        <!-- rollback script must be configure if enable db tag--> 
        <rollback>
            <dropForeignKeyConstraint baseTableName="item" constraintName="item_house_id_fk"/>
            <dropSequence sequenceName="hibernate_sequence"/>
            <dropTable tableName="item"/>
            <dropTable tableName="house"/>
        </rollback>
    </changeSet>
    <changeSet id="tagdb_v2.2.3" author="liangfaan">
        <tagDatabase tag="v2.2.3"/>
    </changeSet>
    <changeSet id="inset_record" author="liangfaan">
        <insert tableName="house">
            <column name="owner" value="testowner1"/>
            <column name="fully_paid" value="1"/>
        </insert>
        <insert tableName="item">
            <column name="name" value="testowner1"/>
            <column name="house_id" valueComputed="(select id from house where owner='testowner1')"/>
        </insert>
        <rollback>
            <delete tableName="item">
                <where>
                    house_id=(select id from house where owner='testowner1')
                </where>
            </delete>
            <delete tableName="house">
                <where>
                    owner = 'testowner1'
                </where>
            </delete>
        </rollback>
    </changeSet>
    <changeSet id="tagdb_v2.2.4" author="liangfaan">
        <tagDatabase tag="v2.2.4"/>
    </changeSet>
</databaseChangeLog>

重头戏： 以下是如何springboot 中实现Liquibase API。在每次回滚中，我们必须保证当前springboot 应用包是包含了所有回滚脚本changset以及数据的tag changeset. 也就是说，我们需要通过API回滚数据库之后，再来回滚应用程序的版本。

Implement Springboot API to rollback liquibase changes to particular tag

by rollback to tag v2.2.3, those insert into item, house records will be rolled back.

curl http://localhost:8080/rollbackLiquibaseByTag?tag=v2.2.3

Sample API which we trigger liquibase rollback via Springboot API

@GetMapping("/rollbackLiquibaseByTag")
public String rollbackLiquibase(String tag){
    try {
        ResourceAccessor resourceAccessor = new SpringResourceAccessor(springLiquibase.getResourceLoader());
        DatabaseConnection connection = new JdbcConnection(springLiquibase.getDataSource().getConnection());
        Liquibase liquibase = new Liquibase(springLiquibase.getChangeLog(), resourceAccessor, connection);
        liquibase.rollback(tag, springLiquibase.getContexts());
        liquibase.close();
    } catch (SQLException | LiquibaseException e) {
        throw new RuntimeException(e);
    }
    return "Ok";
}

具体项目源代码 Github Project