class Notification(db.Model):
""" Describes a Notification for a user """
__tablename__ = "notifications"
__table_args__ = (db.ForeignKeyConstraint(["user_id"], ["users.id"]), )
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.BigInteger, db.ForeignKey("users.id"), index=True)
unread_count = db.Column(db.Integer)
date = db.Column(db.DateTime, default=timestamp)
# Relationships
user = db.relationship(User,
foreign_keys=[user_id],
backref="notifications")
def as_dto(self) -> NotificationDTO:
""" Casts notification object to DTO """
dto = NotificationDTO()
dto.user_id = self.user_id
dto.unread_count = self.unread_count
dto.date = self.date
return dto
def save(self):
db.session.add(self)
db.session.commit()
def update(self):
self.date = timestamp()
db.session.commit()
@staticmethod
def get_unread_message_count(user_id: int) -> NotificationDTO:
""" Get count of unread messages for user """
notifications = Notification.query.filter(
Notification.user_id == user_id).first()
# Create if does not exist.
if notifications is None:
# In case users are new but have not logged in previously.
date_value = datetime.today() - timedelta(days=30)
notifications = Notification(user_id=user_id,
unread_count=0,
date=date_value)
notifications.save()
# Count messages that the user has received after last check.
count = (Message.query.filter(Message.to_user_id == user_id).filter(
Message.date > notifications.date).count())
return count
class Notification(db.Model):
""" Describes a Notification for a user """
__tablename__ = "notifications"
__table_args__ = (db.ForeignKeyConstraint(["user_id"], ["users.id"]), )
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.BigInteger, db.ForeignKey("users.id"), index=True)
unread_count = db.Column(db.Integer)
date = db.Column(db.DateTime, default=timestamp)
# Relationships
user = db.relationship(User,
foreign_keys=[user_id],
backref="notifications")
def as_dto(self) -> NotificationDTO:
""" Casts notification object to DTO """
dto = NotificationDTO()
dto.user_id = self.user_id
dto.unread_count = self.unread_count
dto.date = self.date
return dto
def update_notification_count(self):
current_app.logger.debug("Updating notification count")
db.session.add(self)
db.session.commit()
@staticmethod
def get_unread_message_count(user_id: int) -> NotificationDTO:
""" Get count of unread messages for user """
notifications = Notification.query.filter(
Notification.user_id == user_id).first()
count = 0
if notifications is not None:
count = notifications.unread_count
return count
class Message(db.Model):
""" Describes an individual Message a user can send """
__tablename__ = "messages"
__table_args__ = (db.ForeignKeyConstraint(
["task_id", "project_id"], ["tasks.id", "tasks.project_id"]), )
id = db.Column(db.Integer, primary_key=True)
message = db.Column(db.String)
subject = db.Column(db.String)
from_user_id = db.Column(db.BigInteger, db.ForeignKey("users.id"))
to_user_id = db.Column(db.BigInteger,
db.ForeignKey("users.id"),
index=True)
project_id = db.Column(db.Integer,
db.ForeignKey("projects.id"),
index=True)
task_id = db.Column(db.Integer, index=True)
message_type = db.Column(db.Integer, index=True)
date = db.Column(db.DateTime, default=timestamp)
read = db.Column(db.Boolean, default=False)
# Relationships
from_user = db.relationship(User, foreign_keys=[from_user_id])
to_user = db.relationship(User,
foreign_keys=[to_user_id],
backref="messages")
project = db.relationship(Project,
foreign_keys=[project_id],
backref="messages")
task = db.relationship(
Task,
primaryjoin=
"and_(Task.id == foreign(Message.task_id), Task.project_id == Message.project_id)",
backref="messages",
)
@classmethod
def from_dto(cls, to_user_id: int, dto: MessageDTO):
""" Creates new message from DTO """
message = cls()
message.subject = dto.subject
message.message = dto.message
message.from_user_id = dto.from_user_id
message.to_user_id = to_user_id
message.project_id = dto.project_id
message.task_id = dto.task_id
if dto.message_type is not None:
message.message_type = MessageType(dto.message_type)
return message
def as_dto(self) -> MessageDTO:
""" Casts message object to DTO """
dto = MessageDTO()
dto.message_id = self.id
dto.message = self.message
dto.sent_date = self.date
dto.read = self.read
dto.subject = self.subject
dto.project_id = self.project_id
dto.task_id = self.task_id
if self.message_type is not None:
dto.message_type = MessageType(self.message_type).name
if self.from_user_id:
dto.from_username = self.from_user.username
return dto
def add_message(self):
""" Add message into current transaction - DO NOT COMMIT HERE AS MESSAGES ARE PART OF LARGER TRANSACTIONS"""
current_app.logger.debug("Adding message to session")
db.session.add(self)
def save(self):
""" Save """
db.session.add(self)
db.session.commit()
@staticmethod
def get_all_contributors(project_id: int):
""" Get all contributors to a project """
query = """SELECT mapped_by as contributors from tasks where project_id = :project_id and mapped_by is not null
UNION
SELECT validated_by from tasks where tasks.project_id = :project_id and validated_by is not null"""
contributors = db.engine.execute(text(query), project_id=project_id)
return contributors
def mark_as_read(self):
""" Mark the message in scope as Read """
self.read = True
db.session.commit()
@staticmethod
def get_unread_message_count(user_id: int):
""" Get count of unread messages for user """
return Message.query.filter(Message.to_user_id == user_id,
Message.read == false()).count()
@staticmethod
def get_all_messages(user_id: int) -> MessagesDTO:
""" Gets all messages to the user """
user_messages = Message.query.filter(
Message.to_user_id == user_id).all()
if len(user_messages) == 0:
raise NotFound()
messages_dto = MessagesDTO()
for message in user_messages:
messages_dto.user_messages.append(message.as_dto())
return messages_dto
@staticmethod
def delete_multiple_messages(message_ids: list, user_id: int):
""" Deletes the specified messages to the user """
Message.query.filter(
Message.to_user_id == user_id,
Message.id.in_(message_ids)).delete(synchronize_session=False)
db.session.commit()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
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 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