4x/device/0script/sql/dev_cloud-2023-08-24_v2.7.0.sql

-- 固件升级包增加类型、租户、更新者字段
ALTER TABLE `oper_firmware` 
	ADD COLUMN `updater_id` bigint(0) NULL COMMENT '更新者id' AFTER `firmware_version`,
	ADD COLUMN `type` tinyint(0) NULL DEFAULT 0 COMMENT '升级包类型(0:通用,1:定制)' AFTER `updater_id`,
	ADD COLUMN `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '所属租户ID' AFTER `type`;
	
-- 固件升级任务增加应用id, 来源字段
ALTER TABLE `oper_firmware_upgrade_task_batch` 
	ADD COLUMN `application_id` bigint(0) NULL DEFAULT NULL COMMENT '应用id' AFTER `finish_time`,
	ADD COLUMN `creator_from` tinyint(0) NULL DEFAULT 0 COMMENT '创建者来源:0设备管家 1运维助手' AFTER `application_id`,
    ADD COLUMN `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '创建者租户id' AFTER `creator_from`;
	
-- 指令任务增加来源字段
ALTER TABLE `dev_command_task` 
    ADD COLUMN `application_id` bigint(0) NULL DEFAULT NULL COMMENT '应用id' AFTER `conf_protocol`,
    ADD COLUMN `creator_from` tinyint(0) NULL DEFAULT 0 COMMENT '创建者来源:0设备管家 1运维助手' AFTER `application_id`,
    ADD COLUMN `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '创建者租户id' AFTER `creator_from`;

-- 原始报文增加功能码
ALTER TABLE `dev_packet` 
    ADD COLUMN `function_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '功能码' AFTER `create_time`;

-- 运维设备表增加租户、初次上线时间字段
ALTER TABLE `oper_dev_info`
    ADD COLUMN `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户ID' AFTER `operate_flag`,
    ADD COLUMN `active_time` datetime(0) NULL COMMENT '初次上线时间' AFTER `tenant_id`;

-- 设备表增加产品id
ALTER TABLE `dev_info`
    ADD COLUMN `product_id` bigint(0) NULL COMMENT '产品id' AFTER `dev_picture`;

-- 增加运维助手-用户应用运维权限关联表
CREATE TABLE `assistant_user_application`  (
    `id` bigint(0) NOT NULL,
    `user_id` bigint(0) NOT NULL COMMENT '用户id',
    `tenant_id` bigint(0) NOT NULL COMMENT '租户id',
    `application_id` bigint(0) NOT NULL COMMENT '应用id',
    `creator_id` bigint(0) NULL DEFAULT NULL COMMENT '创建人id',
    `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '运维助手-用户应用运维权限关联表' ROW_FORMAT = Dynamic;

-- 添加要素标签字典
INSERT INTO `sys_dict`(`type_code`, `type_name`, `order_no`, `create_time`, `update_time`) VALUES ('factor_label', '要素标签', 5, '2023-10-19 18:13:07', '2023-10-19 18:13:09');
INSERT INTO `sys_dict_item`(`type_code`, `value_code`, `value_name`, `order_no`, `remark`, `create_time`, `update_time`) VALUES ('factor_label', 'signal', '信号', 1, '', '2023-10-19 18:13:09', '2023-10-19 18:13:09');
INSERT INTO `sys_dict_item`(`type_code`, `value_code`, `value_name`, `order_no`, `remark`, `create_time`, `update_time`) VALUES ('factor_label', 'voltage', '电压', 2, '', '2023-10-19 18:13:09', '2023-10-19 18:13:09');

-- 设备模板要素配置增加要素标签字段
ALTER TABLE `dev_template_factor_conf_snap`
    ADD COLUMN `factor_label` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '要素标签' AFTER `factor_group`;
ALTER TABLE `dev_template_factor_conf_release`
    ADD COLUMN `factor_label` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '要素标签' AFTER `factor_group`;

-- 加解密
CREATE TABLE `dev_cipher` (
  `id` bigint(20) NOT NULL,
  `rsa_private_key` text,
  `rsa_public_key` text,
  `rsa_public_key_md5` varchar(32) DEFAULT NULL,
  `sm2_private_key` text,
  `sm2_public_key` text,
  `sm2_public_key_md5` varchar(32) DEFAULT NULL,
  `device_sm2_public_key` text,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -- -- -- -- -- -- -- -- 以下是旧版本v2.6.0升级到版本v2.7.0的旧数据适配更新sql -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- 更新设备表的产品id值到新字段(维护旧数据适应新版本)
UPDATE dev_info d
    LEFT JOIN dev_template AS dt ON d.template_id = dt.id
    LEFT JOIN dev_product AS dp ON d.product_id = dp.id
    SET d.product_id = dt.product_id
WHERE
    dt.product_id IS NOT NULL;

-- 设备表产品id覆盖运维设备产品id(维护旧数据适应新版本)
UPDATE oper_dev_info od
    LEFT JOIN dev_info d ON d.sn = od.sn
    SET od.init_product_id = d.product_id
WHERE
    d.product_id IS NOT NULL;

-- 更新运维设备表初次上线时间(维护旧数据适应新版本)
UPDATE oper_dev_info odi
    LEFT JOIN dev_info di ON odi.sn = di.sn
    SET odi.active_time =
        CASE WHEN odi.online != 2
        THEN IFNULL(odi.online_change_time, odi.create_time)
        WHEN di.id IS NOT NULL
        THEN odi.create_time
END
WHERE
	odi.active_time IS NULL;

-- 补充白名单设备(之前版本在设备管理存在，在运维设备表不存在的设备)
INSERT INTO oper_dev_info (id, sn, init_product_id, tenant_id, creator_id, create_time, update_time, online, monitor_flag, operate_flag)
SELECT
    dev.id AS id,
    dev.sn AS sn,
    dev.product_id AS init_product_id,
    dev.tenant_id AS tenant_id,
    ifnull(dev.creator_id, 1099) AS creator_id,
    dev.create_time AS create_time,
    dev.update_time AS update_time,
    2 AS online,
    1 AS monitor_flag,
    if(dp.config_protocol_id IS NOT NULL AND dp.config_protocol_id != '' OR dl.type = 'dz', 1, 0) AS operate_flag
FROM
    dev_info dev
    LEFT JOIN dev_product dp ON dev.product_id = dp.id
    LEFT JOIN dev_template dt ON dev.template_id = dt.id
    LEFT JOIN dev_protocol dl ON dt.protocol_id = dl.id
WHERE
    sn NOT IN ( SELECT sn FROM oper_dev_info );

-- 更新设备白名单的租户信息
UPDATE oper_dev_info od
    LEFT JOIN dev_info d ON d.sn = od.sn
    SET od.tenant_id = IFNULL(d.tenant_id, 0)
WHERE
    od.tenant_id IS NULL
    OR od.tenant_id = '';