72 lines
15 KiB
SQL
72 lines
15 KiB
SQL
-- ----------------------------
|
||
-- 新增视频中心菜单
|
||
-- ----------------------------
|
||
INSERT INTO `sys_menu` VALUES (3047, '录像管理', 3044, 2, 'record', 'iot/record/record', null, 1, 0, 'C', '0', '0', '', 'chart', 'admin', '2023-05-16 22:09:34', 'admin', '2023-06-03 22:37:32', '');
|
||
|
||
-- ----------------------------
|
||
-- 更新监控产品物模型和协议类型
|
||
-- ----------------------------
|
||
UPDATE iot_product SET transport = 'GB28181' where device_type = 3;
|
||
UPDATE iot_product SET things_models_json = '{"events": [{"id": "devAlarm", "name": "设备报警", "type": 3, "order": 0, "regId": "devAlarm", "isChart": 0, "datatype": {"type": "enum", "showWay": "select", "enumList": [{"text": "视频丢失报警", "value": "1"}, {"text": "设备防拆报警", "value": "2"}, {"text": "存储设备磁盘满报警", "value": "3"}, {"text": "设备高温报警", "value": "4"}, {"text": "设备低温报警", "value": "5"}]}, "isHistory": 1, "isMonitor": 0, "isReadonly": 1}], "functions": [{"id": "audio_broadcast", "name": "语音广播", "type": 2, "order": 0, "regId": "audio_broadcast", "isChart": 0, "datatype": {"type": "string", "maxLength": 1024}, "isHistory": 1, "isMonitor": 0, "isReadonly": 0}, {"id": "video_push", "name": "设备推流", "type": 2, "order": 0, "regId": "video_push", "isChart": 0, "datatype": {"type": "string", "maxLength": 1024}, "isHistory": 1, "isMonitor": 0, "isReadonly": 0}]}' where device_type = 3;
|
||
|
||
|
||
|
||
-- ----------------------------
|
||
-- 三方登录
|
||
-- ----------------------------
|
||
ALTER TABLE iot_social_platform MODIFY COLUMN client_id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '第三方平台申请Id', MODIFY COLUMN secret_key varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '第三方平台密钥';
|
||
ALTER TABLE iot_social_user ADD PRIMARY KEY (social_user_id);
|
||
ALTER TABLE iot_social_user ADD COLUMN source_client varchar(64) NULL COMMENT '第三方用户来源客户端（web、app、小程序）';
|
||
ALTER TABLE iot_social_user MODIFY COLUMN access_token varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户的授权令牌', MODIFY COLUMN refresh_token varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '刷新令牌(部分平台可能没有)', MODIFY COLUMN open_id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '第三方用户的 open id（部分平台可能没有）', MODIFY COLUMN union_id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '第三方用户的 union id(部分平台可能没有)';
|
||
ALTER TABLE iot_social_user MODIFY COLUMN uuid varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '第三方系统的唯一ID';
|
||
ALTER TABLE iot_social_user MODIFY COLUMN access_token varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户的授权令牌';
|
||
ALTER TABLE iot_social_user DROP INDEX iot_social_user_unique_key;
|
||
|
||
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (4, '微信开放平台网站应用', 'wechat_open_web', 'iot_social_platform', NULL, 'default', 'N', '0', 'admin', '2023-08-23 11:28:15', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (5, '微信开放平台移动应用', 'wechat_open_mobile', 'iot_social_platform', NULL, 'default', 'N', '0', 'admin', '2023-08-23 11:29:14', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (6, '微信开放平台小程序', 'wechat_open_mini_program', 'iot_social_platform', NULL, 'default', 'N', '0', 'admin', '2023-08-23 11:38:12', '', NULL, NULL);
|
||
|
||
|
||
|
||
-- ----------------------------
|
||
-- 分享设备权限
|
||
-- ----------------------------
|
||
ALTER TABLE iot_things_model ADD COLUMN is_share_perm tinyint(1) NULL COMMENT '是否分享设备权限(0-否，1-是)';
|
||
ALTER TABLE iot_things_model_template ADD COLUMN is_share_perm tinyint(1) NULL COMMENT '是否分享设备权限(0-否，1-是)';
|
||
ALTER TABLE iot_device_user ADD COLUMN perms varchar(255) NULL COMMENT '用户物模型权限，多个以英文逗号分隔';
|
||
|
||
|
||
-- modbus数据类型转换 字典
|
||
INSERT INTO `sys_dict_type`(`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('modbus数据类型', 'iot_modbus_data_type', '0', 'admin', '2023-09-04 13:54:17', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '16位 无符号', 'ushort', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:11:54', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, '16位 有符号', 'short', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:12:26', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '32位 有符号(ABCD)', 'long-ABCD', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:12:53', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (3, '32位 有符号(CDAB)', 'long-CDAB', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:13:21', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (4, '32位 无符号(ABCD)', 'ulong-ABCD', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:13:42', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (5, '32位 无符号(CDAB)', 'ulong-CDAB', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:14:06', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (6, '32位 浮点数(ABCD)', 'float-ABCD', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:14:28', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (7, '32位 浮点数(CDAB)', 'float-CDAB', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:14:50', '', NULL, NULL);
|
||
INSERT INTO `sys_dict_data`(`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (8, '位', 'bit', 'iot_modbus_data_type', NULL, 'default', 'N', '0', 'admin', '2023-09-04 14:15:13', '', NULL, NULL);
|
||
|
||
-- modbus数据类型转换，添加字段
|
||
ALTER TABLE `iot_things_model_template`
|
||
ADD COLUMN `parse_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'modbus解析类型' AFTER `old_temp_slave_id`;
|
||
ALTER TABLE `iot_things_model`
|
||
ADD COLUMN `parse_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'modbus解析类型' AFTER `code`;
|
||
|
||
-- modbus数据类型转换测试数据
|
||
INSERT INTO `iot_var_temp`(`template_id`, `template_name`, `type`, `polling_method`, `user_id`, `slave_total`, `point_total`, `share`, `create_time`, `create_by`, `update_time`, `update_by`) VALUES (14, '测试', NULL, 0, NULL, NULL, NULL, NULL, '2023-09-04 08:56:30', NULL, NULL, NULL);
|
||
INSERT INTO `iot_var_temp_salve`(`id`, `device_temp_id`, `slave_addr`, `slave_index`, `slave_ip`, `slave_name`, `slave_port`, `addr_start`, `addr_end`, `packet_length`, `timer`, `status`, `code`, `create_time`, `create_by`, `update_time`, `update_by`, `remark`) VALUES (18, 14, 1, NULL, NULL, '测试设备1', NULL, 0, 9, 10, 60, 0, NULL, '2023-09-04 08:56:48', NULL, NULL, NULL, NULL);
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (348, '开关-位', 1, 'admin', '5', 1, 'enum', '{\"type\": \"enum\", \"showWay\": \"select\", \"enumList\": [{\"text\": \"关\", \"value\": \"0x0000\"}, {\"text\": \"开\", \"value\": \"0xFF00\"}]}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:13:02', '', '2023-09-05 16:16:06', NULL, '14#1', NULL, NULL, 5, NULL, NULL, NULL, 1, '1', NULL, NULL, 'bit');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (349, '参数-16位有符号', 1, 'admin', '6', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:13:40', '', '2023-09-05 16:17:45', NULL, '14#1', NULL, NULL, 6, NULL, NULL, NULL, 1, '3', NULL, NULL, 'short');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (350, '参数-16位有符号', 1, 'admin', '7', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:13:55', '', '2023-09-05 16:17:35', NULL, '14#1', NULL, NULL, 7, NULL, NULL, NULL, 1, '3', NULL, NULL, 'short');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (351, '参数-16位有符号', 1, 'admin', '8', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:14:12', '', '2023-09-05 16:18:04', NULL, '14#1', NULL, NULL, 8, NULL, NULL, NULL, 1, '3', NULL, NULL, 'short');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (352, '参数-32有符号-ABCD', 1, 'admin', '9', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:14:47', '', '2023-09-05 16:18:24', NULL, '14#1', NULL, NULL, 9, NULL, NULL, NULL, 2, '3', NULL, NULL, 'long-ABCD');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (353, '参数-32位有符号-CDAB', 1, 'admin', '11', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:18:58', '', NULL, NULL, '14#1', NULL, NULL, 11, NULL, NULL, NULL, 2, '3', NULL, NULL, 'long-CDAB');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (354, '参数-32位无符号-ABCD', 1, 'admin', '13', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:20:20', '', NULL, NULL, '14#1', NULL, NULL, 13, NULL, NULL, NULL, 2, '3', NULL, NULL, 'ulong-ABCD');
|
||
INSERT INTO `iot_things_model_template`(`template_id`, `template_name`, `tenant_id`, `tenant_name`, `identifier`, `type`, `datatype`, `specs`, `is_sys`, `is_chart`, `is_monitor`, `is_history`, `is_readonly`, `is_share_perm`, `model_order`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `temp_slave_id`, `formula`, `reverse_formula`, `reg_addr`, `bit_option`, `value_type`, `is_params`, `quantity`, `code`, `old_identifier`, `old_temp_slave_id`, `parse_type`) VALUES (355, '参数-32位无符号-CDAB', 1, 'admin', '15', 1, 'integer', '{\"max\": 100, \"min\": 0, \"step\": 1, \"type\": \"integer\", \"unit\": \"\"}', 1, 0, 0, 0, 1, 0, 0, '0', '', '2023-09-05 16:20:55', '', NULL, NULL, '14#1', NULL, NULL, 15, NULL, NULL, NULL, 2, '3', NULL, NULL, 'ulong-CDAB');
|
||
|
||
ALTER TABLE `iot_alert_log`
|
||
DROP COLUMN `user_id`,
|
||
ADD COLUMN `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id' AFTER `device_name`;
|