Untitled
unknown
plain_text
2 years ago
13 kB
13
Indexable
class Quote(Base):
__tablename__ = 'quote'
id = Column(UUIDType, primary_key=True)
eclipse_reference = Column(String(80), nullable=True)
insured_name = Column(String(80), nullable=True)
reinsured_name = Column(String(80), nullable=True)
broker_name = Column(String(80), nullable=True)
inception_date = Column(Date, nullable=True)
expiry_date = Column(Date, nullable=True)
policy_term = Column(Integer, nullable=True) # [days]
status = Column(String(80), nullable=True) # e.g. 'draft', 'quotable'
year_of_account = Column(String(4), nullable=True)
preceding_quote_id = Column(UUIDType, nullable=True) # if NOT NULL then the quote is a renewal
file_id = Column(String(80), nullable=True) # a unique identifier of the SoR file in the blob storage, maybe URL?
asset_quote = relationship("Asset", back_populates="quote_asset") # 1 quote - N assets
class Asset(Base):
__tablename__ = 'asset'
id = Column(UUIDType, primary_key=True)
loc_id = Column(Integer, nullable=True) # if NULL then asset has been input through UI, and not through SoV upload
quote_id = Column(UUIDType, ForeignKey('quote.id'), index=True) # Can't be NULL - an asset needs to be attached
# to a quote
address_id = Column(UUIDType, ForeignKey('address.id'), index=True)
# Potential split to a separate 'SoV' table could happen here, the relationship between Asset & SoV would be 1-1:
floor_area = Column(Float, nullable=True)
number_of_buildings = Column(Integer, nullable=True)
number_of_stories = Column(Integer, nullable=True)
year_built = Column(String(4), nullable=True) # ensure '0', '-', ' ' and '' are sanitised to NULL
occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
construction_type_id = Column(Integer, ForeignKey('construction_type.id'), index=True)
tiv_building = Column(Float, nullable=True)
tiv_contents = Column(Float, nullable=True)
tiv_business_interruption = Column(Float, nullable=True)
quote_asset = relationship("Quote", back_populates="asset_quote") # N assets - 1 quote
address_asset = relationship("Address", back_populates="asset_address") # N assets - 1 address
occupancy_asset = relationship("OccupancyType", back_populates="asset_occupancy") # N assets - 1 occ type
construction_asset = relationship("ConstructionType", back_populates="asset_construction") # N assets - 1 con type
class Address(Base):
__tablename__ = 'address'
id = Column(UUIDType, primary_key=True) # does this need to be a UUID, maybe an INT would be enough?
street_name = Column(String(80), nullable=True) # both street name and number, e.g. '122 Leadenhall Street'
postal_code = Column(String(20), nullable=True)
city_id = Column(Integer, ForeignKey('city.id'), index=True)
territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)
# e.g. county
territorial_entity_high_level_id = Column(Integer, ForeignKey('territorial_entity_high_level.id'), index=True)
# e.g. state, province
country_id = Column(Integer, ForeignKey('country.id'), index=True)
latitude = Column(String(80), nullable=True)
longitude = Column(String(80), nullable=True)
asset_address = relationship("Asset", back_populates="address_asset") # 1 address - N assets
city_address = relationship("City", back_populates="address_city") # N addresses - 1 city
territorial_entity_low_level_address = relationship("TerritorialEntityLowLevel",
back_populates="address_territorial_entity_low_level")
# N addresses - 1 entity
territorial_entity_high_level_address = relationship("TerritorialEntityHighLevel",
back_populates="address_territorial_entity_high_level")
# N addresses - 1 entity
country_address = relationship("Country", back_populates="address_country") # N addresses - 1 country
class OccupancyType(Base):
__tablename__ = 'occupancy_type'
id = Column(Integer, primary_key=True, index=True)
code = Column(String(20), nullable=False) # e.g. '420'
scheme = Column(String(20), nullable=True) # e.g. 'AIR'
category = Column(String(80), nullable=True) # e.g. 'Industrial Facilities Model'
description = Column(Text, nullable=True) # e.g. 'Electronic and other electrical equipment (except computer
# equipment)'
asset_occupancy = relationship("Asset", back_populates="occupancy_asset") # 1 occ type - N assets
quartile_occupancy = relationship("OccupancyCountryQuartile", back_populates="occupancy_quartile")
# 1 occ type - N q
quartile_construction_occupancy = relationship("OccupancyConstructionStateCountryQuartile", # 1 occ type - N q
back_populates="occupancy_construction_quartile")
class ConstructionType(Base):
__tablename__ = 'construction_type'
id = Column(Integer, primary_key=True, index=True)
code = Column(String(20), nullable=False) # e.g. '2274'
scheme = Column(String(20), nullable=False) # e.g. 'AIR'
category = Column(String(80), nullable=True) # e.g. 'Pipelines'
description_short = Column(String(80), nullable=True) # e.g. 'Underground Concrete Pipelines'
description_full = Column(Text, nullable=True) # e.g. 'Pipelines located under the surface of the ground and made
# of concrete material'
asset_construction = relationship("Asset", back_populates="construction_asset") # 1 con type - N assets
quartile_occupancy_construction = relationship("OccupancyConstructionStateCountryQuartile", # 1 con type - N q
back_populates="construction_occupancy_quartile")
class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True, index=True)
name = Column(String(80), nullable=False)
territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)
address_city = relationship("Address", back_populates="city_address") # 1 city - N addresses
territorial_entity_low_level_city = relationship("TerritorialEntityLowLevel", # N cities - 1 low level territory
back_populates="city_territorial_entity_low_level")
class TerritorialEntityLowLevel(Base):
__tablename__ = 'territorial_entity_low_level'
id = Column(Integer, primary_key=True, index=True)
name = Column(String(80), nullable=False)
territorial_entity_high_level_id = Column(Integer, ForeignKey('territorial_entity_high_level.id'), index=True)
address_territorial_entity_low_level = relationship("Address",
back_populates="territorial_entity_low_level_address")
# 1 low level territory - N addresses
city_territorial_entity_low_level = relationship("City", back_populates="territorial_entity_low_level_city")
# 1 low level territory - N cities
territorial_entity_high_low_level = relationship("TerritorialEntityHighLevel",
back_populates="territorial_entity_low_high_level")
# N low level territories - 1 high level territory
class TerritorialEntityHighLevel(Base):
__tablename__ = 'territorial_entity_high_level'
id = Column(Integer, primary_key=True, index=True)
code = Column(String(20), nullable=False)
name = Column(String(80), nullable=False)
country_id = Column(Integer, ForeignKey('country.country_id'), index=True)
address_territorial_entity_high_level = relationship("Address",
back_populates="territorial_entity_high_level_address")
# 1 high level territory - N addresses
territorial_entity_low_high_level = relationship("TerritorialEntityLowLevel",
back_populates="territorial_entity_high_low_level")
# 1 high level territory - N low level territories
country_territorial_entity_high_level = relationship("Country",
back_populates="territorial_entity_high_level_country")
# N high level territories - 1 country
quartile_country_state = relationship("OccupancyConstructionStateCountryQuartile",
back_populates="state_country_quartile")
# 1 high level territory - N q
class Country(Base):
__tablename__ = 'country'
id = Column(Integer, primary_key=True, index=True)
code = Column(String(10), nullable=True)
name = Column(String(80), nullable=False)
address_country = relationship("Address", back_populates="country_address") # 1 country - N addresses
territorial_entity_high_level_country = relationship("TerritorialEntityHighLevel",
back_populates="country_territorial_entity_high_level")
# N high level territories - 1 country
quartile_country = relationship("OccupancyCountryQuartile", back_populates="country_quartile") # 1 country - N q
quartile_state_country = relationship("OccupancyConstructionStateCountryQuartile",
back_populates="country_state_quartile") # 1 country - N q
class OccupancyCountryQuartile(Base):
__tablename__ = 'occupancy_country_quartile'
id = Column(Integer, primary_key=True, index=True)
country_id = Column(Integer, ForeignKey('country.id'), index=True)
occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
mean = Column(Float, nullable=True)
number = Column(Float, nullable=True)
q1 = Column(Float, nullable=True)
q2 = Column(Float, nullable=True)
q3 = Column(Float, nullable=True)
effective_from = Column(Date, nullable=True)
effective_to = Column(Date, nullable=True)
occupancy_quartile = relationship("OccupancyType", back_populates="quartile_occupancy") # N q - 1 occ type
country_quartile = relationship("Country", back_populates="quartile_country") # N q - 1 country
class OccupancyConstructionStateCountryQuartile(Base):
__tablename__ = 'occupancy_construction_state_country_quartile'
id = Column(Integer, primary_key=True, index=True)
occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
construction_type_id = Column(Integer, ForeignKey('construction_type.id'), index=True)
territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)
country_id = Column(Integer, ForeignKey('country.country_id'), index=True)
mean = Column(Float, nullable=True)
number = Column(Integer, nullable=True)
q1 = Column(Float, nullable=True)
q2 = Column(Float, nullable=True)
q3 = Column(Float, nullable=True)
effective_from = Column(Date, nullable=True)
effective_to = Column(Date, nullable=True)
occupancy_construction_quartile = relationship("OccupancyType", # N q - 1 occ type
back_populates="quartile_construction_occupancy")
construction_occupancy_quartile = relationship("ConstructionType", # N q - 1 con type
back_populates="quartile_occupancy_construction")
state_country_quartile = relationship("TerritorialEntityHighLevel",
back_populates="quartile_country_state") # N q - 1 high level territory
country_state_quartile = relationship("Country", back_populates="quartile_state_country") # N q - 1 country
Editor is loading...