Project import generated by Copybara.
GitOrigin-RevId: d9e9e3fb4e31372ec1fb43b178994ca78fa8fe70
diff --git a/services/chart_svc.py b/services/chart_svc.py
new file mode 100644
index 0000000..49ccb51
--- /dev/null
+++ b/services/chart_svc.py
@@ -0,0 +1,411 @@
+# Copyright 2018 The Chromium Authors. All rights reserved.
+# Use of this source code is governed by a BSD-style
+# license that can be found in the LICENSE file or at
+# https://developers.google.com/open-source/licenses/bsd
+
+"""A service for querying data for charts.
+
+Functions for querying the IssueSnapshot table and associated join tables.
+"""
+from __future__ import print_function
+from __future__ import division
+from __future__ import absolute_import
+
+import logging
+import settings
+import time
+
+from features import hotlist_helpers
+from framework import framework_helpers
+from framework import sql
+from search import search_helpers
+from tracker import tracker_bizobj
+from tracker import tracker_helpers
+from search import query2ast
+from search import ast2select
+from search import ast2ast
+
+
+ISSUESNAPSHOT_TABLE_NAME = 'IssueSnapshot'
+ISSUESNAPSHOT2CC_TABLE_NAME = 'IssueSnapshot2Cc'
+ISSUESNAPSHOT2COMPONENT_TABLE_NAME = 'IssueSnapshot2Component'
+ISSUESNAPSHOT2LABEL_TABLE_NAME = 'IssueSnapshot2Label'
+
+ISSUESNAPSHOT_COLS = ['id', 'issue_id', 'shard', 'project_id', 'local_id',
+ 'reporter_id', 'owner_id', 'status_id', 'period_start', 'period_end',
+ 'is_open']
+ISSUESNAPSHOT2CC_COLS = ['issuesnapshot_id', 'cc_id']
+ISSUESNAPSHOT2COMPONENT_COLS = ['issuesnapshot_id', 'component_id']
+ISSUESNAPSHOT2LABEL_COLS = ['issuesnapshot_id', 'label_id']
+
+
+class ChartService(object):
+ """Class for querying chart data."""
+
+ def __init__(self, config_service):
+ """Constructor for ChartService.
+
+ Args:
+ config_service (ConfigService): An instance of ConfigService.
+ """
+ self.config_service = config_service
+
+ # Set up SQL table objects.
+ self.issuesnapshot_tbl = sql.SQLTableManager(ISSUESNAPSHOT_TABLE_NAME)
+ self.issuesnapshot2cc_tbl = sql.SQLTableManager(
+ ISSUESNAPSHOT2CC_TABLE_NAME)
+ self.issuesnapshot2component_tbl = sql.SQLTableManager(
+ ISSUESNAPSHOT2COMPONENT_TABLE_NAME)
+ self.issuesnapshot2label_tbl = sql.SQLTableManager(
+ ISSUESNAPSHOT2LABEL_TABLE_NAME)
+
+ def QueryIssueSnapshots(self, cnxn, services, unixtime, effective_ids,
+ project, perms, group_by=None, label_prefix=None,
+ query=None, canned_query=None, hotlist=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.
+ hotlist (Hotlist, optional): Hotlist to search under (in lieu of project).
+
+ 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.
+ """
+ if hotlist:
+ # TODO(jeffcarp): Get project_ids in a more efficient manner. We can
+ # query for "SELECT DISTINCT(project_id)" for all issues in hotlist.
+ issues_list = services.issue.GetIssues(cnxn,
+ [hotlist_issue.issue_id for hotlist_issue in hotlist.items])
+ hotlist_issues_project_ids = hotlist_helpers.GetAllProjectsOfIssues(
+ [issue for issue in issues_list])
+ config_list = hotlist_helpers.GetAllConfigsOfProjects(
+ cnxn, hotlist_issues_project_ids, services)
+ project_config = tracker_bizobj.HarmonizeConfigs(config_list)
+ else:
+ project_config = services.config.GetProjectConfig(cnxn,
+ project.project_id)
+
+ if project:
+ project_ids = [project.project_id]
+ else:
+ project_ids = hotlist_issues_project_ids
+
+ try:
+ query_left_joins, query_where, unsupported_conds = self._QueryToWhere(
+ cnxn, services, project_config, query, canned_query, project_ids)
+ 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]),
+ ('Issue.is_spam = %s', [False]),
+ ('Issue.deleted = %s', [False]),
+ ]
+ if project_ids:
+ where.append(
+ ('IssueSnapshot.project_id IN (%s)' % sql.PlaceHolders(project_ids),
+ project_ids))
+
+ 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', 'COUNT(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', 'COUNT(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',
+ 'COUNT(IssueSnapshot.issue_id) AS issue_count']
+ group_by = ['IssueSnapshot.is_open']
+ elif group_by == 'status':
+ left_joins.append(('StatusDef AS Stats ON ' \
+ 'Stats.id = IssueSnapshot.status_id', []))
+ cols = ['Stats.status', 'COUNT(IssueSnapshot.issue_id)']
+ group_by = ['Stats.status']
+ elif group_by == 'owner':
+ cols = ['IssueSnapshot.owner_id', 'COUNT(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)
+
+ if hotlist:
+ left_joins.extend([
+ (('IssueSnapshot2Hotlist AS Is2h'
+ ' ON Is2h.issuesnapshot_id = IssueSnapshot.id'
+ ' AND Is2h.hotlist_id = %s'), [hotlist.hotlist_id]),
+ ])
+ where.append(
+ ('Is2h.hotlist_id = %s', [hotlist.hotlist_id]))
+
+ 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
+
+ def StoreIssueSnapshots(self, cnxn, issues, commit=True):
+ """Adds an IssueSnapshot and updates the previous one for each issue."""
+ for issue in issues:
+ right_now = self._currentTime()
+
+ # Update previous snapshot of current issue's end time to right now.
+ self.issuesnapshot_tbl.Update(cnxn,
+ delta={'period_end': right_now},
+ where=[('IssueSnapshot.issue_id = %s', [issue.issue_id]),
+ ('IssueSnapshot.period_end = %s',
+ [settings.maximum_snapshot_period_end])],
+ commit=commit)
+
+ config = self.config_service.GetProjectConfig(cnxn, issue.project_id)
+ period_end = settings.maximum_snapshot_period_end
+ is_open = tracker_helpers.MeansOpenInProject(
+ tracker_bizobj.GetStatus(issue), config)
+ shard = issue.issue_id % settings.num_logical_shards
+ status = tracker_bizobj.GetStatus(issue)
+ status_id = self.config_service.LookupStatusID(
+ cnxn, issue.project_id, status) or None
+ owner_id = tracker_bizobj.GetOwnerId(issue) or None
+
+ issuesnapshot_rows = [(issue.issue_id, shard, issue.project_id,
+ issue.local_id, issue.reporter_id, owner_id, status_id, right_now,
+ period_end, is_open)]
+
+ ids = self.issuesnapshot_tbl.InsertRows(
+ cnxn, ISSUESNAPSHOT_COLS[1:],
+ issuesnapshot_rows,
+ replace=True, commit=commit,
+ return_generated_ids=True)
+ issuesnapshot_id = ids[0]
+
+ # Add all labels to IssueSnapshot2Label.
+ label_rows = [
+ (issuesnapshot_id,
+ self.config_service.LookupLabelID(cnxn, issue.project_id, label))
+ for label in tracker_bizobj.GetLabels(issue)
+ ]
+ self.issuesnapshot2label_tbl.InsertRows(
+ cnxn, ISSUESNAPSHOT2LABEL_COLS,
+ label_rows, replace=True, commit=commit)
+
+ # Add all CCs to IssueSnapshot2Cc.
+ cc_rows = [
+ (issuesnapshot_id, cc_id)
+ for cc_id in tracker_bizobj.GetCcIds(issue)
+ ]
+ self.issuesnapshot2cc_tbl.InsertRows(
+ cnxn, ISSUESNAPSHOT2CC_COLS,
+ cc_rows,
+ replace=True, commit=commit)
+
+ # Add all components to IssueSnapshot2Component.
+ component_rows = [
+ (issuesnapshot_id, component_id)
+ for component_id in issue.component_ids
+ ]
+ self.issuesnapshot2component_tbl.InsertRows(
+ cnxn, ISSUESNAPSHOT2COMPONENT_COLS,
+ component_rows,
+ replace=True, commit=commit)
+
+ # Add all components to IssueSnapshot2Hotlist.
+ # This is raw SQL to obviate passing FeaturesService down through
+ # the call stack wherever this function is called.
+ # TODO(jrobbins): sort out dependencies between service classes.
+ cnxn.Execute('''
+ INSERT INTO IssueSnapshot2Hotlist (issuesnapshot_id, hotlist_id)
+ SELECT %s, hotlist_id FROM Hotlist2Issue WHERE issue_id = %s
+ ''', [issuesnapshot_id, issue.issue_id])
+
+ def ExpungeHotlistsFromIssueSnapshots(self, cnxn, hotlist_ids, commit=True):
+ """Expunge the existence of hotlists from issue snapshots.
+
+ Args:
+ cnxn: connection to SQL database.
+ hotlist_ids: list of hotlist_ids for hotlists we want to delete.
+ commit: set to False to skip the DB commit and do it in a caller.
+ """
+ vals_ph = sql.PlaceHolders(hotlist_ids)
+ cnxn.Execute(
+ 'DELETE FROM IssueSnapshot2Hotlist '
+ 'WHERE hotlist_id IN ({vals_ph})'.format(vals_ph=vals_ph),
+ hotlist_ids,
+ commit=commit)
+
+ def _currentTime(self):
+ """This is a separate method so it can be mocked by tests."""
+ return time.time()
+
+ def _QueryToWhere(self, cnxn, services, project_config, query, canned_query,
+ project_ids):
+ """Parses a query string into LEFT JOIN and WHERE conditions.
+
+ Args:
+ cnxn: A MonorailConnection instance.
+ services: A Services instance.
+ project_config: The configuration for the given project.
+ query (string): The query to parse.
+ canned_query (string): The supplied canned query.
+ project_ids: The current project ID(s).
+
+ Returns:
+ 1. A list of LEFT JOIN clauses for the SQL query.
+ 2. A list of WHERE clases for the SQL query.
+ 3. A list of query conditions that are unsupported with snapshots.
+ """
+ if not (query or canned_query):
+ return [], [], []
+
+ query = query or ''
+ scope = canned_query or ''
+
+ query_ast = query2ast.ParseUserQuery(query, scope,
+ query2ast.BUILTIN_ISSUE_FIELDS, project_config)
+ query_ast = ast2ast.PreprocessAST(cnxn, query_ast, project_ids,
+ services, project_config)
+ left_joins, where, unsupported = ast2select.BuildSQLQuery(query_ast,
+ snapshot_mode=True)
+
+ return left_joins, where, unsupported
+
+ def _BuildSnapshotQuery(self, cols, where, joins, group_by, shard_id):
+ """Given SQL arguments, executes a snapshot COUNT query."""
+ stmt = sql.Statement.MakeSelect('IssueSnapshot', cols, distinct=True)
+ stmt.AddJoinClauses(joins, left=True)
+ stmt.AddWhereTerms(where + [('IssueSnapshot.shard = %s', [shard_id])])
+ if group_by:
+ stmt.AddGroupByTerms(group_by)
+ stmt.SetLimitAndOffset(limit=settings.chart_query_max_rows, offset=0)
+ stmt_str, stmt_args = stmt.Generate()
+ if group_by:
+ if group_by[0] == 'IssueSnapshot.is_open':
+ count_stmt = ('SELECT IF(results.is_open = 1, "Opened", "Closed") ' \
+ 'AS bool_open, results.issue_count ' \
+ 'FROM (%s) AS results' % stmt_str)
+ else:
+ count_stmt = stmt_str
+ else:
+ count_stmt = 'SELECT COUNT(results.issue_id) FROM (%s) AS results' % (
+ stmt_str)
+ return count_stmt, stmt_args