calendar-center/doc/calendar-center.sql

CREATE DATABASE IF NOT EXISTS `calendar-center` DEFAULT CHARACTER SET = utf8;
Use `calendar-center`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for work_calendar
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar`;
CREATE TABLE `work_calendar`  (
  `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'defaultId' COMMENT '项目ID',
  `CALENDAR_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '同一PROJECT_ID下唯一，代码判断',
  `CALENDAR_DESC` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历描述',
  `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  `DEL_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-正常；1-删除',
  `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NK_CALENDAR_GROUP_AND_NAME`(`DEL_FLAG`, `PROJECT_ID`, `CALENDAR_NAME`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for work_calendar_date
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar_date`;
CREATE TABLE `work_calendar_date`  (
  `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ID',
  `RULE_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则ID',
  `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  `CALENDAR_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历名称',
  `CALENDAR_DESC` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历描述',
  `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  `WORK_DATE_START` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新版YYYYMMDD，新版中和规则同步；老版YYYYMMDDHHMMSS,老版中和规则没有关系',
  `WORK_DATE_END` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新版YYYYMMDD，新版中和规则同步；老版YYYYMMDDHHMMSS,老版中和规则没有关系',
  `WORK_DATE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '具体执行日期，新接口YYYYMMDD',
  `WORK_TIME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '具体时间段',
  `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  `CUSTOM_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-非自定义；1-自定义，默认0',
  `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  INDEX `NK_CALENDAR_ID`(`CALENDAR_ID`, `PROJECT_ID`) USING BTREE COMMENT '工作历ID，普通索引',
  INDEX `NK_WORK_FROM_AND_TO`(`WORK_DATE_START`, `WORK_DATE_END`) USING BTREE COMMENT '日期的联合索引',
  INDEX `NK_WORK_CALENDAR_WORK_DATE`(`WORK_DATE`) USING BTREE COMMENT '当天日期普通索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历作息时间表，此表只有新增，无修改[新版]' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for work_calendar_dict
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar_dict`;
CREATE TABLE `work_calendar_dict`  (
  `ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID，集团级别的字典，此值为0',
  `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历ID，项目级别的字典，此值为0',
  `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型的code',
  `DICT_DESC` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型描述',
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NK_DICT_GROUP_PROJECT_CALENDAR`(`GROUP_CODE`, `PROJECT_ID`, `CALENDAR_ID`) USING BTREE,
  INDEX `NK_DICT_TYPE_CODE`(`DICT_TYPE`, `DICT_CODE`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '工作历字典表，后台维护，没有前端' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for work_calendar_label
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar_label`;
CREATE TABLE `work_calendar_label`  (
  `ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  `LABEL_DATE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'label所属日期',
  `DICT_TYPE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  `DICT_CODE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  `DIY_LABEL` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '0-非自定义，1-自定义',
  `DIY_VALUE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自定义时对应的标签集合',
  `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NK_LABEL_CALENDAR_ID_PROJECT`(`CALENDAR_ID`, `PROJECT_ID`) USING BTREE,
  INDEX `NK_LABEL_LABEL_DATE`(`LABEL_DATE`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for work_calendar_object
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar_object`;
CREATE TABLE `work_calendar_object`  (
  `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目ID',
  `OBJECT_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '对象ID',
  `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  UNIQUE INDEX `UK_OBJECT_OBJECT_ID`(`OBJECT_ID`, `PROJECT_ID`) USING BTREE COMMENT '对象ID索引',
  INDEX `NK_OBJECT_CALENDAR_ID`(`CALENDAR_ID`) USING BTREE COMMENT '工作历ID索引'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for work_calendar_rule
-- ----------------------------
DROP TABLE IF EXISTS `work_calendar_rule`;
CREATE TABLE `work_calendar_rule`  (
  `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则ID',
  `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  `RULE_DATE_START` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的开始日期YYYYMMDD',
  `RULE_DATE_END` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的结束日期YYYYMMDD',
  `RULE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的具体时间点HHMMSS',
  `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '最后操作者ID',
  `DEL_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-正常；1-删除',
  `RULE_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-新接口规则；1-老接口规则，方便后续解析',
  `VALUE` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '老版服务接口',
  `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NK_PARSE_RULE_INDEX`(`DEL_FLAG`, `RULE_DATE_END`, `RULE_DATE_START`) USING BTREE COMMENT '快速查找',
  INDEX `NK_RULE_CALENDAR_ID`(`DEL_FLAG`, `CALENDAR_ID`) USING BTREE COMMENT '工作历ID，普通索引',
  INDEX `NK_CALENDAR_RULE_TYPE`(`PROJECT_ID`, `DICT_TYPE`, `DICT_CODE`) USING BTREE COMMENT '同项目下类型和code唯一'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历规则表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;