# models.py
from datetime import datetime, timezone
from sqlalchemy import (
Column,
Integer,
String,
Text,
Boolean,
DateTime,
ForeignKey,
Enum,
Index,
UniqueConstraint,
)
from sqlalchemy.orm import declarative_base, relationship
from typing import Optional
Base = declarative_base()
def utcnow():
return datetime.now(timezone.utc)
class Department(Base):
__tablename__ = "departments"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), unique=True, nullable=False, index=True)
description = Column(Text, nullable=True)
created_at = Column(DateTime, default=utcnow)
# 关系
users = relationship("User", back_populates="department")
rooms = relationship("Room", back_populates="department")
class Character(Base):
__tablename__ = "characters"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), unique=True, nullable=False, index=True)
trait = Column(Text, nullable=False)
avatar_url = Column(String(255), default="/static/avatars/ai_default.png", nullable=False)
created_at = Column(DateTime, default=utcnow)
# 关系
shares = relationship("Share", back_populates="ai_character")
conversations = relationship("UserConversation", back_populates="character")
rooms = relationship("Room", back_populates="ai_character")
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
account = Column(String(50), unique=True, nullable=False, index=True)
password = Column(String(255), nullable=False)
role = Column(Enum("user", "dept_admin", "admin"), nullable=False, default="user")
department_id = Column(Integer, ForeignKey("departments.id"), nullable=True)
created_at = Column(DateTime, default=utcnow)
# 关系
department = relationship("Department", back_populates="users")
profile = relationship("UserProfile", uselist=False, back_populates="user", cascade="all, delete-orphan")
rooms_created = relationship("Room", back_populates="creator")
room_memberships = relationship("RoomMember", back_populates="user")
shares = relationship("Share", back_populates="author")
comments = relationship("Comment", back_populates="commenter")
search_records = relationship("SearchRecord", back_populates="user")
likes = relationship("ShareLike", back_populates="user")
conversations = relationship("UserConversation", back_populates="user")
class UserProfile(Base):
__tablename__ = "user_profiles"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, unique=True)
personality = Column(Text, nullable=True)
role_setting = Column(Text, nullable=True)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow)
# 关系
user = relationship("User", back_populates="profile")
class Share(Base):
__tablename__ = "shares"
id = Column(Integer, primary_key=True, index=True)
title = Column(String(200), nullable=False)
content = Column(Text, nullable=False)
author_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
is_public = Column(Boolean, default=True, index=True)
type = Column(Enum("public", "private", "dept"), nullable=False, default="public")
ai_character_id = Column(Integer, ForeignKey("characters.id"), nullable=True, index=True)
view_count = Column(Integer, default=0)
like_count = Column(Integer, default=0)
comment_count = Column(Integer, default=0)
created_at = Column(DateTime, default=utcnow)
# 关系
author = relationship("User", back_populates="shares")
ai_character = relationship("Character", back_populates="shares")
likes = relationship("ShareLike", back_populates="share", cascade="all, delete-orphan")
comments = relationship("Comment", back_populates="share", cascade="all, delete-orphan")
class SearchRecord(Base):
__tablename__ = "search_records"
id = Column(Integer, primary_key=True, index=True)
keyword = Column(String(100), nullable=False, index=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=True, index=True)
search_time = Column(DateTime, default=utcnow)
# 关系
user = relationship("User", back_populates="search_records")
# 全文索引（需手动创建）
__table_args__ = (
Index("idx_search_time", "search_time"),
Index("idx_user_id_time", "user_id", "search_time"),
)
class Room(Base):
__tablename__ = "rooms"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), nullable=False)
type = Column(Enum("public", "dept", "ai"), nullable=False) # public, dept, ai
dept_id = Column(Integer, ForeignKey("departments.id"), nullable=True)
ai_character_id = Column(Integer, ForeignKey("characters.id"), nullable=True)
description = Column(Text, nullable=True)
creator_id = Column(Integer, ForeignKey("users.id"), nullable=False)
created_at = Column(DateTime, default=utcnow)
# 关系
creator = relationship("User", back_populates="rooms_created")
department = relationship("Department", back_populates="rooms")
ai_character = relationship("Character", back_populates="rooms")
members = relationship("RoomMember", back_populates="room", cascade="all, delete-orphan")
messages = relationship("RoomMessage", back_populates="room", cascade="all, delete-orphan")
class RoomMember(Base):
__tablename__ = "room_members"
id = Column(Integer, primary_key=True)
room_id = Column(Integer, ForeignKey("rooms.id"), nullable=False, index=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
joined_at = Column(DateTime, default=utcnow)
# 唯一性约束
__table_args__ = (UniqueConstraint("room_id", "user_id", name="uk_room_user"),)
# 关系
room = relationship("Room", back_populates="members")
user = relationship("User", back_populates="room_memberships")
class RoomMessage(Base):
__tablename__ = "room_messages"
id = Column(Integer, primary_key=True)
room_id = Column(Integer, ForeignKey("rooms.id"), nullable=False, index=True)
sender_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
content = Column(Text, nullable=False)
sent_at = Column(DateTime, default=utcnow)
# 关系
room = relationship("Room", back_populates="messages")
sender = relationship("User")
class Comment(Base):
__tablename__ = "comments"
id = Column(Integer, primary_key=True)
share_id = Column(Integer, ForeignKey("shares.id"), nullable=False, index=True)
commenter_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
parent_id = Column(Integer, ForeignKey("comments.id"), nullable=True, index=True)
content = Column(Text, nullable=False)
created_at = Column(DateTime, default=utcnow)
# 子评论列表（"一" 的那一边）
replies = relationship(
"Comment",
back_populates="parent", # 不用 backref，显式控制
lazy="selectin",
cascade="all, delete-orphan", # ✅ 正确位置：父级控制子级生命周期
foreign_keys=[parent_id] # 明确指定外键
)
# 父评论引用（"多" 的那一边）
parent = relationship(
"Comment",
back_populates="replies",
remote_side=[id], # 标记谁是“根”
lazy="select"
)
# 其他关系保持不变...
share = relationship("Share", back_populates="comments")
commenter = relationship("User", back_populates="comments")
class ShareLike(Base):
__tablename__ = "share_likes"
id = Column(Integer, primary_key=True)
share_id = Column(Integer, ForeignKey("shares.id"), nullable=False, index=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
liked_at = Column(DateTime, default=utcnow)
# 唯一性约束
__table_args__ = (UniqueConstraint("share_id", "user_id", name="uk_share_user"),)
# 关系
share = relationship("Share", back_populates="likes")
user = relationship("User", back_populates="likes")
class UserConversation(Base):
__tablename__ = "user_conversations"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
character_id = Column(Integer, ForeignKey("characters.id"), nullable=False, index=True)
user_message = Column(Text, nullable=False)
ai_message = Column(Text, nullable=False)
timestamp = Column(DateTime, default=utcnow)
# 关系
user = relationship("User", back_populates="conversations")
character = relationship("Character", back_populates="conversations")
# 复合索引
__table_args__ = (
Index("idx_user_char_time", "user_id", "character_id", "timestamp"),
)
# schemas.py
from pydantic import BaseModel, Field, field_validator
from typing import Optional, List, Dict, Any
from datetime import datetime, date
from enum import Enum as PyEnum
# ======================
# 公共枚举类
# ======================
class RoleType(PyEnum):
user = "user"
dept_admin = "dept_admin"
admin = "admin"
class RoomType(PyEnum):
public = "public"
dept = "dept"
ai = "ai"
class ShareType(PyEnum):
public = "public"
private = "private"
dept = "dept"
# ======================
# 用户相关 Schema
# ======================
class UserCreate(BaseModel):
account: str = Field(..., min_length=1, max_length=50, description="用户名（学号或邮箱前缀）")
password: str = Field(..., min_length=6, max_length=128, description="登录密码")
department_id: Optional[int] = Field(None, ge=1, description="所属院系ID")
@field_validator("password")
def validate_password(cls, v):
if len(v) < 6:
raise ValueError("密码至少6位")
return v
class UserUpdate(BaseModel):
password: Optional[str] = Field(None, min_length=6, max_length=128)
department_id: Optional[int] = Field(None, ge=1)
class UserProfile(BaseModel):
personality: Optional[str] = None
role_setting: Optional[str] = None
updated_at: datetime
class UserOut(BaseModel):
id: int
account: str
role: RoleType
department_id: Optional[int]
created_at: datetime
class Config:
arbitrary_types_allowed = True
class UserDetailOut(UserOut):
department_name: Optional[str] = None
profile: Optional[UserProfile] = None
# ======================
# 院系相关 Schema
# ======================
class DepartmentCreate(BaseModel):
name: str = Field(..., min_length=1, max_length=100)
description: Optional[str] = Field("", max_length=500)
class DepartmentOut(BaseModel):
id: int
name: str
description: Optional[str]
created_at: datetime
class Config:
arbitrary_types_allowed = True
# ======================
# AI角色相关 Schema
# ======================
class CharacterBase(BaseModel):
id: int
name: str
trait: str
avatar_url: str
created_at: datetime
class Config:
arbitrary_types_allowed = True
class CharacterCreate(BaseModel):
name: str = Field(..., min_length=1, max_length=100)
trait: str = Field(..., min_length=1)
avatar_url: Optional[str] = "/static/avatars/ai_default.png"
class CharacterUpdate(BaseModel):
name: Optional[str] = Field(None, min_length=1, max_length=100)
trait: Optional[str] = Field(None, min_length=1)
avatar_url: Optional[str] = None
# ======================
# 聊天室相关 Schema
# ======================
class RoomCreate(BaseModel):
name: str = Field(..., min_length=1, max_length=100)
type: RoomType
dept_id: Optional[int] = None
ai_character_id: Optional[int] = None
description: Optional[str] = Field(None, max_length=500)
class RoomOut(BaseModel):
id: int
name: str
type: RoomType
dept_id: Optional[int]
ai_character_id: Optional[int]
description: Optional[str]
creator_id: int
created_at: datetime
class Config:
arbitrary_types_allowed = True
class MessageCreate(BaseModel):
content: str = Field(..., min_length=1, max_length=2000)
class MessageOut(BaseModel):
id: int
room_id: int
sender_id: int
content: str
sent_at: datetime
class Config:
arbitrary_types_allowed = True
# ======================
# 分享相关 Schema
# ======================
class ShareCreate(BaseModel):
title: str = Field(..., min_length=1, max_length=200)
content: str = Field(..., min_length=1)
is_public: bool = True
type: ShareType = ShareType.public
ai_character_id: Optional[int] = None
class ShareUpdate(BaseModel):
title: Optional[str] = Field(None, min_length=1, max_length=200)
content: Optional[str] = Field(None, min_length=1)
is_public: Optional[bool] = None
type: Optional[ShareType] = None
ai_character_id: Optional[int] = None
class CommentCreate(BaseModel):
content: str = Field(..., min_length=1, max_length=1000)
parent_id: Optional[int] = None
class CommentOut(BaseModel):
id: int
share_id: int
commenter_id: int
parent_id: Optional[int]
content: str
created_at: datetime
commenter: UserOut # 嵌套用户信息
replies: List["CommentOut"] = [] # 子评论（递归定义）
class Config:
arbitrary_types_allowed = True
CommentOut.model_rebuild() # 解决递归引用问题
class ShareLikeOut(BaseModel):
share_id: int
user_id: int
liked_at: datetime
class Config:
arbitrary_types_allowed = True
class ShareOut(BaseModel):
id: int
title: str
content: str
author_id: int
is_public: bool
type: ShareType
ai_character_id: Optional[int]
view_count: int
like_count: int
comment_count: int
created_at: datetime
# 关联字段（非数据库字段，来自 JOIN 查询）
author: UserOut
ai_character: Optional[CharacterBase] = None
comments: List[CommentOut] = []
likes: List[ShareLikeOut] = []
class Config:
arbitrary_types_allowed = True
# ======================
# 搜索相关 Schema
# ======================
class SearchRecordOut(BaseModel):
id: int
keyword: str
user_id: Optional[int]
search_time: datetime
class Config:
arbitrary_types_allowed = True
class HotKeyword(BaseModel):
keyword: str
count: int
class RecommendationItem(ShareOut):
"""推荐项复用 Share 结构"""
pass
class RecommendationResponse(BaseModel):
method_used: str
recommended_count: int
items: List[RecommendationItem]
# ======================
# 统计相关 Schema
# ======================
class UserRoleDistribution(BaseModel):
user: int = 0
dept_admin: int = 0
admin: int = 0
class UserStatsResponse(BaseModel):
total_user: int = 0
new_user: int = 0
role_distribution: UserRoleDistribution
class AICharacterDistribution(BaseModel):
model_config = {"extra": "allow"} # 动态键：character_id -> count
# 示例: {1: 45, 2: 30}
class ShareStatsResponse(BaseModel):
total_share: int = 0
total_like: int = 0
total_comment: int = 0
ai_character_distribution: AICharacterDistribution
# ======================
# 聊天相关 Schema
# ======================
class ChatRequest(BaseModel):
character_id: int = Field(..., ge=1)
message: str = Field(..., min_length=1)
@field_validator("message")
def trim_message(cls, v):
return v.strip()
# ======================
# 分页通用结构
# ======================
class PaginatedResponse(BaseModel):
total: int
page: int
size: int
items: List[Any]
class Config:
arbitrary_types_allowed = True
统一一下两个模型的结构
博客主要提及在flask框架里创建表时修改表名的操作，且指出相关代码应放在models.py文件下。
扫一扫
923
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
