Adamw has submitted this change and it was merged. Change subject: Fixing database patch 2.3 for CentralNotice ......................................................................
Fixing database patch 2.3 for CentralNotice The original patch (https://gerrit.wikimedia.org/r/#/c/52913/) was badly broken and reverted by (https://gerrit.wikimedia.org/r/#/c/55587/) This patch fixes the issues found by Hashar. Change-Id: I2576b0f3230591d84d228c44fee9b4ea0997d566 --- M CentralNotice.php M CentralNotice.sql A patches/CNDatabasePatcher.php A patches/patch-centralnotice-2_3.sql 4 files changed, 344 insertions(+), 24 deletions(-) Approvals: Adamw: Verified; Looks good to me, approved jenkins-bot: Verified diff --git a/CentralNotice.php b/CentralNotice.php index ead11ee..4b4c9ac 100644 --- a/CentralNotice.php +++ b/CentralNotice.php @@ -256,6 +256,8 @@ $wgAutoloadClasses[ 'CampaignLog' ] = $includeDir . 'CampaignLog.php'; $wgAutoloadClasses[ 'GeoTarget' ] = $includeDir . 'GeoTarget.php'; + $wgAutoloadClasses[ 'CNDatabasePatcher' ] = $dir . 'patches/CNDatabasePatcher.php'; + $wgAutoloadClasses[ 'ApiCentralNoticeAllocations' ] = $apiDir . 'ApiCentralNoticeAllocations.php'; $wgAutoloadClasses[ 'ApiCentralNoticeQueryCampaign' ] = $apiDir . 'ApiCentralNoticeQueryCampaign.php'; $wgAutoloadClasses[ 'ApiCentralNoticeLogs' ] = $apiDir . 'ApiCentralNoticeLogs.php'; @@ -631,3 +633,5 @@ return true; } + +$wgHooks[ 'LoadExtensionSchemaUpdates' ][ ] = 'CNDatabasePatcher::applyUpdates'; \ No newline at end of file diff --git a/CentralNotice.sql b/CentralNotice.sql index 3f785b3..6acb2ae 100644 --- a/CentralNotice.sql +++ b/CentralNotice.sql @@ -1,38 +1,169 @@ -CREATE TABLE IF NOT EXISTS /*$wgDBprefix*/cn_notices ( - `not_id` int NOT NULL PRIMARY KEY auto_increment, +-- CentralNotice Schema Install File +-- Last Update: patch-centralnotice-2_3.sql +-- See documentation at http://www.mediawiki.org/wiki/Extension:CentralNotice/Database_schema + +CREATE TABLE IF NOT EXISTS /*_*/cn_notices ( + `not_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT, `not_name` varchar(255) NOT NULL, `not_start` char(14) NOT NULL, `not_end` char(14) NOT NULL, - `not_enabled` bool NOT NULL default '0', - `not_preferred` tinyint(1) NOT NULL default '1', - `not_locked` bool NOT NULL default '0', - `not_language` varchar(32) NOT NULL, - `not_project` varchar(255) NOT NULL + `not_enabled` tinyint(1) NOT NULL DEFAULT '0', + `not_preferred` tinyint(1) NOT NULL DEFAULT '0', + `not_locked` tinyint(1) NOT NULL DEFAULT '0', + `not_geo` tinyint(1) NOT NULL DEFAULT '0', + `not_buckets` tinyint(1) NOT NULL DEFAULT '1', + `not_weight` int(11) NOT NULL DEFAULT '100', + `not_mobile_carrier` tinyint(1) NOT NULL DEFAULT '0', + `not_archived` tinyint(1) NOT NULL DEFAULT '0' ) /*$wgDBTableOptions*/; -CREATE TABLE IF NOT EXISTS /*$wgDBprefix*/cn_assignments ( - `asn_id` int NOT NULL PRIMARY KEY auto_increment, - `not_id` int NOT NULL, - `tmp_id` int NOT NULL, - `tmp_weight` int NOT NULL +CREATE TABLE IF NOT EXISTS /*_*/cn_assignments ( + `asn_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, + `not_id` int(11) NOT NULL, + `tmp_id` int(11) NOT NULL, + `tmp_weight` int(11) NOT NULL, + `asn_bucket` tinyint(1) DEFAULT '0' ) /*$wgDBTableOptions*/; --- FIXME: make tmp_name UNIQUE -CREATE TABLE IF NOT EXISTS /*$wgDBprefix*/cn_templates ( - `tmp_id` int NOT NULL PRIMARY KEY auto_increment, - `tmp_name` varchar(255) default NULL, - `tmp_display_anon` bool NOT NULL DEFAULT 1, - `tmp_display_account` bool NOT NULL DEFAULT 1 +CREATE TABLE IF NOT EXISTS /*_*/cn_templates ( + `tmp_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, + `tmp_name` varchar(255) DEFAULT NULL, + `tmp_display_anon` tinyint(1) NOT NULL DEFAULT '1', + `tmp_display_account` tinyint(1) NOT NULL DEFAULT '1', + `tmp_fundraising` tinyint(1) NOT NULL DEFAULT '0', + `tmp_autolink` tinyint(1) NOT NULL DEFAULT '0', + `tmp_landing_pages` varchar(255) DEFAULT NULL, + `tmp_archived` tinyint(1) NOT NULL DEFAULT '0', + `tmp_category` tinyint NOT NULL DEFAULT '0', + `tmp_preview_sandbox` tinyint(1) NOT NULL DEFAULT '0' ) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tmp_name ON /*_*/cn_templates (tmp_name); -CREATE TABLE IF NOT EXISTS /*$wgDBprefix*/cn_notice_languages ( - `nl_notice_id` int unsigned NOT NULL, +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_languages ( + `nl_notice_id` int(10) unsigned NOT NULL, `nl_language` varchar(32) NOT NULL ) /*$wgDBTableOptions*/; -CREATE UNIQUE INDEX /*i*/nl_notice_id_language ON /*$wgDBprefix*/cn_notice_languages (nl_notice_id, nl_language); +CREATE UNIQUE INDEX /*i*/nl_notice_id_language ON /*_*/cn_notice_languages (nl_notice_id, nl_language); -CREATE TABLE IF NOT EXISTS /*$wgDBprefix*/cn_notice_projects ( - `np_notice_id` int unsigned NOT NULL, +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_projects ( + `np_notice_id` int(10) unsigned NOT NULL, `np_project` varchar(32) NOT NULL ) /*$wgDBTableOptions*/; -CREATE UNIQUE INDEX /*i*/np_notice_id_project ON /*$wgDBprefix*/cn_notice_projects (np_notice_id, np_project); +CREATE UNIQUE INDEX /*i*/np_notice_id_project ON /*_*/cn_notice_projects (np_notice_id, np_project); + +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_countries ( + `nc_notice_id` int(10) unsigned NOT NULL, + `nc_country` varchar(2) NOT NULL +) /*$wgDBTableOptions*/; +CREATE UNIQUE INDEX /*i*/nc_notice_id_country ON /*_*/cn_notice_countries (nc_notice_id, nc_country); + +CREATE TABLE IF NOT EXISTS /*_*/cn_template_mixins ( + `tmxn_id` int PRIMARY KEY AUTO_INCREMENT, + `tmp_id` int(11) NOT NULL, + `page_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tmxn_tmp_id ON /*_*/cn_template_mixins (tmp_id); +CREATE INDEX /*i*/tmxn_page_id ON /*_*/cn_template_mixins (page_id); + +CREATE TABLE IF NOT EXISTS /*_*/cn_known_devices ( + `dev_id` int PRIMARY KEY AUTO_INCREMENT, + `dev_name` varchar(255) NOT NULL, + `dev_display_label` varchar(255) binary NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/dev_name ON /*_*/cn_known_devices (dev_name); +INSERT INTO cn_known_devices VALUES (0, 'desktop', '{{int:centralnotice-devicetype-desktop}}'); + +CREATE TABLE IF NOT EXISTS /*_*/cn_template_devices ( + `tdev_id` int PRIMARY KEY AUTO_INCREMENT, + `tmp_id` int(11) NOT NULL, + `dev_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tdev_template_id ON /*_*/cn_template_devices (tmp_id); + +CREATE TABLE IF NOT EXISTS /*_*/cn_known_mobile_carriers ( + `mc_id` int PRIMARY KEY AUTO_INCREMENT, + `mc_name` varchar(255) NOT NULL, + `mc_display_label` varchar(255) binary NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/mc_name ON /*_*/cn_known_mobile_carriers (mc_name); + +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_mobile_carriers ( + `nmc_id` int PRIMARY KEY AUTO_INCREMENT, + `not_id` int NOT NULL, + `mc_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/nmc_not_id ON /*_*/cn_notice_mobile_carriers (not_id); +CREATE INDEX /*i*/nmc_carrier_id ON /*_*/cn_notice_mobile_carriers (mc_id); + +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_log ( + `notlog_id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + `notlog_timestamp` binary(14) NOT NULL, + `notlog_user_id` int(10) unsigned NOT NULL, + `notlog_action` enum('created','modified','removed') NOT NULL DEFAULT 'modified', + `notlog_not_id` int(10) unsigned NOT NULL, + `notlog_not_name` varchar(255) DEFAULT NULL, + `notlog_begin_projects` varchar(255) DEFAULT NULL, + `notlog_end_projects` varchar(255) DEFAULT NULL, + `notlog_begin_languages` text, + `notlog_end_languages` text, + `notlog_begin_countries` text, + `notlog_end_countries` text, + `notlog_begin_start` char(14) DEFAULT NULL, + `notlog_end_start` char(14) DEFAULT NULL, + `notlog_begin_end` char(14) DEFAULT NULL, + `notlog_end_end` char(14) DEFAULT NULL, + `notlog_begin_enabled` tinyint(1) DEFAULT NULL, + `notlog_end_enabled` tinyint(1) DEFAULT NULL, + `notlog_begin_preferred` tinyint(1) DEFAULT NULL, + `notlog_end_preferred` tinyint(1) DEFAULT NULL, + `notlog_begin_locked` tinyint(1) DEFAULT NULL, + `notlog_end_locked` tinyint(1) DEFAULT NULL, + `notlog_begin_geo` tinyint(1) DEFAULT NULL, + `notlog_end_geo` tinyint(1) DEFAULT NULL, + `notlog_begin_banners` text, + `notlog_end_banners` text, + `notlog_begin_buckets` tinyint(1) DEFAULT NULL, + `notlog_end_buckets` tinyint(1) DEFAULT NULL, + `notlog_begin_mobile_carrier` int DEFAULT NULL, + `notlog_end_mobile_carrier` int DEFAULT NULL, + `notlog_begin_weight` int DEFAULT NULL, + `notlog_end_weight` int DEFAULT NULL, + `notlog_begin_archived` tinyint DEFAULT NULL, + `notlog_end_archived` tinyint DEFAULT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/notlog_timestamp ON /*_*/cn_notice_log (notlog_timestamp); +CREATE INDEX /*i*/notlog_user_id ON /*_*/cn_notice_log (notlog_user_id, notlog_timestamp); +CREATE INDEX /*i*/notlog_not_id ON /*_*/cn_notice_log (notlog_not_id, notlog_timestamp); + +CREATE TABLE IF NOT EXISTS /*_*/cn_template_log ( + `tmplog_id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + `tmplog_timestamp` binary(14) NOT NULL, + `tmplog_user_id` int(10) unsigned NOT NULL, + `tmplog_action` enum('created','modified','removed') NOT NULL DEFAULT 'modified', + `tmplog_template_id` int(10) unsigned NOT NULL, + `tmplog_template_name` varchar(255) DEFAULT NULL, + `tmplog_begin_anon` tinyint(1) DEFAULT NULL, + `tmplog_end_anon` tinyint(1) DEFAULT NULL, + `tmplog_begin_account` tinyint(1) DEFAULT NULL, + `tmplog_end_account` tinyint(1) DEFAULT NULL, + `tmplog_begin_fundraising` tinyint(1) DEFAULT NULL, + `tmplog_end_fundraising` tinyint(1) DEFAULT NULL, + `tmplog_begin_autolink` tinyint(1) DEFAULT NULL, + `tmplog_end_autolink` tinyint(1) DEFAULT NULL, + `tmplog_begin_landingpages` varchar(255) DEFAULT NULL, + `tmplog_end_landingpages` varchar(255) DEFAULT NULL, + `tmplog_content_change` tinyint(1) DEFAULT '0', + `tmplog_begin_prioritylangs` text, + `tmplog_end_prioritylangs` text, + `tmplog_begin_archived` tinyint(1) DEFAULT NULL, + `tmplog_end_archived` tinyint(1) DEFAULT NULL, + `tmplog_begin_category` tinyint DEFAULT NULL, + `tmplog_end_category` tinyint DEFAULT NULL, + `tmplog_begin_preview_sandbox` tinyint(1) DEFAULT NULL, + `tmplog_end_preview_sandbox` tinyint(1) DEFAULT NULL, + `tmplog_begin_controller_mixin` varbinary(4096) DEFAULT NULL, + `tmplog_end_controller_mixin` varbinary(4096) DEFAULT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tmplog_timestamp ON /*_*/cn_template_log (tmplog_timestamp); +CREATE INDEX /*i*/tmplog_user_id ON /*_*/cn_template_log (tmplog_user_id, tmplog_timestamp); +CREATE INDEX /*i*/tmplog_template_id ON /*_*/cn_template_log (tmplog_template_id, tmplog_timestamp); diff --git a/patches/CNDatabasePatcher.php b/patches/CNDatabasePatcher.php new file mode 100644 index 0000000..25a9c88 --- /dev/null +++ b/patches/CNDatabasePatcher.php @@ -0,0 +1,105 @@ +<?php +/** + * @file + * @license GNU General Public Licence 2.0 or later + */ + +/** + * Maintenance helper class that updates the database schema when required. + * + * Apply patches with /maintenance/update.php + */ +class CNDatabasePatcher { + /** + * LoadExtensionSchemaUpdates hook handler + * This function makes sure that the database schema is up to date. + * + * @param $updater DatabaseUpdater|null + * @return bool + */ + public static function applyUpdates( $updater = null ) { + $base = __DIR__; + + if ( $updater->getDB()->getType() == 'mysql' ) { + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_notices', + $base . '/../CentralNotice.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_notices', 'not_preferred', + $base . '/patch-notice_preferred.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_notice_languages', + $base . '/patch-notice_languages.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_templates', 'tmp_display_anon', + $base . '/patch-template_settings.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_templates', 'tmp_fundraising', + $base . '/patch-template_fundraising.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_notice_countries', + $base . '/patch-notice_countries.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_notice_projects', + $base . '/patch-notice_projects.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_notice_log', + $base . '/patch-notice_log.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_template_log', + $base . '/patch-template_log.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_templates', 'tmp_autolink', + $base . '/patch-template_autolink.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_template_log', 'tmplog_begin_prioritylangs', + $base . '/patch-prioritylangs.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addField', 'cn_notices', 'not_buckets', + $base . '/patch-bucketing.sql', true + ) + ); + $updater->addExtensionUpdate( + array( + 'addTable', 'cn_template_mixins', + $base . '/patch-centralnotice-2_3.sql', true + ) + ); + } + return true; + } +} diff --git a/patches/patch-centralnotice-2_3.sql b/patches/patch-centralnotice-2_3.sql new file mode 100644 index 0000000..1b03f2c --- /dev/null +++ b/patches/patch-centralnotice-2_3.sql @@ -0,0 +1,80 @@ +-- Update of CentralNotice for planned 2.3 features +-- These include +-- * Mobile integration (carrier, device type selection) +-- * Ability to archive campaigns and banners +-- * Partial slot filling (campaign weighting) +-- * Stacked slots (controller mixins & optional banners) +-- * Tracking of used exported messages +-- * Support for banner categories beyond 'fundraising' and 'general' + +ALTER TABLE /*_*/cn_notices + DROP COLUMN `not_language`, + DROP COLUMN `not_project`, + ADD COLUMN `not_weight` int(11) NOT NULL DEFAULT '100', + ADD COLUMN `not_mobile_carrier` tinyint(1) NOT NULL DEFAULT '0', + ADD COLUMN `not_archived` tinyint(1) NOT NULL DEFAULT '0'; + +ALTER TABLE /*_*/cn_templates + ADD COLUMN `tmp_archived` tinyint(1) NOT NULL DEFAULT '0', + ADD COLUMN `tmp_category` tinyint NOT NULL DEFAULT '0', + ADD COLUMN `tmp_preview_sandbox` tinyint(1) NOT NULL DEFAULT '0'; + +CREATE TABLE IF NOT EXISTS /*_*/cn_template_mixins ( + `tmxn_id` int PRIMARY KEY AUTO_INCREMENT, + `tmp_id` int(11) NOT NULL, + `page_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tmxn_tmp_id ON /*_*/cn_template_mixins (tmp_id); +CREATE INDEX /*i*/tmxn_page_id ON /*_*/cn_template_mixins (page_id); + +CREATE TABLE IF NOT EXISTS /*_*/cn_known_devices ( + `dev_id` int PRIMARY KEY AUTO_INCREMENT, + `dev_name` varchar(255) NOT NULL, + `dev_display_label` varchar(255) binary NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/dev_name ON /*_*/cn_known_devices (dev_name); +INSERT INTO cn_known_devices VALUES (0, 'desktop', '{{int:centralnotice-devicetype-desktop}}'); + +CREATE TABLE IF NOT EXISTS /*_*/cn_template_devices ( + `tdev_id` int PRIMARY KEY AUTO_INCREMENT, + `tmp_id` int(11) NOT NULL, + `dev_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/tdev_template_id ON /*_*/cn_template_devices (tmp_id); +INSERT INTO cn_template_devices (tmp_id, dev_id) + SELECT tmp_id, dev_id + FROM cn_templates, cn_known_devices + WHERE dev_name='desktop'; + +CREATE TABLE IF NOT EXISTS /*_*/cn_known_mobile_carriers ( + `mc_id` int PRIMARY KEY AUTO_INCREMENT, + `mc_name` varchar(255) NOT NULL, + `mc_display_label` varchar(255) binary NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/mc_name ON /*_*/cn_known_mobile_carriers (mc_name); + +CREATE TABLE IF NOT EXISTS /*_*/cn_notice_mobile_carriers ( + `nmc_id` int PRIMARY KEY AUTO_INCREMENT, + `not_id` int NOT NULL, + `mc_id` int NOT NULL +) /*$wgDBTableOptions*/; +CREATE INDEX /*i*/nmc_not_id ON /*_*/cn_notice_mobile_carriers (not_id); +CREATE INDEX /*i*/nmc_carrier_id ON /*_*/cn_notice_mobile_carriers (mc_id); + +ALTER TABLE /*_*/cn_notice_log + ADD COLUMN `notlog_begin_mobile_carrier` int DEFAULT NULL, + ADD COLUMN `notlog_end_mobile_carrier` int DEFAULT NULL, + ADD COLUMN `notlog_begin_weight` int DEFAULT NULL, + ADD COLUMN `notlog_end_weight` int DEFAULT NULL, + ADD COLUMN `notlog_begin_archived` tinyint DEFAULT NULL, + ADD COLUMN `notlog_end_archived` tinyint DEFAULT NULL; + +ALTER TABLE /*_*/cn_template_log + ADD COLUMN `tmplog_begin_archived` tinyint(1) DEFAULT NULL, + ADD COLUMN `tmplog_end_archived` tinyint(1) DEFAULT NULL, + ADD COLUMN `tmplog_begin_category` tinyint DEFAULT NULL, + ADD COLUMN `tmplog_end_category` tinyint DEFAULT NULL, + ADD COLUMN `tmplog_begin_preview_sandbox` tinyint(1) DEFAULT NULL, + ADD COLUMN `tmplog_end_preview_sandbox` tinyint(1) DEFAULT NULL, + ADD COLUMN `tmplog_begin_controller_mixin` varbinary(4096) DEFAULT NULL, + ADD COLUMN `tmplog_end_controller_mixin` varbinary(4096) DEFAULT NULL; \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/55695 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I2576b0f3230591d84d228c44fee9b4ea0997d566 Gerrit-PatchSet: 3 Gerrit-Project: mediawiki/extensions/CentralNotice Gerrit-Branch: master Gerrit-Owner: Mwalker <mwal...@wikimedia.org> Gerrit-Reviewer: Adamw <awi...@wikimedia.org> Gerrit-Reviewer: Hashar <has...@free.fr> Gerrit-Reviewer: jenkins-bot _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
