198 lines
12 KiB
SQL
198 lines
12 KiB
SQL
-- MySQL dump 10.13 Distrib 8.0.36, for Linux (x86_64)
|
||
--
|
||
-- Host: 144.7.97.70 Database: cmii
|
||
-- ------------------------------------------------------
|
||
-- Server version 8.1.0
|
||
|
||
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
||
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
||
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
||
/*!50503 SET NAMES utf8mb4 */;
|
||
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
||
/*!40103 SET TIME_ZONE='+00:00' */;
|
||
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
||
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
||
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
||
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
||
|
||
|
||
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cmii_fly_center` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;
|
||
|
||
USE `cmii_fly_center`;
|
||
|
||
--
|
||
-- Table structure for table `command_log`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `command_log`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `command_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`drone_sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '无人机 sn',
|
||
`gateway_sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '网关序列号',
|
||
`gateway_type` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '网关类型',
|
||
`cmd_id` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '指令id',
|
||
`cmd_type` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '指令类型',
|
||
`cmd_params` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '指令参数',
|
||
`response_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '指令响应数据',
|
||
`error_msg` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '指令错误信息',
|
||
`status` int NOT NULL COMMENT '指令状态',
|
||
`start_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '指令开始时间',
|
||
`end_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '指令结束时间',
|
||
`cost_time` bigint DEFAULT NULL COMMENT '指令耗时,单位 ms',
|
||
`trace_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '追踪id',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
KEY `command_log_cmd_type_index` (`cmd_type`) USING BTREE,
|
||
KEY `command_log_drone_sn_index` (`drone_sn`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='无人机指令日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `drone_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `drone_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `drone_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备 SN',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '无人机名称',
|
||
`manufacturer` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '厂家',
|
||
`model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '型号，如果有子型号，拼接保存',
|
||
`version` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '固件版本',
|
||
`offline_position` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '无人机下线的时候最后的点位信息,json结构数据',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `drone_info_pk_2` (`sn`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='设备信息';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `file_records`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `file_records`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `file_records` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`file_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件ID',
|
||
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件名称',
|
||
`content_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件类型',
|
||
`signature` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件签名',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`file_size` bigint DEFAULT NULL COMMENT '文件大小',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `uniq_file_records_file_id` (`file_id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='文件记录';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `gateway_bound_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `gateway_bound_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `gateway_bound_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`gateway_sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '网关 sn',
|
||
`drone_sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '无人机 sn',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `uniq_gateway_bound_info_pk` (`drone_sn`,`gateway_sn`) USING BTREE,
|
||
KEY `gateway_bound_info_gateway_sn_index` (`gateway_sn`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=778 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='无人机设备绑定关系';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `gateway_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `gateway_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `gateway_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`sn` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '网关 sn',
|
||
`type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '类型',
|
||
`form` int DEFAULT NULL COMMENT '形态',
|
||
`model` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '型号',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '网关名称',
|
||
`version` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '版本',
|
||
`stream_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '推流地址[list]',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `gateway_info_pk_2` (`sn`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='网关设备信息';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `storage_files`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `storage_files`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `storage_files` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '文件ID',
|
||
`file_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件唯一标识',
|
||
`bucket` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'OSS存储桶',
|
||
`object_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'OSS对象路径',
|
||
`drone_sn` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '无人机序列号',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件名称',
|
||
`type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件类型',
|
||
`size` bigint unsigned DEFAULT '0' COMMENT '文件大小(字节)',
|
||
`md5` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'MD5哈希值',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`extra` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '额外信息',
|
||
`pilot_photo_fingerprint` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `uk_file_id` (`file_id`) USING BTREE,
|
||
KEY `idx_drone_sn` (`drone_sn`) USING BTREE,
|
||
KEY `idx_object_path` (`object_path`(191)) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2210 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='文件存储表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `track_order`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `track_order`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `track_order` (
|
||
`track_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '轨迹id',
|
||
`drone_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '无人机id',
|
||
`gateway_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '网关序列号',
|
||
`first_track_time` datetime NOT NULL COMMENT '轨迹开始时间',
|
||
`last_track_time` datetime NOT NULL COMMENT '轨迹结束时间',
|
||
`distance` bigint NOT NULL COMMENT '轨迹距离,单位 m',
|
||
`duration` bigint NOT NULL COMMENT '轨迹中时长,单位 s',
|
||
`first_latitude` double NOT NULL COMMENT '轨迹开始纬度',
|
||
`first_longitude` double NOT NULL COMMENT '轨迹开始经度',
|
||
`last_latitude` double NOT NULL COMMENT '轨迹结束纬度',
|
||
`last_longitude` double NOT NULL COMMENT '轨迹结束经度',
|
||
`gateway_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '网关类型',
|
||
PRIMARY KEY (`track_id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='无人机轨迹记录表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `wayline_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `wayline_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `wayline_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '航线内容文件名称',
|
||
`file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件在 OSS 的路径',
|
||
`file_md5` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件md5',
|
||
`content` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'JSON 格式的航线内容',
|
||
`drone_model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '无人机型号',
|
||
`payloads` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '载荷型号列表',
|
||
`deleted` int NOT NULL DEFAULT '0' COMMENT '是否删除，0 为未删除，1 为已删除',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1754 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='航线信息'; |