Added script to generate stats on a git checkout

infra/tools/antibody/compute_stats.py

+# Copyright 2015 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.
+
+import json
+
+
+# functions that return stats calculated as lists of lists by month
+def ratio_calculator(numerator, denominator):
+  """Computes the ratio of the counts in two lists
+
+  Args:
+    numerator(list): list of lists with a year-month string as the first index
+                     and a count as the second index
+    denominator(list): list of lists with a year-month string as the first index
+                     and a count as the second index
+
+  Return:
+    ratios(list): a list of lists with ratios rounded to three decimal places
+                  e.g. [['2015-01', .251], ['2014-10', .742]]
+  """
+  ratios = []
+  for i in numerator:
+    for j in denominator:
+      if i[0] == j[0] and j[1] != 0:
+        ratios.append([i[0], round(float(i[1]) / j[1], 3)])
+        break
+      elif i[0] == j[0]:
+        ratios.append([i[0], 0])
+  return ratios
+
+
+def total_commits(cc):
+  """Counts all the git commits sorted by month and year
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists e.g. [['2014-01', 20], ['2014-02', 45]]
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(*)
+        FROM git_commit
+        WHERE YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    result = cc.fetchone()
+    results.append([month, int(result[0])])
+  return results

[pgervais, 2015/07/21 16:48:34]

(not for this CL)
This can be done in SQL only, and would also be more efficient. Something like
this would work I think:

SELECT COUNT(*), YEAR(git_commit.timestamp), MONTH(git_commit.timestamp) 
FROM git_commit
GROUP BY YEAR(git_commit.timestamp), MONTH(git_commit.timestamp)

http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year

+
+
+def total_suspicious(cc):
+  """Counts the number of commits with no review url or TBRed with no lgtm
+     sorted by month and year
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(*)
+        FROM review
+        INNER JOIN git_commit
+        ON review.review_url = git_commit.review_url
+        INNER JOIN commit_people
+        ON commit_people.git_commit_hash = git_commit.hash
+        LEFT JOIN (
+          SELECT review_url, COUNT(*) AS c
+            FROM review_people
+            WHERE type = 'lgtm'
+            GROUP BY review_url) lgtm_count
+        ON review.review_url = lgtm_count.review_url
+        WHERE lgtm_count.c = 0 OR lgtm_count.c IS NULL
+          AND commit_people.type = 'tbr'
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    no_lgtm = cc.fetchone()
+    cc.execute("""SELECT COUNT(*)
+        FROM git_commit
+        WHERE review_url IS NULL
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    no_review = cc.fetchone()
+    result = int(no_lgtm[0]) + int(no_review[0])
+    results.append([month, result])
+  return results
+
+
+def total_tbr(cc):
+  """Counts the number of commits with a TBR
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(DISTINCT git_commit_hash)
+        FROM commit_people
+        INNER JOIN git_commit
+        ON commit_people.git_commit_hash = git_commit.hash
+        WHERE commit_people.type = 'tbr'
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    result = cc.fetchone()
+    results.append([month, int(result[0])])
+  return results
+
+
+def tbr_no_lgtm(cc):
+  """Counts the number of commits with a TBR that have not been lgtm'ed
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(*)
+        FROM review
+        INNER JOIN git_commit ON review.review_url = git_commit.review_url
+        INNER JOIN commit_people
+        ON commit_people.git_commit_hash = git_commit.hash
+        LEFT JOIN (
+          SELECT review_url, COUNT(*) AS c FROM review_people
+            WHERE type = 'lgtm' GROUP BY review_url) lgtm_count
+        ON review.review_url = lgtm_count.review_url
+        WHERE lgtm_count.c = 0 OR lgtm_count.c IS NULL
+          AND commit_people.type = 'tbr'
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    result = cc.fetchone()
+    results.append([month, int(result[0])])
+  return results

[pgervais, 2015/07/21 16:48:34]

See my comment on total_commits above. You may be able to replace that by a
single SQL query.

+
+
+def blank_tbr(cc):
+  """Counts the number of occurences of TBR= with no reviewer listed
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(*)
+        FROM commit_people
+        INNER JOIN git_commit
+        ON commit_people.git_commit_hash = git_commit.hash
+        WHERE commit_people.people_email_address = 'NOBODY'
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    result = cc.fetchone()
+    results.append([month, int(result[0])])
+  return results
+
+
+def no_review_url(cc):
+  """Counts the number of commits with no review url
+
+  Args:
+    cc(cursor)
+
+  Return:
+    results(list): a list of lists
+  """
+  cc.execute("""SELECT DISTINCT DATE_FORMAT(git_commit.timestamp, '%Y-%m')
+      FROM git_commit""")
+  months = cc.fetchall()
+  results = []
+  for month in months:
+    month = month[0]
+    cc.execute("""SELECT COUNT(*)
+        FROM git_commit
+        WHERE review_url IS NULL
+          AND YEAR(git_commit.timestamp) = %s
+          AND MONTH(git_commit.timestamp) = %s""" % (month[:4], month[5:]))
+    result = cc.fetchone()
+    results.append([month, int(result[0])])
+  return results
+
+
+# functions that return totaled stats for a set period back in time
+def totaled_ratio_calculator(numerator, denominator):
+  """Computes the ratio of the counts in two lists
+
+  Args:
+    numerator(int): a totaled count
+    denominator(int): a totaled count
+
+  Return:
+    ratio(float): a ratio rounded to three decimal places
+  """
+  ratio = round(float(numerator) / denominator, 3)

[pgervais, 2015/07/21 16:48:34]

Take into account denominator == 0, as you've done in ratio_calculator(). You
may also want to move this function next to ratio_calculator().

And we need tests :-)

