def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
'L1',
'reporter',
existing_type=sa.Text(),
type_=sa.INTEGER(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
)
op.alter_column(
'L1',
'partner',
existing_type=sa.Text(),
type_=sa.INTEGER(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
)
op.drop_column('L1',
'eu_rep_rate',
schema=quoted_name('world_bank.tariff', quote=True))
op.drop_column('L1',
'eu_part_rate',
schema=quoted_name('world_bank.tariff', quote=True))
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'return',
sa.Column('elr_id', sa.Integer(), nullable=False),
sa.Column('elr_version', sa.Integer(), nullable=False),
sa.Column('status', sa.Text(), nullable=False),
sa.Column('created_datetime', sa.Date(), nullable=False),
sa.Column('status_control', sa.Text(), nullable=False),
sa.Column('batch_id', sa.Integer(), nullable=False),
sa.Column('licence_type', sa.Text(), nullable=False),
sa.Column('el_id', sa.Integer(), nullable=False),
sa.Column('ogl_id', sa.Integer(), nullable=True),
sa.Column('return_period_date', sa.Date(), nullable=True),
sa.Column('end_country_id', sa.Integer(), nullable=True),
sa.Column('usage_count', sa.Integer(), nullable=True),
sa.Column('end_user_type', sa.Text(), nullable=True),
sa.Column('eco_comment', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.PrimaryKeyConstraint('elr_id', 'elr_version'),
schema=quoted_name('spire', quote=True),
)
op.drop_table('returns', schema=quoted_name('spire', quote=True))
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_index(
'L1_partner_idx',
'L1',
['partner'],
unique=False,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='hash',
)
op.create_index(
'L1_product_idx',
'L1',
['product'],
unique=False,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='hash',
)
op.create_index(
'L1_reporter_idx',
'L1',
['reporter'],
unique=False,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='hash',
)
op.create_index(
'L1_year_idx',
'L1',
['year'],
unique=False,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='hash',
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index('L1_year_idx',
table_name='L1',
schema=quoted_name('world_bank.tariff', quote=True))
op.drop_index('L1_reporter_idx',
table_name='L1',
schema=quoted_name('world_bank.tariff', quote=True))
op.drop_index('L1_product_idx',
table_name='L1',
schema=quoted_name('world_bank.tariff', quote=True))
op.drop_index('L1_partner_idx',
table_name='L1',
schema=quoted_name('world_bank.tariff', quote=True))
def schema_upgrades():
"""schema upgrade migrations go here."""
op.add_column(
'L1',
sa.Column('publication_date', sa.Date(), nullable=True),
schema=quoted_name('ons.postcode_directory', quote=True),
)
def __init__(self, name, metadata, as_select, **kwargs):
"""Instantiate a new View Clause."""
super(ViewClause, self).__init__()
# Initialize columns
self._columns = ColumnCollection()
if not isinstance(as_select, Select):
self.as_select = as_select
else:
self.as_select = as_select
for c in as_select.columns:
self._append_column(c)
self.metadata = metadata
self.bind = metadata.bind
self.name = name
self.schema = kwargs.pop("schema", None)
if self.schema is None:
self.schema = metadata.schema
elif self.schema is BLANK_SCHEMA:
self.schema = None
else:
quote_schema = kwargs.pop("quote_schema", None)
self.schema = quoted_name(self.schema, quote_schema)
self.indexes = set()
self.constraints = set()
self.foreign_keys = set()
self.primary_keys = set()
if self.schema is not None:
self.fullname = "%s.%s" % (self.schema, self.name)
else:
self.fullname = self.name
def test_rename_table_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.rename_table("t1", "t2", schema=quoted_name("some.schema", quote=True))
context.assert_('ALTER TABLE "some.schema".t1 RENAME TO t2')
def test_add_column_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.add_column("somename", Column("colname", String), schema=quoted_name("some.schema", quote=True))
context.assert_('ALTER TABLE "some.schema".somename ADD COLUMN colname VARCHAR')
def test_rename_table_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.rename_table('t1',
't2',
schema=quoted_name("some.schema", quote=True))
context.assert_('ALTER TABLE "some.schema".t1 RENAME TO t2')
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'ref_report_ratings',
sa.Column('report_rating', sa.Text(), nullable=False),
schema=quoted_name('spire', quote=True),
)
def test_rename_table_schema_hard_quoting(self):
context = op_fixture("postgresql")
op.rename_table("t1",
"t2",
schema=quoted_name("some.schema", quote=True))
context.assert_('ALTER TABLE "some.schema".t1 RENAME TO t2')
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'roles',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('name', sa.Text(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'),
schema=quoted_name('admin', quote=True),
)
op.create_table(
'users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Text(), nullable=True),
sa.Column('first_name', sa.Text(), nullable=True),
sa.Column('last_name', sa.Text(), nullable=True),
sa.Column('email', sa.Text(), nullable=True),
sa.Column('password', sa.Text(), nullable=True),
sa.Column('active', sa.Boolean(), nullable=True),
sa.Column('last_login_at', sa.DateTime(), nullable=True),
sa.Column('current_login_at', sa.DateTime(), nullable=True),
sa.Column('last_login_ip', sa.Text(), nullable=True),
sa.Column('current_login_ip', sa.Text(), nullable=True),
sa.Column('login_count', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('user_id'),
schema=quoted_name('admin', quote=True),
)
op.create_table(
'users_roles',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['role_id'],
['admin.roles.id'],
),
sa.ForeignKeyConstraint(
['user_id'],
['admin.users.id'],
),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('admin', quote=True),
)
def test_add_column_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.add_column("somename",
Column("colname", String),
schema=quoted_name("some.schema", quote=True))
context.assert_(
'ALTER TABLE "some.schema".somename ADD COLUMN colname VARCHAR')
def test_add_constraint_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.create_check_constraint("ck_user_name_len",
"user_table",
func.len(column('name')) > 5,
schema=quoted_name("some.schema",
quote=True))
context.assert_('ALTER TABLE "some.schema".user_table ADD '
'CONSTRAINT ck_user_name_len CHECK (len(name) > 5)')
def test_add_constraint_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column("name")) > 5,
schema=quoted_name("some.schema", quote=True),
)
context.assert_('ALTER TABLE "some.schema".user_table ADD ' "CONSTRAINT ck_user_name_len CHECK (len(name) > 5)")
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
'control_entries',
'value',
existing_type=sa.Numeric(),
type_=sa.INTEGER(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
def test_add_constraint_schema_hard_quoting(self):
context = op_fixture("postgresql")
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column("name")) > 5,
schema=quoted_name("some.schema", quote=True),
)
context.assert_('ALTER TABLE "some.schema".user_table ADD '
"CONSTRAINT ck_user_name_len CHECK (len(name) > 5)")
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'pipeline',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('organisation', sa.Text(), nullable=False),
sa.Column('dataset', sa.Text(), nullable=False),
sa.Column('slug', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('organisation', 'dataset', name='organisation_dataset_unique_together'),
schema=quoted_name('public', quote=True),
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'returns',
sa.Column('elr_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('elr_version', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('status', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column('created_datetime', sa.DATE(), autoincrement=False, nullable=False),
sa.Column('status_control', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column('batch_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('licence_type', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column('el_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column('ogl_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('return_period_date', sa.DATE(), autoincrement=False, nullable=True),
sa.Column('end_country_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('usage_count', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('end_user_type', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('eco_comment', sa.TEXT(), autoincrement=False, nullable=True),
sa.ForeignKeyConstraint(['batch_id'], ['spire.batch.id'], name='returns_batch_id_fkey'),
sa.PrimaryKeyConstraint('elr_id', 'elr_version', name='returns_pkey'),
schema=quoted_name('spire', quote=True),
)
op.drop_table('return', schema=quoted_name('spire', quote=True))
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_index(
op.f('ix_world_bank_bound_rates_L1_product'),
'L1',
['product'],
unique=False,
schema=quoted_name('world_bank.bound_rates', quote=True),
)
op.create_index(
op.f('ix_world_bank_bound_rates_L1_reporter'),
'L1',
['reporter'],
unique=False,
schema=quoted_name('world_bank.bound_rates', quote=True),
)
op.add_column(
'L1',
sa.Column('eu_eu_rate', sa.Numeric(), nullable=True),
schema=quoted_name('world_bank.tariff', quote=True),
)
op.alter_column(
'L1',
'partner',
existing_type=sa.TEXT(),
type_=sa.Integer(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='partner::integer',
)
op.alter_column(
'L1',
'reporter',
existing_type=sa.TEXT(),
type_=sa.Integer(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='reporter::integer',
)
op.drop_column('L1', 'country_average', schema=quoted_name('world_bank.tariff', quote=True))
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'L1',
sa.Column('country_average', sa.NUMERIC(), autoincrement=False, nullable=True),
schema=quoted_name('world_bank.tariff', quote=True),
)
op.alter_column(
'L1',
'reporter',
existing_type=sa.Integer(),
type_=sa.TEXT(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='reporter::text',
)
op.alter_column(
'L1',
'partner',
existing_type=sa.Integer(),
type_=sa.TEXT(),
existing_nullable=True,
schema=quoted_name('world_bank.tariff', quote=True),
postgresql_using='partner::text',
)
op.drop_column('L1', 'eu_eu_rate', schema=quoted_name('world_bank.tariff', quote=True))
op.drop_index(
op.f('ix_world_bank_bound_rates_L1_reporter'),
table_name='L1',
schema=quoted_name('world_bank.bound_rates', quote=True),
)
op.drop_index(
op.f('ix_world_bank_bound_rates_L1_product'),
table_name='L1',
schema=quoted_name('world_bank.bound_rates', quote=True),
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('L1', schema=quoted_name('world_bank.tariff', quote=True))
op.drop_table('L0', schema=quoted_name('world_bank.tariff', quote=True))
op.drop_table('L1',
schema=quoted_name('world_bank.bound_rates', quote=True))
op.drop_table('L0',
schema=quoted_name('world_bank.bound_rates', quote=True))
op.drop_table('hawk_users', schema=quoted_name('public', quote=True))
op.drop_table('datafile_registry',
schema=quoted_name('operations', quote=True))
op.drop_index(
op.f('ix_ons_postcode_directory_L1_pcds'),
table_name='L1',
schema=quoted_name('ons.postcode_directory', quote=True),
)
op.drop_table('L1',
schema=quoted_name('ons.postcode_directory', quote=True))
op.drop_table('L1',
schema=quoted_name('dit.reference_postcodes', quote=True))
op.drop_table('L1',
schema=quoted_name('dit.eu_country_membership', quote=True))
op.drop_table('L1', schema=quoted_name('dit.baci', quote=True))
op.drop_table('L1',
schema=quoted_name('comtrade.country_code_and_iso',
quote=True))
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('pipeline', schema=quoted_name('public', quote=True))
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
'ultimate_end_user',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ultimate_end_user',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ultimate_end_user',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ultimate_end_user',
'country_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ultimate_end_user',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'country_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'status',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'licence_type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'el_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'created_datetime',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'returns',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ref_country_mapping',
'country_name',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ref_ars_subject',
'ars_subject',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ogl_type',
'title',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ogl_type',
'start_datetime',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'start_datetime',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'single_footnote_text',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'mf_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'footnote_type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'display_text',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'start_datetime',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'mf_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'country_name',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'country_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'torture_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'temporary_licence_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'status',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'report_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'other_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'mil_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'licence_conversion_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'incorporation_flag',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'case_type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'version_no',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'report_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'inc_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'goods_item_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'dest_country_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote_entry',
'status_control',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote_entry',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote_entry',
'ela_grp_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote_entry',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote',
'status',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'status',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'batch_ref',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'approve_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_country',
'start_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_country',
'report_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_country',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_amendment',
'case_type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_amendment',
'case_processing_time',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_amendment',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_amendment',
'amendment_closed_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application',
'initial_processing_time',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application',
'case_type',
existing_type=sa.TEXT(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application',
'case_closed_date',
existing_type=sa.DATE(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application',
'batch_id',
existing_type=sa.INTEGER(),
nullable=True,
schema=quoted_name('spire', quote=True),
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('L1',
'publication_date',
schema=quoted_name('ons.postcode_directory', quote=True))
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('cty_code', sa.Integer(), nullable=True),
sa.Column('cty_name_english', sa.Text(), nullable=True),
sa.Column('cty_fullname_english', sa.Text(), nullable=True),
sa.Column('cty_abbreviation', sa.Text(), nullable=True),
sa.Column('cty_comments', sa.Text(), nullable=True),
sa.Column('iso2_digit_alpha', sa.Text(), nullable=True),
sa.Column('iso3_digit_alpha', sa.Text(), nullable=True),
sa.Column('start_valid_year', sa.Text(), nullable=True),
sa.Column('end_valid_year', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('comtrade.country_code_and_iso', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('year', sa.Integer(), nullable=True),
sa.Column('product_category', sa.Integer(), nullable=True),
sa.Column('exporter', sa.Integer(), nullable=True),
sa.Column('importer', sa.Integer(), nullable=True),
sa.Column('trade_flow_value', sa.Numeric(), nullable=True),
sa.Column('quantity', sa.Numeric(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('dit.baci', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('country', sa.Text(), nullable=True),
sa.Column('iso3', sa.Text(), nullable=True),
sa.Column('year', sa.Integer(), nullable=True),
sa.Column('tariff_code', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('dit.eu_country_membership', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('postcode', sa.Text(), nullable=True),
sa.Column('local_authority_district_code', sa.Text(), nullable=True),
sa.Column('local_authority_district_name', sa.Text(), nullable=True),
sa.Column('local_enterprise_partnership_lep1_code',
sa.Text(),
nullable=True),
sa.Column('local_enterprise_partnership_lep1_name',
sa.Text(),
nullable=True),
sa.Column('local_enterprise_partnership_lep2_code',
sa.Text(),
nullable=True),
sa.Column('local_enterprise_partnership_lep2_name',
sa.Text(),
nullable=True),
sa.Column('region_code', sa.Text(), nullable=True),
sa.Column('region_name', sa.Text(), nullable=True),
sa.Column('national_grid_ref_easting', sa.Text(), nullable=True),
sa.Column('national_grid_ref_northing', sa.Text(), nullable=True),
sa.Column('date_of_introduction', sa.Date(), nullable=True),
sa.Column('date_of_termination', sa.Date(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('dit.reference_postcodes', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('pcd', sa.Text(), nullable=True),
sa.Column('pcd2', sa.Text(), nullable=True),
sa.Column('pcds', sa.Text(), nullable=True),
sa.Column('dointr', sa.Date(), nullable=True),
sa.Column('doterm', sa.Date(), nullable=True),
sa.Column('oscty', sa.Text(), nullable=True),
sa.Column('ced', sa.Text(), nullable=True),
sa.Column('oslaua', sa.Text(), nullable=True),
sa.Column('osward', sa.Text(), nullable=True),
sa.Column('parish', sa.Text(), nullable=True),
sa.Column('usertype', sa.Text(), nullable=True),
sa.Column('oseast1m', sa.Text(), nullable=True),
sa.Column('osnrth1m', sa.Text(), nullable=True),
sa.Column('osgrdind', sa.Text(), nullable=True),
sa.Column('oshlthau', sa.Text(), nullable=True),
sa.Column('nhser', sa.Text(), nullable=True),
sa.Column('ctry', sa.Text(), nullable=True),
sa.Column('rgn', sa.Text(), nullable=True),
sa.Column('streg', sa.Text(), nullable=True),
sa.Column('pcon', sa.Text(), nullable=True),
sa.Column('eer', sa.Text(), nullable=True),
sa.Column('teclec', sa.Text(), nullable=True),
sa.Column('ttwa', sa.Text(), nullable=True),
sa.Column('pct', sa.Text(), nullable=True),
sa.Column('nuts', sa.Text(), nullable=True),
sa.Column('statsward', sa.Text(), nullable=True),
sa.Column('oa01', sa.Text(), nullable=True),
sa.Column('casward', sa.Text(), nullable=True),
sa.Column('park', sa.Text(), nullable=True),
sa.Column('lsoa01', sa.Text(), nullable=True),
sa.Column('msoa01', sa.Text(), nullable=True),
sa.Column('ur01ind', sa.Text(), nullable=True),
sa.Column('oac01', sa.Text(), nullable=True),
sa.Column('oa11', sa.Text(), nullable=True),
sa.Column('lsoa11', sa.Text(), nullable=True),
sa.Column('msoa11', sa.Text(), nullable=True),
sa.Column('wz11', sa.Text(), nullable=True),
sa.Column('ccg', sa.Text(), nullable=True),
sa.Column('bua11', sa.Text(), nullable=True),
sa.Column('buasd11', sa.Text(), nullable=True),
sa.Column('ru11ind', sa.Text(), nullable=True),
sa.Column('oac11', sa.Text(), nullable=True),
sa.Column('lat', sa.Text(), nullable=True),
sa.Column('long', sa.Text(), nullable=True),
sa.Column('lep1', sa.Text(), nullable=True),
sa.Column('lep2', sa.Text(), nullable=True),
sa.Column('pfa', sa.Text(), nullable=True),
sa.Column('imd', sa.Text(), nullable=True),
sa.Column('calncv', sa.Text(), nullable=True),
sa.Column('stp', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('ons.postcode_directory', quote=True),
)
op.create_index(
op.f('ix_ons_postcode_directory_L1_pcds'),
'L1',
['pcds'],
unique=False,
schema=quoted_name('ons.postcode_directory', quote=True),
)
op.create_table(
'datafile_registry',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('source', sa.Text(), nullable=False),
sa.Column('file_name', sa.Text(), nullable=True),
sa.Column(
'state',
sa.Enum(
'processing',
'failed',
'processed',
'ignored',
name='processing_state',
schema=quoted_name('operations', quote=True),
inherit_schema=True,
),
nullable=False,
),
sa.Column('error_message', sa.Text(), nullable=True),
sa.Column('created_timestamp', sa.DateTime(), nullable=False),
sa.Column('updated_timestamp', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('operations', quote=True),
)
op.create_table(
'hawk_users',
sa.Column('id', sa.Text(), nullable=False),
sa.Column('key', sa.Text(), nullable=True),
sa.Column('scope', sa.ARRAY(sa.Text()), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('public', quote=True),
)
op.create_table(
'L0',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('datafile_created', sa.Text(), nullable=True),
sa.Column('datafile_updated', sa.Text(), nullable=True),
sa.Column('data_hash', sa.Text(), nullable=True),
sa.Column('nomen_code', sa.Text(), nullable=True),
sa.Column('reporter', sa.Integer(), nullable=True),
sa.Column('product', sa.Integer(), nullable=True),
sa.Column('bound_rate', sa.Numeric(), nullable=True),
sa.Column('total_number_of_lines', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_hash'),
schema=quoted_name('world_bank.bound_rates', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('reporter', sa.Integer(), nullable=True),
sa.Column('product', sa.Integer(), nullable=True),
sa.Column('bound_rate', sa.Numeric(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('world_bank.bound_rates', quote=True),
)
op.create_table(
'L0',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('datafile_created', sa.Text(), nullable=True),
sa.Column('reporter', sa.Integer(), nullable=True),
sa.Column('year', sa.Integer(), nullable=True),
sa.Column('product', sa.Integer(), nullable=True),
sa.Column('partner', sa.Integer(), nullable=True),
sa.Column('duty_type', sa.Text(), nullable=True),
sa.Column('simple_average', sa.Numeric(), nullable=True),
sa.Column('number_of_total_lines', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('world_bank.tariff', quote=True),
)
op.create_table(
'L1',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('data_source_row_id', sa.Integer(), nullable=True),
sa.Column('product', sa.Integer(), nullable=True),
sa.Column('reporter', sa.Integer(), nullable=True),
sa.Column('partner', sa.Integer(), nullable=True),
sa.Column('year', sa.Integer(), nullable=True),
sa.Column('assumed_tariff', sa.Numeric(), nullable=True),
sa.Column('app_rate', sa.Numeric(), nullable=True),
sa.Column('mfn_rate', sa.Numeric(), nullable=True),
sa.Column('bnd_rate', sa.Numeric(), nullable=True),
sa.Column('country_average', sa.Numeric(), nullable=True),
sa.Column('world_average', sa.Numeric(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('data_source_row_id'),
schema=quoted_name('world_bank.tariff', quote=True),
)
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'batch',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('batch_ref', sa.Text(), nullable=True),
sa.Column('status', sa.Text(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('end_date', sa.Date(), nullable=True),
sa.Column('approve_date', sa.Date(), nullable=True),
sa.Column('release_date', sa.Date(), nullable=True),
sa.Column('staging_date', sa.Date(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'country_group',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'end_user',
sa.Column('eu_id', sa.Integer(), nullable=False),
sa.Column('version_number', sa.Integer(), nullable=False),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('end_user_type', sa.Text(), nullable=True),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('end_user_count', sa.Integer(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('eu_id', 'version_number'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'footnote',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('text', sa.Text(), nullable=True),
sa.Column('status', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'media_footnote',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'media_footnote_country',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('mf_grp_id', sa.Integer(), nullable=True),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('country_name', sa.Text(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('start_datetime', sa.Date(), nullable=True),
sa.Column('end_datetime', sa.Date(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ogl_type',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=True),
sa.Column('start_datetime', sa.Date(), nullable=True),
sa.Column('end_datetime', sa.Date(), nullable=True),
sa.Column('display_order', sa.Integer(), nullable=True),
sa.Column('f680_flag', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ref_ars_subject',
sa.Column('ars_value', sa.Text(), nullable=False),
sa.Column('ars_subject', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('ars_value'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ref_country_mapping',
sa.Column('country_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('country_name', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('country_id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ref_do_not_report_value',
sa.Column('dnr_type', sa.Text(), nullable=False),
sa.Column('dnr_value', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('dnr_type', 'dnr_value'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ref_report_ratings',
sa.Column('rating', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('rating'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'application',
sa.Column('ela_grp_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('case_type', sa.Text(), nullable=True),
sa.Column('case_sub_type', sa.Text(), nullable=True),
sa.Column('initial_processing_time', sa.Integer(), nullable=True),
sa.Column('case_closed_date', sa.Date(), nullable=True),
sa.Column('withheld_status', sa.Text(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('ela_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.PrimaryKeyConstraint('ela_grp_id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'country_group_entry',
sa.Column('cg_id', sa.Integer(), nullable=False),
sa.Column('country_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['cg_id'],
['spire.country_group.id'],
),
sa.PrimaryKeyConstraint('cg_id', 'country_id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'media_footnote_detail',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('mf_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('start_datetime', sa.Date(), nullable=True),
sa.Column('end_datetime', sa.Date(), nullable=True),
sa.Column('footnote_type', sa.Text(), nullable=True),
sa.Column('display_text', sa.Text(), nullable=True),
sa.Column('single_footnote_text', sa.Text(), nullable=True),
sa.Column('joint_footnote_text', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['mf_id'],
['spire.media_footnote.id'],
),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'returns',
sa.Column('elr_id', sa.Integer(), nullable=False),
sa.Column('elr_version', sa.Integer(), nullable=False),
sa.Column('status', sa.Text(), nullable=True),
sa.Column('created_datetime', sa.Date(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('licence_type', sa.Text(), nullable=True),
sa.Column('el_id', sa.Integer(), nullable=True),
sa.Column('ogl_id', sa.Integer(), nullable=True),
sa.Column('return_period_date', sa.Date(), nullable=True),
sa.Column('end_country_id', sa.Integer(), nullable=True),
sa.Column('usage_count', sa.Integer(), nullable=True),
sa.Column('end_user_type', sa.Text(), nullable=True),
sa.Column('eco_comment', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.PrimaryKeyConstraint('elr_id', 'elr_version'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'application_amendment',
sa.Column('ela_grp_id', sa.Integer(), nullable=False),
sa.Column('ela_id', sa.Integer(), nullable=False),
sa.Column('case_type', sa.Text(), nullable=True),
sa.Column('case_sub_type', sa.Text(), nullable=True),
sa.Column('case_processing_time', sa.Integer(), nullable=True),
sa.Column('amendment_closed_date', sa.Date(), nullable=True),
sa.Column('withheld_status', sa.Text(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.PrimaryKeyConstraint('ela_grp_id', 'ela_id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'application_country',
sa.Column('ela_grp_id', sa.Integer(), nullable=False),
sa.Column('country_id', sa.Integer(), nullable=False),
sa.Column('report_date', sa.Date(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.PrimaryKeyConstraint('ela_grp_id', 'country_id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'footnote_entry',
sa.Column('fne_id', sa.Integer(), nullable=False),
sa.Column('fn_id', sa.Integer(), nullable=True),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('goods_item_id', sa.Integer(), nullable=True),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('fnr_id', sa.Integer(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('version_no', sa.Integer(), nullable=False),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('mfd_id', sa.Integer(), nullable=True),
sa.Column('mf_grp_id', sa.Integer(), nullable=True),
sa.Column('mf_free_text', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.ForeignKeyConstraint(
['fn_id'],
['spire.footnote.id'],
),
sa.ForeignKeyConstraint(
['mfd_id'],
['spire.media_footnote_detail.id'],
),
sa.PrimaryKeyConstraint('fne_id', 'version_no'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'goods_incident',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('inc_id', sa.Integer(), nullable=True),
sa.Column('type', sa.Text(), nullable=True),
sa.Column('goods_item_id', sa.Integer(), nullable=True),
sa.Column('dest_country_id', sa.Integer(), nullable=True),
sa.Column('source_country_grp_id', sa.Integer(), nullable=True),
sa.Column('report_date', sa.Date(), nullable=True),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('version_no', sa.Integer(), nullable=True),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.ForeignKeyConstraint(
['source_country_grp_id'],
['spire.country_group.id'],
),
sa.PrimaryKeyConstraint('id'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'incident',
sa.Column('inc_id', sa.Integer(), nullable=False),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('status', sa.Text(), nullable=True),
sa.Column('type', sa.Text(), nullable=True),
sa.Column('case_type', sa.Text(), nullable=True),
sa.Column('case_sub_type', sa.Text(), nullable=True),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('ela_id', sa.Integer(), nullable=True),
sa.Column('licence_id', sa.Integer(), nullable=True),
sa.Column('report_date', sa.Date(), nullable=True),
sa.Column('temporary_licence_flag', sa.Integer(), nullable=True),
sa.Column('licence_conversion_flag', sa.Integer(), nullable=True),
sa.Column('incorporation_flag', sa.Integer(), nullable=True),
sa.Column('mil_flag', sa.Integer(), nullable=True),
sa.Column('other_flag', sa.Integer(), nullable=True),
sa.Column('torture_flag', sa.Integer(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('version_no', sa.Integer(), nullable=False),
sa.Column('ogl_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('else_id', sa.Integer(), nullable=True),
sa.Column('stakeholders_confirmed', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.PrimaryKeyConstraint('inc_id', 'version_no'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'third_party',
sa.Column('tp_id', sa.Integer(), nullable=False),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('sh_id', sa.Integer(), nullable=True),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('ultimate_end_user_flag', sa.Integer(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('version_no', sa.Integer(), nullable=False),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.PrimaryKeyConstraint('tp_id', 'version_no'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ultimate_end_user',
sa.Column('ueu_id', sa.Integer(), nullable=False),
sa.Column('ela_grp_id', sa.Integer(), nullable=True),
sa.Column('country_id', sa.Integer(), nullable=True),
sa.Column('status_control', sa.Text(), nullable=True),
sa.Column('start_date', sa.Date(), nullable=True),
sa.Column('version_no', sa.Integer(), nullable=False),
sa.Column('batch_id', sa.Integer(), nullable=True),
sa.Column('sh_id', sa.Integer(), nullable=True),
sa.Column('ultimate_end_user_flag', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['batch_id'],
['spire.batch.id'],
),
sa.ForeignKeyConstraint(
['ela_grp_id'],
['spire.application.ela_grp_id'],
),
sa.PrimaryKeyConstraint('ueu_id', 'version_no'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'ars',
sa.Column('gi_id', sa.Integer(), nullable=False),
sa.Column('ars_value', sa.Text(), nullable=False),
sa.Column('ars_quantity', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['gi_id'],
['spire.goods_incident.id'],
),
sa.PrimaryKeyConstraint('gi_id', 'ars_value'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'control_entry',
sa.Column('gi_id', sa.Integer(), nullable=False),
sa.Column('rating', sa.Text(), nullable=False),
sa.Column('value', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['gi_id'],
['spire.goods_incident.id'],
),
sa.ForeignKeyConstraint(
['rating'],
['spire.ref_report_ratings.rating'],
),
sa.PrimaryKeyConstraint('gi_id', 'rating'),
schema=quoted_name('spire', quote=True),
)
op.create_table(
'reason_for_refusal',
sa.Column('gi_id', sa.Integer(), nullable=False),
sa.Column('reason_for_refusal', sa.Text(), nullable=False),
sa.ForeignKeyConstraint(
['gi_id'],
['spire.goods_incident.id'],
),
sa.PrimaryKeyConstraint('gi_id', 'reason_for_refusal'),
schema=quoted_name('spire', quote=True),
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('reason_for_refusal',
schema=quoted_name('spire', quote=True))
op.drop_table('control_entry', schema=quoted_name('spire', quote=True))
op.drop_table('ars', schema=quoted_name('spire', quote=True))
op.drop_table('ultimate_end_user', schema=quoted_name('spire', quote=True))
op.drop_table('third_party', schema=quoted_name('spire', quote=True))
op.drop_table('incident', schema=quoted_name('spire', quote=True))
op.drop_table('goods_incident', schema=quoted_name('spire', quote=True))
op.drop_table('footnote_entry', schema=quoted_name('spire', quote=True))
op.drop_table('application_country',
schema=quoted_name('spire', quote=True))
op.drop_table('application_amendment',
schema=quoted_name('spire', quote=True))
op.drop_table('returns', schema=quoted_name('spire', quote=True))
op.drop_table('media_footnote_detail',
schema=quoted_name('spire', quote=True))
op.drop_table('country_group_entry',
schema=quoted_name('spire', quote=True))
op.drop_table('application', schema=quoted_name('spire', quote=True))
op.drop_table('ref_report_ratings',
schema=quoted_name('spire', quote=True))
op.drop_table('ref_do_not_report_value',
schema=quoted_name('spire', quote=True))
op.drop_table('ref_country_mapping',
schema=quoted_name('spire', quote=True))
op.drop_table('ref_ars_subject', schema=quoted_name('spire', quote=True))
op.drop_table('ogl_type', schema=quoted_name('spire', quote=True))
op.drop_table('media_footnote_country',
schema=quoted_name('spire', quote=True))
op.drop_table('media_footnote', schema=quoted_name('spire', quote=True))
op.drop_table('footnote', schema=quoted_name('spire', quote=True))
op.drop_table('end_user', schema=quoted_name('spire', quote=True))
op.drop_table('country_group', schema=quoted_name('spire', quote=True))
op.drop_table('batch', schema=quoted_name('spire', quote=True))
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('ref_report_ratings', 'report_rating', schema=quoted_name('spire', quote=True))
def include_object(obj, name, type_, reflected, compare_to):
if getattr(obj, 'schema', None):
obj.schema = quoted_name(obj.schema, quote=True)
return True
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
'application',
'case_closed_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_amendment',
'amendment_closed_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_country',
'report_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'application_country',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'approve_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'end_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'release_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'staging_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'batch',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'end_user',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'footnote_entry',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'report_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'goods_incident',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'report_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'incident',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'end_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_country',
'start_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'end_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'media_footnote_detail',
'start_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ogl_type',
'end_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ogl_type',
'start_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'return',
'created_datetime',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'return',
'return_period_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=True,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'third_party',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
op.alter_column(
'ultimate_end_user',
'start_date',
existing_type=sa.DATE(),
type_=sa.DateTime(),
existing_nullable=False,
schema=quoted_name('spire', quote=True),
)
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(
'applications_check_1',
'applications',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'applications_check_2',
'applications',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'applications_check_3',
'applications',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint('batches_check_1',
'batches',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('batches_check_2',
'batches',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('footnotes_check',
'footnotes',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint(
'footnote_entries_check_1',
'footnote_entries',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'footnote_entries_check_2',
'footnote_entries',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'footnote_entries_check_3',
'footnote_entries',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'goods_incidents_check_1',
'goods_incidents',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'goods_incidents_check_2',
'goods_incidents',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint('incidents_check_1',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_2',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_3',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_4',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_5',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_6',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_7',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_8',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_9',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_10',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_11',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_12',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_13',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('incidents_check_14',
'incidents',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint(
'media_footnote_countries_check',
'media_footnote_countries',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'media_footnote_details_check_1',
'media_footnote_details',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'media_footnote_details_check_2',
'media_footnote_details',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint('returns_check_1',
'returns',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('returns_check_2',
'returns',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('returns_check_3',
'returns',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('returns_check_4',
'returns',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint('returns_check_5',
'returns',
type_='check',
schema=quoted_name('spire', quote=True))
op.drop_constraint(
'third_parties_check_1',
'third_parties',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'third_parties_check_2',
'third_parties',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'ultimate_end_users_check_1',
'ultimate_end_users',
type_='check',
schema=quoted_name('spire', quote=True),
)
op.drop_constraint(
'ultimate_end_users_check_2',
'ultimate_end_users',
type_='check',
schema=quoted_name('spire', quote=True),
)
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_check_constraint(
'applications_check_1',
'applications',
condition="""
case_type IN ('SIEL', 'OIEL', 'SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'applications_check_2',
'applications',
condition="""
(
case_type = 'SIEL' AND case_sub_type IN ('PERMANENT', 'TEMPORARY', 'TRANSHIPMENT')
)
OR
(
case_type = 'OIEL'
AND case_sub_type IN ('DEALER', 'MEDIA', 'MIL_DUAL', 'UKCONTSHELF','CRYPTO')
)
OR
(
case_type IN ('SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')
AND case_sub_type IS NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'applications_check_3',
'applications',
condition=
"withheld_status IS NULL OR withheld_status IN ('PENDING', 'WITHHELD')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'batches_check_1',
'batches',
condition="status IN ('RELEASED','STAGING')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'batches_check_2',
'batches',
condition="""
(
batch_ref LIKE 'C%' AND start_date IS NULL AND end_date IS NULL
)
OR
(
batch_ref NOT LIKE 'C%'
AND start_date IS NOT NULL
AND date_trunc('day', start_date) = start_date
AND end_date IS NOT NULL
AND date_trunc('day', end_date) = end_date
AND start_date <= end_date
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'footnotes_check',
'footnotes',
condition="status IN ('CURRENT', 'DELETED', 'ARCHIVED')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'footnote_entries_check_1',
'footnote_entries',
condition="""
(
goods_item_id IS NULL AND country_id IS NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NOT NULL AND country_id IS NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NULL AND country_id IS NOT NULL AND fnr_id IS NULL
)
OR
(
goods_item_id IS NULL AND country_id IS NOT NULL AND fnr_id IS NOT NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'footnote_entries_check_2',
'footnote_entries',
condition="version_no >= 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'footnote_entries_check_3',
'footnote_entries',
condition="""
(
fn_id IS NOT NULL AND mfd_id IS NULL AND mf_free_text IS NULL AND mf_grp_id IS NULL
)
OR
(
fn_id IS NULL
AND mfd_id IS NOT NULL
AND mf_free_text IS NULL
AND mf_grp_id IS NOT NULL
)
OR
(
fn_id IS NULL
AND mfd_id IS NULL
AND mf_free_text IS NOT NULL
AND mf_grp_id IS NOT NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'goods_incidents_check_1',
'goods_incidents',
condition="type IN ('REFUSAL', 'ISSUE', 'REVOKE', 'SURRENDER')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'goods_incidents_check_2',
'goods_incidents',
condition="version_no >= 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_1',
'incidents',
condition="status IN ('READY', 'FOR_ATTENTION')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_2',
'incidents',
condition="version_no >= 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_3',
'incidents',
condition="""
(
case_type != 'OGEL' AND ogl_id IS NULL
)
OR
(
case_type = 'OGEL' AND ogl_id IS NOT NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_4',
'incidents',
condition="temporary_licence_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_5',
'incidents',
condition="""
(
type != 'REFUSAL' AND licence_id IS NOT NULL
)
OR
(
type = 'REFUSAL' AND licence_id IS NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_6',
'incidents',
condition="""
(
type = 'SUSPENSION' AND else_id IS NOT NULL
)
OR
(
type != 'SUSPENSION' AND else_id IS NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_7',
'incidents',
condition="""
type IN (
'REFUSAL', 'ISSUE', 'REDUCTION', 'REVOKE', 'DEREGISTRATION',
'SUSPENSION', 'SURRENDER'
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_8',
'incidents',
condition="""
case_type IN (
'SIEL', 'OIEL', 'SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL'
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_9',
'incidents',
condition="""
(
case_type = 'SIEL' AND case_sub_type IN ('PERMANENT', 'TEMPORARY', 'TRANSHIPMENT')
)
OR
(
case_type = 'OIEL'
AND case_sub_type IN ('DEALER', 'MEDIA', 'MIL_DUAL', 'UKCONTSHELF','CRYPTO')
)
OR
(
case_type IN ('SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')
AND case_sub_type IS NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_10',
'incidents',
condition="licence_conversion_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_11',
'incidents',
condition="incorporation_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_12',
'incidents',
condition="mil_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_13',
'incidents',
condition="other_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'incidents_check_14',
'incidents',
condition="torture_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'media_footnote_countries_check',
'media_footnote_countries',
condition="""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'media_footnote_details_check_1',
'media_footnote_details',
condition="""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'media_footnote_details_check_2',
'media_footnote_details',
condition="footnote_type IN ('STANDARD','END_USER')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'returns_check_1',
'returns',
condition="elr_version > 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'returns_check_2',
'returns',
condition="status IN ('WITHDRAWN', 'ACTIVE')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'returns_check_3',
'returns',
condition="status_control IN ('A','P','C')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'returns_check_4',
'returns',
condition="licence_type IN ('OGEL','OIEL','OITCL')",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'returns_check_5',
'returns',
condition="""
(
licence_type = 'OGEL' AND ogl_id IS NOT NULL
)
OR
(
licence_type != 'OGEL' AND ogl_id IS NULL
)
""",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'third_parties_check_1',
'third_parties',
condition=
"ultimate_end_user_flag IS NULL OR ultimate_end_user_flag IN (0, 1)",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'third_parties_check_2',
'third_parties',
condition="version_no >= 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'ultimate_end_users_check_1',
'ultimate_end_users',
condition="version_no >= 0",
schema=quoted_name('spire', quote=True),
)
op.create_check_constraint(
'ultimate_end_users_check_2',
'ultimate_end_users',
condition="status_control IN ('A', 'P', 'C', 'D')",
schema=quoted_name('spire', quote=True),
)
