class WorkQueue(db.Model): """Represents a single item of work to do in a specific queue. Queries: - By task_id for finishing a task or extending a lease. - By Index(queue_name, status, eta) for finding the oldest task for a queue that is still pending. - By Index(status, create) for finding old tasks that should be deleted from the table periodically to free up space. """ CANCELED = 'canceled' DONE = 'done' ERROR = 'error' LIVE = 'live' STATES = frozenset([CANCELED, DONE, ERROR, LIVE]) task_id = db.Column(db.String(100), primary_key=True, nullable=False) queue_name = db.Column(db.String(100), primary_key=True, nullable=False) status = db.Column(db.Enum(*STATES), default=LIVE, nullable=False) eta = db.Column(db.DateTime, default=datetime.datetime.utcnow, nullable=False) build_id = db.Column(db.Integer, db.ForeignKey('build.id')) release_id = db.Column(db.Integer, db.ForeignKey('release.id')) run_id = db.Column(db.Integer, db.ForeignKey('run.id')) source = db.Column(db.String(500)) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) finished = db.Column(db.DateTime) lease_attempts = db.Column(db.Integer, default=0, nullable=False) last_lease = db.Column(db.DateTime) last_owner = db.Column(db.String(500)) heartbeat = db.Column(db.Text) heartbeat_number = db.Column(db.Integer) payload = db.Column(db.LargeBinary) content_type = db.Column(db.String(100)) __table_args__ = ( db.Index('created_index', 'queue_name', 'status', 'created'), db.Index('lease_index', 'queue_name', 'status', 'eta'), db.Index('reap_index', 'status', 'created'), ) @property def lease_outstanding(self): if not self.status == WorkQueue.LIVE: return False if not self.last_owner: return False now = datetime.datetime.utcnow() return now < self.eta
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 ProjectInfo(db.Model): """ Contains all project info localized into supported languages """ __tablename__ = 'project_info' project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), primary_key=True) locale = db.Column(db.String(10), primary_key=True) name = db.Column(db.String(512)) short_description = db.Column(db.String) description = db.Column(db.String) instructions = db.Column(db.String) project_id_str = db.Column(db.String) text_searchable = db.Column( TSVECTOR ) # This contains searchable text and is populated by a DB Trigger __table_args__ = (db.Index('idx_project_info composite', 'locale', 'project_id'), {}) @classmethod def create_from_name(cls, name: str): """ Creates a new ProjectInfo class from name, used when creating draft projects """ new_info = cls() new_info.locale = 'en' # Draft project default to english, PMs can change this prior to publication new_info.name = name return new_info @classmethod def create_from_dto(cls, dto: ProjectInfoDTO): """ Creates a new ProjectInfo class from dto, used from project edit """ new_info = cls() new_info.update_from_dto(dto) return new_info def update_from_dto(self, dto: ProjectInfoDTO): """ Updates existing ProjectInfo from supplied DTO """ self.locale = dto.locale self.name = dto.name self.project_id_str = str( self.project_id) # Allows project_id to be searched # TODO bleach input self.short_description = dto.short_description self.description = dto.description self.instructions = dto.instructions @staticmethod def get_dto_for_locale(project_id, locale, default_locale='en') -> ProjectInfoDTO: """ Gets the projectInfoDTO for the project for the requested locale. If not found, then the default locale is used :param project_id: ProjectID in scope :param locale: locale requested by user :param default_locale: default locale of project :raises: ValueError if no info found for Default Locale """ project_info = ProjectInfo.query.filter_by( project_id=project_id, locale=locale).one_or_none() if project_info is None: # If project is none, get default locale and don't worry about empty translations project_info = ProjectInfo.query.filter_by( project_id=project_id, locale=default_locale).one_or_none() return project_info.get_dto() if locale == default_locale: # If locale == default_locale don't need to worry about empty translations return project_info.get_dto() default_locale = ProjectInfo.query.filter_by( project_id=project_id, locale=default_locale).one_or_none() if default_locale is None: error_message = \ f'BAD DATA - no info found for project {project_id}, locale: {locale}, default {default_locale}' current_app.logger.critical(error_message) raise ValueError(error_message) # Pass thru default_locale in case of partial translation return project_info.get_dto(default_locale) def get_dto(self, default_locale=ProjectInfoDTO()) -> ProjectInfoDTO: """ Get DTO for current ProjectInfo :param default_locale: The default locale string for any empty fields """ project_info_dto = ProjectInfoDTO() project_info_dto.locale = self.locale project_info_dto.name = self.name if self.name else default_locale.name project_info_dto.description = self.description if self.description else default_locale.description project_info_dto.short_description = self.short_description if self.short_description else default_locale.short_description project_info_dto.instructions = self.instructions if self.description else default_locale.instructions return project_info_dto @staticmethod def get_dto_for_all_locales(project_id) -> List[ProjectInfoDTO]: locales = ProjectInfo.query.filter_by(project_id=project_id).all() project_info_dtos = [] for locale in locales: project_info_dto = locale.get_dto() project_info_dtos.append(project_info_dto) return project_info_dtos
project, project_dto = self._get_project_and_base_dto() project_dto.tasks = Task.get_tasks_as_geojson_feature_collection( self.id) project_dto.project_info = ProjectInfo.get_dto_for_locale( self.id, locale, project.default_locale) return project_dto def all_tasks_as_geojson(self): """ Creates a geojson of all areas """ project_tasks = Task.get_tasks_as_geojson_feature_collection(self.id) return project_tasks def as_dto_for_admin(self, project_id): """ Creates a Project DTO suitable for transmitting to project admins """ project, project_dto = self._get_project_and_base_dto() if project is None: return None project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales( project_id) return project_dto # Add index on project geometry db.Index('idx_geometry', Project.geometry, postgresql_using='gist')
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 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