Python sqlalchemy.sql.expression 模块，or_() 实例源码

def search(cls, querystr, sqlalchemy_query=None, user_name=None):
        '''Search name, fullname, email and openid. '''
        if sqlalchemy_query is None:
            query = meta.Session.query(cls)
        else:
            query = sqlalchemy_query
        qstr = '%' + querystr + '%'
        filters = [
            cls.name.ilike(qstr),
            cls.fullname.ilike(qstr),
            cls.openid.ilike(qstr),
        ]
        # sysadmins can search on user emails
        import ckan.authz as authz
        if user_name and authz.is_sysadmin(user_name):
            filters.append(cls.email.ilike(qstr))

        query = query.filter(or_(*filters))
        return query

def get_replica_locks(scope, name, nowait=False, restrict_rses=None, session=None):
    """
    Get the active replica locks for a file

    :param scope:          Scope of the did.
    :param name:           Name of the did.
    :param nowait:         Nowait parameter for the FOR UPDATE statement.
    :param restrict_rses:  Possible RSE_ids to filter on.
    :param session:        The db session.
    :return:               List of dicts {'rse': ..., 'state': ...}
    :raises:               NoResultFound
    """

    query = session.query(models.ReplicaLock).filter_by(scope=scope, name=name)
    if restrict_rses is not None:
        rse_clause = []
        for rse_id in restrict_rses:
            rse_clause.append(models.ReplicaLock.rse_id == rse_id)
        if rse_clause:
            query = query.filter(or_(*rse_clause))

    return query.with_for_update(nowait=nowait).all()

def add_simple_text_search(query, text_columns, keywords, include_rank=True):
    rank = None
    keywords_j = ' & '.join(keywords)
    fts_config = 'simple'
    filters = [func.to_tsvector(fts_config, column).match(keywords_j)
               for column in text_columns]
    if len(filters) > 1:
        filter = or_(*filters)
    else:
        filter = filters[0]
    query = query.filter(filter)
    if include_rank:
        ranks = [func.ts_rank(
            func.to_tsvector(fts_config, column),
            func.to_tsquery(fts_config, keywords_j))
            for column in text_columns]
        rank = reduce(lambda a, b: a + b, ranks)
        query = query.add_column(rank.label('score'))
    return query, rank

def get_player(self, account_id=None, steam_id=None, real_name=None):
        query = Database.session.query(Player)
        if account_id:
            return query.filter(Player.account_id == account_id).first()
        elif steam_id:
            return query.filter(Player.steam_id == steam_id).first()
        elif real_name:  # recommended to be optimized by full-text search.
            return query.filter(or_(text('real_name like :real_name'), text('persona_name like :real_name'))).params(
                real_name="%" + real_name + "%").limit(LIMIT_DATA).all()
        else:
            raise ValueError('Account id or Steam id or real name must be specified!')

def get_tsquery(value, default_op):
        if isinstance(value, list):
            tq = func.plainto_tsquery('english', value.pop())
            while len(value):
                tq = tq.op('||' if default_op == or_ else '&&')(func.plainto_tsquery('english', value.pop()))
        else:
            tq = func.plainto_tsquery('english', value)
        return tq

def get(cls, user_reference):
        # double slashes in an openid often get turned into single slashes
        # by browsers, so correct that for the openid lookup
        corrected_openid_user_ref = cls.DOUBLE_SLASH.sub('://\\1',
                                                         user_reference)
        query = meta.Session.query(cls).autoflush(False)
        query = query.filter(or_(cls.name == user_reference,
                                 cls.openid == corrected_openid_user_ref,
                                 cls.id == user_reference))
        return query.first()

def user_ids_for_name_or_id(self, user_list=[]):
        '''
        This function returns a list of ids from an input that can be a list of
        names or ids
        '''
        query = meta.Session.query(self.id)
        query = query.filter(or_(self.name.in_(user_list),
                                 self.id.in_(user_list)))
        return [user.id for user in query.all()]

