| • ralph2 | |
| tflink | |
| mkrizek |
This is required to solve our state of database, where depchecks have
multiple jobs with the same uuid, and in the resultsdb 2.0 the uuid is
an unique identifier of the group.
This is done by merging the results of each "duplicate" job to the first
one with that specified uuid, and deleting the other (now result-less)
jobs.
Yield jobs per 100 and show progress logs
Tested the migration locally on engineered data
| Lint Warnings | Excuse: sadly, this is the way it is with alembic files... |
| Severity | Location | Code | Message |
|---|---|---|---|
| Warning | alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py:17 | E402 | flake8 1 |
| Warning | alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py:18 | E402 | flake8 1 |
| Warning | alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py:19 | E402 | flake8 1 |
| Warning | alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py:20 | E402 | flake8 1 |
| No Unit Test Coverage |
| Buildable 922 | |
| Build 922: arc lint + arc unit |
| Path | Packages | |||
|---|---|---|---|---|
| M | alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py (82 lines) |
| Commit | Tree | Parents | Author | Summary | Date |
|---|---|---|---|---|---|
| 932f733545e1 | ee2a55c4d2be | 0e23df20007d | Josef Skladanka | Yield jobs per 100 and show progress logs | Nov 24 2016, 4:39 PM |
| 0e23df20007d | 066d5bd46c78 | 816c8963b98f | Josef Skladanka | Fix the migration, so it deals with duplicate job uuids (Show More…) | Nov 24 2016, 4:38 PM |
| 1 | """Change schema to v2.0 - step 1 - prepare columns | 1 | """Change schema to v2.0 - step 1 - prepare columns | ||
|---|---|---|---|---|---|
| 2 | 2 | | |||
| 3 | Revision ID: 59bef5afc9aa | 3 | Revision ID: 59bef5afc9aa | ||
| 4 | Revises: 978007ecd2b | 4 | Revises: 978007ecd2b | ||
| 5 | Create Date: 2016-08-23 20:10:05.734728 | 5 | Create Date: 2016-08-23 20:10:05.734728 | ||
| 6 | 6 | | |||
| 7 | """ | 7 | """ | ||
| 8 | 8 | | |||
| 9 | # revision identifiers, used by Alembic. | 9 | # revision identifiers, used by Alembic. | ||
| 10 | revision = '540dbe71fa91' | 10 | revision = '540dbe71fa91' | ||
| 11 | down_revision = '978007ecd2b' | 11 | down_revision = '978007ecd2b' | ||
| 12 | branch_labels = None | 12 | branch_labels = None | ||
| 13 | depends_on = None | 13 | depends_on = None | ||
| 14 | 14 | | |||
| 15 | from alembic import op | 15 | from alembic import op | ||
| 16 | import sqlalchemy as sa | 16 | import sqlalchemy as sa | ||
| 17 | from sqlalchemy.ext.declarative import declarative_base | ||||
| 18 | from sqlalchemy.orm import relation, sessionmaker | ||||
| 19 | from sqlalchemy.sql import text | ||||
| 20 | import logging | ||||
| 21 | | ||||
| 22 | Session = sessionmaker() | ||||
| 23 | Base = declarative_base() | ||||
| 24 | | ||||
| 25 | | ||||
| 26 | class Job(Base): | ||||
| 27 | __tablename__ = 'job' | ||||
| 28 | | ||||
| 29 | id = sa.Column(sa.Integer, primary_key=True) | ||||
| 30 | uuid = sa.Column(sa.String(36), unique=True) | ||||
| 31 | results = relation('Result', backref='job') | ||||
| 32 | | ||||
| 33 | | ||||
| 34 | class Result(Base): | ||||
| 35 | __tablename__ = 'result' | ||||
| 36 | | ||||
| 37 | id = sa.Column(sa.Integer, primary_key=True) | ||||
| 38 | job_id = sa.Column(sa.Integer, sa.ForeignKey('job.id')) | ||||
| 17 | 39 | | |||
| 18 | 40 | | |||
| 19 | def upgrade(): | 41 | def upgrade(): | ||
| 42 | # Merge duplicate Jobs | ||||
| 43 | logger = logging.getLogger('alembic') | ||||
| 44 | connection = op.get_bind() | ||||
| 45 | session = Session(bind=connection) | ||||
| 46 | merge_targets = {} | ||||
| 47 | jobs_to_delete = [] | ||||
| 48 | | ||||
| 49 | job_count_query = connection.execute("select count(*) from job where uuid in (select uuid from job group by uuid having count(uuid) > 1);") | ||||
| 50 | job_count = -1 | ||||
| 51 | for row in job_count_query: | ||||
| 52 | job_count = row[0] | ||||
| 53 | | ||||
| 54 | logger.info("Jobs marked for inspection: %s", job_count) | ||||
| 55 | | ||||
| 56 | job_query = session.query(Job).from_statement(text( | ||||
| 57 | "select id, uuid from job where uuid in (select uuid from job group by uuid having count(uuid) > 1) order by id;") | ||||
| 58 | ).yield_per(100) | ||||
| 59 | | ||||
| 60 | j = r = 0 | ||||
| 61 | for job in job_query: | ||||
| 62 | j += 1 | ||||
| 63 | primary = merge_targets.setdefault(job.uuid, job) | ||||
| 64 | if primary.id != job.id: | ||||
| 65 | for result in job.results: | ||||
| 66 | r += 1 | ||||
| 67 | result.job_id = primary.id | ||||
| 68 | session.add(result) | ||||
| 69 | jobs_to_delete.append(job) | ||||
| 70 | if not j % 1000: | ||||
| 71 | logger.info("Jobs seen: %s out of %s", j, job_count) | ||||
| 72 | logger.info("Results marked for move: %s", r) | ||||
| 73 | session.commit() | ||||
| 74 | for job in jobs_to_delete: | ||||
| 75 | session.delete(job) | ||||
| 76 | session.commit() | ||||
| 77 | | ||||
| 20 | # JOB | 78 | # JOB | ||
| 21 | op.rename_table('job', 'group') | 79 | op.rename_table('job', 'group') | ||
| 22 | op.alter_column('group', 'name', new_column_name='description') | 80 | op.alter_column('group', 'name', new_column_name='description') | ||
| 23 | op.drop_column(u'group', 'status') | 81 | op.drop_column(u'group', 'status') | ||
| 24 | op.drop_column(u'group', 'start_time') | 82 | op.drop_column(u'group', 'start_time') | ||
| 25 | op.drop_column(u'group', 'end_time') | 83 | op.drop_column(u'group', 'end_time') | ||
| 26 | op.create_unique_constraint(None, 'group', ['uuid']) | 84 | op.create_unique_constraint(None, 'group', ['uuid']) | ||
| 27 | op.create_index( | 85 | op.create_index( | ||
| 28 | 'group_idx_uuid', 'group', ['uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) | 86 | 'group_idx_uuid', 'group', ['uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) | ||
| 29 | 87 | | |||
| 30 | # RESULT | 88 | # RESULT | ||
| 31 | op.add_column(u'result', sa.Column('testcase_name', sa.Text(), nullable=True)) | 89 | op.add_column( | ||
| 90 | u'result', sa.Column('testcase_name', sa.Text(), nullable=True)) | ||||
| 32 | op.alter_column('result', 'summary', new_column_name='note') | 91 | op.alter_column('result', 'summary', new_column_name='note') | ||
| 33 | op.alter_column('result', 'log_url', new_column_name='ref_url') | 92 | op.alter_column('result', 'log_url', new_column_name='ref_url') | ||
| 34 | op.create_index('result_fk_testcase_name', 'result', [ | 93 | op.create_index('result_fk_testcase_name', 'result', [ | ||
| 35 | 'testcase_name'], unique=False, postgresql_ops={'testcase_name': 'text_pattern_ops'}) | 94 | 'testcase_name'], unique=False, postgresql_ops={'testcase_name': 'text_pattern_ops'}) | ||
| 36 | op.drop_index('result_fk_job_id', table_name='result') | 95 | op.drop_index('result_fk_job_id', table_name='result') | ||
| 37 | op.drop_index('result_fk_testcase_id', table_name='result') | 96 | op.drop_index('result_fk_testcase_id', table_name='result') | ||
| 38 | op.drop_constraint(u'result_testcase_id_fkey', 'result', type_='foreignkey') | 97 | op.drop_constraint( | ||
| 98 | u'result_testcase_id_fkey', 'result', type_='foreignkey') | ||||
| 39 | op.drop_constraint(u'result_job_id_fkey', 'result', type_='foreignkey') | 99 | op.drop_constraint(u'result_job_id_fkey', 'result', type_='foreignkey') | ||
| 40 | op.create_foreign_key(None, 'result', 'testcase', ['testcase_name'], ['name']) | 100 | op.create_foreign_key( | ||
| 101 | None, 'result', 'testcase', ['testcase_name'], ['name']) | ||||
| 41 | 102 | | |||
| 42 | # TESTCASE | 103 | # TESTCASE | ||
| 43 | op.alter_column('testcase', 'url', new_column_name='ref_url') | 104 | op.alter_column('testcase', 'url', new_column_name='ref_url') | ||
| 44 | 105 | | |||
| 45 | # MANY TO MANY | 106 | # MANY TO MANY | ||
| 46 | op.create_table('groups_to_results', | 107 | op.create_table('groups_to_results', | ||
| 47 | sa.Column('id', sa.Integer(), nullable=False), | 108 | sa.Column('id', sa.Integer(), nullable=False), | ||
| 48 | sa.Column('group_uuid', sa.String(36), nullable=True), | 109 | sa.Column('group_uuid', sa.String(36), nullable=True), | ||
| 49 | sa.Column('result_id', sa.Integer(), nullable=True), | 110 | sa.Column('result_id', sa.Integer(), nullable=True), | ||
| 50 | sa.ForeignKeyConstraint(['group_uuid'], ['group.uuid'], ), | 111 | sa.ForeignKeyConstraint(['group_uuid'], ['group.uuid'], ), | ||
| 51 | sa.ForeignKeyConstraint(['result_id'], ['result.id'], ), | 112 | sa.ForeignKeyConstraint(['result_id'], ['result.id'], ), | ||
| 52 | sa.PrimaryKeyConstraint('id') | 113 | sa.PrimaryKeyConstraint('id') | ||
| 53 | ) | 114 | ) | ||
| 54 | op.create_index('gtr_fk_group_uuid', 'groups_to_results', [ | 115 | op.create_index('gtr_fk_group_uuid', 'groups_to_results', [ | ||
| 55 | 'group_uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) | 116 | 'group_uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) | ||
| 56 | op.create_index('gtr_fk_result_id', 'groups_to_results', ['result_id'], unique=False) | 117 | op.create_index( | ||
| 118 | 'gtr_fk_result_id', 'groups_to_results', ['result_id'], unique=False) | ||||
| 57 | 119 | | |||
| 58 | 120 | | |||
| 59 | def downgrade(): | 121 | def downgrade(): | ||
| 60 | # TESTCASE | 122 | # TESTCASE | ||
| 61 | op.alter_column('testcase', 'ref_url', new_column_name='url') | 123 | op.alter_column('testcase', 'ref_url', new_column_name='url') | ||
| 62 | 124 | | |||
| 63 | # RESULT | 125 | # RESULT | ||
| 64 | op.alter_column('result', 'note', new_column_name='summary') | 126 | op.alter_column('result', 'note', new_column_name='summary') | ||
| 65 | op.alter_column('result', 'ref_url', new_column_name='log_url') | 127 | op.alter_column('result', 'ref_url', new_column_name='log_url') | ||
| 66 | op.drop_constraint('result_testcase_name_fkey', 'result', type_='foreignkey') | 128 | op.drop_constraint( | ||
| 67 | op.create_index('result_fk_testcase_id', 'result', ['testcase_id'], unique=False) | 129 | 'result_testcase_name_fkey', 'result', type_='foreignkey') | ||
| 130 | op.create_index( | ||||
| 131 | 'result_fk_testcase_id', 'result', ['testcase_id'], unique=False) | ||||
| 68 | op.create_index('result_fk_job_id', 'result', ['job_id'], unique=False) | 132 | op.create_index('result_fk_job_id', 'result', ['job_id'], unique=False) | ||
| 69 | op.drop_index('result_fk_testcase_name', table_name='result') | 133 | op.drop_index('result_fk_testcase_name', table_name='result') | ||
| 70 | op.drop_column(u'result', 'testcase_name') | 134 | op.drop_column(u'result', 'testcase_name') | ||
| 71 | 135 | | |||
| 72 | # JOB | 136 | # JOB | ||
| 73 | op.rename_table('group', 'job') | 137 | op.rename_table('group', 'job') | ||
| 74 | op.alter_column('job', 'description', new_column_name='name') | 138 | op.alter_column('job', 'description', new_column_name='name') | ||
| 75 | op.add_column(u'job', sa.Column('end_time', sa.DateTime(), nullable=True)) | 139 | op.add_column(u'job', sa.Column('end_time', sa.DateTime(), nullable=True)) | ||
| 76 | op.add_column(u'job', sa.Column('start_time', sa.DateTime(), nullable=True)) | 140 | op.add_column( | ||
| 77 | op.add_column(u'job', sa.Column('status', sa.VARCHAR(length=16), nullable=True)) | 141 | u'job', sa.Column('start_time', sa.DateTime(), nullable=True)) | ||
| 142 | op.add_column( | ||||
| 143 | u'job', sa.Column('status', sa.VARCHAR(length=16), nullable=True)) | ||||
| 78 | op.drop_index('group_idx_uuid', table_name='job') | 144 | op.drop_index('group_idx_uuid', table_name='job') | ||
| 79 | 145 | | |||
| 80 | # MANY TO MANY | 146 | # MANY TO MANY | ||
| 81 | op.drop_index('gtr_fk_result_id', table_name='groups_to_results') | 147 | op.drop_index('gtr_fk_result_id', table_name='groups_to_results') | ||
| 82 | op.drop_index('gtr_fk_group_uuid', table_name='groups_to_results') | 148 | op.drop_index('gtr_fk_group_uuid', table_name='groups_to_results') | ||
| 83 | op.drop_table('groups_to_results') | 149 | op.drop_table('groups_to_results') | ||
| 84 | 150 | | |||
| 85 | # CONSTRAINTS | 151 | # CONSTRAINTS | ||
| 86 | op.create_foreign_key(None, 'result', 'job', ['job_id'], ['id']) | 152 | op.create_foreign_key(None, 'result', 'job', ['job_id'], ['id']) | ||
| 87 | op.create_foreign_key(None, 'result', 'testcase', ['testcase_id'], ['id']) | 153 | op.create_foreign_key(None, 'result', 'testcase', ['testcase_id'], ['id']) | ||
| 88 | op.drop_constraint('group_uuid_key', 'job') | 154 | op.drop_constraint('group_uuid_key', 'job') | ||
module level import not at top of file