<reponame>hamhands/pittsburgh-purchasing-suite<filename>migrations/versions/3f80f0c8adf1_add_class_mixins.py<gh_stars>10-100
"""add class mixins

Revision ID: 3f80f0c8adf1
Revises: <PASSWORD>
Create Date: 2015-08-31 12:59:49.789227

"""

# revision identifiers, used by Alembic.
revision = '3f80f0c8adf1'
down_revision = '<PASSWORD>'

from alembic import op
import sqlalchemy as sa

from purchasing.data.models import TRIGGER_TUPLES

def upgrade():
    conn = op.get_bind()
    ### commands auto generated by Alembic - please adjust! ###
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger_insert_update ON {table}
        '''.format(table=table, column=column)))

        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger_delete ON {table}
        '''.format(table=table, column=column)))

        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger ON {table}
        '''.format(table=table, column=column)))

    op.add_column('app_status', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('app_status', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('app_status', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('app_status', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'app_status', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'app_status', 'users', ['updated_by_id'], ['id'])
    op.add_column('category', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('category', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('category', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('category', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'category', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'category', 'users', ['updated_by_id'], ['id'])
    op.add_column('company', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('company', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('company', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('company', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'company', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'company', 'users', ['created_by_id'], ['id'])
    op.add_column('company_contact', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('company_contact', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('company_contact', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('company_contact', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'company_contact', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'company_contact', 'users', ['created_by_id'], ['id'])
    op.add_column('contract', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'contract', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'contract', 'users', ['updated_by_id'], ['id'])
    op.add_column('contract_note', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract_note', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'contract_note', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'contract_note', 'users', ['updated_by_id'], ['id'])
    op.add_column('contract_property', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('contract_property', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract_property', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('contract_property', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'contract_property', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'contract_property', 'users', ['created_by_id'], ['id'])
    op.add_column('contract_stage', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract_stage', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'contract_stage', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'contract_stage', 'users', ['updated_by_id'], ['id'])
    op.add_column('contract_stage_action_item', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('contract_stage_action_item', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract_stage_action_item', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('contract_stage_action_item', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'contract_stage_action_item', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'contract_stage_action_item', 'users', ['updated_by_id'], ['id'])
    op.add_column('contract_type', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('contract_type', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('contract_type', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('contract_type', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'contract_type', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'contract_type', 'users', ['updated_by_id'], ['id'])
    op.add_column('department', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('department', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('department', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('department', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'department', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'department', 'users', ['updated_by_id'], ['id'])
    op.add_column('document', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('document', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('document', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('document', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'document', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'document', 'users', ['created_by_id'], ['id'])
    op.add_column('flow', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('flow', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('flow', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('flow', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'flow', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'flow', 'users', ['updated_by_id'], ['id'])
    op.add_column('line_item', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('line_item', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('line_item', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('line_item', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'line_item', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'line_item', 'users', ['created_by_id'], ['id'])
    op.add_column('opportunity', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('opportunity', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'opportunity', 'users', ['updated_by_id'], ['id'])
    op.add_column('opportunity_document', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('opportunity_document', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('opportunity_document', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('opportunity_document', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'opportunity_document', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'opportunity_document', 'users', ['created_by_id'], ['id'])
    op.add_column('roles', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('roles', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('roles', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('roles', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'roles', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'roles', 'users', ['updated_by_id'], ['id'])
    op.add_column('stage', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('stage', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('stage', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('stage', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'stage', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'stage', 'users', ['updated_by_id'], ['id'])
    op.add_column('stage_property', sa.Column('created_at', sa.DateTime(), nullable=True))
    op.add_column('stage_property', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('stage_property', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('stage_property', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'stage_property', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'stage_property', 'users', ['updated_by_id'], ['id'])
    op.add_column('users', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('users', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('users', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('updated_by_id_fkey', 'users', 'users', ['updated_by_id'], ['id'])
    op.create_foreign_key('created_by_id_fkey', 'users', 'users', ['created_by_id'], ['id'])
    op.add_column('vendor', sa.Column('created_by_id', sa.Integer(), nullable=True))
    op.add_column('vendor', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.add_column('vendor', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.create_foreign_key('created_by_id_fkey', 'vendor', 'users', ['created_by_id'], ['id'])
    op.create_foreign_key('updated_by_id_fkey', 'vendor', 'users', ['updated_by_id'], ['id'])
    ### end Alembic commands ###


def downgrade():
    conn = op.get_bind()
    ### commands auto generated by Alembic - please adjust! ###
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger_insert_update ON {table}
        '''.format(table=table, column=column)))

        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger_delete ON {table}
        '''.format(table=table, column=column)))

        conn.execute(sa.sql.text('''
            CREATE TRIGGER tsv_{table}_{column}_trigger_insert_update AFTER INSERT OR UPDATE OF {column}
            ON {table}
            FOR EACH ROW
            {when}
            EXECUTE PROCEDURE trig_refresh_search_view()
        '''.format(table=table, column=column, when=when)))

        conn.execute(sa.sql.text('''
            CREATE TRIGGER tsv_{table}_{column}_trigger_delete AFTER DELETE
            ON {table}
            FOR EACH ROW
            EXECUTE PROCEDURE trig_refresh_search_view()
        '''.format(table=table, column=column)))

    op.drop_constraint('updated_by_id_fkey', 'vendor', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'vendor', type_='foreignkey')
    op.drop_column('vendor', 'updated_by_id')
    op.drop_column('vendor', 'updated_at')
    op.drop_column('vendor', 'created_by_id')
    op.drop_constraint('created_by_id_fkey', 'users', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'users', type_='foreignkey')
    op.drop_column('users', 'updated_by_id')
    op.drop_column('users', 'updated_at')
    op.drop_column('users', 'created_by_id')
    op.drop_constraint('updated_by_id_fkey', 'stage_property', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'stage_property', type_='foreignkey')
    op.drop_column('stage_property', 'updated_by_id')
    op.drop_column('stage_property', 'updated_at')
    op.drop_column('stage_property', 'created_by_id')
    op.drop_column('stage_property', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'stage', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'stage', type_='foreignkey')
    op.drop_column('stage', 'updated_by_id')
    op.drop_column('stage', 'updated_at')
    op.drop_column('stage', 'created_by_id')
    op.drop_column('stage', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'roles', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'roles', type_='foreignkey')
    op.drop_column('roles', 'updated_by_id')
    op.drop_column('roles', 'updated_at')
    op.drop_column('roles', 'created_by_id')
    op.drop_column('roles', 'created_at')
    op.drop_constraint('created_by_id_fkey', 'opportunity_document', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'opportunity_document', type_='foreignkey')
    op.drop_column('opportunity_document', 'updated_by_id')
    op.drop_column('opportunity_document', 'updated_at')
    op.drop_column('opportunity_document', 'created_by_id')
    op.drop_column('opportunity_document', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'opportunity', type_='foreignkey')
    op.drop_column('opportunity', 'updated_by_id')
    op.drop_column('opportunity', 'updated_at')
    op.drop_constraint('created_by_id_fkey', 'line_item', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'line_item', type_='foreignkey')
    op.drop_column('line_item', 'updated_by_id')
    op.drop_column('line_item', 'updated_at')
    op.drop_column('line_item', 'created_by_id')
    op.drop_column('line_item', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'flow', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'flow', type_='foreignkey')
    op.drop_column('flow', 'updated_by_id')
    op.drop_column('flow', 'updated_at')
    op.drop_column('flow', 'created_by_id')
    op.drop_column('flow', 'created_at')
    op.drop_constraint('created_by_id_fkey', 'document', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'document', type_='foreignkey')
    op.drop_column('document', 'updated_by_id')
    op.drop_column('document', 'updated_at')
    op.drop_column('document', 'created_by_id')
    op.drop_column('document', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'department', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'department', type_='foreignkey')
    op.drop_column('department', 'updated_by_id')
    op.drop_column('department', 'updated_at')
    op.drop_column('department', 'created_by_id')
    op.drop_column('department', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'contract_type', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'contract_type', type_='foreignkey')
    op.drop_column('contract_type', 'updated_by_id')
    op.drop_column('contract_type', 'updated_at')
    op.drop_column('contract_type', 'created_by_id')
    op.drop_column('contract_type', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'contract_stage_action_item', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'contract_stage_action_item', type_='foreignkey')
    op.drop_column('contract_stage_action_item', 'updated_by_id')
    op.drop_column('contract_stage_action_item', 'updated_at')
    op.drop_column('contract_stage_action_item', 'created_by_id')
    op.drop_column('contract_stage_action_item', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'contract_stage', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'contract_stage', type_='foreignkey')
    op.drop_column('contract_stage', 'updated_by_id')
    op.drop_column('contract_stage', 'created_by_id')
    op.drop_constraint('created_by_id_fkey', 'contract_property', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'contract_property', type_='foreignkey')
    op.drop_column('contract_property', 'updated_by_id')
    op.drop_column('contract_property', 'updated_at')
    op.drop_column('contract_property', 'created_by_id')
    op.drop_column('contract_property', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'contract_note', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'contract_note', type_='foreignkey')
    op.drop_column('contract_note', 'updated_by_id')
    op.drop_column('contract_note', 'created_by_id')
    op.drop_constraint('updated_by_id_fkey', 'contract', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'contract', type_='foreignkey')
    op.drop_column('contract', 'updated_by_id')
    op.drop_column('contract', 'created_by_id')
    op.drop_constraint('created_by_id_fkey', 'company_contact', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'company_contact', type_='foreignkey')
    op.drop_column('company_contact', 'updated_by_id')
    op.drop_column('company_contact', 'updated_at')
    op.drop_column('company_contact', 'created_by_id')
    op.drop_column('company_contact', 'created_at')
    op.drop_constraint('created_by_id_fkey', 'company', type_='foreignkey')
    op.drop_constraint('updated_by_id_fkey', 'company', type_='foreignkey')
    op.drop_column('company', 'updated_by_id')
    op.drop_column('company', 'updated_at')
    op.drop_column('company', 'created_by_id')
    op.drop_column('company', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'category', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'category', type_='foreignkey')
    op.drop_column('category', 'updated_by_id')
    op.drop_column('category', 'updated_at')
    op.drop_column('category', 'created_by_id')
    op.drop_column('category', 'created_at')
    op.drop_constraint('updated_by_id_fkey', 'app_status', type_='foreignkey')
    op.drop_constraint('created_by_id_fkey', 'app_status', type_='foreignkey')
    op.drop_column('app_status', 'updated_by_id')
    op.drop_column('app_status', 'updated_at')
    op.drop_column('app_status', 'created_by_id')
    op.drop_column('app_status', 'created_at')
    ### end Alembic commands ###
