84 lines
3.0 KiB
Python
84 lines
3.0 KiB
Python
|
"""ORM 基类、命名约定与公共列。
|
|||
|
|
|
|||
|
|
命名约定按 PROJECT_STANDARDS 红线设置，使 Alembic autogenerate 产出的
|
|||
|
|
约束名自动符合规范（唯一键 `uk_表_字段`、索引 `idx_表_字段`），
|
|||
|
|
无需在每张表上手工指定 —— 手工指定迟早会漏。
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
from datetime import datetime
|
|||
|
|
|
|||
|
|
from sqlalchemy import DateTime, MetaData, text
|
|||
|
|
from sqlalchemy.dialects.mysql import BIGINT, TINYINT
|
|||
|
|
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
|
|||
|
|
|
|||
|
|
# 约束命名模板。`column_0_name` 取约束涉及的第一列，
|
|||
|
|
# 对单列唯一键/索引即为字段名，符合规范的 uk_表_字段 / idx_表_字段
|
|||
|
|
NAMING_CONVENTION = {
|
|||
|
|
"ix": "idx_%(table_name)s_%(column_0_name)s",
|
|||
|
|
"uq": "uk_%(table_name)s_%(column_0_name)s",
|
|||
|
|
"ck": "ck_%(table_name)s_%(constraint_name)s",
|
|||
|
|
"fk": "fk_%(table_name)s_%(column_0_name)s",
|
|||
|
|
"pk": "pk_%(table_name)s",
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
# 主键与外键统一使用无符号 BIGINT（规范模板为 BIGINT UNSIGNED）。
|
|||
|
|
# 两边类型必须完全一致，否则 MySQL 拒绝建立外键约束。
|
|||
|
|
BIGINT_UNSIGNED = BIGINT(unsigned=True)
|
|||
|
|
|
|||
|
|
# 状态类字段统一用 TINYINT（0/1 或少量枚举值）
|
|||
|
|
TINYINT_TYPE = TINYINT
|
|||
|
|
|
|||
|
|
__all__ = ["BIGINT_UNSIGNED", "TINYINT_TYPE", "Base", "TimestampMixin", "mysql_table_args"]
|
|||
|
|
|
|||
|
|
|
|||
|
|
class Base(DeclarativeBase):
|
|||
|
|
"""全部 ORM 模型的基类。"""
|
|||
|
|
|
|||
|
|
metadata = MetaData(naming_convention=NAMING_CONVENTION)
|
|||
|
|
|
|||
|
|
|
|||
|
|
class TimestampMixin:
|
|||
|
|
"""创建时间 / 更新时间公共列。
|
|||
|
|
|
|||
|
|
取默认值交给数据库而非应用层：即使有人直接用 SQL 改数据，时间戳依然准确；
|
|||
|
|
应用层赋值则会被绕过。
|
|||
|
|
|
|||
|
|
`update_time` 必须用 `text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")`
|
|||
|
|
而非 `server_onupdate=func.current_timestamp()` —— 后者只是告诉 ORM
|
|||
|
|
「这个值由服务端维护」，**不会生成 ON UPDATE 子句**，结果是更新时间永远不刷新。
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
create_time: Mapped[datetime] = mapped_column(
|
|||
|
|
DateTime,
|
|||
|
|
nullable=False,
|
|||
|
|
server_default=text("CURRENT_TIMESTAMP"),
|
|||
|
|
comment="创建时间",
|
|||
|
|
)
|
|||
|
|
update_time: Mapped[datetime] = mapped_column(
|
|||
|
|
DateTime,
|
|||
|
|
nullable=False,
|
|||
|
|
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
|||
|
|
comment="更新时间",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def mysql_table_args(comment: str, *extra: object) -> tuple[object, ...]:
|
|||
|
|
"""构造 MySQL 表的公共参数，返回可直接赋给 `__table_args__` 的元组。
|
|||
|
|
|
|||
|
|
显式指定引擎、字符集与排序规则：compose 已把服务端排序规则覆盖为
|
|||
|
|
`utf8mb4_general_ci`，若某处按 MySQL 8.0 默认的 `utf8mb4_0900_ai_ci` 建表，
|
|||
|
|
跨表 JOIN 会报 `Illegal mix of collations`。
|
|||
|
|
|
|||
|
|
:param comment: 表注释（规范红线要求中文 COMMENT）
|
|||
|
|
:param extra: 额外的约束对象（UniqueConstraint / Index 等）
|
|||
|
|
"""
|
|||
|
|
return (
|
|||
|
|
*extra,
|
|||
|
|
{
|
|||
|
|
"mysql_engine": "InnoDB",
|
|||
|
|
"mysql_charset": "utf8mb4",
|
|||
|
|
"mysql_collate": "utf8mb4_general_ci",
|
|||
|
|
"comment": comment,
|
|||
|
|
},
|
|||
|
|
)
|