def _delete_chunk(session: Session, a_chunk: ObjectIDListT) -> Tuple[int, int, List[str]]: """ Delete a chunk from self's object list. Technical Note: We use SQLA Core as we don't want to fetch the rows """ # Start with images which are not deleted via a CASCADE on DB side # This is maybe due to relationship cycle b/w ObjectHeader and Images @See comment in Image class img_del_qry: Delete = Image.__table__.delete() img_del_qry = img_del_qry.where(Image.objid == any_(a_chunk)) img_del_qry = img_del_qry.returning(Image.file_name, Image.thumb_file_name) with CodeTimer("DELETE for %d images: " % len(a_chunk), logger): files_res = session.execute(img_del_qry) img_files = [] nb_img_rows = 0 for a_file_tuple in files_res: # We have main file and optionally the thumbnail one for a_file in a_file_tuple: if a_file: img_files.append(a_file) nb_img_rows += 1 logger.info("Removed: %d rows, to remove: %d files", nb_img_rows, len(img_files)) obj_del_qry: Delete = ObjectHeader.__table__.delete() obj_del_qry = obj_del_qry.where(ObjectHeader.objid == any_(a_chunk)) with CodeTimer("DELETE for %d objs: " % len(a_chunk), logger): nb_objs = session.execute(obj_del_qry).rowcount session.commit() # TODO: Cache delete return nb_objs, nb_img_rows, img_files
def do_after_load(session: Session, prj_id: int): """ After loading of data, update various cross counts. """ # Ensure the ORM has no shadow copy before going to plain SQL session.expunge_all() Sample.propagate_geo(session, prj_id) ProjectBO.update_taxo_stats(session, prj_id) # Stats depend on taxo stats ProjectBO.update_stats(session, prj_id)
def create_or_link_slaves(how: ImportHow, session: Session, object_head_to_write, object_fields_to_write, image_to_write) -> int: """ Create, link or update slave entities, i.e. head, fields, image. Also update them... TODO: Split/fork the def :returns the number of new records """ if object_head_to_write.orig_id in how.existing_objects: # Set the objid which will be copied for storing the image, the object itself # will not be stored due to returned value. objid = how.existing_objects[object_head_to_write.orig_id] object_head_to_write.objid = objid if how.can_update_only: # noinspection DuplicatedCode for a_cls, its_pk, an_upd in zip( [ObjectHeader, ObjectFields], ['objid', 'objfid'], [object_head_to_write, object_fields_to_write]): filter_for_id = text("%s=%d" % (its_pk, objid)) # Fetch the record to update obj = session.query(a_cls).filter(filter_for_id).first() if a_cls == ObjectHeader: # Eventually refresh sun position if an_upd.nb_fields_from(USED_FIELDS_FOR_SUNPOS) > 0: # Give the bean enough data for computation for a_field in USED_FIELDS_FOR_SUNPOS.difference( an_upd.keys()): an_upd[a_field] = getattr(obj, a_field) TSVFile.do_sun_position_field(an_upd) updates = TSVFile.update_orm_object(obj, an_upd) # type: ignore if len(updates) > 0: logger.info("Updating '%s' using %s", filter_for_id, updates) session.flush() ret = 0 # nothing to write else: # 'Simply' a line with a complementary image logger.info("One more image for %s:%s ", object_head_to_write.orig_id, image_to_write) ret = 1 # just a new image else: if how.can_update_only: # No objects creation while updating logger.info("Object %s not found while updating ", object_head_to_write.orig_id) ret = 0 else: # or create it # object_head_to_write.projid = how.prj_id object_head_to_write.random_value = random.randint(1, 99999999) # Below left NULL @see self.update_counts_and_img0 # object_head_to_write.img0id = XXXXX ret = 3 # new image + new object_head + new object_fields return ret
def create_parent(session: Session, dict_to_write, prj_id: ProjectIDT, parent_class): """ Create the SQLAlchemy wrapper for Sample, Acquisition or Process. :return: The created DB wrapper. """ # noinspection PyCallingNonCallable parent = parent_class(**dict_to_write) # Link with project parent.projid = prj_id session.add(parent) session.flush() return parent
def update_taxo_stats(session: Session, projid: int): sql = text(""" DELETE FROM projects_taxo_stat pts WHERE pts.projid = :prjid; INSERT INTO projects_taxo_stat(projid, id, nbr, nbr_v, nbr_d, nbr_p) SELECT sam.projid, COALESCE(obh.classif_id, -1) id, COUNT(*) nbr, COUNT(CASE WHEN obh.classif_qual = 'V' THEN 1 END) nbr_v, COUNT(CASE WHEN obh.classif_qual = 'D' THEN 1 END) nbr_d, COUNT(CASE WHEN obh.classif_qual = 'P' THEN 1 END) nbr_p FROM obj_head obh JOIN acquisitions acq ON acq.acquisid = obh.acquisid JOIN samples sam ON sam.sampleid = acq.acq_sample_id AND sam.projid = :prjid GROUP BY sam.projid, obh.classif_id;""") session.execute(sql, {'prjid': projid})
def update_stats(session: Session, projid: int): sql = text(""" UPDATE projects SET objcount=q.nbr_sum, pctclassified=100.0*nbrclassified/q.nbr_sum, pctvalidated=100.0*nbrvalidated/q.nbr_sum FROM projects p LEFT JOIN (SELECT projid, SUM(nbr) nbr_sum, SUM(CASE WHEN id>0 THEN nbr END) nbrclassified, SUM(nbr_v) nbrvalidated FROM projects_taxo_stat WHERE projid = :prjid GROUP BY projid) q ON p.projid = q.projid WHERE projects.projid = :prjid AND p.projid = :prjid""") session.execute(sql, {'prjid': projid})
def delete(session: Session, prj_id: int): """ Completely remove the project. It is assumed that contained objects have been removed. """ # TODO: Remove from user preferences # Unlink Particle project if any upd_qry = ParticleProject.__table__.update().where(ParticleProject.projid == prj_id).values(projid=None) row_count = session.execute(upd_qry).rowcount logger.info("%d EcoPart project unlinked", row_count) # Remove project session.query(Project). \ filter(Project.projid == prj_id).delete() # Remove privileges session.query(ProjectPrivilege). \ filter(ProjectPrivilege.projid == prj_id).delete()
def read_taxo_stats(session: Session, prj_ids: ProjectIDListT, taxa_ids: Union[str, ClassifIDListT]) -> List[ProjectTaxoStats]: sql = """ SELECT pts.projid, ARRAY_AGG(pts.id) as ids, SUM(CASE WHEN pts.id = -1 THEN pts.nbr ELSE 0 END) as nb_u, SUM(pts.nbr_v) as nb_v, SUM(pts.nbr_d) as nb_d, SUM(pts.nbr_p) as nb_p FROM projects_taxo_stat pts WHERE pts.projid = ANY(:ids)""" params: Dict[str, Any] = {'ids': prj_ids} if len(taxa_ids) > 0: if taxa_ids == 'all': pass else: sql += " AND pts.id = ANY(:tids)" params["tids"] = taxa_ids sql += """ GROUP BY pts.projid""" if len(taxa_ids) > 0: sql += ", pts.id" res: Result = session.execute(text(sql), params) with CodeTimer("stats for %d projects:" % len(prj_ids), logger): ret = [ProjectTaxoStats(rec) for rec in res.fetchall()] for a_stat in ret: a_stat.used_taxa.sort() return ret
def __init__(self, session: Session, prj_ids: ProjectIDListT, public: bool = False): # Query the project and load neighbours as well qry: Query = session.query(Project, ProjectPrivilege) qry = qry.outerjoin(ProjectPrivilege, Project.privs_for_members).options( contains_eager(Project.privs_for_members)) qry = qry.outerjoin(User, ProjectPrivilege.user).options( contains_eager(ProjectPrivilege.user)) qry = qry.filter(Project.projid == any_(prj_ids)) self.projects = [] done = set() with CodeTimer("%s BO projects query & init:" % len(prj_ids), logger): for a_proj, a_pp in qry.all(): # The query yields duplicates so we need to filter if a_proj.projid not in done: if public: self.projects.append(ProjectBO(a_proj)) else: self.projects.append(ProjectBO(a_proj).enrich()) done.add(a_proj.projid) # Add instruments with CodeTimer("%s set instruments:" % len(prj_ids), logger): instruments = DescribedInstrumentSet(session, prj_ids) for a_project in self.projects: instrums = instruments.by_project.get(a_project.projid) if instrums is not None: a_project.instrument = ",".join(instrums)
def remap(session: Session, prj_id: int, table: MappedTableTypeT, remaps: List[RemapOp]): """ Apply remapping operations onto the given table for given project. """ # Do the remapping, including blanking of unused columns values = {a_remap.to: text(a_remap.frm) if a_remap.frm is not None else a_remap.frm for a_remap in remaps} qry: Query = session.query(table) samples_4_prj: Query acqs_4_samples: Query if table == Sample: qry = qry.filter(Sample.projid == prj_id) # type: ignore elif table == Acquisition: samples_4_prj = Query(Sample.sampleid).filter(Sample.projid == prj_id) qry = qry.filter(Acquisition.acq_sample_id.in_(samples_4_prj)) # type: ignore elif table == Process: samples_4_prj = Query(Sample.sampleid).filter(Sample.projid == prj_id) acqs_4_samples = Query(Acquisition.acquisid).filter(Acquisition.acq_sample_id.in_(samples_4_prj)) qry = qry.filter(Process.processid.in_(acqs_4_samples)) # type: ignore elif table == ObjectFields: samples_4_prj = Query(Sample.sampleid).filter(Sample.projid == prj_id) acqs_4_samples = Query(Acquisition.acquisid).filter(Acquisition.acq_sample_id.in_(samples_4_prj)) objs_for_acqs: Query = Query(ObjectHeader.objid).filter(ObjectHeader.acquisid.in_(acqs_4_samples)) qry = qry.filter(ObjectFields.objfid.in_(objs_for_acqs)) # type: ignore qry = qry.update(values=values, synchronize_session=False) logger.info("Remap query for %s: %s", table.__tablename__, qry)
def update(self, session: Session, title: str, visible: bool, status: str, projtype: str, init_classif_list: List[int], classiffieldlist: str, popoverfieldlist: str, cnn_network_id: str, comments: str, contact: Any, managers: List[Any], annotators: List[Any], viewers: List[Any], license_: str): assert contact is not None, "A valid Contact is needed." proj_id = self._project.projid # Field reflexes if cnn_network_id != self._project.cnn_network_id: sub_qry: Query = session.query(ObjectHeader.objid) sub_qry = sub_qry.join(Acquisition, Acquisition.acquisid == ObjectHeader.acquisid) sub_qry = sub_qry.join(Sample, and_(Sample.sampleid == Acquisition.acq_sample_id, Sample.projid == proj_id)) # Delete CNN features which depend on the CNN network qry: Query = session.query(ObjectCNNFeature) qry = qry.filter(ObjectCNNFeature.objcnnid.in_(sub_qry.subquery())) qry.delete(synchronize_session=False) # Fields update self._project.title = title self._project.visible = visible self._project.status = status self._project.projtype = projtype self._project.classiffieldlist = classiffieldlist self._project.popoverfieldlist = popoverfieldlist self._project.cnn_network_id = cnn_network_id self._project.comments = comments self._project.license = license_ # Inverse for extracted values self._project.initclassiflist = ",".join([str(cl_id) for cl_id in init_classif_list]) # Inverse for users by privilege # Dispatch members by right # TODO: Nothing prevents or cares about redundant rights, such as adding same # user as both Viewer and Annotator. by_right = {ProjectPrivilegeBO.MANAGE: managers, ProjectPrivilegeBO.ANNOTATE: annotators, ProjectPrivilegeBO.VIEW: viewers} # Remove all to avoid tricky diffs session.query(ProjectPrivilege). \ filter(ProjectPrivilege.projid == proj_id).delete() # Add all contact_used = False for a_right, a_user_list in by_right.items(): for a_user in a_user_list: # Set flag for contact person extra = None if a_user.id == contact.id and a_right == ProjectPrivilegeBO.MANAGE: extra = 'C' contact_used = True session.add(ProjectPrivilege(projid=proj_id, member=a_user.id, privilege=a_right, extra=extra)) # Sanity check assert contact_used, "Could not set Contact, the designated user is not in Managers list." session.commit()
def get_date_range(cls, session: Session, project_ids: ProjectIDListT) -> Iterable[datetime]: # TODO: Why using the view? sql = ("SELECT min(o.objdate), max(o.objdate)" " FROM objects o " " WHERE o.projid = ANY(:prj)") res: Result = session.execute(text(sql), {"prj": project_ids}) vals = res.first() assert vals return [a_val for a_val in vals]
def get_bounding_geo(cls, session: Session, project_ids: ProjectIDListT) -> Iterable[float]: # TODO: Why using the view? sql = ("SELECT min(o.latitude), max(o.latitude), min(o.longitude), max(o.longitude)" " FROM objects o " " WHERE o.projid = ANY(:prj)") res: Result = session.execute(text(sql), {"prj": project_ids}) vals = res.first() assert vals return [a_val for a_val in vals]
def get_all_num_columns_values(self, session: Session): """ Get all numerical free fields values for all objects in a project. """ from DB.helpers.ORM import MetaData metadata = MetaData(bind=session.get_bind()) # TODO: Cache in a member mappings = ProjectMapping().load_from_project(self._project) num_fields_cols = set([col for col in mappings.object_mappings.tsv_cols_to_real.values() if col[0] == 'n']) obj_fields_tbl = minimal_table_of(metadata, ObjectFields, num_fields_cols, exact_floats=True) qry: Query = session.query(Project) qry = qry.join(Project.all_samples).join(Sample.all_acquisitions).join(Acquisition.all_objects) qry = qry.join(obj_fields_tbl, ObjectHeader.objid == obj_fields_tbl.c.objfid) qry = qry.filter(Project.projid == self._project.projid) qry = qry.order_by(Acquisition.acquisid) qry = qry.with_entities(Acquisition.acquisid, Acquisition.orig_id, obj_fields_tbl) return qry.all()
def get_all_object_ids(cls, session: Session, prj_id: int): # TODO: Problem with recursive import -> ObjetIdListT: """ Return the full list of objects IDs inside a project. TODO: Maybe better in ObjectBO """ qry: Query = session.query(ObjectHeader.objid) qry = qry.join(Acquisition, Acquisition.acquisid == ObjectHeader.acquisid) qry = qry.join(Sample, and_(Sample.sampleid == Acquisition.acq_sample_id, Sample.projid == prj_id)) return [an_id for an_id, in qry.all()]
def update_parent_objects(how: ImportHow, session: Session, dicts_for_update: Mapping[str, Dict]): """ Update of Sample, Acquisition & Process. For locating the records, we tolerate the lack of orig_id like during creation. """ assert how.can_update_only upper_level_pk = how.prj_id # Loop up->down, i.e. Sample to Process parent_class: ParentTableClassT for alias, parent_class in GlobalMapping.PARENT_CLASSES.items(): # The data from TSV, to update with. Eventually just an empty dict, but still a dict. dict_for_update = dicts_for_update[alias] if parent_class != Process: # Locate using Sample & Acquisition orig_id parent_orig_id = dict_for_update.get("orig_id") if parent_orig_id is None: # No orig_id for parent object in provided dict # Default with present parent's parent technical ID parent_orig_id = '__DUMMY_ID__%d__' % upper_level_pk # Look for the parent by its (eventually amended) orig_id parents_for_obj: Dict[ str, ParentTableT] = how.existing_parents[alias] parent = parents_for_obj.get(parent_orig_id) if parent is None: # No parent found to update, thus we cannot locate children, as there # is an implicit relationship just by the fact that the 3 are on the same line break # Collect the PK for children in case we need to use a __DUMMY upper_level_pk = parent.pk() else: # Fetch the process from DB parent = session.query(Process).get(upper_level_pk) assert parent is not None # OK we have something to update # Update the DB line using sqlalchemy updates = TSVFile.update_orm_object(parent, dict_for_update) if len(updates) > 0: logger.info("Updating %s '%s' using %s", alias, parent.orig_id, updates) session.flush()
def incremental_update_taxo_stats(cls, session: Session, prj_id: int, collated_changes: Dict): """ Do not recompute the full stats for a project (which can be long). Instead, apply deltas because in this context we know them. TODO: All SQL to SQLAlchemy form """ needed_ids = list(collated_changes.keys()) # Lock taxo lines to prevent re-entering, during validation it's often a handful of them. pts_sql = """SELECT id FROM taxonomy WHERE id = ANY(:ids) FOR NO KEY UPDATE """ session.execute(text(pts_sql), {"ids": needed_ids}) # Lock the rows we are going to update, including -1 for unclassified pts_sql = """SELECT id, nbr FROM projects_taxo_stat WHERE projid = :prj AND id = ANY(:ids) FOR NO KEY UPDATE""" res = session.execute(text(pts_sql), {"prj": prj_id, "ids": needed_ids}) ids_in_db = {classif_id: nbr for (classif_id, nbr) in res.fetchall()} ids_not_in_db = set(needed_ids).difference(ids_in_db.keys()) if len(ids_not_in_db) > 0: # Insert rows for missing IDs pts_ins = """INSERT INTO projects_taxo_stat(projid, id, nbr, nbr_v, nbr_d, nbr_p) SELECT :prj, COALESCE(obh.classif_id, -1), COUNT(*) nbr, COUNT(CASE WHEN obh.classif_qual = 'V' THEN 1 END) nbr_v, COUNT(CASE WHEN obh.classif_qual = 'D' THEN 1 END) nbr_d, COUNT(CASE WHEN obh.classif_qual = 'P' THEN 1 END) nbr_p FROM obj_head obh JOIN acquisitions acq ON acq.acquisid = obh.acquisid JOIN samples sam ON sam.sampleid = acq.acq_sample_id AND sam.projid = :prj WHERE COALESCE(obh.classif_id, -1) = ANY(:ids) GROUP BY obh.classif_id""" session.execute(text(pts_ins), {'prj': prj_id, 'ids': list(ids_not_in_db)}) # Apply delta for classif_id, chg in collated_changes.items(): if classif_id in ids_not_in_db: # The line was created just above, with OK values continue if ids_in_db[classif_id] + chg['n'] == 0: # The delta means 0 for this taxon in this project, delete the line sqlparam = {'prj': prj_id, 'cid': classif_id} ts_sql = """DELETE FROM projects_taxo_stat WHERE projid = :prj AND id = :cid""" else: # General case sqlparam = {'prj': prj_id, 'cid': classif_id, 'nul': chg['n'], 'val': chg['V'], 'dub': chg['D'], 'prd': chg['P']} ts_sql = """UPDATE projects_taxo_stat SET nbr=nbr+:nul, nbr_v=nbr_v+:val, nbr_d=nbr_d+:dub, nbr_p=nbr_p+:prd WHERE projid = :prj AND id = :cid""" session.execute(text(ts_sql), sqlparam)
def list_public_projects(session: Session, title_filter: str = '') -> List[ProjectIDT]: """ :param session: :param title_filter: If set, filter out the projects with title not matching the required string. :return: The project IDs """ pattern = '%' + title_filter + '%' qry: Query = session.query(Project.projid) qry = qry.filter(Project.visible) qry = qry.filter(Project.title.ilike(pattern)) ret = [an_id for an_id, in qry.all()] return ret
def fetch_existing_images(session: Session, prj_id): """ Get all object/image pairs from the project """ # Must be reloaded from DB, as phase 1 added all objects for duplicates checking # TODO: Why using the view? sql = text("SELECT concat(o.orig_id,'*',i.orig_file_name) " " FROM images i " " JOIN objects o ON i.objid = o.objid " " WHERE o.projid = :prj") res: Result = session.execute(sql, {"prj": prj_id}) ret = {img_id for img_id, in res} return ret
def delete_object_parents(cls, session: Session, prj_id: int) -> List[int]: """ Remove object parents, also project children entities, in the project. """ # The EcoTaxa samples which are going to disappear. We have to cleanup Particle side. soon_deleted_samples: Query = Query(Sample.sampleid).filter(Sample.projid == prj_id) # The EcoPart samples to clean. soon_invalid_part_samples: Query = Query(ParticleSample.psampleid).filter( ParticleSample.sampleid.in_(soon_deleted_samples)) # Cleanup EcoPart corresponding tables del_qry = ParticleCategoryHistogramList.__table__. \ delete().where(ParticleCategoryHistogramList.psampleid.in_(soon_invalid_part_samples)) logger.info("Del part histo lst :%s", str(del_qry)) session.execute(del_qry) del_qry = ParticleCategoryHistogram.__table__. \ delete().where(ParticleCategoryHistogram.psampleid.in_(soon_invalid_part_samples)) logger.info("Del part histo :%s", str(del_qry)) session.execute(del_qry) upd_qry = ParticleSample.__table__. \ update().where(ParticleSample.psampleid.in_(soon_invalid_part_samples)).values(sampleid=None) logger.info("Upd part samples :%s", str(upd_qry)) row_count = session.execute(upd_qry).rowcount logger.info(" %d EcoPart samples unlinked and cleaned", row_count) ret = [] del_acquis_qry: Delete = Acquisition.__table__. \ delete().where(Acquisition.acq_sample_id.in_(soon_deleted_samples)) logger.info("Del acquisitions :%s", str(del_acquis_qry)) gone_acqs = session.execute(del_acquis_qry).rowcount ret.append(gone_acqs) logger.info("%d rows deleted", gone_acqs) del_sample_qry: Delete = Sample.__table__. \ delete().where(Sample.sampleid.in_(soon_deleted_samples)) logger.info("Del samples :%s", str(del_sample_qry)) gone_sams = session.execute(del_sample_qry).rowcount ret.append(gone_sams) logger.info("%d rows deleted", gone_sams) ret.append(gone_acqs) session.commit() return ret
def read_user_stats(session: Session, prj_ids: ProjectIDListT) -> List[ProjectUserStats]: """ Read the users (annotators) involved in each project. Also compute a summary of their activity. This can only be an estimate since, e.g. imported data contains exact same data as the one obtained from live actions. """ # Activity count: Count 1 for present classification for a user per object. # Of course, the classification date is the latest for the user. pqry: Query = session.query(Project.projid, User.id, User.name, func.count(ObjectHeader.objid), func.max(ObjectHeader.classif_when)) pqry = pqry.join(Sample).join(Acquisition).join(ObjectHeader) pqry = pqry.join(User, User.id == ObjectHeader.classif_who) pqry = pqry.filter(Project.projid == any_(prj_ids)) pqry = pqry.filter(ObjectHeader.classif_who == User.id) pqry = pqry.group_by(Project.projid, User.id) pqry = pqry.order_by(Project.projid, User.name) ret = [] user_activities: Dict[UserIDT, UserActivity] = {} user_activities_per_project = {} stats_per_project = {} with CodeTimer("user present stats for %d projects, qry: %s:" % (len(prj_ids), str(pqry)), logger): last_prj = None for projid, user_id, user_name, cnt, last_date in pqry.all(): last_date_str = last_date.replace(microsecond=0).isoformat() if projid != last_prj: last_prj = projid prj_stat = ProjectUserStats((projid, [], [])) ret.append(prj_stat) user_activities = {} # Store for second pass with history stats_per_project[projid] = prj_stat user_activities_per_project[projid] = user_activities prj_stat.annotators.append(MinimalUserBO((user_id, user_name))) user_activity = UserActivity((user_id, cnt, last_date_str)) prj_stat.activities.append(user_activity) # Store for second pass user_activities[user_id] = user_activity # Activity count update: Add 1 for each entry in history for each user. # The dates in history are ignored, except for users which do not appear in first resultset. hqry: Query = session.query(Project.projid, User.id, User.name, func.count(ObjectsClassifHisto.objid), func.max(ObjectsClassifHisto.classif_date)) hqry = hqry.join(Sample).join(Acquisition).join(ObjectHeader).join(ObjectsClassifHisto) hqry = hqry.join(User, User.id == ObjectsClassifHisto.classif_who) hqry = hqry.filter(Project.projid == any_(prj_ids)) hqry = hqry.group_by(Project.projid, User.id) hqry = hqry.order_by(Project.projid, User.name) with CodeTimer("user history stats for %d projects, qry: %s:" % (len(prj_ids), str(hqry)), logger): last_prj = None for projid, user_id, user_name, cnt, last_date in hqry.all(): last_date_str = last_date.replace(microsecond=0).isoformat() if projid != last_prj: last_prj = projid # Just in case if projid not in user_activities_per_project: continue # Get stored data for the project user_activities = user_activities_per_project[projid] prj_stat = stats_per_project[projid] already_there = user_activities.get(user_id) if already_there is not None: # A user in both history and present classification already_there.nb_actions += cnt else: # A user _only_ in history prj_stat.annotators.append(MinimalUserBO((user_id, user_name))) user_activity = UserActivity((user_id, cnt, last_date_str)) prj_stat.activities.append(user_activity) user_activities[user_id] = user_activity return ret
def projects_for_user(session: Session, user: User, for_managing: bool = False, not_granted: bool = False, title_filter: str = '', instrument_filter: str = '', filter_subset: bool = False) -> List[ProjectIDT]: """ :param session: :param user: The user for which the list is needed. :param for_managing: If set, list the projects that the user can manage. :param not_granted: If set, list (only) the projects on which given user has no right, so user can request access to them. :param title_filter: If set, filter out the projects with title not matching the required string, or if set to a number, filter out the projects of which ID does not match. :param instrument_filter: If set, filter out the projects which do not have given instrument in at least one sample. :param filter_subset: If set, filter out any project of which title contains 'subset'. :return: The project IDs """ sql_params: Dict[str, Any] = {"user_id": user.id} # Default query: all projects, eventually with first manager information # noinspection SqlResolve sql = """SELECT p.projid FROM projects p LEFT JOIN ( """ + ProjectPrivilegeBO.first_manager_by_project() + """ ) fpm ON fpm.projid = p.projid """ if not_granted: # Add the projects for which no entry is found in ProjectPrivilege sql += """ LEFT JOIN projectspriv pp ON p.projid = pp.projid AND pp.member = :user_id WHERE pp.member is null """ if for_managing: sql += " AND False " else: if not user.has_role(Role.APP_ADMINISTRATOR): # Not an admin, so restrict to projects which current user can work on, or view sql += """ JOIN projectspriv pp ON p.projid = pp.projid AND pp.member = :user_id """ if for_managing: sql += """ AND pp.privilege = '%s' """ % ProjectPrivilegeBO.MANAGE sql += " WHERE 1 = 1 " if title_filter != '': sql += """ AND ( title ILIKE '%%'|| :title ||'%%' OR TO_CHAR(p.projid,'999999') LIKE '%%'|| :title ) """ sql_params["title"] = title_filter if instrument_filter != '': sql += """ AND p.projid IN (SELECT DISTINCT sam.projid FROM samples sam, acquisitions acq WHERE acq.acq_sample_id = sam.sampleid AND acq.instrument ILIKE '%%'|| :instrum ||'%%' ) """ sql_params["instrum"] = instrument_filter if filter_subset: sql += """ AND NOT title ILIKE '%%subset%%' """ with CodeTimer("Projects query:", logger): res: Result = session.execute(text(sql), sql_params) # single-element tuple :( DBAPI ret = [an_id for an_id, in res.fetchall()] return ret # type:ignore
def query_pg_and_cache(self, table_name: str, pg_sess: Session, cache_sql: str) -> int: logger.info("For cache fetch: %s", cache_sql) res: Result = pg_sess.execute(cache_sql) tbl_cols = self.create_sqlite_table(table_name, res) nb_ins = self.pg_to_sqlite(table_name, tbl_cols, res) return nb_ins