<reponame>bigblue/pynab<filename>alembic/versions/41996f6d756_change_uniques_to_sha1_keys.py
"""change uniques to sha1 keys

Revision ID: 41996f6d756
Revises: <PASSWORD>
Create Date: 2015-03-27 21:22:02.796920

"""

# revision identifiers, used by Alembic.
revision = '41996f6d756'
down_revision = '<PASSWORD>2c<PASSWORD>'

from alembic import op
import sqlalchemy as sa
from pynab.db import Release, windowed_query
import hashlib
from sqlalchemy.orm import sessionmaker

def upgrade():
    # drop duplicate pres
    conn = op.get_bind()

    conn.execute('''
        DELETE FROM pres
        WHERE id IN (SELECT id
                      FROM (SELECT id,
                                row_number() over (partition BY requestid, pretime, requestgroup ORDER BY id) AS rnum
                             FROM pres) t
                      WHERE t.rnum > 1);
    ''')

    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('pres_name_key', 'pres', type_='unique')
    op.create_unique_constraint('pres_uniq', 'pres', ['requestid', 'pretime', 'requestgroup'])
    op.add_column('releases', sa.Column('uniqhash', sa.String(length=40), nullable=True))
    op.drop_constraint('releases_name_group_id_posted_key', 'releases', type_='unique')
    op.create_unique_constraint('releases_uniq', 'releases', ['uniqhash'])

    session = sessionmaker(bind=conn)()
    # update the hashes
    q = session.query(Release.id, Release.name, Release.group_id, Release.posted)
    for release in windowed_query(q, Release.id, 1000):
        uniqhash = hashlib.sha1(
            '{}.{}.{}'.format(
                release.name,
                release.group_id,
                release.posted,
            ).encode('utf-8')
        ).hexdigest()

        session.query(Release).filter(Release.id==release.id).update({Release.uniqhash: uniqhash})

    session.commit()
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('releases_uniq', 'releases', type_='unique')
    op.create_unique_constraint('releases_name_group_id_posted_key', 'releases', ['name', 'group_id', 'posted'])
    op.drop_column('releases', 'uniqhash')
    op.drop_constraint('pres_uniq', 'pres', type_='unique')
    op.create_unique_constraint('pres_name_key', 'pres', ['name'])
    ### end Alembic commands ###

"""change uniques to sha1 keys

Revision ID: 41996f6d756
Revises: <PASSWORD>
Create Date: 2015-03-27 21:22:02.796920

"""

# revision identifiers, used by Alembic.
revision = '41996f6d756'
down_revision = '<PASSWORD>2c<PASSWORD>'

from alembic import op
import sqlalchemy as sa
from pynab.db import Release, windowed_query
import hashlib
from sqlalchemy.orm import sessionmaker

def upgrade():
    # drop duplicate pres
    conn = op.get_bind()

    conn.execute('''
        DELETE FROM pres
        WHERE id IN (SELECT id
                      FROM (SELECT id,
                                row_number() over (partition BY requestid, pretime, requestgroup ORDER BY id) AS rnum
                             FROM pres) t
                      WHERE t.rnum > 1);
    ''')

    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('pres_name_key', 'pres', type_='unique')
    op.create_unique_constraint('pres_uniq', 'pres', ['requestid', 'pretime', 'requestgroup'])
    op.add_column('releases', sa.Column('uniqhash', sa.String(length=40), nullable=True))
    op.drop_constraint('releases_name_group_id_posted_key', 'releases', type_='unique')
    op.create_unique_constraint('releases_uniq', 'releases', ['uniqhash'])

    session = sessionmaker(bind=conn)()
    # update the hashes
    q = session.query(Release.id, Release.name, Release.group_id, Release.posted)
    for release in windowed_query(q, Release.id, 1000):
        uniqhash = hashlib.sha1(
            '{}.{}.{}'.format(
                release.name,
                release.group_id,
                release.posted,
            ).encode('utf-8')
        ).hexdigest()

        session.query(Release).filter(Release.id==release.id).update({Release.uniqhash: uniqhash})

    session.commit()
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('releases_uniq', 'releases', type_='unique')
    op.create_unique_constraint('releases_name_group_id_posted_key', 'releases', ['name', 'group_id', 'posted'])
    op.drop_column('releases', 'uniqhash')
    op.drop_constraint('pres_uniq', 'pres', type_='unique')
    op.create_unique_constraint('pres_name_key', 'pres', ['name'])
    ### end Alembic commands ###
change uniques to sha1 keys  Revision ID: 41996f6d756 Revises: <PASSWORD> Create Date: 2015-03-27 21:22:02.796920 # revision identifiers, used by Alembic. # drop duplicate pres DELETE FROM pres         WHERE id IN (SELECT id                       FROM (SELECT id,                                 row_number() over (partition BY requestid, pretime, requestgroup ORDER BY id) AS rnum                              FROM pres) t                       WHERE t.rnum > 1); ### commands auto generated by Alembic - please adjust! ### # update the hashes ### end Alembic commands ### ### commands auto generated by Alembic - please adjust! ### ### end Alembic commands ###