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