RecommendByHot_scores
Change-Id: Icfef76f6ce21a60288c61d60ad1cd2d70045f953
diff --git a/src/main/resources/schema.sql b/src/main/resources/schema.sql
index d97701c..e5ddbb4 100644
--- a/src/main/resources/schema.sql
+++ b/src/main/resources/schema.sql
@@ -1,111 +1,122 @@
-CREATE DATABASE IF NOT EXISTS g8backend;
-USE g8backend;
-
+-- 用户表（保持不变）
CREATE TABLE IF NOT EXISTS `users` (
- user_id INT AUTO_INCREMENT PRIMARY KEY,
- user_name VARCHAR(255) NOT NULL,
- password VARCHAR(255) NOT NULL,
- email VARCHAR(255) NOT NULL UNIQUE,
- passkey VARCHAR(255) NOT NULL UNIQUE
+ `user_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `user_name` VARCHAR(255) NOT NULL,
+ `password` VARCHAR(255) NOT NULL,
+ `email` VARCHAR(255) NOT NULL UNIQUE,
+ `passkey` VARCHAR(255) NOT NULL UNIQUE
);
+-- 种子表（保持不变）
CREATE TABLE IF NOT EXISTS `torrents` (
- torrent_id INT AUTO_INCREMENT PRIMARY KEY,
- user_id INT NOT NULL,
- torrent_name VARCHAR(255) NOT NULL,
- info_hash BINARY(20) NOT NULL,
- file_size FLOAT NOT NULL,
- FOREIGN KEY (user_id) REFERENCES users(user_id)
+ `torrent_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `user_id` INT NOT NULL,
+ `torrent_name` VARCHAR(255) NOT NULL,
+ `info_hash` BINARY(20) NOT NULL,
+ `file_size` FLOAT NOT NULL,
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
);
+-- Peer表（保持不变）
CREATE TABLE IF NOT EXISTS `peers` (
- passkey VARCHAR(255) NOT NULL,
- info_hash BINARY(20) NOT NULL,
- peer_id VARCHAR(20) NOT NULL,
- ip_address VARCHAR(128) NOT NULL,
- port INT NOT NULL,
- uploaded FLOAT NOT NULL,
- downloaded FLOAT NOT NULL,
- last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- FOREIGN KEY (passkey) REFERENCES users(passkey),
- PRIMARY KEY (passkey, info_hash, peer_id)
+ `passkey` VARCHAR(255) NOT NULL,
+ `info_hash` BINARY(20) NOT NULL,
+ `peer_id` VARCHAR(20) NOT NULL,
+ `ip_address` VARCHAR(128) NOT NULL,
+ `port` INT NOT NULL,
+ `uploaded` FLOAT NOT NULL,
+ `downloaded` FLOAT NOT NULL,
+ `last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ FOREIGN KEY (`passkey`) REFERENCES `users`(`passkey`),
+ PRIMARY KEY (`passkey`, `info_hash`, `peer_id`)
);
+-- 帖子表（新增 hot_score 和 last_calculated 字段）
CREATE TABLE IF NOT EXISTS `posts` (
- post_id INT AUTO_INCREMENT PRIMARY KEY,
- user_id INT NOT NULL,
- hot_score DOUBLE DEFAULT 0.0,
- view_count INT DEFAULT 0,
- post_title VARCHAR(255) NOT NULL,
- post_content TEXT NOT NULL,
- post_type ENUM('resource', 'discussion') NOT NULL,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- FOREIGN KEY (user_id) REFERENCES users(user_id)
+ `post_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `user_id` INT NOT NULL,
+ `hot_score` DOUBLE DEFAULT 5.0 COMMENT '热度评分',
+ `view_count` INT DEFAULT 0 COMMENT '浏览数',
+ `post_title` VARCHAR(255) NOT NULL,
+ `post_content` TEXT NOT NULL,
+ `post_type` ENUM('resource', 'discussion') NOT NULL,
+ `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `last_calculated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后热度计算时间',
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
+ INDEX `idx_hot_score` (`hot_score`), -- 新增热度索引
+ INDEX `idx_post_type` (`post_type`) -- 新增类型索引
);
+-- 标签表（保持不变）
CREATE TABLE IF NOT EXISTS `tags`(
- tag_id INT AUTO_INCREMENT PRIMARY KEY,
- tag_name VARCHAR(255) NOT NULL UNIQUE,
- parent_id INT DEFAULT NULL,
- FOREIGN KEY (parent_id) REFERENCES tags(tag_id)
+ `tag_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `tag_name` VARCHAR(255) NOT NULL UNIQUE,
+ `parent_id` INT DEFAULT NULL,
+ FOREIGN KEY (`parent_id`) REFERENCES `tags`(`tag_id`)
);
+-- 帖子标签关联表（保持不变）
CREATE TABLE IF NOT EXISTS `post_tag` (
- post_id INT NOT NULL,
- tag_id INT NOT NULL,
- FOREIGN KEY (post_id) REFERENCES posts(post_id),
- FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
- PRIMARY KEY (post_id, tag_id)
+ `post_id` INT NOT NULL,
+ `tag_id` INT NOT NULL,
+ FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
+ FOREIGN KEY (`tag_id`) REFERENCES `tags`(`tag_id`),
+ PRIMARY KEY (`post_id`, `tag_id`)
);
--- 关注关系表
+-- 用户关注表（保持不变）
CREATE TABLE IF NOT EXISTS `user_follows` (
- follower_id INT NOT NULL,
- followed_id INT NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- FOREIGN KEY (follower_id) REFERENCES users(user_id),
- FOREIGN KEY (followed_id) REFERENCES users(user_id),
- PRIMARY KEY (follower_id, followed_id)
+ `follower_id` INT NOT NULL,
+ `followed_id` INT NOT NULL,
+ `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (`follower_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`followed_id`) REFERENCES `users`(`user_id`),
+ PRIMARY KEY (`follower_id`, `followed_id`)
);
--- 私信表
+-- 私信表（保持不变）
CREATE TABLE IF NOT EXISTS `private_messages` (
- message_id INT AUTO_INCREMENT PRIMARY KEY,
- sender_id INT NOT NULL,
- receiver_id INT NOT NULL,
- content TEXT NOT NULL,
- sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- is_read BOOLEAN DEFAULT false,
- FOREIGN KEY (sender_id) REFERENCES users(user_id),
- FOREIGN KEY (receiver_id) REFERENCES users(user_id)
+ `message_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `sender_id` INT NOT NULL,
+ `receiver_id` INT NOT NULL,
+ `content` TEXT NOT NULL,
+ `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ `is_read` BOOLEAN DEFAULT false,
+ FOREIGN KEY (`sender_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`receiver_id`) REFERENCES `users`(`user_id`)
);
+-- 评论表（保持不变）
CREATE TABLE IF NOT EXISTS `comments` (
- comment_id INT AUTO_INCREMENT PRIMARY KEY, -- 评论ID
- post_id INT NOT NULL, -- 所属帖子ID
- user_id INT NOT NULL, -- 评论用户ID
- parent_comment_id INT DEFAULT NULL, -- 父评论ID, 如果是顶级评论则为NULL
- content TEXT NOT NULL, -- 评论内容
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 评论时间
- FOREIGN KEY (post_id) REFERENCES posts(post_id), -- 关联帖子
- FOREIGN KEY (user_id) REFERENCES users(user_id), -- 关联用户
- FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id) -- 关联父评论
+ `comment_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `post_id` INT NOT NULL,
+ `user_id` INT NOT NULL,
+ `parent_comment_id` INT DEFAULT NULL,
+ `content` TEXT NOT NULL,
+ `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`parent_comment_id`) REFERENCES `comments`(`comment_id`),
+ INDEX `idx_post_id` (`post_id`) -- 新增评论帖子索引
);
-CREATE TABLE IF NOT EXISTS post_likes (
- user_id INT NOT NULL,
- post_id INT NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (user_id, post_id),
- CONSTRAINT fk_post FOREIGN KEY (post_id) REFERENCES posts(post_id),
- CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
+-- 帖子点赞表（保持不变）
+CREATE TABLE IF NOT EXISTS `post_likes` (
+ `user_id` INT NOT NULL,
+ `post_id` INT NOT NULL,
+ `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`user_id`, `post_id`),
+ FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
);
-CREATE TABLE IF NOT EXISTS post_views (
- view_id INT AUTO_INCREMENT PRIMARY KEY,
- user_id INT NOT NULL,
- post_id INT NOT NULL,
- view_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- FOREIGN KEY (user_id) REFERENCES users(user_id),
- FOREIGN KEY (post_id) REFERENCES posts(post_id)
-);
+-- 帖子浏览记录表（新增复合索引）
+CREATE TABLE IF NOT EXISTS `post_views` (
+ `view_id` INT AUTO_INCREMENT PRIMARY KEY,
+ `user_id` INT NOT NULL,
+ `post_id` INT NOT NULL,
+ `view_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
+ INDEX `idx_user_view_time` (`user_id`, `view_time` DESC) -- 新增用户浏览时间索引
+);
\ No newline at end of file