-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdb_foxnas_schema.sql
More file actions
198 lines (183 loc) · 11.1 KB
/
db_foxnas_schema.sql
File metadata and controls
198 lines (183 loc) · 11.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
/*
Navicat Premium Dump SQL
Source Server : Local
Source Server Type : MySQL
Source Server Version : 80040 (8.0.40)
Source Host : localhost:3306
Source Schema : db_foxnas
Target Server Type : MySQL
Target Server Version : 80040 (8.0.40)
File Encoding : 65001
Date: 14/04/2026 04:57:08
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_disk_metrics
-- ----------------------------
DROP TABLE IF EXISTS `sys_disk_metrics`;
CREATE TABLE `sys_disk_metrics` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`main_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`disk_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`used_space` bigint NOT NULL,
`total_space` bigint NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `f_disk_main_id`(`main_id` ASC) USING BTREE,
CONSTRAINT `f_disk_main_id` FOREIGN KEY (`main_id`) REFERENCES `sys_main_metrics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for sys_main_metrics
-- ----------------------------
DROP TABLE IF EXISTS `sys_main_metrics`;
CREATE TABLE `sys_main_metrics` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`cpu` double NOT NULL,
`total_memory` bigint NOT NULL,
`used_memory` bigint NOT NULL,
`upload_speed` bigint NOT NULL,
`download_speed` bigint NOT NULL,
`timestamp` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`sys_load` double NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE,
INDEX `i_timestamp`(`timestamp` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_ddns_access_secret
-- ----------------------------
DROP TABLE IF EXISTS `tb_ddns_access_secret`;
CREATE TABLE `tb_ddns_access_secret` (
`id` bigint NOT NULL AUTO_INCREMENT,
`access_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`access_secret` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`access_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标识的名字',
`access_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`dns_code` int NULL DEFAULT 1,
PRIMARY KEY (`id`, `access_name`) USING BTREE,
INDEX `id`(`id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_ddns_task
-- ----------------------------
DROP TABLE IF EXISTS `tb_ddns_task`;
CREATE TABLE `tb_ddns_task` (
`id` bigint NOT NULL AUTO_INCREMENT,
`task_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`task_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`dns_secret_id` bigint NOT NULL,
`sync_interval` int NOT NULL,
`ip_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`task_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`main_domain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`status` int NOT NULL,
`domain_rr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`last_failed` int NOT NULL DEFAULT 0,
`is_public_ip` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE,
INDEX `f_dns_secret_id`(`dns_secret_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_error_log
-- ----------------------------
DROP TABLE IF EXISTS `tb_error_log`;
CREATE TABLE `tb_error_log` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`module_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`error_message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`exception_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`stack_trace` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
`uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`params` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`created_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id` DESC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2475 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_permissions
-- ----------------------------
DROP TABLE IF EXISTS `tb_permissions`;
CREATE TABLE `tb_permissions` (
`permission_id` bigint NOT NULL AUTO_INCREMENT,
`area_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '可以访问的选项卡、功能',
`owner_uuid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`permission_id` DESC) USING BTREE,
INDEX `owner_uuid`(`owner_uuid` ASC) USING BTREE,
CONSTRAINT `f_users_id` FOREIGN KEY (`owner_uuid`) REFERENCES `tb_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 146 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_resources
-- ----------------------------
DROP TABLE IF EXISTS `tb_resources`;
CREATE TABLE `tb_resources` (
`resource_id` bigint NOT NULL AUTO_INCREMENT,
`owner_uuid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`permission_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`folder_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`resource_id`) USING BTREE,
INDEX `f_resource_user_id`(`owner_uuid` ASC) USING BTREE,
CONSTRAINT `f_resource_user_id` FOREIGN KEY (`owner_uuid`) REFERENCES `tb_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 94 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_user_oauth
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_oauth`;
CREATE TABLE `tb_user_oauth` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联用户名称',
`provider` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'OAuth 提供方（qq / github / gitee 等）',
`oauth_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '第三方平台唯一ID',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_provider_oauth_id`(`provider` ASC, `oauth_id` ASC) USING BTREE,
INDEX `idx_user_name`(`user_name` ASC) USING BTREE,
CONSTRAINT `fk_user_oauth_user` FOREIGN KEY (`user_name`) REFERENCES `tb_users` (`user_name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2019975381253808131 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户OAuth绑定表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for tb_users
-- ----------------------------
DROP TABLE IF EXISTS `tb_users`;
CREATE TABLE `tb_users` (
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `i_user_name`(`user_name` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for transcode_jobs
-- ----------------------------
DROP TABLE IF EXISTS `transcode_jobs`;
CREATE TABLE `transcode_jobs` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`job_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务ID(UUID)',
`creator_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '创建者用户ID',
`video_path` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '视频文件路径',
`fingerprint` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '视频文件指纹(MD5)',
`job_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'VIDEO' COMMENT '任务类型: VIDEO视频转码, SUBTITLE字幕转码',
`audio_track_index` int NULL DEFAULT 0 COMMENT '音频轨道索引',
`subtitle_track_index` int NULL DEFAULT -1 COMMENT '字幕轨道索引',
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PENDING' COMMENT '状态: PENDING等待中, PROCESSING进行中, COMPLETED已完成, FAILED失败, CANCELLED已取消',
`progress` double NULL DEFAULT 0 COMMENT '进度 0-100',
`current_stage` int NULL DEFAULT 0 COMMENT '当前阶段',
`total_stages` int NULL DEFAULT 0 COMMENT '总阶段数',
`output_path` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '输出文件路径',
`hls_path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'HLS播放路径',
`error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '错误信息',
`retry_count` int NULL DEFAULT 0 COMMENT '重试次数',
`is_immediate` tinyint(1) NULL DEFAULT 0 COMMENT '是否立即观看',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`completed_at` timestamp NULL DEFAULT NULL COMMENT '完成时间',
`expire_secs` bigint NOT NULL DEFAULT 86400 COMMENT '多少秒后过期，默认一天',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `job_id`(`job_id` ASC) USING BTREE,
INDEX `idx_creator_id`(`creator_id` ASC) USING BTREE,
INDEX `idx_status`(`status` ASC) USING BTREE,
INDEX `idx_fingerprint`(`fingerprint` ASC) USING BTREE,
INDEX `idx_created_at`(`created_at` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '转码任务表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;