indigo
c09710f4e5
Departments are now a customizable per-project list (standard + custom), matching the existing custom task type/status pattern, instead of a fixed 7-value enum. They're usable directly on tasks (new field, independent of assignee) and continue to drive team member department roles.
112 lines
3.8 KiB
Python
112 lines
3.8 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Migration script to add the custom_departments column to the projects table.
|
|
|
|
Usage:
|
|
python migrate_project_custom_departments.py
|
|
"""
|
|
|
|
import sqlite3
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
|
|
def get_database_path():
|
|
"""Get the database path, trying multiple possible locations."""
|
|
possible_paths = [
|
|
"vfx_project_management.db", # Primary database
|
|
"database.db",
|
|
"../vfx_project_management.db"
|
|
]
|
|
|
|
for path in possible_paths:
|
|
if Path(path).exists():
|
|
return path
|
|
|
|
return "vfx_project_management.db"
|
|
|
|
|
|
def check_column_exists(cursor, table_name, column_name):
|
|
"""Check if a column exists in a table."""
|
|
cursor.execute(f"PRAGMA table_info({table_name})")
|
|
columns = [column[1] for column in cursor.fetchall()]
|
|
return column_name in columns
|
|
|
|
|
|
def migrate_database():
|
|
"""Add custom_departments column to the projects table."""
|
|
db_path = get_database_path()
|
|
print(f"Using database: {db_path}")
|
|
|
|
conn = None
|
|
try:
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='projects'")
|
|
if not cursor.fetchone():
|
|
print("Projects table not found. Creating new database schema...")
|
|
conn.close()
|
|
return
|
|
|
|
if check_column_exists(cursor, "projects", "custom_departments"):
|
|
print("Column custom_departments already exists, skipping...")
|
|
else:
|
|
print("Adding column: custom_departments")
|
|
cursor.execute("ALTER TABLE projects ADD COLUMN custom_departments TEXT")
|
|
cursor.execute("UPDATE projects SET custom_departments = '[]' WHERE custom_departments IS NULL")
|
|
|
|
# project_members.department_role was previously backed by SQLAlchemy's
|
|
# Enum(DepartmentRole) type, which stores the enum MEMBER NAME (e.g. "LAYOUT"),
|
|
# not its value ("layout"). The old Optional[DepartmentRole] schema silently
|
|
# normalized this back to lowercase on read. Now that the column is a plain
|
|
# string, normalize any existing uppercase values so they match the standard
|
|
# department strings used everywhere else.
|
|
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='project_members'")
|
|
if cursor.fetchone():
|
|
department_name_to_value = {
|
|
'LAYOUT': 'layout',
|
|
'ANIMATION': 'animation',
|
|
'LIGHTING': 'lighting',
|
|
'COMPOSITE': 'composite',
|
|
'MODELING': 'modeling',
|
|
'RIGGING': 'rigging',
|
|
'SURFACING': 'surfacing',
|
|
}
|
|
for old_value, new_value in department_name_to_value.items():
|
|
cursor.execute(
|
|
"UPDATE project_members SET department_role = ? WHERE department_role = ?",
|
|
(new_value, old_value)
|
|
)
|
|
if cursor.rowcount > 0:
|
|
print(f"Normalized {cursor.rowcount} project members from '{old_value}' to '{new_value}'")
|
|
|
|
conn.commit()
|
|
|
|
cursor.execute("SELECT COUNT(*) FROM projects")
|
|
project_count = cursor.fetchone()[0]
|
|
print(f"Migration completed successfully! {project_count} projects unaffected (column defaults to '[]').")
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"Database error: {e}")
|
|
if conn:
|
|
conn.rollback()
|
|
sys.exit(1)
|
|
except Exception as e:
|
|
print(f"Unexpected error: {e}")
|
|
if conn:
|
|
conn.rollback()
|
|
sys.exit(1)
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
print("VFX Project Management - Project Custom Departments Migration")
|
|
print("=" * 60)
|
|
|
|
migrate_database()
|
|
|
|
print("\nMigration completed successfully!")
|