408 lines
34 KiB
SQL
408 lines
34 KiB
SQL
-- 平台对接 / 数据交换表结构
|
||
-- 平台模板层、租户接入层、数据交换层。DCP 等平台共用模板能力，租户数据按 tenant_id 隔离。
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_template`;
|
||
CREATE TABLE `iot_platform_template` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`platform_code` varchar(64) NOT NULL COMMENT '平台编码',
|
||
`platform_name` varchar(128) NOT NULL COMMENT '平台名称',
|
||
`platform_type` varchar(32) NOT NULL COMMENT '平台类型',
|
||
`adapter_code` varchar(64) DEFAULT NULL COMMENT '适配器编码',
|
||
`base_url_template` varchar(512) DEFAULT NULL COMMENT '默认基础地址模板',
|
||
`auth_type` varchar(32) DEFAULT NULL COMMENT '认证类型',
|
||
`auth_schema` json DEFAULT NULL COMMENT '授权配置字段定义',
|
||
`request_config` json DEFAULT NULL COMMENT '默认请求配置',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_platform_code` (`platform_code`),
|
||
KEY `idx_platform_type` (`platform_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-平台模板';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_api`;
|
||
CREATE TABLE `iot_platform_api` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`template_id` bigint(20) NOT NULL COMMENT '平台模板ID',
|
||
`api_code` varchar(64) NOT NULL COMMENT '接口编码',
|
||
`api_name` varchar(128) NOT NULL COMMENT '接口名称',
|
||
`api_type` varchar(32) NOT NULL COMMENT '接口类型',
|
||
`method` varchar(16) DEFAULT NULL COMMENT '请求方法',
|
||
`path_template` varchar(512) DEFAULT NULL COMMENT '路径模板',
|
||
`content_type` varchar(64) DEFAULT NULL COMMENT '内容类型',
|
||
`header_template` json DEFAULT NULL COMMENT 'Header模板',
|
||
`query_template` json DEFAULT NULL COMMENT 'Query模板',
|
||
`body_template` json DEFAULT NULL COMMENT 'Body模板',
|
||
`auth_config` json DEFAULT NULL COMMENT '接口认证配置',
|
||
`idempotency_config` json DEFAULT NULL COMMENT '幂等配置',
|
||
`success_condition` json DEFAULT NULL COMMENT '成功条件',
|
||
`response_mapping` json DEFAULT NULL COMMENT '响应映射',
|
||
`retry_policy` json DEFAULT NULL COMMENT '重试策略',
|
||
`timeout_ms` int(11) DEFAULT NULL COMMENT '超时时间毫秒',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_template_api_code` (`template_id`,`api_code`),
|
||
KEY `idx_template_api_type` (`template_id`,`api_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-接口定义';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_api_field`;
|
||
CREATE TABLE `iot_platform_api_field` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`api_id` bigint(20) NOT NULL COMMENT '接口ID',
|
||
`direction` varchar(16) NOT NULL COMMENT '方向（REQUEST RESPONSE）',
|
||
`field_path` varchar(256) NOT NULL COMMENT '字段路径',
|
||
`field_name` varchar(128) DEFAULT NULL COMMENT '字段名称',
|
||
`data_type` varchar(32) DEFAULT NULL COMMENT '数据类型',
|
||
`required` tinyint(1) DEFAULT '0' COMMENT '是否必填',
|
||
`default_value` varchar(512) DEFAULT NULL COMMENT '默认值',
|
||
`example_value` varchar(512) DEFAULT NULL COMMENT '示例值',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '说明',
|
||
`sort_order` int(11) DEFAULT '0' COMMENT '排序',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_api_direction` (`api_id`,`direction`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-接口字段';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_flow`;
|
||
CREATE TABLE `iot_platform_flow` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`template_id` bigint(20) NOT NULL COMMENT '平台模板ID',
|
||
`flow_code` varchar(64) NOT NULL COMMENT '流程编码',
|
||
`flow_name` varchar(128) NOT NULL COMMENT '流程名称',
|
||
`flow_type` varchar(32) NOT NULL COMMENT '流程类型',
|
||
`adapter_code` varchar(64) DEFAULT NULL COMMENT '适配器编码',
|
||
`sink_type` varchar(32) DEFAULT NULL COMMENT '入站数据落点',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_template_flow_code` (`template_id`,`flow_code`),
|
||
KEY `idx_template_flow_type` (`template_id`,`flow_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-流程模板';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_flow_step`;
|
||
CREATE TABLE `iot_platform_flow_step` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`flow_id` bigint(20) NOT NULL COMMENT '流程ID',
|
||
`step_code` varchar(64) NOT NULL COMMENT '步骤编码',
|
||
`step_name` varchar(128) NOT NULL COMMENT '步骤名称',
|
||
`step_type` varchar(32) NOT NULL COMMENT '步骤类型',
|
||
`api_id` bigint(20) DEFAULT NULL COMMENT '接口ID',
|
||
`adapter_method` varchar(128) DEFAULT NULL COMMENT '适配器方法',
|
||
`input_mapping` json DEFAULT NULL COMMENT '入参映射',
|
||
`output_mapping` json DEFAULT NULL COMMENT '输出映射',
|
||
`condition_expr` varchar(512) DEFAULT NULL COMMENT '执行条件',
|
||
`on_fail` varchar(32) DEFAULT 'STOP' COMMENT '失败策略',
|
||
`sort_order` int(11) DEFAULT '0' COMMENT '排序',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_flow_sort` (`flow_id`,`sort_order`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-流程步骤';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_tenant_channel`;
|
||
CREATE TABLE `iot_platform_tenant_channel` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`tenant_name` varchar(64) DEFAULT '' COMMENT '租户名称',
|
||
`template_id` bigint(20) NOT NULL COMMENT '平台模板ID',
|
||
`channel_code` varchar(64) NOT NULL COMMENT '租户平台实例编码',
|
||
`channel_name` varchar(128) NOT NULL COMMENT '租户平台实例名称',
|
||
`base_url` varchar(512) DEFAULT NULL COMMENT '实际基础地址',
|
||
`auth_config` json DEFAULT NULL COMMENT '租户授权配置',
|
||
`receive_config` json DEFAULT NULL COMMENT '接收安全配置',
|
||
`default_user_id` varchar(128) DEFAULT NULL COMMENT '默认第三方用户ID',
|
||
`default_project_id` varchar(128) DEFAULT NULL COMMENT '默认第三方项目ID',
|
||
`config_json` json DEFAULT NULL COMMENT '扩展配置',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_tenant_channel_code` (`tenant_id`,`channel_code`,`del_flag`),
|
||
KEY `idx_tenant_template` (`tenant_id`,`template_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-租户平台实例';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_resource_mapping`;
|
||
CREATE TABLE `iot_platform_resource_mapping` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`tenant_channel_id` bigint(20) NOT NULL COMMENT '租户平台实例ID',
|
||
`binding_id` bigint(20) DEFAULT NULL COMMENT '绑定ID',
|
||
`resource_scope` varchar(32) NOT NULL COMMENT '资源范围',
|
||
`resource_type` varchar(64) NOT NULL COMMENT '资源类型',
|
||
`local_key` varchar(128) NOT NULL COMMENT '本地唯一键',
|
||
`external_id` varchar(128) DEFAULT NULL COMMENT '外部资源ID',
|
||
`external_code` varchar(128) DEFAULT NULL COMMENT '外部资源编码',
|
||
`external_name` varchar(128) DEFAULT NULL COMMENT '外部资源名称',
|
||
`resource_payload` json DEFAULT NULL COMMENT '外部资源详情',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0失效 1有效）',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_resource` (`tenant_channel_id`,`binding_id`,`resource_type`,`local_key`),
|
||
KEY `idx_resource_tenant_type` (`tenant_id`,`tenant_channel_id`,`resource_type`),
|
||
KEY `idx_external_id` (`tenant_channel_id`,`resource_type`,`external_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-资源映射';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_binding`;
|
||
CREATE TABLE `iot_platform_binding` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`tenant_channel_id` bigint(20) NOT NULL COMMENT '租户平台实例ID',
|
||
`flow_id` bigint(20) NOT NULL COMMENT '平台流程ID',
|
||
`product_id` bigint(20) DEFAULT NULL COMMENT '产品ID',
|
||
`product_name` varchar(128) DEFAULT NULL COMMENT '产品名称',
|
||
`device_id` bigint(20) DEFAULT NULL COMMENT 'FastBee设备ID',
|
||
`device_name` varchar(128) DEFAULT NULL COMMENT 'FastBee设备名称',
|
||
`serial_number` varchar(64) NOT NULL COMMENT 'FastBee设备编号',
|
||
`external_user_id` varchar(128) DEFAULT NULL COMMENT '绑定级第三方用户ID',
|
||
`external_project_id` varchar(128) DEFAULT NULL COMMENT '绑定级第三方项目ID',
|
||
`external_device_type` varchar(32) DEFAULT NULL COMMENT '第三方设备类型',
|
||
`binding_name` varchar(128) DEFAULT NULL COMMENT '绑定名称',
|
||
`exchange_mode` varchar(32) NOT NULL COMMENT '交换模式',
|
||
`config_json` json DEFAULT NULL COMMENT '绑定配置',
|
||
`last_success_time` datetime DEFAULT NULL COMMENT '最近成功时间',
|
||
`last_success_value` varchar(128) DEFAULT NULL COMMENT '最近成功值',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_binding` (`tenant_channel_id`,`flow_id`,`serial_number`,`del_flag`),
|
||
KEY `idx_binding_device` (`tenant_id`,`product_id`,`serial_number`),
|
||
KEY `idx_binding_channel_flow` (`tenant_channel_id`,`flow_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-设备绑定';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_field_mapping`;
|
||
CREATE TABLE `iot_platform_field_mapping` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`binding_id` bigint(20) NOT NULL COMMENT '绑定ID',
|
||
`direction` varchar(32) NOT NULL COMMENT '方向',
|
||
`target_path` varchar(256) NOT NULL COMMENT '目标字段路径',
|
||
`target_name` varchar(128) DEFAULT NULL COMMENT '目标字段名称',
|
||
`source_type` varchar(32) NOT NULL COMMENT '来源类型',
|
||
`source_key` varchar(256) DEFAULT NULL COMMENT '来源键',
|
||
`source_name` varchar(128) DEFAULT NULL COMMENT '来源字段名称',
|
||
`transform_type` varchar(32) DEFAULT 'DIRECT' COMMENT '转换类型',
|
||
`transform_config` json DEFAULT NULL COMMENT '转换配置',
|
||
`default_value` varchar(512) DEFAULT NULL COMMENT '默认值',
|
||
`required` tinyint(1) DEFAULT '0' COMMENT '是否必填',
|
||
`sort_order` int(11) DEFAULT '0' COMMENT '排序',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_mapping_binding` (`binding_id`,`direction`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-字段映射';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_exchange_task`;
|
||
CREATE TABLE `iot_platform_exchange_task` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`tenant_channel_id` bigint(20) NOT NULL COMMENT '租户平台实例ID',
|
||
`flow_id` bigint(20) NOT NULL COMMENT '流程ID',
|
||
`task_name` varchar(128) NOT NULL COMMENT '任务名称',
|
||
`task_type` varchar(32) NOT NULL COMMENT '任务类型',
|
||
`cron_expression` varchar(128) DEFAULT NULL COMMENT 'Cron表达式',
|
||
`window_type` varchar(32) DEFAULT NULL COMMENT '窗口类型',
|
||
`window_config` json DEFAULT NULL COMMENT '窗口配置',
|
||
`cursor_json` json DEFAULT NULL COMMENT '拉取游标',
|
||
`sink_type` varchar(32) DEFAULT NULL COMMENT '入站数据落点',
|
||
`batch_size` int(11) DEFAULT '100' COMMENT '批次大小',
|
||
`max_retry` int(11) DEFAULT '3' COMMENT '最大重试次数',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
`last_run_time` datetime DEFAULT NULL COMMENT '最近执行时间',
|
||
`next_run_time` datetime DEFAULT NULL COMMENT '下次执行时间',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_task_channel_type` (`tenant_channel_id`,`task_type`),
|
||
KEY `idx_task_next_run` (`status`,`next_run_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-数据交换任务';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_task_binding`;
|
||
CREATE TABLE `iot_platform_task_binding` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`task_id` bigint(20) NOT NULL COMMENT '任务ID',
|
||
`binding_id` bigint(20) NOT NULL COMMENT '绑定ID',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '状态（0停用 1启用）',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_task_binding` (`task_id`,`binding_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-任务绑定';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_exchange_record`;
|
||
CREATE TABLE `iot_platform_exchange_record` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`tenant_channel_id` bigint(20) NOT NULL COMMENT '租户平台实例ID',
|
||
`flow_id` bigint(20) NOT NULL COMMENT '流程ID',
|
||
`binding_id` bigint(20) DEFAULT NULL COMMENT '绑定ID',
|
||
`task_id` bigint(20) DEFAULT NULL COMMENT '任务ID',
|
||
`direction` varchar(32) NOT NULL COMMENT '方向',
|
||
`source_type` varchar(32) DEFAULT NULL COMMENT '来源类型',
|
||
`sink_type` varchar(32) DEFAULT NULL COMMENT '落点类型',
|
||
`source_device_id` bigint(20) DEFAULT NULL COMMENT 'FastBee设备ID',
|
||
`serial_number` varchar(64) DEFAULT NULL COMMENT '设备编号',
|
||
`external_message_id` varchar(128) DEFAULT NULL COMMENT '第三方消息ID',
|
||
`source_ref_id` varchar(128) DEFAULT NULL COMMENT '来源ID',
|
||
`source_time` datetime DEFAULT NULL COMMENT '数据时间',
|
||
`time_window_start` datetime DEFAULT NULL COMMENT '窗口开始',
|
||
`time_window_end` datetime DEFAULT NULL COMMENT '窗口结束',
|
||
`raw_payload` json DEFAULT NULL COMMENT '原始输入',
|
||
`standard_payload` json DEFAULT NULL COMMENT '标准消息',
|
||
`request_payload` json DEFAULT NULL COMMENT '请求载荷',
|
||
`response_payload` json DEFAULT NULL COMMENT '响应载荷',
|
||
`payload_hash` varchar(64) DEFAULT NULL COMMENT 'payload哈希',
|
||
`idempotency_key` varchar(128) NOT NULL COMMENT '幂等键',
|
||
`status` varchar(32) NOT NULL DEFAULT 'PENDING' COMMENT '状态',
|
||
`retry_count` int(11) DEFAULT '0' COMMENT '重试次数',
|
||
`max_retry` int(11) DEFAULT '3' COMMENT '最大重试次数',
|
||
`next_retry_time` datetime DEFAULT NULL COMMENT '下次重试时间',
|
||
`last_error` varchar(1000) DEFAULT NULL COMMENT '最近错误',
|
||
`locked_by` varchar(64) DEFAULT NULL COMMENT 'worker锁',
|
||
`locked_time` datetime DEFAULT NULL COMMENT '锁定时间',
|
||
`success_time` datetime DEFAULT NULL COMMENT '成功时间',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_idempotency` (`idempotency_key`),
|
||
KEY `idx_record_poll` (`status`,`next_retry_time`),
|
||
KEY `idx_record_direction` (`tenant_channel_id`,`direction`,`source_time`),
|
||
KEY `idx_record_device` (`tenant_channel_id`,`serial_number`,`source_time`),
|
||
KEY `idx_record_binding` (`binding_id`,`source_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-数据交换记录';
|
||
|
||
DROP TABLE IF EXISTS `iot_platform_exchange_attempt`;
|
||
CREATE TABLE `iot_platform_exchange_attempt` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
||
`record_id` bigint(20) NOT NULL COMMENT '交换记录ID',
|
||
`attempt_no` int(11) NOT NULL COMMENT '尝试次数',
|
||
`direction` varchar(32) NOT NULL COMMENT '方向',
|
||
`request_url` varchar(1024) DEFAULT NULL COMMENT '请求地址或接收入口',
|
||
`request_headers` json DEFAULT NULL COMMENT '请求头',
|
||
`request_payload` json DEFAULT NULL COMMENT '请求体',
|
||
`response_status` int(11) DEFAULT NULL COMMENT '响应状态码',
|
||
`response_body` mediumtext COMMENT '响应内容',
|
||
`success` tinyint(1) DEFAULT '0' COMMENT '是否成功',
|
||
`error_msg` varchar(1000) DEFAULT NULL COMMENT '错误信息',
|
||
`duration_ms` int(11) DEFAULT NULL COMMENT '耗时毫秒',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_attempt_record` (`record_id`,`attempt_no`),
|
||
KEY `idx_attempt_tenant` (`tenant_id`,`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台对接-数据交换尝试日志';
|
||
|
||
INSERT INTO `iot_platform_template` (`platform_code`, `platform_name`, `platform_type`, `adapter_code`, `auth_type`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES ('DCP', 'DCP平台', 'DCP', 'dcp', 'CUSTOM_TOKEN', 1, 'system', NOW(), 'DCP 首批平台对接模板，一测点对应一个 FastBee 设备');
|
||
|
||
-- 平台对接菜单（顶级菜单）。如已存在相同 menu_id，INSERT IGNORE 会跳过。
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3500, '平台对接', 0, 8, 'platformConnect', NULL, NULL, 1, 0, 'M', '0', '0', '', 'international', 'admin', NOW(), 'admin', NOW(), '平台对接菜单');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3501, '平台模板', 3500, 1, 'template', 'iot/platformConnect/template/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:template:list', 'dict', 'admin', NOW(), 'admin', NOW(), '平台模板菜单');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3502, '平台模板查询', 3501, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:template:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3503, '平台模板新增', 3501, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:template:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3504, '平台模板修改', 3501, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:template:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3505, '平台模板删除', 3501, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:template:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3506, '平台模板导出', 3501, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:template:export', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3520, '接口配置', 3500, 2, 'api', 'iot/platformConnect/api/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:api:list', 'api', 'admin', NOW(), 'admin', NOW(), '接口配置菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3521, '接口配置查询', 3520, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:api:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3522, '接口配置新增', 3520, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:api:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3523, '接口配置修改', 3520, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:api:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3524, '接口配置删除', 3520, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:api:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3530, '流程编排', 3500, 3, 'flow', 'iot/platformConnect/flow/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:flow:list', 'tree', 'admin', NOW(), 'admin', NOW(), '流程编排菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3531, '流程查询', 3530, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:flow:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3532, '流程新增', 3530, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:flow:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3533, '流程修改', 3530, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:flow:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3534, '流程删除', 3530, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:flow:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3540, '租户接入', 3500, 4, 'tenantChannel', 'iot/platformConnect/tenantChannel/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:tenantChannel:list', 'peoples', 'admin', NOW(), 'admin', NOW(), '租户接入菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3541, '租户接入查询', 3540, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:tenantChannel:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3542, '租户接入新增', 3540, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:tenantChannel:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3543, '租户接入修改', 3540, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:tenantChannel:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3544, '租户接入删除', 3540, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:tenantChannel:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3550, '资源映射', 3500, 5, 'resource', 'iot/platformConnect/resource/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:resource:list', 'link', 'admin', NOW(), 'admin', NOW(), '资源映射菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3551, '资源映射查询', 3550, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:resource:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3552, '资源映射新增', 3550, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:resource:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3553, '资源映射修改', 3550, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:resource:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3554, '资源映射删除', 3550, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:resource:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3560, '设备绑定', 3500, 6, 'binding', 'iot/platformConnect/binding/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:binding:list', 'device', 'admin', NOW(), 'admin', NOW(), '设备绑定菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3561, '设备绑定查询', 3560, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:binding:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3562, '设备绑定新增', 3560, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:binding:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3563, '设备绑定修改', 3560, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:binding:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3564, '设备绑定删除', 3560, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:binding:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3570, '数据交换任务', 3500, 7, 'task', 'iot/platformConnect/task/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:task:list', 'job', 'admin', NOW(), 'admin', NOW(), '数据交换任务菜单');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3571, '数据交换任务查询', 3570, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:task:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3572, '数据交换任务新增', 3570, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:task:add', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3573, '数据交换任务修改', 3570, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:task:edit', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3574, '数据交换任务删除', 3570, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:task:remove', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3510, '数据交换记录', 3500, 8, 'record', 'iot/platformConnect/record/index', NULL, 1, 0, 'C', '0', '0', 'iot:platformConnect:record:list', 'log', 'admin', NOW(), 'admin', NOW(), '数据交换记录菜单');
|
||
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3511, '数据交换记录查询', 3510, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:record:query', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
INSERT IGNORE INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3512, '数据交换记录重试', 3510, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:platformConnect:record:retry', '#', 'admin', NOW(), 'admin', NOW(), '');
|
||
|
||
-- ===================== Quartz 定时任务种子数据 =====================
|
||
|
||
-- 平台数据交换 Worker（每10秒轮询待处理记录）
|
||
INSERT IGNORE INTO `sys_job` (`job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES ('platform_exchange_worker', 'PLATFORM', 'platformExchangeWorker.pollAndExecute()', '0/10 * * * * ?', '2', '1', '0', 'admin', NOW(), '平台数据交换Worker-轮询待处理记录并执行');
|