1<?php 2 3declare(strict_types=1); 4 5/** 6 * @copyright 2019 Christoph Wurst <christoph@winzerhof-wurst.at> 7 * 8 * @author 2019 Christoph Wurst <christoph@winzerhof-wurst.at> 9 * 10 * @license GNU AGPL version 3 or any later version 11 * 12 * This program is free software: you can redistribute it and/or modify 13 * it under the terms of the GNU Affero General Public License as 14 * published by the Free Software Foundation, either version 3 of the 15 * License, or (at your option) any later version. 16 * 17 * This program is distributed in the hope that it will be useful, 18 * but WITHOUT ANY WARRANTY; without even the implied warranty of 19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 20 * GNU Affero General Public License for more details. 21 * 22 * You should have received a copy of the GNU Affero General Public License 23 * along with this program. If not, see <http://www.gnu.org/licenses/>. 24 */ 25 26namespace OCA\Mail\Db; 27 28use OCA\Mail\Account; 29use OCA\Mail\Address; 30use OCA\Mail\AddressList; 31use OCA\Mail\IMAP\Threading\DatabaseMessage; 32use OCA\Mail\Service\Search\Flag; 33use OCA\Mail\Service\Search\FlagExpression; 34use OCA\Mail\Service\Search\SearchQuery; 35use OCA\Mail\Support\PerformanceLogger; 36use OCA\Mail\Support\PerformanceLoggerTask; 37use OCP\AppFramework\Db\DoesNotExistException; 38use OCP\AppFramework\Db\QBMapper; 39use OCP\AppFramework\Utility\ITimeFactory; 40use OCP\DB\QueryBuilder\IQueryBuilder; 41use OCP\IDBConnection; 42use OCP\IUser; 43use RuntimeException; 44use Throwable; 45use function array_combine; 46use function array_keys; 47use function array_map; 48use function array_udiff; 49use function get_class; 50use function ltrim; 51use function mb_strcut; 52use function OCA\Mail\array_flat_map; 53 54/** 55 * @template-extends QBMapper<Message> 56 */ 57class MessageMapper extends QBMapper { 58 59 /** @var ITimeFactory */ 60 private $timeFactory; 61 62 /** @var TagMapper */ 63 private $tagMapper; 64 65 /** @var PerformanceLogger */ 66 private $performanceLogger; 67 68 public function __construct(IDBConnection $db, 69 ITimeFactory $timeFactory, 70 TagMapper $tagMapper, 71 PerformanceLogger $performanceLogger) { 72 parent::__construct($db, 'mail_messages'); 73 $this->timeFactory = $timeFactory; 74 $this->tagMapper = $tagMapper; 75 $this->performanceLogger = $performanceLogger; 76 } 77 78 /** 79 * @param IQueryBuilder $query 80 * 81 * @return int[] 82 */ 83 private function findUids(IQueryBuilder $query): array { 84 $result = $query->execute(); 85 $uids = array_map(function (array $row) { 86 return (int)$row['uid']; 87 }, $result->fetchAll()); 88 $result->closeCursor(); 89 90 return $uids; 91 } 92 93 /** 94 * @param IQueryBuilder $query 95 * 96 * @return int[] 97 */ 98 private function findIds(IQueryBuilder $query): array { 99 $result = $query->execute(); 100 $uids = array_map(function (array $row) { 101 return (int)$row['id']; 102 }, $result->fetchAll()); 103 $result->closeCursor(); 104 105 return $uids; 106 } 107 108 public function findHighestUid(Mailbox $mailbox): ?int { 109 $query = $this->db->getQueryBuilder(); 110 111 $query->select($query->func()->max('uid')) 112 ->from($this->getTableName()) 113 ->where($query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)); 114 115 $result = $query->execute(); 116 $max = (int)$result->fetchColumn(); 117 $result->closeCursor(); 118 119 if ($max === 0) { 120 return null; 121 } 122 return $max; 123 } 124 125 public function findByUserId(string $userId, int $id): Message { 126 $query = $this->db->getQueryBuilder(); 127 128 $query->select('m.*') 129 ->from($this->getTableName(), 'm') 130 ->join('m', 'mail_mailboxes', 'mb', $query->expr()->eq('m.mailbox_id', 'mb.id', IQueryBuilder::PARAM_INT)) 131 ->join('m', 'mail_accounts', 'a', $query->expr()->eq('mb.account_id', 'a.id', IQueryBuilder::PARAM_INT)) 132 ->where( 133 $query->expr()->eq('a.user_id', $query->createNamedParameter($userId)), 134 $query->expr()->eq('m.id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT) 135 ); 136 137 $results = $this->findRelatedData($this->findEntities($query), $userId); 138 if (empty($results)) { 139 throw new DoesNotExistException("Message $id does not exist"); 140 } 141 return $results[0]; 142 } 143 144 public function findAllUids(Mailbox $mailbox): array { 145 $query = $this->db->getQueryBuilder(); 146 147 $query->select('uid') 148 ->from($this->getTableName()) 149 ->where($query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)); 150 151 return $this->findUids($query); 152 } 153 154 public function findAllIds(Mailbox $mailbox): array { 155 $query = $this->db->getQueryBuilder(); 156 157 $query->select('id') 158 ->from($this->getTableName()) 159 ->where($query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)); 160 161 return $this->findIds($query); 162 } 163 164 /** 165 * @param Mailbox $mailbox 166 * @param int[] $ids 167 * 168 * @return int[] 169 */ 170 public function findUidsForIds(Mailbox $mailbox, array $ids) { 171 if (empty($ids)) { 172 // Shortcut for empty sets 173 return []; 174 } 175 176 $query = $this->db->getQueryBuilder(); 177 $query->select('uid') 178 ->from($this->getTableName()) 179 ->where( 180 $query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT), 181 $query->expr()->in('id', $query->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY) 182 ); 183 184 return array_flat_map(function (array $chunk) use ($query) { 185 $query->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 186 return $this->findUids($query); 187 }, array_chunk($ids, 1000)); 188 } 189 190 /** 191 * @param Account $account 192 * 193 * @return DatabaseMessage[] 194 */ 195 public function findThreadingData(Account $account): array { 196 $mailboxesQuery = $this->db->getQueryBuilder(); 197 $messagesQuery = $this->db->getQueryBuilder(); 198 199 $mailboxesQuery->select('id') 200 ->from('mail_mailboxes') 201 ->where($mailboxesQuery->expr()->eq('account_id', $messagesQuery->createNamedParameter($account->getId(), IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)); 202 $messagesQuery->select('id', 'subject', 'message_id', 'in_reply_to', 'references', 'thread_root_id') 203 ->from($this->getTableName()) 204 ->where($messagesQuery->expr()->in('mailbox_id', $messagesQuery->createFunction($mailboxesQuery->getSQL()), IQueryBuilder::PARAM_INT_ARRAY)) 205 ->andWhere($messagesQuery->expr()->isNotNull('message_id')); 206 207 $result = $messagesQuery->execute(); 208 $messages = array_map(function (array $row) { 209 return DatabaseMessage::fromRowData( 210 (int)$row['id'], 211 $row['subject'], 212 $row['message_id'], 213 $row['references'], 214 $row['in_reply_to'], 215 $row['thread_root_id'] 216 ); 217 }, $result->fetchAll()); 218 $result->closeCursor(); 219 220 return $messages; 221 } 222 223 /** 224 * @param DatabaseMessage[] $messages 225 * 226 * @todo combine threads and send just one query per thread, like UPDATE ... SET thread_root_id = xxx where UID IN (...) 227 */ 228 public function writeThreadIds(array $messages): void { 229 $this->db->beginTransaction(); 230 231 try { 232 $query = $this->db->getQueryBuilder(); 233 $query->update($this->getTableName()) 234 ->set('thread_root_id', $query->createParameter('thread_root_id')) 235 ->where($query->expr()->eq('id', $query->createParameter('id'))); 236 237 foreach ($messages as $message) { 238 $query->setParameter( 239 'thread_root_id', 240 $message->getThreadRootId(), 241 $message->getThreadRootId() === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_STR 242 ); 243 $query->setParameter('id', $message->getDatabaseId(), IQueryBuilder::PARAM_INT); 244 245 $query->execute(); 246 } 247 248 $this->db->commit(); 249 } catch (Throwable $e) { 250 // Make sure to always roll back, otherwise the outer code runs in a failed transaction 251 $this->db->rollBack(); 252 253 throw $e; 254 } 255 } 256 257 /** 258 * @param Message ...$messages 259 * @return void 260 */ 261 public function insertBulk(Account $account, Message ...$messages): void { 262 $this->db->beginTransaction(); 263 264 try { 265 $qb1 = $this->db->getQueryBuilder(); 266 $qb1->insert($this->getTableName()); 267 $qb1->setValue('uid', $qb1->createParameter('uid')); 268 $qb1->setValue('message_id', $qb1->createParameter('message_id')); 269 $qb1->setValue('references', $qb1->createParameter('references')); 270 $qb1->setValue('in_reply_to', $qb1->createParameter('in_reply_to')); 271 $qb1->setValue('thread_root_id', $qb1->createParameter('thread_root_id')); 272 $qb1->setValue('mailbox_id', $qb1->createParameter('mailbox_id')); 273 $qb1->setValue('subject', $qb1->createParameter('subject')); 274 $qb1->setValue('sent_at', $qb1->createParameter('sent_at')); 275 $qb1->setValue('flag_answered', $qb1->createParameter('flag_answered')); 276 $qb1->setValue('flag_deleted', $qb1->createParameter('flag_deleted')); 277 $qb1->setValue('flag_draft', $qb1->createParameter('flag_draft')); 278 $qb1->setValue('flag_flagged', $qb1->createParameter('flag_flagged')); 279 $qb1->setValue('flag_seen', $qb1->createParameter('flag_seen')); 280 $qb1->setValue('flag_forwarded', $qb1->createParameter('flag_forwarded')); 281 $qb1->setValue('flag_junk', $qb1->createParameter('flag_junk')); 282 $qb1->setValue('flag_notjunk', $qb1->createParameter('flag_notjunk')); 283 $qb1->setValue('flag_important', $qb1->createParameter('flag_important')); 284 $qb1->setValue('flag_mdnsent', $qb1->createParameter('flag_mdnsent')); 285 $qb2 = $this->db->getQueryBuilder(); 286 287 $qb2->insert('mail_recipients') 288 ->setValue('message_id', $qb2->createParameter('message_id')) 289 ->setValue('type', $qb2->createParameter('type')) 290 ->setValue('label', $qb2->createParameter('label')) 291 ->setValue('email', $qb2->createParameter('email')); 292 293 foreach ($messages as $message) { 294 $qb1->setParameter('uid', $message->getUid(), IQueryBuilder::PARAM_INT); 295 $qb1->setParameter('message_id', $message->getMessageId(), IQueryBuilder::PARAM_STR); 296 $inReplyTo = $message->getInReplyTo(); 297 $qb1->setParameter('in_reply_to', $inReplyTo, $inReplyTo === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_STR); 298 $references = $message->getReferences(); 299 $qb1->setParameter('references', $references, $references === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_STR); 300 $threadRootId = $message->getThreadRootId(); 301 $qb1->setParameter('thread_root_id', $threadRootId, $threadRootId === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_STR); 302 $qb1->setParameter('mailbox_id', $message->getMailboxId(), IQueryBuilder::PARAM_INT); 303 $qb1->setParameter('subject', $message->getSubject(), IQueryBuilder::PARAM_STR); 304 $qb1->setParameter('sent_at', $message->getSentAt(), IQueryBuilder::PARAM_INT); 305 $qb1->setParameter('flag_answered', $message->getFlagAnswered(), IQueryBuilder::PARAM_BOOL); 306 $qb1->setParameter('flag_deleted', $message->getFlagDeleted(), IQueryBuilder::PARAM_BOOL); 307 $qb1->setParameter('flag_draft', $message->getFlagDraft(), IQueryBuilder::PARAM_BOOL); 308 $qb1->setParameter('flag_flagged', $message->getFlagFlagged(), IQueryBuilder::PARAM_BOOL); 309 $qb1->setParameter('flag_seen', $message->getFlagSeen(), IQueryBuilder::PARAM_BOOL); 310 $qb1->setParameter('flag_forwarded', $message->getFlagForwarded(), IQueryBuilder::PARAM_BOOL); 311 $qb1->setParameter('flag_junk', $message->getFlagJunk(), IQueryBuilder::PARAM_BOOL); 312 $qb1->setParameter('flag_notjunk', $message->getFlagNotjunk(), IQueryBuilder::PARAM_BOOL); 313 $qb1->setParameter('flag_important', $message->getFlagImportant(), IQueryBuilder::PARAM_BOOL); 314 $qb1->setParameter('flag_mdnsent', $message->getFlagMdnsent(), IQueryBuilder::PARAM_BOOL); 315 316 $qb1->execute(); 317 318 $messageId = $qb1->getLastInsertId(); 319 $recipientTypes = [ 320 Address::TYPE_FROM => $message->getFrom(), 321 Address::TYPE_TO => $message->getTo(), 322 Address::TYPE_CC => $message->getCc(), 323 Address::TYPE_BCC => $message->getBcc(), 324 ]; 325 foreach ($recipientTypes as $type => $recipients) { 326 /** @var AddressList $recipients */ 327 foreach ($recipients->iterate() as $recipient) { 328 /** @var Address $recipient */ 329 if ($recipient->getEmail() === null) { 330 // If for some reason the e-mail is not set we should ignore this entry 331 continue; 332 } 333 334 $qb2->setParameter('message_id', $messageId, IQueryBuilder::PARAM_INT); 335 $qb2->setParameter('type', $type, IQueryBuilder::PARAM_INT); 336 $qb2->setParameter('label', mb_strcut($recipient->getLabel(), 0, 255), IQueryBuilder::PARAM_STR); 337 $qb2->setParameter('email', $recipient->getEmail(), IQueryBuilder::PARAM_STR); 338 339 $qb2->execute(); 340 } 341 } 342 foreach ($message->getTags() as $tag) { 343 $this->tagMapper->tagMessage($tag, $message->getMessageId(), $account->getUserId()); 344 } 345 } 346 347 $this->db->commit(); 348 } catch (Throwable $e) { 349 // Make sure to always roll back, otherwise the outer code runs in a failed transaction 350 $this->db->rollBack(); 351 352 throw $e; 353 } 354 } 355 356 /** 357 * @param Account $account 358 * @param bool $permflagsEnabled 359 * @param Message[] $messages 360 * @return Message[] 361 */ 362 public function updateBulk(Account $account, bool $permflagsEnabled, Message ...$messages): array { 363 $this->db->beginTransaction(); 364 365 $perf = $this->performanceLogger->start( 366 'partial sync ' . $account->getId() . ':' . $account->getName() 367 ); 368 369 try { 370 $query = $this->db->getQueryBuilder(); 371 $query->update($this->getTableName()) 372 ->set('flag_answered', $query->createParameter('flag_answered')) 373 ->set('flag_deleted', $query->createParameter('flag_deleted')) 374 ->set('flag_draft', $query->createParameter('flag_draft')) 375 ->set('flag_flagged', $query->createParameter('flag_flagged')) 376 ->set('flag_seen', $query->createParameter('flag_seen')) 377 ->set('flag_forwarded', $query->createParameter('flag_forwarded')) 378 ->set('flag_junk', $query->createParameter('flag_junk')) 379 ->set('flag_notjunk', $query->createParameter('flag_notjunk')) 380 ->set('flag_mdnsent', $query->createParameter('flag_mdnsent')) 381 ->set('flag_important', $query->createParameter('flag_important')) 382 ->set('updated_at', $query->createNamedParameter($this->timeFactory->getTime())) 383 ->where($query->expr()->andX( 384 $query->expr()->eq('uid', $query->createParameter('uid')), 385 $query->expr()->eq('mailbox_id', $query->createParameter('mailbox_id')) 386 )); 387 388 // get all tags before the loop and create a mapping [message_id => [tag,...]] but only if permflags are enabled 389 $tags = []; 390 if ($permflagsEnabled) { 391 $tags = $this->tagMapper->getAllTagsForMessages($messages, $account->getUserId()); 392 $perf->step("Selected Tags for all messages"); 393 } 394 395 foreach ($messages as $message) { 396 if (empty($message->getUpdatedFields()) === false) { 397 // only run if there is anything to actually update 398 $query->setParameter('uid', $message->getUid(), IQueryBuilder::PARAM_INT); 399 $query->setParameter('mailbox_id', $message->getMailboxId(), IQueryBuilder::PARAM_INT); 400 $query->setParameter('flag_answered', $message->getFlagAnswered(), IQueryBuilder::PARAM_BOOL); 401 $query->setParameter('flag_deleted', $message->getFlagDeleted(), IQueryBuilder::PARAM_BOOL); 402 $query->setParameter('flag_draft', $message->getFlagDraft(), IQueryBuilder::PARAM_BOOL); 403 $query->setParameter('flag_flagged', $message->getFlagFlagged(), IQueryBuilder::PARAM_BOOL); 404 $query->setParameter('flag_seen', $message->getFlagSeen(), IQueryBuilder::PARAM_BOOL); 405 $query->setParameter('flag_forwarded', $message->getFlagForwarded(), IQueryBuilder::PARAM_BOOL); 406 $query->setParameter('flag_junk', $message->getFlagJunk(), IQueryBuilder::PARAM_BOOL); 407 $query->setParameter('flag_notjunk', $message->getFlagNotjunk(), IQueryBuilder::PARAM_BOOL); 408 $query->setParameter('flag_mdnsent', $message->getFlagMdnsent(), IQueryBuilder::PARAM_BOOL); 409 $query->setParameter('flag_important', $message->getFlagImportant(), IQueryBuilder::PARAM_BOOL); 410 $query->execute(); 411 $perf->step('Updated message ' . $message->getId()); 412 } 413 414 // check permflags and only go through the tagging logic if they're enabled 415 if ($permflagsEnabled) { 416 $this->updateTags($account, $message, $tags, $perf); 417 } 418 } 419 420 $this->db->commit(); 421 } catch (Throwable $e) { 422 // Make sure to always roll back, otherwise the outer code runs in a failed transaction 423 $this->db->rollBack(); 424 425 throw $e; 426 } 427 428 $perf->end(); 429 430 return $messages; 431 } 432 433 /** 434 * @param Account $account 435 * @param Message $message 436 * @param Tag[][] $tags 437 * @param PerformanceLoggerTask $perf 438 */ 439 private function updateTags(Account $account, Message $message, array $tags, PerformanceLoggerTask $perf): void { 440 $imapTags = $message->getTags(); 441 $dbTags = $tags[$message->getMessageId()] ?? []; 442 443 if (empty($imapTags) && empty($dbTags)) { 444 // neither old nor new tags 445 return; 446 } 447 448 $toAdd = array_udiff($imapTags, $dbTags, static function (Tag $a, Tag $b) { 449 return strcmp($a->getImapLabel(), $b->getImapLabel()); 450 }); 451 foreach ($toAdd as $tag) { 452 $this->tagMapper->tagMessage($tag, $message->getMessageId(), $account->getUserId()); 453 } 454 $perf->step("Tagged messages"); 455 456 if (empty($dbTags)) { 457 // we have nothing to possibly remove 458 return; 459 } 460 461 $toRemove = array_udiff($dbTags, $imapTags, static function (Tag $a, Tag $b) { 462 return strcmp($a->getImapLabel(), $b->getImapLabel()); 463 }); 464 foreach ($toRemove as $tag) { 465 $this->tagMapper->untagMessage($tag, $message->getMessageId()); 466 } 467 $perf->step("Untagged messages"); 468 } 469 470 public function getTags(Message $message) : array { 471 $mqb = $this->db->getQueryBuilder(); 472 $mqb->select('tag_id') 473 ->from('mail_message_tags') 474 ->where($mqb->expr()->eq('imap_message_id', $mqb->createNamedParameter($message->getMessageId()))); 475 $result = $mqb->execute(); 476 $ids = array_map(function (array $row) { 477 return (int)$row['tag_id']; 478 }, $result->fetchAll()); 479 480 return $ids; 481 } 482 483 /** 484 * @param Message ...$messages 485 * 486 * @return Message[] 487 */ 488 public function updatePreviewDataBulk(Message ...$messages): array { 489 $this->db->beginTransaction(); 490 491 try { 492 $query = $this->db->getQueryBuilder(); 493 $query->update($this->getTableName()) 494 ->set('flag_attachments', $query->createParameter('flag_attachments')) 495 ->set('preview_text', $query->createParameter('preview_text')) 496 ->set('structure_analyzed', $query->createNamedParameter(true, IQueryBuilder::PARAM_BOOL)) 497 ->set('updated_at', $query->createNamedParameter($this->timeFactory->getTime(), IQueryBuilder::PARAM_INT)) 498 ->where($query->expr()->andX( 499 $query->expr()->eq('uid', $query->createParameter('uid')), 500 $query->expr()->eq('mailbox_id', $query->createParameter('mailbox_id')) 501 )); 502 503 foreach ($messages as $message) { 504 if (empty($message->getUpdatedFields())) { 505 // Micro optimization 506 continue; 507 } 508 509 $query->setParameter('uid', $message->getUid(), IQueryBuilder::PARAM_INT); 510 $query->setParameter('mailbox_id', $message->getMailboxId(), IQueryBuilder::PARAM_INT); 511 $query->setParameter('flag_attachments', $message->getFlagAttachments(), $message->getFlagAttachments() === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_BOOL); 512 $query->setParameter('preview_text', $message->getPreviewText(), $message->getPreviewText() === null ? IQueryBuilder::PARAM_NULL : IQueryBuilder::PARAM_STR); 513 514 $query->execute(); 515 } 516 517 $this->db->commit(); 518 } catch (Throwable $e) { 519 // Make sure to always roll back, otherwise the outer code runs in a failed transaction 520 $this->db->rollBack(); 521 522 throw $e; 523 } 524 525 return $messages; 526 } 527 528 public function deleteAll(Mailbox $mailbox): void { 529 $messageIdQuery = $this->db->getQueryBuilder(); 530 $messageIdQuery->select('id') 531 ->from($this->getTableName()) 532 ->where($messageIdQuery->expr()->eq('mailbox_id', $messageIdQuery->createNamedParameter($mailbox->getId()))); 533 534 $cursor = $messageIdQuery->execute(); 535 $messageIds = $cursor->fetchAll(); 536 $cursor->closeCursor(); 537 538 $messageIds = array_map(function (array $row) { 539 return (int)$row['id']; 540 }, $messageIds); 541 542 $deleteRecipientsQuery = $this->db->getQueryBuilder(); 543 $deleteRecipientsQuery->delete('mail_recipients') 544 ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createParameter('ids'))); 545 546 foreach (array_chunk($messageIds, 1000) as $chunk) { 547 // delete all related recipient entries 548 $deleteRecipientsQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 549 $deleteRecipientsQuery->execute(); 550 } 551 552 $query = $this->db->getQueryBuilder(); 553 554 $query->delete($this->getTableName()) 555 ->where($query->expr()->eq('mailbox_id', $query->createNamedParameter($mailbox->getId()))); 556 557 $query->execute(); 558 } 559 560 public function deleteByUid(Mailbox $mailbox, int ...$uids): void { 561 $messageIdQuery = $this->db->getQueryBuilder(); 562 $deleteRecipientsQuery = $this->db->getQueryBuilder(); 563 $deleteMessagesQuery = $this->db->getQueryBuilder(); 564 565 // Get all message ids query 566 $messageIdQuery->select('id') 567 ->from($this->getTableName()) 568 ->where( 569 $messageIdQuery->expr()->eq('mailbox_id', $messageIdQuery->createNamedParameter($mailbox->getId())), 570 $messageIdQuery->expr()->in('uid', $messageIdQuery->createParameter('uids')) 571 ); 572 573 $deleteRecipientsQuery->delete('mail_recipients') 574 ->where($deleteRecipientsQuery->expr()->in('message_id', $deleteRecipientsQuery->createParameter('messageIds'))); 575 576 $deleteMessagesQuery->delete($this->getTableName()) 577 ->where($deleteMessagesQuery->expr()->in('id', $deleteMessagesQuery->createParameter('messageIds'))); 578 579 foreach (array_chunk($uids, 1000) as $chunk) { 580 $messageIdQuery->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 581 $cursor = $messageIdQuery->execute(); 582 583 $messageIds = array_map(function (array $message) { 584 return $message['id']; 585 }, $cursor->fetchAll()); 586 $cursor->closeCursor(); 587 588 // delete all related recipient entries 589 $deleteRecipientsQuery->setParameter('messageIds', $messageIds, IQueryBuilder::PARAM_INT_ARRAY); 590 $deleteRecipientsQuery->execute(); 591 592 // delete all messages 593 $deleteMessagesQuery->setParameter('messageIds', $messageIds, IQueryBuilder::PARAM_INT_ARRAY); 594 $deleteMessagesQuery->execute(); 595 } 596 } 597 598 /** 599 * @param Account $account 600 * @param string $threadRootId 601 * 602 * @return Message[] 603 */ 604 public function findThread(Account $account, string $threadRootId): array { 605 $qb = $this->db->getQueryBuilder(); 606 $qb->select('messages.*') 607 ->from($this->getTableName(), 'messages') 608 ->join('messages', 'mail_mailboxes', 'mailboxes', $qb->expr()->eq('messages.mailbox_id', 'mailboxes.id', IQueryBuilder::PARAM_INT)) 609 ->where( 610 $qb->expr()->eq('mailboxes.account_id', $qb->createNamedParameter($account->getId(), IQueryBuilder::PARAM_INT)), 611 $qb->expr()->eq('messages.thread_root_id', $qb->createNamedParameter($threadRootId, IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR) 612 ) 613 ->orderBy('messages.sent_at', 'desc'); 614 615 return $this->findRelatedData($this->findEntities($qb), $account->getUserId()); 616 } 617 618 /** 619 * @param Mailbox $mailbox 620 * @param SearchQuery $query 621 * @param int|null $limit 622 * @param int[]|null $uids 623 * 624 * @return int[] 625 */ 626 public function findIdsByQuery(Mailbox $mailbox, SearchQuery $query, ?int $limit, array $uids = null): array { 627 $qb = $this->db->getQueryBuilder(); 628 629 if ($this->needDistinct($query)) { 630 $select = $qb->selectDistinct(['m.id', 'm.sent_at']); 631 } else { 632 $select = $qb->select(['m.id', 'm.sent_at']); 633 } 634 635 $select->from($this->getTableName(), 'm') 636 ->leftJoin('m', $this->getTableName(), 'm2', 'm.mailbox_id = m2.mailbox_id and m.thread_root_id = m2.thread_root_id and m.sent_at < m2.sent_at'); 637 638 if (!empty($query->getFrom())) { 639 $select->innerJoin('m', 'mail_recipients', 'r0', 'm.id = r0.message_id'); 640 } 641 if (!empty($query->getTo())) { 642 $select->innerJoin('m', 'mail_recipients', 'r1', 'm.id = r1.message_id'); 643 } 644 if (!empty($query->getCc())) { 645 $select->innerJoin('m', 'mail_recipients', 'r2', 'm.id = r2.message_id'); 646 } 647 if (!empty($query->getBcc())) { 648 $select->innerJoin('m', 'mail_recipients', 'r3', 'm.id = r3.message_id'); 649 } 650 651 $select->where( 652 $qb->expr()->eq('m.mailbox_id', $qb->createNamedParameter($mailbox->getId()), IQueryBuilder::PARAM_INT) 653 ); 654 655 if (!empty($query->getFrom())) { 656 $select->andWhere( 657 $qb->expr()->in('r0.email', $qb->createNamedParameter($query->getFrom(), IQueryBuilder::PARAM_STR_ARRAY)) 658 ); 659 } 660 if (!empty($query->getTo())) { 661 $select->andWhere( 662 $qb->expr()->in('r1.email', $qb->createNamedParameter($query->getTo(), IQueryBuilder::PARAM_STR_ARRAY)) 663 ); 664 } 665 if (!empty($query->getCc())) { 666 $select->andWhere( 667 $qb->expr()->in('r2.email', $qb->createNamedParameter($query->getCc(), IQueryBuilder::PARAM_STR_ARRAY)) 668 ); 669 } 670 if (!empty($query->getBcc())) { 671 $select->andWhere( 672 $qb->expr()->in('r3.email', $qb->createNamedParameter($query->getBcc(), IQueryBuilder::PARAM_STR_ARRAY)) 673 ); 674 } 675 676 if (!empty($query->getSubjects())) { 677 $select->andWhere( 678 $qb->expr()->orX( 679 ...array_map(function (string $subject) use ($qb) { 680 return $qb->expr()->iLike( 681 'm.subject', 682 $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($subject) . '%', IQueryBuilder::PARAM_STR), 683 IQueryBuilder::PARAM_STR 684 ); 685 }, $query->getSubjects()) 686 ) 687 ); 688 } 689 690 if ($query->getCursor() !== null) { 691 $select->andWhere( 692 $qb->expr()->lt('m.sent_at', $qb->createNamedParameter($query->getCursor(), IQueryBuilder::PARAM_INT)) 693 ); 694 } 695 // createParameter 696 if ($uids !== null) { 697 $select->andWhere( 698 $qb->expr()->in('m.uid', $qb->createParameter('uids')) 699 ); 700 } 701 foreach ($query->getFlags() as $flag) { 702 $select->andWhere($qb->expr()->eq('m.' . $this->flagToColumnName($flag), $qb->createNamedParameter($flag->isSet(), IQueryBuilder::PARAM_BOOL))); 703 } 704 if (!empty($query->getFlagExpressions())) { 705 $select->andWhere( 706 ...array_map(function (FlagExpression $expr) use ($select) { 707 return $this->flagExpressionToQuery($expr, $select, 'm'); 708 }, $query->getFlagExpressions()) 709 ); 710 } 711 712 $select->andWhere($qb->expr()->isNull('m2.id')); 713 714 $select->orderBy('m.sent_at', 'desc'); 715 716 if ($limit !== null) { 717 $select->setMaxResults($limit); 718 } 719 720 if ($uids !== null) { 721 return array_flat_map(function (array $chunk) use ($qb, $select) { 722 $qb->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 723 return array_map(function (Message $message) { 724 return $message->getId(); 725 }, $this->findEntities($select)); 726 }, array_chunk($uids, 1000)); 727 } 728 729 return array_map(function (Message $message) { 730 return $message->getId(); 731 }, $this->findEntities($select)); 732 } 733 734 public function findIdsGloballyByQuery(IUser $user, SearchQuery $query, ?int $limit, array $uids = null): array { 735 $qb = $this->db->getQueryBuilder(); 736 $qbMailboxes = $this->db->getQueryBuilder(); 737 738 if ($this->needDistinct($query)) { 739 $select = $qb->selectDistinct(['m.id', 'm.sent_at']); 740 } else { 741 $select = $qb->select(['m.id', 'm.sent_at']); 742 } 743 744 $select->from($this->getTableName(), 'm') 745 ->leftJoin('m', $this->getTableName(), 'm2', 'm.mailbox_id = m2.mailbox_id and m.thread_root_id = m2.thread_root_id and m.sent_at < m2.sent_at'); 746 747 if (!empty($query->getFrom())) { 748 $select->innerJoin('m', 'mail_recipients', 'r0', 'm.id = r0.message_id'); 749 } 750 if (!empty($query->getTo())) { 751 $select->innerJoin('m', 'mail_recipients', 'r1', 'm.id = r1.message_id'); 752 } 753 if (!empty($query->getCc())) { 754 $select->innerJoin('m', 'mail_recipients', 'r2', 'm.id = r2.message_id'); 755 } 756 if (!empty($query->getBcc())) { 757 $select->innerJoin('m', 'mail_recipients', 'r3', 'm.id = r3.message_id'); 758 } 759 760 $selectMailboxIds = $qbMailboxes->select('mb.id') 761 ->from('mail_mailboxes', 'mb') 762 ->join('mb', 'mail_accounts', 'a', $qb->expr()->eq('a.id', 'mb.account_id', IQueryBuilder::PARAM_INT)) 763 ->where($qb->expr()->eq('a.user_id', $qb->createNamedParameter($user->getUID()))); 764 $select->where( 765 $qb->expr()->in('m.mailbox_id', $qb->createFunction($selectMailboxIds->getSQL()), IQueryBuilder::PARAM_INT_ARRAY) 766 ); 767 768 if (!empty($query->getFrom())) { 769 $select->andWhere( 770 $qb->expr()->in('r0.email', $qb->createNamedParameter($query->getFrom(), IQueryBuilder::PARAM_STR_ARRAY)) 771 ); 772 } 773 if (!empty($query->getTo())) { 774 $select->andWhere( 775 $qb->expr()->in('r1.email', $qb->createNamedParameter($query->getTo(), IQueryBuilder::PARAM_STR_ARRAY)) 776 ); 777 } 778 if (!empty($query->getCc())) { 779 $select->andWhere( 780 $qb->expr()->in('r2.email', $qb->createNamedParameter($query->getCc(), IQueryBuilder::PARAM_STR_ARRAY)) 781 ); 782 } 783 if (!empty($query->getBcc())) { 784 $select->andWhere( 785 $qb->expr()->in('r3.email', $qb->createNamedParameter($query->getBcc(), IQueryBuilder::PARAM_STR_ARRAY)) 786 ); 787 } 788 789 if (!empty($query->getSubjects())) { 790 $select->andWhere( 791 $qb->expr()->orX( 792 ...array_map(function (string $subject) use ($qb) { 793 return $qb->expr()->iLike( 794 'm.subject', 795 $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($subject) . '%', IQueryBuilder::PARAM_STR), 796 IQueryBuilder::PARAM_STR 797 ); 798 }, $query->getSubjects()) 799 ) 800 ); 801 } 802 803 if ($query->getCursor() !== null) { 804 $select->andWhere( 805 $qb->expr()->lt('m.sent_at', $qb->createNamedParameter($query->getCursor(), IQueryBuilder::PARAM_INT)) 806 ); 807 } 808 if ($uids !== null) { 809 $select->andWhere( 810 $qb->expr()->in('m.uid', $qb->createParameter('uids')) 811 ); 812 } 813 foreach ($query->getFlags() as $flag) { 814 $select->andWhere($qb->expr()->eq('m.' . $this->flagToColumnName($flag), $qb->createNamedParameter($flag->isSet(), IQueryBuilder::PARAM_BOOL))); 815 } 816 if (!empty($query->getFlagExpressions())) { 817 $select->andWhere( 818 ...array_map(function (FlagExpression $expr) use ($select) { 819 return $this->flagExpressionToQuery($expr, $select, 'm'); 820 }, $query->getFlagExpressions()) 821 ); 822 } 823 824 $select->andWhere($qb->expr()->isNull('m2.id')); 825 826 $select->orderBy('m.sent_at', 'desc'); 827 828 if ($limit !== null) { 829 $select->setMaxResults($limit); 830 } 831 832 if ($uids !== null) { 833 return array_flat_map(function (array $chunk) use ($select) { 834 $select->setParameter('uids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 835 return array_map(function (Message $message) { 836 return $message->getId(); 837 }, $this->findEntities($select)); 838 }, array_chunk($uids, 1000)); 839 } 840 841 return array_map(function (Message $message) { 842 return $message->getId(); 843 }, $this->findEntities($select)); 844 } 845 846 /** 847 * Return true when a distinct query is required. 848 * 849 * For the threaded message list it's necessary to self-join 850 * the mail_messages table to figure out if we are the latest message 851 * of a thread. 852 * 853 * Unfortunately a self-join on a larger table has a significant 854 * performance impact. An database index (e.g. on thread_root_id) 855 * could improve the query performance but adding an index is blocked by 856 * - https://github.com/nextcloud/server/pull/25471 857 * - https://github.com/nextcloud/mail/issues/4735 858 * 859 * We noticed a better query performance without distinct. As distinct is 860 * only necessary when a search query is present (e.g. search for mail with 861 * two recipients) it's reasonable to use distinct only for those requests. 862 * 863 * @param SearchQuery $query 864 * @return bool 865 */ 866 private function needDistinct(SearchQuery $query): bool { 867 return !empty($query->getFrom()) 868 || !empty($query->getTo()) 869 || !empty($query->getCc()) 870 || !empty($query->getBcc()); 871 } 872 873 private function flagExpressionToQuery(FlagExpression $expr, IQueryBuilder $qb, string $tableAlias): string { 874 $operands = array_map(function (object $operand) use ($qb, $tableAlias) { 875 if ($operand instanceof Flag) { 876 return $qb->expr()->eq( 877 $tableAlias . '.' . $this->flagToColumnName($operand), 878 $qb->createNamedParameter($operand->isSet(), IQueryBuilder::PARAM_BOOL), 879 IQueryBuilder::PARAM_BOOL 880 ); 881 } 882 if ($operand instanceof FlagExpression) { 883 return $this->flagExpressionToQuery($operand, $qb, $tableAlias); 884 } 885 886 throw new RuntimeException('Invalid operand type ' . get_class($operand)); 887 }, $expr->getOperands()); 888 889 switch ($expr->getOperator()) { 890 case 'and': 891 /** @psalm-suppress InvalidCast */ 892 return (string) $qb->expr()->andX(...$operands); 893 case 'or': 894 /** @psalm-suppress InvalidCast */ 895 return (string) $qb->expr()->orX(...$operands); 896 default: 897 throw new RuntimeException('Unknown operator ' . $expr->getOperator()); 898 } 899 } 900 901 private function flagToColumnName(Flag $flag): string { 902 // workaround for @link https://github.com/nextcloud/mail/issues/25 903 if ($flag->getFlag() === Tag::LABEL_IMPORTANT) { 904 return "flag_important"; 905 } 906 $key = ltrim($flag->getFlag(), '\\$'); 907 return "flag_$key"; 908 } 909 910 /** 911 * @param Mailbox $mailbox 912 * @param int[] $uids 913 * 914 * @return Message[] 915 */ 916 public function findByUids(Mailbox $mailbox, array $uids): array { 917 $qb = $this->db->getQueryBuilder(); 918 919 $select = $qb 920 ->select('*') 921 ->from($this->getTableName()) 922 ->where( 923 $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId()), IQueryBuilder::PARAM_INT), 924 $qb->expr()->in('uid', $qb->createNamedParameter($uids, IQueryBuilder::PARAM_INT_ARRAY)) 925 ) 926 ->orderBy('sent_at', 'desc'); 927 return $this->findRecipients($this->findEntities($select)); 928 } 929 930 /** 931 * @param int[] $ids 932 * 933 * @return Message[] 934 */ 935 public function findByIds(string $userId, array $ids): array { 936 if (empty($ids)) { 937 return []; 938 } 939 940 $qb = $this->db->getQueryBuilder(); 941 $qb->select('*') 942 ->from($this->getTableName()) 943 ->where( 944 $qb->expr()->in('id', $qb->createParameter('ids')) 945 ) 946 ->orderBy('sent_at', 'desc'); 947 948 $results = []; 949 foreach (array_chunk($ids, 1000) as $chunk) { 950 $qb->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 951 $results[] = $this->findRelatedData($this->findEntities($qb), $userId); 952 } 953 return array_merge(...$results); 954 } 955 956 /** 957 * @param Message[] $messages 958 * 959 * @return Message[] 960 */ 961 private function findRecipients(array $messages): array { 962 /** @var Message[] $indexedMessages */ 963 $indexedMessages = array_combine( 964 array_map(static function (Message $msg) { 965 return $msg->getId(); 966 }, $messages), 967 $messages 968 ); 969 970 $qb2 = $this->db->getQueryBuilder(); 971 $qb2->select('label', 'email', 'type', 'message_id') 972 ->from('mail_recipients') 973 ->where($qb2->expr()->in('message_id', $qb2->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY)); 974 975 $recipientsResults = []; 976 foreach (array_chunk(array_keys($indexedMessages), 1000) as $chunk) { 977 $qb2->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 978 $result = $qb2->execute(); 979 $recipientsResults[] = $result->fetchAll(); 980 $result->closeCursor(); 981 } 982 983 $recipientsResults = array_merge(...$recipientsResults); 984 985 foreach ($recipientsResults as $recipient) { 986 $message = $indexedMessages[(int)$recipient['message_id']]; 987 switch ($recipient['type']) { 988 case Address::TYPE_FROM: 989 $message->setFrom( 990 $message->getFrom()->merge(AddressList::fromRow($recipient)) 991 ); 992 break; 993 case Address::TYPE_TO: 994 $message->setTo( 995 $message->getTo()->merge(AddressList::fromRow($recipient)) 996 ); 997 break; 998 case Address::TYPE_CC: 999 $message->setCc( 1000 $message->getCc()->merge(AddressList::fromRow($recipient)) 1001 ); 1002 break; 1003 case Address::TYPE_BCC: 1004 $message->setFrom( 1005 $message->getFrom()->merge(AddressList::fromRow($recipient)) 1006 ); 1007 break; 1008 } 1009 } 1010 1011 return $messages; 1012 } 1013 1014 /** 1015 * @param Message[] $messages 1016 * @return Message[] 1017 */ 1018 public function findRelatedData(array $messages, string $userId): array { 1019 $messages = $this->findRecipients($messages); 1020 $tags = $this->tagMapper->getAllTagsForMessages($messages, $userId); 1021 /** @var Message $message */ 1022 $messages = array_map(function ($message) use ($tags) { 1023 $message->setTags($tags[$message->getMessageId()] ?? []); 1024 return $message; 1025 }, $messages); 1026 return $messages; 1027 } 1028 1029 /** 1030 * @param Mailbox $mailbox 1031 * @param array $ids 1032 * @return int[] 1033 */ 1034 public function findNewIds(Mailbox $mailbox, array $ids): array { 1035 $select = $this->db->getQueryBuilder(); 1036 $subSelect = $this->db->getQueryBuilder(); 1037 1038 $subSelect 1039 ->select($subSelect->func()->min('sent_at')) 1040 ->from($this->getTableName()) 1041 ->where( 1042 $subSelect->expr()->eq('mailbox_id', $select->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), 1043 $subSelect->expr()->orX( 1044 $subSelect->expr()->in('id', $select->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY) 1045 ) 1046 ); 1047 1048 $select 1049 ->select('m.id') 1050 ->from($this->getTableName(), 'm') 1051 ->leftJoin('m', $this->getTableName(), 'm2', 'm.mailbox_id = m2.mailbox_id and m.thread_root_id = m2.thread_root_id and m.sent_at < m2.sent_at') 1052 ->where( 1053 $select->expr()->eq('m.mailbox_id', $select->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), 1054 $select->expr()->andX($subSelect->expr()->notIn('m.id', $select->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY)), 1055 $select->expr()->isNull('m2.id'), 1056 $select->expr()->gt('m.sent_at', $select->createFunction('(' . $subSelect->getSQL() . ')'), IQueryBuilder::PARAM_INT) 1057 ) 1058 ->orderBy('m.sent_at', 'desc'); 1059 1060 $result = []; 1061 foreach (array_chunk($ids, 1000) as $chunk) { 1062 $select->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 1063 array_merge($this->findIds($select), $result); 1064 } 1065 1066 return $result; 1067 } 1068 1069 /** 1070 * Currently unused 1071 */ 1072 public function findChanged(Account $account, Mailbox $mailbox, int $since): array { 1073 $qb = $this->db->getQueryBuilder(); 1074 1075 $select = $qb 1076 ->select('*') 1077 ->from($this->getTableName()) 1078 ->where( 1079 $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId(), IQueryBuilder::PARAM_INT)), 1080 $qb->expr()->gt('updated_at', $qb->createNamedParameter($since, IQueryBuilder::PARAM_INT)) 1081 ); 1082 return $this->findRelatedData($this->findEntities($select), $account->getUserId()); 1083 } 1084 1085 /** 1086 * @param array $mailboxIds 1087 * @param int $limit 1088 * 1089 * @return Message[] 1090 */ 1091 public function findLatestMessages(string $userId, array $mailboxIds, int $limit): array { 1092 $qb = $this->db->getQueryBuilder(); 1093 1094 $select = $qb 1095 ->select('m.*') 1096 ->from($this->getTableName(), 'm') 1097 ->join('m', 'mail_recipients', 'r', $qb->expr()->eq('m.id', 'r.message_id', IQueryBuilder::PARAM_INT)) 1098 ->where( 1099 $qb->expr()->eq('r.type', $qb->createNamedParameter(Address::TYPE_FROM, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT), 1100 $qb->expr()->in('m.mailbox_id', $qb->createNamedParameter($mailboxIds, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY) 1101 ) 1102 ->orderBy('sent_at', 'desc') 1103 ->setMaxResults($limit); 1104 1105 return $this->findRelatedData($this->findEntities($select), $userId); 1106 } 1107 1108 public function deleteOrphans(): void { 1109 $qb1 = $this->db->getQueryBuilder(); 1110 $idsQuery = $qb1->select('m.id') 1111 ->from($this->getTableName(), 'm') 1112 ->leftJoin('m', 'mail_mailboxes', 'mb', $qb1->expr()->eq('m.mailbox_id', 'mb.id')) 1113 ->where($qb1->expr()->isNull('mb.id')); 1114 $result = $idsQuery->execute(); 1115 $ids = array_map(function (array $row) { 1116 return (int)$row['id']; 1117 }, $result->fetchAll()); 1118 $result->closeCursor(); 1119 1120 $qb2 = $this->db->getQueryBuilder(); 1121 $query = $qb2 1122 ->delete($this->getTableName()) 1123 ->where($qb2->expr()->in('id', $qb2->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY)); 1124 foreach (array_chunk($ids, 1000) as $chunk) { 1125 $query->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 1126 $query->execute(); 1127 } 1128 $qb3 = $this->db->getQueryBuilder(); 1129 $recipientIdsQuery = $qb3->selectDistinct('r.id') 1130 ->from('mail_recipients', 'r') 1131 ->leftJoin('r', 'mail_messages', 'm', $qb3->expr()->eq('r.message_id', 'm.id')) 1132 ->where($qb3->expr()->isNull('m.id')); 1133 $result = $recipientIdsQuery->execute(); 1134 $ids = array_map(function (array $row) { 1135 return (int)$row['id']; 1136 }, $result->fetchAll()); 1137 $result->closeCursor(); 1138 1139 $qb4 = $this->db->getQueryBuilder(); 1140 $recipientsQuery = $qb4 1141 ->delete('mail_recipients') 1142 ->where($qb4->expr()->in('id', $qb4->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY)); 1143 foreach (array_chunk($ids, 1000) as $chunk) { 1144 $recipientsQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY); 1145 $recipientsQuery->execute(); 1146 } 1147 } 1148 1149 public function getIdForUid(Mailbox $mailbox, $uid): ?int { 1150 $qb = $this->db->getQueryBuilder(); 1151 1152 $select = $qb 1153 ->select('m.id') 1154 ->from($this->getTableName(), 'm') 1155 ->where( 1156 $qb->expr()->eq('mailbox_id', $qb->createNamedParameter($mailbox->getId()), IQueryBuilder::PARAM_INT), 1157 $qb->expr()->eq('uid', $qb->createNamedParameter($uid, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT) 1158 ); 1159 $result = $select->execute(); 1160 $rows = $result->fetchAll(); 1161 if (empty($rows)) { 1162 return null; 1163 } 1164 return (int)$rows[0]['id']; 1165 } 1166 1167 /** 1168 * @return Message[] 1169 */ 1170 public function findWithEmptyMessageId(): array { 1171 $qb = $this->db->getQueryBuilder(); 1172 1173 $select = $qb->select('*') 1174 ->from($this->getTableName()) 1175 ->where( 1176 $qb->expr()->isNull('message_id') 1177 ); 1178 1179 return $this->findEntities($select); 1180 } 1181 1182 public function resetInReplyTo(): int { 1183 $qb = $this->db->getQueryBuilder(); 1184 1185 $update = $qb->update($this->tableName) 1186 ->set('in_reply_to', $qb->createNamedParameter('NULL', IQueryBuilder::PARAM_NULL)) 1187 ->where( 1188 $qb->expr()->like('in_reply_to', $qb->createNamedParameter("<>", IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR) 1189 ); 1190 return $update->execute(); 1191 } 1192} 1193