3518 lines
184 KiB
SQL
3518 lines
184 KiB
SQL
-- MySQL dump 10.13 Distrib 9.4.0, for macos15.4 (arm64)
|
||
--
|
||
-- Host: 127.0.0.1 Database: test
|
||
-- ------------------------------------------------------
|
||
-- Server version 9.4.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 */;
|
||
|
||
--
|
||
-- Current Database: `test`
|
||
--
|
||
|
||
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
|
||
|
||
USE `test`;
|
||
|
||
--
|
||
-- Table structure for table `admin_operation_logs`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `admin_operation_logs`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `admin_operation_logs` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`admin_id` int NOT NULL,
|
||
`operation_type` varchar(255) DEFAULT NULL,
|
||
`description` text,
|
||
`ip_address` varchar(45) DEFAULT NULL,
|
||
`user_agent` text,
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `ai_chat_history`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `ai_chat_history`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `ai_chat_history` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`session_id` varchar(64) NOT NULL,
|
||
`user_id` bigint DEFAULT NULL,
|
||
`user_message` text NOT NULL,
|
||
`ai_response` text NOT NULL,
|
||
`model` varchar(64) DEFAULT NULL,
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`salt` varchar(6) DEFAULT NULL COMMENT 'MD5的盐值',
|
||
`last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
|
||
`update_password_time` datetime DEFAULT NULL COMMENT '修改密码时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_session_id` (`session_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_file`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_file`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_file` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单ID',
|
||
`order_no` varchar(60) DEFAULT NULL COMMENT '订单号码',
|
||
`category` varchar(10) NOT NULL COMMENT '分类1:上传 2:下载',
|
||
`location` varchar(1) NOT NULL COMMENT '存储位置 1:本地 2:Minio',
|
||
`name` varchar(60) NOT NULL COMMENT '文件名称',
|
||
`suffix` varchar(10) NOT NULL COMMENT '文件尾缀',
|
||
`path` varchar(200) NOT NULL COMMENT '文件路径',
|
||
`length` int NOT NULL COMMENT '文件大小',
|
||
`size` varchar(20) NOT NULL COMMENT '文件大小(带单位)',
|
||
`uuid` varchar(60) NOT NULL COMMENT '文件UUID',
|
||
`content_type` varchar(60) DEFAULT NULL COMMENT '文件类型',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` int DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文件管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_logs_error`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_logs_error`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_logs_error` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`request_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '请求ID',
|
||
`ip` varchar(20) NOT NULL COMMENT 'IP',
|
||
`ip_addr` varchar(45) NOT NULL COMMENT 'IP所属地',
|
||
`user_agent` varchar(200) DEFAULT NULL COMMENT '登录代理',
|
||
`request_uri` varchar(100) DEFAULT NULL COMMENT '请求URI',
|
||
`request_method` varchar(20) DEFAULT NULL COMMENT '请求方式',
|
||
`content_type` varchar(100) DEFAULT NULL COMMENT '请求内容类型',
|
||
`operation` varchar(200) DEFAULT NULL COMMENT '接口说明',
|
||
`method_name` varchar(64) DEFAULT NULL COMMENT '方法名称',
|
||
`method_params` longtext COMMENT '请求参数',
|
||
`use_time` bigint DEFAULT NULL COMMENT '请求耗时',
|
||
`exception_message` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '异常信息',
|
||
`exception_class` varchar(200) DEFAULT NULL COMMENT '异常类',
|
||
`line` int DEFAULT NULL COMMENT '异常行号',
|
||
`stack_trace` longtext COMMENT '堆栈信息',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='错误异常日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_logs_login`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_logs_login`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_logs_login` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`user_name` varchar(40) DEFAULT '',
|
||
`user_real_name` varchar(20) DEFAULT NULL COMMENT '真实姓名',
|
||
`ip` varchar(20) NOT NULL COMMENT 'IP',
|
||
`ip_addr` varchar(45) NOT NULL COMMENT 'IP所属地',
|
||
`user_agent` varchar(200) DEFAULT NULL COMMENT '登录代理',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '登录状态(0:失败 1:成功)',
|
||
`message` varchar(200) DEFAULT NULL COMMENT '登录错误日志',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='登录日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_logs_operation`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_logs_operation`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_logs_operation` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`request_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '请求ID',
|
||
`ip` varchar(20) NOT NULL COMMENT 'IP',
|
||
`ip_addr` varchar(45) NOT NULL COMMENT 'IP所属地',
|
||
`user_agent` varchar(200) DEFAULT NULL COMMENT '登录代理',
|
||
`request_uri` varchar(100) DEFAULT NULL COMMENT '请求URI',
|
||
`request_method` varchar(20) DEFAULT NULL COMMENT '请求方式',
|
||
`content_type` varchar(100) DEFAULT NULL COMMENT '请求内容类型',
|
||
`operation` varchar(200) DEFAULT NULL COMMENT '接口说明',
|
||
`method_name` varchar(64) DEFAULT NULL COMMENT '方法名称',
|
||
`method_params` longtext COMMENT '请求参数',
|
||
`use_time` bigint DEFAULT NULL COMMENT '请求耗时',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='操作日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_logs_scheduler`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_logs_scheduler`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_logs_scheduler` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`job_name` varchar(190) NOT NULL COMMENT '任务名称',
|
||
`job_group` varchar(190) NOT NULL COMMENT '任务组别',
|
||
`trigger_name` varchar(190) NOT NULL COMMENT '触发器名称',
|
||
`trigger_group` varchar(190) NOT NULL COMMENT '触发器组别',
|
||
`use_time` bigint DEFAULT NULL COMMENT '耗时',
|
||
`status` varchar(20) DEFAULT NULL COMMENT '状态',
|
||
`exception_message` varchar(500) DEFAULT NULL COMMENT '异常信息',
|
||
`exception_class` varchar(200) DEFAULT NULL COMMENT '异常类',
|
||
`line` int DEFAULT NULL COMMENT '异常行号',
|
||
`stack_trace` longtext COMMENT '堆栈信息',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='调度日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_BLOB_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_BLOB_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_BLOB_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`BLOB_DATA` blob,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
CONSTRAINT `mon_qrtz_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `mon_qrtz_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_CALENDARS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_CALENDARS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_CALENDARS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`CALENDAR_NAME` varchar(190) NOT NULL,
|
||
`CALENDAR` blob NOT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`CALENDAR_NAME`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_CRON_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_CRON_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_CRON_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`CRON_EXPRESSION` varchar(120) NOT NULL,
|
||
`TIME_ZONE_ID` varchar(80) DEFAULT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
CONSTRAINT `mon_qrtz_CRON_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `mon_qrtz_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_FIRED_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_FIRED_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_FIRED_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`ENTRY_ID` varchar(95) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`INSTANCE_NAME` varchar(190) NOT NULL,
|
||
`FIRED_TIME` bigint NOT NULL,
|
||
`SCHED_TIME` bigint NOT NULL,
|
||
`PRIORITY` int NOT NULL,
|
||
`STATE` varchar(16) NOT NULL,
|
||
`JOB_NAME` varchar(190) DEFAULT NULL,
|
||
`JOB_GROUP` varchar(190) DEFAULT NULL,
|
||
`IS_NONCONCURRENT` varchar(1) DEFAULT NULL,
|
||
`REQUESTS_RECOVERY` varchar(1) DEFAULT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`ENTRY_ID`),
|
||
KEY `IDX_mon_qrtz_FT_TRIG_INST_NAME` (`SCHED_NAME`,`INSTANCE_NAME`),
|
||
KEY `IDX_mon_qrtz_FT_INST_JOB_REQ_RCVRY` (`SCHED_NAME`,`INSTANCE_NAME`,`REQUESTS_RECOVERY`),
|
||
KEY `IDX_mon_qrtz_FT_J_G` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
|
||
KEY `IDX_mon_qrtz_FT_JG` (`SCHED_NAME`,`JOB_GROUP`),
|
||
KEY `IDX_mon_qrtz_FT_T_G` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
KEY `IDX_mon_qrtz_FT_TG` (`SCHED_NAME`,`TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_JOB_DETAILS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_JOB_DETAILS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_JOB_DETAILS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`JOB_NAME` varchar(190) NOT NULL,
|
||
`JOB_GROUP` varchar(190) NOT NULL,
|
||
`DESCRIPTION` varchar(250) DEFAULT NULL,
|
||
`JOB_CLASS_NAME` varchar(250) NOT NULL,
|
||
`IS_DURABLE` varchar(1) NOT NULL,
|
||
`IS_NONCONCURRENT` varchar(1) NOT NULL,
|
||
`IS_UPDATE_DATA` varchar(1) NOT NULL,
|
||
`REQUESTS_RECOVERY` varchar(1) NOT NULL,
|
||
`JOB_DATA` blob,
|
||
PRIMARY KEY (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
|
||
KEY `IDX_mon_qrtz_J_REQ_RECOVERY` (`SCHED_NAME`,`REQUESTS_RECOVERY`),
|
||
KEY `IDX_mon_qrtz_J_GRP` (`SCHED_NAME`,`JOB_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_LOCKS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_LOCKS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_LOCKS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`LOCK_NAME` varchar(40) NOT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`LOCK_NAME`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_PAUSED_TRIGGER_GRPS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_PAUSED_TRIGGER_GRPS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_PAUSED_TRIGGER_GRPS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_SCHEDULER_STATE`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_SCHEDULER_STATE`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_SCHEDULER_STATE` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`INSTANCE_NAME` varchar(190) NOT NULL,
|
||
`LAST_CHECKIN_TIME` bigint NOT NULL,
|
||
`CHECKIN_INTERVAL` bigint NOT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`INSTANCE_NAME`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_SIMPLE_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_SIMPLE_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_SIMPLE_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`REPEAT_COUNT` bigint NOT NULL,
|
||
`REPEAT_INTERVAL` bigint NOT NULL,
|
||
`TIMES_TRIGGERED` bigint NOT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
CONSTRAINT `mon_qrtz_SIMPLE_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `mon_qrtz_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_SIMPROP_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_SIMPROP_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_SIMPROP_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`STR_PROP_1` varchar(512) DEFAULT NULL,
|
||
`STR_PROP_2` varchar(512) DEFAULT NULL,
|
||
`STR_PROP_3` varchar(512) DEFAULT NULL,
|
||
`INT_PROP_1` int DEFAULT NULL,
|
||
`INT_PROP_2` int DEFAULT NULL,
|
||
`LONG_PROP_1` bigint DEFAULT NULL,
|
||
`LONG_PROP_2` bigint DEFAULT NULL,
|
||
`DEC_PROP_1` decimal(13,4) DEFAULT NULL,
|
||
`DEC_PROP_2` decimal(13,4) DEFAULT NULL,
|
||
`BOOL_PROP_1` varchar(1) DEFAULT NULL,
|
||
`BOOL_PROP_2` varchar(1) DEFAULT NULL,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
CONSTRAINT `mon_qrtz_SIMPROP_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `mon_qrtz_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_qrtz_TRIGGERS`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_qrtz_TRIGGERS`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_qrtz_TRIGGERS` (
|
||
`SCHED_NAME` varchar(120) NOT NULL,
|
||
`TRIGGER_NAME` varchar(190) NOT NULL,
|
||
`TRIGGER_GROUP` varchar(190) NOT NULL,
|
||
`JOB_NAME` varchar(190) NOT NULL,
|
||
`JOB_GROUP` varchar(190) NOT NULL,
|
||
`DESCRIPTION` varchar(250) DEFAULT NULL,
|
||
`NEXT_FIRE_TIME` bigint DEFAULT NULL,
|
||
`PREV_FIRE_TIME` bigint DEFAULT NULL,
|
||
`PRIORITY` int DEFAULT NULL,
|
||
`TRIGGER_STATE` varchar(16) NOT NULL,
|
||
`TRIGGER_TYPE` varchar(8) NOT NULL,
|
||
`START_TIME` bigint NOT NULL,
|
||
`END_TIME` bigint DEFAULT NULL,
|
||
`CALENDAR_NAME` varchar(190) DEFAULT NULL,
|
||
`MISFIRE_INSTR` smallint DEFAULT NULL,
|
||
`JOB_DATA` blob,
|
||
PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
|
||
KEY `IDX_mon_qrtz_T_J` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
|
||
KEY `IDX_mon_qrtz_T_JG` (`SCHED_NAME`,`JOB_GROUP`),
|
||
KEY `IDX_mon_qrtz_T_C` (`SCHED_NAME`,`CALENDAR_NAME`),
|
||
KEY `IDX_mon_qrtz_T_G` (`SCHED_NAME`,`TRIGGER_GROUP`),
|
||
KEY `IDX_mon_qrtz_T_STATE` (`SCHED_NAME`,`TRIGGER_STATE`),
|
||
KEY `IDX_mon_qrtz_T_N_STATE` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
|
||
KEY `IDX_mon_qrtz_T_N_G_STATE` (`SCHED_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
|
||
KEY `IDX_mon_qrtz_T_NEXT_FIRE_TIME` (`SCHED_NAME`,`NEXT_FIRE_TIME`),
|
||
KEY `IDX_mon_qrtz_T_NFT_ST` (`SCHED_NAME`,`TRIGGER_STATE`,`NEXT_FIRE_TIME`),
|
||
KEY `IDX_mon_qrtz_T_NFT_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`),
|
||
KEY `IDX_mon_qrtz_T_NFT_ST_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_STATE`),
|
||
KEY `IDX_mon_qrtz_T_NFT_ST_MISFIRE_GRP` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
|
||
CONSTRAINT `mon_qrtz_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `mon_qrtz_JOB_DETAILS` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `mon_scheduler`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `mon_scheduler`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `mon_scheduler` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`job_name` varchar(190) NOT NULL COMMENT '任务名称',
|
||
`job_group` varchar(190) NOT NULL COMMENT '任务组别',
|
||
`trigger_name` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '触发器名称',
|
||
`trigger_group` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '触发器组别',
|
||
`job_data` varchar(200) DEFAULT NULL COMMENT '任务参数',
|
||
`trigger_data` varchar(200) DEFAULT NULL COMMENT '触发器参数',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` int DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='调度管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_config`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_config`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '配置ID',
|
||
`config_key` varchar(100) NOT NULL COMMENT '配置键',
|
||
`config_value` text COMMENT '配置值',
|
||
`config_type` varchar(20) DEFAULT 'string' COMMENT '配置类型(string,number,boolean,json)',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '配置描述',
|
||
`category` varchar(50) DEFAULT 'system' COMMENT '配置分类',
|
||
`is_readonly` tinyint(1) DEFAULT '0' COMMENT '是否只读',
|
||
`create_user` varchar(50) DEFAULT NULL COMMENT '创建人',
|
||
`create_user_id` bigint DEFAULT NULL COMMENT '创建人ID',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_user` varchar(50) DEFAULT NULL COMMENT '更新人',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '更新人ID',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `config_key` (`config_key`),
|
||
KEY `idx_config_key` (`config_key`),
|
||
KEY `idx_category` (`category`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统配置表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_dict`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_dict`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_dict` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`name` varchar(64) DEFAULT NULL COMMENT '字典名称',
|
||
`code` varchar(64) NOT NULL COMMENT '字典编码',
|
||
`type` varchar(2) DEFAULT '1' COMMENT '字典类型(1:系统字典,2:业务字典)',
|
||
`sort` int DEFAULT '999' COMMENT '排序值',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '字典描述',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据字典管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_dict_item`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_dict_item`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_dict_item` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`dict_id` bigint DEFAULT NULL COMMENT '父字典ID',
|
||
`dict_code` varchar(64) NOT NULL COMMENT '父字典编码',
|
||
`value` varchar(64) NOT NULL COMMENT '数据值',
|
||
`zh_cn` varchar(64) NOT NULL COMMENT '中文名称',
|
||
`en_us` varchar(64) DEFAULT NULL COMMENT '英文名称',
|
||
`type` varchar(20) DEFAULT NULL COMMENT '类型(前端渲染类型)',
|
||
`sort` int DEFAULT '999' COMMENT '排序值',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '字典描述',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据字典子项管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_menu`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_menu`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_menu` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`parent_id` bigint DEFAULT NULL COMMENT '父菜单ID',
|
||
`type` varchar(20) NOT NULL COMMENT '菜单类型 1:目录 2:菜单',
|
||
`name` varchar(50) NOT NULL COMMENT '菜单名称',
|
||
`i18n_key` varchar(100) NOT NULL COMMENT '多语言标题',
|
||
`route_name` varchar(50) NOT NULL COMMENT '路由名称',
|
||
`route_path` varchar(255) NOT NULL COMMENT '菜单路径',
|
||
`icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
|
||
`icon_type` varchar(2) DEFAULT NULL COMMENT '图标类型 1:iconify icon 2:local icon',
|
||
`component` varchar(60) DEFAULT NULL COMMENT '路由组件',
|
||
`keep_alive` varchar(2) DEFAULT '1' COMMENT '缓存页面(Y:是,N:否)',
|
||
`hide` varchar(2) DEFAULT '0' COMMENT '是否隐藏(Y:是,N:否)',
|
||
`href` varchar(64) DEFAULT NULL COMMENT '外部链接',
|
||
`sort` int DEFAULT '999' COMMENT '排序值',
|
||
`multi_tab` varchar(2) DEFAULT NULL COMMENT '支持多标签(Y:是,N:否)',
|
||
`fixed_index_in_tab` int DEFAULT NULL COMMENT '固定在页签中的序号',
|
||
`iframe_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '内链URL',
|
||
`query` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '路由查询参数',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID(0:全局菜单)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_menu_customer_id` (`customer_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='菜单管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_notice`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_notice`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_notice` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`category` varchar(2) NOT NULL COMMENT '分类 1:通知 2:公告',
|
||
`title` varchar(200) DEFAULT NULL COMMENT '标题',
|
||
`content` varchar(500) NOT NULL COMMENT '内容',
|
||
`release_time` datetime NOT NULL COMMENT '发布时间',
|
||
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通知公告';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_org`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_org`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_org` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`org_name` varchar(100) NOT NULL COMMENT '组织名称',
|
||
`parent_id` bigint DEFAULT '0' COMMENT '父级组织ID',
|
||
`org_code` varchar(50) DEFAULT NULL COMMENT '组织编码',
|
||
`sort_order` int DEFAULT '0' COMMENT '排序',
|
||
`status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
|
||
`is_deleted` tinyint DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_org_parent_id` (`parent_id`),
|
||
KEY `idx_sys_org_code` (`org_code`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='组织机构表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_org_closure`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_org_closure`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_org_closure` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`ancestor_id` bigint NOT NULL COMMENT '祖先节点ID',
|
||
`descendant_id` bigint NOT NULL COMMENT '后代节点ID',
|
||
`depth` int NOT NULL DEFAULT '0' COMMENT '层级深度(0表示自己到自己)',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID(0表示系统级别)',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_closure_ancestor_descendant` (`ancestor_id`,`descendant_id`,`customer_id`),
|
||
KEY `idx_closure_ancestor` (`ancestor_id`,`customer_id`),
|
||
KEY `idx_closure_descendant` (`descendant_id`,`customer_id`),
|
||
KEY `idx_closure_depth` (`depth`),
|
||
KEY `idx_closure_customer` (`customer_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='组织架构闭包关系表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_org_manager_cache`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_org_manager_cache`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_org_manager_cache` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`org_id` bigint NOT NULL COMMENT '组织ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`user_name` varchar(50) NOT NULL COMMENT '用户名',
|
||
`role_type` varchar(20) NOT NULL COMMENT '角色类型(manager:管理员,supervisor:主管)',
|
||
`org_level` int NOT NULL DEFAULT '0' COMMENT '组织层级',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID',
|
||
`is_active` tinyint DEFAULT '1' COMMENT '是否有效(1有效0无效)',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_manager_org_user_role` (`org_id`,`user_id`,`role_type`,`customer_id`),
|
||
KEY `idx_manager_org` (`org_id`,`customer_id`),
|
||
KEY `idx_manager_user` (`user_id`,`customer_id`),
|
||
KEY `idx_manager_role` (`role_type`,`customer_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='组织管理员关系缓存表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_org_units`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_org_units`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_org_units` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`parent_id` bigint DEFAULT NULL COMMENT '父组织/部门/子部门ID',
|
||
`name` varchar(200) NOT NULL COMMENT '组织/部门/子部门名称',
|
||
`code` varchar(100) DEFAULT NULL COMMENT '组织/部门/子部门编码',
|
||
`abbr` varchar(50) DEFAULT NULL COMMENT '组织/部门/子部门名称简写',
|
||
`level` int NOT NULL DEFAULT '0' COMMENT '组织/部门/子部门层级',
|
||
`ancestors` varchar(500) NOT NULL COMMENT '祖先节点',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '组织/部门/子部门描述',
|
||
`sort` int DEFAULT '999' COMMENT '排序值',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局组织，顶级组织ID表示租户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_org_customer_id` (`customer_id`),
|
||
KEY `idx_sys_org_customer_parent` (`customer_id`,`parent_id`),
|
||
KEY `idx_sys_org_customer_level` (`customer_id`,`level`),
|
||
KEY `idx_sys_org_ancestors` (`ancestors`),
|
||
KEY `idx_sys_org_customer_status` (`customer_id`,`status`,`is_deleted`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1969969539086893059 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='组织/部门/子部门管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_permission`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_permission`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_permission` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`menu_id` bigint DEFAULT NULL COMMENT '菜单ID',
|
||
`menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
|
||
`name` varchar(50) NOT NULL COMMENT '权限(按钮)名称',
|
||
`resource` varchar(500) NOT NULL COMMENT '权限资源',
|
||
`description` varchar(255) DEFAULT NULL COMMENT '描述',
|
||
`sort` int DEFAULT NULL COMMENT '排序',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID(0:全局权限)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_permission_customer_id` (`customer_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='权限(按钮)管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_position`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_position`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_position` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`name` varchar(200) NOT NULL COMMENT '岗位名称',
|
||
`code` varchar(100) DEFAULT NULL COMMENT '岗位编码',
|
||
`abbr` varchar(50) DEFAULT NULL COMMENT '岗位名称简写',
|
||
`description` varchar(500) DEFAULT NULL COMMENT '岗位描述',
|
||
`sort` int DEFAULT '999' COMMENT '排序值',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`org_id` bigint DEFAULT NULL,
|
||
`risk_level` varchar(20) DEFAULT 'normal' COMMENT '岗位风险等级（normal: 普通, medium: 中风险, high: 高风险）',
|
||
`weight` decimal(5,2) DEFAULT '0.15' COMMENT '健康评分按岗位权重',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID (0表示全局岗位，所有租户可见)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_position_customer_id` (`customer_id`),
|
||
KEY `idx_sys_position_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_sys_position_customer_status` (`customer_id`,`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='岗位管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_role`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_role`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_role` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`parent_id` bigint DEFAULT '0' COMMENT '父主键',
|
||
`role_name` varchar(50) NOT NULL COMMENT '角色名称',
|
||
`role_code` varchar(50) NOT NULL COMMENT '角色编码',
|
||
`description` varchar(255) DEFAULT NULL COMMENT '描述',
|
||
`sort` int NOT NULL DEFAULT '999' COMMENT '排序',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否为管理员角色（0普通角色，1管理员角色）',
|
||
`admin_level` tinyint(1) NOT NULL DEFAULT '2' COMMENT '管理员级别（0:超级管理员，1:租户管理员，2:部门管理员）',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局角色，所有租户可见',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
KEY `idx_sys_role_customer_id` (`customer_id`),
|
||
KEY `idx_sys_role_customer_status` (`customer_id`,`status`),
|
||
KEY `idx_sys_role_admin_level` (`is_admin`,`admin_level`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_role_menu`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_role_menu`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_role_menu` (
|
||
`id` bigint NOT NULL,
|
||
`role_id` bigint NOT NULL COMMENT '角色ID',
|
||
`menu_id` bigint NOT NULL COMMENT '菜单ID',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色菜单管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_role_permission`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_role_permission`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_role_permission` (
|
||
`id` bigint NOT NULL,
|
||
`role_id` bigint NOT NULL COMMENT '角色ID',
|
||
`permission_id` bigint NOT NULL COMMENT '权限ID',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色权限管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_test`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_test`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_test` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`menu_id` bigint DEFAULT NULL COMMENT '菜单ID',
|
||
`menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
|
||
`name` varchar(50) NOT NULL COMMENT '权限(按钮)名称',
|
||
`resource` varchar(500) NOT NULL COMMENT '权限资源',
|
||
`description` varchar(255) DEFAULT NULL COMMENT '描述',
|
||
`sort` int DEFAULT NULL COMMENT '排序',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`is_query` char(1) DEFAULT '1' COMMENT '是否查询',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试代码生成表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_user`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_user`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_user` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`user_name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名称',
|
||
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
|
||
`nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '昵称',
|
||
`real_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '真实姓名',
|
||
`avatar` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '头像',
|
||
`email` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
|
||
`phone` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机',
|
||
`gender` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '0' COMMENT '性别 0保密 1男 2女',
|
||
`create_user` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`salt` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'MD5的盐值',
|
||
`last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
|
||
`update_password_time` datetime DEFAULT NULL COMMENT '修改密码时间',
|
||
`status` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`customer_id` bigint NOT NULL DEFAULT '0',
|
||
`user_card_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`working_years` int unsigned DEFAULT '0' COMMENT '工龄（年）',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`org_name` varchar(100) DEFAULT NULL COMMENT '组织名称',
|
||
`birthday` date DEFAULT NULL COMMENT '生日',
|
||
`user_type` tinyint DEFAULT '0' COMMENT '用户类型: 0=普通用户, 1=部门管理员, 2=租户管理员, 3=超级管理员',
|
||
`admin_level` tinyint DEFAULT '0' COMMENT '管理级别: 0=非管理员, 1=部门级, 2=租户级, 3=系统级',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `uk_username_customer` (`user_name`,`customer_id`) COMMENT '用户名租户内唯一',
|
||
KEY `idx_sys_user_customer_id` (`customer_id`),
|
||
KEY `idx_sys_user_customer_status` (`customer_id`,`status`),
|
||
KEY `idx_sys_user_customer_deleted` (`customer_id`,`is_deleted`),
|
||
KEY `idx_sys_user_device_sn` (`device_sn`),
|
||
KEY `idx_sys_user_phone` (`phone`),
|
||
KEY `idx_sys_user_org_id` (`org_id`),
|
||
KEY `idx_sys_user_org_name` (`org_name`),
|
||
KEY `idx_status_customer` (`status`,`customer_id`,`is_deleted`),
|
||
KEY `idx_user_type` (`user_type`) COMMENT '用户类型索引',
|
||
KEY `idx_admin_level` (`admin_level`) COMMENT '管理级别索引',
|
||
KEY `idx_org_admin` (`org_id`,`admin_level`) COMMENT '组织管理员复合索引',
|
||
KEY `idx_customer_admin` (`customer_id`,`admin_level`) COMMENT '租户管理员复合索引',
|
||
KEY `idx_customer_status` (`customer_id`,`status`,`is_deleted`) COMMENT '租户状态组合索引',
|
||
KEY `idx_org_deleted` (`org_id`,`is_deleted`) COMMENT '组织软删除组合索引'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表 - user_name字段不再强制唯一，员工可以重名，通过ID区分';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_user_org`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_user_org`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_user_org` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织/部门/子部门ID',
|
||
`principal` varchar(2) DEFAULT '0' COMMENT '组织/部门/子部门负责人(0:否,1:是)',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自组织或用户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_user_org_customer_user` (`customer_id`,`user_id`),
|
||
KEY `idx_sys_user_org_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_sys_user_org_user_id` (`user_id`),
|
||
KEY `idx_sys_user_org_org_id` (`org_id`),
|
||
KEY `idx_sys_user_org_principal` (`user_id`,`principal`),
|
||
KEY `idx_sys_user_org_composite` (`customer_id`,`user_id`,`org_id`,`is_deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户组织/部门/子部门管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_user_position`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_user_position`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_user_position` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`position_id` bigint DEFAULT NULL COMMENT '岗位ID',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自用户所属租户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_user_position_customer` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户岗位管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `sys_user_role`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `sys_user_role`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `sys_user_role` (
|
||
`id` bigint NOT NULL,
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`role_id` bigint NOT NULL COMMENT '角色ID',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` varchar(2) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sys_user_role_customer` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_action_log`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_action_log`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_action_log` (
|
||
`log_id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`id` int DEFAULT NULL,
|
||
`alert_id` bigint unsigned NOT NULL,
|
||
`action` varchar(255) NOT NULL,
|
||
`action_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`action_user` varchar(255) DEFAULT NULL,
|
||
`action_user_id` bigint DEFAULT NULL,
|
||
`details` text,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`handled_via` varchar(50) DEFAULT NULL COMMENT '处理途径（如微信、消息等）',
|
||
`result` varchar(50) DEFAULT NULL COMMENT '处理结果（如成功、失败等）',
|
||
`notification_type` varchar(20) DEFAULT NULL COMMENT '通知类型:wechat/message/both',
|
||
`retry_attempt` int DEFAULT '0' COMMENT '重试次数',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自告警所属租户',
|
||
`channel` varchar(32) DEFAULT NULL COMMENT '通知渠道：wechat|message|email|screen_popup|webhook',
|
||
`actor` varchar(64) DEFAULT NULL COMMENT '操作执行者：system|user|role',
|
||
`context` json DEFAULT NULL COMMENT '操作上下文：请求/响应/错误码/外部回执',
|
||
`action_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作发生时间',
|
||
`execution_time_ms` int DEFAULT NULL COMMENT '操作执行耗时(毫秒)',
|
||
PRIMARY KEY (`log_id`),
|
||
KEY `fk_alert_id` (`alert_id`),
|
||
KEY `idx_logs_alert_time` (`alert_id`,`action_at` DESC),
|
||
KEY `idx_logs_action_result` (`action`,`result`,`action_at` DESC),
|
||
KEY `idx_logs_channel_time` (`channel`,`action_at` DESC),
|
||
KEY `idx_logs_customer_action` (`customer_id`,`action`,`action_at` DESC),
|
||
CONSTRAINT `fk_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `t_alert_info` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970421178553970690 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_cache_sync`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_cache_sync`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_cache_sync` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL COMMENT '客户ID',
|
||
`cache_key` varchar(255) NOT NULL COMMENT '缓存键名',
|
||
`version` bigint NOT NULL DEFAULT '1' COMMENT '缓存版本号',
|
||
`last_sync_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后同步时间',
|
||
`sync_status` enum('pending','synced','failed') DEFAULT 'pending' COMMENT '同步状态',
|
||
`error_message` text COMMENT '错误信息',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_customer_cache` (`customer_id`,`cache_key`),
|
||
KEY `idx_sync_status` (`sync_status`),
|
||
KEY `idx_last_sync` (`last_sync_time`),
|
||
KEY `idx_customer_id` (`customer_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警规则缓存同步状态表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_config_cache`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_config_cache`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_config_cache` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`config_key` varchar(128) NOT NULL COMMENT '配置键',
|
||
`config_value` json NOT NULL COMMENT '配置值',
|
||
`cache_expires_at` datetime NOT NULL COMMENT '缓存过期时间',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_cache_customer_key` (`customer_id`,`config_key`),
|
||
KEY `idx_cache_expires` (`cache_expires_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警配置缓存表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_event_queue`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_event_queue`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_event_queue` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`event_id` varchar(128) NOT NULL COMMENT '事件唯一标识',
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`event_data` json NOT NULL COMMENT '标准化事件数据',
|
||
`status` varchar(16) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|PROCESSING|PROCESSED|FAILED',
|
||
`retry_count` int NOT NULL DEFAULT '0' COMMENT '重试次数',
|
||
`max_retries` int NOT NULL DEFAULT '3' COMMENT '最大重试次数',
|
||
`next_retry_at` datetime DEFAULT NULL COMMENT '下次重试时间',
|
||
`processed_at` datetime DEFAULT NULL COMMENT '处理完成时间',
|
||
`error_message` text COMMENT '错误信息',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_queue_event_id` (`event_id`),
|
||
KEY `idx_queue_status_retry` (`status`,`next_retry_at`),
|
||
KEY `idx_queue_customer_time` (`customer_id`,`create_time`),
|
||
KEY `idx_queue_processing` (`status`,`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警事件队列表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_info` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`rule_id` bigint NOT NULL,
|
||
`rule_version` bigint DEFAULT NULL COMMENT '规则版本',
|
||
`trigger_conditions` json DEFAULT NULL COMMENT '触发条件详情',
|
||
`evaluation_context` json DEFAULT NULL COMMENT '评估上下文',
|
||
`suppression_key` varchar(128) DEFAULT NULL COMMENT '抑制键',
|
||
`escalation_level` int DEFAULT '0' COMMENT '升级级别',
|
||
`ack_required` tinyint(1) DEFAULT '0' COMMENT '是否需要确认',
|
||
`auto_resolve` tinyint(1) DEFAULT '0' COMMENT '是否自动恢复',
|
||
`alert_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`device_sn` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`alert_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`alert_desc` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`severity_level` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'medium',
|
||
`alert_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'pending',
|
||
`assigned_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`assigned_user_id` bigint DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`longitude` decimal(12,8) DEFAULT '22.54036796' COMMENT '经度',
|
||
`latitude` decimal(12,8) DEFAULT '114.01508952' COMMENT '纬度',
|
||
`altitude` decimal(10,2) DEFAULT '0.00' COMMENT '海拔',
|
||
`health_id` bigint DEFAULT NULL,
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局告警，其他值表示特定租户告警',
|
||
`responded_time` timestamp NULL DEFAULT NULL COMMENT '响应时间',
|
||
`response_duration` int unsigned DEFAULT NULL COMMENT '响应耗时（秒）',
|
||
`source` varchar(16) NOT NULL DEFAULT 'server' COMMENT '告警来源：watch|server',
|
||
`event_type` varchar(64) DEFAULT NULL COMMENT '事件类型',
|
||
`metric` varchar(64) DEFAULT NULL COMMENT '体征指标名称',
|
||
`value` double DEFAULT NULL COMMENT '指标数值',
|
||
`unit` varchar(16) DEFAULT NULL COMMENT '数值单位',
|
||
`level` varchar(16) NOT NULL DEFAULT 'minor' COMMENT '告警级别：info|minor|major|critical',
|
||
`status` varchar(24) NOT NULL DEFAULT 'NEW' COMMENT '告警状态：NEW|NOTIFIED|ACKED|ESCALATED|RESOLVED|CLOSED|AWAITING_REVIEW|ERROR',
|
||
`dedup_key` varchar(128) DEFAULT NULL COMMENT '去重键，用于同类告警去重',
|
||
`throttle_until` datetime DEFAULT NULL COMMENT '抑制窗口截止时间',
|
||
`occur_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '告警发生时间',
|
||
`recover_at` datetime DEFAULT NULL COMMENT '告警恢复时间',
|
||
`assigned_to` varchar(64) DEFAULT NULL COMMENT '指派责任人/组',
|
||
`extend` json DEFAULT NULL COMMENT '扩展数据字段',
|
||
`due_time` timestamp NULL DEFAULT NULL COMMENT '处理截止时间',
|
||
`auto_close_time` timestamp NULL DEFAULT NULL COMMENT '自动关闭时间',
|
||
`auto_processed` tinyint(1) DEFAULT '0' COMMENT '是否自动处理',
|
||
`auto_process_time` timestamp NULL DEFAULT NULL COMMENT '自动处理时间',
|
||
`auto_process_reason` text COMMENT '自动处理原因',
|
||
`processing_stage` enum('NEW','ACKNOWLEDGED','IN_PROGRESS','RESOLVED','SUPPRESSED','ESCALATED') DEFAULT 'NEW' COMMENT '处理阶段',
|
||
`resolution_notes` text COMMENT '解决备注',
|
||
`suppression_reason` text COMMENT '抑制原因',
|
||
`suppression_until` timestamp NULL DEFAULT NULL COMMENT '抑制到什么时间',
|
||
`acknowledgment_time` timestamp NULL DEFAULT NULL COMMENT '确认时间',
|
||
`acknowledgment_user` bigint DEFAULT NULL COMMENT '确认人(用户ID)',
|
||
`priority` int DEFAULT '3' COMMENT '告警优先级 (1-10, 1最高)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_alert_type` (`alert_type`),
|
||
KEY `idx_rule_id` (`rule_id`),
|
||
KEY `idx_alert_info_health_user` (`health_id`,`user_id`),
|
||
KEY `idx_alert_info_tenant_status` (`customer_id`,`alert_status`),
|
||
KEY `idx_alert_org_id` (`org_id`),
|
||
KEY `idx_alert_user_id` (`user_id`),
|
||
KEY `idx_alert_org_user` (`org_id`,`user_id`),
|
||
KEY `idx_alert_customer_user` (`customer_id`,`user_id`),
|
||
KEY `idx_alerts_status_level` (`status`,`level`,`occur_at` DESC),
|
||
KEY `idx_alerts_user_time` (`user_id`,`occur_at` DESC,`is_deleted`),
|
||
KEY `idx_alerts_metric_time` (`metric`,`occur_at` DESC,`is_deleted`),
|
||
KEY `idx_alerts_customer_status` (`customer_id`,`status`,`occur_at` DESC),
|
||
KEY `idx_alerts_dedup` (`dedup_key`,`throttle_until`),
|
||
KEY `idx_alerts_org_status` (`org_id`,`status`,`level`,`occur_at` DESC),
|
||
KEY `idx_suppression` (`suppression_key`),
|
||
KEY `idx_escalation` (`escalation_level`),
|
||
KEY `idx_rule_version` (`rule_id`,`rule_version`),
|
||
KEY `idx_trigger_time` (`alert_timestamp`),
|
||
KEY `idx_alert_info_timestamp_rule` (`alert_timestamp`,`rule_id`),
|
||
KEY `idx_alert_priority_processing` (`priority`,`processing_stage`,`auto_processed`),
|
||
KEY `idx_auto_processing` (`auto_processed`,`auto_process_time`),
|
||
KEY `idx_device_alert_type` (`device_sn`,`alert_type`,`processing_stage`),
|
||
KEY `idx_alert_escalation` (`escalation_level`,`due_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970507480737599493 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_notification_config`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_notification_config`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_notification_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`channel` varchar(32) NOT NULL COMMENT '通知渠道',
|
||
`config` json NOT NULL COMMENT '渠道配置参数',
|
||
`is_enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
|
||
`priority` int NOT NULL DEFAULT '0' COMMENT '优先级，数字越小优先级越高',
|
||
`rate_limit_per_minute` int DEFAULT NULL COMMENT '每分钟发送限制',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_notification_customer_channel` (`customer_id`,`channel`,`is_deleted`),
|
||
KEY `idx_notification_enabled` (`is_enabled`,`priority`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警通知渠道配置表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_rule_performance`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_rule_performance`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_rule_performance` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`rule_id` bigint NOT NULL COMMENT '规则ID',
|
||
`customer_id` bigint NOT NULL COMMENT '客户ID',
|
||
`execution_count` bigint DEFAULT '0' COMMENT '执行次数',
|
||
`success_count` bigint DEFAULT '0' COMMENT '成功次数',
|
||
`failure_count` bigint DEFAULT '0' COMMENT '失败次数',
|
||
`avg_execution_time` decimal(10,3) DEFAULT '0.000' COMMENT '平均执行时间(ms)',
|
||
`max_execution_time` decimal(10,3) DEFAULT '0.000' COMMENT '最大执行时间(ms)',
|
||
`last_execution_time` datetime DEFAULT NULL COMMENT '最后执行时间',
|
||
`alert_generated_count` bigint DEFAULT '0' COMMENT '生成告警数量',
|
||
`performance_score` decimal(5,2) DEFAULT '100.00' COMMENT '性能评分(0-100)',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_rule_customer` (`rule_id`,`customer_id`),
|
||
KEY `idx_rule_id` (`rule_id`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_performance_score` (`performance_score`),
|
||
KEY `idx_last_execution` (`last_execution_time`),
|
||
CONSTRAINT `t_alert_rule_performance_ibfk_1` FOREIGN KEY (`rule_id`) REFERENCES `t_alert_rules` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警规则性能统计表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_rules`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_rules`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_rules` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`rule_type` varchar(50) NOT NULL DEFAULT 'metric' COMMENT 'metric|custom|fallback',
|
||
`rule_category` enum('SINGLE','COMPOSITE','COMPLEX') DEFAULT 'SINGLE' COMMENT '规则类型：单体征/复合/复杂',
|
||
`physical_sign` varchar(50) DEFAULT NULL,
|
||
`threshold_min` decimal(10,2) DEFAULT NULL,
|
||
`threshold_max` decimal(10,2) DEFAULT NULL,
|
||
`deviation_percentage` decimal(5,2) DEFAULT NULL,
|
||
`trend_duration` int DEFAULT NULL,
|
||
`parameters` json DEFAULT NULL,
|
||
`trigger_condition` text,
|
||
`alert_message` text,
|
||
`severity_level` varchar(16) DEFAULT 'minor' COMMENT '向后兼容字段，建议使用level',
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`notification_type` varchar(50) DEFAULT 'message' COMMENT '通知类型:wechat/message/both',
|
||
`customer_id` bigint DEFAULT '0',
|
||
`metric` varchar(64) DEFAULT NULL COMMENT '体征指标名称，rule_type=metric时必填',
|
||
`event_type` varchar(64) DEFAULT NULL COMMENT '自定义事件类型，rule_type=custom时必填',
|
||
`dsl` json DEFAULT NULL COMMENT '规则DSL配置(条件/去重/抑制/升级/自恢复)',
|
||
`condition_expression` json DEFAULT NULL COMMENT '复合条件表达式',
|
||
`time_window_seconds` int DEFAULT '300' COMMENT '时间窗口(秒)',
|
||
`cooldown_seconds` int DEFAULT '600' COMMENT '告警冷却期(秒)',
|
||
`priority_level` int DEFAULT '3' COMMENT '优先级(1-5，数字越小优先级越高)',
|
||
`rule_tags` json DEFAULT NULL COMMENT '规则标签，便于分类管理',
|
||
`effective_time_start` time DEFAULT NULL COMMENT '生效开始时间',
|
||
`effective_time_end` time DEFAULT NULL COMMENT '生效结束时间',
|
||
`effective_days` varchar(20) DEFAULT '1,2,3,4,5,6,7' COMMENT '生效星期(1-7)',
|
||
`version` bigint DEFAULT '1' COMMENT '规则版本号',
|
||
`enabled_channels` json DEFAULT NULL COMMENT '启用的通知渠道',
|
||
`level` varchar(16) NOT NULL DEFAULT 'minor' COMMENT '告警级别：info|minor|major|critical',
|
||
`notify` json DEFAULT NULL COMMENT '通知通道数组：wechat|message|email|screen_popup|webhook',
|
||
`is_enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '规则是否启用',
|
||
`auto_process_enabled` tinyint(1) DEFAULT '0' COMMENT '是否启用自动处理',
|
||
`auto_process_action` enum('AUTO_RESOLVE','AUTO_ACKNOWLEDGE','AUTO_ESCALATE','AUTO_SUPPRESS') DEFAULT NULL COMMENT '自动处理动作',
|
||
`auto_process_delay_seconds` int DEFAULT '0' COMMENT '自动处理延迟秒数',
|
||
`suppress_duration_minutes` int DEFAULT '60' COMMENT '抑制持续时间(分钟)',
|
||
`auto_resolve_threshold_count` int DEFAULT '1' COMMENT '自动解决阈值计数',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_rule_type_physical_sign_severity_customer` (`rule_type`,`physical_sign`,`severity_level`,`customer_id`),
|
||
KEY `idx_rules_metric` (`metric`,`is_enabled`,`is_deleted`),
|
||
KEY `idx_rules_event_type` (`event_type`,`is_enabled`,`is_deleted`),
|
||
KEY `idx_rules_level` (`level`,`is_enabled`,`is_deleted`),
|
||
KEY `idx_rules_customer_enabled` (`customer_id`,`is_enabled`,`is_deleted`),
|
||
KEY `idx_customer_category` (`customer_id`,`rule_category`),
|
||
KEY `idx_priority` (`priority_level`),
|
||
KEY `idx_physical_sign_active` (`physical_sign`,`is_enabled`),
|
||
KEY `idx_version` (`version`),
|
||
KEY `idx_effective_time` (`effective_time_start`,`effective_time_end`),
|
||
KEY `idx_rules_category_enabled_customer` (`rule_category`,`is_enabled`,`customer_id`),
|
||
KEY `idx_rules_priority_category` (`priority_level`,`rule_category`),
|
||
CONSTRAINT `chk_composite_condition` CHECK (((`rule_category` <> _utf8mb4'COMPOSITE') or ((`rule_category` = _utf8mb4'COMPOSITE') and (`condition_expression` is not null)))),
|
||
CONSTRAINT `chk_priority_range` CHECK (((`priority_level` >= 1) and (`priority_level` <= 5))),
|
||
CONSTRAINT `chk_time_positive` CHECK (((`time_window_seconds` > 0) and (`cooldown_seconds` >= 0)))
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970388223399931907 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
|
||
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
|
||
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
|
||
/*!50003 SET character_set_client = utf8mb4 */ ;
|
||
/*!50003 SET character_set_results = utf8mb4 */ ;
|
||
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
|
||
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
|
||
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
|
||
DELIMITER ;;
|
||
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_alert_rules_version_update` BEFORE UPDATE ON `t_alert_rules` FOR EACH ROW BEGIN
|
||
-- 当关键字段发生变化时，增加版本号
|
||
IF (NEW.rule_category != OLD.rule_category OR
|
||
NEW.physical_sign != OLD.physical_sign OR
|
||
NEW.threshold_min != OLD.threshold_min OR
|
||
NEW.threshold_max != OLD.threshold_max OR
|
||
NEW.condition_expression != OLD.condition_expression OR
|
||
NEW.time_window_seconds != OLD.time_window_seconds OR
|
||
NEW.cooldown_seconds != OLD.cooldown_seconds OR
|
||
NEW.priority_level != OLD.priority_level OR
|
||
NEW.effective_time_start != OLD.effective_time_start OR
|
||
NEW.effective_time_end != OLD.effective_time_end OR
|
||
NEW.effective_days != OLD.effective_days OR
|
||
NEW.enabled_channels != OLD.enabled_channels OR
|
||
NEW.is_enabled != OLD.is_enabled OR
|
||
NEW.alert_message != OLD.alert_message) THEN
|
||
SET NEW.version = OLD.version + 1;
|
||
END IF;
|
||
END */;;
|
||
DELIMITER ;
|
||
/*!50003 SET sql_mode = @saved_sql_mode */ ;
|
||
/*!50003 SET character_set_client = @saved_cs_client */ ;
|
||
/*!50003 SET character_set_results = @saved_cs_results */ ;
|
||
/*!50003 SET collation_connection = @saved_col_connection */ ;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_statistics`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_statistics`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_statistics` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`stat_date` date NOT NULL COMMENT '统计日期',
|
||
`level` varchar(16) NOT NULL COMMENT '告警级别',
|
||
`total_count` int NOT NULL DEFAULT '0' COMMENT '总告警数',
|
||
`acked_count` int NOT NULL DEFAULT '0' COMMENT '已确认数',
|
||
`resolved_count` int NOT NULL DEFAULT '0' COMMENT '已解决数',
|
||
`escalated_count` int NOT NULL DEFAULT '0' COMMENT '升级数',
|
||
`avg_response_time_seconds` int DEFAULT NULL COMMENT '平均响应时间(秒)',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_stats_unique` (`customer_id`,`org_id`,`stat_date`,`level`),
|
||
KEY `idx_stats_customer_date` (`customer_id`,`stat_date`),
|
||
KEY `idx_stats_org_date` (`org_id`,`stat_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警统计汇总表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_suppression`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_suppression`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_suppression` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`suppression_key` varchar(128) NOT NULL COMMENT '抑制键',
|
||
`rule_id` bigint NOT NULL COMMENT '规则ID',
|
||
`device_sn` varchar(100) DEFAULT NULL COMMENT '设备序列号',
|
||
`customer_id` bigint NOT NULL COMMENT '客户ID',
|
||
`suppression_start` datetime NOT NULL COMMENT '抑制开始时间',
|
||
`suppression_end` datetime DEFAULT NULL COMMENT '抑制结束时间',
|
||
`suppressed_count` int DEFAULT '1' COMMENT '被抑制告警数量',
|
||
`last_suppressed_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后抑制时间',
|
||
`status` enum('active','expired','cancelled') DEFAULT 'active' COMMENT '抑制状态',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_suppression_key` (`suppression_key`),
|
||
KEY `idx_rule_device` (`rule_id`,`device_sn`),
|
||
KEY `idx_customer_status` (`customer_id`,`status`),
|
||
KEY `idx_suppression_time` (`suppression_start`,`suppression_end`),
|
||
CONSTRAINT `t_alert_suppression_ibfk_1` FOREIGN KEY (`rule_id`) REFERENCES `t_alert_rules` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='告警抑制记录表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_alert_types`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_alert_types`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_alert_types` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`alert_type` varchar(255) NOT NULL,
|
||
`status` enum('enable','disable') NOT NULL DEFAULT 'enable',
|
||
`description` text,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_customer_config`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_customer_config`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_customer_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID',
|
||
`customer_name` varchar(255) NOT NULL DEFAULT '' COMMENT '客户名称',
|
||
`description` text,
|
||
`upload_method` enum('wifi','bluetooth') DEFAULT 'wifi',
|
||
`license_key` int NOT NULL,
|
||
`is_support_license` tinyint(1) NOT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`os_version` varchar(200) DEFAULT NULL,
|
||
`org_id` mediumtext,
|
||
`enable_resume` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否支持断点续传',
|
||
`upload_retry_count` int NOT NULL DEFAULT '3' COMMENT 'HTTP 上传失败重试次数',
|
||
`cache_max_count` int NOT NULL DEFAULT '100' COMMENT '缓存最大记录数（条）',
|
||
`upload_retry_interval` int NOT NULL DEFAULT '5' COMMENT 'HTTP 上传失败重试时间',
|
||
`logo_url` varchar(500) DEFAULT NULL COMMENT '客户自定义logo地址',
|
||
`logo_file_name` varchar(200) DEFAULT NULL COMMENT 'logo文件名',
|
||
`logo_upload_time` datetime DEFAULT NULL COMMENT 'logo上传时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_customer_config_customer_id` (`customer_id`),
|
||
KEY `idx_customer_config_logo` (`logo_url`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1969767964045271042 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_bind_request`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_bind_request`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_bind_request` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`device_sn` varchar(100) NOT NULL COMMENT '设备序列号',
|
||
`user_id` bigint NOT NULL COMMENT '申请用户ID',
|
||
`org_id` bigint NOT NULL COMMENT '申请组织ID',
|
||
`status` enum('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING' COMMENT '申请状态',
|
||
`apply_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
||
`approve_time` datetime DEFAULT NULL COMMENT '审批时间',
|
||
`approver_id` bigint DEFAULT NULL COMMENT '审批人ID',
|
||
`comment` varchar(255) DEFAULT NULL COMMENT '审批备注',
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自申请用户的租户信息',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_user_status` (`user_id`,`status`),
|
||
KEY `idx_apply_time` (`apply_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备绑定申请表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_info`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_info` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`system_software_version` varchar(255) DEFAULT '',
|
||
`wifi_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`bluetooth_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`ip_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`network_access_mode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`imei` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`battery_level` int DEFAULT NULL,
|
||
`model` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`status` enum('INACTIVE','ACTIVE') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`wearable_status` enum('WORN','NOT_WORN') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`charging_status` enum('NONE','CHARGING') DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT NULL,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT NULL,
|
||
`timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
|
||
`voltage` int DEFAULT NULL,
|
||
`user_id` bigint DEFAULT NULL COMMENT '绑定用户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '绑定组织ID',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局设备，其他值表示特定租户设备',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `serial_number` (`serial_number`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_org_id` (`org_id`),
|
||
KEY `idx_serial_number` (`serial_number`),
|
||
KEY `idx_device_customer_serial` (`customer_id`,`serial_number`),
|
||
KEY `idx_device_customer_status` (`customer_id`,`status`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970350138024833027 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_info_history`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_info_history`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_info_history` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备唯一编号',
|
||
`timestamp` datetime NOT NULL COMMENT '采集时间',
|
||
`system_software_version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '系统版本',
|
||
`battery_level` int DEFAULT NULL COMMENT '电量',
|
||
`wearable_status` enum('WORN','NOT_WORN') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '佩戴状态',
|
||
`charging_status` enum('NONE','CHARGING') DEFAULT NULL,
|
||
`voltage` int DEFAULT NULL COMMENT '电压',
|
||
`ip_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'IP地址',
|
||
`is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
||
`network_access_mode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`user_id` bigint DEFAULT NULL COMMENT '绑定用户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '绑定组织ID',
|
||
`customer_id` bigint DEFAULT NULL COMMENT '绑定租户ID',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_sn_time` (`serial_number`,`timestamp`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970351530047246339 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_info_history_archive`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_info_history_archive`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_info_history_archive` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`serial_number` varchar(255) NOT NULL,
|
||
`timestamp` datetime NOT NULL,
|
||
`system_software_version` varchar(255) DEFAULT NULL,
|
||
`battery_level` int DEFAULT NULL,
|
||
`wearable_status` enum('WORN','NOT_WORN') DEFAULT NULL,
|
||
`charging_status` enum('NOT_CHARGING','CHARGING') DEFAULT NULL,
|
||
`voltage` int DEFAULT NULL,
|
||
`ip_address` varchar(255) DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`archive_date` date NOT NULL COMMENT '归档日期',
|
||
`archive_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '归档时间',
|
||
PRIMARY KEY (`id`,`archive_date`),
|
||
KEY `idx_archive_sn_date` (`serial_number`,`archive_date`),
|
||
KEY `idx_archive_date` (`archive_date`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=9977 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备历史数据归档表'
|
||
/*!50100 PARTITION BY RANGE (year(`archive_date`))
|
||
(PARTITION p2024 VALUES LESS THAN (2025) ENGINE = InnoDB,
|
||
PARTITION p2025 VALUES LESS THAN (2026) ENGINE = InnoDB,
|
||
PARTITION p2026 VALUES LESS THAN (2027) ENGINE = InnoDB,
|
||
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_message`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_message`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_message` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`message_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息唯一标识',
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`user_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户ID',
|
||
`device_sn` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备序列号',
|
||
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息标题',
|
||
`message` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息内容',
|
||
`message_type` enum('NOTIFICATION','ALERT','WARNING','INFO','EMERGENCY','JOB','TASK') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NOTIFICATION' COMMENT '消息类型',
|
||
`direction` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'DOWN' COMMENT '消息方向: UP-设备到平台, DOWN-平台到设备',
|
||
`sender_type` enum('SYSTEM','USER','DEVICE','API') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'SYSTEM' COMMENT '发送者类型',
|
||
`receiver_type` enum('USER','DEVICE','GROUP','BROADCAST') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USER' COMMENT '接收者类型',
|
||
`urgency` enum('LOW','MEDIUM','HIGH','CRITICAL') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MEDIUM' COMMENT '紧急程度',
|
||
`priority_level` int DEFAULT '3' COMMENT '优先级数值1-5',
|
||
`message_status` enum('DRAFT','PENDING','SENT','DELIVERED','ACKNOWLEDGED','FAILED','EXPIRED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PENDING' COMMENT '消息状态',
|
||
`channels` json DEFAULT NULL COMMENT '分发渠道JSON',
|
||
`require_ack` tinyint(1) DEFAULT '0' COMMENT '是否需要确认',
|
||
`metadata` json DEFAULT NULL COMMENT '扩展元数据JSON',
|
||
`sent_time` datetime DEFAULT NULL COMMENT '发送时间',
|
||
`received_time` datetime DEFAULT NULL COMMENT '接收时间',
|
||
`acknowledged_time` datetime DEFAULT NULL COMMENT '确认时间',
|
||
`expires_at` datetime DEFAULT NULL COMMENT '过期时间',
|
||
`responded_number` int DEFAULT '0' COMMENT '响应次数',
|
||
`target_count` int DEFAULT '0' COMMENT '目标用户总数',
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`create_user` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
|
||
`update_user` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
|
||
`version` int DEFAULT '1' COMMENT '乐观锁版本号',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `message_id` (`message_id`),
|
||
KEY `idx_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_message_type_status` (`message_type`,`message_status`),
|
||
KEY `idx_sent_time` (`sent_time`),
|
||
KEY `idx_urgency_status` (`urgency`,`message_status`),
|
||
KEY `idx_expires_at` (`expires_at`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_customer_user_time` (`customer_id`,`user_id`,`create_time` DESC,`is_deleted`),
|
||
KEY `idx_customer_user_status` (`customer_id`,`user_id`,`message_status`,`is_deleted`),
|
||
KEY `idx_customer_user_type` (`customer_id`,`user_id`,`message_type`,`is_deleted`),
|
||
KEY `idx_user_org_priority` (`user_id`,`org_id`,`priority_level`,`urgency`),
|
||
KEY `idx_user_status_time` (`user_id`,`message_status`,`create_time` DESC),
|
||
KEY `idx_direction_status` (`direction`,`message_status`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1967609413881368580 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备消息V2主表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_message_backup`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_message_backup`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_message_backup` (
|
||
`id` bigint NOT NULL DEFAULT '0',
|
||
`org_id` bigint DEFAULT NULL,
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`device_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`message` text NOT NULL,
|
||
`message_type` varchar(50) NOT NULL,
|
||
`sender_type` varchar(50) NOT NULL,
|
||
`receiver_type` varchar(50) NOT NULL,
|
||
`message_status` varchar(50) NOT NULL DEFAULT 'pending',
|
||
`sent_time` timestamp NULL DEFAULT NULL,
|
||
`received_time` timestamp NULL DEFAULT NULL,
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT NULL,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`responded_number` int DEFAULT '0',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自设备所属租户'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_message_detail`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_message_detail`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_message_detail` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`message_id` bigint NOT NULL COMMENT '消息ID，关联t_device_message_v2.id',
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`distribution_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '分发唯一ID',
|
||
`target_id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '目标ID（设备SN、用户ID等）',
|
||
`target_type` enum('DEVICE','USER','GROUP') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '目标类型',
|
||
`device_sn` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备序列号',
|
||
`user_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户ID - 主要查询字段',
|
||
`channel` enum('DEVICE','SMS','EMAIL','PUSH','WECHAT') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'DEVICE' COMMENT '分发渠道',
|
||
`delivery_status` enum('PENDING','SENT','DELIVERED','ACKNOWLEDGED','FAILED','EXPIRED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PENDING' COMMENT '分发状态',
|
||
`sent_time` datetime DEFAULT NULL COMMENT '发送时间',
|
||
`delivered_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`acknowledged_time` datetime DEFAULT NULL COMMENT '确认时间',
|
||
`response_duration` int DEFAULT NULL COMMENT '响应耗时(秒)',
|
||
`response_data` json DEFAULT NULL COMMENT '响应数据',
|
||
`delivery_details` json DEFAULT NULL COMMENT '分发详情JSON',
|
||
`client_info` json DEFAULT NULL COMMENT '客户端信息JSON',
|
||
`location_info` json DEFAULT NULL COMMENT '位置信息JSON',
|
||
`failure_reason` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '失败原因',
|
||
`retry_count` int DEFAULT '0' COMMENT '重试次数',
|
||
`last_retry_time` datetime DEFAULT NULL COMMENT '最后重试时间',
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_message_user_target` (`message_id`,`user_id`,`target_id`),
|
||
KEY `idx_message_customer` (`message_id`,`customer_id`),
|
||
KEY `idx_target` (`target_id`,`target_type`),
|
||
KEY `idx_channel_status` (`channel`,`delivery_status`),
|
||
KEY `idx_delivered_time` (`delivered_time`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_customer_user_status` (`customer_id`,`user_id`,`delivery_status`),
|
||
KEY `idx_user_channel_time` (`user_id`,`channel`,`create_time` DESC),
|
||
KEY `idx_user_delivery_status` (`user_id`,`delivery_status`,`acknowledged_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备消息V2分发详情表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_message_detail_backup`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_message_detail_backup`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_message_detail_backup` (
|
||
`id` bigint NOT NULL DEFAULT '0',
|
||
`message_id` varchar(255) NOT NULL,
|
||
`device_sn` varchar(255) NOT NULL,
|
||
`message` text NOT NULL,
|
||
`message_type` varchar(50) NOT NULL,
|
||
`sender_type` varchar(50) NOT NULL,
|
||
`receiver_type` varchar(50) NOT NULL,
|
||
`message_status` varchar(50) NOT NULL DEFAULT 'responded',
|
||
`sent_time` timestamp NULL DEFAULT NULL,
|
||
`received_time` timestamp NULL DEFAULT NULL,
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT NULL,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自消息所属租户',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID - 核心关联字段',
|
||
`org_id` bigint DEFAULT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_device_user`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_device_user`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_device_user` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`device_sn` varchar(200) NOT NULL COMMENT '设备ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`user_name` varchar(50) DEFAULT NULL,
|
||
`operate_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
|
||
`status` enum('BIND','UNBIND') DEFAULT 'BIND' COMMENT '绑定状态 (BIND:绑定, UNBIND:解绑)',
|
||
`is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除 (0:否,1:是)',
|
||
`create_user` varchar(255) DEFAULT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint DEFAULT NULL COMMENT '创建用户ID',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_user` varchar(255) DEFAULT NULL COMMENT '最后修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '最后修改用户ID',
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自用户的租户信息',
|
||
PRIMARY KEY (`id`),
|
||
KEY `fk_user_id` (`user_id`),
|
||
KEY `idx_device_user_device_sn` (`device_sn`) COMMENT '设备序列号索引，用于根据设备查找用户',
|
||
KEY `idx_device_user_customer_user` (`customer_id`,`user_id`) COMMENT '租户-用户复合索引，支持多租户用户查询',
|
||
KEY `idx_device_user_customer_device` (`customer_id`,`device_sn`) COMMENT '租户-设备复合索引，支持多租户设备查询'
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970347309587542019 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备与用户关联表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_event_alarm_queue`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_event_alarm_queue`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_event_alarm_queue` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`event_type` varchar(100) NOT NULL COMMENT '事件类型',
|
||
`device_sn` varchar(50) NOT NULL COMMENT '设备序列号',
|
||
`event_value` varchar(500) DEFAULT NULL COMMENT '事件值',
|
||
`event_data` json DEFAULT NULL COMMENT '完整事件数据(JSON格式)',
|
||
`processing_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '处理状态:pending/processing/completed/failed',
|
||
`retry_count` int NOT NULL DEFAULT '0' COMMENT '已重试次数',
|
||
`error_message` text COMMENT '错误信息',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`process_time` datetime DEFAULT NULL COMMENT '开始处理时间',
|
||
`complete_time` datetime DEFAULT NULL COMMENT '完成时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status_time` (`processing_status`,`create_time`),
|
||
KEY `idx_device_time` (`device_sn`,`create_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='事件告警处理队列';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_fence_points`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_fence_points`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_fence_points` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '路径点唯一标识',
|
||
`fence_id` int NOT NULL COMMENT '所属围栏ID',
|
||
`lng` double NOT NULL COMMENT '经度',
|
||
`lat` double NOT NULL COMMENT '纬度',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`),
|
||
KEY `fence_id` (`fence_id`),
|
||
CONSTRAINT `t_fence_points_ibfk_1` FOREIGN KEY (`fence_id`) REFERENCES `t_fences` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='围栏路径点表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_fences`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_fences`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_fences` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '围栏唯一标识',
|
||
`name` varchar(255) NOT NULL COMMENT '围栏名称',
|
||
`description` text COMMENT '围栏描述',
|
||
`status` enum('active','inactive') DEFAULT 'active' COMMENT '围栏状态',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='围栏信息表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_geofence`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_geofence`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_geofence` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(255) NOT NULL COMMENT '电子围栏名称',
|
||
`area` polygon NOT NULL COMMENT '围栏区域',
|
||
`description` text COMMENT '围栏描述',
|
||
`status` enum('active','inactive') DEFAULT 'active' COMMENT '围栏状态',
|
||
`create_user` varchar(40) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(40) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
`fence_type` enum('CIRCLE','RECTANGLE','POLYGON') DEFAULT 'CIRCLE' COMMENT '围栏类型',
|
||
`center_lng` decimal(11,8) DEFAULT NULL COMMENT '中心点经度',
|
||
`center_lat` decimal(11,8) DEFAULT NULL COMMENT '中心点纬度',
|
||
`radius` float DEFAULT NULL COMMENT '半径(米)-圆形围栏专用',
|
||
`alert_on_enter` tinyint(1) DEFAULT '1' COMMENT '进入围栏时是否告警',
|
||
`alert_on_exit` tinyint(1) DEFAULT '1' COMMENT '离开围栏时是否告警',
|
||
`alert_level` enum('LOW','MEDIUM','HIGH') DEFAULT 'MEDIUM' COMMENT '告警级别',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID',
|
||
`is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_geofence_alert`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_geofence_alert`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_geofence_alert` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`alert_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '告警ID(UUID)',
|
||
`fence_id` bigint NOT NULL COMMENT '围栏ID',
|
||
`fence_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '围栏名称',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`device_sn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备序列号',
|
||
`event_type` enum('ENTER','EXIT','STAY_TIMEOUT') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件类型',
|
||
`event_time` datetime NOT NULL COMMENT '事件发生时间',
|
||
`location_lng` decimal(11,8) NOT NULL COMMENT '事件位置经度',
|
||
`location_lat` decimal(11,8) NOT NULL COMMENT '事件位置纬度',
|
||
`alert_level` enum('LOW','MEDIUM','HIGH') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '告警级别',
|
||
`alert_status` enum('PENDING','PROCESSING','PROCESSED','IGNORED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING' COMMENT '告警状态',
|
||
`process_time` datetime DEFAULT NULL COMMENT '处理时间',
|
||
`processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '处理人',
|
||
`process_note` text COLLATE utf8mb4_unicode_ci COMMENT '处理备注',
|
||
`notification_status` json DEFAULT NULL COMMENT '通知状态',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_alert_id` (`alert_id`),
|
||
KEY `idx_alert_fence_user` (`fence_id`,`user_id`),
|
||
KEY `idx_alert_event_time` (`event_time`),
|
||
KEY `idx_alert_status_level` (`alert_status`,`alert_level`),
|
||
KEY `idx_alert_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_alert_device_time` (`device_sn`,`event_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='围栏告警记录表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_geofence_bind`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_geofence_bind`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_geofence_bind` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`fence_id` bigint NOT NULL COMMENT '围栏ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`bind_type` enum('MONITOR','EXCLUDE') COLLATE utf8mb4_unicode_ci DEFAULT 'MONITOR' COMMENT '绑定类型 MONITOR-监控 EXCLUDE-排除',
|
||
`bind_status` tinyint(1) DEFAULT '1' COMMENT '绑定状态 1-启用 0-停用',
|
||
`priority` int DEFAULT '0' COMMENT '优先级',
|
||
`effective_time` time DEFAULT NULL COMMENT '生效时间',
|
||
`expiry_time` time DEFAULT NULL COMMENT '失效时间',
|
||
`effective_days` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '生效星期 1,2,3,4,5,6,7',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`created_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_fence_user_bind` (`fence_id`,`user_id`),
|
||
KEY `idx_bind_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_bind_user_status` (`user_id`,`bind_status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='围栏用户绑定表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_anomaly`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_anomaly`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_anomaly` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`device_sn` varchar(50) DEFAULT NULL,
|
||
`timestamp` datetime DEFAULT NULL,
|
||
`feature_name` varchar(50) DEFAULT NULL,
|
||
`value` float DEFAULT NULL,
|
||
`anomaly_type` varchar(10) DEFAULT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_baseline`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_baseline`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_baseline` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备序列号',
|
||
`user_id` bigint DEFAULT '0' COMMENT '用户ID',
|
||
`org_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '1' COMMENT '组织ID',
|
||
`feature_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '特征名称',
|
||
`baseline_date` date NOT NULL COMMENT '基线日期',
|
||
`mean_value` decimal(10,2) DEFAULT '0.00' COMMENT '平均值',
|
||
`std_value` decimal(10,2) DEFAULT '0.00' COMMENT '标准差',
|
||
`min_value` decimal(10,2) DEFAULT '0.00' COMMENT '最小值',
|
||
`max_value` decimal(10,2) DEFAULT '0.00' COMMENT '最大值',
|
||
`sample_count` int DEFAULT '0' COMMENT '样本数量',
|
||
`is_current` tinyint DEFAULT '1' COMMENT '是否当前有效',
|
||
`baseline_time` date DEFAULT NULL COMMENT '基线生成时间',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自用户所属租户',
|
||
`baseline_type` varchar(20) DEFAULT 'personal' COMMENT '基线类型：personal|population|position',
|
||
`age_group` varchar(20) DEFAULT NULL COMMENT '年龄组',
|
||
`gender` varchar(10) DEFAULT NULL COMMENT '性别',
|
||
`position_risk_level` varchar(20) DEFAULT NULL COMMENT '职位风险等级',
|
||
`seasonal_factor` decimal(5,4) DEFAULT '1.0000' COMMENT '季节调整因子',
|
||
`confidence_level` decimal(5,4) DEFAULT '0.9500' COMMENT '置信水平',
|
||
`is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除 0-正常 1-删除',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_baseline_device_feature_date` (`device_sn`,`feature_name`,`baseline_date`),
|
||
KEY `idx_baseline_org_feature_date` (`org_id`,`feature_name`,`baseline_date`),
|
||
KEY `idx_baseline_date_feature` (`baseline_date`,`feature_name`),
|
||
KEY `idx_baseline_type_date` (`baseline_type`,`baseline_date`,`is_current`),
|
||
KEY `idx_baseline_user_feature` (`user_id`,`feature_name`,`baseline_date`),
|
||
KEY `idx_health_baseline_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1967287239115124739 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康基线表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_data_config`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_data_config`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_data_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`customer_id` bigint NOT NULL,
|
||
`data_type` varchar(50) NOT NULL,
|
||
`frequency_interval` int DEFAULT NULL,
|
||
`is_realtime` tinyint(1) DEFAULT '1',
|
||
`is_enabled` tinyint(1) DEFAULT '1',
|
||
`is_default` tinyint(1) DEFAULT '0',
|
||
`warning_high` decimal(5,1) DEFAULT NULL,
|
||
`warning_low` decimal(5,1) DEFAULT NULL,
|
||
`warning_cnt` int DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`weight` decimal(5,4) DEFAULT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1969963033763364867 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_data_slow_daily`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_data_slow_daily`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_data_slow_daily` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`customer_id` bigint DEFAULT NULL,
|
||
`date` date NOT NULL,
|
||
`sleep_data` json DEFAULT NULL COMMENT '睡眠数据(每日更新)',
|
||
`exercise_daily_data` json DEFAULT NULL COMMENT '每日运动数据',
|
||
`workout_data` json DEFAULT NULL COMMENT '锻炼数据',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`scientific_sleep_data` json DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_device_date` (`device_sn`,`date`),
|
||
KEY `idx_date` (`date`),
|
||
KEY `idx_device_sn` (`device_sn`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1259 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='每日更新健康数据表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_data_slow_weekly`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_data_slow_weekly`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_data_slow_weekly` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自用户所属租户',
|
||
`week_start` date NOT NULL COMMENT '周开始日期(周一)',
|
||
`exercise_week_data` json DEFAULT NULL COMMENT '每周运动数据',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_device_week` (`device_sn`,`week_start`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_week_start` (`week_start`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1092 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='每周更新健康数据表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_prediction`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_prediction`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_prediction` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL,
|
||
`customer_id` bigint NOT NULL,
|
||
`device_sn` varchar(50) DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`prediction_type` varchar(50) DEFAULT NULL COMMENT '预测类型 (trend, risk, anomaly)',
|
||
`feature_name` varchar(50) DEFAULT NULL COMMENT '健康特征名称',
|
||
`prediction_date` date DEFAULT NULL COMMENT '预测日期',
|
||
`predicted_value` decimal(10,4) DEFAULT NULL COMMENT '预测值',
|
||
`confidence_score` decimal(5,4) DEFAULT NULL COMMENT '置信度 (0-1)',
|
||
`risk_level` varchar(20) DEFAULT NULL COMMENT '风险等级 (low, medium, high)',
|
||
`model_version` varchar(50) DEFAULT NULL COMMENT '预测模型版本',
|
||
`accuracy_rate` decimal(5,4) DEFAULT NULL COMMENT '预测准确率',
|
||
`description` text COMMENT '预测描述',
|
||
`metadata` json DEFAULT NULL COMMENT '预测元数据',
|
||
`is_valid` tinyint(1) DEFAULT '1' COMMENT '是否有效',
|
||
`valid_until` datetime DEFAULT NULL COMMENT '预测有效期至',
|
||
`model_type` varchar(50) DEFAULT NULL COMMENT '模型类型',
|
||
`prediction_start_date` date DEFAULT NULL COMMENT '预测开始日期',
|
||
`prediction_end_date` date DEFAULT NULL COMMENT '预测结束日期',
|
||
`prediction_horizon_days` int DEFAULT NULL COMMENT '预测周期天数',
|
||
`prediction_status` varchar(20) DEFAULT 'pending' COMMENT '预测状态',
|
||
`created_by` varchar(50) DEFAULT NULL COMMENT '创建人',
|
||
`prediction_details` json DEFAULT NULL COMMENT '预测详情',
|
||
`predicted_values` json DEFAULT NULL COMMENT '预测值',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_customer_id` (`customer_id`),
|
||
KEY `idx_org_id` (`org_id`),
|
||
KEY `idx_prediction_date` (`prediction_date`),
|
||
KEY `idx_feature_name` (`feature_name`),
|
||
KEY `idx_prediction_type` (`prediction_type`),
|
||
KEY `idx_health_prediction_user` (`user_id`,`customer_id`),
|
||
KEY `idx_health_prediction_date` (`prediction_date`),
|
||
KEY `idx_health_prediction_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康预测表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_profile`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_profile`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_profile` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL,
|
||
`customer_id` bigint NOT NULL,
|
||
`profile_date` date NOT NULL,
|
||
`overall_health_score` decimal(5,2) DEFAULT '0.00',
|
||
`health_level` varchar(20) DEFAULT 'fair',
|
||
`physiological_score` decimal(5,2) DEFAULT '0.00',
|
||
`behavioral_score` decimal(5,2) DEFAULT '0.00',
|
||
`risk_factor_score` decimal(5,2) DEFAULT '0.00',
|
||
`cardiovascular_score` decimal(5,2) DEFAULT '0.00',
|
||
`respiratory_score` decimal(5,2) DEFAULT '0.00',
|
||
`metabolic_score` decimal(5,2) DEFAULT '0.00',
|
||
`psychological_score` decimal(5,2) DEFAULT '0.00',
|
||
`activity_consistency_score` decimal(5,2) DEFAULT '0.00',
|
||
`sleep_quality_score` decimal(5,2) DEFAULT '0.00',
|
||
`health_engagement_score` decimal(5,2) DEFAULT '0.00',
|
||
`current_risk_level` varchar(20) DEFAULT 'medium',
|
||
`predicted_risk_score` decimal(5,2) DEFAULT '0.00',
|
||
`detailed_analysis` json DEFAULT NULL COMMENT '详细分析数据',
|
||
`trend_analysis` json DEFAULT NULL COMMENT '趋势分析数据',
|
||
`recommendations` json DEFAULT NULL COMMENT '个性化建议',
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`version` int DEFAULT '1',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_user_profile_date` (`user_id`,`profile_date`,`is_deleted`),
|
||
KEY `idx_customer_date` (`customer_id`,`profile_date`),
|
||
KEY `idx_health_level` (`health_level`,`profile_date`),
|
||
KEY `idx_risk_level` (`current_risk_level`,`profile_date`),
|
||
KEY `idx_health_profile_user` (`user_id`,`customer_id`),
|
||
KEY `idx_health_profile_date` (`profile_date`),
|
||
KEY `idx_health_profile_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户健康画像主表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_recommendation`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_recommendation`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_recommendation` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '客户ID',
|
||
`device_sn` varchar(50) DEFAULT NULL COMMENT '设备序列号',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`recommendation_type` varchar(50) NOT NULL COMMENT '建议类型',
|
||
`feature_name` varchar(50) DEFAULT NULL COMMENT '相关健康特征',
|
||
`title` varchar(200) NOT NULL COMMENT '建议标题',
|
||
`content` text NOT NULL COMMENT '建议内容',
|
||
`priority` varchar(20) DEFAULT 'medium' COMMENT '优先级 high/medium/low',
|
||
`category` varchar(50) DEFAULT NULL COMMENT '建议分类',
|
||
`target_value` decimal(10,2) DEFAULT NULL COMMENT '目标值',
|
||
`current_value` decimal(10,2) DEFAULT NULL COMMENT '当前值',
|
||
`improvement_range` varchar(100) DEFAULT NULL COMMENT '改善范围',
|
||
`status` varchar(20) DEFAULT 'active' COMMENT '状态 active/completed/ignored',
|
||
`effective_date` date DEFAULT NULL COMMENT '生效日期',
|
||
`expire_date` date DEFAULT NULL COMMENT '过期日期',
|
||
`is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读',
|
||
`feedback_score` int DEFAULT NULL COMMENT '反馈评分 1-5',
|
||
`feedback_comment` text COMMENT '反馈评论',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_health_recommendation_user` (`user_id`,`customer_id`),
|
||
KEY `idx_health_recommendation_status` (`status`,`is_deleted`),
|
||
KEY `idx_health_recommendation_date` (`effective_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康建议表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_recommendation_track`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_recommendation_track`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_recommendation_track` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL,
|
||
`customer_id` bigint NOT NULL,
|
||
`recommendation_id` varchar(64) NOT NULL,
|
||
`recommendation_type` varchar(50) NOT NULL,
|
||
`title` varchar(200) NOT NULL,
|
||
`description` text,
|
||
`recommended_actions` json DEFAULT NULL,
|
||
`status` varchar(20) DEFAULT 'pending',
|
||
`start_date` date DEFAULT NULL,
|
||
`target_completion_date` date DEFAULT NULL,
|
||
`actual_completion_date` date DEFAULT NULL,
|
||
`effectiveness_score` decimal(5,2) DEFAULT NULL,
|
||
`user_feedback` text,
|
||
`health_improvement_metrics` json DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_status` (`user_id`,`status`,`is_deleted`),
|
||
KEY `idx_customer_type` (`customer_id`,`recommendation_type`),
|
||
KEY `idx_completion_date` (`target_completion_date`,`status`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康建议执行跟踪表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_score`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_score`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_score` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备序列号',
|
||
`user_id` bigint DEFAULT '0' COMMENT '用户ID',
|
||
`org_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '1' COMMENT '组织ID',
|
||
`feature_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '特征名称',
|
||
`avg_value` decimal(10,2) DEFAULT '0.00' COMMENT '当日平均值',
|
||
`z_score` decimal(10,4) DEFAULT '0.0000' COMMENT 'Z分数',
|
||
`score_value` decimal(5,2) DEFAULT '0.00' COMMENT '评分值(0-100)',
|
||
`penalty_value` decimal(5,2) DEFAULT '0.00' COMMENT '惩罚分值',
|
||
`baseline_time` date DEFAULT NULL COMMENT '基线时间',
|
||
`score_date` date NOT NULL COMMENT '评分日期',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，继承自用户所属租户',
|
||
`is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除 0-正常 1-删除',
|
||
`score_level` varchar(20) DEFAULT 'fair' COMMENT '评分等级 excellent/good/fair/poor',
|
||
`raw_value` decimal(10,2) DEFAULT '0.00' COMMENT '原始值',
|
||
`baseline_value` decimal(10,2) DEFAULT '0.00' COMMENT '基线值',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_score_device_feature_date` (`device_sn`,`feature_name`,`score_date`),
|
||
KEY `idx_score_org_feature_date` (`org_id`,`feature_name`,`score_date`),
|
||
KEY `idx_score_date_feature` (`score_date`,`feature_name`),
|
||
KEY `idx_health_score_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1967317438665961475 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康评分表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_summary_daily`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_summary_daily`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_summary_daily` (
|
||
`summary_id` bigint NOT NULL AUTO_INCREMENT COMMENT '汇总记录主键',
|
||
`device_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备序列号',
|
||
`summary_date` date NOT NULL COMMENT '汇总日期',
|
||
`health_score` decimal(5,2) NOT NULL COMMENT '综合健康得分（0–100）',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`heart_rate_score` decimal(5,2) DEFAULT NULL COMMENT '心率得分',
|
||
`blood_oxygen_score` decimal(5,2) DEFAULT NULL COMMENT '血氧得分',
|
||
`temperature_score` decimal(5,2) DEFAULT NULL COMMENT '体温得分',
|
||
`blood_pressure_score` decimal(5,2) DEFAULT NULL COMMENT '血压得分',
|
||
`stress_score` decimal(5,2) DEFAULT NULL COMMENT '压力得分',
|
||
`sleep_data_score` decimal(5,2) DEFAULT NULL COMMENT '睡眠得分',
|
||
`step_score` decimal(5,2) DEFAULT NULL COMMENT '步数得分',
|
||
`distance_score` decimal(5,2) DEFAULT NULL COMMENT '距离得分',
|
||
`calorie_score` decimal(5,2) DEFAULT NULL COMMENT '卡路里得分',
|
||
PRIMARY KEY (`summary_id`),
|
||
UNIQUE KEY `uk_device_date` (`device_sn`,`summary_date`),
|
||
KEY `idx_summary_date` (`summary_date`),
|
||
KEY `idx_device_sn` (`device_sn`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='每日设备健康汇总表：按设备+日期存储综合健康分';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_task_log`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_task_log`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_task_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`task_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务名称',
|
||
`task_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务类型',
|
||
`start_time` datetime NOT NULL COMMENT '开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
||
`status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '执行状态',
|
||
`processed_count` int DEFAULT '0' COMMENT '处理记录数',
|
||
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '错误信息',
|
||
`feature_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '特征名称',
|
||
`target_date` date DEFAULT NULL COMMENT '目标日期',
|
||
`execution_time_ms` bigint DEFAULT '0' COMMENT '执行时间(毫秒)',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_task_log_name_time` (`task_name`,`start_time`),
|
||
KEY `idx_task_log_status_time` (`status`,`start_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康任务日志表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_health_weight_cache`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_health_weight_cache`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_health_weight_cache` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL,
|
||
`customer_id` bigint NOT NULL,
|
||
`metric_name` varchar(50) NOT NULL COMMENT '指标名称',
|
||
`base_weight` decimal(8,6) DEFAULT '0.150000' COMMENT '基础权重',
|
||
`position_risk_multiplier` decimal(8,6) DEFAULT '1.000000' COMMENT '岗位风险倍数',
|
||
`combined_weight` decimal(8,6) DEFAULT '0.150000' COMMENT '综合权重',
|
||
`normalized_weight` decimal(8,6) DEFAULT '0.150000' COMMENT '归一化权重',
|
||
`position_id` bigint DEFAULT '0' COMMENT '岗位ID',
|
||
`position_risk_level` varchar(20) DEFAULT 'normal' COMMENT '岗位风险等级',
|
||
`cache_date` date NOT NULL COMMENT '缓存日期',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_metric_date` (`user_id`,`metric_name`,`cache_date`),
|
||
KEY `idx_user_customer_date` (`user_id`,`customer_id`,`cache_date`),
|
||
KEY `idx_customer_date` (`customer_id`,`cache_date`),
|
||
KEY `idx_cache_date` (`cache_date`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=21621 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户健康指标权重缓存表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_interface`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_interface`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_interface` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(100) NOT NULL,
|
||
`url` varchar(200) NOT NULL,
|
||
`call_interval` int NOT NULL,
|
||
`method` enum('upload','fetch') NOT NULL,
|
||
`description` varchar(500) DEFAULT NULL,
|
||
`is_enabled` tinyint(1) DEFAULT NULL,
|
||
`customer_id` bigint DEFAULT NULL,
|
||
`api_id` varchar(50) DEFAULT NULL,
|
||
`api_auth` varchar(200) DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT '0',
|
||
`create_user` varchar(255) DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1969963033692061699 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_org_health_baseline`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_org_health_baseline`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_org_health_baseline` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`org_id` bigint NOT NULL COMMENT '组织ID',
|
||
`feature_name` varchar(50) NOT NULL COMMENT '体征名称',
|
||
`baseline_date` date NOT NULL COMMENT '基线日期',
|
||
`mean_value` float DEFAULT NULL COMMENT '组织平均值',
|
||
`std_value` float DEFAULT NULL COMMENT '组织标准差',
|
||
`min_value` float DEFAULT NULL COMMENT '组织最小值',
|
||
`max_value` float DEFAULT NULL COMMENT '组织最大值',
|
||
`user_count` int DEFAULT NULL COMMENT '参与用户数',
|
||
`sample_count` int DEFAULT NULL COMMENT '总样本数',
|
||
`create_time` datetime NOT NULL DEFAULT (now()),
|
||
`update_time` datetime NOT NULL DEFAULT (now()),
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_org_baseline_feature` (`org_id`,`feature_name`,`baseline_date`),
|
||
KEY `idx_org_baseline_date` (`org_id`,`baseline_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_performance_baseline`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_performance_baseline`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_performance_baseline` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`optimization_phase` varchar(50) NOT NULL,
|
||
`query_type` varchar(50) NOT NULL,
|
||
`query_description` varchar(200) NOT NULL,
|
||
`execution_time_before_ms` bigint DEFAULT NULL,
|
||
`execution_time_after_ms` bigint DEFAULT NULL,
|
||
`improvement_factor` decimal(10,2) DEFAULT NULL,
|
||
`test_time` datetime NOT NULL,
|
||
`notes` text,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='性能优化基线记录表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_scheduled_tasks`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_scheduled_tasks`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_scheduled_tasks` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`task_name` varchar(100) NOT NULL,
|
||
`task_type` enum('ARCHIVE','CLEANUP','STATS') NOT NULL,
|
||
`schedule_expression` varchar(50) NOT NULL COMMENT 'Cron表达式',
|
||
`last_execution` timestamp NULL DEFAULT NULL,
|
||
`next_execution` timestamp NULL DEFAULT NULL,
|
||
`is_active` tinyint(1) DEFAULT '1',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_task_name` (`task_name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_sys_operation_log`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_sys_operation_log`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_sys_operation_log` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
||
`business_type` tinyint NOT NULL COMMENT '类型（0=其它,1=新增,2=修改,3=删除,4=授权,5=导出,6=导入,7=强退,8=生成代码,9=清空数据）',
|
||
`method` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '方法名称',
|
||
`request_method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '请求方式',
|
||
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT '描述',
|
||
`req_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '请求IP',
|
||
`req_param` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT '请求信息',
|
||
`resp` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT '响应信息',
|
||
`error_msg` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '错误消息',
|
||
`create_id` bigint DEFAULT NULL COMMENT '创建者ID',
|
||
`create_by` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT '创建者名称',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_id` bigint DEFAULT NULL COMMENT '修改者ID',
|
||
`update_by` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL COMMENT '修改者名称',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`is_deleted` tinyint DEFAULT NULL COMMENT '是否已删除：0->未删除；1->已删除',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
|
||
`oper_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '操作人',
|
||
`oper_location` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '操作地点',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 1-可用，2-禁用',
|
||
PRIMARY KEY (`id`),
|
||
KEY `t_sys_operation_log_id_idx` (`id`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=399 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='系统操作日志';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_system_event_process_log`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_system_event_process_log`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_system_event_process_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`event_id` bigint DEFAULT NULL COMMENT '事件队列ID',
|
||
`alert_id` bigint DEFAULT NULL COMMENT '告警ID',
|
||
`device_sn` varchar(50) NOT NULL COMMENT '设备序列号',
|
||
`event_type` varchar(100) NOT NULL COMMENT '事件类型',
|
||
`rule_id` bigint DEFAULT NULL COMMENT '规则ID',
|
||
`process_status` varchar(20) NOT NULL COMMENT '处理状态:processing/completed/failed',
|
||
`notification_type` varchar(20) DEFAULT NULL COMMENT '通知类型:wechat/message/both',
|
||
`message_count` int DEFAULT NULL COMMENT '消息推送数量',
|
||
`wechat_status` varchar(20) DEFAULT NULL COMMENT '微信推送状态:success/failed/skipped',
|
||
`process_duration` int DEFAULT NULL COMMENT '处理耗时(毫秒)',
|
||
`error_message` text COMMENT '错误信息',
|
||
`process_details` json DEFAULT NULL COMMENT '处理详情',
|
||
`create_time` datetime NOT NULL,
|
||
`complete_time` datetime DEFAULT NULL COMMENT '完成时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_device_event_time` (`device_sn`,`event_type`,`create_time`),
|
||
KEY `idx_process_status_time` (`process_status`,`create_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=461 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统事件告警处理日志表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_system_event_rule`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_system_event_rule`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_system_event_rule` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`event_type` varchar(100) NOT NULL COMMENT '完整事件类型(如com.tdtech.ohos.health.action.SOS_EVENT)',
|
||
`rule_type` varchar(50) NOT NULL COMMENT '规则类型(简化,如SOS_EVENT)',
|
||
`severity_level` varchar(20) NOT NULL DEFAULT 'medium' COMMENT '告警级别:critical/high/medium/low',
|
||
`alert_message` varchar(500) NOT NULL COMMENT '告警消息模板',
|
||
`is_emergency` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否紧急事件(触发微信推送)',
|
||
`notification_type` varchar(20) NOT NULL DEFAULT 'message' COMMENT '通知类型:wechat/message/both',
|
||
`retry_count` int NOT NULL DEFAULT '3' COMMENT '失败重试次数',
|
||
`is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用规则',
|
||
`tenant_id` bigint NOT NULL DEFAULT '1' COMMENT '租户ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_rule_tenant` (`rule_type`,`tenant_id`),
|
||
KEY `idx_event_type` (`event_type`),
|
||
KEY `idx_is_emergency` (`is_emergency`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统事件规则配置表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_system_event_rules`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_system_event_rules`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_system_event_rules` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`rule_name` varchar(100) NOT NULL COMMENT '规则名称',
|
||
`event_type` varchar(50) NOT NULL COMMENT '事件类型',
|
||
`trigger_condition` text COMMENT '触发条件',
|
||
`alert_message` text NOT NULL COMMENT '告警消息',
|
||
`notification_type` varchar(50) DEFAULT NULL COMMENT '通知类型',
|
||
`severity_level` varchar(50) DEFAULT NULL COMMENT '严重程度',
|
||
`is_enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
|
||
`is_deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除',
|
||
`create_user` varchar(255) DEFAULT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint DEFAULT NULL COMMENT '创建用户ID',
|
||
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_user` varchar(255) DEFAULT NULL COMMENT '更新用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '更新用户ID',
|
||
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_system_logs`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_system_logs`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_system_logs` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`log_level` varchar(20) NOT NULL COMMENT '日志级别',
|
||
`log_message` text NOT NULL COMMENT '日志消息',
|
||
`log_source` varchar(100) DEFAULT NULL COMMENT '日志来源',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`device_sn` varchar(50) DEFAULT NULL COMMENT '设备序列号',
|
||
`ip_address` varchar(50) DEFAULT NULL COMMENT 'IP地址',
|
||
`user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理',
|
||
`request_data` json DEFAULT NULL COMMENT '请求数据',
|
||
`response_data` json DEFAULT NULL COMMENT '响应数据',
|
||
`execution_time` float DEFAULT NULL COMMENT '执行时间(毫秒)',
|
||
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_alerts`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_alerts`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_alerts` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`userName` varchar(64) NOT NULL,
|
||
`phoneNumber` varchar(20) NOT NULL,
|
||
`alertType` varchar(64) NOT NULL,
|
||
`latitude` float NOT NULL,
|
||
`longitude` float NOT NULL,
|
||
`timestamp` datetime NOT NULL,
|
||
`deviceSn` varchar(255) NOT NULL,
|
||
`severityLevel` varchar(255) NOT NULL,
|
||
`alertStatus` varchar(255) NOT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_health_data`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_data`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_health_data` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`heart_rate` int DEFAULT NULL,
|
||
`pressure_high` int DEFAULT NULL,
|
||
`pressure_low` int DEFAULT NULL,
|
||
`blood_oxygen` int DEFAULT NULL,
|
||
`stress` int DEFAULT NULL,
|
||
`temperature` double(5,2) DEFAULT NULL,
|
||
`step` int DEFAULT NULL,
|
||
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'heguang',
|
||
`latitude` decimal(10,6) DEFAULT NULL,
|
||
`longitude` decimal(10,6) DEFAULT NULL,
|
||
`altitude` double DEFAULT NULL,
|
||
`device_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`distance` double DEFAULT NULL,
|
||
`speed` double DEFAULT NULL COMMENT '速度(km/h)',
|
||
`bearing` double DEFAULT NULL COMMENT '方向角(度，0-360)',
|
||
`accuracy` double DEFAULT NULL COMMENT '定位精度(米)',
|
||
`location_type` tinyint DEFAULT NULL COMMENT '定位类型 1-GPS 2-网络 3-被动',
|
||
`geom` geometry DEFAULT NULL COMMENT '空间几何对象',
|
||
`calorie` double DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`upload_method` enum('wifi','bluetooth','common_event','esim') NOT NULL DEFAULT 'wifi',
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`sleep` double DEFAULT NULL,
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局数据，其他值表示特定租户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_timestamp` (`timestamp`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_health_customer_device_time` (`customer_id`,`device_sn`,`timestamp`),
|
||
KEY `idx_health_customer_user_time` (`customer_id`,`user_id`,`timestamp`),
|
||
KEY `idx_health_customer_status` (`customer_id`,`is_deleted`),
|
||
KEY `idx_health_cover_basic` (`customer_id`,`user_id`,`timestamp`,`heart_rate`,`pressure_high`,`pressure_low`,`blood_oxygen`),
|
||
KEY `idx_user_name` (`user_name`),
|
||
KEY `idx_device_time_optimized` (`device_sn`,`timestamp` DESC),
|
||
KEY `idx_user_id_time` (`user_id`,`timestamp` DESC),
|
||
KEY `idx_create_time` (`create_time` DESC),
|
||
KEY `idx_batch_insert_optimized` (`customer_id`,`create_time` DESC,`device_sn`),
|
||
KEY `idx_health_time_range_query` (`customer_id`,`timestamp` DESC,`is_deleted`,`user_id`,`device_sn`),
|
||
KEY `idx_health_global_time_query` (`timestamp` DESC,`is_deleted`,`customer_id`,`user_id`),
|
||
KEY `idx_health_data_user_time_location` (`user_name`,`timestamp`,`longitude`,`latitude`),
|
||
KEY `idx_health_data_device_time_location` (`device_sn`,`timestamp`,`longitude`,`latitude`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_health_data_202506`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_data_202506`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_health_data_202506` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`heart_rate` int DEFAULT NULL,
|
||
`pressure_high` int DEFAULT NULL,
|
||
`pressure_low` int DEFAULT NULL,
|
||
`blood_oxygen` int DEFAULT NULL,
|
||
`stress` int DEFAULT NULL,
|
||
`temperature` double(5,2) DEFAULT NULL,
|
||
`step` int DEFAULT NULL,
|
||
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'heguang',
|
||
`latitude` decimal(10,6) DEFAULT NULL,
|
||
`longitude` decimal(10,6) DEFAULT NULL,
|
||
`altitude` double DEFAULT NULL,
|
||
`device_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`distance` double DEFAULT NULL,
|
||
`calorie` double DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`upload_method` enum('wifi','bluetooth') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'wifi' COMMENT '数据上传方式（wifi 或 bluetooth）',
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`sleep` double DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_timestamp` (`timestamp`),
|
||
KEY `idx_device_sn` (`device_sn`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_health_data_202508`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_data_202508`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_health_data_202508` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`heart_rate` int DEFAULT NULL,
|
||
`pressure_high` int DEFAULT NULL,
|
||
`pressure_low` int DEFAULT NULL,
|
||
`blood_oxygen` int DEFAULT NULL,
|
||
`stress` int DEFAULT NULL,
|
||
`temperature` double(5,2) DEFAULT NULL,
|
||
`step` int DEFAULT NULL,
|
||
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'heguang',
|
||
`latitude` decimal(10,6) DEFAULT NULL,
|
||
`longitude` decimal(10,6) DEFAULT NULL,
|
||
`altitude` double DEFAULT NULL,
|
||
`device_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`distance` double DEFAULT NULL,
|
||
`calorie` double DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`upload_method` enum('wifi','bluetooth','common_event') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'wifi',
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`sleep` double DEFAULT NULL,
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局数据，其他值表示特定租户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_timestamp` (`timestamp`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_health_customer_device_time` (`customer_id`,`device_sn`,`timestamp`),
|
||
KEY `idx_health_customer_user_time` (`customer_id`,`user_id`,`timestamp`),
|
||
KEY `idx_health_customer_status` (`customer_id`,`is_deleted`),
|
||
KEY `idx_health_cover_basic` (`customer_id`,`user_id`,`timestamp`,`heart_rate`,`pressure_high`,`pressure_low`,`blood_oxygen`),
|
||
KEY `idx_202508_device_time` (`device_sn`,`timestamp`),
|
||
KEY `idx_202508_org_time` (`org_id`,`timestamp`),
|
||
KEY `idx_202508_create_time` (`create_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1008970 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康数据归档表_202508';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_health_data_202510`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_data_202510`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_health_data_202510` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`heart_rate` int DEFAULT NULL,
|
||
`pressure_high` int DEFAULT NULL,
|
||
`pressure_low` int DEFAULT NULL,
|
||
`blood_oxygen` int DEFAULT NULL,
|
||
`stress` int DEFAULT NULL,
|
||
`temperature` double(5,2) DEFAULT NULL,
|
||
`step` int DEFAULT NULL,
|
||
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'heguang',
|
||
`latitude` decimal(10,6) DEFAULT NULL,
|
||
`longitude` decimal(10,6) DEFAULT NULL,
|
||
`altitude` double DEFAULT NULL,
|
||
`device_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
||
`distance` double DEFAULT NULL,
|
||
`speed` double DEFAULT NULL COMMENT '速度(km/h)',
|
||
`bearing` double DEFAULT NULL COMMENT '方向角(度，0-360)',
|
||
`accuracy` double DEFAULT NULL COMMENT '定位精度(米)',
|
||
`location_type` tinyint DEFAULT NULL COMMENT '定位类型 1-GPS 2-网络 3-被动',
|
||
`geom` geometry DEFAULT NULL COMMENT '空间几何对象',
|
||
`calorie` double DEFAULT NULL,
|
||
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
|
||
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`create_user_id` bigint DEFAULT NULL,
|
||
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
|
||
`update_user_id` bigint DEFAULT NULL,
|
||
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`upload_method` enum('wifi','bluetooth','common_event','esim') NOT NULL DEFAULT 'wifi',
|
||
`user_id` bigint DEFAULT NULL,
|
||
`org_id` bigint DEFAULT NULL,
|
||
`sleep` double DEFAULT NULL,
|
||
`customer_id` bigint NOT NULL DEFAULT '0' COMMENT '租户ID，0表示全局数据，其他值表示特定租户',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_timestamp` (`timestamp`),
|
||
KEY `idx_device_sn` (`device_sn`),
|
||
KEY `idx_health_customer_device_time` (`customer_id`,`device_sn`,`timestamp`),
|
||
KEY `idx_health_customer_user_time` (`customer_id`,`user_id`,`timestamp`),
|
||
KEY `idx_health_customer_status` (`customer_id`,`is_deleted`),
|
||
KEY `idx_health_cover_basic` (`customer_id`,`user_id`,`timestamp`,`heart_rate`,`pressure_high`,`pressure_low`,`blood_oxygen`),
|
||
KEY `idx_user_name` (`user_name`),
|
||
KEY `idx_device_time_optimized` (`device_sn`,`timestamp` DESC),
|
||
KEY `idx_user_id_time` (`user_id`,`timestamp` DESC),
|
||
KEY `idx_create_time` (`create_time` DESC),
|
||
KEY `idx_batch_insert_optimized` (`customer_id`,`create_time` DESC,`device_sn`),
|
||
KEY `idx_health_time_range_query` (`customer_id`,`timestamp` DESC,`is_deleted`,`user_id`,`device_sn`),
|
||
KEY `idx_health_global_time_query` (`timestamp` DESC,`is_deleted`,`customer_id`,`user_id`),
|
||
KEY `idx_health_data_user_time_location` (`user_name`,`timestamp`,`longitude`,`latitude`),
|
||
KEY `idx_health_data_device_time_location` (`device_sn`,`timestamp`,`longitude`,`latitude`),
|
||
KEY `idx_202510_device_time` (`device_sn`,`timestamp`),
|
||
KEY `idx_202510_org_time` (`org_id`,`timestamp`),
|
||
KEY `idx_202510_create_time` (`create_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1970507480720941315 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康数据归档表_202510';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Temporary view structure for view `t_user_health_data_partitioned`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_data_partitioned`;
|
||
/*!50001 DROP VIEW IF EXISTS `t_user_health_data_partitioned`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `t_user_health_data_partitioned` AS SELECT
|
||
1 AS `id`,
|
||
1 AS `phone_number`,
|
||
1 AS `heart_rate`,
|
||
1 AS `pressure_high`,
|
||
1 AS `pressure_low`,
|
||
1 AS `blood_oxygen`,
|
||
1 AS `stress`,
|
||
1 AS `temperature`,
|
||
1 AS `step`,
|
||
1 AS `timestamp`,
|
||
1 AS `user_name`,
|
||
1 AS `latitude`,
|
||
1 AS `longitude`,
|
||
1 AS `altitude`,
|
||
1 AS `device_sn`,
|
||
1 AS `distance`,
|
||
1 AS `calorie`,
|
||
1 AS `is_deleted`,
|
||
1 AS `create_user`,
|
||
1 AS `create_user_id`,
|
||
1 AS `create_time`,
|
||
1 AS `update_user`,
|
||
1 AS `update_user_id`,
|
||
1 AS `update_time`,
|
||
1 AS `upload_method`,
|
||
1 AS `user_id`,
|
||
1 AS `org_id`,
|
||
1 AS `sleep`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Table structure for table `t_user_health_profile`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_health_profile`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_health_profile` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`customer_id` bigint NOT NULL COMMENT '租户ID',
|
||
`profile_date` date NOT NULL COMMENT '画像日期',
|
||
`overall_health_score` decimal(5,2) DEFAULT NULL COMMENT '综合健康评分',
|
||
`health_level` varchar(20) DEFAULT NULL COMMENT '健康等级',
|
||
`physiological_score` decimal(5,2) DEFAULT NULL COMMENT '生理指标评分',
|
||
`behavioral_score` decimal(5,2) DEFAULT NULL COMMENT '行为指标评分',
|
||
`risk_factor_score` decimal(5,2) DEFAULT NULL COMMENT '风险因子评分',
|
||
`cardiovascular_score` decimal(5,2) DEFAULT NULL COMMENT '心血管健康评分',
|
||
`respiratory_score` decimal(5,2) DEFAULT NULL COMMENT '呼吸系统评分',
|
||
`metabolic_score` decimal(5,2) DEFAULT NULL COMMENT '代谢功能评分',
|
||
`psychological_score` decimal(5,2) DEFAULT NULL COMMENT '心理健康评分',
|
||
`activity_consistency_score` decimal(5,2) DEFAULT NULL COMMENT '活动一致性评分',
|
||
`sleep_quality_score` decimal(5,2) DEFAULT NULL COMMENT '睡眠质量评分',
|
||
`health_engagement_score` decimal(5,2) DEFAULT NULL COMMENT '健康参与度评分',
|
||
`current_risk_level` varchar(20) DEFAULT NULL COMMENT '当前风险等级',
|
||
`predicted_risk_score` decimal(5,2) DEFAULT NULL COMMENT '预测风险评分',
|
||
`detailed_analysis` json DEFAULT NULL COMMENT '详细分析数据',
|
||
`trend_analysis` json DEFAULT NULL COMMENT '趋势分析数据',
|
||
`recommendations` json DEFAULT NULL COMMENT '个性化建议',
|
||
`create_time` datetime DEFAULT NULL,
|
||
`update_time` datetime DEFAULT NULL,
|
||
`is_deleted` tinyint(1) DEFAULT NULL,
|
||
`version` int DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `idx_user_profile_date` (`user_id`,`profile_date`,`is_deleted`),
|
||
KEY `idx_customer_date` (`customer_id`,`profile_date`),
|
||
KEY `idx_health_level` (`health_level`,`profile_date`),
|
||
KEY `idx_risk_level` (`current_risk_level`,`profile_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户健康画像主表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_user_online_status`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_user_online_status`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_user_online_status` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`device_sn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备序列号',
|
||
`online_status` tinyint(1) DEFAULT '0' COMMENT '在线状态 1-在线 0-离线',
|
||
`last_location_lng` decimal(11,8) DEFAULT NULL COMMENT '最后位置经度',
|
||
`last_location_lat` decimal(11,8) DEFAULT NULL COMMENT '最后位置纬度',
|
||
`last_location_time` datetime DEFAULT NULL COMMENT '最后定位时间',
|
||
`last_heartbeat_time` datetime DEFAULT NULL COMMENT '最后心跳时间',
|
||
`connection_type` enum('GPS','NETWORK','PASSIVE') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '连接类型',
|
||
`battery_level` tinyint DEFAULT NULL COMMENT '电池电量',
|
||
`signal_strength` tinyint DEFAULT NULL COMMENT '信号强度',
|
||
`customer_id` bigint DEFAULT '0' COMMENT '租户ID',
|
||
`org_id` bigint DEFAULT NULL COMMENT '组织ID',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_device_online` (`user_id`,`device_sn`),
|
||
KEY `idx_online_status_time` (`online_status`,`last_heartbeat_time`),
|
||
KEY `idx_online_customer_org` (`customer_id`,`org_id`),
|
||
KEY `idx_online_location_time` (`last_location_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户在线状态表';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `t_wechat_alarm_config`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `t_wechat_alarm_config`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `t_wechat_alarm_config` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`customer_id` bigint NOT NULL,
|
||
`type` varchar(20) NOT NULL COMMENT '微信类型: enterprise(企业微信)/official(公众号)',
|
||
`corp_id` varchar(100) DEFAULT NULL COMMENT '企业微信企业ID',
|
||
`agent_id` varchar(50) DEFAULT NULL COMMENT '企业微信应用AgentID',
|
||
`secret` varchar(100) DEFAULT NULL COMMENT '企业微信应用Secret',
|
||
`appid` varchar(100) DEFAULT NULL COMMENT '微信公众号AppID',
|
||
`appsecret` varchar(100) DEFAULT NULL COMMENT '微信公众号AppSecret',
|
||
`template_id` varchar(100) DEFAULT NULL COMMENT '微信模板消息ID',
|
||
`enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用告警',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_tenant_type` (`customer_id`,`type`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='微信告警配置表(支持企业微信和公众号)';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `tDeviceConfig`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `tDeviceConfig`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `tDeviceConfig` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`spo2MeasurePeriod` int NOT NULL,
|
||
`stressMeasurePeriod` int NOT NULL,
|
||
`bodyTemperatureMeasurePeriod` int NOT NULL,
|
||
`heartRateWarningHigh` int NOT NULL,
|
||
`heartRateWarningLow` int NOT NULL,
|
||
`spo2Warning` int NOT NULL,
|
||
`stressWarning` int NOT NULL,
|
||
`bodyTemperatureHighWarning` float NOT NULL,
|
||
`bodyTemperatureLowWarning` float NOT NULL,
|
||
`httpUrl` varchar(255) DEFAULT 'http://14.153.203.230:7007/',
|
||
`logo` varchar(255) DEFAULT '灵境万象',
|
||
`uiType` varchar(255) NOT NULL DEFAULT 'wifi',
|
||
`bodyTemperatureWarningCnt` int DEFAULT '5',
|
||
`heartWarningCnt` int DEFAULT '5',
|
||
`heartRateMeasurePeriod` int DEFAULT '5000',
|
||
`spo2WarningCnt` int DEFAULT '5',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `tool_generator_table`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `tool_generator_table`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `tool_generator_table` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`table_name` varchar(64) NOT NULL COMMENT '表名称',
|
||
`table_comment` varchar(64) NOT NULL COMMENT '表描述',
|
||
`table_prefix` varchar(20) DEFAULT NULL COMMENT '表前缀',
|
||
`parent_package` varchar(64) NOT NULL COMMENT '生成父包名',
|
||
`module_name` varchar(64) NOT NULL COMMENT '生成模块名',
|
||
`parent_menu_id` bigint NOT NULL COMMENT '父级菜单ID',
|
||
`author` varchar(64) DEFAULT NULL COMMENT '生成作者',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` char(1) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代码生成表管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Table structure for table `tool_generator_table_column`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `tool_generator_table_column`;
|
||
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
CREATE TABLE `tool_generator_table_column` (
|
||
`id` bigint NOT NULL COMMENT '主键',
|
||
`table_id` bigint NOT NULL COMMENT '生成关联表ID',
|
||
`table_name` varchar(64) NOT NULL COMMENT '生成关联表名称',
|
||
`column_name` varchar(64) NOT NULL COMMENT '表列名称',
|
||
`property_name` varchar(50) DEFAULT NULL COMMENT '属性名称',
|
||
`column_comment` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '字段描述',
|
||
`data_type` varchar(40) DEFAULT NULL COMMENT '字段数据类型',
|
||
`java_type` varchar(40) DEFAULT NULL COMMENT 'JAVA 字段类型',
|
||
`typescript_type` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'TypeScript 字段类型',
|
||
`ordinal_position` int NOT NULL COMMENT '表列位置',
|
||
`is_i18n` char(1) DEFAULT '0' COMMENT '是否多语言(0:否,1:是)',
|
||
`is_required` char(1) DEFAULT '0' COMMENT '必填(0:否,1:是)',
|
||
`is_list` char(1) DEFAULT '0' COMMENT '列表(0:否,1:是)',
|
||
`is_search` char(1) DEFAULT '0' COMMENT '查询(0:否,1:是)',
|
||
`search_type` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '查询类型(大于,小于等)',
|
||
`is_added` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '0' COMMENT '新增(0:否,1:是)',
|
||
`is_edit` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '0' COMMENT '编辑更新(0:否,1:是)',
|
||
`dict_code` varchar(64) DEFAULT NULL COMMENT '关联字典代码',
|
||
`render_type` varchar(20) DEFAULT NULL COMMENT '渲染类型(select,radio)',
|
||
`create_user` varchar(64) NOT NULL COMMENT '创建用户',
|
||
`create_user_id` bigint NOT NULL COMMENT '创建用户ID',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_user` varchar(64) DEFAULT NULL COMMENT '修改用户',
|
||
`update_user_id` bigint DEFAULT NULL COMMENT '修改用户ID',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`status` char(1) DEFAULT '1' COMMENT '是否启用(0:禁用,1:启用)',
|
||
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '是否删除(0:否,1:是)',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代码生成表字段列管理';
|
||
/*!40101 SET character_set_client = @saved_cs_client */;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_alert_rule_usage`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_alert_rule_usage`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_alert_rule_usage`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_alert_rule_usage` AS SELECT
|
||
1 AS `rule_type`,
|
||
1 AS `severity_level`,
|
||
1 AS `is_emergency`,
|
||
1 AS `alert_count`,
|
||
1 AS `device_count`,
|
||
1 AS `response_rate`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_alert_rules_enhanced`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_alert_rules_enhanced`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_alert_rules_enhanced`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_alert_rules_enhanced` AS SELECT
|
||
1 AS `id`,
|
||
1 AS `rule_type`,
|
||
1 AS `rule_category`,
|
||
1 AS `physical_sign`,
|
||
1 AS `threshold_min`,
|
||
1 AS `threshold_max`,
|
||
1 AS `condition_expression`,
|
||
1 AS `time_window_seconds`,
|
||
1 AS `cooldown_seconds`,
|
||
1 AS `priority_level`,
|
||
1 AS `rule_tags`,
|
||
1 AS `effective_time_start`,
|
||
1 AS `effective_time_end`,
|
||
1 AS `effective_days`,
|
||
1 AS `alert_message`,
|
||
1 AS `severity_level`,
|
||
1 AS `enabled_channels`,
|
||
1 AS `is_enabled`,
|
||
1 AS `customer_id`,
|
||
1 AS `version`,
|
||
1 AS `create_time`,
|
||
1 AS `update_time`,
|
||
1 AS `execution_count`,
|
||
1 AS `success_count`,
|
||
1 AS `avg_execution_time`,
|
||
1 AS `alert_generated_count`,
|
||
1 AS `performance_score`,
|
||
1 AS `last_execution_time`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_event_alarm_stats`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_event_alarm_stats`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_event_alarm_stats`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_event_alarm_stats` AS SELECT
|
||
1 AS `stat_date`,
|
||
1 AS `processing_status`,
|
||
1 AS `event_count`,
|
||
1 AS `device_count`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_message_indexes`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_message_indexes`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_message_indexes`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_message_indexes` AS SELECT
|
||
1 AS `TABLE_NAME`,
|
||
1 AS `INDEX_NAME`,
|
||
1 AS `index_columns`,
|
||
1 AS `INDEX_COMMENT`,
|
||
1 AS `index_category`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_org_hierarchy_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_org_hierarchy_info`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_org_hierarchy_info`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_org_hierarchy_info` AS SELECT
|
||
1 AS `org_id`,
|
||
1 AS `org_name`,
|
||
1 AS `org_code`,
|
||
1 AS `org_level`,
|
||
1 AS `customer_id`,
|
||
1 AS `direct_children_count`,
|
||
1 AS `total_children_count`,
|
||
1 AS `manager_count`,
|
||
1 AS `supervisor_count`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_table_comparison`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_table_comparison`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_table_comparison`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_table_comparison` AS SELECT
|
||
1 AS `table_version`,
|
||
1 AS `table_name`,
|
||
1 AS `row_count`,
|
||
1 AS `size_mb`,
|
||
1 AS `index_size_mb`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_tenant_info`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_tenant_info`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_tenant_info`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_tenant_info` AS SELECT
|
||
1 AS `tenant_id`,
|
||
1 AS `tenant_name`,
|
||
1 AS `tenant_code`,
|
||
1 AS `tenant_status`,
|
||
1 AS `tenant_description`,
|
||
1 AS `sort_order`,
|
||
1 AS `create_user`,
|
||
1 AS `create_user_id`,
|
||
1 AS `created_at`,
|
||
1 AS `update_user`,
|
||
1 AS `update_user_id`,
|
||
1 AS `updated_at`,
|
||
1 AS `upload_method`,
|
||
1 AS `license_key`,
|
||
1 AS `is_support_license`,
|
||
1 AS `enable_resume`,
|
||
1 AS `upload_retry_count`,
|
||
1 AS `cache_max_count`,
|
||
1 AS `upload_retry_interval`,
|
||
1 AS `logo_url`,
|
||
1 AS `logo_file_name`,
|
||
1 AS `logo_upload_time`,
|
||
1 AS `org_count`,
|
||
1 AS `user_count`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Temporary view structure for view `v_user_message_stats`
|
||
--
|
||
|
||
DROP TABLE IF EXISTS `v_user_message_stats`;
|
||
/*!50001 DROP VIEW IF EXISTS `v_user_message_stats`*/;
|
||
SET @saved_cs_client = @@character_set_client;
|
||
/*!50503 SET character_set_client = utf8mb4 */;
|
||
/*!50001 CREATE VIEW `v_user_message_stats` AS SELECT
|
||
1 AS `user_id`,
|
||
1 AS `total_messages`,
|
||
1 AS `pending_count`,
|
||
1 AS `delivered_count`,
|
||
1 AS `acknowledged_count`,
|
||
1 AS `last_message_time`,
|
||
1 AS `first_message_time`*/;
|
||
SET character_set_client = @saved_cs_client;
|
||
|
||
--
|
||
-- Current Database: `test`
|
||
--
|
||
|
||
USE `test`;
|
||
|
||
--
|
||
-- Final view structure for view `t_user_health_data_partitioned`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `t_user_health_data_partitioned`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `t_user_health_data_partitioned` AS select `t_user_health_data`.`id` AS `id`,`t_user_health_data`.`phone_number` AS `phone_number`,`t_user_health_data`.`heart_rate` AS `heart_rate`,`t_user_health_data`.`pressure_high` AS `pressure_high`,`t_user_health_data`.`pressure_low` AS `pressure_low`,`t_user_health_data`.`blood_oxygen` AS `blood_oxygen`,`t_user_health_data`.`stress` AS `stress`,`t_user_health_data`.`temperature` AS `temperature`,`t_user_health_data`.`step` AS `step`,`t_user_health_data`.`timestamp` AS `timestamp`,`t_user_health_data`.`user_name` AS `user_name`,`t_user_health_data`.`latitude` AS `latitude`,`t_user_health_data`.`longitude` AS `longitude`,`t_user_health_data`.`altitude` AS `altitude`,`t_user_health_data`.`device_sn` AS `device_sn`,`t_user_health_data`.`distance` AS `distance`,`t_user_health_data`.`calorie` AS `calorie`,`t_user_health_data`.`is_deleted` AS `is_deleted`,`t_user_health_data`.`create_user` AS `create_user`,`t_user_health_data`.`create_user_id` AS `create_user_id`,`t_user_health_data`.`create_time` AS `create_time`,`t_user_health_data`.`update_user` AS `update_user`,`t_user_health_data`.`update_user_id` AS `update_user_id`,`t_user_health_data`.`update_time` AS `update_time`,`t_user_health_data`.`upload_method` AS `upload_method`,`t_user_health_data`.`user_id` AS `user_id`,`t_user_health_data`.`org_id` AS `org_id`,`t_user_health_data`.`sleep` AS `sleep` from `t_user_health_data` union all select `t_user_health_data_202506`.`id` AS `id`,`t_user_health_data_202506`.`phone_number` AS `phone_number`,`t_user_health_data_202506`.`heart_rate` AS `heart_rate`,`t_user_health_data_202506`.`pressure_high` AS `pressure_high`,`t_user_health_data_202506`.`pressure_low` AS `pressure_low`,`t_user_health_data_202506`.`blood_oxygen` AS `blood_oxygen`,`t_user_health_data_202506`.`stress` AS `stress`,`t_user_health_data_202506`.`temperature` AS `temperature`,`t_user_health_data_202506`.`step` AS `step`,`t_user_health_data_202506`.`timestamp` AS `timestamp`,`t_user_health_data_202506`.`user_name` AS `user_name`,`t_user_health_data_202506`.`latitude` AS `latitude`,`t_user_health_data_202506`.`longitude` AS `longitude`,`t_user_health_data_202506`.`altitude` AS `altitude`,`t_user_health_data_202506`.`device_sn` AS `device_sn`,`t_user_health_data_202506`.`distance` AS `distance`,`t_user_health_data_202506`.`calorie` AS `calorie`,`t_user_health_data_202506`.`is_deleted` AS `is_deleted`,`t_user_health_data_202506`.`create_user` AS `create_user`,`t_user_health_data_202506`.`create_user_id` AS `create_user_id`,`t_user_health_data_202506`.`create_time` AS `create_time`,`t_user_health_data_202506`.`update_user` AS `update_user`,`t_user_health_data_202506`.`update_user_id` AS `update_user_id`,`t_user_health_data_202506`.`update_time` AS `update_time`,`t_user_health_data_202506`.`upload_method` AS `upload_method`,`t_user_health_data_202506`.`user_id` AS `user_id`,`t_user_health_data_202506`.`org_id` AS `org_id`,`t_user_health_data_202506`.`sleep` AS `sleep` from `t_user_health_data_202506` */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_alert_rule_usage`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_alert_rule_usage`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_unicode_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_alert_rule_usage` AS select `r`.`rule_type` AS `rule_type`,`r`.`severity_level` AS `severity_level`,`r`.`is_emergency` AS `is_emergency`,count(`a`.`id`) AS `alert_count`,count(distinct `a`.`device_sn`) AS `device_count`,avg((case when (`a`.`alert_status` = 'responded') then 1 else 0 end)) AS `response_rate` from (`t_system_event_rule` `r` left join `t_alert_info` `a` on(((convert(`r`.`rule_type` using utf8mb4) collate utf8mb4_general_ci) = (convert(`a`.`alert_type` using utf8mb4) collate utf8mb4_general_ci)))) where (`r`.`is_active` = 1) group by `r`.`id`,`r`.`rule_type`,`r`.`severity_level`,`r`.`is_emergency` */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_alert_rules_enhanced`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_alert_rules_enhanced`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_alert_rules_enhanced` AS select `ar`.`id` AS `id`,`ar`.`rule_type` AS `rule_type`,`ar`.`rule_category` AS `rule_category`,`ar`.`physical_sign` AS `physical_sign`,`ar`.`threshold_min` AS `threshold_min`,`ar`.`threshold_max` AS `threshold_max`,`ar`.`condition_expression` AS `condition_expression`,`ar`.`time_window_seconds` AS `time_window_seconds`,`ar`.`cooldown_seconds` AS `cooldown_seconds`,`ar`.`priority_level` AS `priority_level`,`ar`.`rule_tags` AS `rule_tags`,`ar`.`effective_time_start` AS `effective_time_start`,`ar`.`effective_time_end` AS `effective_time_end`,`ar`.`effective_days` AS `effective_days`,`ar`.`alert_message` AS `alert_message`,`ar`.`level` AS `severity_level`,`ar`.`enabled_channels` AS `enabled_channels`,`ar`.`is_enabled` AS `is_enabled`,`ar`.`customer_id` AS `customer_id`,`ar`.`version` AS `version`,`ar`.`create_time` AS `create_time`,`ar`.`update_time` AS `update_time`,coalesce(`arp`.`execution_count`,0) AS `execution_count`,coalesce(`arp`.`success_count`,0) AS `success_count`,coalesce(`arp`.`avg_execution_time`,0) AS `avg_execution_time`,coalesce(`arp`.`alert_generated_count`,0) AS `alert_generated_count`,coalesce(`arp`.`performance_score`,100.00) AS `performance_score`,`arp`.`last_execution_time` AS `last_execution_time` from (`t_alert_rules` `ar` left join `t_alert_rule_performance` `arp` on(((`ar`.`id` = `arp`.`rule_id`) and (`ar`.`customer_id` = `arp`.`customer_id`)))) where (`ar`.`is_deleted` = 0) */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_event_alarm_stats`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_event_alarm_stats`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_event_alarm_stats` AS select cast(`t_event_alarm_queue`.`create_time` as date) AS `stat_date`,`t_event_alarm_queue`.`processing_status` AS `processing_status`,count(0) AS `event_count`,count(distinct `t_event_alarm_queue`.`device_sn`) AS `device_count` from `t_event_alarm_queue` group by cast(`t_event_alarm_queue`.`create_time` as date),`t_event_alarm_queue`.`processing_status` */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_message_indexes`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_message_indexes`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_message_indexes` AS select `information_schema`.`statistics`.`TABLE_NAME` AS `TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME` AS `INDEX_NAME`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns`,`information_schema`.`statistics`.`INDEX_COMMENT` AS `INDEX_COMMENT`,(case when (`information_schema`.`statistics`.`INDEX_NAME` like '%opt%') then 'PHASE1_OPTIMIZED' when (`information_schema`.`statistics`.`INDEX_NAME` like '%temp%') then 'TEMPORARY' else 'ORIGINAL' end) AS `index_category` from `information_schema`.`STATISTICS` where ((`information_schema`.`statistics`.`TABLE_SCHEMA` = database()) and (`information_schema`.`statistics`.`TABLE_NAME` in ('t_device_message','t_device_message_detail'))) group by `information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`,`information_schema`.`statistics`.`INDEX_COMMENT` order by `information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME` */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_org_hierarchy_info`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_org_hierarchy_info`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_org_hierarchy_info` AS select `org`.`id` AS `org_id`,`org`.`name` AS `org_name`,`org`.`code` AS `org_code`,`org`.`level` AS `org_level`,`org`.`customer_id` AS `customer_id`,(select count(0) from (`sys_org_closure` `c2` join `sys_org_units` `child` on((`c2`.`descendant_id` = `child`.`id`))) where ((`c2`.`ancestor_id` = `org`.`id`) and (`c2`.`depth` = 1) and (`child`.`is_deleted` = 0))) AS `direct_children_count`,(select count(0) from (`sys_org_closure` `c3` join `sys_org_units` `child` on((`c3`.`descendant_id` = `child`.`id`))) where ((`c3`.`ancestor_id` = `org`.`id`) and (`c3`.`depth` > 0) and (`child`.`is_deleted` = 0))) AS `total_children_count`,(select count(0) from `sys_org_manager_cache` `mc` where ((`mc`.`org_id` = `org`.`id`) and (`mc`.`role_type` = 'manager') and (`mc`.`is_active` = 1))) AS `manager_count`,(select count(0) from `sys_org_manager_cache` `mc` where ((`mc`.`org_id` = `org`.`id`) and (`mc`.`role_type` = 'supervisor') and (`mc`.`is_active` = 1))) AS `supervisor_count` from `sys_org_units` `org` where (`org`.`is_deleted` = 0) */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_table_comparison`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_table_comparison`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_table_comparison` AS select 'ORIGINAL' AS `table_version`,'t_device_message' AS `table_name`,`information_schema`.`tables`.`TABLE_ROWS` AS `row_count`,round((((`information_schema`.`tables`.`DATA_LENGTH` + `information_schema`.`tables`.`INDEX_LENGTH`) / 1024) / 1024),2) AS `size_mb`,round(((`information_schema`.`tables`.`INDEX_LENGTH` / 1024) / 1024),2) AS `index_size_mb` from `information_schema`.`TABLES` where ((`information_schema`.`tables`.`TABLE_SCHEMA` = database()) and (`information_schema`.`tables`.`TABLE_NAME` = 't_device_message')) union all select 'OPTIMIZED_V2' AS `table_version`,'t_device_message_v2' AS `table_name`,`information_schema`.`tables`.`TABLE_ROWS` AS `row_count`,round((((`information_schema`.`tables`.`DATA_LENGTH` + `information_schema`.`tables`.`INDEX_LENGTH`) / 1024) / 1024),2) AS `size_mb`,round(((`information_schema`.`tables`.`INDEX_LENGTH` / 1024) / 1024),2) AS `index_size_mb` from `information_schema`.`TABLES` where ((`information_schema`.`tables`.`TABLE_SCHEMA` = database()) and (`information_schema`.`tables`.`TABLE_NAME` = 't_device_message_v2')) */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_tenant_info`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_tenant_info`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_tenant_info` AS select `o`.`id` AS `tenant_id`,`o`.`name` AS `tenant_name`,`o`.`code` AS `tenant_code`,`o`.`status` AS `tenant_status`,`o`.`description` AS `tenant_description`,`o`.`sort` AS `sort_order`,`o`.`create_user` AS `create_user`,`o`.`create_user_id` AS `create_user_id`,`o`.`create_time` AS `created_at`,`o`.`update_user` AS `update_user`,`o`.`update_user_id` AS `update_user_id`,`o`.`update_time` AS `updated_at`,`c`.`upload_method` AS `upload_method`,`c`.`license_key` AS `license_key`,`c`.`is_support_license` AS `is_support_license`,`c`.`enable_resume` AS `enable_resume`,`c`.`upload_retry_count` AS `upload_retry_count`,`c`.`cache_max_count` AS `cache_max_count`,`c`.`upload_retry_interval` AS `upload_retry_interval`,`c`.`logo_url` AS `logo_url`,`c`.`logo_file_name` AS `logo_file_name`,`c`.`logo_upload_time` AS `logo_upload_time`,(select count(0) from `sys_org_units` where ((`sys_org_units`.`customer_id` = `o`.`id`) and (`sys_org_units`.`is_deleted` = 0))) AS `org_count`,(select count(0) from `sys_user` where ((`sys_user`.`customer_id` = `o`.`id`) and (`sys_user`.`is_deleted` = 0))) AS `user_count` from (`sys_org_units` `o` left join `t_customer_config` `c` on((`o`.`id` = `c`.`customer_id`))) where ((`o`.`parent_id` = 0) and (`o`.`customer_id` = `o`.`id`) and (`o`.`is_deleted` = 0)) */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
|
||
--
|
||
-- Final view structure for view `v_user_message_stats`
|
||
--
|
||
|
||
/*!50001 DROP VIEW IF EXISTS `v_user_message_stats`*/;
|
||
/*!50001 SET @saved_cs_client = @@character_set_client */;
|
||
/*!50001 SET @saved_cs_results = @@character_set_results */;
|
||
/*!50001 SET @saved_col_connection = @@collation_connection */;
|
||
/*!50001 SET character_set_client = utf8mb4 */;
|
||
/*!50001 SET character_set_results = utf8mb4 */;
|
||
/*!50001 SET collation_connection = utf8mb4_0900_ai_ci */;
|
||
/*!50001 CREATE ALGORITHM=UNDEFINED */
|
||
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
|
||
/*!50001 VIEW `v_user_message_stats` AS select `m`.`user_id` AS `user_id`,count(0) AS `total_messages`,count((case when (`m`.`message_status` = 'pending') then 1 end)) AS `pending_count`,count((case when (`m`.`message_status` = 'delivered') then 1 end)) AS `delivered_count`,count((case when (`m`.`message_status` = 'acknowledged') then 1 end)) AS `acknowledged_count`,max(`m`.`create_time`) AS `last_message_time`,min(`m`.`create_time`) AS `first_message_time` from `t_device_message` `m` where ((`m`.`user_id` is not null) and (`m`.`is_deleted` = 0)) group by `m`.`user_id` */;
|
||
/*!50001 SET character_set_client = @saved_cs_client */;
|
||
/*!50001 SET character_set_results = @saved_cs_results */;
|
||
/*!50001 SET collation_connection = @saved_col_connection */;
|
||
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
||
|
||
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
||
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
||
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
||
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
||
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
||
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
||
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
||
|
||
-- Dump completed on 2025-12-19 1:33:12
|