Database Version Control : Using liquibase and notes

最近碰到Project更新版本時Database update的問題, 後來有人建議使用liquibase 來管理, 就跑去看了一下,中文官網 : Liquibase(Chinese)
liquibase 主要是追蹤,管理以及 applying 資料庫異動, 這邊也沒用到很復雜的功能,基本上就是控制每次Project version update 時一併Update 相關版本的Database, 建議可以看官方的 快速上手 , 我這邊是用Spring來做處理, 在ApplicationContext.xml 加入下面的設定

<bean id="liquibase" class="liquibase.integration.spring.SpringLiquibase">
  <property name="dataSource" ref="emdDataSource" />
  <property name="changeLog"
   value="classpath:db/changelog/db.changelog-master.xml" />
  <!-- contexts specifies the runtime contexts to use. -->
  <property name="contexts" value="test, production" />
 </bean>

下圖是相關Database 版本跟init 的update sql , changelog xml 檔

db.changelog-master.xml 使用include 把 init changelog 跟 2.0 的change log 加進來

<?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"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 <include file="db/changelog/init/db.changelog-init.xml" />
 <include file="db/changelog/2.0/db.changelog-2.0.xml" />
</databaseChangeLog> 

然後是 db.changelog-init.xml , 中間有使用 tag , 因為直接使用原始的SQL檔會有問題(原本的sql 是用來create function , mysql 平台, 使用DELIMITER //在liquibase 執行時不會過, 後來是拿掉加到 tag裡)

<?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"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

 <changeSet id="init-db" author="root" runOnChange="true">  
  <preConditions onFail="MARK_RAN">
   <not>
    <tableExists tableName="emd_const"/>
   </not>
  </preConditions>
     <comment>initialize database tables data</comment>

     <sqlFile path="db/changelog/init/sql/01-emdp-table.sql" />    
     <sqlFile path="db/changelog/init/sql/02-emdp-table-meta.sql" />
     <sqlFile path="db/changelog/init/sql/03-emdp-table-data.sql" />     
     <sqlFile path="db/changelog/init/sql/05-emdp-view.sql" />
     <sqlFile path="db/changelog/init/sql/09-emdp-const-data.sql" />     
     <sql splitStatements="false" stripComments="false">
     <![CDATA[
CREATE FUNCTION nextval (seq_name varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC

BEGIN
    DECLARE cur_val bigint(20);
    SELECT
        sequence_cur_value INTO cur_val
    FROM
        emd_sequence_data
    WHERE
        sequence_name = seq_name;

    IF cur_val IS NOT NULL THEN
        UPDATE
            emd_sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value   sequence_increment) > sequence_max_value,
                IF (sequence_cycle = TRUE, sequence_min_value, NULL ),
                sequence_cur_value   sequence_increment
            )
        WHERE
            sequence_name = seq_name;
    END IF;
 
 RETURN cur_val;
END;
  ]]></sql>
     <!-- <sqlFile path="db/changelog/init/sql/04-emdp-function.sql" />
      -->
 </changeSet> 

</databaseChangeLog> 

然後是2.0 版本的db.changelog-2.0.xml

<?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"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

 <changeSet id="2.0-updateTable" author="root" runOnChange="true">
     <comment>1.0 update for table change</comment>    
 </changeSet>
 
 <changeSet id="2.0-insertData" author="root" runOnChange="true">  
        <comment>1.0 update for INSERT value sql</comment>
  <sqlFile path="db/changelog/2.0/sql/db.changelog-2.0-INSERT.sql"/>
 </changeSet>
 
 <changeSet id="2.0-updateData" author="root" runOnChange="true">
     <comment>1.0 update for UPDATE value sql</comment>  
  <sqlFile path="db/changelog/2.0/sql/db.changelog-2.0-UPDATE.sql"/>
 </changeSet>

</databaseChangeLog> 

這樣在spring 起來的時候會呼叫liquibase bean , 然後確認相關的change set ,
Change set裡面的runOnChange="true" 主要是因為changelog xml 有變更的時候會產vlidationException, 主要是checkSum不同的錯誤,類似如下

Caused by: liquibase.exception.ValidationFailedException: Validation Failed:
     1 changes have validation errors
          java.lang.ClassCastException: liquibase.statement.core.AddForeignKeyConstraintStatement cannot be cast to liquibase.statement.core.CreateTableStatement

Reference:

Liquibase home (chinese)

SQL Database Version Control – Liquibase

IBM : 让开发自动化: 实现自动化数据库迁移