335 lines
12 KiB
Python
335 lines
12 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Database index optimization script for VFX Project Management System.
|
|
Adds missing indexes to improve query performance, especially for soft deletion filtering.
|
|
"""
|
|
|
|
import sqlite3
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
def optimize_database_indexes():
|
|
"""Add missing indexes to optimize database performance."""
|
|
|
|
# Database path
|
|
db_path = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
if not db_path.exists():
|
|
print(f"Database file not found at {db_path}")
|
|
return False
|
|
|
|
try:
|
|
# Connect to database
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
print("=== Database Index Optimization ===\n")
|
|
|
|
# Define indexes to create
|
|
indexes_to_create = [
|
|
# Task-related indexes for performance
|
|
{
|
|
"name": "idx_tasks_assigned_user_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "assigned_user_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for tasks by assigned user (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_tasks_status_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "status",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for tasks by status (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_tasks_type_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "task_type",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for tasks by type (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_tasks_project_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "project_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for tasks by project (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_tasks_episode_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "episode_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for tasks by episode (non-deleted)"
|
|
},
|
|
|
|
# Shot-related indexes
|
|
{
|
|
"name": "idx_shots_episode_not_deleted",
|
|
"table": "shots",
|
|
"columns": "episode_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for shots by episode (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_shots_status_not_deleted",
|
|
"table": "shots",
|
|
"columns": "status",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for shots by status (non-deleted)"
|
|
},
|
|
|
|
# Asset-related indexes
|
|
{
|
|
"name": "idx_assets_project_not_deleted",
|
|
"table": "assets",
|
|
"columns": "project_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for assets by project (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_assets_category_not_deleted",
|
|
"table": "assets",
|
|
"columns": "category",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for assets by category (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_assets_status_not_deleted",
|
|
"table": "assets",
|
|
"columns": "status",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for assets by status (non-deleted)"
|
|
},
|
|
|
|
# Submission-related indexes
|
|
{
|
|
"name": "idx_submissions_user_not_deleted",
|
|
"table": "submissions",
|
|
"columns": "user_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for submissions by user (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_submissions_submitted_at_not_deleted",
|
|
"table": "submissions",
|
|
"columns": "submitted_at",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries ordering submissions by date (non-deleted)"
|
|
},
|
|
|
|
# Production notes indexes
|
|
{
|
|
"name": "idx_production_notes_user_not_deleted",
|
|
"table": "production_notes",
|
|
"columns": "user_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for notes by user (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_production_notes_created_at_not_deleted",
|
|
"table": "production_notes",
|
|
"columns": "created_at",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries ordering notes by date (non-deleted)"
|
|
},
|
|
|
|
# Task attachments indexes
|
|
{
|
|
"name": "idx_task_attachments_user_not_deleted",
|
|
"table": "task_attachments",
|
|
"columns": "user_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for attachments by user (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_task_attachments_type_not_deleted",
|
|
"table": "task_attachments",
|
|
"columns": "attachment_type",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for attachments by type (non-deleted)"
|
|
},
|
|
|
|
# Reviews indexes
|
|
{
|
|
"name": "idx_reviews_reviewer_not_deleted",
|
|
"table": "reviews",
|
|
"columns": "reviewer_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for reviews by reviewer (non-deleted)"
|
|
},
|
|
{
|
|
"name": "idx_reviews_decision_not_deleted",
|
|
"table": "reviews",
|
|
"columns": "decision",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for reviews by decision (non-deleted)"
|
|
},
|
|
|
|
# Foreign key indexes for better join performance
|
|
{
|
|
"name": "idx_project_members_user_id",
|
|
"table": "project_members",
|
|
"columns": "user_id",
|
|
"condition": "",
|
|
"description": "Optimize joins on project members by user"
|
|
},
|
|
{
|
|
"name": "idx_project_members_project_id",
|
|
"table": "project_members",
|
|
"columns": "project_id",
|
|
"condition": "",
|
|
"description": "Optimize joins on project members by project"
|
|
},
|
|
{
|
|
"name": "idx_episodes_project_id",
|
|
"table": "episodes",
|
|
"columns": "project_id",
|
|
"condition": "",
|
|
"description": "Optimize queries for episodes by project"
|
|
},
|
|
|
|
# Composite indexes for complex queries
|
|
{
|
|
"name": "idx_tasks_composite_not_deleted",
|
|
"table": "tasks",
|
|
"columns": "project_id, status, assigned_user_id",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize complex task queries (project + status + user)"
|
|
},
|
|
{
|
|
"name": "idx_submissions_task_version_not_deleted",
|
|
"table": "submissions",
|
|
"columns": "task_id, version_number",
|
|
"condition": "WHERE deleted_at IS NULL",
|
|
"description": "Optimize queries for latest submission versions"
|
|
}
|
|
]
|
|
|
|
created_count = 0
|
|
skipped_count = 0
|
|
|
|
for index_def in indexes_to_create:
|
|
index_name = index_def["name"]
|
|
table_name = index_def["table"]
|
|
columns = index_def["columns"]
|
|
condition = index_def["condition"]
|
|
description = index_def["description"]
|
|
|
|
# Check if index already exists
|
|
cursor.execute(f"PRAGMA index_list({table_name})")
|
|
existing_indexes = [idx[1] for idx in cursor.fetchall()]
|
|
|
|
if index_name in existing_indexes:
|
|
print(f"SKIP: Index {index_name} already exists")
|
|
skipped_count += 1
|
|
continue
|
|
|
|
# Create the index
|
|
try:
|
|
sql = f"CREATE INDEX {index_name} ON {table_name} ({columns})"
|
|
if condition:
|
|
sql += f" {condition}"
|
|
|
|
print(f"Creating index: {index_name}")
|
|
print(f" Table: {table_name}")
|
|
print(f" Columns: {columns}")
|
|
print(f" Description: {description}")
|
|
|
|
cursor.execute(sql)
|
|
created_count += 1
|
|
print(f"SUCCESS: Created index {index_name}")
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"ERROR: Failed to create index {index_name}: {e}")
|
|
continue
|
|
|
|
print()
|
|
|
|
# Commit all changes
|
|
conn.commit()
|
|
|
|
print(f"=== Index Optimization Complete ===")
|
|
print(f"Created: {created_count} new indexes")
|
|
print(f"Skipped: {skipped_count} existing indexes")
|
|
|
|
return True
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
if conn:
|
|
conn.rollback()
|
|
return False
|
|
except Exception as e:
|
|
print(f"Unexpected error: {e}")
|
|
if conn:
|
|
conn.rollback()
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def verify_index_optimization():
|
|
"""Verify that the index optimization was successful."""
|
|
|
|
db_path = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
try:
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
print("\n=== Verifying Index Optimization ===\n")
|
|
|
|
# Count total indexes
|
|
cursor.execute("""
|
|
SELECT COUNT(*)
|
|
FROM sqlite_master
|
|
WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
|
|
""")
|
|
total_indexes = cursor.fetchone()[0]
|
|
|
|
print(f"Total indexes in database: {total_indexes}")
|
|
|
|
# Check key performance indexes
|
|
key_indexes = [
|
|
"idx_tasks_assigned_user_not_deleted",
|
|
"idx_tasks_status_not_deleted",
|
|
"idx_shots_episode_not_deleted",
|
|
"idx_assets_project_not_deleted",
|
|
"idx_submissions_submitted_at_not_deleted"
|
|
]
|
|
|
|
print("\nKey performance indexes:")
|
|
for index_name in key_indexes:
|
|
cursor.execute("""
|
|
SELECT name FROM sqlite_master
|
|
WHERE type = 'index' AND name = ?
|
|
""", (index_name,))
|
|
|
|
if cursor.fetchone():
|
|
print(f" ✓ {index_name}")
|
|
else:
|
|
print(f" ✗ {index_name} (missing)")
|
|
|
|
return True
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Verification error: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
print("Database Index Optimization Tool")
|
|
print("=" * 50)
|
|
|
|
success = optimize_database_indexes()
|
|
|
|
if success:
|
|
verify_index_optimization()
|
|
print("\nSUCCESS: Database index optimization completed!")
|
|
else:
|
|
print("\n❌ Database index optimization failed!")
|
|
sys.exit(1) |