Differential D1054

Fix the migration, so it deals with duplicate job uuids
ClosedPublic
Actions

Authored by jskladan on Nov 11 2016, 10:01 AM.

Details

Reviewers
• ralph2
tflink
mkrizek
Maniphest Tasks
T866: Can't migrate resultsdb's db schema from 1.0 to 2.0
Commits
rRSDBf9d2ba18408b: Fix the migration, so it deals with duplicate job uuids
Summary

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.

Test Plan

Tested the migration locally on engineered data

Diff Detail

Repository
rRSDB resultsdb
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.
jskladan retitled this revision from to Fix the migration, so it deals with duplicate job uuids.Nov 11 2016, 10:01 AM
jskladan updated this object.
jskladan edited the test plan for this revision. (Show Details)
jskladan added reviewers: tflink, mkrizek, • ralph2.
mkrizek accepted this revision.Nov 21 2016, 3:15 PM

This looks good to me. Was it tested with db dump from e.g. dev? If not, I can try. Or we can sacrifice dev for testing :)

@tflink @ralph2 wdyt?

This revision is now accepted and ready to land.Nov 21 2016, 3:15 PM
tflink accepted this revision.Nov 21 2016, 5:31 PM

I'd say that dev is more-or-less sacrifice-able. I think it would behoove us to make backups of the DB before doing things which could bork data so that we can have more than one try if needed, though.

Closed by commit rRSDBf9d2ba18408b: Fix the migration, so it deals with duplicate job uuids (authored by Josef Skladanka <jskladan@redhat.com>). · Explain WhyNov 22 2016, 9:41 AM
This revision was automatically updated to reflect the committed changes.

Revision Contents

Diff 2690

View Options

alembic/versions/540dbe71fa91_change_schema_to_v2_0_step_1.py

