liquigo: Liquibase by golang

liquigo: Liquibase by golang

liquigo是一个参考Liquibase中间件的golang开发的数据库版本管理工具，实现了大多数以xml格式配置的常用语法，支持的数据库类型和语法参见如下表格：

Liquibase语法（仅支持xml格式）	MySQL / TiDB / OceanBase	MariaDB	openGauss / PostgreSQL	KingbaseES	达梦	SQLite	Oracle	SQL Server
变更集：changeSet	√	√	√	√	√	√	√	√
变更集注释：comment	√	√	√	√	√	√	√	√
创建表：createTable	√	√	√	√	√	√	√	√
删除表：dropTable	√	√	√	√	√	√	√	√
重命名表：renameTable	√	√	√	√	√	-	√	√
添加列：addColumn	√	√	√	√	√	-	√	√
删除列：dropColumn	√	√	√	√	√	√	√	√
重命名列：renameColumn	√	√	√	√	√	-	√	√
变更列：modifyDataType	√	√	√	√	√	-	√	√
删除默认值：dropDefaultValue	√	√	√	√	√	-	√	√
创建视图：createView	√	√	√	√	√	√	√	√
删除视图：dropView	√	√	√	√	√	√	√	√
创建索引：createIndex	√	√	√	√	√	√	√	√
删除索引：dropIndex	√	√	√	√	√	√	√	√
属性替换${}	√	√	√	√	√	√	√	√
执行SQL语句：sql	√	√	√	√	√	√	√	√
执行SQL文件：sqlFile	√	√	√	√	√	√	√	√
前置条件：preConditions	√	√	√	√	√	√	√	√
前置条件与或非：not,and,or	√	√	√	√	√	√	√	√
前置条件存在判断：tableExists,viewExists	√	√	√	√	√	√	√	√
前置条件存在判断：columnExists,sqlCheck	√	√	√	√	√	√	√	√
前置条件存在判断：indexExists	√	√	√	√	√	×	√	√
事务回滚：rollback	√	√	√	√	√	√	√	√
开发调试用停止：stop	√	√	√	√	√	√	√	√

测试过的数据库系统版本有：

openGauss V2.1.0 (兼容PostgreSQL模式)

OceanBase V3.1.1 (兼容MySQL模式)

MySQL V5.7.27 / MySQL V8.0.28

TiDB V5.4.0

MariaDB V10.3.34

PostgreSQL V12.10

KingbaseES V008R006

达梦 V8.1.2.2

SQLite V3.38

Oracle Express Edition V21c

SQL Server V2019

具体使用请参考app.yml配置文件以及entry-*.xml和db目录下的若干xml配置文件。

---

ChangeSet配置详解

变更集：changeSet

• 属性id: 必须全局唯一

• 属性author: 该changeSet的作者

• 属性failOnError: [可选项]运行出错时是否终止运行，默认为true

• 属性runAlways: [可选项]该changeSet是否每次都运行，默认为false

• 属性dbms: [可选项]该changeSet适用的数据库类型，默认为all，例如：mysql,dm,!sqlite 表示适用mysql和达梦，不适用sqlite

• 属性ignore: [可选项]是否忽略该changeSet，默认为false，不忽略

• 元素comment: [可选项]该changeSet的注释说明，会记入db_change_set管理表的changeset_comment字段中

<changeSet id="app01-ddl-03-test-dbms" author="liquigo" failOnError="false" runAlways="true" dbms="mysql,!sqlite,!h2">
    <comment>comment of this changeSet</comment>
    ...
</changeSet>
<changeSet id="app01-dml-03-test-ignore" author="liquigo" ignore="true">
    <comment>ignore propertie for testing changeSet</comment>
    ...
</changeSet>

创建表：createTable

<createTable tableName="test_column_type" remarks="test the data type of the field">
    <column name="type_boolean" type="boolean" />
    <column name="type_bit6" type="bit(6)" />
    <column name="type_tinyint" type="tinyint" />
    <column name="type_smallint" type="smallint" />
    <column name="type_mediumint" type="mediumint" />
    <column name="type_int" type="int" />
    <column name="type_integer" type="integer" />
    <column name="type_bigint" type="bigint" />
    <column name="type_float" type="float(6,2)" />
    <column name="type_real" type="real(5,2)" />
    <column name="type_double" type="double(10,3)" />
    <column name="type_decimal" type="decimal(15,3)" />
    <column name="type_numeric" type="numeric(10,0)" />
    <column name="type_number" type="number(10,0)" />
    <column name="type_char" type="char(36)" />
    <column name="type_varchar" type="varchar(36)" />
    <column name="type_binary" type="binary(36)" />
    <column name="type_varbinary" type="varbinary(36)" />
    <column name="type_tinyblob" type="tinyblob" />
    <column name="type_blob" type="blob" />
    <column name="type_mediumblob" type="mediumblob" />
    <column name="type_longblob" type="longblob" />
    <column name="type_tinytext" type="tinytext" />
    <column name="type_text" type="text" />
    <column name="type_mediumtext" type="mediumtext" />
    <column name="type_longtext" type="longtext" />
    <column name="type_datetime" type="datetime" />
    <column name="type_timestamp" type="timestamp" />
    <column name="type_date" type="date" />
    <column name="type_time" type="time" />
    <column name="type_uuid" type="uuid" />
    <column name="type_currency" type="currency" />
</createTable>
<createTable tableName="test_role" remarks="test role">
    <column name="id" type="varchar(36)">
        <constraints primaryKey="true" nullable="false" primaryKeyName="pk_test_role_key" />
    </column>
    <column name="creator" type="varchar(36)" defaultValue="${createUser.defaultValue}" />
    <column name="created" type="decimal(15)" defaultValue="1" />
    <column name="updater" type="varchar(36)" defaultValue="${createUser.defaultValue}" />
    <column name="updated" type="decimal(15)" defaultValue="1" />
    <column name="uuid" type="varchar(36)">
        <constraints primaryKey="true" nullable="false" primaryKeyName="pk_test_role_key"
                    unique="true" uniqueConstraintName="uk_test_role_uuid"/>
    </column>
    <column name="role_name" type="varchar(50)" remarks="role name">
        <constraints unique="true" nullable="false" uniqueConstraintName="uk_test_role_name" />
    </column>
    <column name="detail" type="varchar(250)" defaultValue="role" remarks="role detail" >
        <constraints nullable="false" />
    </column>
    <column name="app_id" type="varchar(36)" defaultValue="3101" remarks="foreign key sum_app.id" />
</createTable>

对应生成的适配达梦的sql脚本如下：

create table test_column_type (
  type_boolean bit,
  type_bit6 bit,
  type_tinyint tinyint,
  type_smallint smallint,
  type_mediumint int,
  type_int int,
  type_integer integer,
  type_bigint bigint,
  type_float float,
  type_real real,
  type_double double,
  type_decimal decimal(15,3),
  type_numeric numeric(10,0),
  type_number number(10,0),
  type_char char(36),
  type_varchar varchar(36),
  type_binary binary(36),
  type_varbinary varbinary(36),
  type_tinyblob blob,
  type_blob blob,
  type_mediumblob blob,
  type_longblob blob,
  type_tinytext text,
  type_text text,
  type_mediumtext text,
  type_longtext text,
  type_datetime datetime,
  type_timestamp timestamp,
  type_date date,
  type_time time,
  type_uuid varchar(36),
  type_currency decimal(15,2)
);
comment on table test_column_type is 'test the data type of the field';

create table test_role (
  id varchar(36) not null,
  creator varchar(36) default '20000',
  created decimal(15) default 1,
  updater varchar(36) default '20000',
  updated decimal(15) default 1,
  uuid varchar(36) not null,
  role_name varchar(50) not null,
  detail varchar(250) not null default 'role',
  app_id varchar(36) default '3101',
  constraint pk_test_role_key primary key (id,uuid)
);
comment on table test_role is 'test role';
create unique index uk_test_role_uuid on test_role (uuid);
create unique index uk_test_role_name on test_role (role_name);
comment on column test_role.role_name is 'role name';
comment on column test_role.detail is 'role detail';
comment on column test_role.app_id is 'foreign key sum_app.id';

删除表：dropTable

<dropTable cascadeConstraints="true" tableName="test_t03"/>

对应生成的适配达梦的sql脚本如下：

drop table test_t03 cascade constraints;

重命名表：renameTable

<renameTable oldTableName="test_rename_table" newTableName="test_rename_table_new" />

对应生成的适配达梦的sql脚本如下：

alter table test_rename_table rename to test_rename_table_new;

添加列：addColumn

<addColumn tableName="test_t04">
    <column name="first_login_time" type="varchar(30)" remarks="first login time, yyyy-MM-dd HH:mm:ss" />
    <column name="login_count" type="decimal(7)" defaultValue="0" remarks="login success count">
        <constraints nullable="false"/>
    </column>
    <column name="login_account" type="varchar(20)" defaultValue="default value" remarks="login account">
        <constraints unique="true" nullable="false" uniqueConstraintName="uk_user_login_account" />
    </column>
