Web IQuery Usage

[jburel]

List the methods in web gateway using directly IQuery to retrieve data. So we can check impact when doing model changes.

File to review:

• omeroPy/src/gateway/init.py
• omeroWeb/webclient/webclient_gateway.py
• omeroWeb/webadmin/controller/drivespacepy.py

There are various calls in webemdb, webtest, webmobile. Those should be ignored as we are going to remove those apps from the main repository, see #8022

initial document in ​8044_using iQuery in web

[wmoore]

INVALID COMMENT, For details look in the linked google doc.

Pretty much every method in Blitz Gateway is using iQuery.

Default for retrieving any object via getObject() is

def _getQueryString(self):
        """
        Used for building queries in generic methods such as getObjects("Project")
        """
        return "select obj from %s obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent" % self.OMERO_CLASS

Overidden in various classes - NB: some of these seem unecessary~~

query = "select distinct obj from ExperimenterGroup as obj left outer join fetch obj.groupExperimenterMap " \
            "as map left outer join fetch map.child e"
query = "select obj from Plate as obj " \
              "join fetch obj.details.owner join fetch obj.details.group "\
              "join fetch obj.details.creationEvent "\
              "left outer join fetch obj.screenLinks spl " \
              "left outer join fetch spl.parent sc"
return "select obj from Annotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
            "join fetch obj.details.creationEvent"
return "select obj from FileAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
            "join fetch obj.details.creationEvent join fetch obj.file"
return "select obj from TimestampAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select obj from BooleanAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select obj from TagAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select obj from CommentAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
            "join fetch obj.details.creationEvent"
return "select obj from LongAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select obj from DoubleAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select obj from TermAnnotation obj join fetch obj.details.owner as owner join fetch obj.details.group "\
                "join fetch obj.details.creationEvent"
return "select distinct obj from Experimenter as obj left outer join fetch obj.groupExperimenterMap " \
            "as map left outer join fetch map.parent g"

Other examples

_listChildren
query = "select c from %s as c" % self.LINK_CLASS
        if ns is not None:
            params.map["ns"] = omero_type(ns)
        query += """ join fetch c.child as ch
                     left outer join fetch ch.annotationLinks as ial
                     left outer join fetch ial.child as a """
        query += " where c.parent.id=:dsid"
        if ns is not None:
            query += " and a.ns=:ns"
            if val is not None:
                if isinstance(val, StringTypes):
                    params.map["val"] = omero_type(val)
                    query +=" and a.textValue=:val"
        query += " order by c.child.name"

listParents
"from %s as c where c.%s.id=%i" % (pwck.LINK_CLASS, pwck.LINK_CHILD, self._oid)

getParentLinks
sql = "select pchl from %s as pchl " \
                "left outer join fetch pchl.parent as parent " \
                "left outer join fetch pchl.child as child " \
                "where child.id=:child" % link_class

getChildLinks
        sql = "select pchl from %s as pchl left outer join fetch pchl.child as child \
                left outer join fetch pchl.parent as parent where parent.id=:parent" % self.LINK_CLASS
        if isinstance(chids, list) and len(chids) > 0:
            p.map["children"] = rlist([rlong(ch) for ch in chids])
            sql+=" and child.id in (:children)"

_loadAnnotationLinks
query = "select l from %sAnnotationLink as l join fetch l.details.owner join fetch l.details.creationEvent "\
            "join fetch l.child as a join fetch a.details.owner join fetch a.details.creationEvent "\
            "where l.parent.id=%i" % (self.OMERO_CLASS, self._oid)

listOrphanedAnnotations
if anntype is not None:
            if anntype.title() not in ('Text', 'Tag', 'File', 'Long', 'Boolean'):
                raise AttributeError('It only retrieves: Text, Tag, File, Long, Boolean')
            sql = "select an from %sAnnotation as an " % anntype.title()
        else:
            sql = "select an from Annotation as an " \
        if anntype.title() == "File":
            sql += " join fetch an.file "
        sql += "where not exists ( select obal from %sAnnotationLink as obal "\
                "where obal.child=an.id and obal.parent.id=:oid) " % self.OMERO_CLASS

UpdateEventDate
getQueryService().get("Event", self._obj.details.updateEvent.id.val)

