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
131 lines
5.7 KiB
SQL
131 lines
5.7 KiB
SQL
-- =====================================================================
|
||
-- V19 · 中控屏 P2（实时性与完整性）
|
||
-- panel_event(家庭增量事件) / ota_attempt(单设备升级尝试) / panel_configuration(配置下发)
|
||
-- product_property 扩展(semantic/channel/completionMode) / firmware 扩展(packageType/targetModel/minVersion)
|
||
-- 手工迁移（Flyway 历史版本冲突，不自动跑）；幂等，可重复执行。
|
||
-- =====================================================================
|
||
|
||
DELIMITER $$
|
||
|
||
-- 复用 V18 的加列过程（V18 内已 DROP，这里重建）
|
||
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$$
|
||
|
||
DROP PROCEDURE IF EXISTS add_pp_col$$
|
||
CREATE PROCEDURE add_pp_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 = 'product_property' AND COLUMN_NAME = col
|
||
) THEN
|
||
SET @s = CONCAT('ALTER TABLE product_property ADD COLUMN ', col, ' ', def);
|
||
PREPARE stmt FROM @s;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END IF;
|
||
END$$
|
||
|
||
DROP PROCEDURE IF EXISTS add_fw_col$$
|
||
CREATE PROCEDURE add_fw_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 = 'firmware' AND COLUMN_NAME = col
|
||
) THEN
|
||
SET @s = CONCAT('ALTER TABLE firmware ADD COLUMN ', col, ' ', def);
|
||
PREPARE stmt FROM @s;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END IF;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- ===== panel_event：家庭增量事件（长轮询游标 = id） =====
|
||
CREATE TABLE IF NOT EXISTS panel_event (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
home_id BIGINT NOT NULL,
|
||
panel_id BIGINT DEFAULT NULL COMMENT '目标屏(空=家庭内所有屏)',
|
||
device_id VARCHAR(64) DEFAULT NULL,
|
||
event_type VARCHAR(32) NOT NULL COMMENT 'DEVICE_STATE/COMMAND_RESULT/DEVICE_REGISTERED/DEVICE_OFFLINE/CONFIG_CHANGED/SCENE_RUN',
|
||
payload TEXT DEFAULT NULL COMMENT 'JSON',
|
||
create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
tenant_id BIGINT DEFAULT NULL,
|
||
create_by BIGINT DEFAULT NULL,
|
||
update_by BIGINT DEFAULT NULL,
|
||
deleted TINYINT DEFAULT 0,
|
||
PRIMARY KEY (id),
|
||
KEY idx_event_home (home_id, id),
|
||
KEY idx_event_panel (panel_id, id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏家庭增量事件';
|
||
|
||
-- ===== ota_attempt：单设备升级尝试（替代任务聚合计数，支持幂等上报） =====
|
||
CREATE TABLE IF NOT EXISTS ota_attempt (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
ota_task_id BIGINT NOT NULL,
|
||
home_id BIGINT DEFAULT NULL,
|
||
device_id VARCHAR(64) NOT NULL,
|
||
attempt_id VARCHAR(64) DEFAULT NULL COMMENT '屏侧幂等键',
|
||
report_id VARCHAR(64) DEFAULT NULL,
|
||
report_seq BIGINT DEFAULT 0,
|
||
stage VARCHAR(32) DEFAULT 'pending' COMMENT 'pending/downloading/installing/success/failed',
|
||
progress INT DEFAULT 0,
|
||
status VARCHAR(16) DEFAULT 'pending' COMMENT 'pending/success/failed',
|
||
error_code VARCHAR(32) DEFAULT NULL,
|
||
message VARCHAR(255) DEFAULT NULL,
|
||
old_version VARCHAR(32) DEFAULT NULL,
|
||
new_version VARCHAR(32) DEFAULT NULL,
|
||
create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
tenant_id BIGINT DEFAULT NULL,
|
||
create_by BIGINT DEFAULT NULL,
|
||
update_by BIGINT DEFAULT NULL,
|
||
deleted TINYINT DEFAULT 0,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_attempt (ota_task_id, device_id),
|
||
KEY idx_attempt_device (device_id, id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='单设备 OTA 升级尝试';
|
||
|
||
-- ===== panel_configuration：屏配置下发 =====
|
||
CREATE TABLE IF NOT EXISTS panel_configuration (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
panel_id BIGINT NOT NULL,
|
||
version BIGINT NOT NULL DEFAULT 1 COMMENT '每次更新递增',
|
||
config_json TEXT NOT NULL COMMENT 'JSON 配置',
|
||
checksum VARCHAR(64) DEFAULT NULL,
|
||
create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
tenant_id BIGINT DEFAULT NULL,
|
||
create_by BIGINT DEFAULT NULL,
|
||
update_by BIGINT DEFAULT NULL,
|
||
deleted TINYINT DEFAULT 0,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_panel_cfg (panel_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏配置下发';
|
||
|
||
-- ===== product_property 语义字段 =====
|
||
CALL add_pp_col('semantic', "VARCHAR(32) DEFAULT NULL COMMENT '语义标签 dimmer/thermostat/lock/cover 等'");
|
||
CALL add_pp_col('channel', "VARCHAR(32) DEFAULT NULL COMMENT '物理通道 zigbee/matter/ir 等'");
|
||
CALL add_pp_col('completionMode', "VARCHAR(16) DEFAULT NULL COMMENT 'action 完成模式 sync/async'");
|
||
|
||
-- ===== firmware 扩展 =====
|
||
CALL add_fw_col('package_type', "VARCHAR(32) DEFAULT NULL COMMENT 'full/delta'");
|
||
CALL add_fw_col('target_model', "VARCHAR(64) DEFAULT NULL COMMENT '适用机型'");
|
||
CALL add_fw_col('min_version', "VARCHAR(32) DEFAULT NULL COMMENT '最低可升级版本'");
|
||
|
||
DROP PROCEDURE IF EXISTS add_panel_col;
|
||
DROP PROCEDURE IF EXISTS add_pp_col;
|
||
DROP PROCEDURE IF EXISTS add_fw_col;
|