[MediaWiki-commits] [Gerrit] Export all email addresses for each contact - change (wikimedia...tools)
Ejegg has uploaded a new change for review. https://gerrit.wikimedia.org/r/175475 Change subject: Export all email addresses for each contact .. Export all email addresses for each contact Quit de-duping on contact ID. When a donor has multiple email addresses, we want a row for each address in the export. Change-Id: I574a8882430f43a6646cc546bffee2f7f8902fe1 --- M silverpop_export/update_table.sql 1 file changed, 0 insertions(+), 28 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools refs/changes/75/175475/1 diff --git a/silverpop_export/update_table.sql b/silverpop_export/update_table.sql index 6b91398..5d868d2 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -4,7 +4,6 @@ DROP TABLE IF EXISTS temp_silverpop_export; DROP TABLE IF EXISTS temp_silverpop_export_dedupe_email; -DROP TABLE IF EXISTS temp_silverpop_export_dedupe_contact; DROP TABLE IF EXISTS temp_silverpop_export_stat; CREATE TEMPORARY TABLE IF NOT EXISTS temp_silverpop_export( @@ -146,33 +145,6 @@ ex.country = exde.country WHERE exde.maxid = ex.id; - --- Deduplicate rows that have the same contact ID because they'll --- generate the same result (~120 rows) -CREATE TEMPORARY TABLE temp_silverpop_export_dedupe_contact ( - id int PRIMARY KEY AUTO_INCREMENT, - contact_id int, - maxid int, - opted_out tinyint(1), - - INDEX spexdc_optedout (opted_out) -) COLLATE 'utf8_unicode_ci'; - -INSERT INTO temp_silverpop_export_dedupe_contact (contact_id, maxid, opted_out) - SELECT contact_id, max(id) maxid, max(opted_out) opted_out FROM temp_silverpop_export -FORCE INDEX (spex_contact_id) - GROUP BY contact_id - HAVING count(*) 1; - -DELETE temp_silverpop_export FROM temp_silverpop_export, temp_silverpop_export_dedupe_contact - WHERE -temp_silverpop_export.contact_id = temp_silverpop_export_dedupe_contact.contact_id AND -temp_silverpop_export.id != temp_silverpop_export_dedupe_contact.maxid; - -UPDATE temp_silverpop_export ex, temp_silverpop_export_dedupe_contact dc - SET ex.opted_out = 1 - WHERE -dc.opted_out = 1 AND dc.maxid = ex.id; -- Create an aggregate table from a full contribution table scan CREATE TEMPORARY TABLE temp_silverpop_export_stat ( -- To view, visit https://gerrit.wikimedia.org/r/175475 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I574a8882430f43a6646cc546bffee2f7f8902fe1 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Ejegg eeggles...@wikimedia.org ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
[MediaWiki-commits] [Gerrit] Export all email addresses for each contact - change (wikimedia...tools)
Awight has submitted this change and it was merged. Change subject: Export all email addresses for each contact .. Export all email addresses for each contact Quit de-duping on contact ID. When a donor has multiple email addresses, we want a row for each address in the export. Change-Id: I574a8882430f43a6646cc546bffee2f7f8902fe1 --- M silverpop_export/update_table.sql 1 file changed, 0 insertions(+), 28 deletions(-) Approvals: Awight: Looks good to me, approved jenkins-bot: Verified diff --git a/silverpop_export/update_table.sql b/silverpop_export/update_table.sql index 6b91398..5d868d2 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -4,7 +4,6 @@ DROP TABLE IF EXISTS temp_silverpop_export; DROP TABLE IF EXISTS temp_silverpop_export_dedupe_email; -DROP TABLE IF EXISTS temp_silverpop_export_dedupe_contact; DROP TABLE IF EXISTS temp_silverpop_export_stat; CREATE TEMPORARY TABLE IF NOT EXISTS temp_silverpop_export( @@ -146,33 +145,6 @@ ex.country = exde.country WHERE exde.maxid = ex.id; - --- Deduplicate rows that have the same contact ID because they'll --- generate the same result (~120 rows) -CREATE TEMPORARY TABLE temp_silverpop_export_dedupe_contact ( - id int PRIMARY KEY AUTO_INCREMENT, - contact_id int, - maxid int, - opted_out tinyint(1), - - INDEX spexdc_optedout (opted_out) -) COLLATE 'utf8_unicode_ci'; - -INSERT INTO temp_silverpop_export_dedupe_contact (contact_id, maxid, opted_out) - SELECT contact_id, max(id) maxid, max(opted_out) opted_out FROM temp_silverpop_export -FORCE INDEX (spex_contact_id) - GROUP BY contact_id - HAVING count(*) 1; - -DELETE temp_silverpop_export FROM temp_silverpop_export, temp_silverpop_export_dedupe_contact - WHERE -temp_silverpop_export.contact_id = temp_silverpop_export_dedupe_contact.contact_id AND -temp_silverpop_export.id != temp_silverpop_export_dedupe_contact.maxid; - -UPDATE temp_silverpop_export ex, temp_silverpop_export_dedupe_contact dc - SET ex.opted_out = 1 - WHERE -dc.opted_out = 1 AND dc.maxid = ex.id; -- Create an aggregate table from a full contribution table scan CREATE TEMPORARY TABLE temp_silverpop_export_stat ( -- To view, visit https://gerrit.wikimedia.org/r/175475 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I574a8882430f43a6646cc546bffee2f7f8902fe1 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Ejegg eeggles...@wikimedia.org Gerrit-Reviewer: Awight awi...@wikimedia.org Gerrit-Reviewer: Springle sprin...@wikimedia.org Gerrit-Reviewer: jenkins-bot ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
