From 93eb6b470773bc49ea6e1a9d4cbd914eb95d525b Mon Sep 17 00:00:00 2001 From: lrj <owen.stl@gmail.com> Date: 星期二, 30 九月 2025 17:38:04 +0800 Subject: [PATCH] feat: 完善比赛晋级功能并清理测试文件 --- db.sql | 210 ++++++++++++++++++++++++++++++++++++++++++---------- 1 files changed, 168 insertions(+), 42 deletions(-) diff --git a/db.sql b/db.sql index b67c704..7b2b5b0 100644 --- a/db.sql +++ b/db.sql @@ -1,7 +1,16 @@ --- Database schema for ryc --- Generated at: 2025/9/27 19:34:25 +-- 鏁版嵁搴撶粨鏋勫鍑� +-- 鏁版嵁搴�: ryc +-- 瀵煎嚭鏃堕棿: 2025/9/30 08:39:43 +-- +-- 娉ㄦ剰锛氭鏂囦欢浠呭寘鍚〃缁撴瀯锛屼笉鍖呭惈鏁版嵁 --- Table: t_activity +SET NAMES utf8mb4; +SET FOREIGN_KEY_CHECKS = 0; + +-- ---------------------------- +-- Table structure for t_activity +-- ---------------------------- +DROP TABLE IF EXISTS `t_activity`; CREATE TABLE `t_activity` ( `id` bigint NOT NULL AUTO_INCREMENT, `pid` bigint NOT NULL DEFAULT '0', @@ -23,9 +32,12 @@ KEY `fk_t_activity_rating_scheme` (`rating_scheme_id`) USING BTREE, KEY `idx_t_activity_deadline` (`signup_deadline`) USING BTREE, CONSTRAINT `fk_t_activity_rating_scheme` FOREIGN KEY (`rating_scheme_id`) REFERENCES `t_rating_scheme` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT -) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_activity_judge +-- ---------------------------- +-- Table structure for t_activity_judge +-- ---------------------------- +DROP TABLE IF EXISTS `t_activity_judge`; CREATE TABLE `t_activity_judge` ( `id` bigint NOT NULL AUTO_INCREMENT, `activity_id` bigint NOT NULL, @@ -40,9 +52,12 @@ `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uq_stage_judge` (`stage_id`,`judge_id`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_activity_player +-- ---------------------------- +-- Table structure for t_activity_player +-- ---------------------------- +DROP TABLE IF EXISTS `t_activity_player`; CREATE TABLE `t_activity_player` ( `id` bigint NOT NULL AUTO_INCREMENT, `activity_id` bigint NOT NULL, @@ -63,9 +78,12 @@ `update_user_id` bigint DEFAULT NULL, `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_activity_player_rating +-- ---------------------------- +-- Table structure for t_activity_player_rating +-- ---------------------------- +DROP TABLE IF EXISTS `t_activity_player_rating`; CREATE TABLE `t_activity_player_rating` ( `id` bigint NOT NULL AUTO_INCREMENT, `activity_id` bigint NOT NULL, @@ -85,7 +103,10 @@ PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; --- Table: t_activity_player_rating_item +-- ---------------------------- +-- Table structure for t_activity_player_rating_item +-- ---------------------------- +DROP TABLE IF EXISTS `t_activity_player_rating_item`; CREATE TABLE `t_activity_player_rating_item` ( `id` bigint NOT NULL AUTO_INCREMENT, `activity_id` bigint NOT NULL, @@ -107,7 +128,10 @@ PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_carousel +-- ---------------------------- +-- Table structure for t_carousel +-- ---------------------------- +DROP TABLE IF EXISTS `t_carousel`; CREATE TABLE `t_carousel` ( `id` bigint NOT NULL AUTO_INCREMENT, `title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -122,7 +146,10 @@ PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='杞挱鍥�'; --- Table: t_employee +-- ---------------------------- +-- Table structure for t_employee +-- ---------------------------- +DROP TABLE IF EXISTS `t_employee`; CREATE TABLE `t_employee` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -138,9 +165,12 @@ `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `phone` (`phone`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_employee_role +-- ---------------------------- +-- Table structure for t_employee_role +-- ---------------------------- +DROP TABLE IF EXISTS `t_employee_role`; CREATE TABLE `t_employee_role` ( `id` bigint NOT NULL AUTO_INCREMENT, `employee_id` bigint NOT NULL, @@ -154,7 +184,10 @@ KEY `fk_t_user_role_role` (`role_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_judge +-- ---------------------------- +-- Table structure for t_judge +-- ---------------------------- +DROP TABLE IF EXISTS `t_judge`; CREATE TABLE `t_judge` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -174,9 +207,12 @@ `introduction` text COMMENT '涓汉浠嬬粛', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `phone` (`phone`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_judge_tag +-- ---------------------------- +-- Table structure for t_judge_tag +-- ---------------------------- +DROP TABLE IF EXISTS `t_judge_tag`; CREATE TABLE `t_judge_tag` ( `id` bigint NOT NULL AUTO_INCREMENT, `judge_id` bigint NOT NULL, @@ -189,9 +225,12 @@ `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, KEY `fk_t_judge_major_tag` (`tag_id`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_media +-- ---------------------------- +-- Table structure for t_media +-- ---------------------------- +DROP TABLE IF EXISTS `t_media`; CREATE TABLE `t_media` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, @@ -210,10 +249,62 @@ `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `update_user_id` bigint DEFAULT NULL, `version` bigint NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + PRIMARY KEY (`id`) USING BTREE, + KEY `uq_type_id` (`target_type`,`target_id`) +) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_notification_task +-- ---------------------------- +-- Table structure for t_media_backup_avatar_migration +-- ---------------------------- +DROP TABLE IF EXISTS `t_media_backup_avatar_migration`; +CREATE TABLE `t_media_backup_avatar_migration` ( + `id` int NOT NULL DEFAULT '0', + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `target_type` int NOT NULL, + `target_id` bigint NOT NULL, + `media_type` int NOT NULL, + `path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '鑵捐浜戠殑瀛樺偍妗跺湴鍧�', + `thumb_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `file_ext` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `file_size` int NOT NULL, + `duration` int DEFAULT NULL COMMENT '瑙嗛鐨勯暱搴︾', + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `state` int NOT NULL DEFAULT '1', + `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `create_user_id` bigint DEFAULT NULL, + `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `update_user_id` bigint DEFAULT NULL, + `version` bigint NOT NULL DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; + +-- ---------------------------- +-- Table structure for t_msg +-- ---------------------------- +DROP TABLE IF EXISTS `t_msg`; +CREATE TABLE `t_msg` ( + `id` int NOT NULL AUTO_INCREMENT, + `target_type` int NOT NULL, + `target_id` int NOT NULL, + `player_id` int NOT NULL, + `user_id` int NOT NULL, + `content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, + `template_content` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL, + `wx_msg_success` bit(1) NOT NULL, + `wx_msg_err_count` int NOT NULL DEFAULT '0', + `wx_msg_last_err` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, + `state` int NOT NULL COMMENT '0:鏆傛椂涓嶅彂甯冿紝 1锛氬彲浠ュ彂甯冿紝2锛氬凡缁忓彂甯�', + `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `create_user_id` bigint DEFAULT NULL, + `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `update_user_id` bigint DEFAULT NULL, + `version` bigint NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; + +-- ---------------------------- +-- Table structure for t_notification_task +-- ---------------------------- +DROP TABLE IF EXISTS `t_notification_task`; CREATE TABLE `t_notification_task` ( `id` bigint NOT NULL AUTO_INCREMENT, `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -233,7 +324,10 @@ PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_permission +-- ---------------------------- +-- Table structure for t_permission +-- ---------------------------- +DROP TABLE IF EXISTS `t_permission`; CREATE TABLE `t_permission` ( `id` bigint NOT NULL AUTO_INCREMENT, `code` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -251,7 +345,10 @@ KEY `idx_t_permission_code` (`code`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_player +-- ---------------------------- +-- Table structure for t_player +-- ---------------------------- +DROP TABLE IF EXISTS `t_player`; CREATE TABLE `t_player` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, @@ -272,9 +369,12 @@ `user_id` bigint NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `phone` (`phone`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_rating_item +-- ---------------------------- +-- Table structure for t_rating_item +-- ---------------------------- +DROP TABLE IF EXISTS `t_rating_item`; CREATE TABLE `t_rating_item` ( `id` bigint NOT NULL AUTO_INCREMENT, `scheme_id` bigint NOT NULL, @@ -288,11 +388,13 @@ `update_user_id` bigint DEFAULT NULL, `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, - KEY `idx_t_rating_item_scheme` (`scheme_id`) USING BTREE, - CONSTRAINT `fk_t_rating_item_scheme` FOREIGN KEY (`scheme_id`) REFERENCES `t_rating_scheme` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT -) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + KEY `idx_t_rating_item_scheme` (`scheme_id`) USING BTREE +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_rating_scheme +-- ---------------------------- +-- Table structure for t_rating_scheme +-- ---------------------------- +DROP TABLE IF EXISTS `t_rating_scheme`; CREATE TABLE `t_rating_scheme` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -304,9 +406,12 @@ `update_user_id` bigint DEFAULT NULL, `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_region +-- ---------------------------- +-- Table structure for t_region +-- ---------------------------- +DROP TABLE IF EXISTS `t_region`; CREATE TABLE `t_region` ( `id` bigint NOT NULL AUTO_INCREMENT, `pid` bigint NOT NULL COMMENT '鑷叧鑱�', @@ -323,9 +428,12 @@ `version` bigint NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `code` (`code`) USING BTREE -) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_role +-- ---------------------------- +-- Table structure for t_role +-- ---------------------------- +DROP TABLE IF EXISTS `t_role`; CREATE TABLE `t_role` ( `id` bigint NOT NULL AUTO_INCREMENT, `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -342,7 +450,10 @@ KEY `idx_t_role_code` (`code`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_role_permission +-- ---------------------------- +-- Table structure for t_role_permission +-- ---------------------------- +DROP TABLE IF EXISTS `t_role_permission`; CREATE TABLE `t_role_permission` ( `id` int NOT NULL AUTO_INCREMENT, `role_id` bigint NOT NULL, @@ -356,7 +467,10 @@ KEY `fk_t_role_permission_perm` (`permission_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_tag +-- ---------------------------- +-- Table structure for t_tag +-- ---------------------------- +DROP TABLE IF EXISTS `t_tag`; CREATE TABLE `t_tag` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -374,7 +488,10 @@ KEY `idx_t_tag_category` (`category`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_user +-- ---------------------------- +-- Table structure for t_user +-- ---------------------------- +DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, @@ -395,9 +512,12 @@ PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uq_wx_open_id` (`wx_openid`) USING BTREE, UNIQUE KEY `uq_phone` (`phone`) -) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- Table: t_wx_login_record +-- ---------------------------- +-- Table structure for t_wx_login_record +-- ---------------------------- +DROP TABLE IF EXISTS `t_wx_login_record`; CREATE TABLE `t_wx_login_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID', `wx_openid` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '寰俊openid', @@ -411,12 +531,18 @@ `phone_auth_time` datetime DEFAULT NULL COMMENT '鎵嬫満鍙锋巿鏉冩椂闂�', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏇存柊鏃堕棿', - `state` tinyint(1) DEFAULT '1' COMMENT '鐘舵�侊細0-绂佺敤锛�1-鍚敤', + `state` int DEFAULT '1' COMMENT '鐘舵�侊細0-绂佺敤锛�1-鍚敤', + `create_user_id` bigint DEFAULT NULL COMMENT '鍒涘缓鐢ㄦ埛ID', + `update_user_id` bigint DEFAULT NULL COMMENT '鏇存柊鐢ㄦ埛ID', + `version` bigint NOT NULL DEFAULT '0' COMMENT '鐗堟湰鍙�', PRIMARY KEY (`id`), KEY `idx_wx_openid` (`wx_openid`), KEY `idx_wx_unionid` (`wx_unionid`), KEY `idx_user_id` (`user_id`), KEY `idx_login_time` (`login_time`), - KEY `idx_phone_authorized` (`phone_authorized`) -) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='寰俊鐧诲綍璁板綍琛�'; + KEY `idx_phone_authorized` (`phone_authorized`), + KEY `idx_create_user_id` (`create_user_id`), + KEY `idx_update_user_id` (`update_user_id`) +) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='寰俊鐧诲綍璁板綍琛�'; +SET FOREIGN_KEY_CHECKS = 1; -- Gitblit v1.8.0