def get_my_boards_filter(cls,
request: Request,
query: Select,
value: bool = False) -> Select:
if value:
query = query.where(cls.board_alias.c.author_id == request.user.id)
else:
query = query.where(cls.board_alias.c.author_id != request.user.id)
return query
def _apply_ip_or_cidr_filter(
self, stmt: Select, ip_or_cidr: str
) -> Select:
"""Apply an appropriate filter for an IP or CIDR block.
Notes
-----
If there is ever a need to support a database that does not have
native CIDR membership queries, fallback code (probably using a LIKE
expression) will need to be added here.
"""
if "/" in ip_or_cidr:
return stmt.where(text(":c >> ip_address")).params(c=ip_or_cidr)
else:
return stmt.where(TokenChangeHistory.ip_address == str(ip_or_cidr))
def get_search_clause(self, table: sa.Table, query: Select, search: str,
search_fields: Sequence[str]) -> Select:
if not search:
return query
columns = [getattr(table.c, col) for col in search_fields]
return query.where(or_(*(col.ilike(f"%{search}%") for col in columns)))
def _apply_cursor(stmt: Select, cursor: HistoryCursor) -> Select:
"""Apply a cursor to a query."""
time = datetime_to_db(cursor.time)
if cursor.previous:
return stmt.where(
or_(
TokenChangeHistory.event_time > time,
and_(
TokenChangeHistory.event_time == time,
TokenChangeHistory.id > cursor.id,
),
)
)
else:
return stmt.where(
or_(
TokenChangeHistory.event_time < time,
and_(
TokenChangeHistory.event_time == time,
TokenChangeHistory.id <= cursor.id,
),
)
)
async def filter_queryset(self, queryset: Select) -> Select:
validated_data = self.request['validated_data']
group_name = validated_data['group']
version = validated_data.get('version')
filters = [
switches.c.groups.contains(f'{{{group_name}}}'),
switches.c.is_active == true(),
switches.c.is_hidden == false(),
]
if version is not None:
filters.append(switches.c.version <= version)
return queryset.where(and_(*filters))
def paginate(
query: Select,
columns: Dict[Column, Any],
order: Order,
limit: int,
) -> Select:
orderer = get_orderer(order)
comparator = get_comparator(order)
for column, value in columns.items():
query = query.order_by(orderer(column))
if value is not None:
query = query.where(comparator(column, value))
return query.limit(limit)
def _apply_text_filter(query: Select,
args: dict,
column: Column) -> Select:
"""
If a value has been specified by the ['search']['value'] parameter from
a DataTable AJAX request, this adds "column LIKE %['search']['value']%"
to the WHERE clause of the given query.
:param query: the DataTable SQL query
:param args: the query parameters from a DataTable AJAX request
:param column: the column to be filtered
:return: the query with the filter (if it exists) applied
"""
search_value = args['search']['value']
if search_value:
query = query.where(
column.like('%{}%'.format(search_value))
)
return query
def augment_query(
cls,
ctx: HuskyQueryContext,
query: Select,
taxon_model_info_map: Dict[str, TaxonModelInfo],
filter_clause: Optional[FilterClause],
) -> Select:
"""
Adds filters to the query
:param ctx: Husky query runtime
:param query: Original query
:param taxon_model_info_map: Map of taxon slug expression to taxon model info
:param filter_clause: Filter clauses
:return: New query with all modifiers applied
"""
if filter_clause:
new_q = query.where(filter_clause.generate(ctx, query, taxon_model_info_map))
return new_q
else:
return query
def filter_user_uuid(cls, request: Request, query: Select, value: str):
return query.where(cls.user_alias.c.uuid == value)
def filter_by_role(cls, request: Request, query: Select, value: str):
return query.where(cls.user_in_board_alias.c.role == value)
def filter_group(self, queryset: Select, group_name: str) -> Select:
return queryset.where(switches.c.groups.any(group_name))
def filter_hidden(self, qs: Select, request_params: Dict[str,
Any]) -> Select:
if not request_params.get('show_hidden'):
qs = qs.where(switches.c.is_hidden == false())
return qs
async def get_paginator(request: Request, query: Select, columns):
"""columns: column, sort order, from col to url, from url to col, url pattern"""
assert len(columns) > 0
need_reverse = False
pattern = '_'.join([column[4] for column in columns])
if 'after' in request.raw_args and re.match(f"^{pattern}$",
request.raw_args['after']):
cols_vals = request.raw_args['after'].split('_')
col, order, to_url, from_url, _ = columns[0]
order_by = [getattr(col, order)()]
if len(columns) == 1:
if order == 'desc':
condition = col <= from_url(cols_vals[0])
else:
condition = col >= from_url(cols_vals[0])
else:
col2, order2, to_url2, from_url2, _ = columns[1]
order_by.append(getattr(col2, order2)())
if order2 == 'desc':
condition = (col < from_url(cols_vals[0])) \
| (col == from_url(cols_vals[0])) & (col2 <= from_url2(cols_vals[1]))
else:
condition = (col > from_url(cols_vals[0])) \
| (col == from_url(cols_vals[0])) & (col2 >= from_url2(cols_vals[1]))
query = query.where(condition).order_by(*order_by)
limit = PAGE_SIZE + 2
slice_from = 1
elif 'before' in request.raw_args and re.match(f"^{pattern}$",
request.raw_args['before']):
cols_vals = request.raw_args['before'].split('_')
col, order, to_url, from_url, _ = columns[0]
order_by = [col.asc() if order == 'desc' else col.desc()]
if len(columns) == 1:
if order == 'desc':
condition = col >= from_url(cols_vals[0])
else:
condition = col <= from_url(cols_vals[0])
else:
col2, order2, to_url2, from_url2, _ = columns[1]
order_by.append(col2.asc() if order2 == 'desc' else col2.desc())
if order2 == 'desc':
condition = (col > from_url(cols_vals[0])) \
| (col == from_url(cols_vals[0])) & (col2 >= from_url2(cols_vals[1]))
else:
condition = (col < from_url(cols_vals[0])) \
| (col == from_url(cols_vals[0])) & (col2 <= from_url2(cols_vals[1]))
query = query.where(condition).order_by(*order_by)
limit = PAGE_SIZE + 2
slice_from = 1
need_reverse = True
else:
col, order, _, _, _ = columns[0]
order_by = [getattr(col, order)()]
if len(columns) > 1:
col2, order2, _, _, _ = columns[1]
order_by.append(getattr(col2, order2)())
query = query.order_by(*order_by)
limit = PAGE_SIZE + 1
slice_from = 0
query = query.limit(limit)
all_items = await query.gino.all()
items = all_items[slice_from:PAGE_SIZE + slice_from]
after = before = None
if need_reverse:
items = list(reversed(items))
after = True
if len(all_items) == limit:
before = True
else:
if slice_from == 1:
before = True
if len(all_items) == limit:
after = True
if after:
after = '_'.join(
[col[2](getattr(items[-1], col[0].name)) for col in columns])
after = url_without_qs_param(request.url, 'before', {'after': after})
if before:
before = '_'.join(
[col[2](getattr(items[0], col[0].name)) for col in columns])
before = url_without_qs_param(request.url, 'after', {'before': before})
return Paginator(items=items, after=after, before=before)
def filter_version(self, queryset: Select, version: Optional[str] = None) -> Select:
if version is not None:
queryset = queryset.where(switches.c.version <= version)
return queryset
def filter_hidden(self, queryset: Select) -> Select:
return queryset.where(switches.c.is_hidden == false())
def filter_group(self, qs: Select, request_params: Dict[str,
Any]) -> Select:
group = request_params.get('group')
if group:
qs = qs.where(switches.c.groups.any(group))
return qs
def filter_active(self, queryset: Select, is_active: bool = True) -> Select:
return queryset.where(switches.c.is_active.is_(is_active))
