Esempio n. 1
0
def create_view(metadata, molecule_design_pool_tbl,
                stock_sample_tbl, sample_tbl, container_tbl):
    """
    stock_info_view factory.
    """
    mdp = molecule_design_pool_tbl
    ss = stock_sample_tbl
    c = container_tbl
    s = sample_tbl
    stock = select([
            (literal('mdp')
             + cast(mdp.c.molecule_design_set_id, String)
             + literal('c')
             + cast(coalesce(ss.c.concentration * 1e6, 0),
                    String)).label('stock_info_id'),
            mdp.c.molecule_design_set_id,
            # We need to set the label explicitly here because
            # mdp.c.molecule_type_id is really mdp.c.molecule_type.
            mdp.c.molecule_type_id.label('molecule_type_id'),
            # pylint: disable=E1101
            coalesce(ss.c.concentration, 0).label('concentration'),
            coalesce(func.count(c.c.container_id), 0).label('total_tubes'),
            coalesce(func.sum(s.c.volume), 0).label('total_volume'),
            coalesce(func.min(s.c.volume), 0).label('minimum_volume'),
            coalesce(func.max(s.c.volume), 0).label('maximum_volume')
            # pylint: enable=E1101
            ],
            from_obj=
                mdp.outerjoin(ss, ss.c.molecule_design_set_id ==
                                        mdp.c.molecule_design_set_id) \
                .outerjoin(s, s.c.sample_id == ss.c.sample_id) \
                .outerjoin(c,
                           and_(c.c.container_id == s.c.container_id,
                                c.c.item_status ==
                                    _STOCK_CONTAINER_ITEM_STATUS))
                 ).group_by(mdp.c.molecule_design_set_id,
                            ss.c.concentration).alias('ssi')
    fkey_mds = ForeignKey(mdp.c.molecule_design_set_id)
    fkey_mds.parent = stock.c.molecule_design_set_id
    stock.c.molecule_design_set_id.foreign_keys.add(fkey_mds)
    fkey_mt = ForeignKey(mdp.c.molecule_type_id)
    fkey_mt.parent = stock.c.molecule_type_id
    stock.c.molecule_type_id.foreign_keys.add(fkey_mt)
    return view_factory(VIEW_NAME, metadata, stock)
Esempio n. 2
0
def create_view(metadata, molecule_design_pool_tbl, stock_sample_tbl, sample_tbl, container_tbl):
    """
    stock_info_view factory.
    """
    mdp = molecule_design_pool_tbl
    ss = stock_sample_tbl
    c = container_tbl
    s = sample_tbl
    stock = (
        select(
            [
                (
                    literal("mdp")
                    + cast(mdp.c.molecule_design_set_id, String)
                    + literal("c")
                    + cast(coalesce(ss.c.concentration * 1e6, 0), String)
                ).label("stock_info_id"),
                mdp.c.molecule_design_set_id,
                # We need to set the label explicitly here because
                # mdp.c.molecule_type_id is really mdp.c.molecule_type.
                mdp.c.molecule_type_id.label("molecule_type_id"),
                # pylint: disable=E1101
                coalesce(ss.c.concentration, 0).label("concentration"),
                coalesce(func.count(c.c.container_id), 0).label("total_tubes"),
                coalesce(func.sum(s.c.volume), 0).label("total_volume"),
                coalesce(func.min(s.c.volume), 0).label("minimum_volume"),
                coalesce(func.max(s.c.volume), 0).label("maximum_volume")
                # pylint: enable=E1101
            ],
            from_obj=mdp.outerjoin(ss, ss.c.molecule_design_set_id == mdp.c.molecule_design_set_id)
            .outerjoin(s, s.c.sample_id == ss.c.sample_id)
            .outerjoin(c, and_(c.c.container_id == s.c.container_id, c.c.item_status == _STOCK_CONTAINER_ITEM_STATUS)),
        )
        .group_by(mdp.c.molecule_design_set_id, ss.c.concentration)
        .alias("ssi")
    )
    fkey_mds = ForeignKey(mdp.c.molecule_design_set_id)
    fkey_mds.parent = stock.c.molecule_design_set_id
    stock.c.molecule_design_set_id.foreign_keys.add(fkey_mds)
    fkey_mt = ForeignKey(mdp.c.molecule_type_id)
    fkey_mt.parent = stock.c.molecule_type_id
    stock.c.molecule_type_id.foreign_keys.add(fkey_mt)
    return view_factory(VIEW_NAME, metadata, stock)
