first throw of chunked table

Signed-off-by: Maxence Lange <maxence@artificial-owl.com>

8 files changed, 291 insertions, 9 deletions

diff --git a/lib/Cron/Chunk.php b/lib/Cron/Chunk.php
new file mode 100644
index 00000000..718737d4
--- /dev/null
+++ b/lib/Cron/Chunk.php
@@ -0,0 +1,88 @@
+<?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\Cron;
+
+
+use OC\BackgroundJob\TimedJob;
+use OCA\Social\AppInfo\Application;
+use OCA\Social\Service\ConfigService;
+use OCA\Social\Service\MiscService;
+use OCP\AppFramework\QueryException;
+
+
+/**
+ * Class Queue
+ *
+ * @package OCA\Social\Cron
+ */
+class Chunk extends TimedJob {
+
+	/** @var ConfigService */
+	private $configService;
+
+	/** @var MiscService */
+	private $miscService;
+
+
+	/**
+	 * Cache constructor.
+	 */
+	public function __construct() {
+		$this->setInterval(12 * 3600); // 12 heures
+	}
+
+
+	/**
+	 * @param mixed $argument
+	 *
+	 * @throws QueryException
+	 */
+	protected function run($argument) {
+		$app = new Application();
+		$c = $app->getContainer();
+
+		$this->configService = $c->query(ConfigService::class);
+		$this->miscService = $c->query(MiscService::class);
+
+		$size = (int)$this->configService->getAppValue(ConfigService::DATABASE_CHUNK_SIZE);
+		$this->morphChunks($size);
+	}
+
+
+	/**
+	 * @param int $size
+	 */
+	private function morphChunks(int $size) {
+
+	}
+
+}
+
diff --git a/lib/Db/SocialCoreQueryBuilder.php b/lib/Db/SocialCoreQueryBuilder.php
index 917a2b1c..d47712da 100644
--- a/lib/Db/SocialCoreQueryBuilder.php
+++ b/lib/Db/SocialCoreQueryBuilder.php
@@ -33,6 +33,7 @@ namespace OCA\Social\Db;
 
 use daita\MySmallPhpTools\Db\ExtendedQueryBuilder;
 use OCA\Social\Model\ActivityPub\Actor\Person;
+use OCP\DB\QueryBuilder\ICompositeExpression;
 
 
 /**
@@ -47,6 +48,49 @@ class SocialCoreQueryBuilder extends ExtendedQueryBuilder {
 	private $viewer = null;
 
 
+	/** @var int */
+	private $chunk = 0;
+
+
+	/**
+	 * @param int $chunk
+	 *
+	 * @return $this
+	 */
+	public function setChunk(int $chunk): self {
+		$this->chunk = $chunk;
+		$this->inChunk();
+
+		return $this;
+	}
+
+	/**
+	 * @return int
+	 */
+	public function getChunk(): int {
+		return $this->chunk;
+	}
+
+	/**
+	 * Limit the request to a chunk
+	 *
+	 * @param string $alias
+	 * @param ICompositeExpression|null $expr
+	 */
+	public function inChunk(string $alias = '', ICompositeExpression $expr = null) {
+		if ($this->getChunk() === 0) {
+			return;
+		}
+
+		if ($expr !== null) {
+			$expr->add($this->exprLimitToDBFieldInt('chunk', $this->getChunk(), $alias));
+
+			return;
+		}
+		$this->limitToDBFieldInt('chunk', $this->getChunk(), $alias);
+	}
+
+
 	/**
 	 * @return bool
 	 */
