EddieGP has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/367698 )
Change subject: Add primary keys to site_stats
......................................................................
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 MySql,
Sqlite, 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
---
M includes/installer/MssqlUpdater.php
M includes/installer/MysqlUpdater.php
M includes/installer/OracleUpdater.php
M includes/installer/PostgresUpdater.php
M includes/installer/SqliteUpdater.php
A maintenance/archives/patch-site_stats-pk.sql
A maintenance/mssql/archives/patch-site_stats-pk.sql
M maintenance/mssql/tables.sql
A maintenance/oracle/archives/patch-site_stats-pk.sql
M maintenance/oracle/tables.sql
A maintenance/postgres/archives/patch-site_stats-pk.sql
M maintenance/postgres/tables.sql
A maintenance/sqlite/archives/patch-site_stats-pk.sql
M maintenance/tables.sql
14 files changed, 82 insertions(+), 9 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/98/367698/1
diff --git a/includes/installer/MssqlUpdater.php
b/includes/installer/MssqlUpdater.php
index 1a9915d..9025ab3 100644
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -101,6 +101,9 @@
[ 'addField', 'externallinks', 'el_index_60',
'patch-externallinks-el_index_60.sql' ],
[ 'dropIndex', 'oldimage', 'oi_name_archive_name',
'patch-alter-table-oldimage.sql' ],
+
+ // 1.30
+ [ 'modifyTable', 'site_stats',
'patch-site_stats-pk.sql' ],
];
}
diff --git a/includes/installer/MysqlUpdater.php
b/includes/installer/MysqlUpdater.php
index adfe2f6..ecbbdba 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -302,6 +302,9 @@
[ 'addField', 'user_groups', 'ug_expiry',
'patch-user_groups-ug_expiry.sql' ],
[ 'addIndex', 'image', 'img_user_timestamp',
'patch-image-user-index-2.sql' ],
[ 'modifyField', 'image', 'img_media_type',
'patch-add-3d.sql' ],
+
+ // 1.30
+ [ 'modifyTable', 'site_stats',
'patch-site_stats-pk.sql' ],
];
}
diff --git a/includes/installer/OracleUpdater.php
b/includes/installer/OracleUpdater.php
index e262eda..88a8072 100644
--- a/includes/installer/OracleUpdater.php
+++ b/includes/installer/OracleUpdater.php
@@ -123,6 +123,9 @@
[ 'addField', 'externallinks', 'el_index_60',
'patch-externallinks-el_index_60.sql' ],
[ 'addField', 'user_groups', 'ug_expiry',
'patch-user_groups-ug_expiry.sql' ],
+ // 1.30
+ [ 'modifyTable', 'site_stats',
'patch-site_stats-pk.sql' ],
+
// KEEP THIS AT THE BOTTOM!!
[ 'doRebuildDuplicateFunction' ],
diff --git a/includes/installer/PostgresUpdater.php
b/includes/installer/PostgresUpdater.php
index 0172f1a..04f625b 100644
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -452,6 +452,9 @@
[ 'addPgIndex', 'externallinks', 'el_from_index_60', '(
el_from, el_index_60, el_id )' ],
[ 'addPgField', 'user_groups', 'ug_expiry',
"TIMESTAMPTZ NULL" ],
[ 'addPgIndex', 'user_groups', 'user_groups_expiry', '(
ug_expiry )' ],
+
+ // 1.30
+ [ 'modifyTable', 'site_stats',
'patch-site_stats-pk.sql' ],
];
}
diff --git a/includes/installer/SqliteUpdater.php
b/includes/installer/SqliteUpdater.php
index 9c90283..a851f2b 100644
--- a/includes/installer/SqliteUpdater.php
+++ b/includes/installer/SqliteUpdater.php
@@ -165,6 +165,9 @@
[ 'addField', 'externallinks', 'el_index_60',
'patch-externallinks-el_index_60.sql' ],
[ 'addField', 'user_groups', 'ug_expiry',
'patch-user_groups-ug_expiry.sql' ],
[ 'addIndex', 'image', 'img_user_timestamp',
'patch-image-user-index-2.sql' ],
+
+ // 1.30
+ [ 'modifyTable', 'site_stats',
'patch-site_stats-pk.sql' ],
];
}
diff --git a/maintenance/archives/patch-site_stats-pk.sql
b/maintenance/archives/patch-site_stats-pk.sql
new file mode 100644
index 0000000..c9c3178
--- /dev/null
+++ b/maintenance/archives/patch-site_stats-pk.sql
@@ -0,0 +1,3 @@
+ALTER TABLE /*_*/site_stats
+ DROP KEY ss_row_id,
+ ADD PRIMARY KEY ( `ss_row_id` );
diff --git a/maintenance/mssql/archives/patch-site_stats-pk.sql
b/maintenance/mssql/archives/patch-site_stats-pk.sql
new file mode 100644
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 3babb39..7f2583a 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -475,9 +475,6 @@
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
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 fc3c696..90b7d6d 100644
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -250,7 +250,7 @@
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,
@@ -258,7 +258,6 @@
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
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 e19c447..502bec9 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -278,7 +278,7 @@
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,
diff --git a/maintenance/sqlite/archives/patch-site_stats-pk.sql
b/maintenance/sqlite/archives/patch-site_stats-pk.sql
new file mode 100644
index 0000000..8f55193
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-site_stats-pk.sql
@@ -0,0 +1,52 @@
+DROP TABLE IF EXISTS /*_*/site_stats_tmp;
+
+--
+-- Contains a single row with some aggregate info
+-- on the state of the site.
+--
+CREATE TABLE /*_*/site_stats_tmp (
+ -- The single row should contain 1 here.
+ ss_row_id int unsigned NOT NULL PRIMARY KEY default 0,
+
+ -- Total number of edits performed.
+ ss_total_edits bigint unsigned default 0,
+
+ -- An approximate count of pages matching the following criteria:
+ -- * in namespace 0
+ -- * not a redirect
+ -- * contains the text '[['
+ -- See Article::isCountable() in includes/Article.php
+ ss_good_articles bigint unsigned default 0,
+
+ -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except
faster
+ ss_total_pages bigint default '-1',
+
+ -- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
+ ss_users bigint default '-1',
+
+ -- Number of users that still edit
+ ss_active_users bigint default '-1',
+
+ -- Number of images, equivalent to SELECT COUNT(*) FROM image
+ ss_images int default 0
+) /*$wgDBTableOptions*/;
+
+INSERT OR IGNORE INTO /*_*/site_stats_tmp (
+ ss_row_id,
+ ss_total_edits,
+ ss_good_articles,
+ ss_total_pages,
+ ss_active_users,
+ ss_images
+) SELECT
+ ss_row_id,
+ ss_total_edits,
+ ss_good_articles,
+ ss_total_pages,
+ ss_active_users,
+ ss_images
+FROM /*_*/site_stats;
+
+DROP TABLE /*_*/site_stats;
+
+ALTER TABLE /*_*/site_stats_tmp RENAME TO /*_*/site_stats;
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 14f6932..69abbb0 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -803,7 +803,7 @@
--
CREATE TABLE /*_*/site_stats (
-- The single row should contain 1 here.
- ss_row_id int unsigned NOT NULL,
+ ss_row_id int unsigned NOT NULL PRIMARY KEY default 0,
-- Total number of edits performed.
ss_total_edits bigint unsigned default 0,
@@ -828,8 +828,6 @@
ss_images int default 0
) /*$wgDBTableOptions*/;
--- 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
--
To view, visit https://gerrit.wikimedia.org/r/367698
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id10e221f0dc120bc09afc22596fd1dbecbf6a61d
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: EddieGP <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
