bot_dev1
1d550c1bfb
- 阈值管理：支持报警阈值配置（最小值、最大值、警告值） - 信息发布：支持预报/预警信息发布和管理 - 设备管理：支持按名称、类型、时间、位置查询设备 - 新增3个实体类：Threshold、Notification、Equipment - 新增6个Service及其实现类 - 新增3个Controller及对应的VO类 - 新增数据库表结构和示例数据
83 lines
4.2 KiB
SQL
83 lines
4.2 KiB
SQL
-- 阈值表
|
|
CREATE TABLE IF NOT EXISTS threshold (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
device_id VARCHAR(100) NOT NULL,
|
|
device_type VARCHAR(50),
|
|
region VARCHAR(50),
|
|
parameter VARCHAR(100) NOT NULL,
|
|
min_value DOUBLE PRECISION,
|
|
max_value DOUBLE PRECISION,
|
|
warning_min DOUBLE PRECISION,
|
|
warning_max DOUBLE PRECISION,
|
|
unit VARCHAR(20),
|
|
description TEXT,
|
|
status INTEGER DEFAULT 1 COMMENT '1-启用，0-禁用',
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 信息发布表
|
|
CREATE TABLE IF NOT EXISTS notification (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
title VARCHAR(200) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
type VARCHAR(20) NOT NULL COMMENT 'forecast-预报，warning-预警，info-通知',
|
|
region VARCHAR(50),
|
|
priority VARCHAR(20) DEFAULT 'medium' COMMENT 'high-高，medium-中，low-低',
|
|
status INTEGER DEFAULT 1 COMMENT '1-草稿，2-已发布，3-已归档',
|
|
publisher VARCHAR(100),
|
|
publish_time TIMESTAMP,
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 设备表
|
|
CREATE TABLE IF NOT EXISTS equipment (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
device_name VARCHAR(100) NOT NULL,
|
|
device_type VARCHAR(50),
|
|
model VARCHAR(100),
|
|
serial_number VARCHAR(100),
|
|
region VARCHAR(50),
|
|
location TEXT,
|
|
status VARCHAR(20) DEFAULT 'offline' COMMENT 'online-在线，offline-离线，maintenance-维护中，fault-故障',
|
|
manufacturer VARCHAR(100),
|
|
installation_date DATE,
|
|
last_maintenance_date DATE,
|
|
next_maintenance_date DATE,
|
|
latitude DOUBLE PRECISION,
|
|
longitude DOUBLE PRECISION,
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_threshold_device_id ON threshold(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_threshold_region ON threshold(region);
|
|
CREATE INDEX IF NOT EXISTS idx_threshold_parameter ON threshold(parameter);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_region ON notification(region);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_type ON notification(type);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_status ON notification(status);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_device_name ON equipment(device_name);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_device_type ON equipment(device_type);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_region ON equipment(region);
|
|
CREATE INDEX IF NOT EXISTS idx_equipment_status ON equipment(status);
|
|
|
|
-- 插入示例数据
|
|
-- 阈值示例
|
|
INSERT INTO threshold (device_id, device_type, region, parameter, min_value, max_value, warning_min, warning_max, unit, description, status) VALUES
|
|
('DEV001', 'pressure_sensor', '精河县', 'water_pressure', 0.2, 0.8, 0.15, 0.85, 'MPa', '供水压力阈值', 1),
|
|
('DEV002', 'flow_meter', '精河县', 'water_flow', 10, 100, 8, 110, 'm³/h', '水流流量阈值', 1),
|
|
('DEV003', 'quality_sensor', '精河县', 'water_quality', 0, 1, 0.1, 0.9, 'pH', '水质pH值阈值', 1);
|
|
|
|
-- 设备示例
|
|
INSERT INTO equipment (device_name, device_type, model, serial_number, region, location, status, manufacturer, installation_date) VALUES
|
|
('压力传感器001', 'pressure_sensor', 'PS-3000', 'SN0012023001', '精河县', '供水站A区', 'online', '华为', '2023-01-15'),
|
|
('流量计001', 'flow_meter', 'FM-5000', 'SN0012023002', '精河县', '供水站B区', 'online', '西门子', '2023-02-20'),
|
|
('水质检测仪001', 'quality_sensor', 'QS-2000', 'SN0012023003', '精河县', '供水站C区', 'online', '霍尼韦尔', '2023-03-10');
|
|
|
|
-- 信息发布示例
|
|
INSERT INTO notification (title, content, type, region, priority, status, publisher, publish_time) VALUES
|
|
('停水通知', '因设备维护，预计明天9:00-12:00精河县部分区域将暂停供水', 'warning', '精河县', 'high', 2, 'system_admin', '2026-06-14T08:00:00'),
|
|
('水质提升通知', '本季度已完成水质净化设备升级，水质显著提升', 'info', '精河县', 'medium', 2, 'water_quality_team', '2026-06-01T10:00:00'),
|
|
('雨季供水保障通知', '近期降雨较多，各水厂已加强巡检，确保供水稳定', 'forecast', '精河县', 'low', 2, 'emergency_team', '2026-05-20T14:00:00'); |