sqlalchemy.literal

Here are the examples of the python api sqlalchemy.literal taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

36 Examples 7

Example 1

Project: raco
Source File: catalog.py
View license
    def _convert_zeroary_expr(self, cols, expr, input_scheme):
        if isinstance(expr, expression.COUNTALL):
            return func.count(cols[0])
        if isinstance(expr, expression.Literal):
            return literal(expr.value,
                           raco_to_type[expr.typeof(input_scheme, None)])
        raise NotImplementedError("expression {} to sql".format(type(expr)))

Example 2

Project: sqlalchemy
Source File: test_insert.py
View license
    @classmethod
    def define_tables(cls, metadata):
        Table('autoinc_pk', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50))
              )
        Table('manual_pk', metadata,
              Column('id', Integer, primary_key=True, autoincrement=False),
              Column('data', String(50))
              )
        Table('includes_defaults', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50)),
              Column('x', Integer, default=5),
              Column('y', Integer,
                     default=literal_column("2", type_=Integer) + literal(2)))

Example 3

Project: changes
Source File: build_lib.py
View license
def contains_autogenerated_plan(build):
    """Returns true if any of the jobs in this build was created with an
    autogenerated jobplan.
    """
    # type: (Build)->bool
    contains = db.session.query(literal(True)).filter(
        ItemOption.query.join(
            JobPlan, JobPlan.plan_id == ItemOption.item_id,
        ).filter(
            ItemOption.name == 'bazel.autogenerate',
            ItemOption.value == '1',
            JobPlan.build_id == build.id,
        ).exists()
    ).scalar()
    return bool(contains)

Example 4

Project: changes
Source File: project_lib.py
View license
def contains_active_autogenerated_plan(project):
    """Returns true if there are any active autogenerated build plans for
    this project.
    """
    # type: (Project)->bool
    contains = db.session.query(literal(True)).filter(
        ItemOption.query.join(
            Plan, Plan.id == ItemOption.item_id,
        ).filter(
            ItemOption.name == 'bazel.autogenerate',
            ItemOption.value == '1',
            Plan.project_id == project.id,
            Plan.status == PlanStatus.active,
        ).exists()
    ).scalar()
    return bool(contains)

Example 5

Project: ggrc-core
Source File: query_helpers.py
View license
def get_type_select_column(model):
  """Get column name,taking into account polymorphic types."""
  mapper = model._sa_class_manager.mapper
  if mapper.polymorphic_on is None:
    type_column = literal(mapper.class_.__name__)
  else:
    # Handle polymorphic types with CASE
    type_column = case(
        value=mapper.polymorphic_on,
        whens={
            val: m.class_.__name__
            for val, m in mapper.polymorphic_map.items()
        })
  return type_column

Example 6

Project: indico
Source File: items.py
View license
    @is_visible.expression
    def is_visible(cls):
        sections = aliased(RegistrationFormSection)
        query = (db.session.query(literal(True))
                   .filter(sections.id == cls.parent_id)
                   .filter(~sections.is_deleted)
                   .filter(sections.is_enabled)
                   .exists())
        return cls.is_enabled & ~cls.is_deleted & ((cls.parent_id == None) | query)  # noqa

Example 7

