Blame alembic/versions/987edda096f5_access_id_in_user_projects.py

Vivek Anand 967335
"""access_id in user_projects
Vivek Anand 967335
Vivek Anand 967335
Revision ID: 987edda096f5
Vivek Anand 57b3bb
Revises: 8a3b10926153
Vivek Anand 967335
Create Date: 2016-07-05 18:21:14.771273
Vivek Anand 967335
Vivek Anand 967335
"""
Vivek Anand 967335
Vivek Anand 967335
# revision identifiers, used by Alembic.
Vivek Anand 967335
revision = '987edda096f5'
Vivek Anand 57b3bb
down_revision = '8a3b10926153'
Vivek Anand 967335
Vivek Anand 967335
from alembic import op
Vivek Anand 967335
import sqlalchemy as sa
Vivek Anand ca33a1
from sqlalchemy.sql import table, column
Vivek Anand 967335
Vivek Anand ca33a1
acl = table (
Vivek Anand ca33a1
    'access_levels',
Vivek Anand ca33a1
    column('access', sa.String(255))
Vivek Anand ca33a1
)
Vivek Anand 967335
Vivek Anand 967335
def upgrade():
Vivek Anand 967335
    ''' Add a foreign key in user_projects and projects_groups
Vivek Anand 967335
    table for access_levels
Vivek Anand 967335
    '''
Vivek Anand 967335
Vivek Anand ca33a1
    # To allow N + 2 migrations easier
Vivek Anand ca33a1
    # without going through N + 1
Vivek Anand ca33a1
    # Make sure, we have all the tables
Vivek Anand ca33a1
    op.create_table(
Vivek Anand ca33a1
        'access_levels',
Vivek Anand ca33a1
        sa.Column('access', sa.String(255), primary_key=True)
Vivek Anand ca33a1
    )
Vivek Anand ca33a1
    op.bulk_insert(
Vivek Anand ca33a1
        acl,
Vivek Anand ca33a1
        [
Vivek Anand ca33a1
            {'access': 'ticket'},
Vivek Anand ca33a1
            {'access': 'commit'},
Vivek Anand ca33a1
            {'access': 'admin'},
Vivek Anand ca33a1
        ],
Vivek Anand ca33a1
    )
Vivek Anand 967335
    op.add_column(
Vivek Anand 967335
        'user_projects',
Vivek Anand 967335
        sa.Column(
Vivek Anand 967335
            'access',
Vivek Anand 967335
            sa.String(255),
Vivek Anand 967335
            sa.ForeignKey(
Vivek Anand 967335
                'access_levels.access',
Vivek Anand 967335
                onupdate='CASCADE',
Vivek Anand 967335
                ondelete='CASCADE',
Vivek Anand 967335
            ),
Vivek Anand 967335
            nullable=True,
Vivek Anand 967335
        ),
Vivek Anand 967335
    )
Vivek Anand 967335
    op.execute('UPDATE "user_projects" SET access=\'admin\'')
Vivek Anand 967335
    op.alter_column(
Vivek Anand 967335
        'user_projects',
Vivek Anand 967335
        'access',
Vivek Anand 967335
        nullable=False,
Vivek Anand 967335
        existing_nullable=True,
Vivek Anand 967335
    )
Vivek Anand 967335
Vivek Anand 967335
    # for groups
Vivek Anand 967335
    op.add_column(
Vivek Anand 967335
        'projects_groups',
Vivek Anand 967335
        sa.Column(
Vivek Anand 967335
            'access',
Vivek Anand 967335
            sa.String(255),
Vivek Anand 967335
            sa.ForeignKey(
Vivek Anand 967335
                'access_levels.access',
Vivek Anand 967335
                onupdate='CASCADE',
Vivek Anand 967335
                ondelete='CASCADE',
Vivek Anand 967335
            ),
Vivek Anand 967335
            nullable=True,
Vivek Anand 967335
        ),
Vivek Anand 967335
    )
Vivek Anand 967335
    op.execute('UPDATE "projects_groups" SET access=\'admin\'')
Vivek Anand 967335
    op.alter_column(
Vivek Anand 967335
        'projects_groups',
Vivek Anand 967335
        'access',
Vivek Anand 967335
        nullable=False,
Vivek Anand 967335
        existing_nullable=True,
Vivek Anand 967335
    )
Vivek Anand 967335
Vivek Anand 967335
    # alter the constraints
Pierre-Yves Chibon 3decd2
    op.drop_constraint('user_projects_project_id_fkey', 'user_projects')
Vivek Anand 967335
    op.create_unique_constraint(
Vivek Anand 967335
            None,
Vivek Anand 967335
            'user_projects',
Vivek Anand 967335
            ["project_id", "user_id", "access"]
Vivek Anand 967335
    )
Vivek Anand 967335
Vivek Anand 967335
    op.drop_constraint('projects_groups_pkey', 'projects_groups')
Vivek Anand 967335
    op.create_primary_key(
Vivek Anand 967335
            None,
Vivek Anand 967335
            'projects_groups',
Vivek Anand 967335
            ['project_id', 'group_id', 'access'],
Vivek Anand 967335
    )
Vivek Anand 967335
Vivek Anand 967335
Vivek Anand 967335
def downgrade():
Vivek Anand 967335
    ''' Remove column access_id from user_projects and projects_groups '''
Vivek Anand 967335
Vivek Anand 967335
    # this removes the current constraints as well.
Vivek Anand 967335
    op.drop_column('user_projects', 'access')
Vivek Anand 967335
    op.drop_column('projects_groups', 'access')
Vivek Anand 967335
Vivek Anand 967335
    # recreate the previous constraints
Vivek Anand 967335
    op.create_unique_constraint(
Vivek Anand 967335
            None,
Vivek Anand 967335
            'user_projects',
Vivek Anand 967335
            ['project_id', 'user_id'],
Vivek Anand 967335
    )
Vivek Anand 967335
    op.create_primary_key(
Vivek Anand 967335
            None,
Vivek Anand 967335
            'projects_groups',
Vivek Anand 967335
            ['project_id', 'group_id'],
Vivek Anand 967335
    )
Vivek Anand ca33a1
    op.drop_table('access_levels')