def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine build_user_tbl = sa.Table( "build_user", metadata, sa.Column('buildid', sa.Integer, nullable=False), sa.Column('userid', sa.Integer, nullable=True), sa.Column('finish_time', sa.Integer), ) build_user_tbl.create() idx = sa.Index('build_user_buildid', build_user_tbl.c.buildid, build_user_tbl.c.userid) idx.create() users_tbl = sa.Table('users', metadata, autoload=True) builds_table = sa.Table('builds', metadata, autoload=True) cons = constraint.ForeignKeyConstraint([build_user_tbl.c.buildid], [builds_table.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([build_user_tbl.c.userid], [users_tbl.c.uid]) cons.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine buildrequests_tbl = sa.Table('buildrequests', metadata, autoload=True) mergebrid = sa.Column('mergebrid', sa.Integer) mergebrid.create(buildrequests_tbl) idx = sa.Index('buildrequests_mergebrid', buildrequests_tbl.c.mergebrid, unique=False) idx.create() # Data is up to date, now force integrity cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.mergebrid], [buildrequests_tbl.c.id]) cons.create() startbrid = sa.Column('startbrid', sa.Integer) startbrid.create(buildrequests_tbl) idx = sa.Index('buildrequests_startbrid', buildrequests_tbl.c.startbrid, unique=False) idx.create() # Data is up to date, now force integrity cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.startbrid], [buildrequests_tbl.c.id]) cons.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine sourcestamps_table = sa.Table('sourcestamps', metadata, autoload=True) buildsets_table = sa.Table('buildsets', metadata, autoload=True) # Create the sourcestampset table # that defines a sourcestampset sourcestampsets_table = sa.Table( "sourcestampsets", metadata, sa.Column("id", sa.Integer, primary_key=True), ) sourcestampsets_table.create() # All current sourcestampid's are migrated to sourcestampsetid # Insert all sourcestampid's as setid's into sourcestampsets table sourcestampsetids = sa.select([sourcestamps_table.c.id]) # this doesn't seem to work without str() -- verified in sqla 0.6.0 - 0.7.1 migrate_engine.execute( str(sautils.InsertFromSelect(sourcestampsets_table, sourcestampsetids))) # rename the buildsets table column if migrate_engine.dialect.name != 'sqlite': cons = constraint.ForeignKeyConstraint( [buildsets_table.c.sourcestampid], [sourcestamps_table.c.id]) cons.drop() buildsets_table.c.sourcestampid.alter(name='sourcestampsetid') metadata.remove(buildsets_table) buildsets_table = sa.Table('buildsets', metadata, autoload=True) cons = constraint.ForeignKeyConstraint( [buildsets_table.c.sourcestampsetid], [sourcestampsets_table.c.id]) cons.create() # Add sourcestampsetid including index to sourcestamps table ss_sourcestampsetid = sa.Column('sourcestampsetid', sa.Integer) ss_sourcestampsetid.create(sourcestamps_table) # Update the setid to the same value as sourcestampid migrate_engine.execute( str(sourcestamps_table.update().values( sourcestampsetid=sourcestamps_table.c.id))) ss_sourcestampsetid.alter(nullable=False) # Data is up to date, now force integrity cons = constraint.ForeignKeyConstraint( [sourcestamps_table.c.sourcestampsetid], [sourcestampsets_table.c.id]) cons.create() # Add index for performance reasons to find all sourcestamps in a set quickly idx = sa.Index('sourcestamps_sourcestampsetid', sourcestamps_table.c.sourcestampsetid, unique=False) idx.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine # what defines a user users = sa.Table("users", metadata, sa.Column("uid", sa.Integer, primary_key=True), sa.Column("identifier", sa.String(255), nullable=False), ) users.create() idx = sa.Index('users_identifier', users.c.identifier) idx.create() # ways buildbot knows about users users_info = sa.Table("users_info", metadata, sa.Column("uid", sa.Integer, nullable=False), sa.Column("attr_type", sa.String(128), nullable=False), sa.Column("attr_data", sa.String(128), nullable=False) ) users_info.create() cons = constraint.ForeignKeyConstraint([users_info.c.uid], [users.c.uid]) cons.create() idx = sa.Index('users_info_uid', users_info.c.uid) idx.create() idx = sa.Index('users_info_uid_attr_type', users_info.c.uid, users_info.c.attr_type, unique=True) idx.create() idx = sa.Index('users_info_attrs', users_info.c.attr_type, users_info.c.attr_data, unique=True) idx.create() # correlates change authors and user uids changes_tbl= sa.Table('changes', metadata, autoload=True) change_users = sa.Table("change_users", metadata, sa.Column("changeid", sa.Integer, nullable=False), sa.Column("uid", sa.Integer, nullable=False) ) change_users.create() cons = constraint.ForeignKeyConstraint([change_users.c.changeid], [changes_tbl.c.changeid]) cons.create() cons = constraint.ForeignKeyConstraint([change_users.c.uid], [users.c.uid]) cons.create() idx = sa.Index('change_users_changeid', change_users.c.changeid) idx.create()
def test_drop_with_foreign_keys(self): self.table.drop() self.meta.clear() # create FK's target reftable = Table( 'tmp_ref', self.meta, Column('id', Integer, primary_key=True), ) if self.engine.has_table(reftable.name): reftable.drop() reftable.create() # add a table with two foreign key columns self.table = Table( self.table_name, self.meta, Column('id', Integer, primary_key=True), Column('r1', Integer, ForeignKey('tmp_ref.id', name='test_fk1')), Column('r2', Integer, ForeignKey('tmp_ref.id', name='test_fk2')), ) self.table.create() # paranoid check self.assertEqual([['r1'], ['r2']], self._actual_foreign_keys()) # delete one if self.engine.name == 'mysql': constraint.ForeignKeyConstraint([self.table.c.r2], [reftable.c.id], name='test_fk2').drop() self.table.c.r2.drop() # check remaining foreign key is there self.assertEqual([['r1']], self._actual_foreign_keys())
def _visit_column_foreign_key(self, delta): table = delta.table column = getattr(table.c, delta.current_name) cons = constraint.ForeignKeyConstraint(column, autoload=True) fk = delta['foreign_key'] if fk: # For now, cons.columns is limited to one column: # no multicolumn FKs column.foreign_key = ForeignKey(*cons.columns) else: column_foreign_key = None cons.drop() cons.create()
def test_drop_with_complex_foreign_keys(self): from sqlalchemy.schema import ForeignKeyConstraint from sqlalchemy.schema import UniqueConstraint self.table.drop() self.meta.clear() # NOTE(mriedem): DB2 does not currently support unique constraints # on nullable columns, so the columns that are used to create the # foreign keys here need to be non-nullable for testing with DB2 # to work. # create FK's target reftable = Table('tmp_ref', self.meta, Column('id', Integer, primary_key=True), Column('jd', Integer, nullable=False), UniqueConstraint('id','jd') ) if self.engine.has_table(reftable.name): reftable.drop() reftable.create() # add a table with a complex foreign key constraint self.table = Table( self.table_name, self.meta, Column('id', Integer, primary_key=True), Column('r1', Integer, nullable=False), Column('r2', Integer, nullable=False), ForeignKeyConstraint(['r1','r2'], [reftable.c.id,reftable.c.jd], name='test_fk') ) self.table.create() # paranoid check self.assertEqual([['r1','r2']], self._actual_foreign_keys()) # delete one if self.engine.name == 'mysql': constraint.ForeignKeyConstraint([self.table.c.r1, self.table.c.r2], [reftable.c.id, reftable.c.jd], name='test_fk').drop() self.table.c.r2.drop() # check the constraint is gone, since part of it # is no longer there - if people hit this, # they may be confused, maybe we should raise an error # and insist that the constraint is deleted first, separately? self.assertEqual([], self._actual_foreign_keys())
def upgrade(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) resource = sqlalchemy.Table('resource', meta, autoload=True) resource_properties_data = sqlalchemy.Table('resource_properties_data', meta, autoload=True) attr_data_id = sqlalchemy.Column('attr_data_id', sqlalchemy.Integer) attr_data_id.create(resource) res_fkey = constraint.ForeignKeyConstraint( columns=[resource.c.attr_data_id], refcolumns=[resource_properties_data.c.id], name='rsrc_attr_data_ref') res_fkey.create()
def upgrade(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) resource_properties_data = sqlalchemy.Table( 'resource_properties_data', meta, sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True, nullable=False), sqlalchemy.Column('data', types.Json), sqlalchemy.Column('encrypted', sqlalchemy.Boolean), sqlalchemy.Column('created_at', sqlalchemy.DateTime), sqlalchemy.Column('updated_at', sqlalchemy.DateTime), mysql_engine='InnoDB', mysql_charset='utf8') resource_properties_data.create() resource = sqlalchemy.Table('resource', meta, autoload=True) rsrc_prop_data_id = sqlalchemy.Column('rsrc_prop_data_id', sqlalchemy.Integer) rsrc_prop_data_id.create(resource) res_fkey = constraint.ForeignKeyConstraint( columns=[resource.c.rsrc_prop_data_id], refcolumns=[resource_properties_data.c.id], name='rsrc_rsrc_prop_data_ref') res_fkey.create() event = sqlalchemy.Table('event', meta, autoload=True) rsrc_prop_data_id = sqlalchemy.Column('rsrc_prop_data_id', sqlalchemy.Integer) rsrc_prop_data_id.create(event) ev_fkey = constraint.ForeignKeyConstraint( columns=[event.c.rsrc_prop_data_id], refcolumns=[resource_properties_data.c.id], name='ev_rsrc_prop_data_ref') ev_fkey.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine user_props = sa.Table("user_properties", metadata, sa.Column("uid", sa.Integer, nullable=False), sa.Column('prop_type', sa.String(128), nullable=False), sa.Column('prop_data', sa.String(128), nullable=False), sa.UniqueConstraint('uid', 'prop_type', name='users_uid_prop_type'), ) user_props.create() idx = sa.Index('user_props_attrs', user_props.c.prop_type, user_props.c.prop_data) idx.create() users_tbl = sa.Table('users', metadata, autoload=True) cons = constraint.ForeignKeyConstraint([user_props.c.uid], [users_tbl.c.uid]) cons.create()
def downgrade(migrate_engine): if migrate_engine.name == 'sqlite': _downgrade_sqlite(migrate_engine) return meta = sqlalchemy.MetaData(bind=migrate_engine) resource = sqlalchemy.Table('resource', meta, autoload=True) raw_template = sqlalchemy.Table('raw_template', meta, autoload=True) fkey = constraint.ForeignKeyConstraint( columns=[resource.c.current_template_id], refcolumns=[raw_template.c.id], name='current_template_fkey_ref') fkey.drop() resource.c.current_template_id.drop() resource.c.needed_by.drop() resource.c.requires.drop() resource.c.replaces.drop() resource.c.replaced_by.drop()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine user_properties_tbl = sa.Table('user_properties', metadata, autoload=True) users_tbl = sa.Table('users', metadata, autoload=True) # add missing index # this will allow adding other properties to users idx = sa.Index('user_properties_uid', user_properties_tbl.c.uid, unique=False) idx.create() if migrate_engine.dialect.name != 'mysql': return # cascade if user is deleted / updated on the db cons = constraint.ForeignKeyConstraint([user_properties_tbl.c.uid], [users_tbl.c.uid], onupdate="CASCADE", ondelete="CASCADE") cons.drop() cons.create()
def test_fk(self): """Can create columns with foreign keys""" # create FK's target reftable = Table( 'tmp_ref', self.meta, Column('id', Integer, primary_key=True), ) if self.engine.has_table(reftable.name): reftable.drop() reftable.create() # create column with fk col = Column('data', Integer, ForeignKey(reftable.c.id, name='testfk')) col.create(self.table) # check if constraint is added for cons in self.table.constraints: if isinstance(cons, sqlalchemy.schema.ForeignKeyConstraint): break else: self.fail('No constraint found') # TODO: test on db level if constraints work if SQLA_07: self.assertEqual(reftable.c.id.name, list(col.foreign_keys)[0].column.name) else: self.assertEqual(reftable.c.id.name, col.foreign_keys[0].column.name) if self.engine.name == 'mysql': constraint.ForeignKeyConstraint([self.table.c.data], [reftable.c.id], name='testfk').drop() col.drop(self.table) if self.engine.has_table(reftable.name): reftable.drop()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine # existing objects table, used as a foreign key objects = sa.Table( "objects", metadata, # unique ID for this object sa.Column("id", sa.Integer, primary_key=True), # object's user-given name sa.Column('name', sa.String(128), nullable=False), # object's class name, basically representing a "type" for the state sa.Column('class_name', sa.String(128), nullable=False), # prohibit multiple id's for the same object sa.UniqueConstraint('name', 'class_name', name='object_identity'), ) # add mastersconfig table mastersconfig_table = sa.Table( 'mastersconfig', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('buildbotURL', sa.Text, nullable=False), sa.Column('objectid', sa.Integer, index=True, unique=True, nullable=False), ) # create the initial schema mastersconfig_table.create() cons = constraint.ForeignKeyConstraint([mastersconfig_table.c.objectid], [objects.c.id]) cons.create()
def upgrade(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) resource = sqlalchemy.Table('resource', meta, autoload=True) raw_template = sqlalchemy.Table('raw_template', meta, autoload=True) needed_by = sqlalchemy.Column('needed_by', types.List) requires = sqlalchemy.Column('requires', types.List) replaces = sqlalchemy.Column('replaces', sqlalchemy.Integer) replaced_by = sqlalchemy.Column('replaced_by', sqlalchemy.Integer) current_template_id = sqlalchemy.Column('current_template_id', sqlalchemy.Integer) needed_by.create(resource) requires.create(resource) replaces.create(resource) replaced_by.create(resource) current_template_id.create(resource) fkey = constraint.ForeignKeyConstraint( columns=[resource.c.current_template_id], refcolumns=[raw_template.c.id], name='current_template_fkey_ref') fkey.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine objects = sa.Table("objects", metadata, sa.Column("id", sa.Integer, primary_key=True), sa.Column('name', sa.String(128), nullable=False), sa.Column('class_name', sa.String(128), nullable=False), sa.UniqueConstraint('name', 'class_name', name='object_identity'), ) objects.create() object_state = sa.Table("object_state", metadata, sa.Column("objectid", sa.Integer, nullable=False), sa.Column("name", sa.String(length=255), nullable=False), sa.Column("value_json", sa.Text, nullable=False), sa.UniqueConstraint('objectid', 'name', name='name_per_object'), ) object_state.create() cons = constraint.ForeignKeyConstraint([object_state.c.objectid], [objects.c.id]) cons.create()
def upgrade(migrate_engine): meta.bind = migrate_engine collation = 'ascii_general_ci' \ if isinstance(migrate_engine.dialect, mysqldb.MySQLDialect) \ else None package = schema.Table( 'package', meta, schema.Column('id', types.String(32), primary_key=True, nullable=False), schema.Column('archive', types.LargeBinary), schema.Column('fully_qualified_name', StringWithCollation(512, collation=collation), index=True, unique=True), schema.Column('type', types.String(20)), schema.Column('author', types.String(80)), schema.Column('name', types.String(20)), schema.Column('enabled', types.Boolean), schema.Column('description', types.String(512)), schema.Column('is_public', types.Boolean), schema.Column('logo', types.LargeBinary), schema.Column('owner_id', types.String(36)), schema.Column('ui_definition', types.Text), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), ) package.create() category = schema.Table( 'category', meta, schema.Column('id', types.String(32), primary_key=True, nullable=False), schema.Column('name', types.String(80), nullable=False, index=True, unique=True), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), ) category.create() package_to_category = schema.Table( 'package_to_category', meta, schema.Column('package_id', types.String(32)), schema.Column('category_id', types.String(32))) package_to_category.create() constraint.ForeignKeyConstraint(columns=[package_to_category.c.package_id], refcolumns=[package.c.id]).create() constraint.ForeignKeyConstraint( columns=[package_to_category.c.category_id], refcolumns=[category.c.id]).create() tag = schema.Table( 'tag', meta, schema.Column('id', types.String(32), primary_key=True, nullable=False), schema.Column('name', types.String(80), nullable=False, index=True, unique=True), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), ) tag.create() package_to_tag = schema.Table( 'package_to_tag', meta, schema.Column('package_id', types.String(32)), schema.Column('tag_id', types.String(32))) package_to_tag.create() constraint.ForeignKeyConstraint(columns=[package_to_tag.c.package_id], refcolumns=[package.c.id]).create() constraint.ForeignKeyConstraint(columns=[package_to_tag.c.tag_id], refcolumns=[tag.c.id]).create() class_definition = schema.Table( 'class_definition', meta, schema.Column('id', types.String(32), primary_key=True, nullable=False), schema.Column('name', types.String(80), index=True), schema.Column('package_id', types.String(32)), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), ) class_definition.create() constraint.ForeignKeyConstraint(columns=[class_definition.c.package_id], refcolumns=[package.c.id]).create()
def upgrade(migrate_engine): meta.bind = migrate_engine meta.reflect() environment = schema.Table( 'environment', meta, schema.Column('id', types.String(32), primary_key=True), schema.Column('name', types.String(255), nullable=False), schema.Column('created', types.DateTime(), nullable=False), schema.Column('updated', types.DateTime(), nullable=False), schema.Column('tenant_id', types.String(32), nullable=False), schema.Column('version', types.BigInteger, nullable=False, server_default='0'), schema.Column('description', types.Text(), nullable=False)) environment.create() session = schema.Table( 'session', meta, schema.Column('id', types.String(32), primary_key=True), schema.Column('environment_id', types.String(32), nullable=False), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), schema.Column('user_id', types.String(32), nullable=False), schema.Column('version', types.BigInteger, nullable=False, server_default='0'), schema.Column('description', types.Text(), nullable=True), schema.Column('state', types.Text(), nullable=False)) session.create() environment = schema.Table('environment', meta, autoload=True) const.ForeignKeyConstraint(columns=[session.c.environment_id], refcolumns=[environment.c.id]).create() deployment = schema.Table( 'deployment', meta, schema.Column('id', types.String(32), primary_key=True), schema.Column('environment_id', types.String(32), nullable=False), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), schema.Column('started', types.DateTime, nullable=False), schema.Column('description', types.Text(), nullable=True), schema.Column('finished', types.DateTime, nullable=True)) deployment.create() environment = schema.Table('environment', meta, autoload=True) const.ForeignKeyConstraint(columns=[deployment.c.environment_id], refcolumns=[environment.c.id]).create() status = schema.Table( 'status', meta, schema.Column('id', types.String(32), primary_key=True), schema.Column('created', types.DateTime, nullable=False), schema.Column('updated', types.DateTime, nullable=False), schema.Column('entity', types.String(10), nullable=True), schema.Column('entity_id', types.String(32), nullable=True), schema.Column('environment_id', types.String(32), nullable=True), schema.Column('deployment_id', types.String(32), nullable=False), schema.Column('text', types.Text(), nullable=False), schema.Column('details', types.Text(), nullable=True), schema.Column('level', types.String(32), nullable=False, server_default='info')) status.create() const.ForeignKeyConstraint(columns=[status.c.deployment_id], refcolumns=[deployment.c.id]).create()
def upgrade(migrate_engine): # this only applies to postgres if migrate_engine.dialect.name != 'mysql': return metadata = sa.MetaData() metadata.bind = migrate_engine changeset.alter_column(sa.Column('buildername', sa.String(255), nullable=False), table="buildrequests", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('author', sa.String(255), nullable=False), table="changes", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('branch', sa.String(255)), table="changes", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('revision', sa.String(255)), table="changes", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('category', sa.String(255)), table="changes", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('identifier', sa.String(255), nullable=False), table="users", metadata=metadata, engine=migrate_engine) changeset.alter_column(sa.Column('name', sa.String(255), nullable=False), table="object_state", metadata=metadata, engine=migrate_engine) migrate_engine.execute("Alter table buildrequests ENGINE=InnoDB") migrate_engine.execute("Alter table buildsets ENGINE=InnoDB") migrate_engine.execute("Alter table sourcestampsets ENGINE=InnoDB") migrate_engine.execute("Alter table buildrequest_claims ENGINE=InnoDB") migrate_engine.execute("Alter table objects ENGINE=InnoDB") migrate_engine.execute("Alter table builds ENGINE=InnoDB") migrate_engine.execute("Alter table buildset_properties ENGINE=InnoDB") migrate_engine.execute("Alter table change_files ENGINE=InnoDB") migrate_engine.execute("Alter table changes ENGINE=InnoDB") migrate_engine.execute("Alter table change_properties ENGINE=InnoDB") migrate_engine.execute("Alter table change_users ENGINE=InnoDB") migrate_engine.execute("Alter table users ENGINE=InnoDB") migrate_engine.execute("Alter table mastersconfig ENGINE=InnoDB") migrate_engine.execute("Alter table migrate_version ENGINE=InnoDB") migrate_engine.execute("Alter table object_state ENGINE=InnoDB") migrate_engine.execute("Alter table users_info ENGINE=InnoDB") migrate_engine.execute("Alter table patches ENGINE=InnoDB") migrate_engine.execute("Alter table sourcestamps ENGINE=InnoDB") migrate_engine.execute("Alter table sourcestamp_changes ENGINE=InnoDB") migrate_engine.execute("Alter table scheduler_changes ENGINE=InnoDB") buildrequests_tbl = sa.Table('buildrequests', metadata, autoload=True) buildsets_tbl = sa.Table('buildsets', metadata, autoload=True) sourcestampsets_tbl = sa.Table('sourcestampsets', metadata, autoload=True) buildrequest_claims_tbl = sa.Table('buildrequest_claims', metadata, autoload=True) objects_tbl = sa.Table('objects', metadata, autoload=True) builds_tbl = sa.Table('builds', metadata, autoload=True) buildset_properties_tbl = sa.Table('buildset_properties', metadata, autoload=True) change_files_tbl = sa.Table('change_files', metadata, autoload=True) changes_tbl = sa.Table('changes', metadata, autoload=True) change_properties_tbl = sa.Table('change_properties', metadata, autoload=True) change_users_tbl = sa.Table('change_users', metadata, autoload=True) users_tbl = sa.Table('users', metadata, autoload=True) mastersconfig_tbl = sa.Table('mastersconfig', metadata, autoload=True) object_state_tbl = sa.Table('object_state', metadata, autoload=True) users_info_tbl = sa.Table('users_info', metadata, autoload=True) patches_tbl = sa.Table('patches', metadata, autoload=True) sourcestamps_tbl = sa.Table('sourcestamps', metadata, autoload=True) sourcestamp_changes_tbl = sa.Table('sourcestamp_changes', metadata, autoload=True) scheduler_changes_tbl = sa.Table('scheduler_changes', metadata, autoload=True) # add missing FK constraints # buildrequests table cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.mergebrid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.startbrid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [buildrequests_tbl.c.triggeredbybrid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.artifactbrid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.buildsetid], [buildsets_tbl.c.id]) tryDropConst(cons) cons.create() # buildsets table cons = constraint.ForeignKeyConstraint([buildsets_tbl.c.sourcestampsetid], [sourcestampsets_tbl.c.id]) tryDropConst(cons) cons.create() # buildrequest_claims table cons = constraint.ForeignKeyConstraint([buildrequest_claims_tbl.c.brid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [buildrequest_claims_tbl.c.objectid], [objects_tbl.c.id]) tryDropConst(cons) cons.create() # builds table cons = constraint.ForeignKeyConstraint([builds_tbl.c.brid], [buildrequests_tbl.c.id]) tryDropConst(cons) cons.create() # buildset_properties table cons = constraint.ForeignKeyConstraint( [buildset_properties_tbl.c.buildsetid], [buildsets_tbl.c.id]) tryDropConst(cons) cons.create() # change_files table cons = constraint.ForeignKeyConstraint([change_files_tbl.c.changeid], [changes_tbl.c.changeid]) tryDropConst(cons) cons.create() # change_properties table cons = constraint.ForeignKeyConstraint([change_properties_tbl.c.changeid], [changes_tbl.c.changeid]) tryDropConst(cons) cons.create() # change_users table cons = constraint.ForeignKeyConstraint([change_users_tbl.c.changeid], [changes_tbl.c.changeid]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([change_users_tbl.c.uid], [users_tbl.c.uid]) tryDropConst(cons) cons.create() # mastersconfig table cons = constraint.ForeignKeyConstraint([mastersconfig_tbl.c.objectid], [objects_tbl.c.id]) tryDropConst(cons) cons.create() # object_state table cons = constraint.ForeignKeyConstraint([object_state_tbl.c.objectid], [objects_tbl.c.id]) tryDropConst(cons) cons.create() # users_info table cons = constraint.ForeignKeyConstraint([users_info_tbl.c.uid], [users_tbl.c.uid]) tryDropConst(cons) cons.create() # sourcestamps table cons = constraint.ForeignKeyConstraint([sourcestamps_tbl.c.patchid], [patches_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [sourcestamps_tbl.c.sourcestampsetid], [sourcestampsets_tbl.c.id]) tryDropConst(cons) cons.create() # sourcestamp_changes table cons = constraint.ForeignKeyConstraint( [sourcestamp_changes_tbl.c.sourcestampid], [sourcestamps_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [sourcestamp_changes_tbl.c.changeid], [changes_tbl.c.changeid]) tryDropConst(cons) cons.create() # scheduler_changes table cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.objectid], [objects_tbl.c.id]) tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.changeid], [changes_tbl.c.changeid]) tryDropConst(cons) cons.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine # autoload the tables that are only referenced here changes = sa.Table('changes', metadata, autoload=True) objects = sa.Table('buildsets', metadata, autoload=True) sa.Table("objects", metadata, autoload=True) # drop all tables. Schedulers will re-populate on startup scheduler_changes_tbl = sa.Table( 'scheduler_changes', metadata, sa.Column('schedulerid', sa.Integer), # ... ) scheduler_changes_tbl.drop() metadata.remove(scheduler_changes_tbl) scheduler_upstream_buildsets_tbl = sa.Table( 'scheduler_upstream_buildsets', metadata, sa.Column('buildsetid', sa.Integer), # ... ) scheduler_upstream_buildsets_tbl.drop() metadata.remove(scheduler_upstream_buildsets_tbl) schedulers_tbl = sa.Table( "schedulers", metadata, sa.Column('schedulerid', sa.Integer), # ... ) schedulers_tbl.drop() metadata.remove(schedulers_tbl) # schedulers and scheduler_upstream_buildsets aren't coming back, but # scheduler_changes is -- along with its indexes scheduler_changes_tbl = sa.Table( 'scheduler_changes', metadata, sa.Column('objectid', sa.Integer, sa.ForeignKey('objects.id')), sa.Column('changeid', sa.Integer, sa.ForeignKey('changes.changeid')), sa.Column('important', sa.Integer), ) scheduler_changes_tbl.create() cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.objectid], [objects.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.changeid], [changes.c.changeid]) cons.create() idx = sa.Index('scheduler_changes_objectid', scheduler_changes_tbl.c.objectid) idx.create() idx = sa.Index('scheduler_changes_changeid', scheduler_changes_tbl.c.changeid) idx.create() idx = sa.Index('scheduler_changes_unique', scheduler_changes_tbl.c.objectid, scheduler_changes_tbl.c.changeid, unique=True) idx.create()
def add_constraints(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine schedulers_tbl = sa.Table('schedulers', metadata, autoload=True) changes_tbl = sa.Table('changes', metadata, autoload=True) buildrequests_tbl = sa.Table('buildrequests', metadata, autoload=True) buildsets_tbl = sa.Table('buildsets', metadata, autoload=True) scheduler_changes_tbl = sa.Table('scheduler_changes', metadata, autoload=True) scheduler_upstream_buildsets_tbl = sa.Table('scheduler_upstream_buildsets', metadata, autoload=True) change_files_tbl = sa.Table('change_files', metadata, autoload=True) change_links_tbl = sa.Table('change_links', metadata, autoload=True) change_properties_tbl = sa.Table('change_properties', metadata, autoload=True) sourcestamp_changes_tbl = sa.Table('sourcestamp_changes', metadata, autoload=True) builds_tbl = sa.Table('builds', metadata, autoload=True) buildset_properties_tbl = sa.Table('buildset_properties', metadata, autoload=True) sourcestamps_tbl = sa.Table('sourcestamps', metadata, autoload=True) patches_tbl = sa.Table('patches', metadata, autoload=True) cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.schedulerid], [schedulers_tbl.c.schedulerid]) cons.create() cons = constraint.ForeignKeyConstraint([scheduler_upstream_buildsets_tbl.c.schedulerid], [schedulers_tbl.c.schedulerid]) cons.create() cons = constraint.ForeignKeyConstraint([change_files_tbl.c.changeid], [changes_tbl.c.changeid]) cons.create() cons = constraint.ForeignKeyConstraint([change_links_tbl.c.changeid], [changes_tbl.c.changeid]) cons.create() cons = constraint.ForeignKeyConstraint([change_properties_tbl.c.changeid], [changes_tbl.c.changeid]) cons.create() cons = constraint.ForeignKeyConstraint([sourcestamp_changes_tbl.c.changeid], [changes_tbl.c.changeid]) cons.create() cons = constraint.ForeignKeyConstraint([builds_tbl.c.brid], [buildrequests_tbl.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([buildset_properties_tbl.c.buildsetid], [buildsets_tbl.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.buildsetid], [buildsets_tbl.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([sourcestamps_tbl.c.patchid], [patches_tbl.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([sourcestamp_changes_tbl.c.sourcestampid], [sourcestamps_tbl.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([buildsets_tbl.c.sourcestampid], [sourcestamps_tbl.c.id]) cons.create()
def upgrade(migrate_engine): # this only applies to postgres if migrate_engine.dialect.name != 'mysql': return metadata = sa.MetaData() metadata.bind = migrate_engine buildrequests_tbl = sa.Table('buildrequests', metadata, autoload=True) buildsets_tbl = sa.Table('buildsets', metadata, autoload=True) sourcestampsets_tbl = sa.Table('sourcestampsets', metadata, autoload=True) buildrequest_claims_tbl = sa.Table('buildrequest_claims', metadata, autoload=True) objects_tbl = sa.Table('objects', metadata, autoload=True) builds_tbl = sa.Table('builds', metadata, autoload=True) buildset_properties_tbl = sa.Table('buildset_properties', metadata, autoload=True) change_files_tbl = sa.Table('change_files', metadata, autoload=True) changes_tbl = sa.Table('changes', metadata, autoload=True) change_properties_tbl = sa.Table('change_properties', metadata, autoload=True) change_users_tbl = sa.Table('change_users', metadata, autoload=True) users_tbl = sa.Table('users', metadata, autoload=True) mastersconfig_tbl = sa.Table('mastersconfig', metadata, autoload=True) object_state_tbl = sa.Table('object_state', metadata, autoload=True) users_info_tbl = sa.Table('users_info', metadata, autoload=True) patches_tbl = sa.Table('patches', metadata, autoload=True) sourcestamps_tbl = sa.Table('sourcestamps', metadata, autoload=True) sourcestamp_changes_tbl = sa.Table('sourcestamp_changes', metadata, autoload=True) scheduler_changes_tbl = sa.Table('scheduler_changes', metadata, autoload=True) # add missing FK constraints # buildrequests table cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.mergebrid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.startbrid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [buildrequests_tbl.c.triggeredbybrid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.artifactbrid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([buildrequests_tbl.c.buildsetid], [buildsets_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # buildsets table cons = constraint.ForeignKeyConstraint([buildsets_tbl.c.sourcestampsetid], [sourcestampsets_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # buildrequest_claims table cons = constraint.ForeignKeyConstraint([buildrequest_claims_tbl.c.brid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [buildrequest_claims_tbl.c.objectid], [objects_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # builds table cons = constraint.ForeignKeyConstraint([builds_tbl.c.brid], [buildrequests_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # buildset_properties table cons = constraint.ForeignKeyConstraint( [buildset_properties_tbl.c.buildsetid], [buildsets_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # change_files table cons = constraint.ForeignKeyConstraint([change_files_tbl.c.changeid], [changes_tbl.c.changeid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # change_properties table cons = constraint.ForeignKeyConstraint([change_properties_tbl.c.changeid], [changes_tbl.c.changeid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # change_users table cons = constraint.ForeignKeyConstraint([change_users_tbl.c.changeid], [changes_tbl.c.changeid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([change_users_tbl.c.uid], [users_tbl.c.uid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # mastersconfig table cons = constraint.ForeignKeyConstraint([mastersconfig_tbl.c.objectid], [objects_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # object_state table cons = constraint.ForeignKeyConstraint([object_state_tbl.c.objectid], [objects_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # users_info table cons = constraint.ForeignKeyConstraint([users_info_tbl.c.uid], [users_tbl.c.uid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # sourcestamps table cons = constraint.ForeignKeyConstraint([sourcestamps_tbl.c.patchid], [patches_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [sourcestamps_tbl.c.sourcestampsetid], [sourcestampsets_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # sourcestamp_changes table cons = constraint.ForeignKeyConstraint( [sourcestamp_changes_tbl.c.sourcestampid], [sourcestamps_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint( [sourcestamp_changes_tbl.c.changeid], [changes_tbl.c.changeid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() # scheduler_changes table cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.objectid], [objects_tbl.c.id], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create() cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.changeid], [changes_tbl.c.changeid], onupdate="CASCADE", ondelete="CASCADE") tryDropConst(cons) cons.create()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine # a copy of the buildrequests table, but with the foreign keys stripped buildrequests = sa.Table( 'buildrequests', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('buildsetid', sa.Integer, nullable=False), sa.Column('buildername', sa.String(length=255), nullable=False), sa.Column('priority', sa.Integer, nullable=False, server_default=sa.DefaultClause("0")), sa.Column('claimed_at', sa.Integer, server_default=sa.DefaultClause("0")), sa.Column('claimed_by_name', sa.String(length=256)), sa.Column('claimed_by_incarnation', sa.String(length=256)), sa.Column('complete', sa.Integer, server_default=sa.DefaultClause("0")), sa.Column('results', sa.SmallInteger), sa.Column('submitted_at', sa.Integer, nullable=False), sa.Column('complete_at', sa.Integer), ) # existing objects table, used as a foreign key objects = sa.Table( "objects", metadata, # unique ID for this object sa.Column("id", sa.Integer, primary_key=True), # object's user-given name sa.Column('name', sa.String(128), nullable=False), # object's class name, basically representing a "type" for the state sa.Column('class_name', sa.String(128), nullable=False), # prohibit multiple id's for the same object sa.UniqueConstraint('name', 'class_name', name='object_identity'), ) # and a new buildrequest_claims table buildrequest_claims = sa.Table( 'buildrequest_claims', metadata, sa.Column('brid', sa.Integer, index=True, unique=True), sa.Column('objectid', sa.Integer, index=True, nullable=True), sa.Column('claimed_at', sa.Integer, nullable=False), ) # create the new table buildrequest_claims.create() cons = constraint.ForeignKeyConstraint([buildrequest_claims.c.brid], [buildrequests.c.id]) cons.create() cons = constraint.ForeignKeyConstraint([buildrequest_claims.c.objectid], [objects.c.id]) cons.create() # migrate the claims into that table migrate_claims(migrate_engine, metadata, buildrequests, objects, buildrequest_claims) # and drop the claim-related columns in buildrequests drop_columns(metadata, buildrequests)