pagure/alembic/versions/987edda096f5_access_id_in_user_projects.py

"""access_id in user_projects

Revision ID: 987edda096f5
Revises: 8a3b10926153
Create Date: 2016-07-05 18:21:14.771273

"""

# revision identifiers, used by Alembic.
revision = '987edda096f5'
down_revision = '8a3b10926153'

from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column

acl = table (
    'access_levels',
    column('access', sa.String(255))
)

def upgrade():
    ''' Add a foreign key in user_projects and projects_groups
    table for access_levels
    '''

    # To allow N + 2 migrations easier
    # without going through N + 1
    # Make sure, we have all the tables
    op.create_table(
        'access_levels',
        sa.Column('access', sa.String(255), primary_key=True)
    )
    op.bulk_insert(
        acl,
        [
            {'access': 'ticket'},
            {'access': 'commit'},
            {'access': 'admin'},
        ],
    )
    op.add_column(
        'user_projects',
        sa.Column(
            'access',
            sa.String(255),
            sa.ForeignKey(
                'access_levels.access',
                onupdate='CASCADE',
                ondelete='CASCADE',
            ),
            nullable=True,
        ),
    )
    op.execute('UPDATE "user_projects" SET access=\'admin\'')
    op.alter_column(
        'user_projects',
        'access',
        nullable=False,
        existing_nullable=True,
    )

    # for groups
    op.add_column(
        'projects_groups',
        sa.Column(
            'access',
            sa.String(255),
            sa.ForeignKey(
                'access_levels.access',
                onupdate='CASCADE',
                ondelete='CASCADE',
            ),
            nullable=True,
        ),
    )
    op.execute('UPDATE "projects_groups" SET access=\'admin\'')
    op.alter_column(
        'projects_groups',
        'access',
        nullable=False,
        existing_nullable=True,
    )

    # alter the constraints
    op.drop_constraint('user_projects_project_id_fkey', 'user_projects')
    op.create_unique_constraint(
            None,
            'user_projects',
            ["project_id", "user_id", "access"]
    )

    op.drop_constraint('projects_groups_pkey', 'projects_groups')
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id', 'access'],
    )


def downgrade():
    ''' Remove column access_id from user_projects and projects_groups '''

    # this removes the current constraints as well.
    op.drop_column('user_projects', 'access')
    op.drop_column('projects_groups', 'access')

    # recreate the previous constraints
    op.create_unique_constraint(
            None,
            'user_projects',
            ['project_id', 'user_id'],
    )
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id'],
    )
    op.drop_table('access_levels')