getGroupsLeaderOf
sql = "select e from ExperimenterGroup as e where e.id in (:ids)"

findExperimenters
rv = q.findAllByQuery("from Experimenter e where lower(e.omeName) like :start", params)

listStaffs
sql = "select e from Experimenter as e where " \
                "exists ( select gem from GroupExperimenterMap as gem where gem.child = e.id " \
                "and gem.parent.id in (:gids)) order by e.omeName"

getAnnotationLinks
query = "select annLink from %sAnnotationLink as annLink join fetch annLink.details.owner as owner " \
                "join fetch annLink.details.creationEvent " \
                "join fetch annLink.child as ann join fetch ann.details.owner join fetch ann.details.creationEvent "\
                "join fetch annLink.parent as parent" % wrapper().OMERO_CLASS

getObjectsByAnnotations
sql = "select ob from %s ob " \
              "left outer join fetch ob.annotationLinks obal " \
              "left outer join fetch obal.child ann " \
              "where ann.id in (:oids)" % wrapper().OMERO_CLASS

AnnotationWrapper.getParentLinks
sql = "select oal from %sAnnotationLink as oal left outer join fetch oal.child as ch " \
                "left outer join fetch oal.parent as pa " \
                "where ch.id=:aid " % (ptype.title())

listTagsInTagset
sql = "select tg from TagAnnotation tg "\
                "where exists ( select aal from AnnotationAnnotationLink as aal where aal.child.id=tg.id and aal.parent.id=:tid) "

PlateWrapper
sql = "select pa from PlateAcquisition as pa join fetch pa.plate as p where p.id=:pid"
sql = "select minIndex(ws), maxIndex(ws) from Well w " \
            "join w.wellSamples ws where w.plate.id=:oid"
query = "select well from Well as well "\
                    "join fetch well.details.creationEvent "\
                    "join fetch well.details.owner join fetch well.details.group " \
                    "left outer join fetch well.plate as pt "\
                    "left outer join fetch well.wellSamples as ws " \
                    "left outer join fetch ws.image as img "\
                    "where well.plate.id = :oid"

WellWrapper
query = "select well from Well as well "\
                "join fetch well.details.creationEvent "\
                "join fetch well.details.owner join fetch well.details.group " \
                "left outer join fetch well.wellSamples as ws " \
                "left outer join fetch ws.image as img "\
                "where well.id = %d" % self.getId()
query = """select s from Well w
        left outer join w.plate p
        left outer join p.screenLinks spl
        left outer join spl.parent s
        where spl.parent.id=s.id and spl.child.id=p.id and w.plate.id=p.id
        and w.id=:id"""

WellSampleWrapper
rv = self._conn.getQueryService().findAllByQuery("""select w from Well w 
            left outer join fetch w.wellSamples as ws
            where ws.id=%d""" % self.getId(), None)

copyPlaneInfo
query = "select info from PlaneInfo as info where pixels.id=:pid"
        if theC != None:
            params.map["theC"] = rint(theC)
            query += " and info.theC=:theC"
        if theT != None:
            params.map["theT"] = rint(theT)
            query += " and info.theT=:theT"
        if theZ != None:
            params.map["theZ"] = rint(theZ)
            query += " and info.theZ=:theZ"
        query += " order by info.deltaT"

ImageWrapper
select ds from Image i join i.datasetLinks dl join dl.parent ds
            where i.id = %i
select p from Image i join i.datasetLinks dl join dl.parent ds join ds.projectLinks pl join pl.parent p
            where i.id = %i

[jburel]

so we are entering a very dangerous territory, we may want to review and start using the services when/if possible to avoid terrible surprise when the model changes.

[atarkowska]

I am preparing the list of links to develop. Pasting a code in ticket is a doggy.

[atarkowska]

Replying to jburel:

so we are entering a very dangerous territory, we may want to review and start using the services when/if possible to avoid terrible surprise when the model changes.

It all depends of the changes. In order to make clear explanation, which objects exactly relay on generic/hardcoded queries it requires detailed explanation. For the moment the above comment should be ignored as it is not complex.

[atarkowska]

OK, Doc seams to be ready. Please have a look. I am happy to explain what is not clear, especially highlighted information.