d52d50968cf7_move_submissions.py 3.26 KB
Newer Older
Lukáš Lalinský's avatar
Lukáš Lalinský committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
"""move submissions

Revision ID: d52d50968cf7
Revises: ae7e1e5763ef
Create Date: 2019-06-08 11:04:53.563059

"""
import datetime
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import Sequence, CreateSequence, DropSequence
from dateutil.relativedelta import relativedelta


# revision identifiers, used by Alembic.
revision = 'd52d50968cf7'
down_revision = 'ae7e1e5763ef'
branch_labels = None
depends_on = None


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()


31
def upgrade_main():
Lukáš Lalinský's avatar
Lukáš Lalinský committed
32 33 34
    op.rename_table('submission', 'submission_old')


35
def downgrade_main():
Lukáš Lalinský's avatar
Lukáš Lalinský committed
36 37 38
    op.rename_table('submission_old', 'submission')


39
def upgrade_import():
Lukáš Lalinský's avatar
Lukáš Lalinský committed
40 41
    op.execute(CreateSequence(Sequence('submission_id_seq')))
    op.create_table('submission',
42 43 44
        sa.Column('id', sa.Integer(), server_main=sa.text("nextval('submission_id_seq')"), nullable=False),
        sa.Column('created', sa.DateTime(timezone=True), server_main=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('handled', sa.Boolean(), server_main=sa.text('false'), nullable=True),
Lukáš Lalinský's avatar
Lukáš Lalinský committed
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
        sa.Column('account_id', sa.Integer(), nullable=False),
        sa.Column('application_id', sa.Integer(), nullable=False),
        sa.Column('application_version', sa.String(), nullable=True),
        sa.Column('fingerprint', postgresql.ARRAY(sa.Integer()), nullable=False),
        sa.Column('duration', sa.Integer(), nullable=False),
        sa.Column('bitrate', sa.Integer(), nullable=True),
        sa.Column('format', sa.String(), nullable=True),
        sa.Column('mbid', postgresql.UUID(), nullable=True),
        sa.Column('puid', postgresql.UUID(), nullable=True),
        sa.Column('foreignid', sa.String(), nullable=True),
        sa.Column('track', sa.String(), nullable=True),
        sa.Column('artist', sa.String(), nullable=True),
        sa.Column('album', sa.String(), nullable=True),
        sa.Column('album_artist', sa.String(), nullable=True),
        sa.Column('track_no', sa.Integer(), nullable=True),
        sa.Column('disc_no', sa.Integer(), nullable=True),
        sa.Column('year', sa.Integer(), nullable=True),
        postgresql_partition_by='RANGE (created)',
    )

    one_month = relativedelta(months=1)
    partitions_from = datetime.date(2010, 1, 1)
    partitions_to = datetime.date.today().replace(month=1, day=1) + relativedelta(months=12)
    range_from = partitions_from
    while range_from < partitions_to:
        range_to = range_from + one_month
        op.execute("""

            CREATE TABLE IF NOT EXISTS submission_y{range_from.year:04d}m{range_to.month:02d}
                PARTITION OF submission
                FOR VALUES FROM ('{range_from}') TO ('{range_to}');

            ALTER TABLE submission_y{range_from.year:04d}m{range_to.month:02d}
                ADD PRIMARY KEY (id);

            CREATE INDEX submission_y{range_from.year:04d}m{range_to.month:02d}_idx_handled
                ON submission_y{range_from.year:04d}m{range_to.month:02d} (handled);

        """.format(range_from=range_from, range_to=range_to))
        range_from = range_to


87
def downgrade_import():
Lukáš Lalinský's avatar
Lukáš Lalinský committed
88 89
    op.drop_table('submission')
    op.execute(DropSequence(Sequence('submission_id_seq')))