def change_new_password_request(self):
# NewPasswordRequest inherits, but also has a composite primary key, so it cannot be delt with like
# other inheriting tables
table_name = 'newpasswordrequest'
self.schedule(
'drop_fk', op.drop_constraint,
'newpasswordrequest_verificationrequest_requirement_id_fkey',
table_name)
self.rename_pk_column(table_name, 'verificationrequest_requirement_id',
'id', ['id', 'system_account_id'])
self.schedule('create_fk',
op.create_foreign_key,
fk_name(table_name, 'id', 'verificationrequest'),
table_name,
'verificationrequest', ['id'], ['id'],
ondelete='CASCADE')
# NewPasswordRequest relationship to system_account
self.schedule('drop_fk', op.drop_constraint,
'newpasswordrequest_system_account_id_fk', table_name)
self.schedule('create_fk',
op.create_foreign_key,
fk_name(table_name, 'system_account_id',
'systemaccount'),
table_name,
'systemaccount', ['system_account_id'], ['id'],
deferrable=True,
initially='DEFERRED')
def schedule_upgrades(self):
#the fk's were defined as DEFERRABLE INITIALLY deferred. Since MySQL does not cater for it, we need to remove it.
other_table_name = 'systemaccount'
for table_name in [
'newpasswordrequest', 'changeaccountemail', 'activateaccount'
]:
self.schedule(
'drop_fk', op.drop_constraint,
fk_name(table_name, 'system_account_id', other_table_name),
table_name)
self.schedule(
'create_fk', op.create_foreign_key,
fk_name(table_name, 'system_account_id', other_table_name),
table_name, other_table_name, ['system_account_id'], ['id'])
# MySql does not allow unbounded Unicode/UnicodeText to be indexed etc
for table_name in [
'emailandpasswordsystemaccount', 'accountmanagementinterface',
'verifyemailrequest'
]:
self.schedule('alter',
op.alter_column,
table_name,
'email',
existing_type=UnicodeText,
type_=Unicode(254),
existing_nullable=False)
self.schedule('alter',
op.alter_column,
'queue',
'name',
existing_type=UnicodeText,
type_=Unicode(120),
existing_nullable=False)
def change_task(self):
# Task in elixir did not generate a row_type for use as discriminator
self.schedule('alter', op.add_column, 'task',
Column('row_type', String(40)))
# Task in elixir's queue_id was NULLABLE, but should not be (Tasks HAVE to be in a Queue now)
self.schedule('alter',
op.alter_column,
'task',
'queue_id',
nullable=True)
# Rename of relationship foreign key Task.reserved_by_id -> reserved_by_party_id
old_name = 'reserved_by_id'
new_name = 'reserved_by_party_id'
self.schedule('drop_fk', op.drop_constraint,
'%s_%s_fk' % ('task', old_name), 'task')
self.schedule('alter',
op.alter_column,
'task',
old_name,
new_column_name=new_name)
self.schedule('create_fk', op.create_foreign_key,
fk_name('task', new_name, 'party'), 'task', 'party',
[new_name], ['id'])
self.schedule('drop_pk', op.drop_index,
ix_name('task', 'reserved_by_id'))
self.schedule('indexes', op.create_index,
ix_name('task', 'reserved_by_party_id'), 'task',
['reserved_by_party_id'])
def move_party_systemaccount_relationship(self):
# A SystemAccount now points to its 'owner' (a Party) instead of Party which previously pointed to a SystemAccount
# TODO?:Assert no rows for : select count(*) from party group by system_account_party_id having count(*) > 1;
self.schedule('drop_fk', op.drop_constraint, 'party_system_account_id_fk', 'party')
self.schedule('alter', op.add_column, 'systemaccount', Column('owner_party_id', Integer))
migrate_data = 'UPDATE systemaccount SET owner_party_id = PARTY.id FROM PARTY WHERE PARTY.system_account_id = systemaccount.id'
self.schedule('data', op.execute, migrate_data)
self.schedule('cleanup', op.drop_column, 'party', 'system_account_id')
self.schedule('create_fk', op.create_foreign_key, fk_name('systemaccount', 'owner_party_id', 'party'),
'systemaccount', 'party', ['owner_party_id'], ['id'])
self.schedule('drop_pk', op.drop_index, ix_name('party','system_account_id'))
def schedule_upgrades(self):
self.schedule('drop_pk', op.drop_index,
ix_name('usersession', 'account_id'))
self.schedule('alter', op.drop_column, 'usersession', 'account_id')
self.schedule('alter', op.add_column, 'usersession',
Column('salt', String(40), nullable=False))
self.schedule('alter', op.add_column, 'usersession',
Column('secure_salt', String(40), nullable=False))
self.schedule('alter', op.drop_table, 'webusersession')
self.schedule('data', op.execute, 'delete from usersession')
self.schedule(
'drop_fk', op.drop_constraint,
fk_name('sessiondata', 'web_session_id', 'webusersession'),
'sessiondata')
self.schedule('create_fk',
op.create_foreign_key,
fk_name('sessiondata', 'web_session_id', 'usersession'),
'sessiondata',
'usersession', ['web_session_id'], ['id'],
ondelete='CASCADE')
def change_session_scoped(self, table_name):
"""Rename the old session_id relationship on @session_scoped things to user_session_id, and update
the foreign keys and indexes accordingly.
:arg table_name: The name of the table underlying the @session_scoped class.
"""
old_name = 'session_id'
new_name = 'user_session_id'
self.schedule('drop_fk', op.drop_constraint, '%s_%s_fk' % (table_name, old_name), table_name)
self.schedule('alter', op.alter_column, table_name, old_name, new_column_name=new_name)
self.schedule('create_fk', op.create_foreign_key, fk_name(table_name, new_name, 'usersession'), table_name,
'usersession', [new_name], ['id'], ondelete='CASCADE')
self.schedule('drop_pk', op.drop_index, ix_name(table_name, old_name))
self.schedule('indexes', op.create_index, ix_name(table_name, new_name), table_name, [new_name])
def recreate_foreign_key_constraint(self, old_fk_name, table_name, column_name, other_table_name, other_column_name, **create_kwargs):
"""The names of foreign key constraints change according to naming convention. This method affects
such a name change, but in order to do it, it needs all details necessary to recreate the
foreign key constraint.
:arg old_fk_name: The previous name of the foreign key.
:arg table_name: The name of the table from which the foreign key points.
:arg column_name: The name of the column in which the foreign key pointer is stored.
:arg other_table_name: The name of the table to which the foreign key points.
:arg other_column_name: The name of the column to which the foreign key points.
:kwarg create_kwargs: Additional keyword arguments to be passes to alembic's op.create_foreign_key
"""
self.schedule('drop_fk', op.drop_constraint, '%s_fk' % old_fk_name, table_name)
self.schedule('create_fk', op.create_foreign_key, fk_name(table_name, column_name, other_table_name), table_name,
other_table_name, [column_name], [other_column_name], **create_kwargs)
def change_inheriting_table(self, table_name, old_id_column_name, parent_table_name):
"""Tables of classes that inherit from other classes (using joint table inheritance) named their
primary key columns xxx_id (assuming the parent is called xxx here). These were also foreign
keys to the primary key of the parent table. In our Declarative implementation we just always use
the name `id` for a primary key regardless of the situation.
This method renames such primary key columns, and deal with the knock-on effect of this change
to related primary and foreign key as well.
:arg table_name: The name of the table underlying the child/inheriting class.
:arg old_id_column_name: The old name of the primary key column of the child/inheriting class.
:arg parent_table_name: The name of the table underlying the parent class.
"""
self.schedule('drop_fk', op.drop_constraint, '%s_%s_fkey' % (table_name, old_id_column_name), table_name)
self.rename_pk_column(table_name, old_id_column_name, 'id', ['id'])
self.schedule('create_fk', op.create_foreign_key, fk_name(table_name, 'id', parent_table_name), table_name,
parent_table_name, ['id'], ['id'], ondelete='CASCADE')
def rename_link_table(self):
old_table_name = 'requirement_deferred_actions__deferredaction_requirements'
new_table_name = 'deferredaction_requirement'
# Rename table itself
self.schedule('alter', op.rename_table, old_table_name, new_table_name)
# Rename of foreign key names
for old_name, other_table_name in [
('deferredaction_requirements_fk', 'deferredaction'),
('requirement_deferred_actions_fk', 'requirement') ]:
column_name = '%s_id' % other_table_name
new_name = fk_name(new_table_name, column_name, other_table_name)
self.schedule('drop_fk', op.drop_constraint, old_name, old_table_name)
self.schedule('create_fk', op.create_foreign_key, new_name, new_table_name, other_table_name, [column_name], ['id'])
# Primary keys are renamed according to new naming convention - in this case the table too
self.rename_pk(new_table_name, ['deferredaction_id', 'requirement_id'], old_table_name=old_table_name)
