def test_tuple_containment(self):

        for test, exp in [
            ([("a", "b")], True),
            ([("a", "c")], False),
            ([("f", "q"), ("a", "b")], True),
            ([("f", "q"), ("a", "c")], False),
        ]:
            eq_(
                testing.db.execute(
                    select(
                        [
                            tuple_(
                                literal_column("'a'"), literal_column("'b'")
                            ).in_(
                                [
                                    tuple_(
                                        *[
                                            literal_column("'%s'" % letter)
                                            for letter in elem
                                        ]
                                    )
                                    for elem in test
                                ]
                            )
                        ]
                    )
                ).scalar(),
                exp,
            )

    def _fetch(self, last_index, sort_index=None, limit=None, eager=True):
        cursor = self.cursor
        direction = self.sort_column[1] if self.sort_column else sa.asc
        lhs, rhs = (), ()

        if sort_index is not None:
            left_index = self.sort_column[0]

            # Check if we're using a sort expression and if so, use the type
            # associated with it instead of deriving it from the column.
            if not self.sort_column[3]:
                comparator = self.max_column_map.get(
                    str(left_index.property.columns[0].type).lower()
                )
            else:
                comparator = self.max_column_map.get(self.sort_column[5])

            left_index = sa.func.coalesce(left_index, comparator)
            lhs += (left_index,)
            rhs += (sort_index,)

        if last_index is not None:
            lhs += (self.index_column,)
            rhs += (last_index,)

        lhs = sa.tuple_(*lhs)
        rhs = sa.tuple_(*rhs)

        if rhs.clauses:
            filter = lhs > rhs if direction == sa.asc else lhs < rhs
            cursor = cursor.filter(filter)

        query = cursor.order_by(direction(self.index_column)).limit(limit)
        return query.all() if eager else query

    def get_requested_slots(self, resource, except_applications, start_date, end_date):
        query = current_app.db_session.query(RepeatingSlotRequest)

        objects = query.filter(
            RepeatingSlotRequest.application.has(resource_id=resource.id)
        )

        objects = objects.filter(
            RepeatingSlotRequest.application.has(status="Pending")
        )

        if except_applications:
            objects = objects.filter(
                ~RepeatingSlotRequest.application_id.in_(except_applications)
            )

        objects = objects.filter(
            or_(
                tuple_(
                    RepeatingSlotRequest.start_date, RepeatingSlotRequest.end_date
                ).op('overlaps')(
                    tuple_(
                        cast(start_date, Date), cast(end_date, Date)
                    )
                ),
                or_(
                    # First range ends on the start date of the second
                    RepeatingSlotRequest.end_date == cast(start_date, Date),
                    # Second range ends on the start date of the first
                    cast(end_date, Date) == RepeatingSlotRequest.start_date
                )
            )
        )

        return objects.all()

    def filter_single_by_time(cls, type, objects, year=None, week_number=None, day=None):

        assert (week_number and year) or day
        start_date, end_date = None, None

        if year and week_number:
            start_date, end_date = get_start_and_end_date_from_week_and_year(
                year,
                week_number
            )
        if day:
            start_date = day
            end_date = day

        objects = objects.filter(
            or_(
                tuple_(
                    cast(type.start_time, Date), cast(type.end_time, Date)
                ).op('overlaps')(
                    tuple_(
                        start_date, end_date
                    )
                ),
                or_(
                    # First range ends on the start date of the second
                    cast(type.end_time, Date) == start_date,
                    # Second range ends on the start date of the first
                    end_date == cast(type.start_time, Date)
                )
            )
        )

        return objects

    def get_arrangements_slots(self, resource, start_date, end_date):  # start_time, end_time, week_day
        query = current_app.db_session.query(Slot)
        statuses = ["Granted"]
        objects = query.filter(Application.resource == resource,
                               Slot.application_id == Application.id,
                               Application.status.in_(statuses),
                               Application.is_arrangement == True)

        objects = objects.filter(
            or_(
                tuple_(
                    cast(Slot.start_time, Date), cast(Slot.end_time, Date)
                ).op('overlaps')(
                    tuple_(
                        cast(start_date, Date), cast(end_date, Date)
                    )
                ),
                or_(
                    # First range ends on the start date of the second
                    cast(Slot.end_time, Date) == cast(start_date, Date),
                    # Second range ends on the start date of the first
                    cast(end_date, Date) == cast(Slot.start_time, Date)
                )
            )
        )
        return objects.order_by(Slot.start_time).all()

    def filter_repeating_by_time(cls, type, objects, year=None, week_number=None, day=None):

        assert (week_number and year) or day
        start_date, end_date = None, None

        if year and week_number:
            start_date, end_date = get_start_and_end_date_from_week_and_year(
                year,
                week_number
            )
        if day:
            start_date = day
            end_date = day
            objects = objects.filter(
                type.week_day == day.isoweekday()
            )

        objects = objects.filter(
            or_(
                tuple_(
                    type.start_date, type.end_date
                ).op('overlaps')(
                    tuple_(
                        start_date, end_date
                    )
                ),
                or_(
                    # First range ends on the start date of the second
                    type.end_date == start_date,
                    # Second range ends on the start date of the first
                    end_date == type.start_date
                )
            )
        )
        return objects

    def get_repeating(self, resource, except_applications, start_date, end_date, statuses):
        query = current_app.db_session.query(RepeatingSlot)
        objects = query.filter(Application.resource == resource,
                               RepeatingSlot.application_id == Application.id,
                               Application.status.in_(statuses))

        if except_applications:
            objects = objects.filter(
                ~RepeatingSlot.application_id.in_(except_applications)
            )

        objects = objects.filter(
            or_(
                tuple_(
                    RepeatingSlot.start_date, RepeatingSlot.end_date
                ).op('overlaps')(
                    tuple_(
                        cast(start_date, Date), cast(end_date, Date)
                    )
                ),
                or_(
                    # First range ends on the start date of the second
                    RepeatingSlot.end_date == cast(start_date, Date),
                    # Second range ends on the start date of the first
                    cast(end_date, Date) == RepeatingSlot.start_date
                )
            )
        )

        return objects.all()

 def get_repeating_slots(cls, resource, start_date, end_date, week_day, start_time, end_time):
     query = current_app.db_session.query(RepeatingSlot)
     statuses = ["Granted"]
     objects = query.filter(
         Application.resource == resource,
         RepeatingSlot.application_id == Application.id,
         Application.status.in_(statuses),
     )
     objects = objects.filter(
         or_(
             tuple_(RepeatingSlot.start_date, RepeatingSlot.end_date).op("overlaps")(
                 tuple_(cast(start_date, Date), cast(end_date, Date))
             ),
             or_(
                 # First range ends on the start date of the second
                 RepeatingSlot.end_date == cast(start_date, Date),
                 # Second range ends on the start date of the first
                 cast(end_date, Date) == RepeatingSlot.start_date,
             ),
         ),
         tuple_(RepeatingSlot.start_time, RepeatingSlot.end_time).op("overlaps")(
             tuple_(cast(start_time, Time), cast(end_time, Time))
         ),
         and_(week_day == RepeatingSlot.week_day),
     )
     return objects.all()

 def populate_cache(self, stubs):
   """Fetch all mappings for objects in stubs, cache them in self.cache."""
   # Union is here to convince mysql to use two separate indices and
   # merge te results. Just using `or` results in a full-table scan
   # Manual column list avoids loading the full object which would also try to
   # load related objects
   cols = db.session.query(
       Relationship.source_type, Relationship.source_id,
       Relationship.destination_type, Relationship.destination_id)
   relationships = cols.filter(
       sa.tuple_(
           Relationship.source_type,
           Relationship.source_id
       ).in_(
           [(s.type, s.id) for s in stubs]
       )
   ).union_all(
       cols.filter(
           sa.tuple_(
               Relationship.destination_type,
               Relationship.destination_id
           ).in_(
               [(s.type, s.id) for s in stubs]
           )
       )
   ).all()
   for (src_type, src_id, dst_type, dst_id) in relationships:
     src = Stub(src_type, src_id)
     dst = Stub(dst_type, dst_id)
     # only store a neighbor if we queried for it since this way we know
     # we'll be storing complete neighborhood by the end of the loop
     if src in stubs:
       self.cache[src].add(dst)
     if dst in stubs:
       self.cache[dst].add(src)

  def _get_revision_type_query(model, permission_type):
    """Filter model based on availability of related objects.

    This method is used only when quering revisions. In such case only
    revisions of objects user has right permission on should be returned. It
    means, user must have either right permission on object revision belongs
    to or in case it is revision of a relationship, user must have right
    permission on at least one part of the relationship.
    """
    allowed_resources = permissions.all_resources(permission_type)
    if not allowed_resources:
      return sa.false()

    return sa.or_(
        sa.tuple_(
            model.resource_type,
            model.resource_id,
        ).in_(
            allowed_resources,
        ),
        sa.tuple_(
            model.source_type,
            model.source_id,
        ).in_(
            allowed_resources,
        ),
        sa.tuple_(
            model.destination_type,
            model.destination_id,
        ).in_(
            allowed_resources,
        ),
    )

    def test_tuple_containment(self):

        for test, exp in [
            ([('a', 'b')], True),
            ([('a', 'c')], False),
            ([('f', 'q'), ('a', 'b')], True),
            ([('f', 'q'), ('a', 'c')], False)
        ]:
            eq_(
                testing.db.execute(
                    select([
                        tuple_(
                            literal_column("'a'"),
                            literal_column("'b'")
                        ).
                        in_([
                            tuple_(*[
                                literal_column("'%s'" % letter)
                                for letter in elem
                            ]) for elem in test
                        ])
                    ])
                ).scalar(),
                exp
            )

