86 lines
2.7 KiB
Python
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)
|