Skip to main content

mcx_infra.py — Infrastructure Bootstrap

Creates scr_mcx_master, scr_mcxfo_eod, their indexes, and the modi_date trigger on scr_mcx_master. The single source of truth for this DDL — mcx_downloader.py's McxProcessor._ensure_tables_exist() delegates here rather than keeping a second copy.

cd backend
python -m mcxeod.mcx_infra                     # create/verify + report (safe)
python -m mcxeod.mcx_infra --drop-legacy       # ALSO drop superseded tables (destructive)
python -m mcxeod.mcx_infra --purge-scr-master  # ALSO clean MCX rows out of scr_master (destructive)

Why this exists as its own module​

McxProcessor.download_date() already creates the schema lazily on its first write, so a normal sync works fine against an empty database without any extra step. This module exists so the schema can be verified or created explicitly, without touching MCX's network API or writing any price data — useful for:

  • A fresh local checkout, before running any downloader module
  • A VPS deploy, to confirm the table/index/trigger state landed correctly before run_update does its first live download
  • CI / a pre-deploy check that wants a fast, read-only-feeling schema confirmation

All statements are CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS / CREATE OR REPLACE FUNCTION / DROP TRIGGER IF EXISTS + CREATE TRIGGER, so running it repeatedly is a no-op past the first call.

ensure_infrastructure(con=None) → dict​

Accepts an existing connection (what McxProcessor passes in, so the DDL runs inside the caller's connection lifecycle) or opens/closes its own when called standalone. Rolls back and re-raises on any failure — never leaves half-created schema committed.

McxProcessor._ensure_tables_exist() wraps this with a _tables_ready per-process cache flag so a multi-thousand-date backfill doesn't re-run CREATE TABLE / CREATE TRIGGER once per date — the DDL itself is cheap and idempotent, but not free at that call volume.

report_state()​

Read-only diagnostic — prints whether each table exists, its row count, the modi_date trigger installed, and the full index list for both tables. Never writes anything, safe to run at any time.

=== mcxeod infrastructure ===
scr_mcx_master   exists: True
scr_mcxfo_eod    exists: True
  rows: 0
  rows: 0
modi_date trigger installed: True
indexes: ['idx_mcx_master_edate', 'idx_mcx_master_underly', 'idx_mcxfo_eod_sdate', 'mcx_mname_unique', 'pk_mcxfo_eod', 'scr_mcx_master_pkey']

Running the module directly (python -m mcxeod.mcx_infra) calls ensure_infrastructure() then report_state(), so one command both bootstraps and confirms the result.

drop_legacy_tables(con=None, confirm=False)​

Removes tables superseded by the current schema:

TableWhy it is gone
scr_mcx_eodOld schema with the legacy 4-digit-year mname (ZINCMINI28FEB2025). Cannot be reconciled with current rows by name — the documented remedy is re-download, never a rename migration.
scr_mcx_continuousAn earlier design giving continuous series their own table. Removed because it forced every consumer to UNION two tables for no benefit.

Safety properties, all verified:

  • Opt-in only. Requires confirm=True (CLI: --drop-legacy). Without it, returns a dry-run dict naming what would be dropped.
  • Never automatic. Deliberately not called from ensure_infrastructure() — that runs on every McxProcessor write path, so a DROP there would destroy a table silently on every download.
  • Row counts recorded. Each table's count is read and printed before the drop, so the loss appears in the run output rather than happening quietly.
  • No CASCADE. If something unexpectedly depends on the table, the error surfaces instead of triggering a chain of collateral drops.
  • Allow-list. Only names in LEGACY_TABLES are ever dropped.

purge_mcx_from_scr_master(con=None, confirm=False)​

Removes MCX contracts that were wrongly written into NSE's scr_master (rule 1: MCX instruments belong in scr_mcx_master and are never written to scr_master).

Match on typ, never on name​

This is the part that matters most. NSE genuinely lists these:

SymbolWhat it actually is
GOLDBEES, SILVERBEES, GOLDIETFGold/silver ETFs
GOLDIAMGoldiam International Ltd — a real equity
GOLDTECH, SILVERLINE, LEADEDSYSReal NSE equities
GOLDKART, GOLDSTARSME-segment listings

A name-based cleanup would delete every one of them. The typ code is the safe discriminator instead — NSE uses 1–11, MCX uses 20–24, so the ranges cannot collide.

Delete order is not optional​

Price rows keyed to those masids live in scr_nseeq_eod, scr_nsefo_eod and scr_indexpepb. They are deleted before the master rows, inside a single transaction:

Reversing that order — or committing in between — strands price rows pointing at a masid that no longer exists. masid is a SERIAL, so freed values are simply never reused; nothing is renumbered.

A plain run only reports what it finds, including the dependent row counts, so you can see the blast radius before committing to it.

What this does not touch​

  • scr_master / scr_nseeq_eod / scr_nsefo_eod — NSE's tables, entirely separate from MCX's
  • Any row data — this only creates empty structure
  • holidays / specialdays (exch=2) — that calendar is built by mcx_history.py, not this module