From 267d99fa85434c3f26b7c5223cd46c29dedff4e5 Mon Sep 17 00:00:00 2001 From: Reedy Date: Wed, 4 Jul 2018 20:13:07 +0100 Subject: [PATCH] Convert numerous UNIQUE INDEX to PRIMARY KEY MySQL, SQLite, PostgreSQL and MSSQL done with transitional patches. One additional duplicate index removed from PostgreSQL schema. Having a PK is essential to do maintenance, specially on large tasks. By not having a PK it is impossible to add it in a safe way if not done directly on the master. Having a PK means that we can easily change the PK into another one if needed in the future. The ones we chose might not be the best ones, but will allow us to get them changed. Bug: T198811 Change-Id: I6b96a427687085c6c24bcd759c9739f81288b919 --- RELEASE-NOTES-1.32 | 3 ++ includes/installer/MssqlUpdater.php | 4 +++ includes/installer/MysqlUpdater.php | 7 +++++ includes/installer/PostgresUpdater.php | 4 +++ includes/installer/SqliteUpdater.php | 7 +++++ .../archives/patch-interwiki-fix-pk.sql | 1 + .../archives/patch-page_props-fix-pk.sql | 1 + .../patch-protected_titles-fix-pk.sql | 1 + .../patch-site_identifiers-fix-pk.sql | 1 + .../mssql/archives/patch-interwiki-pk.sql | 2 ++ .../mssql/archives/patch-page_props-pk.sql | 2 ++ .../archives/patch-protected_titles-pk.sql | 2 ++ .../archives/patch-site_identifiers-pk.sql | 2 ++ maintenance/mssql/tables.sql | 18 +++++------- .../postgres/archives/patch-interwiki-pk.sql | 3 ++ .../archives/patch-protected_titles-pk.sql | 2 ++ .../archives/patch-site_identifiers-pk.sql | 2 ++ maintenance/postgres/tables.sql | 14 ++++----- .../archives/patch-interwiki-fix-pk.sql | 29 +++++++++++++++++++ .../archives/patch-page_props-fix-pk.sql | 18 ++++++++++++ .../patch-protected_titles-fix-pk.sql | 21 ++++++++++++++ .../patch-site_identifiers-fix-pk.sql | 22 ++++++++++++++ maintenance/tables.sql | 19 ++++++------ 23 files changed, 159 insertions(+), 26 deletions(-) create mode 100644 maintenance/archives/patch-interwiki-fix-pk.sql create mode 100644 maintenance/archives/patch-page_props-fix-pk.sql create mode 100644 maintenance/archives/patch-protected_titles-fix-pk.sql create mode 100644 maintenance/archives/patch-site_identifiers-fix-pk.sql create mode 100644 maintenance/mssql/archives/patch-interwiki-pk.sql create mode 100644 maintenance/mssql/archives/patch-page_props-pk.sql create mode 100644 maintenance/mssql/archives/patch-protected_titles-pk.sql create mode 100644 maintenance/mssql/archives/patch-site_identifiers-pk.sql create mode 100644 maintenance/postgres/archives/patch-interwiki-pk.sql create mode 100644 maintenance/postgres/archives/patch-protected_titles-pk.sql create mode 100644 maintenance/postgres/archives/patch-site_identifiers-pk.sql create mode 100644 maintenance/sqlite/archives/patch-interwiki-fix-pk.sql create mode 100644 maintenance/sqlite/archives/patch-page_props-fix-pk.sql create mode 100644 maintenance/sqlite/archives/patch-protected_titles-fix-pk.sql create mode 100644 maintenance/sqlite/archives/patch-site_identifiers-fix-pk.sql diff --git a/RELEASE-NOTES-1.32 b/RELEASE-NOTES-1.32 index 179e9700b3..e5b5b8f9f0 100644 --- a/RELEASE-NOTES-1.32 +++ b/RELEASE-NOTES-1.32 @@ -236,6 +236,9 @@ because of Phabricator reports. * String type for $lang of DifferenceEngine::setTextLanguage is deprecated. === Other changes in 1.32 === +* (T198811) The following tables have had their UNIQUE indexes turned into proper + PRIMARY KEYs for increased maintainability: interwiki, page_props, + protected_titles and site_identifiers. * … == Compatibility == diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 03b9c488b0..0a66b9af2e 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -140,6 +140,10 @@ class MssqlUpdater extends DatabaseUpdater { [ 'populateContentTables' ], [ 'addIndex', 'logging', 'log_type_action', 'patch-logging-log-type-action-index.sql' ], [ 'dropIndex', 'logging', 'type_action', 'patch-logging-drop-type-action-index.sql' ], + [ 'addIndex', 'interwiki', 'PRIMARY', 'patch-interwiki-pk.sql' ], + [ 'addIndex', 'protected_titles', 'PRIMARY', 'patch-protected_titles-pk.sql' ], + [ 'addIndex', 'page_props', 'PRIMARY', 'patch-page_props-pk.sql' ], + [ 'addIndex', 'site_identifiers', 'PRIMARY', 'patch-site_identifiers-pk.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index f8b5408a5b..3e1d19626e 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -360,6 +360,13 @@ class MysqlUpdater extends DatabaseUpdater { [ 'populateContentTables' ], [ 'addIndex', 'logging', 'log_type_action', 'patch-logging-log-type-action-index.sql' ], [ 'dropIndex', 'logging', 'type_action', 'patch-logging-drop-type-action-index.sql' ], + [ 'renameIndex', 'interwiki', 'iw_prefix', 'PRIMARY', false, 'patch-interwiki-fix-pk.sql' ], + [ 'renameIndex', 'page_props', 'pp_page_propname', 'PRIMARY', false, + 'patch-page_props-fix-pk.sql' ], + [ 'renameIndex', 'protected_titles', 'pt_namespace_title', 'PRIMARY', false, + 'patch-protected_titles-fix-pk.sql' ], + [ 'renameIndex', 'site_identifiers', 'site_ids_type', 'PRIMARY', false, + 'patch-site_identifiers-fix-pk.sql' ], ]; } diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 18ef881e8d..932c9412fa 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -586,6 +586,10 @@ class PostgresUpdater extends DatabaseUpdater { [ 'dropPgIndex', 'archive', 'ar_revid' ], // Probably doesn't exist, but do it anyway. [ 'populateContentTables' ], [ 'addPgIndex', 'logging', 'log_type_action', '( log_type, log_action, log_timestamp )' ], + [ 'dropPgIndex', 'page_props', 'page_props_propname' ], + [ 'addIndex', 'interwiki', 'interwiki_pkey', 'patch-interwiki-pk.sql' ], + [ 'addIndex', 'protected_titles', 'protected_titles_pkey', 'patch-protected_titles-pk.sql' ], + [ 'addIndex', 'site_identifiers', 'site_identifiers_pkey', 'patch-site_identifiers-pk.sql' ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 1139cbf6fb..80eb84330b 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -224,6 +224,13 @@ class SqliteUpdater extends DatabaseUpdater { [ 'populateContentTables' ], [ 'addIndex', 'logging', 'log_type_action', 'patch-logging-log-type-action-index.sql' ], [ 'dropIndex', 'logging', 'type_action', 'patch-logging-drop-type-action-index.sql' ], + [ 'renameIndex', 'interwiki', 'iw_prefix', 'PRIMARY', false, 'patch-interwiki-fix-pk.sql' ], + [ 'renameIndex', 'page_props', 'pp_page_propname', 'PRIMARY', false, + 'patch-page_props-fix-pk.sql' ], + [ 'renameIndex', 'protected_titles', 'pt_namespace_title', 'PRIMARY', false, + 'patch-protected_titles-fix-pk.sql' ], + [ 'renameIndex', 'site_identifiers', 'site_ids_type', 'PRIMARY', false, + 'patch-site_identifiers-fix-pk.sql' ], ]; } diff --git a/maintenance/archives/patch-interwiki-fix-pk.sql b/maintenance/archives/patch-interwiki-fix-pk.sql new file mode 100644 index 0000000000..f0e3ede9c6 --- /dev/null +++ b/maintenance/archives/patch-interwiki-fix-pk.sql @@ -0,0 +1 @@ +ALTER TABLE /*_*/interwiki DROP KEY /*i*/iw_prefix, ADD PRIMARY KEY (iw_prefix); diff --git a/maintenance/archives/patch-page_props-fix-pk.sql b/maintenance/archives/patch-page_props-fix-pk.sql new file mode 100644 index 0000000000..6cbf50bfc1 --- /dev/null +++ b/maintenance/archives/patch-page_props-fix-pk.sql @@ -0,0 +1 @@ +ALTER TABLE /*_*/page_props DROP KEY /*i*/pp_page_propname, ADD PRIMARY KEY (pp_page,pp_propname); diff --git a/maintenance/archives/patch-protected_titles-fix-pk.sql b/maintenance/archives/patch-protected_titles-fix-pk.sql new file mode 100644 index 0000000000..fd4146d9f4 --- /dev/null +++ b/maintenance/archives/patch-protected_titles-fix-pk.sql @@ -0,0 +1 @@ +ALTER TABLE /*_*/protected_titles DROP KEY /*i*/pt_namespace_title, ADD PRIMARY KEY (pt_namespace,pt_title); diff --git a/maintenance/archives/patch-site_identifiers-fix-pk.sql b/maintenance/archives/patch-site_identifiers-fix-pk.sql new file mode 100644 index 0000000000..05f7931766 --- /dev/null +++ b/maintenance/archives/patch-site_identifiers-fix-pk.sql @@ -0,0 +1 @@ +ALTER TABLE /*_*/site_identifiers DROP KEY /*i*/site_ids_type, ADD PRIMARY KEY (si_type,si_key); diff --git a/maintenance/mssql/archives/patch-interwiki-pk.sql b/maintenance/mssql/archives/patch-interwiki-pk.sql new file mode 100644 index 0000000000..e989c44920 --- /dev/null +++ b/maintenance/mssql/archives/patch-interwiki-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX IF EXISTS /*i*/iw_prefix ON /*_*/interwiki; +ALTER TABLE /*_*/interwiki ADD CONSTRAINT PK_interwiki PRIMARY KEY(iw_prefix); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-page_props-pk.sql b/maintenance/mssql/archives/patch-page_props-pk.sql new file mode 100644 index 0000000000..06154405d6 --- /dev/null +++ b/maintenance/mssql/archives/patch-page_props-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX IF EXISTS /*i*/pp_page_propname ON /*_*/page_props; +ALTER TABLE /*_*/page_props ADD CONSTRAINT PK_page_props PRIMARY KEY(pp_page,pp_propname); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-protected_titles-pk.sql b/maintenance/mssql/archives/patch-protected_titles-pk.sql new file mode 100644 index 0000000000..17e059ec49 --- /dev/null +++ b/maintenance/mssql/archives/patch-protected_titles-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX IF EXISTS /*i*/pt_namespace_title ON /*_*/protected_titles; +ALTER TABLE /*_*/protected_titles ADD CONSTRAINT PK_protected_titles PRIMARY KEY(pt_namespace,pt_title); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-site_identifiers-pk.sql b/maintenance/mssql/archives/patch-site_identifiers-pk.sql new file mode 100644 index 0000000000..fa12f7072e --- /dev/null +++ b/maintenance/mssql/archives/patch-site_identifiers-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX IF EXISTS /*i*/site_ids_type ON /*_*/site_identifiers; +ALTER TABLE /*_*/site_identifiers ADD CONSTRAINT PK_site_identifiers PRIMARY KEY(si_type, si_key); \ No newline at end of file diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 3ab7d8911a..8f30e8000a 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -1098,7 +1098,7 @@ CREATE UNIQUE INDEX /*i*/si_page ON /*_*/searchindex (si_page); -- CREATE TABLE /*_*/interwiki ( -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de") - iw_prefix nvarchar(32) NOT NULL, + iw_prefix nvarchar(32) NOT NULL CONSTRAINT PK_interwiki PRIMARY KEY, -- The URL of the wiki, with "$1" as a placeholder for an article name. -- Any spaces in the name will be transformed to underscores before @@ -1119,9 +1119,6 @@ CREATE TABLE /*_*/interwiki ( iw_trans bit NOT NULL default 0 ); -CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix); - - -- -- Used for caching expensive grouped queries -- @@ -1364,10 +1361,10 @@ CREATE TABLE /*_*/protected_titles ( pt_reason_id bigint unsigned NOT NULL CONSTRAINT DF_pt_reason_id DEFAULT 0 CONSTRAINT FK_pt_reason_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), pt_timestamp varchar(14) NOT NULL, pt_expiry varchar(14) NOT NULL, - pt_create_perm nvarchar(60) NOT NULL + pt_create_perm nvarchar(60) NOT NULL, + CONSTRAINT PK_protected_titles PRIMARY KEY(pt_namespace,pt_title) ); -CREATE UNIQUE INDEX /*i*/pt_namespace_title ON /*_*/protected_titles (pt_namespace,pt_title); CREATE INDEX /*i*/pt_timestamp ON /*_*/protected_titles (pt_timestamp); @@ -1376,10 +1373,10 @@ CREATE TABLE /*_*/page_props ( pp_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE, pp_propname nvarchar(60) NOT NULL, pp_value nvarchar(max) NOT NULL, - pp_sortkey float DEFAULT NULL + pp_sortkey float DEFAULT NULL, + CONSTRAINT PK_page_props PRIMARY KEY(pp_page,pp_propname) ); -CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname); CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page); CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page); @@ -1520,9 +1517,10 @@ CREATE TABLE /*_*/site_identifiers ( si_type nvarchar(32) NOT NULL, -- local key value, ie 'en' or 'wiktionary' - si_key nvarchar(32) NOT NULL + si_key nvarchar(32) NOT NULL, + + CONSTRAINT PK_site_identifiers PRIMARY KEY(si_type, si_key) ); -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); diff --git a/maintenance/postgres/archives/patch-interwiki-pk.sql b/maintenance/postgres/archives/patch-interwiki-pk.sql new file mode 100644 index 0000000000..85f6b7ba93 --- /dev/null +++ b/maintenance/postgres/archives/patch-interwiki-pk.sql @@ -0,0 +1,3 @@ +ALTER TABLE interwiki + DROP CONSTRAINT interwiki_iw_prefix_key, + ADD PRIMARY KEY (iw_prefix); \ No newline at end of file diff --git a/maintenance/postgres/archives/patch-protected_titles-pk.sql b/maintenance/postgres/archives/patch-protected_titles-pk.sql new file mode 100644 index 0000000000..17615c7d75 --- /dev/null +++ b/maintenance/postgres/archives/patch-protected_titles-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX protected_titles_unique; +ALTER TABLE protected_titles ADD PRIMARY KEY (pt_namespace, pt_title); \ No newline at end of file diff --git a/maintenance/postgres/archives/patch-site_identifiers-pk.sql b/maintenance/postgres/archives/patch-site_identifiers-pk.sql new file mode 100644 index 0000000000..d5c49ee5d5 --- /dev/null +++ b/maintenance/postgres/archives/patch-site_identifiers-pk.sql @@ -0,0 +1,2 @@ +DROP INDEX si_type_key; +ALTER TABLE site_identifiers ADD PRIMARY KEY (si_type,si_key); \ No newline at end of file diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index f03424fa66..adf2688d0e 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -233,7 +233,6 @@ CREATE TABLE page_props ( pp_sortkey FLOAT ); ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname); -CREATE INDEX page_props_propname ON page_props (pp_propname); CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page); CREATE INDEX pp_propname_sortkey_page ON page_props (pp_propname, pp_sortkey, pp_page) WHERE (pp_sortkey IS NOT NULL); @@ -606,7 +605,7 @@ CREATE INDEX wl_user_notificationtimestamp ON watchlist (wl_user, wl_notificatio CREATE TABLE interwiki ( - iw_prefix TEXT NOT NULL UNIQUE, + iw_prefix TEXT NOT NULL PRIMARY KEY, iw_url TEXT NOT NULL, iw_local SMALLINT NOT NULL, iw_trans SMALLINT NOT NULL DEFAULT 0, @@ -778,10 +777,10 @@ CREATE TABLE protected_titles ( pt_reason_id INTEGER NOT NULL DEFAULT 0, pt_timestamp TIMESTAMPTZ NOT NULL, pt_expiry TIMESTAMPTZ NULL, - pt_create_perm TEXT NOT NULL DEFAULT '' -); -CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title); + pt_create_perm TEXT NOT NULL DEFAULT '', + PRIMARY KEY (pt_namespace, pt_title) +); CREATE TABLE updatelog ( ul_key TEXT NOT NULL PRIMARY KEY, @@ -894,8 +893,9 @@ CREATE INDEX site_forward ON sites (site_forward); CREATE TABLE site_identifiers ( si_site INTEGER NOT NULL, si_type TEXT NOT NULL, - si_key TEXT NOT NULL + si_key TEXT NOT NULL, + + PRIMARY KEY (si_type, si_key) ); -CREATE UNIQUE INDEX si_type_key ON site_identifiers (si_type, si_key); CREATE INDEX si_site ON site_identifiers (si_site); CREATE INDEX si_key ON site_identifiers (si_key); diff --git a/maintenance/sqlite/archives/patch-interwiki-fix-pk.sql b/maintenance/sqlite/archives/patch-interwiki-fix-pk.sql new file mode 100644 index 0000000000..f25f54ca6f --- /dev/null +++ b/maintenance/sqlite/archives/patch-interwiki-fix-pk.sql @@ -0,0 +1,29 @@ +CREATE TABLE /*_*/interwiki_tmp ( + -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de") + iw_prefix varchar(32) NOT NULL PRIMARY KEY, + + -- The URL of the wiki, with "$1" as a placeholder for an article name. + -- Any spaces in the name will be transformed to underscores before + -- insertion. + iw_url blob NOT NULL, + + -- The URL of the file api.php + iw_api blob NOT NULL, + + -- The name of the database (for a connection to be established with LBFactory::getMainLB( 'wikiid' )) + iw_wikiid varchar(64) NOT NULL, + + -- A boolean value indicating whether the wiki is in this project + -- (used, for example, to detect redirect loops) + iw_local bool NOT NULL, + + -- Boolean value indicating whether interwiki transclusions are allowed. + iw_trans tinyint NOT NULL default 0 +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/interwiki_tmp + SELECT * FROM /*_*/interwiki; + +DROP TABLE /*_*/interwiki; + +ALTER TABLE /*_*/interwiki_tmp RENAME TO /*_*/interwiki; diff --git a/maintenance/sqlite/archives/patch-page_props-fix-pk.sql b/maintenance/sqlite/archives/patch-page_props-fix-pk.sql new file mode 100644 index 0000000000..3a40d861d0 --- /dev/null +++ b/maintenance/sqlite/archives/patch-page_props-fix-pk.sql @@ -0,0 +1,18 @@ +CREATE TABLE /*_*/page_props_tmp ( + pp_page int NOT NULL, + pp_propname varbinary(60) NOT NULL, + pp_value blob NOT NULL, + pp_sortkey float DEFAULT NULL, + + PRIMARY KEY (pp_page,pp_propname) +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/page_props_tmp + SELECT * FROM /*_*/page_props; + +DROP TABLE /*_*/page_props; + +ALTER TABLE /*_*/page_props_tmp RENAME TO /*_*/page_props; + +CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page); +CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page); \ No newline at end of file diff --git a/maintenance/sqlite/archives/patch-protected_titles-fix-pk.sql b/maintenance/sqlite/archives/patch-protected_titles-fix-pk.sql new file mode 100644 index 0000000000..083221433d --- /dev/null +++ b/maintenance/sqlite/archives/patch-protected_titles-fix-pk.sql @@ -0,0 +1,21 @@ +CREATE TABLE /*_*/protected_titles_tmp ( + pt_namespace int NOT NULL, + pt_title varchar(255) binary NOT NULL, + pt_user int unsigned NOT NULL, + pt_reason varbinary(767) default '', -- Deprecated. + pt_reason_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that pt_reason should be used) + pt_timestamp binary(14) NOT NULL, + pt_expiry varbinary(14) NOT NULL default '', + pt_create_perm varbinary(60) NOT NULL, + + PRIMARY KEY (pt_namespace,pt_title) +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/protected_titles_tmp + SELECT * FROM /*_*/protected_titles; + +DROP TABLE /*_*/protected_titles; + +ALTER TABLE /*_*/protected_titles_tmp RENAME TO /*_*/protected_titles; + +CREATE INDEX /*i*/pt_timestamp ON /*_*/protected_titles (pt_timestamp); diff --git a/maintenance/sqlite/archives/patch-site_identifiers-fix-pk.sql b/maintenance/sqlite/archives/patch-site_identifiers-fix-pk.sql new file mode 100644 index 0000000000..7ceadb708c --- /dev/null +++ b/maintenance/sqlite/archives/patch-site_identifiers-fix-pk.sql @@ -0,0 +1,22 @@ +CREATE TABLE /*_*/site_identifiers_tmp ( + -- Key on site.site_id + si_site INT UNSIGNED NOT NULL, + + -- local key type, ie 'interwiki' or 'langlink' + si_type varbinary(32) NOT NULL, + + -- local key value, ie 'en' or 'wiktionary' + si_key varbinary(32) NOT NULL, + + PRIMARY KEY (si_type, si_key) +) /*$wgDBTableOptions*/; + +INSERT INTO /*_*/site_identifiers_tmp + SELECT * FROM /*_*/site_identifiers; + +DROP TABLE /*_*/site_identifiers; + +ALTER TABLE /*_*/site_identifiers_tmp RENAME TO /*_*/site_identifiers; + +CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site); +CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key); \ No newline at end of file diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 802b0ef66f..b503bba73c 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1529,7 +1529,7 @@ CREATE FULLTEXT INDEX /*i*/si_text ON /*_*/searchindex (si_text); -- CREATE TABLE /*_*/interwiki ( -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de") - iw_prefix varchar(32) NOT NULL, + iw_prefix varchar(32) NOT NULL PRIMARY KEY, -- The URL of the wiki, with "$1" as a placeholder for an article name. -- Any spaces in the name will be transformed to underscores before @@ -1550,8 +1550,6 @@ CREATE TABLE /*_*/interwiki ( iw_trans tinyint NOT NULL default 0 ) /*$wgDBTableOptions*/; -CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix); - -- -- Used for caching expensive grouped queries @@ -1807,10 +1805,11 @@ CREATE TABLE /*_*/protected_titles ( pt_reason_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that pt_reason should be used) pt_timestamp binary(14) NOT NULL, pt_expiry varbinary(14) NOT NULL default '', - pt_create_perm varbinary(60) NOT NULL + pt_create_perm varbinary(60) NOT NULL, + + PRIMARY KEY (pt_namespace,pt_title) ) /*$wgDBTableOptions*/; -CREATE UNIQUE INDEX /*i*/pt_namespace_title ON /*_*/protected_titles (pt_namespace,pt_title); CREATE INDEX /*i*/pt_timestamp ON /*_*/protected_titles (pt_timestamp); @@ -1819,10 +1818,11 @@ CREATE TABLE /*_*/page_props ( pp_page int NOT NULL, pp_propname varbinary(60) NOT NULL, pp_value blob NOT NULL, - pp_sortkey float DEFAULT NULL + pp_sortkey float DEFAULT NULL, + + PRIMARY KEY (pp_page,pp_propname) ) /*$wgDBTableOptions*/; -CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname); CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page); CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page); @@ -1962,10 +1962,11 @@ CREATE TABLE /*_*/site_identifiers ( si_type varbinary(32) NOT NULL, -- local key value, ie 'en' or 'wiktionary' - si_key varbinary(32) NOT NULL + si_key varbinary(32) NOT NULL, + + PRIMARY KEY (si_type, si_key) ) /*$wgDBTableOptions*/; -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); -- 2.20.1