def __init__(self, session: Session, taxon_ids: ClassifIDListT):
tf = Taxonomy.__table__.alias('tf')
# bind = None # For portable SQL, no 'ilike'
bind = session.get_bind()
select_list = [
tf.c.id, tf.c.display_name, tf.c.name, tf.c.nbrobj, tf.c.nbrobjcum
]
select_list.extend([
text("t%d.name" % level) # type:ignore
for level in range(1, TaxonomyBO.MAX_TAXONOMY_LEVELS)
])
qry = select(select_list, bind=bind)
# Inject the recursive query, for getting parents
_dumm, qry = TaxonomyBO._add_recursive_query(qry, tf, do_concat=False)
qry = qry.where(tf.c.id == any_(taxon_ids))
# Add another join for getting children
logger.info("Taxo query: %s with IDs %s", qry, taxon_ids)
res: ResultProxy = session.execute(qry)
self.taxa: List[TaxonBO] = []
for a_rec in res.fetchall():
lst_rec = list(a_rec)
an_id, display_name, db_name, nbobj1, nbobj2 = lst_rec.pop(0), lst_rec.pop(0), lst_rec.pop(0), \
lst_rec.pop(0), lst_rec.pop(0)
lineage = [db_name] + [name for name in lst_rec if name]
self.taxa.append(
TaxonBO(an_id, display_name, db_name, nbobj1, nbobj2,
lineage)) # type:ignore
self.get_children(session, self.taxa)
def generous_merge_into(cls, session: Session, dest_prj_id: int,
src_prj_id: int):
"""
Merge privileges from source project into destination project.
"""
# Each user who is present in both projects, gets the highest privilege from both projects.
# TODO: Arguable
sql = text("""
UPDATE projectspriv ppdst
SET privilege = CASE WHEN 'Manage' IN (ppsrc.privilege, ppdst.privilege)
THEN 'Manage'
WHEN 'Annotate' IN (ppsrc.privilege, ppdst.privilege)
THEN 'Annotate'
ELSE 'View'
END
FROM projectspriv ppsrc
WHERE ppsrc.projid = :src_prj
AND ppdst.projid = :dst_prj
AND ppsrc.member = ppdst.member""")
session.execute(sql, {"dst_prj": dest_prj_id, "src_prj": src_prj_id})
# Users who were only in source project get their privileges transferred into destination
# TODO: Arguable
sql = text("""
UPDATE projectspriv
SET projid = :dst_prj
WHERE projid = :src_prj
AND member NOT IN (SELECT member
FROM projectspriv
WHERE projid = :dst_prj)""")
session.execute(sql, {"dst_prj": dest_prj_id, "src_prj": src_prj_id})
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()
return nb_objs, nb_img_rows, img_files
def grant(session: Session, user: User, action: Action, prj: Project):
"""
Grant the possibility to do this action on this project to this user.
"""
privilege = ProjectPrivilege()
privilege.privilege = ACTION_TO_PRIV[action]
privilege.project = prj
privilege.user = user
session.add(privilege)
def __init__(self, session: Session, taxon_ids: ClassifIDListT):
tf = WoRMS.__table__.alias('tf')
# bind = None # Uncomment for portable SQL, no 'ilike'
bind = session.get_bind()
select_list = [tf.c.aphia_id, tf.c.scientificname]
select_list.extend([
text("t%d.aphia_id, t%d.scientificname" %
(level, level)) # type:ignore
for level in range(1, TaxonBOSetFromWoRMS.MAX_TAXONOMY_LEVELS)
])
qry = select(select_list, bind=bind)
# Inject a query on names and hierarchy
# Produced SQL looks like:
# left join worms t1 on tf.parent_name_usage_id=t1.aphia_id
# left join worms t2 on t1.parent_name_usage_id=t2.aphia_id
# ...
# left join worms t14 on t13.parent_name_usage_id=t14.aphia_id
lev_alias = WoRMS.__table__.alias('t1')
# Chain outer joins on Taxonomy, for parents
# hook the first OJ to main select
chained_joins = tf.join(
lev_alias,
lev_alias.c.aphia_id == tf.c.parent_name_usage_id,
isouter=True)
prev_alias = lev_alias
for level in range(2, self.MAX_TAXONOMY_LEVELS):
lev_alias = WoRMS.__table__.alias('t%d' % level)
# hook each following OJ to previous one
chained_joins = chained_joins.join(
lev_alias,
lev_alias.c.aphia_id == prev_alias.c.parent_name_usage_id,
isouter=True)
# Collect expressions
prev_alias = lev_alias
qry = qry.select_from(chained_joins)
qry = qry.where(tf.c.aphia_id == any_(taxon_ids))
logger.info("Taxo query: %s with IDs %s", qry, taxon_ids)
res: Result = session.execute(qry)
self.taxa = []
for a_rec in res.fetchall():
lst_rec = list(a_rec)
lineage_id = [an_id for an_id in lst_rec[0::2] if an_id]
lineage = [name for name in lst_rec[1::2] if name]
biota_pos = lineage.index('Biota') + 1
lineage = lineage[:biota_pos]
lineage_id = lineage_id[:biota_pos]
self.taxa.append(
TaxonBO('P', lineage[0], 0, 0, lineage,
lineage_id)) # type:ignore
self.get_children(session, self.taxa)
def __init__(self, session: Session, taxon_ids: ClassifIDListT):
tf = WoRMS.__table__.alias('tf')
# bind = None # For portable SQL, no 'ilike'
bind = session.get_bind()
select_list = [tf.c.aphia_id, tf.c.scientificname]
select_list.extend([
text("t%d.scientificname" % level) # type:ignore
for level in range(1, TaxonomyBO.MAX_TAXONOMY_LEVELS)
])
qry = select(select_list, bind=bind)
# Inject a query on names and hierarchy
# Produced SQL looks like:
# left join worms t1 on tf.parent_name_usage_id=t1.aphia_id
# left join worms t2 on t1.parent_name_usage_id=t2.aphia_id
# ...
# left join worms t14 on t13.parent_name_usage_id=t14.aphia_id
lev_alias = WoRMS.__table__.alias('t1')
# Chain outer joins on Taxonomy, for parents
# hook the first OJ to main select
chained_joins = tf.join(
lev_alias,
lev_alias.c.aphia_id == tf.c.parent_name_usage_id,
isouter=True)
prev_alias = lev_alias
for level in range(2, self.MAX_TAXONOMY_LEVELS):
lev_alias = WoRMS.__table__.alias('t%d' % level)
# hook each following OJ to previous one
chained_joins = chained_joins.join(
lev_alias,
lev_alias.c.aphia_id == prev_alias.c.parent_name_usage_id,
isouter=True)
# Collect expressions
prev_alias = lev_alias
qry = qry.select_from(chained_joins)
qry = qry.where(tf.c.aphia_id == any_(taxon_ids))
logger.info("Taxo query: %s with IDs %s", qry, taxon_ids)
res: ResultProxy = session.execute(qry)
self.taxa = []
for a_rec in res.fetchall():
lst_rec = list(a_rec)
an_id, display_name = lst_rec.pop(0), lst_rec.pop(0)
lineage = [name for name in lst_rec if name]
# In WoRMS, the root is signaled by having itself as parent
while lineage and lineage[-1] == lineage[-2]:
lineage.pop(-1)
self.taxa.append(
TaxonBO(an_id, display_name, display_name, 0, 0,
lineage)) # type:ignore
def resolve_taxa(session: Session, taxo_found, taxon_lower_list):
"""
Match taxa in taxon_lower_list and return the matched ones in taxo_found.
"""
res: ResultProxy = session.execute(
"""SELECT t.id, lower(t.name) AS name, lower(t.display_name) AS display_name,
lower(t.name)||'<'||lower(p.name) AS computedchevronname
FROM taxonomy t
LEFT JOIN taxonomy p on t.parent_id = p.id
WHERE lower(t.name) = ANY(:nms) OR lower(t.display_name) = ANY(:dms)
OR lower(t.name)||'<'||lower(p.name) = ANY(:chv) """, {
"nms": taxon_lower_list,
"dms": taxon_lower_list,
"chv": taxon_lower_list
})
for rec_taxon in res:
for found_k, found_v in taxo_found.items():
if ((found_k == rec_taxon['name'])
or (found_k == rec_taxon['display_name'])
or (found_k == rec_taxon['computedchevronname'])
or (('alterdisplayname' in found_v) and
(found_v['alterdisplayname']
== rec_taxon['display_name']))):
taxo_found[found_k]['nbr'] += 1
taxo_found[found_k]['id'] = rec_taxon['id']
def find_ids(session: Session, classif_id_seen: List):
"""
Return input IDs for the existing ones.
"""
sql = text("SELECT id " " FROM taxonomy " " WHERE id = ANY (:een)")
res: Result = session.execute(sql, {"een": list(classif_id_seen)})
return {int(r['id']) for r in res}
def recent_projects(self, session: Session):
"""
Return display information for last used projects.
"""
mru = self.prefs.get(self.RECENT_PROJECTS_KEY, [])
qry: Query = session.query(Project.projid, Project.title)
qry = qry.filter(Project.projid.in_(mru))
qry = qry.order_by(Project.title)
return [{"projid": projid, "title": title} for projid, title in qry.all()]
def keep_phylo(session: Session, classif_id_seen: ClassifIDListT):
"""
Return input IDs, for the existing ones with 'P' type.
"""
sql = text("SELECT id "
" FROM taxonomy "
" WHERE id = ANY (:een) AND taxotype = 'P'")
res: Result = session.execute(sql, {"een": list(classif_id_seen)})
return {an_id for an_id, in res}
def get_cardinalities(self, session: Session):
# Enrich TaxonBOs with number of objects. Due to ecotaxa/ecotaxa_dev#648, pick data from projects stats.
bos_per_id = {a_bo.id: a_bo for a_bo in self.taxa}
qry: Query = session.query(ProjectTaxoStat.id,
func.sum(ProjectTaxoStat.nbr_v))
qry = qry.filter(ProjectTaxoStat.id == any_(list(bos_per_id.keys())))
qry = qry.group_by(ProjectTaxoStat.id)
for an_id, a_sum in qry.all():
bos_per_id[an_id].nb_objects = a_sum
def get_children(self, session: Session, taxa_list: List[TaxonBO]):
# Enrich TaxonBOs with children
bos_per_id = {a_bo.id: a_bo for a_bo in taxa_list}
tch = Taxonomy.__table__.alias('tch')
qry: Query = session.query(Taxonomy.id, tch.c.id)
qry = qry.join(tch, tch.c.parent_id == Taxonomy.id)
qry = qry.filter(Taxonomy.id == any_(list(bos_per_id.keys())))
for an_id, a_child_id in qry.all():
bos_per_id[an_id].children.append(a_child_id)
def user_has_role(session: Session, user_id: int, role: str) -> User:
"""
Check user role. Should be temporary until a proper action is defined, e.g. refresh taxo tree.
"""
# Load ORM entity
user: User = session.query(User).get(user_id)
# Check
assert user.has_role(role), NOT_AUTHORIZED
return user
def __init__(self, session: Session, taxon_ids: ClassifIDListT):
tf = Taxonomy.__table__.alias('tf')
# bind = None # For portable SQL, no 'ilike'
bind = session.get_bind()
select_list = [
tf.c.taxotype,
tf.c.nbrobj,
tf.c.nbrobjcum,
tf.c.display_name,
tf.c.id,
tf.c.name,
]
select_list.extend([
text("t%d.id, t%d.name" % (level, level)) # type:ignore
for level in range(1, TaxonomyBO.MAX_TAXONOMY_LEVELS)
])
qry = select(select_list, bind=bind)
# Inject the recursive query, for getting parents
_dumm, qry = TaxonomyBO._add_recursive_query(qry, tf, do_concat=False)
qry = qry.where(tf.c.id == any_(taxon_ids))
# Add another join for getting children
logger.info("Taxo query: %s with IDs %s", qry, taxon_ids)
res: Result = session.execute(qry)
self.taxa: List[TaxonBO] = []
for a_rec in res.fetchall():
lst_rec = list(a_rec)
cat_type, nbobj1, nbobj2, display_name = lst_rec.pop(
0), lst_rec.pop(0), lst_rec.pop(0), lst_rec.pop(0)
lineage_id = [an_id for an_id in lst_rec[0::2] if an_id]
lineage = [name for name in lst_rec[1::2] if name]
# assert lineage_id[-1] in (1, 84960, 84959), "Unexpected root %s" % str(lineage_id[-1])
self.taxa.append(
TaxonBO(
cat_type,
display_name,
nbobj1,
nbobj2, # type:ignore
lineage,
lineage_id # type:ignore
))
self.get_children(session)
self.get_cardinalities(session)
def user_wants_create_project(session: Session, user_id: int) \
-> User:
"""
Check rights for the user to do this specific action.
"""
# Load ORM entity
user: User = session.query(User).get(user_id)
assert user is not None, NOT_AUTHORIZED
# Check
assert Action.CREATE_PROJECT in RightsBO.allowed_actions(
user), NOT_AUTHORIZED
return user
def user_wants(session: Session, user_id: int, action: Action, prj_id: int) \
-> Tuple[User, Project]:
"""
Check rights for the user to do this specific action onto this project.
"""
# Load ORM entities
user: User = session.query(User).get(user_id)
project = session.query(Project).get(prj_id)
assert project is not None, NOT_FOUND
# Check
if user.has_role(Role.APP_ADMINISTRATOR):
# King of the world
pass
else:
a_priv: ProjectPrivilege
# Collect privileges for user on project
# noinspection PyTypeChecker
rights_on_proj = {
a_priv.privilege
for a_priv in user.privs_on_projects if a_priv.projid == prj_id
}
if action == Action.ADMINISTRATE:
assert ProjectPrivilegeBO.MANAGE in rights_on_proj, NOT_AUTHORIZED
elif action == Action.ANNOTATE:
# TODO: Bah, not nice
assert ProjectPrivilegeBO.ANNOTATE in rights_on_proj \
or ProjectPrivilegeBO.MANAGE in rights_on_proj, NOT_AUTHORIZED
elif action == Action.READ:
# TODO: Bah, not nice either
assert project.visible \
or ProjectPrivilegeBO.VIEW in rights_on_proj \
or ProjectPrivilegeBO.ANNOTATE in rights_on_proj \
or ProjectPrivilegeBO.MANAGE in rights_on_proj, NOT_AUTHORIZED
else:
raise Exception("Not implemented")
# Keep the last accessed projects
if Preferences(user).add_recent_project(prj_id):
session.commit()
return user, project
def anonymous_wants(session: Session, action: Action, prj_id: int) \
-> Project:
"""
Check rights for an anonymous user to do this action.
"""
# Load ORM entities
project: Optional[Project] = session.query(Project).get(prj_id)
# Check
if project and action == Action.READ:
assert project.visible, NOT_AUTHORIZED
else:
assert False, NOT_AUTHORIZED
return project
def query(cls, session: Session, restrict_to: ClassifIDListT,
priority_set: ClassifIDListT, display_name_filter: str,
name_filters: List[str]):
"""
:param session:
:param restrict_to: If not None, limit the query to given IDs.
:param priority_set: Regardless of MAX_MATCHES, these IDs must appear in the result if they match.
:param display_name_filter:
:param name_filters:
:return:
"""
tf = Taxonomy.__table__.alias('tf')
# bind = None # For portable SQL, no 'ilike'
bind = session.get_bind()
# noinspection PyTypeChecker
priority = case([(tf.c.id == any_(priority_set), text('0'))],
else_=text('1')).label('prio')
qry = select([tf.c.taxotype, tf.c.id, tf.c.display_name, priority],
bind=bind)
if len(name_filters) > 0:
# Add to the query enough to get the full hierarchy for filtering
concat_all, qry = cls._add_recursive_query(qry, tf, do_concat=True)
# Below is quite expensive
taxo_lineage = func.concat(*concat_all)
name_filter = "%<" + "".join(
name_filters) # i.e. anywhere consecutively in the lineage
qry = qry.where(taxo_lineage.ilike(name_filter))
if restrict_to is not None:
qry = qry.where(tf.c.id == any_(restrict_to))
# We have index IS_TaxonomyDispNameLow so this lower() is for free
qry = qry.where(
func.lower(tf.c.display_name).like(display_name_filter))
qry = qry.order_by(priority, func.lower(tf.c.display_name))
qry = qry.limit(cls.MAX_MATCHES)
logger.info("Taxo query: %s with params %s and %s ", qry,
display_name_filter, name_filters)
res: Result = session.execute(qry)
return res.fetchall()
def names_with_parent_for(session: Session,
id_coll: ClassifIDCollT) -> ClassifSetInfoT:
"""
Get taxa names from id list.
"""
ret = {}
res: ResultProxy = session.execute(
"""SELECT t.id, t.name, p.name AS parent_name
FROM taxonomy t
LEFT JOIN taxonomy p ON t.parent_id = p.id
WHERE t.id = ANY(:ids) """, {"ids": list(id_coll)})
for rec_taxon in res:
ret[rec_taxon['id']] = (rec_taxon['name'],
rec_taxon['parent_name'])
return ret
def children_of(session: Session, id_list: List[int]) -> Set[int]:
"""
Get id and children taxa ids for given id.
"""
res: ResultProxy = session.execute(
"""WITH RECURSIVE rq(id)
AS (SELECT id
FROM taxonomy
WHERE id = ANY(:ids)
UNION
SELECT t.id
FROM rq
JOIN taxonomy t ON rq.id = t.parent_id )
SELECT id FROM rq """, {"ids": id_list})
return {int(r['id']) for r in res}
def __init__(self, session: Session, object_ids: Any,
obj_mapping: TableMapping):
needed_cols = obj_mapping.real_cols_to_tsv.keys()
# noinspection PyPep8Naming
ReducedObjectFields = minimal_model_of(MetaData(), ObjectFields,
set(needed_cols))
qry: Query = session.query(ObjectHeader, ReducedObjectFields)
qry = qry.filter(ObjectHeader.objid.in_(object_ids))
# noinspection PyUnresolvedReferences
qry = qry.join(
ReducedObjectFields,
ObjectHeader.objid == ReducedObjectFields.objfid) # type:ignore
qry = qry.options(joinedload(ObjectHeader.all_images))
self.all = [
ObjectBO(session, 0, an_obj, its_fields)
for an_obj, its_fields in qry.all()
]
def strict_match(
session: Session,
used_taxo_ids: ClassifIDListT) -> List[Tuple[Taxonomy, WoRMS]]:
"""
Candidate match until a better is found.
We match Phylo types taxa on one side.
using name <-> scientificname, case insensitive
And a _single_ accepted taxon on the other.
"""
subqry = TaxonomyChangeService.strict_match_subquery(
session, used_taxo_ids, phylo_or_morpho="P")
qry: Query = session.query(Taxonomy, WoRMS)
qry = qry.join(subqry, subqry.c.id == Taxonomy.id)
qry = qry.join(WoRMS, subqry.c.aphia_id == WoRMS.aphia_id)
logger.info("matching qry:%s", str(qry))
res = qry.all()
return res
def _get_result(self, session: Session):
res = session.execute(self.qry)
ret = [tuple(a_row) for a_row in res]
return set(ret)
