930 lines
76 KiB
SQL
930 lines
76 KiB
SQL
create database if not exists uav_lifecycle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
||
use uav_lifecycle;
|
||
CREATE TABLE `uav_lifecycle`.`biz_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`log_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '日志产生时间',
|
||
`app_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '系统名，微服务系统名。如 用户中心',
|
||
`user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户id',
|
||
`company_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '厂商id',
|
||
`category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '模块分类，如 \"登录\"',
|
||
`sub_category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '模块小分类，如 \"验证码登录\"',
|
||
`operate_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '动作事件。 如登录',
|
||
`lang` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'zh_CN' COMMENT '语言,zh_CN:简体中文; en_US:英文;zh_TW:繁体中文',
|
||
`operate_result` smallint NULL DEFAULT NULL COMMENT '操作结果说明，0:操作成功，1:操作失败 2.未知',
|
||
`operate_identity` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '动作事件关联信息。 如 用户id',
|
||
`operate_value` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '动作事件相关数值。如 email',
|
||
`operate_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作相关其他参数。JSON对象存储，存储方式. {\"变量名称\":\"变量的值\"}',
|
||
`operate_time` int NULL DEFAULT NULL COMMENT '操作持续时间',
|
||
`create_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '日志入库时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_category`(`category`) USING BTREE,
|
||
INDEX `idx_log_time`(`log_time`) USING BTREE,
|
||
INDEX `idx_operate_identiy`(`operate_identity`) USING BTREE,
|
||
INDEX `idx_user_id`(`user_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '日志表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`cert_device` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`manufacturer_id` bigint NOT NULL COMMENT '厂商id，如果为0，表示中移凌云的服务器证书',
|
||
`mnf_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产厂商认证的名称',
|
||
`uav_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机唯一识别码',
|
||
`product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机产品名称',
|
||
`product_model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机产品型号',
|
||
`uav_verify_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机校验码',
|
||
`serial_number` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书序列号',
|
||
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证书名称',
|
||
`cert` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书内容，base编码',
|
||
`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书主题',
|
||
`valid_start_date` datetime(6) NOT NULL COMMENT '证书有效开始时间',
|
||
`valid_end_date` datetime(6) NOT NULL COMMENT '证书有效结束时间',
|
||
`private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书对应的私钥',
|
||
`csr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '证书请求的csr',
|
||
`status` int NOT NULL COMMENT '证书状态0：在用，1：过期，2：吊销',
|
||
`history_verify_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机历史的校验码，默认存最近的5条',
|
||
`revoke_time` datetime(6) NULL DEFAULT NULL COMMENT '吊销时间',
|
||
`is_del` bit(1) NOT NULL COMMENT '证书是否删除',
|
||
`create_at` datetime(6) NOT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_cert_uavId_code`(`uav_verify_code`, `uav_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备证书表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`cert_manufacturer_ca` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`manufacturer_id` bigint NULL DEFAULT NULL COMMENT '厂商id，如果为0，表示中移凌云的CA',
|
||
`serial_number` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书序列号',
|
||
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证书名称',
|
||
`mnf_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产厂商认证的名称',
|
||
`cert` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书内容，base编码',
|
||
`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书主题',
|
||
`valid_start_date` datetime(6) NOT NULL COMMENT '证书有效开始时间',
|
||
`valid_end_date` datetime(6) NOT NULL COMMENT '证书有效结束时间',
|
||
`private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书对应的私钥',
|
||
`csr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '证书请求的csr',
|
||
`status` int NOT NULL COMMENT '证书状态0：在用，1：过期，2：吊销，3、非活动',
|
||
`revoke_time` datetime(6) NULL DEFAULT NULL COMMENT '吊销时间',
|
||
`is_del` bit(1) NOT NULL COMMENT '证书是否删除',
|
||
`create_at` datetime(6) NOT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '厂商证书表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`cert_root_ca` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`serial_number` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书序列号',
|
||
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证书名称',
|
||
`cert` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书内容，base编码',
|
||
`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书主题',
|
||
`valid_start_date` datetime(6) NOT NULL COMMENT '证书有效开始时间',
|
||
`valid_end_date` datetime(6) NOT NULL COMMENT '证书有效结束时间',
|
||
`private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证书对应的私钥',
|
||
`csr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '证书请求的csr',
|
||
`status` int NOT NULL COMMENT '证书状态0：在用，1：过期，2：吊销',
|
||
`is_del` bit(1) NOT NULL COMMENT '证书是否删除',
|
||
`create_at` datetime(6) NOT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '根证书表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`gateway_request_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户id',
|
||
`ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '请求ip地址',
|
||
`method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '请求方法',
|
||
`service_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '微服务名称',
|
||
`path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '请求路径',
|
||
`user_agent` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户客户端agent',
|
||
`request_time` datetime(6) NULL DEFAULT NULL COMMENT '请求时间',
|
||
`response_time` datetime(6) NULL DEFAULT NULL COMMENT '响应时间',
|
||
`cost_time` int NULL DEFAULT NULL COMMENT '执行消耗时间',
|
||
`http_status` int NULL DEFAULT NULL COMMENT '请求状态',
|
||
`is_success` bit(1) NULL DEFAULT b'1' COMMENT '业务是否请求成功',
|
||
`platform_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '平台类型',
|
||
`create_at` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '入库时间',
|
||
`code` int NULL DEFAULT NULL COMMENT '业务响应码',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_request_time`(`request_time`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '请求日志表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`manuf_detail` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`manf_id` bigint NULL DEFAULT NULL COMMENT '生产厂商id',
|
||
`type_id` int NULL DEFAULT NULL COMMENT '生产厂商类型id',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
|
||
`number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件号码',
|
||
`pic_front` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件照(正面)',
|
||
`pic_back` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件照(反面)',
|
||
`company_num` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '统一社会信用代码',
|
||
`area` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地区或省',
|
||
`manuf_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产厂家名称',
|
||
`create_at` datetime NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '是否删除',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '生产厂商详情表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`manufacturer` (
|
||
`id` bigint NOT NULL COMMENT '厂商id',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '厂商昵称',
|
||
`telephone` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '厂商手机号',
|
||
`avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像地址',
|
||
`authentication_status` int NOT NULL COMMENT '认证状态，0： 未认证，1已认证',
|
||
`authentication_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认证名称',
|
||
`authentication_time` datetime(6) NULL DEFAULT NULL COMMENT '认证时间',
|
||
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '登录密码',
|
||
`password_modify_time` datetime(6) NULL 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) NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '生产商家表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`manufacturer_login_log` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`login_type` int NULL DEFAULT NULL COMMENT '登录类型，0: 账号密码登录，1：手机号验证码登录',
|
||
`login_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '登陆ip地址',
|
||
`login_time` datetime(6) NOT NULL COMMENT '登陆时间',
|
||
`login_success` bit(1) NOT NULL COMMENT '登录状态，0：失败，1：成功',
|
||
`create_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_login_log_supervisor_id`(`user_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '厂商用户最后登陆信息' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`owner` (
|
||
`id` bigint NOT NULL COMMENT '使用者id',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '使用者昵称',
|
||
`telephone` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '使用者手机号',
|
||
`type` int NOT NULL COMMENT '使用者类型，0：个人，1：企业',
|
||
`avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像地址',
|
||
`authentication_status` int NOT NULL COMMENT '认证状态，0： 未认证，1已认证',
|
||
`authentication_time` datetime(6) NULL DEFAULT NULL 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) NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '普通用户表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`owner_account` (
|
||
`id` bigint NOT NULL COMMENT '账号id，一个用户可有多种登陆方式',
|
||
`user_id` bigint NULL DEFAULT NULL COMMENT '用户id',
|
||
`user_account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户账号',
|
||
`password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户密码',
|
||
`password_modify_time` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '最后更新密码时间',
|
||
`type` smallint NULL DEFAULT NULL COMMENT '用户账号类型0:手机号密码登录',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '是否删除：0:正常，1:删除',
|
||
`create_at` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '注册时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_account_user_account`(`user_account`) USING BTREE,
|
||
INDEX `idx_account_user_id`(`user_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '普通用户账号表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`owner_detail` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
`owner_id` bigint NOT NULL COMMENT '使用者id',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
|
||
`number` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证件号码',
|
||
`f_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '法人姓名',
|
||
`f_number` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '法人证件号码',
|
||
`pic_front` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件照(正面)',
|
||
`pic_back` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件照(反面)',
|
||
`start_at` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件起始日期',
|
||
`end_at` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '证件结束日期',
|
||
`license_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '营业执照',
|
||
`company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '企业名称',
|
||
`company_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '统一社会信用代码',
|
||
`create_at` datetime(6) NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户',
|
||
`type` int NULL DEFAULT NULL COMMENT '使用者类型，0：个人，1：企业',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '用户是否被删除，0：否，1：删除',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '普通用户认证表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`owner_login_log` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`login_type` int NULL DEFAULT NULL COMMENT '登录类型，0: 账号密码登录，1：手机号验证码登录',
|
||
`login_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '登陆ip地址',
|
||
`login_time` datetime(6) NOT NULL COMMENT '登陆时间',
|
||
`login_success` bit(1) NOT NULL COMMENT '登录状态，0：失败，1：成功',
|
||
`create_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_login_log_supervisor_id`(`user_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '普通用户登陆信息' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator` (
|
||
`id` bigint NOT NULL COMMENT '厂商id',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '厂商昵称',
|
||
`is_system_admin` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是系统管理员',
|
||
`telephone` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '厂商手机号',
|
||
`avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像地址',
|
||
`authentication_status` int NOT NULL COMMENT '认证状态，0： 未认证，1已认证',
|
||
`authentication_time` datetime(6) NULL DEFAULT NULL COMMENT '认证时间',
|
||
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '登录密码',
|
||
`password_modify_time` datetime(6) NULL 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) NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户',
|
||
`update_at` datetime(6) NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '监管者表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_api` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'api编码，以API_开头',
|
||
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '接口名称',
|
||
`desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '接口描述',
|
||
`service_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '服务id',
|
||
`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '接口路径',
|
||
`request_method` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '请求方法。POST,GET,DELETE 等',
|
||
`is_open` bit(1) NULL DEFAULT b'0' COMMENT '是否公开，0:内部接口，1:公开接口，可不做权限校验',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '是否删除，0:正常，1:删除',
|
||
`create_at` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建用户id',
|
||
`update_at` datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_resource_code`(`code`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '监管者接口表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_login_log` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`login_type` int NULL DEFAULT NULL COMMENT '登录类型，0: 账号密码登录，1：手机号验证码登录',
|
||
`login_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '登陆ip地址',
|
||
`login_time` datetime(6) NOT NULL COMMENT '登陆时间',
|
||
`login_success` bit(1) NOT NULL COMMENT '登录状态，0：失败，1：成功',
|
||
`create_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_login_log_supervisor_id`(`user_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '监管员用户最后登陆信息' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_resource` (
|
||
`id` bigint NOT NULL COMMENT '自增id',
|
||
`code` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '资源标识，菜单类型以 MENU_ 开头，按钮类型以 ACTION_ 开头，接口类型以 API_ 开头',
|
||
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '资源名称',
|
||
`icon` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '资源图标，针对菜单',
|
||
`desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '资源描述',
|
||
`type` smallint NULL DEFAULT NULL COMMENT '资源类型，0:菜单，1:按钮',
|
||
`path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '资源路径',
|
||
`pid` bigint NULL DEFAULT NULL COMMENT '菜单拥有上级菜单id，按钮的上级id表示菜单的按钮',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '是否删除，0:正常，1:删除',
|
||
`order` int NULL DEFAULT NULL COMMENT '展示顺序，值越小，越在前',
|
||
`level` int NULL DEFAULT 1 COMMENT '菜单层级',
|
||
`create_at` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建用户id',
|
||
`update_at` datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '监管者资源表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_resource_api_mapping` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`resource_id` bigint NULL DEFAULT NULL COMMENT '资源id',
|
||
`api_code` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '接口编码',
|
||
`create_at` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_resouce_api_resouce_id`(`resource_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '资源与接口的映射表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_role` (
|
||
`id` bigint NOT NULL COMMENT '角色id',
|
||
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '角色名称',
|
||
`code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '角色标志符',
|
||
`desc` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '角色描述',
|
||
`is_frozen` bit(1) NULL DEFAULT b'0' COMMENT '是否锁定,0:正常，1:锁定',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '删除标志：0:正常，1删除',
|
||
`create_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_at` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建用户id',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '监管者角色表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_role_mapping` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`regulator_id` bigint NOT NULL COMMENT '监管者id',
|
||
`role_id` bigint NOT NULL COMMENT '角色id',
|
||
`create_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_regulator_role_regulator_id`(`regulator_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '监管者角色映射表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`regulator_role_resource_mapping` (
|
||
`id` bigint NOT NULL COMMENT '主键id',
|
||
`role_id` bigint NOT NULL COMMENT '角色id',
|
||
`resource_id` bigint NOT NULL COMMENT '资源id',
|
||
`create_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
INDEX `idx_role_resouce_role_id`(`role_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '角色与资源的关系' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`sync_mnf` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`mnf_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产厂家',
|
||
`number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '统一社会信用代码',
|
||
`is_delete` bit(1) NULL DEFAULT b'0' COMMENT '删除标识 0: 未删除 1: 删除',
|
||
`create_at` datetime NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户id',
|
||
`update_at` datetime NULL DEFAULT NULL COMMENT '更新用户id',
|
||
`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户id',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '预置的无人机生产厂商表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`sync_type` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`mnf_id` int NULL DEFAULT NULL COMMENT '生产厂商id',
|
||
`product_model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品型号',
|
||
`product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品名称',
|
||
`product_category` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品类别',
|
||
`product_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品类型',
|
||
`empty_weight` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '空机重量(kg)',
|
||
`max_weight` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '最大起飞重量(kg)',
|
||
`usage` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '产品用途',
|
||
`is_delete` bit(1) NULL DEFAULT b'0' COMMENT '0: 未删除 1: 已删除',
|
||
`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户id',
|
||
`create_at` datetime NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新用户id',
|
||
`update_at` datetime NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '预置无人机厂商的无人机型号表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`sys_area_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`code` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标识唯一区域，街道以上区域采用addcode代替。（东莞精确到街道，此时采用上级addcode+街道名称拼音首字母）',
|
||
`parent_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '父节点code',
|
||
`city_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城市编码（每个城市不同）',
|
||
`ad_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道没有独有的adcode，均继承父类（区县）的adcode',
|
||
`level` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区域类型。country:国家，province:省份；city:地市；district：区县 street:街道',
|
||
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名称',
|
||
`longitude` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '中心坐标点经度',
|
||
`latitude` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '中心坐标点维度',
|
||
`source` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'gaodemap:高德地图；man:人工',
|
||
`create_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE INDEX `uniq_code`(`code`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '区域表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`uav_detail` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`identity` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机唯一产品识别码',
|
||
`verify_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '校验码',
|
||
`code_type` int NULL DEFAULT NULL COMMENT '识别码类型 0: 识别码 1: 序列号',
|
||
`manuf_id` int NULL DEFAULT NULL COMMENT '生产厂商id',
|
||
`manuf` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '生产厂商',
|
||
`product_model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品型号',
|
||
`product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品名称',
|
||
`device_id` int NULL DEFAULT NULL COMMENT '型号id',
|
||
`usage` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '使用用途',
|
||
`usage_other` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '其他使用用途',
|
||
`registry_time` datetime NULL DEFAULT NULL COMMENT '注册时间',
|
||
`photo_front` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '无人机正面照片',
|
||
`photo_num` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备标识照片',
|
||
`device_status` tinyint NULL DEFAULT NULL COMMENT '设备状态(0: 未激活 1: 激活 2: 已注销 3: --)',
|
||
`cert_status` int NULL DEFAULT NULL COMMENT '证书状态0：在用，1：过期，2：吊销',
|
||
`logout_date` datetime NULL DEFAULT NULL COMMENT '注销时间',
|
||
`logout_type` int NULL DEFAULT NULL COMMENT '注销类别(0:所有权变更 1:报废/失事 2:出国 3:其他)',
|
||
`logout_reason` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '注销原因',
|
||
`logout_man` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '注销人',
|
||
`create_at` datetime NULL DEFAULT NULL COMMENT '创建时间',
|
||
`create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
|
||
`create_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人姓名',
|
||
`update_at` datetime NULL DEFAULT NULL COMMENT '更新时间',
|
||
`update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
|
||
`update_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人姓名',
|
||
`is_del` bit(1) NULL DEFAULT b'0' COMMENT '是否删除 0: 未删除 1: 删除',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '无人机注册表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `uav_lifecycle`.`weak_password` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
|
||
`password` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '弱密码',
|
||
`encode_password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '加密后的密码',
|
||
`create_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`create_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建用户id',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE INDEX `uniq_weak_password_password`(`password`) USING BTREE,
|
||
UNIQUE INDEX `uniq_weak_password_encoder_passwprd`(`encode_password`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '弱密码表' ROW_FORMAT = DYNAMIC;
|
||
|
||
|
||
create table if not exists uav_lifecycle.bridge_device_provider
|
||
(
|
||
id bigint not null comment '序列号'
|
||
primary key,
|
||
name varchar(90) null comment '提供方名称',
|
||
code varchar(255) not null comment '提供商编码',
|
||
mqtt_user varchar(100) null comment 'mqtt用户',
|
||
is_del bit default b'0' null comment '是否删除',
|
||
create_by bigint null comment '创建人',
|
||
create_at datetime null comment '创建时间',
|
||
update_by bigint null comment '更新人',
|
||
update_at datetime null comment '更新时间'
|
||
)
|
||
comment '设备提供方';
|
||
|
||
create table if not exists uav_lifecycle.bridge_data_consumer
|
||
(
|
||
id bigint not null comment '序号'
|
||
primary key,
|
||
code varchar(255) null comment '消费者编码',
|
||
business varchar(255) null comment '业务',
|
||
purpose varchar(255) null comment '用途',
|
||
contact_name varchar(100) null comment '联系人名称',
|
||
contact_phone varchar(128) null comment '联系人电话',
|
||
mqtt_user varchar(100) null comment 'mqtt用户名',
|
||
is_del bit default b'0' null comment '是否删除',
|
||
create_by bigint null comment '创建人',
|
||
create_at datetime null comment '创建时间',
|
||
update_by bigint null comment '更新人',
|
||
update_at datetime null comment '更新时间'
|
||
)
|
||
comment '数据消费方';
|
||
|
||
create table if not exists uav_lifecycle.sys_dictionary
|
||
(
|
||
id bigint unsigned auto_increment comment '自增ID'
|
||
primary key,
|
||
category varchar(24) not null comment '分类',
|
||
type_code varchar(64) not null comment '类型编号',
|
||
type_name varchar(255) not null comment '类型名称',
|
||
update_at datetime not null comment '修改时间',
|
||
create_at datetime not null comment '创建时间',
|
||
constraint uniq_category
|
||
unique (category, type_code)
|
||
)
|
||
comment '词典配置表' row_format = DYNAMIC;
|
||
|
||
|
||
create table if not exists uav_lifecycle.gis_info
|
||
(
|
||
id bigint auto_increment comment '主键'
|
||
primary key,
|
||
name varchar(50) not null comment '原始名称',
|
||
is_deleted tinyint(1) default 0 not null comment '是否删除，0：未删除，1：已删除',
|
||
url varchar(255) collate utf8mb4_general_ci not null comment '路径',
|
||
file_name varchar(255) collate utf8mb4_general_ci null comment '文件名称',
|
||
xmin decimal(12, 6) null comment '范围：最小经度',
|
||
xmax decimal(12, 6) null comment '范围：最大经度',
|
||
ymin decimal(12, 6) null comment '范围：最小纬度',
|
||
ymax decimal(12, 6) null comment '范围：最大纬度',
|
||
file_id varchar(255) null comment '文件Id',
|
||
company_id varchar(20) null comment '公司id',
|
||
create_time datetime null comment '创建时间',
|
||
create_by varchar(20) null comment '创建人',
|
||
update_time datetime null comment '修改时间',
|
||
update_by varchar(20) null comment '修改人',
|
||
data_type tinyint(1) default 0 null comment '数据类型(0:建图结果、1:三维模型、2:单体模型、3:点云数据、4:地形数据、5:地图数据）',
|
||
status tinyint(1) default 1 null comment '从图层列表中添加（0:添加，1：取消）',
|
||
bucket_name varchar(255) null comment '桶名',
|
||
screen_space int default 16 null comment '显示精度',
|
||
height decimal(10, 2) default 0.00 null comment '相对于模型本身高度的偏移值(米)'
|
||
)
|
||
comment '地图数据管理列表' charset = utf8mb4;
|
||
|
||
|
||
alter table owner_detail
|
||
change f_name fname varchar(100) null comment '法人姓名';
|
||
|
||
alter table owner_detail
|
||
change f_number fnumber varchar(128) null comment '法人证件号码';
|
||
alter table uav_detail
|
||
add product_category varchar(100) null comment '产品类别' after product_name;
|
||
|
||
alter table uav_detail
|
||
add product_type varchar(100) null comment '产品类型' after product_category;
|
||
CREATE TABLE `flight_activities` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`flight_code` bigint NOT NULL COMMENT '飞行活动编号',
|
||
`apply_user_id` bigint DEFAULT NULL COMMENT '申请人ID',
|
||
`apply_user_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '申请人姓名',
|
||
`apply_time` datetime DEFAULT NULL COMMENT '申请时间',
|
||
`operate_mode` int DEFAULT NULL COMMENT '操控模式（1遥控飞行、2自主飞行',
|
||
`fly_mode` int DEFAULT NULL COMMENT '飞行模式（1超视距飞行、2视距内飞行）',
|
||
`task_type` varchar(24) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '任务性质（违法建设检查、海事巡查、汛期地质灾害抢险排查飞行、训练飞行）等',
|
||
`plan_flytime_start` datetime DEFAULT NULL COMMENT '预计飞行开始时间',
|
||
`plan_flytime_end` datetime DEFAULT NULL COMMENT '预计飞行结束时间',
|
||
`max_fly_height` decimal(10,2) DEFAULT NULL COMMENT '最大飞行高度(米）',
|
||
`contact_info` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '通信联络方式',
|
||
`land` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '起降备降场地',
|
||
`emergency` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应急处置程序',
|
||
`support` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '特殊飞行保障需求',
|
||
`monitor` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '导航和被监视能力',
|
||
`method` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '飞行速度和进出空域方法',
|
||
`radio` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '指挥控制链路无线电频率以及占用带宽',
|
||
`radar` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '二次雷达应答机或有关自动监视设备代码',
|
||
`apply_info` text COLLATE utf8mb4_bin COMMENT '其他必要信息',
|
||
`other_relate_info` varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '涉及其他活动',
|
||
`file` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '活动批准文件',
|
||
`status` int DEFAULT NULL COMMENT '飞行活动状态：0.待申请； 1.待审批； 2.已通过； 3.已拒绝； 4. 被驳回；5失效；',
|
||
`aircraft_ids` text COLLATE utf8mb4_bin COMMENT '航空器id',
|
||
`operator_ids` text COLLATE utf8mb4_bin COMMENT '操控员id',
|
||
`airspace_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '飞行空域名称',
|
||
`airspace_info` text COLLATE utf8mb4_bin COMMENT '飞行空域信息。geoJson格式',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`is_del` bit(1) DEFAULT b'0' COMMENT '0 未删除 1 删除',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_flight_code` (`flight_code`),
|
||
KEY `idx_plan_flytime_start` (`plan_flytime_start`),
|
||
KEY `idx_plan_flytime_end` (`plan_flytime_end`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='飞行活动表';
|
||
|
||
|
||
CREATE TABLE `flight_activity_task` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`flight_code` bigint NOT NULL COMMENT '关联飞行活动',
|
||
`task_node_status` int DEFAULT NULL COMMENT '任务节点状态 0.待申请； 1.待审批； 2.已通过； 3.已拒绝； 4. 被驳回；5失效',
|
||
`op_user_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '审批人Id',
|
||
`comment` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '评论',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_flight_code` (`flight_code`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='审批任务记录';
|
||
|
||
CREATE TABLE `ky_airspace_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '空域编号',
|
||
`area_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '空域名称',
|
||
`area_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '空域范围（geojson）',
|
||
`area_type` int NOT NULL COMMENT '空域类型：10 .适飞区 20 管制区 30 临时空域',
|
||
`area_sub_category` int NOT NULL COMMENT '制区分类，针对管制区，21：管制区1， 22：管制区2,... 11:适飞区， 31.临时空域',
|
||
`lower_height` decimal(9,3) DEFAULT NULL COMMENT '空域底部距离地面高度',
|
||
`upper_height` decimal(9,3) DEFAULT NULL COMMENT '空域顶部距离地面高度',
|
||
`create_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人ID',
|
||
`shape_type` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域形状。“Point”、“MultiPoint”、“LineString”、“MultiLineString”、“Polygon”、“MultiPolygon”、“GeometryCollection”',
|
||
`start_time` datetime DEFAULT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '生效截至时间',
|
||
`aircraft_models` int DEFAULT NULL COMMENT '适用飞行机型:1微型; 2轻型 4小型 8中型 16大型。多个机型求和',
|
||
`capacity` int DEFAULT NULL COMMENT '空域容量',
|
||
`risk_level` int DEFAULT NULL COMMENT '风险等级。 10.低风险 20.中风险 30高风险。 NULL则代表未计算 0.无数据,不做评估',
|
||
`min_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最小值',
|
||
`max_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最大值',
|
||
`min_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最小值',
|
||
`max_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最大值',
|
||
`status` int DEFAULT NULL COMMENT '发布状态。 0. 待审批 1.待发布 2.已发布',
|
||
`publish_time` datetime DEFAULT NULL COMMENT '发布时间',
|
||
`code_status` tinyint DEFAULT NULL COMMENT '同步打码状态 0.待打码 1.打码中 2. 打码成功 3.打码失败 ',
|
||
`code_desc` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '打码失败描述',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '关联空域配置记录（修改前的记录）',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`),
|
||
UNIQUE KEY `uniq_area_name` (`area_name`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_end_time` (`end_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='空域配置表';
|
||
|
||
|
||
CREATE TABLE `ky_airspace_config_his` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域编号',
|
||
`area_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域名称',
|
||
`area_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '空域范围（geojson）',
|
||
`area_type` int DEFAULT NULL COMMENT '空域类型：10 .适飞区 20 管制区 30 临时空域',
|
||
`area_sub_category` int DEFAULT NULL COMMENT '制区分类，针对管制区，21：管制区1， 22：管制区2,... 11:适飞区， 31.临时空域',
|
||
`lower_height` decimal(9,3) DEFAULT NULL COMMENT '空域底部距离地面高度',
|
||
`upper_height` decimal(9,3) DEFAULT NULL COMMENT '空域顶部距离地面高度',
|
||
`create_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人ID',
|
||
`shape_type` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域形状。“Point”、“MultiPoint”、“LineString”、“MultiLineString”、“Polygon”、“MultiPolygon”、“GeometryCollection”',
|
||
`start_time` datetime DEFAULT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '生效截至时间',
|
||
`aircraft_models` int DEFAULT NULL COMMENT '适用飞行机型:1微型; 2轻型 4小型 8中型 16大型。多个机型求和',
|
||
`capacity` int DEFAULT NULL COMMENT '空域容量',
|
||
`risk_level` int DEFAULT NULL COMMENT '风险等级。 10.低风险 20.中风险 30高风险。 NULL则代表未计算 0.无数据,不做评估',
|
||
`min_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最小值',
|
||
`max_latitude84` decimal(10,7) DEFAULT NULL COMMENT '空域纬度最大值',
|
||
`min_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最小值',
|
||
`max_longitude84` decimal(10,7) DEFAULT NULL COMMENT '空域经度最大值',
|
||
`real_start_time` datetime DEFAULT NULL COMMENT '实际生效开始时间',
|
||
`real_end_time` datetime DEFAULT NULL COMMENT '实际生效结束时间',
|
||
`status` int DEFAULT NULL COMMENT '发布状态。 0. 待审批 1.待发布 2.已发布',
|
||
`publish_time` datetime DEFAULT NULL COMMENT '发布时间',
|
||
`sync_status` int DEFAULT NULL COMMENT '同步状态。 0.未同步 1.已同步',
|
||
`sync_time` datetime DEFAULT NULL COMMENT '同步时间',
|
||
`validate` tinyint NOT NULL COMMENT '当前记录是否有效。 0, 无效 1.有效',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '关联空域配置记录（修改前的记录）',
|
||
`ref_id` bigint DEFAULT NULL COMMENT '关联的空域记录id',
|
||
`operate_type` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '操作类型. add:新建； update：修改',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '失效时间',
|
||
`delete_type` tinyint DEFAULT NULL COMMENT '触发失效原因 1.删除 2.修改',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_area_name` (`area_name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='空域配置历史表';
|
||
|
||
|
||
CREATE TABLE `ky_airspace_risk_level` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '空域编号',
|
||
`risk_level` int DEFAULT NULL COMMENT '风险等级。 10.低风险 20.中风险 30高风险。 40.无数据,无风险等级 NULL则代表未计算',
|
||
`remark` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '风险备注',
|
||
`status` tinyint DEFAULT NULL COMMENT '0. 待评估 1. 评估中 2.完成评估 3.评估失败 ',
|
||
`evaluation_time` datetime DEFAULT NULL COMMENT '评估时间',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`),
|
||
KEY `idx_update_at` (`update_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='空域风险等级';
|
||
|
||
|
||
CREATE TABLE `ky_config_geonum_detail` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`area_category` tinyint DEFAULT NULL COMMENT '1.飞行活动空域; 2. 划设空域',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '飞行活动编号，空域编号',
|
||
`area_type` int DEFAULT NULL COMMENT '空域类型：10 .适飞区 20 管制区 30 临时空域',
|
||
`area_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '空域信息,geoJSON格式',
|
||
`geo_codes` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '网格对象列表，将区域栅格化为网格编码列表（包含geo_num4，geo_num10_16）',
|
||
`start_time` datetime DEFAULT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '生效截至时间',
|
||
`aircraft_models` int DEFAULT NULL COMMENT '适用飞行机型:1微型; 2轻型 4小型 8中型 16大型。多个机型求和',
|
||
`lower_height` decimal(9,3) DEFAULT NULL COMMENT '空域底部距离地面高度',
|
||
`upper_height` decimal(9,3) DEFAULT NULL COMMENT '空域顶部距离地面高度',
|
||
`sync_status` tinyint DEFAULT NULL COMMENT '0. 待同步; 1.完成空域打码 2.完成网格码同步|完成删除 3.打码失败',
|
||
`is_del` tinyint(1) DEFAULT NULL COMMENT '是否删除。 0.未删除 1.已删除',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`,`area_category`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci comment='空域任务表';
|
||
|
||
|
||
CREATE TABLE `ky_flight_act_geo_detail` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`flight_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '飞行活动编号',
|
||
`stat_date` date DEFAULT NULL COMMENT '统计日期，活动跨日期情况，可产生多条记录',
|
||
`pass_time` datetime DEFAULT NULL COMMENT '通过时间，同步比对使用',
|
||
`geo_num4s` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '二维网格码列表，将区域栅格化为网格编码列表',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态。0.未生成分钟明细表；1.已生成分钟明细表 2.已生产冲突数据',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_flight_code` (`flight_code`,`stat_date`),
|
||
KEY `idx_pass_time` (`pass_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='飞行活动区域网格明细';
|
||
|
||
|
||
CREATE TABLE `ky_flight_conflict_area` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`stat_date` date DEFAULT NULL COMMENT '统计日期',
|
||
`area_type` int DEFAULT NULL COMMENT '空域类型：10 .适飞区 20 管制区 30 临时空域',
|
||
`area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '空域编号',
|
||
`flight_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '飞行活动编号',
|
||
`caculate_time` datetime DEFAULT NULL COMMENT '记录产生时间，每天凌晨2点对前一天飞行活动进行分析',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_code` (`area_code`,`stat_date`,`area_type`,`flight_code`),
|
||
KEY `idx_stat_date` (`stat_date`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='飞行活动冲突空域记录';
|
||
|
||
CREATE TABLE `ky_airspace_kpi` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`area_code` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域编号',
|
||
`kpi_type` tinyint DEFAULT NULL COMMENT '指标类型。 11，人口密度 12,网络信号',
|
||
`kpi_val` varchar(2000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '空域指标值,json存储',
|
||
`status` tinyint DEFAULT NULL COMMENT '0. 待评估 1. 评估中 2.完成评估 3.评估失败 4.空域已删除,不做评估',
|
||
`evaluation_time` datetime DEFAULT NULL COMMENT '最近1次评估时间',
|
||
`tries` int DEFAULT NULL COMMENT '尝试次数',
|
||
`expect_execute_time` datetime DEFAULT NULL COMMENT '期望下次评估时间',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uniq_area_code` (`area_code`,`kpi_type`),
|
||
KEY `idx_execute_time` (`expect_execute_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin comment='空域指标表';
|
||
|
||
delete from `sys_dictionary` where 1=1;
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certificateType','小型无人机操控员执照','小型无人机操控员执照','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certificateType','中型无人机操控员执照','中型无人机操控员执照','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certificateType','大型无人机操控员执照','大型无人机操控员执照','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','飞机','飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','垂直起降飞机','垂直起降飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','旋翼机','旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','倾转旋翼机','倾转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','飞艇','飞艇','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','自由气球','自由气球','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','滑翔机','滑翔机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('categoryLevel','特殊类','特殊类','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certLevel','旋翼机-多旋翼','旋翼机-多旋翼','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certLevel','旋翼机-自转旋翼机','旋翼机-自转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certLevel','旋翼机-直升机','旋翼机-直升机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('certLevel','特殊类','特殊类','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-飞机','超视距-飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-垂直起降飞机','超视距-垂直起降飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-多旋翼','超视距-多旋翼','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-自转旋翼机','超视距-自转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-直升机','超视距-直升机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-倾转旋翼机','超视距-倾转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-飞艇','超视距-飞艇','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-自由气球','超视距-自由气球','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-滑翔机','超视距-滑翔机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('sightLevel','超视距-特殊类','超视距-特殊类','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-飞机','教员-飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-垂直起降飞机','教员-垂直起降飞机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-多旋翼','教员-多旋翼','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-自转旋翼机','教员-自转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-直升机','教员-直升机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-倾转旋翼机','教员-倾转旋翼机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-飞艇','教员-飞艇','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-自由气球','教员-自由气球','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-滑翔机','教员-滑翔机','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
insert into `sys_dictionary` (`category`, `type_code`, `type_name`, `update_at`, `create_at`) values('teacherLevel','教员-型别教员','教员-型别教员','2024-04-12 14:49:08','2024-04-12 14:49:08');
|
||
|
||
CREATE TABLE `owner_flyer_info` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`flyer_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '执照编号',
|
||
`flyer_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
|
||
`sign_date` datetime DEFAULT NULL COMMENT '签发日期',
|
||
`expiration_date` datetime DEFAULT NULL COMMENT '失效日期',
|
||
`certificate_type` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '执照种类',
|
||
`category_level` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类别等级',
|
||
`cert_level` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '级别等级',
|
||
`sight_level` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '超视距等级',
|
||
`teacher_level` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '教员等级',
|
||
`audit_status` int DEFAULT '0' COMMENT '审批状态,0:待验证，1、已通过，2、未通过',
|
||
`no_pass_desc` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '不通过原因',
|
||
`remark` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
|
||
`certificate` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '证书附件',
|
||
`telephone` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '手机号',
|
||
`owner_user_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '归属用户ID',
|
||
`is_del` tinyint(1) DEFAULT NULL COMMENT '是否删除。 0.未删除 1.已删除',
|
||
`create_by` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建者ID',
|
||
`update_by` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '修改者ID',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='操控员信息表';
|
||
|
||
alter table sync_mnf
|
||
add unit_code varchar(100) null comment '统一社会信用代码(new)' after number;
|
||
alter table `uav_lifecycle`.`ky_flight_act_geo_detail` add index `idx_stat_date` (`stat_date`);
|
||
CREATE TABLE `warn_handle` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`warn_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '告警唯一标识',
|
||
`handle_userId` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '处理人员id',
|
||
`handle_time` datetime DEFAULT NULL COMMENT '处理时间',
|
||
`handle_type` int DEFAULT NULL COMMENT '处置方式。 11. 迫降 12.返航 21 无线电干扰 22 导航诱骗',
|
||
`handle_longitude84` double DEFAULT NULL COMMENT '处置时无人机经度(WGS-84)',
|
||
`handle_latitude84` double DEFAULT NULL COMMENT '处置时无人机的纬度(WGS-84)',
|
||
`handle_height` double DEFAULT NULL COMMENT '处置时无人机的高度',
|
||
`handle_content` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '处理内容',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_warn_code` (`warn_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='告警处置记录';
|
||
|
||
|
||
CREATE TABLE `warn_item` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`warn_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '告警唯一标识',
|
||
`uas_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '无人机识别码（或探测目标id',
|
||
`id_type` tinyint DEFAULT NULL COMMENT '无人机的id类型,1、三方平台 2、监管无人机 3、RemoteID 4.探测轨迹',
|
||
`cooperate` tinyint NOT NULL COMMENT '是否为合作类 1.合作； 0.非合作',
|
||
`provider_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '设备来源厂家编号',
|
||
`device_id` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据来源设备id（设备方定义id)',
|
||
`device_type` int DEFAULT NULL COMMENT '来源设备类型(0、5G-A基站，（5ga） 1、雷达设备，(radar)2、频谱设备， (spec)3、光电设备，(oe)4、反制设备,（cm）5、诱骗设备，(dec)6、干扰设备，(ifr) 7、指挥车，(cv)8、察打一体， (isrs)999、其他设备(other))',
|
||
`event` tinyint NOT NULL COMMENT '告警类型(1 偏航预警；2 闯入管制区；3 闯入临时空域；4.非合飞行）',
|
||
`warn_key` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '告警特征值(uasId、event、refcode等产生）',
|
||
`warning_content` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '告警信息,主要是存放各种告警说明',
|
||
`start_time` datetime DEFAULT NULL COMMENT '开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
||
`status` tinyint NOT NULL COMMENT '告警状态：0：告警中，1:告警结束',
|
||
`longitude84` double DEFAULT NULL COMMENT '产生告警时经度(WGS-84)',
|
||
`latitude84` double DEFAULT NULL COMMENT '产生告警时纬度(WGS-84)',
|
||
`height` double DEFAULT NULL COMMENT '产生告警时高度',
|
||
`altitude` double DEFAULT NULL COMMENT '拔高度，单位米',
|
||
`speed` double DEFAULT NULL COMMENT '产生告警时的地速度',
|
||
`end_type` tinyint DEFAULT NULL COMMENT '结束类型，0：自动结束，1：手动结束',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'event为2，3时：首次产生告警时关联空域编号； event为3时：飞行活动编号',
|
||
`ref_area_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'event为2，3时：首次产生告警时关联空域名称.只存第一个空域名称',
|
||
`uav_update_time` datetime DEFAULT NULL COMMENT '无人机信息更新时间(以下为告警最新对应无人机信息)',
|
||
`uav_lng` double DEFAULT NULL COMMENT '最近1次告警时无人机经度',
|
||
`uav_lat` double DEFAULT NULL COMMENT '最近1次告警时无人机纬度',
|
||
`uav_height` double DEFAULT NULL COMMENT '最近1次告警时无人机高度',
|
||
`uav_altitude` double DEFAULT NULL COMMENT '最近1次告警时无人机的拔高度，单位米',
|
||
`uav_speed` double DEFAULT NULL COMMENT '最近1次告警时无人机地速度',
|
||
`is_deal` tinyint DEFAULT NULL COMMENT '是否已经处置。 0.未处置 1.已处置',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_uas_id_event` (`uas_id`,`event`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_warn_key` (`warn_key`,`status`),
|
||
KEY `idx_end_time` (`end_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='告警记录';
|
||
|
||
|
||
create table if not exists uav_lifecycle.bridge_device_info
|
||
(
|
||
id bigint auto_increment comment '序列号'
|
||
primary key,
|
||
provider_code varchar(255) not null comment '设备提供方编码;每类设备接入平台时，由平台统一分配',
|
||
device_id varchar(255) not null comment '设备id;每台设备接入时，需提供该设备ID',
|
||
device_name varchar(255) null comment '设备名称',
|
||
device_longitude decimal(24, 6) null comment '设备安装经度',
|
||
device_latitude decimal(24, 6) null comment '设备安装纬度',
|
||
device_altitude decimal(24, 6) null comment '设备安装海拔高度',
|
||
device_type int null comment '设备类型;0、5G-A基站，1、合作无人机，2、监管无人机，3、RemoteID无人机，4、雷达设备，5、频谱设备，6、光电设备，7、反制设备，8、诱骗设备，9、干扰设备，10、指挥车，11、察打一体，999、其他设备',
|
||
work_state int null comment '设备工作状态;0：未工作，1：工作中，2:设备异常',
|
||
extension varchar(500) null comment '扩展信息',
|
||
is_del bit default b'0' null comment '是否删除',
|
||
create_by bigint null comment '创建人',
|
||
create_at datetime null comment '创建时间',
|
||
update_by bigint null comment '更新人',
|
||
update_at datetime null comment '更新时间'
|
||
)
|
||
comment '设备表';
|
||
|
||
|
||
alter table `uav_lifecycle`.`warn_item` add index `idx_warn_code` (`warn_code`, `status`);
|
||
CREATE TABLE `warn_item_his` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
|
||
`warn_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '告警唯一标识',
|
||
`uas_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '无人机识别码（或探测目标id',
|
||
`id_type` tinyint DEFAULT NULL COMMENT '无人机的id类型,1、三方平台 2、监管无人机 3、RemoteID 4.探测轨迹',
|
||
`cooperate` tinyint NOT NULL COMMENT '是否为合作类 1.合作； 0.非合作',
|
||
`provider_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '设备来源厂家编号',
|
||
`device_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据来源设备id（设备方定义id)',
|
||
`device_type` int DEFAULT NULL COMMENT '来源设备类型(0、5G-A基站，（5ga） 1、雷达设备，(radar)2、频谱设备， (spec)3、光电设备，(oe)4、反制设备,（cm）5、诱骗设备，(dec)6、干扰设备，(ifr) 7、指挥车，(cv)8、察打一体， (isrs)999、其他设备(other))',
|
||
`event` tinyint NOT NULL COMMENT '告警类型(1 偏航预警；2 闯入管制区；3 闯入临时空域；4.非合飞行）',
|
||
`warn_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '告警特征值(uasId、event、refcode等产生）',
|
||
`warning_content` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '告警信息,主要是存放各种告警说明',
|
||
`start_time` datetime DEFAULT NULL COMMENT '开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
||
`status` tinyint NOT NULL COMMENT '告警状态：0：告警中，1:告警结束',
|
||
`longitude84` double DEFAULT NULL COMMENT '产生告警时经度(WGS-84)',
|
||
`latitude84` double DEFAULT NULL COMMENT '产生告警时纬度(WGS-84)',
|
||
`height` double DEFAULT NULL COMMENT '产生告警时高度',
|
||
`altitude` double DEFAULT NULL COMMENT '拔高度，单位米',
|
||
`speed` double DEFAULT NULL COMMENT '产生告警时的地速度',
|
||
`end_type` tinyint DEFAULT NULL COMMENT '结束类型，0：自动结束，1：手动结束',
|
||
`ref_area_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'event为2，3时：首次产生告警时关联空域编号； event为3时：飞行活动编号',
|
||
`ref_area_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'event为2，3时：首次产生告警时关联空域名称.只存第一个空域名称',
|
||
`uav_update_time` datetime DEFAULT NULL COMMENT '无人机信息更新时间(以下为告警最新对应无人机信息)',
|
||
`uav_lng` double DEFAULT NULL COMMENT '最近1次告警时无人机经度',
|
||
`uav_lat` double DEFAULT NULL COMMENT '最近1次告警时无人机纬度',
|
||
`uav_height` double DEFAULT NULL COMMENT '最近1次告警时无人机高度',
|
||
`uav_altitude` double DEFAULT NULL COMMENT '最近1次告警时无人机的拔高度，单位米',
|
||
`uav_speed` double DEFAULT NULL COMMENT '最近1次告警时无人机地速度',
|
||
`is_deal` tinyint DEFAULT NULL COMMENT '是否已经处置。 0.未处置 1.已处置',
|
||
`update_at` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_uas_id_event` (`uas_id`,`event`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_warn_key` (`warn_key`,`status`),
|
||
KEY `idx_end_time` (`end_time`),
|
||
KEY `idx_warn_code` (`warn_code`,`status`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='告警历史记录';
|
||
|
||
CREATE TABLE `uav_lifecycle`.`uav_sec_alarm_event` (
|
||
`id` BIGINT(20) NOT NULL COMMENT '自增ID',
|
||
`alarm_id` VARCHAR(50) NOT NULL COMMENT '告警ID',
|
||
`alarm_type` VARCHAR(50) NOT NULL COMMENT '告警类型',
|
||
`alarm_object` VARCHAR(50) NOT NULL COMMENT '告警对象',
|
||
`alarm_content` VARCHAR(100) NOT NULL COMMENT '告警内容【指令名称、对象名称】',
|
||
`alarm_start_time` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '告警触发时间',
|
||
`alarm_state` INT(1) NOT NULL DEFAULT 0 COMMENT '告警状态: 0进行中，1已解除',
|
||
`report_time` TIMESTAMP(0) NULL DEFAULT NULL COMMENT '告警上报时间',
|
||
`wrong_report` INT(1) NOT NULL DEFAULT 0 COMMENT '是否误报: 0否，1是',
|
||
`alarm_end_time` TIMESTAMP(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '告警结束时间',
|
||
`is_delete` INT(1) NOT NULL DEFAULT 0 COMMENT '删除标志，1为删除，0为未删除',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_uav_sec_alarm_event_id`(`alarm_id`),
|
||
INDEX `idx_uav_sec_alarm_event_type`(`alarm_type`),
|
||
INDEX `idx_uav_sec_alarm_event_object`(`alarm_object`)
|
||
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT = '安全告警事件表';
|
||
|
||
|
||
create table epc_auth
|
||
(
|
||
id int auto_increment comment 'id'
|
||
primary key,
|
||
uav_id varchar(100) null comment '无人机唯一识别码',
|
||
imei varchar(100) null comment 'imei',
|
||
sn varchar(100) null comment 'sn',
|
||
imsi varchar(100) null comment 'imsi',
|
||
create_at datetime null comment '创建时间',
|
||
is_delete bit default b'0' null comment '0 未删除 1 已删除'
|
||
) comment "epc验证表" ;
|
||
|