flaskbb/migrations/201802012053_ec39ababe957_add_ondelete_cascades.py

"""Add ondelete cascades

Revision ID: ec39ababe957
Revises: 7c3fcf8a3335
Create Date: 2018-02-01 20:53:08.146277

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ec39ababe957'
down_revision = '7c3fcf8a3335'
branch_labels = ()
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('conversations', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key("fk_conversations_user_id", 'users', ['user_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('forumgroups', schema=None) as batch_op:
        batch_op.drop_constraint('fk_forum_id', type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'groups', ['group_id'], ['id'], ondelete='CASCADE')
        batch_op.create_foreign_key('fk_fg_forum_id', 'forums', ['forum_id'], ['id'], ondelete='CASCADE', use_alter=True)

    with op.batch_alter_table('forums', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'categories', ['category_id'], ['id'], ondelete='CASCADE')
        batch_op.create_foreign_key(None, 'users', ['last_post_user_id'], ['id'], ondelete='SET NULL')

    with op.batch_alter_table('forumsread', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_fr_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_fr_forum_id', 'forums', ['forum_id'], ['id'], ondelete='CASCADE', use_alter=True)
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('groups_users', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')
        batch_op.create_foreign_key(None, 'groups', ['group_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('messages', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')
        batch_op.create_foreign_key(None, 'conversations', ['conversation_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('moderators', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_mods_forum_id', 'forums', ['forum_id'], ['id'], ondelete='CASCADE', use_alter=True)
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('plugin_store', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'plugin_registry', ['plugin_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('posts', schema=None) as batch_op:
        batch_op.alter_column('hidden',
               existing_type=sa.BOOLEAN(),
               nullable=True)

    with op.batch_alter_table('settings', schema=None) as batch_op:
        batch_op.drop_constraint('fk_settingsgroup', type_='foreignkey')
        batch_op.create_foreign_key('fk_settingsgroup', 'settingsgroup', ['settingsgroup'], ['key'], ondelete='CASCADE', use_alter=True)

    with op.batch_alter_table('topics', schema=None) as batch_op:
        batch_op.alter_column('hidden',
               existing_type=sa.BOOLEAN(),
               nullable=True)
        batch_op.drop_constraint('fk_topic_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_topic_forum_id', 'forums', ['forum_id'], ['id'], ondelete='CASCADE', use_alter=True)

    with op.batch_alter_table('topicsread', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_tr_forum_id', type_='foreignkey')
        batch_op.drop_constraint('fk_tr_topic_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_tr_topic_id', 'topics', ['topic_id'], ['id'], ondelete='CASCADE', use_alter=True)
        batch_op.create_foreign_key('fk_tr_forum_id', 'forums', ['forum_id'], ['id'], ondelete='CASCADE', use_alter=True)
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')

    with op.batch_alter_table('topictracker', schema=None) as batch_op:
        batch_op.drop_constraint('fk_tracker_topic_id', type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'], ondelete='CASCADE')
        batch_op.create_foreign_key('fk_tracker_topic_id', 'topics', ['topic_id'], ['id'], ondelete='CASCADE', use_alter=True)

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('topictracker', schema=None) as batch_op:
        batch_op.drop_constraint('fk_tracker_topic_id', type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])
        batch_op.create_foreign_key('fk_tracker_topic_id', 'topics', ['topic_id'], ['id'])

    with op.batch_alter_table('topicsread', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_tr_forum_id', type_='foreignkey')
        batch_op.drop_constraint('fk_tr_topic_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_tr_topic_id', 'topics', ['topic_id'], ['id'])
        batch_op.create_foreign_key('fk_tr_forum_id', 'forums', ['forum_id'], ['id'])
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])

    with op.batch_alter_table('topics', schema=None) as batch_op:
        batch_op.drop_constraint('fk_topic_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_topic_forum_id', 'forums', ['forum_id'], ['id'])
        batch_op.alter_column('hidden',
               existing_type=sa.BOOLEAN(),
               nullable=False)

    with op.batch_alter_table('settings', schema=None) as batch_op:
        batch_op.drop_constraint('fk_settingsgroup', type_='foreignkey')
        batch_op.create_foreign_key('fk_settingsgroup', 'settingsgroup', ['settingsgroup'], ['key'])

    with op.batch_alter_table('posts', schema=None) as batch_op:
        batch_op.alter_column('hidden',
               existing_type=sa.BOOLEAN(),
               nullable=False)

    with op.batch_alter_table('plugin_store', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'plugin_registry', ['plugin_id'], ['id'])

    with op.batch_alter_table('moderators', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_mods_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_forum_id', 'forums', ['forum_id'], ['id'])
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])

    with op.batch_alter_table('messages', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])
        batch_op.create_foreign_key(None, 'conversations', ['conversation_id'], ['id'])

    with op.batch_alter_table('groups_users', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])
        batch_op.create_foreign_key(None, 'groups', ['group_id'], ['id'])

    with op.batch_alter_table('forumsread', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint('fk_fr_forum_id', type_='foreignkey')
        batch_op.create_foreign_key('fk_fr_forum_id', 'forums', ['forum_id'], ['id'])
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])

    with op.batch_alter_table('forums', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'categories', ['category_id'], ['id'])
        batch_op.create_foreign_key(None, 'users', ['last_post_user_id'], ['id'])

    with op.batch_alter_table('forumgroups', schema=None) as batch_op:
        batch_op.drop_constraint('fk_fg_forum_id', type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'groups', ['group_id'], ['id'])
        batch_op.create_foreign_key('fk_forum_id', 'forums', ['forum_id'], ['id'])

    with op.batch_alter_table('conversations', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.create_foreign_key(None, 'users', ['user_id'], ['id'])

    # ### end Alembic commands ###