Skizzerz has uploaded a new change for review.
https://gerrit.wikimedia.org/r/287049
Change subject: Fix deleting pages/files in Microsoft SQL Server
......................................................................
Fix deleting pages/files in Microsoft SQL Server
Right now we have a slew of foreign key violations whenever we try to
delete something due to the braindead way mediawiki handles page
deletions. Let's remove those foreign keys from places where ids are
maintained even though the records they are pointing to have been deleted.
Also, work has begun on the massive (re)naming of every constraint in the
schema, so that icky hacks like those employed in these patch files don't
need to be used in the future; rather, we can simply refer to the
constraints by name.
Change-Id: I79938d36a8e35debd3e27052d8ef0e3e6c32f40c
---
M includes/installer/MssqlUpdater.php
A maintenance/mssql/archives/patch-archive-drop-fks.sql
A maintenance/mssql/archives/patch-logging-drop-fks.sql
A maintenance/mssql/archives/patch-recentchanges-drop-fks.sql
M maintenance/mssql/tables.sql
5 files changed, 199 insertions(+), 11 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/49/287049/1
diff --git a/includes/installer/MssqlUpdater.php
b/includes/installer/MssqlUpdater.php
index bdaf4c8..64317af 100644
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -69,6 +69,22 @@
[ 'dropTable', 'msg_resource_links' ],
[ 'dropTable', 'msg_resource' ],
[ 'addField', 'watchlist', 'wl_id',
'patch-watchlist-wl_id.sql' ],
+ [ 'dropField', 'mwuser', 'user_options',
'patch-drop-user_options.sql' ],
+ [ 'addTable', 'bot_passwords',
'patch-bot_passwords.sql' ],
+ [ 'addField', 'pagelinks', 'pl_from_namespace',
'patch-pl_from_namespace.sql' ],
+ [ 'addField', 'templatelinks', 'tl_from_namespace',
'patch-tl_from_namespace.sql' ],
+ [ 'addField', 'imagelinks', 'il_from_namespace',
'patch-il_from_namespace.sql' ],
+ [ 'dropIndex', 'categorylinks', 'cl_collation',
'patch-kill-cl_collation_index.sql' ],
+ [ 'addIndex', 'categorylinks', 'cl_collation_ext',
+ 'patch-add-cl_collation_ext_index.sql' ],
+ [ 'dropField', 'recentchanges', 'rc_cur_time',
'patch-drop-rc_cur_time.sql' ],
+ [ 'addField', 'page_props', 'pp_sortkey',
'patch-pp_sortkey.sql' ],
+ [ 'updateSchema', 'oldimage', 'oldimage varchar',
'patch-oldimage-schema.sql' ],
+ [ 'updateSchema', 'filearchive', 'filearchive varchar',
'patch-filearchive-schema.sql' ],
+ [ 'updateSchema', 'image', 'image varchar',
'patch-image-schema.sql' ],
+ [ 'updateSchema', 'recentchanges',
'recentchanges-drop-fks', 'patch-recentchanges-drop-fks.sql' ],
+ [ 'updateSchema', 'logging', 'logging-drop-fks',
'patch-logging-drop-fks.sql' ],
+ [ 'updateSchema', 'archive', 'archive-drop-fks',
'patch-archive-drop-fks.sql' ]
];
}
diff --git a/maintenance/mssql/archives/patch-archive-drop-fks.sql
b/maintenance/mssql/archives/patch-archive-drop-fks.sql
new file mode 100644
index 0000000..3055ac9
--- /dev/null
+++ b/maintenance/mssql/archives/patch-archive-drop-fks.sql
@@ -0,0 +1,59 @@
+DECLARE @base nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @base = 'ALTER TABLE /*_*/archive DROP CONSTRAINT ';--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/archive')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/revision')
+ AND c.name = 'ar_parent_id';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+-- while we're at it, let's fix up the other foreign key constraints on archive
+-- as future patches touch constraints on other tables, they'll take the time
to update constraint names there as well
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/archive')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/mwuser')
+ AND c.name = 'ar_user';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/archive ADD CONSTRAINT ar_user__user_id__fk FOREIGN KEY
(ar_user) REFERENCES /*_*/mwuser(user_id);--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/archive')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/text')
+ AND c.name = 'ar_text_id';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/archive ADD CONSTRAINT ar_text_id__old_id__fk FOREIGN KEY
(ar_text_id) REFERENCES /*_*/text(old_id) ON DELETE CASCADE;
diff --git a/maintenance/mssql/archives/patch-logging-drop-fks.sql
b/maintenance/mssql/archives/patch-logging-drop-fks.sql
new file mode 100644
index 0000000..c9cbca3
--- /dev/null
+++ b/maintenance/mssql/archives/patch-logging-drop-fks.sql
@@ -0,0 +1,37 @@
+DECLARE @base nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @base = 'ALTER TABLE /*_*/logging DROP CONSTRAINT ';--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/logging')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/mwuser')
+ AND c.name = 'log_user';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/logging')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/page')
+ AND c.name = 'log_page';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;
diff --git a/maintenance/mssql/archives/patch-recentchanges-drop-fks.sql
b/maintenance/mssql/archives/patch-recentchanges-drop-fks.sql
new file mode 100644
index 0000000..24f78f6
--- /dev/null
+++ b/maintenance/mssql/archives/patch-recentchanges-drop-fks.sql
@@ -0,0 +1,76 @@
+DECLARE @base nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @base = 'ALTER TABLE /*_*/recentchanges DROP CONSTRAINT ';--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/recentchanges')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/page')
+ AND c.name = 'rc_cur_id';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/recentchanges')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/revision')
+ AND c.name = 'rc_this_oldid';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/recentchanges')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/revision')
+ AND c.name = 'rc_last_oldid';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+-- while we're at it, let's fix up the other foreign key constraints on
recentchanges
+-- as future patches touch constraints on other tables, they'll take the time
to update constraint names there as well
+ALTER TABLE /*_*/recentchanges DROP CONSTRAINT FK_rc_logid_log_id;--
+ALTER TABLE /*_*/recentchanges ADD CONSTRAINT rc_logid__log_id__fk FOREIGN KEY
(rc_logid) REFERENCES /*_*/logging(log_id) ON DELETE CASCADE;--
+
+SELECT @id = fk.name
+FROM sys.foreign_keys fk
+JOIN sys.foreign_key_columns fkc
+ ON fkc.constraint_object_id = fk.object_id
+JOIN sys.columns c
+ ON c.column_id = fkc.parent_column_id
+ AND c.object_id = fkc.parent_object_id
+WHERE
+ fk.parent_object_id = OBJECT_ID('/*_*/recentchanges')
+ AND fk.referenced_object_id = OBJECT_ID('/*_*/mwuser')
+ AND c.name = 'rc_user';--
+
+SET @SQL = @base + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/recentchanges ADD CONSTRAINT rc_user__user_id__fk FOREIGN KEY
(rc_user) REFERENCES /*_*/mwuser(user_id);
diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
index 86bd735..e39ee33 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -193,17 +193,17 @@
ar_title NVARCHAR(255) NOT NULL DEFAULT '',
ar_text NVARCHAR(MAX) NOT NULL,
ar_comment NVARCHAR(255) NOT NULL,
- ar_user INT REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
+ ar_user INT CONSTRAINT ar_user__user_id__fk FOREIGN KEY REFERENCES
/*_*/mwuser(user_id),
ar_user_text NVARCHAR(255) NOT NULL,
ar_timestamp varchar(14) NOT NULL default '',
ar_minor_edit BIT NOT NULL DEFAULT 0,
ar_flags NVARCHAR(255) NOT NULL,
ar_rev_id INT NULL, -- NOT a FK, the row gets deleted from revision and
moved here
- ar_text_id INT REFERENCES /*_*/text(old_id) ON DELETE CASCADE,
+ ar_text_id INT CONSTRAINT ar_text_id__old_id__fk FOREIGN KEY REFERENCES
/*_*/text(old_id) ON DELETE CASCADE,
ar_deleted TINYINT NOT NULL DEFAULT 0,
ar_len INT,
ar_page_id INT NULL, -- NOT a FK, the row gets deleted from page and moved
here
- ar_parent_id INT NULL REFERENCES /*_*/revision(rev_id),
+ ar_parent_id INT NULL, -- NOT FK
ar_sha1 nvarchar(32) default null,
ar_content_model nvarchar(32) DEFAULT NULL,
ar_content_format nvarchar(64) DEFAULT NULL
@@ -763,7 +763,7 @@
-- the last few days, see Article::editUpdates()
--
CREATE TABLE /*_*/recentchanges (
- rc_id int NOT NULL PRIMARY KEY IDENTITY,
+ rc_id int NOT NULL CONSTRAINT recentchanges__pk PRIMARY KEY IDENTITY,
rc_timestamp varchar(14) not null default '',
-- This is no longer used
@@ -772,7 +772,7 @@
rc_cur_time varchar(14) NOT NULL default '',
-- As in revision
- rc_user int NOT NULL default 0 REFERENCES /*_*/mwuser(user_id),
+ rc_user int NOT NULL default 0 CONSTRAINT rc_user__user_id__fk FOREIGN KEY
REFERENCES /*_*/mwuser(user_id),
rc_user_text nvarchar(255) NOT NULL,
-- When pages are renamed, their RC entries do _not_ change.
@@ -794,13 +794,13 @@
-- Key to page_id (was cur_id prior to 1.5).
-- This will keep links working after moves while
-- retaining the at-the-time name in the changes list.
- rc_cur_id int REFERENCES /*_*/page(page_id),
+ rc_cur_id int, -- NOT FK
-- rev_id of the given revision
- rc_this_oldid int REFERENCES /*_*/revision(rev_id),
+ rc_this_oldid int, -- NOT FK
-- rev_id of the prior revision, for generating diff links.
- rc_last_oldid int REFERENCES /*_*/revision(rev_id),
+ rc_last_oldid int, -- NOT FK
-- The type of change entry (RC_EDIT,RC_NEW,RC_LOG,RC_EXTERNAL)
rc_type tinyint NOT NULL default 0,
@@ -969,7 +969,7 @@
log_timestamp varchar(14) NOT NULL default '',
-- The user who performed this action; key to user_id
- log_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
+ log_user int, -- NOT an FK, if a user is deleted we still want to maintain a
record of who did a thing
-- Name of the user who performed this action
log_user_text nvarchar(255) NOT NULL default '',
@@ -978,7 +978,7 @@
-- this will point to the user page.
log_namespace int NOT NULL default 0,
log_title nvarchar(255) NOT NULL default '',
- log_page int NULL REFERENCES /*_*/page(page_id) ON DELETE SET NULL,
+ log_page int NULL, -- NOT an FK, logging entries are inserted for deleted
pages which still reference the deleted page ids
-- Freeform text. Interpreted as edit history comments.
log_comment nvarchar(255) NOT NULL default '',
@@ -1003,7 +1003,7 @@
INSERT INTO /*_*/logging (log_user,log_page,log_params) VALUES(0,0,'');
-ALTER TABLE /*_*/recentchanges ADD CONSTRAINT FK_rc_logid_log_id FOREIGN KEY
(rc_logid) REFERENCES /*_*/logging(log_id) ON DELETE CASCADE;
+ALTER TABLE /*_*/recentchanges ADD CONSTRAINT rc_logid__log_id__fk FOREIGN KEY
(rc_logid) REFERENCES /*_*/logging(log_id) ON DELETE CASCADE;
CREATE TABLE /*_*/log_search (
-- The type of ID (rev ID, log ID, rev timestamp, username)
--
To view, visit https://gerrit.wikimedia.org/r/287049
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I79938d36a8e35debd3e27052d8ef0e3e6c32f40c
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Skizzerz <skizz...@skizzerz.net>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
