75 lines
2.7 KiB
Python
75 lines
2.7 KiB
Python
"""
|
|
Migration script to add activities table to the database.
|
|
"""
|
|
from sqlalchemy import create_engine, text
|
|
from database import DATABASE_URL
|
|
import logging
|
|
|
|
logging.basicConfig(level=logging.INFO)
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def migrate():
|
|
"""Add activities table to the database."""
|
|
engine = create_engine(DATABASE_URL)
|
|
|
|
with engine.connect() as conn:
|
|
try:
|
|
# Create activities table
|
|
logger.info("Creating activities table...")
|
|
conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS 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
|
|
)
|
|
"""))
|
|
|
|
# Create indexes for activities
|
|
logger.info("Creating indexes for activities table...")
|
|
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)
|
|
"""))
|
|
|
|
conn.commit()
|
|
logger.info("✅ Activities table created successfully!")
|
|
|
|
except Exception as e:
|
|
logger.error(f"❌ Migration failed: {str(e)}")
|
|
conn.rollback()
|
|
raise
|
|
|
|
|
|
if __name__ == "__main__":
|
|
migrate()
|