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(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()
