From d0a0e9b417884bd0d9232e77e1ef81c670c82f3e Mon Sep 17 00:00:00 2001 From: "This, that and the other" Date: Sat, 13 Aug 2016 11:06:33 +1000 Subject: [PATCH] Add primary key to change_tag and tag_summary tables Based heavily on 43e386ca16411096bbbd7f14f9f4e15c5e268fe7. Bug: T123225 Change-Id: I33480f4016812259700979f1145099744bb451d4 --- includes/installer/MssqlUpdater.php | 2 ++ includes/installer/MysqlUpdater.php | 2 ++ includes/installer/OracleUpdater.php | 2 ++ includes/installer/PostgresUpdater.php | 6 +++++ includes/installer/SqliteUpdater.php | 2 ++ .../archives/patch-change_tag-ct_id.sql | 5 ++++ .../archives/patch-tag_summary-ts_id.sql | 5 ++++ .../mssql/archives/patch-change_tag-ct_id.sql | 4 +++ .../archives/patch-tag_summary-ts_id.sql | 4 +++ maintenance/mssql/tables.sql | 2 ++ .../archives/patch-change_tag-ct_id.sql | 6 +++++ .../archives/patch-tag_summary-ts_id.sql | 6 +++++ maintenance/oracle/tables.sql | 4 +++ maintenance/postgres/tables.sql | 14 ++++++++--- .../archives/patch-change_tag-ct_id.sql | 25 +++++++++++++++++++ .../archives/patch-tag_summary-ts_id.sql | 23 +++++++++++++++++ maintenance/tables.sql | 2 ++ 17 files changed, 110 insertions(+), 4 deletions(-) create mode 100644 maintenance/archives/patch-change_tag-ct_id.sql create mode 100644 maintenance/archives/patch-tag_summary-ts_id.sql create mode 100644 maintenance/mssql/archives/patch-change_tag-ct_id.sql create mode 100644 maintenance/mssql/archives/patch-tag_summary-ts_id.sql create mode 100644 maintenance/oracle/archives/patch-change_tag-ct_id.sql create mode 100644 maintenance/oracle/archives/patch-tag_summary-ts_id.sql create mode 100644 maintenance/sqlite/archives/patch-change_tag-ct_id.sql create mode 100644 maintenance/sqlite/archives/patch-tag_summary-ts_id.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 770d3bfa77..1175e9e7bd 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -92,6 +92,8 @@ class MssqlUpdater extends DatabaseUpdater { // 1.28 [ 'addIndex', 'recentchanges', 'rc_name_type_patrolled_timestamp', 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], + [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], + [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 693b6ff538..497f27350e 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -288,6 +288,8 @@ class MysqlUpdater extends DatabaseUpdater { 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], [ 'doRevisionPageRevIndexNonUnique' ], [ 'doNonUniquePlTlIl' ], + [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], + [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 8075aace43..e1e0d0f82d 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -116,6 +116,8 @@ class OracleUpdater extends DatabaseUpdater { // 1.28 [ 'addIndex', 'recentchanges', 'rc_name_type_patrolled_timestamp', 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], + [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], + [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index be94d91711..f3d2860144 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -68,6 +68,8 @@ class PostgresUpdater extends DatabaseUpdater { [ 'addSequence', 'archive', false, 'archive_ar_id_seq' ], [ 'addSequence', 'externallinks', false, 'externallinks_el_id_seq' ], [ 'addSequence', 'watchlist', false, 'watchlist_wl_id_seq' ], + [ 'addSequence', 'change_tag', false, 'change_tag_ct_id_seq' ], + [ 'addSequence', 'tag_summary', false, 'tag_summary_ts_id_seq' ], # new tables [ 'addTable', 'category', 'patch-category.sql' ], @@ -437,6 +439,10 @@ class PostgresUpdater extends DatabaseUpdater { // 1.28 [ 'addPgIndex', 'recentchanges', 'rc_name_type_patrolled_timestamp', '( rc_namespace, rc_type, rc_patrolled, rc_timestamp )' ], + [ 'addPgField', 'change_tag', 'ct_id', + "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq')" ], + [ 'addPgField', 'tag_summary', 'ts_id', + "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq')" ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 1c6e6eb9a8..388c0346ce 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -156,6 +156,8 @@ class SqliteUpdater extends DatabaseUpdater { // 1.28 [ 'addIndex', 'recentchanges', 'rc_name_type_patrolled_timestamp', 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], + [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], + [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], ]; } diff --git a/maintenance/archives/patch-change_tag-ct_id.sql b/maintenance/archives/patch-change_tag-ct_id.sql new file mode 100644 index 0000000000..7b986d6781 --- /dev/null +++ b/maintenance/archives/patch-change_tag-ct_id.sql @@ -0,0 +1,5 @@ +-- Primary key in change_tag table + +ALTER TABLE /*$wgDBprefix*/change_tag + ADD COLUMN ct_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, + ADD PRIMARY KEY (ct_id); diff --git a/maintenance/archives/patch-tag_summary-ts_id.sql b/maintenance/archives/patch-tag_summary-ts_id.sql new file mode 100644 index 0000000000..66fa72e15a --- /dev/null +++ b/maintenance/archives/patch-tag_summary-ts_id.sql @@ -0,0 +1,5 @@ +-- Primary key in tag_summary table + +ALTER TABLE /*$wgDBprefix*/tag_summary + ADD COLUMN ts_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, + ADD PRIMARY KEY (ts_id); diff --git a/maintenance/mssql/archives/patch-change_tag-ct_id.sql b/maintenance/mssql/archives/patch-change_tag-ct_id.sql new file mode 100644 index 0000000000..94cb9d1496 --- /dev/null +++ b/maintenance/mssql/archives/patch-change_tag-ct_id.sql @@ -0,0 +1,4 @@ +-- Primary key in change_tag table + +ALTER TABLE /*_*/change_tag ADD ct_id INT IDENTITY; +ALTER TABLE /*_*/change_tag ADD CONSTRAINT pk_change_tag PRIMARY KEY(ct_id) diff --git a/maintenance/mssql/archives/patch-tag_summary-ts_id.sql b/maintenance/mssql/archives/patch-tag_summary-ts_id.sql new file mode 100644 index 0000000000..d62bd35779 --- /dev/null +++ b/maintenance/mssql/archives/patch-tag_summary-ts_id.sql @@ -0,0 +1,4 @@ +-- Primary key in tag_summary table + +ALTER TABLE /*_*/tag_summary ADD ts_id INT IDENTITY; +ALTER TABLE /*_*/tag_summary ADD CONSTRAINT pk_tag_summary PRIMARY KEY(ts_id) diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index ea087a6012..beb972731c 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -1193,6 +1193,7 @@ CREATE TABLE /*_*/updatelog ( -- A table to track tags for revisions, logs and recent changes. CREATE TABLE /*_*/change_tag ( + ct_id int NOT NULL PRIMARY KEY IDENTITY, -- RCID for the change ct_rc_id int NULL REFERENCES /*_*/recentchanges(rc_id), -- LOGID for the change @@ -1215,6 +1216,7 @@ CREATE INDEX /*i*/change_tag_tag_id ON /*_*/change_tag (ct_tag,ct_rc_id,ct_rev_i -- Rollup table to pull a LIST of tags simply without ugly GROUP_CONCAT -- that only works on MySQL 4.1+ CREATE TABLE /*_*/tag_summary ( + ts_id int NOT NULL PRIMARY KEY IDENTITY, -- RCID for the change ts_rc_id int NULL REFERENCES /*_*/recentchanges(rc_id), -- LOGID for the change diff --git a/maintenance/oracle/archives/patch-change_tag-ct_id.sql b/maintenance/oracle/archives/patch-change_tag-ct_id.sql new file mode 100644 index 0000000000..6672872fea --- /dev/null +++ b/maintenance/oracle/archives/patch-change_tag-ct_id.sql @@ -0,0 +1,6 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.change_tag ADD ( +ct_id NUMBER NOT NULL, +); +ALTER TABLE &mw_prefix.change_tag ADD CONSTRAINT &mw_prefix.change_tag_pk PRIMARY KEY (ct_id); diff --git a/maintenance/oracle/archives/patch-tag_summary-ts_id.sql b/maintenance/oracle/archives/patch-tag_summary-ts_id.sql new file mode 100644 index 0000000000..91c3338318 --- /dev/null +++ b/maintenance/oracle/archives/patch-tag_summary-ts_id.sql @@ -0,0 +1,6 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.tag_summary ADD ( +ts_id NUMBER NOT NULL, +); +ALTER TABLE &mw_prefix.tag_summary ADD CONSTRAINT &mw_prefix.tag_summary_pk PRIMARY KEY (ts_id); diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index d9369c92cd..616b401a26 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -616,23 +616,27 @@ CREATE TABLE &mw_prefix.updatelog ( ALTER TABLE &mw_prefix.updatelog ADD CONSTRAINT &mw_prefix.updatelog_pk PRIMARY KEY (ul_key); CREATE TABLE &mw_prefix.change_tag ( + ct_id NUMBER NOT NULL, ct_rc_id NUMBER NULL, ct_log_id NUMBER NULL, ct_rev_id NUMBER NULL, ct_tag VARCHAR2(255) NOT NULL, ct_params BLOB NULL ); +ALTER TABLE &mw_prefix.change_tag ADD CONSTRAINT &mw_prefix.change_tag_pk PRIMARY KEY (ct_id); CREATE UNIQUE INDEX &mw_prefix.change_tag_u01 ON &mw_prefix.change_tag (ct_rc_id,ct_tag); CREATE UNIQUE INDEX &mw_prefix.change_tag_u02 ON &mw_prefix.change_tag (ct_log_id,ct_tag); CREATE UNIQUE INDEX &mw_prefix.change_tag_u03 ON &mw_prefix.change_tag (ct_rev_id,ct_tag); CREATE INDEX &mw_prefix.change_tag_i01 ON &mw_prefix.change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id); CREATE TABLE &mw_prefix.tag_summary ( + ts_id NUMBER NOT NULL, ts_rc_id NUMBER NULL, ts_log_id NUMBER NULL, ts_rev_id NUMBER NULL, ts_tags BLOB NOT NULL ); +ALTER TABLE &mw_prefix.tag_summary ADD CONSTRAINT &mw_prefix.tag_summary_pk PRIMARY KEY (ts_id); CREATE UNIQUE INDEX &mw_prefix.tag_summary_u01 ON &mw_prefix.tag_summary (ts_rc_id); CREATE UNIQUE INDEX &mw_prefix.tag_summary_u02 ON &mw_prefix.tag_summary (ts_log_id); CREATE UNIQUE INDEX &mw_prefix.tag_summary_u03 ON &mw_prefix.tag_summary (ts_rev_id); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 95c87c0cd1..227376187f 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -25,6 +25,8 @@ DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE; DROP SEQUENCE IF EXISTS archive_ar_id_seq CASCADE; DROP SEQUENCE IF EXISTS externallinks_el_id_seq CASCADE; DROP SEQUENCE IF EXISTS sites_site_id_seq CASCADE; +DROP SEQUENCE IF EXISTS change_tag_ct_id_seq CASCADE; +DROP SEQUENCE IF EXISTS tag_summary_ts_id_seq CASCADE; DROP FUNCTION IF EXISTS page_deleted() CASCADE; DROP FUNCTION IF EXISTS ts2_page_title() CASCADE; DROP FUNCTION IF EXISTS ts2_page_text() CASCADE; @@ -653,7 +655,9 @@ CREATE TABLE category ( CREATE UNIQUE INDEX category_title ON category(cat_title); CREATE INDEX category_pages ON category(cat_pages); +CREATE SEQUENCE change_tag_ct_id_seq; CREATE TABLE change_tag ( + ct_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq'), ct_rc_id INTEGER NULL, ct_log_id INTEGER NULL, ct_rev_id INTEGER NULL, @@ -665,11 +669,13 @@ CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag); CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag); CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id); +CREATE SEQUENCE tag_summary_ts_id_seq; CREATE TABLE tag_summary ( - ts_rc_id INTEGER NULL, - ts_log_id INTEGER NULL, - ts_rev_id INTEGER NULL, - ts_tags TEXT NOT NULL + ts_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq'), + ts_rc_id INTEGER NULL, + ts_log_id INTEGER NULL, + ts_rev_id INTEGER NULL, + ts_tags TEXT NOT NULL ); CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id); CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id); diff --git a/maintenance/sqlite/archives/patch-change_tag-ct_id.sql b/maintenance/sqlite/archives/patch-change_tag-ct_id.sql new file mode 100644 index 0000000000..1c0109433b --- /dev/null +++ b/maintenance/sqlite/archives/patch-change_tag-ct_id.sql @@ -0,0 +1,25 @@ +DROP TABLE IF EXISTS /*_*/change_tag_tmp; + +CREATE TABLE /*$wgDBprefix*/change_tag_tmp ( + ct_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + ct_rc_id int NULL, + ct_log_id int NULL, + ct_rev_id int NULL, + ct_tag varchar(255) NOT NULL, + ct_params blob NULL +); + +INSERT OR IGNORE INTO /*_*/change_tag_tmp ( + ct_rc_id, ct_log_id, ct_rev_id, ct_tag, ct_params ) + SELECT + ct_rc_id, ct_log_id, ct_rev_id, ct_tag, ct_params + FROM /*_*/change_tag; + +DROP TABLE /*_*/change_tag; + +ALTER TABLE /*_*/change_tag_tmp RENAME TO /*_*/change_tag; + +CREATE UNIQUE INDEX /*i*/change_tag_rc_tag ON /*_*/change_tag (ct_rc_id,ct_tag); +CREATE UNIQUE INDEX /*i*/change_tag_log_tag ON /*_*/change_tag (ct_log_id,ct_tag); +CREATE UNIQUE INDEX /*i*/change_tag_rev_tag ON /*_*/change_tag (ct_rev_id,ct_tag); +CREATE INDEX /*i*/change_tag_tag_id ON /*_*/change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id); diff --git a/maintenance/sqlite/archives/patch-tag_summary-ts_id.sql b/maintenance/sqlite/archives/patch-tag_summary-ts_id.sql new file mode 100644 index 0000000000..b6a1202846 --- /dev/null +++ b/maintenance/sqlite/archives/patch-tag_summary-ts_id.sql @@ -0,0 +1,23 @@ +DROP TABLE IF EXISTS /*_*/tag_summary_tmp; + +CREATE TABLE /*$wgDBprefix*/tag_summary_tmp ( + ts_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + ts_rc_id int NULL, + ts_log_id int NULL, + ts_rev_id int NULL, + ts_tags blob NOT NULL +); + +INSERT OR IGNORE INTO /*_*/tag_summary_tmp ( + ts_rc_id, ts_log_id, ts_rev_id, ts_tags ) + SELECT + ts_rc_id, ts_log_id, ts_rev_id, ts_tags + FROM /*_*/tag_summary; + +DROP TABLE /*_*/tag_summary; + +ALTER TABLE /*_*/tag_summary_tmp RENAME TO /*_*/tag_summary; + +CREATE UNIQUE INDEX /*i*/tag_summary_rc_id ON /*_*/tag_summary (ts_rc_id); +CREATE UNIQUE INDEX /*i*/tag_summary_log_id ON /*_*/tag_summary (ts_log_id); +CREATE UNIQUE INDEX /*i*/tag_summary_rev_id ON /*_*/tag_summary (ts_rev_id); diff --git a/maintenance/tables.sql b/maintenance/tables.sql index b5c14e30f4..03ce508275 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1472,6 +1472,7 @@ CREATE TABLE /*_*/updatelog ( -- A table to track tags for revisions, logs and recent changes. CREATE TABLE /*_*/change_tag ( + ct_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, -- RCID for the change ct_rc_id int NULL, -- LOGID for the change @@ -1494,6 +1495,7 @@ CREATE INDEX /*i*/change_tag_tag_id ON /*_*/change_tag (ct_tag,ct_rc_id,ct_rev_i -- Rollup table to pull a LIST of tags simply without ugly GROUP_CONCAT -- that only works on MySQL 4.1+ CREATE TABLE /*_*/tag_summary ( + ts_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, -- RCID for the change ts_rc_id int NULL, -- LOGID for the change -- 2.20.1