</addColumn>

对应生成的适配达梦的sql脚本如下：

alter table test_t04 add first_login_time varchar(30);
comment on column test_t04.first_login_time is 'first login time, yyyy-MM-dd HH:mm:ss';
alter table test_t04 add login_count decimal(7) not null default 0;
comment on column test_t04.login_count is 'login success count';
alter table test_t04 add login_account varchar(20) not null default 'default value';
comment on column test_t04.login_account is 'login account';
create unique index uk_user_login_account on test_t04 (login_account);

删除列：dropColumn

<dropColumn columnName="first_login_time" tableName="test_t04"/>
<dropColumn tableName="test_t04">
    <column name="login_count"/>
    <column name="login_account"/>
</dropColumn>

对应生成的适配达梦的sql脚本如下：

alter table test_t04 drop column first_login_time cascade;
alter table test_t04 drop column login_count cascade;
alter table test_t04 drop column login_account cascade;

重命名列：renameColumn

<renameColumn columnDataType="varchar(36)"
        newColumnName="new_app_id"
        oldColumnName="app_id"
        remarks="app id"
        tableName="test_t04"/>

对应生成的适配达梦的sql脚本如下：

alter table test_t04 rename column app_id to new_app_id;
comment on column test_t04.new_app_id is 'app id';

变更列：modifyDataType

<modifyDataType columnName="new_app_id" newDataType="varchar(50)" tableName="test_t04"/>
<modifyDataType columnName="org_id" newDataType="varchar(70)" tableName="test_t04"/>

对应生成的适配达梦的sql脚本如下：

alter table test_t04 modify new_app_id varchar(50);
alter table test_t04 modify org_id varchar(70);

删除默认值：dropDefaultValue

<dropDefaultValue columnDataType="varchar(250)" columnName="detail" tableName="test_role"/>

对应生成的适配达梦的sql脚本如下：

alter table test_role modify detail default null;

创建视图：createView

<createView fullDefinition="false"
        replaceIfExists="true"
        remarks="view of test role"
        viewName="v_test_role">
    select id, role_name, app_id from test_role where created >= 1644800296000;
</createView>

对应生成的适配达梦的sql脚本如下：

drop view if exists v_test_role;
create or replace view v_test_role as 
select id, role_name, app_id from test_role where created >= 1644800296000;
comment on view v_test_role is 'view of test role';

删除视图：dropView

<dropView viewName="v_test_role" dropIfExists="true"/>

对应生成的适配达梦的sql脚本如下：

drop view if exists v_test_role;

创建索引：createIndex

<createIndex indexName="idx_test_property_person_name" tableName="${tableName}">
    <column name="person_name" type="varchar(100)" />
</createIndex>
<createIndex indexName="idx_test_property_id_card" tableName="${tableName}" unique="true">
    <column name="id_card" type="varchar(150)" />
</createIndex>

对应生成的适配达梦的sql脚本如下：

create index idx_test_property_person_name on test_property_replace (person_name);
create unique index idx_test_property_id_card on test_property_replace (id_card);

删除索引：dropIndex

<dropIndex indexName="uk_test_role_name" tableName="test_role"/>

对应生成的适配达梦的sql脚本如下：

drop index uk_test_role_name;

属性替换${}

<!-- entry-***.xml -->
<property name="tableName" value="test_property_replace" dbms="all" />
<property name="pk" value="pk_"/>
<property name="a" value="aaa"/>
<property name="b" value="bbb"/>
<property name="c" value="ccc"/>
<property name="d" value="default ddd"/>
<property name="date" value="date" dbms="oracle" />
<property name="date" value="datetime" dbms="mysql,mariadb,mssql,tidb" />
<property name="date" value="timestamp" dbms="hsqldb" />
<property name="date" value="timestamp" dbms="h2" />
<property name="date" value="date" dbms="dm" />
<property name="date" value="date" dbms="kingbase" />
<property name="date" value="integer" dbms="sqlite" />
<property name="date" value="timestamp" dbms="postgres" />
<property name="createUser.defaultValue" value="20000" dbms="none" />

