Hello Krinkle,
I'd like you to do a code review. Please visit
https://gerrit.wikimedia.org/r/352646
to review the following change.
Change subject: Overhaul site_stats table
......................................................................
Overhaul site_stats table
The site stats table holds a bunch of metric fields, two of which are of
data type "bigint unsigned", 3 are "bigint" (signed) and one is int
(signed). Also the default values differ widely: It is 0 on the
"unsigned" fields, 0 on the unsigned fields and the "int" field, but -1
on the three others. This patch makes all of this more consistent:
Set all fields (except the ss_row_id, which isn't changed) data type to
"bigint unsigned". Also set NULL as the default value for all those
fields. Obviously -1 isn't a possible default value any more. Also, 0
can easily be mistaken for a real value (e.g. ss_active_users=0 -->
"there is nobody active on this wiki"). NULL, by it's definition, is the
value of choice for a value to insert into fields of which we don't know
a correct value.
Bug: T56888
Change-Id: I7d42aae434852a56b6f8dd559d8a5f3bce416021
---
A maintenance/archives/patch-site_stats-modify.sql
A maintenance/mssql/archives/patch-site_stats-modify.sql
M maintenance/mssql/tables.sql
A maintenance/oracle/archives/patch-site_stats-modify.sql
M maintenance/oracle/tables.sql
A maintenance/postgres/archives/patch-site_stats-modify.sql
M maintenance/postgres/tables.sql
A maintenance/sqlite/archives/patch-site_stats-modify.sql
M maintenance/tables.sql
9 files changed, 134 insertions(+), 25 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/46/352646/1
diff --git a/maintenance/archives/patch-site_stats-modify.sql
b/maintenance/archives/patch-site_stats-modify.sql
new file mode 100644
index 0000000..690a04f
--- /dev/null
+++ b/maintenance/archives/patch-site_stats-modify.sql
@@ -0,0 +1,7 @@
+ALTER TABLE /*_*/site_stats
+ ALTER ss_total_edits SET DEFAULT NULL,
+ ALTER ss_good_articles SET DEFAULT NULL,
+ MODIFY COLUMN ss_total_pages bigint unsigned DEFAULT NULL,
+ MODIFY COLUMN ss_users bigint unsigned DEFAULT NULL,
+ MODIFY COLUMN ss_active_users bigint unsigned DEFAULT NULL,
+ MODIFY COLUMN ss_images bigint unsigned DEFAULT NULL;
diff --git a/maintenance/mssql/archives/patch-site_stats-modify.sql
b/maintenance/mssql/archives/patch-site_stats-modify.sql
new file mode 100644
index 0000000..b2de948
--- /dev/null
+++ b/maintenance/mssql/archives/patch-site_stats-modify.sql
@@ -0,0 +1,32 @@
+/* Delete old default constraints */
+DECLARE @sql nvarchar(max)
+SET @sql=''
+
+/* IMHO: A DBMS where you have to do THIS to change a default value sucks. */
+SELECT @sql= @sql + 'ALTER TABLE site_stats DROP CONSTRAINT ' + df.name + '; '
+FROM sys.default_constraints df
+JOIN sys.columns c
+ ON c.object_id = df.parent_object_id
+ AND c.column_id = df.parent_column_id
+WHERE
+ df.parent_object_id = OBJECT_ID('site_stats');--
+
+EXEC sp_executesql @sql;
+
+/* Change data type of ss_images from int to bigint.
+ * All other fields (except ss_row_id) already are bigint.
+ * This MUST happen before adding new constraints. */
+ALTER TABLE site_stats ALTER COLUMN ss_images bigint;
+
+/* Add new default constraints.
+ * Don't ask me why I have to repeat ALTER TABLE site_stats
+ * instead of using commas, for some reason SQL Server 2016
+ * didn't accept it in any other way. Maybe I just don't know
+ * enough about mssql, but this works.
+ */
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_total_edits DEFAULT NULL FOR
ss_total_edits;
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_good_article DEFAULT NULL FOR
ss_good_articles;
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_total_pages DEFAULT NULL FOR
ss_total_pages;
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_users DEFAULT NULL FOR ss_users;
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_active_users DEFAULT NULL FOR
ss_active_users;
+ALTER TABLE site_stats ADD CONSTRAINT col_ss_images DEFAULT NULL FOR ss_images;
diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
index 3babb39..697ac42 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -453,26 +453,26 @@
ss_row_id int NOT NULL,
-- Total number of edits performed.
- ss_total_edits bigint default 0,
+ ss_total_edits bigint default NULL,
-- 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 default 0,
+ ss_good_articles bigint default NULL,
-- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except
faster
- ss_total_pages bigint default '-1',
+ ss_total_pages bigint default NULL,
-- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
- ss_users bigint default '-1',
+ ss_users bigint default NULL,
-- Number of users that still edit
- ss_active_users bigint default '-1',
+ ss_active_users bigint default NULL,
-- Number of images, equivalent to SELECT COUNT(*) FROM image
- ss_images int default 0
+ ss_images bigint default NULL
);
-- Pointless index to assuage developer superstitions
diff --git a/maintenance/oracle/archives/patch-site_stats-modify.sql
b/maintenance/oracle/archives/patch-site_stats-modify.sql
new file mode 100644
index 0000000..9882410
--- /dev/null
+++ b/maintenance/oracle/archives/patch-site_stats-modify.sql
@@ -0,0 +1,7 @@
+ALTER TABLE /*_*/site_stats
+ ALTER ss_total_edits SET DEFAULT NULL,
+ ALTER ss_good_articles SET DEFAULT NULL,
+ ALTER ss_total_pages SET DEFAULT NULL,
+ ALTER ss_users SET DEFAULT NULL,
+ ALTER ss_active_users SET DEFAULT NULL,
+ ALTER ss_images SET DEFAULT NULL;
diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql
index fc3c696..7e9a95b 100644
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -251,12 +251,12 @@
CREATE TABLE &mw_prefix.site_stats (
ss_row_id NUMBER NOT NULL ,
- ss_total_edits NUMBER DEFAULT 0,
- ss_good_articles NUMBER DEFAULT 0,
- ss_total_pages NUMBER DEFAULT -1,
- ss_users NUMBER DEFAULT -1,
- ss_active_users NUMBER DEFAULT -1,
- ss_images NUMBER DEFAULT 0
+ ss_total_edits NUMBER DEFAULT NULL,
+ ss_good_articles NUMBER DEFAULT NULL,
+ ss_total_pages NUMBER DEFAULT NULL,
+ ss_users NUMBER DEFAULT NULL,
+ ss_active_users NUMBER DEFAULT NULL,
+ ss_images NUMBER DEFAULT NULL
);
CREATE UNIQUE INDEX &mw_prefix.site_stats_u01 ON &mw_prefix.site_stats
(ss_row_id);
diff --git a/maintenance/postgres/archives/patch-site_stats-modify.sql
b/maintenance/postgres/archives/patch-site_stats-modify.sql
new file mode 100644
index 0000000..9882410
--- /dev/null
+++ b/maintenance/postgres/archives/patch-site_stats-modify.sql
@@ -0,0 +1,7 @@
+ALTER TABLE /*_*/site_stats
+ ALTER ss_total_edits SET DEFAULT NULL,
+ ALTER ss_good_articles SET DEFAULT NULL,
+ ALTER ss_total_pages SET DEFAULT NULL,
+ ALTER ss_users SET DEFAULT NULL,
+ ALTER ss_active_users SET DEFAULT NULL,
+ ALTER ss_images SET DEFAULT NULL;
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index e19c447..a73acb5 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -279,13 +279,13 @@
CREATE TABLE site_stats (
ss_row_id INTEGER NOT NULL UNIQUE,
- ss_total_edits INTEGER DEFAULT 0,
- ss_good_articles INTEGER DEFAULT 0,
- ss_total_pages INTEGER DEFAULT -1,
- ss_users INTEGER DEFAULT -1,
- ss_active_users INTEGER DEFAULT -1,
- ss_admins INTEGER DEFAULT -1,
- ss_images INTEGER DEFAULT 0
+ ss_total_edits INTEGER DEFAULT NULL,
+ ss_good_articles INTEGER DEFAULT NULL,
+ ss_total_pages INTEGER DEFAULT NULL,
+ ss_users INTEGER DEFAULT NULL,
+ ss_active_users INTEGER DEFAULT NULL,
+ ss_admins INTEGER DEFAULT NULL,
+ ss_images INTEGER DEFAULT NULL
);
diff --git a/maintenance/sqlite/archives/patch-site_stats-modify.sql
b/maintenance/sqlite/archives/patch-site_stats-modify.sql
new file mode 100644
index 0000000..860ddc4
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-site_stats-modify.sql
@@ -0,0 +1,56 @@
+DROP TABLE IF EXISTS /*_*/site_stats_tmp;
+
+/* Copy & paste from maintenance/tables.sql begins, just table name changed */
+CREATE TABLE /*_*/site_stats_tmp (
+ -- The single row should contain 1 here.
+ ss_row_id int unsigned NOT NULL,
+
+ -- Total number of edits performed.
+ ss_total_edits bigint unsigned default NULL,
+
+ -- 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 NULL,
+
+ -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except
faster
+ ss_total_pages bigint unsigned default NULL,
+
+ -- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
+ ss_users bigint unsigned default NULL,
+
+ -- Number of users that still edit
+ ss_active_users bigint unsigned default NULL,
+
+ -- Number of images, equivalent to SELECT COUNT(*) FROM image
+ ss_images bigint unsigned default NULL
+) /*$wgDBTableOptions*/;
+/* Copy & paste from maintenance/tables.sql ends */
+
+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;
+
+
+/* Copy & paste from maintenance/tables.sql begins */
+-- Pointless index to assuage developer superstitions
+CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
+/* Copy & paste from maintenance/tables.sql ends */
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index d4147f4..7eab308 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -806,26 +806,26 @@
ss_row_id int unsigned NOT NULL,
-- Total number of edits performed.
- ss_total_edits bigint unsigned default 0,
+ ss_total_edits bigint unsigned default NULL,
-- 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,
+ ss_good_articles bigint unsigned default NULL,
-- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except
faster
- ss_total_pages bigint default '-1',
+ ss_total_pages bigint unsigned default NULL,
-- Number of users, theoretically equal to SELECT COUNT(*) FROM user;
- ss_users bigint default '-1',
+ ss_users bigint unsigned default NULL,
-- Number of users that still edit
- ss_active_users bigint default '-1',
+ ss_active_users bigint unsigned default NULL,
-- Number of images, equivalent to SELECT COUNT(*) FROM image
- ss_images int default 0
+ ss_images bigint unsigned default NULL
) /*$wgDBTableOptions*/;
-- Pointless index to assuage developer superstitions
--
To view, visit https://gerrit.wikimedia.org/r/352646
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7d42aae434852a56b6f8dd559d8a5f3bce416021
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: EddieGP <wikimedia....@eddie-sh.de>
Gerrit-Reviewer: Krinkle <krinklem...@gmail.com>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
