return None project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales( project_id) return project_dto def create_or_update_interests(self, interests_ids): self.interests = [] objs = [Interest.get_by_id(i) for i in interests_ids] self.interests.extend(objs) db.session.commit() @staticmethod def get_project_campaigns(project_id: int): query = (Campaign.query.join(campaign_projects).filter( campaign_projects.c.project_id == project_id).all()) campaign_list = [] for campaign in query: campaign_dto = CampaignDTO() campaign_dto.id = campaign.id campaign_dto.name = campaign.name campaign_list.append(campaign_dto) return campaign_list # Add index on project geometry db.Index("idx_geometry", Project.geometry, postgresql_using="gist")
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 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 per_task_instructions = db.Column(db.String) __table_args__ = ( db.Index("idx_project_info_composite", "locale", "project_id"), db.Index("textsearch_idx", "text_searchable"), {}, ) @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 # Note project info not bleached on basis that admins are trusted users and shouldn't be doing anything bad self.short_description = dto.short_description self.description = dto.description self.instructions = dto.instructions self.per_task_instructions = dto.per_task_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 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.instructions else default_locale.instructions ) project_info_dto.per_task_instructions = ( self.per_task_instructions if self.per_task_instructions else default_locale.per_task_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
class TaskHistory(db.Model): """ Describes the history associated with a task """ __tablename__ = "task_history" id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer, db.ForeignKey("projects.id"), index=True) task_id = db.Column(db.Integer, nullable=False) action = db.Column(db.String, nullable=False) action_text = db.Column(db.String) action_date = db.Column(db.DateTime, nullable=False, default=timestamp) user_id = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users"), index=True, nullable=False, ) invalidation_history = db.relationship(TaskInvalidationHistory, lazy="dynamic", cascade="all") actioned_by = db.relationship(User) task_mapping_issues = db.relationship(TaskMappingIssue, cascade="all") __table_args__ = ( db.ForeignKeyConstraint([task_id, project_id], ["tasks.id", "tasks.project_id"], name="fk_tasks"), db.Index("idx_task_history_composite", "task_id", "project_id"), db.Index("idx_task_history_project_id_user_id", "user_id", "project_id"), {}, ) def __init__(self, task_id, project_id, user_id): self.task_id = task_id self.project_id = project_id self.user_id = user_id def set_task_locked_action(self, task_action: TaskAction): if task_action not in [ TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION, ]: raise ValueError("Invalid Action") self.action = task_action.name def set_comment_action(self, comment): self.action = TaskAction.COMMENT.name clean_comment = bleach.clean( comment) # Bleach input to ensure no nefarious script tags etc self.action_text = clean_comment def set_state_change_action(self, new_state): self.action = TaskAction.STATE_CHANGE.name self.action_text = new_state.name def set_auto_unlock_action(self, task_action: TaskAction): self.action = task_action.name def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @staticmethod def update_task_locked_with_duration(task_id: int, project_id: int, lock_action: TaskStatus, user_id: int): """ Calculates the duration a task was locked for and sets it on the history record :param task_id: Task in scope :param project_id: Project ID in scope :param lock_action: The lock action, either Mapping or Validation :param user_id: Logged in user updating the task :return: """ try: last_locked = TaskHistory.query.filter_by( task_id=task_id, project_id=project_id, action=lock_action.name, action_text=None, user_id=user_id, ).one() except NoResultFound: # We suspect there's some kind or race condition that is occasionally deleting history records # prior to user unlocking task. Most likely stemming from auto-unlock feature. However, given that # we're trying to update a row that doesn't exist, it's better to return without doing anything # rather than showing the user an error that they can't fix return except MultipleResultsFound: # Again race conditions may mean we have multiple rows within the Task History. Here we attempt to # remove the oldest duplicate rows, and update the newest on the basis that this was the last action # the user was attempting to make. TaskHistory.remove_duplicate_task_history_rows( task_id, project_id, lock_action, user_id) # Now duplicate is removed, we recursively call ourself to update the duration on the remaining row TaskHistory.update_task_locked_with_duration( task_id, project_id, lock_action, user_id) return duration_task_locked = datetime.datetime.utcnow( ) - last_locked.action_date # Cast duration to isoformat for later transmission via api last_locked.action_text = ((datetime.datetime.min + duration_task_locked).time().isoformat()) db.session.commit() @staticmethod def remove_duplicate_task_history_rows(task_id: int, project_id: int, lock_action: TaskStatus, user_id: int): """ Method used in rare cases where we have duplicate task history records for a given action by a user This method will remove the oldest duplicate record, on the basis that the newest record was the last action the user was attempting to perform """ dupe = (TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == lock_action.name, TaskHistory.user_id == user_id, ).order_by(TaskHistory.id.asc()).first()) dupe.delete() @staticmethod def update_expired_and_locked_actions(project_id: int, task_id: int, expiry_date: datetime, action_text: str): """ Sets auto unlock state to all not finished actions, that are older then the expiry date. Action is considered as a not finished, when it is in locked state and doesn't have action text :param project_id: Project ID in scope :param task_id: Task in scope :param expiry_date: Action created before this date is treated as expired :param action_text: Text which will be set for all changed actions :return: """ all_expired = TaskHistory.query.filter( TaskHistory.task_id == task_id, TaskHistory.project_id == project_id, TaskHistory.action_text.is_(None), TaskHistory.action.in_([ TaskAction.LOCKED_FOR_VALIDATION.name, TaskAction.LOCKED_FOR_MAPPING.name, ]), TaskHistory.action_date <= expiry_date, ).all() for task_history in all_expired: unlock_action = (TaskAction.AUTO_UNLOCKED_FOR_MAPPING if task_history.action == "LOCKED_FOR_MAPPING" else TaskAction.AUTO_UNLOCKED_FOR_VALIDATION) task_history.set_auto_unlock_action(unlock_action) task_history.action_text = action_text db.session.commit() @staticmethod def get_all_comments(project_id: int) -> ProjectCommentsDTO: """ Gets all comments for the supplied project_id""" comments = (db.session.query( TaskHistory.task_id, TaskHistory.action_date, TaskHistory.action_text, User.username, ).join(User).filter( TaskHistory.project_id == project_id, TaskHistory.action == TaskAction.COMMENT.name, ).all()) comments_dto = ProjectCommentsDTO() for comment in comments: dto = ProjectComment() dto.comment = comment.action_text dto.comment_date = comment.action_date dto.user_name = comment.username dto.task_id = comment.task_id comments_dto.comments.append(dto) return comments_dto @staticmethod def get_last_status(project_id: int, task_id: int, for_undo: bool = False): """ Get the status the task was set to the last time the task had a STATUS_CHANGE""" result = (db.session.query(TaskHistory.action_text).filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == TaskAction.STATE_CHANGE.name, ).order_by(TaskHistory.action_date.desc()).all()) if not result: return TaskStatus.READY # No result so default to ready status if len(result) == 1 and for_undo: # We're looking for the previous status, however, there isn't any so we'll return Ready return TaskStatus.READY if for_undo and result[0][0] in [ TaskStatus.MAPPED.name, TaskStatus.BADIMAGERY.name, ]: # We need to return a READY when last status of the task is badimagery or mapped. return TaskStatus.READY if for_undo: # Return the second last status which was status the task was previously set to return TaskStatus[result[1][0]] else: return TaskStatus[result[0][0]] @staticmethod def get_last_action(project_id: int, task_id: int): """Gets the most recent task history record for the task""" return (TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id).order_by( TaskHistory.action_date.desc()).first()) @staticmethod def get_last_action_of_type(project_id: int, task_id: int, allowed_task_actions: list): """Gets the most recent task history record having provided TaskAction""" return (TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action.in_(allowed_task_actions), ).order_by(TaskHistory.action_date.desc()).first()) @staticmethod def get_last_locked_action(project_id: int, task_id: int): """Gets the most recent task history record with locked action for the task""" return TaskHistory.get_last_action_of_type( project_id, task_id, [ TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name ], ) @staticmethod def get_last_locked_or_auto_unlocked_action(project_id: int, task_id: int): """Gets the most recent task history record with locked or auto unlocked action for the task""" return TaskHistory.get_last_action_of_type( project_id, task_id, [ TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name, TaskAction.AUTO_UNLOCKED_FOR_MAPPING.name, TaskAction.AUTO_UNLOCKED_FOR_VALIDATION.name, ], ) def get_last_mapped_action(project_id: int, task_id: int): """Gets the most recent mapped action, if any, in the task history""" return (db.session.query(TaskHistory).filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == TaskAction.STATE_CHANGE.name, TaskHistory.action_text.in_( [TaskStatus.BADIMAGERY.name, TaskStatus.MAPPED.name]), ).order_by(TaskHistory.action_date.desc()).first())
class TaskAnnotation(db.Model): """ Describes Task annotaions like derived ML attributes """ __tablename__ = "task_annotations" id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer, db.ForeignKey("projects.id"), index=True) task_id = db.Column(db.Integer, nullable=False) annotation_type = db.Column(db.String, nullable=False) annotation_source = db.Column(db.String) annotation_markdown = db.Column(db.String) updated_timestamp = db.Column(db.DateTime, nullable=False, default=timestamp) properties = db.Column(db.JSON, nullable=False) __table_args__ = ( db.ForeignKeyConstraint( [task_id, project_id], ["tasks.id", "tasks.project_id"], name="fk_task_annotations", ), db.Index("idx_task_annotations_composite", "task_id", "project_id"), {}, ) def __init__( self, task_id, project_id, annotation_type, properties, annotation_source=None, annotation_markdown=None, ): self.task_id = task_id self.project_id = project_id self.annotation_type = annotation_type self.annotation_source = annotation_source self.annotation_markdown = annotation_markdown self.properties = properties def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() def update(self): """ Updates the DB with the current state of the Task Annotations """ db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @staticmethod def get_task_annotation(task_id, project_id, annotation_type): """ Get annotations for a task with supplied type """ return TaskAnnotation.query.filter_by( project_id=project_id, task_id=task_id, annotation_type=annotation_type).one_or_none() def get_dto(self): task_annotation_dto = TaskAnnotationDTO() task_annotation_dto.task_id = self.task_id task_annotation_dto.properties = self.properties task_annotation_dto.annotation_type = self.annotation_type task_annotation_dto.annotation_source = self.annotation_source task_annotation_dto.annotation_markdown = self.annotation_markdown return task_annotation_dto @staticmethod def get_task_annotations_by_project_id_type(project_id, annotation_type): """ Get annotatiols for a project with the supplied type """ project_task_annotations = TaskAnnotation.query.filter_by( project_id=project_id, annotation_type=annotation_type).all() project_task_annotations_dto = ProjectTaskAnnotationsDTO() project_task_annotations_dto.project_id = project_id if project_task_annotations: project_task_annotations_dto = ProjectTaskAnnotationsDTO() project_task_annotations_dto.project_id = project_id for row in project_task_annotations: task_annotation_dto = TaskAnnotationDTO() task_annotation_dto.task_id = row.task_id task_annotation_dto.properties = row.properties task_annotation_dto.annotation_type = row.annotation_type task_annotation_dto.annotation_source = row.annotation_source task_annotation_dto.annotation_markdown = row.annotation_markdown project_task_annotations_dto.tasks.append(task_annotation_dto) return project_task_annotations_dto @staticmethod def get_task_annotations_by_project_id(project_id): """ Get annotatiols for a project with the supplied type """ project_task_annotations = TaskAnnotation.query.filter_by( project_id=project_id).all() project_task_annotations_dto = ProjectTaskAnnotationsDTO() project_task_annotations_dto.project_id = project_id if project_task_annotations: for row in project_task_annotations: task_annotation_dto = TaskAnnotationDTO() task_annotation_dto.task_id = row.task_id task_annotation_dto.properties = row.properties task_annotation_dto.annotation_type = row.annotation_type task_annotation_dto.annotation_source = row.annotation_source project_task_annotations_dto.tasks.append(task_annotation_dto) return project_task_annotations_dto