<!-- changeSet -->
<createTable tableName="${tableName}" remarks="test property replace">
    <column name="id" type="varchar(36)">
        <constraints primaryKey="true" nullable="false"
                        primaryKeyName="${pk}test_property_replace" />
    </column>
    <column name="creator" type="varchar(36)" defaultValue="${createUser.defaultValue}" />
    <column name="create_time" type="decimal(15)" defaultValue="1" />
    <column name="person_name" type="varchar(100)" remarks="person name"/>
    <column name="gender" type="varchar(20)" remarks="gender" />
    <column name="birthdate" type="${date}" remarks="birth date" />
    <column name="${a}_${b}_${c}" type="varchar(50)" defaultValue="${d}" remarks="multi placer" />
    <column name="person_status" type="varchar(20)" remarks="person status" />
    <column name="telephone" type="varchar(150)" remarks="telephone" />
    <column name="email" type="varchar(50)" remarks="email" />
    <column name="photo" type="text" remarks="photo" />
</createTable>

对应生成的适配达梦的sql脚本如下：

create table test_property_replace (
  id varchar(36) not null,
  creator varchar(36) default '20000',
  create_time decimal(15) default 1,
  person_name varchar(100),
  gender varchar(20),
  birthdate date,
  aaa_bbb_ccc varchar(50) default 'default ddd',
  person_status varchar(20),
  telephone varchar(150),
  email varchar(50),
  photo text,
  constraint pk_test_property_replace primary key (id)
);
comment on table test_property_replace is 'test property replace';
comment on column test_property_replace.person_name is 'person name';
comment on column test_property_replace.gender is 'gender';
comment on column test_property_replace.birthdate is 'birth date';
comment on column test_property_replace.aaa_bbb_ccc is 'multi placer';
comment on column test_property_replace.person_status is 'person status';
comment on column test_property_replace.telephone is 'telephone';
comment on column test_property_replace.email is 'email';
comment on column test_property_replace.photo is 'photo';

执行SQL语句：sql

<sql>
    delete from test_role where created = 1644800296000; -- single line comment
    insert into test_role(id, creator, created, updater, updated, uuid, role_name, detail, app_id) values ('1', '1', 1644800296000, '1', 1644800296000, 'uuid1', 'role_name1', '1', '1');
    <!-- comment1 -->
    insert into test_role(id, creator, created, updater, updated, uuid, role_name, detail, app_id) values ('2', '1', 1644800296000, '1', 1644800296000, 'uuid2', 'role_name2', '1', '1');
    /*
        Multiline comment, append record
    */
    <!--
        comment2
    -->
    insert into test_role(id, creator, created, updater, updated, uuid, role_name, detail, app_id) values ('3', '1', 1644800296000, '1', 1644800296000, 'uuid3', 'role_name3', '1', '1');
    insert into test_role(id, creator, created, updater, updated, uuid, role_name, detail, app_id) values ('4', '4', 1644800296000, '4', 1644800296000, 'uuid4', 'role_name4', '1', '1');
</sql>

执行SQL文件：sqlFile

<sqlFile dbms="!sqlite, !postgres, mysql"
    path="db/app01/app01-01.sql" />
<sqlFile dbms="oracle,dameng"
    path="db/app01/app01-01-oracle.sql" />
<sqlFile dbms="!sqlite, postgres, mysql"
    path="D://dev//vue-front//liquigo//db//app01//app01-02.sql" />

前置条件：preConditions

<changeSet id="app01-ddl-pre-cond-01-logical-true" author="liquigo">
    <comment><![CDATA[ !false = true ]]></comment>
    <preConditions onFail="MARK_RAN" onError="HALT" onFailMessage="Fail Message" onErrorMessage="Error Message">
        <not>
            <tableExists tableName="t_user" />
        </not>
    </preConditions>
    <createTable tableName="test_pre_cond_01">
        <column name="id" type="varchar(36)" />
        <column name="name" type="varchar(36)" />
    </createTable>
</changeSet>

前置条件与或非：not,and,or

<changeSet id="app01-ddl-pre-cond-09-logical-true" author="liquigo">
    <comment><![CDATA[ (true && false) || (true && false) || !(true && false) = true ]]></comment>
    <preConditions onFail="MARK_RAN">
        <or>
            <and>
                <tableExists tableName="test_role_true" />
                <viewExists viewName="v_user_view_false" />
            </and>
            <and>
                <tableExists tableName="test_role_1_true" />
                <viewExists viewName="v_test_role_1_false" />
            </and>
            <not>
                <tableExists tableName="test_role_2_true" />
                <viewExists viewName="v_test_role_2_false" />
            </not>
        </or>
    </preConditions>
    <createTable tableName="test_pre_cond_09">
        <column name="id" type="varchar(36)" />
        <column name="name" type="varchar(36)" />
    </createTable>
