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 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 __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 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 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 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 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 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 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