127 lines
8.3 KiB
SQL
127 lines
8.3 KiB
SQL
-- 全新部署时，执行这个脚本：
|
||
DROP DATABASE IF EXISTS `sense_adapter`;
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS sense_adapter;
|
||
USE `sense_adapter`;
|
||
|
||
-- DROP TABLE IF EXISTS `regulator`;
|
||
-- DROP TABLE IF EXISTS `sf_device`;
|
||
-- DROP TABLE IF EXISTS `sf_sense_capability`;
|
||
-- DROP TABLE IF EXISTS `sf_sense_task`;
|
||
-- DROP TABLE IF EXISTS `sense_data_sink_config`;
|
||
|
||
CREATE TABLE `regulator` (
|
||
`id` bigint NOT NULL COMMENT '厂商id',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '厂商昵称',
|
||
`is_system_admin` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是系统管理员',
|
||
`user_type` varchar(255) COMMENT '用户类型, 0:管理员; 1:中信科CICIM, 2:Erission, 3:Huawei，4:ZTE',
|
||
`telephone` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '厂商手机号',
|
||
`avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '头像地址',
|
||
`authentication_status` int NULL COMMENT '认证状态，0： 未认证，1已认证',
|
||
`authentication_time` datetime(6) DEFAULT NULL COMMENT '认证时间',
|
||
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '登录密码',
|
||
`password_modify_time` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '更新密码时间',
|
||
`is_frozen` bit(1) NOT NULL DEFAULT b'0' COMMENT '用户是否被禁用，0：否，1：禁用',
|
||
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '用户是否被删除，0：否，1：删除',
|
||
`create_at` datetime(6) DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime(6) DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新用户',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='监管者表';
|
||
|
||
-- 创建默认的管理员和4个厂商的账户；
|
||
-- 管理员：15928589640 Cmii112233
|
||
-- CICTM 15928589601 c5Wxu)RrM
|
||
-- Ericsson 15928589602 eQKcu&8!
|
||
-- Huawei 15928589603 h0}oF!Mji
|
||
-- ZTE 15928589604 z6(Z*!StF
|
||
-- truncate table regulator;
|
||
INSERT INTO `regulator` (`id`, `name`, `is_system_admin`, `telephone`, `avatar_url`, `authentication_status`, `authentication_time`, `password`, `password_modify_time`, `is_frozen`, `is_del`, `create_at`, `create_by`, `update_at`, `update_by`, `user_type`) VALUES (1,'Admin',_binary '\0','6d46c798b1385c0294badf9903586923',NULL,0,NULL,'$2a$10$FYElzh8BsxQFr0Da.MMDuOpH.Ynm57QGBRsgRIzQowgZIdvXbAb3G','2024-09-23 18:40:49.045124',_binary '\0',_binary '\0','2024-09-23 18:40:47.183882','r_1',NULL,NULL,'0'),(552450964452278272,'CICTM',_binary '\0','ab674bbb9a1298f873464464d0108af1',NULL,0,NULL,'$2a$10$twsDhJrQ8yzt2pP9MvUmTe4YpCD7lJk/Thk1zdrGq9UIQMLWcldnq','2024-09-21 11:22:53.966212',_binary '\0',_binary '\0','2024-09-21 11:22:53.966212','r_2',NULL,NULL,'1'),(552451165640458240,'Ericsson',_binary '\0','74bd84f82b1a4067c0b1f941789f546a',NULL,0,NULL,'$2a$10$5mK/u4upnJLcxbGlLBoTs.Uc9myqREGK1ztRykceL1QtJoXxkj4Di','2024-09-21 11:23:41.935420',_binary '\0',_binary '\0','2024-09-21 11:23:41.935420','r_2',NULL,NULL,'2'),(552451398919258112,'Huawei',_binary '\0','df1eb215362d7dcda6d0341d360c2610',NULL,0,NULL,'$2a$10$lD9ruy3WlsHmGAda1.SL1ey17JrJ0/KFdD8WaSluL5zbswrS18dLC','2024-09-21 11:24:37.553081',_binary '\0',_binary '\0','2024-09-21 11:24:37.553081','r_2',NULL,NULL,'3'),(552451636522385408,'ZTE',_binary '\0','a91fd8159a002ee51ca59f02dcc92b1f',NULL,0,NULL,'$2a$10$xO/EDFvB4TlI2gjfjYP82O.HgAoDeZw3SWoeLiTGHRPXtGDK5rTv2','2024-09-21 11:25:34.202789',_binary '\0',_binary '\0','2024-09-21 11:25:34.202789','r_2',NULL,NULL,'4');
|
||
|
||
CREATE TABLE `sf_device` (
|
||
`sf_dev_id` INT NOT NULL COMMENT 'SF ID',
|
||
`sf_name` VARCHAR(255) COMMENT 'SF名称',
|
||
`sf_vender_id` TINYINT COMMENT '厂商编号, 1: CICTM; 2: Ericsson; 3: Huawei; 4: ZTE',
|
||
|
||
-- 核心网增加的字段：
|
||
`sf_type` TINYINT COMMENT 'SF类型, 0: 无线SF, 1: SCF, 2: SG, 3, NEF',
|
||
`scf_url` VARCHAR(4000) COMMENT 'SCF URL',
|
||
`subscription_id` VARCHAR(255) COMMENT 'SCF分配, 感知能力订阅ID',
|
||
`civic_address_list` MEDIUMTEXT COMMENT '感知业务开展范围JSON',
|
||
`af_notify_url_head` VARCHAR(4000) COMMENT 'AF控制面接收SF通知用的URL的头(因为AF可能有多个网络，需要告知SCF/SG，用哪个URL给自己发消息)',
|
||
`scf_auth_code` VARCHAR(4000) COMMENT '核心网鉴权用的base64字符串, 转换方式: appId:appPassword 转换成base64编码',
|
||
|
||
`company_id` VARCHAR(255) COMMENT '所属公司/机构',
|
||
`province_code` VARCHAR(255) COMMENT '省级行政区',
|
||
`city_code` VARCHAR(255) COMMENT '地级行政区',
|
||
`district_code` VARCHAR(255) COMMENT '区',
|
||
`remarks` TEXT COMMENT '备注',
|
||
`tcp_keep_alive_interval` INT COMMENT '控制面心跳时间间隔(秒)',
|
||
|
||
`is_delete` BOOLEAN COMMENT '是否已经删除',
|
||
|
||
`create_usr_id` VARCHAR(255) COMMENT '创建此SF的用户ID',
|
||
`create_date` DATETIME COMMENT '创建此SF的日期',
|
||
|
||
`upd_usr_id` VARCHAR(255) COMMENT '更新此表的用户ID',
|
||
`upd_date` DATETIME COMMENT '更新此表的日期',
|
||
PRIMARY KEY (`sf_dev_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SF设备信息表';
|
||
|
||
CREATE TABLE `sf_sense_task` (
|
||
`sf_dev_id` INT NOT NULL COMMENT 'SF ID',
|
||
`task_id` INT NOT NULL COMMENT '任务ID',
|
||
`task_name` VARCHAR(255) COMMENT '任务名字',
|
||
`json_string` MEDIUMTEXT COMMENT 'StartSenseTaskReqBean JSON字符串',
|
||
`company_id` VARCHAR(255) COMMENT '所属公司/机构',
|
||
`remarks` TEXT COMMENT '备注',
|
||
`is_enable` BOOLEAN COMMENT '是否已经停止',
|
||
|
||
`sf_resp_result_status` TINYINT COMMENT 'SF返回的执行状态, 0:success, 1:fail',
|
||
`sf_resp_failure_reason` VARCHAR(255) COMMENT 'SF返回的失败原因',
|
||
|
||
`is_delete` BOOLEAN COMMENT '是否已经删除',
|
||
|
||
|
||
-- 核心网增加的字段：
|
||
`data_plane_domain_name` VARCHAR(255) COMMENT '数据面上报IP的domain name，整网规划决定，用于SPF选择用于对接AF的本端接口，由1-20个字母、数字、下划线、横杠组成',
|
||
`data_plane_ipv4_addr` VARCHAR(255) COMMENT '数据面上报IP，IPv4和IPv6只能且必须携带其中1个',
|
||
`data_plane_ipv6_addr` VARCHAR(255) COMMENT '数据面上报IP，IPv4和IPv6只能且必须携带其中1个',
|
||
`data_plane_port` INT COMMENT 'AF接收用户面数据的端口号',
|
||
`civic_address_list` MEDIUMTEXT COMMENT '感知业务开展范围JSON',
|
||
|
||
`create_usr_id` VARCHAR(255) COMMENT '创建此SF的用户ID',
|
||
`create_date` DATETIME COMMENT '创建此SF的日期',
|
||
|
||
`upd_usr_id` VARCHAR(255) COMMENT '更新此表的用户ID',
|
||
`upd_date` DATETIME COMMENT '更新此表的日期',
|
||
PRIMARY KEY (`sf_dev_id`, `task_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SF感知任务信息表';
|
||
|
||
-- 下面数据库表，需要从 uav_industrial_portfolio 库中迁移过来.
|
||
CREATE TABLE `sense_data_sink_config` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '分发者编码',
|
||
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '分发者名称',
|
||
`type` int DEFAULT NULL COMMENT '分发者类型',
|
||
`public_key` varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '公钥',
|
||
`config` varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置',
|
||
`is_delete` tinyint(1) DEFAULT NULL COMMENT '是否删除',
|
||
`upd_usr_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新用户id',
|
||
`upd_date` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
KEY `idx_sense_code` (`code`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='SF adapter的分发配置表';
|
||
|
||
CREATE TABLE `core5g_coop_uav_sub` (
|
||
`subscription_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`sub_dto_json` MEDIUMTEXT NOT NULL COMMENT '核心网订阅信息Core5gPosInfoSubscribeReqDTO的JSON字符串',
|
||
`notif_uri` varchar(4096) NOT NULL COMMENT '核心网提供的用于接收无人机位置信息的URI',
|
||
|
||
PRIMARY KEY (`subscription_id`)
|
||
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='核心网合作无人机轨迹订阅信息表';
|
||
|