171 lines
11 KiB
SQL
171 lines
11 KiB
SQL
-- 第三方平台接入框架
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_integration_platform` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`platform_code` varchar(64) NOT NULL COMMENT '平台编码',
|
||
`platform_name` varchar(128) NOT NULL COMMENT '平台名称',
|
||
`base_url` varchar(512) NOT NULL COMMENT '基础地址',
|
||
`auth_type` varchar(64) DEFAULT NULL COMMENT '认证类型',
|
||
`credential_json` json DEFAULT NULL COMMENT '凭证配置',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_integration_platform_code` (`platform_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接入配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_integration_api` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`platform_id` bigint NOT NULL COMMENT '平台ID',
|
||
`api_code` varchar(64) NOT NULL COMMENT '接口编码',
|
||
`api_name` varchar(128) NOT NULL COMMENT '接口名称',
|
||
`method` varchar(16) NOT NULL COMMENT '请求方法',
|
||
`uri` varchar(512) NOT NULL COMMENT '接口URI',
|
||
`content_type` varchar(128) DEFAULT 'application/json' COMMENT 'Content-Type',
|
||
`headers_json` json DEFAULT NULL COMMENT '请求头模板',
|
||
`query_json` json DEFAULT NULL COMMENT 'Query模板',
|
||
`body_json` json DEFAULT NULL COMMENT 'Body模板',
|
||
`response_path` varchar(256) DEFAULT NULL COMMENT '返回路径',
|
||
`depends_on` varchar(64) DEFAULT NULL COMMENT '依赖接口编码',
|
||
`sort_order` int DEFAULT 0 COMMENT '排序',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_integration_api_code` (`platform_id`, `api_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接口定义';
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_integration_device_mapping` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`platform_id` bigint NOT NULL COMMENT '平台ID',
|
||
`external_device_id` varchar(128) NOT NULL COMMENT '第三方设备ID',
|
||
`external_device_name` varchar(128) DEFAULT NULL COMMENT '第三方设备名称',
|
||
`product_id` bigint NOT NULL COMMENT '本系统产品ID',
|
||
`serial_number` varchar(64) NOT NULL COMMENT '本系统设备编号',
|
||
`identifier_mapping_json` json DEFAULT NULL COMMENT '指标映射 sourceField -> identifier',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_integration_device_mapping` (`platform_id`, `external_device_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方设备映射';
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_integration_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`platform_id` bigint DEFAULT NULL COMMENT '平台ID',
|
||
`platform_code` varchar(64) NOT NULL COMMENT '平台编码',
|
||
`flow_code` varchar(64) NOT NULL COMMENT '流程编码',
|
||
`request_id` varchar(64) NOT NULL COMMENT '请求ID',
|
||
`start_time` datetime DEFAULT NULL COMMENT '开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
||
`status` tinyint DEFAULT 2 COMMENT '状态：0=失败 1=成功 2=执行中',
|
||
`success_count` int DEFAULT 0 COMMENT '成功数量',
|
||
`fail_count` int DEFAULT 0 COMMENT '失败数量',
|
||
`error_msg` varchar(1000) DEFAULT NULL COMMENT '错误信息',
|
||
`detail_json` json DEFAULT NULL COMMENT '详情',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_integration_log_request` (`request_id`),
|
||
KEY `idx_integration_log_platform` (`platform_code`, `start_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接入执行日志';
|
||
|
||
INSERT INTO `iot_integration_platform`
|
||
(`platform_code`, `platform_name`, `base_url`, `auth_type`, `credential_json`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
VALUES
|
||
('DCP', 'DCP轴力计平台', 'http://47.108.182.132:8200', 'PASSWORD_TOKEN',
|
||
JSON_OBJECT('grant_type','password','scope','DACP','client_id','DACP_App','userName','ceshi','password','12345678',
|
||
'openClientId','DCP_Open_App','openUserName','open_user','openPassword','change-me'),
|
||
0, '0', 'admin', NOW())
|
||
ON DUPLICATE KEY UPDATE `platform_name` = VALUES(`platform_name`);
|
||
|
||
-- 启用前请按现场账号修改 credential_json 中的 DCP 上游账号和 openClientId/openUserName/openPassword，并将 status 改为 1。
|
||
-- 若需要第三方应用匿名触发同步，请在 credential_json 中增加 openApiKey：
|
||
-- UPDATE iot_integration_platform SET credential_json = JSON_SET(credential_json, '$.openApiKey', 'replace-with-random-secret') WHERE platform_code = 'DCP';
|
||
--
|
||
-- 设备映射示例，需替换 product_id、serial_number、external_device_id：
|
||
-- INSERT INTO iot_integration_device_mapping
|
||
-- (platform_id, external_device_id, external_device_name, product_id, serial_number, identifier_mapping_json, status, del_flag, create_by, create_time)
|
||
-- SELECT id, 'DCP_DEVICE_ID', 'DCP测点名称', 1, 'FASTBEE_SERIAL_NUMBER',
|
||
-- JSON_OBJECT('e','axialForce','temperature','temperature','ct','ct','pt','pt'),
|
||
-- 1, '0', 'admin', NOW()
|
||
-- FROM iot_integration_platform WHERE platform_code = 'DCP';
|
||
|
||
INSERT INTO `iot_integration_api`
|
||
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
SELECT p.id, 'dcp_token', '获取访问凭证', 'POST', '/connect/token', 'application/x-www-form-urlencoded', NULL, 1, 1, '0', 'admin', NOW()
|
||
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
|
||
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);
|
||
|
||
INSERT INTO `iot_integration_api`
|
||
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
SELECT p.id, 'dcp_user_id', '获取用户信息', 'GET', '/api/app/user/user-id', 'application/json', 'dcp_token', 2, 1, '0', 'admin', NOW()
|
||
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
|
||
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);
|
||
|
||
INSERT INTO `iot_integration_api`
|
||
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
SELECT p.id, 'dcp_project_list', '获取项目信息', 'GET', '/api/app/project/by-user-id/{userId}', 'application/json', 'dcp_user_id', 3, 1, '0', 'admin', NOW()
|
||
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
|
||
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);
|
||
|
||
INSERT INTO `iot_integration_api`
|
||
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
SELECT p.id, 'dcp_stress_sensor_list', '获取项目轴力计测点', 'POST', '/api/app/stress-sensor/by-project-id/{projectId}', 'application/json', 'dcp_project_list', 4, 1, '0', 'admin', NOW()
|
||
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
|
||
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);
|
||
|
||
INSERT INTO `iot_integration_api`
|
||
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
|
||
SELECT p.id, 'dcp_stress_sensor_data', '批量获取轴力计数据', 'POST', '/api/app/inclino-data/load-dataset-by-device-ids', 'application/json', 'dcp_stress_sensor_list', 5, 1, '0', 'admin', NOW()
|
||
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
|
||
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);
|
||
|
||
-- 菜单权限。若目标库已有相同 menu_id，请按现场菜单ID调整后执行。
|
||
INSERT 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
|
||
(3440, '平台接入', 3051, 9, 'integration', 'iot/integration/index', NULL, 1, 0, 'C', '0', '0', 'iot:integration:list', 'api', 'admin', NOW(), '', NULL, '第三方平台接入菜单')
|
||
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `component` = VALUES(`component`), `visible` = VALUES(`visible`), `perms` = VALUES(`perms`);
|
||
|
||
INSERT 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
|
||
(3441, '平台接入查询', 3440, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:list', '#', 'admin', NOW(), '', NULL, '')
|
||
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);
|
||
|
||
INSERT 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
|
||
(3442, '平台数据同步', 3440, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:sync', '#', 'admin', NOW(), '', NULL, '')
|
||
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);
|
||
|
||
INSERT 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
|
||
(3443, '平台接入新增', 3440, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:add', '#', 'admin', NOW(), '', NULL, '')
|
||
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);
|
||
|
||
INSERT 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
|
||
(3444, '平台接入修改', 3440, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:edit', '#', 'admin', NOW(), '', NULL, '')
|
||
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);
|
||
|
||
INSERT 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
|
||
(3445, '平台接入删除', 3440, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:remove', '#', 'admin', NOW(), '', NULL, '')
|
||
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);
|