def StaticNumSenders(startdate, enddate):
fields = " COUNT(DISTINCT(pup.upeople_id)) as senders "
tables = GetTablesOwnUniqueIdsMLS()
filters = GetFiltersOwnUniqueIdsMLS()
q = GetSQLGlobal('first_date', fields, tables, filters, startdate, enddate)
senders = ExecuteQuery(q)
return (senders)
def StaticNumSent(startdate, enddate):
fields = " COUNT(*) as sent "
tables = GetTablesOwnUniqueIdsMLS()
filters = GetFiltersOwnUniqueIdsMLS()
q = GetSQLGlobal('first_date', fields, tables, filters, startdate, enddate)
sent = ExecuteQuery(q)
return (sent)
def GetWaiting4Submitter (period, startdate, enddate, identities_db, type_analysis, evolutionary):
fields = "count(distinct(c.id)) as WaitingForSubmitter "
tables = " changes c, "+\
" issues i, "+\
" (select c.issue_id as issue_id, "+\
" c.old_value as old_value, "+\
" max(c.id) as id "+\
" from changes c, "+\
" issues i "+\
" where c.issue_id = i.id and "+\
" i.status='NEW' "+\
" group by c.issue_id, c.old_value) t1 "
tables = tables + GetSQLReportFromSCR(identities_db, type_analysis)
filters = " i.id = c.issue_id "+\
" and t1.id = c.id "+\
" and (c.field='CRVW' or c.field='Code-Review' or c.field='Verified' or c.field='VRIF') "+\
" and (c.new_value=-1 or c.new_value=-2) "
filters = filters + GetSQLReportWhereSCR(type_analysis)
if (evolutionary):
q = GetSQLPeriod(period, " c.changed_on", fields, tables, filters,
startdate, enddate)
else:
q = GetSQLGlobal(" c.changed_on ", fields, tables, filters,
startdate, enddate)
return(ExecuteQuery(q))
def GetListPeopleIRC (startdate, enddate) :
fields = "DISTINCT(pup.upeople_id) as id, count(irclog.id) total"
tables = GetTablesOwnUniqueIdsIRC()
filters = GetFiltersOwnUniqueIdsIRC()
filters += " AND irclog.type='COMMENT' "
filters += " GROUP BY nick ORDER BY total desc"
q = GetSQLGlobal('date',fields,tables, filters, startdate, enddate)
return(ExecuteQuery(q))
def GetRepoStaticSentSendersIRC (repo, startdate, enddate):
fields = 'COUNT(irclog.id) AS sent,'+\
'COUNT(DISTINCT(pup.upeople_id)) AS senders'
tables = GetTablesReposIRC()
filters = GetFiltersReposIRC()+" AND c.name='"+repo+"'"
filters += " AND irclog.type='COMMENT'"
q = GetSQLGlobal('date',fields, tables, filters, startdate, enddate)
return(ExecuteQuery(q))
def GetListPeopleMLS(startdate, enddate):
fields = "DISTINCT(pup.upeople_id) as id, count(m.message_ID) total"
tables = GetTablesOwnUniqueIdsMLS()
filters = GetFiltersOwnUniqueIdsMLS()
filters += " GROUP BY id ORDER BY total desc"
q = GetSQLGlobal('first_date', fields, tables, filters, startdate, enddate)
data = ExecuteQuery(q)
return (data)
def GetPeopleListITS(startdate, enddate):
fields = "DISTINCT(pup.upeople_id) as pid, count(c.id) as total"
tables = GetTablesOwnUniqueIdsITS()
filters = GetFiltersOwnUniqueIdsITS()
filters += " GROUP BY pid ORDER BY total desc"
q = GetSQLGlobal('changed_on', fields, tables, filters, startdate, enddate)
data = ExecuteQuery(q)
return (data)
def GetListPeopleMediaWiki(startdate, enddate):
fields = "DISTINCT(pup.upeople_id) as id, count(wiki_pages_revs.id) total"
tables = GetTablesOwnUniqueIdsMediaWiki()
filters = GetFiltersOwnUniqueIdsMediaWiki()
filters += " GROUP BY user ORDER BY total desc"
q = GetSQLGlobal('date', fields, tables, filters, startdate, enddate)
data = ExecuteQuery(q)
return (data)
def GetPeopleListSCR (startdate, enddate, bots):
filter_bots = ""
for bot in bots:
filter_bots += " name<>'"+bot+"' and "
fields = "DISTINCT(pup.upeople_id) as id, count(i.id) as total, name"
tables = GetTablesOwnUniqueIdsSCR('issues') + ", people"
filters = filter_bots
filters += GetFiltersOwnUniqueIdsSCR('issues')+ " and people.id = pup.people_id"
filters += " GROUP BY id ORDER BY total desc"
q = GetSQLGlobal('submitted_on', fields, tables, filters, startdate, enddate)
return(ExecuteQuery(q))
def GetQueryPagesMediaWiki(period, startdate, enddate, evol):
fields = "COUNT(page_id) as pages"
tables = " ( "+\
"SELECT wiki_pages.page_id, MIN(date) as date FROM wiki_pages, wiki_pages_revs "+\
"WHERE wiki_pages.page_id=wiki_pages_revs.page_id "+\
"GROUP BY wiki_pages.page_id) t "
filters = ''
if (evol):
q = GetSQLPeriod(period, 'date', fields, tables, filters, startdate,
enddate)
else:
q = GetSQLGlobal('date', fields, tables, filters, startdate, enddate)
return (q)
def GetReviewers (period, startdate, enddate, identities_db, type_analysis, evolutionary):
# TODO: so far without unique identities
fields = " count(distinct(changed_by)) as reviewers "
tables = " changes c "
filters = ""
if (evolutionary):
q = GetSQLPeriod(period, " c.changed_on", fields, tables, filters,
startdate, enddate)
else:
q = GetSQLGlobal(" c.changed_on ", fields, tables, filters,
startdate, enddate)
return(ExecuteQuery(q))
def GetQueryPeopleMediaWiki(developer_id, period, startdate, enddate, evol):
fields = "COUNT(wiki_pages_revs.id) AS revisions"
tables = GetTablesOwnUniqueIdsMediaWiki()
filters = GetFiltersOwnUniqueIdsMediaWiki(
) + " AND pup.upeople_id = " + str(developer_id)
if (evol):
q = GetSQLPeriod(period, 'date', fields, tables, filters, startdate,
enddate)
else:
fields += ",DATE_FORMAT (min(date),'%Y-%m-%d') as first_date, "+\
"DATE_FORMAT (max(date),'%Y-%m-%d') as last_date"
q = GetSQLGlobal('date', fields, tables, filters, startdate, enddate)
return (q)
def GetPeopleQuerySCR (developer_id, period, startdate, enddate, evol):
fields = "COUNT(c.id) AS closed"
tables = GetTablesOwnUniqueIdsSCR()
filters = GetFiltersOwnUniqueIdsSCR()+ " AND pup.upeople_id = "+ str(developer_id)
if (evol):
q = GetSQLPeriod(period,'changed_on', fields, tables, filters,
startdate, enddate)
else:
fields = fields + \
",DATE_FORMAT (min(changed_on),'%Y-%m-%d') as first_date, "+\
" DATE_FORMAT (max(changed_on),'%Y-%m-%d') as last_date"
q = GetSQLGlobal('changed_on', fields, tables, filters,
startdate, enddate)
return (q)
def GetReviewsChanges(period, startdate, enddate, type, type_analysis, evolutionary, identities_db):
fields = "count(issue_id) as "+ type+ "_changes"
tables = "changes c, issues i"
tables = tables + GetSQLReportFromSCR(identities_db, type_analysis)
filters = "c.issue_id = i.id AND new_value='"+type+"'"
filters = filters + GetSQLReportWhereSCR(type_analysis)
#Adding dates filters (and evolutionary or static analysis)
if (evolutionary):
q = GetSQLPeriod(period, " changed_on", fields, tables, filters,
startdate, enddate)
else:
q = GetSQLGlobal(" changed_on ", fields, tables, filters, startdate, enddate)
return(ExecuteQuery(q))
def GetQueryPeopleIRC (developer_id, period, startdate, enddate, evol):
fields = "COUNT(irclog.id) AS sent"
tables = GetTablesOwnUniqueIdsIRC()
filters = GetFiltersOwnUniqueIdsIRC() + " AND pup.upeople_id = " + str(developer_id)
filters += " AND irclog.type='COMMENT'"
if (evol) :
q = GetSQLPeriod(period,'date', fields, tables, filters,
startdate, enddate)
else:
fields = fields + \
",DATE_FORMAT (min(date),'%Y-%m-%d') as first_date,"+\
" DATE_FORMAT (max(date),'%Y-%m-%d') as last_date"
q = GetSQLGlobal('date', fields, tables, filters,
startdate, enddate)
return (q)
def GetQueryPeopleMLS(developer_id, period, startdate, enddate, evol):
fields = "COUNT(m.message_ID) AS sent"
tables = GetTablesOwnUniqueIdsMLS()
filters = GetFiltersOwnUniqueIdsMLS() + "AND pup.upeople_id = " + str(
developer_id)
if (evol):
q = GetSQLPeriod(period, 'first_date', fields, tables, filters,
startdate, enddate)
else:
fields = fields +\
",DATE_FORMAT (min(first_date),'%Y-%m-%d') as first_date, "+\
"DATE_FORMAT (max(first_date),'%Y-%m-%d') as last_date"
q = GetSQLGlobal('first_date', fields, tables, filters, startdate,
enddate)
return (q)
def GetPeopleQueryITS(developer_id, period, startdate, enddate, evol,
closed_condition):
fields = " COUNT(distinct(c.issue_id)) AS closed"
tables = GetTablesOwnUniqueIdsITS()
filters = GetFiltersOwnUniqueIdsITS() + " AND pup.upeople_id = " + str(
developer_id)
filters += " AND " + closed_condition
if (evol):
q = GetSQLPeriod(period, 'changed_on', fields, tables, filters,
startdate, enddate)
else:
fields += ",DATE_FORMAT (min(changed_on),'%Y-%m-%d') as first_date, "+\
"DATE_FORMAT (max(changed_on),'%Y-%m-%d') as last_date"
q = GetSQLGlobal('changed_on', fields, tables, filters, startdate,
enddate)
return (q)
def GetLongestReviews (startdate, enddate, type_analysis = []):
q = "select i.issue as review, "+\
" t1.old_value as patch, "+\
" timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened "+\
" from ( "+\
" select c.issue_id as issue_id, "+\
" c.old_value as old_value, "+\
" min(c.changed_on) as min_time, "+\
" max(c.changed_on) as max_time "+\
" from changes c, "+\
" issues i "+\
" where c.issue_id = i.id and "+\
" i.status='NEW' "+\
" group by c.issue_id, "+\
" c.old_value) t1, "+\
" issues i "+\
" where t1.issue_id = i.id "+\
" order by timeOpened desc "+\
" limit 20"
fields = " i.issue as review, " + \
" t1.old_value as patch, " + \
" timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened, "
tables = " issues i, "+\
" (select c.issue_id as issue_id, "+\
" c.old_value as old_value, "+\
" min(c.changed_on) as min_time, "+\
" max(c.changed_on) as max_time "+\
" from changes c, "+\
" issues i "+\
" where c.issue_id = i.id and "+\
" i.status='NEW' "+\
" group by c.issue_id, "+\
" c.old_value) t1 "
tables = tables + GetSQLReportFromSCR(identities_db, type_analysis)
filters = " t1.issue_id = i.id "
filters = filters + GetSQLReportWhereSCR(type_analysis)
q = GetSQLGlobal(" i.submitted_on ", fields, tables, filters,
startdate, enddate)
return(ExecuteQuery(q))
def GetReviews (period, startdate, enddate, type, type_analysis, evolutionary, identities_db):
#Building the query
fields = " count(distinct(i.issue)) as " + type
tables = "issues i" + GetSQLReportFromSCR(identities_db, type_analysis)
if type == "submitted": filters = ""
elif type == "opened": filters = " (i.status = 'NEW' or i.status = 'WORKINPROGRESS') "
elif type == "new": filters = " i.status = 'NEW' "
elif type == "inprogress": filters = " i.status = 'WORKINGPROGRESS' "
elif type == "closed": filters = " (i.status = 'MERGED' or i.status = 'ABANDONED') "
elif type == "merged": filters = " i.status = 'MERGED' "
elif type == "abandoned": filters = " i.status = 'ABANDONED' "
filters = filters + GetSQLReportWhereSCR(type_analysis)
#Adding dates filters (and evolutionary or static analysis)
if (evolutionary):
q = GetSQLPeriod(period, "i.submitted_on", fields, tables, filters,
startdate, enddate)
else:
q = GetSQLGlobal(" i.submitted_on ", fields, tables, filters, startdate, enddate)
return(ExecuteQuery(q))
def GetTimeToReviewQuerySCR (startdate, enddate, identities_db = None, type_analysis = [], bots = []):
filter_bots = ''
for bot in bots:
filter_bots = filter_bots + " people.name<>'"+bot+"' and "
# Subquery to get the time to review for all reviews
# fields = "DATEDIFF(changed_on,submitted_on) AS revtime, changed_on "
# fields = "TIMEDIFF(changed_on,submitted_on)/(24*3600) AS revtime, changed_on "
fields = "TIMESTAMPDIFF(SECOND, submitted_on, changed_on)/(24*3600) AS revtime, changed_on "
tables = "issues i, changes, people "
tables = tables + GetSQLReportFromSCR(identities_db, type_analysis)
filters = filter_bots + " i.id = changes.issue_id "
filters += " AND people.id = changes.changed_by "
filters += GetSQLReportWhereSCR(type_analysis)
filters += " AND field='status' AND new_value='MERGED' "
# remove autoreviews
filters += " AND i.submitted_by<>changes.changed_by "
filters += " ORDER BY changed_on "
q = GetSQLGlobal('changed_on', fields, tables, filters,
startdate, enddate)
min_days_for_review = 0.042 # one hour
q = "SELECT revtime, changed_on FROM ("+q+") qrevs WHERE revtime>"+str(min_days_for_review)
return (q)
def GetEvaluations (period, startdate, enddate, type, type_analysis, evolutionary):
# verified - VRIF
# approved - APRV
# code review - CRVW
# submitted - SUBM
#Building the query
fields = " count(distinct(c.id)) as " + type
tables = " changes c, issues i " + GetSQLReportFromSCR(None, type_analysis)
if type == "verified": filters = " (c.field = 'VRIF' OR c.field = 'Verified') "
elif type == "approved": filters = " c.field = 'APRV' "
elif type == "codereview": filters = " (c.field = 'CRVW' OR c.field = 'Code-Review') "
elif type == "sent": filters = " c.field = 'SUBM' "
filters = filters + " and i.id = c.issue_id "
filters = filters + GetSQLReportWhereSCR(type_analysis)
#Adding dates filters
if (evolutionary):
q = GetSQLPeriod(period, " c.changed_on", fields, tables, filters,
startdate, enddate)
else:
q = GetSQLGlobal(" c.changed_on", fields, tables, filters,
startdate, enddate)
return(ExecuteQuery(q))