def downgrade(migrate_engine): meta = MetaData(migrate_engine) unique_name = Table('unique_name', meta, Column('id', Integer, primary_key=True), Column('key', String(255), unique=True)) trait_type = Table('trait_type', meta, autoload=True) trait = Table('trait', meta, autoload=True) # Create the UniqueName table, drop the foreign key constraint # to trait_type, drop the trait_type table, rename the # trait.trait_type column to traitname, re-add the dtype to # the trait table, and re-add the old foreign key constraint unique_name.create(migrate_engine) conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT trait_type.id, trait_type.desc " "FROM trait_type") conn.execute(sql) conn.close() params = { 'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id] } if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() # Re-create the old columns in trait Column("name_id", Integer).create(trait) Column("t_type", Integer).create(trait) # copy data from trait_type.data_type into trait.t_type query = select([trait_type.c.id, trait_type.c.data_type]) for key, value in migration.paged(query): trait.update().where(trait.c.trait_type_id == key)\ .values({"t_type": value}).execute() # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.trait_type_id]) for key, value in migration.paged(query): trait.update().where(trait.c.id == key)\ .values({"name_id": value}).execute() # Add a foreign key to the unique_name table params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'trait_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create() trait.c.trait_type_id.drop() # Drop the trait_type table. It isn't needed anymore trait_type.drop()
def _convert_data_type(table, col, from_t, to_t, pk_attr='id', index=False): temp_col_n = 'convert_data_type_temp_col' # Override column we're going to convert with from_t, since the type we're # replacing could be custom and we need to tell SQLALchemy how to perform # CRUD operations with it. table = sa.Table(table.name, table.metadata, sa.Column(col, from_t), extend_existing=True) sa.Column(temp_col_n, to_t).create(table) key_attr = getattr(table.c, pk_attr) orig_col = getattr(table.c, col) new_col = getattr(table.c, temp_col_n) query = sa.select([key_attr, orig_col]) for key, value in migration.paged(query): (table.update().where(key_attr == key).values({ temp_col_n: value }).execute()) orig_col.drop() new_col.alter(name=col) if index: sa.Index('ix_%s_%s' % (table.name, col), new_col).create()
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table( 'event_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255), unique=True), mysql_engine='InnoDB', mysql_charset='utf8', ) event_type.create() event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Event type is a specialization of Unique name, so # we insert into the event_type table all the distinct # unique names from the event.unique_name field along # with the key from the unique_name table, and # then rename the event.unique_name field to event.event_type conn = migrate_engine.connect() sql = ("INSERT INTO event_type " "SELECT unique_name.id, unique_name.key FROM event " "INNER JOIN unique_name " "ON event.unique_name_id = unique_name.id " "GROUP BY unique_name.id") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the event.unique_name column, and re-add a new foreign # key constraint params = { 'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id] } if migrate_engine.name == 'mysql': params['name'] = "event_ibfk_1" fkey = ForeignKeyConstraint(**params) fkey.drop() Column('event_type_id', Integer).create(event) # Move data from unique_name_id column into event_type_id column # and delete the entry from the unique_name table query = select([event.c.id, event.c.unique_name_id]) for key, value in migration.paged(query): event.update().where(event.c.id == key)\ .values({"event_type_id": value}).execute() unique_name.delete()\ .where(unique_name.c.id == key).execute() params = { 'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id] } if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() event.c.unique_name_id.drop()
def upgrade(migrate_engine): meta = MetaData(migrate_engine) trait_type = Table( 'trait_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255)), Column('data_type', Integer), UniqueConstraint('desc', 'data_type', name="tt_unique") ) trait = Table('trait', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) trait_type.create(migrate_engine) # Trait type extracts data from Trait and Unique name. # We take all trait names from Unique Name, and data types # from Trait. We then remove dtype and name from trait, and # remove the name field. conn = migrate_engine.connect() sql = ("INSERT INTO trait_type " "SELECT unique_name.id, unique_name.key, trait.t_type FROM trait " "INNER JOIN unique_name " "ON trait.name_id = unique_name.id " "GROUP BY unique_name.id, unique_name.key, trait.t_type") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the trait.name column, and re-add a new foreign # key constraint params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = "trait_ibfk_1" # foreign key to the unique name table fkey = ForeignKeyConstraint(**params) fkey.drop() Column('trait_type_id', Integer).create(trait) # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.name_id]) for key, value in migration.paged(query): trait.update().where(trait.c.id == key)\ .values({"trait_type_id": value}).execute() trait.c.name_id.drop() params = {'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() # Drop the t_type column to data_type. trait.c.t_type.drop() # Finally, drop the unique_name table - we don't need it # anymore. unique_name.drop()
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table( 'event_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255), unique=True), mysql_engine='InnoDB', mysql_charset='utf8', ) event_type.create() event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Event type is a specialization of Unique name, so # we insert into the event_type table all the distinct # unique names from the event.unique_name field along # with the key from the unique_name table, and # then rename the event.unique_name field to event.event_type conn = migrate_engine.connect() sql = ("INSERT INTO event_type " "SELECT unique_name.id, unique_name.key FROM event " "INNER JOIN unique_name " "ON event.unique_name_id = unique_name.id " "GROUP BY unique_name.id") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the event.unique_name column, and re-add a new foreign # key constraint params = {'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = "event_ibfk_1" fkey = ForeignKeyConstraint(**params) fkey.drop() Column('event_type_id', Integer).create(event) # Move data from unique_name_id column into event_type_id column # and delete the entry from the unique_name table query = select([event.c.id, event.c.unique_name_id]) for key, value in migration.paged(query): event.update().where(event.c.id == key)\ .values({"event_type_id": value}).execute() unique_name.delete()\ .where(unique_name.c.id == key).execute() params = {'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() event.c.unique_name_id.drop()
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table('event_type', meta, autoload=True) event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Re-insert the event type table records into the old # unique_name table. conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT event_type.id, event_type.desc FROM event_type") conn.execute(sql) conn.close() # Drop the foreign key constraint to event_type, drop the # event_type table, rename the event.event_type column to # event.unique_name, and re-add the old foreign # key constraint params = { 'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id] } if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() event_type.drop() Column('unique_name_id', Integer).create(event) # Move data from event_type_id column to unique_name_id column query = select([event.c.id, event.c.event_type_id]) for key, value in migration.paged(query): (event.update().where(event.c.id == key).values({ "unique_name_id": value }).execute()) event.c.event_type_id.drop() params = { 'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id] } if migrate_engine.name == 'mysql': params['name'] = 'event_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create()
def _convert_data_type(table, col, from_t, to_t, pk_attr="id"): temp_col_n = "convert_data_type_temp_col" # Override column we're going to convert with from_t, since the type we're # replacing could be custom and we need to tell SQLALchemy how to perform # CRUD operations with it. table = sa.Table(table.name, table.metadata, sa.Column(col, from_t), extend_existing=True) sa.Column(temp_col_n, to_t).create(table) key_attr = getattr(table.c, pk_attr) orig_col = getattr(table.c, col) new_col = getattr(table.c, temp_col_n) query = sa.select([key_attr, orig_col]) for key, value in migration.paged(query): (table.update().where(key_attr == key).values({temp_col_n: value}).execute()) orig_col.drop() new_col.alter(name=col)
def upgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) # drop foreign keys if migrate_engine.name != 'sqlite': for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] params = { 'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]] } fk_table_name = table_name if migrate_engine.name == "mysql": params['name'] = "_".join(('fk', fk_table_name, column)) elif (migrate_engine.name == "postgresql" and table_name == 'sample'): # fk was not renamed in script 030 params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.drop() # create source field in sample sample = load_tables['sample'] sample.create_column(sa.Column('source_id', sa.String(255))) # move source values to samples sourceassoc = load_tables['sourceassoc'] query = (sa.select([sourceassoc.c.sample_id, sourceassoc.c.source_id ]).where(sourceassoc.c.sample_id.isnot(None))) for sample_id, source_id in migration.paged(query): (sample.update().where(sample_id == sample.c.id).values({ 'source_id': source_id }).execute()) # drop tables for table_name in DROP_TABLES: sa.Table(table_name, meta, autoload=True).drop()
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table('event_type', meta, autoload=True) event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Re-insert the event type table records into the old # unique_name table. conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT event_type.id, event_type.desc FROM event_type") conn.execute(sql) conn.close() # Drop the foreign key constraint to event_type, drop the # event_type table, rename the event.event_type column to # event.unique_name, and re-add the old foreign # key constraint params = {'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() event_type.drop() Column('unique_name_id', Integer).create(event) # Move data from event_type_id column to unique_name_id column query = select([event.c.id, event.c.event_type_id]) for key, value in migration.paged(query): event.update().where(event.c.id == key)\ .values({"unique_name_id": value}).execute() event.c.event_type_id.drop() params = {'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'event_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create()
def upgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) # drop foreign keys if migrate_engine.name != 'sqlite': for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] params = {'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]]} fk_table_name = table_name if migrate_engine.name == "mysql": params['name'] = "_".join(('fk', fk_table_name, column)) elif (migrate_engine.name == "postgresql" and table_name == 'sample'): # fk was not renamed in script 030 params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.drop() # create source field in sample sample = load_tables['sample'] sample.create_column(sa.Column('source_id', sa.String(255))) # move source values to samples sourceassoc = load_tables['sourceassoc'] query = (sa.select([sourceassoc.c.sample_id, sourceassoc.c.source_id]). where(sourceassoc.c.sample_id.isnot(None))) for sample_id, source_id in migration.paged(query): (sample.update().where(sample_id == sample.c.id). values({'source_id': source_id}).execute()) # drop tables for table_name in DROP_TABLES: sa.Table(table_name, meta, autoload=True).drop()
def downgrade(migrate_engine): meta = MetaData(migrate_engine) unique_name = Table( 'unique_name', meta, Column('id', Integer, primary_key=True), Column('key', String(255), unique=True), mysql_engine='InnoDB', mysql_charset='utf8', ) trait_type = Table('trait_type', meta, autoload=True) trait = Table('trait', meta, autoload=True) # Create the UniqueName table, drop the foreign key constraint # to trait_type, drop the trait_type table, rename the # trait.trait_type column to traitname, re-add the dtype to # the trait table, and re-add the old foreign key constraint unique_name.create(migrate_engine) conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT trait_type.id, trait_type.desc " "FROM trait_type") conn.execute(sql) conn.close() params = {'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() # Re-create the old columns in trait Column("name_id", Integer).create(trait) Column("t_type", Integer).create(trait) # copy data from trait_type.data_type into trait.t_type query = select([trait_type.c.id, trait_type.c.data_type]) for key, value in migration.paged(query): trait.update().where(trait.c.trait_type_id == key)\ .values({"t_type": value}).execute() # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.trait_type_id]) for key, value in migration.paged(query): trait.update().where(trait.c.id == key)\ .values({"name_id": value}).execute() # Add a foreign key to the unique_name table params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'trait_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create() trait.c.trait_type_id.drop() # Drop the trait_type table. It isn't needed anymore trait_type.drop()