class Relationship_Actor_Videos(db.Model): __tablename__ = 'relationship_actor_videos' video_id = db.Column(db.Text, primary_key=True) channel_id = db.Column(db.Text, primary_key=True) collected_date = db.Column(db.Date, primary_key=True) __table_args__ = ( db.ForeignKeyConstraint( [video_id, collected_date], [Videos.video_id, Videos.collected_date] ), db.ForeignKeyConstraint( [channel_id, collected_date], [Actor.channel_id, Actor.collected_date] ) ) def __init__(self, video_id, channel_id, collected_date): self.video_id = video_id self.channel_id = channel_id self.collected_date = collected_date
class Relationship_Videos(db.Model): __tablename__ = 'relationship_videos' video_id = db.Column(db.Text, primary_key=True) original_video_id = db.Column(db.Text, primary_key=True) collected_date = db.Column(db.Date, primary_key=True) __table_args__ = (db.ForeignKeyConstraint( [video_id, collected_date], [Videos.video_id, Videos.collected_date]), {}) def __init__(self, video_id, original_video_id, collected_date): self.video_id = video_id self.original_video_id = original_video_id self.collected_date = collected_date
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 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 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 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