Merge "DB migration changes to support DB2 as sqlalchemy backend"

This commit is contained in:
Jenkins 2013-12-03 07:30:29 +00:00 committed by Gerrit Code Review
commit 6d2b7ea3fc
4 changed files with 280 additions and 23 deletions

View File

@ -29,6 +29,15 @@ def define_image_properties_table(meta):
images = define_images_table(meta)
# NOTE(dperaza) DB2: specify the UniqueConstraint option when creating the
# table will cause an index being created to specify the index
# name and skip the step of creating another index with the same columns.
# The index name is needed so it can be dropped and re-created later on.
constr_kwargs = {}
if meta.bind.name == 'ibm_db_sa':
constr_kwargs['name'] = 'ix_image_properties_image_id_key'
image_properties = Table('image_properties',
meta,
Column('id',
@ -50,13 +59,15 @@ def define_image_properties_table(meta):
nullable=False,
default=False,
index=True),
UniqueConstraint('image_id', 'key'),
UniqueConstraint('image_id', 'key',
**constr_kwargs),
mysql_engine='InnoDB',
extend_existing=True)
Index('ix_image_properties_image_id_key',
image_properties.c.image_id,
image_properties.c.key)
if meta.bind.name != 'ibm_db_sa':
Index('ix_image_properties_image_id_key',
image_properties.c.image_id,
image_properties.c.key)
return image_properties

View File

@ -79,13 +79,26 @@ def upgrade(migrate_engine):
'004_add_checksum', ['get_image_properties_table'])
image_properties = get_image_properties_table(meta)
index = Index('ix_image_properties_image_id_key',
image_properties.c.image_id,
image_properties.c.key)
index.rename('ix_image_properties_image_id_name')
if migrate_engine.name == "ibm_db_sa":
# NOTE(dperaza) ibm db2 does not allow ALTER INDEX so we will drop
# the index, rename the column, then re-create the index
sql_commands = [
"""ALTER TABLE image_properties DROP UNIQUE
ix_image_properties_image_id_key;""",
"""ALTER TABLE image_properties RENAME COLUMN \"key\" to name;""",
"""ALTER TABLE image_properties ADD CONSTRAINT
ix_image_properties_image_id_name UNIQUE(image_id, name);""",
]
for command in sql_commands:
meta.bind.execute(command)
else:
index = Index('ix_image_properties_image_id_key',
image_properties.c.image_id,
image_properties.c.key)
index.rename('ix_image_properties_image_id_name')
image_properties = get_image_properties_table(meta)
image_properties.columns['key'].alter(name="name")
image_properties = get_image_properties_table(meta)
image_properties.columns['key'].alter(name="name")
def downgrade(migrate_engine):
@ -94,9 +107,22 @@ def downgrade(migrate_engine):
image_properties = get_image_properties_table(meta)
index = Index('ix_image_properties_image_id_name',
image_properties.c.image_id,
image_properties.c.name)
index.rename('ix_image_properties_image_id_key')
if migrate_engine.name == "ibm_db_sa":
# NOTE(dperaza) ibm db2 does not allow ALTER INDEX so we will drop
# the index, rename the column, then re-create the index
sql_commands = [
"""ALTER TABLE image_properties DROP UNIQUE
ix_image_properties_image_id_name;""",
"""ALTER TABLE image_properties RENAME COLUMN name to \"key\";""",
"""ALTER TABLE image_properties ADD CONSTRAINT
ix_image_properties_image_id_key UNIQUE(image_id, \"key\");""",
]
for command in sql_commands:
meta.bind.execute(command)
else:
index = Index('ix_image_properties_image_id_name',
image_properties.c.image_id,
image_properties.c.name)
index.rename('ix_image_properties_image_id_key')
image_properties.columns['name'].alter(name="key")
image_properties.columns['name'].alter(name="key")

View File

@ -76,8 +76,11 @@ def get_image_members_table(meta):
mysql_engine='InnoDB',
extend_existing=True)
Index('ix_image_members_image_id_member', image_members.c.image_id,
image_members.c.member)
# DB2: an index has already been created for the UniqueConstraint option
# specified on the Table() statement above.
if meta.bind.name != "ibm_db_sa":
Index('ix_image_members_image_id_member', image_members.c.image_id,
image_members.c.member)
return image_members

