def _downgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) stack = sqlalchemy.Table('stack', meta, autoload=True) table_name = stack.name # ignore CheckConstraints and FK Constraint on prev_raw_template_id. ignorecols = [ stack.c.prev_raw_template_id.name, stack.c.current_traversal.name, stack.c.current_deps.name, ] ignorecons = [ 'prev_raw_template_ref', ] new_stack = migrate_utils.clone_table(table_name + '__tmp__', stack, meta, ignorecols=ignorecols, ignorecons=ignorecons) migrate_utils.migrate_data(migrate_engine, stack, new_stack, ['prev_raw_template_id', 'current_traversal', 'current_deps']) # add the indexes back to new table _add_indexes(migrate_engine, new_stack)
def _downgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) stack = sqlalchemy.Table('stack', meta, autoload=True) table_name = stack.name # ignore CheckConstraints and FK Constraint on prev_raw_template_id. ignorecols = [ stack.c.prev_raw_template_id.name, stack.c.current_traversal.name, stack.c.current_deps.name, ] ignorecons = [ 'prev_raw_template_ref', ] new_stack = migrate_utils.clone_table(table_name + '__tmp__', stack, meta, ignorecols=ignorecols, ignorecons=ignorecons) migrate_utils.migrate_data( migrate_engine, stack, new_stack, ['prev_raw_template_id', 'current_traversal', 'current_deps']) # add the indexes back to new table _add_indexes(migrate_engine, new_stack)
def _downgrade_062_sqlite(migrate_engine, metadata, table): new_table = migrate_utils.clone_table( table.name + '__tmp__', table, metadata, ignorecols=['parent_resource_name']) migrate_utils.migrate_data(migrate_engine, table, new_table, ['parent_resource_name'])
def upgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) newcols = [ sqlalchemy.Column('environment', heat_db_types.Json), sqlalchemy.Column('predecessor', sqlalchemy.Integer, sqlalchemy.ForeignKey('raw_template.id')) ] new_template = migrate_utils.clone_table('new_raw_template', tmpl_table, meta, newcols=newcols) stack_table = sqlalchemy.Table('stack', meta, autoload=True) ignorecols = [stack_table.c.parameters.name] new_stack = migrate_utils.clone_table('new_stack', stack_table, meta, ignorecols=ignorecols) # migrate parameters to environment templates = list(tmpl_table.select().order_by( sqlalchemy.sql.expression.asc(tmpl_table.c.created_at)).execute()) stacks = list(stack_table.select().order_by( sqlalchemy.sql.expression.asc(stack_table.c.created_at)).execute()) stack_parameters = {} for s in stacks: stack_parameters[s.raw_template_id] = (s.parameters, s.deleted_at) colnames = [c.name for c in tmpl_table.columns] for template in templates: values = dict( zip(colnames, map(lambda colname: getattr(template, colname), colnames))) params, del_at = stack_parameters.get(values['id'], (None, None)) if params is not None and del_at is None: values['environment'] = params migrate_engine.execute(new_template.insert(values)) # migrate stacks to new table migrate_utils.migrate_data(migrate_engine, stack_table, new_stack, skip_columns=['parameters']) # Drop old tables and rename new ones tmpl_table.drop() # add the indexes back to new table _add_indexes(migrate_engine, new_stack) new_template.rename('raw_template')
def test_migrate_data(self): meta = MetaData(bind=self.engine) # create TableA table_a = Table('TableA', meta, Column('id', Integer, primary_key=True), Column('first', String(8), nullable=False), Column('second', Integer)) table_a.create() # update it with sample data values = [ {'id': 1, 'first': 'a'}, {'id': 2, 'first': 'b'}, {'id': 3, 'first': 'c'} ] for value in values: self.engine.execute(table_a.insert(values=value)) # create TableB similar to TableA, except column 'second' table_b = Table('TableB', meta, Column('id', Integer, primary_key=True), Column('first', String(8), nullable=False)) table_b.create() # migrate data migrate_utils.migrate_data(self.engine, table_a, table_b, ['second']) # validate table_a is dropped self.assertTrue(self.engine.dialect.has_table( self.engine.connect(), 'TableA'), 'Data migration failed to drop source table') # validate table_b is updated with data from table_a table_b_rows = list(table_b.select().execute()) self.assertEqual(3, len(table_b_rows), "Data migration is failed") table_b_values = [] for row in table_b_rows: table_b_values.append({'id': row.id, 'first': row.first}) self.assertEqual(values, table_b_values, "Data migration failed with invalid data copy")
def upgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) newcols = [ sqlalchemy.Column('environment', heat_db_types.Json), sqlalchemy.Column('predecessor', sqlalchemy.Integer, sqlalchemy.ForeignKey('raw_template.id'))] new_template = migrate_utils.clone_table('new_raw_template', tmpl_table, meta, newcols=newcols) stack_table = sqlalchemy.Table('stack', meta, autoload=True) ignorecols = [stack_table.c.parameters.name] new_stack = migrate_utils.clone_table('new_stack', stack_table, meta, ignorecols=ignorecols) # migrate parameters to environment templates = list(tmpl_table.select().order_by( sqlalchemy.sql.expression.asc(tmpl_table.c.created_at)) .execute()) stacks = list(stack_table.select().order_by( sqlalchemy.sql.expression.asc(stack_table.c.created_at)) .execute()) stack_parameters = {} for s in stacks: stack_parameters[s.raw_template_id] = (s.parameters, s.deleted_at) colnames = [c.name for c in tmpl_table.columns] for template in templates: values = dict(zip(colnames, map(lambda colname: getattr(template, colname), colnames))) params, del_at = stack_parameters.get(values['id'], (None, None)) if params is not None and del_at is None: values['environment'] = params migrate_engine.execute(new_template.insert(values)) # migrate stacks to new table migrate_utils.migrate_data(migrate_engine, stack_table, new_stack, skip_columns=['parameters']) # Drop old tables and rename new ones tmpl_table.drop() # add the indexes back to new table _add_indexes(migrate_engine, new_stack) new_template.rename('raw_template')
def upgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) ignorecols = [tmpl_table.c.predecessor.name] new_template = migrate_utils.clone_table('new_raw_template', tmpl_table, meta, ignorecols=ignorecols) # migrate stacks to new table migrate_utils.migrate_data(migrate_engine, tmpl_table, new_template, skip_columns=['predecessor'])
def test_migrate_data(self): meta = MetaData(bind=self.engine) # create TableA table_a = Table('TableA', meta, Column('id', Integer, primary_key=True), Column('first', String(8), nullable=False), Column('second', Integer)) table_a.create() # update it with sample data values = [{ 'id': 1, 'first': 'a' }, { 'id': 2, 'first': 'b' }, { 'id': 3, 'first': 'c' }] for value in values: self.engine.execute(table_a.insert(values=value)) # create TableB similar to TableA, except column 'second' table_b = Table('TableB', meta, Column('id', Integer, primary_key=True), Column('first', String(8), nullable=False)) table_b.create() # migrate data migrate_utils.migrate_data(self.engine, table_a, table_b, ['second']) # validate table_a is dropped self.assertTrue( self.engine.dialect.has_table(self.engine.connect(), 'TableA'), 'Data migration failed to drop source table') # validate table_b is updated with data from table_a table_b_rows = list(table_b.select().execute()) self.assertEqual(3, len(table_b_rows), "Data migration is failed") table_b_values = [] for row in table_b_rows: table_b_values.append({'id': row.id, 'first': row.first}) self.assertEqual(values, table_b_values, "Data migration failed with invalid data copy")
def _downgrade_052_sqlite(migrate_engine, metadata, table): table_name = table.name constraints = [ c.copy() for c in table.constraints if not isinstance(c, sqlalchemy.CheckConstraint) ] columns = [c.copy() for c in table.columns if c.name != "convergence"] new_table = sqlalchemy.Table(table_name + "__tmp__", metadata, *(columns + constraints)) new_table.create() migrate_utils.migrate_data(migrate_engine, table, new_table, ['convergence'])
def _downgrade_045_sqlite(migrate_engine, metadata, table): table_name = table.name constraints = [ c.copy() for c in table.constraints if not isinstance(c, sqlalchemy.CheckConstraint) ] columns = [c.copy() for c in table.columns if c.name != "backup"] new_table = sqlalchemy.Table(table_name + "__tmp__", metadata, *(columns + constraints)) new_table.create() migrate_utils.migrate_data(migrate_engine, table, new_table, ['backup'])
def downgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine stack_table = sqlalchemy.Table('stack', meta, autoload=True) newcols = [sqlalchemy.Column('parameters', heat_db_types.Json)] new_stack = migrate_utils.clone_table('new_stack', stack_table, meta, newcols=newcols) tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) ignorecols = [tmpl_table.c.environment.name, tmpl_table.c.predecessor.name] new_template = migrate_utils.clone_table('new_raw_template', tmpl_table, meta, ignorecols=ignorecols) # migrate stack data to new table stacks = list(stack_table.select().order_by( sqlalchemy.sql.expression.asc(stack_table.c.created_at)) .execute()) colnames = [c.name for c in stack_table.columns] for stack in stacks: values = dict(zip(colnames, map(lambda colname: getattr(stack, colname), colnames))) migrate_engine.execute(new_stack.insert(values)) update_query = new_stack.update().values( parameters=sqlalchemy.select([tmpl_table.c.environment]). where(new_stack.c.raw_template_id == tmpl_table.c.id). as_scalar()) migrate_engine.execute(update_query) # migrate template data to new table migrate_utils.migrate_data(migrate_engine, tmpl_table, new_template, skip_columns=['environment', 'predecessor']) stack_table.drop() new_stack.rename('stack') # add the indexes back to new table _add_indexes(migrate_engine, new_stack)
def _downgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine resource_table = sqlalchemy.Table('resource', meta, autoload=True) ignorecons = ['current_template_fkey_ref'] ignorecols = [resource_table.c.current_template_id.name, resource_table.c.needed_by.name, resource_table.c.requires.name, resource_table.c.replaces.name, resource_table.c.replaced_by.name] new_resource = migrate_utils.clone_table('new_resource', resource_table, meta, ignorecols=ignorecols, ignorecons=ignorecons) # migrate resources to new table migrate_utils.migrate_data(migrate_engine, resource_table, new_resource, skip_columns=ignorecols)
def _downgrade_sqlite(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine resource_table = sqlalchemy.Table('resource', meta, autoload=True) ignorecons = ['current_template_fkey_ref'] ignorecols = [ resource_table.c.current_template_id.name, resource_table.c.needed_by.name, resource_table.c.requires.name, resource_table.c.replaces.name, resource_table.c.replaced_by.name ] new_resource = migrate_utils.clone_table('new_resource', resource_table, meta, ignorecols=ignorecols, ignorecons=ignorecons) # migrate resources to new table migrate_utils.migrate_data(migrate_engine, resource_table, new_resource, skip_columns=ignorecols)