from __future__ import annotations import argparse import json import os import sqlite3 from datetime import datetime from typing import Any from .db import init_db, session_scope from .models import BacktestRecord, Task def _parse_dt(value: Any) -> datetime | None: if value is None: return None if isinstance(value, datetime): return value if isinstance(value, (int, float)): try: return datetime.utcfromtimestamp(float(value)) except Exception: return None s = str(value).strip() if not s: return None s = s.replace('Z', '+00:00') try: return datetime.fromisoformat(s) except Exception: return None def _parse_json(value: Any) -> Any: if value is None: return None if isinstance(value, (dict, list)): return value if isinstance(value, (bytes, bytearray)): try: value = value.decode('utf-8', errors='replace') except Exception: return None if isinstance(value, str): s = value.strip() if not s: return None try: return json.loads(s) except Exception: return None return None def _sqlite_table_exists(conn: sqlite3.Connection, name: str) -> bool: cur = conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1", (name,), ) return cur.fetchone() is not None def _sqlite_fetch_all(conn: sqlite3.Connection, table: str) -> list[dict[str, Any]]: conn.row_factory = sqlite3.Row cur = conn.execute(f'SELECT * FROM {table}') return [dict(r) for r in cur.fetchall()] def migrate_v2_sqlite(sqlite_path: str, *, overwrite: bool) -> dict[str, int]: stats = { 'tasks_total': 0, 'tasks_migrated': 0, 'tasks_skipped': 0, 'records_total': 0, 'records_migrated': 0, 'records_skipped': 0, } if not os.path.exists(sqlite_path): return stats conn = sqlite3.connect(sqlite_path) try: if _sqlite_table_exists(conn, 'tasks'): rows = _sqlite_fetch_all(conn, 'tasks') stats['tasks_total'] = len(rows) for r in rows: task_id = str(r.get('id') or '').strip() if not task_id: continue payload = { 'id': task_id, 'user_id': (str(r.get('user_id')) if r.get('user_id') is not None else None), 'type': str(r.get('type') or ''), 'status': str(r.get('status') or ''), 'progress': int(r.get('progress') or 0), 'stage': (str(r.get('stage')) if r.get('stage') is not None else None), 'message': (str(r.get('message')) if r.get('message') is not None else None), 'params_json': _parse_json(r.get('params_json')), 'result_json': _parse_json(r.get('result_json')), 'file_path': (str(r.get('file_path')) if r.get('file_path') is not None else None), 'error': (str(r.get('error')) if r.get('error') is not None else None), 'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(), 'started_at': _parse_dt(r.get('started_at')), 'finished_at': _parse_dt(r.get('finished_at')), } with session_scope() as s: existing = s.get(Task, task_id) if existing is not None and not overwrite: stats['tasks_skipped'] += 1 continue if existing is None: s.add(Task(**payload)) else: for k, v in payload.items(): if k == 'id': continue setattr(existing, k, v) stats['tasks_migrated'] += 1 if _sqlite_table_exists(conn, 'backtest_records_v2'): rows = _sqlite_fetch_all(conn, 'backtest_records_v2') stats['records_total'] = len(rows) for r in rows: rid = str(r.get('id') or '').strip() if not rid: continue parameters = _parse_json(r.get('parameters')) or {} results = _parse_json(r.get('results')) payload = { 'id': rid, 'user_id': (str(r.get('user_id')) if r.get('user_id') is not None else None), 'strategy_type': str(r.get('strategy_type') or ''), 'symbol': str(r.get('symbol') or ''), 'start_date': (str(r.get('start_date')) if r.get('start_date') is not None else None), 'end_date': (str(r.get('end_date')) if r.get('end_date') is not None else None), 'parameters': parameters, 'results': results, 'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(), } with session_scope() as s: existing = s.get(BacktestRecord, rid) if existing is not None and not overwrite: stats['records_skipped'] += 1 continue if existing is None: s.add(BacktestRecord(**payload)) else: for k, v in payload.items(): if k == 'id': continue setattr(existing, k, v) stats['records_migrated'] += 1 return stats finally: conn.close() def migrate_legacy_sqlite(sqlite_path: str, *, overwrite: bool) -> dict[str, int]: stats = { 'legacy_total': 0, 'legacy_migrated': 0, 'legacy_skipped': 0, } if not os.path.exists(sqlite_path): return stats conn = sqlite3.connect(sqlite_path) try: if not _sqlite_table_exists(conn, 'backtest_records'): return stats rows = _sqlite_fetch_all(conn, 'backtest_records') stats['legacy_total'] = len(rows) for r in rows: rid = str(r.get('id') or '').strip() if not rid: continue strategy_type = str(r.get('strategy_type') or '').strip() symbol = str(r.get('symbol') or '').strip() parameters = _parse_json(r.get('parameters')) or {} results = _parse_json(r.get('results')) initial_capital = r.get('initial_capital') if initial_capital is not None and 'initial_capital' not in parameters: try: parameters['initial_capital'] = float(initial_capital) except Exception: pass payload = { 'id': rid, 'user_id': None, 'strategy_type': strategy_type, 'symbol': symbol, 'start_date': (str(r.get('start_date')) if r.get('start_date') is not None else None), 'end_date': (str(r.get('end_date')) if r.get('end_date') is not None else None), 'parameters': parameters, 'results': results, 'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(), } with session_scope() as s: existing = s.get(BacktestRecord, rid) if existing is not None and not overwrite: stats['legacy_skipped'] += 1 continue if existing is None: s.add(BacktestRecord(**payload)) else: for k, v in payload.items(): if k == 'id': continue setattr(existing, k, v) stats['legacy_migrated'] += 1 return stats finally: conn.close() def _default_backend_data_file(name: str) -> str: backend_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) return os.path.join(backend_dir, 'data', name) def main() -> int: parser = argparse.ArgumentParser(description='Migrate WGJY SQLite databases to PostgreSQL (Postgres-only).') parser.add_argument('--v2-sqlite', default=_default_backend_data_file('wgjy_v2.db')) parser.add_argument('--legacy-sqlite', default=_default_backend_data_file('backtest.db')) parser.add_argument('--overwrite', action='store_true', help='Overwrite existing rows in PostgreSQL') args = parser.parse_args() init_db() v2_stats = migrate_v2_sqlite(args.v2_sqlite, overwrite=args.overwrite) legacy_stats = migrate_legacy_sqlite(args.legacy_sqlite, overwrite=args.overwrite) print('=== Migration summary ===') print(f"v2 sqlite: {args.v2_sqlite}") print(f" tasks: {v2_stats['tasks_migrated']}/{v2_stats['tasks_total']} migrated, {v2_stats['tasks_skipped']} skipped") print( f" backtest_records_v2: {v2_stats['records_migrated']}/{v2_stats['records_total']} migrated, {v2_stats['records_skipped']} skipped" ) print(f"legacy sqlite: {args.legacy_sqlite}") print( f" backtest_records: {legacy_stats['legacy_migrated']}/{legacy_stats['legacy_total']} migrated, {legacy_stats['legacy_skipped']} skipped" ) print('=========================') return 0 if __name__ == '__main__': raise SystemExit(main())