Merge branch 'master' into feature/noid/attachments

12 files changed, 916 insertions, 105 deletions

diff --git a/lib/Db/CacheActorsRequest.php b/lib/Db/CacheActorsRequest.php
index 82a2f5de..cabb1008 100644
--- a/lib/Db/CacheActorsRequest.php
+++ b/lib/Db/CacheActorsRequest.php
@@ -114,8 +114,10 @@ class CacheActorsRequest extends CacheActorsRequestBuilder {
 	 * insert cache about an Actor in database.
 	 *
 	 * @param Person $actor
+	 *
+	 * @return int
 	 */
-	public function update(Person $actor) {
+	public function update(Person $actor): int {
 
 		if ($actor->getCreation() > 0) {
 			$dTime = new DateTime();
@@ -155,7 +157,8 @@ class CacheActorsRequest extends CacheActorsRequestBuilder {
 		$qb->set('icon_id', $qb->createNamedParameter($iconId));
 
 		$this->limitToIdString($qb, $actor->getId());
-		$qb->execute();
+
+		return $qb->execute();
 	}
 
 
@@ -247,6 +250,7 @@ class CacheActorsRequest extends CacheActorsRequestBuilder {
 		$this->searchInAccount($qb, $search);
 		$this->leftJoinCacheDocuments($qb, 'icon_id');
 		$this->leftJoinDetails($qb);
+		$this->limitResults($qb, 25);
 
 		$accounts = [];
 		$cursor = $qb->execute();
diff --git a/lib/Db/CoreRequestBuilder.php b/lib/Db/CoreRequestBuilder.php
index 66b5325e..d5f1f8f3 100644
--- a/lib/Db/CoreRequestBuilder.php
+++ b/lib/Db/CoreRequestBuilder.php
@@ -37,7 +37,7 @@ use Doctrine\DBAL\Query\QueryBuilder;
 use Exception;
 use OCA\Social\Exceptions\InvalidResourceException;
 use OCA\Social\Model\ActivityPub\Object\Document;
-use OCA\Social\Model\ActivityPub\Activity\Follow;
+use OCA\Social\Model\ActivityPub\Object\Follow;
 use OCA\Social\Model\ActivityPub\Object\Image;
 use OCA\Social\Model\ActivityPub\Actor\Person;
 use OCA\Social\Service\ConfigService;
@@ -58,12 +58,15 @@ class CoreRequestBuilder {
 
 	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';
 
+  
 	/** @var IDBConnection */
 	protected $dbConnection;
 
@@ -77,8 +80,8 @@ class CoreRequestBuilder {
 	/** @var string */
 	protected $defaultSelectAlias;
 
-	/** @var string */
-	private $viewerId = '';
+	/** @var Person */
+	protected $viewer = null;
 
 
 	/**
@@ -98,17 +101,10 @@ class CoreRequestBuilder {
 
 
 	/**
-	 * @return string
-	 */
-	public function getViewerId(): string {
-		return $this->viewerId;
-	}
-
-	/**
-	 * @param string $viewerId
+	 * @param Person $viewer
 	 */
-	public function setViewerId(string $viewerId) {
-		$this->viewerId = $viewerId;
+	public function setViewer(Person $viewer) {
+		$this->viewer = $viewer;
 	}
 
 
@@ -146,6 +142,17 @@ class CoreRequestBuilder {
 
 
 	/**
+	 * Limit the request to the ActivityId
+	 *
+	 * @param IQueryBuilder $qb
+	 * @param string $activityId
+	 */
+	protected function limitToActivityId(IQueryBuilder &$qb, string $activityId) {
+		$this->limitToDBField($qb, 'activity_id', $activityId, false);
+	}
+
+
+	/**
 	 * Limit the request to the Preferred Username
 	 *
 	 * @param IQueryBuilder $qb
@@ -189,6 +196,39 @@ class CoreRequestBuilder {
 		$this->limitToDBField($qb, 'token', $token);
 	}
 
+	/**
+	 * Limit the results to a given number
+	 *
+	 * @param IQueryBuilder $qb
+	 * @param int $limit
+	 */
+	protected function limitResults(IQueryBuilder $qb, int $limit) {
+		$qb->setMaxResults($limit);
+	}
+
+
+	/**
+	 * Limit the request to the ActorId
+	 *
+	 * @param IQueryBuilder $qb
+	 * @param string $hashtag
+	 */
+	protected function limitToHashtag(IQueryBuilder &$qb, string $hashtag) {
+		$this->limitToDBField($qb, 'hashtag', $hashtag, false);
+	}
+
+
+	/**
+	 * Limit the request to the ActorId
+	 *
+	 * @param IQueryBuilder $qb
+	 * @param string $hashtag
+	 */
+	protected function searchInHashtag(IQueryBuilder &$qb, string $hashtag) {
+		$dbConn = $this->dbConnection;
+		$this->searchInDBField($qb, 'hashtag', '%' . $dbConn->escapeLikeParameter($hashtag) . '%');
+	}
+
 
 	/**
 	 * Limit the request to the ActorId
@@ -496,6 +536,27 @@ class CoreRequestBuilder {
 
 	/**
 	 * @param IQueryBuilder $qb
+	 * @param int $timestamp
+	 * @param string $field
+	 */
+	protected function limitToSince(IQueryBuilder $qb, int $timestamp, string $field) {
+		$dTime = new \DateTime();
+		$dTime->setTimestamp($timestamp);
+
+		$expr = $qb->expr();
+		$pf = ($qb->getType() === QueryBuilder::SELECT) ? $this->defaultSelectAlias . '.' : '';
+		$field = $pf . $field;
+
+		$orX = $expr->orX();
+		$orX->add($expr->gte($field, $qb->createNamedParameter($dTime, IQueryBuilder::PARAM_DATE)));
+
+		$qb->andWhere($orX);
+
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
 	 * @param string $field
 	 * @param array $values
 	 */
@@ -666,8 +727,7 @@ class CoreRequestBuilder {
 			return;
 		}
 
-		$viewerId = $this->getViewerId();
-		if ($viewerId === '') {
+		if ($this->viewer === null) {
 			return;
 		}
 
@@ -688,7 +748,7 @@ class CoreRequestBuilder {
 			$andX->add(
 				$expr->eq(
 					$func->lower($prefix . '_f.actor_id'),
-					$func->lower($qb->createNamedParameter($viewerId))
+					$func->lower($qb->createNamedParameter($this->viewer->getId()))
 				)
 			);
 		} else {
@@ -700,7 +760,7 @@ class CoreRequestBuilder {
 			$andX->add(
 				$expr->eq(
 					$func->lower($prefix . '_f.object_id'),
-					$func->lower($qb->createNamedParameter($viewerId))
+					$func->lower($qb->createNamedParameter($this->viewer->getId()))
 				)
 			);
 		}
@@ -764,24 +824,25 @@ class CoreRequestBuilder {
 	 * @param array $data
 	 */
 	protected function assignDetails(Person $actor, array $data) {
-		if ($this->getViewerId() !== '') {
-
-			try {
-				$this->parseFollowLeftJoin($data, 'as_follower');
-				$actor->addDetailBool('following', true);
-			} catch (InvalidResourceException $e) {
-				$actor->addDetailBool('following', false);
-			}
+		if ($this->viewer === null) {
+			return;
+		}
 
-			try {
-				$this->parseFollowLeftJoin($data, 'as_followed');
-				$actor->addDetailBool('followed', true);
-			} catch (InvalidResourceException $e) {
-				$actor->addDetailBool('followed', false);
-			}
+		try {
+			$this->parseFollowLeftJoin($data, 'as_follower');
+			$actor->addDetailBool('following', true);
+		} catch (InvalidResourceException $e) {
+			$actor->addDetailBool('following', false);
+		}
 
-			$actor->setCompleteDetails(true);
+		try {
+			$this->parseFollowLeftJoin($data, 'as_followed');
+			$actor->addDetailBool('followed', true);
+		} catch (InvalidResourceException $e) {
+			$actor->addDetailBool('followed', false);
 		}
+
+		$actor->setCompleteDetails(true);
 	}
 
 
diff --git a/lib/Db/FollowsRequest.php b/lib/Db/FollowsRequest.php
index 7cec6859..a22375d9 100644
--- a/lib/Db/FollowsRequest.php
+++ b/lib/Db/FollowsRequest.php
@@ -34,7 +34,7 @@ namespace OCA\Social\Db;
 use daita\MySmallPhpTools\Traits\TArrayTools;
 use DateTime;
 use OCA\Social\Exceptions\FollowDoesNotExistException;
-use OCA\Social\Model\ActivityPub\Activity\Follow;
+use OCA\Social\Model\ActivityPub\Object\Follow;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 
 
diff --git a/lib/Db/FollowsRequestBuilder.php b/lib/Db/FollowsRequestBuilder.php
index 6fb323a6..ab2729fb 100644
--- a/lib/Db/FollowsRequestBuilder.php
+++ b/lib/Db/FollowsRequestBuilder.php
@@ -33,7 +33,7 @@ namespace OCA\Social\Db;
 
 use daita\MySmallPhpTools\Traits\TArrayTools;
 use OCA\Social\Exceptions\InvalidResourceException;
-use OCA\Social\Model\ActivityPub\Activity\Follow;
+use OCA\Social\Model\ActivityPub\Object\Follow;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 
 
diff --git a/lib/Db/HashtagsRequest.php b/lib/Db/HashtagsRequest.php
new file mode 100644
index 00000000..7cf9c167
--- /dev/null
+++ b/lib/Db/HashtagsRequest.php
@@ -0,0 +1,145 @@
+<?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 DateTime;
+use OCA\Social\Exceptions\FollowDoesNotExistException;
+use OCA\Social\Exceptions\HashtagDoesNotExistException;
+use OCA\Social\Model\ActivityPub\Activity\Follow;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+
+
+/**
+ * Class HashtagsRequest
+ *
+ * @package OCA\Social\Db
+ */
+class HashtagsRequest extends HashtagsRequestBuilder {
+
+
+	use TArrayTools;
+
+
+	/**
+	 * Insert a new Hashtag.
+	 *
+	 * @param string $hashtag
+	 * @param array $trend
+	 */
+	public function save(string $hashtag, array $trend) {
+		$qb = $this->getHashtagsInsertSql();
+		$qb->setValue('hashtag', $qb->createNamedParameter($hashtag))
+		   ->setValue('trend', $qb->createNamedParameter(json_encode($trend)));
+
+		$qb->execute();
+	}
+
+
+	/**
+	 * Insert a new Hashtag.
+	 *
+	 * @param string $hashtag
+	 * @param array $trend
+	 */
+	public function update(string $hashtag, array $trend) {
+		$qb = $this->getHashtagsUpdateSql();
+		$qb->set('trend', $qb->createNamedParameter(json_encode($trend)));
+		$this->limitToHashtag($qb, $hashtag);
+
+		$qb->execute();
+	}
+
+
+	/**
+	 * @return array
+	 */
+	public function getAll(): array {
+		$qb = $this->getHashtagsSelectSql();
+
+		$hashtags = [];
+		$cursor = $qb->execute();
+		while ($data = $cursor->fetch()) {
+			$hashtags[] = $this->parseHashtagsSelectSql($data);
+		}
+		$cursor->closeCursor();
+
+		return $hashtags;
+	}
+
+
+	/**
+	 * @param string $hashtag
+	 *
+	 * @return array
+	 * @throws HashtagDoesNotExistException
+	 */
+	public function getHashtag(string $hashtag): array {
+		$qb = $this->getHashtagsSelectSql();
+
+		$this->limitToHashtag($qb, $hashtag);
+
+		$cursor = $qb->execute();
+		$data = $cursor->fetch();
+		$cursor->closeCursor();
+
+		if ($data === false) {
+			throw new HashtagDoesNotExistException();
+		}
+
+		return $this->parseHashtagsSelectSql($data);
+	}
+
+
+	/**
+	 * @param string $hashtag
+	 *
+	 * @return array
+	 */
+	public function searchHashtags(string $hashtag): array {
+		$qb = $this->getHashtagsSelectSql();
+		$this->searchInHashtag($qb, $hashtag);
+		$this->limitResults($qb, 25);
+
+		$hashtags = [];
+		$cursor = $qb->execute();
+		while ($data = $cursor->fetch()) {
+			$hashtags[] = $this->parseHashtagsSelectSql($data);
+		}
+		$cursor->closeCursor();
+
+		return $hashtags;
+	}
+
+
+}
+
diff --git a/lib/Db/HashtagsRequestBuilder.php b/lib/Db/HashtagsRequestBuilder.php
new file mode 100644
index 00000000..b3f1b946
--- /dev/null
+++ b/lib/Db/HashtagsRequestBuilder.php
@@ -0,0 +1,121 @@
+<?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 OCA\Social\Exceptions\InvalidResourceException;
+use OCA\Social\Model\ActivityPub\Activity\Follow;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+
+
+/**
+ * Class HashtagsRequestBuilder
+ *
+ * @package OCA\Social\Db
+ */
+class HashtagsRequestBuilder extends CoreRequestBuilder {
+
+
+	use TArrayTools;
+
+
+	/**
+	 * Base of the Sql Insert request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getHashtagsInsertSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_SERVER_HASHTAGS);
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Update request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getHashtagsUpdateSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->update(self::TABLE_SERVER_HASHTAGS);
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Select request for Shares
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getHashtagsSelectSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+
+		/** @noinspection PhpMethodParametersCountMismatchInspection */
+		$qb->select('h.hashtag', 'h.trend')
+		   ->from(self::TABLE_SERVER_HASHTAGS, 'h');
+
+		$this->defaultSelectAlias = 'h';
+
+		return $qb;
+	}
+
+
+	/**
+	 * Base of the Sql Delete request
+	 *
+	 * @return IQueryBuilder
+	 */
+	protected function getHashtagsDeleteSql(): IQueryBuilder {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->delete(self::TABLE_SERVER_HASHTAGS);
+
+		return $qb;
+	}
+
+
+	/**
+	 * @param array $data
+	 *
+	 * @return array
+	 */
+	protected function parseHashtagsSelectSql($data): array {
+		return [
+			'hashtag' => $this->get('hashtag', $data, ''),
+			'trend'   => $this->getArray('trend', $data, [])
+		];
+	}
+
+}
+
diff --git a/lib/Db/NotesRequest.php b/lib/Db/NotesRequest.php
index 4d8221c8..35d99c3f 100644
--- a/lib/Db/NotesRequest.php
+++ b/lib/Db/NotesRequest.php
@@ -30,12 +30,14 @@ declare(strict_types=1);
 namespace OCA\Social\Db;
 
 
+use daita\MySmallPhpTools\Model\Cache;
 use DateTime;
 use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
 use OCA\Social\Exceptions\NoteNotFoundException;
 use OCA\Social\Model\ActivityPub\ACore;
 use OCA\Social\Model\ActivityPub\Actor\Person;
 use OCA\Social\Model\ActivityPub\Object\Note;
+use OCA\Social\Model\ActivityPub\Stream;
 use OCA\Social\Service\ConfigService;
 use OCA\Social\Service\MiscService;
 use OCP\DB\QueryBuilder\IQueryBuilder;
@@ -59,57 +61,36 @@ class NotesRequest extends NotesRequestBuilder {
 
 
 	/**
-	 * Insert a new Note in the database.
-	 *
-	 * @param Note $note
+	 * @param Stream $stream
 	 */
-	public function save(Note $note) {
-		$dTime = new DateTime();
-		$dTime->setTimestamp($note->getPublishedTime());
-
-		$qb = $this->getNotesInsertSql();
-		$qb->setValue('id', $qb->createNamedParameter($note->getId()))
-		   ->setValue('type', $qb->createNamedParameter($note->getType()))
-		   ->setValue('to', $qb->createNamedParameter($note->getTo()))
-		   ->setValue(
-			   'to_array', $qb->createNamedParameter(
-			   json_encode($note->getToArray(), JSON_UNESCAPED_SLASHES)
-		   )
-		   )
-		   ->setValue(
-			   'cc', $qb->createNamedParameter(
-			   json_encode($note->getCcArray(), JSON_UNESCAPED_SLASHES)
-		   )
-		   )
-		   ->setValue(
-			   'bcc', $qb->createNamedParameter(
-			   json_encode($note->getBccArray()), JSON_UNESCAPED_SLASHES
-		   )
-		   )
-		   ->setValue('content', $qb->createNamedParameter($note->getContent()))
-		   ->setValue('summary', $qb->createNamedParameter($note->getSummary()))
-		   ->setValue(
+	public function save(Stream $stream) {
+		$qb = $this->saveStream($stream);
+
+		if ($stream->getType() === Note::TYPE) {
+			/** @var Note $stream */
+			$qb->setValue(
+				'hashtags', $qb->createNamedParameter(json_encode($stream->getHashtags()))
+			)
+         ->setValue(
 			   'attachments', $qb->createNamedParameter(
 			   json_encode($note->getAttachments(), JSON_UNESCAPED_SLASHES)
 		   )
-		   )
-		   ->setValue('published', $qb->createNamedParameter($note->getPublished()))
-		   ->setValue(
-			   'published_time', $qb->createNamedParameter($dTime, IQueryBuilder::PARAM_DATE)
-		   )
-		   ->setValue('attributed_to', $qb->createNamedParameter($note->getAttributedTo()))
-		   ->setValue('in_reply_to', $qb->createNamedParameter($note->getInReplyTo()))
-		   ->setValue('source', $qb->createNamedParameter($note->getSource()))
-		   ->setValue(
-			   'instances', $qb->createNamedParameter(
-			   json_encode($note->getInstancePaths(), JSON_UNESCAPED_SLASHES)
-		   )
-		   )
-		   ->setValue('local', $qb->createNamedParameter(($note->isLocal()) ? '1' : '0'))
-		   ->setValue(
-			   'creation',
-			   $qb->createNamedParameter(new DateTime('now'), IQueryBuilder::PARAM_DATE)
 		   );
+		}
+
+		$qb->execute();
+	}
+
+
+	/**
+	 * @param Stream $stream
+	 * @param Cache $cache
+	 */
+	public function updateCache(Stream $stream, Cache $cache) {
+		$qb = $this->getNotesUpdateSql();
+		$qb->set('cache', $qb->createNamedParameter(json_encode($cache, JSON_UNESCAPED_SLASHES)));
+
+		$this->limitToIdString($qb, $stream->getId());
 
 		try {
 			$qb->execute();
@@ -120,11 +101,12 @@ class NotesRequest extends NotesRequestBuilder {
 
 	/**
 	 * @param string $id
+	 * @param bool $asViewer
 	 *
 	 * @return Note
 	 * @throws NoteNotFoundException
 	 */
-	public function getNoteById(string $id): Note {
+	public function getNoteById(string $id, bool $asViewer = false): Note {
 		if ($id === '') {
 			throw new NoteNotFoundException();
 		};
@@ -132,6 +114,36 @@ class NotesRequest extends NotesRequestBuilder {
 		$qb = $this->getNotesSelectSql();
 		$this->limitToIdString($qb, $id);
 
+		if ($asViewer) {
+			$this->limitToViewer($qb);
+		}
+
+		$cursor = $qb->execute();
+		$data = $cursor->fetch();
+		$cursor->closeCursor();
+
+		if ($data === false) {
+			throw new NoteNotFoundException('Post not found');
+		}
+
+		return $this->parseNotesSelectSql($data);
+	}
+
+
+	/**
+	 * @param string $id
+	 *
+	 * @return Note
+	 * @throws NoteNotFoundException
+	 */
+	public function getNoteByActivityId(string $id): Note {
+		if ($id === '') {
+			throw new NoteNotFoundException();
+		};
+
+		$qb = $this->getNotesSelectSql();
+		$this->limitToActivityId($qb, $id);
+
 		$cursor = $qb->execute();
 		$data = $cursor->fetch();
 		$cursor->closeCursor();
@@ -316,6 +328,64 @@ class NotesRequest extends NotesRequestBuilder {
 
 
 	/**
+	 * Should returns:
+	 *  - All public post related to a tag (not yet)
+	 *  - direct message related to a tag (not yet)
+	 *  - message to followers related to a tag (not yet)
+	 *
+	 * @param Person $actor
+	 * @param string $hashtag
+	 * @param int $since
+	 * @param int $limit
+	 *
+	 * @return array
+	 */
+	public function getStreamTag(Person $actor, string $hashtag, int $since = 0, int $limit = 5
+	): array {
+		$qb = $this->getNotesSelectSql();
+
+		$on = $this->exprJoinFollowing($qb, $actor);
+		$on->add($this->exprLimitToRecipient($qb, ACore::CONTEXT_PUBLIC, false));
+		$on->add($this->exprLimitToRecipient($qb, $actor->getId(), true));
+		$qb->join($this->defaultSelectAlias, CoreRequestBuilder::TABLE_SERVER_FOLLOWS, 'f', $on);
+
+		$qb->andWhere($this->exprValueWithinJsonFormat($qb, 'hashtags', '' . $hashtag));
+
+		$this->limitPaginate($qb, $since, $limit);
+		$this->leftJoinCacheActors($qb, 'attributed_to');
+
+		$notes = [];
+		$cursor = $qb->execute();
+		while ($data = $cursor->fetch()) {
+			$notes[] = $this->parseNotesSelectSql($data);
+		}
+		$cursor->closeCursor();
+
+		return $notes;
+	}
+
+
+	/**
+	 * @param int $since
+	 *
+	 * @return Note[]
+	 */
+	public function getNotesSince(int $since): array {
+		$qb = $this->getNotesSelectSql();
+		$this->limitToSince($qb, $since, 'published_time');
+
+		$notes = [];
+		$cursor = $qb->execute();
+		while ($data = $cursor->fetch()) {
+			$notes[] = $this->parseNotesSelectSql($data);
+		}
+		$cursor->closeCursor();
+
+		return $notes;
+	}
+
+
+	/**
 	 * @param string $id
 	 */
 	public function deleteNoteById(string $id) {
@@ -325,5 +395,77 @@ class NotesRequest extends NotesRequestBuilder {
 		$qb->execute();
 	}
 
+
+	/**
+	 * @param string $actorId
+	 */
+	public function deleteByAuthor(string $actorId) {
+		$qb = $this->getNotesDeleteSql();
+		$this->limitToAttributedTo($qb, $actorId);
+
+		$qb->execute();
+	}
+
+
+	/**
+	 * Insert a new Note in the database.
+	 *
+	 * @param Stream $note
+	 *
+	 * @return IQueryBuilder
+	 */
+	public function saveStream(Stream $note): IQueryBuilder {
+		$dTime = new DateTime();
+		$dTime->setTimestamp($note->getPublishedTime());
+
+		$cache = '[]';
+		if ($note->gotCache()) {
+			$cache = json_encode($note->getCache(), JSON_UNESCAPED_SLASHES);
+		}
+
+		$qb = $this->getNotesInsertSql();
+		$qb->setValue('id', $qb->createNamedParameter($note->getId()))
+		   ->setValue('type', $qb->createNamedParameter($note->getType()))
+		   ->setValue('to', $qb->createNamedParameter($note->getTo()))
+		   ->setValue(
+			   'to_array', $qb->createNamedParameter(
+			   json_encode($note->getToArray(), JSON_UNESCAPED_SLASHES)
+		   )
+		   )
+		   ->setValue(
+			   'cc', $qb->createNamedParameter(
+			   json_encode($note->getCcArray(), JSON_UNESCAPED_SLASHES)
+		   )
+		   )
+		   ->setValue(
+			   'bcc', $qb->createNamedParameter(
+			   json_encode($note->getBccArray()), JSON_UNESCAPED_SLASHES
+		   )
+		   )
+		   ->setValue('content', $qb->createNamedParameter($note->getContent()))
+		   ->setValue('summary', $qb->createNamedParameter($note->getSummary()))
+		   ->setValue('published', $qb->createNamedParameter($note->getPublished()))
+		   ->setValue(
+			   'published_time', $qb->createNamedParameter($dTime, IQueryBuilder::PARAM_DATE)
+		   )
+		   ->setValue('attributed_to', $qb->createNamedParameter($note->getAttributedTo()))
+		   ->setValue('in_reply_to', $qb->createNamedParameter($note->getInReplyTo()))
+		   ->setValue('source', $qb->createNamedParameter($note->getSource()))
+		   ->setValue('object_id', $qb->createNamedParameter($note->getObjectId()))
+		   ->setValue('cache', $qb->createNamedParameter($cache))
+		   ->setValue(
+			   'instances', $qb->createNamedParameter(
+			   json_encode($note->getInstancePaths(), JSON_UNESCAPED_SLASHES)
+		   )
+		   )
+		   ->setValue('local', $qb->createNamedParameter(($note->isLocal()) ? '1' : '0'))
+		   ->setValue(
+			   'creation',
+			   $qb->createNamedParameter(new DateTime('now'), IQueryBuilder::PARAM_DATE)
+		   );
+
+		return $qb;
+	}
+
 }
 
diff --git a/lib/Db/NotesRequestBuilder.php b/lib/Db/NotesRequestBuilder.php
index dccdb1e6..011c478a 100644
--- a/lib/Db/NotesRequestBuilder.php
+++ b/lib/Db/NotesRequestBuilder.php
@@ -33,6 +33,7 @@ namespace OCA\Social\Db;
 use daita\MySmallPhpTools\Traits\TArrayTools;
 use Doctrine\DBAL\Query\QueryBuilder;
 use OCA\Social\Exceptions\InvalidResourceException;
+use OCA\Social\Model\ActivityPub\ACore;
 use OCA\Social\Model\ActivityPub\Actor\Person;
 use OCA\Social\Model\ActivityPub\Object\Note;
 use OCA\Social\Model\InstancePath;
@@ -80,11 +81,13 @@ class NotesRequestBuilder extends CoreRequestBuilder {
 		$qb = $this->dbConnection->getQueryBuilder();
 
 		/** @noinspection PhpMethodParametersCountMismatchInspection */
-		$qb->select(
-			'sn.id', 'sn.type', 'sn.to', 'sn.to_array', 'sn.cc', 'sn.bcc', 'sn.content',
-			'sn.summary', 'sn.attachments', 'sn.published', 'sn.published_time', 'sn.attributed_to',
-			'sn.in_reply_to', 'sn.source', 'sn.local', 'sn.instances', 'sn.creation'
-		)
+		$qb->selectDistinct('sn.id')
+		   ->addSelect(
+			   'sn.type', 'sn.to', 'sn.to_array', 'sn.cc', 'sn.bcc', 'sn.content',
+			   'sn.summary', 'sn.attachments', 'sn.published', 'sn.published_time', 'sn.cache', 'sn.object_id',
+			   'sn.attributed_to', 'sn.in_reply_to', 'sn.source', 'sn.local', 'sn.instances',
+			   'sn.creation'
+		   )
 		   ->from(self::TABLE_SERVER_NOTES, 'sn');
 
 		$this->defaultSelectAlias = 'sn';
@@ -124,6 +127,19 @@ class NotesRequestBuilder extends CoreRequestBuilder {
 
 	/**
 	 * @param IQueryBuilder $qb
+	 */
+	protected function limitToViewer(IQueryBuilder $qb) {
+		$actor = $this->viewer;
+
+		$on = $this->exprJoinFollowing($qb, $actor, false);
+		$on->add($this->exprLimitToRecipient($qb, ACore::CONTEXT_PUBLIC, false));
+		$on->add($this->exprLimitToRecipient($qb, $actor->getId(), true));
+		$qb->join($this->defaultSelectAlias, CoreRequestBuilder::TABLE_SERVER_FOLLOWS, 'f', $on);
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
 	 * @param Person $actor
 	 */
 	protected function joinFollowing(IQueryBuilder $qb, Person $actor) {
@@ -131,12 +147,29 @@ class NotesRequestBuilder extends CoreRequestBuilder {
 			return;
 		}
 
+		$on = $this->exprJoinFollowing($qb, $actor);
+
+		$qb->join($this->defaultSelectAlias, CoreRequestBuilder::TABLE_SERVER_FOLLOWS, 'f', $on);
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param Person