def import_reps_raw():
date_format="%Y/%M/%e"
export_query="SELECT DISTINCT \
auth_user.email, reports_ngreport.report_date, \
reports_ngreport.created_on, TIMEDIFF(UTC_TIMESTAMP(),NOW()), \
LEFT(reports_ngreport.link,255), \
LEFT(IFNULL(reports_ngreport.activity_description,''),255), \
IFNULL(reports_activity.name,''), IFNULL(reports_campaign.name,''), \
IFNULL(profiles_functionalarea.name,''), reports_ngreport.location, \
reports_ngreport.latitude, reports_ngreport.longitude, \
CONCAT('https://reps.mozilla.org/u/', profiles_userprofile.display_name, '/r/', \
DATE_FORMAT(reports_ngreport.report_date, %s), \
'/', reports_ngreport.id, '/') AS canonical \
FROM reports_ngreport LEFT JOIN profiles_userprofile USING (mentor_id) \
LEFT JOIN auth_user ON (profiles_userprofile.user_id=auth_user.id) \
LEFT JOIN reports_activity ON (activity_id=reports_activity.id) \
LEFT JOIN reports_campaign ON (campaign_id=reports_campaign.id) \
LEFT JOIN events_event ON (event_id=events_event.id) \
LEFT JOIN reports_ngreport_functional_areas ON (reports_ngreport.id=ngreport_id) \
LEFT JOIN profiles_functionalarea ON (functionalarea_id=profiles_functionalarea.id) \
WHERE auth_user.email IS NOT NULL \
AND reports_ngreport.created_on BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO reps_facts_raw \
set email=%s, report_date=%s, local_datetime=%s, tz_offset=%s, \
event_url=%s, activity_desc=%s, activity=%s, campaign=%s, contribution_area=%s, \
location=%s, latitude=%s, longitude=%s, canonical=%s;"
dw_mysql.export_import("reps", export_query, (str(date_format),str(lower_limit),str(upper_limit)),import_query)
def import_status():
export_query = "SELECT id,value FROM bug_status;"
import_query = "INSERT IGNORE INTO bug_status \
(status_key,status_name) \
VALUES (%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (), import_query)
def import_components():
export_query = "SELECT id,name,product_id FROM components;"
import_query = "INSERT IGNORE INTO bug_component \
(component_key,component_name,product_key) \
VALUES (%s,%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (), import_query)
def import_products():
export_query = "SELECT id,name FROM products;"
import_query = "INSERT IGNORE INTO bug_product \
(product_key,product_name) \
VALUES (%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (), import_query)
def import_campaign():
export_query = "SELECT id,name FROM reports_campaign;"
import_query = "INSERT IGNORE INTO reps_campaign \
(campaign_key,campaign_name) \
VALUES (%s,%s);"
dw_mysql.export_import("reps", export_query, (), import_query)
def import_reps_raw():
date_format = "%Y/%M/%e"
export_query = "SELECT DISTINCT \
auth_user.email, reports_ngreport.report_date, \
reports_ngreport.created_on, TIMEDIFF(UTC_TIMESTAMP(),NOW()), \
LEFT(reports_ngreport.link,255), \
LEFT(IFNULL(reports_ngreport.activity_description,''),255), \
IFNULL(reports_activity.name,''), IFNULL(reports_campaign.name,''), \
IFNULL(profiles_functionalarea.name,''), reports_ngreport.location, \
reports_ngreport.latitude, reports_ngreport.longitude, \
CONCAT('https://reps.mozilla.org/u/', profiles_userprofile.display_name, '/r/', \
DATE_FORMAT(reports_ngreport.report_date, %s), \
'/', reports_ngreport.id, '/') AS canonical \
FROM reports_ngreport LEFT JOIN profiles_userprofile USING (mentor_id) \
LEFT JOIN auth_user ON (profiles_userprofile.user_id=auth_user.id) \
LEFT JOIN reports_activity ON (activity_id=reports_activity.id) \
LEFT JOIN reports_campaign ON (campaign_id=reports_campaign.id) \
LEFT JOIN events_event ON (event_id=events_event.id) \
LEFT JOIN reports_ngreport_functional_areas ON (reports_ngreport.id=ngreport_id) \
LEFT JOIN profiles_functionalarea ON (functionalarea_id=profiles_functionalarea.id) \
WHERE auth_user.email IS NOT NULL \
AND reports_ngreport.created_on BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO reps_facts_raw \
set email=%s, report_date=%s, local_datetime=%s, tz_offset=%s, \
event_url=%s, activity_desc=%s, activity=%s, campaign=%s, contribution_area=%s, \
location=%s, latitude=%s, longitude=%s, canonical=%s;"
dw_mysql.export_import(
"reps", export_query,
(str(date_format), str(lower_limit), str(upper_limit)), import_query)
def import_account_creation():
export_query="SELECT login_name, creation_ts, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) AS tz_offset, \
'Creating Bugzilla account', 0, '', '', '' \
FROM bugs.profiles \
WHERE creation_ts BETWEEN %s and %s"
import_query="INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, status=%s, product=%s, component=%s;"
dw_mysql.export_import("bugzilla", export_query, (str(lower_limit),str(upper_limit)),import_query)
def import_account_creation():
export_query = "SELECT login_name, creation_ts, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) AS tz_offset, \
'Creating Bugzilla account', 0, '', '', '' \
FROM bugs.profiles \
WHERE creation_ts BETWEEN %s and %s"
import_query = "INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, status=%s, product=%s, component=%s;"
dw_mysql.export_import("bugzilla", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_comments():
export_query="SELECT \
login_name, longdescs.bug_when, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) AS tz_offset, \
'comment', longdescs.bug_id, bug_status, \
products.name, components.name \
FROM bugs.longdescs INNER JOIN bugs.bugs USING (bug_id) \
INNER JOIN bugs.profiles ON (who=profiles.userid) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE bug_when BETWEEN %s and %s"
import_query="INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, status=%s, product=%s, component=%s"
dw_mysql.export_import("bugzilla", export_query, (str(lower_limit),str(upper_limit)),import_query)
def import_accounts():
export_query="SELECT \
email, '' as locale, '' as topic, '' as product, \
CONCAT('https://support.mozilla.org/en-US/user/',username) as canonical, \
'Creating SUMO account' as action, \
0 as extra_id, date_joined as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM auth_user \
WHERE date_joined BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO sumo_facts_raw \
set email=%s, extra_locale=%s, extra_topic=%s, \
extra_product=%s, canonical=%s, action=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query, (str(lower_limit),str(upper_limit)), import_query)
def import_accounts():
export_query = "SELECT \
email, '' as locale, '' as topic, '' as product, \
CONCAT('https://support.mozilla.org/en-US/user/',username) as canonical, \
'Creating SUMO account' as action, \
0 as extra_id, date_joined as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM auth_user \
WHERE date_joined BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO sumo_facts_raw \
set email=%s, extra_locale=%s, extra_topic=%s, \
extra_product=%s, canonical=%s, action=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_github_activity():
export_query = "SELECT \
github_public_email,github_commit_url,happened_on, \
github_organization,github_repository, \
IFNULL(github_username,github_public_email),LEFT(commit_msg,255), \
commit_id,action_type \
FROM activities \
WHERE github_public_email is not null AND happened_on BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO github_facts_raw \
set email=%s, canonical=%s, local_datetime=%s, \
extra_github_org=%s, extra_github_repo=%s, \
extra_github_username=%s, extra_commit_msg=%s, \
extra_commit_id=%s, action=%s;"
dw_mysql.export_import("github", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_bugs_activity():
export_query= "SELECT \
login_name,bug_when,TIMEDIFF(UTC_TIMESTAMP(),NOW()), \
fielddefs.name as field, bugs_activity.bug_id, added, removed, \
attach_id, bug_status, products.name as product, \
components.name as component \
FROM bugs.bugs_activity INNER JOIN bugs.bugs USING (bug_id) \
INNER JOIN bugs.profiles ON (who=profiles.userid)INNER JOIN bugs.fielddefs ON (fieldid=fielddefs.id) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE bug_when BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, added_values=%s, removed_values=%s, \
attachment_id=%s, status=%s, product=%s, component=%s"
dw_mysql.export_import("bugzilla", export_query, (str(lower_limit),str(upper_limit)),import_query)
def import_comments():
export_query = "SELECT \
login_name, longdescs.bug_when, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) AS tz_offset, \
'comment', longdescs.bug_id, bug_status, \
products.name, components.name \
FROM bugs.longdescs INNER JOIN bugs.bugs USING (bug_id) \
INNER JOIN bugs.profiles ON (who=profiles.userid) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE bug_when BETWEEN %s and %s"
import_query = "INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, status=%s, product=%s, component=%s"
dw_mysql.export_import("bugzilla", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_bugs_activity():
export_query = "SELECT \
login_name,bug_when,TIMEDIFF(UTC_TIMESTAMP(),NOW()), \
fielddefs.name as field, bugs_activity.bug_id, added, removed, \
attach_id, bug_status, products.name as product, \
components.name as component \
FROM bugs.bugs_activity INNER JOIN bugs.bugs USING (bug_id) \
INNER JOIN bugs.profiles ON (who=profiles.userid)INNER JOIN bugs.fielddefs ON (fieldid=fielddefs.id) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE bug_when BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO bug_facts_raw \
set contributor_email=%s, local_datetime=%s, tz_offset=%s, \
fields=%s, bug_id=%s, added_values=%s, removed_values=%s, \
attachment_id=%s, status=%s, product=%s, component=%s"
dw_mysql.export_import("bugzilla", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_attachments():
export_query="SELECT \
attach_id,login_name,ispatch, \
products.name,components.name, \
modification_time, TIMEDIFF(UTC_TIMESTAMP(),NOW()) \
FROM attachments \
INNER JOIN profiles on (submitter_id=userid) \
INNER JOIN bugs USING (bug_id) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE modification_time BETWEEN %s AND %s \
OR attachments.creation_ts BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO bug_attachment \
set attachment_key=%s, email=%s, ispatch=%s, \
product=%s, component=%s, \
local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("bugzilla", export_query, (str(lower_limit),str(upper_limit),str(lower_limit),str(upper_limit)),import_query)
def import_forum_posts():
export_query="SELECT \
email, locale, '' as topic, \
IF(ISNULL(products_product.title),'',products_product.title) as product, \
questions_answer.question_id as question, \
CONCAT('https://support.mozilla.org/questions/',questions_answer.question_id,'#answer-',questions_answer.id) as canonical, \
'forum answer' as action, questions_answer.id as extra_id, \
questions_answer.updated as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM questions_answer INNER JOIN auth_user ON (creator_id=auth_user.id) \
INNER JOIN questions_question ON (question_id=questions_question.id) \
LEFT JOIN products_product on (products_product.id=questions_question.product_id) \
WHERE questions_answer.updated BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO sumo_facts_raw \
set email=%s, extra_locale=%s, extra_topic=%s, \
extra_product=%s, extra_question=%s, canonical=%s, action=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query, (str(lower_limit),str(upper_limit)), import_query)
def import_forum_posts():
export_query = "SELECT \
email, locale, '' as topic, \
IF(ISNULL(products_product.title),'',products_product.title) as product, \
questions_answer.question_id as question, \
CONCAT('https://support.mozilla.org/questions/',questions_answer.question_id,'#answer-',questions_answer.id) as canonical, \
'forum answer' as action, questions_answer.id as extra_id, \
questions_answer.updated as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM questions_answer INNER JOIN auth_user ON (creator_id=auth_user.id) \
INNER JOIN questions_question ON (question_id=questions_question.id) \
LEFT JOIN products_product on (products_product.id=questions_question.product_id) \
WHERE questions_answer.updated BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO sumo_facts_raw \
set email=%s, extra_locale=%s, extra_topic=%s, \
extra_product=%s, extra_question=%s, canonical=%s, action=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_attachments():
export_query = "SELECT \
attach_id,login_name,ispatch, \
products.name,components.name, \
modification_time, TIMEDIFF(UTC_TIMESTAMP(),NOW()) \
FROM attachments \
INNER JOIN profiles on (submitter_id=userid) \
INNER JOIN bugs USING (bug_id) \
INNER JOIN bugs.products ON (products.id=product_id) \
INNER JOIN bugs.components ON (components.id=component_id) \
WHERE modification_time BETWEEN %s AND %s \
OR attachments.creation_ts BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO bug_attachment \
set attachment_key=%s, email=%s, ispatch=%s, \
product=%s, component=%s, \
local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("bugzilla", export_query,
(str(lower_limit), str(upper_limit),
str(lower_limit), str(upper_limit)), import_query)
def import_kb():
export_query="SELECT \
email, 'kb' as action, locale as extra_locale, \
CONCAT('https://support.mozilla.org/',locale,'/kb/',wiki_document.slug,'/revision/',wiki_revision.id) as canonical, \
IF (ISNULL(products_product.title),'',products_product.title) as product, \
IF (ISNULL(products_topic.title),'',products_topic.title) as topic, \
wiki_document.id as extra_id, wiki_revision.created as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM wiki_revision LEFT JOIN wiki_document on (document_id=wiki_document.id) \
LEFT JOIN auth_user ON (auth_user.id=creator_id) \
LEFT JOIN wiki_document_products ON (wiki_document_products.document_id=wiki_document.id) \
LEFT JOIN products_product on (products_product.id=wiki_document_products.product_id) \
LEFT JOIN wiki_document_topics ON (wiki_document_topics.document_id=wiki_document.id) \
LEFT JOIN products_topic on (products_topic.id=wiki_document_topics.topic_id) \
WHERE locale='en-US' AND wiki_revision.created BETWEEN %s AND %s;"
import_query="INSERT IGNORE INTO sumo_facts_raw \
set email=%s, action=%s, extra_locale=%s, \
canonical=%s, extra_product=%s, extra_topic=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query, (str(lower_limit),str(upper_limit)), import_query)
def import_kb():
export_query = "SELECT \
email, 'kb' as action, locale as extra_locale, \
CONCAT('https://support.mozilla.org/',locale,'/kb/',wiki_document.slug,'/revision/',wiki_revision.id) as canonical, \
IF (ISNULL(products_product.title),'',products_product.title) as product, \
IF (ISNULL(products_topic.title),'',products_topic.title) as topic, \
wiki_document.id as extra_id, wiki_revision.created as local_datetime, \
TIMEDIFF(UTC_TIMESTAMP(),NOW()) as tz_offset \
FROM wiki_revision LEFT JOIN wiki_document on (document_id=wiki_document.id) \
LEFT JOIN auth_user ON (auth_user.id=creator_id) \
LEFT JOIN wiki_document_products ON (wiki_document_products.document_id=wiki_document.id) \
LEFT JOIN products_product on (products_product.id=wiki_document_products.product_id) \
LEFT JOIN wiki_document_topics ON (wiki_document_topics.document_id=wiki_document.id) \
LEFT JOIN products_topic on (products_topic.id=wiki_document_topics.topic_id) \
WHERE locale='en-US' AND wiki_revision.created BETWEEN %s AND %s;"
import_query = "INSERT IGNORE INTO sumo_facts_raw \
set email=%s, action=%s, extra_locale=%s, \
canonical=%s, extra_product=%s, extra_topic=%s, \
extra_id=%s, local_datetime=%s, tz_offset=%s;"
dw_mysql.export_import("sumo", export_query,
(str(lower_limit), str(upper_limit)), import_query)
def import_status():
export_query="SELECT id,value FROM bug_status;"
import_query="INSERT IGNORE INTO bug_status \
(status_key,status_name) \
VALUES (%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (),import_query)
def import_products():
export_query="SELECT id,name FROM products;"
import_query="INSERT IGNORE INTO bug_product \
(product_key,product_name) \
VALUES (%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (),import_query)
def import_components():
export_query="SELECT id,name,product_id FROM components;"
import_query="INSERT IGNORE INTO bug_component \
(component_key,component_name,product_key) \
VALUES (%s,%s,%s);"
dw_mysql.export_import("bugzilla", export_query, (),import_query)
def import_campaign():
export_query="SELECT id,name FROM reports_campaign;"
import_query="INSERT IGNORE INTO reps_campaign \
(campaign_key,campaign_name) \
VALUES (%s,%s);"
dw_mysql.export_import("reps", export_query, (),import_query)