92 lines
3.4 KiB
Python
92 lines
3.4 KiB
Python
"""
|
|
Migration script to add custom_task_statuses column to projects table
|
|
and convert task status from enum to string.
|
|
"""
|
|
import sqlite3
|
|
import json
|
|
from pathlib import Path
|
|
|
|
# Database path
|
|
DB_PATH = Path(__file__).parent / "vfx_project_management.db"
|
|
|
|
|
|
def migrate_database():
|
|
"""Add custom_task_statuses column and migrate task status to string"""
|
|
conn = sqlite3.connect(DB_PATH)
|
|
cursor = conn.cursor()
|
|
|
|
try:
|
|
print("Starting migration for custom task statuses...")
|
|
|
|
# Check if column already exists in projects table
|
|
cursor.execute("PRAGMA table_info(projects)")
|
|
columns = [col[1] for col in cursor.fetchall()]
|
|
|
|
# Add custom_task_statuses column if it doesn't exist
|
|
if 'custom_task_statuses' not in columns:
|
|
print("Adding custom_task_statuses column to projects table...")
|
|
cursor.execute("""
|
|
ALTER TABLE projects
|
|
ADD COLUMN custom_task_statuses TEXT
|
|
""")
|
|
# Initialize with empty arrays
|
|
cursor.execute("""
|
|
UPDATE projects
|
|
SET custom_task_statuses = '[]'
|
|
WHERE custom_task_statuses IS NULL
|
|
""")
|
|
print("✓ Added custom_task_statuses column")
|
|
else:
|
|
print("✓ custom_task_statuses column already exists")
|
|
|
|
# Note: SQLite doesn't support changing column types directly
|
|
# The status column in tasks table will remain as TEXT in the database
|
|
# The Enum constraint was only enforced at the application level
|
|
print("✓ Task status column already supports string values")
|
|
|
|
# Verify existing task statuses and convert uppercase to lowercase
|
|
cursor.execute("SELECT DISTINCT status FROM tasks")
|
|
existing_statuses = [row[0] for row in cursor.fetchall()]
|
|
print(f"✓ Existing task statuses in database: {existing_statuses}")
|
|
|
|
# Convert uppercase enum values to lowercase string values
|
|
status_mapping = {
|
|
'NOT_STARTED': 'not_started',
|
|
'IN_PROGRESS': 'in_progress',
|
|
'SUBMITTED': 'submitted',
|
|
'APPROVED': 'approved',
|
|
'RETAKE': 'retake'
|
|
}
|
|
|
|
for old_status, new_status in status_mapping.items():
|
|
cursor.execute(
|
|
"UPDATE tasks SET status = ? WHERE status = ?",
|
|
(new_status, old_status)
|
|
)
|
|
updated_count = cursor.rowcount
|
|
if updated_count > 0:
|
|
print(f"✓ Converted {updated_count} tasks from '{old_status}' to '{new_status}'")
|
|
|
|
# Verify conversion
|
|
cursor.execute("SELECT DISTINCT status FROM tasks")
|
|
updated_statuses = [row[0] for row in cursor.fetchall()]
|
|
print(f"✓ Updated task statuses in database: {updated_statuses}")
|
|
|
|
conn.commit()
|
|
print("\n✅ Migration completed successfully!")
|
|
print("\nNext steps:")
|
|
print("1. System statuses remain available: not_started, in_progress, submitted, approved, retake")
|
|
print("2. Projects can now define custom statuses via the custom_task_statuses JSON column")
|
|
print("3. Tasks can use both system and custom status values")
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
print(f"\n❌ Migration failed: {e}")
|
|
raise
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
migrate_database()
|