def GetRepoEvolSentSendersIRC (repo, period, 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 = GetSQLPeriod(period,'date', fields, tables, filters, startdate, enddate)
return(ExecuteQuery(q))
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 GetEvolChanges(period, startdate, enddate, value):
fields = "count(issue_id) as "+ value+ "_changes"
tables = "changes"
filters = "new_value='"+value+"'"
q = GetSQLPeriod(period, " changed_on", fields, tables, filters,
startdate, enddate)
return(ExecuteQuery(q))
def EvolTimeToReviewSCRsql (period, startdate, enddate, identities_db = None, type_analysis = []):
q = GetTimeToReviewQuerySCR (startdate, enddate, identities_db, type_analysis)
# Evolution in time of AVG review time
fields = "SUM(revtime)/COUNT(revtime) AS review_time_days_avg "
tables = "("+q+") t"
filters = ""
q = GetSQLPeriod(period,'changed_on', fields, tables, filters,
startdate, enddate)
data = ExecuteQuery(q)
if not isinstance(data['review_time_days_avg'], (list)):
data['review_time_days_avg'] = [data['review_time_days_avg']]
return(data)
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 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 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 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 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))