scratch/check_db_schema.py

import sqlite3
from pathlib import Path

db_path = Path(__file__).resolve().parent.parent / "data" / "quantforge_v2.db"
print(f"Checking database at {db_path}...")

if db_path.exists():
    conn = sqlite3.connect(str(db_path))
    cursor = conn.cursor()
    
    # Check tables
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [t[0] for t in cursor.fetchall()]
    print(f"Tables: {tables}")
    
    if "alphas" in tables:
        cursor.execute("PRAGMA table_info(alphas)")
        columns = [col[1] for col in cursor.fetchall()]
        print(f"Columns in alphas: {columns}")
        
        # List of expected columns in alphas
        expected_columns = {
            "trash_reason": "VARCHAR",
            "pasteurization": "VARCHAR NOT NULL DEFAULT 'On'",
            "nan_handling": "VARCHAR",
            "unit_handling": "VARCHAR",
            "lookback": "INTEGER",
            "test_period": "VARCHAR",
            "max_trade": "FLOAT",
            "max_position": "FLOAT",
            "language": "VARCHAR NOT NULL DEFAULT 'FASTEXPR'"
        }
        
        for col_name, col_type in expected_columns.items():
            if col_name not in columns:
                print(f"Adding column '{col_name}' to 'alphas' table...")
                try:
                    cursor.execute(f"ALTER TABLE alphas ADD COLUMN {col_name} {col_type}")
                    print(f"Successfully added '{col_name}' column.")
                except Exception as e:
                    print(f"Failed to add column '{col_name}': {e}")
        
        conn.commit()
    conn.close()
    print("Verification completed.")
else:
    print("Database file does not exist.")