# 2021.04.06 V1.0
## 更新内容(数据库)

### sms_monitor_info 添加字段type
1、ALTER TABLE sms_monitor_info ADD TYPE int;
### sms_alarm_setting 添加字段START_TIME、END_TIME
1、ALTER TABLE sms_alarm_setting ADD START_TIME varchar(20) null COMMENT '报警设置开始时间',
  				ADD END_TIME varchar(20) null COMMENT '报警设置结束时间';


## uims数据库

### uims_user 添加字段
1、ALTER TABLE uims_user ADD login_account varchar(50) comment '登录第三方账号'
2、ALTER TABLE uims_user ADD login_secret_key varchar(50) comment '登录第三方密钥'
3、ALTER TABLE uims_user ADD login_code varchar(50) comment '登录第三方厂家编码'
4、ALTER TABLE uims_user ADD login_org_code varchar(50) comment '登录第三方机构编码'
### sms_month_report表添加索引
1.ALTER TABLE sms_month_report ADD  INDEX `index_collect_date`(`COLLECT_DATE`) USING BTREE 

# 创建报表维度配置表 sms_report_dimension_setting
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sms_report_dimension_setting
-- ----------------------------
DROP TABLE IF EXISTS `sms_report_dimension_setting`;
CREATE TABLE `sms_report_dimension_setting`  (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
`tenant_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '租户ID',
`device_parm_id` int(11) NULL DEFAULT NULL COMMENT '设备参数ID',
`classify` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '归类',
`show_on_off` smallint(6) NULL DEFAULT NULL COMMENT '展示开关',
`show_min_value` smallint(6) NULL DEFAULT NULL COMMENT '是否展示最小值',
`show_avg_value` smallint(6) NULL DEFAULT NULL COMMENT '是否展示平均值',
`show_max_value` smallint(6) NULL DEFAULT NULL COMMENT '是否展示最大值',
`show_sum_value` smallint(6) NULL DEFAULT NULL COMMENT '是否展示合计值',
`show_latest_value` smallint(6) NULL DEFAULT NULL COMMENT '是否展示最新值',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 72 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

#创建水泵状态报表 sms_report_water_pump_state
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sms_report_water_pump_state
-- ----------------------------
DROP TABLE IF EXISTS `sms_report_water_pump_state`;
CREATE TABLE `sms_report_water_pump_state`  (
`id` int(11) NOT NULL AUTO_INCREMENT,
`state` smallint(1) NOT NULL COMMENT '运行状态 ：1-停止，0-运行',
`state_begin_time` datetime(0) NULL DEFAULT NULL COMMENT '运行状态开始时间',
`state_end_time` datetime(0) NULL DEFAULT NULL COMMENT '运行状态结束时间',
`tenant_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '租户标识',
`device_id` int(11) NULL DEFAULT NULL COMMENT '设备信息',
`device_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备名称',
`device_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备编号',
`attribute_id` int(11) NULL DEFAULT NULL COMMENT '设备属性',
`attribute_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备属性名称',
`year` smallint(4) NULL DEFAULT NULL COMMENT '年',
`month` smallint(2) NULL DEFAULT NULL COMMENT '月',
`day` smallint(2) NULL DEFAULT NULL COMMENT '日',
`md5_query` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'MD5查询',
`date_create` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
`date_update` datetime(0) NULL DEFAULT NULL COMMENT '更新日期',
PRIMARY KEY (`id`) USING BTREE,
INDEX `index_md5_date`(`md5_query`, `state_begin_time`) USING BTREE,
INDEX `index_tenant_id`(`tenant_id`) USING BTREE,
INDEX `index_device_time`(`device_id`, `state_begin_time`) USING BTREE,
INDEX `index_device_attr`(`device_id`, `attribute_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16499 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;