網頁

2018/3/30

Liquibase 使用command line操作

本篇介紹使用command line指令來操作Liquibase產生changelog,執行update及rollback。可先參考學習Liquibase

範例使用的資料庫為MySQL 5.7.12

資料庫字符集character set及collation設定。

SELECT @@character_set_database, @@collation_database;
+----------------------------+-----------------------+
| # @@character_set_database |  @@collation_database |
+----------------------------+-----------------------+
| utf32                      |  utf32_general_ci     |
+----------------------------+-----------------------+

範例用的資料庫為testdb,裡面有一個空資料表user_info,欄位為user_idemailpassworduser_name



下載Liquibase (3.5.5)並解壓縮至D:\Liquibase\,即為Liquibase的安裝目錄。



因為Liquibase連資料庫需要jdbc driver,所以範例將MySQL的jdbc driver(mysql connector/J)放在與Liquibase同個目錄。

jdbc driver也可以放在其他目錄,只要在執行時將--classpath參數的值設為jdbc driver所在目錄位置即可。



測試一,開啟Windows的命令提示字元(command line),然後將工作目錄移到Liquibase的安裝目錄,也就是D:\Liquibase

如果使用的mysql jdbc driver是6.x的版本,則執行下面指令。

liquibase --driver=com.mysql.cj.jdbc.Driver --changeLogFile=changelog.xml:--url="jdbc:mysql://localhost:3306/TestDB?serverTimezone=CST&useSSL=false&nullNamePatternMatchesAll=true" --classpath=mysql-connector-java-6.0.6.jar --username=matthung --password=12345 generateChangeLog



參數說明:

liquibase為執行Liquibase的執行命令,實際是執行liquibase.bat

--driver=com.mysql.cj.jdbc.Driver是因為MySQL jdbc driver版本為6.x。如果是5.x則改用com.mysql.jdbc.Driver

--changeLogFile=changelog.xml是產生Liquibase change log file檔的名稱及位置,這邊設定在執行時的工作目錄,也就是D:\Liquibase

--url的值為資料庫位置。因為範例MySQL資料庫安裝於本機,所以是localhost:3306。資料庫名稱為TestDB。因為6.x的driver需要提供其他參數才可以正常連線,所以後面接了參數serverTimezone=CST&useSSL=false&nullNamePatternMatchesAll=true

serverTimezone指定時區,範例使用CST。

userSSL設定是否使用SSL加密連線。設為false。

nullNamePatternMatchesAll在5.x預設為true,但6.x似乎變為false。這邊要設為true,否則會出現錯誤
Unexpected error running Liquibase: liquibase.exception.DatabaseException: java.sql.SQLException: Table name pattern can not be NULL or empty.

--classpath=mysql-connector-java-6.0.6.jar為jdbc driver的位置,範例中把driver放在與執行命令的同個目錄D:\Liquibase

--username 資料庫使用者名稱。

--password 資料庫使用者密碼。

generateChangeLog指令用來產生change log file參數。

指令各參數可參考Liquibase Command Line

如果MySQL jdbc driver是5.x,則命令如下

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 generateChangeLog

執行後會依照--changeLogFile參數產生changelog file。此範例在Liquibase安裝目錄D:\Liquibase下產生changelog.xml



產生的changelog.xml內容如下。

changelog.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <changeSet author="matthung (generated)" id="1522383544517-1">
        <createTable tableName="user_info">
            <column name="user_id" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="email" type="VARCHAR(300)"/>
            <column name="password" type="VARCHAR(45)"/>
            <column name="user_name" type="VARCHAR(45)"/>
        </createTable>
    </changeSet>
    <changeSet author="matthung (generated)" id="1522383544517-2">
        <addPrimaryKey columnNames="user_id" constraintName="PRIMARY" tableName="user_info"/>
    </changeSet>
</databaseChangeLog>

預設只會產生資料庫的schema設定,如果要產出包含資料的changelog file,則要輸入參數--diffTypes="tables,views,columns,indexs,foreignkeys,primarykeys,uniqueconstraints,data",例如

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 --diffTypes="tables,views,columns,indexs,foreignkeys,primarykeys,uniqueconstraints,data" generateChangeLog

