lijianfu
/
eemcs
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
高效能源监控管理系统
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
56 Commits
1 Branch
0 Tags
4.5 MiB
 
 
 
 
 
 
Branch: dev
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'dev'
${ noResults }
eemcs/sql/表结构设计.sql

703 lines
39 KiB
Raw Permalink Blame History

--1、创建设备台账管理 device_ledger 表
CREATE TABLE device_ledger (
id VARCHAR(36) NOT NULL, -- 设备id
device_name VARCHAR(50) NULL, -- 设备名称
model_specs VARCHAR(100) NULL, -- 规格型号
asset_num VARCHAR(50) NULL, -- 资产编号
qr_code_id VARCHAR(36) NULL, -- 二维码id
service_life VARCHAR(10) NULL, -- 使用寿命
device_type VARCHAR(50) NULL, -- 设备类型(字典)
tech_params VARCHAR(200) NULL, -- 技术参数
tech_doc VARCHAR(100) NULL, -- 技术文档
install_time TIMESTAMP NULL, -- 安装时间
create_time TIMESTAMP NULL, -- 创建时间
update_time TIMESTAMP NULL, -- 更新时间
create_by VARCHAR(100) NULL, -- 创建者
status int null, -- 状态（0：在线或者使用中，1：不在线或者停用）
is_collection int null, -- 是否是需要采集（0：是，1：不采集）
is_calc_energy int null, -- 是否计算能效(0:计算，1不计算)
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE device_ledger IS '设备台账表，用于记录所有设备的基本信息、安装及维护情况';
-- 为各列添加备注
COMMENT ON COLUMN device_ledger.id IS '设备唯一标识符';
COMMENT ON COLUMN device_ledger.device_name IS '设备的名称';
COMMENT ON COLUMN device_ledger.model_specs IS '设备的规格和型号';
COMMENT ON COLUMN device_ledger.asset_num IS '设备的资产编号';
COMMENT ON COLUMN device_ledger.qr_code_id IS '与设备关联的二维码ID';
COMMENT ON COLUMN device_ledger.service_life IS '设备的预计使用寿命';
COMMENT ON COLUMN device_ledger.device_type IS '设备所属类型（字典值获取）';
COMMENT ON COLUMN device_ledger.tech_params IS '设备的技术参数描述';
COMMENT ON COLUMN device_ledger.tech_doc IS '设备相关的技术文档链接或文件名';
COMMENT ON COLUMN device_ledger.install_time IS '设备的安装日期和时间';
COMMENT ON COLUMN device_ledger.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN device_ledger.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN device_ledger.create_by IS '创建该记录的用户';
COMMENT ON COLUMN device_ledger.status IS '状态（0：在线或者使用中，1：不在线或者停用）';
COMMENT ON COLUMN device_ledger.is_collection IS '是否是需要采集（0：是，1：不采集）';
COMMENT ON COLUMN device_ledger.is_calc_energy IS '是否计算能效(0:计算，1不计算)';
-- 为 device_ledger 表的 device_type_id 字段添加索引
CREATE INDEX idx_device_ledger_device_ledger_id ON device_ledger (device_type);
-- 为 device_ledger 表的 create_time 字段添加索引（倒序）
CREATE INDEX idx_device_ledger_create_time_desc ON device_ledger (create_time DESC);
ALTER TABLE public.device_ledger ADD update_by varchar(100) NULL;
COMMENT ON COLUMN public.device_ledger.update_by IS '更新者';
--2、创建二维码管理 device_qr_manage 表
CREATE TABLE device_qr_manage (
id VARCHAR(36) NOT NULL, -- 设备二维码管理记录ID
serial_num VARCHAR(50) NULL, -- 设备序列号编号
pic_content VARCHAR(100) NULL, -- 二维码图片内容或链接
device_ledger_id VARCHAR(36), -- 关联的设备台账ID
device_name VARCHAR(50), -- 设备名称（冗余字段，方便查询）
bind_time TIMESTAMP NULL, -- 二维码与设备绑定的时间
create_time TIMESTAMP NULL, -- 记录创建的时间戳
update_time TIMESTAMP NULL, -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE device_qr_manage IS '设备二维码管理表，用于记录设备与二维码之间的绑定关系及二维码相关信息';
-- 为各列添加备注
COMMENT ON COLUMN device_qr_manage.id IS '设备二维码管理记录的唯一标识符';
COMMENT ON COLUMN device_qr_manage.serial_num IS '设备的序列号，用于唯一识别每个设备';
COMMENT ON COLUMN device_qr_manage.pic_content IS '二维码图片的内容或存储路径';
COMMENT ON COLUMN device_qr_manage.device_ledger_id IS '关联到设备台账表的设备ID，建立外键关系';
COMMENT ON COLUMN device_qr_manage.device_name IS '设备名称，作为冗余字段便于快速查询';
COMMENT ON COLUMN device_qr_manage.bind_time IS '二维码与设备绑定的具体时间';
COMMENT ON COLUMN device_qr_manage.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN device_qr_manage.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN device_qr_manage.create_by IS '创建该记录的用户';
-- 为 device_qr_manage 表的 device_ledger_id 字段添加索引
CREATE INDEX idx_device_qr_manage_device_ledger_id ON device_qr_manage (device_ledger_id);
-- 为 device_qr_manage 表的 create_time 字段添加索引（倒序）
CREATE INDEX idx_device_qr_manage_create_time_desc ON device_qr_manage (create_time DESC);
ALTER TABLE public.device_qr_manage ADD update_by varchar(100) NULL;
COMMENT ON COLUMN public.device_qr_manage.update_by IS '更新者';
ALTER TABLE public.device_qr_manage ADD status int NULL;
COMMENT ON COLUMN public.device_qr_manage.status IS '二维码状态：0：已绑定，1：未绑定';
--3、采集参数管理表 collection_params_manage 表
CREATE TABLE collection_params_manage (
id varchar(36) NOT NULL, -- 主键
device_ledger_id varchar(36) NULL, -- 设备台账id
mt_type varchar(10), -- 设备类型(字典)
mt_num VARCHAR(20) NULL, -- 设备地址
other_name VARCHAR(100) NULL, -- 别称
mt_code VARCHAR(20) NULL, -- 设备采集地址
register_addr VARCHAR(20) NULL, -- 寄存器地址
func_code VARCHAR(20) NULL, -- 功能码
identify_code VARCHAR(20) NULL, -- 数据标识码
mt_caliber_pulse VARCHAR(20) NULL, -- 口径
mt_range NUMERIC(24, 3) NULL, -- 取值范围
mt_ratio INT NULL, -- 倍率
mt_init_value NUMERIC(24, 3) NULL, -- 初始值
digits INT NULL, -- 保留小数位
data_type INT NULL, -- 数据类型(字典)
cur_value NUMERIC(24, 3) NULL, -- 当前值
cur_time TIMESTAMP NULL, -- 采集时间
mt_is_sum BOOLEAN NULL, -- 是否是总表
unit VARCHAR(20) NULL, -- 单位
sort BIGINT NULL, -- 排序
gateway_id varchar(36) NULL, -- 网关id
param_id varchar(36) NULL, -- 串口采集通讯配置id
protocol_type varchar(5) NULL, -- 协议类型
communication_type varchar(5) NULL, -- 通讯类型（字典）
remark varchar(200) NULL, -- 备注
register_size INT NULL, -- 读取寄存器大小
is_use INT NULL, -- 是否启用采集（0：正常，1：不采集）
create_time TIMESTAMP NULL, -- 创建时间
update_time TIMESTAMP NULL, -- 更新时间
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE collection_params_manage IS '仪表管理表，用于记录各种仪表的基本信息、配置及状态';
-- 为各列添加备注
COMMENT ON COLUMN collection_params_manage.id IS '自增主键';
COMMENT ON COLUMN collection_params_manage.mt_type IS '仪表类型ID';
COMMENT ON COLUMN collection_params_manage.other_name IS '其他名称';
COMMENT ON COLUMN collection_params_manage.mt_brand IS '仪表品牌';
COMMENT ON COLUMN collection_params_manage.mt_num IS '仪表编号';
COMMENT ON COLUMN collection_params_manage.mt_code IS '仪表代码';
COMMENT ON COLUMN collection_params_manage.register_addr IS '注册地址';
COMMENT ON COLUMN collection_params_manage.func_code IS '功能代码';
COMMENT ON COLUMN collection_params_manage.identify_code IS '识别代码';
COMMENT ON COLUMN collection_params_manage.mt_caliber_pulse IS '口径脉冲';
COMMENT ON COLUMN collection_params_manage.mt_range IS '量程';
COMMENT ON COLUMN collection_params_manage.mt_ratio IS '比例';
COMMENT ON COLUMN collection_params_manage.mt_init_value IS '初始值';
COMMENT ON COLUMN collection_params_manage.digits IS '位数';
COMMENT ON COLUMN collection_params_manage.data_type IS '数据类型';
COMMENT ON COLUMN collection_params_manage.cur_value IS '当前值';
COMMENT ON COLUMN collection_params_manage.cur_time IS '当前时间';
COMMENT ON COLUMN collection_params_manage.mt_is_sum IS '是否累计（0: 否, 1: 是）';
COMMENT ON COLUMN collection_params_manage.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN collection_params_manage.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN collection_params_manage.unit IS '单位';
COMMENT ON COLUMN collection_params_manage.sort IS '排序';
COMMENT ON COLUMN collection_params_manage.data_com IS '数据通信配置';
COMMENT ON COLUMN collection_params_manage.device_id IS '设备ID';
COMMENT ON COLUMN collection_params_manage.gateway_id IS '网关ID';
COMMENT ON COLUMN collection_params_manage.param_id IS '参数ID';
COMMENT ON COLUMN collection_params_manage.protocol_type IS '协议类型ID';
COMMENT ON COLUMN collection_params_manage.communication_type IS '通信类型';
COMMENT ON COLUMN collection_params_manage.remark IS '备注';
COMMENT ON COLUMN collection_params_manage.register_size IS '注册大小';
COMMENT ON COLUMN collection_params_manage.is_use IS '是否使用（0: 使用, 1: 不使用）';
-- 为 device_qr_manage 表的 device_ledger_id 字段添加索引
CREATE INDEX idx_meter_manage_mt_type ON collection_params_manage (mt_type);
-- 为 device_qr_manage 表的 create_time 字段添加索引（倒序）
CREATE INDEX idx_meter_manage_create_time_desc ON collection_params_manage (create_time DESC);
--4、创建备件/备品管理 spare_parts_manage 表
CREATE TABLE spare_parts_manage (
id VARCHAR(36) NOT NULL, -- 备件ID
serial_num VARCHAR(50) NULL, -- 序列号编号
spare_name VARCHAR(100) NULL, -- 备件名称
model_specs VARCHAR(100) NULL, -- 规格型号
device_type_id VARCHAR(50) NULL, -- 设备类型ID
inventory_levels INT NULL, -- 当前库存量
min_inventory_levels INT NULL, -- 最低库存量阈值
service_life INT NULL, -- 使用年限（单位：年）
unit_price NUMERIC(24, 3), -- 单价（保留三位小数）
create_time TIMESTAMP NULL, -- 记录创建的时间戳
update_time TIMESTAMP NULL, -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE spare_parts_manage IS '备品/备件管理表，用于记录所有备件的基本信息、库存情况及价格';
-- 为各列添加备注
COMMENT ON COLUMN spare_parts_manage.id IS '备件的唯一标识符';
COMMENT ON COLUMN spare_parts_manage.serial_num IS '备件的序列号，用于唯一识别每个备件';
COMMENT ON COLUMN spare_parts_manage.spare_name IS '备件的名称';
COMMENT ON COLUMN spare_parts_manage.model_specs IS '备件的规格和型号';
COMMENT ON COLUMN spare_parts_manage.device_type_id IS '关联到设备类型的ID，标识备件适用于哪种设备类型';
COMMENT ON COLUMN spare_parts_manage.inventory_levels IS '当前备件的库存数量';
COMMENT ON COLUMN spare_parts_manage.min_inventory_levels IS '最低库存量阈值，当库存低于此值时需要补货';
COMMENT ON COLUMN spare_parts_manage.service_life IS '备件的预计使用年限，单位为年';
COMMENT ON COLUMN spare_parts_manage.unit_price IS '备件的单价，保留三位小数';
COMMENT ON COLUMN spare_parts_manage.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN spare_parts_manage.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN spare_parts_manage.create_by IS '创建该记录的用户';
CREATE INDEX idx_spare_parts_device_type_id ON spare_parts_manage (device_type_id);
CREATE INDEX idx_spare_parts_create_time_desc ON spare_parts_manage (create_time DESC);
ALTER TABLE public.spare_parts_manage ADD update_by varchar NULL;
COMMENT ON COLUMN public.spare_parts_manage.update_by IS '更新者';
--5、创建出入库管理device_in_out_manage
CREATE TABLE device_in_out_manage (
id VARCHAR(36) NOT NULL, -- 出入库管理ID
spare_parts_id VARCHAR(36) NULL, -- 备件ID
spare_name VARCHAR(100) NULL, -- 备件名称
opera_type VARCHAR(10) NULL, -- 出入库操作类型（0：入库，1：出库）
int_out_num INT NULL, -- 出入库数量
inventory_levels INT NULL, -- 当前库c存量
create_time TIMESTAMP NULL, -- 记录创建的时间戳
update_time TIMESTAMP NULL, -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
update_by VARCHAR(100) NULL, -- 更新该记录的用户
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE device_in_out_manage IS '备品/备件出入库管理表，用于记录备件的出入库操作及当前库存情况';
-- 为各列添加备注
COMMENT ON COLUMN device_in_out_manage.id IS '出入库管理记录的唯一标识符';
COMMENT ON COLUMN device_in_out_manage.spare_parts_id IS '关联到备件的唯一标识符，用于标识具体的备件';
COMMENT ON COLUMN device_in_out_manage.spare_name IS '备件的名称，便于快速识别';
COMMENT ON COLUMN device_in_out_manage.opera_type IS '出入库操作类型，0表示入库，1表示出库';
COMMENT ON COLUMN device_in_out_manage.int_out_num IS '本次出入库操作的数量，正值表示入库，负值表示出库';
COMMENT ON COLUMN device_in_out_manage.inventory_levels IS '当前备件的库存数量，在每次出入库操作后更新';
COMMENT ON COLUMN device_in_out_manage.create_time IS '记录创建的时间戳，表示此次出入库操作的时间';
COMMENT ON COLUMN device_in_out_manage.update_time IS '记录最后一次更新的时间戳，通常与 create_time 相同，除非后续有修改';
COMMENT ON COLUMN device_in_out_manage.create_by IS '创建该记录的用户，记录是谁执行了此次出入库操作';
CREATE INDEX idx_device_in_out_manage_opera_type ON device_in_out_manage (opera_type);
CREATE INDEX idx_device_in_out_manage_spare_parts_id ON device_in_out_manage (spare_parts_id);
CREATE INDEX idx_device_in_out_manage_create_time_desc ON device_in_out_manage (create_time DESC);
-- 6、创建网关管理 gateway_manage 表
CREATE TABLE gateway_manage (
id varchar(36) NOT NULL, -- 自增主键
gw_name VARCHAR(100) NULL, -- 网关名称
gw_ip VARCHAR(20) NULL, -- 网关IP地址
gw_addr VARCHAR(100) NULL, -- 网关对应的编号地址
port INT NULL, -- 网关端口
collection_loop INT NULL, -- 采集周期
create_time TIMESTAMP NULL, -- 创建时间
update_time TIMESTAMP NULL, -- 更新时间
connect_time TIMESTAMP NULL, -- 连接时间
internet_card VARCHAR(50) NULL, -- 卡号
operator_type INT NULL, -- 运营商类型（0: 中国移动, 1: 中国联通, 2: 中国电信）
remark VARCHAR(100) NULL, -- 备注
communication_type INT NULL, -- 通信类型（字典表）
grade INT NULL, -- 预留字段
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE gateway_manage IS '网关管理表，用于记录网关的基本信息、配置及状态';
-- 为各列添加备注
COMMENT ON COLUMN gateway_manage.id IS '编号';
COMMENT ON COLUMN gateway_manage.gw_name IS '网关名称';
COMMENT ON COLUMN gateway_manage.gw_ip IS '网关IP地址';
COMMENT ON COLUMN gateway_manage.gw_addr IS '网关对应的编号地址';
COMMENT ON COLUMN gateway_manage.port IS '网关端口';
COMMENT ON COLUMN gateway_manage.collection_loop IS '采集周期';
COMMENT ON COLUMN gateway_manage.create_time IS '创建时间';
COMMENT ON COLUMN gateway_manage.update_time IS '更新时间';
COMMENT ON COLUMN gateway_manage.connect_time IS '连接时间';
COMMENT ON COLUMN gateway_manage.internet_card IS '卡号';
COMMENT ON COLUMN gateway_manage.operator_type IS '运营商类型（0: 中国移动, 1: 中国联通, 2: 中国电信）';
COMMENT ON COLUMN gateway_manage.remark IS '备注';
COMMENT ON COLUMN gateway_manage.communication_type IS '通信类型（字典表）';
COMMENT ON COLUMN gateway_manage.grade IS '预留字段';
CREATE INDEX idx_gateway_manage_gw_name ON gateway_manage (gw_name);
CREATE INDEX idx_gateway_manage_status ON gateway_manage (status);
CREATE INDEX idx_gateway_manage_create_time_desc ON gateway_manage (create_time DESC);
ALTER TABLE public.gateway_manage ADD create_by varchar(100) NULL;
COMMENT ON COLUMN public.gateway_manage.create_by IS '创建者';
ALTER TABLE public.gateway_manage ADD update_by varchar(100) NULL;
COMMENT ON COLUMN public.gateway_manage.update_by IS '更新者';
ALTER TABLE public.gateway_manage ADD status int NULL;
COMMENT ON COLUMN public.gateway_manage.status IS '状态（0：在线，1：离线）';
-- 7、通讯采集参数创建 device_params 表
CREATE TABLE communication_params (
id varchar(36) NOT NULL, -- 自增主键
mt_type INT NOT NULL, -- 仪表类型（从字典中获取）
baud_rate INT NOT NULL, -- 波特率
data_bit INT NOT NULL, -- 数据位
stop_bit INT NOT NULL, -- 停止位
parity VARCHAR(10) NOT NULL, -- 校验位
remark VARCHAR(100) NULL, -- 备注
PRIMARY KEY (id)
);
-- 为表添加备注
COMMENT ON TABLE communication_params IS '设备采集参数表，用于记录设备的通信参数';
-- 为各列添加备注
COMMENT ON COLUMN communication_params.id IS '编号';
COMMENT ON COLUMN communication_params.mt_type IS '仪表类型（从字典中获取）';
COMMENT ON COLUMN communication_params.baud_rate IS '波特率';
COMMENT ON COLUMN communication_params.data_bit IS '数据位';
COMMENT ON COLUMN communication_params.stop_bit IS '停止位';
COMMENT ON COLUMN communication_params.parity IS '校验位';
COMMENT ON COLUMN communication_params.remark IS '备注';
ALTER TABLE public.communication_params ADD create_by varchar(100) NULL;
COMMENT ON COLUMN public.communication_params.create_by IS '创建者';
ALTER TABLE public.communication_params ADD update_by varchar(100) NULL;
COMMENT ON COLUMN public.communication_params.update_by IS '更新者';
ALTER TABLE public.communication_params ADD create_time TIMESTAMP NULL;
COMMENT ON COLUMN public.communication_params.create_time IS '创建时间';
ALTER TABLE public.communication_params ADD update_time TIMESTAMP NULL;
COMMENT ON COLUMN public.communication_params.update_time IS '更新时间';
-- 8、维保管理
CREATE TABLE maintain_info
(
id varchar(36) NOT NULL,
device_id varchar(36) , -- 设备表id
device_type VARCHAR(32) NULL,
maintain_type VARCHAR(50) NULL,
maintain_people VARCHAR(50) NULL,
cost NUMERIC(24, 2) NULL,
contents VARCHAR(100) NULL,
evaluate VARCHAR(10) NULL,
exec_time timestamp NULL, -- 维修时间
spend_times int NULL, -- 花费时间
create_time TIMESTAMP NULL, -- 创建时间
update_time TIMESTAMP NULL, -- 更新时间
create_by varchar(100) NULL,
update_by varchar(100) NULL,
PRIMARY KEY (id)
);
CREATE INDEX idx_maintain_info_create_time_desc ON maintain_info (create_time DESC);
CREATE INDEX idx_maintain_info_maintain_people_desc ON maintain_info (maintain_people);
COMMENT ON TABLE maintain_info IS '维保管理：登记维护信息';
-- Extended properties
COMMENT
ON COLUMN maintain_info.id IS '序号';
COMMENT
ON COLUMN maintain_info.device_id IS '设备id';
COMMENT
ON COLUMN maintain_info.device_type IS '设备类型';
COMMENT
ON COLUMN maintain_info.maintain_type IS '维护类型';
COMMENT
ON COLUMN maintain_info.maintain_people IS '维护人员';
COMMENT
ON COLUMN maintain_info.cost IS '材料费用';
COMMENT
ON COLUMN maintain_info.contents IS '维保内容';
COMMENT
ON COLUMN maintain_info.evaluate IS '评价内容';
COMMENT
ON COLUMN maintain_info.create_time IS '创建时间';
COMMENT
ON COLUMN maintain_info.update_time IS '更新时间';
COMMENT
ON COLUMN maintain_info.create_by IS '创建者';
COMMENT
ON COLUMN maintain_info.update_by IS '更新者';
COMMENT
ON COLUMN maintain_info.exec_time IS '执行维修时间';
COMMENT
ON COLUMN maintain_info.spend_times IS '维修花费时间';
-- 报警编码管理（类似模板消息）
CREATE TABLE alarm_code (
id VARCHAR(36) NOT NULL, -- id
msg_code VARCHAR(36) NULL, -- 消息编码
collection_type varchar(10) NULL, -- 采集类别：0：遥测（连续变化参数），1：遥信（离散开关状态）
event_type VARCHAR(10) NULL, -- 事件类型
alarm_level VARCHAR(10) NULL, -- 报警等级
msg_title varchar(100) NULL, -- 消息标题
msg_content varchar(500) null, -- 消息内容
create_time TIMESTAMP NULL, -- 记录创建的时间戳
update_time TIMESTAMP NULL, -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
update_by VARCHAR(100) NULL, -- 更新该记录的用户
PRIMARY KEY (id)
);
COMMENT ON COLUMN alarm_code.id IS '唯一标识符';
COMMENT ON COLUMN alarm_code.msg_code IS '消息编码';
COMMENT ON COLUMN alarm_code.collection_type IS '采集类别：0：遥测（连续变化参数），1：遥信（离散开关状态）';
COMMENT ON COLUMN alarm_code.event_type IS '事件类型（字典）';
COMMENT ON COLUMN alarm_code.alarm_level IS '报警等级（字典）';
COMMENT ON COLUMN alarm_code.msg_title IS '消息标题';
COMMENT ON COLUMN alarm_code.msg_content IS '消息内容';
COMMENT ON COLUMN alarm_code.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN alarm_code.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN alarm_code.create_by IS '创建该记录的用户';
COMMENT ON COLUMN alarm_code.update_by IS '更新该记录的用户';
-- 添加索引
CREATE INDEX idx_alarm_code_msg_code ON alarm_code (msg_code);
CREATE INDEX idx_alarm_code_collection_type ON alarm_code (collection_type);
CREATE INDEX idx_alarm_code_event_type ON alarm_code (event_type);
CREATE INDEX idx_alarm_code_alarm_level ON alarm_code (alarm_level);
CREATE INDEX idx_alarm_code_msg_title ON alarm_code (msg_title);
CREATE INDEX idx_alarm_code_create_time_desc ON alarm_code (create_time DESC);
-- 仪表报警规则
CREATE TABLE alarm_rules (
id VARCHAR(36) NOT NULL, -- id
alarm_type varchar(10) null, -- 报警类型
event_type VARCHAR(10) NULL, -- 事件类型
alarm_level VARCHAR(10) NULL, -- 报警等级
ledger_id varchar(36) null, -- 设备台账id
device_name VARCHAR(50) NULL, -- 设备名称
cpm_id varchar(36) NULL, -- 仪表参数id
cpm_name varchar(100) null, -- 参数名称
cpm_mt_num varchar(100) null, -- 参数编号（仪表编号）
delay_times int NULL, -- 报警延时时间
time_period_set int null, -- 时段设置（0：全时段，1：自定义时段）
begin_time timestamp null, -- 自定义开始时段
end_time timestamp null, -- 自定义结束时段
trigger_event int null, -- 选择遥信事件显示：触发事件（0：开，1：关）
condition1 varchar(10) null, -- 选择越限事件显示：条件1：>\<\!=\=\>=\<=
threshold1 varchar(10) null, -- 选择越限事件显示：阈值1
condition2 varchar(10) null, -- 选择越限事件显示：条件2：>\<\!=\=\>=\<=
threshold2 varchar(10) null, -- 选择越限事件显示：阈值2
energy_node varchar(36) null, -- 能源节点(预留是能耗方面的)
energy_type varchar(20) null, -- 能源类型(预留是能耗方面的)
remark INT NULL, -- 消息说明
create_time TIMESTAMP NULL, -- 记录创建的时间戳
update_time TIMESTAMP NULL, -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
update_by VARCHAR(100) NULL, -- 更新该记录的用户
PRIMARY KEY (id)
);
COMMENT ON COLUMN alarm_rules.id IS '唯一标识符';
COMMENT ON COLUMN alarm_rules.event_type IS '事件类型（字典）';
COMMENT ON COLUMN alarm_rules.alarm_type IS '报警类型（字典）';
COMMENT ON COLUMN alarm_rules.alarm_level IS '报警等级（字典）';
COMMENT ON COLUMN alarm_rules.ledger_id IS '设备台账ID';
COMMENT ON COLUMN alarm_rules.device_name IS '设备名称';
COMMENT ON COLUMN alarm_rules.cpm_id IS '仪表参数ID';
COMMENT ON COLUMN alarm_rules.cpm_name IS '参数名称';
COMMENT ON COLUMN alarm_rules.cpm_mt_num IS '参数编号（仪表编号）';
COMMENT ON COLUMN alarm_rules.delay_times IS '报警延时时间';
COMMENT ON COLUMN alarm_rules.time_period_set IS '时段设置（0：全时段，1：自定义时段）';
COMMENT ON COLUMN alarm_rules.begin_time IS '自定义开始时段';
COMMENT ON COLUMN alarm_rules.end_time IS '自定义结束时段';
COMMENT ON COLUMN alarm_rules.trigger_event IS '选择遥信事件显示：触发事件（0：开，1：关）';
COMMENT ON COLUMN alarm_rules.condition1 IS '选择越限事件显示：条件1：>\<\!=\=\>=\<=' ;
COMMENT ON COLUMN alarm_rules.threshold1 IS '选择越限事件显示：阈值1';
COMMENT ON COLUMN alarm_rules.condition2 IS '选择越限事件显示：条件2：>\<\!=\=\>=\<=';
COMMENT ON COLUMN alarm_rules.threshold2 IS '选择越限事件显示：阈值2';
COMMENT ON COLUMN alarm_rules.energy_node IS '能源节点(预留用于能耗方面)';
COMMENT ON COLUMN alarm_rules.energy_type IS '能源类型(预留用于能耗方面)';
COMMENT ON COLUMN alarm_rules.remark IS '消息说明';
COMMENT ON COLUMN alarm_rules.create_time IS '记录创建的时间戳';
COMMENT ON COLUMN alarm_rules.update_time IS '记录最后一次更新的时间戳';
COMMENT ON COLUMN alarm_rules.create_by IS '创建该记录的用户';
COMMENT ON COLUMN alarm_rules.update_by IS '更新该记录的用户';
-- 添加索引
CREATE INDEX idx_alarm_rules_event_type ON alarm_rules (event_type);
CREATE INDEX idx_alarm_rules_alarm_level ON alarm_rules (alarm_level);
CREATE INDEX idx_alarm_rules_alarm_type ON alarm_rules (alarm_type);
CREATE INDEX idx_alarm_rules_create_time_desc ON alarm_rules (create_time DESC);
-- 2025-02-13
ALTER TABLE public.collection_params_manage ADD param_type varchar NULL;
COMMENT ON COLUMN public.collection_params_manage.param_type IS '参数类型：0:运行状态，1:启停状态，2：频率调节，3：频率反馈';
ALTER TABLE public.collection_params_manage RENAME COLUMN param_id TO communication_param_id;
COMMENT ON COLUMN public.collection_params_manage.communication_param_id IS '通信参数ID';
ALTER TABLE public.collection_params_manage ADD system_type varchar NULL;
COMMENT ON COLUMN public.collection_params_manage.system_type IS '系统类型';
CREATE TABLE device_control_log (
id varchar(36) NOT NULL,
device_name varchar(50) ,
control_type varchar(50) ,
control_content varchar(200) ,
create_user varchar(50) ,
create_time timestamp,
CONSTRAINT PK_device_control_log PRIMARY KEY (id)
);
CREATE INDEX idx_device_control_log_create_time ON device_control_log (create_time DESC);
CREATE INDEX idx_device_control_log_device_name ON device_control_log (device_name ASC);
-- Comments for table and columns
COMMENT ON TABLE device_control_log IS '设备操作历史记录';
COMMENT ON COLUMN device_control_log.id IS 'id主键';
COMMENT ON COLUMN device_control_log.device_name IS '设备名称';
COMMENT ON COLUMN device_control_log.control_type IS '操作类型';
COMMENT ON COLUMN device_control_log.control_content IS '操作内容';
COMMENT ON COLUMN device_control_log.create_user IS '操作账号';
COMMENT ON COLUMN device_control_log.create_time IS '操作日期';
-- mqtt订阅列表
CREATE TABLE mqtt_subscriptions (
id varchar(36) PRIMARY KEY,
topic VARCHAR(255) NOT NULL UNIQUE,
qos SMALLINT NOT NULL CHECK (qos >= 0 AND qos <= 2),
client_id VARCHAR(255) NOT NULL,
status bpchar(1) NOT NULL DEFAULT '0',
remark TEXT,
create_time TIMESTAMP NOT NULL DEFAULT NOW(), -- 记录创建的时间戳
update_time TIMESTAMP NOT NULL DEFAULT NOW(), -- 记录最后一次更新的时间戳
create_by VARCHAR(100) NULL, -- 创建该记录的用户
update_by VARCHAR(100) NULL -- 更新该记录的用户
);
CREATE INDEX idx_mqtt_subscriptions_create_time ON mqtt_subscriptions (create_time DESC);
CREATE INDEX idx_topic ON mqtt_subscriptions (topic);
CREATE INDEX idx_client_id ON mqtt_subscriptions (client_id);
COMMENT ON TABLE mqtt_subscriptions IS '设备操作历史记录';
COMMENT ON COLUMN mqtt_subscriptions.id IS 'id主键';
COMMENT ON COLUMN mqtt_subscriptions.topic IS 'MQTT订阅的主题（Topic），确保唯一性。';
COMMENT ON COLUMN mqtt_subscriptions.qos IS 'QoS级别（0、1或2）';
COMMENT ON COLUMN mqtt_subscriptions.client_id IS '订阅该主题的客户端ID。';
COMMENT ON COLUMN mqtt_subscriptions.status IS '状态：0：正常，1：停用';
COMMENT ON COLUMN mqtt_subscriptions.remark IS '备注';
COMMENT ON COLUMN mqtt_subscriptions.create_time IS '创建时间';
COMMENT ON COLUMN mqtt_subscriptions.update_time IS '更新时间';
COMMENT ON COLUMN mqtt_subscriptions.create_by IS '创建者';
COMMENT ON COLUMN mqtt_subscriptions.update_by IS '更新者';
ALTER TABLE public.collection_params_manage ADD collection_type varchar(10) NULL;
COMMENT ON COLUMN public.collection_params_manage.collection_type IS '采集类别：0：遥测（连续变化参数），1：遥信（离散开关状态）';
-- 2025-02-18 创建报警记录
CREATE TABLE alarm_records (
id VARCHAR(36) PRIMARY KEY, -- 报警记录唯一标识符
content TEXT NOT NULL, -- 报警内容
alarm_type VARCHAR(50) NOT NULL, -- 报警类型
event_type VARCHAR(50) NOT NULL, -- 事件类型
alarm_level VARCHAR(20) NOT NULL, -- 报警等级
ledger_id VARCHAR(36) NOT NULL, -- 设备台账ID
device_name VARCHAR(100) NOT NULL, -- 设备名称
cpm_id VARCHAR(36) NOT NULL, -- 仪表参数ID
cpm_name VARCHAR(100) NOT NULL, -- 仪表名称
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 报警时间，默认当前时间
);
COMMENT ON TABLE alarm_records IS '用于记录系统中的各种报警信息';
COMMENT ON COLUMN alarm_records.id IS '报警记录的唯一标识符';
COMMENT ON COLUMN alarm_records.content IS '详细描述报警的具体内容';
COMMENT ON COLUMN alarm_records.create_time IS '报警发生的时间戳';
COMMENT ON COLUMN alarm_records.event_type IS '触发报警的事件类型';
COMMENT ON COLUMN alarm_records.alarm_level IS '表示报警的重要程度或紧急程度';
COMMENT ON COLUMN alarm_records.ledger_id IS '关联到设备台账的唯一标识符';
COMMENT ON COLUMN alarm_records.device_name IS '涉及报警的设备名称';
COMMENT ON COLUMN alarm_records.cpm_id IS '关联到仪表参数的唯一标识符';
COMMENT ON COLUMN alarm_records.cpm_name IS '涉及报警的仪表名称';
CREATE INDEX idx_alarm_records_ledger_id ON alarm_records(ledger_id);
CREATE INDEX idx_alarm_records_create_time ON alarm_records(create_time);
ALTER TABLE public.alarm_rules ADD status int4 NULL;
COMMENT ON COLUMN public.alarm_rules.status IS '状态：0：已启用，1：未启用';
ALTER TABLE public.data_min2025 ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_min2025.register_addr IS '寄存器地址';
ALTER TABLE public.data_hour2024 ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_hour2024.register_addr IS '寄存器地址';
ALTER TABLE public.data_hour2025 ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_hour2025.register_addr IS '寄存器地址';
ALTER TABLE public.data_day2024 ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_day2024.register_addr IS '寄存器地址';
ALTER TABLE public.data_month ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_month.register_addr IS '寄存器地址';
ALTER TABLE public.data_year ADD register_addr varchar(20) NULL;
COMMENT ON COLUMN public.data_year.register_addr IS '寄存器地址';
ALTER TABLE public.device_ledger ADD system_type varchar NULL;
COMMENT ON COLUMN public.device_ledger.system_type IS '系统类型';
ALTER TABLE public.collection_params_manage ADD terminal_device_type varchar(10) NULL;
COMMENT ON COLUMN public.collection_params_manage.terminal_device_type IS '终端设备类型';
-- 预测表结构值
CREATE TABLE history_data_pre (
cur_date DATE,
system_type VARCHAR(50),
env_min_temp NUMERIC(24,2),
env_max_temp NUMERIC(24,2),
water_value NUMERIC(24,2),
elect_value NUMERIC(24,2),
water_level NUMERIC(24,2),
id BIGSERIAL PRIMARY KEY,
water_value_pre NUMERIC(24,2),
elect_value_pre NUMERIC(24,2),
water_level_pre NUMERIC(24,2),
remark VARCHAR(200)
);
CREATE INDEX history_data_pre_building_id ON history_data_pre (system_type ASC);
CREATE INDEX history_data_pre_cur_date ON history_data_pre (cur_date ASC);
-- Extended properties
COMMENT ON TABLE history_data_pre IS '历史水电用量以及预测值';
COMMENT ON COLUMN history_data_pre.cur_date IS '日期';
COMMENT ON COLUMN history_data_pre.system_type IS '系统类型';
COMMENT ON COLUMN history_data_pre.env_min_temp IS '环境最低温度';
COMMENT ON COLUMN history_data_pre.env_max_temp IS '环境最高温度';
COMMENT ON COLUMN history_data_pre.water_value IS '实际用水量';
COMMENT ON COLUMN history_data_pre.elect_value IS '实际用电量';
COMMENT ON COLUMN history_data_pre.water_level IS '平均水位';
COMMENT ON COLUMN history_data_pre.id IS 'id';
COMMENT ON COLUMN history_data_pre.water_value_pre IS '用水量预测值';
COMMENT ON COLUMN history_data_pre.elect_value_pre IS '用电量预测值';
COMMENT ON COLUMN history_data_pre.water_level_pre IS '平均水位预测值';
COMMENT ON COLUMN history_data_pre.remark IS '备注';
-- 用于存储各地区天气观测数据
CREATE TABLE weather_data (
id SERIAL PRIMARY KEY, -- 主键ID，自增长
province varchar(50) null, -- 省份名称，如"广东省"
city varchar(50) NULL, -- 城市名称，如"广州市"
ad_code varchar(10) null, -- 行政区划代码，如"440100"
weather varchar(50) null, -- 天气现象，如"晴"、"多云"、"小雨"等
temperature int null, -- 温度，单位：摄氏度
humidity int null, -- 相对湿度，单位：百分比(0-100)
wind_direction varchar(20) null, -- 风向描述，如"东北风"、"西南风"
wind_power varchar(20) null, -- 风力描述，如"3-4级"、"5级"
report_time timestamp null, -- 天气数据报告时间，记录数据采集的具体时间
created_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 记录创建时间，自动设置为当前时间
-- 注意：可根据需要添加外键约束或其他约束
-- 添加联合唯一约束，防止同一地区同一时间的重复数据
CONSTRAINT uk_weather_time_region UNIQUE (ad_code, report_time)
);
CREATE INDEX idx_weather_time ON weather_data(report_time);
-- 表注释
COMMENT ON TABLE weather_data IS '天气观测数据表，存储各地区天气信息';
-- 列注释
COMMENT ON COLUMN weather_data.province IS '省份名称';
COMMENT ON COLUMN weather_data.city IS '城市名称';
COMMENT ON COLUMN weather_data.ad_code IS '行政区划代码，国家标准编码';
COMMENT ON COLUMN weather_data.weather IS '天气现象描述';
COMMENT ON COLUMN weather_data.temperature IS '当前温度，单位：摄氏度';
COMMENT ON COLUMN weather_data.humidity IS '相对湿度，百分比(0-100)';
COMMENT ON COLUMN weather_data.wind_direction IS '风向描述';
COMMENT ON COLUMN weather_data.wind_power IS '风力等级描述';
COMMENT ON COLUMN weather_data.report_time IS '天气数据报告时间，数据来源的原始时间';
COMMENT ON COLUMN weather_data.created_time IS '记录创建时间，系统自动记录';
-- 建表语句
CREATE TABLE policy_manage (
id VARCHAR(36) PRIMARY KEY not NULL,
policy_type VARCHAR(50) NOT NULL,
policy_name VARCHAR(50) NOT NULL,
point_name VARCHAR(100) NOT NULL,
input_type VARCHAR(50) NOT NULL,
unit VARCHAR(20),
cpm_id VARCHAR(36) NOT NULL,
system_type VARCHAR(10) NOT NULL,
fun_policy_type varchar(10) not null,
FOREIGN KEY (cpm_id) REFERENCES collection_params_manage(id)
) WITH (OIDS = FALSE);
-- 添加注释
COMMENT ON TABLE policy_manage IS '策略管理核心表';
COMMENT ON COLUMN policy_manage.policy_type IS '策略类型（枚举值）';
COMMENT ON COLUMN policy_manage.policy_name IS '策略名称';
COMMENT ON COLUMN policy_manage.input_type IS '输入类型：select,text等';
COMMENT ON COLUMN policy_manage.unit IS '单位符号，允许为空表示无单位';
COMMENT ON COLUMN policy_manage.cpm_id IS '采集点位参数id';
COMMENT ON COLUMN policy_manage.system_type IS '项目类型';
COMMENT ON COLUMN policy_manage.fun_policy_type IS '功能策略类型：0 设备策略，1 定时策略';
-- 创建索引
CREATE INDEX idx_binding_param ON policy_manage(cpm_id);
-- 2025-04-17 策略管理添加字段
ALTER TABLE public.policy_manage ADD min int NULL;
COMMENT ON COLUMN public.policy_manage.min IS '最低值';
ALTER TABLE public.policy_manage ADD max int NULL;
COMMENT ON COLUMN public.policy_manage.max IS '最大值';
ALTER TABLE public.policy_manage ADD digits int NULL DEFAULT 0;
COMMENT ON COLUMN public.policy_manage.digits IS '保留的小数位';
-- mqtt消息队列
ALTER TABLE public.mqtt_subscription ADD queue_type int NULL;
COMMENT ON COLUMN public.mqtt_subscription.queue_type IS '主题类型：1（接收），2（发送）';