95 lines
5.1 KiB
SQL
95 lines
5.1 KiB
SQL
|
||
-- 新增设备日志表
|
||
CREATE TABLE IF NOT EXISTS `dev_log_record` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`dev_id` bigint(20) NULL DEFAULT NULL COMMENT '设备id',
|
||
`log_type` int(11) NULL DEFAULT NULL COMMENT '日志类型',
|
||
`log_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '日志内容',
|
||
`month` int(11) NULL DEFAULT NULL COMMENT '年月',
|
||
`log_time` datetime(0) NULL DEFAULT NULL COMMENT '日志时间',
|
||
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '入库时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `dev_id_index`(`dev_id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备日志' ROW_FORMAT = Dynamic;
|
||
|
||
-- 新增设备原始报文表
|
||
CREATE TABLE IF NOT EXISTS `dev_packet` (
|
||
`id` bigint(0) NOT NULL,
|
||
`access_id` varchar(50) COMMENT '设备接入id',
|
||
`dev_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备序列号',
|
||
`dev_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备名称',
|
||
`packet_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '报文名称',
|
||
`packet_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '报文内容',
|
||
`report_time` datetime(0) NULL DEFAULT NULL COMMENT '报文采集时间',
|
||
`product_id` bigint(0) NULL DEFAULT NULL COMMENT '产品id',
|
||
`product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品名称',
|
||
`protocol_id` bigint(0) NULL DEFAULT NULL COMMENT '协议id',
|
||
`protocol_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '协议名称',
|
||
`tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
|
||
`tenant_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '租户名称',
|
||
`creator_id` bigint(0) NULL DEFAULT NULL COMMENT '创建人id',
|
||
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `dev_id_index`(`access_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备原始报文表' ROW_FORMAT = Dynamic;
|
||
|
||
-- 指令任务表增加字段
|
||
ALTER TABLE `dev_command_task`
|
||
MODIFY COLUMN `status` tinyint(1) NOT NULL COMMENT '执行状态 0未执行 1已执行 2已终止' AFTER `command_params`,
|
||
ADD COLUMN `type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '指令任务类型: 0指令面板任务 1指令缓存任务' AFTER `update_time`,
|
||
ADD COLUMN `route` tinyint(1) NOT NULL COMMENT '下发通道: 0传输通道， 1配置通道' AFTER `type`,
|
||
ADD COLUMN `conf_protocol` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '配置协议版本号, 指令任务类型为指令缓存任务时需要记录' AFTER `route`;
|
||
|
||
-- 更新指令任务表的下发通道字段
|
||
UPDATE dev_command_task task LEFT JOIN dev_command_panel panel ON task.command_panel_id = panel.id SET task.route = panel.protocol_kind WHERE task.type = 0;
|
||
|
||
-- 设备运维表增加字段
|
||
ALTER TABLE `oper_dev_info`
|
||
ADD COLUMN `monitor_flag` tinyint(1) NULL DEFAULT 0 COMMENT '可业务监控 0否 1是' AFTER `update_time`,
|
||
ADD COLUMN `operate_flag` tinyint(1) NULL DEFAULT 1 COMMENT '可运维配置 0否 1是' AFTER `monitor_flag`;
|
||
|
||
-- 更新运维表的可业务监控、可运维配置字段
|
||
UPDATE oper_dev_info oper_dev
|
||
LEFT JOIN
|
||
dev_info dev ON oper_dev.sn = dev.sn
|
||
SET oper_dev.monitor_flag =
|
||
CASE WHEN dev.online is null
|
||
THEN 0
|
||
WHEN dev.online != 2
|
||
THEN 1
|
||
WHEN dev.online = 2
|
||
THEN 0
|
||
END
|
||
,oper_dev.operate_flag =
|
||
CASE WHEN dev.online is null
|
||
THEN 1
|
||
WHEN dev.online != 2
|
||
THEN 1
|
||
WHEN dev.online = 2
|
||
THEN 0
|
||
END;
|
||
|
||
-- 更新设备指令任务表access_id字段长度
|
||
ALTER TABLE `dev_command_task`
|
||
MODIFY COLUMN `access_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '设备接入id' AFTER `id`;
|
||
|
||
-- 设备指令任务表增加设备sn字段
|
||
ALTER TABLE `dev_command_task`
|
||
ADD COLUMN `sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备sn' AFTER `access_id`;
|
||
|
||
-- 更新指令任务旧数据的sn
|
||
update dev_command_task t left join oper_dev_info od on t.access_id = od.connect_id set t.sn = od.sn where od.sn is not null and t.access_id is not null and t.sn = '';
|
||
update dev_command_task t left join dev_info di on t.access_id = di.access_id set t.sn = di.sn where di.sn is not null and t.access_id is not null and t.sn = '';
|
||
|
||
-- 固件升级增加协议类型字段
|
||
ALTER TABLE `oper_firmware_upgrade_task`
|
||
ADD COLUMN `protocol_type` tinyint(1) NULL DEFAULT 0 COMMENT '协议类型 0 GPRS-tcp, 1 GPRS-udp, 2 LAN-tcp, 3 LAN-udp' AFTER `stoping`;
|
||
|
||
-- 挂测升级增加协议类型字段
|
||
ALTER TABLE `oper_mount_upgrade_task`
|
||
ADD COLUMN `protocol_type` tinyint(1) NULL DEFAULT 0 COMMENT '协议类型 0 GPRS-tcp, 1 GPRS-udp, 2 LAN-tcp, 3 LAN-udp' AFTER `finish_time`;
|
||
|
||
-- 原始报文表增加索引
|
||
ALTER TABLE `dev_packet`
|
||
ADD INDEX `dev_sn_index`(`dev_sn`) USING BTREE,
|
||
ADD INDEX `report_time_index`(`report_time`) USING BTREE; |