def testQueryIssueSnapshots_Status(self): """Test a burndown query from a regular user grouping by open status.""" project = fake.Project(project_id=789) perms = permissions.PermissionSet(['BarPerm']) search_helpers.GetPersonalAtRiskLabelIDs(self.cnxn, None, self.config_service, [10, 20], project, perms).AndReturn([91, 81]) cols = [ 'Stats.status', 'COUNT(IssueSnapshot.issue_id)', ] left_joins = self.defaultLeftJoins + [ ('StatusDef AS Stats ON ' \ 'Stats.id = IssueSnapshot.status_id', []) ] where = self.defaultWheres group_by = ['Stats.status'] stmt, stmt_args = self.services.chart._BuildSnapshotQuery(cols, where, left_joins, group_by, shard_id=0) self.services.chart._QueryToWhere(mox.IgnoreArg(), mox.IgnoreArg(), mox.IgnoreArg(), mox.IgnoreArg(), mox.IgnoreArg(), mox.IgnoreArg()).AndReturn( ([], [], [])) self.cnxn.Execute(stmt, stmt_args, shard_id=0).AndReturn([]) self._verifySQL(cols, left_joins, where, group_by) self.mox.ReplayAll() self.services.chart.QueryIssueSnapshots(self.cnxn, self.services, unixtime=1514764800, effective_ids=[10, 20], project=project, perms=perms, group_by='status') self.mox.VerifyAll()
def QueryIssueSnapshots(self, cnxn, services, unixtime, effective_ids, project, perms, group_by=None, label_prefix=None, query=None, canned_query=None): """Queries historical issue counts grouped by label or component. Args: cnxn: A MonorailConnection instance. services: A Services instance. unixtime: An integer representing the Unix time in seconds. effective_ids: The effective User IDs associated with the current user. project: A project object representing the current project. perms: A permissions object associated with the current user. group_by (str, optional): Which dimension to group by. Values can be 'label', 'component', or None, in which case no grouping will be applied. label_prefix: Required when group_by is 'label.' Will limit the query to only labels with the specified prefix (for example 'Pri'). query (str, optional): A query string from the request to apply to the snapshot query. canned_query (str, optional): Parsed canned query applied to the query scope. Returns: 1. A dict of {'2nd dimension or "total"': number of occurences}. 2. A list of any unsupported query conditions in query. 3. A boolean that is true if any results were capped. """ project_config = services.config.GetProjectConfig( cnxn, project.project_id) try: query_left_joins, query_where, unsupported_conds = self._QueryToWhere( cnxn, services, project_config, query, canned_query, project) except ast2select.NoPossibleResults: return {}, ['Invalid query.'], False restricted_label_ids = search_helpers.GetPersonalAtRiskLabelIDs( cnxn, None, self.config_service, effective_ids, project, perms) left_joins = [ ('Issue ON IssueSnapshot.issue_id = Issue.id', []), ] if restricted_label_ids: left_joins.append((('Issue2Label AS Forbidden_label' ' ON Issue.id = Forbidden_label.issue_id' ' AND Forbidden_label.label_id IN (%s)' % (sql.PlaceHolders(restricted_label_ids))), restricted_label_ids)) if effective_ids: left_joins.append( ('Issue2Cc AS I2cc' ' ON Issue.id = I2cc.issue_id' ' AND I2cc.cc_id IN (%s)' % sql.PlaceHolders(effective_ids), effective_ids)) # TODO(jeffcarp): Handle case where there are issues with no labels. where = [ ('IssueSnapshot.period_start <= %s', [unixtime]), ('IssueSnapshot.period_end > %s', [unixtime]), ('IssueSnapshot.project_id = %s', [project.project_id]), ('Issue.is_spam = %s', [False]), ('Issue.deleted = %s', [False]), ] forbidden_label_clause = 'Forbidden_label.label_id IS NULL' if effective_ids: if restricted_label_ids: forbidden_label_clause = ' OR %s' % forbidden_label_clause else: forbidden_label_clause = '' where.append( (('(Issue.reporter_id IN (%s)' ' OR Issue.owner_id IN (%s)' ' OR I2cc.cc_id IS NOT NULL' '%s)') % (sql.PlaceHolders(effective_ids), sql.PlaceHolders(effective_ids), forbidden_label_clause), list(effective_ids) + list(effective_ids))) else: where.append((forbidden_label_clause, [])) if group_by == 'component': cols = ['Comp.path', 'IssueSnapshot.issue_id'] left_joins.extend([ (('IssueSnapshot2Component AS Is2c ON' ' Is2c.issuesnapshot_id = IssueSnapshot.id'), []), ('ComponentDef AS Comp ON Comp.id = Is2c.component_id', []), ]) group_by = ['Comp.path'] elif group_by == 'label': cols = ['Lab.label', 'IssueSnapshot.issue_id'] left_joins.extend([ (('IssueSnapshot2Label AS Is2l' ' ON Is2l.issuesnapshot_id = IssueSnapshot.id'), []), ('LabelDef AS Lab ON Lab.id = Is2l.label_id', []), ]) if not label_prefix: raise ValueError( '`label_prefix` required when grouping by label.') # TODO(jeffcarp): If LookupIDsOfLabelsMatching() is called on output, # ensure regex is case-insensitive. where.append( ('LOWER(Lab.label) LIKE %s', [label_prefix.lower() + '-%'])) group_by = ['Lab.label'] elif group_by == 'open': cols = ['IssueSnapshot.is_open', 'IssueSnapshot.issue_id'] group_by = ['IssueSnapshot.is_open'] elif group_by == 'status': left_joins.append(('StatusDef AS Stats ON ' \ 'Stats.id = IssueSnapshot.status_id', [])) cols = ['Stats.status', 'IssueSnapshot.issue_id'] group_by = ['Stats.status'] elif group_by == 'owner': cols = ['IssueSnapshot.owner_id', 'IssueSnapshot.issue_id'] group_by = ['IssueSnapshot.owner_id'] elif not group_by: cols = ['IssueSnapshot.issue_id'] else: raise ValueError('`group_by` must be label, component, ' \ 'open, status, owner or None.') if query_left_joins: left_joins.extend(query_left_joins) if query_where: where.extend(query_where) promises = [] for shard_id in range(settings.num_logical_shards): count_stmt, stmt_args = self._BuildSnapshotQuery(cols=cols, where=where, joins=left_joins, group_by=group_by, shard_id=shard_id) promises.append( framework_helpers.Promise(cnxn.Execute, count_stmt, stmt_args, shard_id=shard_id)) shard_values_dict = {} search_limit_reached = False for promise in promises: # Wait for each query to complete and add it to the dict. shard_values = list(promise.WaitAndGetValue()) if not shard_values: continue if group_by: for name, count in shard_values: if count >= settings.chart_query_max_rows: search_limit_reached = True shard_values_dict.setdefault(name, 0) shard_values_dict[name] += count else: if shard_values[0][0] >= settings.chart_query_max_rows: search_limit_reached = True shard_values_dict.setdefault('total', 0) shard_values_dict['total'] += shard_values[0][0] unsupported_field_names = list( set([ field.field_name for cond in unsupported_conds for field in cond.field_defs ])) return shard_values_dict, unsupported_field_names, search_limit_reached