MZC/KnowMathBot/backend/app/db.py

"""
FastAPI + SQLModel(SQLAlchemy)操作关系型数据库:引擎创建、启动时建表与轻量迁移、请求级Session生成。
"""
from sqlalchemy import text
from sqlmodel import Session, SQLModel, create_engine
from app.core.config import settings

#建立与数据库的连接通道
engine = create_engine(settings.database_url, echo=False)

def init_db() -> None:
    #应用启动时建表并对已有数据库进行维护
    SQLModel.metadata.create_all(engine)
    user_table = "`user`" if engine.dialect.name == "mysql" else '"user"'
    with Session(engine) as session:
        session.exec(
            text(f"UPDATE {user_table} SET role = :to_role WHERE role = :from_role"),
            params={"to_role": "teacher", "from_role": "admin"},
        )
        # MySQL 专用：扩长对话字段、为旧库 exercise 表补列
        if engine.dialect.name == "mysql":
            session.exec(text("ALTER TABLE chatmessage MODIFY COLUMN content LONGTEXT"))
            ddl_statements = [
                "ALTER TABLE exercise ADD COLUMN question_type VARCHAR(32) NULL",
                "ALTER TABLE exercise ADD COLUMN correct_answer LONGTEXT NULL",
                "ALTER TABLE exercise ADD COLUMN resolution LONGTEXT NULL",
                "ALTER TABLE exercise ADD COLUMN knowledge_grade VARCHAR(64) NULL",
                "ALTER TABLE exercise ADD COLUMN knowledge_category VARCHAR(128) NULL",
                "ALTER TABLE exercise ADD COLUMN knowledge_point_name VARCHAR(255) NULL",
                "ALTER TABLE exercise ADD COLUMN weight INT NOT NULL DEFAULT 0",
            ]
            for ddl in ddl_statements:
                try:
                    session.exec(text(ddl))
                except Exception:
                    pass
            try:
                session.exec(
                    text(
                        "ALTER TABLE exercise MODIFY COLUMN question_type VARCHAR(32) NULL "
                        "COMMENT '题目类型：选择、填空、计算、判断'"
                    )
                )
            except Exception:
                pass
        session.commit()

def get_session():
    #每个请求注入一个数据库会话，请求结束自动关闭。
    with Session(engine) as session:
        yield session