+  return ratio
+
+
+def totaled_total_commits(cc, sql_time_specification):
+  """Counts all the git commits in a given timeframe
+
+  Args:
+    cc(cursor)
+

[pgervais, 2015/07/21 16:48:34]

Describe sql_time_specification (same below)

+  Return:
+    result(int): a count of all the commits
+  """
+  cc.execute("""SELECT COUNT(*)
+      FROM git_commit
+      WHERE %s""" % sql_time_specification)
+  result = cc.fetchone()
+  return int(result[0])
+
+
+def totaled_total_suspicious(cc, sql_time_specification):
+  """Counts the number of commits with no review url or TBRed with no lgtm
+     in a given timeframe
+
+  Args:
+    cc(cursor)
+
+  Return:
+    result(int): a count of all the suspicious commits
+  """
+  cc.execute("""SELECT COUNT(*)
+      FROM review
+      INNER JOIN git_commit
+      ON review.review_url = git_commit.review_url
+      INNER JOIN commit_people
+      ON commit_people.git_commit_hash = git_commit.hash
+      LEFT JOIN (
+        SELECT review_url, COUNT(*) AS c
+          FROM review_people
+          WHERE type = 'lgtm'
+          GROUP BY review_url) lgtm_count
+      ON review.review_url = lgtm_count.review_url
+      WHERE lgtm_count.c = 0 OR lgtm_count.c IS NULL
+        AND commit_people.type = 'tbr' AND %s""" % sql_time_specification)
+  no_lgtm = cc.fetchone()
+  cc.execute("""SELECT COUNT(*)
+      FROM git_commit
+      WHERE review_url IS NULL AND %s""" % sql_time_specification)
+  no_review = cc.fetchone()
+  result = int(no_lgtm[0]) + int(no_review[0])
+  return result
+
+
+def totaled_total_tbr(cc, sql_time_specification):
+  """Counts the total number of commits with a TBR in a given timeframe
+
+  Args:
+    cc(cursor)
+
+  Return:
+    result(int): a count of all commits with a TBR
+  """
+  cc.execute("""SELECT COUNT(DISTINCT git_commit_hash)
+      FROM commit_people
+      INNER JOIN git_commit
+      ON commit_people.git_commit_hash = git_commit.hash
+      WHERE commit_people.type = 'tbr' AND %s""" % sql_time_specification)
+  result = cc.fetchone()
+  return int(result[0])
+
+
+def totaled_tbr_no_lgtm(cc, sql_time_specification):
+  """Counts the number of commits with a TBR that have not been lgtm'ed
+     in a given timeframe
+
+  Args:
+    cc(cursor)
+
+  Return:
+    result(int): a count of all commits with a TBR and no lgtm
+  """
+  cc.execute("""SELECT COUNT(*)
+      FROM review
+      INNER JOIN git_commit
+      ON review.review_url = git_commit.review_url
+      INNER JOIN commit_people
+      ON commit_people.git_commit_hash = git_commit.hash
+      LEFT JOIN (
+        SELECT review_url, COUNT(*) AS c
+          FROM review_people
+          WHERE type = 'lgtm'
+          GROUP BY review_url) lgtm_count
+      ON review.review_url = lgtm_count.review_url
+      WHERE lgtm_count.c = 0 OR lgtm_count.c IS NULL
+        AND commit_people.type = 'tbr' AND %s""" % sql_time_specification)
+  result = cc.fetchone()
+  return int(result[0])
+
+
+def totaled_blank_tbr(cc, sql_time_specification):
+  """Counts the number of occurences of TBR= with no reviewer listed in a
+     given timeframe
+
+  Args:
+    cc(cursor)
+
+  Return:
+    result(int): a count of all blank TBRs (TBR=)
+  """
+  cc.execute("""SELECT COUNT(*)
+      FROM commit_people
+      INNER JOIN git_commit
+      ON commit_people.git_commit_hash = git_commit.hash
+      WHERE commit_people.people_email_address = 'NOBODY'
+        AND %s""" % sql_time_specification)
+  result = cc.fetchone()
+  return int(result[0])
+
+
+def totaled_no_review_url(cc, sql_time_specification):
+  """Counts the number of commits with no review url in a given timeframe
+
+  Args:
+    cc(cursor)
+
+  Return:
+    result(int): a count of all commits with no review_url
+  """
+  cc.execute("""SELECT COUNT(*)
+    FROM git_commit
+    WHERE review_url IS NULL AND %s""" % sql_time_specification)
+  result = cc.fetchone()
+  return int(result[0])
+
+
+# TODO(keelerh): change the SQL query to avoid using temporary tables
+# add time specification back in
+def tbr_to_total_people_ratio(cc, sql_time_specification=''):
+  """Calculate TBR stats by author for the Antibody leaderboard and writes
+     them to a json file
+
+  Args:
+    cc(cursor)
+
+  Return:
+    people_tbr_data(list): a list of dictionaries containing author email,
+                           ratio of TBRed commits to total commits, number of
+                           TBRed commits, total number of commits, and overall
+                           rank, reverse sorted by ratio
+  """
+  cc.execute("""CREATE TEMPORARY TABLE addr_tbr_hash (email VARCHAR(200), tbr

[pgervais, 2015/07/21 16:48:34]

If the insert statement fails, the table will remain. Adding a 'finally' clause
that calls 'drop table' would prevent this kind of issues.

+      INT, hash VARCHAR(40), `timestamp` TIMESTAMP)""")
+  cc.execute("""INSERT INTO addr_tbr_hash (email, tbr, hash)
+      SELECT commit_people.people_email_address, tbr_count.c,
+        commit_people.git_commit_hash FROM commit_people
+      LEFT JOIN (
+        SELECT git_commit_hash, COUNT(*)
+        AS c FROM commit_people
+        INNER JOIN git_commit
+        ON git_commit.hash = commit_people.git_commit_hash
+        WHERE commit_people.type='tbr' %s
+        GROUP BY git_commit_hash) tbr_count
+      ON commit_people.git_commit_hash = tbr_count.git_commit_hash
+      WHERE commit_people.type='author'""" % sql_time_specification)
+  cc.execute("""SELECT email, SUM(CASE WHEN tbr<>0 THEN 1 ELSE 0 END) num_tbrd,
+        COUNT(*)
+      AS num_total
+      FROM addr_tbr_hash
+      GROUP BY email""")
+  people_tbr_data = cc.fetchall()
+  people_tbr_data = [[data_item[0],
+                      round(float(data_item[1]) / data_item[2], 3),
+                      int(data_item[1]), int(data_item[2])] for data_item in
+                      people_tbr_data]
+  sorted_people_tbr_data = sorted(people_tbr_data, key=lambda x: x[1],
+                                  reverse=True)
+  top_100 = sorted_people_tbr_data[:100]
+  ordered_people_tbr_data = []
+  for i in range(len(top_100)):
+    temp = {}
+    temp['email'] = sorted_people_tbr_data[i][0]
+    temp['ratio'] = sorted_people_tbr_data[i][1]
+    temp['suspicious'] = sorted_people_tbr_data[i][2]
+    temp['total'] = sorted_people_tbr_data[i][3]
+    temp['rank'] = i
+    ordered_people_tbr_data.append(temp)
+  cc.execute("""DROP TABLE addr_tbr_hash""")
+  return ordered_people_tbr_data
+
+
+def compute_monthly_breakdown_stats(cc):
+  """Computes stats broken down by month
+
+  Args:
+    cc(cursor)
+
+  Returns:
+    monthly_breakdown_stats(dict): contains stats calculated per month
+                                   including: timeframe, total_commits,
+                                   total_suspicious, suspicious_to_total_ratio,
+                                   total_tbr, tbr_no_lgtm,
+                                   no_tbr_lgtm_to_total_ratio, blank_tbr,
+                                   blank_tbr_to_total_ratio, no_review_url,
+                                   no_review_url_to_total_ratio
+  """
+  monthly_breakdown_stats = {}
+  monthly_breakdown_stats['timeframe'] = 'monthly_breakdown'
+  tot_commits = total_commits(cc)
+  monthly_breakdown_stats['total_commits'] = tot_commits
+  tot_suspicious = total_suspicious(cc)
+  monthly_breakdown_stats['total_suspicious'] = tot_suspicious
+  monthly_breakdown_stats['suspicious_to_total_ratio'] = ratio_calculator(
+      tot_suspicious, tot_commits)
+  tot_tbr = total_tbr(cc)
+  monthly_breakdown_stats['total_tbr'] = tot_tbr
+  tot_tbr_no_lgtm = tbr_no_lgtm(cc)
+  monthly_breakdown_stats['tbr_no_lgtm'] = tot_tbr_no_lgtm
+  monthly_breakdown_stats['no_tbr_lgtm_to_total_ratio'] = ratio_calculator(
+      tot_tbr_no_lgtm, tot_tbr)
+  tot_blank_tbr = blank_tbr(cc)
+  monthly_breakdown_stats['blank_tbrs'] = tot_blank_tbr
+  monthly_breakdown_stats['blank_tbr_to_total_ratio'] = ratio_calculator(
+      tot_blank_tbr, tot_tbr)
+  tot_no_review_url = no_review_url(cc)
+  monthly_breakdown_stats['no_review_url'] = tot_no_review_url
+  monthly_breakdown_stats['no_review_url_to_total_ratio'] = ratio_calculator(
+      tot_no_review_url, tot_commits)
+  return monthly_breakdown_stats
+
+
+def compute_stats_by_time(cc):
+  """Computes the stats for the past 7 days, past 30 days, and all time
+
+  Args:
+    cc(cursor)
+
+  Returns:
+    output(list): three dictionaries containing stats for the past 7 days, 30
+                  days, and all time respectively, each including timeframe,
+                  suspicious_to_total_ratio, total_commits, tbr_no_lgtm,
+                  no_review_url, blank_tbr
+  """
+  stats_7_days, stats_30_days, stats_all_time = {}, {}, {}
+  for d in [stats_7_days, stats_30_days, stats_all_time]:
+    if d == stats_7_days:
+      d['timeframe'] = '7_days'

