def GetSendersIRC (period, startdate, enddate, identities_db, type_analysis, evolutionary):
fields = " count(distinct(nick)) as senders "
tables = " irclog " + GetSQLReportFrom(identities_db, type_analysis)
filters = GetSQLReportWhere(type_analysis, "author")
filters += " and type='COMMENT' "
q = BuildQuery(period, startdate, enddate, " date ", fields, tables, filters, evolutionary)
return(ExecuteQuery(q))
def GetChangers(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
#This function returns the evolution or agg number of changed issues
#This function can be also reproduced using the Backlog function.
#However this function is less time expensive.
fields = " count(distinct(pup.upeople_id)) as changers "
tables = " issues i, changes ch " + GetITSSQLReportFrom(
identities_db, type_analysis)
filters = " i.id = ch.issue_id "
filters_ext = GetITSSQLReportWhere(type_analysis)
if (filters_ext != ""):
filters += " and " + filters_ext
#unique identities filters
if (type_analysis is None or len(type_analysis) != 2):
#Specific case for the basic option where people_upeople table is needed
#and not taken into account in the initial part of the query
tables += ", people_upeople pup"
filters += " and i.submitted_by = pup.people_id"
elif (type_analysis[0] == "repository"):
#Adding people_upeople table
tables += ", people_upeople pup"
filters += " and i.submitted_by = pup.people_id "
#Action needed to replace issues filters by changes one
filters = filters.replace("i.submitted", "ch.changed")
q = BuildQuery(period, startdate, enddate, " ch.changed_on ", fields,
tables, filters, evolutionary)
data = ExecuteQuery(q)
return (data)
def GetIPs(period, startdate, enddate, evolutionary):
# Generic function to obtain number of IPs
fields = "count(distinct(ip)) as ips"
tables = "downloads"
filters = ""
query = BuildQuery(period, startdate, enddate, " date ", fields, tables,
filters, evolutionary)
return (ExecuteQuery(query))
def GetAuthorsMediaWiki(period, startdate, enddate, identities_db,
type_analysis, evolutionary):
fields = " count(distinct(user)) as authors "
tables = " wiki_pages_revs " + GetSQLReportFrom(identities_db,
type_analysis)
filters = GetSQLReportWhere(type_analysis, "author")
q = BuildQuery(period, startdate, enddate, " date ", fields, tables,
filters, evolutionary)
return (ExecuteQuery(q))
def GetIssuesRepositories(period, startdate, enddate, identities_db,
type_analysis, evolutionary):
# Generic function that counts repositories
fields = " COUNT(DISTINCT(tracker_id)) AS trackers "
tables = " issues i " + GetITSSQLReportFrom(identities_db, type_analysis)
filters = GetITSSQLReportWhere(type_analysis)
q = BuildQuery(period, startdate, enddate, " i.submitted_on ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetThreads(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
# Generic function that counts threads
fields = " count(distinct(m.is_response_of)) as threads"
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
filters = GetMLSSQLReportWhere(type_analysis)
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetMLSRepositories(rfield, period, startdate, enddate, identities_db,
type_analysis, evolutionary):
# Generic function that counts threads
fields = " COUNT(DISTINCT(" + rfield + ")) AS repositories "
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
filters = GetMLSSQLReportWhere(type_analysis)
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetMLSInit(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
# Generic function that counts replies
fields = " count(distinct(m.message_ID)) as sent_init"
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
filters = GetMLSSQLReportWhere(
type_analysis) + " m.is_response_of is null "
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetOpened(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
#This function returns the evolution or agg number of opened issues
#This function can be also reproduced using the Backlog function.
#However this function is less time expensive.
fields = " count(distinct(i.id)) as opened "
tables = " issues i " + GetITSSQLReportFrom(identities_db, type_analysis)
filters = GetITSSQLReportWhere(type_analysis)
q = BuildQuery(period, startdate, enddate, " submitted_on ", fields,
tables, filters, evolutionary)
data = ExecuteQuery(q)
return (data)
def GetDate(startdate, enddate, identities_db, type_analysis, type):
# date of submmitted issues (type= max or min)
if (type == "max"):
fields = " DATE_FORMAT (max(submitted_on), '%Y-%m-%d') as last_date"
else:
fields = " DATE_FORMAT (min(submitted_on), '%Y-%m-%d') as first_date"
tables = " issues i " + GetITSSQLReportFrom(identities_db, type_analysis)
filters = GetITSSQLReportWhere(type_analysis)
q = BuildQuery(None, startdate, enddate, " i.submitted_on ", fields,
tables, filters, False)
data = ExecuteQuery(q)
return (data)
def GetIssuesStudies(period, startdate, enddate, identities_db, type_analysis,
evolutionary, study):
# Generic function that counts evolution/agg number of specific studies with similar
# database schema such as domains, companies and countries
fields = ' count(distinct(name)) as ' + study
tables = " issues i " + GetITSSQLReportFrom(identities_db, type_analysis)
filters = GetITSSQLReportWhere(type_analysis)
#Filtering last part of the query, not used in this case
#filters = gsub("and\n( )+(d|c|cou|com).name =.*$", "", filters)
q = BuildQuery(period, startdate, enddate, " i.submitted_on ", fields,
tables, filters, evolutionary)
q = re.sub(r'and (d|c|cou|com).name.*=', "", q)
data = ExecuteQuery(q)
return (data)
def GetEmailsSent(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
# Generic function that counts emails sent
if (evolutionary):
fields = " count(distinct(m.message_ID)) as sent "
else:
fields = " count(distinct(m.message_ID)) as sent, "+\
" DATE_FORMAT (min(m.first_date), '%Y-%m-%d') as first_date, "+\
" DATE_FORMAT (max(m.first_date), '%Y-%m-%d') as last_date "
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
filters = GetMLSSQLReportWhere(type_analysis)
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetMLSStudies(period, startdate, enddate, identities_db, type_analysis,
evolutionary, study):
# Generic function that counts evolution/agg number of specific studies with similar
# database schema such as domains, companies and countries
fields = ' count(distinct(name)) as ' + study
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
filters = GetMLSSQLReportWhere(
type_analysis) + " and m.is_response_of is null "
#Filtering last part of the query, not used in this case
#filters = gsub("and\n( )+(d|c|cou|com).name =.*$", "", filters)
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
q = re.sub(r'(d|c|cou|com).name.*and', "", q)
data = ExecuteQuery(q)
return (data)
def GetClosed(period, startdate, enddate, identities_db, type_analysis,
evolutionary, closed_condition):
#This function returns the evolution or agg number of closed issues
#This function can be also reproduced using the Backlog function.
#However this function is less time expensive.
fields = " count(distinct(i.id)) as closed "
tables = " issues i, changes ch " + GetITSSQLReportFrom(
identities_db, type_analysis)
filters = " i.id = ch.issue_id and " + closed_condition
filters_ext = GetITSSQLReportWhere(type_analysis)
if (filters_ext != ""):
filters += " and " + filters_ext
#Action needed to replace issues filters by changes one
filters = filters.replace("i.submitted", "ch.changed")
q = BuildQuery(period, startdate, enddate, " ch.changed_on ", fields,
tables, filters, evolutionary)
data = ExecuteQuery(q)
return (data)
def GetOpeners(period, startdate, enddate, identities_db, type_analysis,
evolutionary, closed_condition):
#This function returns the evolution or agg number of people opening issues
fields = " count(distinct(pup.upeople_id)) as openers "
tables = " issues i " + GetITSSQLReportFrom(identities_db, type_analysis)
filters = GetITSSQLReportWhere(type_analysis)
if (type_analysis is None or len(type_analysis) != 2):
#Specific case for the basic option where people_upeople table is needed
#and not taken into account in the initial part of the query
tables += ", people_upeople pup"
filters += " and i.submitted_by = pup.people_id"
elif (type_analysis[0] == "repository"):
#Adding people_upeople table
tables += ", people_upeople pup"
filters += " and i.submitted_by = pup.people_id "
q = BuildQuery(period, startdate, enddate, " submitted_on ", fields,
tables, filters, evolutionary)
data = ExecuteQuery(q)
return (data)
def GetMLSSendersInit(period, startdate, enddate, identities_db, type_analysis,
evolutionary):
#Generic function that counts people sending messages
fields = " count(distinct(pup.upeople_id)) as senders_init "
tables = " messages m " + GetMLSSQLReportFrom(identities_db, type_analysis)
if (tables == " messages m "):
# basic case: it's needed to add unique ids filters
tables += ", messages_people mp, people_upeople pup "
filters = GetMLSFiltersOwnUniqueIdsMLS()
else:
filters = GetMLSSQLReportWhere(type_analysis)
if (type_analysis and type_analysis[0] == "repository"):
#Adding people_upeople table
tables += ", messages_people mp, people_upeople pup "
filters += " and m.message_ID = mp.message_id and "+\
" mp.email_address = pup.people_id and "+\
" mp.type_of_recipient=\'From\' "
filters += " and m.is_response_of is null "
q = BuildQuery(period, startdate, enddate, " m.first_date ", fields,
tables, filters, evolutionary)
return (ExecuteQuery(q))
def GetRepositoriesIRC (period, startdate, enddate, identities_db, type_analysis, evolutionary):
fields = " COUNT(DISTINCT(channel_id)) AS repositories "
tables = " irclog " + GetSQLReportFrom(identities_db, type_analysis)
filters = GetSQLReportWhere(type_analysis, "author")
q = BuildQuery(period, startdate, enddate, " date ", fields, tables, filters, evolutionary)
return(ExecuteQuery(q))