Esempio n. 3
0
def create_view(metadata, molecule_design_tbl, molecule_design_structure_tbl,
                chemical_structure_tbl):
    """
    molecule_type_modification_view factory.
    """
    md = molecule_design_tbl
    mds = molecule_design_structure_tbl
    chs = chemical_structure_tbl
    modification = \
      select([md.c.molecule_type_id.label('molecule_type_id'),
              chs.c.representation.label('name'),
              chs.c.chemical_structure_id],
             from_obj=md \
              .join(mds,
                    mds.c.molecule_design_id == md.c.molecule_design_id) \
              .join(chs,
                    and_(chs.c.chemical_structure_id ==
                                        mds.c.chemical_structure_id,
                         chs.c.structure_type_id ==
                                CHEMICAL_STRUCTURE_TYPE_IDS.MODIFICATION))
             )
    return view_factory(VIEW_NAME, metadata, modification)
def create_view(metadata, molecule_design_tbl, molecule_design_structure_tbl,
                chemical_structure_tbl):
    """
    molecule_type_modification_view factory.
    """
    md = molecule_design_tbl
    mds = molecule_design_structure_tbl
    chs = chemical_structure_tbl
    modification = \
      select([md.c.molecule_type_id.label('molecule_type_id'),
              chs.c.representation.label('name'),
              chs.c.chemical_structure_id],
             from_obj=md \
              .join(mds,
                    mds.c.molecule_design_id == md.c.molecule_design_id) \
              .join(chs,
                    and_(chs.c.chemical_structure_id ==
                                        mds.c.chemical_structure_id,
                         chs.c.structure_type_id ==
                                CHEMICAL_STRUCTURE_TYPE_IDS.MODIFICATION))
             )
    return view_factory(VIEW_NAME, metadata, modification)
def create_view(metadata, molecule_tbl, single_supplier_molecule_design_tbl,
                supplier_molecule_design_tbl):
    """
    molecule_type_modification_view factory.
    """
    m = molecule_tbl
    ssmd = single_supplier_molecule_design_tbl
    smd = supplier_molecule_design_tbl
    msmd = \
      select([m.c.molecule_id,
              smd.c.supplier_molecule_design_id],
             from_obj=m \
              .join(ssmd,
                    ssmd.c.molecule_design_id == m.c.molecule_design_id) \
              .join(smd,
                    and_(smd.c.supplier_molecule_design_id ==
                                    ssmd.c.supplier_molecule_design_id,
                         smd.c.supplier_id == m.c.supplier_id,
                         smd.c.is_current))
              )
    fkey_m = ForeignKey(m.c.molecule_id)
    fkey_m.parent = msmd.c.molecule_id
    msmd.c.molecule_id.foreign_keys.add(fkey_m)
    return view_factory(VIEW_NAME, metadata, msmd)
Esempio n. 6
0
def create_view(metadata, molecule_tbl, single_supplier_molecule_design_tbl,
                supplier_molecule_design_tbl):
    """
    molecule_type_modification_view factory.
    """
    m = molecule_tbl
    ssmd = single_supplier_molecule_design_tbl
    smd = supplier_molecule_design_tbl
    msmd = \
      select([m.c.molecule_id,
              smd.c.supplier_molecule_design_id],
             from_obj=m \
              .join(ssmd,
                    ssmd.c.molecule_design_id == m.c.molecule_design_id) \
              .join(smd,
                    and_(smd.c.supplier_molecule_design_id ==
                                    ssmd.c.supplier_molecule_design_id,
                         smd.c.supplier_id == m.c.supplier_id,
                         smd.c.is_current))
              )
    fkey_m = ForeignKey(m.c.molecule_id)
    fkey_m.parent = msmd.c.molecule_id
    msmd.c.molecule_id.foreign_keys.add(fkey_m)
    return view_factory(VIEW_NAME, metadata, msmd)