From 78ee469e83ea3e5f80ed584f93762321842150da Mon Sep 17 00:00:00 2001 From: Alexander Ebert Date: Sun, 20 Nov 2016 20:30:09 +0100 Subject: [PATCH] Use a combined script to drop columns --- com.woltlab.wcf/package.xml | 1 + com.woltlab.wcf/update_2.sql | 28 ----------- com.woltlab.wcf/update_3.sql | 23 +++++++-- com.woltlab.wcf/update_4.sql | 3 -- ...update_com.woltlab.wcf_3.0_dropColumns.php | 48 +++++++++++++++++++ 5 files changed, 69 insertions(+), 34 deletions(-) create mode 100644 wcfsetup/install/files/acp/update_com.woltlab.wcf_3.0_dropColumns.php diff --git a/com.woltlab.wcf/package.xml b/com.woltlab.wcf/package.xml index bf79a4b854..5e60ae8caf 100644 --- a/com.woltlab.wcf/package.xml +++ b/com.woltlab.wcf/package.xml @@ -60,6 +60,7 @@ acp/update_com.woltlab.wcf_3.0_columnLength.php update_1.sql update_2.sql + acp/update_com.woltlab.wcf_3.0_dropColumns.php update_3.sql update_4.sql update_5.sql diff --git a/com.woltlab.wcf/update_2.sql b/com.woltlab.wcf/update_2.sql index 45175cf428..9c6a1573a5 100644 --- a/com.woltlab.wcf/update_2.sql +++ b/com.woltlab.wcf/update_2.sql @@ -77,31 +77,3 @@ ALTER TABLE wcf1_modification_log ADD parentObjectID INT(10); ALTER TABLE wcf1_package_update_version DROP COLUMN isCritical; DROP TABLE IF EXISTS wcf1_page_menu_item; - -ALTER TABLE wcf1_session DROP COLUMN controller; -ALTER TABLE wcf1_session DROP COLUMN parentObjectType; -ALTER TABLE wcf1_session DROP COLUMN parentObjectID; -ALTER TABLE wcf1_session DROP COLUMN objectType; -ALTER TABLE wcf1_session DROP COLUMN objectID; -ALTER TABLE wcf1_session DROP COLUMN sessionVariables; - -ALTER TABLE wcf1_session ADD pageID INT(10); -ALTER TABLE wcf1_session ADD pageObjectID INT(10); -ALTER TABLE wcf1_session ADD parentPageID INT(10); -ALTER TABLE wcf1_session ADD parentPageObjectID INT(10); -ALTER TABLE wcf1_session ADD KEY pageID (pageID, pageObjectID); -ALTER TABLE wcf1_session ADD KEY parentPageID (parentPageID, parentPageObjectID); - -ALTER TABLE wcf1_session_virtual ADD sessionVariables MEDIUMTEXT; - -DROP TABLE IF EXISTS wcf1_sitemap; - -ALTER TABLE wcf1_smiley ADD smileyPath2x VARCHAR(255) NOT NULL DEFAULT ''; - -ALTER TABLE wcf1_style ADD packageName VARCHAR(255) NOT NULL DEFAULT ''; -ALTER TABLE wcf1_style ADD isTainted TINYINT(1) NOT NULL DEFAULT 0; - -ALTER TABLE wcf1_template_listener ADD permissions TEXT; -ALTER TABLE wcf1_template_listener ADD options TEXT; - -ALTER TABLE wcf1_user CHANGE lostPasswordKey lostPasswordKey CHAR(40) DEFAULT NULL; \ No newline at end of file diff --git a/com.woltlab.wcf/update_3.sql b/com.woltlab.wcf/update_3.sql index 0d6ddcc823..9d98bf1a12 100644 --- a/com.woltlab.wcf/update_3.sql +++ b/com.woltlab.wcf/update_3.sql @@ -1,3 +1,20 @@ -ALTER TABLE wcf1_user DROP COLUMN signatureEnableBBCodes; -ALTER TABLE wcf1_user DROP COLUMN signatureEnableSmilies; -ALTER TABLE wcf1_user DROP COLUMN socialNetworkPrivacySettings; +ALTER TABLE wcf1_session ADD pageID INT(10); +ALTER TABLE wcf1_session ADD pageObjectID INT(10); +ALTER TABLE wcf1_session ADD parentPageID INT(10); +ALTER TABLE wcf1_session ADD parentPageObjectID INT(10); +ALTER TABLE wcf1_session ADD KEY pageID (pageID, pageObjectID); +ALTER TABLE wcf1_session ADD KEY parentPageID (parentPageID, parentPageObjectID); + +ALTER TABLE wcf1_session_virtual ADD sessionVariables MEDIUMTEXT; + +DROP TABLE IF EXISTS wcf1_sitemap; + +ALTER TABLE wcf1_smiley ADD smileyPath2x VARCHAR(255) NOT NULL DEFAULT ''; + +ALTER TABLE wcf1_style ADD packageName VARCHAR(255) NOT NULL DEFAULT ''; +ALTER TABLE wcf1_style ADD isTainted TINYINT(1) NOT NULL DEFAULT 0; + +ALTER TABLE wcf1_template_listener ADD permissions TEXT; +ALTER TABLE wcf1_template_listener ADD options TEXT; + +ALTER TABLE wcf1_user CHANGE lostPasswordKey lostPasswordKey CHAR(40) DEFAULT NULL; diff --git a/com.woltlab.wcf/update_4.sql b/com.woltlab.wcf/update_4.sql index c48729f004..4414a25f8b 100644 --- a/com.woltlab.wcf/update_4.sql +++ b/com.woltlab.wcf/update_4.sql @@ -1,6 +1,3 @@ -ALTER TABLE wcf1_user_avatar DROP COLUMN cropX; -ALTER TABLE wcf1_user_avatar DROP COLUMN cropY; - ALTER TABLE wcf1_user_notification ADD KEY (confirmTime); DELETE FROM wcf1_user_profile_visitor WHERE userID IS NULL OR ownerID IS NULL; diff --git a/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.0_dropColumns.php b/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.0_dropColumns.php new file mode 100644 index 0000000000..cac9ea3bd1 --- /dev/null +++ b/wcfsetup/install/files/acp/update_com.woltlab.wcf_3.0_dropColumns.php @@ -0,0 +1,48 @@ + + * @package WoltLabSuite\Core + */ +$data = <<getVar('__wcfUpdateDropColumns'); +if ($rebuildData === null) { + $rebuildData = [ + 'i' => 0, + 'max' => count($lines) + ]; +} + +// MySQL drops a column by creating a new table in the +// background, copying over all data except from the +// deleted column and uses this table afterwards. +// +// Using a single `ALTER TABLE` to drop multiple columns +// results in the same runtime, because copying the table +// is what actually takes ages. +$statement = WCF::getDB()->prepareStatement($lines[$rebuildData['i']]); +$statement->execute(); + +$rebuildData['i']++; + +if ($rebuildData['i'] === $rebuildData['max']) { + WCF::getSession()->unregister('__wcfUpdateDropColumns'); +} +else { + WCF::getSession()->register('__wcfUpdateDropColumns', $rebuildData); + + // call this script again + throw new SplitNodeException(); +} -- 2.20.1