Project: waymarked-trails-site
Source File: listings.py
View license
    @cherrypy.expose
    def segments(self, relations=None, bbox=None, **params):
        b = api.common.Bbox(bbox)

        objs = []

        if relations is not None:
            idlist = [ int(x) for x in relations.split(',') if x.isdigit() ]

            r = cherrypy.request.app.config['DB']['map'].tables.routes.data

            sel = sa.select([sa.literal("r"), r.c.id,
                             r.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                   .where(r.c.id.in_(idlist))

            objs = cherrypy.request.db.execute(sel)

        return self.create_segments_out(objs)

Example 8

Project: waymarked-trails-site
Source File: listings.py
View license
    @cherrypy.expose
    def segments(self, relations=None, bbox=None, **params):
        b = api.common.Bbox(bbox)

        objs = []

        if relations is not None:
            idlist = [ int(x) for x in relations.split(',') if x.isdigit() ]

            r = cherrypy.request.app.config['DB']['map'].tables.routes.data

            sel = sa.select([sa.literal("r"), r.c.id,
                             r.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                   .where(r.c.id.in_(idlist))

            objs = cherrypy.request.db.execute(sel)

        return self.create_segments_out(objs)

Example 9

Project: SickGear
Source File: test_types.py
View license
    @testing.provide_metadata
    def _literal_round_trip(self, type_, input_, output, filter_=None):
        """test literal rendering """

        # for literal, we test the literal render in an INSERT
        # into a typed column.  we can then SELECT it back as it's
        # official type; ideally we'd be able to use CAST here
        # but MySQL in particular can't CAST fully
        t = Table('t', self.metadata, Column('x', type_))
        t.create()

        for value in input_:
            ins = t.insert().values(x=literal(value)).compile(
                            dialect=testing.db.dialect,
                            compile_kwargs=dict(literal_binds=True)
                        )
            testing.db.execute(ins)

        for row in t.select().execute():
            value = row[0]
            if filter_ is not None:
                value = filter_(value)
            assert value in output

Example 10

Project: SickRage
Source File: test_types.py
View license
    @testing.provide_metadata
    def _literal_round_trip(self, type_, input_, output, filter_=None):
        """test literal rendering """

        # for literal, we test the literal render in an INSERT
        # into a typed column.  we can then SELECT it back as it's
        # official type; ideally we'd be able to use CAST here
        # but MySQL in particular can't CAST fully
        t = Table('t', self.metadata, Column('x', type_))
        t.create()

        for value in input_:
            ins = t.insert().values(x=literal(value)).compile(
                            dialect=testing.db.dialect,
                            compile_kwargs=dict(literal_binds=True)
                        )
            testing.db.execute(ins)

        for row in t.select().execute():
            value = row[0]
            if filter_ is not None:
                value = filter_(value)
            assert value in output

Example 11

Project: moviegrabber
Source File: test_types.py
View license
    @testing.provide_metadata
    def _literal_round_trip(self, type_, input_, output, filter_=None):
        """test literal rendering """

        # for literal, we test the literal render in an INSERT
        # into a typed column.  we can then SELECT it back as it's
        # official type; ideally we'd be able to use CAST here
        # but MySQL in particular can't CAST fully
        t = Table('t', self.metadata, Column('x', type_))
        t.create()

        for value in input_:
            ins = t.insert().values(x=literal(value)).compile(
                            dialect=testing.db.dialect,
                            compile_kwargs=dict(literal_binds=True)
                        )
            testing.db.execute(ins)

        for row in t.select().execute():
            value = row[0]
            if filter_ is not None:
                value = filter_(value)
            assert value in output

Example 12

Project: sqlalchemy
Source File: test_types.py
View license
    @testing.provide_metadata
    def _literal_round_trip(self, type_, input_, output, filter_=None):
        """test literal rendering """

        # for literal, we test the literal render in an INSERT
        # into a typed column.  we can then SELECT it back as its
        # official type; ideally we'd be able to use CAST here
        # but MySQL in particular can't CAST fully
        t = Table('t', self.metadata, Column('x', type_))
        t.create()

        for value in input_:
            ins = t.insert().values(x=literal(value)).compile(
                dialect=testing.db.dialect,
                compile_kwargs=dict(literal_binds=True)
            )
            testing.db.execute(ins)

        for row in t.select().execute():
            value = row[0]
            if filter_ is not None:
                value = filter_(value)
            assert value in output

Example 13

Project: sqlalchemy
Source File: test_compiler.py
View license
    def test_strict_binds(self):
        """test the 'strict' compiler binds."""

        from sqlalchemy.dialects.mssql.base import MSSQLStrictCompiler
        mxodbc_dialect = mxodbc.dialect()
        mxodbc_dialect.statement_compiler = MSSQLStrictCompiler

        t = table('sometable', column('foo'))

        for expr, compile in [
            (
                select([literal("x"), literal("y")]),
                "SELECT 'x' AS anon_1, 'y' AS anon_2",
            ),
            (
                select([t]).where(t.c.foo.in_(['x', 'y', 'z'])),
                "SELECT sometable.foo FROM sometable WHERE sometable.foo "
                "IN ('x', 'y', 'z')",
            ),
            (
                t.c.foo.in_([None]),
                "sometable.foo IN (NULL)"
            )
        ]:
            self.assert_compile(expr, compile, dialect=mxodbc_dialect)

Example 14

Project: sqlalchemy
Source File: test_query.py
View license
    def test_seven(self):
        self._test(literal(datetime.timedelta(seconds=10))
                   - literal(datetime.timedelta(seconds=10)), "all",
                   overrides={"hour": 0, "minute": 0, "month": 0,
                              "year": 0, "day": 0, "epoch": 0})

Example 15

Project: sqlalchemy
Source File: test_hybrid.py
View license
    @classmethod
    def setup_class(cls):
        from sqlalchemy import literal

        symbols = ('usd', 'gbp', 'cad', 'eur', 'aud')
        currency_lookup = dict(
            ((currency_from, currency_to), Decimal(str(rate)))
            for currency_to, values in zip(
                symbols,
                [
                    (1, 1.59009, 0.988611, 1.37979, 1.02962),
                    (0.628895, 1, 0.621732, 0.867748, 0.647525),
                    (1.01152, 1.6084, 1, 1.39569, 1.04148),
                    (0.724743, 1.1524, 0.716489, 1, 0.746213),
                    (0.971228, 1.54434, 0.960166, 1.34009, 1),
                ])
            for currency_from, rate in zip(symbols, values)
        )

        class Amount(object):
            def __init__(self, amount, currency):
                self.currency = currency
                self.amount = amount

            def __add__(self, other):
                return Amount(
                    self.amount +
                    other.as_currency(self.currency).amount,
                    self.currency
                )

            def __sub__(self, other):
                return Amount(
                    self.amount -
                    other.as_currency(self.currency).amount,
                    self.currency
                )

            def __lt__(self, other):
                return self.amount < other.as_currency(self.currency).amount

            def __gt__(self, other):
                return self.amount > other.as_currency(self.currency).amount

            def __eq__(self, other):
                return self.amount == other.as_currency(self.currency).amount

            def as_currency(self, other_currency):
                return Amount(
                    currency_lookup[(self.currency, other_currency)] *
                    self.amount,
                    other_currency
                )

            def __clause_element__(self):
                # helper method for SQLAlchemy to interpret
                # the Amount object as a SQL element
                if isinstance(self.amount, (float, int, Decimal)):
                    return literal(self.amount)
                else:
                    return self.amount

            def __str__(self):
                return "%2.4f %s" % (self.amount, self.currency)

            def __repr__(self):
                return "Amount(%r, %r)" % (self.amount, self.currency)

        Base = declarative_base()

        class BankAccount(Base):
            __tablename__ = 'bank_account'
            id = Column(Integer, primary_key=True)

            _balance = Column('balance', Numeric)

            @hybrid.hybrid_property
            def balance(self):
                """Return an Amount view of the current balance."""
                return Amount(self._balance, "usd")

            @balance.setter
            def balance(self, value):
                self._balance = value.as_currency("usd").amount

        cls.Amount = Amount
        cls.BankAccount = BankAccount

Example 16

Project: sqlalchemy
Source File: test_functions.py
View license
    def test_assorted(self):
        table1 = table('mytable',
                       column('myid', Integer),
                       )

        table2 = table(
            'myothertable',
            column('otherid', Integer),
        )

        # test an expression with a function
        self.assert_compile(func.lala(3, 4, literal("five"),
                                      table1.c.myid) * table2.c.otherid,
                            "lala(:lala_1, :lala_2, :param_1, mytable.myid) * "
                            "myothertable.otherid")

        # test it in a SELECT
        self.assert_compile(select(
            [func.count(table1.c.myid)]),
            "SELECT count(mytable.myid) AS count_1 FROM mytable")

        # test a "dotted" function name
        self.assert_compile(select([func.foo.bar.lala(
            table1.c.myid)]),
            "SELECT foo.bar.lala(mytable.myid) AS lala_1 FROM mytable")

        # test the bind parameter name with a "dotted" function name is
        # only the name (limits the length of the bind param name)
        self.assert_compile(select([func.foo.bar.lala(12)]),
                            "SELECT foo.bar.lala(:lala_2) AS lala_1")

        # test a dotted func off the engine itself
        self.assert_compile(func.lala.hoho(7), "lala.hoho(:hoho_1)")

        # test None becomes NULL
        self.assert_compile(
            func.my_func(
                1,
                2,
                None,
                3),
            "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)")

        # test pickling
        self.assert_compile(
            util.pickle.loads(util.pickle.dumps(
                func.my_func(1, 2, None, 3))),
            "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)")

        # assert func raises AttributeError for __bases__ attribute, since
        # its not a class fixes pydoc
        try:
            func.__bases__
            assert False
        except AttributeError:
            assert True

Example 17

Project: sqlalchemy
Source File: test_query.py
View license
    @testing.requires.boolean_col_expressions
    def test_or_and_as_columns(self):
        true, false = literal(True), literal(False)

        eq_(testing.db.execute(select([and_(true, false)])).scalar(), False)
        eq_(testing.db.execute(select([and_(true, true)])).scalar(), True)
        eq_(testing.db.execute(select([or_(true, false)])).scalar(), True)
        eq_(testing.db.execute(select([or_(false, false)])).scalar(), False)
        eq_(
            testing.db.execute(select([not_(or_(false, false))])).scalar(),
            True)

        row = testing.db.execute(
            select(
                [or_(false, false).label("x"),
                    and_(true, false).label("y")])).first()
        assert row.x == False  # noqa
        assert row.y == False  # noqa

        row = testing.db.execute(
            select(
                [or_(true, false).label("x"),
                    and_(true, false).label("y")])).first()
        assert row.x == True  # noqa
        assert row.y == False  # noqa

Example 18

Project: sqlalchemy
Source File: test_update.py
View license
    def test_update_11(self):
        table1 = self.tables.mytable

        values = {
            table1.c.name: table1.c.name + 'lala',
            table1.c.myid: func.do_stuff(table1.c.myid, literal('hoho'))
        }

        self.assert_compile(
            update(
                table1,
                (table1.c.myid == func.hoho(4)) & (
                    table1.c.name == literal('foo') +
                    table1.c.name +
                    literal('lala')),
                values=values),
            'UPDATE mytable '
            'SET '
            'myid=do_stuff(mytable.myid, :param_1), '
            'name=(mytable.name || :name_1) '
            'WHERE '
            'mytable.myid = hoho(:hoho_1) AND '
            'mytable.name = :param_2 || mytable.name || :param_3')

Example 19

Project: sqlalchemy
Source File: test_update.py
View license
    def test_update_ordered_parameters_1(self):
        table1 = self.tables.mytable

        # Confirm that we can pass values as list value pairs
        # note these are ordered *differently* from table.c
        values = [
            (table1.c.name, table1.c.name + 'lala'),
            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho'))),
        ]
        self.assert_compile(
            update(
                table1,
                (table1.c.myid == func.hoho(4)) & (
                    table1.c.name == literal('foo') +
                    table1.c.name +
                    literal('lala')),
                preserve_parameter_order=True,
                values=values),
            'UPDATE mytable '
            'SET '
            'name=(mytable.name || :name_1), '
            'myid=do_stuff(mytable.myid, :param_1) '
            'WHERE '
            'mytable.myid = hoho(:hoho_1) AND '
            'mytable.name = :param_2 || mytable.name || :param_3')

Example 20

Project: sqlalchemy
Source File: test_update.py
View license
    def test_update_ordered_parameters_2(self):
        table1 = self.tables.mytable

        # Confirm that we can pass values as list value pairs
        # note these are ordered *differently* from table.c
        values = [
            (table1.c.name, table1.c.name + 'lala'),
            ('description', 'some desc'),
            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho')))
        ]
        self.assert_compile(
            update(
                table1,
                (table1.c.myid == func.hoho(4)) & (
                    table1.c.name == literal('foo') +
                    table1.c.name +
                    literal('lala')),
                preserve_parameter_order=True).values(values),
            'UPDATE mytable '
            'SET '
            'name=(mytable.name || :name_1), '
            'description=:description, '
            'myid=do_stuff(mytable.myid, :param_1) '
            'WHERE '
            'mytable.myid = hoho(:hoho_1) AND '
            'mytable.name = :param_2 || mytable.name || :param_3')

Example 21

Project: sqlalchemy
Source File: test_update.py
View license
    def test_update_ordereddict(self):
        table1 = self.tables.mytable

        # Confirm that ordered dicts are treated as normal dicts,
        # columns sorted in table order
        values = util.OrderedDict((
            (table1.c.name, table1.c.name + 'lala'),
            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho')))))

        self.assert_compile(
            update(
                table1,
                (table1.c.myid == func.hoho(4)) & (
                    table1.c.name == literal('foo') +
                    table1.c.name +
                    literal('lala')),
                values=values),
            'UPDATE mytable '
            'SET '
            'myid=do_stuff(mytable.myid, :param_1), '
            'name=(mytable.name || :name_1) '
            'WHERE '
            'mytable.myid = hoho(:hoho_1) AND '
            'mytable.name = :param_2 || mytable.name || :param_3')

Example 22

Project: ibis
Source File: alchemy.py
View license
def _literal(t, expr):
    return sa.literal(expr.op().value)

Example 23

Project: kokoropy
Source File: test_types.py
View license
    @testing.provide_metadata
    def _literal_round_trip(self, type_, input_, output, filter_=None):
        """test literal rendering """

        # for literal, we test the literal render in an INSERT
        # into a typed column.  we can then SELECT it back as its
        # official type; ideally we'd be able to use CAST here
        # but MySQL in particular can't CAST fully
        t = Table('t', self.metadata, Column('x', type_))
        t.create()

        for value in input_:
            ins = t.insert().values(x=literal(value)).compile(
                dialect=testing.db.dialect,
                compile_kwargs=dict(literal_binds=True)
            )
            testing.db.execute(ins)

        for row in t.select().execute():
            value = row[0]
            if filter_ is not None:
                value = filter_(value)
            assert value in output

Example 24

Project: ggrc-core
Source File: json.py
View license
def build_type_query(type_, result_spec):
  model = ggrc.models.get_model(type_)
  mapper = model._sa_class_manager.mapper
  columns = []
  columns_indexes = {}
  if len(list(mapper.self_and_descendants)) == 1:
    type_column = sqlalchemy.literal(mapper.class_.__name__)
  else:
    # Handle polymorphic types with CASE
    type_column = sqlalchemy.case(
        value=mapper.polymorphic_on,
        whens={
            val: mapper.class_.__name__
            for val, mapper in mapper.polymorphic_map.items()
        })
  columns.append(type_column)
  columns_indexes['type'] = 0
  columns.append(model.id)
  columns_indexes['id'] = 1
  columns.append(mapper.c.context_id)
  columns_indexes['context_id'] = 2
  columns.append(mapper.c.updated_at)
  columns_indexes['updated_at'] = 3

  conditions = {}
  for keys, vals in result_spec.items():
    for key in keys:
      if key not in columns_indexes:
        columns_indexes[key] = len(columns)
        columns.append(mapper.c[key])
    conditions.setdefault(keys, []).extend(vals.keys())

  where_clauses = []
  for keys, vals in conditions.items():
    if len(keys) == 1:
      # If the key is singular, use `IN (...)`
      where_clauses.append(
          columns[columns_indexes[keys[0]]].in_([v[0] for v in vals]))
    else:
      # If multiple keys, build `OR` of multiple `AND` clauses
      clauses = []
      cols = [columns[columns_indexes[k]] for k in keys]
      for val in vals:
        # Now build OR clause with (key, val) pairs
        clause = []
        for i, _ in enumerate(val):
          clause.append(cols[i] == val[i])
        clauses.append(sqlalchemy.and_(*clause))
      where_clauses.append(sqlalchemy.or_(*clauses))
  where_clause = sqlalchemy.or_(*where_clauses)

  query = db.session.query(*columns).filter(where_clause)

  return columns_indexes, query

Example 25

Project: ggrc-core
Source File: json.py
View license
def build_stub_union_query(queries):
  results = {}
  for (type_, conditions) in queries:
    if isinstance(conditions, (int, long, str, unicode)):
      # Assume `id` query
      keys, vals = ('id',), (conditions,)
      results.setdefault(type_, {}).setdefault(keys, {}).setdefault(vals, [])
    elif isinstance(conditions, dict):
      keys, vals = zip(*sorted(conditions.items()))
      results.setdefault(type_, {}).setdefault(keys, {}).setdefault(vals, [])
    else:
      # FIXME: Handle aggregated conditions recursively
      pass

  column_count = 0
  type_column_indexes = {}
  type_queries = {}
  for (type_, result_spec) in results.items():
    columns_indexes, query = build_type_query(type_, result_spec)
    type_column_indexes[type_] = columns_indexes
    type_queries[type_] = query
    if len(columns_indexes) > column_count:
      column_count = len(columns_indexes)

  for (type_, query) in type_queries.items():
    for _ in range(column_count - len(type_column_indexes[type_])):
      query = query.add_column(sqlalchemy.literal(None))
    type_queries[type_] = query

  queries_for_union = type_queries.values()
  if len(queries_for_union) == 0:
    query = None
  elif len(queries_for_union) == 1:
    query = queries_for_union[0]
  else:
    query = db.session.query(
        sqlalchemy.sql.expression.union(
            *[q for q in type_queries.values()]).alias('union_query'))
  return results, type_column_indexes, query

Example 26

Project: ggrc-core
Source File: mysql.py
View license
  def search(self, terms, types=None, permission_type='read',
             permission_model=None, contact_id=None, extra_params={}):
    """Prepare the search query and return the results set based on the
    full text table."""
    model_names = self._get_grouped_types(types, extra_params)
    columns = (
        self.record_type.key.label('key'),
        self.record_type.type.label('type'),
        self.record_type.property.label('property'),
        self.record_type.content.label('content'),
        case(
            [(self.record_type.property == 'title', literal(0))],
            else_=literal(1)).label('sort_key'))

    query = db.session.query(*columns)
    query = query.filter(self.get_permissions_query(
        model_names, permission_type, permission_model))
    query = query.filter(self._get_filter_query(terms))
    query = self.search_get_owner_query(query, types, contact_id)

    model_names = [model.__name__ for model in all_models.all_models]
    if types is not None:
      model_names = [m for m in model_names if m in types]

    unions = [query]
    # Add extra_params and extra_colums:
    for k, v in extra_params.iteritems():
      if k not in model_names:
        continue
      q = db.session.query(*columns)
      q = q.filter(
          self.get_permissions_query([k], permission_type, permission_model))
      q = q.filter(self._get_filter_query(terms))
      q = self.search_get_owner_query(q, [k], contact_id)
      q = self._add_extra_params_query(q, k, v)
      unions.append(q)
    all_queries = union(*unions)
    all_queries = aliased(all_queries.order_by(
        all_queries.c.sort_key, all_queries.c.content))
    return db.session.execute(
        select([all_queries.c.key, all_queries.c.type]).distinct())

Example 27

Project: ggrc-core
Source File: mysql.py
View license
  def counts(self, terms, types=None, contact_id=None,
             extra_params={}, extra_columns={}):
    """Prepare the search query, but return only count for each of
     the requested objects."""
    model_names = self._get_grouped_types(types, extra_params)
    query = db.session.query(
        self.record_type.type, func.count(distinct(
            self.record_type.key)), literal(""))
    query = query.filter(self.get_permissions_query(model_names))
    query = query.filter(self._get_filter_query(terms))
    query = self.search_get_owner_query(query, types, contact_id)
    query = query.group_by(self.record_type.type)
    all_extra_columns = dict(extra_columns.items() +
                             [(p, p) for p in extra_params
                              if p not in extra_columns])
    if not all_extra_columns:
      return query.all()

    # Add extra_params and extra_colums:
    for k, v in all_extra_columns.iteritems():
      q = db.session.query(
          self.record_type.type, func.count(
              distinct(self.record_type.key)), literal(k))
      q = q.filter(self.get_permissions_query([v]))
      q = q.filter(self._get_filter_query(terms))
      q = self.search_get_owner_query(q, [v], contact_id)
      q = self._add_extra_params_query(q, v, extra_params.get(k, None))
      q = q.group_by(self.record_type.type)
      query = query.union(q)
    return query.all()

Example 28

Project: ggrc-core
Source File: with_similarity_score.py
View license
  @classmethod
  def _emulate_relationships(cls, id_, types, relevant_types):
    """Get a list of queries for second-tier objects mapped via foreign key.

    This is used primarily to determine Requests mapped to Audits (Request
    model has a foreign key to Audit model and is not mapped with a
    Relationship object).

    Each query returns results compliant with the results of
    _join_relationships as they get UNIONed.
    """

    # Note: this is a hack that can go away only when Request-Audit mapping
    # will be implemented in Relationships table

    from ggrc.models import Audit, Request

    result = []
    if Audit.__name__ in relevant_types:
      # Note: this code assumes that `types` is a single-element list
      if cls is Request and Request.__name__ in types:
        similar_requests = aliased(cls, name="similar_requests")
        result.append(db.session.query(
            literal(Audit.__name__).label("related_type"),
            similar_requests.id.label("similar_id"),
            literal(Request.__name__).label("similar_type"),
        ).select_from(
            similar_requests,
        ).join(
            cls,
            similar_requests.audit_id == cls.audit_id,
        ).filter(
            and_(cls.id == id_,
                 cls.id != similar_requests.id),
        ))
      elif cls is Request and Request.__name__ not in types:
        audit_to_similar = aliased(Relationship, name="audit_to_similar")
        result.append(db.session.query(
            literal(Audit.__name__).label("related_type"),
            audit_to_similar.source_id.label("similar_id"),
            audit_to_similar.source_type.label("similar_type"),
        ).select_from(
            audit_to_similar,
        ).join(
            cls,
            and_(audit_to_similar.destination_id == cls.audit_id,
                 audit_to_similar.destination_type == Audit.__name__),
        ))
        result.append(db.session.query(
            literal(Audit.__name__).label("related_type"),
            audit_to_similar.destination_id.label("similar_id"),
            audit_to_similar.destination_type.label("similar_type"),
        ).select_from(
            audit_to_similar,
        ).join(
            cls,
            and_(audit_to_similar.source_id == cls.audit_id,
                 audit_to_similar.source_type == Audit.__name__),
        ))
      elif cls is not Request and Request.__name__ in types:
        self_to_audit = aliased(Relationship, name="self_to_audit")
        request = aliased(Request)

        result.append(db.session.query(
            literal(Audit.__name__).label("related_type"),
            request.id.label("similar_id"),
            literal(Request.__name__).label("similar_type"),
        ).select_from(
            request,
        ).join(
            self_to_audit,
            or_(
                and_(self_to_audit.source_id == id_,
                     self_to_audit.source_type == cls.__name__,
                     self_to_audit.destination_id == request.audit_id,
                     self_to_audit.destination_type == Audit.__name__),
                and_(self_to_audit.destination_id == id_,
                     self_to_audit.destination_type == cls.__name__,
                     self_to_audit.source_id == request.audit_id,
                     self_to_audit.source_type == Audit.__name__),
            ),
        ))

    return result

Example 29

Project: ggrc-core
Source File: __init__.py
View license
def objects_via_assignable_query(user_id, context_not_role=True):
  """Creates a query that returns objects a user can access because she is
     assigned via the assignable mixin.

    Args:
        user_id (int): id of the user

    Returns:
        db.session.query object that selects the following columns:
            | id | type | context_id |
  """

  rel1 = aliased(all_models.Relationship, name="rel1")
  rel2 = aliased(all_models.Relationship, name="rel2")
  _attrs = aliased(all_models.RelationshipAttr, name="attrs")

  def assignable_join(query):
    """Joins relationship_attrs to the query. This filters out only the
       relationship objects where the user is mapped with an AssigneeType.
    """
    return query.join(
        _attrs, and_(
            _attrs.relationship_id == rel1.id,
            _attrs.attr_name == "AssigneeType",
            case([
                (rel1.destination_type == "Person",
                 rel1.destination_id)
            ], else_=rel1.source_id) == user_id))

  def related_assignables():
    """Header for the mapped_objects join"""
    return db.session.query(
        case([
            (rel2.destination_type == rel1.destination_type,
             rel2.source_id)
        ], else_=rel2.destination_id).label('id'),
        case([
            (rel2.destination_type == rel1.destination_type,
             rel2.source_type)
        ], else_=rel2.destination_type).label('type'),
        rel1.context_id if context_not_role else literal('R')
    ).select_from(rel1)

  # First we fetch objects where a user is mapped as an assignee
  assigned_objects = assignable_join(db.session.query(
      case([
          (rel1.destination_type == "Person",
           rel1.source_id)
      ], else_=rel1.destination_id),
      case([
          (rel1.destination_type == "Person",
           rel1.source_type)
      ], else_=rel1.destination_type),
      rel1.context_id if context_not_role else literal('RUD')))

  # The user should also have access to objects mapped to the assigned_objects
  # We accomplish this by filtering out relationships where the user is
  # assigned and then joining the relationship table for the second time,
  # retrieving the mapped objects.
  #
  # We have a union here because using or_ to join both by destination and
  # source was not performing well (8s+ query times)
  mapped_objects = assignable_join(
      # Join by destination:
      related_assignables()).join(rel2, and_(
          case([
              (rel1.destination_type == "Person",
               rel1.source_id)
          ], else_=rel1.destination_id) == rel2.destination_id,
          case([
              (rel1.destination_type == "Person",
               rel1.source_type)
          ], else_=rel1.destination_type) == rel2.destination_type)
  ).union(assignable_join(
      # Join by source:
      related_assignables()).join(rel2, and_(
          case([
              (rel1.destination_type == "Person",
               rel1.source_id)
          ], else_=rel1.destination_id) == rel2.source_id,
          case([
              (rel1.destination_type == "Person",
               rel1.source_type)
          ], else_=rel1.destination_type) == rel2.source_type))
  )
  return mapped_objects.union(assigned_objects)

Example 30

Project: ggrc-core
Source File: __init__.py
View license
def objects_via_relationships_query(model, roles, user_id, context_not_role):
  """Creates a query that returns objects a user can access via mappings.

    Args:
        model: base model upon the roles are given
        roles: list of roles names to check
        user_id: id of the user
        context_not_role: use context instead of the role for the third column
            in the search api we need to return (obj_id, obj_type, context_id),
            but in ggrc_basic_permissions we need a role instead of a
            context_id (obj_id, obj_type, role_name)

    Returns:
        db.session.query object that selects the following columns:
            | id | type | role_name or context |
        Rows represent objects that are mapped to objects of the given model
        (where the user has a listed role) and the corresponding relationships.
  """
  _role = aliased(all_models.Role, name="r")
  _implications = aliased(all_models.ContextImplication, name="ci")
  _model = aliased(model, name="p")
  _relationship = aliased(all_models.Relationship, name="rl")
  _user_role = aliased(all_models.UserRole, name="ur")

  def _join_filter(query, cond):
    """Filter a query based on user roles

    Args:
        query (sqlalchemy.orm.query.Query): query to be filtered
        cond (sqlalchemy.sql.elements.BooleanClauseList): condition used for
            the initial model query

    Returns:
        query (sqlalchemy.orm.query.Query): object with applied conditions
    """
    user_role_cond = and_(_user_role.person_id == user_id,
                          _user_role.context_id == _implications.context_id)
    role_cond = and_(_user_role.role_id == _role.id,
                     _role.name.in_(roles))
    return query.join(_model, cond).join(
        _implications, _model.context_id == _implications.source_context_id).\
        join(_user_role, user_role_cond).\
        join(_role, role_cond).\
        distinct().\
        union(query.join(_model, cond).join(
            _implications, _model.context_id == _implications.context_id).
        join(_user_role, user_role_cond).
        join(_role, role_cond).
        distinct())

  def _add_relationship_join(query):
    # We do a UNION here because using an OR to JOIN both destination
    # and source causes a full table scan
    return _join_filter(query,
                        and_(_relationship.source_type == model.__name__,
                             _model.id == _relationship.source_id))\
        .union(_join_filter(
            query,
            and_(_relationship.destination_type == model.__name__,
                 _model.id == _relationship.destination_id)
        ))

  objects = _add_relationship_join(db.session.query(
      case([
          (_relationship.destination_type == model.__name__,
           _relationship.source_id.label('id'))
      ], else_=_relationship.destination_id.label('id')),
      case([
          (_relationship.destination_type == model.__name__,
           _relationship.source_type.label('type'))
      ], else_=_relationship.destination_type.label('type')),
      literal(None).label('context_id') if context_not_role else _role.name))

  # We also need to return relationships themselves:
  relationships = _add_relationship_join(db.session.query(
      _relationship.id, literal("Relationship"), _relationship.context_id))
  return objects.union(relationships)

Example 31

Project: ggrc-core
Source File: __init__.py
View license
def context_relationship_query(contexts):
  """Load a list of objects related to the given contexts

  Args:
    contexts (list(int)): A list of context ids
  Returns:
    objects (list((id, type, None))): Related objects
  """
  if not len(contexts):
    return []

  _context = aliased(all_models.Context, name="c")
  _relationship = aliased(all_models.Relationship, name="rl")

  headers = (case([
      (_relationship.destination_type == _context.related_object_type,
       _relationship.source_id.label('id'))
  ], else_=_relationship.destination_id.label('id')),
      case([
          (_relationship.destination_type == _context.related_object_type,
           _relationship.source_type.label('type'))
      ], else_=_relationship.destination_type.label('type')),
      literal(None))

  return db.session.query(*headers).join(_context, and_(
      _context.id.in_(contexts),
      _relationship.destination_id == _context.related_object_id,
      _relationship.destination_type == _context.related_object_type,
  )).union(db.session.query(*headers).join(_context, and_(
      _context.id.in_(contexts),
      _relationship.source_id == _context.related_object_id,
      _relationship.source_type == _context.related_object_type,
  ))).all()

Example 32

Project: indico
Source File: __init__.py
View license
@signals.event.sidemenu.connect
def _extend_event_menu(sender, **kwargs):
    from indico.modules.events.contributions.util import get_contributions_with_user_as_submitter
    from indico.modules.events.layout.util import MenuEntryData

    def _visible_my_contributions(conf):
        return session.user and bool(get_contributions_with_user_as_submitter(conf.as_event, session.user))

    def _visible_list_of_contributions(conf):
        return bool(Contribution.query.with_entities(literal(True))
                    .filter(Contribution.event_new == conf.as_event)
                    .count())

    yield MenuEntryData(title=_("My Contributions"), name='my_contributions', visible=_visible_my_contributions,
                        endpoint='contributions.my_contributions', position=2, parent='my_conference')
    yield MenuEntryData(title=_("Contribution List"), name='contributions', endpoint='contributions.contribution_list',
                        position=4, static_site=True, visible=_visible_list_of_contributions)
    yield MenuEntryData(title=_("Author List"), name='author_index', endpoint='contributions.author_list', position=5,
                        is_enabled=False, static_site=True)
    yield MenuEntryData(title=_("Speaker List"), name='speaker_index', endpoint='contributions.speaker_list',
                        position=6, is_enabled=False, static_site=True)

Example 33

Project: waymarked-trails-site
Source File: listings.py
View license
    @cherrypy.expose
    def segments(self, relations=None, ways=None, waysets=None, bbox=None, **params):
        b = api.common.Bbox(bbox)
        tables = cherrypy.request.app.config['DB']['map'].tables
        objs = []

        if relations is not None:
            ids = [ int(x) for x in relations.split(',') if x.isdigit() ]
            r = tables.routes.data
            sel = sa.select([sa.literal("r"), r.c.id,
                             r.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                   .where(r.c.id.in_(ids))
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        if ways is not None:
            ids = [ int(x) for x in ways.split(',') if x.isdigit() ]
            w = tables.ways.data
            sel = sa.select([sa.literal("w"), w.c.id,
                             w.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                    .where(w.c.id.in_(ids))
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        if waysets is not None:
            ids = [ int(x) for x in waysets.split(',') if x.isdigit() ]
            ws = tables.joined_ways.data
            sel = sa.select([sa.literal("w"),
                             ws.c.virtual_id.label('id'),
                             sa.func.ST_AsGeoJSON(sa.func.ST_CollectionHomogenize(
                                 sa.func.ST_Collect(w.c.geom.ST_Intersection(b.as_sql()))))
                            ])\
                    .select_from(w.join(ws, w.c.id == ws.c.child))\
                    .where(ws.c.virtual_id.in_(ids)).group_by(ws.c.virtual_id)
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        return self.create_segments_out(objs)

Example 34

Project: waymarked-trails-site
Source File: listings.py
View license
    @cherrypy.expose
    def segments(self, relations=None, ways=None, waysets=None, bbox=None, **params):
        b = api.common.Bbox(bbox)
        tables = cherrypy.request.app.config['DB']['map'].tables
        objs = []

        if relations is not None:
            ids = [ int(x) for x in relations.split(',') if x.isdigit() ]
            r = tables.routes.data
            sel = sa.select([sa.literal("r"), r.c.id,
                             r.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                   .where(r.c.id.in_(ids))
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        if ways is not None:
            ids = [ int(x) for x in ways.split(',') if x.isdigit() ]
            w = tables.ways.data
            sel = sa.select([sa.literal("w"), w.c.id,
                             w.c.geom.ST_Intersection(b.as_sql()).ST_AsGeoJSON()])\
                    .where(w.c.id.in_(ids))
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        if waysets is not None:
            ids = [ int(x) for x in waysets.split(',') if x.isdigit() ]
            ws = tables.joined_ways.data
            sel = sa.select([sa.literal("w"),
                             ws.c.virtual_id.label('id'),
                             sa.func.ST_AsGeoJSON(sa.func.ST_CollectionHomogenize(
                                 sa.func.ST_Collect(w.c.geom.ST_Intersection(b.as_sql()))))
                            ])\
                    .select_from(w.join(ws, w.c.id == ws.c.child))\
                    .where(ws.c.virtual_id.in_(ids)).group_by(ws.c.virtual_id)
            for x in cherrypy.request.db.execute(sel):
                objs.append(x)

        return self.create_segments_out(objs)

Example 35

View license
    def __init__(self, against, model, mode=FullTextMode.DEFAULT):
        self.model = model
        self.against = literal(against)
        self.mode = mode

Example 36

View license
    def __init__(self, against, model, mode=FullTextMode.DEFAULT):
        self.model = model
        self.against = literal(against)
        self.mode = mode