116 lines
4.8 KiB
Python
116 lines
4.8 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Migration script to fix the activity_metadata column name in the activities table.
|
|
"""
|
|
from sqlalchemy import create_engine, text
|
|
from database import DATABASE_URL
|
|
import logging
|
|
|
|
logging.basicConfig(level=logging.INFO)
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def migrate():
|
|
"""Fix the activity_metadata column name in the activities table."""
|
|
engine = create_engine(DATABASE_URL)
|
|
|
|
with engine.connect() as conn:
|
|
try:
|
|
# Check if the column exists with the wrong name
|
|
result = conn.execute(text("PRAGMA table_info(activities)")).fetchall()
|
|
columns = [row[1] for row in result]
|
|
|
|
if 'metadata' in columns and 'activity_metadata' not in columns:
|
|
logger.info("Renaming 'metadata' column to 'activity_metadata'...")
|
|
|
|
# SQLite doesn't support ALTER COLUMN RENAME directly, so we need to recreate the table
|
|
# First, create a backup of the data
|
|
conn.execute(text("""
|
|
CREATE TABLE activities_backup AS
|
|
SELECT * FROM activities
|
|
"""))
|
|
|
|
# Drop the old table
|
|
conn.execute(text("DROP TABLE activities"))
|
|
|
|
# Create the new table with correct column name
|
|
conn.execute(text("""
|
|
CREATE TABLE activities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
type VARCHAR(50) NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
project_id INTEGER,
|
|
task_id INTEGER,
|
|
asset_id INTEGER,
|
|
shot_id INTEGER,
|
|
submission_id INTEGER,
|
|
description TEXT NOT NULL,
|
|
activity_metadata TEXT,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (shot_id) REFERENCES shots(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
|
|
)
|
|
"""))
|
|
|
|
# Copy data back with correct column mapping
|
|
conn.execute(text("""
|
|
INSERT INTO activities (
|
|
id, type, user_id, project_id, task_id, asset_id, shot_id,
|
|
submission_id, description, activity_metadata, created_at
|
|
)
|
|
SELECT
|
|
id, type, user_id, project_id, task_id, asset_id, shot_id,
|
|
submission_id, description, metadata, created_at
|
|
FROM activities_backup
|
|
"""))
|
|
|
|
# Drop the backup table
|
|
conn.execute(text("DROP TABLE activities_backup"))
|
|
|
|
# Recreate indexes
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_activities_type
|
|
ON activities(type)
|
|
"""))
|
|
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_activities_user_id
|
|
ON activities(user_id)
|
|
"""))
|
|
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_activities_project_id
|
|
ON activities(project_id)
|
|
"""))
|
|
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_activities_created_at
|
|
ON activities(created_at)
|
|
"""))
|
|
|
|
logger.info("✅ Column renamed successfully!")
|
|
|
|
elif 'activity_metadata' in columns:
|
|
logger.info("✅ Column 'activity_metadata' already exists - no migration needed")
|
|
else:
|
|
logger.info("Adding 'activity_metadata' column...")
|
|
conn.execute(text("""
|
|
ALTER TABLE activities
|
|
ADD COLUMN activity_metadata TEXT
|
|
"""))
|
|
logger.info("✅ Column added successfully!")
|
|
|
|
conn.commit()
|
|
|
|
except Exception as e:
|
|
logger.error(f"❌ Migration failed: {str(e)}")
|
|
conn.rollback()
|
|
raise
|
|
|
|
|
|
if __name__ == "__main__":
|
|
migrate() |