Script 4d06318043d3_add_buildchroot_id_and_coprchroot_id_py
[hide private]
[frames] | no frames]

Source Code for Script script-4d06318043d3_add_buildchroot_id_and_coprchroot_id_py

  1  """ 
  2  Add BuildChroot.id and CoprChroot.id 
  3   
  4  Revision ID: 4d06318043d3 
  5  Revises: 6f83ea2ba416 
  6  Create Date: 2020-04-22 13:37:56.137818 
  7  """ 
  8   
  9  import time 
 10   
 11  import sqlalchemy as sa 
 12  from alembic import op 
 13   
 14  revision = '4d06318043d3' 
 15  down_revision = '6f83ea2ba416' 
 16   
 17  Base = sa.ext.declarative.declarative_base() 
 18   
 19  FIRST_TIME = time.time() 
 20   
 21   
22 -def _info_checkpoint(message):
23 secs = time.time() - FIRST_TIME 24 print("{:6.2f} {}".format(secs, message))
25 26
27 -def upgrade():
28 _info_checkpoint("add CoprChroot.id (and index)") 29 op.execute("ALTER TABLE copr_chroot ADD COLUMN id SERIAL") 30 op.create_index('temporary_copr_chroot_id', 'copr_chroot', ['id'], 31 unique=True) 32 33 _info_checkpoint("add BuildChroot.copr_chroot_id column") 34 op.add_column('build_chroot', 35 sa.Column('copr_chroot_id', sa.Integer(), nullable=True)) 36 37 _info_checkpoint("creating temporary table with index") 38 op.execute(""" 39 CREATE TEMP TABLE temporary_table AS ( 40 SELECT 41 bch.build_id as build_id, 42 bch.mock_chroot_id, 43 c.id as copr_id, 44 cch.id as copr_chroot_id 45 FROM 46 build_chroot as bch 47 LEFT JOIN 48 build as b on bch.build_id = b.id 49 LEFT JOIN 50 copr as c on c.id = b.copr_id 51 LEFT JOIN 52 copr_chroot as cch ON cch.copr_id = c.id AND 53 cch.mock_chroot_id = bch.mock_chroot_id 54 ) 55 """) 56 op.execute(""" 57 CREATE INDEX temporary_index ON temporary_table (build_id, mock_chroot_id) 58 """) 59 60 _info_checkpoint("drop constraints/indexes to speedup update") 61 op.drop_constraint('build_chroot_pkey', 'build_chroot', type_='primary') 62 # drop those temporarily 63 op.drop_index('ix_build_chroot_build_id', table_name='build_chroot') 64 op.drop_index('ix_build_chroot_started_on', table_name='build_chroot') 65 op.drop_index('ix_build_chroot_ended_on', table_name='build_chroot') 66 op.drop_index('build_chroot_status_started_on_idx', 67 table_name='build_chroot') 68 69 _info_checkpoint("add BuildChroot.id") 70 op.execute("ALTER TABLE build_chroot ADD COLUMN id SERIAL") 71 72 _info_checkpoint("starting the expensive query") 73 sql_major_query = """ 74 UPDATE 75 build_chroot 76 SET 77 copr_chroot_id = sq.copr_chroot_id 78 FROM 79 temporary_table as sq 80 WHERE 81 build_chroot.mock_chroot_id = sq.mock_chroot_id AND 82 build_chroot.build_id = sq.build_id 83 """ 84 op.execute(sql_major_query) 85 86 _info_checkpoint("creating other constraints") 87 op.create_unique_constraint('copr_chroot_mock_chroot_id_copr_id_uniq', 88 'copr_chroot', ['mock_chroot_id', 'copr_id']) 89 90 _info_checkpoint("drop the temporary stuff") 91 op.drop_table('temporary_table') 92 93 # those were temporarily removed 94 _info_checkpoint("create temporarily removed constraints/indexes") 95 op.create_index('build_chroot_status_started_on_idx', 'build_chroot', 96 ['status', 'started_on'], unique=False) 97 op.create_index(op.f('ix_build_chroot_build_id'), 'build_chroot', 98 ['build_id'], unique=False) 99 op.create_index(op.f('ix_build_chroot_started_on'), 'build_chroot', 100 ['started_on'], unique=False) 101 op.create_index(op.f('ix_build_chroot_ended_on'), 'build_chroot', 102 ['ended_on'], unique=False) 103 104 _info_checkpoint("create changed indexes/constraints") 105 op.create_primary_key('build_chroot_pkey', 'build_chroot', ['id']) 106 op.create_unique_constraint('build_chroot_mock_chroot_id_build_id_uniq', 107 'build_chroot', ['mock_chroot_id', 'build_id']) 108 op.create_index(op.f('ix_build_chroot_copr_chroot_id'), 'build_chroot', 109 ['copr_chroot_id'], unique=False) 110 111 op.drop_constraint('copr_chroot_pkey', 'copr_chroot', type_='primary') 112 op.create_primary_key('copr_chroot_pkey', 'copr_chroot', ['id']) 113 op.drop_index('temporary_copr_chroot_id', table_name='copr_chroot') 114 op.create_foreign_key(None, 'build_chroot', 'copr_chroot', 115 ['copr_chroot_id'], ['id'], ondelete='SET NULL')
116 117
118 -def downgrade():
119 """ not implemented """ 120 raise NotImplementedError("Sorry, this migration cannot be undone")
121