def populate_reps_location(): populate_reps_location="INSERT IGNORE INTO reps_location \ (location_name, longitude, latitude) \ SELECT distinct location, longitude, latitude \ FROM reps_facts_raw \ WHERE local_datetime BETWEEN %s and %s"; run_queries.run_dw_query(populate_reps_location, (str(lower_limit),str(upper_limit)))
def populate_github_repo():
populate_github_repo = "INSERT IGNORE INTO github_repo \
(github_repo_name) \
SELECT distinct extra_github_repo \
FROM github_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_github_repo,
(str(lower_limit), str(upper_limit)))
#see https://docs.google.com/spreadsheets/d/1cMZQYEmEiuDTAY93j4siiq58exaMZOa2o3YdoLyEYTI/edit#gid=0
set_repo_teams = "UPDATE github_repo LEFT JOIN team ON \
(team.team_name=%s) \
SET github_repo.team_key=team.team_key \
WHERE github_repo_name in (%s);"
list_of_repo_teams=(('Firefox OS', '123done'),('Firefox OS', 'acidity'),('Firefox','gecko-dev'),('Firefox OS', 'gaia'),('Firefox OS', 'b2g'),('Firefox OS', 'b2g-manifests'), ('Firefox OS', 'gonk-misc'), ('Firefox OS', 'fxos-certsuite'), ('Firefox OS', 'device-flame'), ('Firefox OS', 'marionette-js-runner'), ('Firefox OS', 'device-gp-keon'), ('Firefox OS', 'screencap-gonk'), ('Firefox OS', 'orangutan'), ('Firefox OS', 'android-device-crespo4g'), ('Firefox OS', 'unbootimg'), ('Firefox OS', 'fake-dalvik'), ('Firefox OS', 'fake-libdvm'), \
('Firefox OS', 'gonk-patches'), ('Firefox OS', 'platform_external_apriori'), ('Firefox OS', 'moztt'), ('Firefox OS', 'device-wasabi'), ('Firefox OS', 'platform_prebuilts_qemu-kernel'), ('Firefox OS', 'librecovery'), ('Firefox OS', 'device-fugu'), ('Firefox OS', 'device-leo'), ('Firefox OS', 'android-device-unagi'), ('Firefox OS', 'android-device-panda'), ('Firefox OS', 'device-helix'), ('Firefox OS', 'android-device-hamachi'), ('Firefox OS', 'device_generic_goldfish'), ('Firefox OS', 'device-flatfish'), ('Firefox OS', 'device-inari'), ('Firefox OS', 'notes'), ('Firefox OS', 'sockit-to-me'), ('Firefox OS', 'gaia-node-modules'), \
('Firefox OS', 'fxos-appgen'), ('Firefox OS', 'gaia-email-libs-and-more'), ('Firefox OS', 'mocha-tbpl-reporter'), ('Firefox OS', 'marionette-b2gdesktop-host'), ('Firefox OS', 'marionette-extension'), ('Firefox OS', 'gaia-specs'), ('Firefox OS', 'kernel_goldfish'), ('Firefox OS', 'platform_system_nfcd'), ('Firefox OS', 'marionette-apps'), ('Firefox OS', 'js-test-agent'), ('Firefox OS', 'firefoxos-loop-client'), ('Firefox OS', 'travis-project-jobs'), ('Firefox OS', 'mail-fakeservers'), ('Firefox OS', 'marionette-plugin-forms'), ('Firefox OS', 'marionette-js-client'), ('Firefox OS', 'marionette-js-logger'), ('Firefox OS', 'caldav'), ('Firefox OS', 'mozilla-runner'), ('Firefox OS', 'uplift'), ('Firefox OS', 'marionette-helper'), ('Firefox OS', 'Gaia-UI-Building-Blocks'), ('Firefox OS', 'jsas'), ('Firefox OS', 'jswbxml'), ('Firefox OS', 'bisect_b2g'), ('Firefox OS', 'marionette-content-script'), ('Firefox OS', 'device-sora'), ('Firefox OS', 'marionette-device-host'), \
('Firefox OS', 'marionette-profile-builder'), ('Firefox OS', 'mozilla-profile-builder'), ('Firefox OS', 'ntpclient-android'), ('Firefox OS', 'marionette-file-manager'), ('Firefox OS', 'mozilla-get-url'), ('Firefox OS', 'mozilla-download'), ('Firefox OS', 'mocha-json-proxy'), \
('Firefox OS', 'marionette-firefox-host'), ('Firefox OS', 'marionette-debug'), ('Firefox OS', 'marionette-orientation'), ('Firefox OS', 'mozilla-extract'), ('Firefox OS', 'mozilla-detect-os'), ('Firefox OS', 'bleach.js'), ('Firefox OS', 'marionette-debug-server'), ('Firefox OS', 'marionette-settings-api'), ('Firefox OS', 'haida-planning'), ('Firefox OS', 'gaia-profile-builder'), ('Firefox OS', 'boing'), ('Firefox OS', 'dogdish'), ('Firefox OS', 'dogfood-setup'), ('Firefox OS', 'gaia-botio-scripts'), ('Firefox OS', 'b2g-toolchains'), \
('QA', 'mozilla-services'), ('QA', 'mozilla/fxa-auth-server'), ('QA', 'mozilla/fxa-content-server'), ('QA', 'mozilla/fxa-js-client'), ('QA', 'mozilla-services/pushgo'), ('QA', 'mozilla-services/puppet-config'), ('QA', 'mozilla-services/svcops-oompaloompas'))
for team_name, repo_name in list_of_repo_teams:
run_queries.run_dw_query(set_repo_teams,
(str(team_name), str(repo_name)))
def populate_reps_activity(): populate_reps_activity="INSERT IGNORE INTO reps_activity \ (activity_name) \ SELECT distinct activity \ FROM reps_facts_raw \ WHERE local_datetime BETWEEN %s and %s"; run_queries.run_dw_query(populate_reps_activity, (str(lower_limit),str(upper_limit)))
def import_topic():
import_query = "INSERT IGNORE INTO sumo_topic (topic_name) \
SELECT distinct extra_topic FROM sumo_facts_raw \
WHERE local_datetime BETWEEN %s AND %s"
run_queries.run_dw_query(import_query,
(str(lower_limit), str(upper_limit)))
def aggregate_to_contributor_facts(): one_in_8_weeks_query="REPLACE INTO contributor_facts \ (canonical, utc_datetime, cnt, utc_date_key, contributor_key, \ conversion_key, source_key,team_key) \ SELECT canonical, utc_datetime, 1, utc_date_key, \ contributor_key, conversion_key, source_key, team_key \ FROM reps_facts \ INNER JOIN conversion ON (conversion_desc='1 in 8 weeks') \ INNER JOIN team ON (team_name='Reps') \ WHERE utc_datetime BETWEEN %s - interval 8 week and %s" run_queries.run_dw_query(one_in_8_weeks_query, (str(lower_limit),str(upper_limit))) one_in_4_weeks_query="REPLACE INTO contributor_facts \ (canonical, utc_datetime, cnt, utc_date_key, contributor_key, \ conversion_key, source_key,team_key) \ SELECT canonical, utc_datetime, 1, utc_date_key, \ contributor_key, conversion_key, source_key, team_key \ FROM reps_facts \ INNER JOIN conversion ON (conversion_desc='1 in 4 weeks') \ INNER JOIN team ON (team_name='Reps') \ WHERE utc_datetime BETWEEN %s - interval 4 week and %s" run_queries.run_dw_query(one_in_4_weeks_query, (str(lower_limit),str(upper_limit))) four_in_4_weeks_query="REPLACE INTO contributor_facts \ (canonical, utc_datetime, cnt, utc_date_key, contributor_key, \ conversion_key, source_key,team_key) \ SELECT canonical, utc_datetime, 1, utc_date_key, \ contributor_key, conversion_key, source_key, team_key \ FROM reps_facts \ INNER JOIN conversion as for_1_activity ON (for_1_activity.conversion_desc='1 in 4 weeks' AND for_1_activity.conversion_key=contributor_facts.conversion_key) \ INNER JOIN conversion as for_4_activities ON (for_4_activities.conversion_desc='4 in 4 weeks') \ INNER JOIN team ON (team_name='Reps') \ WHERE utc_datetime BETWEEN %s - interval 4 week and %s \ GROUP BY contributor_key,for_1_activity.conversion_key,canonical \ HAVING COUNT(*)>=4";
def aggregate_to_contributor_facts():
submit_patch_query = "REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key, team_key \
FROM github_facts \
INNER JOIN conversion ON (conversion_desc='Submitting patch') \
INNER JOIN source ON (source_name='github') \
INNER JOIN github_repo ON (github_facts.github_repo_key=github_repo.github_repo_key) \
WHERE utc_datetime BETWEEN %s and %s \
AND action='pull-request-opened' "
run_queries.run_dw_query(submit_patch_query,
(str(lower_limit), str(upper_limit)))
merge_patch_query = "REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key, team_key \
FROM github_facts \
INNER JOIN conversion ON (conversion_desc='Having patch be merged') \
INNER JOIN source ON (source_name='github') \
INNER JOIN github_repo ON (github_facts.github_repo_key=github_repo.github_repo_key) \
WHERE utc_datetime BETWEEN %s and %s \
AND action='commit-author' "
run_queries.run_dw_query(merge_patch_query,
(str(lower_limit), str(upper_limit)))
def aggregate_to_contributor_facts():
one_in_8_weeks_query = "REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key, team_key \
FROM reps_facts \
INNER JOIN conversion ON (conversion_desc='1 in 8 weeks') \
INNER JOIN team ON (team_name='Reps') \
WHERE utc_datetime BETWEEN %s - interval 8 week and %s"
run_queries.run_dw_query(one_in_8_weeks_query,
(str(lower_limit), str(upper_limit)))
one_in_4_weeks_query = "REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key, team_key \
FROM reps_facts \
INNER JOIN conversion ON (conversion_desc='1 in 4 weeks') \
INNER JOIN team ON (team_name='Reps') \
WHERE utc_datetime BETWEEN %s - interval 4 week and %s"
run_queries.run_dw_query(one_in_4_weeks_query,
(str(lower_limit), str(upper_limit)))
four_in_4_weeks_query = "REPLACE INTO contributor_facts \
def populate_contributor():
populate_contributor = "INSERT IGNORE INTO contributor (email) \
SELECT distinct email \
FROM github_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_contributor,
(str(lower_limit), str(upper_limit)))
def populate_github_org():
populate_github_org = "INSERT IGNORE INTO github_org \
(github_org_name) \
SELECT distinct extra_github_org \
FROM github_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_github_org,
(str(lower_limit), str(upper_limit)))
def populate_reps_activity():
populate_reps_activity = "INSERT IGNORE INTO reps_activity \
(activity_name) \
SELECT distinct activity \
FROM reps_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_reps_activity,
(str(lower_limit), str(upper_limit)))
def populate_reps_location():
populate_reps_location = "INSERT IGNORE INTO reps_location \
(location_name, longitude, latitude) \
SELECT distinct location, longitude, latitude \
FROM reps_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_reps_location,
(str(lower_limit), str(upper_limit)))
def populate_contributor(): populate_contributor="INSERT IGNORE INTO contributor (email) \ SELECT contributor_email \ FROM bug_facts_raw \ WHERE local_datetime BETWEEN %s and %s"; run_queries.run_dw_query(populate_contributor, (str(lower_limit),str(upper_limit))) populate_contributor="INSERT IGNORE INTO contributor (email) \ SELECT email \ FROM bug_attachment \ WHERE local_datetime BETWEEN %s and %s" run_queries.run_dw_query(populate_contributor, (str(lower_limit),str(upper_limit)))
def import_contributors_to_dimension(source, lower_limit, upper_limit):
if source == "sumo":
table = 'sumo_facts_raw'
if source == "github":
table = 'github_facts_raw'
if source == "bugzilla":
table = 'bug_facts_raw'
import_query="INSERT IGNORE INTO contributor (email) \
SELECT DISTINCT email FROM " + table + \
" WHERE local_datetime BETWEEN %s and %s;"
run_queries.run_dw_query(import_query, (lower_limit, upper_limit))
def import_contributors_to_dimension (source, lower_limit, upper_limit):
if source=="sumo":
table='sumo_facts_raw'
if source=="github":
table='github_facts_raw'
if source=="bugzilla":
table='bug_facts_raw'
import_query="INSERT IGNORE INTO contributor (email) \
SELECT DISTINCT email FROM " + table + \
" WHERE local_datetime BETWEEN %s and %s;"
run_queries.run_dw_query(import_query,(lower_limit,upper_limit))
def aggregate_to_sumo_facts(): aggregate_query="INSERT IGNORE INTO sumo_facts ( \ cnt, contributor_key, utc_datetime, canonical, \ utc_date_key, product_key, topic_key, action, \ locale, question, contribution_id_part) \ SELECT 1, contributor_key, \ ADDTIME(local_datetime,tz_offset), \ canonical, utc_date_key, product_key, topic_key, \ action, extra_locale, extra_question, extra_id \ FROM sumo_facts_raw INNER JOIN contributor USING (email) \ INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \ INNER JOIN sumo_product ON (product_name=extra_product) \ INNER JOIN sumo_topic ON (topic_name=extra_topic) \ WHERE local_datetime BETWEEN %s AND %s;" run_queries.run_dw_query(aggregate_query, (str(lower_limit),str(upper_limit)))
def number_comments():
get_bugs_query="SELECT bug_id FROM bug_facts \
WHERE fields='comment' AND utc_datetime BETWEEN %s AND %s"
bugs=run_queries.run_dw_query(get_bugs_query,(str(lower_limit),str(upper_limit)))
for key,value in bugs.iteritems():
for idx, val in enumerate(value):
single_bug_query="SELECT min(utc_datetime) FROM bug_facts \
WHERE fields='comment' AND bug_id=%s ORDER BY utc_datetime;"
single_bug=run_queries.run_dw_query(single_bug_query,(val))
for key2,value2 in single_bug.iteritems():
for idx2, val2 in enumerate(value2):
comment_num_update="UPDATE bug_facts SET comment_num=0 \
WHERE bug_id=%s AND utc_datetime=%s"
run_queries.run_dw_query(comment_num_update,(val,val2))
def export_import (source, exp_query, exp_params, imp_query):
if source=="bugzilla":
output=run_queries.run_bugzilla_query(exp_query, exp_params)
if source=="github":
output=run_queries.run_github_query(exp_query, exp_params)
if source=="reps":
output=run_queries.run_reps_query(exp_query, exp_params)
if source=="sumo":
output=run_queries.run_sumo_query(exp_query, exp_params)
for key,value in output.iteritems():
imp_params=()
for idx, val in enumerate(value):
#print idx, val
imp_params=imp_params + (val,)
#print imp_query ,imp_params
run_queries.run_dw_query(imp_query, imp_params)
def export_import(source, exp_query, exp_params, imp_query):
if source == "bugzilla":
output = run_queries.run_bugzilla_query(exp_query, exp_params)
if source == "github":
output = run_queries.run_github_query(exp_query, exp_params)
if source == "reps":
output = run_queries.run_reps_query(exp_query, exp_params)
if source == "sumo":
output = run_queries.run_sumo_query(exp_query, exp_params)
for key, value in output.iteritems():
imp_params = ()
for idx, val in enumerate(value):
#print idx, val
imp_params = imp_params + (val, )
#print imp_query ,imp_params
run_queries.run_dw_query(imp_query, imp_params)
def import_dates_to_UTC(source, lower_limit, upper_limit):
if source=="bugzilla":
table='bug_facts_raw'
else:
table=source+"_facts_raw"
import_query="INSERT IGNORE INTO utc_date_only (utc_date_only) \
SELECT distinct(DATE(ADDTIME(local_datetime,tz_offset))) FROM " \
+ table + " WHERE local_datetime BETWEEN %s and %s ORDER BY 1;"
run_queries.run_dw_query(import_query,(lower_limit,upper_limit))
update_query="UPDATE utc_date_only \
SET dayOfWeek=DAYOFWEEK(utc_date_only), weekOfYear=WEEK(utc_date_only), \
monthOfYear=MONTH(utc_date_only), year=YEAR(utc_date_only) \
WHERE utc_date_only BETWEEN %s and %s";
run_queries.run_dw_query(update_query,(lower_limit,upper_limit))
def populate_contributor():
populate_contributor = "INSERT IGNORE INTO contributor (email) \
SELECT contributor_email \
FROM bug_facts_raw \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_contributor,
(str(lower_limit), str(upper_limit)))
populate_contributor = "INSERT IGNORE INTO contributor (email) \
SELECT email \
FROM bug_attachment \
WHERE local_datetime BETWEEN %s and %s"
run_queries.run_dw_query(populate_contributor,
(str(lower_limit), str(upper_limit)))
def aggregate_to_bug_facts():
aggregate_query="INSERT IGNORE INTO bug_facts (utc_datetime, fields, \
canonical, added_values, removed_values, attachment_key, bug_id, \
contributor_key,product_key, component_key, status_key, utc_date_key) \
SELECT ADDTIME(local_datetime,tz_offset), fields, \
CONCAT('https://bugzilla.mozilla.org/show_bug.cgi?id=',bug_id) as canonical, \
added_values, removed_values, attachment_id, bug_id, \
contributor.contributor_key, bug_product.product_key, \
IFNULL(bug_component.component_key,0), \
bug_status.status_key, utc_date_only.utc_date_key \
FROM bug_facts_raw INNER JOIN contributor ON (contributor_email=email) \
INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \
LEFT JOIN bug_status ON (status=status_name) \
LEFT JOIN bug_product ON (product=product_name) \
LEFT JOIN bug_component ON (component=component_name) \
WHERE local_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(aggregate_query, (str(lower_limit),str(upper_limit)))
def aggregate_to_sumo_facts():
aggregate_query = "INSERT IGNORE INTO sumo_facts ( \
cnt, contributor_key, utc_datetime, canonical, \
utc_date_key, product_key, topic_key, action, \
locale, question, contribution_id_part) \
SELECT 1, contributor_key, \
ADDTIME(local_datetime,tz_offset), \
canonical, utc_date_key, product_key, topic_key, \
action, extra_locale, extra_question, extra_id \
FROM sumo_facts_raw INNER JOIN contributor USING (email) \
INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \
INNER JOIN sumo_product ON (product_name=extra_product) \
INNER JOIN sumo_topic ON (topic_name=extra_topic) \
WHERE local_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(aggregate_query,
(str(lower_limit), str(upper_limit)))
def import_dates_to_UTC(source, lower_limit, upper_limit):
if source == "bugzilla":
table = 'bug_facts_raw'
else:
table = source + "_facts_raw"
import_query="INSERT IGNORE INTO utc_date_only (utc_date_only) \
SELECT distinct(DATE(ADDTIME(local_datetime,tz_offset))) FROM " \
+ table + " WHERE local_datetime BETWEEN %s and %s ORDER BY 1;"
run_queries.run_dw_query(import_query, (lower_limit, upper_limit))
update_query = "UPDATE utc_date_only \
SET dayOfWeek=DAYOFWEEK(utc_date_only), weekOfYear=WEEK(utc_date_only), \
monthOfYear=MONTH(utc_date_only), year=YEAR(utc_date_only) \
WHERE utc_date_only BETWEEN %s and %s"
run_queries.run_dw_query(update_query, (lower_limit, upper_limit))
def aggregate_to_github_facts():
aggregate_query = "INSERT IGNORE INTO github_facts ( \
contributor_key, canonical, action, github_org_key, github_repo_key, \
utc_datetime, utc_date_key) \
SELECT contributor.contributor_key, canonical, action, \
IFNULL(github_org.github_org_key,0), \
IFNULL(github_repo.github_repo_key,0), \
ADDTIME(local_datetime,tz_offset), \
utc_date_only.utc_date_key \
FROM github_facts_raw INNER JOIN contributor ON (github_facts_raw.email=contributor.email) \
INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \
LEFT JOIN github_org ON (extra_github_org=github_org_name) \
LEFT JOIN github_repo ON (extra_github_repo=github_repo_name) \
WHERE local_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(aggregate_query,
(str(lower_limit), str(upper_limit)))
def aggregate_to_reps_facts(): aggregate_query="INSERT IGNORE INTO reps_facts ( \ contributor_key, canonical, utc_datetime, utc_date_key, \ activity_key, source_key, campaign_key, location_key, \ contribution_area, event_url, activity_desc) \ SELECT contributor.contributor_key, canonical, \ ADDTIME(local_datetime,tz_offset), utc_date_only.utc_date_key, \ IFNULL(reps_activity.activity_key,0), source_key, \ IFNULL(reps_campaign.campaign_key,0), \ IFNULL(rl.location_key,0), \ contribution_area, event_url, activity_desc \ FROM reps_facts_raw AS raw INNER JOIN contributor ON (raw.email=contributor.email) \ INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \ LEFT JOIN reps_activity ON (raw.activity=activity_name) \ LEFT JOIN source ON (raw.source_text=source_name) \ LEFT JOIN reps_campaign ON (raw.campaign=campaign_name) \ LEFT JOIN reps_location AS rl ON (raw.location=rl.location_name AND raw.longitude=rl.longitude AND raw.latitude=rl.latitude) \ WHERE local_datetime BETWEEN %s AND %s;" run_queries.run_dw_query(aggregate_query, (str(lower_limit),str(upper_limit)))
def number_comments():
get_bugs_query = "SELECT bug_id FROM bug_facts \
WHERE fields='comment' AND utc_datetime BETWEEN %s AND %s"
bugs = run_queries.run_dw_query(get_bugs_query,
(str(lower_limit), str(upper_limit)))
for key, value in bugs.iteritems():
for idx, val in enumerate(value):
single_bug_query = "SELECT min(utc_datetime) FROM bug_facts \
WHERE fields='comment' AND bug_id=%s ORDER BY utc_datetime;"
single_bug = run_queries.run_dw_query(single_bug_query, (val))
for key2, value2 in single_bug.iteritems():
for idx2, val2 in enumerate(value2):
comment_num_update = "UPDATE bug_facts SET comment_num=0 \
WHERE bug_id=%s AND utc_datetime=%s"
run_queries.run_dw_query(comment_num_update, (val, val2))
def aggregate_to_bug_facts():
aggregate_query = "INSERT IGNORE INTO bug_facts (utc_datetime, fields, \
canonical, added_values, removed_values, attachment_key, bug_id, \
contributor_key,product_key, component_key, status_key, utc_date_key) \
SELECT ADDTIME(local_datetime,tz_offset), fields, \
CONCAT('https://bugzilla.mozilla.org/show_bug.cgi?id=',bug_id) as canonical, \
added_values, removed_values, attachment_id, bug_id, \
contributor.contributor_key, bug_product.product_key, \
IFNULL(bug_component.component_key,0), \
bug_status.status_key, utc_date_only.utc_date_key \
FROM bug_facts_raw INNER JOIN contributor ON (contributor_email=email) \
INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \
LEFT JOIN bug_status ON (status=status_name) \
LEFT JOIN bug_product ON (product=product_name) \
LEFT JOIN bug_component ON (component=component_name) \
WHERE local_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(aggregate_query,
(str(lower_limit), str(upper_limit)))
def aggregate_to_reps_facts():
aggregate_query = "INSERT IGNORE INTO reps_facts ( \
contributor_key, canonical, utc_datetime, utc_date_key, \
activity_key, source_key, campaign_key, location_key, \
contribution_area, event_url, activity_desc) \
SELECT contributor.contributor_key, canonical, \
ADDTIME(local_datetime,tz_offset), utc_date_only.utc_date_key, \
IFNULL(reps_activity.activity_key,0), source_key, \
IFNULL(reps_campaign.campaign_key,0), \
IFNULL(rl.location_key,0), \
contribution_area, event_url, activity_desc \
FROM reps_facts_raw AS raw INNER JOIN contributor ON (raw.email=contributor.email) \
INNER JOIN utc_date_only ON (DATE(ADDTIME(local_datetime,tz_offset))=utc_date_only) \
LEFT JOIN reps_activity ON (raw.activity=activity_name) \
LEFT JOIN source ON (raw.source_text=source_name) \
LEFT JOIN reps_campaign ON (raw.campaign=campaign_name) \
LEFT JOIN reps_location AS rl ON (raw.location=rl.location_name AND raw.longitude=rl.longitude AND raw.latitude=rl.latitude) \
WHERE local_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(aggregate_query,
(str(lower_limit), str(upper_limit)))
INNER JOIN source USING (source_key) \
WHERE contributor_level='active' and \
utc_datetime BETWEEN %s - interval 1 year and %s"
update_new1 = "CREATE TABLE `contributor_new_%s` (contributor_key int unsigned not null,c_date date not null); "
update_new2 = "INSERT INTO `contributor_new_%s` (contributor_key,c_date) \
select contributor_key,c_date from contributor_active \
where c_date between %s - INTERVAL 1 YEAR and %s \
group by contributor_key \
having count(contributor_key) = 1 and c_date = %s"
update_new3 = "update contributor_active SET is_new=0 WHERE c_date=%s;"
update_new4 = "update contributor_active SET is_new=1 \
WHERE c_date=%s AND contributor_key in \
( SELECT contributor_key FROM `contributor_new_%s` WHERE c_date=%s); "
update_new5 = "DROP TABLE `contributor_new_%s`;"
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays = dw_mysql.get_mondays(str(lower_limit), str(upper_limit))
for key, value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(import_active_contributors, (val, val, val))
run_queries.run_dw_query(update_new1, (val))
run_queries.run_dw_query(update_new2, (val, val, val, val))
run_queries.run_dw_query(update_new3, (val))
run_queries.run_dw_query(update_new4, (val, val, val))
run_queries.run_dw_query(update_new5, (val))
def aggregate_to_contributor_facts():
file_one_firefoxOS_bug_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Filing a bug Firefox OS') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE comment_num=0 AND product_name='Firefox OS' \
AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(file_one_firefoxOS_bug_query,
(str(lower_limit), str(upper_limit)))
create_bugzilla_account_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Creating Bugzilla account') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields='Creating Bugzilla account' \
AND utc_datetime BETWEEN %s AND %s"
run_queries.run_dw_query(create_bugzilla_account_query,
(str(lower_limit), str(upper_limit)))
file_one_bug_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Filing a bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE comment_num=0 AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(file_one_bug_query,
(str(lower_limit), str(upper_limit)))
submit_one_patch_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor.contributor_key, conversion_key, source_key,IFNULL(team_key,0) \
FROM bug_facts \
INNER JOIN contributor USING (contributor_key) \
INNER JOIN conversion ON (conversion_desc='Submitting patch') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN bug_attachment USING (attachment_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE ispatch=1 AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(submit_one_patch_query,
(str(lower_limit), str(upper_limit)))
approve_one_patch_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor.contributor_key, conversion_key, source_key,IFNULL(team_key,0) \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Having patch be approved') \
INNER JOIN source ON (source_name='bugzilla') \
INNER JOIN bug_attachment USING (attachment_key) \
INNER JOIN contributor USING (contributor_key) \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields = 'flagtypes.name' AND added_values REGEXP 'review\\+' \
AND ispatch=1 AND utc_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(approve_one_patch_query,
(str(lower_limit), str(upper_limit)))
one_triage_product_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change product 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'product' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_product_query,
(str(lower_limit), str(upper_limit)))
one_triage_component_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change component 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'component' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_component_query,
(str(lower_limit), str(upper_limit)))
one_triage_status_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change status 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'bug_status' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_status_query,
(str(lower_limit), str(upper_limit)))
one_triage_comment_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Comment 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields='comment' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_comment_query,
(str(lower_limit), str(upper_limit)))
triage_10_bugs_query = create_triage_query(str(10))
triage_25_bugs_query = create_triage_query(str(25))
triage_50_bugs_query = create_triage_query(str(50))
triage_100_bugs_query = create_triage_query(str(100))
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays = dw_mysql.get_mondays(str(lower_limit), str(upper_limit))
for key, value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(triage_10_bugs_query, (val, val))
run_queries.run_dw_query(triage_25_bugs_query, (val, val))
run_queries.run_dw_query(triage_50_bugs_query, (val, val))
run_queries.run_dw_query(triage_100_bugs_query, (val, val))
def aggregate_to_contributor_facts():
create_account_query="REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(canonical_size) + ") as canonical, \
min(utc_datetime), 1, min(utc_date_key), \
contributor_key, conversion_key, source_key, team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='Creating SUMO account') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
GROUP BY contributor_key"
run_queries.run_dw_query(create_account_query, (str(lower_limit),str(upper_limit)))
edit_kb_article_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(canonical_size) + ") as canonical, \
utc_datetime, 1, utc_date_key, \
contributor_key,conversion_key,source.source_key,team.team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='edit 1 article in KB') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
AND action='kb';"
run_queries.run_dw_query(edit_kb_article_query, (str(lower_limit),str(upper_limit)))
localize_kb_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(canonical_size) + ") as canonical, \
utc_datetime, 1, utc_date_key, \
contributor_key,conversion_key,source.source_key,team.team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='l10n 1 KB article') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
AND action='localization';"
run_queries.run_dw_query(localize_kb_query, (str(lower_limit),str(upper_limit)))
edit_5_kb_articles_query=create_kb_revision_query(str(5))
localize_5_kb_articles_query=create_l10n_query(str(5))
at_least_4_forum_answers=create_forum_answer_query(str(4))
at_least_10_forum_answers=create_forum_answer_query(str(10))
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays=dw_mysql.get_mondays(str(lower_limit),str(upper_limit))
for key,value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(edit_5_kb_articles_query, (val,val))
run_queries.run_dw_query(localize_5_kb_articles_query, (val,val))
run_queries.run_dw_query(at_least_4_forum_answers, (val,val))
run_queries.run_dw_query(at_least_10_forum_answers, (val,val))
INNER JOIN team USING (team_key) \
INNER JOIN source USING (source_key) \
WHERE contributor_level='active' and \
utc_datetime BETWEEN %s - interval 1 year and %s"
update_new1="CREATE TABLE `contributor_new_%s` (contributor_key int unsigned not null,c_date date not null); "
update_new2="INSERT INTO `contributor_new_%s` (contributor_key,c_date) \
select contributor_key,c_date from contributor_active \
where c_date between %s - INTERVAL 1 YEAR and %s \
group by contributor_key \
having count(contributor_key) = 1 and c_date = %s"
update_new3="update contributor_active SET is_new=0 WHERE c_date=%s;"
update_new4="update contributor_active SET is_new=1 \
WHERE c_date=%s AND contributor_key in \
( SELECT contributor_key FROM `contributor_new_%s` WHERE c_date=%s); "
update_new5="DROP TABLE `contributor_new_%s`;"
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays=dw_mysql.get_mondays(str(lower_limit),str(upper_limit))
for key,value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(import_active_contributors, (val,val,val))
run_queries.run_dw_query(update_new1, (val))
run_queries.run_dw_query(update_new2, (val,val,val,val))
run_queries.run_dw_query(update_new3, (val))
run_queries.run_dw_query(update_new4, (val,val,val))
run_queries.run_dw_query(update_new5, (val))
def aggregate_to_contributor_facts():
create_account_query = "REPLACE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(
canonical_size) + ") as canonical, \
min(utc_datetime), 1, min(utc_date_key), \
contributor_key, conversion_key, source_key, team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='Creating SUMO account') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
GROUP BY contributor_key"
run_queries.run_dw_query(create_account_query,
(str(lower_limit), str(upper_limit)))
edit_kb_article_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(
canonical_size) + ") as canonical, \
utc_datetime, 1, utc_date_key, \
contributor_key,conversion_key,source.source_key,team.team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='edit 1 article in KB') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
AND action='kb';"
run_queries.run_dw_query(edit_kb_article_query,
(str(lower_limit), str(upper_limit)))
localize_kb_query = "INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT LEFT(group_concat(canonical)," + str(
canonical_size) + ") as canonical, \
utc_datetime, 1, utc_date_key, \
contributor_key,conversion_key,source.source_key,team.team_key \
FROM sumo_facts \
INNER JOIN conversion ON (conversion_desc='l10n 1 KB article') \
INNER JOIN source ON (source_name='sumo') \
INNER JOIN team ON (team_name='Sumo') \
WHERE utc_datetime BETWEEN %s and %s \
AND action='localization';"
run_queries.run_dw_query(localize_kb_query,
(str(lower_limit), str(upper_limit)))
edit_5_kb_articles_query = create_kb_revision_query(str(5))
localize_5_kb_articles_query = create_l10n_query(str(5))
at_least_4_forum_answers = create_forum_answer_query(str(4))
at_least_10_forum_answers = create_forum_answer_query(str(10))
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays = dw_mysql.get_mondays(str(lower_limit), str(upper_limit))
for key, value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(edit_5_kb_articles_query, (val, val))
run_queries.run_dw_query(localize_5_kb_articles_query, (val, val))
run_queries.run_dw_query(at_least_4_forum_answers, (val, val))
run_queries.run_dw_query(at_least_10_forum_answers, (val, val))
def get_mondays(lower_limit, upper_limit): get_mondays_query='SELECT utc_date_only FROM utc_date_only WHERE dayOfWeek=2 \ AND utc_date_only BETWEEN %s and %s' return run_queries.run_dw_query(get_mondays_query, (str(lower_limit),str(upper_limit)))
def populate_contributor(): populate_contributor="INSERT IGNORE INTO contributor (email) \ SELECT distinct email \ FROM reps_facts_raw \ WHERE local_datetime BETWEEN %s and %s"; run_queries.run_dw_query(populate_contributor, (str(lower_limit),str(upper_limit)))
def aggregate_to_contributor_facts():
file_one_firefoxOS_bug_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Filing a bug Firefox OS') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE comment_num=0 AND product_name='Firefox OS' \
AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(file_one_firefoxOS_bug_query, (str(lower_limit),str(upper_limit)))
create_bugzilla_account_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Creating Bugzilla account') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields='Creating Bugzilla account' \
AND utc_datetime BETWEEN %s AND %s"
run_queries.run_dw_query(create_bugzilla_account_query, (str(lower_limit),str(upper_limit)))
file_one_bug_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Filing a bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE comment_num=0 AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(file_one_bug_query, (str(lower_limit),str(upper_limit)))
submit_one_patch_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor.contributor_key, conversion_key, source_key,IFNULL(team_key,0) \
FROM bug_facts \
INNER JOIN contributor USING (contributor_key) \
INNER JOIN conversion ON (conversion_desc='Submitting patch') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN bug_attachment USING (attachment_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE ispatch=1 AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(submit_one_patch_query, (str(lower_limit),str(upper_limit)))
approve_one_patch_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key,team_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor.contributor_key, conversion_key, source_key,IFNULL(team_key,0) \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Having patch be approved') \
INNER JOIN source ON (source_name='bugzilla') \
INNER JOIN bug_attachment USING (attachment_key) \
INNER JOIN contributor USING (contributor_key) \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields = 'flagtypes.name' AND added_values REGEXP 'review\\+' \
AND ispatch=1 AND utc_datetime BETWEEN %s AND %s;"
run_queries.run_dw_query(approve_one_patch_query, (str(lower_limit),str(upper_limit)))
one_triage_product_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change product 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'product' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_product_query, (str(lower_limit),str(upper_limit)))
one_triage_component_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change component 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'component' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_component_query, (str(lower_limit),str(upper_limit)))
one_triage_status_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Change status 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields regexp 'bug_status' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_status_query, (str(lower_limit),str(upper_limit)))
one_triage_comment_query="INSERT IGNORE INTO contributor_facts \
(canonical, utc_datetime, cnt, utc_date_key, contributor_key, \
conversion_key, source_key) \
SELECT canonical, utc_datetime, 1, utc_date_key, \
contributor_key, conversion_key, source_key \
FROM bug_facts \
INNER JOIN conversion ON (conversion_desc='Comment 1 bug') \
INNER JOIN source ON (source_name='bugzilla') \
LEFT JOIN bug_product USING (product_key) \
LEFT JOIN team ON (team_name=product_name) \
WHERE fields='comment' AND utc_datetime BETWEEN %s and %s"
run_queries.run_dw_query(one_triage_comment_query, (str(lower_limit),str(upper_limit)))
triage_10_bugs_query=create_triage_query(str(10))
triage_25_bugs_query=create_triage_query(str(25))
triage_50_bugs_query=create_triage_query(str(50))
triage_100_bugs_query=create_triage_query(str(100))
# for each Monday from lower_limit to upper_limit
# run the aggregate queries with the date as the param.
mondays=dw_mysql.get_mondays(str(lower_limit),str(upper_limit))
for key,value in mondays.iteritems():
for idx, val in enumerate(value):
run_queries.run_dw_query(triage_10_bugs_query, (val,val))
run_queries.run_dw_query(triage_25_bugs_query, (val,val))
run_queries.run_dw_query(triage_50_bugs_query, (val,val))
run_queries.run_dw_query(triage_100_bugs_query, (val,val))
def import_product(): import_query="INSERT IGNORE INTO sumo_product (product_name) \ SELECT distinct extra_product FROM sumo_facts_raw \ WHERE local_datetime BETWEEN %s AND %s" run_queries.run_dw_query(import_query, (str(lower_limit),str(upper_limit)))
def get_mondays(lower_limit, upper_limit):
get_mondays_query = 'SELECT utc_date_only FROM utc_date_only WHERE dayOfWeek=2 \
AND utc_date_only BETWEEN %s and %s'
return run_queries.run_dw_query(get_mondays_query,
(str(lower_limit), str(upper_limit)))