142 lines
7.4 KiB
SQL
142 lines
7.4 KiB
SQL
-- ============================================================================
|
||
-- 数据库初始化脚本
|
||
-- 描述：创建 log_platform 库（逻辑参考）、room_status 模式及 room_status_moment 分区表
|
||
-- 对应项目需求：project.md #L57-65
|
||
-- ============================================================================
|
||
|
||
-- 注意：在 PostgreSQL 中，CREATE DATABASE 不能在事务块中执行。
|
||
-- 通常建议先手动创建数据库，然后再执行后续脚本。
|
||
-- CREATE DATABASE log_platform;
|
||
|
||
-- 切换到 log_platform 数据库后执行以下内容：
|
||
|
||
-- 1. 创建模式
|
||
CREATE SCHEMA IF NOT EXISTS room_status;
|
||
|
||
-- 2. 创建主表 (使用声明式分区)
|
||
-- 根据需求 L57-65，考虑后期十万级以上数据的扩展，按 hotel_id 进行 LIST 分区
|
||
CREATE TABLE IF NOT EXISTS room_status.room_status_moment (
|
||
-- 基础标识字段
|
||
guid UUID NOT NULL,
|
||
ts_ms INT8 NOT NULL DEFAULT (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000)::BIGINT,
|
||
hotel_id INT2 NOT NULL,
|
||
room_id TEXT NOT NULL,
|
||
device_id TEXT NOT NULL,
|
||
|
||
-- 设备状态字段
|
||
sys_lock_status INT2,
|
||
online_status INT2,
|
||
launcher_version TEXT,
|
||
app_version TEXT,
|
||
config_version TEXT,
|
||
register_ts_ms INT8,
|
||
upgrade_ts_ms INT8,
|
||
config_ts_ms INT8,
|
||
ip TEXT,
|
||
|
||
-- 房间业务状态字段
|
||
pms_status INT2,
|
||
power_state INT2,
|
||
cardless_state INT2,
|
||
service_mask INT8,
|
||
insert_card INT2,
|
||
bright_g INT2,
|
||
agreement_ver TEXT,
|
||
|
||
-- 空调相关
|
||
air_address TEXT[],
|
||
air_state INT2[],
|
||
air_model INT2[],
|
||
air_speed INT2[],
|
||
air_set_temp INT2[],
|
||
air_now_temp INT2[],
|
||
air_solenoid_valve INT2[],
|
||
|
||
-- 能耗相关
|
||
elec_address TEXT[],
|
||
elec_voltage DOUBLE PRECISION[],
|
||
elec_ampere DOUBLE PRECISION[],
|
||
elec_power DOUBLE PRECISION[],
|
||
elec_phase DOUBLE PRECISION[],
|
||
elec_energy DOUBLE PRECISION[],
|
||
elec_sum_energy DOUBLE PRECISION[],
|
||
|
||
-- 节能与外设
|
||
carbon_state INT2,
|
||
dev_loops JSONB,
|
||
energy_carbon_sum DOUBLE PRECISION,
|
||
energy_nocard_sum DOUBLE PRECISION,
|
||
external_device JSONB DEFAULT '{}',
|
||
faulty_device_count JSONB DEFAULT '{}',
|
||
|
||
-- 约束：分区表的主键必须包含分区键 (hotel_id)
|
||
PRIMARY KEY (hotel_id, room_id, device_id, guid)
|
||
) PARTITION BY LIST (hotel_id);
|
||
|
||
-- 3. 创建索引 (针对高频查询字段)
|
||
-- 注意：在分区表上创建索引会自动在所有子表上创建对应的索引
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_hotel_room ON room_status.room_status_moment (hotel_id, room_id);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_device_id ON room_status.room_status_moment (device_id);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_sys_lock ON room_status.room_status_moment (sys_lock_status);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_online ON room_status.room_status_moment (online_status);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_pms ON room_status.room_status_moment (pms_status);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_power ON room_status.room_status_moment (power_state);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_cardless ON room_status.room_status_moment (cardless_state);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_insert_card ON room_status.room_status_moment (insert_card);
|
||
CREATE INDEX IF NOT EXISTS idx_room_status_moment_carbon ON room_status.room_status_moment (carbon_state);
|
||
|
||
-- 3.1 唯一索引 (支持 UPSERT)
|
||
-- 必须在 (hotel_id, room_id, device_id) 上建立唯一约束，才能使用 ON CONFLICT
|
||
CREATE UNIQUE INDEX IF NOT EXISTS idx_room_status_unique_device
|
||
ON room_status.room_status_moment (hotel_id, room_id, device_id);
|
||
|
||
-- 4. 示例：创建第一个分区 (hotel_id = 1)
|
||
-- 实际部署时，可根据 hotel_id 动态创建分区
|
||
CREATE TABLE IF NOT EXISTS room_status.room_status_moment_h1
|
||
PARTITION OF room_status.room_status_moment
|
||
FOR VALUES IN (1);
|
||
|
||
-- 5. 添加表和字段注释
|
||
COMMENT ON TABLE room_status.room_status_moment IS '房间即时状态表 - 记录设备及房间业务的最新实时状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.guid IS '主键 guid uuid 32位无符号UUID';
|
||
COMMENT ON COLUMN room_status.room_status_moment.ts_ms IS '最后更新时间';
|
||
COMMENT ON COLUMN room_status.room_status_moment.hotel_id IS '酒店';
|
||
COMMENT ON COLUMN room_status.room_status_moment.room_id IS '房间';
|
||
COMMENT ON COLUMN room_status.room_status_moment.device_id IS '设备编号';
|
||
COMMENT ON COLUMN room_status.room_status_moment.sys_lock_status IS '系统锁状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.online_status IS '设备在线状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.launcher_version IS '设备launcher版本';
|
||
COMMENT ON COLUMN room_status.room_status_moment.app_version IS '设备App版本';
|
||
COMMENT ON COLUMN room_status.room_status_moment.config_version IS '设备配置版本';
|
||
COMMENT ON COLUMN room_status.room_status_moment.register_ts_ms IS '最后一次注册时间';
|
||
COMMENT ON COLUMN room_status.room_status_moment.upgrade_ts_ms IS '最后一次升级时间';
|
||
COMMENT ON COLUMN room_status.room_status_moment.config_ts_ms IS '最后一次下发配置时间';
|
||
COMMENT ON COLUMN room_status.room_status_moment.ip IS '当前公网IP地址';
|
||
COMMENT ON COLUMN room_status.room_status_moment.pms_status IS 'PMS状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.power_state IS '取电状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.cardless_state IS '有、无人状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.service_mask IS '服务状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.insert_card IS '插卡状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_address IS '空调地址';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_state IS '空调状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_model IS '空调模型';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_speed IS '空调风速';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_set_temp IS '空调设置温度';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_now_temp IS '房间当前温度';
|
||
COMMENT ON COLUMN room_status.room_status_moment.air_solenoid_valve IS '空调电磁阀状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_address IS '能耗表地址';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_voltage IS '能耗表电压';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_ampere IS '能耗表电流';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_power IS '能耗表功率';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_phase IS '当前相位';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_energy IS '能耗表能耗';
|
||
COMMENT ON COLUMN room_status.room_status_moment.elec_sum_energy IS '能耗表累计能耗';
|
||
COMMENT ON COLUMN room_status.room_status_moment.carbon_state IS '碳达人状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.bright_g IS '光亮值';
|
||
COMMENT ON COLUMN room_status.room_status_moment.agreement_ver IS '协议版本';
|
||
COMMENT ON COLUMN room_status.room_status_moment.dev_loops IS '回路状态';
|
||
COMMENT ON COLUMN room_status.room_status_moment.energy_carbon_sum IS '碳达人节能累计';
|
||
COMMENT ON COLUMN room_status.room_status_moment.energy_nocard_sum IS '无卡节能累计';
|
||
COMMENT ON COLUMN room_status.room_status_moment.external_device IS '外设设备管理（数组）';
|
||
COMMENT ON COLUMN room_status.room_status_moment.faulty_device_count IS '故障设备数量';
|