yuxuanhui
235 lines
9.0 KiB
Python
235 lines
9.0 KiB
Python
"""Create replayable independent sector radar tables."""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
from sqlalchemy.dialects.postgresql import JSONB
|
|
|
|
revision: str = "0004_sector_radar"
|
|
down_revision: str | None = "0003_market_integrity_checks"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
"""Create source, point-in-time fact, publication, and ranking tables."""
|
|
|
|
op.create_table(
|
|
"sector_radar_source_snapshot",
|
|
sa.Column("id", sa.String(64), primary_key=True),
|
|
sa.Column("api_name", sa.String(32), nullable=False),
|
|
sa.Column("normalized_params", JSONB, nullable=False),
|
|
sa.Column("target_trade_date", sa.Date()),
|
|
sa.Column("partition_key", sa.String(64)),
|
|
sa.Column("observed_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("payload", JSONB, nullable=False),
|
|
sa.Column("row_count", sa.Integer(), nullable=False),
|
|
sa.Column("returned_fields", JSONB, nullable=False),
|
|
sa.Column("content_sha256", sa.String(64), nullable=False),
|
|
sa.Column("row_limit", sa.Integer()),
|
|
sa.Column("limit_reached", sa.Boolean(), nullable=False),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.text("now()"),
|
|
),
|
|
sa.CheckConstraint("row_count >= 0", name="ck_sector_radar_source_row_count"),
|
|
sa.CheckConstraint(
|
|
"row_limit IS NULL OR row_limit > 0", name="ck_sector_radar_source_limit"
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_sector_radar_source_api_date",
|
|
"sector_radar_source_snapshot",
|
|
["api_name", "target_trade_date", "observed_at"],
|
|
)
|
|
|
|
op.create_table(
|
|
"sector_radar_membership",
|
|
sa.Column(
|
|
"source_snapshot_id",
|
|
sa.String(64),
|
|
sa.ForeignKey("sector_radar_source_snapshot.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("trade_date", sa.Date(), nullable=False),
|
|
sa.Column("sector_type", sa.String(16), nullable=False),
|
|
sa.Column("sector_code", sa.String(16), nullable=False),
|
|
sa.Column("sector_name", sa.String(128), nullable=False),
|
|
sa.Column("stock_code", sa.String(12), nullable=False),
|
|
sa.Column("stock_name", sa.String(128), nullable=False),
|
|
sa.Column("membership_status", sa.String(32), nullable=False),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.text("now()"),
|
|
),
|
|
sa.PrimaryKeyConstraint("source_snapshot_id", "sector_code", "stock_code"),
|
|
sa.CheckConstraint(
|
|
"sector_type IN ('concept', 'industry')",
|
|
name="ck_sector_radar_membership_type",
|
|
),
|
|
sa.CheckConstraint(
|
|
"membership_status = 'available'",
|
|
name="ck_sector_radar_membership_status",
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_sector_radar_membership_date_sector",
|
|
"sector_radar_membership",
|
|
["trade_date", "sector_type", "sector_code"],
|
|
)
|
|
|
|
op.create_table(
|
|
"sector_radar_stock_fact",
|
|
sa.Column("fact_revision", sa.String(64), nullable=False),
|
|
sa.Column("trade_date", sa.Date(), nullable=False),
|
|
sa.Column("ts_code", sa.String(12), nullable=False),
|
|
sa.Column("source_snapshot_ids", JSONB, nullable=False),
|
|
sa.Column("status", sa.String(32), nullable=False),
|
|
sa.Column("turnover_yuan", sa.Numeric(28, 6)),
|
|
sa.Column("net_amount_yuan", sa.Numeric(28, 6)),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.text("now()"),
|
|
),
|
|
sa.PrimaryKeyConstraint("fact_revision", "ts_code"),
|
|
sa.CheckConstraint(
|
|
"turnover_yuan IS NULL OR turnover_yuan >= 0",
|
|
name="ck_sector_radar_stock_turnover",
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_sector_radar_stock_fact_date",
|
|
"sector_radar_stock_fact",
|
|
["trade_date", "ts_code"],
|
|
)
|
|
|
|
op.create_table(
|
|
"sector_radar_publication",
|
|
sa.Column("id", sa.String(64), primary_key=True),
|
|
sa.Column("target_trade_date", sa.Date(), nullable=False),
|
|
sa.Column("status", sa.String(16), nullable=False),
|
|
sa.Column("source_version", sa.String(128), nullable=False),
|
|
sa.Column("universe_version", sa.String(128), nullable=False),
|
|
sa.Column("metric_versions", JSONB, nullable=False),
|
|
sa.Column("input_hash", sa.String(64)),
|
|
sa.Column("coverage", sa.Numeric(8, 6), nullable=False),
|
|
sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("finished_at", sa.DateTime(timezone=True)),
|
|
sa.Column("error_summary", sa.String(500)),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.text("now()"),
|
|
),
|
|
sa.CheckConstraint(
|
|
"status IN ('running', 'success', 'partial', 'failed')",
|
|
name="ck_sector_radar_publication_status",
|
|
),
|
|
sa.CheckConstraint(
|
|
"coverage >= 0 AND coverage <= 1",
|
|
name="ck_sector_radar_publication_coverage",
|
|
),
|
|
sa.CheckConstraint(
|
|
"(status = 'running' AND finished_at IS NULL) OR "
|
|
"(status <> 'running' AND finished_at IS NOT NULL)",
|
|
name="ck_sector_radar_publication_finished",
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_sector_radar_publication_status_date",
|
|
"sector_radar_publication",
|
|
["status", "target_trade_date", "finished_at"],
|
|
)
|
|
op.create_index(
|
|
"uq_sector_radar_publication_running_date",
|
|
"sector_radar_publication",
|
|
["target_trade_date"],
|
|
unique=True,
|
|
postgresql_where=sa.text("status = 'running'"),
|
|
)
|
|
|
|
op.create_table(
|
|
"sector_radar_ranking",
|
|
sa.Column(
|
|
"publication_id",
|
|
sa.String(64),
|
|
sa.ForeignKey("sector_radar_publication.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("trade_date", sa.Date(), nullable=False),
|
|
sa.Column("sector_type", sa.String(16), nullable=False),
|
|
sa.Column("sector_code", sa.String(16), nullable=False),
|
|
sa.Column("sector_name", sa.String(128), nullable=False),
|
|
sa.Column("metric_kind", sa.String(16), nullable=False),
|
|
sa.Column("metric_version", sa.String(128), nullable=False),
|
|
sa.Column("implementation_kind", sa.String(16), nullable=False),
|
|
sa.Column("unit", sa.String(16), nullable=False),
|
|
sa.Column("metric_value", sa.Numeric(28, 12)),
|
|
sa.Column("quality", sa.String(32), nullable=False),
|
|
sa.Column("member_count", sa.Integer(), nullable=False),
|
|
sa.Column("valid_sample_count", sa.Integer(), nullable=False),
|
|
sa.Column("membership_coverage", sa.Numeric(8, 6), nullable=False),
|
|
sa.Column("moneyflow_coverage", sa.Numeric(8, 6), nullable=False),
|
|
sa.Column("rank_position", sa.Integer()),
|
|
sa.Column("rank_percentile", sa.Numeric(18, 12)),
|
|
sa.Column("rank_changes", JSONB, nullable=False),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.text("now()"),
|
|
),
|
|
sa.PrimaryKeyConstraint(
|
|
"publication_id",
|
|
"sector_type",
|
|
"sector_code",
|
|
"metric_version",
|
|
),
|
|
sa.CheckConstraint(
|
|
"sector_type IN ('concept', 'industry')",
|
|
name="ck_sector_radar_ranking_type",
|
|
),
|
|
sa.CheckConstraint(
|
|
"implementation_kind = 'independent'",
|
|
name="ck_sector_radar_ranking_implementation",
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_sector_radar_ranking_query",
|
|
"sector_radar_ranking",
|
|
["publication_id", "sector_type", "metric_version", "rank_position"],
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
"""Drop only sector radar tables in dependency-safe order."""
|
|
|
|
op.drop_index("ix_sector_radar_ranking_query", table_name="sector_radar_ranking")
|
|
op.drop_table("sector_radar_ranking")
|
|
op.drop_index(
|
|
"uq_sector_radar_publication_running_date",
|
|
table_name="sector_radar_publication",
|
|
)
|
|
op.drop_index(
|
|
"ix_sector_radar_publication_status_date",
|
|
table_name="sector_radar_publication",
|
|
)
|
|
op.drop_table("sector_radar_publication")
|
|
op.drop_index("ix_sector_radar_stock_fact_date", table_name="sector_radar_stock_fact")
|
|
op.drop_table("sector_radar_stock_fact")
|
|
op.drop_index(
|
|
"ix_sector_radar_membership_date_sector",
|
|
table_name="sector_radar_membership",
|
|
)
|
|
op.drop_table("sector_radar_membership")
|
|
op.drop_index("ix_sector_radar_source_api_date", table_name="sector_radar_source_snapshot")
|
|
op.drop_table("sector_radar_source_snapshot")
|