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

diff --git a/RELEASE-NOTES-1.32 b/RELEASE-NOTES-1.32
index 179e970..e5b5b8f 100644 (file)
--- 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 ===
 * 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 ==
 * …
 
 == Compatibility ==

diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php
index 03b9c48..0a66b9a 100644 (file)
--- 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' ],
                        [ '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 f8b5408..3e1d196 100644 (file)
--- 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' ],
                        [ '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 18ef881..932c941 100644 (file)
--- 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', '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 1139cbf..80eb843 100644 (file)
--- 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' ],
                        [ '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 (file)
index 0000000..f0e3ede
--- /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 (file)
index 0000000..6cbf50b
--- /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 (file)
index 0000000..fd4146d
--- /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 (file)
index 0000000..05f7931
--- /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 (file)
index 0000000..e989c44
--- /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 (file)
index 0000000..0615440
--- /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 (file)
index 0000000..17e059e
--- /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 (file)
index 0000000..fa12f70
--- /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 3ab7d89..8f30e80 100644 (file)
--- 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")
 --
 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
 
   -- 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
 );
 
   iw_trans bit NOT NULL default 0
 );
 

-CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix);
-
-

 --
 -- Used for caching expensive grouped queries
 --
 --
 -- 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_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);
 
 
 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_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);
 
 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_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);
 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 (file)
index 0000000..85f6b7b
--- /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 (file)
index 0000000..17615c7
--- /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 (file)
index 0000000..d5c49ee
--- /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 f03424f..adf2688 100644 (file)
--- 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);
   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);
 
 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 (
 
 
 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,
   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_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,
 
 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,
 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);
 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 (file)
index 0000000..f25f54c
--- /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 (file)
index 0000000..3a40d86
--- /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 (file)
index 0000000..0832214
--- /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 (file)
index 0000000..7ceadb7
--- /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 802b0ef..b503bba 100644 (file)
--- 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")
 --
 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
 
   -- 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*/;
 
   iw_trans tinyint NOT NULL default 0
 ) /*$wgDBTableOptions*/;
 

-CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix);
-

 
 --
 -- Used for caching expensive grouped queries
 
 --
 -- 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_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*/;
 
 ) /*$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);
 
 
 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_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*/;
 
 ) /*$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);
 
 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_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*/;
 
 ) /*$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);
 
 CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site);
 CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key);