也就是產出的changelog會包含 tables,views,columns,indexs,foreignkeys,primarykeys,uniqueconstraints及data。


測試二,接下來建立一個新的changelog檔changelog-20180330130430.xml來新增一筆資料,內容如下。請確認檔案的編碼和xml的encoding設定相同,例如範例皆為UTF-8,若不同可能會出現錯誤Invalid byte 1 of 1-byte UTF-8 sequence.

changelog-20180330130430.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <changeSet author="matthung" id="insert-1">
        <insert tableName="user_info">
            <column name="user_id" value="1"/>
            <column name="email" value="matt@123.com"/>
            <column name="password" value="abc123"/>
            <column name="user_name" value="肉豬"/>
        </insert>
        <rollback>
            DELETE FROM user_info WHERE user_id = 1;
        </rollback>
    </changeSet>
</databaseChangeLog>

<insert>用來新增資料。

<rollback>用來回退這個changeSet的動作。只有使用rollback指令時才會被執行。

存檔後執行update。

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 update



執行完畢後檢視資料庫,重新整理便可以看到testdb中多了由Liquibase建立的databasechangelogdatabasechangeloglock資料表,且user_info確實依照changelog的設定增加了一筆資料。



每次update時,每一個changeSet會在databasechangelog新增一筆記錄,而此次的新增動作在databasechangelog中紀錄如下。

+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
|    ID    |   AUTHOR  |            FILENAME           |     DATEEXECUTED     |  ORDEREXECUTED |  EXECTYPE |                MD5SUM               |         DESCRIPTION         |  COMMENTS |  TAG |  LIQUIBASE |  CONTEXTS |  LABELS |  DEPLOYMENT_ID |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
| insert-1 |  matthung |  changelog-20180330130430.xml |  2018-03-30 13:19:55 |              1 |  EXECUTED |  7:d4ffc9347022823456fd4557a46e684a |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2387195202 |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+

接著執行下面指令來rollback剛剛的新增動作。

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 rollbackCount 1

rollbackCount 1 是從目前的狀態往回退1筆,也就是回復上一洞的意思(如果你有當過兵應該知道什麼叫上一洞)。

執行完後檢視資料庫,可以發現剛剛user_info中新增的資料不見了,同時databasechangelog中的記錄也消失了。


測試三。接著在changelog-20180330130430.xml新增兩筆資料如下。

changelog-20180330130430.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <changeSet author="matthung" id="insert-1">
        <insert tableName="user_info">
            <column name="user_id" value="1"/>
            <column name="email" value="matt@123.com"/>
            <column name="password" value="abc123"/>
            <column name="user_name" value="肉豬"/>
        </insert>
        <rollback>
            DELETE FROM user_info WHERE user_id = 1;
        </rollback>
    </changeSet>
    <changeSet author="matthung" id="insert-2">
        <insert tableName="user_info">
            <column name="user_id" value="2"/>
            <column name="email" value="john@123.com"/>
            <column name="password" value="edf456"/>
            <column name="user_name" value="約翰"/>
        </insert>
        <rollback>
            DELETE FROM user_info WHERE user_id = 2;
        </rollback>
    </changeSet>
</databaseChangeLog>

再次執行update命令。

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 update

執行完後user_info新增兩筆資料。



因為有兩個changeSet,所以databasechangelog中有兩筆記錄

+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
|    ID    |   AUTHOR  |            FILENAME           |     DATEEXECUTED     |  ORDEREXECUTED |  EXECTYPE |                MD5SUM               |         DESCRIPTION         |  COMMENTS |  TAG |  LIQUIBASE |  CONTEXTS |  LABELS |  DEPLOYMENT_ID |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
| insert-1 |  matthung |  changelog-20180330130430.xml |  2018-03-30 14:22:02 |              1 |  EXECUTED |  7:d4ffc9347022823456fd4557a46e684a |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2390922422 |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+

執行rollback命令,這次rollblackCount為2。

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 rollbackCount 2

