zkt_alarm_tables_mysql_innodb.sql 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192
  1. #
  2. # In your Quartz properties file, you'll need to set
  3. # org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
  4. #
  5. #
  6. # By: Ron Cordell - roncordell
  7. # I didn't see this anywhere, so I thought I'd post it here. This is the script from Quartz to create the tables in a MySQL database, modified to use INNODB instead of MYISAM.
  8. DROP TABLE IF EXISTS ZKT_ALARM_FIRED_TRIGGERS;
  9. DROP TABLE IF EXISTS ZKT_ALARM_PAUSED_TRIGGER_GRPS;
  10. DROP TABLE IF EXISTS ZKT_ALARM_SCHEDULER_STATE;
  11. DROP TABLE IF EXISTS ZKT_ALARM_LOCKS;
  12. DROP TABLE IF EXISTS ZKT_ALARM_SIMPLE_TRIGGERS;
  13. DROP TABLE IF EXISTS ZKT_ALARM_SIMPROP_TRIGGERS;
  14. DROP TABLE IF EXISTS ZKT_ALARM_CRON_TRIGGERS;
  15. DROP TABLE IF EXISTS ZKT_ALARM_BLOB_TRIGGERS;
  16. DROP TABLE IF EXISTS ZKT_ALARM_TRIGGERS;
  17. DROP TABLE IF EXISTS ZKT_ALARM_JOB_DETAILS;
  18. DROP TABLE IF EXISTS ZKT_ALARM_CALENDARS;
  19. DROP TABLE IF EXISTS `zkt_alarm_record`;
  20. CREATE TABLE ZKT_ALARM_JOB_DETAILS(
  21. SCHED_NAME VARCHAR(120) NOT NULL,
  22. JOB_NAME VARCHAR(190) NOT NULL,
  23. JOB_GROUP VARCHAR(190) NOT NULL,
  24. DESCRIPTION VARCHAR(250) NULL,
  25. JOB_CLASS_NAME VARCHAR(250) NOT NULL,
  26. IS_DURABLE VARCHAR(1) NOT NULL,
  27. IS_NONCONCURRENT VARCHAR(1) NOT NULL,
  28. IS_UPDATE_DATA VARCHAR(1) NOT NULL,
  29. REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
  30. JOB_DATA BLOB NULL,
  31. PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP))
  32. ENGINE=InnoDB;
  33. CREATE TABLE ZKT_ALARM_TRIGGERS (
  34. SCHED_NAME VARCHAR(120) NOT NULL,
  35. TRIGGER_NAME VARCHAR(190) NOT NULL,
  36. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  37. JOB_NAME VARCHAR(190) NOT NULL,
  38. JOB_GROUP VARCHAR(190) NOT NULL,
  39. DESCRIPTION VARCHAR(250) NULL,
  40. NEXT_FIRE_TIME BIGINT(13) NULL,
  41. PREV_FIRE_TIME BIGINT(13) NULL,
  42. PRIORITY INTEGER NULL,
  43. TRIGGER_STATE VARCHAR(16) NOT NULL,
  44. TRIGGER_TYPE VARCHAR(8) NOT NULL,
  45. START_TIME BIGINT(13) NOT NULL,
  46. END_TIME BIGINT(13) NULL,
  47. CALENDAR_NAME VARCHAR(190) NULL,
  48. MISFIRE_INSTR SMALLINT(2) NULL,
  49. JOB_DATA BLOB NULL,
  50. PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  51. FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
  52. REFERENCES ZKT_ALARM_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP))
  53. ENGINE=InnoDB;
  54. CREATE TABLE ZKT_ALARM_SIMPLE_TRIGGERS (
  55. SCHED_NAME VARCHAR(120) NOT NULL,
  56. TRIGGER_NAME VARCHAR(190) NOT NULL,
  57. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  58. REPEAT_COUNT BIGINT(7) NOT NULL,
  59. REPEAT_INTERVAL BIGINT(12) NOT NULL,
  60. TIMES_TRIGGERED BIGINT(10) NOT NULL,
  61. PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  62. FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  63. REFERENCES ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
  64. ENGINE=InnoDB;
  65. CREATE TABLE ZKT_ALARM_CRON_TRIGGERS (
  66. SCHED_NAME VARCHAR(120) NOT NULL,
  67. TRIGGER_NAME VARCHAR(190) NOT NULL,
  68. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  69. CRON_EXPRESSION VARCHAR(120) NOT NULL,
  70. TIME_ZONE_ID VARCHAR(80),
  71. PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  72. FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  73. REFERENCES ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
  74. ENGINE=InnoDB;
  75. CREATE TABLE ZKT_ALARM_SIMPROP_TRIGGERS
  76. (
  77. SCHED_NAME VARCHAR(120) NOT NULL,
  78. TRIGGER_NAME VARCHAR(190) NOT NULL,
  79. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  80. STR_PROP_1 VARCHAR(512) NULL,
  81. STR_PROP_2 VARCHAR(512) NULL,
  82. STR_PROP_3 VARCHAR(512) NULL,
  83. INT_PROP_1 INT NULL,
  84. INT_PROP_2 INT NULL,
  85. LONG_PROP_1 BIGINT NULL,
  86. LONG_PROP_2 BIGINT NULL,
  87. DEC_PROP_1 NUMERIC(13,4) NULL,
  88. DEC_PROP_2 NUMERIC(13,4) NULL,
  89. BOOL_PROP_1 VARCHAR(1) NULL,
  90. BOOL_PROP_2 VARCHAR(1) NULL,
  91. PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  92. FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  93. REFERENCES ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
  94. ENGINE=InnoDB;
  95. CREATE TABLE ZKT_ALARM_BLOB_TRIGGERS (
  96. SCHED_NAME VARCHAR(120) NOT NULL,
  97. TRIGGER_NAME VARCHAR(190) NOT NULL,
  98. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  99. BLOB_DATA BLOB NULL,
  100. PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  101. INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
  102. FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  103. REFERENCES ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
  104. ENGINE=InnoDB;
  105. CREATE TABLE ZKT_ALARM_CALENDARS (
  106. SCHED_NAME VARCHAR(120) NOT NULL,
  107. CALENDAR_NAME VARCHAR(190) NOT NULL,
  108. CALENDAR BLOB NOT NULL,
  109. PRIMARY KEY (SCHED_NAME,CALENDAR_NAME))
  110. ENGINE=InnoDB;
  111. CREATE TABLE ZKT_ALARM_PAUSED_TRIGGER_GRPS (
  112. SCHED_NAME VARCHAR(120) NOT NULL,
  113. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  114. PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP))
  115. ENGINE=InnoDB;
  116. CREATE TABLE ZKT_ALARM_FIRED_TRIGGERS (
  117. SCHED_NAME VARCHAR(120) NOT NULL,
  118. ENTRY_ID VARCHAR(95) NOT NULL,
  119. TRIGGER_NAME VARCHAR(190) NOT NULL,
  120. TRIGGER_GROUP VARCHAR(190) NOT NULL,
  121. INSTANCE_NAME VARCHAR(190) NOT NULL,
  122. FIRED_TIME BIGINT(13) NOT NULL,
  123. SCHED_TIME BIGINT(13) NOT NULL,
  124. PRIORITY INTEGER NOT NULL,
  125. STATE VARCHAR(16) NOT NULL,
  126. JOB_NAME VARCHAR(190) NULL,
  127. JOB_GROUP VARCHAR(190) NULL,
  128. IS_NONCONCURRENT VARCHAR(1) NULL,
  129. REQUESTS_RECOVERY VARCHAR(1) NULL,
  130. PRIMARY KEY (SCHED_NAME,ENTRY_ID))
  131. ENGINE=InnoDB;
  132. CREATE TABLE ZKT_ALARM_SCHEDULER_STATE (
  133. SCHED_NAME VARCHAR(120) NOT NULL,
  134. INSTANCE_NAME VARCHAR(190) NOT NULL,
  135. LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
  136. CHECKIN_INTERVAL BIGINT(13) NOT NULL,
  137. PRIMARY KEY (SCHED_NAME,INSTANCE_NAME))
  138. ENGINE=InnoDB;
  139. CREATE TABLE ZKT_ALARM_LOCKS (
  140. SCHED_NAME VARCHAR(120) NOT NULL,
  141. LOCK_NAME VARCHAR(40) NOT NULL,
  142. PRIMARY KEY (SCHED_NAME,LOCK_NAME))
  143. ENGINE=InnoDB;
  144. CREATE INDEX IDX_ZKT_ALARM_J_REQ_RECOVERY ON ZKT_ALARM_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY);
  145. CREATE INDEX IDX_ZKT_ALARM_J_GRP ON ZKT_ALARM_JOB_DETAILS(SCHED_NAME,JOB_GROUP);
  146. CREATE INDEX IDX_ZKT_ALARM_T_J ON ZKT_ALARM_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
  147. CREATE INDEX IDX_ZKT_ALARM_T_JG ON ZKT_ALARM_TRIGGERS(SCHED_NAME,JOB_GROUP);
  148. CREATE INDEX IDX_ZKT_ALARM_T_C ON ZKT_ALARM_TRIGGERS(SCHED_NAME,CALENDAR_NAME);
  149. CREATE INDEX IDX_ZKT_ALARM_T_G ON ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
  150. CREATE INDEX IDX_ZKT_ALARM_T_STATE ON ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_STATE);
  151. CREATE INDEX IDX_ZKT_ALARM_T_N_STATE ON ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
  152. CREATE INDEX IDX_ZKT_ALARM_T_N_G_STATE ON ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
  153. CREATE INDEX IDX_ZKT_ALARM_T_NEXT_FIRE_TIME ON ZKT_ALARM_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME);
  154. CREATE INDEX IDX_ZKT_ALARM_T_NFT_ST ON ZKT_ALARM_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
  155. CREATE INDEX IDX_ZKT_ALARM_T_NFT_MISFIRE ON ZKT_ALARM_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
  156. CREATE INDEX IDX_ZKT_ALARM_T_NFT_ST_MISFIRE ON ZKT_ALARM_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
  157. CREATE INDEX IDX_ZKT_ALARM_T_NFT_ST_MISFIRE_GRP ON ZKT_ALARM_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
  158. CREATE INDEX IDX_ZKT_ALARM_FT_TRIG_INST_NAME ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME);
  159. CREATE INDEX IDX_ZKT_ALARM_FT_INST_JOB_REQ_RCVRY ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
  160. CREATE INDEX IDX_ZKT_ALARM_FT_J_G ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
  161. CREATE INDEX IDX_ZKT_ALARM_FT_JG ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP);
  162. CREATE INDEX IDX_ZKT_ALARM_FT_T_G ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
  163. CREATE INDEX IDX_ZKT_ALARM_FT_TG ON ZKT_ALARM_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
  164. CREATE TABLE `zkt_alarm_record` (
  165. `definition_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '报警定义ID',
  166. `item_code` varchar(42) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '报警条目编码',
  167. `obj_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '报警对象ID',
  168. `state` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警定义对应最新一条报警记录状态',
  169. `alarm_id` varchar(42) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警定义对应最新一条报警记录ID',
  170. `alarm_time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警时间',
  171. `effect_end_time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警生效结束时间(报警过期时间)',
  172. `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  173. `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警名称',
  174. PRIMARY KEY (`definition_id`) USING BTREE
  175. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '报警记录ID' ROW_FORMAT = Dynamic;
  176. commit;