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
python类or_()的实例源码
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
