数科技术流程规范-数据库DDL-V1.0

数据库DDL统一使用liquibase进行更新，只允许更新表结构（增加字段）和新建表，不允许删除字段、修改字段名，数据的增删改通过a8进行。

集成示例，我们以paas-micro-order为例

• 在paas-micro-order/admin的pom.xml中加入liquibase依赖，具体如下：

		<dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>4.16.0</version>
        </dependency>

• 在paas-micro-order/admin的config中增加类(LiquibaseConfiguration.java)，具体如下：

package com.ebuytech.paas.micro.order.config;

import liquibase.integration.spring.SpringLiquibase;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * <pre>
 *
 * </pre>
 *
 * @author jiangjin
 * @version $Id: object.java, v 0.1 2023/10/13 下午3:04 jiangjin Exp $$
 */
@Configuration
public class LiquibaseConfiguration {

    @Autowired
    private ApplicationContext springContext;

    @Bean
    public SpringLiquibase dsLiquibase() {
        return liquibase("ds");
    }

    @Bean
    public SpringLiquibase ds0Liquibase() {

        return liquibase("ds0");
    }

    public SpringLiquibase liquibase(String dsName) {
        SpringLiquibase liquibase = new SpringLiquibase();
        DataSource ds = this.springContext.getBean(dsName, DataSource.class);
        // Liquibase文件路径
        liquibase.setChangeLog("classpath:db/changelog-master.xml");
        liquibase.setDataSource(ds);
        liquibase.setShouldRun(true);
        return liquibase;
    }
}

• 在paas-micro-order/admin的src/main/resources中增加文件db/changelog-master-ds.xml,db/changelog-master-ds0.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.1.xsd">

    <include file="classpath:/db/changelog_ds.sql"/>
</databaseChangeLog>

• changelog_ds.sql的示例如下：

-- liquibase formatted sql

-- changeset jiangjin:2023-1013-0958
alter table order_rule add column test varchar(10);

-- changeset jiangjin:2023-1016-0958
alter table order_rule drop column test;

-- changeset jiangjin:2023-1016-1133
alter table order_rule add column test2 varchar(10);

-- changeset jiangjin:2023-1016-1303
alter table order_rule drop column test2;