From 6039593649963c50cd0c73135ef3cb63939e9902 Mon Sep 17 00:00:00 2001 From: Amir Sarabadani Date: Fri, 4 May 2018 19:29:53 +0200 Subject: [PATCH] Introduce change_tag_def table Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag See T185355 for more information Bug: T193867 Bug: T185355 Change-Id: I4fd943589b3ed304471304c8beda15327a8edbcf --- includes/installer/MssqlUpdater.php | 3 +++ includes/installer/MysqlUpdater.php | 3 +++ includes/installer/OracleUpdater.php | 3 +++ includes/installer/PostgresUpdater.php | 3 +++ includes/installer/SqliteUpdater.php | 3 +++ maintenance/archives/patch-change_tag_def.sql | 16 ++++++++++++ .../mssql/archives/patch-change_tag_def.sql | 16 ++++++++++++ maintenance/mssql/tables.sql | 18 +++++++++++++ .../oracle/archives/patch-change_tag_def.sql | 25 ++++++++++++++++++ maintenance/oracle/tables.sql | 26 +++++++++++++++++++ .../archives/patch-change_tag_def.sql | 14 ++++++++++ maintenance/postgres/tables.sql | 13 ++++++++++ maintenance/tables.sql | 16 ++++++++++++ 13 files changed, 159 insertions(+) create mode 100644 maintenance/archives/patch-change_tag_def.sql create mode 100644 maintenance/mssql/archives/patch-change_tag_def.sql create mode 100644 maintenance/oracle/archives/patch-change_tag_def.sql create mode 100644 maintenance/postgres/archives/patch-change_tag_def.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 2e339997c4..e389135be6 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -128,6 +128,9 @@ class MssqlUpdater extends DatabaseUpdater { [ 'modifyField', 'recentchanges', 'rc_patrolled', 'patch-rc_patrolled_type.sql' ], [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', 'patch-recentchanges-nttindex.sql' ], + + // 1.32 + [ 'addTable', 'change_tag_def', 'patch-change_tag_def.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 60bb69fdeb..b4bb194195 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -348,6 +348,9 @@ class MysqlUpdater extends DatabaseUpdater { [ 'populateArchiveRevId' ], [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', 'patch-recentchanges-nttindex.sql' ], + + // 1.32 + [ 'addTable', 'change_tag_def', 'patch-change_tag_def.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 737b1728fa..b08e26f0ab 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -148,6 +148,9 @@ class OracleUpdater extends DatabaseUpdater { [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', 'patch-recentchanges-nttindex.sql' ], + // 1.32 + [ 'addTable', 'change_tag_def', 'patch-change_tag_def.sql' ], + // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 5026ae924c..c72e206a55 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -569,6 +569,9 @@ class PostgresUpdater extends DatabaseUpdater { [ 'setSequenceOwner', 'change_tag', 'ct_id', 'change_tag_ct_id_seq' ], [ 'setSequenceOwner', 'tag_summary', 'ts_id', 'tag_summary_ts_id_seq' ], [ 'setSequenceOwner', 'sites', 'site_id', 'sites_site_id_seq' ], + + // 1.32 + [ 'addTable', 'change_tag_def', 'patch-change_tag_def.sql' ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index b107fd11ec..76e1ca968f 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -212,6 +212,9 @@ class SqliteUpdater extends DatabaseUpdater { [ 'populateArchiveRevId' ], [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', 'patch-recentchanges-nttindex.sql' ], + + // 1.32 + [ 'addTable', 'change_tag_def', 'patch-change_tag_def.sql' ], ]; } diff --git a/maintenance/archives/patch-change_tag_def.sql b/maintenance/archives/patch-change_tag_def.sql new file mode 100644 index 0000000000..2b6b75f22d --- /dev/null +++ b/maintenance/archives/patch-change_tag_def.sql @@ -0,0 +1,16 @@ +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag + +CREATE TABLE /*_*/change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name varbinary(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined tinyint(1) NOT NULL, + -- Number of times this tag was used + ctd_count bigint unsigned NOT NULL default 0 +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/ctd_name ON /*_*/change_tag_def (ctd_name); +CREATE INDEX /*i*/ctd_count ON /*_*/change_tag_def (ctd_count); +CREATE INDEX /*i*/ctd_user_defined ON /*_*/change_tag_def (ctd_user_defined); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-change_tag_def.sql b/maintenance/mssql/archives/patch-change_tag_def.sql new file mode 100644 index 0000000000..1ddeb0174d --- /dev/null +++ b/maintenance/mssql/archives/patch-change_tag_def.sql @@ -0,0 +1,16 @@ +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag + +CREATE TABLE /*_*/change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id int NOT NULL CONSTRAINT PK_change_tag_def PRIMARY KEY IDENTITY, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name nvarchar(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined tinyint NOT NULL CONSTRAINT DF_ctd_user_defined DEFAULT 0, + -- Number of times this tag was used + ctd_count int NOT NULL CONSTRAINT DF_ctd_count DEFAULT 0 +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/ctd_name ON /*_*/change_tag_def (ctd_name); +CREATE INDEX /*i*/ctd_count ON /*_*/change_tag_def (ctd_count); +CREATE INDEX /*i*/ctd_user_defined ON /*_*/change_tag_def (ctd_user_defined); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 65ede90ea8..278b156eb2 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -503,6 +503,24 @@ CREATE UNIQUE INDEX /*i*/cat_title ON /*_*/category (cat_title); CREATE INDEX /*i*/cat_pages ON /*_*/category (cat_pages); +-- +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag +-- +CREATE TABLE /*_*/change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id int NOT NULL CONSTRAINT PK_change_tag_def PRIMARY KEY IDENTITY, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name nvarchar(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined tinyint NOT NULL CONSTRAINT DF_ctd_user_defined DEFAULT 0, + -- Number of times this tag was used + ctd_count int NOT NULL CONSTRAINT DF_ctd_count DEFAULT 0 +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/ctd_name ON /*_*/change_tag_def (ctd_name); +CREATE INDEX /*i*/ctd_count ON /*_*/change_tag_def (ctd_count); +CREATE INDEX /*i*/ctd_user_defined ON /*_*/change_tag_def (ctd_user_defined); + -- -- Track links to external URLs -- diff --git a/maintenance/oracle/archives/patch-change_tag_def.sql b/maintenance/oracle/archives/patch-change_tag_def.sql new file mode 100644 index 0000000000..6bf77723de --- /dev/null +++ b/maintenance/oracle/archives/patch-change_tag_def.sql @@ -0,0 +1,25 @@ +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag +CREATE SEQUENCE change_tag_def_ctd_id_seq; +CREATE TABLE &mw_prefix.change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id NUMBER NOT NULL, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name VARCHAR2(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined CHAR(1) DEFAULT '0' NOT NULL, + -- Number of times this tag was used + ctd_count NUMBER NOT NULL DEFAULT 0 +); + +ALTER TABLE &mw_prefix.change_tag_def ADD CONSTRAINT &mw_prefix.change_tag_def_pk PRIMARY KEY (ctd_id); +CREATE UNIQUE INDEX &mw_prefix.ctd_name ON &mw_prefix.change_tag_def (ctd_name); +CREATE INDEX &mw_prefix.ctd_count ON &mw_prefix.change_tag_def (ctd_count); +CREATE INDEX &mw_prefix.ctd_user_defined ON &mw_prefix.change_tag_def (ctd_user_defined); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.change_tag_def_seq_trg BEFORE INSERT ON &mw_prefix.change_tag_def + FOR EACH ROW WHEN (new.ctd_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(change_tag_def_ctd_id_seq.nextval, :new.ctd_id); +END; +/*$mw$*/ \ No newline at end of file diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 87039fb090..df74f649f5 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -436,6 +436,32 @@ BEGIN END; /*$mw$*/ +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag +CREATE SEQUENCE change_tag_def_ctd_id_seq; +CREATE TABLE &mw_prefix.change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id NUMBER NOT NULL, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name VARCHAR2(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined CHAR(1) DEFAULT '0' NOT NULL, + -- Number of times this tag was used + ctd_count NUMBER NOT NULL DEFAULT 0 +); + +ALTER TABLE &mw_prefix.change_tag_def ADD CONSTRAINT &mw_prefix.change_tag_def_pk PRIMARY KEY (ctd_id); +CREATE UNIQUE INDEX &mw_prefix.ctd_name ON &mw_prefix.change_tag_def (ctd_name); +CREATE INDEX &mw_prefix.ctd_count ON &mw_prefix.change_tag_def (ctd_count); +CREATE INDEX &mw_prefix.ctd_user_defined ON &mw_prefix.change_tag_def (ctd_user_defined); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.change_tag_def_seq_trg BEFORE INSERT ON &mw_prefix.change_tag_def + FOR EACH ROW WHEN (new.ctd_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(change_tag_def_ctd_id_seq.nextval, :new.ctd_id); +END; +/*$mw$*/ + CREATE TABLE &mw_prefix.langlinks ( ll_from NUMBER NOT NULL, ll_lang VARCHAR2(20), diff --git a/maintenance/postgres/archives/patch-change_tag_def.sql b/maintenance/postgres/archives/patch-change_tag_def.sql new file mode 100644 index 0000000000..c16597d643 --- /dev/null +++ b/maintenance/postgres/archives/patch-change_tag_def.sql @@ -0,0 +1,14 @@ +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag + +CREATE SEQUENCE change_tag_def_ctd_id_seq; +CREATE TABLE change_tag_def ( + ctd_id int NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_def_ctd_id_seq'), + ctd_name TEXT NOT NULL, + ctd_user_defined SMALLINT NOT NULL DEFAULT 0, + ctd_count INTEGER NOT NULL DEFAULT 0 +); +ALTER SEQUENCE change_tag_def_ctd_id_seq OWNED BY change_tag_def.ctd_id; + +CREATE UNIQUE INDEX ctd_name ON change_tag_def (ctd_name); +CREATE INDEX ctd_count ON change_tag_def (ctd_count); +CREATE INDEX ctd_user_defined ON change_tag_def (ctd_user_defined); \ No newline at end of file diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 53026acff9..1a53359a99 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -354,6 +354,19 @@ CREATE TABLE categorylinks ( CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); +CREATE SEQUENCE change_tag_def_ctd_id_seq; +CREATE TABLE change_tag_def ( + ctd_id int NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_def_ctd_id_seq'), + ctd_name TEXT NOT NULL, + ctd_user_defined SMALLINT NOT NULL DEFAULT 0, + ctd_count INTEGER NOT NULL DEFAULT 0 +); +ALTER SEQUENCE change_tag_def_ctd_id_seq OWNED BY change_tag_def.ctd_id; + +CREATE UNIQUE INDEX ctd_name ON change_tag_def (ctd_name); +CREATE INDEX ctd_count ON change_tag_def (ctd_count); +CREATE INDEX ctd_user_defined ON change_tag_def (ctd_user_defined); + CREATE SEQUENCE externallinks_el_id_seq; CREATE TABLE externallinks ( el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'), diff --git a/maintenance/tables.sql b/maintenance/tables.sql index df3264a43c..2f1e44c303 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1968,4 +1968,20 @@ CREATE UNIQUE INDEX /*i*/site_ids_type ON /*_*/site_identifiers (si_type, si_key CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site); CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key); +-- Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag +CREATE TABLE /*_*/change_tag_def ( + -- Numerical ID of the tag (ct_tag_id refers to this) + ctd_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + -- Symbolic name of the tag (what would previously be put in ct_tag) + ctd_name varbinary(255) NOT NULL, + -- Whether this tag was defined manually by a privileged user using Special:Tags + ctd_user_defined tinyint(1) NOT NULL, + -- Number of times this tag was used + ctd_count bigint unsigned NOT NULL default 0 +) /*$wgDBTableOptions*/; + +CREATE UNIQUE INDEX /*i*/ctd_name ON /*_*/change_tag_def (ctd_name); +CREATE INDEX /*i*/ctd_count ON /*_*/change_tag_def (ctd_count); +CREATE INDEX /*i*/ctd_user_defined ON /*_*/change_tag_def (ctd_user_defined); + -- vim: sw=2 sts=2 et -- 2.20.1