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)