夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 1 | -- 用户表(保持不变) |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 2 | CREATE TABLE IF NOT EXISTS `users` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 3 | `user_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 4 | `user_name` VARCHAR(255) NOT NULL, |
| 5 | `password` VARCHAR(255) NOT NULL, |
| 6 | `email` VARCHAR(255) NOT NULL UNIQUE, |
| 7 | `passkey` VARCHAR(255) NOT NULL UNIQUE |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 8 | ); |
wuchimedes | a26ed3f | 2025-04-25 14:41:09 +0800 | [diff] [blame] | 9 | -- 用户统计表 |
wuchimedes | 22ee83c | 2025-04-25 00:17:47 +0800 | [diff] [blame] | 10 | CREATE TABLE IF NOT EXISTS `user_stats` ( |
| 11 | user_id INT PRIMARY KEY, |
| 12 | passkey VARCHAR(255) NOT NULL UNIQUE, |
| 13 | total_upload FLOAT NOT NULL DEFAULT 0, |
| 14 | total_download FLOAT NOT NULL DEFAULT 0, |
| 15 | last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 16 | FOREIGN KEY (user_id) REFERENCES users(user_id), |
| 17 | FOREIGN KEY (passkey) REFERENCES users(passkey) |
| 18 | ); |
wuchimedes | a26ed3f | 2025-04-25 14:41:09 +0800 | [diff] [blame] | 19 | -- 种子表 |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 20 | CREATE TABLE IF NOT EXISTS `torrents` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 21 | `torrent_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 22 | `user_id` INT NOT NULL, |
| 23 | `torrent_name` VARCHAR(255) NOT NULL, |
wuchimedes | 8a576e0 | 2025-05-13 17:50:46 +0800 | [diff] [blame] | 24 | `file_path` VARCHAR(255) NOT NULL, |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 25 | `info_hash` BINARY(20) NOT NULL, |
| 26 | `file_size` FLOAT NOT NULL, |
| 27 | FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 28 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 29 | -- Peer表(保持不变) |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 30 | CREATE TABLE IF NOT EXISTS `peers` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 31 | `passkey` VARCHAR(255) NOT NULL, |
| 32 | `info_hash` BINARY(20) NOT NULL, |
| 33 | `peer_id` VARCHAR(20) NOT NULL, |
| 34 | `ip_address` VARCHAR(128) NOT NULL, |
| 35 | `port` INT NOT NULL, |
| 36 | `uploaded` FLOAT NOT NULL, |
| 37 | `downloaded` FLOAT NOT NULL, |
| 38 | `last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 39 | FOREIGN KEY (`passkey`) REFERENCES `users`(`passkey`), |
| 40 | PRIMARY KEY (`passkey`, `info_hash`, `peer_id`) |
wuchimedes | 079c163 | 2025-04-02 22:01:20 +0800 | [diff] [blame] | 41 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 42 | -- 帖子表(新增 hot_score 和 last_calculated 字段) |
wuchimedes | e5722e3 | 2025-04-13 17:38:50 +0800 | [diff] [blame] | 43 | CREATE TABLE IF NOT EXISTS `posts` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 44 | `post_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 45 | `user_id` INT NOT NULL, |
| 46 | `hot_score` DOUBLE DEFAULT 5.0 COMMENT '热度评分', |
| 47 | `view_count` INT DEFAULT 0 COMMENT '浏览数', |
| 48 | `post_title` VARCHAR(255) NOT NULL, |
| 49 | `post_content` TEXT NOT NULL, |
wuchimedes | 8a576e0 | 2025-05-13 17:50:46 +0800 | [diff] [blame] | 50 | `torrent_id` INT DEFAULT NULL, |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 51 | `post_type` ENUM('resource', 'discussion') NOT NULL, |
| 52 | `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 53 | `last_calculated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后热度计算时间', |
| 54 | FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`), |
wuchimedes | 8a576e0 | 2025-05-13 17:50:46 +0800 | [diff] [blame] | 55 | FOREIGN KEY (`torrent_id`) REFERENCES `torrents`(`torrent_id`), |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 56 | INDEX `idx_hot_score` (`hot_score`), -- 新增热度索引 |
| 57 | INDEX `idx_post_type` (`post_type`) -- 新增类型索引 |
wuchimedes | e5722e3 | 2025-04-13 17:38:50 +0800 | [diff] [blame] | 58 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 59 | -- 标签表(保持不变) |
wuchimedes | 03f9358 | 2025-04-21 15:32:59 +0800 | [diff] [blame] | 60 | CREATE TABLE IF NOT EXISTS `tags`( |
wuchimedes | 8a576e0 | 2025-05-13 17:50:46 +0800 | [diff] [blame] | 61 | `tag_id` INT PRIMARY KEY, |
| 62 | `tag_name` VARCHAR(255) NOT NULL, |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 63 | `parent_id` INT DEFAULT NULL, |
| 64 | FOREIGN KEY (`parent_id`) REFERENCES `tags`(`tag_id`) |
wuchimedes | 03f9358 | 2025-04-21 15:32:59 +0800 | [diff] [blame] | 65 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 66 | -- 帖子标签关联表(保持不变) |
wuchimedes | 03f9358 | 2025-04-21 15:32:59 +0800 | [diff] [blame] | 67 | CREATE TABLE IF NOT EXISTS `post_tag` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 68 | `post_id` INT NOT NULL, |
| 69 | `tag_id` INT NOT NULL, |
| 70 | FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`), |
| 71 | FOREIGN KEY (`tag_id`) REFERENCES `tags`(`tag_id`), |
| 72 | PRIMARY KEY (`post_id`, `tag_id`) |
wuchimedes | 03f9358 | 2025-04-21 15:32:59 +0800 | [diff] [blame] | 73 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 74 | -- 用户关注表(保持不变) |
夜雨声烦 | 7e6eb38 | 2025-04-22 01:18:00 +0800 | [diff] [blame] | 75 | CREATE TABLE IF NOT EXISTS `user_follows` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 76 | `follower_id` INT NOT NULL, |
| 77 | `followed_id` INT NOT NULL, |
| 78 | `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 79 | FOREIGN KEY (`follower_id`) REFERENCES `users`(`user_id`), |
| 80 | FOREIGN KEY (`followed_id`) REFERENCES `users`(`user_id`), |
| 81 | PRIMARY KEY (`follower_id`, `followed_id`) |
夜雨声烦 | 7e6eb38 | 2025-04-22 01:18:00 +0800 | [diff] [blame] | 82 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 83 | -- 私信表(保持不变) |
夜雨声烦 | 7e6eb38 | 2025-04-22 01:18:00 +0800 | [diff] [blame] | 84 | CREATE TABLE IF NOT EXISTS `private_messages` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 85 | `message_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 86 | `sender_id` INT NOT NULL, |
| 87 | `receiver_id` INT NOT NULL, |
| 88 | `content` TEXT NOT NULL, |
| 89 | `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 90 | `is_read` BOOLEAN DEFAULT false, |
| 91 | FOREIGN KEY (`sender_id`) REFERENCES `users`(`user_id`), |
| 92 | FOREIGN KEY (`receiver_id`) REFERENCES `users`(`user_id`) |
夜雨声烦 | 4527a72 | 2025-04-23 17:04:25 +0800 | [diff] [blame] | 93 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 94 | -- 评论表(保持不变) |
夜雨声烦 | 4527a72 | 2025-04-23 17:04:25 +0800 | [diff] [blame] | 95 | CREATE TABLE IF NOT EXISTS `comments` ( |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 96 | `comment_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 97 | `post_id` INT NOT NULL, |
| 98 | `user_id` INT NOT NULL, |
| 99 | `parent_comment_id` INT DEFAULT NULL, |
| 100 | `content` TEXT NOT NULL, |
| 101 | `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 102 | FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`), |
| 103 | FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`), |
| 104 | FOREIGN KEY (`parent_comment_id`) REFERENCES `comments`(`comment_id`), |
| 105 | INDEX `idx_post_id` (`post_id`) -- 新增评论帖子索引 |
夜雨声烦 | 4527a72 | 2025-04-23 17:04:25 +0800 | [diff] [blame] | 106 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 107 | -- 帖子点赞表(保持不变) |
| 108 | CREATE TABLE IF NOT EXISTS `post_likes` ( |
| 109 | `user_id` INT NOT NULL, |
| 110 | `post_id` INT NOT NULL, |
| 111 | `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 112 | PRIMARY KEY (`user_id`, `post_id`), |
| 113 | FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`), |
| 114 | FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) |
夜雨声烦 | 0206359 | 2025-04-23 18:10:00 +0800 | [diff] [blame] | 115 | ); |
夜雨声烦 | 368e356 | 2025-04-24 01:49:46 +0800 | [diff] [blame] | 116 | -- 帖子浏览记录表(新增复合索引) |
| 117 | CREATE TABLE IF NOT EXISTS `post_views` ( |
| 118 | `view_id` INT AUTO_INCREMENT PRIMARY KEY, |
| 119 | `user_id` INT NOT NULL, |
| 120 | `post_id` INT NOT NULL, |
| 121 | `view_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 122 | FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`), |
| 123 | FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`), |
| 124 | INDEX `idx_user_view_time` (`user_id`, `view_time` DESC) -- 新增用户浏览时间索引 |
夜雨声烦 | f77d813 | 2025-04-24 19:31:18 +0800 | [diff] [blame] | 125 | ); |
wuchimedes | 8a576e0 | 2025-05-13 17:50:46 +0800 | [diff] [blame] | 126 | CREATE TABLE IF NOT EXISTS user_tag_preference ( |
夜雨声烦 | f77d813 | 2025-04-24 19:31:18 +0800 | [diff] [blame] | 127 | user_id INT NOT NULL COMMENT '用户ID', |
| 128 | tag_id INT NOT NULL COMMENT '标签ID', |
| 129 | weight DOUBLE DEFAULT 1.0 COMMENT '偏好权重(浏览越多权重越高)', |
| 130 | last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间', |
| 131 | PRIMARY KEY (user_id, tag_id), |
| 132 | FOREIGN KEY (user_id) REFERENCES users(user_id), |
| 133 | FOREIGN KEY (tag_id) REFERENCES tags(tag_id) |
22301071 | 8e412ad | 2025-04-24 22:24:51 +0800 | [diff] [blame] | 134 | ); |