def __query__(self):
        filter_ = []
        for column in self.table._columns:
            for f in column.filters:
                if isinstance(f, Ilike) and f.__query__() is not None:
                    filter_.append(f.__query__())

        return or_(*filter_) if filter_ else None

def get_account_limits(account, rse_ids=None, session=None):
    """
    Returns the account limits for the account on the list of rses.

    :param account:  Account to check the limit for.
    :param rse_ids:  List of RSE ids to check the limit for.
    :param session:  Database session in use.
    :return:         Dictionary {'rse_id': bytes, ...}.
    """

    account_limits = {}
    if rse_ids:
        rse_id_clauses = []
        for rse_id in rse_ids:
            rse_id_clauses.append(and_(models.AccountLimit.rse_id == rse_id, models.AccountLimit.account == account))
        rse_id_clause_chunks = [rse_id_clauses[x:x + 10] for x in xrange(0, len(rse_id_clauses), 10)]
        for rse_id_chunk in rse_id_clause_chunks:
            tmp_limits = session.query(models.AccountLimit).filter(or_(*rse_id_chunk)).all()
            for limit in tmp_limits:
                if limit.bytes == -1:
                    account_limits[limit.rse_id] = float("inf")
                else:
                    account_limits[limit.rse_id] = limit.bytes
    else:
        account_limits_tmp = session.query(models.AccountLimit).filter(models.AccountLimit.account == account).all()
        for limit in account_limits_tmp:
            if limit.bytes == -1:
                account_limits[limit.rse_id] = float("inf")
            else:
                account_limits[limit.rse_id] = limit.bytes
    return account_limits

def sort_rses(rses, session=None):
    """
    Sort a list of RSES by srm free space (ascending order).

    :param rses: List of RSEs.
    :param session: The database session in use.

    :returns: Sorted list of RSEs
    """
    if not rses:
        raise exception.InputValidationError('The list rses should not be empty!')

    if len(rses) == 1:
        return rses

    false_value = False
    query = session.query(models.RSE.rse, models.RSE.staging_area, models.RSEUsage.rse_id).\
        filter(or_(models.RSEUsage.source == 'srm', models.RSEUsage.source == 'gsiftp')).\
        filter(models.RSEUsage.rse_id == models.RSE.id).\
        filter(models.RSE.deleted == false_value)
    condition = []
    for rse in rses:
        condition.append(models.RSE.id == rse['id'])
    query = query.filter(or_(*condition)).order_by(models.RSEUsage.free.asc())
    return [{'rse': rse, 'staging_area': staging_area, 'id': rse_id} for rse, staging_area, rse_id in query]
    # return sample(rses, len(rses))

def json_search():
    query = request.args['query']
    return jsonify(users=map(
        lambda u: {"id": u.id, "name": u.name},
        session.query(User.id, User.name).filter(or_(
            func.lower(User.name).like(func.lower(u"%{}%".format(query))),
            User.id.like(u"{}%".format(query))
        ))
    ))

