def test_nine(self):
     x = column("x")
     self.assert_compile(
         and_(x == 7, x == 9, false(), x == 5),
         "false"
     )
     self.assert_compile(
         ~and_(x == 7, x == 9, false(), x == 5),
         "true"
     )

 def filter(self, joins):
     arch_name = self.get_xml_attr('arch', unicode, None)
     try:
         arch = Arch.by_name(arch_name)
     except ValueError:
         return (joins, false())
     osmajor = self.get_xml_attr('osmajor', unicode, None)
     if not osmajor:
         return (joins, false())
     osminor = self.get_xml_attr('osminor', unicode, None) or None
     clause = System.compatible_with_distro_tree(arch, osmajor, osminor)
     return (joins, clause)

 def allForAccount(self, account, order=None, filters={}, onOrAfter=None, before=None):
     """ Return all transactions for the given account with the applied filters """
     transactions = []
     with self.session() as session:
         accountQuery = self.getTransactionsForAccountQuery(session, account)
         transfersQuery = session.query(self.table_class).join(Transfer).filter(Transfer.account == account)
         resultQuery = accountQuery.union(transfersQuery)
         for column in filters:
             unionQuery = session.query(self.table_class).filter(sql.false())
             for value in filters[column]:
                 tempQuery = accountQuery.filter(column==value)
                 unionQuery = unionQuery.union(tempQuery)
             resultQuery = resultQuery.intersect(unionQuery)
             
         if onOrAfter is not None:
             resultQuery = resultQuery.filter(Transaction.date >= onOrAfter)
         elif before is not None:
             resultQuery = resultQuery.filter(Transaction.date < before)
         
         if order is None:
             transactions = resultQuery.order_by(Transaction.date).all()
             transactions.reverse()
         else:
             transactions = resultQuery.order_by(order).all()
     return transactions# + account.transfers

