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)