此時剛剛新增的兩筆資料都及databasechangelog的紀錄都會消失,也就是兩個新增都rollback了。


測試四,新增另一個changelog-20180330144830.xml,內容如下。

changelog-20180330144830.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <changeSet author="matthung" id="insert-3">
        <insert tableName="user_info">
            <column name="user_id" value="3"/>
            <column name="email" value="yoko@123.com"/>
            <column name="password" value="xyz123"/>
            <column name="user_name" value="優口"/>
        </insert>
        <rollback>
            DELETE FROM user_info WHERE user_id = 3;
        </rollback>
    </changeSet>
    <changeSet author="matthung" id="insert-4">
        <insert tableName="user_info">
            <column name="user_id" value="4"/>
            <column name="email" value="gary@123.com"/>
            <column name="password" value="999kkk"/>
            <column name="user_name" value="蓋瑞"/>
        </insert>
        <rollback>
            DELETE FROM user_info WHERE user_id = 4;
        </rollback>
    </changeSet>
</databaseChangeLog>

changelog-20180330130430.xmlchangelog-20180330144830.xml執行update。

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 update

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330144830.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 update

執行後user_info新增了4筆資料。



databasechangelog

+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
|    ID    |   AUTHOR  |            FILENAME           |     DATEEXECUTED     |  ORDEREXECUTED |  EXECTYPE |                MD5SUM               |         DESCRIPTION         |  COMMENTS |  TAG |  LIQUIBASE |  CONTEXTS |  LABELS |  DEPLOYMENT_ID |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
| insert-1 |  matthung |  changelog-20180330130430.xml |  2018-03-30 14:54:16 |              1 |  EXECUTED |  7:d4ffc9347022823456fd4557a46e684a |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2392856265 |
| insert-2 |  matthung |  changelog-20180330130430.xml |  2018-03-30 14:54:16 |              2 |  EXECUTED |  7:da99fc26daba88fa4c5b46caa58f6f67 |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2392856265 |
| insert-3 |  matthung |  changelog-20180330144830.xml |  2018-03-30 14:57:02 |              3 |  EXECUTED |  7:28f32b80a468d0d7314974e50273addb |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2393022770 |
| insert-4 |  matthung |  changelog-20180330144830.xml |  2018-03-30 14:57:03 |              4 |  EXECUTED |  7:c59ecff67265441326eb934b9da0d9a3 |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2393022770 |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+

然後只針對changelog-20180330130430.xml 執行rollback

liquibase --driver=com.mysql.jdbc.Driver --changeLogFile=changelog-20180330130430.xml --url="jdbc:mysql://localhost:3306/TestDB?useSSL=false" --classpath=mysql-connector-java-5.1.46.jar --username=matthung --password=12345 rollbackCount 1

執行後user_info只剩3筆資料。



databasechangelog

+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
|    ID    |   AUTHOR  |            FILENAME           |     DATEEXECUTED     |  ORDEREXECUTED |  EXECTYPE |                MD5SUM               |         DESCRIPTION         |  COMMENTS |  TAG |  LIQUIBASE |  CONTEXTS |  LABELS |  DEPLOYMENT_ID |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+
| insert-1 |  matthung |  changelog-20180330130430.xml |  2018-03-30 14:54:16 |              1 |  EXECUTED |  7:d4ffc9347022823456fd4557a46e684a |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2392856265 |
| insert-3 |  matthung |  changelog-20180330144830.xml |  2018-03-30 14:57:02 |              3 |  EXECUTED |  7:28f32b80a468d0d7314974e50273addb |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2393022770 |
| insert-4 |  matthung |  changelog-20180330144830.xml |  2018-03-30 14:57:03 |              4 |  EXECUTED |  7:c59ecff67265441326eb934b9da0d9a3 |  insert tableName=user_info |           |      |      3.5.4 |           |         |     2393022770 |
+----------+-----------+-------------------------------+----------------------+----------------+-----------+-------------------------------------+-----------------------------+-----------+------+------------+-----------+---------+----------------+

結果只有changelog-20180330130430.xml中的insert-2被rollback。



參考:

沒有留言:

張貼留言