From 94acb0431ea554da25b1994e93705fe82057afce Mon Sep 17 00:00:00 2001 From: Alexander Ebert Date: Tue, 8 Aug 2017 15:26:13 +0200 Subject: [PATCH] Improved upgrade, split SQL into separate requests --- com.woltlab.wcf/files_pre_update.tar | Bin 4608 -> 0 bytes com.woltlab.wcf/package.xml | 11 ++- com.woltlab.wcf/update_3.1_1.sql | 4 ++ com.woltlab.wcf/update_3.1_2.sql | 4 ++ .../{update_3.1.sql => update_3.1_3.sql} | 58 --------------- com.woltlab.wcf/update_3.1_4.sql | 1 + .../update_com.woltlab.wcf_3.1_addColumn.php | 66 ++++++++++++++++++ ...te_com.woltlab.wcf_3.1_pageSearchIndex.php | 6 ++ 8 files changed, 90 insertions(+), 60 deletions(-) delete mode 100644 com.woltlab.wcf/files_pre_update.tar create mode 100644 com.woltlab.wcf/update_3.1_1.sql create mode 100644 com.woltlab.wcf/update_3.1_2.sql rename com.woltlab.wcf/{update_3.1.sql => update_3.1_3.sql} (61%) create mode 100644 com.woltlab.wcf/update_3.1_4.sql create mode 100644 wcfsetup/install/files/acp/update_com.woltlab.wcf_3.1_addColumn.php diff --git a/com.woltlab.wcf/files_pre_update.tar b/com.woltlab.wcf/files_pre_update.tar deleted file mode 100644 index e2b50d596ec9a4e102d5faa16e07c6187f5843f3..0000000000000000000000000000000000000000 GIT binary patch literal 0 HcmV?d00001 literal 4608 zcmeHJZExC05YE>_;y=tql`s`AHZS2yj;0PQhgGmsW71TYleIAmSaEE+UN^_TzgfVP zhNg*f$*IzTguv^0d1juO*`eONeDJ~_RaG1HI_!AC^Yn9|RaN!qs8O$JjRQQaS8D1( zeMjR34w6_<4!hu9z}3%ak|O`fd`ty%z38TN8wDbuSEXBT;#NzlJLk-eKL@4xZ2olg z$mdb5wwvNL`PGVAUGd-0)Jj7|{MN?B?=g zyL+6?l*{1FXgFoSLN6No;S{2Y6w_QR8#iWg>__1-gv`UglrLZsartHbG=yum06*Lj zcfa^tBs9ooUSVND3l8{^Xhb8pxnx(^SC(yRcb7DK-SkUh2w%(RHL*hUp zCcoWW)li_{*Bz2RyGiIHW~^%e5JP!|U5aU9g6(wy- zZA(+W_UHO|%+WZU>-JKj`ew^^hjjH(-+^3C3S*ISeJVLjg|#=@z0v`Vh3*Oei!Tr`FXipy!nP^eSL63`oZq4IFae$k!v&_%I?eDo{2t6<~wE02_4qY$gxH{KGIw z%Lv1$a|)>^86g&J>rT<8&;L=;t~rHu9Luit63%62WfWgq(=dm!>5y~l!J2(AF9o$$B1px=-3p`oidZKgW-;1zp=3+zT5%_J~cs`oXzyveR~#-_FFRPK`g)iL-7a zId(Qh^w-^)lG5Tu(Y4RAnRhcr$BZkvArr;+@~{cH5~sHb50;aeDQ(MoZxHv)CSBtK zN|0MA_h>wmV>Fj91A8vaUx&Kvh`L8szn%w`Y4Ncqcf6vbLuEF*GSAGp0Yk8;RHEx~biqJEL&^7wx zS11-ik0BO(xeC%4d;tNDp|}8I=Hp`wbZZB^jretu@+b+%@VdNj@7 - update_3.1.sql + files_pre_sql.tar + + acp/update_com.woltlab.wcf_3.1_addColumn.php + + update_3.1_1.sql + update_3.1_2.sql + update_3.1_3.sql + update_3.1_4.sql @@ -61,7 +68,7 @@ - + diff --git a/com.woltlab.wcf/update_3.1_1.sql b/com.woltlab.wcf/update_3.1_1.sql new file mode 100644 index 0000000000..c1d7a7362a --- /dev/null +++ b/com.woltlab.wcf/update_3.1_1.sql @@ -0,0 +1,4 @@ +ALTER TABLE wcf1_comment DROP FOREIGN KEY objectTypeID; +ALTER TABLE wcf1_comment DROP KEY objectTypeID; +ALTER TABLE wcf1_comment ADD KEY (objectTypeID, objectID, isDisabled, time); +ALTER TABLE wcf1_comment ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; diff --git a/com.woltlab.wcf/update_3.1_2.sql b/com.woltlab.wcf/update_3.1_2.sql new file mode 100644 index 0000000000..0f9fa11daf --- /dev/null +++ b/com.woltlab.wcf/update_3.1_2.sql @@ -0,0 +1,4 @@ +ALTER TABLE wcf1_comment_response DROP FOREIGN KEY commentID; +ALTER TABLE wcf1_comment_response DROP KEY commentID; +ALTER TABLE wcf1_comment_response ADD KEY (commentID, isDisabled, time); +ALTER TABLE wcf1_comment_response ADD FOREIGN KEY (commentID) REFERENCES wcf1_comment (commentID) ON DELETE CASCADE; diff --git a/com.woltlab.wcf/update_3.1.sql b/com.woltlab.wcf/update_3.1_3.sql similarity index 61% rename from com.woltlab.wcf/update_3.1.sql rename to com.woltlab.wcf/update_3.1_3.sql index 692503eff6..d2fa2bcad1 100644 --- a/com.woltlab.wcf/update_3.1.sql +++ b/com.woltlab.wcf/update_3.1_3.sql @@ -1,37 +1,8 @@ -ALTER TABLE wcf1_article ADD COLUMN isDeleted TINYINT(1) NOT NULL DEFAULT 0; -ALTER TABLE wcf1_article ADD COLUMN hasLabels TINYINT(1) NOT NULL DEFAULT 0; - -ALTER TABLE wcf1_article_content ADD COLUMN teaserImageID INT(10); - -ALTER TABLE wcf1_bbcode_media_provider ADD COLUMN name VARCHAR(80) NOT NULL; -ALTER TABLE wcf1_bbcode_media_provider ADD COLUMN packageID INT(10) NOT NULL; -ALTER TABLE wcf1_bbcode_media_provider ADD COLUMN className varchar(255) NOT NULL DEFAULT ''; - -- remove default media providers (they'll be re-added later during the upgrade) DELETE FROM wcf1_bbcode_media_provider WHERE title IN ('YouTube', 'YouTube Playlist', 'Vimeo', 'Clipfish', 'Veoh', 'DailyMotion', 'github gist', 'Soundcloud', 'Soundcloud set'); UPDATE wcf1_bbcode_media_provider SET name = CONCAT('com.woltlab.wcf.generic', providerID); ALTER TABLE wcf1_bbcode_media_provider ADD UNIQUE KEY name (name, packageID); -ALTER TABLE wcf1_box ADD COLUMN lastUpdateTime INT(10) NOT NULL DEFAULT 0; - -ALTER TABLE wcf1_comment ADD COLUMN unfilteredResponses MEDIUMINT(7) NOT NULL DEFAULT '0'; -ALTER TABLE wcf1_comment ADD COLUMN unfilteredResponseIDs VARCHAR(255) NOT NULL DEFAULT ''; -ALTER TABLE wcf1_comment ADD COLUMN enableHtml TINYINT(1) NOT NULL DEFAULT 0; -ALTER TABLE wcf1_comment ADD COLUMN isDisabled TINYINT(1) NOT NULL DEFAULT 0; - --- WARNING: May be slow, use a separate request? // the foreign key relies on the index `objectTypeID` -ALTER TABLE wcf1_comment DROP FOREIGN KEY objectTypeID; -ALTER TABLE wcf1_comment DROP KEY objectTypeID; -ALTER TABLE wcf1_comment ADD KEY (objectTypeID, objectID, isDisabled, time); -ALTER TABLE wcf1_comment ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; - -ALTER TABLE wcf1_comment_response ADD COLUMN isDisabled TINYINT(1) NOT NULL DEFAULT 0; --- WARNING: May be slow, use a separate request? // the foreign key relies on the index `commentID` -ALTER TABLE wcf1_comment_response DROP FOREIGN KEY commentID; -ALTER TABLE wcf1_comment_response DROP KEY commentID; -ALTER TABLE wcf1_comment_response ADD KEY (commentID, isDisabled, time); -ALTER TABLE wcf1_comment_response ADD FOREIGN KEY (commentID) REFERENCES wcf1_comment (commentID) ON DELETE CASCADE; - DROP TABLE IF EXISTS wcf1_contact_option; CREATE TABLE wcf1_contact_option ( optionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, @@ -67,26 +38,8 @@ CREATE TABLE wcf1_devtools_project ( UNIQUE KEY name (name) ); --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_language_item ADD COLUMN languageItemOldValue MEDIUMTEXT; --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_language_item ADD COLUMN languageCustomItemDisableTime INT(10); - -ALTER TABLE wcf1_media ADD COLUMN categoryID INT(10); - --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_modification_log ADD COLUMN hidden TINYINT(1) NOT NULL DEFAULT 0; - --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_package_update ADD COLUMN pluginStoreFileID INT(10) NOT NULL DEFAULT 0; - ALTER TABLE wcf1_package_update_server CHANGE COLUMN apiVersion apiVersion ENUM('2.0', '2.1', '3.1') NOT NULL DEFAULT '2.0'; -ALTER TABLE wcf1_page ADD COLUMN cssClassName VARCHAR(255) NOT NULL DEFAULT ''; -ALTER TABLE wcf1_page ADD COLUMN availableDuringOfflineMode TINYINT(1) NOT NULL DEFAULT 0; -ALTER TABLE wcf1_page ADD COLUMN allowSpidersToIndex TINYINT(1) NOT NULL DEFAULT 0; -ALTER TABLE wcf1_page ADD COLUMN excludeFromLandingPage TINYINT(1) NOT NULL DEFAULT 0; - DROP TABLE IF EXISTS wcf1_page_box_order; CREATE TABLE wcf1_page_box_order ( pageID INT(10) NOT NULL, @@ -95,10 +48,6 @@ CREATE TABLE wcf1_page_box_order ( UNIQUE KEY pageToBox (pageID, boxID) ); -ALTER TABLE wcf1_paid_subscription_user ADD COLUMN sentExpirationNotification TINYINT(1) NOT NULL DEFAULT 0; - -ALTER TABLE wcf1_style ADD hasFavicon TINYINT(1) NOT NULL DEFAULT 0; - DROP TABLE IF EXISTS wcf1_trophy; CREATE TABLE wcf1_trophy( trophyID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, @@ -115,11 +64,6 @@ CREATE TABLE wcf1_trophy( KEY(categoryID) ); --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_user ADD COLUMN trophyPoints INT(10) NOT NULL DEFAULT 0; --- WARNING: May be slow, use a separate request? -ALTER TABLE wcf1_user ADD KEY trophyPoints (trophyPoints); - DROP TABLE IF EXISTS wcf1_user_special_trophy; CREATE TABLE wcf1_user_special_trophy( trophyID INT(10) NOT NULL, @@ -138,8 +82,6 @@ CREATE TABLE wcf1_user_trophy( KEY(trophyID, time) ); -ALTER TABLE wcf1_user_rank ADD COLUMN hideTitle TINYINT(1) NOT NULL DEFAULT 0; - ALTER TABLE wcf1_article_content ADD FOREIGN KEY (teaserImageID) REFERENCES wcf1_media (mediaID) ON DELETE SET NULL; ALTER TABLE wcf1_bbcode_media_provider ADD FOREIGN KEY (packageID) REFERENCES wcf1_package (packageID) ON DELETE CASCADE; ALTER TABLE wcf1_media ADD FOREIGN KEY (categoryID) REFERENCES wcf1_category (categoryID) ON DELETE SET NULL; diff --git a/com.woltlab.wcf/update_3.1_4.sql b/com.woltlab.wcf/update_3.1_4.sql new file mode 100644 index 0000000000..6b24d572ac --- /dev/null +++ b/com.woltlab.wcf/update_3.1_4.sql @@ -0,0 +1 @@ +ALTER TABLE wcf1_user ADD KEY trophyPoints (trophyPoints); diff --git a/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.1_addColumn.php b/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.1_addColumn.php new file mode 100644 index 0000000000..672ce832c4 --- /dev/null +++ b/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.1_addColumn.php @@ -0,0 +1,66 @@ + + * @package WoltLabSuite\Core + */ +$data = <<