def get_all_orders(email=None):
    session = Db.instance().session
    # Create subquery for retrieving the last state of each order
    subquery = session.query(
        Order_state.order_id, func.max(Order_state.date)
    ).group_by(Order_state.order_id).subquery()
    if email != None:
        return Order().queryObject().join(Order_state).filter(and_(
        tuple_(Order_state.order_id, Order_state.date).in_(subquery),
        Order.user.has(email=email))).order_by(Order_state.state,\
        desc(Order_state.date)).all()
    else:
        return Order().queryObject().join(Order_state).filter(
        tuple_(Order_state.order_id, Order_state.date).in_(subquery)).\
        order_by(Order_state.state, desc(Order_state.date)).all()

def get_db_records(pids):
    """Get an iterator on record metadata from the DB.

    Args:
        pids (Iterable[Tuple[str, Union[str, int]]): a list of (pid_type, pid_value) tuples.

    Yields:
        dict: metadata of a record found in the database.

    Warning:
        The order in which records are returned is different from the order of
        the input.
    """
    pids = [(pid_type, str(pid_value)) for (pid_type, pid_value) in pids]

    if not pids:
        return

    query = RecordMetadata.query.join(
        PersistentIdentifier, RecordMetadata.id == PersistentIdentifier.object_uuid
    ).filter(
        PersistentIdentifier.object_type == 'rec',  # So it can use the 'idx_object' index
        tuple_(PersistentIdentifier.pid_type, PersistentIdentifier.pid_value).in_(pids)
    )

    for record in query.yield_per(100):
        yield record.json

 def test_tuple(self):
     expr = self.parser(
         sa.tuple_(self.User.name, 3).in_([(u'someone', 3)])
     )
     assert str(expr) == (
         '(category.name, :param_1) IN ((:param_2, :param_3))'
     )

