9d0d29ef18
Database Infrastructure: - Added Alembic migration system (alembic.ini, env.py) - Migration 001: Initial schema - Migration 004: Stat materialized views (enhanced) - Migration 005: Composite indexes for performance - operations.py: Session injection support for test isolation - session.py: Enhanced session management Application Updates: - main.py: Integration with new database infrastructure - health.py: Enhanced health checks with pool monitoring Integration Tests: - conftest.py: Session injection pattern for reliable tests - test_operations.py: Database operations tests - test_migrations.py: Migration verification tests Session injection pattern enables: - Production: Auto-commit per operation - Testing: Shared session with automatic rollback - Transactions: Multiple ops, single commit 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
81 lines
2.2 KiB
Python
81 lines
2.2 KiB
Python
"""Add composite indexes for common query patterns.
|
|
|
|
Creates indexes to optimize:
|
|
- Game recovery (plays by game_id + play_number)
|
|
- Team lineup queries (game_id + team_id + is_active)
|
|
- Active player queries (game_id + is_active)
|
|
- Roll history queries (game_id + roll_type)
|
|
- Game listing queries (status + created_at)
|
|
|
|
Revision ID: 005
|
|
Revises: 004
|
|
Create Date: 2025-11-27
|
|
|
|
"""
|
|
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
|
|
revision: str = '005'
|
|
down_revision: Union[str, Sequence[str], None] = '004'
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
"""Create composite indexes for common query patterns."""
|
|
# Plays table - optimize game recovery and play history
|
|
# Used by: get_plays_for_game, state recovery
|
|
op.create_index(
|
|
'idx_play_game_number',
|
|
'plays',
|
|
['game_id', 'play_number'],
|
|
unique=False
|
|
)
|
|
|
|
# Lineups table - optimize team lineup queries
|
|
# Used by: get_lineup_for_team, substitution lookups
|
|
op.create_index(
|
|
'idx_lineup_game_team_active',
|
|
'lineups',
|
|
['game_id', 'team_id', 'is_active'],
|
|
unique=False
|
|
)
|
|
|
|
# Lineups table - optimize active player queries
|
|
# Used by: get_active_players
|
|
op.create_index(
|
|
'idx_lineup_game_active',
|
|
'lineups',
|
|
['game_id', 'is_active'],
|
|
unique=False
|
|
)
|
|
|
|
# Rolls table - optimize roll history queries
|
|
# Used by: get_rolls_for_game, roll auditing
|
|
op.create_index(
|
|
'idx_roll_game_type',
|
|
'rolls',
|
|
['game_id', 'roll_type'],
|
|
unique=False
|
|
)
|
|
|
|
# Games table - optimize status queries
|
|
# Used by: list_active_games, game browsing
|
|
op.create_index(
|
|
'idx_game_status_created',
|
|
'games',
|
|
['status', 'created_at'],
|
|
unique=False
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
"""Remove composite indexes."""
|
|
op.drop_index('idx_play_game_number', table_name='plays')
|
|
op.drop_index('idx_lineup_game_team_active', table_name='lineups')
|
|
op.drop_index('idx_lineup_game_active', table_name='lineups')
|
|
op.drop_index('idx_roll_game_type', table_name='rolls')
|
|
op.drop_index('idx_game_status_created', table_name='games')
|