def get_stuck_rules(total_workers, worker_number, delta=600, limit=10, blacklisted_rules=[], session=None):
    """
    Get stuck rules.

    :param total_workers:      Number of total workers.
    :param worker_number:      id of the executing worker.
    :param delta:              Delta in seconds to select rules in.
    :param limit:              Maximum number of rules to select.
    :param blacklisted_rules:  Blacklisted rules to filter out.
    :param session:            Database session in use.
    """
    if session.bind.dialect.name == 'oracle':
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_STUCKSTATE_IDX)", 'oracle').\
            filter(text("(CASE when rules.state='S' THEN rules.state ELSE null END)= 'S' ")).\
            filter(models.ReplicationRule.state == RuleState.STUCK).\
            filter(models.ReplicationRule.updated_at < datetime.utcnow() - timedelta(seconds=delta)).\
            filter(or_(models.ReplicationRule.expires_at == null(),
                       models.ReplicationRule.expires_at > datetime.utcnow(),
                       models.ReplicationRule.locked == true())).\
            order_by(models.ReplicationRule.updated_at)  # NOQA
    else:
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_STUCKSTATE_IDX)", 'oracle').\
            filter(models.ReplicationRule.state == RuleState.STUCK).\
            filter(models.ReplicationRule.updated_at < datetime.utcnow() - timedelta(seconds=delta)).\
            filter(or_(models.ReplicationRule.expires_at == null(),
                       models.ReplicationRule.expires_at > datetime.utcnow(),
                       models.ReplicationRule.locked == true())).\
            order_by(models.ReplicationRule.updated_at)

    if session.bind.dialect.name == 'oracle':
        bindparams = [bindparam('worker_number', worker_number),
                      bindparam('total_workers', total_workers)]
        query = query.filter(text('ORA_HASH(name, :total_workers) = :worker_number', bindparams=bindparams))
    elif session.bind.dialect.name == 'mysql':
        query = query.filter(text('mod(md5(name), %s) = %s' % (total_workers + 1, worker_number)))
    elif session.bind.dialect.name == 'postgresql':
        query = query.filter(text('mod(abs((\'x\'||md5(name))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))

    if limit:
        fetched_rules = query.limit(limit).all()
        filtered_rules = [rule for rule in fetched_rules if rule[0] not in blacklisted_rules]
        if len(fetched_rules) == limit and len(filtered_rules) == 0:
            return get_stuck_rules(total_workers=total_workers,
                                   worker_number=worker_number,
                                   delta=delta,
                                   limit=None,
                                   blacklisted_rules=blacklisted_rules,
                                   session=session)
        else:
            return filtered_rules
    else:
        return [rule for rule in query.all() if rule[0] not in blacklisted_rules]

def get_files_and_replica_locks_of_dataset(scope, name, nowait=False, restrict_rses=None, only_stuck=False, session=None):
    """
    Get all the files of a dataset and, if existing, all locks of the file.

    :param scope:          Scope of the dataset
    :param name:           Name of the datset
    :param nowait:         Nowait parameter for the FOR UPDATE statement
    :param restrict_rses:  Possible RSE_ids to filter on.
    :param only_stuck:     If true, only get STUCK locks.
    :param session:        The db session.
    :return:               Dictionary with keys: (scope, name)
                           and as value: [LockObject]
    :raises:               NoResultFound
    """
    # with_hint(models.ReplicaLock, "INDEX(LOCKS LOCKS_PK)", 'oracle').\
    query = session.query(models.DataIdentifierAssociation.child_scope,
                          models.DataIdentifierAssociation.child_name,
                          models.ReplicaLock).\
        with_hint(models.DataIdentifierAssociation, "INDEX_RS_ASC(CONTENTS CONTENTS_PK) NO_INDEX_FFS(CONTENTS CONTENTS_PK)", 'oracle').\
        outerjoin(models.ReplicaLock,
                  and_(models.DataIdentifierAssociation.child_scope == models.ReplicaLock.scope,
                       models.DataIdentifierAssociation.child_name == models.ReplicaLock.name))\
        .filter(models.DataIdentifierAssociation.scope == scope, models.DataIdentifierAssociation.name == name)

    if restrict_rses is not None:
        rse_clause = []
        for rse_id in restrict_rses:
            rse_clause.append(models.ReplicaLock.rse_id == rse_id)
        if rse_clause:
            query = session.query(models.DataIdentifierAssociation.child_scope,
                                  models.DataIdentifierAssociation.child_name,
                                  models.ReplicaLock).\
                with_hint(models.DataIdentifierAssociation, "INDEX_RS_ASC(CONTENTS CONTENTS_PK) NO_INDEX_FFS(CONTENTS CONTENTS_PK)", 'oracle').\
                outerjoin(models.ReplicaLock,
                          and_(models.DataIdentifierAssociation.child_scope == models.ReplicaLock.scope,
                               models.DataIdentifierAssociation.child_name == models.ReplicaLock.name,
                               or_(*rse_clause)))\
                .filter(models.DataIdentifierAssociation.scope == scope,
                        models.DataIdentifierAssociation.name == name)

    if only_stuck:
        query = query.filter(models.ReplicaLock.state == LockState.STUCK)

    query = query.with_for_update(nowait=nowait, of=models.ReplicaLock.state)

    locks = {}

    for child_scope, child_name, lock in query:
        if (child_scope, child_name) not in locks:
            if lock is None:
                locks[(child_scope, child_name)] = []
            else:
                locks[(child_scope, child_name)] = [lock]
        else:
            locks[(child_scope, child_name)].append(lock)

    return locks

def user_show(user_id):

    user = User.q.get(user_id)
    if user is None:
        flash(u"Nutzer mit ID %s existiert nicht!" % (user_id,), 'error')
        abort(404)

    room = Room.q.get(user.room_id)
    form = UserLogEntry()

    if form.validate_on_submit():
        lib.logging.create_user_log_entry(message=form.message.data,
            timestamp=datetime.now(),
            author=current_user,
            user=user)
        flash(u'Kommentar hinzugefügt', 'success')

    log_list = user.user_log_entries + room.room_log_entries
    log_list.sort(key=lambda LogEntry: LogEntry.timestamp, reverse=True)
    user_log_list = user.user_log_entries[::-1]
    room_log_list = room.room_log_entries[::-1]

    memberships = Membership.q.filter(Membership.user_id == user.id)
    memberships_active = memberships.filter(
        # it is important to use == here, "is" does NOT work
        or_(Membership.start_date == None,
            Membership.start_date <= datetime.now())
    ).filter(
        # it is important to use == here, "is" does NOT work
        or_(Membership.end_date == None,
            Membership.end_date > datetime.now())
    )

    return render_template('user/user_show.html',
        user=user,
        all_log=log_list,
        user_log=user_log_list,
        room_log=room_log_list,
        room=room,
        form=form,
        memberships=memberships.all(),
        memberships_active=memberships_active.all())

def list_bangumi(self, page, count, sort_field, sort_order, name, user_id, bangumi_type):
        try:

            session = SessionManager.Session()
            query_object = session.query(Bangumi).\
                options(joinedload(Bangumi.cover_image)).\
                filter(Bangumi.delete_mark == None)

            if bangumi_type != -1:
                query_object = query_object.filter(Bangumi.type == bangumi_type)

            if name is not None:
                name_pattern = '%{0}%'.format(name.encode('utf-8'),)
                logger.debug(name_pattern)
                query_object = query_object.\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern)))
                # count total rows
                total = session.query(func.count(Bangumi.id)).\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern))).\
                    scalar()
            else:
                total = session.query(func.count(Bangumi.id)).scalar()

            if sort_order == 'desc':
                query_object = query_object.\
                    order_by(desc(getattr(Bangumi, sort_field)))

            else:
                query_object = query_object.\
                    order_by(asc(getattr(Bangumi, sort_field)))

            if count == -1:
                bangumi_list = query_object.all()
            else:
                offset = (page - 1) * count
                bangumi_list = query_object.offset(offset).limit(count).all()

            bangumi_id_list = [bgm.id for bgm in bangumi_list]

            favorites = session.query(Favorites).\
                filter(Favorites.bangumi_id.in_(bangumi_id_list)).\
                filter(Favorites.user_id == user_id).\
                all()

            bangumi_dict_list = []
            for bgm in bangumi_list:
                bangumi = row2dict(bgm)
                bangumi['cover'] = utils.generate_cover_link(bgm)
                utils.process_bangumi_dict(bgm, bangumi)
                for fav in favorites:
                    if fav.bangumi_id == bgm.id:
                        bangumi['favorite_status'] = fav.status
                bangumi_dict_list.append(bangumi)

            return json_resp({'data': bangumi_dict_list, 'total': total})
        finally:
            SessionManager.Session.remove()