diff --git a/lib/Db/SocialCrossQueryBuilder.php b/lib/Db/SocialCrossQueryBuilder.php
index 4cd50c5d..c0207ef5 100644
--- a/lib/Db/SocialCrossQueryBuilder.php
+++ b/lib/Db/SocialCrossQueryBuilder.php
@@ -55,6 +55,7 @@ class SocialCrossQueryBuilder extends SocialCoreQueryBuilder {
 
 		if ($aliasDest !== '') {
 			$this->from(CoreRequestBuilder::TABLE_STREAM_DEST, $aliasDest);
+//			$this->inChunk($aliasDest);
 		}
 		if ($aliasFollowing !== '') {
 			$this->from(CoreRequestBuilder::TABLE_FOLLOWS, $aliasFollowing);
@@ -178,13 +179,16 @@ class SocialCrossQueryBuilder extends SocialCoreQueryBuilder {
 		$orX->add($expr->eq($alias . '.stream_id_prim', $pf . '.object_id_prim'));
 
 		$on = $expr->andX();
+//		$this->inChunk('sa', $on);
 		$viewer = $this->getViewer();
 		$idPrim = $this->prim($viewer->getId());
 
 		$on->add($expr->eq($alias . '.actor_id_prim', $this->createNamedParameter($idPrim)));
 		$on->add($orX);
 
-		$this->leftJoin($this->getDefaultSelectAlias(), CoreRequestBuilder::TABLE_STREAM_ACTIONS, 'sa', $on);
+		$this->leftJoin(
+			$this->getDefaultSelectAlias(), CoreRequestBuilder::TABLE_STREAM_ACTIONS, $alias, $on
+		);
 	}
 
 
diff --git a/lib/Db/SocialLimitsQueryBuilder.php b/lib/Db/SocialLimitsQueryBuilder.php
index c1dc173e..09455e40 100644
--- a/lib/Db/SocialLimitsQueryBuilder.php
+++ b/lib/Db/SocialLimitsQueryBuilder.php
@@ -46,6 +46,7 @@ use OCP\DB\QueryBuilder\ICompositeExpression;
  */
 class SocialLimitsQueryBuilder extends SocialCrossQueryBuilder {
 
+
 	/**
 	 * Limit the request to the Type
 	 *
diff --git a/lib/Db/StreamRequest.php b/lib/Db/StreamRequest.php
index 8591eeaf..620da0db 100644
--- a/lib/Db/StreamRequest.php
+++ b/lib/Db/StreamRequest.php
@@ -370,6 +370,7 @@ class StreamRequest extends StreamRequestBuilder {
 	 */
 	public function getTimelineHome(int $since = 0, int $limit = 5): array {
 		$qb = $this->getStreamSelectSql();
+		$qb->setChunk(1);
 
 		$qb->filterType(SocialAppNotification::TYPE);
 		$qb->limitPaginate($since, $limit);
diff --git a/lib/Migration/Version0003Date20200611000001.php b/lib/Migration/Version0003Date20200611000001.php
index 6593a912..eed82542 100644
--- a/lib/Migration/Version0003Date20200611000001.php
+++ b/lib/Migration/Version0003Date20200611000001.php
@@ -404,6 +404,14 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 			]
 		);
 		$table->addColumn(
+			'chunk', Type::SMALLINT,
+			[
+				'default'  => 1,
+				'length'   => 1,
+				'unsigned' => true
+			]
+		);
+		$table->addColumn(
 			'id_prim', 'string',
 			[
 				'notnull' => false,
@@ -603,6 +611,7 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 		);
 
 		$table->setPrimaryKey(['id_prim']);
+		$table->addIndex(['chunk'], 'chunk');
 		$table->addUniqueIndex(
 			[
 				'id_prim',
@@ -996,6 +1005,14 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 			]
 		);
 		$table->addColumn(
+			'chunk', Type::SMALLINT,
+			[
+				'default'  => 1,
+				'length'   => 1,
+				'unsigned' => true
+			]
+		);
+		$table->addColumn(
 			'actor_id', 'string',
 			[
 				'notnull' => false,
@@ -1039,6 +1056,7 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 		);
 
 		$table->setPrimaryKey(['id']);
+		$table->addIndex(['chunk'], 'chunk');
 		$table->addUniqueIndex(['stream_id_prim', 'actor_id_prim'], 'sa');
 	}
 
@@ -1053,6 +1071,14 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 
 		$table = $schema->createTable('social_3_stream_dest');
 		$table->addColumn(
+			'chunk', Type::SMALLINT,
+			[
+				'default'  => 1,
+				'length'   => 1,
+				'unsigned' => true
+			]
+		);
+		$table->addColumn(
 			'stream_id', 'string',
 			[
 				'notnull' => false,
@@ -1085,6 +1111,7 @@ class Version0003Date20200611000001 extends SimpleMigrationStep {
 			]
 		);
 
+		$table->addIndex(['chunk'], 'chunk');
 		$table->addUniqueIndex(['stream_id', 'actor_id', 'type'], 'sat');
 		$table->addIndex(['type', 'subtype'], 'ts');
 	}
diff --git a/lib/Migration/Version0003Date20200730213528.php b/lib/Migration/Version0003Date20200730213528.php
new file mode 100644
index 00000000..cf7fed6d
--- /dev/null
+++ b/lib/Migration/Version0003Date20200730213528.php
@@ -0,0 +1,114 @@
+<?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\Migration;
+
+
+use Closure;
+use Doctrine\DBAL\Schema\SchemaException;
+use Doctrine\DBAL\Types\Type;
+use OCP\DB\ISchemaWrapper;
+use OCP\IDBConnection;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+
+/**
+ * Class Version0003Date20200611000001
+ *
+ * @package OCA\Social\Migration
+ */
+class Version0003Date20200730213528 extends SimpleMigrationStep {
+
+
+	/** @var IDBConnection */
+	private $connection;
+
+
+	/**
+	 * @param IDBConnection $connection
+	 */
+	public function __construct(IDBConnection $connection) {
+		$this->connection = $connection;
+	}
+
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 *
+	 * @return ISchemaWrapper
+	 * @throws SchemaException
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options
+	): ISchemaWrapper {
+		/** @var ISchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		$this->addChunkToTable($schema, 'social_3_stream');
+		$this->addChunkToTable($schema, 'social_3_stream_act');
+		$this->addChunkToTable($schema, 'social_3_stream_dest');
+
+		return $schema;
+	}
+
+
+	/**
+	 * @param ISchemaWrapper $schema
+	 * @param string $tableName
+	 *
+	 * @throws SchemaException
+	 */
+	private function addChunkToTable(ISchemaWrapper $schema, string $tableName) {
+		if (!$schema->hasTable($tableName)) {
+			return;
+		}
+
+		$table = $schema->getTable($tableName);
+		if ($table->hasColumn('chunk')) {
+			return;
+		}
+
+		$table->addColumn(
+			'chunk', Type::SMALLINT,
+			[
+				'default'  => 1,
+				'length'   => 1,
+				'unsigned' => true
+			]
+		);
+
+		$table->addIndex(['chunk'], 'chunk');
+	}
+
+
+}
+
diff --git a/lib/Service/ConfigService.php b/lib/Service/ConfigService.php
index afa57656..b77fda3e 100644
--- a/lib/Service/ConfigService.php
+++ b/lib/Service/ConfigService.php
@@ -56,6 +56,7 @@ class ConfigService {
 	const CLOUD_URL = 'cloud_url';
 	const SOCIAL_URL = 'social_url';
 	const SOCIAL_ADDRESS = 'social_address';
+	const DATABASE_CHUNK_SIZE = 'db_chunk_size';
 
 	const SOCIAL_SERVICE = 'service';
 	const SOCIAL_MAX_SIZE = 'max_size';
@@ -64,6 +65,7 @@ class ConfigService {
 
 	const SOCIAL_SELF_SIGNED = 'allow_self_signed';
 
+
 	const BACKGROUND_CRON = 1;
 	const BACKGROUND_ASYNC = 2;
 	const BACKGROUND_SERVICE = 3;
@@ -71,14 +73,15 @@ class ConfigService {
 
 	/** @var array */
 	public $defaults = [
-		self::CLOUD_URL          => '',
-		self::SOCIAL_URL         => '',
-		self::SOCIAL_ADDRESS     => '',
-		self::SOCIAL_SERVICE     => 1,
-		self::SOCIAL_MAX_SIZE    => 10,
-		self::SOCIAL_ACCESS_TYPE => 'all_but',
-		self::SOCIAL_ACCESS_LIST => '[]',
-		self::SOCIAL_SELF_SIGNED => '0'
+		self::CLOUD_URL           => '',
+		self::SOCIAL_URL          => '',
+		self::SOCIAL_ADDRESS      => '',
+		self::SOCIAL_SERVICE      => 1,
+		self::SOCIAL_MAX_SIZE     => 10,
+		self::SOCIAL_ACCESS_TYPE  => 'all_but',
+		self::SOCIAL_ACCESS_LIST  => '[]',
+		self::SOCIAL_SELF_SIGNED  => '0',
+		self::DATABASE_CHUNK_SIZE => 10000
 	];
 
 	/** @var array */