def get_query_for_ids(modelquery, model, ids):
    """
        Return a query object, that contains all entities of the given model for
        the primary keys provided in the ids-parameter.

        The ``pks`` parameter is a tuple, that contains the different primary key values,
        that should be returned. If the primary key of the model consists of multiple columns
        every entry of the ``pks`` parameter must be a tuple containing the columns-values in the
        correct order, that make up the primary key of the model

        If the model has multiple primary keys, the
        `tuple_ <http://docs.sqlalchemy.org/en/latest/core/expression_api.html#sqlalchemy.sql.expression.tuple_>`_
        operator will be used. As this operator does not work on certain databases,
        notably on sqlite, a workaround function :func:`tuple_operator_in` is provided
        that implements the same logic using OR and AND operations.

        When having multiple primary keys, the pks are provided as a list of tuple-look-alike-strings,
        ``[u'(1, 2)', u'(1, 1)']``. These needs to be evaluated into real tuples, where
        `Stackoverflow Question 3945856 <http://stackoverflow.com/questions/3945856/converting-string-to-tuple-and-adding-to-tuple>`_
        pointed to `Literal Eval <http://docs.python.org/2/library/ast.html#ast.literal_eval>`_, which is now used.
    """
    if has_multiple_pks(model):
        model_pk = [getattr(model, pk_name).expression for pk_name in get_primary_key(model)]
        ids = [literal_eval(id) for id in ids]
        try:
            query = modelquery.filter(tuple_(*model_pk).in_(ids))
            # Only the execution of the query will tell us, if the tuple_
            # operator really works
            query.all()
        except DBAPIError:
            query = modelquery.filter(tuple_operator_in(model_pk, ids))
    else:
        model_pk = getattr(model, get_primary_key(model))
        query = modelquery.filter(model_pk.in_(ids))
    return query

