From 9d3b749fd9b71de1e3a6b096b082988066d49231 Mon Sep 17 00:00:00 2001 From: Matthias Schmidt Date: Thu, 27 Jun 2019 18:34:51 +0200 Subject: [PATCH] Use `PreparedStatementConditionBuilder` in bulk-optimized queries --- .../data/AbstractDatabaseObjectAction.class.php | 15 +++++++++++---- .../files/lib/data/DatabaseObjectEditor.class.php | 8 ++++++-- .../lib/system/edit/EditHistoryManager.class.php | 10 +++++++--- .../AbstractModerationQueueManager.class.php | 7 +++++-- .../mysql/MysqlSearchIndexManager.class.php | 8 ++++++-- 5 files changed, 35 insertions(+), 13 deletions(-) diff --git a/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php b/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php index 4d8c9f508a..332b2955d4 100644 --- a/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php +++ b/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php @@ -1,5 +1,6 @@ objectIDs, $i * $itemsPerLoop, $itemsPerLoop); + $conditionBuilder = new PreparedStatementConditionBuilder(); + $conditionBuilder->add($indexName . ' IN (?)', [$batchObjectIDs]); + $sql = "UPDATE " . $tableName . " SET " . $updateSQL . " - WHERE " . $indexName . " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); - $statement->execute(array_merge($statementParameters, $batchObjectIDs)); + $statement->execute(array_merge($statementParameters, $conditionBuilder->getParameters())); } WCF::getDB()->commitTransaction(); } @@ -403,11 +407,14 @@ abstract class AbstractDatabaseObjectAction implements IDatabaseObjectAction, ID for ($i = 0; $i < $loopCount; $i++) { $batchObjectIDs = array_slice($this->objectIDs, $i * $itemsPerLoop, $itemsPerLoop); + $conditionBuilder = new PreparedStatementConditionBuilder(); + $conditionBuilder->add($indexName . ' IN (?)', [$batchObjectIDs]); + $sql = "UPDATE " . $tableName . " SET " . $updateSQL . " - WHERE " . $indexName . " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); - $statement->execute(array_merge($statementParameters, $batchObjectIDs)); + $statement->execute(array_merge($statementParameters, $conditionBuilder->getParameters())); } WCF::getDB()->commitTransaction(); } diff --git a/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php b/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php index d367c1c9fc..19cea93519 100644 --- a/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php +++ b/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php @@ -1,5 +1,6 @@ add(static::getDatabaseTableIndexName() . ' IN (?)', [$batchObjectIDs]); + $sql = "DELETE FROM " . static::getDatabaseTableName() . " - WHERE " . static::getDatabaseTableIndexName() . " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); - $statement->execute($batchObjectIDs); + $statement->execute($conditionBuilder->getParameters()); $affectedCount += $statement->getAffectedRows(); } WCF::getDB()->commitTransaction(); diff --git a/wcfsetup/install/files/lib/system/edit/EditHistoryManager.class.php b/wcfsetup/install/files/lib/system/edit/EditHistoryManager.class.php index 1567330c0a..1ec3fb6192 100644 --- a/wcfsetup/install/files/lib/system/edit/EditHistoryManager.class.php +++ b/wcfsetup/install/files/lib/system/edit/EditHistoryManager.class.php @@ -2,6 +2,7 @@ namespace wcf\system\edit; use wcf\data\edit\history\entry\EditHistoryEntryList; use wcf\data\object\type\ObjectTypeCache; +use wcf\system\database\util\PreparedStatementConditionBuilder; use wcf\system\exception\SystemException; use wcf\system\SingletonFactory; use wcf\system\WCF; @@ -84,11 +85,14 @@ class EditHistoryManager extends SingletonFactory { for ($i = 0; $i < $loopCount; $i++) { $batchObjectIDs = array_slice($objectIDs, $i * $itemsPerLoop, $itemsPerLoop); + $conditionBuilder = new PreparedStatementConditionBuilder(); + $conditionBuilder->add('objectTypeID = ?', [$objectTypeID]); + $conditionBuilder->add('objectID IN (?)', [$batchObjectIDs]); + $sql = "DELETE FROM wcf".WCF_N."_edit_history_entry - WHERE objectTypeID = ? - AND objectID IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); - $statement->execute(array_merge([$objectTypeID], $batchObjectIDs)); + $statement->execute($conditionBuilder->getParameters()); } WCF::getDB()->commitTransaction(); } diff --git a/wcfsetup/install/files/lib/system/moderation/queue/AbstractModerationQueueManager.class.php b/wcfsetup/install/files/lib/system/moderation/queue/AbstractModerationQueueManager.class.php index 370c45fda7..853698b89a 100644 --- a/wcfsetup/install/files/lib/system/moderation/queue/AbstractModerationQueueManager.class.php +++ b/wcfsetup/install/files/lib/system/moderation/queue/AbstractModerationQueueManager.class.php @@ -199,6 +199,9 @@ abstract class AbstractModerationQueueManager extends SingletonFactory implement for ($i = 0; $i < $batchCount; $i++) { $batchQueueIDs = array_slice($queueIDs, $i * $itemsPerLoop, $itemsPerLoop); + $conditionBuilder = new PreparedStatementConditionBuilder(); + $conditionBuilder->add('queueID IN (?)', [$batchQueueIDs]); + $sql = "UPDATE wcf" . WCF_N . "_moderation_queue SET status = ?, containerID = ?, @@ -206,7 +209,7 @@ abstract class AbstractModerationQueueManager extends SingletonFactory implement time = ?, lastChangeTime = ?, additionalData = ? - WHERE queueID IN (?" . str_repeat(', ?', count($batchQueueIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); $statement->execute(array_merge( [ @@ -217,7 +220,7 @@ abstract class AbstractModerationQueueManager extends SingletonFactory implement TIME_NOW, $serializedData ], - $batchQueueIDs + $conditionBuilder->getParameters() )); } } diff --git a/wcfsetup/install/files/lib/system/search/mysql/MysqlSearchIndexManager.class.php b/wcfsetup/install/files/lib/system/search/mysql/MysqlSearchIndexManager.class.php index 6c17ba7682..1d5ed9ab54 100644 --- a/wcfsetup/install/files/lib/system/search/mysql/MysqlSearchIndexManager.class.php +++ b/wcfsetup/install/files/lib/system/search/mysql/MysqlSearchIndexManager.class.php @@ -2,6 +2,7 @@ namespace wcf\system\search\mysql; use wcf\data\object\type\ObjectType; use wcf\system\database\exception\DatabaseQueryExecutionException; +use wcf\system\database\util\PreparedStatementConditionBuilder; use wcf\system\search\AbstractSearchIndexManager; use wcf\system\search\SearchIndexManager; use wcf\system\WCF; @@ -52,10 +53,13 @@ class MysqlSearchIndexManager extends AbstractSearchIndexManager { for ($i = 0; $i < $loopCount; $i++) { $batchObjectIDs = array_slice($objectIDs, $i * $itemsPerLoop, $itemsPerLoop); + $conditionBuilder = new PreparedStatementConditionBuilder(); + $conditionBuilder->add('objectID IN (?)', [$batchObjectIDs]); + $sql = "DELETE FROM " . $tableName . " - WHERE objectID IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + " . $conditionBuilder; $statement = WCF::getDB()->prepareStatement($sql); - $statement->execute($batchObjectIDs); + $statement->execute($conditionBuilder->getParameters()); } WCF::getDB()->commitTransaction(); } -- 2.20.1