XuJiacheng
4e0f5213db
在 RCU 事件处理中新增回路名称（loop_name）字段，用于标识具体设备回路。 - 在 rcu_action_events 表中添加 loop_name 字段 - 新增项目元数据缓存模块，每日从 temporary_project 表刷新房间与回路信息 - 处理消息时，根据 device_id、dev_addr 等字段查询缓存获取回路名称 - 若缓存未命中，则根据设备类型规则生成兜底名称 - 更新环境变量、文档及测试用例以适配新功能
50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Database Initialization Script for BLS RCU Action Server
|
|
|
|
CREATE SCHEMA IF NOT EXISTS rcu_action;
|
|
|
|
CREATE TABLE IF NOT EXISTS rcu_action.rcu_action_events (
|
|
guid VARCHAR(32) NOT NULL,
|
|
ts_ms BIGINT NOT NULL,
|
|
write_ts_ms BIGINT NOT NULL,
|
|
hotel_id INTEGER NOT NULL,
|
|
room_id VARCHAR(32) NOT NULL,
|
|
device_id VARCHAR(32) NOT NULL,
|
|
direction VARCHAR(10) NOT NULL,
|
|
cmd_word VARCHAR(10) NOT NULL,
|
|
frame_id INTEGER NOT NULL,
|
|
udp_raw TEXT NOT NULL,
|
|
action_type VARCHAR(20) NOT NULL,
|
|
sys_lock_status SMALLINT,
|
|
report_count SMALLINT,
|
|
dev_type SMALLINT,
|
|
dev_addr SMALLINT,
|
|
dev_loop INTEGER,
|
|
dev_data INTEGER,
|
|
fault_count SMALLINT,
|
|
error_type SMALLINT,
|
|
error_data SMALLINT,
|
|
type_l SMALLINT,
|
|
type_h SMALLINT,
|
|
details JSONB,
|
|
extra JSONB,
|
|
loop_name VARCHAR(255),
|
|
PRIMARY KEY (ts_ms, guid)
|
|
) PARTITION BY RANGE (ts_ms);
|
|
|
|
ALTER TABLE rcu_action.rcu_action_events
|
|
ADD COLUMN IF NOT EXISTS device_id VARCHAR(32) NOT NULL DEFAULT '';
|
|
|
|
ALTER TABLE rcu_action.rcu_action_events
|
|
ADD COLUMN IF NOT EXISTS loop_name VARCHAR(255);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_hotel_id ON rcu_action.rcu_action_events (hotel_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_room_id ON rcu_action.rcu_action_events (room_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_device_id ON rcu_action.rcu_action_events (device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_direction ON rcu_action.rcu_action_events (direction);
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_cmd_word ON rcu_action.rcu_action_events (cmd_word);
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_action_type ON rcu_action.rcu_action_events (action_type);
|
|
|
|
-- Composite Index for typical query pattern (Hotel + Room + Time)
|
|
CREATE INDEX IF NOT EXISTS idx_rcu_action_query_main ON rcu_action.rcu_action_events (hotel_id, room_id, ts_ms DESC);
|