dev/2020/2020-014(OK)/2020-014-xpp-001.sql

USE DCP;
/*生成表结构中数据列的定义*/
DROP TABLE `sys_table_attributes`;

CREATE TABLE `sys_table_attributes` AS (
SELECT
   CONCAT(information_schema.columns.TABLE_NAME,'-',information_schema.columns.COLUMN_NAME) AS  `id`,
  `information_schema`.`columns`.`TABLE_NAME`     AS  `table_attribute`,
  `information_schema`.`columns`.`COLUMN_NAME`    AS `column_attribute`,
  `information_schema`.`columns`.`COLUMN_COMMENT` AS `colunm_comment`,
  `information_schema`.`columns`.`DATA_TYPE`      AS `data_type`
FROM `information_schema`.`columns`
WHERE (`information_schema`.`columns`.`TABLE_SCHEMA` = 'dcp')
);

ALTER TABLE `dcp`.`sys_table_attributes`
  ADD  INDEX `idx_sys_table_attributes` (`table_attribute`);

ALTER TABLE `dcp`.`sys_table_attributes`
  ADD  INDEX `idx_sys_table_id` (`id`);

delete from `sys_table_attributes`;

insert into sys_table_attributes
SELECT
   CONCAT(information_schema.columns.TABLE_NAME,'-',information_schema.columns.COLUMN_NAME) AS  `id`,
  `information_schema`.`columns`.`TABLE_NAME`     AS  `table_attribute`,
  `information_schema`.`columns`.`COLUMN_NAME`    AS `column_attribute`,
  `information_schema`.`columns`.`COLUMN_COMMENT` AS `colunm_comment`,
  `information_schema`.`columns`.`DATA_TYPE`      AS `data_type`
FROM `information_schema`.`columns`
WHERE (`information_schema`.`columns`.`TABLE_SCHEMA` = 'dcp');

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_data_change_log
-- ----------------------------
DROP TABLE IF EXISTS `sys_data_change_log`;
CREATE TABLE `sys_data_change_log`  (
  `CHANGE_LOG_ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '变动日志ID',
  `CHANGE_LOG_TYPE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '0新增1修改2删除',
  `apply_info_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '申请单id',
  `BIZ_OBJECT_ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务对象ID',
  `BIZ_OBJECT_TYPE` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务对象类型',
  `OPERATE_NAME` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '操作说明',
  `BIZ_BUSINESS_ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务ID',
  `BIZ_BUSINESS_TYPE` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务类型',
  `BIZ_FIELD` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '数据字段',
  `BIZ_FIELD_NAME` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '数据字段名称',
  `PRE_VALUE` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '原值',
  `PRE_DESCRIPTION` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '原值描述',
  `TARGET_VALUE` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '目标值',
  `TARGET_DESCRIPTION` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '目标值描述',
  `CHANGEED_DATA_ITEM` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '被更改过的数据项',
  `CHANGE_FLAG` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '变更状态',
  `AUDIT_FLAG` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '审批状态',
  `AUDIT_USER_ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '审批人',
  `AUDIT_TIME` datetime(0) DEFAULT NULL COMMENT '审批时间',
  `CREATE_BY` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人员ID',
  `CREATE_DATE` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `UPDATE_BY` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '更新人',
  `UPDATE_DATE` datetime(0) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`CHANGE_LOG_ID`) USING BTREE,
  INDEX `IDX_SYS_CHANGE_LOG_MISC`(`BIZ_OBJECT_TYPE`, `BIZ_BUSINESS_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统数据更改日志记录表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


-- 已执行159、161环境