post_rating
Change-Id: Ia1a6fb3f87b793a6307046e36951c1fb36b213c8
diff --git a/src/main/resources/schema.sql b/src/main/resources/schema.sql
index c0659b3..f6d061b 100644
--- a/src/main/resources/schema.sql
+++ b/src/main/resources/schema.sql
@@ -6,11 +6,22 @@
`email` VARCHAR(255) NOT NULL UNIQUE,
`passkey` VARCHAR(255) NOT NULL UNIQUE
);
--- 种子表（保持不变）
+-- 用户统计表
+CREATE TABLE IF NOT EXISTS `user_stats` (
+ user_id INT PRIMARY KEY,
+ passkey VARCHAR(255) NOT NULL UNIQUE,
+ total_upload FLOAT NOT NULL DEFAULT 0,
+ total_download FLOAT NOT NULL DEFAULT 0,
+ last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ FOREIGN KEY (user_id) REFERENCES users(user_id),
+ FOREIGN KEY (passkey) REFERENCES users(passkey)
+);
+-- 种子表
CREATE TABLE IF NOT EXISTS `torrents` (
`torrent_id` INT AUTO_INCREMENT PRIMARY KEY,
`user_id` INT NOT NULL,
`torrent_name` VARCHAR(255) NOT NULL,
+ `file_path` VARCHAR(255) NOT NULL,
`info_hash` BINARY(20) NOT NULL,
`file_size` FLOAT NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
@@ -36,17 +47,21 @@
`view_count` INT DEFAULT 0 COMMENT '浏览数',
`post_title` VARCHAR(255) NOT NULL,
`post_content` TEXT NOT NULL,
+ `torrent_id` INT DEFAULT 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 '最后热度计算时间',
+ `average_rating` DECIMAL(3,2) DEFAULT 0.00 COMMENT '帖子平均评分',
+ `rating_count` INT DEFAULT 0 COMMENT '总评分人数';
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`torrent_id`) REFERENCES `torrents`(`torrent_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,
+ `tag_id` INT PRIMARY KEY,
+ `tag_name` VARCHAR(255) NOT NULL,
`parent_id` INT DEFAULT NULL,
FOREIGN KEY (`parent_id`) REFERENCES `tags`(`tag_id`)
);
@@ -110,7 +125,7 @@
FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
INDEX `idx_user_view_time` (`user_id`, `view_time` DESC) -- 新增用户浏览时间索引
);
-CREATE TABLE user_tag_preference (
+CREATE TABLE IF NOT EXISTS user_tag_preference (
user_id INT NOT NULL COMMENT '用户ID',
tag_id INT NOT NULL COMMENT '标签ID',
weight DOUBLE DEFAULT 1.0 COMMENT '偏好权重（浏览越多权重越高）',
@@ -118,4 +133,14 @@
PRIMARY KEY (user_id, tag_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
+);
+CREATE TABLE IF NOT EXISTS `post_ratings` (
+ `user_id` INT NOT NULL COMMENT '用户ID',
+ `post_id` INT NOT NULL COMMENT '帖子ID',
+ `rating` TINYINT NOT NULL CHECK (`rating` BETWEEN 1 AND 5) COMMENT '评分值（1-5）',
+ `rated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '评分时间',
+ PRIMARY KEY (`user_id`, `post_id`), -- 确保每个用户对同一帖子只能评分一次
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
+ FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
+ INDEX idx_post_ratings_post_id ON post_ratings (post_id)
);
\ No newline at end of file