def get_search_results(self, term, offset=None, order=None, limit=None):
order = order or [Books.sort]
pagination = None
term.strip().lower()
self.session.connection().connection.connection.create_function("lower", 1, lcase)
q = list()
authorterms = re.split("[, ]+", term)
for authorterm in authorterms:
q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%")))
result = self.session.query(Books).filter(self.common_filters(True)).filter(
or_(Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")),
Books.series.any(func.lower(Series.name).ilike("%" + term + "%")),
Books.authors.any(and_(*q)),
Books.publishers.any(func.lower(Publishers.name).ilike("%" + term + "%")),
func.lower(Books.title).ilike("%" + term + "%")
)).order_by(*order).all()
result_count = len(result)
if offset != None and limit != None:
offset = int(offset)
limit_all = offset + int(limit)
pagination = Pagination((offset / (int(limit)) + 1), limit, result_count)
else:
offset = 0
limit_all = result_count
ub.store_ids(result)
return result[offset:limit_all], result_count, pagination
def upgrade():
op.execute(
sa.schema.CreateIndex(sa.Index('ix_user_email',
func.lower(User.email))))
# op.execute(sa.schema.CreateIndex(sa.Index('users_username_index', func.lower(User.username))))
sa.Index('ix_user_username',
func.lower(User.email)).create(bind=op.get_bind())
def resolve_no_team_students(root, info, division_id, school_id):
return Student.get_query(info).filter_by(
school_id=school_id,
current_division_id=division_id,
current_team_id=None).order_by(func.lower(StudentModel.last),
func.lower(
StudentModel.first)).all()
def sort( self, trans, query, ascending, column_name=None ):
""" Sort column using case-insensitive alphabetical sorting on item's username. """
if ascending:
query = query.order_by( func.lower( self.model_class.username ).asc() )
else:
query = query.order_by( func.lower( self.model_class.username ).desc() )
return query
def search_query(self, term, *join):
term.strip().lower()
self.session.connection().connection.connection.create_function(
"lower", 1, lcase)
q = list()
authorterms = re.split("[, ]+", term)
for authorterm in authorterms:
q.append(
Books.authors.any(
func.lower(Authors.name).ilike("%" + authorterm + "%")))
query = self.session.query(Books)
if len(join) == 3:
query = query.outerjoin(join[0], join[1]).outerjoin(join[2])
elif len(join) == 2:
query = query.outerjoin(join[0], join[1])
elif len(join) == 1:
query = query.outerjoin(join[0])
return query.filter(self.common_filters(True)).filter(
or_(
Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")),
Books.series.any(
func.lower(Series.name).ilike("%" + term + "%")),
Books.authors.any(and_(*q)),
Books.publishers.any(
func.lower(Publishers.name).ilike("%" + term + "%")),
func.lower(Books.title).ilike("%" + term + "%")))
def sort( self, trans, query, ascending, column_name=None ):
""" Sort column using case-insensitive alphabetical sorting on item's username. """
if ascending:
query = query.order_by( func.lower( self.model_class.username ).asc() )
else:
query = query.order_by( func.lower( self.model_class.username ).desc() )
return query
def get_filter(self, trans, user, column_filter):
# Parse filter to extract multiple tags.
if isinstance(column_filter, list):
# Collapse list of tags into a single string; this is redundant but effective. TODO: fix this by iterating over tags.
column_filter = ",".join(column_filter)
raw_tags = trans.app.tag_handler.parse_tags(column_filter.encode("utf-8"))
clause_list = []
for name, value in raw_tags.items():
if name:
# Filter by individual's tag names.
clause_list.append(
self.model_class.tags.any(
and_(
func.lower(self.model_tag_association_class.user_tname).like("%" + name.lower() + "%"),
self.model_tag_association_class.user == user,
)
)
)
if value:
# Filter by individual's tag values.
clause_list.append(
self.model_class.tags.any(
and_(
func.lower(self.model_tag_association_class.user_value).like("%" + value.lower() + "%"),
self.model_tag_association_class.user == user,
)
)
)
return and_(*clause_list)
def filter(self, query, q, **kwargs):
if q:
query = query.filter(
or_(func.lower(User.first_name).like(q + "%"),
func.lower(User.last_name).like(q + "%"))
)
return query
def filter(self, query, q, **kwargs):
if q:
query = query.filter(
or_(
func.lower(User.first_name).like(q + "%"),
func.lower(User.last_name).like(q + "%")))
return query
def addMod(message):
sysSettings = settings.settings.query.first()
if '@' in str(message['JID']):
JID = str(message['JID'])
else:
username = str(message['JID'])
userQuery = Sec.User.query.filter(
func.lower(Sec.User.username) == func.lower(username)).first()
if userQuery is not None:
JID = username + '@' + sysSettings.siteAddress
channelLoc = str(message['channelLoc'])
channelQuery = Channel.Channel.query.filter_by(
channelLoc=channelLoc, owningUser=current_user.id).first()
if channelQuery is not None and JID != "":
from app import ejabberd
ejabberd.set_room_affiliation(channelLoc,
'conference.' + sysSettings.siteAddress,
JID, 'admin')
emit('addMod', {
'mod': str(JID),
'channelLoc': str(channelLoc)
},
broadcast=False)
else:
pass
db.session.commit()
db.session.close()
return 'OK'
def data(self, *args, **kw):
security = get_service('security')
length = int(kw.get("iDisplayLength", 0))
start = int(kw.get("iDisplayStart", 0))
sort_dir = kw.get("sSortDir_0", "asc")
echo = int(kw.get("sEcho", 0))
search = kw.get("sSearch", "").replace("%", "").strip().lower()
end = start + length
query = Group.query \
.options(sa.orm.noload('*'))
total_count = query.count()
if search:
# TODO: gérer les accents
query = query.filter(
func.lower(Group.name).like("%" + search + "%"),
)
count = query.count()
columns = [func.lower(Group.name)]
direction = asc if sort_dir == 'asc' else desc
order_by = list(map(direction, columns))
# sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
engine = query.session.get_bind(Group.__mapper__)
if engine.name != 'sqlite':
order_by[0] = nullslast(order_by[0])
query = query.order_by(*order_by) \
.add_columns(Group.members_count)
groups = query.slice(start, end).all()
data = []
for group, members_count in groups:
# TODO: this should be done on the browser.
group_url = url_for(".groups_group", group_id=group.id)
name = escape(getattr(group, "name") or "")
roles = [r for r in security.get_roles(group) if r.assignable]
columns = [
'<a href="{url}">{name}</a>'.format(url=group_url, name=name),
text_type(members_count or 0),
render_template_string(
'''{%- for role in roles %}
<span class="badge badge-default">{{ role }}</span>
{%- endfor %}''',
roles=roles,
),
'\u2713' if group.public else '',
]
data.append(columns)
return {
"sEcho": echo,
"iTotalRecords": total_count,
"iTotalDisplayRecords": count,
"aaData": data,
}
def order_by_name(query, table, language=None, *extra_languages, **kwargs):
"""Order a query by name.
query: The query to order
table: Table of the named objects
language: The language to order names by. If None, use the
connection default.
extra_languages: Extra languages to order by, should the translations for
`language` be incomplete (or ambiguous).
name_attribute (keyword argument): the attribute to use; defaults to 'name'
Uses the identifier as a fallback ordering.
"""
name_attribute = kwargs.pop('name', 'name')
if kwargs:
raise ValueError('Unexpected keyword arguments: %s' % kwargs.keys())
order_columns = []
if language is None:
query = query.outerjoin(table.names_local)
order_columns.append(func.lower(getattr(table.names_table, name_attribute)))
else:
extra_languages = (language, ) + extra_languages
for language in extra_languages:
names_table = aliased(table.names_table)
query = query.outerjoin(names_table)
query = query.filter(names_table.foreign_id == table.id)
query = query.filter(names_table.local_language_id == language.id)
order_columns.append(func.lower(getattr(names_table, name_attribute)))
order_columns.append(table.identifier)
query = query.order_by(coalesce(*order_columns))
return query
def order_by_name(query, table, language=None, *extra_languages, **kwargs):
"""Order a query by name.
query: The query to order
table: Table of the named objects
language: The language to order names by. If None, use the
connection default.
extra_languages: Extra languages to order by, should the translations for
`language` be incomplete (or ambiguous).
name_attribute (keyword argument): the attribute to use; defaults to 'name'
Uses the identifier as a fallback ordering.
"""
name_attribute = kwargs.pop('name', 'name')
if kwargs:
raise ValueError('Unexpected keyword arguments: %s' % list(kwargs.keys()))
order_columns = []
if language is None:
query = query.outerjoin(table.names_local)
order_columns.append(func.lower(getattr(table.names_table, name_attribute)))
else:
extra_languages = (language, ) + extra_languages
for language in extra_languages:
names_table = aliased(table.names_table)
query = query.outerjoin(names_table)
query = query.filter(names_table.foreign_id == table.id)
query = query.filter(names_table.local_language_id == language.id)
order_columns.append(func.lower(getattr(names_table, name_attribute)))
order_columns.append(table.identifier)
query = query.order_by(coalesce(*order_columns))
return query
def get_filter(self, trans, user, column_filter):
# Parse filter to extract multiple tags.
if isinstance(column_filter, list):
# Collapse list of tags into a single string; this is redundant but effective. TODO: fix this by iterating over tags.
column_filter = ",".join(column_filter)
raw_tags = trans.app.tag_handler.parse_tags(
column_filter.encode("utf-8"))
clause_list = []
for name, value in raw_tags.items():
if name:
# Filter by individual's tag names.
clause_list.append(
self.model_class.tags.any(
and_(
func.lower(self.model_tag_association_class.
user_tname).like("%" + name.lower() +
"%"),
self.model_tag_association_class.user == user)))
if value:
# Filter by individual's tag values.
clause_list.append(
self.model_class.tags.any(
and_(
func.lower(
self.model_tag_association_class.user_value
).like("%" + value.lower() + "%"),
self.model_tag_association_class.user ==
user)))
return and_(*clause_list)
def apply_filters(query):
if 'filters' not in query_params:
return query
filters = query_params['filters']
for key, val in filters.items():
op, field_name = key.split('_', 1)
try:
field = getattr(cls, field_name)
except AttributeError:
log.warn('RestMixin.rest_get_list(): filter "%s=%s": unknown attribute %s',
key, val, field_name)
continue
if op == 'e':
query = query.filter(field == val)
elif op == 'n':
query = query.filter(or_(field == val, field == None))
elif op == 'l':
query = query.filter(func.lower(field).like('%' + val.lower() + '%'))
elif op == 's':
query = query.filter(func.lower(field).like(val.lower() + '%'))
else:
log.error('get_for_rest_grid: filter "%s=%s": unknown op: %s' % (key, val, op))
return query
def data(self, *args, **kw) -> Dict:
security = get_service("security")
length = int(kw.get("iDisplayLength", 0))
start = int(kw.get("iDisplayStart", 0))
sort_dir = kw.get("sSortDir_0", "asc")
echo = int(kw.get("sEcho", 0))
search = kw.get("sSearch", "").replace("%", "").strip().lower()
end = start + length
# pyre-fixme[16]: `Group` has no attribute `query`.
query = Group.query.options(sa.orm.noload("*"))
total_count = query.count()
if search:
# TODO: gérer les accents
query = query.filter(
func.lower(Group.name).like("%" + search + "%"))
count = query.count()
columns = [func.lower(Group.name)]
direction = asc if sort_dir == "asc" else desc
order_by = list(map(direction, columns))
# sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
# pyre-fixme[16]: `Group` has no attribute `__mapper__`.
engine = query.session.get_bind(Group.__mapper__)
if engine.name != "sqlite":
order_by[0] = nullslast(order_by[0])
query = query.order_by(*order_by).add_columns(Group.members_count)
groups = query.slice(start, end).all()
data = []
for group, members_count in groups:
# TODO: this should be done on the browser.
group_url = url_for(".groups_group", group_id=group.id)
name = html.escape(group.name or "")
# pyre-fixme[16]: `Service` has no attribute `get_roles`.
roles = [r for r in security.get_roles(group) if r.assignable]
columns = [
f'<a href="{group_url}">{name}</a>',
str(members_count or 0),
render_template_string(
"""{%- for role in roles %}
<span class="badge badge-default">{{ role }}</span>
{%- endfor %}""",
roles=roles,
),
"\u2713" if group.public else "",
]
data.append(columns)
return {
"sEcho": echo,
"iTotalRecords": total_count,
"iTotalDisplayRecords": count,
"aaData": data,
}
def validate_username(form, field):
log.error("validate_username")
if not re.match('[a-zA-Z0-9-_]+', field.data):
raise validators.ValidationError(
'Username may only contain alpha-numeric characters')
if DBSession.query(User).filter(
func.lower(User.username) == func.lower(field.data)).first():
raise validators.ValidationError('Username already taken')
def exact_name_lookup(card, edition=None):
cquery = session.query(CardInfo).filter(func.lower(CardInfo.name)==func.lower(card))
if edition is not None:
cquery = cquery.filter(func.lower(CardInfo.edition)==func.lower(edition))
cards = cquery.all();
if cards == []:
print 'not found:', card
return cards[0:1]
def quicksearch(request):
message = None
query = DBSession.query(Languoid)
term = request.params['search'].strip()
titlecase = term.istitle()
term = term.lower()
params = {'iso': '', 'country': '',
'name': '', 'namequerytype': 'part', 'multilingual': ''}
if not term:
query = None
elif len(term) < 3:
query = None
message = ('Please enter at least four characters for a name search '
'or three characters for an iso code')
elif len(term) == 3 and not titlecase:
query = query.filter(Languoid.identifiers.any(
type=IdentifierType.iso.value, name=term))
kind = 'ISO 639-3'
elif len(term) == 8 and GLOTTOCODE_PATTERN.match(term):
query = query.filter(Languoid.id == term)
kind = 'Glottocode'
else:
_query = query.filter(func.lower(Languoid.name) == term)
if DBSession.query(_query.exists()).scalar():
query = _query
else:
query = query.filter(or_(
func.lower(Languoid.name).contains(term),
Languoid.identifiers.any(and_(
Identifier.type == u'name',
Identifier.description == Languoid.GLOTTOLOG_NAME,
func.lower(Identifier.name).contains(term)))))
kind = 'name part'
params['name'] = term
if query is None:
languoids = []
else:
languoids = query.order_by(Languoid.name)\
.options(joinedload(Languoid.family)).all()
if not languoids:
term_pre = HTML.kbd(term, style='white-space: pre')
message = 'No matching languoids found for %s "' % kind + term_pre + '"'
elif len(languoids) == 1:
raise HTTPFound(request.resource_url(languoids[0]))
map_, icon_map, family_map = get_selected_languages_map(request, languoids)
layer = list(map_.get_layers())[0]
if not layer.data['features']:
map_ = None
countries = json.dumps(['%s (%s)' % (c.name, c.id) for c in
DBSession.query(Country).order_by(Country.description)])
return {'message': message, 'params': params, 'languoids': languoids,
'map': map_, 'countries': countries}
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 sort( self, trans, query, ascending, column_name=None ):
"""Sort column using case-insensitive alphabetical sorting."""
if column_name is None:
column_name = self.key
if ascending:
query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).asc() )
else:
query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).desc() )
return query
def check_exists_book(self, authr, title):
self.session.connection().connection.connection.create_function("lower", 1, lcase)
q = list()
authorterms = re.split(r'\s*&\s*', authr)
for authorterm in authorterms:
q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%")))
return self.session.query(Books)\
.filter(and_(Books.authors.any(and_(*q)), func.lower(Books.title).ilike("%" + title + "%"))).first()
def sort( self, trans, query, ascending, column_name=None ):
"""Sort column using case-insensitive alphabetical sorting."""
if column_name is None:
column_name = self.key
if ascending:
query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).asc() )
else:
query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).desc() )
return query
def data(self, *args, **kw):
security = current_app.services['security']
length = int(kw.get("iDisplayLength", 0))
start = int(kw.get("iDisplayStart", 0))
sort_dir = kw.get("sSortDir_0", "asc")
echo = int(kw.get("sEcho", 0))
search = kw.get("sSearch", "").replace("%", "").strip().lower()
end = start + length
q = Group.query \
.options(sa.orm.noload('*'))
total_count = q.count()
if search:
# TODO: gérer les accents
q = q.filter(func.lower(Group.name).like("%" + search + "%"))
count = q.count()
columns = [func.lower(Group.name)]
direction = asc if sort_dir == 'asc' else desc
order_by = map(direction, columns)
# sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
engine = q.session.get_bind(Group.__mapper__)
if engine.name != 'sqlite':
order_by[0] = nullslast(order_by[0])
q = q.order_by(*order_by) \
.add_columns(Group.members_count)
groups = q.slice(start, end).all()
data = []
for group, members_count in groups:
# TODO: this should be done on the browser.
group_url = url_for(".groups_group", group_id=group.id)
name = escape(getattr(group, "name") or "")
roles = [r for r in security.get_roles(group) if r.assignable]
columns = []
columns.append(
u'<a href="{url}">{name}</a>'.format(url=group_url, name=name)
)
columns.append(unicode(members_count or 0))
columns.append(render_template_string(
u'''{%- for role in roles %}
<span class="badge badge-default">{{ role }}</span>
{%- endfor %}''',
roles=roles))
columns.append(u'\u2713' if group.public else u'')
data.append(columns)
return {
"sEcho": echo,
"iTotalRecords": total_count,
"iTotalDisplayRecords": count,
"aaData": data,
}
def data(self, *args, **kw):
security = get_service("security")
length = int(kw.get("iDisplayLength", 0))
start = int(kw.get("iDisplayStart", 0))
sort_dir = kw.get("sSortDir_0", "asc")
echo = int(kw.get("sEcho", 0))
search = kw.get("sSearch", "").replace("%", "").strip().lower()
end = start + length
query = Group.query.options(sa.orm.noload("*"))
total_count = query.count()
if search:
# TODO: gérer les accents
query = query.filter(func.lower(Group.name).like("%" + search + "%"))
count = query.count()
columns = [func.lower(Group.name)]
direction = asc if sort_dir == "asc" else desc
order_by = list(map(direction, columns))
# sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
engine = query.session.get_bind(Group.__mapper__)
if engine.name != "sqlite":
order_by[0] = nullslast(order_by[0])
query = query.order_by(*order_by).add_columns(Group.members_count)
groups = query.slice(start, end).all()
data = []
for group, members_count in groups:
# TODO: this should be done on the browser.
group_url = url_for(".groups_group", group_id=group.id)
name = html.escape(group.name or "")
roles = [r for r in security.get_roles(group) if r.assignable]
columns = [
f'<a href="{group_url}">{name}</a>',
str(members_count or 0),
render_template_string(
"""{%- for role in roles %}
<span class="badge badge-default">{{ role }}</span>
{%- endfor %}""",
roles=roles,
),
"\u2713" if group.public else "",
]
data.append(columns)
return {
"sEcho": echo,
"iTotalRecords": total_count,
"iTotalDisplayRecords": count,
"aaData": data,
}
def rss_category(self):
category = func.lower(self.request.matchdict['category'])
query = self.Entry.get_all_active(self.request)
query = query.join(self.Category)
query = query.filter(
func.lower(self.Category.title) == category
)
entries = query.all()
return self.get_rss_data(entries)
def wildcard_handler(col, wc_term):
if wc_term.startswith('*') and not wc_term.endswith('*'):
#postfix == endswith
wc_term = remove_prefix(wc_term, prefix='*')
return func.lower(col).endswith(func.lower(wc_term))
elif wc_term.startswith('*') and wc_term.endswith('*'):
#wildcard == ilike
wc_term = remove_prefix(wc_term, prefix='*')
wc_term = remove_suffix(wc_term, suffix='*')
return func.lower(col).contains(func.lower(wc_term))
def teacher_by_name(first_name, last_name):
"""
Returns the FIRST DistrictTeacher object that matches a given CASE
INSENSITIVE first and last name
"""
return (db_session.query(DistrictTeacher).filter(
func.lower(DistrictTeacher.teacher_first_name) ==
first_name.lower()).filter(
func.lower(DistrictTeacher.teacher_last_name) ==
last_name.lower()).first())
def wildcard_handler(col, wc_term):
if wc_term.startswith('*') and not wc_term.endswith('*'):
#postfix == endswith
wc_term = remove_prefix(wc_term, prefix='*')
return func.lower(col).endswith(wc_term)
elif wc_term.startswith('*') and wc_term.endswith('*'):
#wildcard == ilike
wc_term = remove_prefix(wc_term, prefix='*')
wc_term = remove_suffix(wc_term, suffix='*')
return func.lower(col).contains(wc_term)
def search_query(self, term, config_read_column, *join):
term.strip().lower()
self.session.connection().connection.connection.create_function(
"lower", 1, lcase)
q = list()
authorterms = re.split("[, ]+", term)
for authorterm in authorterms:
q.append(
Books.authors.any(
func.lower(Authors.name).ilike("%" + authorterm + "%")))
if not config_read_column:
query = (self.session.query(
Books, ub.ArchivedBook.is_archived,
ub.ReadBook).select_from(Books).outerjoin(
ub.ReadBook,
and_(Books.id == ub.ReadBook.book_id,
int(current_user.id) == ub.ReadBook.user_id)))
else:
try:
read_column = cc_classes[config_read_column]
query = (self.session.query(
Books, ub.ArchivedBook.is_archived,
read_column.value).select_from(Books).outerjoin(
read_column, read_column.book == Books.id))
except (KeyError, AttributeError):
log.error(
"Custom Column No.%d is not existing in calibre database",
config_read_column)
# Skip linking read column
query = self.session.query(Books, ub.ArchivedBook.is_archived,
None)
query = query.outerjoin(
ub.ArchivedBook,
and_(Books.id == ub.ArchivedBook.book_id,
int(current_user.id) == ub.ArchivedBook.user_id))
if len(join) == 6:
query = query.outerjoin(join[0],
join[1]).outerjoin(join[2]).outerjoin(
join[3], join[4]).outerjoin(join[5])
if len(join) == 3:
query = query.outerjoin(join[0], join[1]).outerjoin(join[2])
elif len(join) == 2:
query = query.outerjoin(join[0], join[1])
elif len(join) == 1:
query = query.outerjoin(join[0])
return query.filter(self.common_filters(True)).filter(
or_(
Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")),
Books.series.any(
func.lower(Series.name).ilike("%" + term + "%")),
Books.authors.any(and_(*q)),
Books.publishers.any(
func.lower(Publishers.name).ilike("%" + term + "%")),
func.lower(Books.title).ilike("%" + term + "%")))
def deleteInvitedUser(message):
newUsername = message['username']
userQuery = Sec.User.query.filter(
func.lower(Sec.User.username) == func.lower(newUsername)).first()
if userQuery is None:
emit('checkUniqueUsernameAck', {'results': str(1)}, broadcast=False)
else:
emit('checkUniqueUsernameAck', {'results': str(0)}, broadcast=False)
db.session.commit()
db.session.close()
return 'OK'
def users_json():
q = request.args.get("q").replace("%", " ").lower()
if not q or len(q) < 2:
raise InternalServerError()
query = User.query.filter(
or_(
func.lower(User.first_name).like(q + "%"),
func.lower(User.last_name).like(q + "%"),
)).order_by(func.lower(User.last_name))
with_membership = request.args.get("with_membership")
if with_membership is not None:
# provide membership info for a community
with_membership = int(with_membership)
query = (query.outerjoin(
Membership,
and_(
Membership.user.expression,
Membership.community_id == with_membership,
),
).filter(User.can_login == True).add_columns(Membership.role))
exclude_community = request.args.get("exclude_community")
if exclude_community is not None:
exclude_community = int(exclude_community)
exclude = (~Membership.query.filter(
Membership.user.expression, Membership.community_id
== exclude_community).options(sa.orm.noload("user"),
sa.orm.noload("community")).exists())
query = query.filter(exclude)
results = []
for user in query.all():
role = None
if with_membership is not None:
user, role = user
if role is not None:
role = text_type(role)
item = {
"id": user.id,
"text": "{} ({})".format(user.name, user.email),
"name": user.name,
"email": user.email,
"role": role,
}
results.append(item)
return jsonify({"results": results})
def resolve_viewer_attendees_by_contest(root, info, contest_id, **kwargs):
user = get_current_user()
contest = Contest.get_query(info).get(localize_id(contest_id))
if not contest:
raise GraphQLError("Invalid contest provided!")
return Student.get_query(info).filter_by(
school_id=user.school_id).filter(
StudentModel.attendance.any(
contest_id=contest.id, attended=True)).order_by(
StudentModel.current_team_id.desc(),
func.lower(StudentModel.last),
func.lower(StudentModel.first),
).all()
def search_all_members(self, query, limit=10):
"""
Searches for members from this place or places below this by name, phone or email.
:param query: search query
:return: List of object Member
"""
like_q = "%{0}%".format(query.lower())
q1 = Member.query.filter(
place_parents.c.child_id == Member.place_id,
place_parents.c.parent_id == self.id,
(func.lower(Member.name).like(like_q) | Member.phone.like(like_q) | func.lower(Member.email).like(
like_q))).limit(limit)
return q1.all()
def findCard(self, card, edition=None):
cquery = datasources.database.db.session.query(CardInfo).filter(func.lower(CardInfo.name) == func.lower(card))
edition = self.getRealEditionName(edition)
if edition is not None:
cquery = cquery.filter(func.lower(CardInfo.edition) == func.lower(edition))
cards = cquery.all()
if not cards:
logger.warn('not found: %s', card)
if len(cards) > 1 and self.forceSingleMatch:
logger.warn('more than one matches found for card: {card}, selecting first match: {ed}'.format(card=card,
ed=cards[0].edition))
return cards[0:1]
return cards
def search_all_members(self, query, limit=10):
"""
Searches for members from this place or places below this by name, phone or email.
:param query: search query
:return: List of object Member
"""
like_q = "%{0}%".format(query.lower())
q1 = Member.query.filter(
place_parents.c.child_id == Member.place_id,
place_parents.c.parent_id == self.id,
(func.lower(Member.name).like(like_q) | Member.phone.like(like_q)
| func.lower(Member.email).like(like_q))).limit(limit)
return q1.all()
def users_json():
q = request.args.get("q").replace("%", " ").lower()
if not q or len(q) < 2:
raise InternalServerError()
query = User.query \
.filter(or_(func.lower(User.first_name).like(q + "%"),
func.lower(User.last_name).like(q + "%"))) \
.order_by(func.lower(User.last_name))
with_membership = request.args.get('with_membership')
if with_membership is not None:
# provide membership info for a community
with_membership = int(with_membership)
query = query \
.outerjoin(Membership,
and_(Membership.user.expression,
Membership.community_id == with_membership)) \
.add_columns(Membership.role)
exclude_community = request.args.get('exclude_community')
if exclude_community is not None:
exclude_community = int(exclude_community)
exclude = ~Membership.query \
.filter(Membership.user.expression,
Membership.community_id == exclude_community) \
.options(sa.orm.noload('user'),
sa.orm.noload('community')) \
.exists()
query = query.filter(exclude)
results = []
for user in query.all():
role = None
if with_membership is not None:
user, role = user
if role is not None:
role = text_type(role)
item = {
'id': user.id,
'text': u'{} ({})'.format(user.name, user.email),
'name': user.name,
'email': user.email,
'role': role,
}
results.append(item)
return jsonify({'results': results})
def get_by_name_or_email(self, name_or_email):
"""Get a user by name or email
"""
User = tables.User
user = (
self.session
.query(User)
.filter(or_(
func.lower(User.user_name) == name_or_email.lower(),
func.lower(User.email) == name_or_email.lower()
))
)
return user.first()
def get_search_results(self, term):
term.strip().lower()
self.session.connection().connection.connection.create_function("lower", 1, lcase)
q = list()
authorterms = re.split("[, ]+", term)
for authorterm in authorterms:
q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%")))
return self.session.query(Books).filter(self.common_filters(True)).filter(
or_(Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")),
Books.series.any(func.lower(Series.name).ilike("%" + term + "%")),
Books.authors.any(and_(*q)),
Books.publishers.any(func.lower(Publishers.name).ilike("%" + term + "%")),
func.lower(Books.title).ilike("%" + term + "%")
)).order_by(Books.sort).all()
def resolve_viewer_students_by_contest(root, info, contest_id, **kwargs):
user = get_current_user()
contest = Contest.get_query(info).get(localize_id(contest_id))
if not contest:
raise GraphQLError("Invalid contest provided!")
return Student.get_query(info).filter_by(
current_division_id=contest.division_id,
school_id=user.school_id).join(
StudentDivisionAssociationModel,
StudentModel.current_division_assoc).order_by(
StudentModel.current_team_id.desc(),
StudentDivisionAssociationModel.is_alternate.desc(),
func.lower(StudentModel.last),
func.lower(StudentModel.first),
).all()
def account_query(q):
'''
Returns an SQLAlchemy query object. Example usage:
for row in account_query(u'work pensions').all():
print row
'''
query = model.Session.query(model.Account)
for word in q.split():
query = query.filter(or_(
func.lower(model.Account.name).contains(word.lower()),
func.lower(model.Account.notes).contains(word.lower()),
model.Account.id.startswith(word.lower())
))
return query
def apply_search(query):
search_columns = getattr(cls, '_rest_search_columns', None)
if not search_columns or not 'search' in query_params:
return query
search = query_params['search'].lower()
if len(search_columns) == 1:
col = search_columns[0]
search_filter = func.lower(col).like('%' + search + '%')
else: # > 1
clauses = [func.lower(col).like('%' + search + '%') for col in search_columns]
search_filter = or_(*clauses)
return query.filter(search_filter)
def get_typeahead(self, database, query, replace=('', ''), tag_filter=true()):
query = query or ''
self.session.connection().connection.connection.create_function("lower", 1, lcase)
entries = self.session.query(database).filter(tag_filter). \
filter(func.lower(database.name).ilike("%" + query + "%")).all()
json_dumps = json.dumps([dict(name=r.name.replace(*replace)) for r in entries])
return json_dumps
def _query_active_users_by_email(email):
q = session.query(UserData.email,
UserData.firstname + u" " + UserData.lastname)
q = q.filter(UserData.enabled == True)
q = q.filter(
func.lower(UserData.email).like(u"%" + email.lower() + u"%"))
return q
def update_tag(self, tag, topic):
restr = r',|，|:|：|\|'
tags = [ x.strip() for x in re.split(restr, tag)
if x.strip() != '' ]
old_id_list = [ x.id for x in topic.tags]
taglist = []
# TODO: dotag is ugly.
dotag = []
for tagname in tags:
if tagname in dotag: continue
dotag.append(tagname)
T = self.db.query(ForumTopicTag).filter(
func.lower(ForumTopicTag.name) == tagname.lower() ).first()
if T:
if T.id not in old_id_list:
T.hit += 1
else:
T = ForumTopicTag( name = tagname )
self.db.add(T)
taglist.append( T )
topic.tags = taglist
# delete hit
new_id_list = [ x.id for x in topic.tags]
for ID in old_id_list:
if ID not in new_id_list:
T = self.db.query(ForumTopicTag).get(ID)
if T.hit == 1:
self.db.delete(T)
else:
T.hit -= 1
self.db.commit()
def auto_movie(self, q):
orig_q = q
q = q.lstrip().lower().split(' ')
full_words, q = q[:-1], q[-1]
if 'the' in full_words:
full_words.remove('the')
target = literal(' ').op('||')(Movie.title)
filters = []
for word in full_words:
filters.append(target.ilike('%% %s %%' % word))
filters.append(target.ilike('%% %s%%' % q))
if len(filters) > 1:
filters = and_(*filters)
else:
filters = filters[0]
res = self.session.query(Movie.id, Movie.title, Rating.rating)\
.outerjoin((Rating, and_(Rating.movie_id == Movie.id,
Rating.user == self.user)))\
.filter(filters)\
.order_by(func.similarity(func.lower(target), orig_q).desc())\
.limit(7).all()
self.return_json(res)
def check_auth(username, password):
if sys.version_info.major == 3:
username = username.encode('windows-1252')
user = ub.session.query(ub.User).filter(
func.lower(ub.User.nickname) == username.decode(
'utf-8').lower()).first()
return bool(user and check_password_hash(str(user.password), password))
def index(self):
# Return a rendered template
p = int(request.params.get('page', 1))
c.user = User.get(self.rhodecode_user.user_id)
all_repos = self.sa.query(Repository)\
.filter(Repository.user_id == c.user.user_id)\
.order_by(func.lower(Repository.repo_name)).all()
c.user_repos = ScmModel().get_repos(all_repos)
c.following = self.sa.query(UserFollowing)\
.filter(UserFollowing.user_id == self.rhodecode_user.user_id)\
.options(joinedload(UserFollowing.follows_repository))\
.all()
journal = self._get_journal_data(c.following)
c.journal_pager = Page(journal, page=p, items_per_page=20)
c.journal_day_aggreagate = self._get_daily_aggregate(c.journal_pager)
c.journal_data = render('journal/journal_data.html')
if request.environ.get('HTTP_X_PARTIAL_XHR'):
return c.journal_data
return render('journal/journal.html')
def show(self, group_name, format='html'):
"""GET /repos_groups/group_name: Show a specific item"""
# url('repos_group', group_name=GROUP_NAME)
c.group = c.repos_group = ReposGroupModel()._get_repo_group(group_name)
c.group_repos = c.group.repositories.all()
#overwrite our cached list with current filter
gr_filter = c.group_repos
c.repo_cnt = 0
groups = RepoGroup.query().order_by(RepoGroup.group_name)\
.filter(RepoGroup.group_parent_id == c.group.group_id).all()
c.groups = self.scm_model.get_repos_groups(groups)
c.repos_list = Repository.query()\
.filter(Repository.group_id == c.group.group_id)\
.order_by(func.lower(Repository.repo_name))\
.all()
repos_data = RepoModel().get_repos_as_dict(repos_list=c.repos_list,
admin=False)
#json used to render the grid
c.data = json.dumps(repos_data)
return render('admin/repos_groups/repos_groups.html')
def init_db(app_db_path):
# Open session for database connection
global session
global app_DB_path
app_DB_path = app_db_path
engine = create_engine(u'sqlite:///{0}'.format(app_db_path), echo=False)
Session = scoped_session(sessionmaker())
Session.configure(bind=engine)
session = Session()
if os.path.exists(app_db_path):
Base.metadata.create_all(engine)
migrate_Database(session)
clean_database(session)
else:
Base.metadata.create_all(engine)
create_admin_user(session)
create_anonymous_user(session)
if cli.user_credentials:
username, password = cli.user_credentials.split(':')
user = session.query(User).filter(func.lower(User.nickname) == username.lower()).first()
if user:
user.password = generate_password_hash(password)
if session_commit() == "":
print("Password for user '{}' changed".format(username))
sys.exit(0)
else:
print("Failed changing password")
sys.exit(3)
else:
print("Username '{}' not valid, can't change password".format(username))
sys.exit(3)
def critic():
""" Return all movies reviewed by a given critic.
Takes in name and publication as JSON parameters."""
name = request.args.get('name')
publication = request.args.get('publication')
if not name:
abort(400)
query = session.query(Critic).filter(func.lower(Critic.name) == func.lower(name))
if publication:
query = query.filter(func.lower(Critic.publication) == func.lower(publication))
critic = query.first()
reviews = [review.to_json() for review in critic.reviews] if critic else []
return jsonify(reviews=reviews)
def get_searches_ordered_by_label_matches(
labels,
searcher_role,
searching_for_role,
offset_number=0,
max_number=10):
if offset_number > 0:
offset_number *= max_number
labelnames = [label.name.lower() for label in labels]
query = store.session.query(Search, func.count(Label.id).label('matches'))
query = query.join(Search.labels)
query = query.filter(func.lower(Label.name).in_(labelnames))
query = query.filter(Search.active==True)
query = query.filter(Search.searcher_role==searcher_role)
query = query.filter(Search.searching_for_role==searching_for_role)
query = query.join(Search.searcher_user)
query = query.filter(User.email_confirmed==True)
query = query.filter(User.active==True)
query = query.group_by(Search.id)
query = query.order_by('matches DESC')
searches_and_count = query.offset(offset_number).limit(max_number)
searches = [sc[0] for sc in searches_and_count]
return searches
def get_single_filter( self, user, a_filter ):
""" Filter by annotation and annotation owner. """
return self.model_class.annotations.any(
and_( func.lower( self.model_annotation_association_class.annotation ).like( "%" + a_filter.lower() + "%" ),
# TODO: not sure why, to filter by owner's annotations, we have to do this rather than
# 'self.model_class.user==self.model_annotation_association_class.user'
self.model_annotation_association_class.table.c.user_id == self.model_class.table.c.user_id ) )
def validate(self):
location = self.name_input.value
if (location is None) or (not ListManagerEditBlock.is_alnum_phrase(location)):
error_popup = ErrorPopup(text="Name must be non-empty and contain only alphanumeric characters. Entered: %s" % location)
error_popup.open()
return False
app = SymptomDiaryApp.get_running_app()
session = app.getDBSession()
pain_site_query = session.query(PainSite).filter(func.lower(PainSite.location) == location)
try:
result = pain_site_query.one()
except NoResultFound:
result = None
if (result is not None) and (result.pain_site_id != self.record.pain_site_id):
# We found a similar record (same name ) but it has a different ID
# We should not allow such duplicates
error_popup = ErrorPopup(text = "Pain site %s is already defined in the DB. You must edit that record instead"
% (location) )
error_popup.open()
return False
return True
def index(self):
c.groups = self.scm_model.get_repos_groups()
c.group = None
if c.visual.lightweight_dashboard is False:
c.repos_list = self.scm_model.get_repos()
## lightweight version of dashboard
else:
c.repos_list = Repository.query()\
.filter(Repository.group_id == None)\
.order_by(func.lower(Repository.repo_name))\
.all()
repos_data = []
total_records = len(c.repos_list)
_tmpl_lookup = rhodecode.CONFIG['pylons.app_globals'].mako_lookup
template = _tmpl_lookup.get_template('data_table/_dt_elements.html')
quick_menu = lambda repo_name: (template.get_def("quick_menu")
.render(repo_name, _=_, h=h, c=c))
repo_lnk = lambda name, rtype, private, fork_of: (
template.get_def("repo_name")
.render(name, rtype, private, fork_of, short_name=False,
admin=False, _=_, h=h, c=c))
last_change = lambda last_change: (template.get_def("last_change")
.render(last_change, _=_, h=h, c=c))
rss_lnk = lambda repo_name: (template.get_def("rss")
.render(repo_name, _=_, h=h, c=c))
atom_lnk = lambda repo_name: (template.get_def("atom")
.render(repo_name, _=_, h=h, c=c))
def desc(desc):
if c.visual.stylify_metatags:
return h.urlify_text(h.desc_stylize(h.truncate(desc, 60)))
else:
return h.urlify_text(h.truncate(desc, 60))
for repo in c.repos_list:
repos_data.append({
"menu": quick_menu(repo.repo_name),
"raw_name": repo.repo_name.lower(),
"name": repo_lnk(repo.repo_name, repo.repo_type,
repo.private, repo.fork),
"last_change": last_change(repo.last_db_change),
"desc": desc(repo.description),
"owner": h.person(repo.user.username),
"rss": rss_lnk(repo.repo_name),
"atom": atom_lnk(repo.repo_name),
})
c.data = json.dumps({
"totalRecords": total_records,
"startIndex": 0,
"sort": "name",
"dir": "asc",
"records": repos_data
})
return render('/index.html')
def is_unused_user_attribute(attrname, value, lowercase=False):
attr = getattr(User, attrname)
query = DBSession.query(User)
if lowercase:
query = query.filter(func.lower(attr) == value.lower())
else:
query = query.filter(attr == value)
return query.count() == 0
def inner():
r = (
self.db.query(Ban)
.filter(func.lower(Ban.banmask) == mask.lower())
.filter(Ban.channel == channel)
.delete(synchronize_session="fetch")
)
self.db.commit()
return r > 0
def find(cls, abbr):
session = Session()
try:
t = session.query(NHLTeam).filter(func.lower(NHLTeam.abbr) == abbr.lower()).one()
except:
t = None
finally:
session.close()
return t
