1.sql 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182
  1. create table if not exists db_version(
  2. id BIGSERIAL PRIMARY KEY,
  3. version varchar(100) not null,
  4. status int not null default(1)
  5. );
  6. INSERT INTO db_version (id, version, status)
  7. VALUES (1, '20261126', 0)
  8. ON CONFLICT (id)
  9. DO UPDATE SET
  10. version = EXCLUDED.version,
  11. status = EXCLUDED.status;
  12. -- 告警模板表
  13. DROP TABLE IF EXISTS alarm_event_template;
  14. CREATE TABLE alarm_event_template (
  15. id BIGSERIAL PRIMARY KEY,
  16. level INT NOT NULL,
  17. template_name VARCHAR(100) NOT NULL,
  18. category VARCHAR(50),
  19. content_format VARCHAR(500),
  20. color_code VARCHAR(20),
  21. is_active SMALLINT NOT NULL DEFAULT 1,
  22. enable_flag BOOLEAN NOT NULL DEFAULT TRUE,
  23. del_flag BOOLEAN NOT NULL DEFAULT FALSE,
  24. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  25. create_by VARCHAR(32),
  26. update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  27. update_by VARCHAR(32)
  28. );
  29. -- 添加注释
  30. COMMENT ON TABLE alarm_event_template IS '告警模板表';
  31. COMMENT ON COLUMN alarm_event_template.id IS '主键ID';
  32. COMMENT ON COLUMN alarm_event_template.level IS '告警级别(1-紧急,2-重要,3-次要,4-提示)';
  33. COMMENT ON COLUMN alarm_event_template.template_name IS '模板名称';
  34. COMMENT ON COLUMN alarm_event_template.category IS '告警分类(如:数据异常、设备故障、预测偏差)';
  35. COMMENT ON COLUMN alarm_event_template.content_format IS '内容模板(支持占位符,如:{station_name}发生{metric}偏差)';
  36. COMMENT ON COLUMN alarm_event_template.color_code IS 'UI展示颜色(如:#FF0000)';
  37. COMMENT ON COLUMN alarm_event_template.is_active IS '是否有效(1-有效,0-无效)';
  38. COMMENT ON COLUMN alarm_event_template.create_time IS '创建时间';
  39. COMMENT ON COLUMN alarm_event_template.update_time IS '更新时间';
  40. INSERT INTO alarm_event_template VALUES (1, 2, '设备通信中断', 'DEVICE FAULT', '{device_name} 设备通信断开,发生时间 {occur_time}', NULL, true, false, '2026-11-18 16:36:13.831302', 'admin', '2026-11-18 16:36:13.831302', 'admin');
  41. INSERT INTO alarm_event_template VALUES (2, 2, '数据越限异常', 'DATA EXCEPTION', '{point_name} 设备通信断开,发生时间 {occur_time}', NULL, true, false, '2026-11-18 16:36:13.831302', 'admin', '2026-11-18 16:36:13.831302', 'admin');
  42. INSERT INTO alarm_event_template VALUES (3, 3, '传输异常', 'TRANSMIT EXCEPTION', '{channel_name}通道传输失败,发生时间 {occur_time}', NULL, true, false, '2026-11-18 16:36:13.831302', 'admin', '2026-11-18 16:36:13.831302', 'admin');
  43. -- 事件规则表
  44. DROP TABLE IF EXISTS alarm_event_rule;
  45. CREATE TABLE alarm_event_rule (
  46. id BIGSERIAL PRIMARY KEY,
  47. name VARCHAR(50) NOT NULL,
  48. alarm_template_id BIGINT NOT NULL,
  49. rule_code VARCHAR(50) NOT NULL,
  50. condition_json JSONB,
  51. station_id BIGINT DEFAULT 0,
  52. priority INT DEFAULT 0,
  53. remark VARCHAR(255),
  54. need_sent BOOLEAN NOT NULL DEFAULT FALSE,
  55. send_set JSONB,
  56. send_user JSONB,
  57. need_popup BOOLEAN NOT NULL DEFAULT FALSE,
  58. enable_flag BOOLEAN NOT NULL DEFAULT TRUE,
  59. del_flag BOOLEAN NOT NULL DEFAULT FALSE,
  60. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  61. create_by VARCHAR(32),
  62. update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  63. update_by VARCHAR(32),
  64. CONSTRAINT fk_rule_template FOREIGN KEY (alarm_template_id) REFERENCES alarm_event_template(id)
  65. );
  66. -- 添加索引
  67. CREATE INDEX idx_rule_station ON alarm_event_rule(station_id);
  68. CREATE UNIQUE INDEX idx_rule_code ON alarm_event_rule(rule_code);
  69. -- 添加注释
  70. COMMENT ON TABLE alarm_event_rule IS '事件规则表';
  71. COMMENT ON COLUMN alarm_event_rule.id IS '主键ID';
  72. COMMENT ON COLUMN alarm_event_rule.name IS '规则名称(与程序中的事件名关联)';
  73. COMMENT ON COLUMN alarm_event_rule.alarm_template_id IS '关联模板ID';
  74. COMMENT ON COLUMN alarm_event_rule.rule_code IS '规则编码(唯一标识,如:PREDICT_DEV_01)';
  75. COMMENT ON COLUMN alarm_event_rule.condition_json IS '触发条件(JSONB存储:如{"metric":"deviation", "operator":">", "value":0.2, "duration":300})';
  76. COMMENT ON COLUMN alarm_event_rule.station_id IS '适用电站(0表示全局规则,非0表示特定电站规则)';
  77. COMMENT ON COLUMN alarm_event_rule.priority IS '优先级(用于规则冲突时的处理)';
  78. COMMENT ON COLUMN alarm_event_rule.remark IS '规则描述';
  79. COMMENT ON COLUMN alarm_event_rule.need_sent IS '是否要推送';
  80. COMMENT ON COLUMN alarm_event_rule.send_set IS 'value:“NO”,label:“不推送”,value:“EMAIL”,label:“邮件”,value:“SMS”,label:“短信”,';
  81. COMMENT ON COLUMN alarm_event_rule.send_user IS '推送用户信息';
  82. COMMENT ON COLUMN alarm_event_rule.create_time IS '创建时间';
  83. COMMENT ON COLUMN alarm_event_rule.update_time IS '更新时间';
  84. INSERT INTO alarm_event_rule (id, name, alarm_template_id, rule_code, condition_json, station_id, priority, remark, need_sent, send_set, send_user, need_popup, enable_flag, del_flag, create_time, create_by, update_time, update_by) VALUES (3, '传输异常', 3, 'TRANSMIT EXCEPTION', NULL, 0, 0, NULL, false, NULL, NULL, false, true, false, '2026-11-18 16:41:41.440323', 'admin', '2026-11-18 16:41:41.440323', 'admin');
  85. INSERT INTO alarm_event_rule (id, name, alarm_template_id, rule_code, condition_json, station_id, priority, remark, need_sent, send_set, send_user, need_popup, enable_flag, del_flag, create_time, create_by, update_time, update_by) VALUES (1, '设备通信中断', 1, 'DEVICE_DISCONNECT', NULL, 0, 0, NULL, false, NULL, NULL, false, true, false, '2026-11-18 16:41:41.440323', 'admin', '2026-11-18 16:41:41.440323', 'admin');
  86. INSERT INTO alarm_event_rule (id, name, alarm_template_id, rule_code, condition_json, station_id, priority, remark, need_sent, send_set, send_user, need_popup, enable_flag, del_flag, create_time, create_by, update_time, update_by) VALUES (2, '数据越限异常', 2, 'DATA EXCEPTION', NULL, 0, 0, NULL, false, NULL, NULL, false, true, false, '2026-11-18 16:41:41.440323', 'admin', '2026-11-18 16:41:41.440323', 'admin');
  87. -- 事件记录表
  88. DROP TABLE IF EXISTS alarm_event_record;
  89. CREATE TABLE alarm_event_record (
  90. id BIGSERIAL PRIMARY KEY,
  91. station_id BIGINT NOT NULL,
  92. source VARCHAR(50) NOT NULL,
  93. alarm_event_rule_id BIGINT NOT NULL,
  94. alarm_template_id BIGINT NOT NULL,
  95. alarm_time TIMESTAMP NOT NULL,
  96. recover_time TIMESTAMP,
  97. alarm_content VARCHAR(500),
  98. alarm_level INT NOT NULL,
  99. status SMALLINT DEFAULT 0,
  100. handler_id BIGINT,
  101. handle_time TIMESTAMP,
  102. remark VARCHAR(255),
  103. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  104. create_by VARCHAR(32),
  105. update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  106. update_by VARCHAR(32),
  107. CONSTRAINT fk_record_rule FOREIGN KEY (alarm_event_rule_id) REFERENCES alarm_event_rule(id),
  108. CONSTRAINT fk_record_template FOREIGN KEY (alarm_template_id) REFERENCES alarm_event_template(id)
  109. );
  110. -- 添加索引(针对高频查询场景:按电站+时间查、按状态查、按级别查)
  111. CREATE INDEX idx_record_station_time ON alarm_event_record(station_id, alarm_time DESC);
  112. CREATE INDEX idx_record_status ON alarm_event_record(status);
  113. CREATE INDEX idx_record_level ON alarm_event_record(alarm_level);
  114. -- 添加注释
  115. COMMENT ON TABLE alarm_event_record IS '事件记录表';
  116. COMMENT ON COLUMN alarm_event_record.id IS '主键ID';
  117. COMMENT ON COLUMN alarm_event_record.station_id IS '电站ID';
  118. COMMENT ON COLUMN alarm_event_record.source IS '事件源';
  119. COMMENT ON COLUMN alarm_event_record.alarm_event_rule_id IS '关联的规则ID';
  120. COMMENT ON COLUMN alarm_event_record.alarm_template_id IS '冗余字段:关联模板ID(加速查询)';
  121. COMMENT ON COLUMN alarm_event_record.alarm_time IS '告警发生时间';
  122. COMMENT ON COLUMN alarm_event_record.recover_time IS '告警恢复时间(为空表示未恢复)';
  123. COMMENT ON COLUMN alarm_event_record.alarm_content IS '实际告警内容(如:“1号逆变器功率预测偏差>20%”)';
  124. COMMENT ON COLUMN alarm_event_record.alarm_level IS '冗余字段:告警级别快照(防止模板修改导致历史记录级别变更)';
  125. COMMENT ON COLUMN alarm_event_record.status IS '状态(0-未确认,1-已确认,2-已恢复,3-已忽略)';
  126. COMMENT ON COLUMN alarm_event_record.handler_id IS '处理人ID';
  127. COMMENT ON COLUMN alarm_event_record.handle_time IS '处理时间';
  128. COMMENT ON COLUMN alarm_event_record.remark IS '处理备注';
  129. COMMENT ON COLUMN alarm_event_record.create_time IS '创建时间';
  130. COMMENT ON COLUMN alarm_event_record.update_time IS '更新时间';
  131. -- 告警通知记录表
  132. DROP TABLE IF EXISTS alarm_notify_log;
  133. CREATE TABLE alarm_notify_log (
  134. id BIGSERIAL PRIMARY KEY,
  135. alarm_record_id BIGINT NOT NULL,
  136. notify_type VARCHAR(20) NOT NULL,
  137. receiver VARCHAR(100) NOT NULL,
  138. send_status SMALLINT DEFAULT 0,
  139. send_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  140. error_msg VARCHAR(255),
  141. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  142. create_by VARCHAR(32),
  143. update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  144. update_by VARCHAR(32),
  145. CONSTRAINT fk_notify_record FOREIGN KEY (alarm_record_id) REFERENCES alarm_event_record(id)
  146. );
  147. CREATE INDEX idx_notify_record_id ON alarm_notify_log(alarm_record_id);
  148. COMMENT ON TABLE alarm_notify_log IS '告警通知记录表';
  149. COMMENT ON COLUMN alarm_notify_log.id IS '主键ID';
  150. COMMENT ON COLUMN alarm_notify_log.alarm_record_id IS '关联的事件记录ID';
  151. COMMENT ON COLUMN alarm_notify_log.notify_type IS '通知类型(SMS/EMAIL/APP)';
  152. COMMENT ON COLUMN alarm_notify_log.receiver IS '接收人/接收组';
  153. COMMENT ON COLUMN alarm_notify_log.send_status IS '发送状态(0-失败,1-成功)';
  154. COMMENT ON COLUMN alarm_notify_log.send_time IS '发送时间';
  155. COMMENT ON COLUMN alarm_notify_log.error_msg IS '失败原因';
  156. INSERT INTO db_version (id, version, status)
  157. VALUES (1, '20261126', 1)
  158. ON CONFLICT (id)
  159. DO UPDATE SET
  160. version = EXCLUDED.version,
  161. status = EXCLUDED.status;