def list_bangumi(self, page, count, sort_field, sort_order, name, bangumi_type):
        try:
            session = SessionManager.Session()
            query_object = session.query(Bangumi).\
                options(joinedload(Bangumi.cover_image)).\
                options(joinedload(Bangumi.created_by)).\
                options(joinedload(Bangumi.maintained_by)).\
                filter(Bangumi.delete_mark == None)

            if bangumi_type != -1:
                query_object = query_object.filter(Bangumi.type == bangumi_type)

            if name is not None:
                name_pattern = '%{0}%'.format(name.encode('utf-8'),)
                logger.debug(name_pattern)
                query_object = query_object.\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern)))
                # count total rows
                total = session.query(func.count(Bangumi.id)).\
                    filter(or_(Bangumi.name.ilike(name_pattern), Bangumi.name_cn.ilike(name_pattern))).\
                    scalar()
            else:
                total = session.query(func.count(Bangumi.id)).scalar()

            if sort_order == 'desc':
                query_object = query_object.\
                    order_by(desc(getattr(Bangumi, sort_field)))
            else:
                query_object = query_object.\
                    order_by(asc(getattr(Bangumi, sort_field)))

            # we now support query all method by passing count = -1
            if count == -1:
                bangumi_list = query_object.all()
            else:
                offset = (page - 1) * count
                bangumi_list = query_object.offset(offset).limit(count).all()

            bangumi_dict_list = []
            for bgm in bangumi_list:
                bangumi = row2dict(bgm)
                bangumi['cover'] = utils.generate_cover_link(bgm)
                utils.process_bangumi_dict(bgm, bangumi)
                self.__process_user_obj_in_bangumi(bgm, bangumi)
                bangumi_dict_list.append(bangumi)

            return json_resp({'data': bangumi_dict_list, 'total': total})
            # raise ClientError('something happened')
        finally:
            SessionManager.Session.remove()