1"""Change schema to v2.0 - step 1 - prepare columns 1​"""Change schema to v2.0 - step 1 - prepare columns
2 2​
3Revision ID: 59bef5afc9aa 3​Revision ID: 59bef5afc9aa
4Revises: 978007ecd2b 4​Revises: 978007ecd2b
5Create 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.
10revision = '540dbe71fa91' 10​revision = '540dbe71fa91'
11down_revision = '978007ecd2b' 11​down_revision = '978007ecd2b'
12branch_labels = None 12​branch_labels = None
13depends_on = None 13​depends_on = None
14 14​
15from alembic import op 15​from alembic import op
16import 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​
21​Session = sessionmaker()
22​Base = declarative_base()
23​
24​
25​class Job(Base):
26​ __tablename__ = 'job'
27​
28​ id = sa.Column(sa.Integer, primary_key=True)
29​ uuid = sa.Column(sa.String(36), unique=True)
30​ results = relation('Result', backref='job')
31​
32​
33​class Result(Base):
34​ __tablename__ = 'result'
35​
36​ id = sa.Column(sa.Integer, primary_key=True)
37​ job_id = sa.Column(sa.Integer, sa.ForeignKey('job.id'))
17 38​
18 39​
19def upgrade(): 40​def upgrade():
41​ # Merge duplicate Jobs
42​ connection = op.get_bind()
43​ session = Session(bind=connection)
44​ merge_targets = {}
45​ jobs_to_delete = []
46​ job_query = session.query(Job).from_statement(text(
47​ "select id, uuid from job where uuid in (select uuid from job group by uuid having count(uuid) > 1) order by id;"))
48​ for job in job_query:
49​ primary = merge_targets.setdefault(job.uuid, job)
50​ if primary.id != job.id:
51​ for result in job.results:
52​ result.job_id = primary.id
53​ session.add(result)
54​ jobs_to_delete.append(job)
55​ session.commit()
56​ for job in jobs_to_delete:
57​ session.delete(job)
58​ session.commit()
59​
20 # JOB 60​ # JOB
21 op.rename_table('job', 'group') 61​ op.rename_table('job', 'group')
22 op.alter_column('group', 'name', new_column_name='description') 62​ op.alter_column('group', 'name', new_column_name='description')
23 op.drop_column(u'group', 'status') 63​ op.drop_column(u'group', 'status')
24 op.drop_column(u'group', 'start_time') 64​ op.drop_column(u'group', 'start_time')
25 op.drop_column(u'group', 'end_time') 65​ op.drop_column(u'group', 'end_time')
26 op.create_unique_constraint(None, 'group', ['uuid']) 66​ op.create_unique_constraint(None, 'group', ['uuid'])
27 op.create_index( 67​ op.create_index(
28 'group_idx_uuid', 'group', ['uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) 68​ 'group_idx_uuid', 'group', ['uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'})
29 69​
30 # RESULT 70​ # RESULT
31 op.add_column(u'result', sa.Column('testcase_name', sa.Text(), nullable=True)) 71​ op.add_column(
72​ u'result', sa.Column('testcase_name', sa.Text(), nullable=True))
32 op.alter_column('result', 'summary', new_column_name='note') 73​ op.alter_column('result', 'summary', new_column_name='note')
33 op.alter_column('result', 'log_url', new_column_name='ref_url') 74​ op.alter_column('result', 'log_url', new_column_name='ref_url')
34 op.create_index('result_fk_testcase_name', 'result', [ 75​ op.create_index('result_fk_testcase_name', 'result', [
35 'testcase_name'], unique=False, postgresql_ops={'testcase_name': 'text_pattern_ops'}) 76​ 'testcase_name'], unique=False, postgresql_ops={'testcase_name': 'text_pattern_ops'})
36 op.drop_index('result_fk_job_id', table_name='result') 77​ op.drop_index('result_fk_job_id', table_name='result')
37 op.drop_index('result_fk_testcase_id', table_name='result') 78​ op.drop_index('result_fk_testcase_id', table_name='result')
38 op.drop_constraint(u'result_testcase_id_fkey', 'result', type_='foreignkey') 79​ op.drop_constraint(
80​ u'result_testcase_id_fkey', 'result', type_='foreignkey')
39 op.drop_constraint(u'result_job_id_fkey', 'result', type_='foreignkey') 81​ op.drop_constraint(u'result_job_id_fkey', 'result', type_='foreignkey')
40 op.create_foreign_key(None, 'result', 'testcase', ['testcase_name'], ['name']) 82​ op.create_foreign_key(
83​ None, 'result', 'testcase', ['testcase_name'], ['name'])
41 84​
42 # TESTCASE 85​ # TESTCASE
43 op.alter_column('testcase', 'url', new_column_name='ref_url') 86​ op.alter_column('testcase', 'url', new_column_name='ref_url')
44 87​
45 # MANY TO MANY 88​ # MANY TO MANY
46 op.create_table('groups_to_results', 89​ op.create_table('groups_to_results',
47 sa.Column('id', sa.Integer(), nullable=False), 90​ sa.Column('id', sa.Integer(), nullable=False),
48 sa.Column('group_uuid', sa.String(36), nullable=True), 91​ sa.Column('group_uuid', sa.String(36), nullable=True),
49 sa.Column('result_id', sa.Integer(), nullable=True), 92​ sa.Column('result_id', sa.Integer(), nullable=True),
50 sa.ForeignKeyConstraint(['group_uuid'], ['group.uuid'], ), 93​ sa.ForeignKeyConstraint(['group_uuid'], ['group.uuid'], ),
51 sa.ForeignKeyConstraint(['result_id'], ['result.id'], ), 94​ sa.ForeignKeyConstraint(['result_id'], ['result.id'], ),
52 sa.PrimaryKeyConstraint('id') 95​ sa.PrimaryKeyConstraint('id')
53 ) 96​ )
54 op.create_index('gtr_fk_group_uuid', 'groups_to_results', [ 97​ op.create_index('gtr_fk_group_uuid', 'groups_to_results', [
55 'group_uuid'], unique=False, postgresql_ops={'uuid': 'text_pattern_ops'}) 98​ '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) 99​ op.create_index(
100​ 'gtr_fk_result_id', 'groups_to_results', ['result_id'], unique=False)
57 101​
58 102​
59def downgrade(): 103​def downgrade():
60 # TESTCASE 104​ # TESTCASE
61 op.alter_column('testcase', 'ref_url', new_column_name='url') 105​ op.alter_column('testcase', 'ref_url', new_column_name='url')
62 106​
63 # RESULT 107​ # RESULT
64 op.alter_column('result', 'note', new_column_name='summary') 108​ op.alter_column('result', 'note', new_column_name='summary')
65 op.alter_column('result', 'ref_url', new_column_name='log_url') 109​ op.alter_column('result', 'ref_url', new_column_name='log_url')
66 op.drop_constraint('result_testcase_name_fkey', 'result', type_='foreignkey') 110​ op.drop_constraint(
67 op.create_index('result_fk_testcase_id', 'result', ['testcase_id'], unique=False) 111​ 'result_testcase_name_fkey', 'result', type_='foreignkey')
112​ op.create_index(
113​ 'result_fk_testcase_id', 'result', ['testcase_id'], unique=False)
68 op.create_index('result_fk_job_id', 'result', ['job_id'], unique=False) 114​ op.create_index('result_fk_job_id', 'result', ['job_id'], unique=False)
69 op.drop_index('result_fk_testcase_name', table_name='result') 115​ op.drop_index('result_fk_testcase_name', table_name='result')
70 op.drop_column(u'result', 'testcase_name') 116​ op.drop_column(u'result', 'testcase_name')
71 117​
72 # JOB 118​ # JOB
73 op.rename_table('group', 'job') 119​ op.rename_table('group', 'job')
74 op.alter_column('job', 'description', new_column_name='name') 120​ op.alter_column('job', 'description', new_column_name='name')
75 op.add_column(u'job', sa.Column('end_time', sa.DateTime(), nullable=True)) 121​ 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)) 122​ op.add_column(
77 op.add_column(u'job', sa.Column('status', sa.VARCHAR(length=16), nullable=True)) 123​ u'job', sa.Column('start_time', sa.DateTime(), nullable=True))
124​ op.add_column(
125​ u'job', sa.Column('status', sa.VARCHAR(length=16), nullable=True))
78 op.drop_index('group_idx_uuid', table_name='job') 126​ op.drop_index('group_idx_uuid', table_name='job')
79 127​
80 # MANY TO MANY 128​ # MANY TO MANY
81 op.drop_index('gtr_fk_result_id', table_name='groups_to_results') 129​ 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') 130​ op.drop_index('gtr_fk_group_uuid', table_name='groups_to_results')
83 op.drop_table('groups_to_results') 131​ op.drop_table('groups_to_results')
84 132​
85 # CONSTRAINTS 133​ # CONSTRAINTS
86 op.create_foreign_key(None, 'result', 'job', ['job_id'], ['id']) 134​ op.create_foreign_key(None, 'result', 'job', ['job_id'], ['id'])
87 op.create_foreign_key(None, 'result', 'testcase', ['testcase_id'], ['id']) 135​ op.create_foreign_key(None, 'result', 'testcase', ['testcase_id'], ['id'])
88 op.drop_constraint('group_uuid_key', 'job') 136​ op.drop_constraint('group_uuid_key', 'job')