[MediaWiki-commits] [Gerrit] wikimedia...tools[master]: Consolidate updates from intermediate tables
Ejegg has uploaded a new change for review. https://gerrit.wikimedia.org/r/320715 Change subject: Consolidate updates from intermediate tables .. Consolidate updates from intermediate tables None of these joins will block any Civi tables, and we might as well do all these writes at once. Change-Id: I57a023616ab9570fde0b4d0b7948e16210ff59c3 --- M silverpop_export/update_table.sql 1 file changed, 16 insertions(+), 25 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools refs/changes/15/320715/1 diff --git a/silverpop_export/update_table.sql b/silverpop_export/update_table.sql index e69215b..06e2e5d 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -225,28 +225,6 @@ ct.contribution_status_id = 1 -- Only completed status GROUP BY e.email; --- (10 minutes) -UPDATE silverpop_export_staging ex - INNER JOIN silverpop_export_stat exs on ex.id = exs.exid - SET -ex.lifetime_usd_total = exs.total_usd, -ex.donation_count = exs.cnt_total, -ex.has_recurred_donation = exs.has_recurred_donation; - --- Populate information about the latest and greatest contributions -UPDATE silverpop_export_staging ex, silverpop_export_latest lt, silverpop_export_highest hg - SET -ex.latest_currency = lt.latest_currency, -ex.latest_native_amount = lt.latest_native_amount, -ex.latest_usd_amount = lt.latest_usd_amount, -ex.latest_donation = lt.latest_donation, -ex.highest_native_currency = hg.highest_native_currency, -ex.highest_native_amount = hg.highest_native_amount, -ex.highest_usd_amount = hg.highest_usd_amount, -ex.highest_donation_date = hg.highest_donation_date - WHERE -ex.email = lt.email AND ex.email=hg.email; - -- Postal addresses by email CREATE TABLE silverpop_export_address ( email varchar(255) PRIMARY KEY, @@ -276,11 +254,24 @@ ORDER BY isnull(a.postal_code) ASC, a.id DESC ON DUPLICATE KEY UPDATE email = e.email; --- (3 minutes) +-- Pull in address and latest/greatest/cumulative stats from intermediate tables UPDATE silverpop_export_staging ex - JOIN silverpop_export_address addr - ON ex.email = addr.email + LEFT JOIN silverpop_export_stat exs ON ex.id = exs.exid + LEFT JOIN silverpop_export_latest lt ON ex.email = lt.email + LEFT JOIN silverpop_export_highest hg ON ex.email = hg.email + LEFT JOIN silverpop_export_address addr ON ex.email = addr.email SET +ex.lifetime_usd_total = exs.total_usd, +ex.donation_count = exs.cnt_total, +ex.has_recurred_donation = exs.has_recurred_donation, +ex.latest_currency = lt.latest_currency, +ex.latest_native_amount = lt.latest_native_amount, +ex.latest_usd_amount = lt.latest_usd_amount, +ex.latest_donation = lt.latest_donation, +ex.highest_native_currency = hg.highest_native_currency, +ex.highest_native_amount = hg.highest_native_amount, +ex.highest_usd_amount = hg.highest_usd_amount, +ex.highest_donation_date = hg.highest_donation_date, ex.city = addr.city, ex.country = addr.country, ex.postal_code = addr.postal_code, -- To view, visit https://gerrit.wikimedia.org/r/320715 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I57a023616ab9570fde0b4d0b7948e16210ff59c3 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Ejegg ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
[MediaWiki-commits] [Gerrit] wikimedia...tools[master]: Consolidate updates from intermediate tables
jenkins-bot has submitted this change and it was merged. Change subject: Consolidate updates from intermediate tables .. Consolidate updates from intermediate tables None of these joins will block any Civi tables, and we might as well do all these writes at once. Change-Id: I57a023616ab9570fde0b4d0b7948e16210ff59c3 --- M silverpop_export/update_table.sql 1 file changed, 16 insertions(+), 25 deletions(-) Approvals: Cdentinger: Looks good to me, approved jenkins-bot: Verified diff --git a/silverpop_export/update_table.sql b/silverpop_export/update_table.sql index e69215b..06e2e5d 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -225,28 +225,6 @@ ct.contribution_status_id = 1 -- Only completed status GROUP BY e.email; --- (10 minutes) -UPDATE silverpop_export_staging ex - INNER JOIN silverpop_export_stat exs on ex.id = exs.exid - SET -ex.lifetime_usd_total = exs.total_usd, -ex.donation_count = exs.cnt_total, -ex.has_recurred_donation = exs.has_recurred_donation; - --- Populate information about the latest and greatest contributions -UPDATE silverpop_export_staging ex, silverpop_export_latest lt, silverpop_export_highest hg - SET -ex.latest_currency = lt.latest_currency, -ex.latest_native_amount = lt.latest_native_amount, -ex.latest_usd_amount = lt.latest_usd_amount, -ex.latest_donation = lt.latest_donation, -ex.highest_native_currency = hg.highest_native_currency, -ex.highest_native_amount = hg.highest_native_amount, -ex.highest_usd_amount = hg.highest_usd_amount, -ex.highest_donation_date = hg.highest_donation_date - WHERE -ex.email = lt.email AND ex.email=hg.email; - -- Postal addresses by email CREATE TABLE silverpop_export_address ( email varchar(255) PRIMARY KEY, @@ -276,11 +254,24 @@ ORDER BY isnull(a.postal_code) ASC, a.id DESC ON DUPLICATE KEY UPDATE email = e.email; --- (3 minutes) +-- Pull in address and latest/greatest/cumulative stats from intermediate tables UPDATE silverpop_export_staging ex - JOIN silverpop_export_address addr - ON ex.email = addr.email + LEFT JOIN silverpop_export_stat exs ON ex.id = exs.exid + LEFT JOIN silverpop_export_latest lt ON ex.email = lt.email + LEFT JOIN silverpop_export_highest hg ON ex.email = hg.email + LEFT JOIN silverpop_export_address addr ON ex.email = addr.email SET +ex.lifetime_usd_total = exs.total_usd, +ex.donation_count = exs.cnt_total, +ex.has_recurred_donation = exs.has_recurred_donation, +ex.latest_currency = lt.latest_currency, +ex.latest_native_amount = lt.latest_native_amount, +ex.latest_usd_amount = lt.latest_usd_amount, +ex.latest_donation = lt.latest_donation, +ex.highest_native_currency = hg.highest_native_currency, +ex.highest_native_amount = hg.highest_native_amount, +ex.highest_usd_amount = hg.highest_usd_amount, +ex.highest_donation_date = hg.highest_donation_date, ex.city = addr.city, ex.country = addr.country, ex.postal_code = addr.postal_code, -- To view, visit https://gerrit.wikimedia.org/r/320715 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I57a023616ab9570fde0b4d0b7948e16210ff59c3 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Ejegg Gerrit-Reviewer: Awight Gerrit-Reviewer: Cdentinger Gerrit-Reviewer: jenkins-bot <> ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
