架构设计第七讲：数据巡检系统之daily&线上表结构自动化比对

alter table finance_sub_order_info modify total_receive decimal(19,3) default 0.000 not null comment '待删除';
alter table finance_sub_order_info modify total_pay decimal(19,3) default 0.000 not null comment '待删除';
alter table finance_sub_order_info modify total_cost decimal(19,3) default 0.000 not null comment '待删除';
alter table finance_fare_info modify img text null comment '附件图片';
alter table finance_bill drop column bill_amount;
alter table finance_sub_order_settlement drop column price;
alter table finance_sub_order_settlement drop column business_types;
alter table finance_sub_order_settlement drop column invalid_state;
alter table finance_sub_order_settlement drop column record_user;
alter table finance_sub_order_settlement drop column can_settlement;
alter table finance_sub_order_settlement drop column create_user;

confirm_time            datetime           null comment '确认时间',
confirm_user            bigint                null comment '确认人',

invoice_code   varchar(128)      null comment '发票号码数组，以逗号分割',

team_state              int         default 0                    not null comment '协作状态 0非协作费用 1协作费用',

settlement_owned_type      tinyint        default 0                 not null comment '1 自营  2-外协',
settlement_entity_id       bigint         default 0                 not null comment '结算实体id',
settlement_entity_classify tinyint        default 0                 not null comment '结算实体类型,1-司机 2-企业id 3-车队id ',

-- daily
create index idx_tenantid_settlementtype_invalidstate
    on falcon_convoy.finance_sub_order_settlement (tenant_id, settlement_type);
-- 线上
create index idx_tenantid_settlementtype_invalidstate on falcon_convoy.finance_sub_order_settlement (tenant_id, settlement_type, invalid_state);
-- todo 索引名称需要修改

-- daily
create index idx_tenantid_settlementtype_invalidstate
    on falcon_convoy.finance_sub_order_settlement (tenant_id, settlement_type);
-- 线上
create index idx_tenantid_settlementtype_invalidstate on falcon_convoy.finance_sub_order_settlement (tenant_id, settlement_type, invalid_state);

datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    first:
      url: ${huxun.datasource.url}
      username: ${huxun.datasource.username}
      password: ${huxun.datasource.password}
    second:
      url: ${huxun.datasource.daily.url}
      username: ${huxun.datasource.daily.username}
      password: ${huxun.datasource.daily.password}

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        DataSourceType.DataBaseType dataBaseType = DataSourceType.getDataBaseType();
        return dataBaseType;
    }
}

public class DataSourceType {
    public enum DataBaseType {
        //默认数据库
        FIRST,
        SECOND;
    }
    // 使用ThreadLocal保证线程安全
    private static final ThreadLocal<DataBaseType> TYPE = new ThreadLocal<DataBaseType>();
    // 往当前线程里设置数据源类型
    public static void setDataBaseType(DataBaseType dataBaseType) {
        if (dataBaseType == null) {
            throw new NullPointerException();
        }
        System.out.println("[将当前数据源改为]：" + dataBaseType);
        TYPE.set(dataBaseType);
    }
    // 获取数据源类型
    public static DataBaseType getDataBaseType() {
        DataBaseType dataBaseType = TYPE.get() == null ? DataBaseType.FIRST : TYPE.get();
        System.out.println("[获取当前数据源的类型为]：" + dataBaseType);
        return dataBaseType;
    }
    // 清空数据类型（清理时机不好掌控，且目前ThreadLocal只存在一个值，不清理也没影响）
    public static void clearDataBaseType() {
        TYPE.remove();
    }
}

@Configuration
@MapperScan(basePackages = {"com.huxun.inspection.mapper"}, sqlSessionFactoryRef = "SqlSessionFactory")
public class DruidConfig {
    @Bean(name = "firstDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.first")
    public DataSource firstDataSource(){
        return DruidDataSourceBuilder
                .create()
                .build();
    }
    @Bean(name = "secondDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.second")
    public DataSource secondDataSource(){
        return DruidDataSourceBuilder
                .create()
                .build();
    }
    @Bean(name = "dynamicDataSource")
    public DynamicDataSource DataSource(@Qualifier("firstDataSource") DataSource test1DataSource,
                                        @Qualifier("secondDataSource") DataSource test2DataSource) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DataSourceType.DataBaseType.FIRST, test1DataSource);
        targetDataSource.put(DataSourceType.DataBaseType.SECOND, test2DataSource);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSource);
        dataSource.setDefaultTargetDataSource(test1DataSource);
        return dataSource;
    }
    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dynamicDataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
        return bean.getObject();
    }
}

@Aspect
@Component
public class DataSourceAspect {
    @Before("execution(* com.huxun.inspection.mapper..Daily*.*(..))")
    public void setDataSource2test01() {
        System.err.println("读取第二个数据源");
        DataSourceType.setDataBaseType(DataSourceType.DataBaseType.SECOND);
    }
    @Before("execution(* com.huxun.inspection.mapper..*.*(..)) && !execution(* com.huxun.inspection.mapper..Daily*.*(..))")
    public void setDataSource2test02() {
        System.err.println("读取第一个数据源");
        DataSourceType.setDataBaseType(DataSourceType.DataBaseType.FIRST);
    }
}

CREATE TABLE IF NOT EXISTS `table_diff_inspection`(
    `id`           bigint             unsigned auto_increment comment '主键id' primary key,
    `biz_id`       bigint             not null comment '业务id',
    `batch_id`     bigint             not null comment '批次id',
    `status`       tinyint(1)         default 0  not null comment '状态,0-待确认,1-确认',
    `key_field_json` longtext         not null comment '业务关键字段数据',
    `diff_type`      tinyint          null comment '差异类别 (0-create、1-update、2-delete)',
    `db_name`    varchar(50)          not null COMMENT '库名',
    `group_name`    varchar(50)            not null COMMENT '处理人',
    `create_user`   bigint            not null comment '创建人',
    `update_user`   bigint            null comment '更新人',
    `create_time` datetime default CURRENT_TIMESTAMP not null comment '创建时间',
    `update_time`   datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
) DEFAULT CHARACTER SET = utf8mb4 COMMENT = 'table差异巡检表';
create index idx_batchId_bizIds on falcon_inspection.falcon_table_diff (batch_id, biz_id);

/**
 * 创建人
 */
private Long createUser;
/**
 * 更新人
 */
private Long updateUser;
/**
 * 创建时间
 */
private Date createTime;
/**
 * 更新时间
 */
private Date updateTime;