"""IMDS data tables — market index, announcement, market statistics, trade summary Revision ID: c1f8a2d3e5b7 Revises: bfd7a23f9437 Create Date: 2026-06-03 Ported from MySQL source schema to PostgreSQL: imds_idx_data Market index values (capital value, deviation) imds_man_data Announcements / corporate actions imds_mkistat_data Per-instrument market statistics (OHLC, VWAP, volume) imds_trd_data Aggregate trade summary (total trades, volume, value) MySQL → PostgreSQL conversions applied: int(11) AUTO_INCREMENT → INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY decimal(M,D) → NUMERIC(M,D) float → DOUBLE PRECISION datetime → TIMESTAMP (without time zone — source data is local) datetime DEFAULT now() → TIMESTAMP DEFAULT NOW() text → TEXT date → DATE time → TIME varchar(N) → VARCHAR(N) ENGINE/CHARSET/COLLATE → (PostgreSQL; removed) """ from __future__ import annotations import sqlalchemy as sa from alembic import op # --------------------------------------------------------------------------- # Revision identifiers # --------------------------------------------------------------------------- revision: str = "c1f8a2d3e5b7" down_revision: str = "bfd7a23f9437" branch_labels = None depends_on = None # =========================================================================== # UPGRADE # =========================================================================== def upgrade() -> None: # ----------------------------------------------------------------------- # 1. imds_idx_data — market index snapshots # ----------------------------------------------------------------------- op.create_table( "imds_idx_data", sa.Column( "IDX_ID", sa.Integer, primary_key=True, autoincrement=True, comment="Auto-incrementing surrogate PK", ), sa.Column("IDX_INDEX_ID", sa.String(10), nullable=False, comment="Index identifier code"), sa.Column("IDX_DATE_TIME", sa.DateTime, nullable=False, comment="Timestamp of the index snapshot"), sa.Column("IDX_CAPITAL_VALUE", sa.Numeric(20, 6), nullable=True, comment="Index capital value"), sa.Column("IDX_DEVIATION", sa.Numeric(20, 6), nullable=True, comment="Absolute deviation from reference"), sa.Column("IDX_PERCENTAGE_DEVIATION", sa.Numeric(20, 6), nullable=True, comment="Percentage deviation from reference"), sa.Column("IDX_STORE_DATE", sa.Date, nullable=True, comment="Date the record was stored"), sa.Column("IDX_STORE_TIME", sa.Time, nullable=True, comment="Time the record was stored"), sa.Column( "IDX_STORE_TIMESTAMP", sa.DateTime, nullable=True, server_default=sa.text("NOW()"), comment="DB insert timestamp (auto-set)", ), ) op.create_index("ix_imds_idx_index_id", "imds_idx_data", ["IDX_INDEX_ID"]) op.create_index("ix_imds_idx_date_time", "imds_idx_data", ["IDX_DATE_TIME"]) op.create_index("ix_imds_idx_store_date", "imds_idx_data", ["IDX_STORE_DATE"]) # ----------------------------------------------------------------------- # 2. imds_man_data — market announcements # ----------------------------------------------------------------------- op.create_table( "imds_man_data", sa.Column( "MAN_ID", sa.Integer, primary_key=True, autoincrement=True, comment="Auto-incrementing surrogate PK", ), sa.Column("MAN_ANNOUNCEMENT_DATE_TIME", sa.DateTime, nullable=False, comment="Official announcement timestamp"), sa.Column("MAN_ANNOUNCEMENT_PREFIX", sa.String(20), nullable=True, comment="Short label / category prefix"), sa.Column("MAN_ANNOUNCEMENT", sa.Text, nullable=True, comment="Full announcement body text"), sa.Column("MAN_EXPIRY_DATE", sa.Date, nullable=True, comment="Date the announcement expires / becomes inactive"), sa.Column("MAN_STORE_DATE", sa.Date, nullable=True, comment="Date the record was stored"), sa.Column("MAN_STORE_TIME", sa.Time, nullable=True, comment="Time the record was stored"), sa.Column( "MAN_STORE_TIMESTAMP", sa.DateTime, nullable=True, server_default=sa.text("NOW()"), comment="DB insert timestamp (auto-set)", ), ) op.create_index( "ix_imds_man_announcement_dt", "imds_man_data", ["MAN_ANNOUNCEMENT_DATE_TIME"], ) op.create_index("ix_imds_man_expiry_date", "imds_man_data", ["MAN_EXPIRY_DATE"]) op.create_index("ix_imds_man_store_date", "imds_man_data", ["MAN_STORE_DATE"]) # ----------------------------------------------------------------------- # 3. imds_mkistat_data — per-instrument market statistics # ----------------------------------------------------------------------- op.create_table( "imds_mkistat_data", sa.Column( "MKISTAT_ID", sa.Integer, primary_key=True, autoincrement=True, comment="Auto-incrementing surrogate PK", ), sa.Column("MKISTAT_INSTRUMENT_CODE", sa.String(50), nullable=True), sa.Column("MKISTAT_INSTRUMENT_NUMBER", sa.String(50), nullable=True), sa.Column("MKISTAT_QUOTE_BASES", sa.String(50), nullable=True), sa.Column("MKISTAT_OPEN_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_PUB_LAST_TRADED_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_SPOT_LAST_TRADED_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_HIGH_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_LOW_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_CLOSE_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_YDAY_CLOSE_PRICE", sa.Numeric(10, 2), nullable=True), sa.Column("MKISTAT_TOTAL_TRADES", sa.Float, nullable=True), sa.Column("MKISTAT_TOTAL_VOLUME", sa.Float, nullable=True), sa.Column("MKISTAT_TOTAL_VALUE", sa.Numeric(20, 6), nullable=True), sa.Column("MKISTAT_PUBLIC_TOTAL_TRADES", sa.Numeric(20, 3), nullable=True), sa.Column("MKISTAT_PUBLIC_TOTAL_VOLUME", sa.Float, nullable=True), sa.Column("MKISTAT_PUBLIC_TOTAL_VALUE", sa.Numeric(20, 3), nullable=True), sa.Column("MKISTAT_SPOT_TOTAL_TRADES", sa.Float, nullable=True), sa.Column("MKISTAT_SPOT_TOTAL_VOLUME", sa.Float, nullable=True), sa.Column("MKISTAT_SPOT_TOTAL_VALUE", sa.Numeric(20, 3), nullable=True), sa.Column("MKISTAT_LM_DATE_TIME", sa.DateTime, nullable=True, comment="Last-modified datetime"), # NOT NULL columns without defaults — must be supplied on insert sa.Column("MKISTAT_CHANGE_YDAY_CLOSE", sa.Float, nullable=False), sa.Column("MKISTAT_AVG_TRADE_SHARE", sa.Float, nullable=False), sa.Column("MKISTAT_ADV_SHARES", sa.Float, nullable=False), sa.Column("MKISTAT_ADV_VALUE", sa.Float, nullable=False), sa.Column("MKISTAT_CHANGE_VOLUME", sa.Float, nullable=False), sa.Column("MKISTAT_PVR", sa.Float, nullable=False), sa.Column("MKISTAT_STD_DEV_60MIN", sa.Float, nullable=False), sa.Column("MKISTAT_VWAP_DAILY", sa.Float, nullable=False), sa.Column("MKISTAT_VWAP_1H", sa.Float, nullable=False), sa.Column("MKISTAT_STORE_DATE", sa.Date, nullable=True), sa.Column("MKISTAT_STORE_TIME", sa.Time, nullable=True), sa.Column( "MKISTAT_STORE_TIMESTAMP", sa.DateTime, nullable=True, server_default=sa.text("NOW()"), comment="DB insert timestamp (auto-set)", ), ) op.create_index( "ix_imds_mkistat_instrument_code", "imds_mkistat_data", ["MKISTAT_INSTRUMENT_CODE"], ) op.create_index( "ix_imds_mkistat_lm_date_time", "imds_mkistat_data", ["MKISTAT_LM_DATE_TIME"], ) op.create_index( "ix_imds_mkistat_store_date", "imds_mkistat_data", ["MKISTAT_STORE_DATE"], ) # Composite: fast lookups by instrument + date range op.create_index( "ix_imds_mkistat_instrument_store_date", "imds_mkistat_data", ["MKISTAT_INSTRUMENT_CODE", "MKISTAT_STORE_DATE"], ) # ----------------------------------------------------------------------- # 4. imds_trd_data — aggregate trade summary # ----------------------------------------------------------------------- op.create_table( "imds_trd_data", sa.Column( "TRD_ID", sa.Integer, primary_key=True, autoincrement=True, comment="Auto-incrementing surrogate PK", ), sa.Column("TRD_SNO", sa.Integer, nullable=True, comment="Source serial number"), sa.Column("TRD_TOTAL_TRADES", sa.Float, nullable=True), sa.Column("TRD_TOTAL_VOLUME", sa.Float, nullable=True), sa.Column("TRD_TOTAL_VALUE", sa.Numeric(20, 4), nullable=True), sa.Column("TRD_MKT_STATUS", sa.String(255), nullable=True, comment="Market status text (e.g. Open, Closed, Pre-Open)"), sa.Column("TRD_LM_DATE_TIME", sa.DateTime, nullable=True, comment="Last-modified datetime"), sa.Column("TRD_STORE_DATE", sa.Date, nullable=True), sa.Column("TRD_STORE_TIME", sa.Time, nullable=True), sa.Column( "TRD_STORE_TIMESTAMP", sa.DateTime, nullable=True, server_default=sa.text("NOW()"), comment="DB insert timestamp (auto-set)", ), ) op.create_index("ix_imds_trd_store_date", "imds_trd_data", ["TRD_STORE_DATE"]) op.create_index("ix_imds_trd_lm_date_time", "imds_trd_data", ["TRD_LM_DATE_TIME"]) op.create_index("ix_imds_trd_mkt_status", "imds_trd_data", ["TRD_MKT_STATUS"]) # =========================================================================== # DOWNGRADE # =========================================================================== def downgrade() -> None: op.drop_index("ix_imds_trd_mkt_status", table_name="imds_trd_data") op.drop_index("ix_imds_trd_lm_date_time", table_name="imds_trd_data") op.drop_index("ix_imds_trd_store_date", table_name="imds_trd_data") op.drop_table("imds_trd_data") op.drop_index("ix_imds_mkistat_instrument_store_date", table_name="imds_mkistat_data") op.drop_index("ix_imds_mkistat_store_date", table_name="imds_mkistat_data") op.drop_index("ix_imds_mkistat_lm_date_time", table_name="imds_mkistat_data") op.drop_index("ix_imds_mkistat_instrument_code", table_name="imds_mkistat_data") op.drop_table("imds_mkistat_data") op.drop_index("ix_imds_man_store_date", table_name="imds_man_data") op.drop_index("ix_imds_man_expiry_date", table_name="imds_man_data") op.drop_index("ix_imds_man_announcement_dt", table_name="imds_man_data") op.drop_table("imds_man_data") op.drop_index("ix_imds_idx_store_date", table_name="imds_idx_data") op.drop_index("ix_imds_idx_date_time", table_name="imds_idx_data") op.drop_index("ix_imds_idx_index_id", table_name="imds_idx_data") op.drop_table("imds_idx_data")