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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
# Copyright (c) 2013 Hewlett-Packard Development Company, L.P
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may
# not use this file except in compliance with the License. You may obtain
# a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
# License for the specific language governing permissions and limitations
# under the License.
"""Migrated the endpoint 'region' column to 'region_id.
In addition to the rename, the new column is made a foreign key to the
respective 'region' in the region table, ensuring that we auto-create
any regions that are missing. Further, since the old region column
was 255 chars, and the id column in the region table is 64 chars, the size
of the id column in the region table is increased to match.
To Upgrade:
Region Table
Increase the size of the if column in the region table
Endpoint Table
a. Add the endpoint region_id column, that is a foreign key to the region table
b. For each endpoint
i. Ensure there is matching region in region table, and if not, create it
ii. Assign the id to the region_id column
c. Remove the column region
To Downgrade:
Endpoint Table
a. Add back in the region column
b. For each endpoint
i. Copy the region_id column to the region column
c. Remove the column region_id
Region Table
Decrease the size of the id column in the region table, making sure that
we don't get classing primary keys.
"""
import migrate
import six
import sqlalchemy as sql
from sqlalchemy.orm import sessionmaker
def _migrate_to_region_id(migrate_engine, region_table, endpoint_table):
endpoints = list(endpoint_table.select().execute())
for endpoint in endpoints:
if endpoint.region is None:
continue
region = list(region_table.select(
whereclause=region_table.c.id == endpoint.region).execute())
if len(region) == 1:
region_id = region[0].id
else:
region_id = endpoint.region
region = {'id': region_id,
'description': '',
'extra': '{}'}
session = sessionmaker(bind=migrate_engine)()
region_table.insert(region).execute()
session.commit()
new_values = {'region_id': region_id}
f = endpoint_table.c.id == endpoint.id
update = endpoint_table.update().where(f).values(new_values)
migrate_engine.execute(update)
migrate.ForeignKeyConstraint(
columns=[endpoint_table.c.region_id],
refcolumns=[region_table.c.id],
name='fk_endpoint_region_id').create()
def _migrate_to_region(migrate_engine, region_table, endpoint_table):
endpoints = list(endpoint_table.select().execute())
for endpoint in endpoints:
new_values = {'region': endpoint.region_id}
f = endpoint_table.c.id == endpoint.id
update = endpoint_table.update().where(f).values(new_values)
migrate_engine.execute(update)
if 'sqlite' != migrate_engine.name:
migrate.ForeignKeyConstraint(
columns=[endpoint_table.c.region_id],
refcolumns=[region_table.c.id],
name='fk_endpoint_region_id').drop()
endpoint_table.c.region_id.drop()
def _prepare_regions_for_id_truncation(migrate_engine, region_table):
"""Ensure there are no IDs that are bigger than 64 chars.
The size of the id and parent_id fields where increased from 64 to 255
during the upgrade. On downgrade we have to make sure that the ids can
fit in the new column size. For rows with ids greater than this, we have
no choice but to dump them.
"""
for region in list(region_table.select().execute()):
if (len(six.text_type(region.id)) > 64 or
len(six.text_type(region.parent_region_id)) > 64):
delete = region_table.delete(region_table.c.id == region.id)
migrate_engine.execute(delete)
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
region_table = sql.Table('region', meta, autoload=True)
region_table.c.id.alter(type=sql.String(length=255))
region_table.c.parent_region_id.alter(type=sql.String(length=255))
endpoint_table = sql.Table('endpoint', meta, autoload=True)
region_id_column = sql.Column('region_id',
sql.String(length=255), nullable=True)
region_id_column.create(endpoint_table)
_migrate_to_region_id(migrate_engine, region_table, endpoint_table)
endpoint_table.c.region.drop()
def downgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
region_table = sql.Table('region', meta, autoload=True)
endpoint_table = sql.Table('endpoint', meta, autoload=True)
region_column = sql.Column('region', sql.String(length=255))
region_column.create(endpoint_table)
_migrate_to_region(migrate_engine, region_table, endpoint_table)
_prepare_regions_for_id_truncation(migrate_engine, region_table)
region_table.c.id.alter(type=sql.String(length=64))
region_table.c.parent_region_id.alter(type=sql.String(length=64))
|