def _get_acl_filter():
  """Get filter for acl entries.

  This creates a filter to select only acl entries for objects that were
  specified in the request json.

  If this filter is used we must not store the results of the permissions dict
  into memcache.

  Returns:
    list of filter statements.
  """
  stubs = getattr(flask.g, "referenced_object_stubs", {})
  if not stubs:
    return []
  roleable_models = {m.__name__ for m in all_models.all_models
                     if issubclass(m, Roleable)}
  keys = [(type_, id_)
          for type_, ids in stubs.iteritems()
          for id_ in ids
          if type_ in roleable_models]
  if not keys:
    return []
  return [
      sa.tuple_(
          all_models.AccessControlList.object_type,
          all_models.AccessControlList.object_id,
      ).in_(
          keys,
      )
  ]

 def _key_conditions(self, keys):
     vals = []
     for key in keys:
         row = self._key_orm.to_row(key)
         val = tuple(row[c] for c in self._key_cols)
         vals.append(val)
     return tuple_(*self._key_cols).in_(vals)

    def test_expanding_in_composite(self):
        testing.db.execute(
            users.insert(),
            [
                dict(user_id=7, user_name='jack'),
                dict(user_id=8, user_name='fred'),
                dict(user_id=9, user_name=None)
            ]
        )

        with testing.db.connect() as conn:
            stmt = select([users]).where(
                tuple_(
                    users.c.user_id,
                    users.c.user_name
                ).in_(bindparam('uname', expanding=True))
            ).order_by(users.c.user_id)

            eq_(
                conn.execute(stmt, {"uname": [(7, 'jack')]}).fetchall(),
                [(7, 'jack')]
            )

            eq_(
                conn.execute(stmt, {"uname": [(7, 'jack'), (8, 'fred')]}).fetchall(),
                [(7, 'jack'), (8, 'fred')]
            )

    def __eq__(self, other):
        """
        Compare the userid for equality with `other`.

        `other` can be anything plausibly on the RHS of a comparison, which
        can include other SQL clause elements or expressions, as in

            User.userid == sa.tuple_(User.username, Group.authority)

        or literals, as in

            User.userid == 'acct:[email protected]'

        We treat the literal case specially, and split the string into
        username and authority ourselves. If the string is not a well-formed
        userid, the comparison will always return False.
        """
        if isinstance(other, string_types):
            try:
                val = split_user(other)
            except ValueError:
                # The value being compared isn't a valid userid
                return False
            else:
                other = sa.tuple_(_normalise_username(val['username']),
                                  val['domain'])
        return self.__clause_element__() == other

def get_query_for_ids(modelquery, model, ids):
    """
        Return a query object filtered by primary key values passed in `ids` argument.

        Unfortunately, it is not possible to use `in_` filter if model has more than one
        primary key.
    """
    if has_multiple_pks(model):
        # Decode keys to tuples
        decoded_ids = [iterdecode(v) for v in ids]

        # Get model primary key property references
        model_pk = [getattr(model, name) for name in get_primary_key(model)]

        try:
            query = modelquery.filter(tuple_(*model_pk).in_(decoded_ids))
            # Only the execution of the query will tell us, if the tuple_
            # operator really works
            query.all()
        except DBAPIError:
            query = modelquery.filter(tuple_operator_in(model_pk, decoded_ids))
    else:
        model_pk = getattr(model, get_primary_key(model))
        query = modelquery.filter(model_pk.in_(ids))

    return query