def upgrade():
    op.create_table(
        'reservations',
        sa.Column('id', sa.String(length=36), nullable=False),
        sa.Column('tenant_id', sa.String(length=255), nullable=True),
        sa.Column('expiration', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'))

    op.create_table(
        'resourcedeltas',
        sa.Column('resource', sa.String(length=255), nullable=False),
        sa.Column('reservation_id', sa.String(length=36), nullable=False),
        sa.Column('amount', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['reservation_id'], ['reservations.id'],
                                ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('resource', 'reservation_id'))

    op.create_table(
        'quotausages',
        sa.Column('tenant_id', sa.String(length=255),
                  nullable=False, primary_key=True, index=True),
        sa.Column('resource', sa.String(length=255),
                  nullable=False, primary_key=True, index=True),
        sa.Column('dirty', sa.Boolean(), nullable=False,
                  server_default=sql.false()),
        sa.Column('in_use', sa.Integer(), nullable=False,
                  server_default='0'),
        sa.Column('reserved', sa.Integer(), nullable=False,
                  server_default='0'))

	def all_children(self,typ):
		from .objtyp import ObjType

		if is_undefined(typ):
			from sqlalchemy.sql import false
			return ObjType.q.filter(false())
		return ObjType.get_mod(typ).q.filter_by(parent=self)

def populate_package_groups(packages):
    """
    Adds `visible_groups` field to package objects. It contains a list of PackageGroup
    objects that are visible to current user - user is on Group's ACL.
    Global groups are visible to everyone.

    Ideally, this would be expressed using a SQLA relationship instead, but realtionships
    don't allow additional inputs (current user).

    :param packages: object with base_id attribute that allows adding attributes
    """
    base_map = {}
    for package in packages:
        package.visible_groups = []
        base_map[package.base_id] = package
    filter_expr = PackageGroup.namespace == None
    if g.user:
        filter_expr |= GroupACL.user_id == g.user.id
    query = (
        db.query(PackageGroupRelation)
        .options(contains_eager(PackageGroupRelation.group))
        .filter(
            PackageGroupRelation.base_id.in_(base_map.keys())
            if base_map else false()
        )
        .join(PackageGroup)
        .filter(filter_expr)
        .order_by(PackageGroup.namespace, PackageGroup.name)
    )
    if g.user:
        query = query.outerjoin(GroupACL)
    for r in query:
        base_map[r.base_id].visible_groups.append(r.group)

  def get_permissions_query(model_names, permission_type='read',
                            permission_model=None):
    """Prepare the query based on the allowed contexts and resources for
     each of the required objects(models).
    """
    type_queries = []
    for model_name in model_names:
      contexts, resources = query_helpers.get_context_resource(
          model_name=model_name,
          permission_type=permission_type,
          permission_model=permission_model
      )
      if contexts is not None:
        if resources:
          resource_sql = and_(
              MysqlRecordProperty.type == model_name,
              MysqlRecordProperty.key.in_(resources))
        else:
          resource_sql = false()

        type_query = or_(
            and_(
                MysqlRecordProperty.type == model_name,
                context_query_filter(MysqlRecordProperty.context_id, contexts)
            ),
            resource_sql)
        type_queries.append(type_query)

    return and_(
        MysqlRecordProperty.type.in_(model_names),
        or_(*type_queries))

    def has_property(cls, prop, when=None):
        # TODO Use joins
        property_granted_select = select(
            [null()],
            from_obj=[
                Property.__table__,
                PropertyGroup.__table__,
                Membership.__table__
            ]
        ).where(
            and_(
                Property.name == prop,
                Property.property_group_id == PropertyGroup.id,
                PropertyGroup.id == Membership.group_id,
                Membership.user_id == cls.id,
                Membership.active(when)
            )
        )
        #.cte("property_granted_select")
        return and_(
            not_(exists(
                property_granted_select.where(
                    Property.granted == false())

            )),
            exists(
                property_granted_select.where(
                    Property.granted == true()
                )
            )
        ).self_group().label("has_property_" + prop)

 def get_build_task_queue(cls):
     """
     Returns BuildChroots which are - waiting to be built or
                                    - older than 2 hours and unfinished
     """
     # todo: filter out build without package
     query = (models.BuildChroot.query.join(models.Build)
              .filter(models.Build.canceled == false())
              .filter(or_(
                  models.BuildChroot.status == helpers.StatusEnum("pending"),
                  models.BuildChroot.status == helpers.StatusEnum("starting"),
                  and_(
                      # We are moving ended_on to the BuildChroot, now it should be reliable,
                      # so we don't want to reschedule failed chroots
                      # models.BuildChroot.status.in_([
                      # # Bug 1206562 - Cannot delete Copr because it incorrectly thinks
                      # # there are unfinished builds. Solution: `failed` but unfinished
                      # # (ended_on is null) builds should be rescheduled.
                      # # todo: we need to be sure that correct `failed` set is set together wtih `ended_on`
                      # helpers.StatusEnum("running"),
                      # helpers.StatusEnum("failed")
                      #]),
                      models.BuildChroot.status == helpers.StatusEnum("running"),
                      models.BuildChroot.started_on < int(time.time() - 1.1 * MAX_BUILD_TIMEOUT),
                      models.BuildChroot.ended_on.is_(None)
                  ))
     ))
     query = query.order_by(models.BuildChroot.build_id.asc())
     return query

    def _apply_filters_to_query(self, query, model, filters, context=None):
        if filters:
            for key, value in six.iteritems(filters):
                column = getattr(model, key, None)
                # NOTE(kevinbenton): if column is a hybrid property that
                # references another expression, attempting to convert to
                # a boolean will fail so we must compare to None.
                # See "An Important Expression Language Gotcha" in:
                # docs.sqlalchemy.org/en/rel_0_9/changelog/migration_06.html
                if column is not None:
                    if not value:
                        query = query.filter(sql.false())
                        return query
                    if isinstance(column, associationproxy.AssociationProxy):
                        # association proxies don't support in_ so we have to
                        # do multiple equals matches
                        query = query.filter(
                            or_(*[column == v for v in value]))
                    else:
                        query = query.filter(column.in_(value))
                elif key == 'shared' and hasattr(model, 'rbac_entries'):
                    # translate a filter on shared into a query against the
                    # object's rbac entries
                    query = query.outerjoin(model.rbac_entries)
                    rbac = model.rbac_entries.property.mapper.class_
                    matches = [rbac.target_tenant == '*']
                    if context:
                        matches.append(rbac.target_tenant == context.tenant_id)
                    # any 'access_as_shared' records that match the
                    # wildcard or requesting tenant
                    is_shared = and_(rbac.action == 'access_as_shared',
                                     or_(*matches))
                    if not value[0]:
                        # NOTE(kevinbenton): we need to find objects that don't
                        # have an entry that matches the criteria above so
                        # we use a subquery to exclude them.
                        # We can't just filter the inverse of the query above
                        # because that will still give us a network shared to
                        # our tenant (or wildcard) if it's shared to another
                        # tenant.
                        # This is the column joining the table to rbac via
                        # the object_id. We can't just use model.id because
                        # subnets join on network.id so we have to inspect the
                        # relationship.
                        join_cols = model.rbac_entries.property.local_columns
                        oid_col = list(join_cols)[0]
                        is_shared = ~oid_col.in_(
                            query.session.query(rbac.object_id).
                            filter(is_shared)
                        )
                    query = query.filter(is_shared)
            for _nam, hooks in six.iteritems(self._model_query_hooks.get(model,
                                                                         {})):
                result_filter = hooks.get('result_filters', None)
                if isinstance(result_filter, six.string_types):
                    result_filter = getattr(self, result_filter, None)

                if result_filter:
                    query = result_filter(query, filters)
        return query

def upgrade():

    op.add_column(
        'gp_application_policy_groups',
        sa.Column('shared', sa.Boolean, nullable=True,
                  server_default=sql.false())
    )

 def get_query(self):
     if current_user.is_anonymous:
         return Plan.query.filter(sql.false())
     else:
         return Plan.query\
             .filter(Plan.user_id == current_user.id)\
             .order_by(sql.desc(Plan.created_at))

def upgrade():
    op.create_table(
        u'channel',
        sa.Column('id', sa.Integer(), nullable=False, primary_key=True),
        sa.Column('name', sa.Unicode(64), nullable=False, unique=True, index=True),
        sa.Column('introduction', sa.Unicode(1024), nullable=True),
        sa.Column('date_created', sa.DateTime(timezone=True),
                    nullable=False, index=True,
                    server_default=sa.func.current_timestamp()),
    )

    op.create_table(
        u'article',
        sa.Column('id', sa.Integer(), nullable=False, primary_key=True),
        sa.Column('channel_id', sa.Integer(), nullable=False, index=True),
        sa.Column('is_sticky', sa.Boolean(),
                    server_default=sql.false(), nullable=False),
        sa.Column('title', sa.Unicode(64), nullable=False, unique=True, index=True),
        sa.Column('date_published', sa.DateTime(timezone=True),
                    nullable=False, index=True,
                    server_default=sa.func.current_timestamp()),
        sa.Column('date_created', sa.DateTime(timezone=True),
                    nullable=False, index=True,
                    server_default=sa.func.current_timestamp()),
    )

    op.create_table(
        u'article_content',
        sa.Column('id', sa.Integer(), sa.ForeignKey('article.id'),
                    nullable=False, primary_key=True),
        sa.Column('content', sa.UnicodeText(), nullable=False),
    )

    def has_property(self, prop):
        property_granted_select = select(
            [null()],
            from_obj=[
                Property.__table__,
                PropertyGroup.__table__,
                Membership.__table__
            ]
        ).where(
            and_(
                Property.name == prop,
                Property.property_group_id == PropertyGroup.id,
                PropertyGroup.id == Membership.group_id,
                Membership.user_id == self.id,
                Membership.active
            )
        )
        #.cte("property_granted_select")
        return and_(
            not_(exists(
                property_granted_select.where(
                    Property.granted == false())

            )),
            exists(
                property_granted_select.where(
                    Property.granted == true()
                )
            )
        )

    def bounding_box_query(ne_lat, ne_lng, sw_lat, sw_lng, start_date, end_date,
                           fatal, severe, light, inaccurate, show_markers=True, is_thin=False, yield_per=None):
        # example:
        # ne_lat=32.36292402647484&ne_lng=35.08873443603511&sw_lat=32.29257266524761&sw_lng=34.88445739746089
        # >>>  m = Marker.bounding_box_query(32.36, 35.088, 32.292, 34.884)
        # >>> m.count()
        # 250

        if not show_markers:
            return Marker.query.filter(sql.false())
        accurate = not inaccurate
        markers = Marker.query \
            .filter(Marker.longitude <= ne_lng) \
            .filter(Marker.longitude >= sw_lng) \
            .filter(Marker.latitude <= ne_lat) \
            .filter(Marker.latitude >= sw_lat) \
            .filter(Marker.created >= start_date) \
            .filter(Marker.created < end_date) \
            .order_by(desc(Marker.created))
        if yield_per:
            markers = markers.yield_per(yield_per)
        if accurate:
            markers = markers.filter(Marker.locationAccuracy == 1)
        if not fatal:
            markers = markers.filter(Marker.severity != 1)
        if not severe:
            markers = markers.filter(Marker.severity != 2)
        if not light:
            markers = markers.filter(Marker.severity != 3)
        if is_thin:
            markers = markers.options(load_only("id", "longitude", "latitude"))
        return markers

 def _get_by_disabled_from_db(context, disabled):
     if disabled:
         return context.session.query(api_models.CellMapping).filter_by(
             disabled=true()).order_by(asc(api_models.CellMapping.id)).all()
     else:
         return context.session.query(api_models.CellMapping).filter_by(
             disabled=false()).order_by(asc(
                 api_models.CellMapping.id)).all()

 def get_query(self):
     if current_user.is_anonymous:
         return News.query.filter(sql.false())
     else:
         return News.query\
             .join(Schedule)\
             .filter(Schedule.owner_id == current_user.id)\
             .order_by(sql.desc(News.created))

 def test_true_false(self):
     self.assert_compile(
         sql.false(), "0"
     )
     self.assert_compile(
         sql.true(),
         "1"
     )

def _array_union(queries):
  """ Union of all valid queries in array """
  clean_queries = [q for q in queries if q]
  if not clean_queries:
    return db.session.query(Relationship.source_id).filter(sql.false())

  query = clean_queries.pop()
  return query.union(*clean_queries)

 def _get_min_service_version(self, context, binary):
     meta = MetaData(bind=db_session.get_engine(context=context))
     services = Table('services', meta, autoload=True)
     return select([sqlfunc.min(services.c.version)]).select_from(
         services).where(and_(
             services.c.binary == binary,
             services.c.deleted == 0,
             services.c.forced_down == false())).scalar()