Merge pull request #143 from nextcloud/support-sqlite

replace rightJoin -> join

7 files changed, 176 insertions, 101 deletions

diff --git a/lib/Controller/LocalController.php b/lib/Controller/LocalController.php
index 8ba1c6f7..a99782d9 100644
--- a/lib/Controller/LocalController.php
+++ b/lib/Controller/LocalController.php
@@ -208,7 +208,7 @@ class LocalController extends Controller {
 	public function streamHome($since = 0, int $limit = 5): DataResponse {
 		try {
 			$this->initViewer(true);
-			$posts = $this->noteService->getStreamHome($this->viewer->getId(), $since, $limit);
+			$posts = $this->noteService->getStreamHome($this->viewer, $since, $limit);
 
 			return $this->success($posts);
 		} catch (Exception $e) {
@@ -259,7 +259,7 @@ class LocalController extends Controller {
 	public function streamDirect(int $since = 0, int $limit = 5): DataResponse {
 		try {
 			$this->initViewer();
-			$posts = $this->noteService->getStreamDirect($this->viewer->getId(), $since, $limit);
+			$posts = $this->noteService->getStreamDirect($this->viewer, $since, $limit);
 
 			return $this->success($posts);
 		} catch (Exception $e) {
diff --git a/lib/Cron/Queue.php b/lib/Cron/Queue.php
index e63f8383..44438df8 100644
--- a/lib/Cron/Queue.php
+++ b/lib/Cron/Queue.php
@@ -85,7 +85,7 @@ class Queue extends TimedJob {
 
 
 	private function manageQueue() {
-		$requests = $this->queueService->getRequestStandby($total = 0);
+		$requests = $this->queueService->getRequestStandby();
 		$this->activityService->manageInit();
 
 		foreach ($requests as $request) {
diff --git a/lib/Db/CoreRequestBuilder.php b/lib/Db/CoreRequestBuilder.php
index e35de17a..7176ed8c 100644
--- a/lib/Db/CoreRequestBuilder.php
+++ b/lib/Db/CoreRequestBuilder.php
@@ -348,52 +348,6 @@ class CoreRequestBuilder {
 
 	/**
 	 * @param IQueryBuilder $qb
-	 * @param string $recipient
-	 * @param bool $asAuthor
-	 */
-	protected function limitToRecipient(
-		IQueryBuilder &$qb, string $recipient, bool $asAuthor = false
-	) {
-		$expr = $qb->expr();
-		$orX = $expr->orX();
-		$dbConn = $this->dbConnection;
-
-		if ($asAuthor === true) {
-			$func = $qb->func();
-			$orX->add(
-				$expr->eq(
-					$func->lower('attributed_to'),
-					$func->lower($qb->createNamedParameter($recipient))
-				)
-			);
-		}
-
-		$orX->add($expr->eq('to', $qb->createNamedParameter($recipient)));
-		$orX->add(
-			$expr->like(
-				'to_array',
-				$qb->createNamedParameter('%"' . $dbConn->escapeLikeParameter($recipient) . '"%')
-			)
-		);
-		$orX->add(
-			$expr->like(
-				'cc',
-				$qb->createNamedParameter('%"' . $dbConn->escapeLikeParameter($recipient) . '"%')
-			)
-		);
-		$orX->add(
-			$expr->like(
-				'bcc',
-				$qb->createNamedParameter('%"' . $dbConn->escapeLikeParameter($recipient) . '"%')
-			)
-		);
-
-		$qb->andWhere($orX);
-	}
-
-
-	/**
-	 * @param IQueryBuilder $qb
 	 * @param int $since
 	 * @param int $limit
 	 */
@@ -405,9 +359,8 @@ class CoreRequestBuilder {
 		}
 
 		$qb->setMaxResults($limit);
-
 		$pf = $this->defaultSelectAlias;
-		$qb->orderBy($pf . '.creation', 'desc');
+		$qb->orderBy($pf . '.published_time', 'desc');
 	}
 
 
@@ -522,6 +475,7 @@ class CoreRequestBuilder {
 
 		$orX = $expr->orX();
 		$orX->add($expr->lte($field, $qb->createNamedParameter($date, IQueryBuilder::PARAM_DATE)));
+
 		if ($orNull === true) {
 			$orX->add($expr->isNull($field));
 		}
@@ -818,7 +772,6 @@ class CoreRequestBuilder {
 			$actor->setCompleteDetails(true);
 		}
 	}
-}
-
 
+}
 
diff --git a/lib/Db/FollowsRequest.php b/lib/Db/FollowsRequest.php
index 708f2513..fac47cdb 100644
--- a/lib/Db/FollowsRequest.php
+++ b/lib/Db/FollowsRequest.php
@@ -177,7 +177,7 @@ class FollowsRequest extends FollowsRequestBuilder {
 		$this->limitToAccepted($qb, true);
 		$this->leftJoinCacheActors($qb, 'actor_id');
 		$this->leftJoinDetails($qb, 'id', 'ca');
-		$qb->orderBy('creation', 'desc');
+		$qb->orderBy('f.creation', 'desc');
 
 		$follows = [];
 		$cursor = $qb->execute();
@@ -201,7 +201,7 @@ class FollowsRequest extends FollowsRequestBuilder {
 		$this->limitToAccepted($qb, true);
 		$this->leftJoinCacheActors($qb, 'object_id');
 		$this->leftJoinDetails($qb, 'id', 'ca');
-		$qb->orderBy('creation', 'desc');
+		$qb->orderBy('f.creation', 'desc');
 
 		$follows = [];
 		$cursor = $qb->execute();
diff --git a/lib/Db/NotesRequest.php b/lib/Db/NotesRequest.php
index b4de9316..9e687e25 100644
--- a/lib/Db/NotesRequest.php
+++ b/lib/Db/NotesRequest.php
@@ -33,6 +33,7 @@ namespace OCA\Social\Db;
 use DateTime;
 use OCA\Social\Exceptions\NoteNotFoundException;
 use OCA\Social\Model\ActivityPub\Note;
+use OCA\Social\Model\ActivityPub\Person;
 use OCA\Social\Service\ActivityService;
 use OCA\Social\Service\ConfigService;
 use OCA\Social\Service\MiscService;
@@ -160,20 +161,16 @@ class NotesRequest extends NotesRequestBuilder {
 	 *  * Own posts,
 	 *  * Followed accounts
 	 *
-	 * @param string $actorId
+	 * @param Person $actor
 	 * @param int $since
 	 * @param int $limit
 	 *
 	 * @return array
 	 */
-	public function getStreamHome(string $actorId, int $since = 0, int $limit = 5): array {
+	public function getStreamHome(Person $actor, int $since = 0, int $limit = 5): array {
 		$qb = $this->getNotesSelectSql();
 
-		$this->rightJoinFollowing($qb);
-		$this->limitToActorId($qb, $actorId, 'f');
-		$this->limitToAccepted($qb, true, 'f');
-		$qb->orWhere($this->exprLimitToDBField($qb, 'attributed_to', $actorId));
-
+		$this->joinFollowing($qb, $actor);
 		$this->limitPaginate($qb, $since, $limit);
 		$this->leftJoinCacheActors($qb, 'attributed_to');
 
@@ -201,10 +198,10 @@ class NotesRequest extends NotesRequestBuilder {
 	 */
 	public function getStreamAccount(string $actorId, int $since = 0, int $limit = 5): array {
 		$qb = $this->getNotesSelectSql();
-		$this->limitToRecipient($qb, ActivityService::TO_PUBLIC);
 		$this->limitPaginate($qb, $since, $limit);
 		$this->limitToAttributedTo($qb, $actorId);
 		$this->leftJoinCacheActors($qb, 'attributed_to');
+		$this->limitToRecipient($qb, ActivityService::TO_PUBLIC);
 
 		$notes = [];
 		$cursor = $qb->execute();
@@ -222,16 +219,20 @@ class NotesRequest extends NotesRequestBuilder {
 	 *  * Private message.
 	 *  - group messages.
 	 *
-	 * @param string $actorId
+	 * @param Person $actor
 	 * @param int $since
 	 * @param int $limit
 	 *
 	 * @return array
 	 */
-	public function getStreamDirect(string $actorId, int $since = 0, int $limit = 5): array {
+	public function getStreamDirect(Person $actor, int $since = 0, int $limit = 5): array {
 		$qb = $this->getNotesSelectSql();
-		$this->limitToRecipient($qb, $actorId, true);
 		$this->limitPaginate($qb, $since, $limit);
+
+		$this->limitToRecipient($qb, $actor->getId(), true);
+		$this->filterToRecipient($qb, ActivityService::TO_PUBLIC);
+		$this->filterToRecipient($qb, $actor->getFollowers());
+
 		$this->leftJoinCacheActors($qb, 'attributed_to');
 
 		$notes = [];
@@ -258,12 +259,13 @@ class NotesRequest extends NotesRequestBuilder {
 	public function getStreamTimeline(int $since = 0, int $limit = 5, bool $localOnly = true
 	): array {
 		$qb = $this->getNotesSelectSql();
-		$this->limitToRecipient($qb, ActivityService::TO_PUBLIC);
 		$this->limitPaginate($qb, $since, $limit);
 		if ($localOnly) {
 			$this->limitToLocal($qb, true);
 		}
 		$this->leftJoinCacheActors($qb, 'attributed_to');
+		// TODO: to: = real public, cc: = unlisted !?
+		$this->limitToRecipient($qb, ActivityService::TO_PUBLIC);
 
 		$notes = [];
 		$cursor = $qb->execute();
diff --git a/lib/Db/NotesRequestBuilder.php b/lib/Db/NotesRequestBuilder.php
index 9b68a4d9..872004fa 100644
--- a/lib/Db/NotesRequestBuilder.php
+++ b/lib/Db/NotesRequestBuilder.php
@@ -35,7 +35,9 @@ use DateTime;
 use Doctrine\DBAL\Query\QueryBuilder;
 use OCA\Social\Exceptions\InvalidResourceException;
 use OCA\Social\Model\ActivityPub\Note;
+use OCA\Social\Model\ActivityPub\Person;
 use OCA\Social\Model\InstancePath;
+use OCP\DB\QueryBuilder\ICompositeExpression;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 
 class NotesRequestBuilder extends CoreRequestBuilder {
@@ -124,8 +126,9 @@ class NotesRequestBuilder extends CoreRequestBuilder {
 
 	/**
 	 * @param IQueryBuilder $qb
+	 * @param Person $actor
 	 */
-	protected function rightJoinFollowing(IQueryBuilder $qb) {
+	protected function joinFollowing(IQueryBuilder $qb, Person $actor) {
 		if ($qb->getType() !== QueryBuilder::SELECT) {
 			return;
 		}
@@ -134,38 +137,156 @@ class NotesRequestBuilder extends CoreRequestBuilder {
 		$func = $qb->func();
 		$pf = $this->defaultSelectAlias . '.';
 
-		$orX = $expr->orX();
-		$orX->add($expr->eq($func->lower($pf . 'to'), $func->lower('f.follow_id')));
-		$orX->add(
-			$expr->like(
-				$func->lower($pf . 'to_array'), $func->concat(
-				$qb->createNamedParameter('%"'),
-				$func->concat($func->lower('f.follow_id'), $qb->createNamedParameter('"%'))
-			)
-			)
+		$on = $expr->orX();
+		$on->add($this->exprLimitToRecipient($qb, $actor->getFollowers(), true));
+
+		// list of possible recipient as a follower (to, to_array, cc, ...)
+		$recipientFields = $expr->orX();
+		$recipientFields->add($expr->eq($func->lower($pf . 'to'), $func->lower('f.follow_id')));
+		$recipientFields->add($this->exprFieldWithinJsonFormat($qb, 'to_array', 'f.follow_id'));
+		$recipientFields->add($this->exprFieldWithinJsonFormat($qb, 'cc', 'f.follow_id'));
+		$recipientFields->add($this->exprFieldWithinJsonFormat($qb, 'bcc', 'f.follow_id'));
+
+		// all possible follow, but linked by followers (actor_id) and accepted follow
+		$crossFollows = $expr->andX();
+		$crossFollows->add($recipientFields);
+		$crossFollows->add($this->exprLimitToDBField($qb, 'actor_id', $actor->getId(), false, 'f'));
+		$crossFollows->add($this->exprLimitToDBFieldInt($qb, 'accepted', 1, 'f'));
+		$on->add($crossFollows);
+
+		$qb->join($this->defaultSelectAlias, CoreRequestBuilder::TABLE_SERVER_FOLLOWS, 'f', $on);
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $field
+	 * @param string $fieldRight
+	 * @param string $alias
+	 *
+	 * @return string
+	 */
+	protected function exprFieldWithinJsonFormat(
+		IQueryBuilder $qb, string $field, string $fieldRight, string $alias = ''
+	) {
+		$func = $qb->func();
+		$expr = $qb->expr();
+
+		if ($alias === '') {
+			$alias = $this->defaultSelectAlias;
+		}
+
+		$concat = $func->concat(
+			$qb->createNamedParameter('%"'),
+			$func->concat($fieldRight, $qb->createNamedParameter('"%'))
 		);
-		$orX->add(
-			$expr->like(
-				$func->lower($pf . 'cc'), $func->concat(
-				$qb->createNamedParameter('%"'),
-				$func->concat($func->lower('f.follow_id'), $qb->createNamedParameter('"%'))
-			)
-			)
+
+		return $expr->iLike($alias . '.' . $field, $concat);
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $field
+	 * @param string $value
+	 *
+	 * @return string
+	 */
+	protected function exprValueWithinJsonFormat(IQueryBuilder $qb, string $field, string $value
+	): string {
+		$dbConn = $this->dbConnection;
+		$expr = $qb->expr();
+
+		return $expr->iLike(
+			$field,
+			$qb->createNamedParameter('%"' . $dbConn->escapeLikeParameter($value) . '"%')
 		);
-		$orX->add(
-			$expr->like(
-				$func->lower($pf . 'bcc'), $func->concat(
-				$qb->createNamedParameter('%"'),
-				$func->concat($func->lower('f.follow_id'), $qb->createNamedParameter('"%'))
-			)
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $field
+	 * @param string $value
+	 *
+	 * @return string
+	 */
+	protected function exprValueNotWithinJsonFormat(IQueryBuilder $qb, string $field, string $value
+	): string {
+		$dbConn = $this->dbConnection;
+		$expr = $qb->expr();
+		$func = $qb->func();
+
+		return $expr->notLike(
+			$func->lower($field),
+			$qb->createNamedParameter(
+				'%"' . $func->lower($dbConn->escapeLikeParameter($value)) . '"%'
 			)
 		);
+	}
 
-		$qb->rightJoin(
-			$this->defaultSelectAlias, CoreRequestBuilder::TABLE_SERVER_FOLLOWS, 'f',
-			$orX
-		);
 
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $recipient
+	 * @param bool $asAuthor
+	 */
+	protected function limitToRecipient(
+		IQueryBuilder &$qb, string $recipient, bool $asAuthor = false
+	) {
+		$qb->andWhere($this->exprLimitToRecipient($qb, $recipient, $asAuthor));
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $recipient
+	 * @param bool $asAuthor
+	 *
+	 * @return ICompositeExpression
+	 */
+	protected function exprLimitToRecipient(
+		IQueryBuilder &$qb, string $recipient, bool $asAuthor = false
+	): ICompositeExpression {
+
+		$expr = $qb->expr();
+		$limit = $expr->orX();
+
+		if ($asAuthor === true) {
+			$func = $qb->func();
+			$limit->add(
+				$expr->eq(
+					$func->lower('attributed_to'),
+					$func->lower($qb->createNamedParameter($recipient))
+				)
+			);
+		}
+
+		$limit->add($expr->eq('to', $qb->createNamedParameter($recipient)));
+		$limit->add($this->exprValueWithinJsonFormat($qb, 'to_array', $recipient));
+		$limit->add($this->exprValueWithinJsonFormat($qb, 'cc', $recipient));
+		$limit->add($this->exprValueWithinJsonFormat($qb, 'bcc', $recipient));
+
+		return $limit;
+	}
+
+
+	/**
+	 * @param IQueryBuilder $qb
+	 * @param string $recipient
+	 */
+	protected function filterToRecipient(IQueryBuilder &$qb, string $recipient) {
+
+		$expr = $qb->expr();
+		$filter = $expr->andX();
+
+		$filter->add($expr->neq('to', $qb->createNamedParameter($recipient)));
+		$filter->add($this->exprValueNotWithinJsonFormat($qb, 'to_array', $recipient));
+		$filter->add($this->exprValueNotWithinJsonFormat($qb, 'cc', $recipient));
+		$filter->add($this->exprValueNotWithinJsonFormat($qb, 'bcc', $recipient));
+
+		$qb->andWhere($filter);
+//		return $filter;
 	}
 
 
diff --git a/lib/Service/ActivityPub/NoteService.php b/lib/Service/ActivityPub/NoteService.php
index 8bddea7b..af0257f9 100644
--- a/lib/Service/ActivityPub/NoteService.php
+++ b/lib/Service/ActivityPub/NoteService.php
@@ -350,15 +350,14 @@ class NoteService implements ICoreService {
 
 
 	/**
-	 * @param string $actorId
-	 *
+	 * @param Person $actor
 	 * @param int $since
 	 * @param int $limit
 	 *
 	 * @return Note[]
 	 */
-	public function getStreamHome(string $actorId, int $since = 0, int $limit = 5): array {
-		return $this->notesRequest->getStreamHome($actorId, $since, $limit);
+	public function getStreamHome(Person $actor, int $since = 0, int $limit = 5): array {
+		return $this->notesRequest->getStreamHome($actor, $since, $limit);
 	}
 
 
@@ -375,14 +374,14 @@ class NoteService implements ICoreService {
 
 
 	/**
-	 * @param string $actorId
+	 * @param Person $actor
 	 * @param int $since
 	 * @param int $limit
 	 *
 	 * @return Note[]
 	 */
-	public function getStreamDirect(string $actorId, int $since = 0, int $limit = 5): array {
-		return $this->notesRequest->getStreamDirect($actorId, $since, $limit);
+	public function getStreamDirect(Person $actor, int $since = 0, int $limit = 5): array {
+		return $this->notesRequest->getStreamDirect($actor, $since, $limit);
 	}