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
Esempio n. 3
0
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()
Esempio n. 4
0
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])
Esempio n. 5
0
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