# -*- coding: utf-8 -*-
# <nbformat>3.0</nbformat>
# <codecell>
# Code developed in Ipython Notebook. Reason why there are "codecells" around in the Python code.
%pylab inline
# <codecell>
from vizgrimoire.metrics.query_builder import SCRQuery
from vizgrimoire.metrics.metrics_filter import MetricFilters
import vizgrimoire.metrics.scr_metrics as scr
# <codecell>
user="root"
password=""
scr_db = "wikimedia_gerrit_20150224"
identities_db ="wikimedia_git_20150224"
people_out = ["Translation updater bot", "", "", ""]
dbcon=SCRQuery(user, password, scr_db, identities_db)
# <codecell>
period = MetricFilters.PERIOD_MONTH
startdate="'2013-01-01'"
enddate="'2015-02-01'"
filters = MetricFilters(period, startdate, enddate, ["autoreviews", "fake"])
# <codecell>
reviewers = scr.ActiveCoreReviewers(dbcon, filters)
# <codecell>
reviewers.get_agg()
#get query: reviewers._get_sql(False)
# <codecell>
from datetime import datetime
dates = []
unixtime = reviewers.get_ts()["unixtime"]
for i in unixtime:
dates.append(datetime.fromtimestamp(float(i)))
ts = reviewers.get_ts()
# <codecell>
fig = plt.figure()
plot(dates, ts["core_reviewers"])
fig.autofmt_xdate()
# <codecell>
period = MetricFilters.PERIOD_MONTH
startdate="'2015-01-24'"
enddate="'2015-02-24'"
filters = MetricFilters(period, startdate, enddate, ["autoreviews", "fake"])
reviewers = scr.ActiveCoreReviewers(dbcon, filters)
# <codecell>
list_reviewers = reviewers.get_list()
# <codecell>
#Now adding to the query the affiliation
query = """select up.id as id , up.identifier as identifier , c.name as company, count(distinct(ch.id)) as reviews
from issues_ext_gerrit ieg , issues i , changes ch ,
wikimedia_git_20150224.upeople up , people_upeople pup,
wikimedia_git_20150224.upeople_companies upc, wikimedia_git_20150224.companies c
where field = 'Code-Review' and changed_on < '2015-02-24' and ieg.issue_id = i.id and changed_on >= '2015-01-24' and
ch.issue_id = i.id and pup.upeople_id = up.id and (ch.new_value = -2 or ch.new_value = 2) and
ch.changed_by = pup.people_id and ieg.branch like '%master%' and
up.id = upc.upeople_id and upc.company_id = c.id and
i.id not in
(select distinct(i.id) as issue_id
from issues i,
changes ch
where i.submitted_by = ch.changed_by and
i.id = ch.issue_id and
ch.field = 'status' and
ch.new_value = 'MERGED')
group by up.id, up.identifier order by count(distinct(ch.id)) desc
"""
list_affs = reviewers.db.ExecuteQuery(query)
# <codecell>
for i in range(0,len(list_affs["id"])):
identifier = list_affs["identifier"][i]
company = list_affs["company"][i]
reviews = list_affs["reviews"][i]
print identifier.split('@')[0] + "," + company + "," + str(reviews)
# <codecell>
startdate = "'2013-01-01'"
enddate = "'2015-02-01'"
filters = MetricFilters(period, startdate, enddate)
participants = scr.Participants(dbcon, filters)
# <codecell>
participants.get_agg()
# <codecell>
ts= participants.get_ts()
# <codecell>
fig = plt.figure()
plot(dates, ts["participants"])
fig.autofmt_xdate()
# <codecell>
startdate = "'2015-01-24'"
enddate = "'2015-02-24'"
filters = MetricFilters(period, startdate, enddate)
participants = scr.Participants(dbcon, filters)
list_participants = participants.get_list()
# <codecell>
#Adding companies affiliation
query = """
SELECT u.identifier , c.name as company, count(*) as events
FROM ((SELECT comments.submitted_on as submitted_on , comments.submitted_by as submitted_by
FROM comments
WHERE comments.submitted_on>='2015-01-24' AND comments.submitted_on<'2015-02-24') union
(SELECT ch.changed_by as submitted_by , ch.changed_on as submitted_on
FROM changes ch
WHERE ch.changed_on>='2015-01-24' AND ch.changed_on<'2015-02-24') union
(SELECT i.submitted_on as submitted_on , i.submitted_by as submitted_by
FROM issues i
WHERE i.submitted_on>='2015-01-24' AND i.submitted_on<'2015-02-24')) t ,
people_upeople pup , wikimedia_git_20150224.upeople u,
wikimedia_git_20150224.upeople_companies upc, wikimedia_git_20150224.companies c
WHERE t.submitted_on>='2015-01-24' AND t.submitted_on<'2015-02-24' AND pup.upeople_id = u.id and
t.submitted_by = pup.people_id and u.id = upc.upeople_id and upc.company_id = c.id
GROUP by u.identifier
ORDER by count(*) desc
"""
list_affs = participants.db.ExecuteQuery(query)
# <codecell>
for i in range(0,len(list_affs["identifier"])):
identifier = list_affs["identifier"][i]
company = list_affs["company"][i]
events = list_affs["events"][i]
print identifier.split('@')[0] + "," + company + "," + str(events)
# <codecell>