46 lines
1.4 KiB
Python
46 lines
1.4 KiB
Python
"""phase13 — add facility_id to issues, make area_id nullable
|
|
|
|
Revision ID: phase13_issue_facility
|
|
Revises: phase12_performance_indexes
|
|
"""
|
|
|
|
revision = 'phase13_issue_facility'
|
|
down_revision = 'phase_b_mobile_local_id'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
def upgrade():
|
|
# 1. Add facility_id column (nullable FK to facilities)
|
|
with op.batch_alter_table('issues') as batch_op:
|
|
batch_op.add_column(
|
|
sa.Column('facility_id', sa.Integer(),
|
|
sa.ForeignKey('facilities.id', ondelete='SET NULL'),
|
|
nullable=True)
|
|
)
|
|
|
|
# 2. Back-fill facility_id for all existing issues that have an area
|
|
op.execute("""
|
|
UPDATE issues
|
|
JOIN areas ON issues.area_id = areas.id
|
|
SET issues.facility_id = areas.facility_id
|
|
WHERE issues.area_id IS NOT NULL
|
|
""")
|
|
|
|
# 3. Make area_id nullable (was nullable=False)
|
|
with op.batch_alter_table('issues') as batch_op:
|
|
batch_op.alter_column('area_id',
|
|
existing_type=sa.Integer(),
|
|
nullable=True)
|
|
|
|
|
|
def downgrade():
|
|
# Restore area_id to non-nullable (requires no NULL rows)
|
|
with op.batch_alter_table('issues') as batch_op:
|
|
batch_op.alter_column('area_id',
|
|
existing_type=sa.Integer(),
|
|
nullable=False)
|
|
batch_op.drop_column('facility_id') |