Add primary keys to site_stats

As discussed in I7d42aae434852a56b6f8dd559d8a5f3bce416021 primary keys
are needed to perform various schema changes on  the site_stats table.
This patch aims to introduce primary keys for all supported dbms.

The respective *.sql patch files were tested locally against Postgres
and SQL Server 2016. Please note that neither the patch file for Oracle
DB nor the mediawiki upgrade through update.php was tested.

Bug: T56888
Change-Id: Id10e221f0dc120bc09afc22596fd1dbecbf6a61d

diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php
index a2aa8c0..411d2c8 100644 (file)
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -104,6 +104,7 @@ class MssqlUpdater extends DatabaseUpdater {
 
                        // 1.30
                        [ 'modifyField', 'image', 'img_media_type', 'patch-add-3d.sql' ],
+                       [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ],
                ];
        }
 

diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php
index 00b9661..040b54a 100644 (file)
--- a/includes/installer/OracleUpdater.php
+++ b/includes/installer/OracleUpdater.php
@@ -125,6 +125,7 @@ class OracleUpdater extends DatabaseUpdater {
 
                        // 1.30
                        [ 'doAutoIncrementTriggers' ],
+                       [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ],
 
                        // KEEP THIS AT THE BOTTOM!!
                        [ 'doRebuildDuplicateFunction' ],

diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php
index 0475fe4..1f17fec 100644 (file)
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -481,6 +481,7 @@ class PostgresUpdater extends DatabaseUpdater {
                        [ 'changeNullableField', 'protected_titles', 'pt_reason', 'NOT NULL', true ],
                        [ 'addPgField', 'protected_titles', 'pt_reason_id', 'INTEGER NOT NULL DEFAULT 0' ],
                        [ 'addTable', 'comment', 'patch-comment-table.sql' ],
+                       [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ],
                ];
        }
 

diff --git a/maintenance/mssql/archives/patch-site_stats-pk.sql b/maintenance/mssql/archives/patch-site_stats-pk.sql
new file mode 100644 (file)
index 0000000..7533719
--- /dev/null
+++ b/maintenance/mssql/archives/patch-site_stats-pk.sql
@@ -0,0 +1,2 @@
+DROP INDEX ss_row_id ON site_stats;
+ALTER TABLE /*_*/site_stats ADD CONSTRAINT /*i*/ss_row_id PRIMARY KEY (ss_row_id);

diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
index 2a67294..119cd5b 100644 (file)
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -450,7 +450,7 @@ CREATE INDEX /*i*/iwl_prefix_from_title ON /*_*/iwlinks (iwl_prefix, iwl_from, i
 --
 CREATE TABLE /*_*/site_stats (
   -- The single row should contain 1 here.
-  ss_row_id int NOT NULL,
+  ss_row_id int NOT NULL CONSTRAINT /*i*/ss_row_id PRIMARY KEY,
 
   -- Total number of edits performed.
   ss_total_edits bigint default 0,
@@ -475,9 +475,6 @@ CREATE TABLE /*_*/site_stats (
   ss_images int default 0
 );
 
--- Pointless index to assuage developer superstitions
-CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
-
 
 --
 -- The internet is full of jerks, alas. Sometimes it's handy

diff --git a/maintenance/oracle/archives/patch-site_stats-pk.sql b/maintenance/oracle/archives/patch-site_stats-pk.sql
new file mode 100644 (file)
index 0000000..a288c08
--- /dev/null
+++ b/maintenance/oracle/archives/patch-site_stats-pk.sql
@@ -0,0 +1,4 @@
+define mw_prefix='{$wgDBprefix}';
+
+ALTER TABLE &mw_prefix.site_stats DROP CONSTRAINT &mw_prefix.site_stats_u01;
+ALTER TABLE &mw_prefix.site_stats ADD CONSTRAINT &mw_prefix.site_stats_pk PRIMARY KEY(ss_row_id);

diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql
index 44c907c..e6e2e56 100644 (file)
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -321,7 +321,7 @@ CREATE UNIQUE INDEX &mw_prefix.iwlinks_ui01 ON &mw_prefix.iwlinks (iwl_from, iwl
 CREATE UNIQUE INDEX &mw_prefix.iwlinks_ui02 ON &mw_prefix.iwlinks (iwl_prefix, iwl_title, iwl_from);
 
 CREATE TABLE &mw_prefix.site_stats (
-  ss_row_id         NUMBER  NOT NULL ,
+  ss_row_id         NUMBER  NOT NULL PRIMARY KEY,
   ss_total_edits    NUMBER            DEFAULT 0,
   ss_good_articles  NUMBER            DEFAULT 0,
   ss_total_pages    NUMBER            DEFAULT -1,
@@ -329,7 +329,6 @@ CREATE TABLE &mw_prefix.site_stats (
   ss_active_users   NUMBER            DEFAULT -1,
   ss_images         NUMBER            DEFAULT 0
 );
-CREATE UNIQUE INDEX &mw_prefix.site_stats_u01 ON &mw_prefix.site_stats (ss_row_id);
 
 CREATE SEQUENCE ipblocks_ipb_id_seq;
 CREATE TABLE &mw_prefix.ipblocks (

diff --git a/maintenance/postgres/archives/patch-site_stats-pk.sql b/maintenance/postgres/archives/patch-site_stats-pk.sql
new file mode 100644 (file)
index 0000000..faa5e9f
--- /dev/null
+++ b/maintenance/postgres/archives/patch-site_stats-pk.sql
@@ -0,0 +1,3 @@
+ALTER TABLE site_stats DROP CONSTRAINT site_stats_ss_row_id_key;
+ALTER TABLE site_stats ADD PRIMARY KEY (ss_row_id);
+ALTER TABLE site_stats ALTER ss_row_id SET DEFAULT 0;

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index eea9e68..d6d2f24 100644 (file)
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -296,7 +296,7 @@ CREATE INDEX langlinks_lang_title    ON langlinks (ll_lang,ll_title);
 
 
 CREATE TABLE site_stats (
-  ss_row_id         INTEGER  NOT NULL  UNIQUE,
+  ss_row_id         INTEGER  NOT NULL  PRIMARY KEY DEFAULT 0,
   ss_total_edits    INTEGER            DEFAULT 0,
   ss_good_articles  INTEGER            DEFAULT 0,
   ss_total_pages    INTEGER            DEFAULT -1,