class TextMultiItem(self.Base):
__tablename__ = 'textmultiitem'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
name = sa.Column(sa.Unicode(255))
content = sa.Column(sa.UnicodeText)
name_vector = sa.Column(TSVectorType('name'))
content_vector = sa.Column(TSVectorType('content'))
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 Plugin(Base):
__table_args__ = {"schema": "dispatch_core"}
id = Column(Integer, primary_key=True)
title = Column(String)
slug = Column(String, unique=True)
description = Column(String)
version = Column(String)
author = Column(String)
author_url = Column(String)
type = Column(String)
multiple = Column(Boolean)
search_vector = Column(
TSVectorType(
"title",
"slug",
"type",
"description",
weights={
"title": "A",
"slug": "B",
"type": "C",
"description": "C"
},
))
@property
def configuration_schema(self):
"""Renders the plugin's schema to JSON Schema."""
plugin = plugins.get(self.slug)
return plugin.configuration_schema.schema()
class TagType(Base, TimeStampMixin, ProjectMixin):
__table_args__ = (UniqueConstraint("name", "project_id"), )
id = Column(Integer, primary_key=True)
name = Column(String)
description = Column(String)
exclusive = Column(Boolean, default=False)
search_vector = Column(TSVectorType("name"))
class IndividualContact(Base, ContactMixin, ProjectMixin):
__table_args__ = (UniqueConstraint("email", "project_id"), )
id = Column(Integer, primary_key=True)
name = Column(String)
mobile_phone = Column(String)
office_phone = Column(String)
title = Column(String)
weblink = Column(String)
external_id = Column(String)
events = relationship("Event", backref="individual")
filters = relationship("SearchFilter",
secondary=assoc_individual_filters,
backref="individuals")
team_contact_id = Column(Integer, ForeignKey("team_contact.id"))
team_contact = relationship("TeamContact", backref="individuals")
search_vector = Column(
TSVectorType(
"name",
"title",
"company",
"notes",
weights={
"name": "A",
"title": "B",
"company": "C",
"notes": "D"
},
))
class IncidentType(ProjectMixin, Base):
__table_args__ = (UniqueConstraint("name", "project_id"),)
id = Column(Integer, primary_key=True)
name = Column(String)
slug = Column(String)
description = Column(String)
exclude_from_metrics = Column(Boolean, default=False)
default = Column(Boolean, default=False)
visibility = Column(String, default=Visibility.open.value)
plugin_metadata = Column(JSON, default=[])
template_document_id = Column(Integer, ForeignKey("document.id"))
template_document = relationship("Document")
commander_service_id = Column(Integer, ForeignKey("service.id"))
commander_service = relationship("Service", foreign_keys=[commander_service_id])
liaison_service_id = Column(Integer, ForeignKey("service.id"))
liaison_service = relationship("Service", foreign_keys=[liaison_service_id])
search_vector = Column(TSVectorType("name", "description"))
@hybrid_method
def get_meta(self, slug):
if not self.plugin_metadata:
return
for m in self.plugin_metadata:
if m["slug"] == slug:
return m
class Move(Base):
__tablename__ = 'moves'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, unique=True, nullable=False)
flavor_text = db.Column(db.Text, nullable=True, default=None)
short_effect = db.Column(db.Text, nullable=False)
effect = db.Column(db.Text, nullable=False)
damage_class = db.Column(db.Text, nullable=True, default=None)
power_points = db.Column(db.Integer, nullable=True, default=None)
power = db.Column(db.Integer, nullable=True, default=None)
accuracy = db.Column(db.Integer, nullable=True, default=None)
search_vector = db.Column(TSVectorType('name',
'flavor_text',
'short_effect',
'effect',
'damage_class'))
pokemon = db.relationship('Pokemon',
secondary=pokemon_moves,
back_populates='moves')
def __repr__(self):
return '<Move {}>'.format(self.name)
class CustomList(db.Model):
query_class = CustomListQUery
search_vector = db.Column(TSVectorType('name'))
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(64))
owner_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
items = relationship('CustomListItem',
cascade='all, delete-orphan',
backref='list')
creation_ts = db.Column(db.DateTime(), default=datetime.utcnow)
description = db.Column(db.String(256))
private = db.Column(db.Boolean(), default=False)
def get_list_details(self):
ret = {
'name': self.name,
'list_id': self.id,
'creation_ts': self.creation_ts,
'private': self.private,
'items': []
}
for item in self.items:
ret['items'].append({
'title': item.movie.title,
'ordering': item.ordering,
'notes': item.notes
})
ret['num_items'] = len(ret['items'])
return ret
class ItemInventoryEvent(Base, TimeStampMixin):
# columns
id = Column(Integer, primary_key=True)
uuid = Column(SQLAlchemyUUID(as_uuid=True), unique=True, nullable=False)
event_type = Column(String, nullable=False, default="Job")
source = Column(String, nullable=False)
description = Column(String, nullable=False)
details = Column(JSONType, nullable=True)
created_at = Column(DateTime, nullable=False)
ended_at = Column(DateTime, nullable=True)
# relationships
item_id = Column(Integer, nullable=False)
item_code = Column(String, nullable=True)
depot_id = Column(Integer, nullable=False)
depot_code = Column(String, nullable=True)
job_id = Column(Integer, nullable=True)
job_code = Column(String, nullable=True)
account_id = Column(Integer, nullable=True)
account_code = Column(String, nullable=True)
# full text search capabilities
search_vector = Column(
TSVectorType("source",
"description",
"item_code",
weights={
"source": "A",
"description": "B",
"item_code": "C"
}))
class Album(db.Model):
__tablename__ = "albums"
query_class = SearchQuery
id = db.Column(UUIDType, primary_key=True, default=uuid.uuid1)
title = db.Column(db.String)
tracks = db.Column(JSONType, default=list)
search_vector = db.Column(TSVectorType())
torrent_id = db.Column(db.String(40), db.ForeignKey("torrents.id"))
torrent = db.relationship("Torrent", back_populates="albums")
artist_id = db.Column(UUIDType, db.ForeignKey("artists.id"))
artist = db.relationship("Artist", back_populates="albums")
def update_search_vector(self):
words = [
self.title, self.artist.name,
([track.get("title", []),
track.get("artists", [])] for track in self.tracks)
]
text = " ".join(flatten(words))
self.search_vector = db.func.to_tsvector(text)
def __repr__(self):
return "<Album %s by %s>" % (self.title, self.artist.name)
class User(self.Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
name = sa.Column(sa.Unicode(255))
search_vector = sa.Column(TSVectorType('name'))
class Depot(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)
org_id = Column(Integer, nullable=True, default=0)
max_volume = Column(Integer, nullable=True, default=0)
max_weight = Column(Integer, nullable=True, default=0)
#
location = relationship("Location", backref="location_depot")
location_id = Column(BigInteger, ForeignKey("location.id"))
flex_form_data = Column(JSON, default={})
search_vector = Column(
TSVectorType(
"code",
"name",
"description",
weights={"email": "A", "full_name": "B"},
)
)
__table_args__ = (UniqueConstraint('code', 'org_id', name='uix_org_id_depot_code'),)
class IndividualContact(ContactMixin, Base):
id = Column(Integer, primary_key=True)
name = Column(String)
mobile_phone = Column(String)
office_phone = Column(String)
title = Column(String)
weblink = Column(String)
external_id = Column(String)
# this is a self referential relationship lets punt on this for now.
# relationship_owner_id = Column(Integer, ForeignKey("individual_contact.id"))
# relationship_owner = relationship("IndividualContact", backref="individual_contacts")
events = relationship("Event", backref="individual")
incident_types = relationship(
"IncidentType", secondary=assoc_individual_contact_incident_types, backref="individuals"
)
incident_priorities = relationship(
"IncidentPriority",
secondary=assoc_individual_contact_incident_priorities,
backref="individuals",
)
# participant = relationship("Participant", lazy="subquery", backref="individual")
team_contact_id = Column(Integer, ForeignKey("team_contact.id"))
team_contact = relationship("TeamContact", backref="individuals")
terms = relationship("Term", secondary=assoc_individual_contact_terms, backref="individuals")
search_vector = Column(
TSVectorType(
"name",
"title",
"company",
"notes",
weights={"name": "A", "title": "B", "company": "C", "notes": "D"},
)
)
class Team(Base, TimeStampMixin):
id = Column(Integer, primary_key=True)
code = Column(String, nullable=False)
org_id = Column(Integer, nullable=False, default=0)
name = Column(String)
description = Column(String)
# Two teams may share same service, leveraging same trained model and parameters
service_id = Column(Integer, ForeignKey("service.id"))
planner_service = relationship("Service")
flex_form_data = Column(
JSON,
default={"travel_speed_km_hour": 40, "travel_min_minutes": 10, "planning_working_days": 2},
)
latest_env_kafka_offset = Column(Integer, default=0)
latest_env_db_sink_offset = Column(Integer, default=0)
# I'm not sure this needs to be set explictly rather than via a query
jobs = relationship("Job", backref="teams")
search_vector = Column(
TSVectorType(
"code",
"name",
"description",
weights={
"code": "A",
"description": "B",
"name": "C",
},
)
)
# code = Column(String, nullable=False)
# 2020-08-09 13:18:47 , I have decided service->team. Each service belong to a team and in service, you can get_rl_planner_service_for_team_id (team.id). This is LRU cached instance. Maximum one RL service per team.
"""
class Plugin(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
slug = Column(String, unique=True)
description = Column(String)
version = Column(String)
author = Column(String)
author_url = Column(String)
type = Column(String)
required = Column(Boolean)
multiple = Column(Boolean)
workflows = relationship("Workflow", backref="plugin")
instances = relationship("PluginInstance", backref="plugin")
search_vector = Column(
TSVectorType(
"title",
"slug",
"type",
"description",
weights={
"title": "A",
"slug": "B",
"type": "C",
"description": "C"
},
))
class DispatchUser(Base, TimeStampMixin):
__table_args__ = {"schema": "dispatch_core"}
id = Column(Integer, primary_key=True)
email = Column(String, unique=True)
password = Column(LargeBinary, nullable=False)
search_vector = Column(TSVectorType("email", weights={"email": "A"}))
def check_password(self, password):
return bcrypt.checkpw(password.encode("utf-8"), self.password)
@property
def token(self):
now = datetime.utcnow()
exp = (now + timedelta(seconds=DISPATCH_JWT_EXP)).timestamp()
data = {
"exp": exp,
"email": self.email,
}
return jwt.encode(data,
DISPATCH_JWT_SECRET,
algorithm=DISPATCH_JWT_ALG)
def get_organization_role(self, organization_name):
"""Gets the users role for a given organization."""
for o in self.organizations:
if o.organization.name == organization_name:
return o.role
class Location(Base):
id = Column(Integer, primary_key=True)
location_code = Column(String, unique=True)
geo_longitude = Column(Float, nullable=False)
geo_latitude = Column(Float, nullable=False)
geo_address_text = Column(String)
geo_json = Column(JSON)
# LocationJobHistoryFeatures
# job_historical_worker_service_dict
job_history_feature_data = Column(JSON, default={})
job_count = Column(Integer, default=0)
# list_requested_worker_code = models.CharField(null=True, blank=True, max_length=4000)
avg_actual_start_minutes = Column(Float, default=0)
avg_actual_duration_minutes = Column(Float, default=0)
avg_days_delay = Column(Float, default=0)
stddev_days_delay = Column(Float, default=0)
search_vector = Column(
TSVectorType(
"location_code",
"geo_address_text",
weights={
"location_code": "A",
"geo_address_text": "B"
},
))
class Item(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)
org_id = Column(Integer, nullable=False, default=0)
# In meter cubical
volume = Column(Float, nullable=True, default=0.001)
# In KG
weight = Column(Float, nullable=True, default=1)
flex_form_data = Column(JSON, default={})
search_vector = Column(TSVectorType(
"code",
"name",
"description",
))
__table_args__ = (UniqueConstraint('code',
'org_id',
name='uix_org_id_item_code'), )
class Definition(Base, ProjectMixin):
id = Column(Integer, primary_key=True)
text = Column(String, unique=True)
source = Column(String, default="dispatch")
terms = relationship("Term", secondary=definition_terms, backref="definitions")
teams = relationship("TeamContact", secondary=definition_teams)
search_vector = Column(TSVectorType("text"))
class DispatchUser(Base, TimeStampMixin):
id = Column(Integer, primary_key=True)
email = Column(String, unique=True)
password = Column(Binary, nullable=False)
search_vector = Column(TSVectorType("email", weights={"email": "A"}))
def check_password(self, password):
return bcrypt.checkpw(password.encode("utf-8"), self.password)
@property
def token(self):
now = datetime.utcnow()
exp = (now + timedelta(seconds=DISPATCH_JWT_EXP)).timestamp()
data = {
"exp":
exp,
"email":
self.email,
"projects":
[UserProject.from_orm(p).dict() for p in self.projects],
"organizations":
[UserOrganization.from_orm(o).dict() for o in self.organizations],
}
return jwt.encode(data,
DISPATCH_JWT_SECRET,
algorithm=DISPATCH_JWT_ALG)
def get_project_role(self, project_name):
"""Gets the users role for a given project."""
for p in self.projects:
if p.name == project_name:
return p.role
class Project(Base):
id = Column(Integer, primary_key=True)
name = Column(String)
description = Column(String)
default = Column(Boolean, default=False)
color = Column(String)
annual_employee_cost = Column(Integer, default=50000)
business_year_hours = Column(Integer, default=2080)
owner_email = Column(String)
owner_conversation = Column(String)
organization_id = Column(Integer, ForeignKey(Organization.id))
organization = relationship("Organization")
@hybrid_property
def slug(self):
return slugify(self.name)
search_vector = Column(
TSVectorType("name",
"description",
weights={
"name": "A",
"description": "B"
}))
class IncidentType(Base):
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
slug = Column(String)
description = Column(String)
exclude_from_metrics = Column(Boolean, default=False)
default = Column(Boolean, default=False)
visibility = Column(String, default=Visibility.open)
plugin_metadata = Column(JSON, default=[])
template_document_id = Column(Integer, ForeignKey("document.id"))
template_document = relationship("Document")
commander_service_id = Column(Integer, ForeignKey("service.id"))
commander_service = relationship("Service")
search_vector = Column(TSVectorType("name", "description"))
@hybrid_method
def get_meta(self, slug):
if not self.plugin_metadata:
return
for m in self.plugin_metadata:
if m["slug"] == slug:
return m
class Pokemon(Base):
__tablename__ = 'pokemon'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, unique=True, nullable=False)
flavor_text = db.Column(db.Text, nullable=False)
habitat = db.Column(db.Text, nullable=True, default=None)
color = db.Column(db.Text, nullable=False)
shape = db.Column(db.Text, nullable=False)
search_vector = db.Column(TSVectorType('name',
'flavor_text',
'habitat',
'color',
'shape'))
pokedexes = db.relationship('Pokedex',
secondary=pokedex_pokemon,
back_populates='pokemon')
moves = db.relationship('Move',
secondary=pokemon_moves,
back_populates='pokemon')
def __repr__(self):
return '<Pokemon {}>'.format(self.name)
class Document(ProjectMixin, ResourceMixin, Base):
id = Column(Integer, primary_key=True)
name = Column(String)
description = Column(String)
report_id = Column(Integer, ForeignKey("report.id"))
incident_id = Column(Integer, ForeignKey("incident.id",
ondelete="CASCADE"))
incident_priorities = relationship(
"IncidentPriority",
secondary=assoc_document_incident_priorities,
backref="documents")
incident_types = relationship("IncidentType",
secondary=assoc_document_incident_types,
backref="documents")
terms = relationship("Term",
secondary=assoc_document_terms,
backref="documents")
evergreen = Column(Boolean)
evergreen_owner = Column(String)
evergreen_reminder_interval = Column(Integer, default=90) # number of days
evergreen_last_reminder_at = Column(DateTime)
search_vector = Column(TSVectorType("name"))
class Report(Base):
id = Column(Integer, primary_key=True)
created_at = Column(DateTime, default=datetime.utcnow)
details = Column(JSONType, nullable=True)
details_raw = Column(String, nullable=True)
type = Column(String,
nullable=False,
server_default=ReportTypes.tactical_report)
# relationships
incident_id = Column(Integer, ForeignKey("incident.id",
ondelete="CASCADE"))
participant_id = Column(Integer, ForeignKey("participant.id"))
document = relationship("Document", uselist=False, backref="report")
# full text search capabilities
search_vector = Column(TSVectorType("details_raw"))
@staticmethod
def _details_raw(mapper, connection, target):
target.details_raw = " ".join(target.details.values())
@classmethod
def __declare_last__(cls):
event.listen(cls, "before_update", cls._details_raw)
class Actor(db.Model):
id = db.Column(db.Integer(), primary_key=True) # 1-1 w/ api
query_class = ActorQuery
search_vector = db.Column(TSVectorType('name'))
biography = db.Column(db.Text())
birthday = db.Column(db.Date())
deathday = db.Column(db.Date())
homepage = db.Column(db.Text())
name = db.Column(db.Text())
place_of_birth = db.Column(db.Text())
profile_path = db.Column(db.Text())
imdb_id = db.Column(db.Text())
def get_actor_metadata(self):
ret = dict()
ret['biography'] = self.biography
ret['birthday'] = self.birthday
ret['deathday'] = self.deathday
ret['homepage'] = self.homepage
ret['name'] = self.name
ret['place_of_birth'] = self.place_of_birth
ret['profile_path'] = self.profile_path
ret['imdb_id'] = self.imdb_id
return ret
class Task(Base, ResourceMixin, TimeStampMixin):
id = Column(Integer, primary_key=True)
resolved_at = Column(DateTime)
resolve_by = Column(DateTime, default=default_resolution_time)
last_reminder_at = Column(DateTime)
creator = relationship("Participant", backref="created_tasks")
creator_id = Column(Integer, ForeignKey("participant.id"))
assignees = relationship("Participant",
secondary=assoc_task_assignees,
backref="assigned_tasks")
description = Column(String)
source = Column(String, default=TaskSource.incident)
priority = Column(String, default=TaskPriority.low)
status = Column(String, default=TaskStatus.open)
reminders = Column(Boolean, default=True)
incident_id = Column(Integer, ForeignKey("incident.id"))
search_vector = Column(TSVectorType("description"))
tickets = relationship("Ticket",
secondary=assoc_task_tickets,
backref="tasks")
@staticmethod
def _resolved_at(mapper, connection, target):
if target.status == TaskStatus.resolved:
target.resolved_at = datetime.utcnow()
@classmethod
def __declare_last__(cls):
event.listen(cls, "before_update", cls._resolved_at)
class Plugin(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
slug = Column(String, unique=True)
description = Column(String)
version = Column(String)
author = Column(String)
author_url = Column(String)
type = Column(String)
enabled = Column(Boolean)
required = Column(Boolean)
multiple = Column(Boolean)
config = Column(JSON)
config_form_spec = Column(
JSON) # Column(String, default='{"key_1":["skill_1"]}')
@property
def instance(self):
"""Fetches a plugin instance that matches this record."""
p = plugins.get(self.slug)
print("getting by slug", self.slug, p)
return plugins.get(self.slug)
search_vector = Column(TSVectorType("title", "slug", "type"))
class Plugin(Base):
__table_args__ = {"schema": "dispatch_core"}
id = Column(Integer, primary_key=True)
title = Column(String)
slug = Column(String, unique=True)
description = Column(String)
version = Column(String)
author = Column(String)
author_url = Column(String)
type = Column(String)
multiple = Column(Boolean)
search_vector = Column(
TSVectorType(
"title",
"slug",
"type",
"description",
weights={
"title": "A",
"slug": "B",
"type": "C",
"description": "C"
},
))
class Plugin(Base, ProjectMixin):
id = Column(Integer, primary_key=True)
title = Column(String)
slug = Column(String, unique=True)
description = Column(String)
version = Column(String)
author = Column(String)
author_url = Column(String)
type = Column(String)
enabled = Column(Boolean)
required = Column(Boolean)
multiple = Column(Boolean)
configuration = Column(JSONType)
workflows = relationship("Workflow", backref="plugin")
@property
def instance(self):
"""Fetches a plugin instance that matches this record."""
return plugins.get(self.slug)
search_vector = Column(
TSVectorType(
"title",
"slug",
"type",
"description",
weights={
"title": "A",
"slug": "B",
"type": "C",
"description": "C"
},
))