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