From 43e386ca16411096bbbd7f14f9f4e15c5e268fe7 Mon Sep 17 00:00:00 2001 From: addshore Date: Wed, 17 Feb 2016 23:16:20 +0000 Subject: [PATCH] Add id field to watchlist db table Bug: T125990 Change-Id: I3ce3a736d51bc06fe40fd773f079e694039b4f3e --- includes/installer/MssqlUpdater.php | 1 + includes/installer/MysqlUpdater.php | 1 + includes/installer/OracleUpdater.php | 1 + includes/installer/PostgresUpdater.php | 5 ++++ includes/installer/SqliteUpdater.php | 1 + .../archives/patch-watchlist-wl_id.sql | 5 ++++ .../mssql/archives/patch-watchlist-wl_id.sql | 2 ++ maintenance/mssql/tables.sql | 1 + .../oracle/archives/patch-watchlist-wl_id.sql | 6 +++++ maintenance/oracle/tables.sql | 2 ++ maintenance/postgres/tables.sql | 3 +++ .../sqlite/archives/patch-watchlist-wl_id.sql | 23 +++++++++++++++++++ maintenance/tables.sql | 1 + 13 files changed, 52 insertions(+) create mode 100644 maintenance/archives/patch-watchlist-wl_id.sql create mode 100644 maintenance/mssql/archives/patch-watchlist-wl_id.sql create mode 100644 maintenance/oracle/archives/patch-watchlist-wl_id.sql create mode 100644 maintenance/sqlite/archives/patch-watchlist-wl_id.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index b3675f82cb..bdaf4c815d 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -68,6 +68,7 @@ class MssqlUpdater extends DatabaseUpdater { // 1.27 [ 'dropTable', 'msg_resource_links' ], [ 'dropTable', 'msg_resource' ], + [ 'addField', 'watchlist', 'wl_id', 'patch-watchlist-wl_id.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 57eaffed26..b09f3a6f91 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -279,6 +279,7 @@ class MysqlUpdater extends DatabaseUpdater { [ 'dropTable', 'msg_resource_links' ], [ 'dropTable', 'msg_resource' ], [ 'addTable', 'bot_passwords', 'patch-bot_passwords.sql' ], + [ 'addField', 'watchlist', 'wl_id', 'patch-watchlist-wl_id.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 02e59f5543..334256b784 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -111,6 +111,7 @@ class OracleUpdater extends DatabaseUpdater { // 1.27 [ 'dropTable', 'msg_resource_links' ], [ 'dropTable', 'msg_resource' ], + [ 'addField', 'watchlist', 'wl_id', 'patch-watchlist-wl_id.sql' ], // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 038c9531b9..a3b50ac565 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -67,6 +67,7 @@ class PostgresUpdater extends DatabaseUpdater { [ 'addSequence', 'filearchive', 'fa_id', 'filearchive_fa_id_seq' ], [ 'addSequence', 'archive', false, 'archive_ar_id_seq' ], [ 'addSequence', 'externallinks', false, 'externallinks_el_id_seq' ], + [ 'addSequence', 'watchlist', false, 'watchlist_wl_id_seq' ], # new tables [ 'addTable', 'category', 'patch-category.sql' ], @@ -428,6 +429,10 @@ class PostgresUpdater extends DatabaseUpdater { // 1.27 [ 'dropTable', 'msg_resource_links' ], [ 'dropTable', 'msg_resource' ], + [ + 'addPgField', 'watchlist', 'wl_id', + "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('watchlist_wl_id_seq')" + ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 7db4f1af4e..99ab4e506c 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -148,6 +148,7 @@ class SqliteUpdater extends DatabaseUpdater { [ 'dropTable', 'msg_resource_links' ], [ 'dropTable', 'msg_resource' ], [ 'addTable', 'bot_passwords', 'patch-bot_passwords.sql' ], + [ 'addField', 'watchlist', 'wl_id', 'patch-watchlist-wl_id.sql' ], ]; } diff --git a/maintenance/archives/patch-watchlist-wl_id.sql b/maintenance/archives/patch-watchlist-wl_id.sql new file mode 100644 index 0000000000..a73e514c8d --- /dev/null +++ b/maintenance/archives/patch-watchlist-wl_id.sql @@ -0,0 +1,5 @@ +-- Primary key in watchlist + +ALTER TABLE /*$wgDBprefix*/watchlist + ADD COLUMN wl_id int unsigned NOT NULL AUTO_INCREMENT FIRST, + ADD PRIMARY KEY (wl_id); diff --git a/maintenance/mssql/archives/patch-watchlist-wl_id.sql b/maintenance/mssql/archives/patch-watchlist-wl_id.sql new file mode 100644 index 0000000000..b71f817af2 --- /dev/null +++ b/maintenance/mssql/archives/patch-watchlist-wl_id.sql @@ -0,0 +1,2 @@ +ALTER TABLE /*_*/watchlist ADD wl_id INT IDENTITY; +ALTER TABLE /*_*/watchlist ADD CONSTRAINT pk_watchlist PRIMARY KEY(wl_id) diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 0e58563c69..86bd7356d0 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -847,6 +847,7 @@ CREATE INDEX /*i*/rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp CREATE TABLE /*_*/watchlist ( + wl_id int NOT NULL PRIMARY KEY IDENTITY, -- Key to user.user_id wl_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE, diff --git a/maintenance/oracle/archives/patch-watchlist-wl_id.sql b/maintenance/oracle/archives/patch-watchlist-wl_id.sql new file mode 100644 index 0000000000..4f7180d510 --- /dev/null +++ b/maintenance/oracle/archives/patch-watchlist-wl_id.sql @@ -0,0 +1,6 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.watchlist ADD ( +wl_id NUMBER NOT NULL, +); +ALTER TABLE &mw_prefix.watchlist ADD CONSTRAINT &mw_prefix.watchlist_pk PRIMARY KEY (wl_id); diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 9a70b8777e..27c3030019 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -436,11 +436,13 @@ CREATE INDEX &mw_prefix.recentchanges_i06 ON &mw_prefix.recentchanges (rc_namesp CREATE INDEX &mw_prefix.recentchanges_i07 ON &mw_prefix.recentchanges (rc_user_text, rc_timestamp); CREATE TABLE &mw_prefix.watchlist ( + wl_id NUMBER NOT NULL, wl_user NUMBER NOT NULL, wl_namespace NUMBER DEFAULT 0 NOT NULL, wl_title VARCHAR2(255) NOT NULL, wl_notificationtimestamp TIMESTAMP(6) WITH TIME ZONE ); +ALTER TABLE &mw_prefix.watchlist ADD CONSTRAINT &mw_prefix.watchlist_pk PRIMARY KEY (wl_id); ALTER TABLE &mw_prefix.watchlist ADD CONSTRAINT &mw_prefix.watchlist_fk1 FOREIGN KEY (wl_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.watchlist_u01 ON &mw_prefix.watchlist (wl_user, wl_namespace, wl_title); CREATE INDEX &mw_prefix.watchlist_i01 ON &mw_prefix.watchlist (wl_namespace, wl_title); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index c9f049b61b..61cb19817e 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -18,6 +18,7 @@ DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE; DROP SEQUENCE IF EXISTS filearchive_fa_id_seq CASCADE; DROP SEQUENCE IF EXISTS uploadstash_us_id_seq CASCADE; DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE; +DROP SEQUENCE IF EXISTS watchlist_wl_id_seq CASCADE; DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE; DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE; DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE; @@ -447,7 +448,9 @@ CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_times CREATE INDEX rc_ip ON recentchanges (rc_ip); +CREATE SEQUENCE watchlist_wl_id_seq; CREATE TABLE watchlist ( + wl_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('watchlist_wl_id_seq'), wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, wl_namespace SMALLINT NOT NULL DEFAULT 0, wl_title TEXT NOT NULL, diff --git a/maintenance/sqlite/archives/patch-watchlist-wl_id.sql b/maintenance/sqlite/archives/patch-watchlist-wl_id.sql new file mode 100644 index 0000000000..771f9b7e34 --- /dev/null +++ b/maintenance/sqlite/archives/patch-watchlist-wl_id.sql @@ -0,0 +1,23 @@ +DROP TABLE IF EXISTS /*_*/watchlist_tmp; + +CREATE TABLE /*$wgDBprefix*/watchlist_tmp ( + wl_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + wl_user INTEGER NOT NULL, + wl_namespace INTEGER NOT NULL default 0, + wl_title TEXT NOT NULL default '', + wl_notificationtimestamp BLOB +); + +INSERT OR IGNORE INTO /*_*/watchlist_tmp ( + wl_user, wl_namespace, wl_title, wl_notificationtimestamp ) + SELECT + wl_user, wl_namespace, wl_title, wl_notificationtimestamp + FROM /*_*/watchlist; + +DROP TABLE /*_*/watchlist; + +ALTER TABLE /*_*/watchlist_tmp RENAME TO /*_*/watchlist; + +CREATE UNIQUE INDEX /*i*/wl_user ON /*_*/watchlist (wl_user, wl_namespace, wl_title); +CREATE INDEX /*i*/namespace_title ON /*_*/watchlist (wl_namespace, wl_title); +CREATE INDEX /*i*/wl_user_notificationtimestamp ON /*_*/watchlist (wl_user, wl_notificationtimestamp); diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 743b9be3b9..7942687579 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1139,6 +1139,7 @@ CREATE INDEX /*i*/rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp CREATE TABLE /*_*/watchlist ( + wl_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Key to user.user_id wl_user int unsigned NOT NULL, -- 2.20.1