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配置文件。
属性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 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 cascadeConstraints="true" tableName="test_t03"/>
对应生成的适配达梦的sql脚本如下:
drop table test_t03 cascade constraints;
<renameTable oldTableName="test_rename_table" newTableName="test_rename_table_new" />
对应生成的适配达梦的sql脚本如下:
alter table test_rename_table rename to test_rename_table_new;
<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 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 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 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 columnDataType="varchar(250)" columnName="detail" tableName="test_role"/>
对应生成的适配达梦的sql脚本如下:
alter table test_role modify detail default null;
<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 viewName="v_test_role" dropIfExists="true"/>
对应生成的适配达梦的sql脚本如下:
drop view if exists v_test_role;
<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 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>
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>
<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" />
<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>
<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 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>
<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的结构,按顺序如下:
comment 提供注释说明,用于备忘
preConditions 进行前置条件判断
create/drop/add/.. 一个或多个数据库变更标签
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标签的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>
所有的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>
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。