一、介绍
Liquibase是一个用于数据库重构和迁移的开源工具,通过日志文件的形式记录数据库的变更,然后执行日志文件中的修改,将数据库更新或回滚到一致的状态。它的目标是提供一种数据库类型无关的解决方案,通过执行schema类型的文件来达到迁移。其有点主要有以下:
支持几乎所有主流的数据库,如MySQL, PostgreSQL, Oracle, Sql Server, DB2等;
支持多开发者的协作维护;
日志文件支持多种格式,如XML, YAML, JSON, SQL等;
支持多种运行方式,如命令行、Spring集成、Maven插件、Gradle插件等。
快速使用
二、快速使用
pom文件中增加配置
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<build>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.5.3</version>
<configuration>
<changeLogFile>${basedir}/src/main/resources/liquibase/master.xml</changeLogFile>
<promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
<propertyFile>${basedir}/src/main/resources/liquibase/config/${db.profile}.properties</propertyFile>
</configuration>
</plugin>
</plugins>
</build>
开发环境账号密码的配置
\src\main\resources\liquibase\config\dev.properties
driver=org.postgresql.Driver
url=jdbc:postgresql://172.16.11.130:5432/test
username=test
password=test
\src\main\resources\liquibase\change_log\rel-1\script_suite.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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<include file="liquibase/change_log/rel-1/script_suite.xml"/>
</databaseChangeLog>
\src\main\resources\liquibase\change_log\rel-1\script_suite.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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<include file="liquibase/change_log/rel-1/t_book.xml"/>
</databaseChangeLog>
\src\main\resources\liquibase\change_log\rel-1\t_book.xml
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<!--<preConditions>-->
<!--<dbms type="postgresql"/>-->
<!--</preConditions>-->
<changeSet id="create.table" author="ggq">
<createTable tableName="t_book">
<column name="id" type="int8" remarks="主键">
<constraints primaryKey="true"/>
</column>
<column name="name" type="varchar(200)" remarks="书名" />
<column name="isbn" type="varchar(200)" remarks="isbn条码" />
<column name="book_note" type="text" remarks="备注" />
</createTable>
<createIndex tableName="t_book" indexName="idx_book_isbn">
<column name="isbn" />
</createIndex>
</changeSet>
<changeSet id="add.seq" author="ggq">
<createSequence sequenceName="hibernate_sequence" startValue="1" />
</changeSet>
<changeSet id="addRow" author="zwy">
<insert tableName="t_book">
<column name="id" value="1000000" />
<column name="name" value="moneyToChineseNum" />
</insert>
</changeSet>
</databaseChangeLog>
配置使用利用maven的工具进行手动扫描
yml文件中配置,可以启动直接执行配置文件
liquibase:
change-log: classpath:liquibase/master.xml
url: ${spring.datasource.url}
user: ${spring.datasource.username}
password: ${spring.datasource.password}
enabled: false
drop-first: false
enalbed 表示springboot启动的时候是否使用liquibase扫描的xml。
三、常用的语法
一、建表
createTable 是用于创建新表, createIndex 用于创建索引
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<!--<preConditions>-->
<!--<dbms type="postgresql"/>-->
<!--</preConditions>-->
<changeSet id="create.table" author="ggq">
<createTable tableName="t_book">
<column name="id" type="int8" remarks="主键">
<constraints primaryKey="true"/>
</column>
<column name="name" type="varchar(200)" remarks="书名" />
<column name="isbn" type="varchar(200)" remarks="isbn条码" />
<column name="book_note" type="text" remarks="备注" />
</createTable>
<createIndex tableName="t_book" indexName="idx_book_isbn">
<column name="isbn" />
</createIndex>
</changeSet>
<changeSet id="add.seq" author="ggq">
<createSequence sequenceName="hibernate_sequence" startValue="1" />
</changeSet>
<changeSet id="addRow" author="zwy">
<insert tableName="t_book">
<column name="id" value="1000000" />
<column name="name" value="moneyToChineseNum" />
</insert>
</changeSet>
</databaseChangeLog>
二、修改类型
<changeSet id="modify.change_dataType20190114" author="zwy">
<modifyDataType tableName="t_task_example_fullpage" columnName="invoke_fail_num_rate" newDataType="double" />
</changeSet>
三、注解重置
<changeSet id="modify.add_col20190111" author="zwy">
<setColumnRemarks tableName="t_task_example_fullpage" columnName="conver_num" remarks="字段定位数(字段定位成功数,返回的json结果中有的字段,而且在样本实例中有的字段的总数)" ></setColumnRemarks>
</changeSet>
四、删除字段
<changeSet id="modify.add_col20190111" author="zwy">
<dropColumn tableName="t_task_example_fullpage" columnName="ocr_fail_conver_num" ></dropColumn>
</changeSet>
五、增加字段
<addColumn tableName="t_task_example_fullpage">
<column name="sample_num" type="int" remarks="样本数" />
<column name="template_no_fail_num" type="int" remarks="理票失败数据" />
<column name="template_no_wrong_num" type="int" remarks="理票错误数据" />
<column name="all_right_rate" type="double" defaultValue="0"
remarks="票据整体成功率(理票*定位*识别) template_no_right_rate * conver_rate * ocr_right_rate" />
<column name="invoke_fail_num" type="int" remarks="调用失败数" />
<column name="invoke_fail_num_rate" type="int" remarks="调用失败率" />
</addColumn>
六、更新语句
<changeSet id="changeParamName.20190914" author="zwy">
<update tableName="t_system_parameter" >
<column name="param_name" value="易道引擎AppKey" />
<where>id = 14</where>
</update>
<update tableName="t_system_parameter" >
<column name="param_name" value="易道引擎APPSecret" />
<where>id = 15</where>
</update>
</changeSet>
七、创建唯一索引
<changeSet id="createIndex.oauth_access_token.20190410" author="zwy">
<addUniqueConstraint tableName="oauth_access_token" columnNames="authentication_id" />
</changeSet>
八、创建唯一复合索引
<changeSet id="add.uniqueIndex.20190730" author="zwy">
<createIndex tableName="t_sample" indexName="u_index_sample_sample_set_id_source_name" unique="true">
<column name="sample_set_id"/>
<column name="source_name"/>
</createIndex>
</changeSet>
九、全局序列号
<changeSet id="add.seq" author="ggq">
<createSequence sequenceName="due_order_sequence" startValue="1" />
</changeSet>
十、增加数据
<changeSet id="addRow.zwy.20191016" author="zwy">
<insert tableName="base_menu">
<column name="id" value="254" />
<column name="menu_code" value="sampleSetSampleField" />
<column name="menu_name" value="样本字段" />
<column name="parent_menu_code" value="OnlineLabelSampleManagement" />
<column name="module_type" value="1" /> <!--1:标注平台 2:测试平台 -->
<column name="status" value="1" /><!--0:菜单栏的菜单 1:普通菜单 -->
</insert>
</changeSet>