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