Merge pull request #6030 from nextcloud/feature/outbox-db

Add database schema and layer for local mailboxes

13 files changed, 1105 insertions, 7 deletions

diff --git a/appinfo/info.xml b/appinfo/info.xml
index e4132a7cc..c26a173d6 100644
--- a/appinfo/info.xml
+++ b/appinfo/info.xml
@@ -12,7 +12,7 @@
 - **🙈 We’re not reinventing the wheel!** Based on the great [Horde](https://horde.org) libraries.
 - **📬 Want to host your own mail server?** We don’t have to reimplement this as you could set up [Mail-in-a-Box](https://mailinabox.email)!
 	]]></description>
-	<version>1.12.0-alpha.2</version>
+	<version>1.12.0-alpha.3</version>
 	<licence>agpl</licence>
 	<author>Greta Doçi</author>
 	<author homepage="https://github.com/nextcloud/groupware">Nextcloud Groupware Team</author>
diff --git a/lib/Db/LocalAttachment.php b/lib/Db/LocalAttachment.php
index 1052aee82..f938a0369 100644
--- a/lib/Db/LocalAttachment.php
+++ b/lib/Db/LocalAttachment.php
@@ -1,5 +1,7 @@
 <?php
 
+declare(strict_types=1);
+
 /**
  * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  * @author Luc Calaresu <dev@calaresu.com>
@@ -32,8 +34,10 @@ use OCP\AppFramework\Db\Entity;
  * @method void setFileName(string $fileName)
  * @method string getMimeType()
  * @method void setMimeType(string $mimeType)
- * @method int getCreatedAt()
+ * @method int|null getCreatedAt()
  * @method void setCreatedAt(int $createdAt)
+ * @method int|null getLocalMessageId()
+ * @method void setLocalMessageId(int $localMessageId)
  */
 class LocalAttachment extends Entity implements JsonSerializable {
 
@@ -46,13 +50,19 @@ class LocalAttachment extends Entity implements JsonSerializable {
 	/** @var string */
 	protected $mimeType;
 
-	/** @var mixed */
+	/** @var int|null */
 	protected $createdAt;
 
+	/** @var int|null */
+	protected $localMessageId;
+
 	public function jsonSerialize() {
 		return [
 			'id' => $this->id,
 			'fileName' => $this->fileName,
+			'mimeType' => $this->mimeType,
+			'createdAt' => $this->createdAt,
+			'localMessageId' => $this->localMessageId
 		];
 	}
 }
diff --git a/lib/Db/LocalAttachmentMapper.php b/lib/Db/LocalAttachmentMapper.php
index c8ccc3c36..62f0c5bb6 100644
--- a/lib/Db/LocalAttachmentMapper.php
+++ b/lib/Db/LocalAttachmentMapper.php
@@ -1,5 +1,7 @@
 <?php
 
+declare(strict_types=1);
+
 /**
  * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  * @author Luc Calaresu <dev@calaresu.com>
@@ -26,6 +28,7 @@ use OCP\AppFramework\Db\DoesNotExistException;
 use OCP\AppFramework\Db\QBMapper;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\IDBConnection;
+use Throwable;
 
 /**
  * @template-extends QBMapper<LocalAttachment>
@@ -40,10 +43,33 @@ class LocalAttachmentMapper extends QBMapper {
 	}
 
 	/**
+	 * @return LocalAttachment[]
+	 */
+	public function findByLocalMessageId(int $localMessageId): array {
+		$qb = $this->db->getQueryBuilder();
+		$qb->select('*')
+			->from($this->getTableName())
+			->where(
+				$qb->expr()->eq('local_message_id', $qb->createNamedParameter($localMessageId, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)
+			);
+		return $this->findEntities($qb);
+	}
+
+	/**
+	 * @return LocalAttachment[]
+	 */
+	public function findByLocalMessageIds(array $localMessageIds): array {
+		$qb = $this->db->getQueryBuilder();
+		$qb->select('*')
+			->from($this->getTableName())
+			->where(
+				$qb->expr()->in('local_message_id', $qb->createNamedParameter($localMessageIds, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)
+			);
+		return $this->findEntities($qb);
+	}
+
+	/**
 	 * @throws DoesNotExistException
-	 *
-	 * @param string $userId
-	 * @param int $id
 	 */
 	public function find(string $userId, int $id): LocalAttachment {
 		$qb = $this->db->getQueryBuilder();
@@ -55,4 +81,18 @@ class LocalAttachmentMapper extends QBMapper {
 
 		return $this->findEntity($query);
 	}
+
+	public function deleteForLocalMailbox(int $localMessageId): void {
+		$this->db->beginTransaction();
+		try {
+			$qb = $this->db->getQueryBuilder();
+			$qb->delete($this->getTableName())
+				->where($qb->expr()->eq('local_message_id', $qb->createNamedParameter($localMessageId), IQueryBuilder::PARAM_INT));
+			$qb->execute();
+			$this->db->commit();
+		} catch (Throwable $e) {
+			$this->db->rollBack();
+			throw $e;
+		}
+	}
 }
diff --git a/lib/Db/LocalMessage.php b/lib/Db/LocalMessage.php
new file mode 100644
index 000000000..52580618e
--- /dev/null
+++ b/lib/Db/LocalMessage.php
@@ -0,0 +1,142 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna@nextcloud.com>
+ *
+ * @author 2022 Anna Larch <anna@nextcloud.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Db;
+
+use JsonSerializable;
+use OCP\AppFramework\Db\Entity;
+
+/**
+ * @method int getType()
+ * @method void setType(int $type)
+ * @method int getAccountId()
+ * @method void setAccountId(int $accountId)
+ * @method int|null getAliasId()
+ * @method void setAliasId(?int $aliasId)
+ * @method int getSendAt()
+ * @method void setSendAt(int $sendAt)
+ * @method string getSubject()
+ * @method void setSubject(string $subject)
+ * @method string getBody()
+ * @method void setBody(string $body)
+ * @method bool isHtml()
+ * @method void setHtml(bool $html)
+ * @method string|null getInReplyToMessageId()
+ * @method void setInReplyToMessageId(?string $inReplyToId)
+ */
+class LocalMessage extends Entity implements JsonSerializable {
+	public const TYPE_OUTGOING = 0;
+	public const TYPE_DRAFT = 1;
+
+	/**
+	 * @var int
+	 * @psalm-var self::TYPE_*
+	 */
+	protected $type;
+
+	/** @var int */
+	protected $accountId;
+
+	/** @var int|null */
+	protected $aliasId;
+
+	/** @var int */
+	protected $sendAt;
+
+	/** @var string */
+	protected $subject;
+
+	/** @var string */
+	protected $body;
+
+	/** @var bool */
+	protected $html;
+
+	/** @var string|null */
+	protected $inReplyToMessageId;
+
+	/** @var array|null */
+	protected $attachments;
+
+	/** @var array|null */
+	protected $recipients;
+
+	public function __construct() {
+		$this->addType('type', 'integer');
+		$this->addType('accountId', 'integer');
+		$this->addType('aliasId', 'integer');
+		$this->addType('sendAt', 'integer');
+		$this->addType('html', 'boolean');
+	}
+
+	/**
+	 * @return array
+	 */
+	public function jsonSerialize() {
+		return [
+			'id' => $this->getId(),
+			'type' => $this->getType(),
+			'accountId' => $this->getAccountId(),
+			'aliasId' => $this->getAccountId(),
+			'sendAt' => $this->getSendAt(),
+			'subject' => $this->getSubject(),
+			'text' => $this->getBody(),
+			'html' => $this->isHtml(),
+			'inReplyToMessageId' => $this->getInReplyToMessageId(),
+			'attachments' => $this->getAttachments(),
+			'recipients' => $this->getRecipients()
+		];
+	}
+
+	/**
+	 * @param LocalAttachment[] $attachments
+	 * @return void
+	 */
+	public function setAttachments(array $attachments): void {
+		$this->attachments = $attachments;
+	}
+
+	/**
+	 * @return LocalAttachment[]|null
+	 */
+	public function getAttachments(): ?array {
+		return $this->attachments;
+	}
+
+	/**
+	 * @param Recipient[] $recipients
+	 * @return void
+	 */
+	public function setRecipients(array $recipients): void {
+		$this->recipients = $recipients;
+	}
+
+	/**
+	 * @return Recipient[]|null
+	 */
+	public function getRecipients(): ?array {
+		return $this->recipients;
+	}
+}
diff --git a/lib/Db/LocalMessageMapper.php b/lib/Db/LocalMessageMapper.php
new file mode 100644
index 000000000..4b63849d5
--- /dev/null
+++ b/lib/Db/LocalMessageMapper.php
@@ -0,0 +1,146 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna@nextcloud.com>
+ *
+ * @author 2022 Anna Larch <anna@nextcloud.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Db;
+
+use OCP\AppFramework\Db\DoesNotExistException;
+use OCP\DB\Exception as DBException;
+use Throwable;
+use function array_map;
+use OCP\AppFramework\Db\QBMapper;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\IDBConnection;
+
+/**
+ * @template-extends QBMapper<LocalMessage>
+ */
+class LocalMessageMapper extends QBMapper {
+	/** @var LocalAttachmentMapper */
+	private $attachmentMapper;
+
+	/** @var RecipientMapper */
+	private $recipientMapper;
+
+	public function __construct(IDBConnection $db,
+								LocalAttachmentMapper $attachmentMapper,
+								RecipientMapper $recipientMapper) {
+		parent::__construct($db, 'mail_local_messages');
+		$this->recipientMapper = $recipientMapper;
+		$this->attachmentMapper = $attachmentMapper;
+	}
+
+	/**
+	 * @param string $userId
+	 * @return LocalMessage[]
+	 * @throws DBException
+	 */
+	public function getAllForUser(string $userId): array {
+		$qb = $this->db->getQueryBuilder();
+		$qb->select('m.*')
+			->from('mail_accounts', 'a')
+			->join('a', $this->getTableName(), 'm', $qb->expr()->eq('m.account_id', 'a.id'))
+			->where(
+				$qb->expr()->eq('a.user_id', $qb->createNamedParameter($userId, IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR),
+				$qb->expr()->eq('m.type', $qb->createNamedParameter(LocalMessage::TYPE_OUTGOING, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)
+			);
+		$rows = $qb->execute();
+
+		$results = [];
+		$ids = [];
+		while (($row = $rows->fetch()) !== false) {
+			$results[] = $this->mapRowToEntity($row);
+			$ids[] = $row['id'];
+		}
+		$rows->closeCursor();
+
+		$attachments = $this->attachmentMapper->findByLocalMessageIds($ids);
+		$recipients = $this->recipientMapper->findByLocalMessageIds($ids);
+
+		$recipientMap = [];
+		foreach ($recipients as $r) {
+			$recipientMap[$r->getLocalMessageId()][] = $r;
+		}
+		$attachmentMap = [];
+		foreach ($attachments as $a) {
+			$attachmentMap[$a->getLocalMessageId()][] = $a;
+		}
+
+		return array_map(static function ($localMessage) use ($attachmentMap, $recipientMap) {
+			$localMessage->setAttachments($attachmentMap[$localMessage->getId()] ?? []);
+			$localMessage->setRecipients($recipientMap[$localMessage->getId()] ?? []);
+			return $localMessage;
+		}, $results);
+	}
+
+	/**
+	 * @throws DoesNotExistException
+	 */
+	public function findById(int $id, string $userId): LocalMessage {
+		$qb = $this->db->getQueryBuilder();
+		$qb->select('m.*')
+			->from('mail_accounts', 'a')
+			->join('a', $this->getTableName(), 'm', $qb->expr()->eq('m.account_id', 'a.id'))
+			->where(
+				$qb->expr()->eq('a.user_id', $qb->createNamedParameter($userId, IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR),
+				$qb->expr()->eq('m.id', $qb->createNamedParameter($id, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT)
+			);
+		$entity = $this->findEntity($qb);
+		$entity->setAttachments($this->attachmentMapper->findByLocalMessageId($id));
+		$entity->setRecipients($this->recipientMapper->findByLocalMessageId($id));
+		return $entity;
+	}
+
+	/**
+	 * @param Recipient[] $to
+	 * @param Recipient[] $cc
+	 * @param Recipient[] $bcc
+	 */
+	public function saveWithRelatedData(LocalMessage $message, array $to, array $cc, array $bcc): void {
+		$this->db->beginTransaction();
+		try {
+			$message = $this->insert($message);
+			$this->recipientMapper->saveRecipients($message->getId(), $to, Recipient::TYPE_TO);
+			$this->recipientMapper->saveRecipients($message->getId(), $cc, Recipient::TYPE_CC);
+			$this->recipientMapper->saveRecipients($message->getId(), $bcc, Recipient::TYPE_BCC);
+			$this->db->commit();
+		} catch (Throwable $e) {
+			$this->db->rollBack();
+			throw $e;
+		}
+	}
+
+	public function deleteWithRelated(LocalMessage $message): void {
+		$this->db->beginTransaction();
+		try {
+			$this->attachmentMapper->deleteForLocalMailbox($message->getId());
+			$this->recipientMapper->deleteForLocalMailbox($message->getId());
+			$this->delete($message);
+			$this->db->commit();
+		} catch (Throwable $e) {
+			$this->db->rollBack();
+			throw $e;
+		}
+	}
+}
diff --git a/lib/Db/MessageMapper.php b/lib/Db/MessageMapper.php
index 5320f4248..3e5930f7a 100644
--- a/lib/Db/MessageMapper.php
+++ b/lib/Db/MessageMapper.php
@@ -1129,7 +1129,10 @@ class MessageMapper extends QBMapper {
 		$recipientIdsQuery = $qb3->selectDistinct('r.id')
 			->from('mail_recipients', 'r')
 			->leftJoin('r', 'mail_messages', 'm', $qb3->expr()->eq('r.message_id', 'm.id'))
-			->where($qb3->expr()->isNull('m.id'));
+			->where(
+				$qb3->expr()->isNull('m.id'),
+				$qb3->expr()->isNull('r.local_message_id')
+			);
 		$result = $recipientIdsQuery->execute();
 		$ids = array_map(function (array $row) {
 			return (int)$row['id'];
diff --git a/lib/Db/Recipient.php b/lib/Db/Recipient.php
new file mode 100644
index 000000000..5bfb276f8
--- /dev/null
+++ b/lib/Db/Recipient.php
@@ -0,0 +1,85 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * Mail App
+ *
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author Anna Larch <anna.larch@gmx.net>
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or any later version.
+ *
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU AFFERO GENERAL PUBLIC LICENSE for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public
+ * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Mail\Db;
+
+use JsonSerializable;
+use OCP\AppFramework\Db\Entity;
+
+/**
+ * @method int|null getMessageId()
+ * @method void setMessageId(int $messageId)
+ * @method int|null getLocalMessageId()
+ * @method void setLocalMessageId(int $localMessageId)
+ * @method int getType()
+ * @method void setType(int $type)
+ * @method string getLabel()
+ * @method void setLabel(string $label)
+ * @method string getEmail()
+ * @method void setEmail(string $email)
+ */
+class Recipient extends Entity implements JsonSerializable {
+	public const TYPE_FROM = 0;
+	public const TYPE_TO = 1;
+	public const TYPE_CC = 2;
+	public const TYPE_BCC = 3;
+
+	/** @var int|null */
+	protected $messageId;
+
+	/**
+	 * @var int
+	 * @psalm-var self::TYPE_*
+	 */
+	protected $type;
+
+	/** @var int|null */
+	protected $localMessageId;
+
+	/** @var string */
+	protected $label;
+
+	/** @var string */
+	protected $email;
+
+	public function __construct() {
+		$this->addType('messageId', 'integer');
+		$this->addType('localMessageId', 'integer');
+		$this->addType('type', 'integer');
+		$this->addType('mailboxType', 'integer');
+	}
+
+	public function jsonSerialize(): array {
+		return [
+			'id' => $this->getId(),
+			'messageId' => $this->getMessageId(),
+			'localMessageId' => $this->getLocalMessageId(),
+			'type' => $this->getType(),
+			'label' => $this->getLabel(),
+			'email' => $this->getEmail()
+		];
+	}
+}
diff --git a/lib/Db/RecipientMapper.php b/lib/Db/RecipientMapper.php
new file mode 100644
index 000000000..b5c2fee45
--- /dev/null
+++ b/lib/Db/RecipientMapper.php
@@ -0,0 +1,107 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * Mail App
+ *
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author Anna Larch <anna.larch@gmx.net>
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or any later version.
+ *
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU AFFERO GENERAL PUBLIC LICENSE for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public
+ * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Mail\Db;
+
+use OCP\AppFramework\Db\QBMapper;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\IDBConnection;
+
+/**
+ * @template-extends QBMapper<Recipient>
+ */
+class RecipientMapper extends QBMapper {
+	public function __construct(IDBConnection $db) {
+		parent::__construct($db, 'mail_recipients');
+	}
+
+	/**
+	 * @returns Recipient[]
+	 */
+	public function findByLocalMessageId(int $localMessageId): array {
+		$qb = $this->db->getQueryBuilder();
+
+		$query = $qb->select('*')
+			->from($this->getTableName())
+			->where(
+				$qb->expr()->eq('local_message_id', $qb->createNamedParameter($localMessageId, IQueryBuilder::PARAM_INT))
+			);
+
+		return $this->findEntities($query);
+	}
+
+	/**
+	 *  @return Recipient[]
+	 */
+	public function findByLocalMessageIds(array $localMessageIds): array {
+		$qb = $this->db->getQueryBuilder();
+
+		$query = $qb->select('*')
+			->from($this->getTableName())
+			->where(
+				$qb->expr()->in('local_message_id', $qb->createNamedParameter($localMessageIds, IQueryBuilder::PARAM_INT_ARRAY), IQueryBuilder::PARAM_INT_ARRAY)
+			);
+
+		return $this->findEntities($query);
+	}
+
+	public function deleteForLocalMailbox(int $localMessageId): void {
+		$qb = $this->db->getQueryBuilder();
+
+		$qb->delete($this->getTableName())
+			->where(
+				$qb->expr()->eq('local_message_id', $qb->createNamedParameter($localMessageId, IQueryBuilder::PARAM_INT))
+			);
+		$qb->execute();
+	}
+
+	/**
+	 * @param int $localMessageId
+	 * @param Recipient[] $recipients
+	 * @param int $type
+	 * @psalm-param Recipient::TYPE_* $type
+	 */
+	public function saveRecipients(int $localMessageId, array $recipients, int $type): void {
+		if (empty($recipients)) {
+			return;
+		}
+
+		$qb = $this->db->getQueryBuilder();
+		$qb->insert($this->getTableName());
+		$qb->setValue('local_message_id', $qb->createParameter('local_message_id'));
+		$qb->setValue('type', $qb->createParameter('type'));
+		$qb->setValue('label', $qb->createParameter('label'));
+		$qb->setValue('email', $qb->createParameter('email'));
+
+		foreach ($recipients as $recipient) {
+			$qb->setParameter('local_message_id', $localMessageId, IQueryBuilder::PARAM_INT);
+			$qb->setParameter('type', $type, IQueryBuilder::PARAM_INT);
+			$qb->setParameter('label', $recipient->getLabel() ?? $recipient->getEmail(), IQueryBuilder::PARAM_STR);
+			$qb->setParameter('email', $recipient->getEmail(), IQueryBuilder::PARAM_STR);
+			$qb->execute();
+		}
+	}
+}
diff --git a/lib/Migration/Version1120Date20220223094709.php b/lib/Migration/Version1120Date20220223094709.php
new file mode 100644
index 000000000..0571a0782
--- /dev/null
+++ b/lib/Migration/Version1120Date20220223094709.php
@@ -0,0 +1,106 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * Mail App
+ *
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author Anna Larch <anna.larch@gmx.net>
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or any later version.
+ *
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU AFFERO GENERAL PUBLIC LICENSE for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public
+ * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Mail\Migration;
+
+use Closure;
+use Doctrine\DBAL\Schema\Table;
+use OCP\DB\ISchemaWrapper;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+class Version1120Date20220223094709 extends SimpleMigrationStep {
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		$schema = $schemaClosure();
+
+		$localMessageTable = $schema->createTable('mail_local_messages');
+		$localMessageTable->addColumn('id', 'integer', [
+			'autoincrement' => true,
+			'notnull' => true,
+			'length' => 4,
+		]);
+		$localMessageTable->addColumn('type', 'integer', [
+			'notnull' => true,
+			'unsigned' => true,
+			'length' => 1,
+		]);
+		$localMessageTable->addColumn('account_id', 'integer', [
+			'notnull' => true,
+			'length' => 4,
+		]);
+		$localMessageTable->addColumn('alias_id', 'integer', [
+			'notnull' => false,
+			'length' => 4,
+		]);
+		$localMessageTable->addColumn('send_at', 'integer', [
+			'notnull' => false,
+			'length' => 4
+		]);
+		$localMessageTable->addColumn('subject', 'text', [
+			'notnull' => true,
+			'length' => 255
+		]);
+		$localMessageTable->addColumn('body', 'text', [
+			'notnull' => true
+		]);
+		$localMessageTable->addColumn('html', 'boolean', [
+			'notnull' => false,
+			'default' => false,
+		]);
+		$localMessageTable->addColumn('in_reply_to_message_id', 'text', [
+			'notnull' => false,
+			'length' => 1023,
+		]);
+		$localMessageTable->setPrimaryKey(['id']);
+
+		/** @var Table $recipientsTable */
+		$recipientsTable = $schema->getTable('mail_recipients');
+		$recipientsTable->addColumn('local_message_id', 'integer', [
+			'notnull' => false,
+			'length' => 4,
+		]);
+		$recipientsTable->changeColumn('message_id', [
+			'notnull' => false
+		]);
+		$recipientsTable->addForeignKeyConstraint($localMessageTable, ['local_message_id'], ['id'], ['onDelete' => 'CASCADE']);
+
+		$attachmentsTable = $schema->getTable('mail_attachments');
+		$attachmentsTable->addColumn('local_message_id', 'integer', [
+			'notnull' => false,
+			'length' => 4,
+		]);
+		$attachmentsTable->addForeignKeyConstraint($localMessageTable, ['local_message_id'], ['id'], ['onDelete' => 'CASCADE']);
+
+		return $schema;
+	}
+}
diff --git a/tests/Integration/Db/LocalMessageMapperTest.php b/tests/Integration/Db/LocalMessageMapperTest.php
new file mode 100644
index 000000000..a63a10490
--- /dev/null
+++ b/tests/Integration/Db/LocalMessageMapperTest.php
@@ -0,0 +1,173 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Tests\Integration\Db;
+
+use ChristophWurst\Nextcloud\Testing\TestCase;
+use OCA\Mail\Db\LocalAttachmentMapper;
+use OCA\Mail\Db\LocalMessage;
+use OCA\Mail\Db\LocalMessageMapper;
+use OCA\Mail\Db\MailAccount;
+use OCA\Mail\Db\Recipient;
+use OCA\Mail\Db\RecipientMapper;
+use OCA\Mail\Tests\Integration\Framework\ImapTestAccount;
+use OCP\AppFramework\Db\DoesNotExistException;
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\IDBConnection;
+use PHPUnit\Framework\MockObject\MockObject;
+
+class LocalMessageMapperTest extends TestCase {
+	use ImapTestAccount;
+
+	/** @var IDBConnection */
+	private $db;
+
+	/** @var LocalMessageMapper */
+	private $mapper;
+
+	/** @var ITimeFactory| MockObject */
+	private $timeFactory;
+
+	/** @var LocalMessage */
+	private $entity;
+
+	/** @var MailAccount */
+	private $account;
+
+	protected function setUp(): void {
+		parent::setUp();
+
+		$this->db = \OC::$server->getDatabaseConnection();
+		$recipientMapper = new RecipientMapper(
+			$this->db
+		);
+		$this->mapper = new LocalMessageMapper(
+			$this->db,
+			$this->createMock(LocalAttachmentMapper::class),
+			$recipientMapper
+		);
+
+		$qb = $this->db->getQueryBuilder();
+		$delete = $qb->delete($this->mapper->getTableName());
+		$delete->execute();
+
+		$this->account = $this->createTestAccount();
+
+		$message = new LocalMessage();
+		$message->setType(LocalMessage::TYPE_OUTGOING);
+		$message->setAccountId($this->account->getId());
+		$message->setAliasId(2);
+		$message->setSendAt(123);
+		$message->setSubject('subject');
+		$message->setBody('message');
+		$message->setHtml(true);
+		$message->setInReplyToMessageId('abc');
+		$this->entity = $this->mapper->insert($message);
+	}
+
+	public function testFindAllForUser(): void {
+		$result = $this->mapper->getAllForUser($this->getTestAccountUserId());
+
+		$this->assertCount(1, $result);
+		$row = $result[0];
+		$this->assertEquals(LocalMessage::TYPE_OUTGOING, $row->getType());
+		$this->assertEquals(2, $row->getAliasId());
+		$this->assertEquals($this->account->getId(), $row->getAccountId());
+		$this->assertEquals('subject', $row->getSubject());
+		$this->assertEquals('message', $row->getBody());
+		$this->assertEquals('abc', $row->getInReplyToMessageId());
+		$this->assertTrue($row->isHtml());
+		$this->assertEmpty($row->getAttachments());
+		$this->assertEmpty($row->getRecipients());
+	}
+
+	/**
+	 * @depends testFindAllForUser
+	 */
+	public function testFindById(): void {
+		$row = $this->mapper->findById($this->entity->getId(), $this->account->getUserId());
+
+		$this->assertEquals(LocalMessage::TYPE_OUTGOING, $row->getType());
+		$this->assertEquals(2, $row->getAliasId());
+		$this->assertEquals($this->account->getId(), $row->getAccountId());
+		$this->assertEquals('subject', $row->getSubject());
+		$this->assertEquals('message', $row->getBody());
+		$this->assertEquals('abc', $row->getInReplyToMessageId());
+		$this->assertTrue($row->isHtml());
+		$this->assertEmpty($row->getAttachments());
+		$this->assertEmpty($row->getRecipients());
+	}
+
+	public function testFindByIdNotFound(): void {
+		$this->expectException(DoesNotExistException::class);
+		$this->mapper->findById(1337, $this->account->getUserId());
+	}
+
+	/**
+	 * @depends testFindById
+	 */
+	public function testDeleteWithRelated(): void {
+		$this->mapper->deleteWithRelated($this->entity);
+
+		$result = $this->mapper->getAllForUser($this->getTestAccountUserId());
+
+		$this->assertEmpty($result);
+	}
+
+	public function testSaveWithRelatedData(): void {
+		// cleanup
+		$qb = $this->db->getQueryBuilder();
+		$delete = $qb->delete($this->mapper->getTableName());
+		$delete->execute();
+
+		$message = new LocalMessage();
+		$message->setType(LocalMessage::TYPE_OUTGOING);
+		$message->setAccountId($this->account->getId());
+		$message->setAliasId(3);
+		$message->setSendAt(3);
+		$message->setSubject('savedWithRelated');
+		$message->setBody('message');
+		$message->setHtml(true);
+		$message->setInReplyToMessageId('abcdefg');
+		$recipient = new Recipient();
+		$recipient->setEmail('wizard@stardew-valley.com');
+		$recipient->setLabel('M. Rasmodeus');
+		$to = [$recipient];
+
+		$this->mapper->saveWithRelatedData($message, $to, [], []);
+
+		$results = $this->mapper->getAllForUser($this->account->getUserId());
+		$row = $results[0];
+		$this->assertEquals(LocalMessage::TYPE_OUTGOING, $row->getType());
+		$this->assertEquals(3, $row->getAliasId());
+		$this->assertEquals($this->account->getId(), $row->getAccountId());
+		$this->assertEquals('savedWithRelated', $row->getSubject());
+		$this->assertEquals('message', $row->getBody());
+		$this->assertEquals('abcdefg', $row->getInReplyToMessageId());
+		$this->assertTrue($row->isHtml());
+		$this->assertEmpty($row->getAttachments());
+		$this->assertCount(1, $row->getRecipients());
+	}
+}
diff --git a/tests/Integration/Db/LocalMessageTest.php b/tests/Integration/Db/LocalMessageTest.php
new file mode 100644
index 000000000..449aa56d5
--- /dev/null
+++ b/tests/Integration/Db/LocalMessageTest.php
@@ -0,0 +1,66 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Tests\Integration\Db;
+
+use ChristophWurst\Nextcloud\Testing\TestCase;
+use OCA\Mail\Db\LocalMessage;
+use OCP\AppFramework\Utility\ITimeFactory;
+use PHPUnit\Framework\MockObject\MockObject;
+
+class LocalMessageTest extends TestCase {
+
+	/** @var ITimeFactory|MockObject  */
+	private $timeFactory;
+
+	protected function setUp(): void {
+		$this->timeFactory = $this->createMock(ITimeFactory::class);
+	}
+
+	public function testGettersSetters(): void {
+		$time = $this->timeFactory->getTime();
+		$message = new LocalMessage();
+
+		$message->setType(LocalMessage::TYPE_OUTGOING);
+		$message->setAccountId(1);
+		$message->setAliasId(2);
+		$message->setSendAt($time);
+		$message->setSubject('subject');
+		$message->setBody('message');
+		$message->setHtml(true);
+		$message->setInReplyToMessageId('<abcdefg@12345678.com>');
+
+		$this->assertEquals(LocalMessage::TYPE_OUTGOING, $message->getType());
+		$this->assertEquals(1, $message->getAccountId());
+		$this->assertEquals(2, $message->getAliasId());
+		$this->assertEquals($time, $message->getSendAt());
+		$this->assertEquals('subject', $message->getSubject());
+		$this->assertEquals('message', $message->getBody());
+		$this->assertTrue($message->isHtml());
+		$this->assertEquals('<abcdefg@12345678.com>', $message->getInReplyToMessageId());
+		$this->assertNull($message->getAttachments());
+		$this->assertNull($message->getRecipients());
+	}
+}
diff --git a/tests/Integration/Db/RecipientMapperTest.php b/tests/Integration/Db/RecipientMapperTest.php
new file mode 100644
index 000000000..e9e4e5e5b
--- /dev/null
+++ b/tests/Integration/Db/RecipientMapperTest.php
@@ -0,0 +1,170 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Tests\Integration\Db;
+
+use ChristophWurst\Nextcloud\Testing\TestCase;
+use OCA\Mail\Db\LocalAttachmentMapper;
+use OCA\Mail\Db\LocalMessage;
+use OCA\Mail\Db\LocalMessageMapper;
+use OCA\Mail\Db\Recipient;
+use OCA\Mail\Db\RecipientMapper;
+use OCA\Mail\Tests\Integration\Framework\ImapTestAccount;
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\IDBConnection;
+use PHPUnit\Framework\MockObject\MockObject;
+
+class RecipientMapperTest extends TestCase {
+	use ImapTestAccount;
+
+	/** @var IDBConnection */
+	private $db;
+
+	/** @var RecipientMapper */
+	private $mapper;
+
+	/** @var ITimeFactory| MockObject */
+	private $timeFactory;
+
+	/** @var Recipient */
+	private $inboxRecipient;
+
+	/** @var Recipient */
+	private $outboxRecipient;
+
+	/** @var LocalMessage  */
+	private $message;
+
+	protected function setUp(): void {
+		parent::setUp();
+
+		$this->db = \OC::$server->getDatabaseConnection();
+		$this->mapper = new RecipientMapper(
+			$this->db
+		);
+		$this->localMessageMapper = new LocalMessageMapper(
+			$this->db,
+			$this->createMock(LocalAttachmentMapper::class),
+			$this->createMock(RecipientMapper::class)
+		);
+
+		$qb = $this->db->getQueryBuilder();
+
+		$delete = $qb->delete($this->mapper->getTableName());
+		$delete->execute();
+
+		$qb = $this->db->getQueryBuilder();
+
+		$delete = $qb->delete($this->localMessageMapper->getTableName());
+		$delete->execute();
+
+		$message = new LocalMessage();
+		$message->setType(LocalMessage::TYPE_OUTGOING);
+		$message->setAccountId(1);
+		$message->setAliasId(2);
+		$message->setSendAt(123);
+		$message->setSubject('subject');
+		$message->setBody('message');
+		$message->setHtml(true);
+		$message->setInReplyToMessageId('abcd');
+		$this->message = $this->localMessageMapper->insert($message);
+
+		$this->outboxRecipient = new Recipient();
+		$this->outboxRecipient->setLocalMessageId($this->message->getId());
+		$this->outboxRecipient->setEmail('doc@stardew-clinic.com');
+		$this->outboxRecipient->setType(Recipient::TYPE_TO);
+		$this->outboxRecipient->setLabel('Dr. Harvey');
+		$this->mapper->insert($this->outboxRecipient);
+
+		$inboxRecipientTwo = new Recipient();
+		$inboxRecipientTwo->setLocalMessageId($this->message->getId());
+		$inboxRecipientTwo->setEmail('pierre@stardewvalley.com');
+		$inboxRecipientTwo->setType(Recipient::TYPE_CC);
+		$inboxRecipientTwo->setLabel("Pierre's General Store");
+		$this->mapper->insert($inboxRecipientTwo);
+	}
+
+	public function testFindRecipients(): void {
+		$result = $this->mapper->findByLocalMessageId($this->message->getId());
+		$this->assertCount(2, $result);
+	}
+
+	/**
+	 * @depends testFindRecipients
+	 */
+	public function testFindAllRecipients(): void {
+		$result = $this->mapper->findByLocalMessageIds([$this->message->getId(),789,789]);
+		$this->assertCount(2, $result);
+	}
+
+	/**
+	 * @depends testFindAllRecipients
+	 */
+	public function testFindAllRecipientsEmpty(): void {
+		$result = $this->mapper->findByLocalMessageIds([12,57842]);
+		$this->assertEmpty($result);
+	}
+
+	/**
+	 * @depends testFindAllRecipientsEmpty
+	 */
+	public function testDeleteForLocalMailbox(): void {
+		$this->mapper->deleteForLocalMailbox($this->message->getId());
+		$result = $this->mapper->findByLocalMessageId($this->message->getId());
+		$this->assertEmpty($result);
+	}
+
+	/**
+	 * @depends testDeleteForLocalMailbox
+	 */
+	public function testSaveRecipients(): void {
+		$message = new LocalMessage();
+		$message->setType(LocalMessage::TYPE_OUTGOING);
+		$message->setAccountId(1);
+		$message->setAliasId(2);
+		$message->setSendAt(123);
+		$message->setSubject('subject');
+		$message->setBody('message');
+		$message->setHtml(true);
+		$message->setInReplyToMessageId('abcd');
+		$message = $this->localMessageMapper->insert($message);
+
+		$recipient = new Recipient();
+		$recipient->setEmail('penny@stardewvalleylibrary.edu');
+		$recipient->setLabel('Penny');
+		$this->mapper->saveRecipients($message->getId(), [$recipient], Recipient::TYPE_FROM);
+
+		$results = $this->mapper->findByLocalMessageId($message->getId());
+		$this->assertCount(1, $results);
+
+		/** @var Recipient $entity */
+		$entity = $results[0];
+		$this->assertEquals($message->getId(), $entity->getLocalMessageId());
+		$this->assertNull($entity->getMessageId());
+		$this->assertEquals(Recipient::TYPE_FROM, $entity->getType());
+		$this->assertEquals('Penny', $entity->getLabel());
+		$this->assertEquals('penny@stardewvalleylibrary.edu', $entity->getEmail());
+	}
+}
diff --git a/tests/Integration/Db/RecipientTest.php b/tests/Integration/Db/RecipientTest.php
new file mode 100644
index 000000000..9eff50dbd
--- /dev/null
+++ b/tests/Integration/Db/RecipientTest.php
@@ -0,0 +1,50 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @author 2022 Anna Larch <anna.larch@gmx.net>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ */
+
+namespace OCA\Mail\Tests\Integration\Db;
+
+use ChristophWurst\Nextcloud\Testing\TestCase;
+use OCA\Mail\Db\Recipient;
+
+class RecipientTest extends TestCase {
+	protected function setUp(): void {
+	}
+
+	public function testGettersSetters(): void {
+		$recipient = new Recipient();
+		$recipient->setMessageId(1);
+		$recipient->setLocalMessageId(100);
+		$recipient->setType(Recipient::TYPE_TO);
+		$recipient->setLabel('Penny');
+		$recipient->setEmail('penny@stardew-library.edu');
+
+
+		$this->assertEquals(1, $recipient->getMessageId());
+		$this->assertEquals(100, $recipient->getLocalMessageId());
+		$this->assertEquals(Recipient::TYPE_TO, $recipient->getType());
+		$this->assertEquals('Penny', $recipient->getLabel());
+		$this->assertEquals('penny@stardew-library.edu', $recipient->getEmail());
+	}
+}