unis_hrms/app/db/base.py

86 lines
2.7 KiB
Python

"""Declarative base + portable column types.
Everything here is chosen so the exact same model definitions run on SQLite
(today) and MySQL (later):
* money -> :class:`Money` (DECIMAL(14,2)) with Decimal normalisation
* enums -> plain ``VARCHAR`` + Python ``Literal`` validation in schemas
* json -> ``JSON`` (SQLite 3.38+ / MySQL 5.7+)
* bool -> ``BOOLEAN`` (TINYINT(1) on MySQL)
"""
from __future__ import annotations
from datetime import datetime, timezone
from decimal import Decimal
from typing import Any
from sqlalchemy import DateTime, Integer, MetaData, Numeric, TypeDecorator, event
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.schema import Table
def utcnow() -> datetime:
return datetime.now(timezone.utc).replace(tzinfo=None)
NAMING_CONVENTION = {
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s",
"pk": "pk_%(table_name)s",
}
@event.listens_for(Table, "after_parent_attach")
def _mysql_table_defaults(table: Table, _parent) -> None: # pragma: no cover
"""Give every table InnoDB + utf8mb4 so the same schema lands cleanly on MySQL."""
table.kwargs.setdefault("mysql_engine", "InnoDB")
table.kwargs.setdefault("mysql_charset", "utf8mb4")
class Base(DeclarativeBase):
"""Root declarative base with deterministic constraint names."""
metadata = MetaData(naming_convention=NAMING_CONVENTION)
class Money(TypeDecorator):
"""DECIMAL(14,2) that always yields ``Decimal`` (SQLite returns float)."""
impl = Numeric
cache_ok = True
def __init__(self, precision: int = 14, scale: int = 2) -> None:
super().__init__(precision=precision, scale=scale)
def process_bind_param(self, value: Any, dialect) -> Decimal | None:
if value is None or value == "":
return None
return Decimal(str(value)).quantize(Decimal("0.01"))
def process_result_value(self, value: Any, dialect) -> Decimal | None:
if value is None:
return None
return Decimal(str(value)).quantize(Decimal("0.01"))
class TimestampMixin:
created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, nullable=False)
updated_at: Mapped[datetime] = mapped_column(
DateTime, default=utcnow, onupdate=utcnow, nullable=False
)
class SoftDeleteMixin:
deleted_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
class PKMixin:
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
class AuditMixin:
created_by: Mapped[int | None] = mapped_column(Integer, nullable=True)
updated_by: Mapped[int | None] = mapped_column(Integer, nullable=True)