302 lines
10 KiB
Python
302 lines
10 KiB
Python
"""initial schema: справочники, бумаги, котировки, курсы, операции, журнал запросов
|
||
|
||
Revision ID: ef0ccff2e887
|
||
Revises:
|
||
Create Date: 2026-10-09 00:00:00
|
||
|
||
"""
|
||
|
||
from typing import Sequence, Union
|
||
|
||
import sqlalchemy as sa
|
||
from sqlalchemy.dialects import postgresql
|
||
|
||
from alembic import op
|
||
from app.core.config import settings
|
||
|
||
# revision identifiers, used by Alembic.
|
||
revision: str = "ef0ccff2e887"
|
||
down_revision: Union[str, None] = None
|
||
branch_labels: Union[str, Sequence[str], None] = None
|
||
depends_on: Union[str, Sequence[str], None] = None
|
||
|
||
_schema = settings.DATABASE_SCHEMA or None
|
||
|
||
# Типы зафиксированы в ревизии (а не импортированы из app/db/types.py), чтобы
|
||
# будущие правки моделей не меняли задним числом уже применённую миграцию.
|
||
_BIGINT_PK = sa.BigInteger().with_variant(sa.Integer(), "sqlite")
|
||
_JSON = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")
|
||
_PRICE = sa.Numeric(18, 6)
|
||
_RATE = sa.Numeric(18, 8)
|
||
_AMOUNT = sa.Numeric(20, 6)
|
||
_PERCENT = sa.Numeric(12, 6)
|
||
_QUANTITY = sa.Numeric(20, 4)
|
||
_SEED_USER = "system"
|
||
|
||
|
||
def _fk(table_column: str) -> str:
|
||
return f"{_schema}.{table_column}" if _schema else table_column
|
||
|
||
|
||
def _id() -> sa.Column:
|
||
return sa.Column("id", _BIGINT_PK, sa.Identity(), primary_key=True)
|
||
|
||
|
||
def _audit() -> list[sa.Column]:
|
||
return [
|
||
sa.Column(
|
||
"created_at",
|
||
sa.DateTime(timezone=True),
|
||
server_default=sa.func.now(),
|
||
nullable=False,
|
||
),
|
||
sa.Column("created_by", sa.String(255), nullable=False),
|
||
sa.Column(
|
||
"updated_at",
|
||
sa.DateTime(timezone=True),
|
||
server_default=sa.func.now(),
|
||
nullable=False,
|
||
),
|
||
sa.Column("updated_by", sa.String(255), nullable=False),
|
||
]
|
||
|
||
|
||
def _reference_table(name: str, code_length: int, name_length: int) -> sa.Table:
|
||
return op.create_table(
|
||
name,
|
||
_id(),
|
||
sa.Column("code", sa.String(code_length), nullable=False),
|
||
sa.Column("name", sa.String(name_length), nullable=False),
|
||
*_audit(),
|
||
sa.UniqueConstraint("code", name=f"{name}_code_key"),
|
||
schema=_schema,
|
||
)
|
||
|
||
|
||
def _seed(table: sa.Table, rows: list[tuple[str, str]]) -> None:
|
||
op.bulk_insert(
|
||
table,
|
||
[
|
||
{
|
||
"code": code,
|
||
"name": name,
|
||
"created_by": _SEED_USER,
|
||
"updated_by": _SEED_USER,
|
||
}
|
||
for code, name in rows
|
||
],
|
||
)
|
||
|
||
|
||
def upgrade() -> None:
|
||
"""Upgrade schema."""
|
||
currency = _reference_table("currency", 3, 100)
|
||
portfolio = _reference_table("portfolio", 20, 255)
|
||
operation_type = _reference_table("operation_type", 20, 255)
|
||
|
||
op.create_table(
|
||
"security",
|
||
_id(),
|
||
sa.Column("isin", sa.String(12), nullable=False),
|
||
sa.Column("secid", sa.String(51), nullable=False),
|
||
sa.Column("name", sa.String(255), nullable=False),
|
||
sa.Column("initial_face_value", _PRICE, nullable=True),
|
||
sa.Column(
|
||
"currency_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("currency.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column("placement_date", sa.Date(), nullable=True),
|
||
sa.Column("maturity_date", sa.Date(), nullable=True),
|
||
sa.Column("coupon_rate", _PERCENT, nullable=True),
|
||
sa.Column("coupon_frequency", sa.Integer(), nullable=True),
|
||
sa.Column("is_active", sa.Boolean(), server_default=sa.true(), nullable=False),
|
||
*_audit(),
|
||
sa.UniqueConstraint("isin", name="security_isin_key"),
|
||
schema=_schema,
|
||
)
|
||
|
||
op.create_table(
|
||
"market_data",
|
||
_id(),
|
||
sa.Column(
|
||
"security_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("security.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column("market_date", sa.Date(), nullable=False),
|
||
sa.Column("board", sa.String(12), nullable=False),
|
||
sa.Column("clean_price", _PRICE, nullable=True),
|
||
sa.Column("clean_price_source", sa.String(20), nullable=True),
|
||
sa.Column("yield_value", _PERCENT, nullable=True),
|
||
sa.Column("yield_source", sa.String(20), nullable=True),
|
||
sa.Column("duration_days", _QUANTITY, nullable=True),
|
||
sa.Column("accrued_interest", _PRICE, nullable=True),
|
||
sa.Column("face_value", _PRICE, nullable=True),
|
||
sa.Column("coupon_rate", _PERCENT, nullable=True),
|
||
sa.Column("num_trades", sa.BigInteger(), nullable=True),
|
||
sa.Column("volume", _QUANTITY, nullable=True),
|
||
sa.Column("raw", _JSON, nullable=True),
|
||
sa.Column(
|
||
"loaded_at",
|
||
sa.DateTime(timezone=True),
|
||
server_default=sa.func.now(),
|
||
nullable=False,
|
||
),
|
||
*_audit(),
|
||
sa.UniqueConstraint(
|
||
"security_id", "market_date", name="uq_market_data_security_date"
|
||
),
|
||
schema=_schema,
|
||
)
|
||
op.create_index(
|
||
"ix_market_data_market_date", "market_data", ["market_date"], schema=_schema
|
||
)
|
||
|
||
op.create_table(
|
||
"exchange_rate",
|
||
_id(),
|
||
sa.Column(
|
||
"currency_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("currency.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column("rate_date", sa.Date(), nullable=False),
|
||
sa.Column("nominal", sa.Integer(), nullable=False),
|
||
sa.Column("rate", _RATE, nullable=False),
|
||
sa.Column(
|
||
"loaded_at",
|
||
sa.DateTime(timezone=True),
|
||
server_default=sa.func.now(),
|
||
nullable=False,
|
||
),
|
||
*_audit(),
|
||
sa.UniqueConstraint(
|
||
"currency_id", "rate_date", name="uq_exchange_rate_currency_date"
|
||
),
|
||
schema=_schema,
|
||
)
|
||
|
||
op.create_table(
|
||
"operation",
|
||
_id(),
|
||
sa.Column(
|
||
"security_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("security.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column(
|
||
"portfolio_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("portfolio.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column(
|
||
"operation_type_id",
|
||
sa.BigInteger(),
|
||
sa.ForeignKey(_fk("operation_type.id")),
|
||
nullable=False,
|
||
),
|
||
sa.Column("trade_date", sa.Date(), nullable=False),
|
||
sa.Column("trade_time", sa.Time(), nullable=True),
|
||
sa.Column("settlement_date", sa.Date(), nullable=True),
|
||
sa.Column("account", sa.String(50), nullable=True),
|
||
sa.Column("quantity", _QUANTITY, nullable=True),
|
||
sa.Column("price", _PRICE, nullable=True),
|
||
sa.Column("amount", _AMOUNT, nullable=True),
|
||
sa.Column("amount_with_accrued_interest", _AMOUNT, nullable=True),
|
||
sa.Column("comment", sa.Text(), nullable=True),
|
||
sa.Column("settlement_code", sa.String(20), nullable=True),
|
||
sa.Column("order_number", sa.String(50), nullable=True),
|
||
sa.Column("trade_number", sa.String(50), nullable=True),
|
||
sa.Column("counterparty", sa.String(255), nullable=True),
|
||
sa.Column("commission_ts", _AMOUNT, nullable=True),
|
||
sa.Column("commission_clearing", _AMOUNT, nullable=True),
|
||
sa.Column("commission_exchange", _AMOUNT, nullable=True),
|
||
sa.Column("commission_tc", _AMOUNT, nullable=True),
|
||
sa.Column("commission_broker", _AMOUNT, nullable=True),
|
||
sa.Column("import_batch_id", sa.BigInteger(), nullable=True),
|
||
*_audit(),
|
||
schema=_schema,
|
||
)
|
||
op.create_index(
|
||
"ux_operation_portfolio_trade_number",
|
||
"operation",
|
||
["portfolio_id", "trade_number"],
|
||
unique=True,
|
||
schema=_schema,
|
||
postgresql_where=sa.text("trade_number IS NOT NULL"),
|
||
sqlite_where=sa.text("trade_number IS NOT NULL"),
|
||
)
|
||
|
||
op.create_table(
|
||
"external_request_log",
|
||
_id(),
|
||
sa.Column("source", sa.String(10), nullable=False),
|
||
sa.Column("endpoint", sa.String(500), nullable=False),
|
||
sa.Column("params", _JSON, nullable=True),
|
||
sa.Column("http_status", sa.Integer(), nullable=True),
|
||
sa.Column("duration_ms", sa.Integer(), nullable=False),
|
||
sa.Column("ok", sa.Boolean(), nullable=False),
|
||
sa.Column("error", sa.Text(), nullable=True),
|
||
sa.Column("gateway_transaction_id", sa.String(100), nullable=True),
|
||
sa.Column(
|
||
"created_at",
|
||
sa.DateTime(timezone=True),
|
||
server_default=sa.func.now(),
|
||
nullable=False,
|
||
),
|
||
sa.Column("created_by", sa.String(255), nullable=False),
|
||
schema=_schema,
|
||
)
|
||
op.create_index(
|
||
"ix_external_request_log_created_at",
|
||
"external_request_log",
|
||
["created_at"],
|
||
schema=_schema,
|
||
)
|
||
|
||
_seed(
|
||
currency,
|
||
[("RUB", "Российский рубль"), ("CNY", "Китайский юань"), ("USD", "Доллар США")],
|
||
)
|
||
_seed(portfolio, [("UW", "Андеррайтинг"), ("SALES", "Продажи")])
|
||
_seed(
|
||
operation_type,
|
||
[
|
||
("BUY", "Покупка"),
|
||
("SELL", "Продажа"),
|
||
("CPN", "Купон"),
|
||
("NOM", "Частичное погашение номинала"),
|
||
# TODO(BIQ-38115): уточнить смысл VAL у ДРРК и переименовать (Q7).
|
||
("VAL", "VAL (смысл уточняется)"),
|
||
],
|
||
)
|
||
|
||
|
||
def downgrade() -> None:
|
||
"""Downgrade schema."""
|
||
op.drop_index(
|
||
"ix_external_request_log_created_at",
|
||
table_name="external_request_log",
|
||
schema=_schema,
|
||
)
|
||
op.drop_table("external_request_log", schema=_schema)
|
||
op.drop_index(
|
||
"ux_operation_portfolio_trade_number", table_name="operation", schema=_schema
|
||
)
|
||
op.drop_table("operation", schema=_schema)
|
||
op.drop_table("exchange_rate", schema=_schema)
|
||
op.drop_index(
|
||
"ix_market_data_market_date", table_name="market_data", schema=_schema
|
||
)
|
||
op.drop_table("market_data", schema=_schema)
|
||
op.drop_table("security", schema=_schema)
|
||
op.drop_table("operation_type", schema=_schema)
|
||
op.drop_table("portfolio", schema=_schema)
|
||
op.drop_table("currency", schema=_schema)
|