def test_complex_using_tuples(self, session):
query = session.query(Bar)
filters = ({
'and': (
{
'or': (
{
'field': 'id',
'op': '==',
'value': 2
},
{
'field': 'id',
'op': '==',
'value': 3
},
)
},
{
'not': ({
'field': 'name',
'op': '==',
'value': 'name_2'
}, )
},
),
}, )
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].id == 3
def test_no_filters_provided(self, session):
query = session.query(Bar)
filters = []
filtered_query = apply_filters(query, filters)
assert query == filtered_query
def test_or_with_three_args(self, session):
query = session.query(Bar)
filters = [
{
'or': [
{
'field': 'id',
'op': '==',
'value': 1
},
{
'field': 'id',
'op': '==',
'value': 3
},
{
'field': 'id',
'op': '==',
'value': 4
},
]
},
]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 3
assert result[0].id == 1
assert result[1].id == 3
assert result[2].id == 4
def job_list_json_route():
"""list jobs, data endpoint"""
columns = [
ColumnDT(Job.id, mData='id'),
ColumnDT(Queue.id, mData='queue_id'),
ColumnDT(Queue.name, mData='queue_name'),
ColumnDT(Job.assignment, mData='assignment'),
ColumnDT(Job.retval, mData='retval'),
ColumnDT(Job.time_start, mData='time_start'),
ColumnDT(Job.time_end, mData='time_end'),
ColumnDT((Job.time_end - Job.time_start), mData='time_taken'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Job).outerjoin(Queue)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
jobs = DataTables(request.values.to_dict(), query, columns).output_result()
return Response(json.dumps(jobs, cls=SnerJSONEncoder),
mimetype='application/json')
def test_multiple_models(self, session):
query = session.query(Bar, Qux)
filters = [
{
'model': 'Bar',
'field': 'name',
'op': '==',
'value': 'name_1'
},
{
'model': 'Qux',
'field': 'name',
'op': '==',
'value': 'name_1'
},
]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 4
bars, quxs = zip(*result)
assert set(map(type, bars)) == {Bar}
assert {bar.id for bar in bars} == {1, 3}
assert {bar.name for bar in bars} == {"name_1"}
assert set(map(type, quxs)) == {Qux}
assert {qux.id for qux in quxs} == {1, 3}
assert {qux.name for qux in quxs} == {"name_1"}
def test_filter_by_hybrid_methods(self, session):
query = session.query(Bar, Qux)
filters = [
{
'model': 'Bar',
'field': 'three_times_count',
'op': '==',
'value': 30
},
{
'model': 'Qux',
'field': 'three_times_count',
'op': '>=',
'value': 31
},
]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
bars, quxs = zip(*result)
assert set(map(type, bars)) == {Bar}
assert {bar.id for bar in bars} == {2}
assert {bar.three_times_count() for bar in bars} == {30}
assert set(map(type, quxs)) == {Qux}
assert {qux.id for qux in quxs} == {4}
assert {qux.three_times_count() for qux in quxs} == {45}
def note_list_json_route():
"""list notes, data endpoint"""
columns = [
ColumnDT(Note.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
ColumnDT(Note.xtype, mData='xtype'),
ColumnDT(Note.data, mData='data'),
ColumnDT(Note.tags, mData='tags'),
ColumnDT(Note.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Note).outerjoin(
Host, Note.host_id == Host.id).outerjoin(Service,
Note.service_id == Service.id)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
notes = DataTables(request.values.to_dict(), query,
columns).output_result()
return jsonify(notes)
def test_filter_by_hybrid_properties(self, session):
query = session.query(Bar, Qux)
filters = [
{
'model': 'Bar',
'field': 'count_square',
'op': '==',
'value': 100
},
{
'model': 'Qux',
'field': 'count_square',
'op': '>=',
'value': 26
},
]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 2
bars, quxs = zip(*result)
assert set(map(type, bars)) == {Bar}
assert {bar.id for bar in bars} == {2}
assert {bar.count_square for bar in bars} == {100}
assert set(map(type, quxs)) == {Qux}
assert {qux.id for qux in quxs} == {2, 4}
assert {qux.count_square for qux in quxs} == {100, 225}
def test_orm_descriptors_not_valid_hybrid_attributes(
self, session, field, expected_error
):
query = session.query(Bar)
filters = [
{
'model': 'Bar',
'field': field,
'op': '==',
'value': 100
}
]
with pytest.raises(FieldNotFound) as exc:
apply_filters(query, filters)
assert expected_error in str(exc)
def _vnf_package_list_by_filters(context, read_deleted=None, filters=None):
query = api.model_query(context,
models.VnfPackage,
read_deleted=read_deleted,
project_only=True).options(joinedload('_metadata'))
if filters:
# Need to join VnfDeploymentFlavour, VnfSoftwareImage and
# VnfSoftwareImageMetadata db table explicitly
# only when filters contains one of the column matching
# from VnfSoftwareImage or VnfSoftwareImageMetadata db table.
filter_data = json.dumps(filters)
if 'VnfSoftwareImageMetadata' in filter_data:
query = query.join(models.VnfDeploymentFlavour).join(
models.VnfSoftwareImage).join(models.VnfSoftwareImageMetadata)
elif 'VnfSoftwareImage' in filter_data:
query = query.join(models.VnfDeploymentFlavour).join(
models.VnfSoftwareImage)
if 'VnfPackageArtifactInfo' in filter_data:
query = query.join(models.VnfPackageArtifactInfo)
query = apply_filters(query, filters)
return query.all()
def storage_service_list(**kwargs):
"""service listing; used to feed manymap queues from storage data"""
def get_host(svc, hostnames=False):
"""return address or hostname"""
if hostnames and svc.host.hostname:
return svc.host.hostname
return format_host_address(svc.host.address)
def get_data(svc):
"""return common data as dict"""
return {'proto': svc.proto, 'port': svc.port, 'name': svc.name, 'state': svc.state, 'info': json.dumps(svc.info)}
if kwargs['long'] and kwargs['short']:
current_app.logger.error('--short and --long are mutualy exclusive options')
sys.exit(1)
query = Service.query
if kwargs['filter']:
query = apply_filters(query, filter_parser.parse(kwargs['filter']), do_auto_join=False)
fmt = '{proto}://{host}:{port}'
if kwargs['short']:
fmt = '{host}'
elif kwargs['long']:
fmt = '{proto}://{host}:{port} {name} {state} {info}'
for tmp in query.all():
print(fmt.format(**get_data(tmp), host=get_host(tmp, kwargs['hostnames'])))
def test_and_with_three_args(self, session):
query = session.query(Bar)
filters = [
{
'and': [
{
'field': 'id',
'op': '<=',
'value': 3
},
{
'field': 'name',
'op': '==',
'value': 'name_1'
},
{
'field': 'count',
'op': 'is_not_null'
},
]
},
]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].id == 1
def filter_query(self):
print("\n\nFILTROS EN PAGINACION: \n", self.__data)
if len(self.__data['filter']) != 0:
self.__query = apply_filters(self.__query, self.__data['filter'])
return self.__query
else:
return self.__query
def test_complex(self, session):
query = session.query(Bar)
filters = [{
'and': [
{
'or': [
{
'field': 'id',
'op': '==',
'value': 2
},
{
'field': 'id',
'op': '==',
'value': 3
},
]
},
{
'not': [{
'field': 'name',
'op': '==',
'value': 'name_2'
}]
},
],
}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].id == 3
def match(*, db_session, filter_spec: List[dict], class_instance: Base):
"""Matches a class instance with a given search filter."""
table_name = get_table_name_by_class_instance(class_instance)
model_cls = get_class_by_tablename(table_name)
query = db_session.query(model_cls)
query = apply_filters(query, filter_spec)
return query.filter(model_cls.id == class_instance.id).one_or_none()
def search_filter_sort_paginate(
db_session,
model,
query_str: str = None,
page: int = 1,
items_per_page: int = 5,
sort_by: List[str] = None,
descending: List[bool] = None,
fields: List[str] = None,
ops: List[str] = None,
values: List[str] = None,
):
"""Common functionality for searching, filtering and sorting"""
if query_str:
query = search(db_session=db_session, query_str=query_str, model=model)
else:
query = get_all(db_session=db_session, model=model)
filter_spec = create_filter_spec(model, fields, ops, values)
query = apply_filters(query, filter_spec)
sort_spec = create_sort_spec(model, sort_by, descending)
query = apply_sort(query, sort_spec)
query, pagination = apply_pagination(query,
page_number=page,
page_size=items_per_page)
return {
"items": query.all(),
"itemsPerPage": pagination.page_size,
"page": pagination.page_number,
"total": pagination.total_results,
}
def test_filter_field_with_no_null_values(self, session):
query = session.query(Bar)
filters = [{'field': 'name', 'op': 'is_null'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 0
def test_field_not_in_value_list(self, session):
query = session.query(Bar)
filters = [{'field': 'count', 'op': 'in', 'value': [1, 2, 3]}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 0
def test_null_datetime(self, session):
query = session.query(Qux)
filters = [{'field': 'execution_time', 'op': 'is_null'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].execution_time is None
def test_one_filter_applied_to_a_single_model(self, session, operator):
query = session.query(Bar)
filters = [{'field': 'count', 'op': operator, 'value': '7'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].id == 1
def test_filter_date_equality(self, session, value):
query = session.query(Qux)
filters = [{'field': 'created_at', 'op': '==', 'value': value}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].created_at == datetime.date(2016, 7, 14)
def filters(self):
filter_spec = self.__get_uri_query()
query = self.db_session.query(self.model)
try:
filtered_query = apply_filters(query, filter_spec)
except Exception as e:
raise HTTPResponse(status=200, body=e.args)
result = Paginate(filtered_query, self.serializer)
return result
def test_apply_filter_on_aggregate_query(self, session):
query = session.query(func.count(Bar.id))
filters = [{'field': 'name', 'op': '==', 'value': 'name_1'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0] == (2,)
def test_null_date(self, session):
query = session.query(Qux)
filters = [{'field': 'created_at', 'op': 'is_null'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 1
assert result[0].created_at is None
def vuln_export(qfilter=None):
"""export all vulns in storage without aggregation"""
host_address_format = case([(func.family(
Host.address) == 6, func.concat('[', func.host(Host.address), ']'))],
else_=func.host(Host.address))
host_ident = case([(func.char_length(Host.hostname) > 0, Host.hostname)],
else_=host_address_format)
endpoint_address = func.concat_ws(':', host_address_format, Service.port)
endpoint_hostname = func.concat_ws(':', host_ident, Service.port)
query = db.session \
.query(
host_ident.label('host_ident'),
Vuln.name.label('vulnerability'),
Vuln.descr.label('description'),
Vuln.data,
func.text(Vuln.severity).label('severity'),
Vuln.tags,
endpoint_address.label('endpoint_address'),
endpoint_hostname.label('endpoint_hostname'),
Vuln.refs.label('references')
) \
.outerjoin(Host, Vuln.host_id == Host.id) \
.outerjoin(Service, Vuln.service_id == Service.id)
if qfilter:
query = apply_filters(query,
FILTER_PARSER.parse(qfilter),
do_auto_join=False)
content_trimmed = False
fieldnames = [
'id', 'host_ident', 'vulnerability', 'severity', 'description', 'data',
'tags', 'endpoint_address', 'endpoint_hostname', 'references'
]
output_buffer = StringIO()
output = DictWriter(output_buffer,
fieldnames,
restval='',
quoting=QUOTE_ALL)
output.writeheader()
for row in query.all():
rdata = row._asdict()
rdata['tags'] = list_to_lines(rdata['tags'])
rdata['references'] = list_to_lines(
map(url_for_ref, rdata['references']))
rdata, trim_trigger = trim_rdata(rdata)
content_trimmed |= trim_trigger
output.writerow(rdata)
if content_trimmed:
output.writerow({'host_ident': 'WARNING: some cells were trimmed'})
return output_buffer.getvalue()
def test_no_operator_provided(self, session):
query = session.query(Bar)
filters = [{'field': 'name', 'value': 'name_1'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 2
assert result[0].id == 1
assert result[1].id == 3
def filter(self,
ClassName,
column,
value,
operation='=='
): # operation может быть не только '==', но и '<', '>'
query = self.db.session.query(self.tables[ClassName])
filter_spec = [{'field': column, 'op': operation, 'value': value}]
result = apply_filters(query, filter_spec).all()
return result
def test_field_contains_value(self, session):
query = session.query(Bar)
filters = [{'field': 'name', 'op': 'contains', 'value': '_1'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 2
assert result[0].id == 1
assert result[1].id == 3
def test_one_filter_applied_to_a_single_model(self, session):
query = session.query(Bar)
filters = [{'field': 'name', 'op': 'ilike', 'value': '%ME_1'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 2
assert result[0].id == 1
assert result[1].id == 3
def test_apply_filter_on_single_field_query(self, session):
query = session.query(Bar.id)
filters = [{'field': 'name', 'op': '==', 'value': 'name_1'}]
filtered_query = apply_filters(query, filters)
result = filtered_query.all()
assert len(result) == 2
assert result[0] == (1,)
assert result[1] == (3,)
