def not_empty_revisions(exp, object_class, target_class, query): """Filter revisions containing object state changes.""" if object_class is not all_models.Revision: raise BadQueryException("'not_empty_revisions' operator works with " "Revision only") resource_type = exp["resource_type"] resource_id = exp["resource_id"] resource_cls = getattr(all_models, resource_type, None) if resource_cls is None: raise BadQueryException( "'{}' resource type does not exist".format(resource_type)) query = all_models.Revision.query.filter( all_models.Revision.resource_type == resource_type, all_models.Revision.resource_id == resource_id, ).order_by(all_models.Revision.created_at, ) current_instance = resource_cls.query.get(resource_id) prev_diff = None revision_with_changes = [] for revision in query: diff = revdiff_builder.prepare(current_instance, revision.content) if diff != prev_diff: revision_with_changes.append(revision.id) prev_diff = diff if not revision_with_changes: return sqlalchemy.sql.false() return all_models.Revision.id.in_(revision_with_changes)
def not_empty_revisions(exp, object_class, target_class, query): """Filter revisions containing object state changes. This operator is useful if revisions with object state changes are needed. Revisions without object state changes are created when object editing without any actual changes is performed. """ if object_class is not revision.Revision: raise BadQueryException("'not_empty_revisions' operator works with " "Revision only") resource_type = exp["resource_type"] resource_id = exp["resource_id"] resource_cls = getattr(all_models, resource_type, None) if resource_cls is None: raise BadQueryException( "'{}' resource type does not exist".format(resource_type)) rev_q = db.session.query(revision.Revision.id, ).filter( revision.Revision.resource_type == resource_type, revision.Revision.resource_id == resource_id, sqlalchemy.not_(revision.Revision.is_empty), ).order_by(revision.Revision.created_at, ) result = {_id for _id, in rev_q} if not result: return sqlalchemy.sql.false() return object_class.id.in_(result)
def _get_limit(limit): """Get limit parameters for sqlalchemy.""" try: first, last = [int(i) for i in limit] except (ValueError, TypeError): raise BadQueryException("Invalid limit operator. Integers expected.") if first < 0 or last < 0: raise BadQueryException("Limit cannot contain negative numbers.") elif first >= last: raise BadQueryException("Limit start should be smaller than end.") else: page_size = last - first return page_size, first
def similar(exp, object_class, target_class, query): """Filter by relationships similarity. Note: only the first id from the list of ids is used. Args: object_name: the name of the class of the objects to which similarity will be computed. ids: the ids of similar objects of type `object_name`. Returns: sqlalchemy.sql.elements.BinaryExpression if an object of `object_class` is similar to one the given objects. """ similar_class = inflector.get_model(exp['object_name']) if not hasattr(similar_class, "get_similar_objects_query"): raise BadQueryException(u"{} does not define weights to count " u"relationships similarity" .format(similar_class.__name__)) similar_objects_query = similar_class.get_similar_objects_query( id_=exp['ids'][0], type_=object_class.__name__, ) similar_objects_ids = {obj[0] for obj in similar_objects_query} if similar_objects_ids: return object_class.id.in_(similar_objects_ids) return sqlalchemy.sql.false()
def related_evidence(exp, object_class, target_class, query): """Special Filter by relevant object used to display audit scope evidence returns list of evidence ids mapped to assessments for given audit. Evidence mapped to audit itself are ignored. """ if not exp["object_name"] == "Audit": raise BadQueryException("relevant_evidence operation " "works with object Audit only") ids = exp["ids"] evid_dest = db.session.query( all_models.Relationship.destination_id.label("id") ).join( all_models.Assessment, all_models.Assessment.id == all_models.Relationship.source_id ).filter( all_models.Relationship.destination_type == target_class.__name__, all_models.Relationship.source_type == all_models.Assessment.__name__, all_models.Assessment.audit_id.in_(ids) ) evid_source = db.session.query( all_models.Relationship.source_id.label("id") ).join( all_models.Assessment, all_models.Assessment.id == all_models.Relationship.destination_id ).filter( all_models.Relationship.source_type == target_class.__name__, all_models.Relationship.destination_type == all_models.Assessment.__name__, all_models.Assessment.audit_id.in_(ids) ) result = evid_dest.union(evid_source) return object_class.id.in_(result)
def unknown(exp, object_class, target_class, query): """A fake operator for invalid operator names.""" name = exp.get("op", {}).get("name") if name is None: msg = u"No operator name sent" else: msg = u"Unknown operator \"{}\"".format(name) raise BadQueryException(msg)
def decorated_operator(exp, *args, **kwargs): """Decorated operator """ operation_name = exp["op"]["name"] error_fields = required_fields_set - set(exp.keys()) if error_fields: raise BadQueryException("\n".join([ required_tmpl.format(field=field, operation=operation_name) for field in error_fields])) return operation(exp, *args, **kwargs)
def _clean_query(self, query): """ sanitize the query object """ for object_query in query: if "object_name" not in object_query: raise BadQueryException("`object_name` required for each object block") filters = object_query.get("filters", {}).get("expression") self._clean_filters(filters) self._macro_expand_object_query(object_query) return query
def not_empty_revisions(exp, object_class, target_class, query): """Filter revisions containing object state changes. This operator is useful if revisions with object state changes are needed. Revisions without object state changes are created when object editing without any actual changes is performed. """ if object_class is not all_models.Revision: raise BadQueryException("'not_empty_revisions' operator works with " "Revision only") resource_type = exp["resource_type"] resource_id = exp["resource_id"] resource_cls = getattr(all_models, resource_type, None) if resource_cls is None: raise BadQueryException( "'{}' resource type does not exist".format(resource_type)) query = all_models.Revision.query.filter( all_models.Revision.resource_type == resource_type, all_models.Revision.resource_id == resource_id, ).order_by(all_models.Revision.created_at, ) current_instance = resource_cls.query.get(resource_id) current_instance_meta = revisions_diff.meta_info.MetaInfo(current_instance) latest_rev_content = revisions_diff.builder.get_latest_revision_content( current_instance, ) prev_diff = None revision_with_changes = [] for revision in query: diff = revisions_diff.builder.prepare_content_diff( instance_meta_info=current_instance_meta, l_content=latest_rev_content, r_content=revision.content, ) if diff != prev_diff: revision_with_changes.append(revision.id) prev_diff = diff if not revision_with_changes: return sqlalchemy.sql.false() return all_models.Revision.id.in_(revision_with_changes)
def expand_task_dates(exp): """Parse task dates from the specified expression.""" if not isinstance(exp, dict) or "op" not in exp: return operator_name = exp["op"]["name"] if operator_name in ["AND", "OR"]: expand_task_dates(exp["left"]) expand_task_dates(exp["right"]) elif isinstance(exp["left"], (str, unicode)): key = exp["left"] if key in ["start", "end"]: parts = exp["right"].split("/") if len(parts) == 3: try: month, day, year = [int(part) for part in parts] except Exception: raise BadQueryException( "Date must consist of numbers") exp["left"] = key + "_date" exp["right"] = datetime.date(year, month, day) elif len(parts) == 2: month, day = parts exp["op"] = {"name": u"AND"} exp["left"] = { "op": { "name": operator_name }, "left": "relative_" + key + "_month", "right": month, } exp["right"] = { "op": { "name": operator_name }, "left": "relative_" + key + "_day", "right": day, } elif len(parts) == 1: exp["left"] = "relative_" + key + "_day" else: raise BadQueryException( u"Field {} should be a date of one of the" u" following forms: DD, MM/DD, MM/DD/YYYY".format( key))
def autocast(exp, target_class): """Try to guess the type of `value` and parse it from the string. Args: operator_name: the name of the operator being applied. value: the value being compared. """ operation = exp["op"]["name"] exp.update(EXP_TMPL) key = exp['left'] key = key.lower() key, _ = target_class.attributes_map().get(key, (key, None)) extra_parser, any_parser = get_parsers(target_class, key) if not extra_parser and not any_parser: # It's look like filter by snapshot return exp extra_exp = None current_exp = None if extra_parser: try: left_date, right_date = extra_parser.get_filter_value( unicode(exp['right']), operation) or [None, None] except ValueError: raise BadQueryException(extra_parser.get_value_error_msg()) if not left_date and not right_date and not any_parser: raise BadQueryException(extra_parser.get_value_error_msg()) if any(o in operation for o in ["~", "="]): operator_suffix = "=" else: operator_suffix = "" if "!" in operation: operator_suffix = "" connect_operator = "OR" else: connect_operator = "AND" left_exp = build_exp(key, left_date, ">" + operator_suffix) right_exp = build_exp(key, right_date, "<" + operator_suffix) extra_exp = (build_exp(left_exp, right_exp, connect_operator) or left_exp or right_exp) if any_parser: current_exp = exp return build_exp(extra_exp, current_exp, "OR") or current_exp or extra_exp
def parent_op(exp, object_class, target_class, query): """Filter by parents objects""" if not (exp["object_name"] == "Program" and object_class is all_models.Program): raise BadQueryException("parent operation " "works with object Program only") ids = exp["ids"] _parents_ids = set() for _id in ids: _parents_ids.update(object_class.get_relatives_ids(_id, "parents")) return object_class.id.in_(_parents_ids)
def child_op(exp, object_class, *_): """Filter by children objects""" if not (exp["object_name"] == "Program" and object_class is all_models.Program): raise BadQueryException("child operation " "works with object Program only") ids = exp["ids"] _children_ids = set() for _id in ids: _children_ids.update(object_class.get_relatives_ids(_id, "children")) return object_class.id.in_(_children_ids)
def build_expression(exp, object_class, target_class, query): """Make an SQLAlchemy filtering expression from exp expression tree.""" if not exp: # empty expression doesn't required filter return None if autocast.is_autocast_required_for(exp): exp = validate("left", "right")(autocast.autocast)(exp, target_class) if not exp: # empty expression after autocast is invalid and should raise an exception raise BadQueryException("Invalid filter data") operation = OPS.get(exp.get("op", {}).get("name")) or unknown return operation(exp, object_class, target_class, query)
def _apply_limit(query, limit): """Apply limits for pagination. Args: query: filter query; limit: a tuple of indexes in format (from, to); objects is sliced to objects[from, to]. Returns: matched objects ids and total count. """ try: first, last = limit first, last = int(first), int(last) except (ValueError, TypeError): raise BadQueryException( "Invalid limit operator. Integers expected.") if first < 0 or last < 0: raise BadQueryException("Limit cannot contain negative numbers.") elif first >= last: raise BadQueryException("Limit start should be smaller than end.") else: page_size = last - first with benchmark("Apply limit: _apply_limit > query_limit"): # Note: limit request syntax is limit:[0,10]. We are counting # offset from 0 as the offset of the initial row for sql is 0 (not 1). ids = [obj.id for obj in query.limit(page_size).offset(first)] with benchmark("Apply limit: _apply_limit > query_count"): if len(ids) < page_size: total = len(ids) + first else: # Note: using func.count() as query.count() is generating additional # subquery count_q = query.statement.with_only_columns( [sa.func.count()]) total = db.session.execute(count_q).scalar() return ids, total
def is_filter(exp, object_class, target_class, query): """Handle 'is' operator. As in 'CA is empty' expression """ if exp['right'] != u"empty": raise BadQueryException(u"Invalid operator near 'is': {}".format( exp['right'])) left = exp['left'].lower() left, _ = target_class.attributes_map().get(left, (left, None)) subquery = db.session.query(Record.key).filter( Record.type == object_class.__name__, Record.property == left, sqlalchemy.not_( sqlalchemy.or_(Record.content == u"", Record.content.is_(None))), ) return object_class.id.notin_(subquery)
def _clean_filters(self, expression): """Prepare the filter expression for building the query.""" if not expression or not isinstance(expression, dict): return slugs = expression.get("slugs") if slugs: ids = expression.get("ids", []) ids.extend(self._slugs_to_ids(expression["object_name"], slugs)) expression["ids"] = ids try: expression["ids"] = [int(id_) for id_ in expression.get("ids", [])] except ValueError as error: # catch missing relevant filter (undefined id) if expression.get("op", {}).get("name", "") == "relevant": raise BadQueryException(u"Invalid relevant filter for {}".format( expression.get("object_name", ""))) raise error self._clean_filters(expression.get("left")) self._clean_filters(expression.get("right"))
def cascade_unmappable(exp, object_class, target_class, query): """Special operator to get the effect of cascade unmap of Issue from Asmt.""" issue_id = exp["issue"].get("id") assessment_id = exp["assessment"].get("id") if not issue_id: raise BadQueryException("Missing 'id' key in 'issue': {}" .format(exp["issue"])) if not assessment_id: raise BadQueryException("Missing 'id' key in 'assessment': {}" .format(exp["assessment"])) if object_class.__name__ not in {"Audit", "Snapshot"}: raise BadQueryException("'cascade_unmapping' can't be applied to {}" .format(object_class.__name__)) mapped_to_issue = aliased(sqlalchemy.union_all( db.session.query( all_models.Relationship.destination_id.label("target_id"), ).filter( all_models.Relationship.source_id == issue_id, all_models.Relationship.source_type == "Issue", all_models.Relationship.destination_type == object_class.__name__, ~all_models.Relationship.automapping_id.is_(None), ), db.session.query( all_models.Relationship.source_id.label("target_id"), ).filter( all_models.Relationship.destination_id == issue_id, all_models.Relationship.destination_type == "Issue", all_models.Relationship.source_type == object_class.__name__, ), ), name="mapped_to_issue") mapped_to_assessment = aliased(sqlalchemy.union_all( db.session.query( all_models.Relationship.destination_id.label("target_id"), ).filter( all_models.Relationship.source_id == assessment_id, all_models.Relationship.source_type == "Assessment", all_models.Relationship.destination_type == object_class.__name__, ), db.session.query( all_models.Relationship.source_id.label("target_id"), ).filter( all_models.Relationship.destination_id == assessment_id, all_models.Relationship.destination_type == "Assessment", all_models.Relationship.source_type == object_class.__name__, ), ), "mapped_to_assessment") other_assessments = aliased(sqlalchemy.union_all( db.session.query( all_models.Relationship.destination_id.label("assessment_id"), ).filter( all_models.Relationship.source_id == issue_id, all_models.Relationship.source_type == "Issue", all_models.Relationship.destination_id != assessment_id, all_models.Relationship.destination_type == "Assessment", ), db.session.query( all_models.Relationship.source_id.label("assessment_id"), ).filter( all_models.Relationship.destination_id == issue_id, all_models.Relationship.destination_type == "Issue", all_models.Relationship.source_id != assessment_id, all_models.Relationship.source_type == "Assessment", ), ), "other_assessments") mapped_to_other_assessments = aliased(sqlalchemy.union_all( db.session.query( all_models.Relationship.destination_id.label("target_id"), ).filter( all_models.Relationship.source_id.in_(other_assessments), all_models.Relationship.source_type == "Assessment", all_models.Relationship.destination_type == object_class.__name__, ), db.session.query( all_models.Relationship.source_id.label("target_id"), ).filter( all_models.Relationship.destination_id != assessment_id, all_models.Relationship.destination_type == "Assessment", all_models.Relationship.source_type == object_class.__name__, ), ), "mapped_to_other_assessments") result = set(db.session.query(mapped_to_issue)) result &= set(db.session.query(mapped_to_assessment)) result -= set(db.session.query(mapped_to_other_assessments)) if not result: return sqlalchemy.sql.false() return object_class.id.in_([row[0] for row in result])
def joins_and_order(clause): """Get join operations and ordering field from item of order_by list. Args: clause: {"name": the name of model's field, "desc": reverse sort on this field if True} Returns: ([joins], order) - a tuple of joins required for this ordering to work and ordering clause itself; join is None if no join required or [(aliased entity, relationship field)] if joins required. """ def by_similarity(): """Join similar_objects subquery, order by weight from it.""" join_target = flask.g.similar_objects_query.subquery() join_condition = model.id == join_target.c.id joins = [(join_target, join_condition)] order = join_target.c.weight return joins, order def by_fulltext(): """Join fulltext index table, order by indexed CA value.""" alias = sa.orm.aliased(Record, name=u"fulltext_{}".format(self._count)) joins = [(alias, sa.and_(alias.key == model.id, alias.type == model.__name__, alias.property == key, alias.subproperty.in_(["", "__sort__"])))] order = alias.content return joins, order def by_foreign_key(): """Join the related model, order by title or name/email.""" related_model = attr.property.mapper.class_ if issubclass(related_model, models.mixins.Titled): joins = [(alias, _)] = [(sa.orm.aliased(attr), attr)] order = alias.title else: raise NotImplementedError( u"Sorting by {model.__name__} is " u"not implemented yet.".format(model=related_model)) return joins, order # transform clause["name"] into a model's field name key = clause["name"].lower() if key == "__similarity__": # special case if hasattr(flask.g, "similar_objects_query"): joins, order = by_similarity() else: raise BadQueryException( "Can't order by '__similarity__' when no " "'similar' filter was applied.") else: key, _ = tgt_class.attributes_map().get(key, (key, None)) if key in custom_operators.GETATTR_WHITELIST: attr = getattr(model, key.encode('utf-8'), None) if (isinstance(attr, sa.orm.attributes.InstrumentedAttribute) and isinstance( attr.property, sa.orm.properties.RelationshipProperty)): joins, order = by_foreign_key() else: # a simple attribute joins, order = None, attr else: # Snapshot or non object attributes are treated as custom attributes self._count += 1 joins, order = by_fulltext() if clause.get("desc", False): order = order.desc() return joins, order