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