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
133 lines
7.0 KiB
SQL
133 lines
7.0 KiB
SQL
-- ============================================================
|
||
-- 产品管理模块（Product & TSL & OTA）
|
||
-- 新增产品、物模型（属性/事件/服务）、固件、OTA 任务；
|
||
-- device 表新增 product_id（保留原 model/manufacturer 作为兼容快照，后续再废弃）。
|
||
-- ============================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `product` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_key` VARCHAR(64) NOT NULL COMMENT '产品业务编码',
|
||
`name` VARCHAR(128) NOT NULL COMMENT '产品名称',
|
||
`category` VARCHAR(32) DEFAULT '' COMMENT '品类(light/switch/ac/security...)',
|
||
`manufacturer` VARCHAR(64) DEFAULT '' COMMENT '厂商',
|
||
`model` VARCHAR(64) DEFAULT '' COMMENT '型号',
|
||
`platform` VARCHAR(32) DEFAULT 'tuya' COMMENT '接入平台(tuya/local/matter)',
|
||
`platform_product_id` VARCHAR(64) DEFAULT '' COMMENT '平台产品ID(如涂鸦productId)',
|
||
`connect_type` VARCHAR(32) DEFAULT '' COMMENT '联网方式(wifi/ble/zigbee/matter)',
|
||
`net_config_type` VARCHAR(32) DEFAULT '' COMMENT '配网方式',
|
||
`icon` VARCHAR(255) DEFAULT '' COMMENT '图标',
|
||
`description` VARCHAR(512) DEFAULT '' COMMENT '说明',
|
||
`status` VARCHAR(16) DEFAULT 'draft' COMMENT 'draft/published',
|
||
`spec_version` INT DEFAULT 1 COMMENT '物模型版本',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_product_key` (`product_key`),
|
||
KEY `idx_platform_product` (`platform`, `platform_product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `product_property` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`code` VARCHAR(64) NOT NULL COMMENT '功能点编码(如 switch_1)',
|
||
`name` VARCHAR(128) DEFAULT '' COMMENT '名称',
|
||
`access` VARCHAR(16) DEFAULT 'read' COMMENT 'read/write/notify',
|
||
`type` VARCHAR(16) DEFAULT 'bool' COMMENT 'bool/int/enum/float/string/json',
|
||
`min` VARCHAR(32) DEFAULT '' COMMENT '最小值',
|
||
`max` VARCHAR(32) DEFAULT '' COMMENT '最大值',
|
||
`step` VARCHAR(32) DEFAULT '' COMMENT '步长',
|
||
`unit` VARCHAR(32) DEFAULT '' COMMENT '单位',
|
||
`enum_list` TEXT COMMENT '枚举值(JSON数组)',
|
||
`dp_id` VARCHAR(32) DEFAULT '' COMMENT '平台DP点映射(如涂鸦DP)',
|
||
`default_value` VARCHAR(128) DEFAULT '' COMMENT '默认值',
|
||
`sort` INT DEFAULT 0 COMMENT '排序',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_product_property` (`product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品属性(物模型)';
|
||
|
||
CREATE TABLE IF NOT EXISTS `product_event` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`code` VARCHAR(64) NOT NULL COMMENT '事件编码',
|
||
`name` VARCHAR(128) DEFAULT '' COMMENT '名称',
|
||
`type` VARCHAR(16) DEFAULT 'info' COMMENT 'info/alert/fault',
|
||
`params` TEXT COMMENT '事件参数(JSON)',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_product_event` (`product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品事件(物模型)';
|
||
|
||
CREATE TABLE IF NOT EXISTS `product_action` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`code` VARCHAR(64) NOT NULL COMMENT '服务编码',
|
||
`name` VARCHAR(128) DEFAULT '' COMMENT '名称',
|
||
`input` TEXT COMMENT '输入参数(JSON)',
|
||
`output` TEXT COMMENT '输出参数(JSON)',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_product_action` (`product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品服务(物模型)';
|
||
|
||
CREATE TABLE IF NOT EXISTS `firmware` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`version` VARCHAR(64) NOT NULL COMMENT '固件版本',
|
||
`file_url` VARCHAR(512) DEFAULT '' COMMENT '升级包地址',
|
||
`file_md5` VARCHAR(64) DEFAULT '' COMMENT '校验MD5',
|
||
`size` BIGINT DEFAULT 0 COMMENT '大小(字节)',
|
||
`release_note` VARCHAR(512) DEFAULT '' COMMENT '更新说明',
|
||
`is_latest` INT DEFAULT 0 COMMENT '是否最新(0/1)',
|
||
`status` VARCHAR(16) DEFAULT 'draft' COMMENT 'draft/published',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_firmware_product` (`product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='固件表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `ota_task` (
|
||
`id` BIGINT NOT NULL COMMENT '主键',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`firmware_id` BIGINT DEFAULT NULL COMMENT '目标固件ID',
|
||
`target_version` VARCHAR(64) DEFAULT '' COMMENT '目标版本',
|
||
`scope` VARCHAR(16) DEFAULT 'all' COMMENT 'all/designated/gray',
|
||
`device_ids` TEXT COMMENT '指定设备ID(JSON数组)',
|
||
`status` VARCHAR(16) DEFAULT 'running' COMMENT 'running/done/paused/failed',
|
||
`success_count` INT DEFAULT 0 COMMENT '成功数',
|
||
`fail_count` INT DEFAULT 0 COMMENT '失败数',
|
||
`tenant_id` BIGINT DEFAULT NULL,
|
||
`create_time` DATETIME DEFAULT NULL,
|
||
`update_time` DATETIME DEFAULT NULL,
|
||
`deleted` INT DEFAULT 0,
|
||
`create_by` BIGINT DEFAULT NULL,
|
||
`update_by` BIGINT DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_ota_product` (`product_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OTA升级任务';
|
||
|
||
ALTER TABLE `device` ADD COLUMN `product_id` BIGINT DEFAULT NULL COMMENT '所属产品ID' AFTER `home_id`;
|