brunoandClaude
1f54225066
- 添加完整的数据库初始化文档 (docs/DATABASE_SETUP.md) - 添加快速开始指南 (docs/QUICK_START.md) - 更新 requirements.txt 添加缺失的依赖 (requests, python-docx) - 优化 .gitignore 配置，保留 uploads 目录结构 - 创建 uploads 目录结构 (images/, pdfs/) - 支持 PostgreSQL 和 SQLite 两种数据库配置 数据库文档包含： - PostgreSQL 和 SQLite 安装配置指南 - 完整的表结构说明 - 管理员账户初始化步骤 - 数据库迁移说明 - 常见问题排查 - 备份恢复方案 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <[email protected]>
487 lines
9.8 KiB
Markdown
487 lines
9.8 KiB
Markdown
# 数据库初始化指南
|
||
|
||
本文档提供完整的数据库初始化和配置指南，包括 PostgreSQL 和 SQLite 两种数据库的设置方法。
|
||
|
||
## 目录
|
||
|
||
- [1. 环境要求](#1-环境要求)
|
||
- [2. PostgreSQL 数据库设置（推荐生产环境）](#2-postgresql-数据库设置推荐生产环境)
|
||
- [3. SQLite 数据库设置（开发环境）](#3-sqlite-数据库设置开发环境)
|
||
- [4. 数据库表结构说明](#4-数据库表结构说明)
|
||
- [5. 初始化管理员账户](#5-初始化管理员账户)
|
||
- [6. 数据库迁移](#6-数据库迁移)
|
||
- [7. 常见问题](#7-常见问题)
|
||
|
||
---
|
||
|
||
## 1. 环境要求
|
||
|
||
### 基础软件
|
||
- Python 3.8+
|
||
- PostgreSQL 14+ (如使用 PostgreSQL)
|
||
- Node.js 16+ (前端)
|
||
|
||
### Python 依赖
|
||
项目依赖已在 `backend/requirements.txt` 中定义，主要包括：
|
||
- FastAPI
|
||
- SQLAlchemy
|
||
- psycopg2-binary (PostgreSQL 驱动)
|
||
- python-dotenv
|
||
|
||
---
|
||
|
||
## 2. PostgreSQL 数据库设置（推荐生产环境）
|
||
|
||
### 2.1 安装 PostgreSQL
|
||
|
||
**macOS (使用 Homebrew):**
|
||
```bash
|
||
brew install postgresql@14
|
||
brew services start postgresql@14
|
||
```
|
||
|
||
**Ubuntu/Debian:**
|
||
```bash
|
||
sudo apt update
|
||
sudo apt install postgresql postgresql-contrib
|
||
sudo systemctl start postgresql
|
||
sudo systemctl enable postgresql
|
||
```
|
||
|
||
**Windows:**
|
||
从官网下载安装: https://www.postgresql.org/download/windows/
|
||
|
||
### 2.2 创建数据库和用户
|
||
|
||
使用 PostgreSQL 命令行创建数据库：
|
||
|
||
```bash
|
||
# 连接到 PostgreSQL
|
||
psql -U postgres
|
||
|
||
# 在 psql 命令行中执行以下命令：
|
||
CREATE DATABASE wordmaster;
|
||
CREATE USER wordmaster_user WITH PASSWORD 'your_secure_password';
|
||
GRANT ALL PRIVILEGES ON DATABASE wordmaster TO wordmaster_user;
|
||
|
||
# 退出 psql
|
||
\q
|
||
```
|
||
|
||
**更简单的方式（使用默认 postgres 用户）:**
|
||
```bash
|
||
# 创建数据库
|
||
psql -U postgres -c "CREATE DATABASE wordmaster;"
|
||
|
||
# 验证数据库创建
|
||
psql -U postgres -c "\l" | grep wordmaster
|
||
```
|
||
|
||
### 2.3 配置环境变量
|
||
|
||
在项目根目录创建 `.env` 文件：
|
||
|
||
```bash
|
||
# 复制示例配置文件
|
||
cp backend/.env.example .env
|
||
```
|
||
|
||
编辑 `.env` 文件，配置数据库连接：
|
||
|
||
```env
|
||
# PostgreSQL 配置
|
||
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/wordmaster
|
||
|
||
# 或使用自定义用户
|
||
# DATABASE_URL=postgresql://wordmaster_user:your_secure_password@localhost:5432/wordmaster
|
||
|
||
# 安全配置
|
||
SECRET_KEY=your-super-secret-key-change-in-production
|
||
ALGORITHM=HS256
|
||
ACCESS_TOKEN_EXPIRE_MINUTES=30
|
||
```
|
||
|
||
### 2.4 初始化数据库表
|
||
|
||
数据库表会在首次启动后端服务时自动创建：
|
||
|
||
```bash
|
||
cd backend
|
||
python3 create_admin.py
|
||
```
|
||
|
||
或者手动启动后端让其自动创建：
|
||
|
||
```bash
|
||
cd backend
|
||
source venv/bin/activate # 激活虚拟环境
|
||
python main.py
|
||
```
|
||
|
||
### 2.5 验证数据库连接
|
||
|
||
```bash
|
||
# 连接数据库
|
||
psql -U postgres -d wordmaster
|
||
|
||
# 查看所有表
|
||
\dt
|
||
|
||
# 应该看到以下表：
|
||
# - users
|
||
# - words
|
||
# - user_word_progress
|
||
# - papers
|
||
# - questions
|
||
# - question_collections
|
||
# - mistakes
|
||
# - review_logs
|
||
```
|
||
|
||
---
|
||
|
||
## 3. SQLite 数据库设置（开发环境）
|
||
|
||
SQLite 无需安装，非常适合开发和测试环境。
|
||
|
||
### 3.1 配置环境变量
|
||
|
||
在 `.env` 文件中配置：
|
||
|
||
```env
|
||
# SQLite 配置（开发环境）
|
||
DATABASE_URL=sqlite:///./wordmaster.db
|
||
|
||
SECRET_KEY=dev-secret-key-change-in-production
|
||
ALGORITHM=HS256
|
||
ACCESS_TOKEN_EXPIRE_MINUTES=30
|
||
```
|
||
|
||
### 3.2 初始化数据库
|
||
|
||
```bash
|
||
cd backend
|
||
python3 create_admin.py
|
||
```
|
||
|
||
数据库文件将自动创建在 `backend/wordmaster.db`
|
||
|
||
---
|
||
|
||
## 4. 数据库表结构说明
|
||
|
||
### 4.1 核心表结构
|
||
|
||
#### users - 用户表
|
||
```sql
|
||
CREATE TABLE users (
|
||
id SERIAL PRIMARY KEY,
|
||
username VARCHAR UNIQUE NOT NULL,
|
||
email VARCHAR UNIQUE NOT NULL,
|
||
hashed_password VARCHAR NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
#### words - 单词表
|
||
```sql
|
||
CREATE TABLE words (
|
||
id SERIAL PRIMARY KEY,
|
||
word VARCHAR NOT NULL,
|
||
translation TEXT,
|
||
difficulty INTEGER DEFAULT 1,
|
||
category VARCHAR,
|
||
example_sentence TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
#### user_word_progress - 用户单词学习进度
|
||
```sql
|
||
CREATE TABLE user_word_progress (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INTEGER REFERENCES users(id),
|
||
word_id INTEGER REFERENCES words(id),
|
||
ease_factor FLOAT DEFAULT 2.5,
|
||
interval INTEGER DEFAULT 0,
|
||
repetitions INTEGER DEFAULT 0,
|
||
next_review_date TIMESTAMP,
|
||
last_review_date TIMESTAMP,
|
||
status VARCHAR DEFAULT 'learning'
|
||
);
|
||
```
|
||
|
||
#### papers - 试卷表
|
||
```sql
|
||
CREATE TABLE papers (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INTEGER REFERENCES users(id),
|
||
title VARCHAR NOT NULL,
|
||
subject VARCHAR,
|
||
file_path VARCHAR,
|
||
upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
description TEXT,
|
||
tags JSON
|
||
);
|
||
```
|
||
|
||
#### questions - 题目表
|
||
```sql
|
||
CREATE TABLE questions (
|
||
id SERIAL PRIMARY KEY,
|
||
paper_id INTEGER REFERENCES papers(id),
|
||
question_text TEXT NOT NULL,
|
||
question_type VARCHAR,
|
||
answer TEXT,
|
||
difficulty INTEGER,
|
||
page_number INTEGER,
|
||
page_spans JSON,
|
||
source_text TEXT,
|
||
images JSON,
|
||
formula JSON,
|
||
score FLOAT,
|
||
question_number VARCHAR,
|
||
auto_extracted BOOLEAN DEFAULT FALSE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
#### mistakes - 错题表
|
||
```sql
|
||
CREATE TABLE mistakes (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INTEGER REFERENCES users(id),
|
||
question_id INTEGER REFERENCES questions(id),
|
||
user_answer TEXT,
|
||
correct_answer TEXT,
|
||
notes TEXT,
|
||
leitner_box INTEGER DEFAULT 1,
|
||
next_review_date TIMESTAMP,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
#### review_logs - 复习记录表
|
||
```sql
|
||
CREATE TABLE review_logs (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INTEGER REFERENCES users(id),
|
||
mistake_id INTEGER REFERENCES mistakes(id),
|
||
review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
is_correct BOOLEAN,
|
||
time_spent INTEGER
|
||
);
|
||
```
|
||
|
||
#### question_collections - 题目集合
|
||
```sql
|
||
CREATE TABLE question_collections (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INTEGER REFERENCES users(id),
|
||
name VARCHAR NOT NULL,
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 5. 初始化管理员账户
|
||
|
||
### 5.1 使用脚本创建管理员
|
||
|
||
项目提供了便捷的管理员创建脚本：
|
||
|
||
```bash
|
||
cd backend
|
||
python3 create_admin.py
|
||
```
|
||
|
||
**默认管理员账户：**
|
||
- 用户名: `admin`
|
||
- 密码: `admin`
|
||
- 邮箱: `[email protected]`
|
||
|
||
### 5.2 修改管理员密码
|
||
|
||
如需修改密码，可以再次运行脚本（会更新密码）或通过 API 修改。
|
||
|
||
### 5.3 手动创建用户
|
||
|
||
也可以通过 PostgreSQL 命令行手动创建：
|
||
|
||
```bash
|
||
psql -U postgres -d wordmaster
|
||
```
|
||
|
||
```sql
|
||
-- 插入用户（密码需要使用 bcrypt 哈希）
|
||
-- 这里的哈希值对应密码 "admin"
|
||
INSERT INTO users (username, email, hashed_password, created_at)
|
||
VALUES (
|
||
'admin',
|
||
'[email protected]',
|
||
'$2b$12$KIX.qh0yRsYQUzH7EqQv1eRWZG5g9V.Lv3mK5yH9rJC.YmMvxhFWK',
|
||
CURRENT_TIMESTAMP
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 6. 数据库迁移
|
||
|
||
### 6.1 SQLite 迁移脚本
|
||
|
||
如果数据库结构发生变化，使用迁移脚本更新：
|
||
|
||
```bash
|
||
cd backend
|
||
python3 migrate_db.py
|
||
```
|
||
|
||
此脚本会为 `questions` 表添加新字段（如果缺失）。
|
||
|
||
### 6.2 PostgreSQL 迁移
|
||
|
||
PostgreSQL 使用 SQLAlchemy 的自动迁移：
|
||
|
||
```python
|
||
# 在 backend/main.py 中
|
||
Base.metadata.create_all(bind=engine)
|
||
```
|
||
|
||
首次启动时会自动创建所有表。
|
||
|
||
---
|
||
|
||
## 7. 常见问题
|
||
|
||
### 7.1 数据库连接失败
|
||
|
||
**问题**: `psycopg2.OperationalError: could not connect to server`
|
||
|
||
**解决方案**:
|
||
```bash
|
||
# 检查 PostgreSQL 是否运行
|
||
brew services list | grep postgresql
|
||
# 或
|
||
sudo systemctl status postgresql
|
||
|
||
# 启动 PostgreSQL
|
||
brew services start postgresql@14
|
||
# 或
|
||
sudo systemctl start postgresql
|
||
```
|
||
|
||
### 7.2 数据库不存在
|
||
|
||
**问题**: `database "wordmaster" does not exist`
|
||
|
||
**解决方案**:
|
||
```bash
|
||
psql -U postgres -c "CREATE DATABASE wordmaster;"
|
||
```
|
||
|
||
### 7.3 权限问题
|
||
|
||
**问题**: `permission denied for database wordmaster`
|
||
|
||
**解决方案**:
|
||
```bash
|
||
psql -U postgres
|
||
GRANT ALL PRIVILEGES ON DATABASE wordmaster TO postgres;
|
||
# 或授权给特定用户
|
||
GRANT ALL PRIVILEGES ON DATABASE wordmaster TO wordmaster_user;
|
||
```
|
||
|
||
### 7.4 表已存在错误
|
||
|
||
**问题**: `table "users" already exists`
|
||
|
||
这不是错误，说明表已经创建。可以忽略或删除数据库重新创建：
|
||
|
||
```bash
|
||
# 删除数据库（警告：会丢失所有数据）
|
||
psql -U postgres -c "DROP DATABASE wordmaster;"
|
||
psql -U postgres -c "CREATE DATABASE wordmaster;"
|
||
```
|
||
|
||
### 7.5 虚拟环境依赖问题
|
||
|
||
**问题**: `ModuleNotFoundError: No module named 'psycopg2'`
|
||
|
||
**解决方案**:
|
||
```bash
|
||
cd backend
|
||
source venv/bin/activate
|
||
pip install -r requirements.txt
|
||
```
|
||
|
||
---
|
||
|
||
## 8. 备份和恢复
|
||
|
||
### 8.1 PostgreSQL 备份
|
||
|
||
```bash
|
||
# 备份数据库
|
||
pg_dump -U postgres wordmaster > backup_$(date +%Y%m%d).sql
|
||
|
||
# 恢复数据库
|
||
psql -U postgres wordmaster < backup_20241127.sql
|
||
```
|
||
|
||
### 8.2 SQLite 备份
|
||
|
||
```bash
|
||
# 简单复制数据库文件
|
||
cp backend/wordmaster.db backend/wordmaster.db.backup
|
||
|
||
# 恢复
|
||
cp backend/wordmaster.db.backup backend/wordmaster.db
|
||
```
|
||
|
||
---
|
||
|
||
## 9. 生产环境建议
|
||
|
||
1. **更改默认密码**: 修改 `.env` 中的 `SECRET_KEY` 和数据库密码
|
||
2. **启用 SSL**: 配置 PostgreSQL SSL 连接
|
||
3. **定期备份**: 设置自动备份计划
|
||
4. **监控**: 使用 pg_stat_statements 监控数据库性能
|
||
5. **连接池**: 配置适当的数据库连接池大小
|
||
|
||
---
|
||
|
||
## 附录：完整初始化流程
|
||
|
||
### 一键初始化（PostgreSQL）
|
||
|
||
```bash
|
||
# 1. 安装 PostgreSQL
|
||
brew install postgresql@14
|
||
brew services start postgresql@14
|
||
|
||
# 2. 创建数据库
|
||
psql -U postgres -c "CREATE DATABASE wordmaster;"
|
||
|
||
# 3. 配置环境变量
|
||
cp backend/.env.example .env
|
||
# 编辑 .env 设置 DATABASE_URL
|
||
|
||
# 4. 安装 Python 依赖
|
||
cd backend
|
||
python3 -m venv venv
|
||
source venv/bin/activate
|
||
pip install -r requirements.txt
|
||
|
||
# 5. 创建表和管理员
|
||
python3 create_admin.py
|
||
|
||
# 6. 启动服务
|
||
cd ..
|
||
./start.sh
|
||
```
|
||
|
||
完成！系统已就绪，访问 http://localhost:3008 开始使用。
|