class ListQuery: def __init__(self, model, view): self.model = model if model not in (MarkSafe, MarkUnsafe, MarkUnknown): raise ValueError('Unknown mark model: %s' % type(model)) self.view = view self._objects = None self._version_joined = False self._links_subquery = None @cached_property def _order_direction(self): if 'order' not in self.view: return 'ASC' return 'DESC' if self.view['order'][0] == 'up' else 'ASC' def __join_version(self): version_model_map = { MarkSafe: MarkSafeHistory, MarkUnsafe: MarkUnsafeHistory, MarkUnknown: MarkUnknownHistory } version_model = version_model_map[self.model] if self._version_joined: return version_model self.sql.join('INNER', version_model, ('mark_id', 'id'), ('version', 'version')) self._version_joined = True return version_model def __join_links(self): if self._links_subquery is None: # Not joined yet sq_model = { MarkSafe: MarkSafeReport, MarkUnsafe: MarkUnsafeReport, MarkUnknown: MarkUnknownReport } self._links_subquery = RawQuery(sq_model[self.model]) self._links_subquery.select('mark_id') self._links_subquery.group_by('mark_id') self.sql.join('LEFT OUTER', self._links_subquery, 'mark_id', 'id') return self._links_subquery def __process_num_of_links(self): links_aggr = '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)' subquery = self.__join_links() subquery.aggregate('num_of_links', 'COUNT({0})', 'id') self.sql.aggregate('num_of_links', links_aggr, ('num_of_links', subquery)) if self.model == MarkUnsafe: subquery.aggregate('broken_links', 'COUNT({0}) FILTER (WHERE {1} IS NOT NULL)', 'id', 'error') self.sql.aggregate('broken_links', '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)', ('broken_links', subquery)) if 'order' in self.view and self.view['order'][1] == 'num_of_links': self.sql.order_by_aggregation(links_aggr, self._order_direction, ('num_of_links', subquery)) def __process_total_similarity(self): if self.model != MarkUnsafe: # Only unsafe marks have similarity return total_sim_aggr = '(CASE WHEN {0} IS NULL THEN 0.0 ELSE {0} END)' subquery = self.__join_links() subquery.aggregate( 'total_similarity', '(CASE WHEN COUNT({0}) = 0 THEN 0 ELSE (SUM({0})/COUNT({0})) END)', 'result') self.sql.aggregate('total_similarity', total_sim_aggr, ('total_similarity', subquery)) if 'order' in self.view and self.view['order'][1] == 'total_similarity': self.sql.order_by_aggregation(total_sim_aggr, self._order_direction, ('total_similarity', subquery)) def __process_component(self): if self.model != MarkUnknown: # Only unknown marks have component return self.sql.join('INNER', Component, 'id', 'component_id') self.sql.select(('name', Component, 'component')) # Filter by component if 'component' in self.view: if self.view['component'][0] == 'is': operation = '{0} = %s' args = [self.view['component'][1]] else: # startswith operation = 'UPPER({0}) LIKE UPPER(%s)' args = [self.view['component'][1] + '%'] self.sql.where(operation, ('name', Component), args_list=args) # Sort by component name if 'order' in self.view and self.view['order'][1] == 'component': self.sql.order_by(('name', Component), self._order_direction) def __process_pattern(self): if self.model != MarkUnknown: # Only unknown marks have pattern return self.sql.select(('problem_pattern', None, 'pattern')) def __process_verdict(self): if self.model == MarkUnknown: # Unknown marks don't have verdict return self.sql.select('verdict') # Filter if 'verdict' in self.view: operation = "{0} = %s" if self.view['verdict'][0] != 'is': operation = 'NOT (%s)' % operation self.sql.where(operation, 'verdict', args_list=[self.view['verdict'][1]]) def __process_tags(self): if self.model == MarkUnknown: # Unknown marks don't have tags return v_model = self.__join_version() sq_model = MarkSafeTag if self.model == MarkSafe else MarkUnsafeTag tags_model = SafeTag if self.model == MarkSafe else UnsafeTag subquery = RawQuery(sq_model) subquery.select('mark_version_id') subquery.join('INNER', tags_model, 'id', 'tag_id') subquery.aggregate('tags', 'ARRAY_AGG({0})', ('tag', tags_model)) subquery.group_by('mark_version_id') self.sql.join('LEFT OUTER', subquery, 'mark_version_id', 'id', table_to=v_model) self.sql.select(('tags', subquery)) if 'tags' in self.view: args = self.__get_tags(self.view['tags'][0]) operation = 'COUNT({0}) FILTER (WHERE {0} IN (%s))' % ','.join( ['%s'] * len(args)) subquery.aggregate('tags_filter_cnt', operation, 'tag_id', args_list=args) self.sql.where('{0} = %s', ('tags_filter_cnt', subquery), args_list=[len(args)]) def __process_status(self): self.sql.select('status') # Filter if 'status' in self.view: operation = "{0} = %s" if self.view['status'][0] != 'is': operation = 'NOT (%s)' % operation self.sql.where(operation, 'status', args_list=[self.view['status'][1]]) def __process_identifier(self): self.sql.select('identifier') if 'identifier' in self.view: self.sql.where("{0} = %s", 'identifier', args_list=[self.view['identifier'][0]]) def __process_author(self): self.sql.join('LEFT OUTER', User, 'id', 'author_id') self.sql.select('author_id', ('first_name', User), ('last_name', User)) if 'author' in self.view: self.sql.where('{0} = %s', 'author_id', args_list=[int(self.view['author'][0])]) def __process_change_date(self): self.sql.select('change_date') if 'change_date' in self.view: operation = '{0} ' + ('<' if self.view['change_date'][0] == 'older' else '>') + ' %s' # UTC+0 value = now() - timedelta(**{ self.view['change_date'][2]: int(self.view['change_date'][1]) }) self.sql.where(operation, 'change_date', args_list=[value]) if 'order' in self.view and self.view['order'][1] == 'change_date': self.sql.order_by('change_date', self._order_direction) def __process_format(self): self.sql.select('format') def __process_source(self): self.sql.select(('type', None, 'source')) # Filter by source type if 'source' in self.view: operation = "{0} = %s" if self.view['source'][0] != 'is': operation = 'NOT (%s)' % operation self.sql.where(operation, 'type', args_list=[self.view['source'][1]]) def __filter_by_attribute(self): mark_attrs_model = { MarkSafe: MarkSafeAttr, MarkUnsafe: MarkUnsafeAttr, MarkUnknown: MarkUnknownAttr } if 'attr' in self.view: v_model = self.__join_version() args = list( Attr.objects.filter( **{ 'name__name__iexact': self.view['attr'][0], 'value__' + self.view['attr'][1]: self.view['attr'][2] }).values_list('id', flat=True)) if len(args) == 0: raise EmptyQuery( 'There are no attributes found for filtering by it') operation = ( '{0} IN (%s)' % ', '.join(['%s'] * len(args))) if len(args) > 1 else '{0} = %s' subquery = RawQuery(mark_attrs_model[self.model]) subquery.select('mark_id') subquery.where(operation, 'attr_id', args_list=args) subquery.group_by('mark_id') self.sql.join('INNER', subquery, 'mark_id', 'id', table_to=v_model) if 'order' in self.view and self.view['order'][1] == 'attr': v_model = self.__join_version() subquery = RawQuery(mark_attrs_model[self.model]) subquery.join('INNER', Attr, 'id', 'attr_id') subquery.join('INNER', AttrName, 'id', 'name_id', table_to=Attr) subquery.where('{0} = %s', ('name', AttrName), args_list=[self.view['order'][2]]) subquery.group_by('mark_id', ('value', Attr)) subquery.select('mark_id', ('value', Attr, 'order_attr_value')) self.sql.join('LEFT OUTER', subquery, 'mark_id', 'id', table_to=v_model) self.sql.order_by(('order_attr_value', subquery), self._order_direction) def __get_tags(self, tags_string): tags_model = SafeTag if self.model == MarkSafe else UnsafeTag view_tags = set(x.strip() for x in tags_string.split(';')) if '' in view_tags: view_tags.remove('') tags_ids = list( tags_model.objects.filter(tag__in=view_tags).values_list( 'id', flat=True)) if len(tags_ids) != len(view_tags): raise EmptyQuery( "One of the tags wasn't found for filtering by it") return tags_ids def __generate_sql(self): self.sql = RawQuery(self.model) self.sql.select('id') # Default sorting by mark id (it can be overwritten later) self.sql.order_by('id', 'ASC') for col_name in self.view['columns']: method = '_{0}__process_{1}'.format(self.__class__.__name__, col_name) if hasattr(self, method): getattr(self, method)() self.__filter_by_attribute() def get_objects(self): if self._objects is None: try: self.__generate_sql() except EmptyQuery as e: logger.error(e) self._objects = [] else: self._objects = self.sql.execute() return self._objects
class LeavesQuery: def __init__(self, model, view, **kwargs): self.model = model if model not in (ReportSafe, ReportUnsafe, ReportUnknown): raise ValueError('Unknown leaf model: %s' % type(model)) self.view = view self.kwargs = kwargs self._objects = None @cached_property def _order_direction(self): if 'order' not in self.view: return 'ASC' return 'DESC' if self.view['order'][0] == 'up' else 'ASC' def __filter_by_report(self): if 'report' not in self.kwargs: return leaf_field = { ReportSafe: 'safe_id', ReportUnsafe: 'unsafe_id', ReportUnknown: 'unknown_id' } subquery = RawQuery(ReportComponentLeaf) subquery.select(leaf_field[self.model]) subquery.where('{0} = %s', 'report_id', args_list=[self.kwargs['report'].id]) subquery.group_by(leaf_field[self.model]) self.sql.join('INNER', subquery, (leaf_field[self.model], 'id'), table_to=Report) def __process_verdict(self): if self.model == ReportUnknown: # Unknowns don't have verdicts return # Add column if needed if 'report_verdict' in self.view['columns']: self.sql.select('verdict') operation = "{0} = %s" args = [] if self.kwargs.get('verdict') is not None: args.append(self.kwargs['verdict']) elif 'verdict' in self.view: v_num = len(self.view['verdict']) if v_num == 0: return if v_num == 1: args.append(self.view['verdict'][0]) else: args = self.view['verdict'] operation = "{0} IN (%s)" % ', '.join( ['%s'] * len(self.view['verdict'])) else: # There's not filters by verdict return self.sql.where(operation, 'verdict', args_list=args) def __get_ms(self, value, measure): if isinstance(value, str): value = float(value.replace(',', '.')) if measure == 's': return value * 1000 elif measure == 'm': return value * 60000 return value def __process_cpu_time(self): if 'verifiers:cpu' not in self.view['columns']: # We are not going to filter/sort by cpu time if it isn't shown return self.sql.select('cpu_time') # Filter by cpu time if 'parent_cpu' in self.view: operation = '{0} ' + ('<' if self.view['parent_cpu'][0] == 'lt' else '>') + ' %s' value = self.__get_ms(self.view['parent_cpu'][1], self.view['parent_cpu'][2]) self.sql.where(operation, 'cpu_time', args_list=[value]) # Order by cpu time if 'order' in self.view and self.view['order'][1] == 'parent_cpu': self.sql.order_by('cpu_time', self._order_direction) def __process_wall_time(self): if 'verifiers:wall' not in self.view['columns']: # We are not going to filter/sort by wall time if it isn't shown return self.sql.select('wall_time') # Filter by wall time if 'parent_wall' in self.view: operation = '{0} ' + ('<' if self.view['parent_wall'][0] == 'lt' else '>') + ' %s' value = self.__get_ms(self.view['parent_wall'][1], self.view['parent_wall'][2]) self.sql.where(operation, 'wall_time', args_list=[value]) # Order by wall time if 'order' in self.view and self.view['order'][1] == 'parent_wall': self.sql.order_by('wall_time', self._order_direction) def __process_memory(self): if 'verifiers:memory' not in self.view['columns']: # We are not going to filter/sort by memory if it isn't shown return self.sql.select('memory') # Filter by memory if 'parent_memory' in self.view: operation = '{0} ' + ('<' if self.view['parent_memory'][0] == 'lt' else '>') + ' %s' value = float(self.view['parent_memory'][1].replace(',', '.')) if self.view['parent_memory'][2] == 'KB': value *= 1024 elif self.view['parent_memory'][2] == 'MB': value *= 1024 * 1024 elif self.view['parent_memory'][2] == 'GB': value *= 1024**3 self.sql.where(operation, 'memory', args_list=[value]) # Order by memory if 'order' in self.view and self.view['order'][1] == 'parent_memory': self.sql.order_by('memory', self._order_direction) def __process_marks(self): if 'marks_number' not in self.view['columns']: # We are not going to fitler by number of (confirmed) marks if it isn't shown return # Is confirmed number shown? with_confirmed = 'hidden' not in self.view or 'confirmed_marks' not in self.view[ 'hidden'] sq_model = { ReportSafe: MarkSafeReport, ReportUnsafe: MarkUnsafeReport, ReportUnknown: MarkUnknownReport } # Subquery with marks numbers subquery = RawQuery(sq_model[self.model]) subquery.select('report_id') subquery.aggregate('marks_number', 'COUNT({0})', 'id') if with_confirmed: subquery.aggregate( 'confirmed', 'COUNT(CASE WHEN {0} = %s THEN 1 ELSE NULL END)', 'type', args_list=[ASSOCIATION_TYPE[1][0]]) subquery.group_by('report_id') self.sql.join('LEFT OUTER', subquery, 'report_id', 'id', table_to=Report) self.sql.aggregate('marks_number', '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)', ('marks_number', subquery)) if with_confirmed: self.sql.aggregate('confirmed', '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)', ('confirmed', subquery)) # Filter if 'marks_number' in self.view: operation = '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END) ' if self.view['marks_number'][1] == 'lte': operation += '<=' elif self.view['marks_number'][1] == 'gte': operation += '>=' else: # iexact operation += '=' operation += ' %s' if self.view['marks_number'][0] == 'confirmed': if not with_confirmed: # Don't filter by confirmed number if it is hidden (wasn't aggregated) return field_name = 'confirmed' else: field_name = 'marks_number' self.sql.where(operation, (field_name, subquery), args_list=[int(self.view['marks_number'][2])]) def __process_total_similarity(self): if 'total_similarity' not in self.view['columns']: # We are not going to order by total similarity if it isn't shown return sq_model = { ReportSafe: MarkSafeReport, ReportUnsafe: MarkUnsafeReport, ReportUnknown: MarkUnknownReport } # Subquery with marks numbers links_sq = RawQuery(sq_model[self.model]) links_sq.select('report_id') total_sim_aggr = '(CASE WHEN {0} IS NULL THEN 0.0 ELSE {0} END)' links_sq.aggregate( 'total_similarity', '(CASE WHEN COUNT({0}) = 0 THEN 0 ELSE (SUM({0})/COUNT({0})) END)', 'result') links_sq.group_by('report_id') self.sql.join('LEFT OUTER', links_sq, 'report_id', 'id', table_to=Report) self.sql.aggregate('total_similarity', total_sim_aggr, ('total_similarity', links_sq)) if 'order' in self.view and self.view['order'][1] == 'total_similarity': self.sql.order_by_aggregation(total_sim_aggr, self._order_direction, ('total_similarity', links_sq)) def __process_tags(self): if self.model == ReportUnknown: # Unknowns don't have tags return report_tags_model = { ReportSafe: SafeReportTag, ReportUnsafe: UnsafeReportTag } tags_model = {ReportSafe: SafeTag, ReportUnsafe: UnsafeTag} if 'tags' in self.view['columns']: subquery = RawQuery(report_tags_model[self.model]) subquery.select('report_id') subquery.join('INNER', tags_model[self.model], 'id', 'tag_id') subquery.aggregate('tags', 'ARRAY_AGG({0})', ('tag', tags_model[self.model])) subquery.group_by('report_id') self.sql.join('LEFT OUTER', subquery, 'report_id', 'id', table_to=Report) self.sql.select(('tags', subquery)) # Filter operation = '{0} = %s' if self.kwargs.get('tag') is not None: args = [self.kwargs['tag'].id] elif 'tags' in self.view: args = self.__get_tags(self.view['tags'][0]) if len(args) > 1: operation = '{0} IN (%s)' % ', '.join(['%s'] * len(args)) else: # There are no filters by tag(s) return subquery = RawQuery(report_tags_model[self.model]) subquery.select('report_id') subquery.where(operation, 'tag_id', args_list=args) subquery.group_by('report_id') self.sql.join('INNER', subquery, 'report_id', 'id', table_to=Report) def __process_attributes(self): # Attributes will be get after pagination to get its order for columns # Sorting by attribute value if 'order' in self.view and self.view['order'][1] == 'attr': subquery = RawQuery(ReportAttr) subquery.join('INNER', Attr, 'id', 'attr_id') subquery.join('INNER', AttrName, 'id', 'name_id', table_to=Attr) subquery.select('report_id', ('value', Attr, 'order')) subquery.where('{0} = %s', ('name', AttrName), args_list=[self.view['order'][2]]) self.sql.join('LEFT OUTER', subquery, 'report_id', 'id', table_to=Report) self.sql.order_by(('order', subquery), self._order_direction) # Filter by attribute(s) operation = '{0} = %s' if self.kwargs.get('attr') is not None: args = [self.kwargs['attr'].id] elif 'attr' in self.view: args = list( Attr.objects.filter( **{ 'name__name__iexact': self.view['attr'][0], 'value__' + self.view['attr'][1]: self.view['attr'][2] }).values_list('id', flat=True)) if len(args) == 0: raise EmptyQuery( 'There are no attributes found for filtering by it') if len(args) > 1: operation = '{0} IN (%s)' % ', '.join(['%s'] * len(args)) else: # Ther are no filters by attribute return subquery = RawQuery(ReportAttr) subquery.select('report_id') subquery.where(operation, 'attr_id', args_list=args) subquery.group_by('report_id') self.sql.join('INNER', subquery, 'report_id', 'id', table_to=Report) def __process_component(self): if self.model != ReportUnknown: return self.sql.join('INNER', Component, 'id', 'component_id') self.sql.select(('name', Component, 'component')) if self.kwargs.get('component') is not None: self.sql.where('{0} = %s', ('id', Component), args_list=[int(self.kwargs['component'])]) elif 'component' in self.view and self.view['component'][0] in { 'iexact', 'istartswith', 'icontains' }: if self.view['component'][0] == 'iexact': value = self.view['component'][1] operation = 'UPPER({0}) = UPPER(%s)' elif self.view['component'][0] == 'istartswith': value = '%' + self.view['component'][1] operation = 'UPPER({0}) LIKE UPPER(%s)' elif self.view['component'][0] == 'icontains': value = '%' + self.view['component'][1] + '%' operation = 'UPPER({0}) LIKE UPPER(%s)' else: # Unsupported operation return self.sql.where(operation, ('name', Component), args_list=[value]) def __process_problems(self): if self.model != ReportUnknown: return # Get problems list for each report if 'problems' in self.view['columns']: subquery = RawQuery(MarkUnknownReport) subquery.select('report_id') subquery.join('INNER', UnknownProblem, 'id', 'problem_id') subquery.aggregate('problems', 'ARRAY_AGG({0})', ('name', UnknownProblem)) subquery.group_by('report_id') self.sql.join('LEFT OUTER', subquery, 'report_id', 'id', table_to=Report) self.sql.select(('problems', subquery)) problem = self.kwargs.get('problem') if problem is None and 'problem' in self.view: try: problem = UnknownProblem.objects.get( name=self.view['problem'][0].strip()) except ObjectDoesNotExist: raise EmptyQuery( "Unknown problem wasn't found to filter by it") if isinstance(problem, UnknownProblem): subquery = RawQuery(MarkUnknownReport) subquery.select('report_id') subquery.where('{0} = %s', 'problem_id', args_list=[problem.id]) subquery.group_by('report_id') self.sql.join('INNER', subquery, 'report_id', 'id', table_to=Report) elif problem == 0: subquery = RawQuery(MarkUnknownReport) subquery.select('report_id') subquery.group_by('report_id') self.sql.join('LEFT OUTER', subquery, 'report_id', 'id', table_to=Report) # Exclude all reports with marks self.sql.where('{0} IS NULL', ('report_id', subquery)) def __filter_by_has_confirmed(self): if self.model != ReportUnknown and self.kwargs.get('confirmed', False): self.sql.where('{0} = %s', 'has_confirmed', args_list=[True]) def __get_tags(self, tags_string): tags_model = SafeTag if self.model == ReportSafe else UnsafeTag view_tags = set(x.strip() for x in tags_string.split(';')) if '' in view_tags: view_tags.remove('') tags_ids = list( tags_model.objects.filter(tag__in=view_tags).values_list( 'id', flat=True)) if len(tags_ids) != len(view_tags): raise EmptyQuery( "One of the tags wasn't found for filtering by it") return tags_ids def __generate_sql(self): self.sql = RawQuery(self.model) if self.model == ReportUnsafe: self.sql.select('trace_id') # Add fields 'id' and 'parent_id' from Report self.sql.join('INNER', Report, 'id', 'report_ptr_id') self.sql.select(('id', Report), ('parent_id', Report)) self.__filter_by_report() self.__process_verdict() self.__process_cpu_time() self.__process_wall_time() self.__process_memory() self.__process_marks() self.__process_total_similarity() self.__process_tags() self.__process_attributes() self.__process_component() self.__process_problems() self.__filter_by_has_confirmed() def get_objects(self): if self._objects is None: try: self.__generate_sql() except EmptyQuery as e: logger.error(e) self._objects = [] else: self._objects = self.sql.execute() return self._objects