class ReportTableDefinition(JusticeCountsBase):
"""The definition for what a table within a report describes."""
__tablename__ = "report_table_definition"
id = Column(Integer, autoincrement=True)
system = Column(Enum(System))
metric_type = Column(Enum(MetricType))
measurement_type = Column(Enum(MeasurementType))
# Any dimensions where the data in the table only accounts for a subset of values for that dimension. For instance,
# a table for the population metric may only cover data for the prison population, not those on parole or
# probation. In that case filters would include a filter on population type.
filtered_dimensions = Column(ARRAY(String(255)))
# The value for each dimension from the above array.
filtered_dimension_values = Column(ARRAY(String(255)))
# The dimensions that the metric is broken down by in the report table. Each cell in a table instance has a unique
# combination of values for the aggregated dimensions. Dimensions are sorted deterministically within the array.
aggregated_dimensions = Column(ARRAY(String(255)))
__table_args__ = tuple(
[
PrimaryKeyConstraint(id),
UniqueConstraint(
metric_type,
measurement_type,
filtered_dimensions,
filtered_dimension_values,
aggregated_dimensions,
),
]
)
class Array(fixtures.ComparableEntity, Base):
__tablename__ = "array"
id = Column(sa.Integer, primary_key=True,
test_needs_autoincrement=True)
array = Column(ARRAY(Integer), default=[])
array0 = Column(ARRAY(Integer, zero_indexes=True), default=[])
first = index_property('array', 0)
first0 = index_property('array0', 0, onebased=False)
class Worker(TimeStampMixin, Base):
id = Column(BigInteger, primary_key=True)
code = Column(String, nullable=False,)
name = Column(String)
description = Column(String) # , nullable=False
is_active = Column(Boolean, default=True)
team_id = Column(Integer, ForeignKey("team.id"))
team = relationship("Team", backref="workers")
org_id = Column(Integer, nullable=True, default=0)
# Kandbox
location = relationship("Location", backref="location_worker")
location_id = Column(BigInteger, ForeignKey("location.id"))
flex_form_data = Column(JSON, default={})
business_hour = Column(JSON, default=DEFAULT_BUSINESS_HOUR)
# material part product asset item
# auth_username = Column(String) # map to column email in auth.user table
dispatch_user_id = Column(Integer, ForeignKey("dispatch_core.dispatch_user.id"))
dispatch_user = relationship("DispatchUser", backref="worker_auth")
job_history_feature_data = Column(JSON, default={})
# belongs to Workder+Location_affinity
# served_location_gmm=models.CharField(max_length=2000, null=True, blank=True) # [1,2,'termite']
# this is a self referential relationship lets punt on this for now.
# relationship_owner_id = Column(Integer, ForeignKey("worker.id"))
# relationship_owner = relationship("Worker", backref="workers")
events = relationship("Event", backref="worker")
# skills should be independent, basic information, outside of flex_form.
skills = Column(ARRAY(String))
# Only used for item/material based dispatching. Keep null for others.
loaded_items = Column(ARRAY(String))
search_vector = Column(
TSVectorType(
"code",
"name",
"description",
search_vector=Column(
TSVectorType("code", "name", "description",
weights={"code": "A", "name": "B", "description": "C"})
)
)
)
__table_args__ = (UniqueConstraint('code', 'org_id', name='uix_org_id_worker_code'),)
class A(Base):
__tablename__ = "a"
id = Column("id", Integer, primary_key=True)
array = Column("_array", ARRAY(Integer), default=[])
first = index_property("array", 0)
fifth = index_property("array", 4)
class Podcast(Audio):
"""
Model ORM class for podcast audio type
"""
__tablename__ = "podcasts"
host = Column(String(100), nullable=False)
participants = Column(ARRAY(String(100)))
class Goal(Base):
__tablename__ = "goals"
id = Column(Integer, primary_key=True, index=True)
title = Column(String, index=True)
description = Column(String, index=True)
userId = Column(Integer, ForeignKey("users.id"))
isActive = Column(Boolean, default=True)
attachmentIDs = Column(ARRAY(String))
owner = relationship("User", back_populates="goals")
tasks = relationship("Todo", back_populates="goal")
class AnimalSighting(Base):
__tablename__ = "animal_sightings"
id: UUID
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
location: str
location = Column(String)
coords: Geography
coords = Column(Geography("POINT", srid=4326))
images: typing.List[str]
images = Column(ARRAY(String))
animals: typing.List[str]
animals = Column(ARRAY(String))
poster = str
poster = Column(String)
timestamp = DateTime
timestamp = Column(DateTime(timezone=True), server_default=func.now())
def as_dict(self):
return {
c.name: str(getattr(self, c.name))
for c in self.__table__.columns
}
class Cell(JusticeCountsBase):
"""A single data point within a table.
"""
__tablename__ = 'cell'
id = Column(Integer, autoincrement=True)
report_table_instance_id = Column(Integer, nullable=False)
aggregated_dimension_values = Column(ARRAY(String(255)), nullable=False)
value = Column(Numeric, nullable=False)
__table_args__ = tuple([
PrimaryKeyConstraint(id),
UniqueConstraint(report_table_instance_id, aggregated_dimension_values),
ForeignKeyConstraint([report_table_instance_id], [ReportTableInstance.id])])
report_table_instance = relationship(ReportTableInstance)
class Mixin(object):
data = Column(MutableList.as_mutable(ARRAY(Integer)))
class Job(Base, TimeStampMixin):
id = Column(BigInteger, primary_key=True)
code = Column(String, nullable=False) # code
job_type = Column(String, nullable=False, default=JobType.JOB) # JobType
name = Column(String)
description = Column(String) # , nullable=False
planning_status = Column(String,
nullable=False,
default=JobPlanningStatus.UNPLANNED)
life_cycle_status = Column(String,
nullable=False,
default=JobLifeCycleStatus.CREATED)
auto_planning = Column(Boolean, default=True)
is_active = Column(Boolean, default=True) # job vs absence
team_id = Column(Integer, ForeignKey("team.id"), nullable=False)
team = relationship("Team", backref="job_to_team_id")
org_id = Column(Integer, nullable=True, default=0)
flex_form_data = Column(
JSON, default={"job_schedule_type":
"N"}) # Column(String, default='{"key_1":["skill_1"]}')
# Reserved for customer usage. Not visible to planner and workers.
# cust_flex_form = Column( JSON, default={ } )
# requested_worker_code = models.ForeignKey(Worker, null=True, on_delete=models.DO_NOTHING)
requested_start_datetime = Column(DateTime) # null=True, blank=True,
requested_duration_minutes = Column(Float)
requested_primary_worker_id = Column(BigInteger, ForeignKey("worker.id"))
# https://docs.sqlalchemy.org/en/13/orm/join_conditions.html
# foreign_keys is needed
requested_primary_worker = relationship(
"Worker",
backref="incident_requested",
foreign_keys=[requested_primary_worker_id],
)
# requested_secondary_worker are in participant, with "requested_secondary"
# This is more oriented to customer site time span, which may request location owner (customer)'s attention.
# Each participant's time should better be in participant table.
scheduled_start_datetime = Column(DateTime)
scheduled_duration_minutes = Column(Float)
scheduled_primary_worker_id = Column(BigInteger, ForeignKey("worker.id"))
scheduled_primary_worker = relationship(
"Worker",
backref="incident_scheduled",
foreign_keys=[scheduled_primary_worker_id],
)
# appointment = relationship("Appointment", backref="included_jobs")
# appointment_id = Column(Integer, ForeignKey("appointment.id"))
location = relationship("Location", backref="job_loc")
location_id = Column(BigInteger, ForeignKey("location.id"))
search_vector = Column(
TSVectorType(
"code",
"description",
"name",
weights={
"code": "A",
"name": "B",
"description": "C"
},
))
events = relationship("Event", backref="job")
tags = relationship("Tag", secondary=assoc_job_tags, backref="job_tag")
scheduled_secondary_workers = relationship(
"Worker",
secondary=job_scheduled_secondary_workers,
backref="job_scheduled_secondary_workers_rel",
)
requested_skills = Column(ARRAY(String))
requested_items = Column(ARRAY(String))
__table_args__ = (UniqueConstraint('code', 'org_id',
name='uix_org_code'), )
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
array = Column(ARRAY(Integer))
first = index_property('array', 1, mutable=False)
# TODO: support parametric timestamps (https://github.com/trinodb/trino-python-client/issues/107)
}
@pytest.mark.parametrize(
"type_str, sql_type",
parse_type_options_testcases.items(),
ids=parse_type_options_testcases.keys(),
)
def test_parse_type_options(type_str: str, sql_type: TypeEngine, assert_sqltype):
actual_type = datatype.parse_sqltype(type_str)
assert_sqltype(actual_type, sql_type)
parse_array_testcases = {
"array(integer)": ARRAY(INTEGER()),
"array(varchar(10))": ARRAY(VARCHAR(10)),
"array(decimal(20,3))": ARRAY(DECIMAL(20, 3)),
"array(array(varchar(10)))": ARRAY(VARCHAR(10), dimensions=2),
"array(map(char, integer))": ARRAY(MAP(CHAR(), INTEGER())),
"array(row(a integer, b varchar))": ARRAY(ROW([("a", INTEGER()), ("b", VARCHAR())])),
}
@pytest.mark.parametrize(
"type_str, sql_type",
parse_array_testcases.items(),
ids=parse_array_testcases.keys(),
)
def test_parse_array(type_str: str, sql_type: ARRAY, assert_sqltype):
actual_type = datatype.parse_sqltype(type_str)
class A(Base):
__tablename__ = 'a'
id = Column('id', Integer, primary_key=True)
array = Column('_array', ARRAY(Integer), default=[])
first = index_property('array', 0)
fifth = index_property('array', 4)