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 <eeggles...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
