|
|
# 接口文档
|
|
|
|
|
|
### 数据库设计
|
|
|
|
|
|
用户表 (users)
|
|
|
用于存储用户信息，支持登录、注册、找回密码等功能，并包含用户状态。
|
|
|
|
|
|
Sql
|
|
|
|
|
|
```
|
|
|
CREATE TABLE users (
|
|
|
user_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|
|
password_hash VARCHAR(255) NOT NULL, -- 建议使用安全哈希算法存储密码
|
|
|
status ENUM('admin', 'normal', 'blocked', 'deactivated', 'pending') DEFAULT 'pending',
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
activation_token VARCHAR(255), -- 用于激活账户或重置密码
|
|
|
reset_password_token VARCHAR(255), -- 密码重置令牌
|
|
|
reset_password_expires TIMESTAMP -- 密码重置令牌有效期
|
|
|
);
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
管理员表 (administrators)
|
|
|
用于标识哪些用户是管理员。这里假设如果用户的状态为admin，则不需要再额外记录到administrators表中，简化了数据库结构。
|
|
|
|
|
|
Sql
|
|
|
|
|
|
```
|
|
|
-- 如果需要单独维护管理员列表，可以保留此表；否则可以根据用户状态判断是否为管理员。
|
|
|
CREATE TABLE administrators (
|
|
|
admin_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
user_id INT NOT NULL UNIQUE,
|
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
|
|
|
);
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
文章表 (posts)
|
|
|
用于存储用户发布的文章。注意：只有状态为normal和admin的用户才能发布文章。
|
|
|
|
|
|
Sql
|
|
|
|
|
|
```
|
|
|
CREATE TABLE posts (
|
|
|
post_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
user_id INT NOT NULL,
|
|
|
title VARCHAR(255) NOT NULL,
|
|
|
content TEXT NOT NULL,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
|
|
|
);
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
点赞点踩表 (reactions)
|
|
|
记录用户对文章的点赞和点踩操作。只有状态为normal和admin的用户才能进行这些互动。
|
|
|
|
|
|
Sql
|
|
|
|
|
|
```
|
|
|
CREATE TABLE reactions (
|
|
|
reaction_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
post_id INT NOT NULL,
|
|
|
user_id INT NOT NULL,
|
|
|
type ENUM('like', 'dislike') NOT NULL,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
|
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
|
|
|
UNIQUE (post_id, user_id) -- 每个用户对每篇文章只能有一个反应
|
|
|
);
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
公告表 (announcements)
|
|
|
用于发布站内公告。只有状态为admin的用户可以发布或编辑公告。
|
|
|
|
|
|
Sql
|
|
|
|
|
|
|
|
|
```sql
|
|
|
CREATE TABLE announcements (
|
|
|
announcement_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
admin_id INT NOT NULL,
|
|
|
title VARCHAR(255) NOT NULL,
|
|
|
content TEXT NOT NULL,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
is_published BOOLEAN DEFAULT FALSE,
|
|
|
FOREIGN KEY (admin_id) REFERENCES users(user_id) ON DELETE CASCADE -- 直接引用用户表中的user_id
|
|
|
);
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
应用逻辑注意事项
|
|
|
权限控制：在应用程序逻辑中，确保根据用户的 status 字段来控制不同操作的权限。例如，只有 admin 用户可以发布或编辑公告，而 blocked 或 deactivated 用户不能执行任何操作。
|
|
|
状态转换：定义清晰的状态转换规则。例如，从 pending 转换为 normal 需要管理员审批；blocked 用户不能自行恢复状态，需由管理员处理等。
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ALTER TABLE reactions ADD CONSTRAINT unique_user_post_reaction UNIQUE (post_id, user_id);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
### 新增评论表
|
|
|
|
|
|
CREATE TABLE comments ( comment_id INT AUTO_INCREMENT PRIMARY KEY, post_id INT NOT NULL, user_id INT NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted BOOLEAN DEFAULT FALSE, -- 标记评论是否被删除，默认不删除 FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE );
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
