Mwalker has uploaded a new change for review.
https://gerrit.wikimedia.org/r/52913
Change subject: CentralNotice v2.3 DB Schema Updates
......................................................................
CentralNotice v2.3 DB Schema Updates
Pushing the majority of SQL changes at once so they are available
at code development/deployment time.
Major features being deployed include:
* Mobile integration (carrier, device type selection)
* Ability to archive campaigns and banners
* Partial slot filling (campaign weighting)
* Stacked slots (controller mixins & optional banners)
Change-Id: If91202ff409d6c882f7ae8d47c167552b5d62107
---
M CentralNotice.php
M CentralNotice.sql
A patches/DbPatches.php
A patches/patch-centralnotice-2_3.sql
4 files changed, 327 insertions(+), 26 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/CentralNotice
refs/changes/13/52913/1
diff --git a/CentralNotice.php b/CentralNotice.php
index 32d475a..27c29d4 100644
--- a/CentralNotice.php
+++ b/CentralNotice.php
@@ -21,7 +21,7 @@
'Matthew Walker',
'Adam Roses Wight',
),
- 'version' => '2.2',
+ 'version' => '2.3',
'url' =>
'https://www.mediawiki.org/wiki/Extension:CentralNotice',
'descriptionmsg' => 'centralnotice-desc',
);
@@ -260,7 +260,6 @@
$wgAPIListModules[ 'centralnoticelogs' ] = 'ApiCentralNoticeLogs';
// Register hooks
- $wgHooks[ 'LoadExtensionSchemaUpdates' ][ ] = 'efCentralNoticeSchema';
$wgHooks[ 'UnitTestsList' ][ ] = 'efCentralNoticeUnitTests';
// If CentralNotice banners should be shown on this wiki, load the
components we need for
@@ -624,3 +623,6 @@
return true;
}
+
+// Hook definitions not present in this file
+require_once( __DIR__ . '/patches/DbPatches.php' );
diff --git a/CentralNotice.sql b/CentralNotice.sql
index 3f785b3..375f137 100644
--- a/CentralNotice.sql
+++ b/CentralNotice.sql
@@ -1,38 +1,164 @@
-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_languages
(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 (
+ `tmp_id` int(11) NOT NULL PRIMARY KEY,
+ `page_id` int NOT NULL
+) /*$wgDBTableOptions*/;
+CREATE INDEX /*i*/tmxn_id ON /*_*/cn_template_mixins (page_id);
+
+CREATE TABLE IF NOT EXISTS /*_*/cn_known_devices (
+ `dev_id` int NOT NULL 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 (
+ `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 NOT NULL PRIMARY KEY AUTO_INCREMENT,
+ `mc_name` varchar(255) NOT NULL,
+ `mc_friendly_name` 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 (
+ `not_id` int NOT NULL PRIMARY KEY,
+ `mc_id` int NOT NULL
+) /*$wgDBTableOptions*/;
+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/DbPatches.php b/patches/DbPatches.php
new file mode 100644
index 0000000..e7c0d3b
--- /dev/null
+++ b/patches/DbPatches.php
@@ -0,0 +1,98 @@
+<?php
+/**
+ * @file
+ * @license GNU General Public Licence 2.0 or later
+ */
+
+/**
+ * LoadExtensionSchemaUpdates hook handler
+ * This function makes sure that the database schema is up to date.
+ *
+ * @param $updater DatabaseUpdater|null
+ * @return bool
+ */
+$wgHooks[ 'LoadExtensionSchemaUpdates' ][ ] = function( $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_controller_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..d1faac6
--- /dev/null
+++ b/patches/patch-centralnotice-2_3.sql
@@ -0,0 +1,75 @@
+-- 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 (
+ `tmp_id` int(11) NOT NULL PRIMARY KEY,
+ `page_id` int NOT NULL
+) /*$wgDBTableOptions*/;
+CREATE INDEX /*i*/tmxn_id ON /*_*/cn_template_mixins (page_id);
+
+CREATE TABLE IF NOT EXISTS /*_*/cn_known_devices (
+ `dev_id` int NOT NULL 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 (
+ `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_header_value='desktop';
+
+CREATE TABLE IF NOT EXISTS /*_*/cn_known_mobile_carriers (
+ `mc_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
+ `mc_name` varchar(255) NOT NULL,
+ `mc_friendly_name` 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 (
+ `not_id` int NOT NULL PRIMARY KEY,
+ `mc_id` int NOT NULL
+) /*$wgDBTableOptions*/;
+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/52913
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: If91202ff409d6c882f7ae8d47c167552b5d62107
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/extensions/CentralNotice
Gerrit-Branch: master
Gerrit-Owner: Mwalker <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
