90 lines
2.9 KiB
Python
90 lines
2.9 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Verification script to check that all soft deletion columns and indexes are properly created.
|
|
"""
|
|
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
def verify_soft_deletion_schema():
|
|
"""Verify that all soft deletion columns and indexes exist."""
|
|
|
|
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:
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
# Tables to check
|
|
tables = [
|
|
"shots",
|
|
"assets",
|
|
"tasks",
|
|
"submissions",
|
|
"task_attachments",
|
|
"production_notes",
|
|
"reviews"
|
|
]
|
|
|
|
print("=== Soft Deletion Schema Verification ===\n")
|
|
|
|
all_verified = True
|
|
|
|
for table_name in tables:
|
|
print(f"Checking {table_name} table:")
|
|
|
|
# Check table structure
|
|
cursor.execute(f"PRAGMA table_info({table_name})")
|
|
columns = {column[1]: column[2] for column in cursor.fetchall()}
|
|
|
|
# Check for soft deletion columns
|
|
has_deleted_at = 'deleted_at' in columns
|
|
has_deleted_by = 'deleted_by' in columns
|
|
|
|
if has_deleted_at and has_deleted_by:
|
|
print(f" SUCCESS: Soft deletion columns present")
|
|
else:
|
|
print(f" ERROR: Missing soft deletion columns")
|
|
if not has_deleted_at:
|
|
print(f" - deleted_at column missing")
|
|
if not has_deleted_by:
|
|
print(f" - deleted_by column missing")
|
|
all_verified = False
|
|
|
|
# Check for partial index
|
|
cursor.execute(f"PRAGMA index_list({table_name})")
|
|
indexes = [index[1] for index in cursor.fetchall()]
|
|
|
|
expected_index = f"idx_{table_name}_not_deleted"
|
|
if expected_index in indexes:
|
|
print(f" SUCCESS: Partial index {expected_index} exists")
|
|
else:
|
|
print(f" ERROR: Partial index {expected_index} missing")
|
|
all_verified = False
|
|
|
|
print()
|
|
|
|
if all_verified:
|
|
print("SUCCESS: All soft deletion schema changes verified!")
|
|
print("\nSummary:")
|
|
print("- All tables have deleted_at and deleted_by columns")
|
|
print("- All tables have partial indexes for efficient querying")
|
|
print("- Schema is ready for soft deletion implementation")
|
|
else:
|
|
print("ERROR: Some schema changes are missing!")
|
|
|
|
return all_verified
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
verify_soft_deletion_schema() |