diff options
author | Anna Larch <anna@nextcloud.com> | 2021-04-13 14:31:43 +0300 |
---|---|---|
committer | Christoph Wurst <christoph@winzerhof-wurst.at> | 2021-05-20 18:02:52 +0300 |
commit | 93a9a4baefce23da225e6106baef1445657ee25e (patch) | |
tree | 716a41c0ab963ef6d1ec279246ddb4aabcfaaabc | |
parent | 39853c80d1c1614b922800f56338b73ef0859218 (diff) |
chunking in statements
Signed-off-by: Anna Larch <anna@nextcloud.com>
-rw-r--r-- | lib/Db/AliasMapper.php | 10 | ||||
-rw-r--r-- | lib/Db/CollectedAddressMapper.php | 7 | ||||
-rw-r--r-- | lib/Db/MailboxMapper.php | 10 | ||||
-rw-r--r-- | lib/Db/MessageMapper.php | 135 | ||||
-rw-r--r-- | lib/Db/TagMapper.php | 38 | ||||
-rw-r--r-- | tests/psalm-baseline.xml | 34 |
6 files changed, 139 insertions, 95 deletions
diff --git a/lib/Db/AliasMapper.php b/lib/Db/AliasMapper.php index ea2607503..bf2e17b5d 100644 --- a/lib/Db/AliasMapper.php +++ b/lib/Db/AliasMapper.php @@ -102,9 +102,11 @@ class AliasMapper extends QBMapper { $result->closeCursor(); $qb2 = $this->db->getQueryBuilder(); - $query = $qb2 - ->delete($this->getTableName()) - ->where($qb2->expr()->in('id', $qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); - $query->execute(); + foreach (array_chunk($ids, 1000) as $chunk) { + $query = $qb2 + ->delete($this->getTableName()) + ->where($qb2->expr()->in('id', $qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); + $query->execute(); + } } } diff --git a/lib/Db/CollectedAddressMapper.php b/lib/Db/CollectedAddressMapper.php index bd741fcf4..b5ae92bb6 100644 --- a/lib/Db/CollectedAddressMapper.php +++ b/lib/Db/CollectedAddressMapper.php @@ -121,7 +121,10 @@ class CollectedAddressMapper extends QBMapper { $qb2 = $this->db->getQueryBuilder(); $query = $qb2 ->delete($this->getTableName()) - ->where($qb2->expr()->in('id', $qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); - $query->execute(); + ->where($qb2->expr()->in('id', $qb2->createParameter('ids'))); + foreach (array_chunk($ids, 1000) as $chunk) { + $query->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $query->execute(); + } } } diff --git a/lib/Db/MailboxMapper.php b/lib/Db/MailboxMapper.php index 801fd64ca..c7323619e 100644 --- a/lib/Db/MailboxMapper.php +++ b/lib/Db/MailboxMapper.php @@ -241,9 +241,11 @@ class MailboxMapper extends QBMapper { $result->closeCursor(); $qb2 = $this->db->getQueryBuilder(); - $query = $qb2 - ->delete($this->getTableName()) - ->where($qb2->expr()->in('id', $qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); - $query->execute(); + foreach (array_chunk($ids, 1000) as $chunk) { + $query = $qb2 + ->delete($this->getTableName()) + ->where($qb2->expr()->in('id', $qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); + $query->execute(); + } } } diff --git a/lib/Db/MessageMapper.php b/lib/Db/MessageMapper.php index ef63d3312..6b79fb38a 100644 --- a/lib/Db/MessageMapper.php +++ b/lib/Db/MessageMapper.php @@ -47,7 +47,7 @@ use OCA\Mail\Service\Search\FlagExpression; use OCA\Mail\IMAP\Threading\DatabaseMessage; use OCA\Mail\Support\PerformanceLogger; use OCP\AppFramework\Db\DoesNotExistException; -use function OCA\Mail\array_flat_map; +use function \OCA\Mail\array_flat_map; /** * @template-extends QBMapper<Message> @@ -494,11 +494,15 @@ class MessageMapper extends QBMapper { return (int)$row['id']; }, $messageIds); - // delete all related recipient entries $deleteRecipientsQuery = $this->db->getQueryBuilder(); $deleteRecipientsQuery->delete('mail_recipients') - ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createNamedParameter($messageIds, IQueryBuilder::PARAM_INT_ARRAY))); - $deleteRecipientsQuery->execute(); + ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createParameter('ids'))); + + foreach (array_chunk($messageIds, 1000) as $chunk) { + // delete all related recipient entries + $deleteRecipientsQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $deleteRecipientsQuery->execute(); + } $query = $this->db->getQueryBuilder(); @@ -510,36 +514,40 @@ class MessageMapper extends QBMapper { public function deleteByUid(Mailbox $mailbox, int ...$uids): void { $messageIdQuery = $this->db->getQueryBuilder(); + $deleteRecipientsQuery = $this->db->getQueryBuilder(); + $deleteMessagesQuery = $this->db->getQueryBuilder(); + + // Get all message ids query $messageIdQuery->select('id') ->from($this->getTableName()) ->where( $messageIdQuery->expr()->eq('mailbox_id', $messageIdQuery->createNamedParameter($mailbox->getId())), - $messageIdQuery->expr()->in('uid', $messageIdQuery->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)) + $messageIdQuery->expr()->in('uid', $messageIdQuery->createParameter('uids')) ); - $cursor = $messageIdQuery->execute(); - $messageIds = $cursor->fetchAll(); - $cursor->closeCursor(); + $deleteRecipientsQuery->delete('mail_recipients') + ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createParameter('messageIds'))); - $messageIds = array_map(function (array $row) { - return (int)$row['id']; - }, $messageIds); + $deleteMessagesQuery->delete($this->getTableName()) + ->where($deleteMessagesQuery->expr()->in('id', $deleteMessagesQuery->createParameter('messageIds'))); - // delete all related recipient entries - $deleteRecipientsQuery = $this->db->getQueryBuilder(); - $deleteRecipientsQuery->delete('mail_recipients') - ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createNamedParameter($messageIds, IQueryBuilder::PARAM_INT_ARRAY))); - $deleteRecipientsQuery->execute(); + foreach (array_chunk($uids, 1000) as $chunk) { + $messageIdQuery->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $cursor = $messageIdQuery->execute(); - $query = $this->db->getQueryBuilder(); + $messageIds = array_map(function (array $message) { + return $message['id']; + }, $cursor->fetchAll()); + $cursor->closeCursor(); - $query->delete($this->getTableName()) - ->where( - $query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId())), - $query->expr()->in('uid', $query->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)) - ); + // delete all related recipient entries + $deleteRecipientsQuery->setParameter('messageIds', $messageIds, IQueryBuilder::PARAM_INT_ARRAY); + $deleteRecipientsQuery->execute(); - $query->execute(); + // delete all messages + $deleteMessagesQuery->setParameter('messageIds', $messageIds, IQueryBuilder::PARAM_INT_ARRAY); + $deleteMessagesQuery->execute(); + } } /** @@ -647,9 +655,10 @@ class MessageMapper extends QBMapper { $qb->expr()->lt('sent_at', $qb->createNamedParameter($query->getCursor(), IQueryBuilder::PARAM_INT)) ); } + // createParameter if ($uids !== null) { $select->andWhere( - $qb->expr()->in('uid', $qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)) + $qb->expr()->in('uid', $qb->createParameter('uids')) ); } foreach ($query->getFlags() as $flag) { @@ -670,6 +679,15 @@ class MessageMapper extends QBMapper { $select = $select->setMaxResults($limit); } + if ($uids !== null) { + return array_flat_map(function (array $chunk) use ($qb, $select) { + $qb->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + return array_map(function (Message $message) { + return $message->getId(); + }, $this->findEntities($select)); + }, array_chunk($uids, 1000)); + } + return array_map(function (Message $message) { return $message->getId(); }, $this->findEntities($select)); @@ -747,7 +765,7 @@ class MessageMapper extends QBMapper { } if ($uids !== null) { $select->andWhere( - $qb->expr()->in('uid', $qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)) + $qb->expr()->in('uid', $qb->createParameter('uids')) ); } foreach ($query->getFlags() as $flag) { @@ -768,6 +786,15 @@ class MessageMapper extends QBMapper { $select = $select->setMaxResults($limit); } + if ($uids !== null) { + return array_flat_map(function (array $chunk) use ($select) { + $select->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + return array_map(function (Message $message) { + return $message->getId(); + }, $this->findEntities($select)); + }, array_chunk($uids, 1000)); + } + return array_map(function (Message $message) { return $message->getId(); }, $this->findEntities($select)); @@ -839,17 +866,21 @@ class MessageMapper extends QBMapper { if (empty($ids)) { return []; } - $qb = $this->db->getQueryBuilder(); - $select = $qb - ->select('*') + $qb = $this->db->getQueryBuilder(); + $qb->select('*') ->from($this->getTableName()) ->where( - $qb->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY) + $qb->expr()->in('id', $qb->createParameter('ids')) ) ->orderBy('sent_at', 'desc'); - return $this->findRelatedData($this->findEntities($select), $userId); + $results = []; + foreach (array_chunk($ids, 1000) as $chunk) { + $qb->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $results[] = $this->findRelatedData($this->findEntities($qb), $userId); + } + return array_merge(...$results); } /** @@ -919,30 +950,34 @@ class MessageMapper extends QBMapper { /** * @param Mailbox $mailbox - * @param int $highest - * + * @param array $ids * @return int[] */ public function findNewIds(Mailbox $mailbox, array $ids): array { - $qb = $this->db->getQueryBuilder(); $sub = $this->db->getQueryBuilder(); + $qb = $this->db->getQueryBuilder(); $subSelect = $sub ->select($sub->func()->max('uid')) ->from($this->getTableName()) ->where( $sub->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), - $sub->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY) + $sub->expr()->in('id', $qb->createParameter('ids')) ); - $select = $qb - ->select('id') + + $qb->select('id') ->from($this->getTableName()) ->where( - $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), - $qb->expr()->gt('uid', $qb->createFunction('(' . $subSelect->getSQL() . ')'), IQueryBuilder::PARAM_INT) + $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)) ); - return $this->findIds($select); + return array_flat_map(function (array $chunk) use ($qb, $subSelect) { + $qb->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $select = $qb->andWhere( + $qb->expr()->gt('uid', $qb->createFunction('(' . $subSelect->getSQL() . ')'), IQueryBuilder::PARAM_INT) + ); + return $this->findIds($select); + }, array_chunk($ids, 1000)); } /** @@ -958,7 +993,6 @@ class MessageMapper extends QBMapper { $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), $qb->expr()->gt('updated_at', $qb->createNamedParameter($since, IQueryBuilder::PARAM_INT)) ); - // TODO: change this to findRelatedData return $this->findRelatedData($this->findEntities($select), $account->getUserId()); } @@ -998,11 +1032,12 @@ class MessageMapper extends QBMapper { $result->closeCursor(); $qb2 = $this->db->getQueryBuilder(); - $query = $qb2 - ->delete($this->getTableName()) - ->where($qb2->expr()->in('id', $qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); - $query->execute(); - + foreach (array_chunk($ids, 1000) as $chunk) { + $query = $qb2 + ->delete($this->getTableName()) + ->where($qb2->expr()->in('id', $qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); + $query->execute(); + } $qb3 = $this->db->getQueryBuilder(); $recipientIdsQuery = $qb3->selectDistinct('r.id') ->from('mail_recipients', 'r') @@ -1015,10 +1050,12 @@ class MessageMapper extends QBMapper { $result->closeCursor(); $qb4 = $this->db->getQueryBuilder(); - $recipientsQuery = $qb4 - ->delete('mail_recipients') - ->where($qb4->expr()->in('id', $qb4->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); - $recipientsQuery->execute(); + foreach (array_chunk($ids, 1000) as $chunk) { + $recipientsQuery = $qb4 + ->delete('mail_recipients') + ->where($qb4->expr()->in('id', $qb4->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)); + $recipientsQuery->execute(); + } } public function getIdForUid(Mailbox $mailbox, $uid): ?int { diff --git a/lib/Db/TagMapper.php b/lib/Db/TagMapper.php index 945179e48..c12ae0d59 100644 --- a/lib/Db/TagMapper.php +++ b/lib/Db/TagMapper.php @@ -134,33 +134,37 @@ class TagMapper extends QBMapper { return $message->getMessageId(); }, $messages); + $tags = []; $qb = $this->db->getQueryBuilder(); $tagsQuery = $qb->selectDistinct(['t.*', 'mt.imap_message_id']) ->from($this->getTableName(), 't') ->join('t', 'mail_message_tags', 'mt', $qb->expr()->eq('t.id', 'mt.tag_id', IQueryBuilder::PARAM_INT)) ->where( - $qb->expr()->in('mt.imap_message_id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_STR_ARRAY)), + $qb->expr()->in('mt.imap_message_id', $qb->createParameter('ids')), $qb->expr()->eq('t.user_id', $qb->createNamedParameter($userId, IQueryBuilder::PARAM_STR)) ); - $queryResult = $tagsQuery->execute(); - $tags = []; - while (($row = $queryResult->fetch()) !== false) { - $messageId = $row['imap_message_id']; - if (!isset($tags[$messageId])) { - $tags[$messageId] = []; - } + foreach (array_chunk($ids, 1000) as $chunk) { + $tagsQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $queryResult = $tagsQuery->execute(); + + while (($row = $queryResult->fetch()) !== false) { + $messageId = $row['imap_message_id']; + if (!isset($tags[$messageId])) { + $tags[$messageId] = []; + } - // Construct a Tag instance but omit any other joined columns - $tags[$messageId][] = Tag::fromRow(array_filter( - $row, - function (string $key) { - return $key !== 'imap_message_id'; - }, - ARRAY_FILTER_USE_KEY - )); + // Construct a Tag instance but omit any other joined columns + $tags[$messageId][] = Tag::fromRow(array_filter( + $row, + function (string $key) { + return $key !== 'imap_message_id'; + }, + ARRAY_FILTER_USE_KEY + )); + } + $queryResult->closeCursor(); } - $queryResult->closeCursor(); return $tags; } diff --git a/tests/psalm-baseline.xml b/tests/psalm-baseline.xml index 7f7a470e1..39bf79298 100644 --- a/tests/psalm-baseline.xml +++ b/tests/psalm-baseline.xml @@ -80,30 +80,29 @@ </file> <file src="lib/Db/AliasMapper.php"> <ImplicitToStringCast occurrences="1"> - <code>$qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> </ImplicitToStringCast> </file> <file src="lib/Db/CollectedAddressMapper.php"> <ImplicitToStringCast occurrences="1"> - <code>$qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$qb2->createParameter('ids')</code> </ImplicitToStringCast> </file> <file src="lib/Db/MailboxMapper.php"> <ImplicitToStringCast occurrences="1"> - <code>$qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> </ImplicitToStringCast> </file> <file src="lib/Db/MessageMapper.php"> <ImplicitToStringCast occurrences="26"> - <code>$deleteRecipientsQuery->createFunction($messageIdQuery->getSQL())</code> - <code>$deleteRecipientsQuery->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$messageIdQuery->createFunction($messageIdQuery->getSQL())</code> + <code>$deleteMessagesQuery->createParameter('messageIds')</code> + <code>$deleteRecipientsQuery->createParameter('ids')</code> + <code>$deleteRecipientsQuery->createParameter('messageIds')</code> + <code>$messageIdQuery->createParameter('uids')</code> <code>$messagesQuery->createFunction($mailboxesQuery->getSQL())</code> <code>$qb->createFunction($mailboxIdsQuery->getSQL())</code> <code>$qb->createFunction($mailboxIdsQuery->getSQL())</code> <code>$qb->createFunction($selectMailboxIds->getSQL())</code> - <code>$qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> <code>$qb->createNamedParameter($mailboxIds, IQueryBuilder::PARAM_INT_ARRAY)</code> <code>$qb->createNamedParameter($query->getBcc(), IQueryBuilder::PARAM_STR_ARRAY)</code> <code>$qb->createNamedParameter($query->getBcc(), IQueryBuilder::PARAM_STR_ARRAY)</code> @@ -114,12 +113,14 @@ <code>$qb->createNamedParameter($query->getTo(), IQueryBuilder::PARAM_STR_ARRAY)</code> <code>$qb->createNamedParameter($query->getTo(), IQueryBuilder::PARAM_STR_ARRAY)</code> <code>$qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb2->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$qb->createParameter('ids')</code> + <code>$qb->createParameter('ids')</code> + <code>$qb->createParameter('uids')</code> + <code>$qb->createParameter('uids')</code> + <code>$qb2->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> <code>$qb2->createNamedParameter(array_keys($indexedMessages), IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb4->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$query->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$qb4->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> + <code>$query->createParameter('ids')</code> </ImplicitToStringCast> </file> <file src="lib/Db/StatisticsDao.php"> @@ -137,7 +138,7 @@ <ImplicitToStringCast occurrences="3"> <code>$deleteQB->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> <code>$deleteQB->createNamedParameter($chunk, IQueryBuilder::PARAM_INT_ARRAY)</code> - <code>$qb->createNamedParameter($ids, IQueryBuilder::PARAM_STR_ARRAY)</code> + <code>$qb->createParameter('ids')</code> </ImplicitToStringCast> <InvalidArgument occurrences="1"/> </file> @@ -268,11 +269,6 @@ <code>$this->contactsManager->getUserAddressBooks()</code> </UndefinedDocblockClass> </file> - <file src="lib/Service/Html.php"> - <UndefinedClass occurrences="1"> - <code>UrlLinker</code> - </UndefinedClass> - </file> <file src="lib/Service/HtmlPurify/TransformURLScheme.php"> <NullArgument occurrences="3"> <code>null</code> |