class InteractionsAnalysedInteractionIdLog(BaseModel):
__tablename__ = 'interactions_analysed_interaction_id_log'
__table_args__ = {'schema': 'algorithm'}
datahub_interaction_id = _col(_uuid(as_uuid=True), primary_key=True)
analysed_at = _col(_dt)
class ETLStatus(BaseModel):
__tablename__ = 'etl_status'
__table_args__ = {'schema': 'public'}
id = _col(_int, primary_key=True)
status = _col(_text)
timestamp = _col(_dt)
class UserRole(BaseModel):
__tablename__ = 'users_roles'
__table_args__ = {'schema': 'admin'}
id = _col(_int(), primary_key=True, autoincrement=True)
user_id = _col(_int(), db.ForeignKey('admin.users.id'))
role_id = _col(_int(), db.ForeignKey('admin.roles.id'))
class Pipeline(BaseModel):
__tablename__ = 'pipeline'
__table_args__ = (_unique('organisation',
'dataset',
name='organisation_dataset_unique_together'), )
id = _col('id', _int, primary_key=True, autoincrement=True)
organisation = _col(_text, nullable=False)
dataset = _col(_text, nullable=False)
slug = _col(_text, nullable=False)
data_files = _relationship('PipelineDataFile',
order_by='desc(PipelineDataFile.processed_at)')
@staticmethod
def generate_slug(mapper, connection, target):
if not target.slug:
target.slug = slugify(f'{target.organisation} {target.dataset}')
@property
def pipeline_schema(self):
return f'{self.organisation}.{self.dataset}'
@property
def latest_version(self):
# data_files is always sorted by processed_at descending
for data_file in self.data_files:
if data_file.state == DataUploaderFileState.COMPLETED.value:
return data_file
return None
def __str__(self):
return self.pipeline_schema
class SPIRECountryGroupEntry(BaseModel):
__tablename__ = 'country_group_entries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
cg_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.country_groups.id'),
primary_key=True)
country_id = _col(_int, primary_key=True)
class SPIREReasonForRefusal(BaseModel):
__tablename__ = 'reasons_for_refusal'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
reason_for_refusal = _col(_text, primary_key=True)
class StandardisedCountries(BaseModel):
__tablename__ = 'standardised_countries'
__table_args__ = {'schema': 'algorithm'}
id = _col(_int, primary_key=True)
country = _col(_text)
standardised_country = _col(_text)
similarity = _col(_num)
class SPIREArs(BaseModel):
__tablename__ = 'ars'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
ars_value = _col(_text, primary_key=True)
ars_quantity = _col(_int)
class DatahubContact(BaseModel):
__tablename__ = 'datahub_contact'
__table_args__ = {'schema': 'public'}
id = _col(_int, primary_key=True, autoincrement=True)
datahub_contact_id = _col(_uuid(as_uuid=True), unique=True)
datahub_company_id = _col(_uuid(as_uuid=True))
email = _col(_text)
class SPIRERefReportRating(BaseModel):
__tablename__ = 'ref_report_ratings'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
rating = _col(_text, primary_key=True)
report_rating = _col(_text, nullable=False)
control_entries = _relationship('SPIREControlEntry',
backref='ref_report_rating')
class Role(BaseModel, RoleMixin):
__tablename__ = 'roles'
__table_args__ = {'schema': 'admin'}
id = _col(_int(), primary_key=True, autoincrement=True)
name = _col(_text(), unique=True)
description = _col(_text())
def __str__(self):
return self.name
class SPIREMediaFootnoteDetail(BaseModel):
__tablename__ = 'media_footnote_details'
id = _col(_int, primary_key=True)
mf_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.media_footnotes.id'),
nullable=False)
status_control = _col(_text)
start_datetime = _col(_dt, nullable=False)
end_datetime = _col(_dt)
footnote_type = _col(_text, nullable=False)
display_text = _col(_text, nullable=False)
single_footnote_text = _col(_text, nullable=False)
joint_footnote_text = _col(_text)
footnote_entries = _relationship('SPIREFootnoteEntry',
backref='media_footnote_detail')
__table_args__ = (
_check("""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
"""),
_check("footnote_type IN ('STANDARD','END_USER')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREThirdParty(BaseModel):
__tablename__ = 'third_parties'
tp_id = _col(_int, primary_key=True)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
sh_id = _col(_int)
country_id = _col(_int, nullable=False)
ultimate_end_user_flag = _col(_int)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
status_control = _col(_text, nullable=False)
__table_args__ = (
_check(
"ultimate_end_user_flag IS NULL OR ultimate_end_user_flag IN (0, 1)"
),
_check("version_no >= 0"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREUltimateEndUser(BaseModel):
__tablename__ = 'ultimate_end_users'
ueu_id = _col(_int, primary_key=True)
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
nullable=False)
country_id = _col(_int, nullable=False)
status_control = _col(_text, nullable=False)
start_date = _col(_dt, nullable=False)
version_no = _col(_int, primary_key=True)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
sh_id = _col(_int)
ultimate_end_user_flag = _col(_int)
__table_args__ = (
_check("version_no >= 0"),
_check("status_control IN ('A', 'P', 'C', 'D')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREControlEntry(BaseModel):
__tablename__ = 'control_entries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
gi_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.goods_incidents.id'),
primary_key=True)
rating = _col(
_text,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.ref_report_ratings.rating'),
primary_key=True)
value = _col(_decimal)
class WorldBankBoundRateL1(BaseModel):
"""
World bank bound rates
"""
__tablename__ = 'L1'
__table_args__ = {'schema': WorldBankBoundRatesPipeline.schema}
id = _col(_int, primary_key=True, autoincrement=True)
data_source_row_id = _col(_int, unique=True)
reporter = _col(_int, index=True)
product = _col(_int, index=True)
bound_rate = _col(_decimal)
class SPIREMediaFootnoteCountry(BaseModel):
__tablename__ = 'media_footnote_countries'
id = _col(_int, primary_key=True)
ela_grp_id = _col(_int, nullable=False)
mf_grp_id = _col(_int, nullable=False)
country_id = _col(_int, nullable=False)
country_name = _col(_text, nullable=False)
status_control = _col(_text)
start_datetime = _col(_dt, nullable=False)
end_datetime = _col(_dt)
__table_args__ = (
_check("""
(
status_control = 'C' AND end_datetime IS NULL
)
OR
(
status_control IS NULL AND end_datetime IS NOT NULL
)
"""),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREFootnote(BaseModel):
__tablename__ = 'footnotes'
id = _col(_int, primary_key=True)
text = _col(_text)
status = _col(_text, nullable=False)
footnote_entries = _relationship('SPIREFootnoteEntry', backref='footnote')
__table_args__ = (
_check("status IN ('CURRENT', 'DELETED', 'ARCHIVED')"),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class SPIREApplicationCountry(BaseModel):
__tablename__ = 'application_countries'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
ela_grp_id = _col(
_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.applications.ela_grp_id'),
primary_key=True)
country_id = _col(_int, primary_key=True)
report_date = _col(_dt, nullable=False)
start_date = _col(_dt, nullable=False)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
class CountriesAndSectorsInterestMatched(BaseModel):
__tablename__ = "countries_and_sectors_interest_matched"
id = _col(_int, primary_key=True, autoincrement=True)
service_company_id = _col(_text)
company_match_id = _col(_int, index=True)
country = _col(_text)
sector = _col(_text)
type = _col(_text, index=True)
service = _col(_text, index=True)
source = _col(_text, index=True)
source_id = _col(_text)
timestamp = _col(_dt)
__table_args__ = ({'schema': 'public'}, )
class SPIREMediaFootnote(BaseModel):
__tablename__ = 'media_footnotes'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
id = _col(_int, primary_key=True)
media_footnote_details = _relationship('SPIREMediaFootnoteDetail',
backref='media_footnote')
class SPIREEndUser(BaseModel):
__tablename__ = 'end_users'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
eu_id = _col(_int, primary_key=True)
version_number = _col(_int, primary_key=True)
ela_grp_id = _col(_int)
end_user_type = _col(_text)
country_id = _col(_int)
end_user_count = _col(_int)
start_date = _col(_dt)
status_control = _col(_text)
batch_id = _col(_int)
class ExportWins(BaseModel):
__tablename__ = 'export_wins'
__table_args__ = {'schema': 'public'}
id = _col(_int, primary_key=True, autoincrement=True)
export_wins_id = _col(_uuid(as_uuid=True), unique=True)
company_name = _col(_text)
export_wins_company_id = _col(_text)
contact_email_address = _col(_text)
created_on = _col(_dt)
sector = _col(_text)
country = _col(_text)
date_won = _col(_date)
class SPIRECountryGroup(BaseModel):
__tablename__ = 'country_groups'
__table_args__ = {'schema': SPIRE_SCHEMA_NAME}
id = _col(_int, primary_key=True, autoincrement=True)
country_group_entries = _relationship('SPIRECountryGroupEntry',
backref='country_group')
goods_incidents = _relationship('SPIREGoodsIncident',
backref='country_group')
class SPIREApplication(BaseModel):
__tablename__ = 'applications'
ela_grp_id = _col(_int, primary_key=True, autoincrement=True)
case_type = _col(_text, nullable=False)
case_sub_type = _col(_text)
initial_processing_time = _col(_int, nullable=False)
case_closed_date = _col(_dt, nullable=False)
withheld_status = _col(_text)
batch_id = _col(_int,
_foreign_key(f'{SPIRE_SCHEMA_NAME}.batches.id'),
nullable=False)
ela_id = _col(_int)
application_countries = _relationship('SPIREApplicationCountry',
backref='application')
application_amendments = _relationship('SPIREApplicationAmendment',
backref='application')
goods_incidents = _relationship('SPIREGoodsIncident',
backref='application')
footnote_entries = _relationship('SPIREFootnoteEntry',
backref='application')
incidents = _relationship('SPIREIncident', backref='application')
third_parties = _relationship('SPIREThirdParty', backref='application')
ultimate_end_users = _relationship('SPIREUltimateEndUser',
backref='application')
__table_args__ = (
_check(
"case_type IN ('SIEL', 'OIEL', 'SITCL', 'OITCL', 'OGEL', 'GPL', 'TA_SIEL', 'TA_OIEL')"
),
_check("""
(
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
)
"""),
_check(
"withheld_status IS NULL OR withheld_status IN ('PENDING', 'WITHHELD')"
),
{
'schema': SPIRE_SCHEMA_NAME
},
)
class DatahubCompanyExportCountry(BaseModel):
__tablename__ = 'datahub_company_export_country'
__table_args__ = {'schema': 'public'}
id = _col(_int, primary_key=True, autoincrement=True)
company_export_country_id = _col(_uuid(as_uuid=True))
company_id = _col(_uuid(as_uuid=True))
country = _col(_text)
country_iso_alpha2_code = _col(_text)
created_on = _col(_dt)
modified_on = _col(_dt)
status = _col(_text)
class DatahubCompany(BaseModel):
__tablename__ = 'datahub_company'
__table_args__ = {'schema': 'public'}
id = _col(_int, primary_key=True, autoincrement=True)
company_name = _col(_text)
datahub_company_id = _col(_uuid(as_uuid=True), unique=True)
companies_house_id = _col(_text)
sector = _col(_text)
reference_code = _col(_text)
postcode = _col(_text)
modified_on = _col(_dt)
class InteractionsAnalysed(BaseModel):
__tablename__ = 'interactions_analysed'
__table_args__ = {'schema': 'algorithm'}
id = _col(_int, primary_key=True, autoincrement=True)
datahub_interaction_id = _col(_uuid(as_uuid=True))
place = _col(_text, nullable=False)
standardized_place = _col(_text)
action = _col(_text)
type = _col(_text)
context = _col(_sa.ARRAY(_text))
negation = _col(_bool)
class DITBACIL1(BaseModel):
__tablename__ = 'L1'
__table_args__ = {'schema': DITBACIPipeline.schema}
id = _col(_int, primary_key=True, autoincrement=True)
data_source_row_id = _col(_int, unique=True)
year = _col(_int)
product_category = _col(_int)
exporter = _col(_int)
importer = _col(_int)
trade_flow_value = _col(_decimal)
quantity = _col(_decimal)
class WorldBankTariffL0(BaseModel):
"""
Raw world bank tariff data
"""
__tablename__ = 'L0'
__table_args__ = {'schema': WorldBankTariffTransformPipeline.schema}
id = _col(_int, primary_key=True, autoincrement=True)
datafile_created = _col(_text)
reporter = _col(_int)
year = _col(_int)
product = _col(_int)
partner = _col(_int)
duty_type = _col(_text)
simple_average = _col(_decimal)
number_of_total_lines = _col(_int)
