Merge pull request #752 from nextcloud/bugfix/737/optimize-stream

SQL optimization (part 1)

19 files changed, 1056 insertions, 116 deletions

diff --git a/lib/Command/Reset.php b/lib/Command/Reset.php
index 3f52b326..5601aab4 100644
--- a/lib/Command/Reset.php
+++ b/lib/Command/Reset.php
@@ -34,6 +34,7 @@ namespace OCA\Social\Command;
 use Exception;
 use OC\Core\Command\Base;
 use OCA\Social\Db\CoreRequestBuilder;
+use OCA\Social\Service\CheckService;
 use OCA\Social\Service\ConfigService;
 use OCA\Social\Service\MiscService;
 use Symfony\Component\Console\Input\InputInterface;
@@ -48,6 +49,9 @@ class Reset extends Base {
 
 	private $coreRequestBuilder;
 
+	/** @var CheckService */
+	private $checkService;
+
 	/** @var ConfigService */
 	private $configService;
 
@@ -59,15 +63,17 @@ class Reset extends Base {
 	 * CacheUpdate constructor.
 	 *
 	 * @param CoreRequestBuilder $coreRequestBuilder
+	 * @param CheckService $checkService
 	 * @param ConfigService $configService
 	 * @param MiscService $miscService
 	 */
 	public function __construct(
-		CoreRequestBuilder $coreRequestBuilder, ConfigService $configService,
+		CoreRequestBuilder $coreRequestBuilder, CheckService $checkService, ConfigService $configService,
 		MiscService $miscService
 	) {
 		parent::__construct();
 
+		$this->checkService = $checkService;
 		$this->coreRequestBuilder = $coreRequestBuilder;
 		$this->configService = $configService;
 		$this->miscService = $miscService;
@@ -131,7 +137,7 @@ class Reset extends Base {
 			return;
 		}
 
-
+		$this->checkService->checkInstallationStatus(true);
 		$output->writeln('');
 
 		$cloudAddress = $this->configService->getCloudUrl();
@@ -148,6 +154,7 @@ class Reset extends Base {
 		}
 
 		$this->configService->setCloudUrl($newCloudAddress);
+
 		$output->writeln('');
 		$output->writeln('New address: <info>' . $newCloudAddress . '</info>');
 	}
@@ -177,8 +184,7 @@ class Reset extends Base {
 		if ($this->configService->getCoreValue('public_host-meta') === 'social/lib/hostmeta.php') {
 			$this->configService->unsetCoreValue('public_host-meta');
 		}
-		if ($this->configService->getCoreValue('public_host-meta-json')
-			=== 'social/lib/hostmeta.php') {
+		if ($this->configService->getCoreValue('public_host-meta-json') === 'social/lib/hostmeta.php') {
 			$this->configService->unsetCoreValue('public_host-meta-json');
 		}
 	}
diff --git a/lib/Db/ActorsRequest.php b/lib/Db/ActorsRequest.php
index d0318d1a..7417be12 100644
--- a/lib/Db/ActorsRequest.php
+++ b/lib/Db/ActorsRequest.php
@@ -62,16 +62,15 @@ class ActorsRequest extends ActorsRequestBuilder {
 	 *
 	 * @param Person $actor
 	 *
-	 * @return string
 	 * @throws SocialAppConfigException
 	 */
-	public function create(Person $actor): string {
+	public function create(Person $actor) {
 
-		$id = $this->configService->getSocialUrl() . '@' . $actor->getPreferredUsername();
+		$actor->setId($this->configService->getSocialUrl() . '@' . $actor->getPreferredUsername());
 		$qb = $this->getActorsInsertSql();
 
-		$qb->setValue('id', $qb->createNamedParameter($id))
-//			   ->setValue('type', $qb->createNamedParameter($actor->getType()))
+		$qb->setValue('id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('id_prim', $qb->createNamedParameter($this->prim($actor->getId())))
 		   ->setValue('user_id', $qb->createNamedParameter($actor->getUserId()))
 		   ->setValue('name', $qb->createNamedParameter($actor->getName()))
 		   ->setValue('summary', $qb->createNamedParameter($actor->getSummary()))
@@ -86,11 +85,7 @@ class ActorsRequest extends ActorsRequestBuilder {
 			   $qb->createNamedParameter(new DateTime('now'), IQueryBuilder::PARAM_DATE)
 		   );
 
-		$this->generatePrimaryKey($qb, $id);
-
 		$qb->execute();
-
-		return $id;
 	}
 
 
diff --git a/lib/Db/ActorsRequestBuilder.php b/lib/Db/ActorsRequestBuilder.php
index 99b1036c..444f4ea5 100644
--- a/lib/Db/ActorsRequestBuilder.php
+++ b/lib/Db/ActorsRequestBuilder.php
@@ -77,12 +77,12 @@ class ActorsRequestBuilder extends CoreRequestBuilder {
 
 		/** @noinspection PhpMethodParametersCountMismatchInspection */
 		$qb->select(
-			'sa.id', 'sa.user_id', 'sa.preferred_username', 'sa.name', 'sa.summary',
-			'sa.public_key', 'sa.avatar_version', 'sa.private_key', 'sa.creation'
+			'a.id', 'a.id_prim', 'a.user_id', 'a.preferred_username', 'a.name', 'a.summary',
+			'a.public_key', 'a.avatar_version', 'a.private_key', 'a.creation'
 		)
-		   ->from(self::TABLE_ACTORS, 'sa');
+		   ->from(self::TABLE_ACTORS, 'a');
 
-		$this->defaultSelectAlias = 'sa';
+		$this->defaultSelectAlias = 'a';
 
 		return $qb;
 	}
diff --git a/lib/Db/CacheActorsRequest.php b/lib/Db/CacheActorsRequest.php
index 8f5bda80..f74dcb40 100644
--- a/lib/Db/CacheActorsRequest.php
+++ b/lib/Db/CacheActorsRequest.php
@@ -65,9 +65,9 @@ class CacheActorsRequest extends CacheActorsRequestBuilder {
 	 * @param Person $actor
 	 */
 	public function save(Person $actor) {
-
 		$qb = $this->getCacheActorsInsertSql();
 		$qb->setValue('id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('id_prim', $qb->createNamedParameter($this->prim($actor->getId())))
 		   ->setValue('account', $qb->createNamedParameter($actor->getAccount()))
 		   ->setValue('type', $qb->createNamedParameter($actor->getType()))
 		   ->setValue('local', $qb->createNamedParameter(($actor->isLocal()) ? '1' : '0'))
diff --git a/lib/Db/CoreRequestBuilder.php b/lib/Db/CoreRequestBuilder.php
index 040c1736..bfa2b1f6 100644
--- a/lib/Db/CoreRequestBuilder.php
+++ b/lib/Db/CoreRequestBuilder.php
@@ -58,23 +58,14 @@ use OCP\IDBConnection;
 class CoreRequestBuilder {
 
 
-//	const TABLE_REQUEST_QUEUE = 'social_request_queue';
-//
-//	const TABLE_SERVER_ACTORS = 'social_server_actors';
-//	const TABLE_SERVER_NOTES = 'social_server_notes';
-//	const TABLE_SERVER_HASHTAGS = 'social_server_hashtags';
-//	const TABLE_SERVER_FOLLOWS = 'social_server_follows';
-//
-//	const TABLE_CACHE_ACTORS = 'social_cache_actors';
-//	const TABLE_CACHE_DOCUMENTS = 'social_cache_documents';
-//
-//	const TABLE_QUEUE_STREAM = 'social_queue_stream';
-//	const TABLE_STREAM_ACTIONS = 'social_stream_actions';
-
 	const TABLE_REQUEST_QUEUE = 'social_a2_request_queue';
 
 	const TABLE_ACTORS = 'social_a2_actors';
-	const TABLE_STREAMS = 'social_a2_stream';
+	const TABLE_STREAM = 'social_a2_stream';
+	const TABLE_STREAM_DEST = 'social_a2_stream_dest';
+	const TABLE_STREAM_QUEUE = 'social_a2_stream_queue';
+	const TABLE_STREAM_ACTIONS = 'social_a2_stream_action';
+
 	const TABLE_HASHTAGS = 'social_a2_hashtags';
 	const TABLE_FOLLOWS = 'social_a2_follows';
 	const TABLE_ACTIONS = 'social_a2_actions';
@@ -82,20 +73,18 @@ class CoreRequestBuilder {
 	const TABLE_CACHE_ACTORS = 'social_a2_cache_actors';
 	const TABLE_CACHE_DOCUMENTS = 'social_a2_cache_documts';
 
-	const TABLE_STREAM_QUEUE = 'social_a2_stream_queue';
-	const TABLE_STREAM_ACTIONS = 'social_a2_stream_action';
-
 	/** @var array */
 	private $tables = [
 		self::TABLE_REQUEST_QUEUE,
 		self::TABLE_ACTORS,
-		self::TABLE_STREAMS,
+		self::TABLE_STREAM,
 		self::TABLE_HASHTAGS,
 		self::TABLE_FOLLOWS,
 		self::TABLE_ACTIONS,
 		self::TABLE_CACHE_ACTORS,
 		self::TABLE_CACHE_DOCUMENTS,
 		self::TABLE_STREAM_QUEUE,
+		self::TABLE_STREAM_DEST,
 		self::TABLE_STREAM_ACTIONS
 	];
 
@@ -141,11 +130,27 @@ class CoreRequestBuilder {
 
 
 	/**
+	 * @param string $id
+	 *
+	 * @return string
+	 */
+	public function prim(string $id): string {
+		if ($id === '') {
+			return '';
+		}
+
+		return hash('sha512', $id);
+	}
+
+
+	/**
 	 * @param IQueryBuilder $qb
 	 * @param string $id
+	 *
+	 * @deprecated - not that useful, the raw line should be implemented instead of calling this method !
 	 */
 	public function generatePrimaryKey(IQueryBuilder $qb, string $id) {
-		$qb->setValue('id_prim', $qb->createNamedParameter(hash('sha512', $id)));
+		$qb->setValue('id_prim', $qb->createNamedParameter($this->prim($id)));
 	}
 
 
@@ -544,7 +549,7 @@ class CoreRequestBuilder {
 	 * @return string
 	 */
 	protected function exprLimitToDBField(
-		IQueryBuilder &$qb, string $field, string $value, bool $eq, bool $cs = true,
+		IQueryBuilder &$qb, string $field, string $value, bool $eq = true, bool $cs = true,
 		string $alias = ''
 	): string {
 		$expr = $qb->expr();
@@ -714,9 +719,42 @@ class CoreRequestBuilder {
 
 	/**
 	 * @param IQueryBuilder $qb
+	 * @param string $alias
+	 */
+	protected function selectCacheActors(IQueryBuilder &$qb, string $alias = 'ca') {
+		if ($qb->getType() !== QueryBuilder::SELECT) {
+			return;
+		}
+
+		$pf = (($alias === '') ? $this->defaultSelectAlias : $alias);
+		$qb->from(self::TABLE_CACHE_ACTORS, $pf);
+		$qb->selectAlias($pf . '.id', 'cacheactor_id')
+		   ->selectAlias($pf . '.type', 'cacheactor_type')
+		   ->selectAlias($pf . '.account', 'cacheactor_account')
+		   ->selectAlias($pf . '.following', 'cacheactor_following')
+		   ->selectAlias($pf . '.followers', 'cacheactor_followers')
+		   ->selectAlias($pf . '.inbox', 'cacheactor_inbox')
+		   ->selectAlias($pf . '.shared_inbox', 'cacheactor_shared_inbox')
+		   ->selectAlias($pf . '.outbox', 'cacheactor_outbox')
+		   ->selectAlias($pf . '.featured', 'cacheactor_featured')
+		   ->selectAlias($pf . '.url', 'cacheactor_url')
+		   ->selectAlias($pf . '.preferred_username', 'cacheactor_preferred_username')
+		   ->selectAlias($pf . '.name', 'cacheactor_name')
+		   ->selectAlias($pf . '.summary', 'cacheactor_summary')
+		   ->selectAlias($pf . '.public_key', 'cacheactor_public_key')
+		   ->selectAlias($pf . '.source', 'cacheactor_source')
+		   ->selectAlias($pf . '.creation', 'cacheactor_creation')
+		   ->selectAlias($pf . '.local', 'cacheactor_local');
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
 	 * @param string $fieldActorId
 	 * @param Person $author
 	 * @param string $alias
+	 *
+	 * @deprecated ?
 	 */
 	protected function leftJoinCacheActors(
 		IQueryBuilder &$qb, string $fieldActorId, Person $author = null, string $alias = ''
@@ -863,10 +901,8 @@ class CoreRequestBuilder {
 			return;
 		}
 
-		$expr = $qb->expr();
-		$func = $qb->func();
-
 		$pf = $this->defaultSelectAlias;
+		$expr = $qb->expr();
 
 		$qb->selectAlias('sa.id', 'streamaction_id')
 		   ->selectAlias('sa.actor_id', 'streamaction_actor_id')
@@ -874,21 +910,20 @@ class CoreRequestBuilder {
 		   ->selectAlias('sa.values', 'streamaction_values');
 
 		$orX = $expr->orX();
-		$orX->add($expr->eq($func->lower($pf . '.id'), $func->lower('sa.stream_id')));
-		$orX->add($expr->eq($func->lower($pf . '.object_id'), $func->lower('sa.stream_id')));
+		$orX->add($expr->eq('sa.stream_id_prim', $pf . '.id_prim'));
+		$orX->add($expr->eq('sa.stream_id_prim', $pf . '.object_id_prim'));
 
-		$andX = $expr->andX();
-		$andX->add($orX);
-		$andX->add(
+		$on = $expr->andX();
+		$on->add(
 			$expr->eq(
-				$func->lower('sa.actor_id'),
-				$qb->createNamedParameter(strtolower($this->viewer->getId()))
+				'sa.actor_id_prim', $qb->createNamedParameter($this->prim($this->viewer->getId()))
 			)
 		);
+		$on->add($orX);
 
 		$qb->leftJoin(
 			$this->defaultSelectAlias, CoreRequestBuilder::TABLE_STREAM_ACTIONS, 'sa',
-			$andX
+			$on
 		);
 	}
 
diff --git a/lib/Db/FollowsRequest.php b/lib/Db/FollowsRequest.php
index dea89655..cf2b19d3 100644
--- a/lib/Db/FollowsRequest.php
+++ b/lib/Db/FollowsRequest.php
@@ -35,6 +35,7 @@ use daita\MySmallPhpTools\Traits\TArrayTools;
 use DateTime;
 use Exception;
 use OCA\Social\Exceptions\FollowDoesNotExistException;
+use OCA\Social\Model\ActivityPub\Actor\Person;
 use OCA\Social\Model\ActivityPub\Object\Follow;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 
@@ -62,7 +63,10 @@ class FollowsRequest extends FollowsRequestBuilder {
 		   ->setValue('type', $qb->createNamedParameter($follow->getType()))
 		   ->setValue('object_id', $qb->createNamedParameter($follow->getObjectId()))
 		   ->setValue('follow_id', $qb->createNamedParameter($follow->getFollowId()))
-		   ->setValue('accepted', $qb->createNamedParameter(($follow->isAccepted()) ? '1' : '0'));
+		   ->setValue('accepted', $qb->createNamedParameter(($follow->isAccepted()) ? '1' : '0'))
+		   ->setValue('actor_id_prim', $qb->createNamedParameter($this->prim($follow->getActorId())))
+		   ->setValue('object_id_prim', $qb->createNamedParameter($this->prim($follow->getObjectId())))
+		   ->setValue('follow_id_prim', $qb->createNamedParameter($this->prim($follow->getFollowId())));
 
 		try {
 			$qb->setValue(
@@ -78,6 +82,32 @@ class FollowsRequest extends FollowsRequestBuilder {
 	}
 
 
+	public function generateLoopbackAccount(Person $actor) {
+		$qb = $this->getFollowsInsertSql();
+		$qb->setValue('id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('actor_id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('type', $qb->createNamedParameter('Loopback'))
+		   ->setValue('object_id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('follow_id', $qb->createNamedParameter($actor->getId()))
+		   ->setValue('accepted', $qb->createNamedParameter('1'))
+		   ->setValue('actor_id_prim', $qb->createNamedParameter($this->prim($actor->getId())))
+		   ->setValue('object_id_prim', $qb->createNamedParameter($this->prim($actor->getId())))
+		   ->setValue('follow_id_prim', $qb->createNamedParameter($this->prim($actor->getId())));
+
+		try {
+			$qb->setValue(
+				'creation',
+				$qb->createNamedParameter(new DateTime('now'), IQueryBuilder::PARAM_DATE)
+			);
+		} catch (Exception $e) {
+		}
+
+		$this->generatePrimaryKey($qb, $actor->getId());
+
+		$qb->execute();
+	}
+
+
 	/**
 	 * @param Follow $follow
 	 */
diff --git a/lib/Db/StreamActionsRequest.php b/lib/Db/StreamActionsRequest.php
index 67490923..b1e4d84a 100644
--- a/lib/Db/StreamActionsRequest.php
+++ b/lib/Db/StreamActionsRequest.php
@@ -51,7 +51,9 @@ class StreamActionsRequest extends StreamActionsRequestBuilder {
 	public function create(StreamAction $action) {
 		$qb = $this->getStreamActionInsertSql();
 		$qb->setValue('actor_id', $qb->createNamedParameter($action->getActorId()))
+		   ->setValue('actor_id_prim', $qb->createNamedParameter($this->prim($action->getActorId())))
 		   ->setValue('stream_id', $qb->createNamedParameter($action->getStreamId()))
+		   ->setValue('stream_id_prim', $qb->createNamedParameter($this->prim($action->getStreamId())))
 		   ->setValue(
 			   'values', $qb->createNamedParameter(
 			   json_encode($action->getValues(), JSON_UNESCAPED_SLASHES)
diff --git a/lib/Db/StreamDestRequest.php b/lib/Db/StreamDestRequest.php
new file mode 100644
index 00000000..9b68c171
--- /dev/null
+++ b/lib/Db/StreamDestRequest.php
@@ -0,0 +1,113 @@
+<?php
+declare(strict_types=1);
+
+
+/**
+ * Nextcloud - Social Support
+ *
+ * This file is licensed under the Affero General Public License version 3 or
+ * later. See the COPYING file.
+ *
+ * @author Maxence Lange <maxence@artificial-owl.com>
+ * @copyright 2018, Maxence Lange <maxence@artificial-owl.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\Social\Db;
+
+
+use daita\MySmallPhpTools\Traits\TStringTools;
+use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
+use OCA\Social\Model\ActivityPub\Stream;
+
+
+/**
+ * Class StreamDestRequest
+ *
+ * @package OCA\Social\Db
+ */
+class StreamDestRequest extends StreamDestRequestBuilder {
+
+
+	use TStringTools;
+
+
+	/**
+	 * @return int
+	 */
+	public function countStreamDest(): int {
+		$qb = $this->countStreamDestSelectSql();
+
+		$cursor = $qb->execute();
+		$data = $cursor->fetch();
+		$cursor->closeCursor();
+
+		return $this->getInt('count', $data, 0);
+	}
+
+
+	/**
+	 * @param Stream $stream
+	 */
+	public function generateStreamDest(Stream $stream) {
+		$recipients = [
+			'to'            => $stream->getToAll(),
+			'cc'            => $stream->getCcArray(),
+			'bcc'           => $stream->getBccArray(),
+			'attributed_to' => [$stream->getAttributedTo()]
+		];
+
+		$streamId = $this->prim($stream->getId());
+		foreach (array_keys($recipients) as $dest) {
+			$type = $dest;
+			foreach ($recipients[$dest] as $actorId) {
+				if ($actorId === '') {
+					continue;
+				}
+
+				$qb = $this->getStreamDestInsertSql();
+
+				$qb->setValue('stream_id', $qb->createNamedParameter($streamId));
+				$qb->setValue('actor_id', $qb->createNamedParameter($this->prim($actorId)));
+				$qb->setValue('type', $qb->createNamedParameter($type));
+				try {
+					$qb->execute();
+				} catch (UniqueConstraintViolationException $e) {
+					\OC::$server->getLogger()
+								->log(3, 'Social - Duplicate recipient on Stream ' . json_encode($stream));
+				}
+			}
+		}
+	}
+
+
+	/**
+	 *
+	 */
+	public function generateRandomDest() {
+		$qb = $this->getStreamDestInsertSql();
+
+		$qb->setValue('actor_id', $qb->createNamedParameter($this->uuid()));
+		$qb->setValue('stream_id', $qb->createNamedParameter($this->uuid()));
+		$qb->setValue('type', $qb->createNamedParameter('unk'));
+
+		$qb->execute();
+	}
+
+}
+
diff --git a/lib/Db/StreamDestRequestBuilder.php b/lib/Db/StreamDestRequestBuilder.php
new file mode 100644
index 00000000..b35b7a5a
--- /dev/null
+++ b/lib/Db/StreamDestRequestBuilder.php
@@ -0,0 +1,122 @@
+<?php
+declare(strict_types=1);
+
+
+/**
+ * Nextcloud - Social Support
+ *
+ * This file is licensed under the Affero General Public License version 3 or
+ * later. See the COPYING file.
+ *
+ * @author Maxence Lange <maxence@artificial-owl.com>
+ * @copyright 2018, Maxence Lange <maxence@artificial-owl.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\Social\Db;
+
+
+use daita\MySmallPhpTools\Traits\TArrayTools;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+
+
+/**
+ * Class StreamDestRequestBuilder
+ *
+ * @package OCA\Social\Db
+ */
+class StreamDestRequestBuilder extends CoreRequestBuilder {
+
+
+	use TArrayTools;
+
+
+	/**
+	 * Base of the Sql Insert request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getStreamDestInsertSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_STREAM_DEST);
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Update request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getStreamDestUpdateSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->update(self::TABLE_STREAM_DEST);
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Select request for Shares
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getStreamDestSelectSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+
+		/** @noinspection PhpMethodParametersCountMismatchInspection */
+		$qb->select('sd.actor_id', 'sd.stream_id', 'sd.type')
+		   ->from(self::TABLE_STREAM_DEST, 'sd');
+
+		$this->defaultSelectAlias = 'sd';
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Delete request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getStreamDestDeleteSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->delete(self::TABLE_STREAM_DEST);
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Select request for Shares
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function countStreamDestSelectSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->selectAlias($qb->createFunction('COUNT(*)'), 'count')
+		   ->from(self::TABLE_STREAM_DEST, 'sd');
+
+		$this->defaultSelectAlias = 'sd';
+
+		return $qb;
+	}
+
+
+}
+
diff --git a/lib/Db/StreamRequest.php b/lib/Db/StreamRequest.php
index 71da370b..20ca012e 100644
--- a/lib/Db/StreamRequest.php
+++ b/lib/Db/StreamRequest.php
@@ -61,17 +61,25 @@ use OCP\IDBConnection;
 class StreamRequest extends StreamRequestBuilder {
 
 
+	/** @var StreamDestRequest */
+	private $streamDestRequest;
+
+
 	/**
 	 * StreamRequest constructor.
 	 *
 	 * @param IDBConnection $connection
+	 * @param StreamDestRequest $streamDestRequest
 	 * @param ConfigService $configService
 	 * @param MiscService $miscService
 	 */
 	public function __construct(
-		IDBConnection $connection, ConfigService $configService, MiscService $miscService
+		IDBConnection $connection, StreamDestRequest $streamDestRequest, ConfigService $configService,
+		MiscService $miscService
 	) {
 		parent::__construct($connection, $configService, $miscService);
+
+		$this->streamDestRequest = $streamDestRequest;
 	}
 
 
@@ -82,9 +90,7 @@ class StreamRequest extends StreamRequestBuilder {
 		$qb = $this->saveStream($stream);
 		if ($stream->getType() === Note::TYPE) {
 			/** @var Note $stream */
-			$qb->setValue(
-				'hashtags', $qb->createNamedParameter(json_encode($stream->getHashtags()))
-			)
+			$qb->setValue('hashtags', $qb->createNamedParameter(json_encode($stream->getHashtags())))
 			   ->setValue(
 				   'attachments', $qb->createNamedParameter(
 				   json_encode($stream->getAttachments(), JSON_UNESCAPED_SLASHES)
@@ -94,6 +100,8 @@ class StreamRequest extends StreamRequestBuilder {
 
 		try {
 			$qb->execute();
+
+			$this->streamDestRequest->generateStreamDest($stream);
 		} catch (UniqueConstraintViolationException $e) {
 		}
 	}
@@ -188,6 +196,7 @@ class StreamRequest extends StreamRequestBuilder {
 	public function updateAttributedTo(string $itemId, string $to) {
 		$qb = $this->getStreamUpdateSql();
 		$qb->set('attributed_to', $qb->createNamedParameter($to));
+		$qb->set('attributed_to_prim', $qb->createNamedParameter($this->prim($to)));
 
 		$this->limitToIdString($qb, $itemId);
 
@@ -357,14 +366,17 @@ class StreamRequest extends StreamRequestBuilder {
 	 */
 	public function getTimelineHome(Person $actor, int $since = 0, int $limit = 5): array {
 		$qb = $this->getStreamSelectSql();
+		$expr = $qb->expr();
 
-		$this->leftJoinFollowing($qb, $actor);
-		$this->limitToFollowing($qb, $actor);
+		$this->selectCacheActors($qb, 'ca');
+		$this->selectDestFollowing($qb, 'sd', 'f');
 		$this->limitPaginate($qb, $since, $limit);
 
-		$this->leftJoinCacheActors($qb, 'object_id', $actor, 'f');
-		$this->leftJoinStreamAction($qb);
+		$qb->andWhere($this->exprLimitToDBField($qb, 'type', SocialAppNotification::TYPE, false));
+		$qb->andWhere($this->exprInnerJoinDestFollowing($qb, $actor, 'id_prim', 'sd', 'f'));
+		$qb->andWhere($expr->eq('f.object_id_prim', 'ca.id_prim'));
 
+		$this->leftJoinStreamAction($qb);
 		$this->filterDuplicate($qb);
 
 		$streams = [];
@@ -713,10 +725,12 @@ class StreamRequest extends StreamRequestBuilder {
 		   ->setValue('summary', $qb->createNamedParameter($stream->getSummary()))
 		   ->setValue('published', $qb->createNamedParameter($stream->getPublished()))
 		   ->setValue('attributed_to', $qb->createNamedParameter($attributedTo))
+		   ->setValue('attributed_to_prim', $qb->createNamedParameter($this->prim($attributedTo)))
 		   ->setValue('in_reply_to', $qb->createNamedParameter($stream->getInReplyTo()))
 		   ->setValue('source', $qb->createNamedParameter($stream->getSource()))
 		   ->setValue('activity_id', $qb->createNamedParameter($stream->getActivityId()))
 		   ->setValue('object_id', $qb->createNamedParameter($stream->getObjectId()))
+		   ->setValue('object_id_prim', $qb->createNamedParameter($this->prim($stream->getObjectId())))
 		   ->setValue('details', $qb->createNamedParameter(json_encode($stream->getDetailsAll())))
 		   ->setValue('cache', $qb->createNamedParameter($cache))
 		   ->setValue(
@@ -759,13 +773,14 @@ class StreamRequest extends StreamRequestBuilder {
 		}
 
 		$expr = $qb->expr();
-		$func = $qb->func();
 		$pf = $this->defaultSelectAlias . '.';
 
 		$on = $expr->andX();
-		$on->add($this->exprLimitToDBField($qb, 'actor_id', $actor->getId(), true, false, 'fs'));
-		$on->add($expr->eq($func->lower($pf . 'attributed_to'), $func->lower('fs.object_id')));
 		$on->add($this->exprLimitToDBFieldInt($qb, 'accepted', 1, 'fs'));
+		$on->add(
+			$this->exprLimitToDBField($qb, 'actor_id_prim', $this->prim($actor->getId()), true, true, 'fs')