64 lines
2.1 KiB
Python
64 lines
2.1 KiB
Python
"""Migration: Create tryout_coaches association table and migrate existing data.
|
|
|
|
Run this script to create the many-to-many relationship between tryouts and coaches.
|
|
Usage: python migrations/add_tryout_coaches.py
|
|
"""
|
|
|
|
import sys
|
|
import os
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|
|
|
from app.app import create_app
|
|
from app.extensions import db
|
|
from sqlalchemy import text
|
|
|
|
app = create_app()
|
|
|
|
with app.app_context():
|
|
# Check if table already exists
|
|
result = db.session.execute(text(
|
|
"SELECT COUNT(*) FROM information_schema.tables "
|
|
"WHERE table_name = 'tryout_coaches'"
|
|
))
|
|
exists = result.scalar() > 0
|
|
|
|
if exists:
|
|
print("Table 'tryout_coaches' already exists. Skipping creation.")
|
|
else:
|
|
db.session.execute(text("""
|
|
CREATE TABLE tryout_coaches (
|
|
tryout_id INTEGER NOT NULL,
|
|
coach_id INTEGER NOT NULL,
|
|
PRIMARY KEY (tryout_id, coach_id),
|
|
FOREIGN KEY (tryout_id) REFERENCES tryouts (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (coach_id) REFERENCES users (id) ON DELETE CASCADE
|
|
)
|
|
"""))
|
|
db.session.commit()
|
|
print("Created 'tryout_coaches' association table.")
|
|
|
|
# Migrate existing coach_id data into the new table
|
|
result = db.session.execute(text(
|
|
"SELECT COUNT(*) FROM tryouts WHERE coach_id IS NOT NULL"
|
|
))
|
|
count = result.scalar()
|
|
|
|
if count > 0:
|
|
# Check how many already migrated
|
|
migrated = db.session.execute(text(
|
|
"SELECT COUNT(*) FROM tryout_coaches"
|
|
)).scalar()
|
|
|
|
if migrated == 0:
|
|
db.session.execute(text("""
|
|
INSERT INTO tryout_coaches (tryout_id, coach_id)
|
|
SELECT id, coach_id FROM tryouts WHERE coach_id IS NOT NULL
|
|
"""))
|
|
db.session.commit()
|
|
print(f"Migrated {count} existing coach assignments to tryout_coaches.")
|
|
else:
|
|
print(f"Skipping data migration — {migrated} rows already exist in tryout_coaches.")
|
|
else:
|
|
print("No existing coach assignments to migrate.")
|
|
|
|
print("Migration complete.") |