| 1 | from __future__ import annotations |
| 2 | |
| 3 | import psycopg |
| 4 | from psycopg.rows import dict_row |
| 5 | |
| 6 | from app.persistence import SqliteResearchPersistence |
| 7 | from app.settings import Settings |
| 8 | |
| 9 | |
| 10 | class PostgresResearchPersistence(SqliteResearchPersistence): |
| 11 | """PostgreSQL implementation using the same repository contract as the test SQLite store.""" |
| 12 | |
| 13 | def __init__(self, settings: Settings) -> None: |
| 14 | self.settings = settings |
| 15 | connection = psycopg.connect( |
| 16 | host=settings.research_database_host, |
| 17 | port=settings.research_database_port, |
| 18 | dbname=settings.research_database_name, |
| 19 | user=settings.research_database_user, |
| 20 | password=settings.research_database_password or "", |
| 21 | sslmode=settings.research_database_ssl_mode, |
| 22 | connect_timeout=settings.research_database_connect_timeout_seconds, |
| 23 | options=f"-c statement_timeout={settings.research_database_statement_timeout_seconds * 1000}", |
| 24 | autocommit=False, |
| 25 | row_factory=dict_row, |
| 26 | ) |
| 27 | self._connection = _PostgresConnectionAdapter(connection) |
| 28 | _validate_identifier(settings.research_database_schema) |
| 29 | self._connection.execute(f"SET search_path TO {settings.research_database_schema}") |
| 30 | self._connection.commit() |
| 31 | self._assert_schema_available() |
| 32 | |
| 33 | def migrate(self) -> None: |
| 34 | raise RuntimeError("PostgreSQL research schema is owned by research-service Flyway migrations") |
| 35 | |
| 36 | def _assert_schema_available(self) -> None: |
| 37 | try: |
| 38 | self._connection.execute("SELECT 1 FROM research_documents LIMIT 0") |
| 39 | self._connection.execute("SELECT 1 FROM research_events LIMIT 0") |
| 40 | self._connection.execute("SELECT 1 FROM research_event_sources LIMIT 0") |
| 41 | self._connection.execute("SELECT 1 FROM research_refresh_runs LIMIT 0") |
| 42 | self._connection.execute("SELECT 1 FROM global_shareholding_snapshots LIMIT 0") |
| 43 | self._connection.execute("SELECT 1 FROM global_shareholding_snapshot_values LIMIT 0") |
| 44 | self._connection.execute("SELECT 1 FROM global_financial_facts LIMIT 0") |
| 45 | self._connection.execute("SELECT 1 FROM global_structured_market_snapshots LIMIT 0") |
| 46 | self._connection.execute("SELECT 1 FROM global_market_price_observations LIMIT 0") |
| 47 | self._connection.execute("SELECT 1 FROM global_daily_market_bars LIMIT 0") |
| 48 | self._connection.execute("SELECT 1 FROM company_business_exposure_profiles LIMIT 0") |
| 49 | self._connection.execute("SELECT 1 FROM research_news_search_runs LIMIT 0") |
| 50 | self._connection.execute("SELECT 1 FROM research_event_impact_features LIMIT 0") |
| 51 | self._connection.execute("SELECT 1 FROM global_opportunity_snapshot LIMIT 0") |
| 52 | self._connection.execute("SELECT 1 FROM stock_recommendation_history LIMIT 0") |
| 53 | self._connection.execute("SELECT 1 FROM recommendation_current_state LIMIT 0") |
| 54 | self._connection.execute("SELECT 1 FROM global_opportunity_top_selection LIMIT 0") |
| 55 | self._connection.execute("SELECT 1 FROM recommendation_backtest_run LIMIT 0") |
| 56 | self._connection.execute("SELECT 1 FROM global_stock_rule_engine_results LIMIT 0") |
| 57 | self._connection.execute("SELECT 1 FROM market_trading_schedules LIMIT 0") |
| 58 | self._connection.execute("SELECT 1 FROM market_trading_calendar_exceptions LIMIT 0") |
| 59 | self._connection.commit() |
| 60 | except Exception as exc: |
| 61 | self._connection.connection.rollback() |
| 62 | raise RuntimeError("RESEARCH_SCHEMA_NOT_MIGRATED") from exc |
| 63 | |
| 64 | |
| 65 | class _PostgresConnectionAdapter: |
| 66 | def __init__(self, connection) -> None: |
| 67 | self.connection = connection |
| 68 | |
| 69 | def execute(self, sql: str, params=None): |
| 70 | statement = sql.replace("?", "%s") |
| 71 | if "INSERT OR IGNORE INTO" in statement: |
| 72 | statement = statement.replace("INSERT OR IGNORE INTO", "INSERT INTO") + " ON CONFLICT DO NOTHING" |
| 73 | return self.connection.execute(statement, params) |
| 74 | |
| 75 | def executescript(self, sql: str) -> None: |
| 76 | self.connection.execute(sql) |
| 77 | |
| 78 | def commit(self) -> None: |
| 79 | self.connection.commit() |
| 80 | |
| 81 | def __enter__(self): |
| 82 | return self |
| 83 | |
| 84 | def __exit__(self, exc_type, exc, tb): |
| 85 | if exc_type is None: |
| 86 | self.connection.commit() |
| 87 | else: |
| 88 | self.connection.rollback() |
| 89 | return False |
| 90 | |
| 91 | |
| 92 | def _validate_identifier(value: str) -> None: |
| 93 | if not value or not value.replace("_", "").isalnum() or value[0].isdigit(): |
| 94 | raise RuntimeError("Invalid research database schema identifier") |