def _rename_columns():
column_rename = {
'contributors': {
'person_id': 'user_id'
},
'institutionuser': {
'person_id': 'user_id'
},
'instrumentuser': {
'custodian_id': 'user_id'
},
'projectuser': {
'person_id': 'user_id'
},
'transactionuser': {
'authorized_person_id': 'user_id'
}
}
migrator = SchemaMigrator(DB)
for table_name, column_tre in column_rename.items():
for old_name, new_name in column_tre.items():
migrate(migrator.rename_column(table_name, old_name, new_name))
old_fkey_name = '_'.join([table_name, old_name, 'fkey'])
new_fkey_name = '_'.join([table_name, new_name, 'fkey'])
DB.execute_sql('alter table {} rename constraint {} TO {}'.format(
table_name, old_fkey_name, new_fkey_name))
old_index_name = '_'.join([table_name, old_name])
new_index_name = '_'.join([table_name, new_name])
DB.execute_sql('alter index {} rename to {}'.format(
old_index_name, new_index_name))
def update_schema():
"""Update schema from 2.0 to 2.1."""
DB.execute_sql(
'alter table projects rename constraint proposals_pkey to projects_pkey'
)
rename_indexes = {
'atoolproject_proposal_id': 'atoolproject_project_id',
'citationproject_proposal_id': 'citationproject_project_id',
'projectgroup_proposal_id': 'projectgroup_project_id',
'projectinstrument_proposal_id': 'projectinstrument_project_id',
'projectparticipant_proposal_id': 'projectparticipant_project_id',
'proposals_accepted_date': 'projects_accepted_date',
'proposals_actual_end_date': 'projects_actual_end_date',
'proposals_actual_start_date': 'projects_actual_start_date',
'proposals_closed_date': 'projects_closed_date',
'proposals_created': 'projects_created',
'proposals_deleted': 'projects_deleted',
'proposals_short_name': 'projects_short_name',
'proposals_submitted_date': 'projects_submitted_date',
'proposals_suspense_date': 'projects_suspense_date',
'proposals_title': 'projects_title',
'proposals_updated': 'projects_updated',
'transsap_proposal_id': 'transsap_project_id',
'transsip_proposal_id': 'transsip_project_id'
}
for old_index, new_index in rename_indexes.items():
DB.execute_sql('alter index {} rename to {}'.format(
old_index, new_index))
migrator = SchemaMigrator(DB)
for table_name in ['transsip', 'transsap']:
for col_name in ['created', 'deleted', 'updated']:
migrate(migrator.add_index(table_name, (col_name, ), False))
def update_0_0_to_1_0(cls):
"""Update by creating the table."""
if not IngestState.table_exists():
IngestState.create_table()
col_names = [col_md.name for col_md in DB.get_columns('ingeststate')]
if 'complete' in col_names:
migrator = SchemaMigrator(DB)
migrate(migrator.drop_column('ingeststate', 'complete'))
def update_0_1_to_1_0(cls):
"""Update by adding the boolean column."""
migrator = SchemaMigrator(DB)
migrate(
migrator.add_column(
'cart',
'bundle',
BooleanField(default=False, null=True)
)
)
def _rename_column():
table_name = 'usergroup'
old_name = 'person_id'
new_name = 'user_id'
migrator = SchemaMigrator(DB)
migrate(migrator.rename_column(table_name, old_name, new_name))
old_fkey_name = '_'.join([table_name, old_name, 'fkey'])
new_fkey_name = '_'.join([table_name, new_name, 'fkey'])
DB.execute_sql('alter table {} rename constraint {} TO {}'.format(
table_name, old_fkey_name, new_fkey_name))
old_index_name = '_'.join([table_name, old_name])
new_index_name = '_'.join([table_name, new_name])
DB.execute_sql('alter index {} rename to {}'.format(
old_index_name, new_index_name))
def update_schema():
"""Update schema from 3.0 to 4.0."""
migrator = SchemaMigrator(DB)
for table_name in ['keys', 'values', 'groups']:
migrate(
migrator.add_column(table_name, 'display_name',
CharField(default='', index=True)),
migrator.add_column(table_name, 'description',
TextField(default='')))
new_rel_list = [{
'name':
'upload_required',
'display_name':
'Required for Upload',
'description':
'This relationship means that the objects are required for upload to be asserted.'
}, {
'name':
'search_required',
'display_name':
'Required for Search',
'description':
'This relationship means that the objects are required for search to be asserted.'
}, {
'name':
'co_principal_investigator',
'display_name':
'Co-Principal Investigator',
'description':
'subject is the co-principal investigator of the object'
}]
for new_rel in new_rel_list:
Relationships.get_or_create(**new_rel)
rel_obj = Relationships.get(name='upload_required')
migrate(
migrator.add_column(
'instrumentkeyvalue', 'relationship_id',
ForeignKeyField(Relationships,
field=Relationships.uuid,
default=rel_obj.uuid)))
DB.execute_sql(
'alter table instrumentkeyvalue drop constraint instrumentkeyvalue_pkey'
)
DB.execute_sql('''
alter table
instrumentkeyvalue
add constraint
instrumentkeyvalue_pkey primary key (instrument_id, key_id, value_id, relationship_id)
''')
def _rename_tables():
table_rename = {
'institutionperson': 'institutionuser',
'instrumentcustodian': 'instrumentuser',
'projectparticipant': 'projectuser',
'transactionrelease': 'transactionuser'
}
migrator = SchemaMigrator(DB)
for old_table, new_table in table_rename.items():
migrate(migrator.rename_table(old_table, new_table))
for index_meta in DB.get_indexes(new_table):
new_index_name = '{}{}'.format(new_table, index_meta.name[len(old_table):])
DB.execute_sql('alter index {} rename to {}'.format(index_meta.name, new_index_name))
for fkey_meta in DB.get_foreign_keys(new_table):
old_name = '_'.join([old_table, fkey_meta.column, 'fkey'])
new_name = '_'.join([new_table, fkey_meta.column, 'fkey'])
DB.execute_sql('alter table {} rename constraint {} TO {}'.format(new_table, old_name, new_name))
def update_0_1_to_1_0(cls):
"""Update from 0.1 to 1.0."""
migrator = SchemaMigrator(DB)
TransSIP.create_table()
TransSAP.create_table()
class OldTrans(Model):
"""This is the old transactions."""
submitter = ForeignKeyField(Users, backref='transactions')
instrument = ForeignKeyField(Instruments, backref='transactions')
proposal = ForeignKeyField(Proposals, backref='transactions')
created = DateTimeField()
updated = DateTimeField()
deleted = DateTimeField(null=True)
class Meta(object):
"""This is the meta class for OldTrans."""
database = DB
table_name = 'transactions'
migrate(
migrator.add_column('transactions', 'description',
TextField(null=True)))
for old_trans in OldTrans.select():
transsip = TransSIP()
for attr in [
'submitter', 'instrument', 'proposal', 'created',
'updated', 'deleted'
]:
setattr(transsip, attr, getattr(old_trans, attr))
setattr(transsip, 'id',
Transactions.get(Transactions.id == old_trans.id))
transsip.save(force_insert=True)
migrate(migrator.drop_column('transactions', 'submitter_id'),
migrator.drop_column('transactions', 'instrument_id'),
migrator.drop_column('transactions', 'proposal_id'))
def update_schema():
"""Update from 0.1 to 1.0."""
migrator = SchemaMigrator(DB)
OldTransSIP.create_table()
OldTransSAP.create_table()
migrate(
migrator.add_column('transactions', 'description',
TextField(null=True)))
for old_trans in OldTrans.select():
transsip = OldTransSIP()
for attr in [
'submitter', 'instrument', 'proposal', 'created', 'updated',
'deleted'
]:
setattr(transsip, attr, getattr(old_trans, attr))
setattr(transsip, 'id',
Transactions.get(Transactions.id == old_trans.id))
transsip.save(force_insert=True)
migrate(migrator.drop_column('transactions', 'submitter_id'),
migrator.drop_column('transactions', 'instrument_id'),
migrator.drop_column('transactions', 'proposal_id'))
def update_1_0_to_2_0(cls):
"""Update by adding the auth column."""
migrator = SchemaMigrator(DB)
migrate(migrator.add_column('eventmatch', 'auth',
TextField(null=True)))
def update_0_0_to_1_0(cls):
"""Update by adding the new table."""
if not EventMatch.table_exists():
EventMatch.create_table()
migrator = SchemaMigrator(DB)
migrate(migrator.drop_column('eventmatch', 'auth'))
def _add_relationship_columns():
table_rel = {
'institutionuser':
('member_of', 'institution_user', ('institution_id', 'user_id')),
'instrumentuser':
('custodian', 'instrument_user', ('instrument_id', 'user_id')),
'projectuser':
('member_of', 'project_user', ('project_id', 'user_id')),
'projectinstrument':
('member_of', 'project_instrument', ('instrument_id', 'project_id')),
'transactionuser': ('authorized_releaser', 'transaction_user',
('transaction_id', 'user_id'))
}
migrator = SchemaMigrator(DB)
DB.execute_sql(
'alter table citationtransaction drop constraint citationtransaction_transaction_id_fkey'
)
DB.execute_sql('drop index citationtransaction_transaction_id')
DB.execute_sql(
'alter table doitransaction drop constraint doitransaction_transaction_id_fkey'
)
DB.execute_sql('drop index doitransaction_transaction_id')
migrate(
migrator.rename_column('citationtransaction', 'transaction_id',
'trans_old_id'),
migrator.rename_column('doitransaction', 'transaction_id',
'trans_old_id'))
for table_name, rel_info in table_rel.items():
DB.execute_sql('alter table {} drop constraint {}_pkey'.format(
table_name, table_name))
rel_name, backref, pkey_columns = rel_info
rel_obj = Relationships.get(Relationships.name == rel_name)
migrate(
migrator.add_column(
table_name, 'relationship_id',
ForeignKeyField(Relationships,
field=Relationships.uuid,
default=rel_obj.uuid,
backref=backref)),
migrator.add_column(table_name, 'uuid', UUIDField(null=True)),
migrator.add_index(table_name, [
fkey_meta.column
for fkey_meta in DB.get_foreign_keys(table_name)
],
unique=True))
for row in DB.execute_sql('select {} from {}'.format(
','.join(pkey_columns), table_name)):
condition = []
for key, value in zip(pkey_columns, row):
param_val = value if isinstance(value,
int) else u"'{}'".format(value)
condition.append(u'{} = {}'.format(key, param_val))
condition = u' and '.join(condition)
DB.execute_sql(
u'update {} set uuid = %s where {}'.format(
table_name, condition), (str(uuid.uuid4()), ))
DB.execute_sql(
'alter table {} add constraint {}_pkey primary key (uuid)'.format(
table_name, table_name))
migrate(
migrator.add_column(
'citationtransaction', 'transaction_id',
ForeignKeyField(TransactionUser,
index=True,
default=None,
null=True,
field=TransactionUser.uuid)),
migrator.add_column(
'doitransaction', 'transaction_id',
ForeignKeyField(TransactionUser,
index=True,
default=None,
null=True,
field=TransactionUser.uuid)))
for row in DB.execute_sql('select trans_old_id from citationtransaction'):
cursor = DB.execute_sql(
'select uuid from transactionuser where transaction_id = {}'.
format(row[0]))
new_uuid = list(cursor)[0][0]
DB.execute_sql(
"update citationtransaction set transaction_id = '{}' where trans_old_id = {}"
.format(new_uuid, row[0]))
for row in DB.execute_sql('select trans_old_id from doitransaction'):
cursor = DB.execute_sql(
'select uuid from transactionuser where transaction_id = {}'.
format(row[0]))
new_uuid = list(cursor)[0][0]
DB.execute_sql(
"update doitransaction set transaction_id = '{}' where trans_old_id = {}"
.format(new_uuid, row[0]))
migrate(
migrator.drop_column('citationtransaction', 'trans_old_id'),
migrator.drop_column('doitransaction', 'trans_old_id'),
migrator.add_not_null('citationtransaction', 'transaction_id'),
migrator.add_not_null('doitransaction', 'transaction_id'),
)
for table_name, rel_info in table_rel.items():
rel_name, backref, pkey_columns = rel_info
new_index_name = '_'.join(
[table_name, pkey_columns[1], pkey_columns[0], 'relationship_id'])
old_index_name = '_'.join(
[table_name, pkey_columns[0], pkey_columns[1]])
DB.execute_sql('drop index if exists {}'.format(old_index_name))
old_index_name = '_'.join(
[table_name, pkey_columns[1], pkey_columns[0]])
DB.execute_sql('drop index if exists {}'.format(old_index_name))
DB.execute_sql('create unique index {} on {} ({})'.format(
new_index_name, table_name,
','.join([pkey_columns[1], pkey_columns[0], 'relationship_id'])))
DB.execute_sql(
'alter table {table} add constraint {table}_pkey primary key (citation_id, transaction_id)'
.format(table='citationtransaction'))
DB.execute_sql(
'create index transactionuser_transaction_id on transactionuser (transaction_id)'
)
def update_schema():
"""Update schema from 1.0 to 2.0."""
old_table_data = {
'atoolproposal': {
'new_name': 'atoolproject',
'column_rename': {
'proposal': 'project'
}
},
'citationproposal': {
'new_name': 'citationproject',
},
'proposalgroup': {
'new_name': 'projectgroup',
},
'proposalinstrument': {
'new_name': 'projectinstrument',
},
'proposalparticipant': {
'new_name': 'projectparticipant',
},
'transsip': {},
'transsap': {}
}
migrator = SchemaMigrator(DB)
migrate(
migrator.rename_table('proposals', 'projects'),
migrator.rename_column('projects', 'proposal_type', 'project_type'))
for old_table_name, old_table_value in old_table_data.items():
if old_table_value.get('new_name', False):
migrate(
migrator.rename_table(old_table_name,
old_table_value['new_name']))
new_table_name = old_table_value.get('new_name', old_table_name)
migrate(
migrator.rename_column(new_table_name, 'proposal_id',
'project_id'))
if old_table_value.get('new_name', False):
for index_meta in DB.get_indexes(new_table_name):
new_index_name = '{}{}'.format(
new_table_name, index_meta.name[len(old_table_name):])
DB.execute_sql('alter index {} rename to {}'.format(
index_meta.name, new_index_name))
for fkey_meta in DB.get_foreign_keys(new_table_name):
if not old_table_value.get(
'new_name', False) and fkey_meta.dest_table != 'projects':
continue
if old_table_value.get('new_name', False):
old_index_prefix = old_table_name
else:
old_index_prefix = new_table_name
if fkey_meta.dest_table == 'projects':
old_fkey_name = '_'.join(
[old_index_prefix, 'proposal_id', 'fkey'])
else:
old_fkey_name = '_'.join(
[old_index_prefix, fkey_meta.column, 'fkey'])
new_fkey_name = '_'.join(
[new_table_name, fkey_meta.column, 'fkey'])
DB.execute_sql('alter table {} rename constraint {} TO {}'.format(
new_table_name, old_fkey_name, new_fkey_name))
def update_1_0_to_2_0(cls):
"""Update by adding the boolean column."""
migrator = SchemaMigrator(DB)
migrate(
migrator.add_column('ingeststate', 'complete',
BooleanField(default=False)))
