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
25
26
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
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
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
119 """ not implemented """
120 raise NotImplementedError("Sorry, this migration cannot be undone")
121