</changeSet>

前置条件存在判断：tableExists,viewExists,columnExists,indexExists,sqlCheck

<tableExists tableName="test_role_2_true" />
<viewExists viewName="v_test_role_2_false" />
<columnExists tableName="test_role" columnName="role_name" />
<indexExists indexName="idx_test_property_person_name" />
<sqlCheck expectedResult="1">select count(1) from test_role where id = '1'</sqlCheck>

事务回滚：rollback

<changeSet id="app01-ddl-17-rollback" author="liquigo" runOnChange="true">
    <comment>test preConditions and rollback</comment>
    <preConditions onFail="MARK_RAN">
        <not>
            <tableExists tableName="test_rollback" />
        </not>
    </preConditions>
    <createTable tableName="test_rollback" remarks="test rollback">
        <column name="id" type="varchar(36)">
            <constraints primaryKey="true" nullable="false" primaryKeyName="pk_test_rollback_key" />
        </column>
        <column name="role_name" type="varchar(50)" remarks="role name" />
        <column name="detail" type="varchar(260)" defaultValue="role detail" remarks="role detail" />
        <column name="app_id" type="varchar(36)" remarks="foreign key app.id" />
        <column name="org_id" type="varchar(36)" remarks="foreign key org.id" />
    </createTable>
    <addColumn tableName="test_rollback">
        <column name="first_login_time" type="varchar(30)" remarks="first login time, yyyy-MM-dd HH:mm:ss" />
        <column name="login_count" type="decimal(7)" defaultValue="0" remarks="login count" />
    </addColumn>
    <rollback>
        <dropColumn tableName="test_rollback">
            <column name="login_count"/>
            <column name="first_login_time"/>
        </dropColumn>
        <dropTable cascadeConstraints="true" tableName="test_rollback"/>
    </rollback>
    <rollback>
        drop table test_rollback
    </rollback>
</changeSet>

推荐一个完整的changeSet的结构，按顺序如下：

1. comment 提供注释说明，用于备忘

2. preConditions 进行前置条件判断

3. create/drop/add/.. 一个或多个数据库变更标签

4. rollback 数据库变更失败后的回滚操作

<changeSet id="app01-ddl-17-rollback" author="liquigo">
    <comment>注释说明，用于备忘</comment>
    <preConditions onFail="MARK_RAN">
        <not>
            <tableExists tableName="test_rollback" />
        </not>
    </preConditions>
    <createTable tableName="test_rollback" remarks="test rollback">
        <column name="id" type="varchar(36)">
            <constraints primaryKey="true" nullable="false" primaryKeyName="pk_test_rollback_key" />
        </column>
        <column name="role_name" type="varchar(50)" remarks="role name" />
        <column name="detail" type="varchar(260)" defaultValue="role detail" remarks="role detail" />
        <column name="app_id" type="varchar(36)" remarks="foreign key app.id" />
        <column name="org_id" type="varchar(36)" remarks="foreign key org.id" />
    </createTable>
    <rollback>
        <dropTable cascadeConstraints="true" tableName="test_rollback"/>
    </rollback>
</changeSet>

开发调试用停止：stop

当运行到包含stop标签的changeSet时停止（包含stop标签的changeSet不运行）。

<changeSet id="..." author="liquigo">
    ...
</changeSet>
<changeSet id="liquigo-stop-for-debug" author="liquigo">
    <stop message="liquigo stop message"/>
</changeSet>
<changeSet id="..." author="liquigo">
    ...
</changeSet>

---

注意事项

【禁止在sql标签中运行ddl语句】

所有的ddl变更必须使用对应的xml标签配置实现

<changeSet id="app01-create-add-index" author="liquigo">
    <sql splitStatements="true" stripComments="true">
        create unique index idx_sys_dict_type on sys_dict_type(name);
        create index idx_itemcode_sys_dict_item on sys_dict_item(item_code);
    </sql>
</changeSet>
<changeSet id="app01-init-create-06" author="liquigo">
    <sql splitStatements="true" stripComments="true">
        alter table sys_audit_log modify user_agent varchar(250);
    </sql>
</changeSet>
<changeSet id="app01-create-02" author="liquigo">
    <sql splitStatements="true" stripComments="true">
        alter table svc_datax_task add target_name varchar(36);
    </sql>
</changeSet>