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