tehreemfatimaTF/phase-3-bot

1"""2Database optimization script for Todo Web Application3 4This script creates indexes on frequently queried columns to improve performance.5Run this after initial database setup or when deploying to production.6"""7 8from sqlmodel import create_engine, text9import os10from dotenv import load_dotenv11 12load_dotenv()13 14DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./todo_app.db")15engine = create_engine(DATABASE_URL, echo=True)16 17 18def create_indexes():19    """Create database indexes for optimized query performance"""20 21    with engine.connect() as conn:22        print("Creating database indexes...")23 24        # Index on tasks.user_id for fast user task lookups25        # Most common query: SELECT * FROM tasks WHERE user_id = ?26        try:27            conn.execute(text(28                "CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON task(user_id)"29            ))30            print("✓ Created index on tasks.user_id")31        except Exception as e:32            print(f"✗ Index on tasks.user_id: {e}")33 34        # Index on tasks.completed for filtering by completion status35        # Common query: SELECT * FROM tasks WHERE user_id = ? AND completed = ?36        try:37            conn.execute(text(38                "CREATE INDEX IF NOT EXISTS idx_tasks_completed ON task(completed)"39            ))40            print("✓ Created index on tasks.completed")41        except Exception as e:42            print(f"✗ Index on tasks.completed: {e}")43 44        # Composite index on (user_id, completed) for filtered user queries45        # Optimizes: SELECT * FROM tasks WHERE user_id = ? AND completed = ?46        try:47            conn.execute(text(48                "CREATE INDEX IF NOT EXISTS idx_tasks_user_completed ON task(user_id, completed)"49            ))50            print("✓ Created composite index on tasks(user_id, completed)")51        except Exception as e:52            print(f"✗ Composite index on tasks(user_id, completed): {e}")53 54        # Index on tasks.created_at for sorting by creation date55        # Common query: SELECT * FROM tasks WHERE user_id = ? ORDER BY created_at DESC56        try:57            conn.execute(text(58                "CREATE INDEX IF NOT EXISTS idx_tasks_created_at ON task(created_at)"59            ))60            print("✓ Created index on tasks.created_at")61        except Exception as e:62            print(f"✗ Index on tasks.created_at: {e}")63 64        # Index on tasks.due_date for filtering by due date65        # Useful for future features like "tasks due today"66        try:67            conn.execute(text(68                "CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON task(due_date)"69            ))70            print("✓ Created index on tasks.due_date")71        except Exception as e:72            print(f"✗ Index on tasks.due_date: {e}")73 74        # Index on users.email for fast login lookups75        # Most common auth query: SELECT * FROM users WHERE email = ?76        try:77            conn.execute(text(78                "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON user(email)"79            ))80            print("✓ Created unique index on users.email")81        except Exception as e:82            print(f"✗ Index on users.email: {e}")83 84        conn.commit()85        print("\n✓ All indexes created successfully!")86 87 88def analyze_query_performance():89    """Analyze query performance with EXPLAIN"""90 91    with engine.connect() as conn:92        print("\n" + "="*60)93        print("Query Performance Analysis")94        print("="*60)95 96        # Test query 1: Get all tasks for a user97        print("\n1. Query: Get all tasks for a user")98        print("   SELECT * FROM task WHERE user_id = ?")99        try:100            result = conn.execute(text(101                "EXPLAIN QUERY PLAN SELECT * FROM task WHERE user_id = 'test-user-id'"102            ))103            for row in result:104                print(f"   {row}")105        except Exception as e:106            print(f"   Analysis not available: {e}")107 108        # Test query 2: Get completed tasks for a user109        print("\n2. Query: Get completed tasks for a user")110        print("   SELECT * FROM task WHERE user_id = ? AND completed = true")111        try:112            result = conn.execute(text(113                "EXPLAIN QUERY PLAN SELECT * FROM task WHERE user_id = 'test-user-id' AND completed = 1"114            ))115            for row in result:116                print(f"   {row}")117        except Exception as e:118            print(f"   Analysis not available: {e}")119 120        # Test query 3: Get user by email121        print("\n3. Query: Get user by email (login)")122        print("   SELECT * FROM user WHERE email = ?")123        try:124            result = conn.execute(text(125                "EXPLAIN QUERY PLAN SELECT * FROM user WHERE email = 'test@example.com'"126            ))127            for row in result:128                print(f"   {row}")129        except Exception as e:130            print(f"   Analysis not available: {e}")131 132 133def show_index_info():134    """Display information about created indexes"""135 136    with engine.connect() as conn:137        print("\n" + "="*60)138        print("Database Indexes")139        print("="*60)140 141        try:142            # SQLite specific query to show indexes143            result = conn.execute(text(144                "SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name IN ('task', 'user')"145            ))146 147            for row in result:148                print(f"\nIndex: {row[0]}")149                print(f"Table: {row[1]}")150                if row[2]:151                    print(f"SQL: {row[2]}")152        except Exception as e:153            print(f"Could not retrieve index information: {e}")154 155 156if __name__ == "__main__":157    print("="*60)158    print("Database Optimization Script")159    print("="*60)160    print(f"Database: {DATABASE_URL}")161    print()162 163    # Create indexes164    create_indexes()165 166    # Show index information167    show_index_info()168 169    # Analyze query performance170    analyze_query_performance()171 172    print("\n" + "="*60)173    print("Optimization Complete!")174    print("="*60)175    print("\nRecommendations:")176    print("1. Run this script after initial database setup")177    print("2. Re-run after significant schema changes")178    print("3. Monitor query performance in production")179    print("4. Consider additional indexes based on usage patterns")180