calendar-center.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128
  1. CREATE DATABASE IF NOT EXISTS `calendar-center` DEFAULT CHARACTER SET = utf8;
  2. Use `calendar-center`;
  3. SET NAMES utf8mb4;
  4. SET FOREIGN_KEY_CHECKS = 0;
  5. -- ----------------------------
  6. -- Table structure for work_calendar
  7. -- ----------------------------
  8. DROP TABLE IF EXISTS `work_calendar`;
  9. CREATE TABLE `work_calendar` (
  10. `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  11. `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'defaultId' COMMENT '项目ID',
  12. `CALENDAR_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '同一PROJECT_ID下唯一,代码判断',
  13. `CALENDAR_DESC` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历描述',
  14. `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  15. `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  16. `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  17. `DEL_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-正常;1-删除',
  18. `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  19. PRIMARY KEY (`ID`) USING BTREE,
  20. INDEX `NK_CALENDAR_GROUP_AND_NAME`(`DEL_FLAG`, `PROJECT_ID`, `CALENDAR_NAME`) USING BTREE
  21. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历主表' ROW_FORMAT = Dynamic;
  22. -- ----------------------------
  23. -- Table structure for work_calendar_date
  24. -- ----------------------------
  25. DROP TABLE IF EXISTS `work_calendar_date`;
  26. CREATE TABLE `work_calendar_date` (
  27. `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ID',
  28. `RULE_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则ID',
  29. `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  30. `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  31. `CALENDAR_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历名称',
  32. `CALENDAR_DESC` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历描述',
  33. `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  34. `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  35. `WORK_DATE_START` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新版YYYYMMDD,新版中和规则同步;老版YYYYMMDDHHMMSS,老版中和规则没有关系',
  36. `WORK_DATE_END` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新版YYYYMMDD,新版中和规则同步;老版YYYYMMDDHHMMSS,老版中和规则没有关系',
  37. `WORK_DATE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '具体执行日期,新接口YYYYMMDD',
  38. `WORK_TIME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '具体时间段',
  39. `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  40. `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  41. `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  42. `CUSTOM_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-非自定义;1-自定义,默认0',
  43. `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  44. INDEX `NK_CALENDAR_ID`(`CALENDAR_ID`, `PROJECT_ID`) USING BTREE COMMENT '工作历ID,普通索引',
  45. INDEX `NK_WORK_FROM_AND_TO`(`WORK_DATE_START`, `WORK_DATE_END`) USING BTREE COMMENT '日期的联合索引',
  46. INDEX `NK_WORK_CALENDAR_WORK_DATE`(`WORK_DATE`) USING BTREE COMMENT '当天日期普通索引'
  47. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历作息时间表,此表只有新增,无修改[新版]' ROW_FORMAT = Dynamic;
  48. -- ----------------------------
  49. -- Table structure for work_calendar_dict
  50. -- ----------------------------
  51. DROP TABLE IF EXISTS `work_calendar_dict`;
  52. CREATE TABLE `work_calendar_dict` (
  53. `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  54. `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工作历ID',
  55. `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  56. `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  57. `DICT_DESC` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型描述',
  58. UNIQUE INDEX `UK_DICT_CALENDAR_TYPE`(`DICT_TYPE`, `CALENDAR_ID`, `DICT_DESC`) USING BTREE
  59. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '工作历字典表,后台维护,没有前端' ROW_FORMAT = Dynamic;
  60. -- ----------------------------
  61. -- Table structure for work_calendar_label
  62. -- ----------------------------
  63. DROP TABLE IF EXISTS `work_calendar_label`;
  64. CREATE TABLE `work_calendar_label` (
  65. `ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  66. `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  67. `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  68. `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  69. `LABEL_DATE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'label所属日期',
  70. `DICT_TYPE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  71. `DICT_CODE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  72. `DIY_LABEL` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '0-非自定义,1-自定义',
  73. `DIY_VALUE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自定义时对应的标签集合',
  74. `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  75. `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  76. `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  77. PRIMARY KEY (`ID`) USING BTREE,
  78. INDEX `NK_LABEL_CALENDAR_ID_PROJECT`(`CALENDAR_ID`, `PROJECT_ID`) USING BTREE,
  79. INDEX `NK_LABEL_LABEL_DATE`(`LABEL_DATE`) USING BTREE
  80. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  81. -- ----------------------------
  82. -- Table structure for work_calendar_object
  83. -- ----------------------------
  84. DROP TABLE IF EXISTS `work_calendar_object`;
  85. CREATE TABLE `work_calendar_object` (
  86. `GROUP_CODE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  87. `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  88. `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目ID',
  89. `OBJECT_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '对象ID',
  90. `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  91. `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作用户ID',
  92. UNIQUE INDEX `UK_OBJECT_OBJECT_ID`(`OBJECT_ID`, `PROJECT_ID`) USING BTREE COMMENT '对象ID索引',
  93. INDEX `NK_OBJECT_CALENDAR_ID`(`CALENDAR_ID`) USING BTREE COMMENT '工作历ID索引'
  94. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  95. -- ----------------------------
  96. -- Table structure for work_calendar_rule
  97. -- ----------------------------
  98. DROP TABLE IF EXISTS `work_calendar_rule`;
  99. CREATE TABLE `work_calendar_rule` (
  100. `ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则ID',
  101. `CALENDAR_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作历ID',
  102. `PROJECT_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '项目ID',
  103. `DICT_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典类型',
  104. `DICT_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字典数据的code',
  105. `RULE_DATE_START` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的开始日期YYYYMMDD',
  106. `RULE_DATE_END` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的结束日期YYYYMMDD',
  107. `RULE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规则生效的具体时间点HHMMSS',
  108. `CREATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建时间',
  109. `UPDATE_TIME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新时间',
  110. `UPDATE_USER` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '最后操作者ID',
  111. `DEL_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-正常;1-删除',
  112. `RULE_FLAG` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '0-新接口规则;1-老接口规则,方便后续解析',
  113. `VALUE` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '老版服务接口',
  114. `GROUP_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '集团编码',
  115. PRIMARY KEY (`ID`) USING BTREE,
  116. INDEX `NK_PARSE_RULE_INDEX`(`DEL_FLAG`, `RULE_DATE_END`, `RULE_DATE_START`) USING BTREE COMMENT '快速查找',
  117. INDEX `NK_RULE_CALENDAR_ID`(`DEL_FLAG`, `CALENDAR_ID`) USING BTREE COMMENT '工作历ID,普通索引',
  118. INDEX `NK_CALENDAR_RULE_TYPE`(`PROJECT_ID`, `DICT_TYPE`, `DICT_CODE`) USING BTREE COMMENT '同项目下类型和code唯一'
  119. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作历规则表' ROW_FORMAT = Dynamic;
  120. SET FOREIGN_KEY_CHECKS = 1;