def define_images_table(meta):
images = Table('images',
meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('name', String(255)),
Column('type', String(30)),
Column('size', Integer()),
Column('status', String(30), nullable=False),
Column('is_public',
Boolean(),
nullable=False,
default=False,
index=True),
Column('location', Text()),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
Column('deleted_at', DateTime()),
Column('deleted',
Boolean(),
nullable=False,
default=False,
index=True),
mysql_engine='InnoDB',
extend_existing=True)
return images
def get_images_table(meta):
"""
Returns the Table object for the images table that
corresponds to the images table definition of this version.
"""
images = Table('images',
meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('name', String(255)),
Column('disk_format', String(20)),
Column('container_format', String(20)),
Column('size', Integer()),
Column('status', String(30), nullable=False),
Column('is_public',
Boolean(),
nullable=False,
default=False,
index=True),
Column('location', Text()),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
Column('deleted_at', DateTime()),
Column('deleted',
Boolean(),
nullable=False,
default=False,
index=True),
Column('checksum', String(32)),
mysql_engine='InnoDB',
useexisting=True)
return images
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
(define_images_table, ) = from_migration_import('001_add_images_table',
['define_images_table'])
(define_image_properties_table, ) = from_migration_import(
'002_add_image_properties_table', ['define_image_properties_table'])
conn = migrate_engine.connect()
images = define_images_table(meta)
image_properties = define_image_properties_table(meta)
# Steps to take, in this order:
# 1) Move the existing type column from Image into
# ImageProperty for all image records that have a non-NULL
# type column
# 2) Drop the type column in images
# 3) Add the new columns to images
# The below wackiness correlates to the following ANSI SQL:
# SELECT images.* FROM images
# LEFT JOIN image_properties
# ON images.id = image_properties.image_id
# AND image_properties.key = 'type'
# WHERE image_properties.image_id IS NULL
# AND images.type IS NOT NULL
#
# which returns all the images that have a type set
# but that DO NOT yet have an image_property record
# with key of type.
sel = select([images],
from_obj=[
images.outerjoin(
image_properties,
and_(images.c.id == image_properties.c.image_id,
image_properties.c.key == 'type'))
]).where(
and_(image_properties.c.image_id == None,
images.c.type != None))
image_records = conn.execute(sel).fetchall()
property_insert = image_properties.insert()
for record in image_records:
conn.execute(property_insert,
image_id=record.id,
key='type',
created_at=record.created_at,
deleted=False,
value=record.type)
conn.close()
disk_format = Column('disk_format', String(20))
disk_format.create(images)
container_format = Column('container_format', String(20))
container_format.create(images)
images.columns['type'].drop()
def get_image_properties_table(meta):
"""
Returns the Table object for the image_properties table that
corresponds to the image_properties table definition of this version.
"""
(get_images_table,) = from_migration_import(
'004_add_checksum', ['get_images_table'])
images = get_images_table(meta)
image_properties = Table('image_properties', meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('image_id', Integer(), ForeignKey('images.id'), nullable=False,
index=True),
Column('name', String(255), nullable=False),
Column('value', Text()),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
Column('deleted_at', DateTime()),
Column('deleted', Boolean(), nullable=False, default=False,
index=True),
UniqueConstraint('image_id', 'name'),
mysql_engine='InnoDB',
useexisting=True)
return image_properties
def get_image_members_table(meta):
images = get_images_table(meta)
image_members = Table('image_members',
meta,
Column('id',
Integer(),
primary_key=True,
nullable=False),
Column('image_id',
Integer(),
ForeignKey('images.id'),
nullable=False,
index=True),
Column('member', String(255), nullable=False),
Column('can_share',
Boolean(),
nullable=False,
default=False),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
Column('deleted_at', DateTime()),
Column('deleted',
Boolean(),
nullable=False,
default=False,
index=True),
UniqueConstraint('image_id', 'member'),
mysql_engine='InnoDB',
extend_existing=True)
Index('ix_image_members_image_id_member', image_members.c.image_id,
image_members.c.member)
return image_members
def define_image_properties_table(meta):
(define_images_table,) = from_migration_import(
'001_add_images_table', ['define_images_table'])
images = define_images_table(meta)
image_properties = Table('image_properties', meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('image_id', Integer(), ForeignKey('images.id'), nullable=False,
index=True),
Column('key', String(255), nullable=False),
Column('value', Text()),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
Column('deleted_at', DateTime()),
Column('deleted', Boolean(), nullable=False, default=False,
index=True),
UniqueConstraint('image_id', 'key'),
mysql_engine='InnoDB',
extend_existing=True)
Index('ix_image_properties_image_id_key', image_properties.c.image_id,
image_properties.c.key)
return image_properties
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# Steps to take, in this order:
# 1) Add type column back to Image
# 2) Move the existing type properties from ImageProperty into
# Image.type
# 3) Drop the disk_format and container_format columns in Image
conn = migrate_engine.connect()
images = get_images_table(meta)
image_properties = get_image_properties_table(meta)
type_col = Column('type', String(30))
type_col.create(images)
sel = select([image_properties]).where(image_properties.c.key == 'type')
type_property_records = conn.execute(sel).fetchall()
for record in type_property_records:
upd = images.update().where(images.c.id == record.image_id).values(
type=record.value)
conn.execute(upd)
dlt = image_properties.delete().where(
image_properties.c.image_id == record.image_id)
conn.execute(dlt)
conn.close()
images.columns['disk_format'].drop()
images.columns['container_format'].drop()
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
images = get_images_table(meta)
checksum = Column('checksum', String(32))
checksum.create(images)
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
images = get_images_table(meta)
owner = Column('owner', String(255))
owner.create(images)