def add_text_search(query, join_columns, keywords, locales, include_rank=True, lse=None):
    from assembl.models.langstrings import LangStringEntry
    rank = None
    keywords_j = ' & '.join(keywords)
    lse = lse or aliased(LangStringEntry)
    join_conds = [lse.langstring_id == join_column for join_column in join_columns]
    if len(join_conds) > 1:
        join_cond = or_(*join_conds)
    else:
        join_cond = join_conds[0]
    query = query.join(lse, join_cond)
    if locales:
        active_text_indices = get('active_text_indices', 'en')
        locales_by_config = defaultdict(list)
        any_locale = 'any' in locales
        for locale in locales:
            fts_config = postgres_language_configurations.get(locale, 'simple')
            if fts_config not in active_text_indices:
                fts_config = 'simple'
            locales_by_config[fts_config].append(locale)
        conds = {}
        # TODO: to_tsquery vs plainto_tsquery vs phraseto_tsquery
        for fts_config, locales in locales_by_config.items():
            conds[fts_config] = (
                or_(*[((lse.locale == locale) | lse.locale.like(locale + "_%"))
                    for locale in locales]) if 'any' not in locales else None,
                func.to_tsvector(fts_config, lse.value))
        filter = [cond & v.match(keywords_j, postgresql_regconfig=conf)
                  for (conf, (cond, v)) in conds.items()
                  if cond is not None]
        if any_locale:
            (_, v) = conds['simple']
            filter.append(v.match(keywords_j, postgresql_regconfig='simple'))
        query = query.filter(or_(*filter))
        if include_rank:
            if len(conds) > 1:
                if any_locale:
                    (_, v) = conds['simple']
                    else_case = func.ts_rank(v, func.to_tsquery('simple', keywords_j))
                else:
                    else_case = 0
                rank = case([
                    (cond, func.ts_rank(v, func.to_tsquery(conf, keywords_j)))
                    for (conf, (cond, v)) in conds.items()
                    if cond is not None], else_ = else_case).label('score')
            else:
                (conf, (cond, v)) = next(iter(conds.items()))
                rank = func.ts_rank(v, func.to_tsquery(conf, keywords_j)).label('score')
            query = query.add_column(rank)
    else:
        fts_config = 'simple'
        query = query.filter(
            func.to_tsvector(fts_config, lse.value
                ).match(keywords_j, postgresql_regconfig=fts_config))
        if include_rank:
            rank = func.ts_rank(
                func.to_tsvector(fts_config, lse.value),
                func.to_tsquery(fts_config, keywords_j)).label('score')
            query = query.add_column(rank)
    return query, rank