return None
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(
project_id)
return project_dto
def create_or_update_interests(self, interests_ids):
self.interests = []
objs = [Interest.get_by_id(i) for i in interests_ids]
self.interests.extend(objs)
db.session.commit()
@staticmethod
def get_project_campaigns(project_id: int):
query = (Campaign.query.join(campaign_projects).filter(
campaign_projects.c.project_id == project_id).all())
campaign_list = []
for campaign in query:
campaign_dto = CampaignDTO()
campaign_dto.id = campaign.id
campaign_dto.name = campaign.name
campaign_list.append(campaign_dto)
return campaign_list
# Add index on project geometry
db.Index("idx_geometry", Project.geometry, postgresql_using="gist")
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 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
per_task_instructions = db.Column(db.String)
__table_args__ = (
db.Index("idx_project_info_composite", "locale", "project_id"),
db.Index("textsearch_idx", "text_searchable"),
{},
)
@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
# Note project info not bleached on basis that admins are trusted users and shouldn't be doing anything bad
self.short_description = dto.short_description
self.description = dto.description
self.instructions = dto.instructions
self.per_task_instructions = dto.per_task_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 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.instructions else default_locale.instructions
)
project_info_dto.per_task_instructions = (
self.per_task_instructions
if self.per_task_instructions
else default_locale.per_task_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
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"),
index=True,
nullable=False,
)
invalidation_history = db.relationship(TaskInvalidationHistory,
lazy="dynamic",
cascade="all")
actioned_by = db.relationship(User)
task_mapping_issues = db.relationship(TaskMappingIssue, cascade="all")
__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"),
db.Index("idx_task_history_project_id_user_id", "user_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 and result[0][0] in [
TaskStatus.MAPPED.name,
TaskStatus.BADIMAGERY.name,
]:
# We need to return a READY when last status of the task is badimagery or mapped.
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,
],
)
def get_last_mapped_action(project_id: int, task_id: int):
"""Gets the most recent mapped action, if any, in the task history"""
return (db.session.query(TaskHistory).filter(
TaskHistory.project_id == project_id,
TaskHistory.task_id == task_id,
TaskHistory.action == TaskAction.STATE_CHANGE.name,
TaskHistory.action_text.in_(
[TaskStatus.BADIMAGERY.name, TaskStatus.MAPPED.name]),
).order_by(TaskHistory.action_date.desc()).first())
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()
project_task_annotations_dto = ProjectTaskAnnotationsDTO()
project_task_annotations_dto.project_id = project_id
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 = row.annotation_markdown
project_task_annotations_dto.tasks.append(task_annotation_dto)
return project_task_annotations_dto
@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()
project_task_annotations_dto = ProjectTaskAnnotationsDTO()
project_task_annotations_dto.project_id = project_id
if project_task_annotations:
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
