def migrate_claims(migrate_engine, metadata, buildrequests, objects, buildrequest_claims): # First, ensure there is an object row for each master new_objects = sa.select( [ sa.null().label('id'), buildrequests.c.claimed_by_name.label("name"), sa.literal_column("'BuildMaster'").label("class_name"), ], whereclause=buildrequests.c.claimed_by_name != None, distinct=True) # this doesn't seem to work without str() -- verified in sqla 0.6.0 - 0.7.1 migrate_engine.execute(str(sautils.InsertFromSelect(objects, new_objects))) # now make a buildrequest_claims row for each claimed build request join = buildrequests.join( objects, (buildrequests.c.claimed_by_name == objects.c.name) # (have to use sa.text because str, below, doesn't work # with placeholders) & (objects.c.class_name == sa.text("'BuildMaster'"))) claims = sa.select([ buildrequests.c.id.label('brid'), objects.c.id.label('objectid'), buildrequests.c.claimed_at, ], from_obj=[join], whereclause=buildrequests.c.claimed_by_name != None) migrate_engine.execute( str(sautils.InsertFromSelect(buildrequest_claims, claims)))
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 _migrate_workers_table_data(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine buildslaves = sautils.Table('buildslaves', metadata, autoload=True) workers = sautils.Table('workers', metadata, autoload=True) c = buildslaves.c q = sa.select([c.id, c.name, c.info]) migrate_engine.execute(str(sautils.InsertFromSelect(workers, q)))
def _migrate_connected_workers_table_data(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine connected_buildslaves = sautils.Table('connected_buildslaves', metadata, autoload=True) connected_workers = sautils.Table('connected_workers', metadata, autoload=True) c = connected_buildslaves.c q = sa.select([c.id, c.masterid, c.buildslaveid.label('workerid')]) migrate_engine.execute(str(sautils.InsertFromSelect(connected_workers, q)))
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine sa.Table('buildrequests', metadata, sa.Column('id', sa.Integer, primary_key=True), # .. ) 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), ) masters = sa.Table("masters", metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.String(128), nullable=False), sa.Column('name_hash', sa.String(40), nullable=False), sa.Column('active', sa.Integer, nullable=False), sa.Column('last_active', sa.Integer, nullable=False), ) buildrequest_claims_old = sa.Table("buildrequest_claims_old", metadata, sa.Column('brid', sa.Integer, index=True), sa.Column('objectid', sa.Integer, index=True, nullable=True), sa.Column('claimed_at', sa.Integer, nullable=False), ) buildrequest_claims = sa.Table('buildrequest_claims', metadata, sa.Column('brid', sa.Integer, sa.ForeignKey('buildrequests.id'), nullable=False), sa.Column('masterid', sa.Integer, sa.ForeignKey('masters.id'), index=True, nullable=True), sa.Column('claimed_at', sa.Integer, nullable=False) ) # create the new table masters.create() # migrate buildrequest_claims.. # # buildrequest claims currently point to master objects in the objects # table, but we want them to point to masters, instead. So, we set up # a mapping from objectid to masterid, then replace the table using # that mapping. # rename buildrequest_claims to buildrequest_claims_old rename_buildrequest_claims(migrate_engine) # insert master rows, and capture the id mapping idmap = {} # objectid : masterid r = migrate_engine.execute(sa.select([objects.c.id, objects.c.name], whereclause=(objects.c.class_name == u'buildbot.master.BuildMaster'))) for row in r.fetchall(): r = migrate_engine.execute(masters.insert(), name=row.name, name_hash=hashlib.sha1(row.name).hexdigest(), active=0, last_active=0) masterid = r.inserted_primary_key[0] idmap[row.id] = masterid # copy data from old to new, using the mapping buildrequest_claims.create() if idmap: case_stmt = sa.cast( sa.case(value=buildrequest_claims_old.c.objectid, whens=idmap, else_=None), sa.Integer).label('masterid') else: case_stmt = sa.text('NULL') buildrequests_with_masterid = sa.select( [buildrequest_claims_old.c.brid, case_stmt, buildrequest_claims_old.c.claimed_at]) migrate_engine.execute(sautils.InsertFromSelect( buildrequest_claims, buildrequests_with_masterid)) # drop the old table buildrequest_claims_old.drop() # add the indices sa.Index('master_name_hashes', masters.c.name_hash, unique=True).create() sa.Index('buildrequest_claims_brids', buildrequest_claims.c.brid, unique=True).create()