[pgervais, 2015/07/21 16:48:34]

Do that on line 466:
stats_7_days = {'timeframe': '7_days'}

+      sql_insert = 'DATEDIFF(NOW(), git_commit.timestamp) <= 7'

[pgervais, 2015/07/21 16:48:34]

Just set the value instead of writing a where clause, something like: timeframe
= 7. You can use timeframe = None for 'all times'. Then pass this value directly
to totaled_total_commits and other functions, which will compute the where
clause. If use_timeframe == None, then do not add any clause.

[keelerh, 2015/07/22 21:17:55]

I was initially going to do that, but then didn't know what to do for all_time,
because if I had written the where clause and then timeframe was None and I
didn't want to include the clause, I would have to recopy basically the entire
sql query with the where clause removed, which seemed like a ton of extra space.
Is there a value I can put there where it will include all rows?

+    elif d == stats_30_days:
+      d['timeframe'] = '30_days'
+      sql_insert = 'DATEDIFF(NOW(), git_commit.timestamp) <= 30'
+    elif d == stats_all_time:
+      d['timeframe'] = 'all_time'
+      sql_insert = 'DATEDIFF(git_commit.timestamp, NOW()) < 0'
+    tot_commits = totaled_total_commits(cc, sql_insert)
+    tot_suspicious = totaled_total_suspicious(cc, sql_insert)
+    d['suspicious_to_total_ratio'] = totaled_ratio_calculator(tot_suspicious,
+                                                              tot_commits)
+    d['total_commits'] = tot_commits
+    d['tbr_no_lgtm'] = totaled_tbr_no_lgtm(cc, sql_insert)
+    d['no_review_url'] = totaled_no_review_url(cc, sql_insert)
+    d['blank_tbr'] = totaled_blank_tbr(cc, sql_insert)
+  output = [stats_7_days, stats_30_days, stats_all_time]
+  return output
+
+
+def all_monthly_stats(cc, filename):
+  """Write stats calculated over different time segments as json to file
+
+  Args:
+    cc(cursor)
+    filename(str): the json file to write to
+  """
+  output = {}
+  stats_monthly_breakdown = compute_monthly_breakdown_stats(cc)
+  stats_by_time = compute_stats_by_time(cc)
+  stats_by_time.append(stats_monthly_breakdown)
+  for d in stats_by_time:
+    label = d['timeframe']
+    output[label] = d
+  with open(filename, 'w') as f:
+    json.dump(output, f)
+
+
+def all_time_leaderboard(cc, filename):
+  """Writes stats for all-time Antibody leaderboard to a json file
+
+  Args:
+    cc(cursor)
+    filename(str): the json file to write to
+  """
+  output = tbr_to_total_people_ratio(cc)
+  with open(filename, 'w') as f:
+    json.dump(output, f)
+
+
+def past_month_leaderboard(cc, filename):
+  """Writes stats for the past month Antibody leaderboard to a json file
+
+  Args:
+    cc(cursor)
+    filename(str): the json file to write to
+  """
+  sql_time_specification = 'AND DATEDIFF(NOW(), git_commit.timestamp) <= 30'
+  output = tbr_to_total_people_ratio(cc, sql_time_specification)
+  with open(filename, 'w') as f:
+    json.dump(output, f)