class WorkQueue(db.Model):
"""Represents a single item of work to do in a specific queue.
Queries:
- By task_id for finishing a task or extending a lease.
- By Index(queue_name, status, eta) for finding the oldest task for a queue
that is still pending.
- By Index(status, create) for finding old tasks that should be deleted
from the table periodically to free up space.
"""
CANCELED = 'canceled'
DONE = 'done'
ERROR = 'error'
LIVE = 'live'
STATES = frozenset([CANCELED, DONE, ERROR, LIVE])
task_id = db.Column(db.String(100), primary_key=True, nullable=False)
queue_name = db.Column(db.String(100), primary_key=True, nullable=False)
status = db.Column(db.Enum(*STATES), default=LIVE, nullable=False)
eta = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
nullable=False)
build_id = db.Column(db.Integer, db.ForeignKey('build.id'))
release_id = db.Column(db.Integer, db.ForeignKey('release.id'))
run_id = db.Column(db.Integer, db.ForeignKey('run.id'))
source = db.Column(db.String(500))
created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
finished = db.Column(db.DateTime)
lease_attempts = db.Column(db.Integer, default=0, nullable=False)
last_lease = db.Column(db.DateTime)
last_owner = db.Column(db.String(500))
heartbeat = db.Column(db.Text)
heartbeat_number = db.Column(db.Integer)
payload = db.Column(db.LargeBinary)
content_type = db.Column(db.String(100))
__table_args__ = (
db.Index('created_index', 'queue_name', 'status', 'created'),
db.Index('lease_index', 'queue_name', 'status', 'eta'),
db.Index('reap_index', 'status', 'created'),
)
@property
def lease_outstanding(self):
if not self.status == WorkQueue.LIVE:
return False
if not self.last_owner:
return False
now = datetime.datetime.utcnow()
return now < self.eta
class TaskHistory(db.Model):
""" Describes the history associated with a task """
__tablename__ = "task_history"
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id'),
index=True)
task_id = db.Column(db.Integer, nullable=False)
action = db.Column(db.String, nullable=False)
action_text = db.Column(db.String)
action_date = db.Column(db.DateTime, nullable=False, default=timestamp)
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id', name='fk_users'),
nullable=False)
actioned_by = db.relationship(User)
__table_args__ = (db.ForeignKeyConstraint([task_id, project_id],
['tasks.id', 'tasks.project_id'],
name='fk_tasks'),
db.Index('idx_task_history_composite', 'task_id',
'project_id'), {})
def __init__(self, task_id, project_id, user_id):
self.task_id = task_id
self.project_id = project_id
self.user_id = user_id
def set_task_locked_action(self, task_action: TaskAction):
if task_action not in [
TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION
]:
raise ValueError('Invalid Action')
self.action = task_action.name
def set_comment_action(self, comment):
self.action = TaskAction.COMMENT.name
self.action_text = comment
def set_state_change_action(self, new_state):
self.action = TaskAction.STATE_CHANGE.name
self.action_text = new_state.name
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def update_task_locked_with_duration(task_id, project_id, lock_action):
"""
Calculates the duration a task was locked for and sets it on the history record
:param task_id: Task in scope
:param project_id: Project ID in scope
:param lock_action: The lock action, either Mapping or Validation
:return:
"""
last_locked = TaskHistory.query.filter_by(task_id=task_id,
project_id=project_id,
action=lock_action.name,
action_text=None).one()
duration_task_locked = datetime.datetime.utcnow(
) - last_locked.action_date
# Cast duration to isoformat for later transmission via api
last_locked.action_text = (datetime.datetime.min +
duration_task_locked).time().isoformat()
db.session.commit()
@staticmethod
def get_all_comments(project_id: int) -> ProjectCommentsDTO:
""" Gets all comments for the supplied project_id"""
comments = db.session.query(TaskHistory.action_date,
TaskHistory.action_text,
User.username) \
.join(User) \
.filter(TaskHistory.project_id == project_id, TaskHistory.action == TaskAction.COMMENT.name).all()
comment_list = []
for comment in comments:
dto = ProjectComment()
dto.comment = comment.action_text
dto.comment_date = comment.action_date
dto.user_name = comment.username
comment_list.append(dto)
comments_dto = ProjectCommentsDTO()
comments_dto.comments = comment_list
return comments_dto
@staticmethod
def get_last_status(project_id: int, task_id: int):
""" Get the status the task was set to the last time the task had a STATUS_CHANGE"""
result = db.session.query(TaskHistory.action_text) \
.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id,
TaskHistory.action == TaskAction.STATE_CHANGE.name) \
.order_by(TaskHistory.action_date.desc()).first()
if result == None:
return TaskStatus.READY
return TaskStatus[result[0]]
@staticmethod
def get_last_action(project_id: int, task_id: int):
"""Gets the most recent task history record for the task"""
return TaskHistory.query.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id) \
.order_by(TaskHistory.action_date.desc()).first()
class ProjectInfo(db.Model):
""" Contains all project info localized into supported languages """
__tablename__ = 'project_info'
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id'),
primary_key=True)
locale = db.Column(db.String(10), primary_key=True)
name = db.Column(db.String(512))
short_description = db.Column(db.String)
description = db.Column(db.String)
instructions = db.Column(db.String)
project_id_str = db.Column(db.String)
text_searchable = db.Column(
TSVECTOR
) # This contains searchable text and is populated by a DB Trigger
__table_args__ = (db.Index('idx_project_info composite', 'locale',
'project_id'), {})
@classmethod
def create_from_name(cls, name: str):
""" Creates a new ProjectInfo class from name, used when creating draft projects """
new_info = cls()
new_info.locale = 'en' # Draft project default to english, PMs can change this prior to publication
new_info.name = name
return new_info
@classmethod
def create_from_dto(cls, dto: ProjectInfoDTO):
""" Creates a new ProjectInfo class from dto, used from project edit """
new_info = cls()
new_info.update_from_dto(dto)
return new_info
def update_from_dto(self, dto: ProjectInfoDTO):
""" Updates existing ProjectInfo from supplied DTO """
self.locale = dto.locale
self.name = dto.name
self.project_id_str = str(
self.project_id) # Allows project_id to be searched
# TODO bleach input
self.short_description = dto.short_description
self.description = dto.description
self.instructions = dto.instructions
@staticmethod
def get_dto_for_locale(project_id,
locale,
default_locale='en') -> ProjectInfoDTO:
"""
Gets the projectInfoDTO for the project for the requested locale. If not found, then the default locale is used
:param project_id: ProjectID in scope
:param locale: locale requested by user
:param default_locale: default locale of project
:raises: ValueError if no info found for Default Locale
"""
project_info = ProjectInfo.query.filter_by(
project_id=project_id, locale=locale).one_or_none()
if project_info is None:
# If project is none, get default locale and don't worry about empty translations
project_info = ProjectInfo.query.filter_by(
project_id=project_id, locale=default_locale).one_or_none()
return project_info.get_dto()
if locale == default_locale:
# If locale == default_locale don't need to worry about empty translations
return project_info.get_dto()
default_locale = ProjectInfo.query.filter_by(
project_id=project_id, locale=default_locale).one_or_none()
if default_locale is None:
error_message = \
f'BAD DATA - no info found for project {project_id}, locale: {locale}, default {default_locale}'
current_app.logger.critical(error_message)
raise ValueError(error_message)
# Pass thru default_locale in case of partial translation
return project_info.get_dto(default_locale)
def get_dto(self, default_locale=ProjectInfoDTO()) -> ProjectInfoDTO:
"""
Get DTO for current ProjectInfo
:param default_locale: The default locale string for any empty fields
"""
project_info_dto = ProjectInfoDTO()
project_info_dto.locale = self.locale
project_info_dto.name = self.name if self.name else default_locale.name
project_info_dto.description = self.description if self.description else default_locale.description
project_info_dto.short_description = self.short_description if self.short_description else default_locale.short_description
project_info_dto.instructions = self.instructions if self.description else default_locale.instructions
return project_info_dto
@staticmethod
def get_dto_for_all_locales(project_id) -> List[ProjectInfoDTO]:
locales = ProjectInfo.query.filter_by(project_id=project_id).all()
project_info_dtos = []
for locale in locales:
project_info_dto = locale.get_dto()
project_info_dtos.append(project_info_dto)
return project_info_dtos
project, project_dto = self._get_project_and_base_dto()
project_dto.tasks = Task.get_tasks_as_geojson_feature_collection(
self.id)
project_dto.project_info = ProjectInfo.get_dto_for_locale(
self.id, locale, project.default_locale)
return project_dto
def all_tasks_as_geojson(self):
""" Creates a geojson of all areas """
project_tasks = Task.get_tasks_as_geojson_feature_collection(self.id)
return project_tasks
def as_dto_for_admin(self, project_id):
""" Creates a Project DTO suitable for transmitting to project admins """
project, project_dto = self._get_project_and_base_dto()
if project is None:
return None
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(
project_id)
return project_dto
# Add index on project geometry
db.Index('idx_geometry', Project.geometry, postgresql_using='gist')
class TaskHistory(db.Model):
""" Describes the history associated with a task """
__tablename__ = "task_history"
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), index=True)
task_id = db.Column(db.Integer, nullable=False)
action = db.Column(db.String, nullable=False)
action_text = db.Column(db.String)
action_date = db.Column(db.DateTime, nullable=False, default=timestamp)
user_id = db.Column(db.BigInteger, db.ForeignKey('users.id', name='fk_users'), nullable=False)
actioned_by = db.relationship(User)
__table_args__ = (db.ForeignKeyConstraint([task_id, project_id], ['tasks.id', 'tasks.project_id'], name='fk_tasks'),
db.Index('idx_task_history_composite', 'task_id', 'project_id'), {})
def __init__(self, task_id, project_id, user_id):
self.task_id = task_id
self.project_id = project_id
self.user_id = user_id
def set_task_locked_action(self, task_action: TaskAction):
if task_action not in [TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION]:
raise ValueError('Invalid Action')
self.action = task_action.name
def set_comment_action(self, comment):
self.action = TaskAction.COMMENT.name
clean_comment = bleach.clean(comment) # Bleach input to ensure no nefarious script tags etc
self.action_text = clean_comment
def set_state_change_action(self, new_state):
self.action = TaskAction.STATE_CHANGE.name
self.action_text = new_state.name
def set_auto_unlock_action(self, task_action: TaskAction):
self.action = task_action.name
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def update_task_locked_with_duration(task_id: int, project_id: int, lock_action: TaskStatus, user_id: int):
"""
Calculates the duration a task was locked for and sets it on the history record
:param task_id: Task in scope
:param project_id: Project ID in scope
:param lock_action: The lock action, either Mapping or Validation
:param user_id: Logged in user updating the task
:return:
"""
try:
last_locked = TaskHistory.query.filter_by(task_id=task_id, project_id=project_id, action=lock_action.name,
action_text=None, user_id=user_id).one()
except NoResultFound:
# We suspect there's some kind or race condition that is occasionally deleting history records
# prior to user unlocking task. Most likely stemming from auto-unlock feature. However, given that
# we're trying to update a row that doesn't exist, it's better to return without doing anything
# rather than showing the user an error that they can't fix
return
except MultipleResultsFound:
# Again race conditions may mean we have multiple rows within the Task History. Here we attempt to
# remove the oldest duplicate rows, and update the newest on the basis that this was the last action
# the user was attempting to make.
TaskHistory.remove_duplicate_task_history_rows(task_id, project_id, lock_action, user_id)
# Now duplicate is removed, we recursively call ourself to update the duration on the remaining row
TaskHistory.update_task_locked_with_duration(task_id, project_id, lock_action, user_id)
return
duration_task_locked = datetime.datetime.utcnow() - last_locked.action_date
# Cast duration to isoformat for later transmission via api
last_locked.action_text = (datetime.datetime.min + duration_task_locked).time().isoformat()
db.session.commit()
@staticmethod
def remove_duplicate_task_history_rows(task_id: int, project_id: int, lock_action: TaskStatus, user_id: int):
""" Method used in rare cases where we have duplicate task history records for a given action by a user
This method will remove the oldest duplicate record, on the basis that the newest record was the
last action the user was attempting to perform
"""
dupe = TaskHistory.query.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id,
TaskHistory.action == lock_action.name,
TaskHistory.user_id == user_id).order_by(TaskHistory.id.asc()).first()
dupe.delete()
@staticmethod
def update_expired_and_locked_actions(project_id: int, task_id: int, expiry_date: datetime, action_text: str):
"""
Sets auto unlock state to all not finished actions, that are older then the expiry date.
Action is considered as a not finished, when it is in locked state and doesn't have action text
:param project_id: Project ID in scope
:param task_id: Task in scope
:param expiry_date: Action created before this date is treated as expired
:param action_text: Text which will be set for all changed actions
:return:
"""
all_expired = TaskHistory.query.filter(
TaskHistory.task_id == task_id,
TaskHistory.project_id == project_id,
TaskHistory.action_text.is_(None),
TaskHistory.action.in_([TaskAction.LOCKED_FOR_VALIDATION.name, TaskAction.LOCKED_FOR_MAPPING.name]),
TaskHistory.action_date <= expiry_date).all()
for task_history in all_expired:
unlock_action = TaskAction.AUTO_UNLOCKED_FOR_MAPPING if task_history.action == 'LOCKED_FOR_MAPPING' \
else TaskAction.AUTO_UNLOCKED_FOR_VALIDATION
task_history.set_auto_unlock_action(unlock_action)
task_history.action_text = action_text
db.session.commit()
@staticmethod
def get_all_comments(project_id: int) -> ProjectCommentsDTO:
""" Gets all comments for the supplied project_id"""
comments = db.session.query(TaskHistory.task_id,
TaskHistory.action_date,
TaskHistory.action_text,
User.username) \
.join(User) \
.filter(TaskHistory.project_id == project_id, TaskHistory.action == TaskAction.COMMENT.name).all()
comments_dto = ProjectCommentsDTO()
for comment in comments:
dto = ProjectComment()
dto.comment = comment.action_text
dto.comment_date = comment.action_date
dto.user_name = comment.username
dto.task_id = comment.task_id
comments_dto.comments.append(dto)
return comments_dto
@staticmethod
def get_last_status(project_id: int, task_id: int, for_undo: bool = False):
""" Get the status the task was set to the last time the task had a STATUS_CHANGE"""
result = db.session.query(TaskHistory.action_text) \
.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id,
TaskHistory.action == TaskAction.STATE_CHANGE.name) \
.order_by(TaskHistory.action_date.desc()).all()
if not result:
return TaskStatus.READY # No result so default to ready status
if len(result) == 1 and for_undo:
# We're looking for the previous status, however, there isn't any so we'll return Ready
return TaskStatus.READY
if for_undo:
# Return the second last status which was status the task was previously set to
return TaskStatus[result[1][0]]
else:
return TaskStatus[result[0][0]]
@staticmethod
def get_last_action(project_id: int, task_id: int):
"""Gets the most recent task history record for the task"""
return TaskHistory.query.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id) \
.order_by(TaskHistory.action_date.desc()).first()
@staticmethod
def get_last_action_of_type(project_id: int, task_id: int, allowed_task_actions: list):
"""Gets the most recent task history record having provided TaskAction"""
return TaskHistory.query.filter(TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id,
TaskHistory.action.in_(allowed_task_actions)) \
.order_by(TaskHistory.action_date.desc()).first()
@staticmethod
def get_last_locked_action(project_id: int, task_id: int):
"""Gets the most recent task history record with locked action for the task"""
return TaskHistory.get_last_action_of_type(
project_id, task_id,
[TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name])
@staticmethod
def get_last_locked_or_auto_unlocked_action(project_id: int, task_id: int):
"""Gets the most recent task history record with locked or auto unlocked action for the task"""
return TaskHistory.get_last_action_of_type(
project_id, task_id,
[TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name,
TaskAction.AUTO_UNLOCKED_FOR_MAPPING.name, TaskAction.AUTO_UNLOCKED_FOR_VALIDATION.name])
class TaskInvalidationHistory(db.Model):
""" Describes the most recent history of task invalidation and subsequent validation """
__tablename__ = "task_invalidation_history"
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id'),
nullable=False)
task_id = db.Column(db.Integer, nullable=False)
is_closed = db.Column(db.Boolean, default=False)
mapper_id = db.Column(db.BigInteger,
db.ForeignKey('users.id', name='fk_mappers'))
mapped_date = db.Column(db.DateTime)
invalidator_id = db.Column(
db.BigInteger, db.ForeignKey('users.id', name='fk_invalidators'))
invalidated_date = db.Column(db.DateTime)
invalidation_history_id = db.Column(
db.Integer,
db.ForeignKey('task_history.id', name='fk_invalidation_history'))
validator_id = db.Column(db.BigInteger,
db.ForeignKey('users.id', name='fk_validators'))
validated_date = db.Column(db.DateTime)
updated_date = db.Column(db.DateTime, default=timestamp)
__table_args__ = (db.ForeignKeyConstraint([task_id, project_id],
['tasks.id', 'tasks.project_id'],
name='fk_tasks'),
db.Index('idx_task_validation_history_composite',
'task_id', 'project_id'),
db.Index('idx_task_validation_mapper_status_composite',
'invalidator_id', 'is_closed'),
db.Index('idx_task_validation_mapper_status_composite',
'mapper_id', 'is_closed'), {})
def __init__(self, project_id, task_id):
self.project_id = project_id
self.task_id = task_id
self.is_closed = False
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def get_open_for_task(project_id, task_id):
return TaskInvalidationHistory.query.filter_by(
task_id=task_id, project_id=project_id,
is_closed=False).one_or_none()
@staticmethod
def close_all_for_task(project_id, task_id):
TaskInvalidationHistory.query.filter_by(task_id=task_id, project_id=project_id, is_closed=False) \
.update({"is_closed": True})
@staticmethod
def record_invalidation(project_id, task_id, invalidator_id, history):
# Invalidation always kicks off a new entry for a task, so close any existing ones.
TaskInvalidationHistory.close_all_for_task(project_id, task_id)
last_mapped = TaskHistory.get_last_mapped_action(project_id, task_id)
if last_mapped is None:
return
entry = TaskInvalidationHistory(project_id, task_id)
entry.invalidation_history_id = history.id
entry.mapper_id = last_mapped.user_id
entry.mapped_date = last_mapped.action_date
entry.invalidator_id = invalidator_id
entry.invalidated_date = history.action_date
entry.updated_date = timestamp()
db.session.add(entry)
@staticmethod
def record_validation(project_id, task_id, validator_id, history):
entry = TaskInvalidationHistory.get_open_for_task(project_id, task_id)
# If no open invalidation to update, then nothing to do
if entry is None:
return
last_mapped = TaskHistory.get_last_mapped_action(project_id, task_id)
entry.mapper_id = last_mapped.user_id
entry.mapped_date = last_mapped.action_date
entry.validator_id = validator_id
entry.validated_date = history.action_date
entry.is_closed = True
entry.updated_date = timestamp()
class TaskAnnotation(db.Model):
""" Describes Task annotaions like derived ML attributes """
__tablename__ = "task_annotations"
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id'),
index=True)
task_id = db.Column(db.Integer, nullable=False)
annotation_type = db.Column(db.String, nullable=False)
annotation_source = db.Column(db.String)
annotation_markdown = db.Column(db.String)
updated_timestamp = db.Column(db.DateTime,
nullable=False,
default=timestamp)
properties = db.Column(db.JSON, nullable=False)
__table_args__ = (db.ForeignKeyConstraint([task_id, project_id],
['tasks.id', 'tasks.project_id'],
name='fk_task_annotations'),
db.Index('idx_task_annotations_composite', 'task_id',
'project_id'), {})
def __init__(self,
task_id,
project_id,
annotation_type,
properties,
annotation_source=None,
annotation_markdown=None):
self.task_id = task_id
self.project_id = project_id
self.annotation_type = annotation_type
self.annotation_source = annotation_source
self.annotation_markdown = annotation_markdown
self.properties = properties
def create(self):
""" Creates and saves the current model to the DB """
db.session.add(self)
db.session.commit()
def update(self):
""" Updates the DB with the current state of the Task Annotations """
db.session.commit()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def get_task_annotation(task_id, project_id, annotation_type):
""" Get annotations for a task with supplied type """
return TaskAnnotation.query.filter_by(
project_id=project_id,
task_id=task_id,
annotation_type=annotation_type).one_or_none()
def get_dto(self):
task_annotation_dto = TaskAnnotationDTO()
task_annotation_dto.task_id = self.task_id
task_annotation_dto.properties = self.properties
task_annotation_dto.annotation_type = self.annotation_type
task_annotation_dto.annotation_source = self.annotation_source
task_annotation_dto.annotation_markdown = self.annotation_markdown
return task_annotation_dto
@staticmethod
def get_task_annotations_by_project_id_type(project_id, annotation_type):
""" Get annotatiols for a project with the supplied type """
project_task_annotations = TaskAnnotation.query.filter_by(
project_id=project_id, annotation_type=annotation_type).all()
if project_task_annotations:
project_task_annotations_dto = ProjectTaskAnnotationsDTO()
project_task_annotations_dto.project_id = project_id
for row in project_task_annotations:
task_annotation_dto = TaskAnnotationDTO()
task_annotation_dto.task_id = row.task_id
task_annotation_dto.properties = row.properties
task_annotation_dto.annotation_type = row.annotation_type
task_annotation_dto.annotation_source = row.annotation_source
task_annotation_dto.annotation_markdown = self.annotation_markdown
project_task_annotations_dto.tasks.append(task_annotation_dto)
return project_task_annotations_dto
else:
raise NotFound
@staticmethod
def get_task_annotations_by_project_id(project_id):
""" Get annotatiols for a project with the supplied type """
project_task_annotations = TaskAnnotation.query.filter_by(
project_id=project_id).all()
if project_task_annotations:
project_task_annotations_dto = ProjectTaskAnnotationsDTO()
project_task_annotations_dto.project_id = project_id
for row in project_task_annotations:
task_annotation_dto = TaskAnnotationDTO()
task_annotation_dto.task_id = row.task_id
task_annotation_dto.properties = row.properties
task_annotation_dto.annotation_type = row.annotation_type
task_annotation_dto.annotation_source = row.annotation_source
project_task_annotations_dto.tasks.append(task_annotation_dto)
return project_task_annotations_dto
else:
raise NotFound