View File

@ -1,5 +1,6 @@
# vim: tabstop=4 shiftwidth=4 softtabstop=4
# Copyright 2013 IBM Corp.
# Copyright 2011 OpenStack Foundation
# All Rights Reserved.
#
@ -45,6 +46,10 @@ def upgrade(migrate_engine):
if dialect == "sqlite":
_upgrade_sqlite(t_images, t_image_members, t_image_properties)
_update_all_ids_to_uuids(t_images, t_image_members, t_image_properties)
elif dialect == "ibm_db_sa":
_upgrade_db2(t_images, t_image_members, t_image_properties)
_update_all_ids_to_uuids(t_images, t_image_members, t_image_properties)
_add_db2_constraints()
else:
_upgrade_other(t_images, t_image_members, t_image_properties, dialect)
@ -62,6 +67,10 @@ def downgrade(migrate_engine):
if dialect == "sqlite":
_update_all_uuids_to_ids(t_images, t_image_members, t_image_properties)
_downgrade_sqlite(t_images, t_image_members, t_image_properties)
elif dialect == "ibm_db_sa":
_remove_db2_constraints()
_update_all_uuids_to_ids(t_images, t_image_members, t_image_properties)
_downgrade_db2(t_images, t_image_members, t_image_properties)
else:
_downgrade_other(t_images, t_image_members, t_image_properties,
dialect)
@ -137,6 +146,214 @@ def _upgrade_sqlite(t_images, t_image_members, t_image_properties):
_sqlite_table_swap(t_image_members, t_image_properties, t_images)
def _upgrade_db2(t_images, t_image_members, t_image_properties):
"""
Upgrade for DB2.
"""
t_images.c.id.alter(sqlalchemy.String(36), primary_key=True)
image_members_backup = sqlalchemy.Table(
'image_members_backup',
meta,
sqlalchemy.Column('id',
sqlalchemy.Integer(),
primary_key=True,
nullable=False),
sqlalchemy.Column('image_id',
sqlalchemy.String(36),
nullable=False,
index=True),
sqlalchemy.Column('member',
sqlalchemy.String(255),
nullable=False),
sqlalchemy.Column('can_share',
sqlalchemy.Boolean(),
nullable=False,
default=False),
sqlalchemy.Column('created_at',
sqlalchemy.DateTime(),
nullable=False),
sqlalchemy.Column('updated_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted',
sqlalchemy.Boolean(),
nullable=False,
default=False,
index=True),
sqlalchemy.UniqueConstraint('image_id', 'member'),
extend_existing=True)
image_properties_backup = sqlalchemy.Table(
'image_properties_backup',
meta,
sqlalchemy.Column('id',
sqlalchemy.Integer(),
primary_key=True,
nullable=False),
sqlalchemy.Column('image_id',
sqlalchemy.String(36),
nullable=False,
index=True),
sqlalchemy.Column('name',
sqlalchemy.String(255),
nullable=False),
sqlalchemy.Column('value',
sqlalchemy.Text()),
sqlalchemy.Column('created_at',
sqlalchemy.DateTime(),
nullable=False),
sqlalchemy.Column('updated_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted',
sqlalchemy.Boolean(),
nullable=False,
default=False,
index=True),
sqlalchemy.UniqueConstraint(
'image_id', 'name',
name='ix_image_properties_image_id_name'),
extend_existing=True)
image_members_backup.create()
image_properties_backup.create()
sql_commands = [
"""INSERT INTO image_members_backup
SELECT * FROM image_members;""",
"""INSERT INTO image_properties_backup
SELECT * FROM image_properties;""",
]
for command in sql_commands:
meta.bind.execute(command)
t_image_members.drop()
t_image_properties.drop()
image_members_backup.rename(name='image_members')
image_properties_backup.rename(name='image_properties')
def _add_db2_constraints():
#Create the foreign keys
sql_commands = [
"""ALTER TABLE image_members ADD CONSTRAINT member_image_id
FOREIGN KEY (image_id)
REFERENCES images (id);""",
"""ALTER TABLE image_properties ADD CONSTRAINT property_image_id
FOREIGN KEY (image_id)
REFERENCES images (id);""",
]
for command in sql_commands:
meta.bind.execute(command)
def _remove_db2_constraints():
#remove the foreign keys constraints
sql_commands = [
"""ALTER TABLE image_members DROP CONSTRAINT member_image_id;""",
"""ALTER TABLE image_properties DROP CONSTRAINT property_image_id;"""
]
for command in sql_commands:
meta.bind.execute(command)
def _downgrade_db2(t_images, t_image_members, t_image_properties):
"""
Downgrade for DB2.
"""
t_images.c.id.alter(sqlalchemy.Integer(), primary_key=True)
image_members_old = sqlalchemy.Table(
'image_members_old',
meta,
sqlalchemy.Column('id',
sqlalchemy.Integer(),
primary_key=True,
nullable=False),
sqlalchemy.Column('image_id',
sqlalchemy.Integer(),
nullable=False,
index=True),
sqlalchemy.Column('member',
sqlalchemy.String(255),
nullable=False),
sqlalchemy.Column('can_share',
sqlalchemy.Boolean(),
nullable=False,
default=False),
sqlalchemy.Column('created_at',
sqlalchemy.DateTime(),
nullable=False),
sqlalchemy.Column('updated_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted',
sqlalchemy.Boolean(),
nullable=False,
default=False,
index=True),
sqlalchemy.UniqueConstraint('image_id', 'member'),
extend_existing=True)
image_properties_old = sqlalchemy.Table(
'image_properties_old',
meta,
sqlalchemy.Column('id',
sqlalchemy.Integer(),
primary_key=True,
nullable=False),
sqlalchemy.Column('image_id',
sqlalchemy.Integer(),
nullable=False,
index=True),
sqlalchemy.Column('name',
sqlalchemy.String(255),
nullable=False),
sqlalchemy.Column('value',
sqlalchemy.Text()),
sqlalchemy.Column('created_at',
sqlalchemy.DateTime(),
nullable=False),
sqlalchemy.Column('updated_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted_at',
sqlalchemy.DateTime()),
sqlalchemy.Column('deleted',
sqlalchemy.Boolean(),
nullable=False,
default=False,
index=True),
sqlalchemy.UniqueConstraint(
'image_id', 'name',
name='ix_image_properties_image_id_name'),
extend_existing=True)
image_members_old.create()
image_properties_old.create()
sql_commands = [
"""INSERT INTO image_members_old
SELECT * FROM image_members;""",
"""INSERT INTO image_properties_old
SELECT * FROM image_properties;""",
]
for command in sql_commands:
meta.bind.execute(command)
t_image_members.drop()
t_image_properties.drop()
image_members_old.rename(name='image_members')
image_properties_old.rename(name='image_properties')
def _downgrade_sqlite(t_images, t_image_members, t_image_properties):
"""
Downgrade 012 -> 011 with special SQLite-compatible logic.
@ -239,12 +456,12 @@ def _downgrade_other(t_images, t_image_members, t_image_properties, dialect):
for fk in foreign_keys:
fk.drop()
_update_all_uuids_to_ids(t_images, t_image_members, t_image_properties)
t_images.c.id.alter(sqlalchemy.Integer(), primary_key=True)
t_image_members.c.image_id.alter(sqlalchemy.Integer())
t_image_properties.c.image_id.alter(sqlalchemy.Integer())
_update_all_uuids_to_ids(t_images, t_image_members, t_image_properties)
for fk in foreign_keys:
fk.create()
@ -336,20 +553,20 @@ def _update_all_uuids_to_ids(t_images, t_image_members, t_image_properties):
t_images.update().\
where(t_images.c.id == old_id).\
values(id=new_id).execute()
values(id=str(new_id)).execute()
t_image_members.update().\
where(t_image_members.c.image_id == old_id).\
values(image_id=new_id).execute()
values(image_id=str(new_id)).execute()
t_image_properties.update().\
where(t_image_properties.c.image_id == old_id).\
values(image_id=new_id).execute()
values(image_id=str(new_id)).execute()
t_image_properties.update().\
where(and_(or_(t_image_properties.c.name == 'kernel_id',
t_image_properties.c.name == 'ramdisk_id'),
t_image_properties.c.value == old_id)).\
values(value=new_id).execute()
values(value=str(new_id)).execute()
new_id += 1