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)))