fetch-all-descendants.py

@paginate
def fetch_all_descendants(self, fragment_id, *expr, **kwargs):
    """
    Returns all the descending fragments of the fragment with the given
    fragment_id using an recursive SQL query.
    """
    # query part that will be used in both parts of the recursive query
    query = Fragment.query.with_entities(Fragment.fragment_id)
    query = query.join(FragmentHierarchy, FragmentHierarchy.child_id==Fragment.fragment_id)

    # fragment_id is the recursive term
    descendants = query.filter(FragmentHierarchy.parent_id==fragment_id)
    descendants = descendants.cte(name="descendants", recursive=True)

    # alias is necessary to reference to the statement in the recursive part
    desc_alias = aliased(descendants, name="d")

    # recursive part
    end = query.join(desc_alias, desc_alias.c.fragment_id==FragmentHierarchy.parent_id)
    end = end.filter(FragmentHierarchy.child_id!=None)
    descendants = descendants.union(end)

    # Join the recursive statement against the fragments table to get all
    # the fragment entities and add optional filter expressions
    query = self.query.join(descendants, Fragment.fragment_id==descendants.c.fragment_id)
    query = query.filter(and_(*expr)).order_by(Fragment.fragment_id.asc())

    return query

fetch-all-leaves.py

    def fetch_all_leaves(self, fragment_id, *expr, **kwargs):
        """
        Returns only the terminal (leaf) fragments of the fragment with the
        given fragment_id.
        """
        return self.fetch_all_descendants(fragment_id, Fragment.is_terminal==True,
                                          *expr, **kwargs)

fragment-contacts.py

# pick a rule-of-three compliant fragments
fragment = root.Descendants.filter(Fragment.is_rule_of_three==True).all()[3]
fragment.ism
u'c1cc(cnc1)c2ccncn2'

# fragments are mapped onto ligands and their atoms:
# pick a random one of the list
lf = fragment.LigandFragments[0]

# this is the ligand this fragment is part of
lf.Ligand
<Ligand(1OPJ/2/B/STI`4)>

# get all the hydrogen bonds this fragment has in this ligand
lf.Contacts.filter(Contact.is_hbond==True).all()
[<Contact(79831427, 79830051)>, <Contact(79830056, 79829947)>]

fragment-cte-examples.py

from credoscript.models import *

# get the root fragment of Imatinib
sti = ChemComp.query.filter_by(het_id='STI').first()
root = sti.Fragments.filter(ChemCompFragment.is_root==True).first()

# The Descendants property is implemented with the fetch_all_descendants()
# method of the FragmentAdaptor class
root.Descendants.all()
[<Fragment(3)>, <Fragment(641)>, <Fragment(683)>, 
 <Fragment(1116)>, <Fragment(2181)>, <Fragment(3366)>,
 <Fragment(8362)>, <Fragment(14861)>, <Fragment(16510)>, 
 <Fragment(20127)>, <Fragment(28509)>, <Fragment(33256)>,
 <Fragment(34818)>, <Fragment(43139)>, <Fragment(43835)>]

# only the terminal fragments
# implemented through fetch_all_leaves() with @property decorator
root.Leaves.all()
[<Fragment(3)>, <Fragment(641)>, <Fragment(683)>,
 <Fragment(1116)>, <Fragment(2181)>, <Fragment(3366)>]

# only rule-of-three compliant descendants
root.Descendants.filter(Fragment.is_rule_of_three==True).all() 
[<Fragment(1116)>, <Fragment(2181)>, <Fragment(3366)>,
 <Fragment(8362)>, <Fragment(16510)>]

fragment-descendants.sql

    WITH RECURSIVE descendants(fragment_id) AS
         (
          SELECT f.*
            FROM pdbchem.fragment_hierarchies h
            JOIN pdbchem.fragments f ON f.fragment_id = h.child_id
           WHERE h.parent_id = fragment_in_id
           UNION
          SELECT f.*
            FROM pdbchem.fragments f,
                 pdbchem.fragment_hierarchies h,
                 descendants d
           WHERE h.child_id = f.fragment_id
                 AND h.parent_id = d.fragment_id
                 AND h.child_id IS NOT NULL
          )
  SELECT *
    FROM descendants
ORDER BY 1 DESC;