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 <eeggles...@wikimedia.org> Gerrit-Reviewer: Awight <awi...@wikimedia.org> Gerrit-Reviewer: Cdentinger <cdentin...@wikimedia.org> Gerrit-Reviewer: jenkins-bot <> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits