0ce191c448
纳入范围: - backend/ Spring Boot 多模块后端(iot-common/device/rule/data/alarm/auth) - frontend/admin/ Vue3 + Element Plus 管理后台 - Android/ 涂鸦原生 APP (com.xhai.iot) - backend/sql/ Flyway 风格迁移脚本(V2~V21) - docs/ 对接文档与设计文档 排除范围: - 构建产物: node_modules/ target/ build/ dist/ .gradle/ - 签名密钥: *.jks *.keystore (严禁入库) - 本机配置: Android/local.properties - 运行日志: logs/ *.log - AI 协作本地数据 .codebuddy/ 与第三方 demo tuya-demo/ .gitattributes 统一以 LF 存储, Windows 批处理保留 CRLF
165 lines
10 KiB
SQL
165 lines
10 KiB
SQL
-- V18 中控屏直连接入（P1 一期）
|
||
-- 对应文档：docs/10寸中控屏/05-评审回复与落地方案.md
|
||
--
|
||
-- 说明：
|
||
-- 1) 生产库 device 表的 parent_device_id / is_gateway 两列已存在（历史手工补列），
|
||
-- 本次不再重复 ADD；新环境请确认 init.sql 建表语句中已包含这两列。
|
||
-- 2) 本文件按团队现状以手动方式执行（Flyway 历史版本号存在重复）。
|
||
-- 3) 各表均包含 BaseEntity 审计列（create_time/update_time/deleted/tenant_id/create_by/update_by）。
|
||
|
||
-- ============ 中控屏终端身份 ============
|
||
CREATE TABLE IF NOT EXISTS panel_terminal (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
serial_number VARCHAR(64) NOT NULL COMMENT '屏序列号(与预置终端记录核对)',
|
||
installation_id VARCHAR(64) DEFAULT NULL COMMENT '当前应用安装实例ID',
|
||
bootstrap_secret_hash VARCHAR(128) NOT NULL COMMENT '终端初始化凭证 BCrypt 哈希',
|
||
home_id BIGINT DEFAULT NULL COMMENT '绑定的家庭ID',
|
||
member_id BIGINT DEFAULT NULL COMMENT '关联的会员ID(业务上下文)',
|
||
gateway_device_id VARCHAR(64) DEFAULT NULL COMMENT '对应的网关设备 deviceId',
|
||
role VARCHAR(16) NOT NULL DEFAULT 'GATEWAY' COMMENT 'GATEWAY / DISPLAY',
|
||
enabled TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0禁用',
|
||
features VARCHAR(512) DEFAULT NULL COMMENT '支持的协议/功能(JSON数组文本)',
|
||
last_seen_at DATETIME DEFAULT NULL COMMENT '最近心跳时间',
|
||
last_ip VARCHAR(64) DEFAULT NULL COMMENT '最近来源IP',
|
||
current_boot_id VARCHAR(64) DEFAULT NULL COMMENT '当前有效启动实例',
|
||
state_epoch BIGINT NOT NULL DEFAULT 0 COMMENT '状态世代(bootId 变更时递增)',
|
||
app_version VARCHAR(32) DEFAULT NULL COMMENT '应用版本',
|
||
model VARCHAR(64) DEFAULT NULL COMMENT '屏型号',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除 0未删 1已删',
|
||
tenant_id BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
create_by BIGINT DEFAULT NULL COMMENT '创建人',
|
||
update_by BIGINT DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_panel_serial (serial_number)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏终端身份';
|
||
|
||
-- ============ 中控屏刷新令牌（支持轮换与撤销） ============
|
||
CREATE TABLE IF NOT EXISTS panel_refresh_token (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
panel_id BIGINT NOT NULL COMMENT 'panel_terminal.id',
|
||
token_hash VARCHAR(128) NOT NULL COMMENT 'refresh token 的 SHA-256 哈希',
|
||
boot_id VARCHAR(64) DEFAULT NULL COMMENT '签发时的启动实例',
|
||
expires_at DATETIME NOT NULL COMMENT '过期时间',
|
||
revoked TINYINT NOT NULL DEFAULT 0 COMMENT '1已撤销/已轮换',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',
|
||
tenant_id BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
create_by BIGINT DEFAULT NULL COMMENT '创建人',
|
||
update_by BIGINT DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_panel_refresh_hash (token_hash),
|
||
KEY idx_panel_refresh_panel (panel_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏刷新令牌(支持轮换与撤销)';
|
||
|
||
-- ============ 设备控制命令单据（状态机，支持中控屏异步执行回执） ============
|
||
CREATE TABLE IF NOT EXISTS device_command (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
command_id VARCHAR(64) NOT NULL COMMENT '对外命令ID',
|
||
device_id VARCHAR(64) NOT NULL COMMENT '平台业务设备ID',
|
||
panel_id BIGINT DEFAULT NULL COMMENT '执行屏(execution_source=LOCAL_PANEL 时)',
|
||
home_id BIGINT DEFAULT NULL COMMENT '所属家庭',
|
||
command_type VARCHAR(16) NOT NULL COMMENT 'set / action',
|
||
params_json TEXT COMMENT '命令参数(JSON)',
|
||
execution_source VARCHAR(16) NOT NULL DEFAULT 'CLOUD' COMMENT 'CLOUD / LOCAL_PANEL',
|
||
state VARCHAR(24) NOT NULL DEFAULT 'QUEUED' COMMENT 'QUEUED/DELIVERED/EXECUTING/ACCEPTED/CONFIRMED/PARTIAL_FAILURE/FAILED/EXPIRED/CANCELLED/UNKNOWN',
|
||
terminal TINYINT NOT NULL DEFAULT 0 COMMENT '是否终态',
|
||
route_revision BIGINT DEFAULT 0 COMMENT '执行归属版本',
|
||
schema_revision BIGINT DEFAULT 0 COMMENT '依据的物模型版本',
|
||
completion_mode VARCHAR(16) DEFAULT 'STATE' COMMENT 'STATE / ACK_ONLY',
|
||
delivery_token VARCHAR(128) DEFAULT NULL COMMENT '投递凭证(一次性)',
|
||
delivery_expires_at DATETIME DEFAULT NULL COMMENT '投递租期截止',
|
||
execution_token VARCHAR(128) DEFAULT NULL COMMENT '执行凭证',
|
||
expires_at DATETIME NOT NULL COMMENT '命令总有效期截止',
|
||
result_json TEXT COMMENT '执行结果与证据(JSON)',
|
||
report_seq BIGINT DEFAULT 0 COMMENT '已接受的最大回报序号',
|
||
error_code VARCHAR(64) DEFAULT NULL COMMENT '结构化错误码',
|
||
error_message VARCHAR(512) DEFAULT NULL COMMENT '错误说明',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',
|
||
tenant_id BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
create_by BIGINT DEFAULT NULL COMMENT '创建人',
|
||
update_by BIGINT DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_command_id (command_id),
|
||
KEY idx_cmd_device (device_id),
|
||
KEY idx_cmd_panel_state (panel_id, state)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备控制命令单据(支持中控屏异步执行回执)';
|
||
|
||
-- ============ 设备属性级当前状态（带序号，防止旧状态覆盖新状态） ============
|
||
CREATE TABLE IF NOT EXISTS device_state (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
device_id VARCHAR(64) NOT NULL COMMENT '平台业务设备ID',
|
||
property_code VARCHAR(64) NOT NULL COMMENT '属性码',
|
||
property_value VARCHAR(512) DEFAULT NULL COMMENT '属性值(文本化存储)',
|
||
state_epoch BIGINT NOT NULL DEFAULT 0 COMMENT '屏端状态世代',
|
||
sequence BIGINT NOT NULL DEFAULT 0 COMMENT '该世代内设备级递增序号',
|
||
quality VARCHAR(16) DEFAULT 'GOOD' COMMENT 'GOOD / STALE / UNAVAILABLE',
|
||
reported_at DATETIME DEFAULT NULL COMMENT '屏观测时间',
|
||
received_at DATETIME DEFAULT NULL COMMENT '平台接收时间',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',
|
||
tenant_id BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
create_by BIGINT DEFAULT NULL COMMENT '创建人',
|
||
update_by BIGINT DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_state_device_prop (device_id, property_code),
|
||
KEY idx_state_device (device_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备属性级当前状态(带序号)';
|
||
|
||
-- ============ 幂等记录（保留 7 天） ============
|
||
CREATE TABLE IF NOT EXISTS idempotency_record (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
scope_key VARCHAR(128) NOT NULL COMMENT '范围键: 租户+调用主体+方法+规范化路径',
|
||
idempotency_key VARCHAR(128) NOT NULL COMMENT '客户端幂等键',
|
||
request_hash VARCHAR(128) NOT NULL COMMENT '请求内容指纹(用于冲突检测)',
|
||
response_json TEXT COMMENT '首次响应快照(用于重放)',
|
||
expires_at DATETIME NOT NULL COMMENT '过期时间(默认7天)',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',
|
||
tenant_id BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
create_by BIGINT DEFAULT NULL COMMENT '创建人',
|
||
update_by BIGINT DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_idem_scope_key (scope_key, idempotency_key),
|
||
KEY idx_idem_expires (expires_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='幂等记录(保留7天)';
|
||
|
||
-- ============ device 表新增中控屏相关列（幂等，可重复执行） ============
|
||
DELIMITER $$
|
||
DROP PROCEDURE IF EXISTS add_panel_col$$
|
||
CREATE PROCEDURE add_panel_col(IN col VARCHAR(64), IN def TEXT)
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'device' AND COLUMN_NAME = col
|
||
) THEN
|
||
SET @s = CONCAT('ALTER TABLE device ADD COLUMN ', col, ' ', def);
|
||
PREPARE stmt FROM @s;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END IF;
|
||
END$$
|
||
DELIMITER ;
|
||
|
||
CALL add_panel_col('executor_panel_id', "BIGINT DEFAULT NULL COMMENT '执行屏ID(LOCAL_PANEL 时)'");
|
||
CALL add_panel_col('route_revision', "BIGINT DEFAULT 0 COMMENT '执行归属版本'");
|
||
CALL add_panel_col('schema_revision', "BIGINT DEFAULT 0 COMMENT '设备级物模型版本'");
|
||
CALL add_panel_col('local_device_id', "VARCHAR(64) DEFAULT NULL COMMENT '屏内稳定设备ID'");
|
||
CALL add_panel_col('protocol', "VARCHAR(32) DEFAULT NULL COMMENT 'MATTER/ZIGBEE/BLE_GATT/BLUETOOTH_MESH'");
|
||
CALL add_panel_col('execution_source', "VARCHAR(16) DEFAULT 'CLOUD' COMMENT 'CLOUD / LOCAL_PANEL'");
|
||
CALL add_panel_col('schema_json', "TEXT COMMENT '设备级标准控制模型(JSON，中控屏登记/更新时上报)'");
|
||
DROP PROCEDURE IF EXISTS add_panel_col;
|
||
|
||
SET @idx = (SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'device' AND INDEX_NAME = 'idx_device_executor');
|
||
SET @s = IF(@idx = 0, 'CREATE INDEX idx_device_executor ON device(executor_panel_id)', 'SELECT 1');
|
||
PREPARE stmt FROM @s;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|