Newer
Older
newfiber-termite / doc / upgrade.sql
@xiongkai xiongkai on 4 Jun 4 KB 设备状态同步接口
--#已更新
ALTER TABLE `newfiber_standard_termite`.`ter_project_info`
ADD COLUMN `production_users` VARCHAR(512) NULL COMMENT '生产人员' AFTER `field_explain`,
ADD COLUMN `install_users` VARCHAR(512) NULL COMMENT '安装人员' AFTER `production_users`,
ADD COLUMN `analysis_users` VARCHAR(512) NULL COMMENT '分析人员' AFTER `install_users`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD COLUMN `category` VARCHAR(32) NULL DEFAULT 'project' COMMENT '分类(生产 production | 项目 project)' AFTER `id`;

ALTER TABLE `newfiber_standard_termite`.`ter_project_info`
ADD COLUMN `center_point` VARCHAR(128) NULL COMMENT '地图中心点' AFTER `analysis_users`;

--#已更新 0527
ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD COLUMN `first_warn_datetime` DATETIME NULL COMMENT '首次报警时间(连续3次手动确认则报警)' AFTER `last_pic_time`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
ADD COLUMN `heating` VARCHAR(1) NULL DEFAULT '2' COMMENT '是否加热(1是 | 2否)' AFTER `druing`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
ADD COLUMN `heating` VARCHAR(1) NULL DEFAULT '2' COMMENT '是否加热(1是 | 2否)' AFTER `druing`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
ADD COLUMN `druing_datetime` DATETIME NULL COMMENT '投药时间' AFTER `druing`,
ADD COLUMN `heating_datetime` DATETIME NULL COMMENT '加热时间' AFTER `heating`;
ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
ADD COLUMN `druing_datetime` DATETIME NULL COMMENT '投药时间' AFTER `druing`,
ADD COLUMN `heating_datetime` DATETIME NULL COMMENT '加热时间' AFTER `heating`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD COLUMN `remark` VARCHAR(45) NULL AFTER `first_warn_datetime`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
ADD COLUMN `remark` VARCHAR(45) NULL AFTER `camera`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
ADD COLUMN `remark` VARCHAR(45) NULL AFTER `camera`,
ADD COLUMN `status` VARCHAR(16) NULL AFTER `remark`,
ADD COLUMN `create_by` VARCHAR(64) NULL AFTER `status`,
ADD COLUMN `create_time` DATETIME NULL AFTER `create_by`,
ADD COLUMN `update_by` VARCHAR(64) NULL AFTER `create_time`,
ADD COLUMN `update_time` DATETIME NULL AFTER `update_by`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data`
ADD COLUMN `del_flag` CHAR(1) NULL AFTER `update_time`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_monitoring_data_realtime`
ADD COLUMN `del_flag` CHAR(1) NULL AFTER `update_time`;

update ter_device_info set del_flag = 0;
update ter_device_monitoring_data_realtime set del_flag = 0;
update ter_device_monitoring_data set del_flag = 0;

--未更新 0603

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
 ADD COLUMN  `software` varchar(255) DEFAULT NULL COMMENT '软件版本' AFTER `first_warn_datetime`,
 ADD COLUMN  `hardware` varchar(255) DEFAULT NULL COMMENT '硬件版本' AFTER `software`,
 ADD COLUMN  `voltage` varchar(255) DEFAULT NULL COMMENT '电压' AFTER `hardware`,
 ADD COLUMN  `temperature` varchar(255) DEFAULT NULL COMMENT '温度' AFTER `voltage`,
 ADD COLUMN  `rssi` varchar(100) DEFAULT NULL  COMMENT '信号质量' AFTER `temperature`,
 ADD COLUMN  `ap` varchar(100) DEFAULT NULL COMMENT '中继名称'  AFTER `rssi`,
 ADD COLUMN  `camera` varchar(100) DEFAULT NULL  COMMENT '摄像头' AFTER `ap`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD UNIQUE INDEX `index2` (`sn` ASC);

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD COLUMN `extend_config` VARCHAR(1024) NULL COMMENT '扩展配置(JSON)' AFTER `lonandlat`,
CHANGE COLUMN `del_flag` `del_flag` VARCHAR(1) NULL DEFAULT '0' COMMENT '逻辑删除  0:否 1:是' AFTER `remark`;

ALTER TABLE `newfiber_standard_termite`.`ter_device_info`
ADD COLUMN `sync_state` VARCHAR(32) NULL DEFAULT 'success' COMMENT '同步状态(成功 success | 失败 fail)' AFTER `camera`;