Select Git revision
2b698bb45c09_normalize_nrens.py
Remco Tukker authored
2b698bb45c09_normalize_nrens.py 8.69 KiB
"""normalize nrens
Revision ID: 2b698bb45c09
Revises: b123f21a8f4c
Create Date: 2023-04-12 20:51:05.934130
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '2b698bb45c09'
down_revision = 'b123f21a8f4c'
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'nren',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('pk_nren'))
)
op.execute(
"INSERT INTO nren (name) VALUES "
# "('AAF - Australian Access Federation'),"
# "('AARNet'),"
"('ACOnet'),"
# "('AfgREN'),"
"('AMRES'),"
# "('Ankabut'),"
# "('Arandu'),"
"('ARENA'),"
# "('ARN'),"
"('ARNES'),"
# "('ASGC'),"
"('ASNET-AM'),"
# "('AzRena'),"
"('AzScienceNet'),"
# "('BARNet'),"
"('BASNET'),"
# "('BdREN'),"
"('Belnet'),"
"('BIHARNET'),"
# "('BOLNET'),"
"('BREN'),"
# "('Brunet'),"
# "('CameroonianNREN'),"
# "('CANARIE'),"
"('CARNET'),"
# "('CEDIA'),"
# "('CERNET'),"
"('CESNET'),"
# "('CNRS'),"
# "('CNTI'),"
# "('CRNet'),"
# "('CSTNet'),"
# "('CUDI'),"
"('CYNET'),"
"('DeIC'),"
"('DFN'),"
# "('e-ARENA'),"
# "('eb@le'),"
"('EENet'),"
# "('ErdemNet'),"
# "('ERNET'),"
# "('EtherNet'),"
# "('EUN'),"
"('FCCN'),"
# "('FREEnet'),"
"('Funet'),"
# "('GabonREN'),"
# "('GARNET'),"
"('GARR'),"
# "('GCC'),"
"('GRENA'),"
"('GRNET S.A.'),"
# "('HARNET'),"
"('HEAnet'),"
# "('INNOVA|RED'),"
# "('Internet2'),"
# "('IRANET'),"
# "('IRANET/IPM'),"
# "('iRENALA'),"
# "('ITB'),"
# "('ITC'),"
"('IUCC'),"
"('Jisc'),"
# "('JREN'),"
# "('JUNet'),"
# "('KAUST'),"
# "('KAZRENA'),"
# "('KENET'),"
"('KIFU'),"
# "('KOREN'),"
# "('KREN'),"
# "('KRENA-AKNET'),"
# "('KREONET'),"
"('LAT'),"
# "('LEARN'),"
# "('LERNET'),"
"('LITNET'),"
# "('MaliREN'),"
# "('MAREN'),"
"('MARnet'),"
# "('MARWAN'),"
# "('mmREN'),"
# "('MoRENet'),"
"('MREN'),"
# "('MYREN'),"
# "('NCHC'),"
# "('ngNER'),"
# "('ngREN'),"
# "('NiCT'),"
# "('NigerREN'),"
# "('NII'),"
# "('NITC'),"
# "('NREN'),"
# "('OMREN'),"
# "('PADI2'),"
# "('PERN'),"
"('PIONIER'),"
# "('PNGARNet'),"
# "('PREGINET'),"
# "('Qatar Foundation'),"
# "('RAAP'),"
# "('RADEI'),"
# "('RAGIE'),"
# "('RAICES'),"
"('RASH'),"
# "('RAU'),"
# "('REACCIUN'),"
# "('REANNZ'),"
# "('RedCyT'),"
"('RedIRIS'),"
# "('RedUNIV'),"
"('RENAM'),"
# "('RENATA'),"
"('RENATER'),"
# "('RENER'),"
# "('RENIA'),"
# "('RENU'),"
# "('RerBenin'),"
"('RESTENA'),"
# "('REUNA'),"
"('RHnet'),"
# "('RITER'),"
# "('RNERT'),"
# "('RNP'),"
# "('RNRT'),"
# "('RNU'),"
"('RoEduNet'),"
# "('RUB'),"
# "('RwEdNet'),"
"('SANET'),"
# "('SANReN'),"
# "('SAREN'),"
"('SARNET'),"
# "('SHERN'),"
"('SigmaNet'),"
"('SIKT'),"
# "('SingAREN'),"
# "('snRER'),"
# "('Somaliren'),"
# "('SudREN'),"
"('SUNET'),"
# "('SUREN'),"
"('SURF'),"
"('SWITCH'),"
# "('TARENA'),"
# "('TENET'),"
# "('TERNET'),"
# "('ThaiREN'),"
# "('TTRENT'),"
# "('TuRENA'),"
"('UARNet'),"
"('ULAKBIM'),"
# "('UNITEC'),"
"('UNREN'),"
"('UoM'),"
"('URAN')"
# "('UzSciNet'),"
# "('VinaREN'),"
# "('ZAMREN')"
)
op.add_column('budgets', sa.Column('nren_id', sa.Integer()))
op.execute(
"UPDATE budgets SET nren_id = nren.id FROM nren "
"WHERE (UPPER(budgets.nren) = UPPER(nren.name))"
" OR (budgets.nren = 'ASNET' AND nren.name = 'ASNET-AM')"
" OR (budgets.nren = 'KIFU (NIIF)' AND nren.name = 'KIFU')"
" OR (budgets.nren = 'SURFnet' AND nren.name = 'SURF')"
" OR (budgets.nren = 'UoM/RicerkaNet' AND nren.name = 'UoM')"
" OR (budgets.nren = 'UNINETT' AND nren.name = 'SIKT')"
" OR (budgets.nren = 'LANET' AND nren.name = 'LAT')"
" OR (budgets.nren = 'ANA' AND nren.name = 'RASH')"
)
op.alter_column('budgets', 'nren_id', nullable=False)
op.create_foreign_key(
op.f('fk_budgets_nren_id_nren'),
'budgets', 'nren', ['nren_id'], ['id'])
op.drop_column('budgets', 'nren')
op.add_column('funding_source', sa.Column('nren_id', sa.Integer()))
op.execute(
"UPDATE funding_source SET nren_id = nren.id FROM nren "
"WHERE (UPPER(funding_source.nren) = UPPER(nren.name))"
"OR (funding_source.nren = 'ASNET' AND nren.name = 'ASNET-AM')"
"OR (funding_source.nren = 'KIFU (NIIF)' AND nren.name = 'KIFU')"
"OR (funding_source.nren = 'SURFnet' AND nren.name = 'SURF')"
"OR (funding_source.nren = 'UoM/RicerkaNet' AND nren.name = 'UoM')"
"OR (funding_source.nren = 'UNINETT' AND nren.name = 'SIKT')"
"OR (funding_source.nren = 'LANET' AND nren.name = 'LAT')"
"OR (funding_source.nren = 'ANA' AND nren.name = 'RASH')"
)
op.alter_column('funding_source', 'nren_id', nullable=False)
op.create_foreign_key(
op.f('fk_funding_source_nren_id_nren'),
'funding_source', 'nren', ['nren_id'], ['id']
)
op.drop_column('funding_source', 'nren')
op.add_column('charging_structure', sa.Column('nren_id', sa.Integer()))
op.execute(
"UPDATE charging_structure SET nren_id = nren.id FROM nren "
"WHERE (UPPER(charging_structure.nren) = UPPER(nren.name))"
"OR (charging_structure.nren = 'ASNET' AND nren.name = 'ASNET-AM')"
"OR (charging_structure.nren = 'KIFU (NIIF)' AND nren.name = 'KIFU')"
"OR (charging_structure.nren = 'SURFnet' AND nren.name = 'SURF')"
"OR (charging_structure.nren = 'UoM/RicerkaNet' AND nren.name = 'UoM')"
"OR (charging_structure.nren = 'UNINETT' AND nren.name = 'SIKT')"
"OR (charging_structure.nren = 'LANET' AND nren.name = 'LAT')"
"OR (charging_structure.nren = 'ANA' AND nren.name = 'RASH')"
)
op.alter_column('charging_structure', 'nren_id', nullable=False)
op.create_foreign_key(
op.f('fk_charging_structure_nren_id_nren'),
'charging_structure', 'nren', ['nren_id'], ['id'])
op.drop_column('charging_structure', 'nren')
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'charging_structure',
sa.Column('nren', sa.VARCHAR(length=128), autoincrement=False)
)
op.execute(
"UPDATE charging_structure SET nren = nren.name FROM nren"
" WHERE charging_structure.nren_id = nren.id"
)
op.alter_column('charging_structure', 'nren', nullable=False)
op.drop_constraint(
op.f('fk_charging_structure_nren_id_nren'),
'charging_structure',
type_='foreignkey'
)
op.drop_column('charging_structure', 'nren_id')
op.add_column(
'funding_source',
sa.Column('nren', sa.VARCHAR(length=128), autoincrement=False)
)
op.execute(
"UPDATE funding_source SET nren = nren.name FROM nren"
" WHERE funding_source.nren_id = nren.id"
)
op.alter_column('funding_source', 'nren', nullable=False)
op.drop_constraint(
op.f('fk_funding_source_nren_id_nren'),
'funding_source',
type_='foreignkey'
)
op.drop_column('funding_source', 'nren_id')
op.add_column(
'budgets',
sa.Column('nren', sa.VARCHAR(length=128), autoincrement=False)
)
op.execute(
"UPDATE budgets SET nren = nren.name FROM nren"
" WHERE budgets.nren_id = nren.id"
)
op.alter_column('budgets', 'nren', nullable=False)
op.drop_constraint(
op.f('fk_budgets_nren_id_nren'), 'budgets', type_='foreignkey')
op.drop_column('budgets', 'nren_id')
op.drop_table('nren')
# ### end Alembic commands ###