From 4ea08502b1b33e334752eadf02fda5d687ec219a Mon Sep 17 00:00:00 2001 From: Ryan Schmidt Date: Wed, 4 May 2016 22:27:28 -0700 Subject: [PATCH] Fix deleting pages/files when using Microsoft SQL Server Right now we have a slew of foreign key violations whenever we try to delete something due to the 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 --- includes/installer/MssqlUpdater.php | 6 +- .../mssql/archives/patch-archive-drop-fks.sql | 59 ++++++++++++++ .../mssql/archives/patch-logging-drop-fks.sql | 37 +++++++++ .../archives/patch-recentchanges-drop-fks.sql | 76 +++++++++++++++++++ maintenance/mssql/tables.sql | 22 +++--- 5 files changed, 188 insertions(+), 12 deletions(-) create mode 100644 maintenance/mssql/archives/patch-archive-drop-fks.sql create mode 100644 maintenance/mssql/archives/patch-logging-drop-fks.sql create mode 100644 maintenance/mssql/archives/patch-recentchanges-drop-fks.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index a6ab05c0b7..accc42fd2f 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -83,7 +83,11 @@ class MssqlUpdater extends DatabaseUpdater { [ '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', '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 0000000000..3055ac9863 --- /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 0000000000..c9cbca35fd --- /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 0000000000..24f78f68bb --- /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 bd1a1b5652..12cfed8e71 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -206,17 +206,17 @@ CREATE TABLE /*_*/archive ( 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 @@ -785,11 +785,11 @@ CREATE INDEX /*i*/us_timestamp ON /*_*/uploadstash (us_timestamp); -- 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 '', -- 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. @@ -811,13 +811,13 @@ CREATE TABLE /*_*/recentchanges ( -- 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, @@ -986,7 +986,7 @@ CREATE TABLE /*_*/logging ( 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 '', @@ -995,7 +995,7 @@ CREATE TABLE /*_*/logging ( -- 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 '', @@ -1020,7 +1020,7 @@ CREATE INDEX /*i*/log_user_text_time ON /*_*/logging (log_user_text, log_timesta 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) -- 2.20.1