Awight has submitted this change and it was merged. Change subject: Replace donatewiki counts table with view ......................................................................
Replace donatewiki counts table with view No need to manually update the thing. Bug: T114010 Change-Id: I1b506206d6bf4ff4efc1bd98b0128cfa349fdb2d --- M fundraiser/analytics/management/commands/LoadLPImpressions.py A sql/003_donatewiki_counts_should_be_a_view.sql M sql/create.sql 3 files changed, 12 insertions(+), 28 deletions(-) Approvals: Awight: Verified; Looks good to me, approved diff --git a/fundraiser/analytics/management/commands/LoadLPImpressions.py b/fundraiser/analytics/management/commands/LoadLPImpressions.py index ef88959..1a18da2 100644 --- a/fundraiser/analytics/management/commands/LoadLPImpressions.py +++ b/fundraiser/analytics/management/commands/LoadLPImpressions.py @@ -399,7 +399,6 @@ try: if not self.debug: self.write(self.unique_sql, self.pending_uniques) - self.update_donatewiki_counts() except Exception: self.logger.exception("Error writing donatewiki uniques to the database") finally: @@ -415,7 +414,6 @@ if not self.debug: self.write(self.impression_sql, self.pending_impressions) self.write(self.unique_sql, self.pending_uniques) - self.update_donatewiki_counts() self.pending_impressions = [] self.pending_uniques = [] @@ -477,19 +475,3 @@ for i in impressions: self.write(base_sql, [i]) - - @transaction.commit_manually - def update_donatewiki_counts(self): - """ - Keep the donatewiki_counts table up to date - """ - cursor = connections['default'].cursor() - - try: - cursor.execute('TRUNCATE donatewiki_counts') - cursor.execute('INSERT INTO donatewiki_counts (utm_source, utm_campaign, link_id, count) SELECT utm_source, utm_campaign, link_id, COUNT(*) FROM donatewiki_unique GROUP BY utm_source, utm_campaign, link_id ORDER BY utm_campaign, utm_source, link_id') - transaction.commit('default') - - except Exception as e: - transaction.rollback() - self.logger.exception("UNHANDLED EXCEPTION UPDATING DONATEWIKI COUNTS") diff --git a/sql/003_donatewiki_counts_should_be_a_view.sql b/sql/003_donatewiki_counts_should_be_a_view.sql new file mode 100644 index 0000000..ffba8ac --- /dev/null +++ b/sql/003_donatewiki_counts_should_be_a_view.sql @@ -0,0 +1,7 @@ +DROP TABLE `donatewiki_counts`; + +CREATE VIEW `donatewiki_counts` AS ( + SELECT utm_source, utm_campaign, link_id, COUNT(*) AS count + FROM donatewiki_unique + GROUP BY utm_source, utm_campaign, link_id +); diff --git a/sql/create.sql b/sql/create.sql index b75bd49..debd2c6 100644 --- a/sql/create.sql +++ b/sql/create.sql @@ -147,16 +147,11 @@ UNIQUE KEY utm_source (utm_source, contact_id) ) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB; -CREATE TABLE IF NOT EXISTS `donatewiki_counts` ( - id INT(11) UNSIGNED AUTO_INCREMENT, - utm_source VARCHAR(255) CHARACTER SET utf8 DEFAULT '' NOT NULL, - utm_campaign VARCHAR(255) CHARACTER SET utf8 DEFAULT '' NOT NULL, - link_id VARCHAR(128) CHARACTER SET utf8 DEFAULT '' NOT NULL, - count MEDIUMINT(11) UNSIGNED DEFAULT 0, - - PRIMARY KEY (id), - UNIQUE KEY (utm_source, utm_campaign, link_id) -) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB; +CREATE OR REPLACE VIEW `donatewiki_counts` AS ( + SELECT utm_source, utm_campaign, link_id, COUNT(*) AS count + FROM donatewiki_unique + GROUP BY utm_source, utm_campaign, link_id +); CREATE TABLE IF NOT EXISTS `globalcollect_orderids` ( orderid BIGINT(11) UNSIGNED NOT NULL, -- To view, visit https://gerrit.wikimedia.org/r/259623 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I1b506206d6bf4ff4efc1bd98b0128cfa349fdb2d Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools/DjangoBannerStats Gerrit-Branch: master Gerrit-Owner: Ejegg <eeggles...@wikimedia.org> Gerrit-Reviewer: Awight <awi...@wikimedia.org> Gerrit-Reviewer: Springle <sprin...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits