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)