"""improve search rebuild trigger

Revision ID: 2e30804e1994
Revises: 3dcad2e19bc4
Create Date: 2015-08-06 02:49:40.245741

"""

# revision identifiers, used by Alembic.
revision = '2e30804e1994'
down_revision = '3c8dbc9ebf7e'

from alembic import op
import sqlalchemy as sa

from purchasing.data.models import TRIGGER_TUPLES

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(), nullable=False, server_default=sa.schema.DefaultClause('false')))

    op.execute(sa.schema.CreateSequence(sa.schema.Sequence("department_id_seq")))
    department = op.create_table('department',
    sa.Column('id', sa.Integer(), nullable=False, server_default=sa.text("nextval('department_id_seq'::regclass)")),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_unique_constraint('department_unique_name', 'department', ['name'])

    # link contract stages to flows as well
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(), nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', ['flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', ['contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey', 'contract_stage', 'flow', ['flow_id'], ['id'])

    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
    ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', ['contract_id', 'stage_id', 'flow_id'])

    # handle departments, build foreign key relationships
    # we need to replace the current values with references to the new model
    # therefore, we build out everything, make the update, and the drop
    # the old (string) column reference

    op.add_column(u'users', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users', 'department', ['department_id'], ['id'])

    op.add_column(u'contract', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey', 'contract', 'department', ['department_id'], ['id'])

    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey', 'opportunity', 'department', ['department_id'], ['id'])

    op.bulk_insert(department, [
        {'name': 'Bureau of Neighborhood Empowerment'},
        {'name': 'Citizen Police Review Board'},
        {'name': "City Clerk's Office"},
        {'name': 'City Controller'},
        {'name': 'City Council'},
        {'name': 'Commission on Human Relations'},
        {'name': 'Department of City Planning'},
        {'name': 'Department of Finance'},
        {'name': 'Department of Law'},
        {'name': 'Department of Parks and Recreation'},
        {'name': 'Department of Permits, Licenses, and Inspections'},
        {'name': 'Department of Public Safety'},
        {'name': 'Department of Public Works'},
        {'name': 'Equal Opportunity Review Commission'},
        {'name': 'Ethics Board'},
        {'name': 'Innovation and Performance'},
        {'name': 'Office of Management and Budget'},
        {'name': 'Office of Municipal Investigations'},
        {'name': 'Office of the Mayor'},
        {'name': 'Personnel and Civil Service Commission'},
        {'name': 'Other'},
        {'name': 'New User'}
    ])

    # recreate the triggers, only rebuild when updates
    # are made
    conn = op.get_bind()

    # update departments
    conn.execute(sa.sql.text(
        '''UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        '''
    ))

    conn.execute(sa.sql.text(
        '''UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        '''
    ))

    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')

    # add a new role
    conn.execute(sa.sql.text(
        '''INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        '''
    ))

    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column(u'stage', sa.Column('send_notifs', sa.BOOLEAN(), server_default=sa.text(u'false'), autoincrement=False, nullable=False))

    op.add_column(u'users', sa.Column('department', sa.VARCHAR(length=255), server_default=sa.text(u"'Other'::character varying"), autoincrement=False, nullable=False))

    op.drop_constraint('contract_stage_flow_id_flow_id_fkey', 'contract_stage', type_='foreignkey')
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', ['contract_id', 'stage_id'], unique=False)
    op.drop_index(op.f('ix_contract_stage_flow_id'), table_name='contract_stage')

    op.drop_constraint('contract_stage_pkey', 'contract_stage', type_='primary')
    op.drop_column(u'contract_stage', 'flow_id')

    op.drop_constraint('user_id_department_user_id_fkey', 'users', type_='foreignkey')
    op.drop_column(u'users', 'department_id')

    op.drop_constraint('department_unique_name', 'department', type_='unique')
    op.drop_constraint('department_id_contract_department_id_fkey', 'contract', type_='foreignkey')
    op.drop_column(u'contract', 'department_id')

    op.add_column(u'opportunity', sa.Column('department', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
    op.drop_constraint('opportunity_department_id_department_id_fkey', 'opportunity', type_='foreignkey')
    op.drop_column(u'opportunity', 'department_id')

    op.drop_table('department')

    # rebuild the triggers as they were before
    conn = op.get_bind()
    for table, column, _ 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 AFTER TRUNCATE OR INSERT OR DELETE OR UPDATE OF {column}
            ON {table} FOR EACH STATEMENT
            EXECUTE PROCEDURE trig_refresh_search_view()
        '''.format(table=table, column=column)))

    conn.execute(sa.sql.text(
        '''DELETE FROM roles WHERE name = 'county'
        '''
    ))

    op.drop_column(u'contract', 'is_visible')
    op.execute(sa.schema.DropSequence(sa.schema.Sequence("department_id_seq")))
    ### end Alembic commands ###

6d0b20af86864d0d62eea49d6bd45e756f865ad6
<mask token>


def upgrade():
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(),
        nullable=False, server_default=sa.schema.DefaultClause('false')))
    op.execute(sa.schema.CreateSequence(sa.schema.Sequence(
        'department_id_seq')))
    department = op.create_table('department', sa.Column('id', sa.Integer(),
        nullable=False, server_default=sa.text(
        "nextval('department_id_seq'::regclass)")), sa.Column('name', sa.
        String(length=255), nullable=False), sa.PrimaryKeyConstraint('id'))
    op.create_unique_constraint('department_unique_name', 'department', [
        'name'])
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(),
        nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', [
        'flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', 'flow', ['flow_id'], ['id'])
    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
        ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'])
    op.add_column(u'users', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users',
        'department', ['department_id'], ['id'])
    op.add_column(u'contract', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey',
        'contract', 'department', ['department_id'], ['id'])
    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey',
        'opportunity', 'department', ['department_id'], ['id'])
    op.bulk_insert(department, [{'name':
        'Bureau of Neighborhood Empowerment'}, {'name':
        'Citizen Police Review Board'}, {'name': "City Clerk's Office"}, {
        'name': 'City Controller'}, {'name': 'City Council'}, {'name':
        'Commission on Human Relations'}, {'name':
        'Department of City Planning'}, {'name': 'Department of Finance'},
        {'name': 'Department of Law'}, {'name':
        'Department of Parks and Recreation'}, {'name':
        'Department of Permits, Licenses, and Inspections'}, {'name':
        'Department of Public Safety'}, {'name':
        'Department of Public Works'}, {'name':
        'Equal Opportunity Review Commission'}, {'name': 'Ethics Board'}, {
        'name': 'Innovation and Performance'}, {'name':
        'Office of Management and Budget'}, {'name':
        'Office of Municipal Investigations'}, {'name':
        'Office of the Mayor'}, {'name':
        'Personnel and Civil Service Commission'}, {'name': 'Other'}, {
        'name': 'New User'}])
    conn = op.get_bind()
    conn.execute(sa.sql.text(
        """UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        """
        ))
    conn.execute(sa.sql.text(
        """UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        """
        ))
    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')
    conn.execute(sa.sql.text(
        """INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        """
        ))
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text(
            """
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))


<mask token>

<mask token>


def upgrade():
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(),
        nullable=False, server_default=sa.schema.DefaultClause('false')))
    op.execute(sa.schema.CreateSequence(sa.schema.Sequence(
        'department_id_seq')))
    department = op.create_table('department', sa.Column('id', sa.Integer(),
        nullable=False, server_default=sa.text(
        "nextval('department_id_seq'::regclass)")), sa.Column('name', sa.
        String(length=255), nullable=False), sa.PrimaryKeyConstraint('id'))
    op.create_unique_constraint('department_unique_name', 'department', [
        'name'])
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(),
        nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', [
        'flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', 'flow', ['flow_id'], ['id'])
    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
        ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'])
    op.add_column(u'users', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users',
        'department', ['department_id'], ['id'])
    op.add_column(u'contract', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey',
        'contract', 'department', ['department_id'], ['id'])
    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey',
        'opportunity', 'department', ['department_id'], ['id'])
    op.bulk_insert(department, [{'name':
        'Bureau of Neighborhood Empowerment'}, {'name':
        'Citizen Police Review Board'}, {'name': "City Clerk's Office"}, {
        'name': 'City Controller'}, {'name': 'City Council'}, {'name':
        'Commission on Human Relations'}, {'name':
        'Department of City Planning'}, {'name': 'Department of Finance'},
        {'name': 'Department of Law'}, {'name':
        'Department of Parks and Recreation'}, {'name':
        'Department of Permits, Licenses, and Inspections'}, {'name':
        'Department of Public Safety'}, {'name':
        'Department of Public Works'}, {'name':
        'Equal Opportunity Review Commission'}, {'name': 'Ethics Board'}, {
        'name': 'Innovation and Performance'}, {'name':
        'Office of Management and Budget'}, {'name':
        'Office of Municipal Investigations'}, {'name':
        'Office of the Mayor'}, {'name':
        'Personnel and Civil Service Commission'}, {'name': 'Other'}, {
        'name': 'New User'}])
    conn = op.get_bind()
    conn.execute(sa.sql.text(
        """UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        """
        ))
    conn.execute(sa.sql.text(
        """UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        """
        ))
    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')
    conn.execute(sa.sql.text(
        """INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        """
        ))
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text(
            """
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))


def downgrade():
    op.add_column(u'stage', sa.Column('send_notifs', sa.BOOLEAN(),
        server_default=sa.text(u'false'), autoincrement=False, nullable=False))
    op.add_column(u'users', sa.Column('department', sa.VARCHAR(length=255),
        server_default=sa.text(u"'Other'::character varying"),
        autoincrement=False, nullable=False))
    op.drop_constraint('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', type_='foreignkey')
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id'], unique=False)
    op.drop_index(op.f('ix_contract_stage_flow_id'), table_name=
        'contract_stage')
    op.drop_constraint('contract_stage_pkey', 'contract_stage', type_='primary'
        )
    op.drop_column(u'contract_stage', 'flow_id')
    op.drop_constraint('user_id_department_user_id_fkey', 'users', type_=
        'foreignkey')
    op.drop_column(u'users', 'department_id')
    op.drop_constraint('department_unique_name', 'department', type_='unique')
    op.drop_constraint('department_id_contract_department_id_fkey',
        'contract', type_='foreignkey')
    op.drop_column(u'contract', 'department_id')
    op.add_column(u'opportunity', sa.Column('department', sa.VARCHAR(length
        =255), autoincrement=False, nullable=True))
    op.drop_constraint('opportunity_department_id_department_id_fkey',
        'opportunity', type_='foreignkey')
    op.drop_column(u'opportunity', 'department_id')
    op.drop_table('department')
    conn = op.get_bind()
    for table, column, _ 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 AFTER TRUNCATE OR INSERT OR DELETE OR UPDATE OF {column}
            ON {table} FOR EACH STATEMENT
            EXECUTE PROCEDURE trig_refresh_search_view()
        """
            .format(table=table, column=column)))
    conn.execute(sa.sql.text(
        "DELETE FROM roles WHERE name = 'county'\n        "))
    op.drop_column(u'contract', 'is_visible')
    op.execute(sa.schema.DropSequence(sa.schema.Sequence('department_id_seq')))

<mask token>
revision = '2e30804e1994'
down_revision = '3c8dbc9ebf7e'
<mask token>


def upgrade():
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(),
        nullable=False, server_default=sa.schema.DefaultClause('false')))
    op.execute(sa.schema.CreateSequence(sa.schema.Sequence(
        'department_id_seq')))
    department = op.create_table('department', sa.Column('id', sa.Integer(),
        nullable=False, server_default=sa.text(
        "nextval('department_id_seq'::regclass)")), sa.Column('name', sa.
        String(length=255), nullable=False), sa.PrimaryKeyConstraint('id'))
    op.create_unique_constraint('department_unique_name', 'department', [
        'name'])
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(),
        nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', [
        'flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', 'flow', ['flow_id'], ['id'])
    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
        ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'])
    op.add_column(u'users', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users',
        'department', ['department_id'], ['id'])
    op.add_column(u'contract', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey',
        'contract', 'department', ['department_id'], ['id'])
    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey',
        'opportunity', 'department', ['department_id'], ['id'])
    op.bulk_insert(department, [{'name':
        'Bureau of Neighborhood Empowerment'}, {'name':
        'Citizen Police Review Board'}, {'name': "City Clerk's Office"}, {
        'name': 'City Controller'}, {'name': 'City Council'}, {'name':
        'Commission on Human Relations'}, {'name':
        'Department of City Planning'}, {'name': 'Department of Finance'},
        {'name': 'Department of Law'}, {'name':
        'Department of Parks and Recreation'}, {'name':
        'Department of Permits, Licenses, and Inspections'}, {'name':
        'Department of Public Safety'}, {'name':
        'Department of Public Works'}, {'name':
        'Equal Opportunity Review Commission'}, {'name': 'Ethics Board'}, {
        'name': 'Innovation and Performance'}, {'name':
        'Office of Management and Budget'}, {'name':
        'Office of Municipal Investigations'}, {'name':
        'Office of the Mayor'}, {'name':
        'Personnel and Civil Service Commission'}, {'name': 'Other'}, {
        'name': 'New User'}])
    conn = op.get_bind()
    conn.execute(sa.sql.text(
        """UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        """
        ))
    conn.execute(sa.sql.text(
        """UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        """
        ))
    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')
    conn.execute(sa.sql.text(
        """INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        """
        ))
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text(
            """
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))


def downgrade():
    op.add_column(u'stage', sa.Column('send_notifs', sa.BOOLEAN(),
        server_default=sa.text(u'false'), autoincrement=False, nullable=False))
    op.add_column(u'users', sa.Column('department', sa.VARCHAR(length=255),
        server_default=sa.text(u"'Other'::character varying"),
        autoincrement=False, nullable=False))
    op.drop_constraint('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', type_='foreignkey')
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id'], unique=False)
    op.drop_index(op.f('ix_contract_stage_flow_id'), table_name=
        'contract_stage')
    op.drop_constraint('contract_stage_pkey', 'contract_stage', type_='primary'
        )
    op.drop_column(u'contract_stage', 'flow_id')
    op.drop_constraint('user_id_department_user_id_fkey', 'users', type_=
        'foreignkey')
    op.drop_column(u'users', 'department_id')
    op.drop_constraint('department_unique_name', 'department', type_='unique')
    op.drop_constraint('department_id_contract_department_id_fkey',
        'contract', type_='foreignkey')
    op.drop_column(u'contract', 'department_id')
    op.add_column(u'opportunity', sa.Column('department', sa.VARCHAR(length
        =255), autoincrement=False, nullable=True))
    op.drop_constraint('opportunity_department_id_department_id_fkey',
        'opportunity', type_='foreignkey')
    op.drop_column(u'opportunity', 'department_id')
    op.drop_table('department')
    conn = op.get_bind()
    for table, column, _ 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 AFTER TRUNCATE OR INSERT OR DELETE OR UPDATE OF {column}
            ON {table} FOR EACH STATEMENT
            EXECUTE PROCEDURE trig_refresh_search_view()
        """
            .format(table=table, column=column)))
    conn.execute(sa.sql.text(
        "DELETE FROM roles WHERE name = 'county'\n        "))
    op.drop_column(u'contract', 'is_visible')
    op.execute(sa.schema.DropSequence(sa.schema.Sequence('department_id_seq')))

<mask token>
revision = '2e30804e1994'
down_revision = '3c8dbc9ebf7e'
from alembic import op
import sqlalchemy as sa
from purchasing.data.models import TRIGGER_TUPLES


def upgrade():
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(),
        nullable=False, server_default=sa.schema.DefaultClause('false')))
    op.execute(sa.schema.CreateSequence(sa.schema.Sequence(
        'department_id_seq')))
    department = op.create_table('department', sa.Column('id', sa.Integer(),
        nullable=False, server_default=sa.text(
        "nextval('department_id_seq'::regclass)")), sa.Column('name', sa.
        String(length=255), nullable=False), sa.PrimaryKeyConstraint('id'))
    op.create_unique_constraint('department_unique_name', 'department', [
        'name'])
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(),
        nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', [
        'flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', 'flow', ['flow_id'], ['id'])
    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
        ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', [
        'contract_id', 'stage_id', 'flow_id'])
    op.add_column(u'users', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users',
        'department', ['department_id'], ['id'])
    op.add_column(u'contract', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey',
        'contract', 'department', ['department_id'], ['id'])
    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(),
        nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey',
        'opportunity', 'department', ['department_id'], ['id'])
    op.bulk_insert(department, [{'name':
        'Bureau of Neighborhood Empowerment'}, {'name':
        'Citizen Police Review Board'}, {'name': "City Clerk's Office"}, {
        'name': 'City Controller'}, {'name': 'City Council'}, {'name':
        'Commission on Human Relations'}, {'name':
        'Department of City Planning'}, {'name': 'Department of Finance'},
        {'name': 'Department of Law'}, {'name':
        'Department of Parks and Recreation'}, {'name':
        'Department of Permits, Licenses, and Inspections'}, {'name':
        'Department of Public Safety'}, {'name':
        'Department of Public Works'}, {'name':
        'Equal Opportunity Review Commission'}, {'name': 'Ethics Board'}, {
        'name': 'Innovation and Performance'}, {'name':
        'Office of Management and Budget'}, {'name':
        'Office of Municipal Investigations'}, {'name':
        'Office of the Mayor'}, {'name':
        'Personnel and Civil Service Commission'}, {'name': 'Other'}, {
        'name': 'New User'}])
    conn = op.get_bind()
    conn.execute(sa.sql.text(
        """UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        """
        ))
    conn.execute(sa.sql.text(
        """UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        """
        ))
    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')
    conn.execute(sa.sql.text(
        """INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        """
        ))
    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text(
            """
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))


def downgrade():
    op.add_column(u'stage', sa.Column('send_notifs', sa.BOOLEAN(),
        server_default=sa.text(u'false'), autoincrement=False, nullable=False))
    op.add_column(u'users', sa.Column('department', sa.VARCHAR(length=255),
        server_default=sa.text(u"'Other'::character varying"),
        autoincrement=False, nullable=False))
    op.drop_constraint('contract_stage_flow_id_flow_id_fkey',
        'contract_stage', type_='foreignkey')
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', [
        'contract_id', 'stage_id'], unique=False)
    op.drop_index(op.f('ix_contract_stage_flow_id'), table_name=
        'contract_stage')
    op.drop_constraint('contract_stage_pkey', 'contract_stage', type_='primary'
        )
    op.drop_column(u'contract_stage', 'flow_id')
    op.drop_constraint('user_id_department_user_id_fkey', 'users', type_=
        'foreignkey')
    op.drop_column(u'users', 'department_id')
    op.drop_constraint('department_unique_name', 'department', type_='unique')
    op.drop_constraint('department_id_contract_department_id_fkey',
        'contract', type_='foreignkey')
    op.drop_column(u'contract', 'department_id')
    op.add_column(u'opportunity', sa.Column('department', sa.VARCHAR(length
        =255), autoincrement=False, nullable=True))
    op.drop_constraint('opportunity_department_id_department_id_fkey',
        'opportunity', type_='foreignkey')
    op.drop_column(u'opportunity', 'department_id')
    op.drop_table('department')
    conn = op.get_bind()
    for table, column, _ 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 AFTER TRUNCATE OR INSERT OR DELETE OR UPDATE OF {column}
            ON {table} FOR EACH STATEMENT
            EXECUTE PROCEDURE trig_refresh_search_view()
        """
            .format(table=table, column=column)))
    conn.execute(sa.sql.text(
        "DELETE FROM roles WHERE name = 'county'\n        "))
    op.drop_column(u'contract', 'is_visible')
    op.execute(sa.schema.DropSequence(sa.schema.Sequence('department_id_seq')))

"""improve search rebuild trigger

Revision ID: 2e30804e1994
Revises: 3dcad2e19bc4
Create Date: 2015-08-06 02:49:40.245741

"""

# revision identifiers, used by Alembic.
revision = '2e30804e1994'
down_revision = '3c8dbc9ebf7e'

from alembic import op
import sqlalchemy as sa

from purchasing.data.models import TRIGGER_TUPLES

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column(u'stage', 'send_notifs')
    op.add_column(u'contract', sa.Column('is_visible', sa.Boolean(), nullable=False, server_default=sa.schema.DefaultClause('false')))

    op.execute(sa.schema.CreateSequence(sa.schema.Sequence("department_id_seq")))
    department = op.create_table('department',
    sa.Column('id', sa.Integer(), nullable=False, server_default=sa.text("nextval('department_id_seq'::regclass)")),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_unique_constraint('department_unique_name', 'department', ['name'])

    # link contract stages to flows as well
    op.add_column(u'contract_stage', sa.Column('flow_id', sa.Integer(), nullable=False, server_default=sa.schema.DefaultClause('1')))
    op.create_index(op.f('ix_contract_stage_flow_id'), 'contract_stage', ['flow_id'], unique=False)
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', ['contract_id', 'stage_id', 'flow_id'], unique=False)
    op.create_foreign_key('contract_stage_flow_id_flow_id_fkey', 'contract_stage', 'flow', ['flow_id'], ['id'])

    op.execute(sa.sql.text(
        'ALTER TABLE contract_stage DROP CONSTRAINT IF EXISTS contract_stage_pkey'
    ))
    op.create_primary_key('contract_stage_pkey', 'contract_stage', ['contract_id', 'stage_id', 'flow_id'])

    # handle departments, build foreign key relationships
    # we need to replace the current values with references to the new model
    # therefore, we build out everything, make the update, and the drop
    # the old (string) column reference

    op.add_column(u'users', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('user_id_department_user_id_fkey', 'users', 'department', ['department_id'], ['id'])

    op.add_column(u'contract', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('department_id_contract_department_id_fkey', 'contract', 'department', ['department_id'], ['id'])

    op.add_column(u'opportunity', sa.Column('department_id', sa.Integer(), nullable=True))
    op.create_foreign_key('opportunity_department_id_department_id_fkey', 'opportunity', 'department', ['department_id'], ['id'])

    op.bulk_insert(department, [
        {'name': 'Bureau of Neighborhood Empowerment'},
        {'name': 'Citizen Police Review Board'},
        {'name': "City Clerk's Office"},
        {'name': 'City Controller'},
        {'name': 'City Council'},
        {'name': 'Commission on Human Relations'},
        {'name': 'Department of City Planning'},
        {'name': 'Department of Finance'},
        {'name': 'Department of Law'},
        {'name': 'Department of Parks and Recreation'},
        {'name': 'Department of Permits, Licenses, and Inspections'},
        {'name': 'Department of Public Safety'},
        {'name': 'Department of Public Works'},
        {'name': 'Equal Opportunity Review Commission'},
        {'name': 'Ethics Board'},
        {'name': 'Innovation and Performance'},
        {'name': 'Office of Management and Budget'},
        {'name': 'Office of Municipal Investigations'},
        {'name': 'Office of the Mayor'},
        {'name': 'Personnel and Civil Service Commission'},
        {'name': 'Other'},
        {'name': 'New User'}
    ])

    # recreate the triggers, only rebuild when updates
    # are made
    conn = op.get_bind()

    # update departments
    conn.execute(sa.sql.text(
        '''UPDATE users SET department_id = (
            SELECT id FROM department WHERE users.department = department.name
        )
        '''
    ))

    conn.execute(sa.sql.text(
        '''UPDATE opportunity SET department_id = (
            SELECT id FROM department WHERE opportunity.department = department.name
        )
        '''
    ))

    op.drop_column(u'users', 'department')
    op.drop_column(u'opportunity', 'department')

    # add a new role
    conn.execute(sa.sql.text(
        '''INSERT INTO roles VALUES (nextval('roles_id_seq'::regclass), 'county')
        '''
    ))

    for table, column, when in TRIGGER_TUPLES:
        conn.execute(sa.sql.text('''
            DROP TRIGGER IF EXISTS tsv_{table}_{column}_trigger 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)))
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column(u'stage', sa.Column('send_notifs', sa.BOOLEAN(), server_default=sa.text(u'false'), autoincrement=False, nullable=False))

    op.add_column(u'users', sa.Column('department', sa.VARCHAR(length=255), server_default=sa.text(u"'Other'::character varying"), autoincrement=False, nullable=False))

    op.drop_constraint('contract_stage_flow_id_flow_id_fkey', 'contract_stage', type_='foreignkey')
    op.drop_index('ix_contrage_stage_combined_id', table_name='contract_stage')
    op.create_index('ix_contrage_stage_combined_id', 'contract_stage', ['contract_id', 'stage_id'], unique=False)
    op.drop_index(op.f('ix_contract_stage_flow_id'), table_name='contract_stage')

    op.drop_constraint('contract_stage_pkey', 'contract_stage', type_='primary')
    op.drop_column(u'contract_stage', 'flow_id')

    op.drop_constraint('user_id_department_user_id_fkey', 'users', type_='foreignkey')
    op.drop_column(u'users', 'department_id')

    op.drop_constraint('department_unique_name', 'department', type_='unique')
    op.drop_constraint('department_id_contract_department_id_fkey', 'contract', type_='foreignkey')
    op.drop_column(u'contract', 'department_id')

    op.add_column(u'opportunity', sa.Column('department', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
    op.drop_constraint('opportunity_department_id_department_id_fkey', 'opportunity', type_='foreignkey')
    op.drop_column(u'opportunity', 'department_id')

    op.drop_table('department')

    # rebuild the triggers as they were before
    conn = op.get_bind()
    for table, column, _ 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 AFTER TRUNCATE OR INSERT OR DELETE OR UPDATE OF {column}
            ON {table} FOR EACH STATEMENT
            EXECUTE PROCEDURE trig_refresh_search_view()
        '''.format(table=table, column=column)))

    conn.execute(sa.sql.text(
        '''DELETE FROM roles WHERE name = 'county'
        '''
    ))

    op.drop_column(u'contract', 'is_visible')
    op.execute(sa.schema.DropSequence(sa.schema.Sequence("department_id_seq")))
    ### end Alembic commands ###
