103 lines
3.8 KiB
Python
103 lines
3.8 KiB
Python
|
"""系统用户与用户-角色关联模型。"""
|
|||
|
|
|
|||
|
|
from datetime import datetime
|
|||
|
|
from typing import TYPE_CHECKING
|
|||
|
|
|
|||
|
|
from sqlalchemy import (
|
|||
|
|
DateTime,
|
|||
|
|
ForeignKey,
|
|||
|
|
Index,
|
|||
|
|
Integer,
|
|||
|
|
String,
|
|||
|
|
UniqueConstraint,
|
|||
|
|
text,
|
|||
|
|
)
|
|||
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|||
|
|
|
|||
|
|
from app.models.base import BIGINT_UNSIGNED, TINYINT_TYPE, Base, TimestampMixin, mysql_table_args
|
|||
|
|
|
|||
|
|
if TYPE_CHECKING: # 仅用于类型标注，避免运行期循环导入
|
|||
|
|
from app.models.role import Role
|
|||
|
|
|
|||
|
|
|
|||
|
|
class User(Base, TimestampMixin):
|
|||
|
|
"""系统登录账号。
|
|||
|
|
|
|||
|
|
与营销人员业务数据**严格分离**：营销人员是核算对象，数据来自 T+ 同步，
|
|||
|
|
其中多数不需要登录系统（内勤、离职人员、仅参与核算的业务员）。
|
|||
|
|
合并成一张表会让 T+ 全量同步覆盖掉 password，导致全员无法登录。
|
|||
|
|
|
|||
|
|
采用「删除即禁用」（status=0）而非软删除列：软删除与
|
|||
|
|
`uk_users_username` 唯一键会冲突（删除后的用户名无法重新启用）。
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
__tablename__ = "users"
|
|||
|
|
__table_args__ = mysql_table_args("系统用户表")
|
|||
|
|
|
|||
|
|
id: Mapped[int] = mapped_column(
|
|||
|
|
BIGINT_UNSIGNED, primary_key=True, autoincrement=True, comment="主键ID"
|
|||
|
|
)
|
|||
|
|
username: Mapped[str] = mapped_column(
|
|||
|
|
String(64), nullable=False, unique=True, comment="登录用户名"
|
|||
|
|
)
|
|||
|
|
password: Mapped[str] = mapped_column(
|
|||
|
|
String(100), nullable=False, comment="密码哈希（BCrypt，固定60字符）"
|
|||
|
|
)
|
|||
|
|
real_name: Mapped[str | None] = mapped_column(String(64), comment="真实姓名")
|
|||
|
|
phone: Mapped[str | None] = mapped_column(String(20), index=True, comment="手机号")
|
|||
|
|
email: Mapped[str | None] = mapped_column(String(128), comment="邮箱")
|
|||
|
|
status: Mapped[int] = mapped_column(
|
|||
|
|
TINYINT_TYPE, nullable=False, server_default="1", comment="状态：1启用 0禁用"
|
|||
|
|
)
|
|||
|
|
# 「离职即失效」的核心：每次鉴权比对 JWT 的 ver 声明与本列。
|
|||
|
|
# 相比只在 Redis 维护黑名单，这种方式在 Redis 不可用时依然有效
|
|||
|
|
token_version: Mapped[int] = mapped_column(
|
|||
|
|
Integer,
|
|||
|
|
nullable=False,
|
|||
|
|
server_default="1",
|
|||
|
|
comment="令牌版本号：改密/禁用/强制下线时+1，使已签发Token立即失效",
|
|||
|
|
)
|
|||
|
|
last_login_time: Mapped[datetime | None] = mapped_column(DateTime, comment="最后登录时间")
|
|||
|
|
remark: Mapped[str | None] = mapped_column(String(255), comment="备注")
|
|||
|
|
|
|||
|
|
user_roles: Mapped[list["UserRole"]] = relationship(
|
|||
|
|
back_populates="user", cascade="all, delete-orphan", lazy="selectin"
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
class UserRole(Base):
|
|||
|
|
"""用户-角色关联表。
|
|||
|
|
|
|||
|
|
不继承 TimestampMixin（关联关系不会被"更新"，只有建立与删除），
|
|||
|
|
但保留 create_time 以便审计「角色何时授予」。
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
__tablename__ = "user_roles"
|
|||
|
|
__table_args__ = mysql_table_args(
|
|||
|
|
"用户角色关联表",
|
|||
|
|
UniqueConstraint("user_id", "role_id", name="uk_user_roles_user_id_role_id"),
|
|||
|
|
Index("idx_user_roles_role_id", "role_id"),
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
id: Mapped[int] = mapped_column(
|
|||
|
|
BIGINT_UNSIGNED, primary_key=True, autoincrement=True, comment="主键ID"
|
|||
|
|
)
|
|||
|
|
user_id: Mapped[int] = mapped_column(
|
|||
|
|
BIGINT_UNSIGNED,
|
|||
|
|
ForeignKey("users.id", ondelete="CASCADE"),
|
|||
|
|
nullable=False,
|
|||
|
|
comment="用户ID（users.id）",
|
|||
|
|
)
|
|||
|
|
role_id: Mapped[int] = mapped_column(
|
|||
|
|
BIGINT_UNSIGNED,
|
|||
|
|
ForeignKey("roles.id", ondelete="CASCADE"),
|
|||
|
|
nullable=False,
|
|||
|
|
comment="角色ID（roles.id）",
|
|||
|
|
)
|
|||
|
|
create_time: Mapped[datetime] = mapped_column(
|
|||
|
|
DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
user: Mapped[User] = relationship(back_populates="user_roles")
|
|||
|
|
role: Mapped["Role"] = relationship(back_populates="user_roles", lazy="selectin")
|