| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 |
- create table if not exists db_version(
- id BIGSERIAL PRIMARY KEY,
- version varchar(100) not null,
- status int not null default(1)
- );
- INSERT INTO db_version (id, version, status)
- VALUES (1, '20261126', 0)
- ON CONFLICT (id)
- DO UPDATE SET
- version = EXCLUDED.version,
- status = EXCLUDED.status;
- -- 告警模板表
- DROP TABLE IF EXISTS alarm_event_template;
- CREATE TABLE alarm_event_template (
- id BIGSERIAL PRIMARY KEY,
- level INT NOT NULL,
- template_name VARCHAR(100) NOT NULL,
- category VARCHAR(50),
- content_format VARCHAR(500),
- color_code VARCHAR(20),
- is_active SMALLINT NOT NULL DEFAULT 1,
- enable_flag BOOLEAN NOT NULL DEFAULT TRUE,
- del_flag BOOLEAN NOT NULL DEFAULT FALSE,
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- create_by VARCHAR(32),
- update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_by VARCHAR(32)
- );
- -- 添加注释
- COMMENT ON TABLE alarm_event_template IS '告警模板表';
- COMMENT ON COLUMN alarm_event_template.id IS '主键ID';
- COMMENT ON COLUMN alarm_event_template.level IS '告警级别(1-紧急,2-重要,3-次要,4-提示)';
- COMMENT ON COLUMN alarm_event_template.template_name IS '模板名称';
- COMMENT ON COLUMN alarm_event_template.category IS '告警分类(如:数据异常、设备故障、预测偏差)';
- COMMENT ON COLUMN alarm_event_template.content_format IS '内容模板(支持占位符,如:{station_name}发生{metric}偏差)';
- COMMENT ON COLUMN alarm_event_template.color_code IS 'UI展示颜色(如:#FF0000)';
- COMMENT ON COLUMN alarm_event_template.is_active IS '是否有效(1-有效,0-无效)';
- COMMENT ON COLUMN alarm_event_template.create_time IS '创建时间';
- COMMENT ON COLUMN alarm_event_template.update_time IS '更新时间';
- 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');
- 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');
- 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');
- -- 事件规则表
- DROP TABLE IF EXISTS alarm_event_rule;
- CREATE TABLE alarm_event_rule (
- id BIGSERIAL PRIMARY KEY,
- name VARCHAR(50) NOT NULL,
- alarm_template_id BIGINT NOT NULL,
- rule_code VARCHAR(50) NOT NULL,
- condition_json JSONB,
- station_id BIGINT DEFAULT 0,
- priority INT DEFAULT 0,
- remark VARCHAR(255),
- need_sent BOOLEAN NOT NULL DEFAULT FALSE,
- send_set JSONB,
- send_user JSONB,
- need_popup BOOLEAN NOT NULL DEFAULT FALSE,
- enable_flag BOOLEAN NOT NULL DEFAULT TRUE,
- del_flag BOOLEAN NOT NULL DEFAULT FALSE,
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- create_by VARCHAR(32),
- update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_by VARCHAR(32),
- CONSTRAINT fk_rule_template FOREIGN KEY (alarm_template_id) REFERENCES alarm_event_template(id)
- );
- -- 添加索引
- CREATE INDEX idx_rule_station ON alarm_event_rule(station_id);
- CREATE UNIQUE INDEX idx_rule_code ON alarm_event_rule(rule_code);
- -- 添加注释
- COMMENT ON TABLE alarm_event_rule IS '事件规则表';
- COMMENT ON COLUMN alarm_event_rule.id IS '主键ID';
- COMMENT ON COLUMN alarm_event_rule.name IS '规则名称(与程序中的事件名关联)';
- COMMENT ON COLUMN alarm_event_rule.alarm_template_id IS '关联模板ID';
- COMMENT ON COLUMN alarm_event_rule.rule_code IS '规则编码(唯一标识,如:PREDICT_DEV_01)';
- COMMENT ON COLUMN alarm_event_rule.condition_json IS '触发条件(JSONB存储:如{"metric":"deviation", "operator":">", "value":0.2, "duration":300})';
- COMMENT ON COLUMN alarm_event_rule.station_id IS '适用电站(0表示全局规则,非0表示特定电站规则)';
- COMMENT ON COLUMN alarm_event_rule.priority IS '优先级(用于规则冲突时的处理)';
- COMMENT ON COLUMN alarm_event_rule.remark IS '规则描述';
- COMMENT ON COLUMN alarm_event_rule.need_sent IS '是否要推送';
- COMMENT ON COLUMN alarm_event_rule.send_set IS 'value:“NO”,label:“不推送”,value:“EMAIL”,label:“邮件”,value:“SMS”,label:“短信”,';
- COMMENT ON COLUMN alarm_event_rule.send_user IS '推送用户信息';
- COMMENT ON COLUMN alarm_event_rule.create_time IS '创建时间';
- COMMENT ON COLUMN alarm_event_rule.update_time IS '更新时间';
- 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');
- 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');
- 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');
- -- 事件记录表
- DROP TABLE IF EXISTS alarm_event_record;
- CREATE TABLE alarm_event_record (
- id BIGSERIAL PRIMARY KEY,
- station_id BIGINT NOT NULL,
- source VARCHAR(50) NOT NULL,
- alarm_event_rule_id BIGINT NOT NULL,
- alarm_template_id BIGINT NOT NULL,
- alarm_time TIMESTAMP NOT NULL,
- recover_time TIMESTAMP,
- alarm_content VARCHAR(500),
- alarm_level INT NOT NULL,
- status SMALLINT DEFAULT 0,
- handler_id BIGINT,
- handle_time TIMESTAMP,
- remark VARCHAR(255),
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- create_by VARCHAR(32),
- update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_by VARCHAR(32),
- CONSTRAINT fk_record_rule FOREIGN KEY (alarm_event_rule_id) REFERENCES alarm_event_rule(id),
- CONSTRAINT fk_record_template FOREIGN KEY (alarm_template_id) REFERENCES alarm_event_template(id)
- );
- -- 添加索引(针对高频查询场景:按电站+时间查、按状态查、按级别查)
- CREATE INDEX idx_record_station_time ON alarm_event_record(station_id, alarm_time DESC);
- CREATE INDEX idx_record_status ON alarm_event_record(status);
- CREATE INDEX idx_record_level ON alarm_event_record(alarm_level);
- -- 添加注释
- COMMENT ON TABLE alarm_event_record IS '事件记录表';
- COMMENT ON COLUMN alarm_event_record.id IS '主键ID';
- COMMENT ON COLUMN alarm_event_record.station_id IS '电站ID';
- COMMENT ON COLUMN alarm_event_record.source IS '事件源';
- COMMENT ON COLUMN alarm_event_record.alarm_event_rule_id IS '关联的规则ID';
- COMMENT ON COLUMN alarm_event_record.alarm_template_id IS '冗余字段:关联模板ID(加速查询)';
- COMMENT ON COLUMN alarm_event_record.alarm_time IS '告警发生时间';
- COMMENT ON COLUMN alarm_event_record.recover_time IS '告警恢复时间(为空表示未恢复)';
- COMMENT ON COLUMN alarm_event_record.alarm_content IS '实际告警内容(如:“1号逆变器功率预测偏差>20%”)';
- COMMENT ON COLUMN alarm_event_record.alarm_level IS '冗余字段:告警级别快照(防止模板修改导致历史记录级别变更)';
- COMMENT ON COLUMN alarm_event_record.status IS '状态(0-未确认,1-已确认,2-已恢复,3-已忽略)';
- COMMENT ON COLUMN alarm_event_record.handler_id IS '处理人ID';
- COMMENT ON COLUMN alarm_event_record.handle_time IS '处理时间';
- COMMENT ON COLUMN alarm_event_record.remark IS '处理备注';
- COMMENT ON COLUMN alarm_event_record.create_time IS '创建时间';
- COMMENT ON COLUMN alarm_event_record.update_time IS '更新时间';
- -- 告警通知记录表
- DROP TABLE IF EXISTS alarm_notify_log;
- CREATE TABLE alarm_notify_log (
- id BIGSERIAL PRIMARY KEY,
- alarm_record_id BIGINT NOT NULL,
- notify_type VARCHAR(20) NOT NULL,
- receiver VARCHAR(100) NOT NULL,
- send_status SMALLINT DEFAULT 0,
- send_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- error_msg VARCHAR(255),
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- create_by VARCHAR(32),
- update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_by VARCHAR(32),
- CONSTRAINT fk_notify_record FOREIGN KEY (alarm_record_id) REFERENCES alarm_event_record(id)
- );
- CREATE INDEX idx_notify_record_id ON alarm_notify_log(alarm_record_id);
- COMMENT ON TABLE alarm_notify_log IS '告警通知记录表';
- COMMENT ON COLUMN alarm_notify_log.id IS '主键ID';
- COMMENT ON COLUMN alarm_notify_log.alarm_record_id IS '关联的事件记录ID';
- COMMENT ON COLUMN alarm_notify_log.notify_type IS '通知类型(SMS/EMAIL/APP)';
- COMMENT ON COLUMN alarm_notify_log.receiver IS '接收人/接收组';
- COMMENT ON COLUMN alarm_notify_log.send_status IS '发送状态(0-失败,1-成功)';
- COMMENT ON COLUMN alarm_notify_log.send_time IS '发送时间';
- COMMENT ON COLUMN alarm_notify_log.error_msg IS '失败原因';
- INSERT INTO db_version (id, version, status)
- VALUES (1, '20261126', 1)
- ON CONFLICT (id)
- DO UPDATE SET
- version = EXCLUDED.version,
- status = EXCLUDED.status;
|