def downgrade(migrate_engine):
if migrate_engine.name == 'sqlite':
return
meta = MetaData(bind=migrate_engine)
load_tables = dict((table_name, Table(table_name, meta, autoload=True))
for table_name in TABLES)
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]]
}
if migrate_engine.name == 'mysql':
params['name'] = "_".join(('fk', table_name, column))
with migrate_engine.begin():
fkey = ForeignKeyConstraint(**params)
fkey.drop()
with migrate_engine.begin():
# Restore data that had been dropped.
table_dump_name = 'dump_' + table_name
table_dump = Table(table_dump_name, meta, autoload=True)
sql = utils.InsertFromSelect(table, table_dump.select())
migrate_engine.execute(sql)
table_dump.drop()
def upgrade(migrate_engine):
if migrate_engine.name == 'sqlite':
return
meta = MetaData(bind=migrate_engine)
load_tables = dict((table_name, Table(table_name, meta, autoload=True))
for table_name in TABLES)
for table_name, indexes in INDEXES.items():
table = load_tables[table_name]
# Save data that conflicted with FK.
columns = [column.copy() for column in table.columns]
table_dump = Table('dump_' + table_name,
meta,
*columns,
mysql_engine='InnoDB')
table_dump.create()
for column, ref_table_name, ref_column_name in indexes:
ref_table = load_tables[ref_table_name]
subq = select([getattr(ref_table.c, ref_column_name)])
sql = utils.InsertFromSelect(
table_dump,
table.select().where(~getattr(table.c, column).in_(subq)))
sql_del = table.delete().where(~getattr(table.c, column).in_(subq))
migrate_engine.execute(sql)
migrate_engine.execute(sql_del)
params = {
'columns': [table.c[column]],
'refcolumns': [ref_table.c[ref_column_name]]
}
if migrate_engine.name == 'mysql':
params['name'] = "_".join(('fk', table_name, column))
fkey = ForeignKeyConstraint(**params)
fkey.create()
def restore_rows(engine, meta, table_name):
# Restore data that had been dropped in the upgrade
table = Table(table_name, meta, autoload=True)
table_dump_name = 'dump_' + table_name
table_dump = Table(table_dump_name, meta, autoload=True)
sql = utils.InsertFromSelect(table, table_dump.select())
engine.execute(sql)
table_dump.drop()
def test_insert_from_select(self):
insert_table_name = "__test_insert_to_table__"
select_table_name = "__test_select_from_table__"
uuidstrs = []
for unused in range(10):
uuidstrs.append(uuid.uuid4().hex)
for key, engine in self.engines.items():
meta = MetaData()
meta.bind = engine
conn = engine.connect()
insert_table = Table(
insert_table_name, meta,
Column('id',
Integer,
primary_key=True,
nullable=False,
autoincrement=True),
Column('uuid', String(36), nullable=False))
select_table = Table(
select_table_name, meta,
Column('id',
Integer,
primary_key=True,
nullable=False,
autoincrement=True),
Column('uuid', String(36), nullable=False))
insert_table.create()
select_table.create()
# Add 10 rows to select_table
for uuidstr in uuidstrs:
ins_stmt = select_table.insert().values(uuid=uuidstr)
conn.execute(ins_stmt)
# Select 4 rows in one chunk from select_table
column = select_table.c.id
query_insert = select([select_table],
select_table.c.id < 5).order_by(column)
insert_statement = utils.InsertFromSelect(insert_table,
query_insert)
result_insert = conn.execute(insert_statement)
# Verify we insert 4 rows
self.assertEqual(result_insert.rowcount, 4)
query_all = select([insert_table]).\
where(insert_table.c.uuid.in_(uuidstrs))
rows = conn.execute(query_all).fetchall()
# Verify we really have 4 rows in insert_table
self.assertEqual(len(rows), 4)
insert_table.drop()
select_table.drop()
def dump_cleanup_rows(engine, meta, table, where):
# Dump rows that match where clause, then delete the rows
dump_table_name = 'dump_' + table.name
table_dump = meta.tables.get(dump_table_name)
if table_dump is None:
columns = [c.copy() for c in table.columns]
table_dump = Table(dump_table_name,
meta,
*columns,
mysql_engine='InnoDB')
table_dump.create()
engine.execute(
utils.InsertFromSelect(table_dump,
table.select().where(where)))
engine.execute(table.delete().where(where))