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 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 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 __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 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 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 __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 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, 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 __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 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 _get_result(self, session: Session):
res = session.execute(self.qry)
ret = [tuple(a_row) for a_row in res]
return set(ret)