def _update_sqlite_namespace_id_name_constraint(metadef, metadef_namespaces,
new_constraint_name,
new_fk_name):
migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name).drop()
migrate.UniqueConstraint(metadef.c.namespace_id,
metadef.c.name,
name=new_constraint_name).create()
migrate.ForeignKeyConstraint([metadef.c.namespace_id],
[metadef_namespaces.c.id],
name=new_fk_name).create()
def _downgrade_sqlite_namespace_id_name_constraint(metadef, metadef_namespaces,
constraint_name, fk_name):
migrate.UniqueConstraint(metadef.c.namespace_id,
metadef.c.name,
name=constraint_name).drop()
migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name).create()
migrate.ForeignKeyConstraint([metadef.c.namespace_id],
[metadef_namespaces.c.id],
name=fk_name).drop()
migrate.ForeignKeyConstraint([metadef.c.namespace_id],
[metadef_namespaces.c.id]).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
nonlocal_user = sql.Table('nonlocal_user', meta, autoload=True)
migrate.UniqueConstraint(nonlocal_user.c.user_id,
name='ixu_nonlocal_user_user_id').create()
def _add_unique_constraint_to_role_name(self,
constraint_name='ixu_role_name'):
meta = sqlalchemy.MetaData()
meta.bind = self.engine
role_table = sqlalchemy.Table('role', meta, autoload=True)
migrate.UniqueConstraint(role_table.c.name,
name=constraint_name).create()
def _drop_unique_constraint_if_exists(inspector, table_name, metadef):
name = _get_unique_constraint_name(inspector, table_name,
['namespace_id', 'name'])
if name:
migrate.UniqueConstraint(metadef.c.namespace_id,
metadef.c.name,
name=name).drop()
def _alter_sourceassoc(meta, t_name, ix_name, post_action=False):
if meta.bind.engine.name == 'sqlite':
return
sourceassoc = sa.Table('sourceassoc', meta, autoload=True)
table = sa.Table(t_name, meta, autoload=True)
user = sa.Table('user', meta, autoload=True)
c_name = '%s_id' % t_name
col = getattr(sourceassoc.c, c_name)
uniq_name = 'uniq_sourceassoc0%s0user_id' % c_name
uniq_cols = (c_name, 'user_id')
param = {'columns': [col], 'refcolumns': [table.c.id]}
user_param = {
'columns': [sourceassoc.c.user_id],
'refcolumns': [user.c.id]
}
if meta.bind.engine.name == 'mysql':
param['name'] = 'fk_sourceassoc_%s' % c_name
user_param['name'] = 'fk_sourceassoc_user_id'
actions = [
migrate.ForeignKeyConstraint(**user_param),
migrate.ForeignKeyConstraint(**param),
sa.Index(ix_name, sourceassoc.c.source_id, col),
migrate.UniqueConstraint(*uniq_cols, table=sourceassoc, name=uniq_name)
]
for action in actions:
action.create() if post_action else action.drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
role_table = sql.Table(_ROLE_TABLE_NAME, meta, autoload=True)
# NOTE(morganfainberg): the `role_name` unique constraint is not
# guaranteed to be named 'ixu_role_name', so we need to search for the
# correct constraint that only affects role_table.c.name and drop
# that constraint.
#
# This is an idempotent change that reflects the fix to migration
# 88 if the role_name unique constraint was not named consistently and
# someone manually fixed the migrations / db without dropping the
# old constraint.
# This is a copy of migration 96 to catch any/all deployments that
# are close to master. migration 96 will be backported to
# stable/mitaka.
to_drop = None
if migrate_engine.name == 'mysql':
for c in role_table.indexes:
if (c.unique and len(c.columns) == 1
and _ROLE_NAME_COLUMN_NAME in c.columns):
to_drop = c
break
else:
for c in role_table.constraints:
if len(c.columns) == 1 and _ROLE_NAME_COLUMN_NAME in c.columns:
to_drop = c
break
if to_drop is not None:
migrate.UniqueConstraint(role_table.c.name, name=to_drop.name).drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
# For Mysql and PostgreSQL, drop the FK in limit table, drop the unique
# constraint in registered limit and limit tables.
#
# For SQLite, drop the old tables, then rename the new tables.
limit_table = sql.Table('limit', meta, autoload=True)
registered_limit_table = sql.Table('registered_limit', meta, autoload=True)
if migrate_engine.name != 'sqlite':
project_table = sql.Table('project', meta, autoload=True)
inspector = sql.inspect(migrate_engine)
for fk in inspector.get_foreign_keys('limit'):
fkey = migrate.ForeignKeyConstraint([limit_table.c.project_id],
[project_table.c.id],
name=fk['name'])
fkey.drop()
for uc in inspector.get_unique_constraints('limit'):
if set(uc['column_names']) == set(
['project_id', 'service_id', 'region_id', 'resource_name']):
uc = migrate.UniqueConstraint(limit_table.c.project_id,
limit_table.c.service_id,
limit_table.c.region_id,
limit_table.c.resource_name,
name=uc['name'])
uc.drop()
for uc in inspector.get_unique_constraints('registered_limit'):
if set(uc['column_names']) == set(
['service_id', 'region_id', 'resource_name']):
uc = migrate.UniqueConstraint(
registered_limit_table.c.service_id,
registered_limit_table.c.region_id,
registered_limit_table.c.resource_name,
name=uc['name'])
uc.drop()
else:
registered_limit_table_new = sql.Table('registered_limit_new',
meta,
autoload=True)
limit_table_new = sql.Table('limit_new', meta, autoload=True)
limit_table.drop()
limit_table_new.rename('limit')
registered_limit_table.drop()
registered_limit_table_new.rename('registered_limit')
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
role_table = sql.Table(_ROLE_TABLE_NAME, meta, autoload=True)
domain_id = sql.Column(_DOMAIN_ID_COLUMN_NAME,
sql.String(64),
nullable=False,
server_default=_NULL_DOMAIN_ID)
role_table.create_column(domain_id)
migrate.UniqueConstraint(role_table.c.name,
name=_ROLE_NAME_OLD_CONSTRAINT).drop()
migrate.UniqueConstraint(role_table.c.name,
role_table.c.domain_id,
name=_ROLE_NAME_NEW_CONSTRAINT).create()
def change_uniq(meta, downgrade=False):
uniq_name = 'uniq_sourceassoc0meter_id0user_id'
columns = ('meter_id', 'user_id')
if meta.bind.engine.name == 'sqlite':
return
sourceassoc = sa.Table('sourceassoc', meta, autoload=True)
meter = sa.Table('meter', meta, autoload=True)
user = sa.Table('user', meta, autoload=True)
if meta.bind.engine.name == 'mysql':
# For mysql dialect all dependent FK should be removed
# before renaming of constraint.
params = {
'columns': [sourceassoc.c.meter_id],
'refcolumns': [meter.c.id],
'name': 'fk_sourceassoc_meter_id'
}
migrate.ForeignKeyConstraint(**params).drop()
params = {
'columns': [sourceassoc.c.user_id],
'refcolumns': [user.c.id],
'name': 'fk_sourceassoc_user_id'
}
migrate.ForeignKeyConstraint(**params).drop()
if downgrade:
migrate.UniqueConstraint(*columns, table=sourceassoc,
name=uniq_name).drop()
else:
migrate.UniqueConstraint(*columns, table=sourceassoc,
name=uniq_name).create()
if meta.bind.engine.name == 'mysql':
params = {
'columns': [sourceassoc.c.meter_id],
'refcolumns': [meter.c.id],
'name': 'fk_sourceassoc_meter_id'
}
migrate.ForeignKeyConstraint(**params).create()
params = {
'columns': [sourceassoc.c.user_id],
'refcolumns': [user.c.id],
'name': 'fk_sourceassoc_user_id'
}
migrate.ForeignKeyConstraint(**params).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
cascaded_pods = sql.Table(
'cascaded_pods', meta,
sql.Column('pod_id', sql.String(length=36), primary_key=True),
sql.Column('pod_name', sql.String(length=255), unique=True,
nullable=False),
sql.Column('pod_az_name', sql.String(length=255), nullable=True),
sql.Column('dc_name', sql.String(length=255), nullable=True),
sql.Column('az_name', sql.String(length=255), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
cascaded_pod_service_configuration = sql.Table(
'cascaded_pod_service_configuration', meta,
sql.Column('service_id', sql.String(length=64), primary_key=True),
sql.Column('pod_id', sql.String(length=64), nullable=False),
sql.Column('service_type', sql.String(length=64), nullable=False),
sql.Column('service_url', sql.String(length=512), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
pod_binding = sql.Table(
'pod_binding', meta,
sql.Column('id', sql.String(36), primary_key=True),
sql.Column('tenant_id', sql.String(length=255), nullable=False),
sql.Column('pod_id', sql.String(length=255), nullable=False),
sql.Column('is_binding', sql.Boolean, nullable=False),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'tenant_id', 'pod_id',
name='pod_binding0tenant_id0pod_id'),
mysql_engine='InnoDB',
mysql_charset='utf8')
tables = [cascaded_pods, cascaded_pod_service_configuration,
pod_binding]
for table in tables:
table.create()
fkey = {'columns': [cascaded_pod_service_configuration.c.pod_id],
'references': [cascaded_pods.c.pod_id]}
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
fkey = {'columns': [pod_binding.c.pod_id],
'references': [cascaded_pods.c.pod_id]}
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
instance_types = Table('instance_types', meta, autoload=True)
string_column = Column('flavorid_str', String(255))
string_column.create(instance_types)
try:
# NOTE(bcwaldon): This catches a bug with python-migrate
# failing to add the unique constraint
try:
migrate.UniqueConstraint(string_column).create()
except migrate.changeset.NotSupportedError:
LOG.error("Failed to add unique constraint on flavorid")
pass
# NOTE(bcwaldon): this is a hack to preserve uniqueness constraint
# on existing 'name' column
try:
migrate.UniqueConstraint(instance_types.c.name).create()
except Exception:
pass
integer_column = instance_types.c.flavorid
instance_type_rows = list(instance_types.select().execute())
for instance_type in instance_type_rows:
flavorid_int = instance_type.flavorid
instance_types.update()\
.where(integer_column == flavorid_int)\
.values(flavorid_str=str(flavorid_int))\
.execute()
except Exception:
string_column.drop()
raise
integer_column.alter(name='flavorid_int')
string_column.alter(name='flavorid')
integer_column.drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
user_table = sql.Table('user', meta, autoload=True)
local_user_table = sql.Table('local_user', meta, autoload=True)
password_table = sql.Table('password', meta, autoload=True)
# migrate data to local_user table
local_user_values = []
for row in user_table.select().execute():
# skip the row that already exists in `local_user`, this could
# happen if run into a partially-migrated table due to the
# bug #1549705.
filter_by = local_user_table.c.user_id == row['id']
user_count = sql.select([func.count()]).select_from(
local_user_table).where(filter_by).execute().fetchone()[0]
if user_count == 0:
local_user_values.append({
'user_id': row['id'],
'domain_id': row['domain_id'],
'name': row['name']
})
if local_user_values:
local_user_table.insert().values(local_user_values).execute()
# migrate data to password table
sel = (sql.select([user_table, local_user_table],
use_labels=True).select_from(
user_table.join(
local_user_table,
user_table.c.id == local_user_table.c.user_id)))
user_rows = sel.execute()
password_values = []
for row in user_rows:
if row['user_password']:
password_values.append({
'local_user_id': row['local_user_id'],
'password': row['user_password']
})
if password_values:
password_table.insert().values(password_values).execute()
# remove domain_id and name unique constraint
if migrate_engine.name != 'sqlite':
migrate.UniqueConstraint(user_table.c.domain_id,
user_table.c.name,
name='ixu_user_name_domain_id').drop()
# drop user columns
user_table.c.domain_id.drop()
user_table.c.name.drop()
user_table.c.password.drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
role_table = sql.Table(_ROLE_TABLE_NAME, meta, autoload=True)
domain_id = sql.Column(_DOMAIN_ID_COLUMN_NAME,
sql.String(64),
nullable=False,
server_default=_NULL_DOMAIN_ID)
# NOTE(morganfainberg): the `role_name` unique constraint is not
# guaranteed to be a fixed name, such as 'ixu_role_name`, so we need to
# search for the correct constraint that only affects role_table.c.name
# and drop that constraint.
to_drop = None
if migrate_engine.name == 'mysql':
for c in role_table.indexes:
if (c.unique and len(c.columns) == 1
and _ROLE_NAME_COLUMN_NAME in c.columns):
to_drop = c
break
else:
for c in role_table.constraints:
if len(c.columns) == 1 and _ROLE_NAME_COLUMN_NAME in c.columns:
to_drop = c
break
if to_drop is not None:
migrate.UniqueConstraint(role_table.c.name, name=to_drop.name).drop()
# perform changes after constraint is dropped.
if 'domain_id' not in role_table.columns:
# Only create the column if it doesn't already exist.
role_table.create_column(domain_id)
migrate.UniqueConstraint(role_table.c.name,
role_table.c.domain_id,
name=_ROLE_NAME_NEW_CONSTRAINT).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
user_table = sql.Table(_USER_TABLE_NAME, meta, autoload=True)
# NOTE(gnuoy): the `domain_id` unique constraint is not guaranteed to
# be a fixed name, such as 'ixu_user_name_domain_id`, so we need to
# search for the correct constraint that only affects
# user_table.c.domain_id and drop that constraint. (Fix based on
# morganfainbergs fix in 088_domain_specific_roles.py)
#
# This is an idempotent change that reflects the fix to migration
# 91 if the user name & domain_id unique constraint was not named
# consistently and someone manually fixed the migrations / db
# without dropping the old constraint.
# This is a copy of migration 97 to catch any/all deployments that
# are close to master. migration 97 will be backported to
# stable/mitaka.
to_drop = None
if migrate_engine.name == 'mysql':
for index in user_table.indexes:
if (index.unique and len(index.columns) == 2 and
_USER_DOMAINID_COLUMN_NAME in index.columns and
_USER_NAME_COLUMN_NAME in index.columns):
to_drop = index
break
else:
for index in user_table.constraints:
if (len(index.columns) == 2 and
_USER_DOMAINID_COLUMN_NAME in index.columns and
_USER_NAME_COLUMN_NAME in index.columns):
to_drop = index
break
# remove domain_id and name unique constraint
if to_drop is not None:
migrate.UniqueConstraint(user_table.c.domain_id,
user_table.c.name,
name=to_drop.name).drop()
# If migration 91 was aborted due to Bug #1572341 then columns may not
# have been dropped.
if _USER_DOMAINID_COLUMN_NAME in user_table.c:
user_table.c.domain_id.drop()
if _USER_NAME_COLUMN_NAME in user_table.c:
user_table.c.name.drop()
if _USER_PASSWORD_COLUMN_NAME in user_table.c:
user_table.c.password.drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
access_rule = sql.Table('access_rule', meta, autoload=True)
external_id = sql.Column('external_id', sql.String(64))
access_rule.create_column(external_id)
sql.Index('external_id', access_rule.c.external_id).create()
unique_constraint_id = migrate.UniqueConstraint('external_id',
table=access_rule)
unique_constraint_id.create()
user_id = sql.Column('user_id', sql.String(64))
access_rule.create_column(user_id)
sql.Index('user_id', access_rule.c.user_id).create()
unique_constraint_rule_for_user = migrate.UniqueConstraint(
'user_id',
'service',
'path',
'method',
name='duplicate_access_rule_for_user_constraint',
table=access_rule)
unique_constraint_rule_for_user.create()
def _downgrade_constraint_with_fk(metadef, metadef_namespaces, constraint_name,
fk_curr_name, fk_next_name):
fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id],
[metadef_namespaces.c.id],
name=fk_curr_name)
fkc.drop()
migrate.UniqueConstraint(metadef.c.namespace_id,
metadef.c.name,
name=constraint_name).drop()
fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id],
[metadef_namespaces.c.id],
name=fk_next_name)
fkc.create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
user_table = sql.Table('user', meta, autoload=True)
local_user_table = sql.Table('local_user', meta, autoload=True)
password_table = sql.Table('password', meta, autoload=True)
# migrate data to local_user table
local_user_values = []
for row in user_table.select().execute():
local_user_values.append({
'user_id': row['id'],
'domain_id': row['domain_id'],
'name': row['name']
})
if local_user_values:
local_user_table.insert().values(local_user_values).execute()
# migrate data to password table
sel = (sql.select([user_table, local_user_table],
use_labels=True).select_from(
user_table.join(
local_user_table,
user_table.c.id == local_user_table.c.user_id)))
user_rows = sel.execute()
password_values = []
for row in user_rows:
password_values.append({
'local_user_id': row['local_user_id'],
'password': row['user_password']
})
if password_values:
password_table.insert().values(password_values).execute()
# remove domain_id and name unique constraint
if migrate_engine.name != 'sqlite':
migrate.UniqueConstraint(user_table.c.domain_id,
user_table.c.name,
name='ixu_user_name_domain_id').drop()
# drop user columns
user_table.c.domain_id.drop()
user_table.c.name.drop()
user_table.c.password.drop()
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
instance_types = Table('instance_types', meta, autoload=True)
integer_column = Column('flavorid_int', Integer())
integer_column.create(instance_types)
try:
# NOTE(bcwaldon): This catches a bug with python-migrate
# failing to add the unique constraint
try:
migrate.UniqueConstraint(integer_column).create()
except migrate.changeset.NotSupportedError:
LOG.info("Failed to add unique constraint on flavorid")
pass
string_column = instance_types.c.flavorid
instance_types_rows = list(instance_types.select().execute())
for instance_type in instance_types_rows:
flavorid_str = instance_type.flavorid
try:
flavorid_int = int(instance_type.flavorid)
except ValueError:
msg = _('Could not cast flavorid to integer: %s. '
'Set flavorid to an integer-like string to downgrade.')
LOG.error(msg % instance_type.flavorid)
raise
instance_types.update()\
.where(string_column == flavorid_str)\
.values(flavorid_int=flavorid_int)\
.execute()
except Exception:
integer_column.drop()
raise
string_column.alter(name='flavorid_str')
integer_column.alter(name='flavorid')
string_column.drop()
def index_cleanup(meta, engine_names, table_name, uniq_name, columns, create,
unique, limited):
table = sa.Table(table_name, meta, autoload=True)
if create:
if limited and meta.bind.engine.name == 'mysql':
# For some versions of mysql we can get an error
# "Specified key was too long; max key length is 1000 bytes".
# We should create an index by hand in this case with limited
# length of columns.
columns_mysql = ",".join((c + "(100)" for c in columns))
sql = ("create index %s ON %s (%s)" %
(uniq_name, table, columns_mysql))
meta.bind.engine.execute(sql)
else:
cols = [table.c[col] for col in columns]
sa.Index(uniq_name, *cols, unique=unique).create()
else:
if unique:
migrate.UniqueConstraint(*columns, table=table).drop()
else:
cols = [table.c[col] for col in columns]
sa.Index(uniq_name, *cols, unique=unique).drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
shadow_agents = sql.Table(
'shadow_agents', meta,
sql.Column('id', sql.String(length=36), primary_key=True),
sql.Column('pod_id', sql.String(length=64), nullable=False),
sql.Column('host', sql.String(length=255), nullable=False),
sql.Column('type', sql.String(length=36), nullable=False),
sql.Column('tunnel_ip', sql.String(length=48), nullable=False),
migrate.UniqueConstraint(
'host', 'type',
name='host0type'),
mysql_engine='InnoDB',
mysql_charset='utf8')
shadow_agents.create()
pods = sql.Table('pods', meta, autoload=True)
fkey = {'columns': [shadow_agents.c.pod_id],
'references': [pods.c.pod_id]}
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
resource_routings = sql.Table(
'resource_routings',
meta,
sql.Column('id', sql.BigInteger, primary_key=True),
sql.Column('top_id', sql.String(length=127), nullable=False),
sql.Column('bottom_id', sql.String(length=36)),
sql.Column('pod_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=36)),
sql.Column('resource_type', sql.String(length=64), nullable=False),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'top_id',
'pod_id',
'resource_type',
name='resource_routings0top_id0pod_id0resource_type'),
mysql_engine='InnoDB',
mysql_charset='utf8')
async_jobs = sql.Table(
'async_jobs',
meta,
sql.Column('id', sql.String(length=36), primary_key=True),
sql.Column('type', sql.String(length=36)),
sql.Column('timestamp',
sql.TIMESTAMP,
server_default=sql.text('CURRENT_TIMESTAMP'),
index=True),
sql.Column('status', sql.String(length=36)),
sql.Column('resource_id', sql.String(length=127)),
sql.Column('extra_id', sql.String(length=36)),
migrate.UniqueConstraint(
'type',
'status',
'resource_id',
'extra_id',
name='async_jobs0type0status0resource_id0extra_id'),
mysql_engine='InnoDB',
mysql_charset='utf8')
async_job_logs = sql.Table(
'async_job_logs',
meta,
sql.Column('id', sql.String(length=36), primary_key=True),
sql.Column('resource_id', sql.String(length=127)),
sql.Column('type', sql.String(length=36)),
sql.Column('timestamp',
sql.TIMESTAMP,
server_default=sql.text('CURRENT_TIMESTAMP'),
index=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
tables = [async_jobs, resource_routings, async_job_logs]
for table in tables:
table.create()
pods = sql.Table('pods', meta, autoload=True)
fkeys = [{
'columns': [resource_routings.c.pod_id],
'references': [pods.c.pod_id]
}]
for fkey in fkeys:
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
inspector = sql.inspect(migrate_engine)
user = sql.Table('user', meta, autoload=True)
local_user = sql.Table('local_user', meta, autoload=True)
nonlocal_user = sql.Table('nonlocal_user', meta, autoload=True)
# drop previous fk constraints
fk_name = _get_fk_name(inspector, 'local_user', 'user_id')
if fk_name:
migrate.ForeignKeyConstraint(columns=[local_user.c.user_id],
refcolumns=[user.c.id],
name=fk_name).drop()
fk_name = _get_fk_name(inspector, 'nonlocal_user', 'user_id')
if fk_name:
migrate.ForeignKeyConstraint(columns=[nonlocal_user.c.user_id],
refcolumns=[user.c.id],
name=fk_name).drop()
# create user unique constraint needed for the new composite fk constraint
migrate.UniqueConstraint(user.c.id,
user.c.domain_id,
name='ixu_user_id_domain_id').create()
# create new composite fk constraints
migrate.ForeignKeyConstraint(
columns=[local_user.c.user_id, local_user.c.domain_id],
refcolumns=[user.c.id, user.c.domain_id],
onupdate='CASCADE',
ondelete='CASCADE').create()
migrate.ForeignKeyConstraint(
columns=[nonlocal_user.c.user_id, nonlocal_user.c.domain_id],
refcolumns=[user.c.id, user.c.domain_id],
onupdate='CASCADE',
ondelete='CASCADE').create()
# drop triggers
if upgrades.USE_TRIGGERS:
if migrate_engine.name == 'postgresql':
drop_local_user_insert_trigger = (
'DROP TRIGGER local_user_after_insert_trigger on local_user;')
drop_local_user_update_trigger = (
'DROP TRIGGER local_user_after_update_trigger on local_user;')
drop_nonlocal_user_insert_trigger = (
'DROP TRIGGER nonlocal_user_after_insert_trigger '
'on nonlocal_user;')
drop_nonlocal_user_update_trigger = (
'DROP TRIGGER nonlocal_user_after_update_trigger '
'on nonlocal_user;')
elif migrate_engine.name == 'mysql':
drop_local_user_insert_trigger = (
'DROP TRIGGER local_user_after_insert_trigger;')
drop_local_user_update_trigger = (
'DROP TRIGGER local_user_after_update_trigger;')
drop_nonlocal_user_insert_trigger = (
'DROP TRIGGER nonlocal_user_after_insert_trigger;')
drop_nonlocal_user_update_trigger = (
'DROP TRIGGER nonlocal_user_after_update_trigger;')
else:
drop_local_user_insert_trigger = (
'DROP TRIGGER IF EXISTS local_user_after_insert_trigger;')
drop_local_user_update_trigger = (
'DROP TRIGGER IF EXISTS local_user_after_update_trigger;')
drop_nonlocal_user_insert_trigger = (
'DROP TRIGGER IF EXISTS nonlocal_user_after_insert_trigger;')
drop_nonlocal_user_update_trigger = (
'DROP TRIGGER IF EXISTS nonlocal_user_after_update_trigger;')
migrate_engine.execute(drop_local_user_insert_trigger)
migrate_engine.execute(drop_local_user_update_trigger)
migrate_engine.execute(drop_nonlocal_user_insert_trigger)
migrate_engine.execute(drop_nonlocal_user_update_trigger)
def upgrade(migrate_engine):
# For both registered_limit and limit tables in MySQL and PostgreSQL:
#
# 1. drop the primary key on `id` column.
# 2. create a auto increment `internal_id` column with primary key.
# 3. add unique constraint on `id` column.
#
# But SQLite doesn't support add primary key to a existed table, so for
# SQLite, we'll follow the steps, take the registered_limit as an example:
#
# 1. Add a new table `registered_limit_new` which contains `internal_id`
# column.
# 2. migrate the data from `registered_limit` to `registered_limit_new`
# 3. drop the `registered_limit`, rename `registered_limit_new` to
# `registered_limit`.
meta = sql.MetaData()
meta.bind = migrate_engine
registered_limit_table = sql.Table('registered_limit', meta, autoload=True)
limit_table = sql.Table('limit', meta, autoload=True)
if migrate_engine.name != 'sqlite':
pk = migrate.PrimaryKeyConstraint('id', table=registered_limit_table)
pk.drop()
if migrate_engine.name == 'mysql':
migrate_engine.execute(MYSQL_CREATE_ID_PRIMARY_KEY_COLUMN %
'registered_limit')
else:
migrate_engine.execute(POSTGRESQL_CREATE_ID_PRIMARY_KEY_COLUMN %
'registered_limit')
unique_constraint = migrate.UniqueConstraint(
'id', table=registered_limit_table)
unique_constraint.create()
pk = migrate.PrimaryKeyConstraint('id', table=limit_table)
pk.drop()
if migrate_engine.name == 'mysql':
migrate_engine.execute(MYSQL_CREATE_ID_PRIMARY_KEY_COLUMN %
'limit')
else:
migrate_engine.execute(POSTGRESQL_CREATE_ID_PRIMARY_KEY_COLUMN %
'limit')
unique_constraint = migrate.UniqueConstraint('id', table=limit_table)
unique_constraint.create()
else:
# SQLite case
registered_limit_table_new = sql.Table(
'registered_limit_new',
meta,
sql.Column('internal_id', sql.Integer, primary_key=True),
sql.Column('id', sql.String(length=64), unique=True),
sql.Column('service_id', sql.String(64)),
sql.Column('region_id', sql.String(64), nullable=True),
sql.Column('resource_name', sql.String(255)),
sql.Column('default_limit', sql.Integer, nullable=False),
sql.Column('description', sql.Text),
mysql_engine='InnoDB',
mysql_charset='utf8')
registered_limit_table_new.create(migrate_engine, checkfirst=True)
limit_table_new = sql.Table('limit_new',
meta,
sql.Column('internal_id',
sql.Integer,
primary_key=True),
sql.Column('id',
sql.String(length=64),
unique=True),
sql.Column('project_id', sql.String(64)),
sql.Column('service_id', sql.String(64)),
sql.Column('region_id',
sql.String(64),
nullable=True),
sql.Column('resource_name',
sql.String(255)),
sql.Column('resource_limit',
sql.Integer,
nullable=False),
sql.Column('description', sql.Text),
mysql_engine='InnoDB',
mysql_charset='utf8')
limit_table_new.create(migrate_engine, checkfirst=True)
def upgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
inspector = inspect(migrate_engine)
metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
metadef_properties = Table('metadef_properties', meta, autoload=True)
metadef_objects = Table('metadef_objects', meta, autoload=True)
metadef_ns_res_types = Table('metadef_namespace_resource_types',
meta,
autoload=True)
metadef_resource_types = Table('metadef_resource_types',
meta,
autoload=True)
metadef_tags = Table('metadef_tags', meta, autoload=True)
constraints = [
('ix_namespaces_namespace', [metadef_namespaces.c.namespace]),
('ix_objects_namespace_id_name',
[metadef_objects.c.namespace_id, metadef_objects.c.name]),
('ix_metadef_properties_namespace_id_name',
[metadef_properties.c.namespace_id, metadef_properties.c.name])
]
metadef_tags_constraints = inspector.get_unique_constraints('metadef_tags')
for constraint in metadef_tags_constraints:
if set(constraint['column_names']) == set(['namespace_id', 'name']):
constraints.append(
(constraint['name'],
[metadef_tags.c.namespace_id, metadef_tags.c.name]))
if meta.bind.name == "ibm_db_sa":
# For db2, the following constraints need to be dropped first,
# otherwise the index like ix_metadef_ns_res_types_namespace_id
# will fail to create. These constraints will be added back at
# the end. It should not affect the origional logic for other
# database backends.
for (constraint_name, cols) in constraints:
_change_db2_unique_constraint('drop', constraint_name, *cols)
else:
Index('ix_namespaces_namespace', metadef_namespaces.c.namespace).drop()
Index('ix_objects_namespace_id_name', metadef_objects.c.namespace_id,
metadef_objects.c.name).drop()
Index('ix_metadef_properties_namespace_id_name',
metadef_properties.c.namespace_id,
metadef_properties.c.name).drop()
fkc = migrate.ForeignKeyConstraint([metadef_tags.c.namespace_id],
[metadef_namespaces.c.id])
fkc.create()
# `migrate` module removes unique constraint after adding
# foreign key to the table in sqlite.
# The reason of this issue is that it isn't possible to add fkc to
# existing table in sqlite. Instead of this we should recreate the table
# with needed fkc in the declaration. Migrate package provide us with such
# possibility, but unfortunately it recreates the table without
# constraints. Create unique constraint manually.
if migrate_engine.name == 'sqlite' and len(
inspector.get_unique_constraints('metadef_tags')) == 0:
uc = migrate.UniqueConstraint(metadef_tags.c.namespace_id,
metadef_tags.c.name)
uc.create()
if meta.bind.name != "ibm_db_sa":
Index('ix_tags_namespace_id_name', metadef_tags.c.namespace_id,
metadef_tags.c.name).drop()
Index('ix_metadef_tags_name', metadef_tags.c.name).create()
Index('ix_metadef_tags_namespace_id', metadef_tags.c.namespace_id,
metadef_tags.c.name).create()
if migrate_engine.name == 'mysql':
# We need to drop some foreign keys first because unique constraints
# that we want to delete depend on them. So drop the fk and recreate
# it again after unique constraint deletion.
fkc = migrate.ForeignKeyConstraint([metadef_properties.c.namespace_id],
[metadef_namespaces.c.id],
name='metadef_properties_ibfk_1')
fkc.drop()
constraint = UniqueConstraint(metadef_properties.c.namespace_id,
metadef_properties.c.name,
name='namespace_id')
migrate_engine.execute(DropConstraint(constraint))
fkc.create()
fkc = migrate.ForeignKeyConstraint([metadef_objects.c.namespace_id],
[metadef_namespaces.c.id],
name='metadef_objects_ibfk_1')
fkc.drop()
constraint = UniqueConstraint(metadef_objects.c.namespace_id,
metadef_objects.c.name,
name='namespace_id')
migrate_engine.execute(DropConstraint(constraint))
fkc.create()
constraint = UniqueConstraint(metadef_ns_res_types.c.resource_type_id,
metadef_ns_res_types.c.namespace_id,
name='resource_type_id')
migrate_engine.execute(DropConstraint(constraint))
constraint = UniqueConstraint(metadef_namespaces.c.namespace,
name='namespace')
migrate_engine.execute(DropConstraint(constraint))
constraint = UniqueConstraint(metadef_resource_types.c.name,
name='name')
migrate_engine.execute(DropConstraint(constraint))
if migrate_engine.name == 'postgresql':
met_obj_index_name = (
inspector.get_unique_constraints('metadef_objects')[0]['name'])
constraint = UniqueConstraint(metadef_objects.c.namespace_id,
metadef_objects.c.name,
name=met_obj_index_name)
migrate_engine.execute(DropConstraint(constraint))
met_prop_index_name = (
inspector.get_unique_constraints('metadef_properties')[0]['name'])
constraint = UniqueConstraint(metadef_properties.c.namespace_id,
metadef_properties.c.name,
name=met_prop_index_name)
migrate_engine.execute(DropConstraint(constraint))
metadef_namespaces_name = (
inspector.get_unique_constraints('metadef_namespaces')[0]['name'])
constraint = UniqueConstraint(metadef_namespaces.c.namespace,
name=metadef_namespaces_name)
migrate_engine.execute(DropConstraint(constraint))
metadef_resource_types_name = (inspector.get_unique_constraints(
'metadef_resource_types')[0]['name'])
constraint = UniqueConstraint(metadef_resource_types.c.name,
name=metadef_resource_types_name)
migrate_engine.execute(DropConstraint(constraint))
constraint = UniqueConstraint(
metadef_tags.c.namespace_id,
metadef_tags.c.name,
name='metadef_tags_namespace_id_name_key')
migrate_engine.execute(DropConstraint(constraint))
Index('ix_metadef_ns_res_types_namespace_id',
metadef_ns_res_types.c.namespace_id).create()
Index('ix_metadef_namespaces_namespace',
metadef_namespaces.c.namespace).create()
Index('ix_metadef_namespaces_owner', metadef_namespaces.c.owner).create()
Index('ix_metadef_objects_name', metadef_objects.c.name).create()
Index('ix_metadef_objects_namespace_id',
metadef_objects.c.namespace_id).create()
Index('ix_metadef_properties_name', metadef_properties.c.name).create()
Index('ix_metadef_properties_namespace_id',
metadef_properties.c.namespace_id).create()
if meta.bind.name == "ibm_db_sa":
# For db2, add these constraints back. It should not affect the
# origional logic for other database backends.
for (constraint_name, cols) in constraints:
_change_db2_unique_constraint('create', constraint_name, *cols)
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
if migrate_engine.name == 'mysql':
# In Folsom we explicitly converted migrate_version to UTF8.
migrate_engine.execute(
'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8')
# Set default DB charset to UTF8.
migrate_engine.execute('ALTER DATABASE %s DEFAULT CHARACTER SET utf8' %
migrate_engine.url.database)
credential = sql.Table('credential',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('user_id',
sql.String(length=64),
nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('type',
sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
domain = sql.Table('domain',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('name',
sql.String(length=64),
nullable=False),
sql.Column('enabled',
sql.Boolean,
default=True,
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
endpoint = sql.Table('endpoint',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('legacy_endpoint_id',
sql.String(length=64)),
sql.Column('interface',
sql.String(length=8),
nullable=False),
sql.Column('region', sql.String(length=255)),
sql.Column('service_id',
sql.String(length=64),
nullable=False),
sql.Column('url', sql.Text, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group = sql.Table('group',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
sql.Column('name', sql.String(length=64),
nullable=False),
sql.Column('description', sql.Text),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group_domain_metadata = sql.Table('group_domain_metadata',
meta,
sql.Column('group_id',
sql.String(length=64),
primary_key=True),
sql.Column('domain_id',
sql.String(length=64),
primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group_project_metadata = sql.Table('group_project_metadata',
meta,
sql.Column('group_id',
sql.String(length=64),
primary_key=True),
sql.Column('project_id',
sql.String(length=64),
primary_key=True),
sql.Column('data',
ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
policy = sql.Table('policy',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('type',
sql.String(length=255),
nullable=False),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
project = sql.Table('project',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('name',
sql.String(length=64),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('description', sql.Text),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
role = sql.Table('role',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
service = sql.Table('service',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('type', sql.String(length=255)),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
token = sql.Table('token',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('expires', sql.DateTime, default=None),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('valid',
sql.Boolean,
default=True,
nullable=False),
sql.Column('trust_id', sql.String(length=64)),
sql.Column('user_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust = sql.Table('trust',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('trustor_user_id',
sql.String(length=64),
nullable=False),
sql.Column('trustee_user_id',
sql.String(length=64),
nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('impersonation', sql.Boolean, nullable=False),
sql.Column('deleted_at', sql.DateTime),
sql.Column('expires_at', sql.DateTime),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust_role = sql.Table('trust_role',
meta,
sql.Column('trust_id',
sql.String(length=64),
primary_key=True,
nullable=False),
sql.Column('role_id',
sql.String(length=64),
primary_key=True,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
user = sql.Table('user',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('password', sql.String(length=128)),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
sql.Column('default_project_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_domain_metadata = sql.Table('user_domain_metadata',
meta,
sql.Column('user_id',
sql.String(length=64),
primary_key=True),
sql.Column('domain_id',
sql.String(length=64),
primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_group_membership = sql.Table('user_group_membership',
meta,
sql.Column('user_id',
sql.String(length=64),
primary_key=True),
sql.Column('group_id',
sql.String(length=64),
primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_project_metadata = sql.Table('user_project_metadata',
meta,
sql.Column('user_id',
sql.String(length=64),
primary_key=True),
sql.Column('project_id',
sql.String(length=64),
primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
# create all tables
tables = [
credential, domain, endpoint, group, group_domain_metadata,
group_project_metadata, policy, project, role, service, token, trust,
trust_role, user, user_domain_metadata, user_group_membership,
user_project_metadata
]
for table in tables:
try:
table.create()
except Exception:
LOG.exception('Exception while creating table: %r', table)
raise
# Unique Constraints
migrate.UniqueConstraint(user.c.domain_id,
user.c.name,
name='ixu_user_name_domain_id').create()
migrate.UniqueConstraint(group.c.domain_id,
group.c.name,
name='ixu_group_name_domain_id').create()
migrate.UniqueConstraint(role.c.name, name='ixu_role_name').create()
migrate.UniqueConstraint(project.c.domain_id,
project.c.name,
name='ixu_project_name_domain_id').create()
migrate.UniqueConstraint(domain.c.name, name='ixu_domain_name').create()
# Indexes
sql.Index('ix_token_expires', token.c.expires).create()
sql.Index('ix_token_valid', token.c.valid).create()
fkeys = [{
'columns': [user_project_metadata.c.project_id],
'references': [project.c.id],
'name': 'fk_user_project_metadata_project_id'
}, {
'columns': [user_domain_metadata.c.domain_id],
'references': [domain.c.id],
'name': 'fk_user_domain_metadata_domain_id'
}, {
'columns': [group_project_metadata.c.project_id],
'references': [project.c.id],
'name': 'fk_group_project_metadata_project_id'
}, {
'columns': [group_domain_metadata.c.domain_id],
'references': [domain.c.id],
'name': 'fk_group_domain_metadata_domain_id'
}, {
'columns': [endpoint.c.service_id],
'references': [service.c.id]
}, {
'columns': [user_group_membership.c.group_id],
'references': [group.c.id],
'name': 'fk_user_group_membership_group_id'
}, {
'columns': [user_group_membership.c.user_id],
'references': [user.c.id],
'name': 'fk_user_group_membership_user_id'
}, {
'columns': [user.c.domain_id],
'references': [domain.c.id],
'name': 'fk_user_domain_id'
}, {
'columns': [group.c.domain_id],
'references': [domain.c.id],
'name': 'fk_group_domain_id'
}, {
'columns': [project.c.domain_id],
'references': [domain.c.id],
'name': 'fk_project_domain_id'
}]
for fkey in fkeys:
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
# Create the default domain.
session = orm.sessionmaker(bind=migrate_engine)()
domain.insert(migration_helpers.get_default_domain()).execute()
session.commit()
def _drop_unique_constraint_to_role_name(self,
constraint_name='ixu_role_name'):
role_table = sqlalchemy.Table('role', self.metadata, autoload=True)
migrate.UniqueConstraint(role_table.c.name,
name=constraint_name).drop()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
if migrate_engine.name == 'mysql':
# In Folsom we explicitly converted migrate_version to UTF8.
migrate_engine.execute(
'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8')
# Set default DB charset to UTF8.
migrate_engine.execute('ALTER DATABASE %s DEFAULT CHARACTER SET utf8' %
migrate_engine.url.database)
credential = sql.Table('credential',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('user_id',
sql.String(length=64),
nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('type',
sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
domain = sql.Table('domain',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('name',
sql.String(length=64),
nullable=False),
sql.Column('enabled',
sql.Boolean,
default=True,
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
endpoint = sql.Table('endpoint',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('legacy_endpoint_id',
sql.String(length=64)),
sql.Column('interface',
sql.String(length=8),
nullable=False),
sql.Column('service_id',
sql.String(length=64),
nullable=False),
sql.Column('url', sql.Text, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('enabled',
sql.Boolean,
nullable=False,
default=True,
server_default='1'),
sql.Column('region_id',
sql.String(length=255),
nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
group = sql.Table('group',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
sql.Column('name', sql.String(length=64),
nullable=False),
sql.Column('description', sql.Text),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
policy = sql.Table('policy',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('type',
sql.String(length=255),
nullable=False),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
project = sql.Table('project',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('name',
sql.String(length=64),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('description', sql.Text),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
sql.Column('parent_id', sql.String(64), nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
role = sql.Table('role',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
service = sql.Table('service',
meta,
sql.Column('id',
sql.String(length=64),
primary_key=True),
sql.Column('type', sql.String(length=255)),
sql.Column('enabled',
sql.Boolean,
nullable=False,
default=True,
server_default='1'),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
token = sql.Table('token',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('expires', sql.DateTime, default=None),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('valid',
sql.Boolean,
default=True,
nullable=False),
sql.Column('trust_id', sql.String(length=64)),
sql.Column('user_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust = sql.Table('trust',
meta,
sql.Column('id', sql.String(length=64),
primary_key=True),
sql.Column('trustor_user_id',
sql.String(length=64),
nullable=False),
sql.Column('trustee_user_id',
sql.String(length=64),
nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('impersonation', sql.Boolean, nullable=False),
sql.Column('deleted_at', sql.DateTime),
sql.Column('expires_at', sql.DateTime),
sql.Column('remaining_uses', sql.Integer, nullable=True),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust_role = sql.Table('trust_role',
meta,
sql.Column('trust_id',
sql.String(length=64),
primary_key=True,
nullable=False),
sql.Column('role_id',
sql.String(length=64),
primary_key=True,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
user = sql.Table('user',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255),
nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('password', sql.String(length=128)),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id',
sql.String(length=64),
nullable=False),
sql.Column('default_project_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_group_membership = sql.Table('user_group_membership',
meta,
sql.Column('user_id',
sql.String(length=64),
primary_key=True),
sql.Column('group_id',
sql.String(length=64),
primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
region = sql.Table('region',
meta,
sql.Column('id', sql.String(255), primary_key=True),
sql.Column('description',
sql.String(255),
nullable=False),
sql.Column('parent_region_id',
sql.String(255),
nullable=True),
sql.Column('extra', sql.Text()),
mysql_engine='InnoDB',
mysql_charset='utf8')
assignment = sql.Table(
'assignment',
meta,
sql.Column('type',
sql.Enum(assignment_sql.AssignmentType.USER_PROJECT,
assignment_sql.AssignmentType.GROUP_PROJECT,
assignment_sql.AssignmentType.USER_DOMAIN,
assignment_sql.AssignmentType.GROUP_DOMAIN,
name='type'),
nullable=False),
sql.Column('actor_id', sql.String(64), nullable=False),
sql.Column('target_id', sql.String(64), nullable=False),
sql.Column('role_id', sql.String(64), nullable=False),
sql.Column('inherited', sql.Boolean, default=False, nullable=False),
sql.PrimaryKeyConstraint('type', 'actor_id', 'target_id', 'role_id'),
mysql_engine='InnoDB',
mysql_charset='utf8')
mapping = sql.Table('id_mapping',
meta,
sql.Column('public_id',
sql.String(64),
primary_key=True),
sql.Column('domain_id', sql.String(64),
nullable=False),
sql.Column('local_id', sql.String(64), nullable=False),
sql.Column('entity_type',
sql.Enum(mapping_backend.EntityType.USER,
mapping_backend.EntityType.GROUP,
name='entity_type'),
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
domain_config_whitelist = sql.Table('whitelisted_config',
meta,
sql.Column('domain_id',
sql.String(64),
primary_key=True),
sql.Column('group',
sql.String(255),
primary_key=True),
sql.Column('option',
sql.String(255),
primary_key=True),
sql.Column('value',
ks_sql.JsonBlob.impl,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
domain_config_sensitive = sql.Table('sensitive_config',
meta,
sql.Column('domain_id',
sql.String(64),
primary_key=True),
sql.Column('group',
sql.String(255),
primary_key=True),
sql.Column('option',
sql.String(255),
primary_key=True),
sql.Column('value',
ks_sql.JsonBlob.impl,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
# create all tables
tables = [
credential, domain, endpoint, group, policy, project, role, service,
token, trust, trust_role, user, user_group_membership, region,
assignment, mapping, domain_config_whitelist, domain_config_sensitive
]
for table in tables:
try:
table.create()
except Exception:
LOG.exception('Exception while creating table: %r', table)
raise
# Unique Constraints
migrate.UniqueConstraint(user.c.domain_id,
user.c.name,
name='ixu_user_name_domain_id').create()
migrate.UniqueConstraint(group.c.domain_id,
group.c.name,
name='ixu_group_name_domain_id').create()
migrate.UniqueConstraint(role.c.name, name='ixu_role_name').create()
migrate.UniqueConstraint(project.c.domain_id,
project.c.name,
name='ixu_project_name_domain_id').create()
migrate.UniqueConstraint(domain.c.name, name='ixu_domain_name').create()
migrate.UniqueConstraint(mapping.c.domain_id,
mapping.c.local_id,
mapping.c.entity_type,
name='domain_id').create()
# Indexes
sql.Index('ix_token_expires', token.c.expires).create()
sql.Index('ix_token_expires_valid', token.c.expires,
token.c.valid).create()
sql.Index('ix_actor_id', assignment.c.actor_id).create()
sql.Index('ix_token_user_id', token.c.user_id).create()
sql.Index('ix_token_trust_id', token.c.trust_id).create()
# NOTE(stevemar): The two indexes below were named 'service_id' and
# 'group_id' in 050_fk_consistent_indexes.py, and need to be preserved
sql.Index('service_id', endpoint.c.service_id).create()
sql.Index('group_id', user_group_membership.c.group_id).create()
fkeys = [
{
'columns': [endpoint.c.service_id],
'references': [service.c.id]
},
{
'columns': [user_group_membership.c.group_id],
'references': [group.c.id],
'name': 'fk_user_group_membership_group_id'
},
{
'columns': [user_group_membership.c.user_id],
'references': [user.c.id],
'name': 'fk_user_group_membership_user_id'
},
{
'columns': [project.c.domain_id],
'references': [domain.c.id],
'name': 'fk_project_domain_id'
},
{
'columns': [endpoint.c.region_id],
'references': [region.c.id],
'name': 'fk_endpoint_region_id'
},
{
'columns': [project.c.parent_id],
'references': [project.c.id],
'name': 'project_parent_id_fkey'
},
]
if migrate_engine.name == 'sqlite':
# NOTE(stevemar): We need to keep this FK constraint due to 073, but
# only for sqlite, once we collapse 073 we can remove this constraint
fkeys.append({
'columns': [assignment.c.role_id],
'references': [role.c.id],
'name': 'fk_assignment_role_id'
})
for fkey in fkeys:
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
# Create the default domain.
session = orm.sessionmaker(bind=migrate_engine)()
domain.insert(migration_helpers.get_default_domain()).execute()
session.commit()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
aggregates = sql.Table(
'aggregates', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('name', sql.String(255), unique=True),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8')
aggregate_metadata = sql.Table(
'aggregate_metadata', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('key', sql.String(255), nullable=False),
sql.Column('value', sql.String(255), nullable=False),
sql.Column('aggregate_id', sql.Integer, nullable=False),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'aggregate_id', 'key',
name='uniq_aggregate_metadata0aggregate_id0key'),
mysql_engine='InnoDB',
mysql_charset='utf8')
instance_types = sql.Table(
'instance_types', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('name', sql.String(255), unique=True),
sql.Column('memory_mb', sql.Integer, nullable=False),
sql.Column('vcpus', sql.Integer, nullable=False),
sql.Column('root_gb', sql.Integer),
sql.Column('ephemeral_gb', sql.Integer),
sql.Column('flavorid', sql.String(255), unique=True),
sql.Column('swap', sql.Integer, nullable=False, default=0),
sql.Column('rxtx_factor', sql.Float, default=1),
sql.Column('vcpu_weight', sql.Integer),
sql.Column('disabled', sql.Boolean, default=False),
sql.Column('is_public', sql.Boolean, default=True),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8')
instance_type_projects = sql.Table(
'instance_type_projects', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('instance_type_id', sql.Integer, nullable=False),
sql.Column('project_id', sql.String(255)),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'instance_type_id', 'project_id',
name='uniq_instance_type_projects0instance_type_id0project_id'),
mysql_engine='InnoDB',
mysql_charset='utf8')
instance_type_extra_specs = sql.Table(
'instance_type_extra_specs', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('key', sql.String(255)),
sql.Column('value', sql.String(255)),
sql.Column('instance_type_id', sql.Integer, nullable=False),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'instance_type_id', 'key',
name='uniq_instance_type_extra_specs0instance_type_id0key'),
mysql_engine='InnoDB',
mysql_charset='utf8')
enum = sql.Enum('ssh', 'x509', metadata=meta, name='keypair_types')
enum.create()
key_pairs = sql.Table(
'key_pairs', meta,
sql.Column('id', sql.Integer, primary_key=True, nullable=False),
sql.Column('name', sql.String(255), nullable=False),
sql.Column('user_id', sql.String(255)),
sql.Column('fingerprint', sql.String(255)),
sql.Column('public_key', MediumText()),
sql.Column('type', enum, nullable=False, server_default='ssh'),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
migrate.UniqueConstraint(
'user_id', 'name',
name='uniq_key_pairs0user_id0name'),
mysql_engine='InnoDB',
mysql_charset='utf8')
quotas = sql.Table(
'quotas', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('project_id', sql.String(255), index=True),
sql.Column('resource', sql.String(255), nullable=False),
sql.Column('hard_limit', sql.Integer),
sql.Column('allocated', sql.Integer, default=0),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
sql.Column('deleted_at', sql.DateTime),
sql.Column('deleted', sql.Integer),
mysql_engine='InnoDB',
mysql_charset='utf8')
quota_classes = sql.Table(
'quota_classes', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('class_name', sql.String(255), index=True),
sql.Column('resource', sql.String(255), nullable=False),
sql.Column('hard_limit', sql.Integer),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
sql.Column('deleted_at', sql.DateTime),
sql.Column('deleted', sql.Integer),
mysql_engine='InnoDB',
mysql_charset='utf8')
quota_usages = sql.Table(
'quota_usages', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('project_id', sql.String(255), index=True),
sql.Column('user_id', sql.String(255), index=True),
sql.Column('resource', sql.String(255), nullable=False),
sql.Column('in_use', sql.Integer),
sql.Column('reserved', sql.Integer),
sql.Column('until_refresh', sql.Integer),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
sql.Column('deleted_at', sql.DateTime),
sql.Column('deleted', sql.Integer),
mysql_engine='InnoDB',
mysql_charset='utf8')
reservations = sql.Table(
'reservations', meta,
sql.Column('id', sql.Integer(), primary_key=True),
sql.Column('uuid', sql.String(length=36), nullable=False),
sql.Column('usage_id', sql.Integer(),
sql.ForeignKey('quota_usages.id'),
nullable=False),
sql.Column('project_id',
sql.String(length=255),
index=True),
sql.Column('resource',
sql.String(length=255)),
sql.Column('delta', sql.Integer(), nullable=False),
sql.Column('expire', sql.DateTime),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
sql.Column('deleted_at', sql.DateTime),
sql.Column('deleted', sql.Boolean(create_constraint=True,
name=None)),
mysql_engine='InnoDB',
mysql_charset='utf8')
volume_types = sql.Table(
'volume_types', meta,
sql.Column('id', sql.String(36), primary_key=True),
sql.Column('name', sql.String(255), unique=True),
sql.Column('description', sql.String(255)),
sql.Column('qos_specs_id', sql.String(36)),
sql.Column('is_public', sql.Boolean, default=True),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8')
quality_of_service_specs = sql.Table(
'quality_of_service_specs', meta,
sql.Column('id', sql.String(36), primary_key=True),
sql.Column('specs_id', sql.String(36)),
sql.Column('key', sql.String(255)),
sql.Column('value', sql.String(255)),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8')
cascaded_pods_resource_routing = sql.Table(
'cascaded_pods_resource_routing', meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('top_id', sql.String(length=127), nullable=False),
sql.Column('bottom_id', sql.String(length=36)),
sql.Column('pod_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=36)),
sql.Column('resource_type', sql.String(length=64), nullable=False),
sql.Column('created_at', sql.DateTime),
sql.Column('updated_at', sql.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8')
job = sql.Table(
'job', meta,
sql.Column('id', sql.String(length=36), primary_key=True),
sql.Column('type', sql.String(length=36)),
sql.Column('timestamp', sql.TIMESTAMP,
server_default=sql.text('CURRENT_TIMESTAMP')),
sql.Column('status', sql.String(length=36)),
sql.Column('resource_id', sql.String(length=36)),
sql.Column('extra_id', sql.String(length=36)),
migrate.UniqueConstraint(
'type', 'status', 'resource_id', 'extra_id',
name='job0type0status0resource_id0extra_id'),
mysql_engine='InnoDB',
mysql_charset='utf8')
tables = [aggregates, aggregate_metadata, instance_types,
instance_type_projects, instance_type_extra_specs, key_pairs,
quotas, quota_classes, quota_usages, reservations,
volume_types, job,
quality_of_service_specs, cascaded_pods_resource_routing]
for table in tables:
table.create()
cascaded_pods = sql.Table('cascaded_pods', meta, autoload=True)
fkeys = [{'columns': [instance_type_projects.c.instance_type_id],
'references': [instance_types.c.id]},
{'columns': [instance_type_extra_specs.c.instance_type_id],
'references': [instance_types.c.id]},
{'columns': [reservations.c.usage_id],
'references': [quota_usages.c.id]},
{'columns': [volume_types.c.qos_specs_id],
'references': [quality_of_service_specs.c.id]},
{'columns': [quality_of_service_specs.c.specs_id],
'references': [quality_of_service_specs.c.id]},
{'columns': [aggregate_metadata.c.aggregate_id],
'references': [aggregates.c.id]},
{'columns': [cascaded_pods_resource_routing.c.pod_id],
'references': [cascaded_pods.c.pod_id]}]
for fkey in fkeys:
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
if migrate_engine.name == 'mysql':
# In Folsom we explicitly converted migrate_version to UTF8.
migrate_engine.execute(
'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8')
# Set default DB charset to UTF8.
migrate_engine.execute('ALTER DATABASE %s DEFAULT CHARACTER SET utf8' %
migrate_engine.url.database)
application_credential = sql.Table(
'application_credential',
meta,
sql.Column('internal_id',
sql.Integer,
primary_key=True,
nullable=False),
sql.Column('id', sql.String(length=64), nullable=False),
sql.Column('name', sql.String(length=255), nullable=False),
sql.Column('secret_hash', sql.String(length=255), nullable=False),
sql.Column('description', sql.Text),
sql.Column('user_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(64), nullable=True),
sql.Column('expires_at', ks_sql.DateTimeInt()),
sql.Column('system', sql.String(64), nullable=True),
sql.Column('unrestricted', sql.Boolean),
sql.UniqueConstraint('user_id',
'name',
name='duplicate_app_cred_constraint'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
assignment = sql.Table(
'assignment',
meta,
sql.Column(
'type',
sql.Enum(
assignment_sql.AssignmentType.USER_PROJECT,
assignment_sql.AssignmentType.GROUP_PROJECT,
assignment_sql.AssignmentType.USER_DOMAIN,
assignment_sql.AssignmentType.GROUP_DOMAIN,
name='type',
),
nullable=False,
),
sql.Column('actor_id', sql.String(64), nullable=False),
sql.Column('target_id', sql.String(64), nullable=False),
sql.Column('role_id', sql.String(64), nullable=False),
sql.Column('inherited', sql.Boolean, default=False, nullable=False),
sql.PrimaryKeyConstraint(
'type',
'actor_id',
'target_id',
'role_id',
'inherited',
),
sql.Index('ix_actor_id', 'actor_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
access_rule = sql.Table(
'access_rule',
meta,
sql.Column('id', sql.Integer, primary_key=True, nullable=False),
sql.Column('service', sql.String(64)),
sql.Column('path', sql.String(128)),
sql.Column('method', sql.String(16)),
sql.Column('external_id', sql.String(64)),
sql.Column('user_id', sql.String(64)),
sql.UniqueConstraint(
'external_id',
name='access_rule_external_id_key',
),
sql.UniqueConstraint(
'user_id',
'service',
'path',
'method',
name='duplicate_access_rule_for_user_constraint',
),
sql.Index('user_id', 'user_id'),
sql.Index('external_id', 'external_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
config_register = sql.Table(
'config_register',
meta,
sql.Column('type', sql.String(64), primary_key=True),
sql.Column('domain_id', sql.String(64), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
consumer = sql.Table(
'consumer',
meta,
sql.Column('id', sql.String(64), primary_key=True, nullable=False),
sql.Column('description', sql.String(64), nullable=True),
sql.Column('secret', sql.String(64), nullable=False),
sql.Column('extra', sql.Text(), nullable=False),
)
credential = sql.Table(
'credential',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('user_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('type', sql.String(length=255), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('key_hash', sql.String(64), nullable=False),
sql.Column(
'encrypted_blob',
ks_sql.Text,
nullable=False,
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
group = sql.Table(
'group',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('domain_id', sql.String(length=64), nullable=False),
sql.Column('name', sql.String(length=64), nullable=False),
sql.Column('description', sql.Text),
sql.Column('extra', ks_sql.JsonBlob.impl),
migrate.UniqueConstraint(
'domain_id',
'name',
name='ixu_group_name_domain_id',
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
id_mapping = sql.Table(
'id_mapping',
meta,
sql.Column('public_id', sql.String(64), primary_key=True),
sql.Column('domain_id', sql.String(64), nullable=False),
sql.Column('local_id', sql.String(64), nullable=False),
sql.Column(
'entity_type',
sql.Enum(
mapping_backend.EntityType.USER,
mapping_backend.EntityType.GROUP,
name='entity_type',
),
nullable=False,
),
migrate.UniqueConstraint(
'domain_id',
'local_id',
'entity_type',
name='domain_id',
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
identity_provider = sql.Table(
'identity_provider',
meta,
sql.Column('id', sql.String(64), primary_key=True),
sql.Column('enabled', sql.Boolean, nullable=False),
sql.Column('description', sql.Text(), nullable=True),
sql.Column('domain_id', sql.String(64), nullable=False),
sql.Column('authorization_ttl', sql.Integer, nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
idp_remote_ids = sql.Table(
'idp_remote_ids',
meta,
sql.Column(
'idp_id',
sql.String(64),
sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'),
),
sql.Column('remote_id', sql.String(255), primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
mapping = sql.Table(
'mapping',
meta,
sql.Column('id', sql.String(64), primary_key=True),
sql.Column('rules', sql.Text(), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
policy = sql.Table(
'policy',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('type', sql.String(length=255), nullable=False),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
policy_association = sql.Table(
'policy_association',
meta,
sql.Column('id', sql.String(64), primary_key=True),
sql.Column('policy_id', sql.String(64), nullable=False),
sql.Column('endpoint_id', sql.String(64), nullable=True),
sql.Column('service_id', sql.String(64), nullable=True),
sql.Column('region_id', sql.String(64), nullable=True),
sql.UniqueConstraint('endpoint_id', 'service_id', 'region_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
project = sql.Table(
'project',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=64), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('description', sql.Text),
sql.Column('enabled', sql.Boolean),
sql.Column(
'domain_id',
sql.String(length=64),
sql.ForeignKey(
'project.id',
name='project_domain_id_fkey',
),
nullable=False,
),
sql.Column(
'parent_id',
sql.String(64),
sql.ForeignKey(
'project.id',
name='project_parent_id_fkey',
),
nullable=True,
),
sql.Column(
'is_domain',
sql.Boolean,
nullable=False,
server_default='0',
default=False,
),
migrate.UniqueConstraint(
'domain_id',
'name',
name='ixu_project_name_domain_id',
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
project_endpoint = sql.Table(
'project_endpoint',
meta,
sql.Column('endpoint_id',
sql.String(64),
primary_key=True,
nullable=False),
sql.Column('project_id',
sql.String(64),
primary_key=True,
nullable=False),
)
project_option = sql.Table(
'project_option',
meta,
sql.Column(
'project_id',
sql.String(64),
sql.ForeignKey(project.c.id, ondelete='CASCADE'),
nullable=False,
primary_key=True,
),
sql.Column('option_id',
sql.String(4),
nullable=False,
primary_key=True),
sql.Column('option_value', ks_sql.JsonBlob, nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
# NOTE(lamt) To allow tag name to be case sensitive for MySQL, the 'name'
# column needs to use collation, which is incompatible with Postgresql.
# Using unicode to mirror nova's server tag:
# https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/models.py
project_tag = sql.Table(
'project_tag',
meta,
sql.Column(
'project_id',
sql.String(64),
sql.ForeignKey(project.c.id, ondelete='CASCADE'),
nullable=False,
primary_key=True,
),
sql.Column('name', sql.Unicode(255), nullable=False, primary_key=True),
sql.UniqueConstraint('project_id', 'name'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
region = sql.Table(
'region',
meta,
sql.Column('id', sql.String(255), primary_key=True),
sql.Column('description', sql.String(255), nullable=False),
sql.Column('parent_region_id', sql.String(255), nullable=True),
sql.Column('extra', sql.Text()),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
registered_limit = sql.Table(
'registered_limit',
meta,
sql.Column('id', sql.String(length=64), nullable=False),
sql.Column('service_id', sql.String(255)),
sql.Column('region_id', sql.String(64), nullable=True),
sql.Column('resource_name', sql.String(255)),
sql.Column('default_limit', sql.Integer, nullable=False),
sql.Column('description', sql.Text),
sql.Column('internal_id', sql.Integer, primary_key=True),
# NOTE(stephenfin): Name chosen to preserve backwards compatibility
# with names used for primary key unique constraints
sql.UniqueConstraint('id', name='registered_limit_id_key'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
request_token = sql.Table(
'request_token',
meta,
sql.Column('id', sql.String(64), primary_key=True, nullable=False),
sql.Column('request_secret', sql.String(64), nullable=False),
sql.Column('verifier', sql.String(64), nullable=True),
sql.Column('authorizing_user_id', sql.String(64), nullable=True),
sql.Column('requested_project_id', sql.String(64), nullable=False),
sql.Column('role_ids', sql.Text(), nullable=True),
sql.Column(
'consumer_id',
sql.String(64),
sql.ForeignKey(consumer.c.id),
nullable=False,
index=True,
),
sql.Column('expires_at', sql.String(64), nullable=True),
)
revocation_event = sql.Table(
'revocation_event',
meta,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('domain_id', sql.String(64)),
sql.Column('project_id', sql.String(64)),
sql.Column('user_id', sql.String(64)),
sql.Column('role_id', sql.String(64)),
sql.Column('trust_id', sql.String(64)),
sql.Column('consumer_id', sql.String(64)),
sql.Column('access_token_id', sql.String(64)),
sql.Column('issued_before', sql.DateTime(), nullable=False),
sql.Column('expires_at', sql.DateTime()),
sql.Column('revoked_at', sql.DateTime(), nullable=False),
sql.Column('audit_id', sql.String(32), nullable=True),
sql.Column('audit_chain_id', sql.String(32), nullable=True),
# NOTE(stephenfin): The '_new' suffix here is due to migration 095,
# which changed the 'id' column from String(64) to Integer. It did this
# by creating a 'revocation_event_new' table and populating it with
# data from the 'revocation_event' table before deleting the
# 'revocation_event' table and renaming the 'revocation_event_new'
# table to 'revocation_event'. Because the 'revoked_at' column had
# 'index=True', sqlalchemy automatically generated the index name as
# 'ix_{table}_{column}'. However, when intitially created, '{table}'
# was 'revocation_event_new' so the index got that name. We may wish to
# rename this eventually.
sql.Index('ix_revocation_event_new_revoked_at', 'revoked_at'),
sql.Index('ix_revocation_event_issued_before', 'issued_before'),
sql.Index(
'ix_revocation_event_project_id_issued_before',
'project_id',
'issued_before',
),
sql.Index(
'ix_revocation_event_user_id_issued_before',
'user_id',
'issued_before',
),
sql.Index(
'ix_revocation_event_audit_id_issued_before',
'audit_id',
'issued_before',
),
)
role = sql.Table(
'role',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column(
'domain_id',
sql.String(64),
nullable=False,
server_default='<<null>>',
),
sql.Column('description', sql.String(255), nullable=True),
migrate.UniqueConstraint(
'name',
'domain_id',
name='ixu_role_name_domain_id',
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
role_option = sql.Table(
'role_option',
meta,
sql.Column(
'role_id',
sql.String(64),
sql.ForeignKey(role.c.id, ondelete='CASCADE'),
nullable=False,
primary_key=True,
),
sql.Column('option_id',
sql.String(4),
nullable=False,
primary_key=True),
sql.Column('option_value', ks_sql.JsonBlob, nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
sensitive_config = sql.Table(
'sensitive_config',
meta,
sql.Column('domain_id', sql.String(64), primary_key=True),
sql.Column('group', sql.String(255), primary_key=True),
sql.Column('option', sql.String(255), primary_key=True),
sql.Column('value', ks_sql.JsonBlob.impl, nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
service = sql.Table(
'service',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('type', sql.String(length=255)),
sql.Column(
'enabled',
sql.Boolean,
nullable=False,
default=True,
server_default='1',
),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
service_provider = sql.Table(
'service_provider',
meta,
sql.Column('auth_url', sql.String(256), nullable=False),
sql.Column('id', sql.String(64), primary_key=True),
sql.Column('enabled', sql.Boolean, nullable=False),
sql.Column('description', sql.Text(), nullable=True),
sql.Column('sp_url', sql.String(256), nullable=False),
sql.Column(
'relay_state_prefix',
sql.String(256),
nullable=False,
server_default=service_provider_relay_state_prefix_default,
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
system_assignment = sql.Table(
'system_assignment',
meta,
sql.Column('type', sql.String(64), nullable=False),
sql.Column('actor_id', sql.String(64), nullable=False),
sql.Column('target_id', sql.String(64), nullable=False),
sql.Column('role_id', sql.String(64), nullable=False),
sql.Column('inherited', sql.Boolean, default=False, nullable=False),
sql.PrimaryKeyConstraint('type', 'actor_id', 'target_id', 'role_id',
'inherited'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
token = sql.Table(
'token',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('expires', sql.DateTime, default=None),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('valid', sql.Boolean, default=True, nullable=False),
sql.Column('trust_id', sql.String(length=64)),
sql.Column('user_id', sql.String(length=64)),
sql.Index('ix_token_expires', 'expires'),
sql.Index('ix_token_expires_valid', 'expires', 'valid'),
sql.Index('ix_token_user_id', 'user_id'),
sql.Index('ix_token_trust_id', 'trust_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
trust = sql.Table(
'trust',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('trustor_user_id', sql.String(length=64), nullable=False),
sql.Column('trustee_user_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('impersonation', sql.Boolean, nullable=False),
sql.Column('deleted_at', sql.DateTime),
sql.Column('expires_at', sql.DateTime),
sql.Column('remaining_uses', sql.Integer, nullable=True),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('expires_at_int', ks_sql.DateTimeInt()),
sql.UniqueConstraint(
'trustor_user_id',
'trustee_user_id',
'project_id',
'impersonation',
'expires_at',
'expires_at_int',
name='duplicate_trust_constraint_expanded',
),
sql.Column(
'redelegated_trust_id',
sql.String(64),
nullable=True,
),
sql.Column(
'redelegation_count',
sql.Integer,
nullable=True,
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
trust_role = sql.Table(
'trust_role',
meta,
sql.Column('trust_id',
sql.String(length=64),
primary_key=True,
nullable=False),
sql.Column('role_id',
sql.String(length=64),
primary_key=True,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
user = sql.Table(
'user',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('enabled', sql.Boolean),
sql.Column('default_project_id', sql.String(length=64)),
sql.Column('created_at', sql.DateTime(), nullable=True),
sql.Column('last_active_at', sql.Date(), nullable=True),
sql.Column('domain_id', sql.String(64), nullable=False),
sql.UniqueConstraint('id', 'domain_id', name='ixu_user_id_domain_id'),
sql.Index('ix_default_project_id', 'default_project_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
user_group_membership = sql.Table(
'user_group_membership',
meta,
sql.Column(
'user_id',
sql.String(length=64),
sql.ForeignKey(
user.c.id,
name='fk_user_group_membership_user_id',
),
primary_key=True,
),
sql.Column(
'group_id',
sql.String(length=64),
sql.ForeignKey(
group.c.id,
name='fk_user_group_membership_group_id',
),
primary_key=True,
),
# NOTE(stevemar): The index was named 'group_id' in
# 050_fk_consistent_indexes.py and needs to be preserved
sql.Index('group_id', 'group_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
user_option = sql.Table(
'user_option',
meta,
sql.Column(
'user_id',
sql.String(64),
sql.ForeignKey(user.c.id, ondelete='CASCADE'),
nullable=False,
primary_key=True,
),
sql.Column('option_id',
sql.String(4),
nullable=False,
primary_key=True),
sql.Column('option_value', ks_sql.JsonBlob, nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
whitelisted_config = sql.Table(
'whitelisted_config',
meta,
sql.Column('domain_id', sql.String(64), primary_key=True),
sql.Column('group', sql.String(255), primary_key=True),
sql.Column('option', sql.String(255), primary_key=True),
sql.Column('value', ks_sql.JsonBlob.impl, nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
access_token = sql.Table(
'access_token',
meta,
sql.Column('id', sql.String(64), primary_key=True, nullable=False),
sql.Column('access_secret', sql.String(64), nullable=False),
sql.Column('authorizing_user_id',
sql.String(64),
nullable=False,
index=True),
sql.Column('project_id', sql.String(64), nullable=False),
sql.Column('role_ids', sql.Text(), nullable=False),
sql.Column(
'consumer_id',
sql.String(64),
sql.ForeignKey(consumer.c.id),
nullable=False,
index=True,
),
sql.Column('expires_at', sql.String(64), nullable=True),
)
application_credential_role = sql.Table(
'application_credential_role',
meta,
sql.Column(
'application_credential_id',
sql.Integer,
sql.ForeignKey(application_credential.c.internal_id,
ondelete='CASCADE'),
primary_key=True,
nullable=False,
),
sql.Column('role_id',
sql.String(length=64),
primary_key=True,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
application_credential_access_rule = sql.Table(
'application_credential_access_rule',
meta,
sql.Column(
'application_credential_id',
sql.Integer,
sql.ForeignKey(application_credential.c.internal_id,
ondelete='CASCADE'),
primary_key=True,
nullable=False,
),
sql.Column(
'access_rule_id',
sql.Integer,
sql.ForeignKey(access_rule.c.id, ondelete='CASCADE'),
primary_key=True,
nullable=False,
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
endpoint = sql.Table(
'endpoint',
meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('legacy_endpoint_id', sql.String(length=64)),
sql.Column('interface', sql.String(length=8), nullable=False),
sql.Column(
'service_id',
sql.String(length=64),
sql.ForeignKey(
service.c.id,
name='endpoint_service_id_fkey',
),
nullable=False,
),
sql.Column('url', sql.Text, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column(
'enabled',
sql.Boolean,
nullable=False,
default=True,
server_default='1',
),
sql.Column(
'region_id',
sql.String(length=255),
sql.ForeignKey(
region.c.id,
name='fk_endpoint_region_id',
),
nullable=True,
),
# NOTE(stevemar): The index was named 'service_id' in
# 050_fk_consistent_indexes.py and needs to be preserved
sql.Index('service_id', 'service_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
endpoint_group = sql.Table(
'endpoint_group',
meta,
sql.Column('id', sql.String(64), primary_key=True),
sql.Column('name', sql.String(255), nullable=False),
sql.Column('description', sql.Text, nullable=True),
sql.Column('filters', sql.Text(), nullable=False),
)
expiring_user_group_membership = sql.Table(
'expiring_user_group_membership',
meta,
sql.Column(
'user_id',
sql.String(64),
sql.ForeignKey(user.c.id),
primary_key=True,
),
sql.Column(
'group_id',
sql.String(64),
sql.ForeignKey(group.c.id),
primary_key=True,
),
sql.Column(
'idp_id',
sql.String(64),
sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'),
primary_key=True,
),
sql.Column('last_verified', sql.DateTime(), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
federation_protocol = sql.Table(
'federation_protocol',
meta,
sql.Column('id', sql.String(64), primary_key=True),
sql.Column(
'idp_id',
sql.String(64),
sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'),
primary_key=True,
),
sql.Column('mapping_id', sql.String(64), nullable=False),
sql.Column('remote_id_attribute', sql.String(64)),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
implied_role = sql.Table(
'implied_role',
meta,
sql.Column(
'prior_role_id',
sql.String(length=64),
sql.ForeignKey(
role.c.id,
name='implied_role_prior_role_id_fkey',
ondelete='CASCADE',
),
primary_key=True,
),
sql.Column(
'implied_role_id',
sql.String(length=64),
sql.ForeignKey(
role.c.id,
name='implied_role_implied_role_id_fkey',
ondelete='CASCADE',
),
primary_key=True,
),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
limit = sql.Table(
'limit',
meta,
sql.Column('id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(64), nullable=True),
sql.Column('resource_limit', sql.Integer, nullable=False),
sql.Column('description', sql.Text),
sql.Column('internal_id', sql.Integer, primary_key=True),
# FIXME(stephenfin): This should have a foreign key constraint on
# registered_limit.id, but sqlalchemy-migrate clearly didn't handle
# creating a column with embedded FK info as was attempted in 048
sql.Column(
'registered_limit_id',
sql.String(64),
),
sql.Column('domain_id', sql.String(64), nullable=True),
# NOTE(stephenfin): Name chosen to preserve backwards compatibility
# with names used for primary key unique constraints
sql.UniqueConstraint('id', name='limit_id_key'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
local_user = sql.Table(
'local_user',
meta,
sql.Column('id', sql.Integer, primary_key=True, nullable=False),
sql.Column(
'user_id',
sql.String(64),
nullable=False,
unique=True,
),
sql.Column('domain_id', sql.String(64), nullable=False),
sql.Column('name', sql.String(255), nullable=False),
sql.Column('failed_auth_count', sql.Integer, nullable=True),
sql.Column('failed_auth_at', sql.DateTime(), nullable=True),
sql.ForeignKeyConstraint(
['user_id', 'domain_id'],
[user.c.id, user.c.domain_id],
name='local_user_user_id_fkey',
onupdate='CASCADE',
ondelete='CASCADE',
),
sql.UniqueConstraint('domain_id', 'name'),
)
nonlocal_user = sql.Table(
'nonlocal_user',
meta,
sql.Column('domain_id', sql.String(64), primary_key=True),
sql.Column('name', sql.String(255), primary_key=True),
sql.Column(
'user_id',
sql.String(64),
nullable=False,
),
sql.ForeignKeyConstraint(
['user_id', 'domain_id'],
[user.c.id, user.c.domain_id],
name='nonlocal_user_user_id_fkey',
onupdate='CASCADE',
ondelete='CASCADE',
),
sql.UniqueConstraint('user_id', name='ixu_nonlocal_user_user_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
password = sql.Table(
'password',
meta,
sql.Column('id', sql.Integer, primary_key=True, nullable=False),
sql.Column(
'local_user_id',
sql.Integer,
sql.ForeignKey(local_user.c.id, ondelete='CASCADE'),
nullable=False,
),
sql.Column('expires_at', sql.DateTime(), nullable=True),
sql.Column(
'self_service',
sql.Boolean,
nullable=False,
server_default='0',
default=False,
),
# NOTE(notmorgan): To support the full range of scrypt and pbkfd
# password hash lengths, this should be closer to varchar(1500) instead
# of varchar(255).
sql.Column('password_hash', sql.String(255), nullable=True),
sql.Column(
'created_at_int',
ks_sql.DateTimeInt(),
nullable=False,
default=0,
server_default='0',
),
sql.Column('expires_at_int', ks_sql.DateTimeInt(), nullable=True),
sql.Column(
'created_at',
sql.DateTime(),
nullable=False,
default=datetime.datetime.utcnow,
),
)
project_endpoint_group = sql.Table(
'project_endpoint_group',
meta,
sql.Column(
'endpoint_group_id',
sql.String(64),
sql.ForeignKey(endpoint_group.c.id),
nullable=False,
),
sql.Column('project_id', sql.String(64), nullable=False),
sql.PrimaryKeyConstraint('endpoint_group_id', 'project_id'),
)
federated_user = sql.Table(
'federated_user',
meta,
sql.Column('id', sql.Integer, primary_key=True, nullable=False),
sql.Column(
'user_id',
sql.String(64),
sql.ForeignKey(user.c.id, ondelete='CASCADE'),
nullable=False,
),
sql.Column(
'idp_id',
sql.String(64),
sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'),
nullable=False,
),
sql.Column('protocol_id', sql.String(64), nullable=False),
sql.Column('unique_id', sql.String(255), nullable=False),
sql.Column('display_name', sql.String(255), nullable=True),
sql.ForeignKeyConstraint(
['protocol_id', 'idp_id'],
[federation_protocol.c.id, federation_protocol.c.idp_id],
name='federated_user_protocol_id_fkey',
ondelete='CASCADE',
),
sql.UniqueConstraint('idp_id', 'protocol_id', 'unique_id'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
# create all tables
tables = [
access_rule,
application_credential,
assignment,
config_register,
consumer,
credential,
group,
id_mapping,
identity_provider,
idp_remote_ids,
mapping,
policy,
policy_association,
project,
project_endpoint,
project_option,
project_tag,
region,
registered_limit,
request_token,
revocation_event,
role,
role_option,
sensitive_config,
service,
service_provider,
system_assignment,
token,
trust,
trust_role,
user,
user_group_membership,
user_option,
whitelisted_config,
access_token,
application_credential_access_rule,
application_credential_role,
endpoint,
endpoint_group,
expiring_user_group_membership,
federation_protocol,
implied_role,
limit,
local_user,
nonlocal_user,
password,
project_endpoint_group,
federated_user,
]
for table in tables:
try:
table.create()
except Exception:
LOG.exception('Exception while creating table: %r', table)
raise
fkeys = []
if migrate_engine.name == 'sqlite':
# NOTE(stevemar): We need to keep this FK constraint due to 073, but
# only for sqlite, once we collapse 073 we can remove this constraint
fkeys.append(
{
'columns': [assignment.c.role_id],
'references': [role.c.id],
'name': 'fk_assignment_role_id',
}, )
for fkey in fkeys:
migrate.ForeignKeyConstraint(
columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name'),
ondelete=fkey.get('ondelete'),
onupdate=fkey.get('onupdate'),
).create()
# TODO(stephenfin): Remove these procedures in a future contract migration
if migrate_engine.name == 'postgresql':
error_message = ('Credential migration in progress. Cannot perform '
'writes to credential table.')
credential_update_trigger = textwrap.dedent(f"""
CREATE OR REPLACE FUNCTION keystone_read_only_update()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.encrypted_blob IS NULL THEN
RAISE EXCEPTION '{error_message}';
END IF;
IF NEW.encrypted_blob IS NOT NULL AND OLD.blob IS NULL THEN
RAISE EXCEPTION '{error_message}';
END IF;
RETURN NEW;
END
$BODY$ LANGUAGE plpgsql;
""")
migrate_engine.execute(credential_update_trigger)
error_message = ('Identity provider migration in progress. Cannot '
'insert new rows into the identity_provider table at '
'this time.')
identity_provider_insert_trigger = textwrap.dedent(f"""
CREATE OR REPLACE FUNCTION keystone_read_only_insert()
RETURNS trigger AS
$BODY$
BEGIN
RAISE EXCEPTION '{error_message}';
END
$BODY$ LANGUAGE plpgsql;
""")
migrate_engine.execute(identity_provider_insert_trigger)
federated_user_insert_trigger = textwrap.dedent("""
CREATE OR REPLACE FUNCTION update_federated_user_domain_id()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE "user" SET domain_id = (
SELECT domain_id FROM identity_provider WHERE id = NEW.idp_id)
WHERE id = NEW.user_id and domain_id IS NULL;
RETURN NULL;
END
$BODY$ LANGUAGE plpgsql;
""")
migrate_engine.execute(federated_user_insert_trigger)
local_user_insert_trigger = textwrap.dedent("""
CREATE OR REPLACE FUNCTION update_user_domain_id()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE "user" SET domain_id = NEW.domain_id
WHERE id = NEW.user_id;
RETURN NULL;
END
$BODY$ LANGUAGE plpgsql;
""")
migrate_engine.execute(local_user_insert_trigger)
# FIXME(stephenfin): Remove these indexes. They're left over from attempts
# to remove foreign key constraints in past migrations. Apparently
# sqlalchemy-migrate didn't do the job fully and left behind indexes
if migrate_engine.name == 'mysql':
sql.Index('region_id', registered_limit.c.region_id).create()
# FIXME(stephenfin): This should be dropped when we add the FK
# constraint to this column
sql.Index('registered_limit_id', limit.c.registered_limit_id).create()
# FIXME(stephenfin): These are leftover from when we removed a FK
# constraint and should probable be dropped
sql.Index('domain_id', identity_provider.c.domain_id).create()
sql.Index('domain_id', user.c.domain_id).create()