Move oc_file_metadata.metadata migration to a background job

Signed-off-by: Louis Chemineau <louis@chmn.me>
Signed-off-by: Max <max@nextcloud.com>

7 files changed, 205 insertions, 27 deletions

diff --git a/core/BackgroundJobs/MetadataMigrationJob.php b/core/BackgroundJobs/MetadataMigrationJob.php
new file mode 100644
index 00000000000..0e88c31318e
--- /dev/null
+++ b/core/BackgroundJobs/MetadataMigrationJob.php
@@ -0,0 +1,102 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2024 Louis Chemineau <louis@chmn.me>
+ *
+ * @author Louis Chemineau <louis@chmn.me>
+ *
+ * @license AGPL-3.0-or-later
+ *
+ * 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 OC\Core\BackgroundJobs;
+
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\BackgroundJob\IJobList;
+use OCP\BackgroundJob\TimedJob;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\IDBConnection;
+
+// Migrate oc_file_metadata.metadata to oc_file_metadata.value.
+// This was previously done in a migration, but it is taking to much time in large instances.
+// This job will progressively migrate the data 1 hour per night every night.
+// Once done, it will remove itself from the job list.
+class MetadataMigrationJob extends TimedJob {
+	public function __construct(
+		ITimeFactory $time,
+		private IDBConnection $db,
+		private IJobList $jobList,
+	) {
+		parent::__construct($time);
+
+		$this->setTimeSensitivity(\OCP\BackgroundJob\IJob::TIME_INSENSITIVE);
+		$this->setInterval(24 * 3600);
+	}
+
+	protected function run(mixed $argument): void {
+		if (!$this->db->createSchema()->getTable('oc_file_metadata')->hasColumn('metadata')) {
+			return;
+		}
+
+		$updateQuery = $this->db->getQueryBuilder();
+		$updateQuery->update('file_metadata')
+			->set('value', $updateQuery->createParameter('value'))
+			->set('metadata', $updateQuery->createParameter('metadata'))
+			->where($updateQuery->expr()->eq('id', $updateQuery->createParameter('id')))
+			->andWhere($updateQuery->expr()->eq('group_name', $updateQuery->createParameter('group_name')));
+
+		$selectQuery = $this->db->getQueryBuilder();
+		$selectQuery->select('id', 'group_name', 'metadata')
+			->from('file_metadata')
+			->where($selectQuery->expr()->nonEmptyString('metadata'))
+			->setMaxResults(1000);
+
+		$movedRows = 0;
+		$startTime = time();
+
+		do {
+			// Stop if execution time is more than one hour.
+			if (time() - $startTime > 3600) {
+				return;
+			}
+			$movedRows = $this->chunkedCopying($updateQuery, $selectQuery);
+		} while ($movedRows !== 0);
+
+
+		$this->jobList->remove(MetadataMigrationJob::class);
+	}
+
+	protected function chunkedCopying(IQueryBuilder $updateQuery, IQueryBuilder $selectQuery): int {
+		$this->db->beginTransaction();
+
+		$results = $selectQuery->executeQuery();
+
+		while ($row = $results->fetch()) {
+			$updateQuery
+				->setParameter('id', (int)$row['id'])
+				->setParameter('group_name', $row['group_name'])
+				->setParameter('value', $row['metadata'])
+				->setParameter('metadata', '')
+				->executeStatement();
+		}
+
+		$results->closeCursor();
+		$this->db->commit();
+
+		return $results->rowCount();
+	}
+}
diff --git a/core/Migrations/Version27000Date20230309104325.php b/core/Migrations/Version27000Date20230309104325.php
index e11b37b4b29..0ecaa4a73a7 100644
--- a/core/Migrations/Version27000Date20230309104325.php
+++ b/core/Migrations/Version27000Date20230309104325.php
@@ -72,19 +72,19 @@ class Version27000Date20230309104325 extends SimpleMigrationStep {
 	 * @param array $options
 	 * @return void
 	 */
-	public function postSchemaChange(IOutput $output, Closure $schemaClosure, array $options) {
-		/** @var ISchemaWrapper $schema */
-		$schema = $schemaClosure();
-		$metadataTable = $schema->getTable('file_metadata');
+	// public function postSchemaChange(IOutput $output, Closure $schemaClosure, array $options) {
+	// 	/** @var ISchemaWrapper $schema */
+	// 	$schema = $schemaClosure();
+	// 	$metadataTable = $schema->getTable('file_metadata');
 
-		if (!$metadataTable->hasColumn('metadata')) {
-			return;
-		}
+	// 	if (!$metadataTable->hasColumn('metadata')) {
+	// 		return;
+	// 	}
 
-		$this->connection
-			->getQueryBuilder()
-			->update('file_metadata')
-			->set('value', 'metadata')
-			->executeStatement();
-	}
+	// 	$this->connection
+	// 		->getQueryBuilder()
+	// 		->update('file_metadata')
+	// 		->set('value', 'metadata')
+	// 		->executeStatement();
+	// }
 }
diff --git a/core/Migrations/Version27000Date20230309104802.php b/core/Migrations/Version27000Date20230309104802.php
index 4bd50fe0396..260ae83d970 100644
--- a/core/Migrations/Version27000Date20230309104802.php
+++ b/core/Migrations/Version27000Date20230309104802.php
@@ -43,14 +43,14 @@ class Version27000Date20230309104802 extends SimpleMigrationStep {
 	 * @return null|ISchemaWrapper
 	 */
 	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
-		/** @var ISchemaWrapper $schema */
-		$schema = $schemaClosure();
-		$metadataTable = $schema->getTable('file_metadata');
+		// /** @var ISchemaWrapper $schema */
+		// $schema = $schemaClosure();
+		// $metadataTable = $schema->getTable('file_metadata');
 
-		if ($metadataTable->hasColumn('metadata')) {
-			$metadataTable->dropColumn('metadata');
-			return $schema;
-		}
+		// if ($metadataTable->hasColumn('metadata')) {
+		// 	$metadataTable->dropColumn('metadata');
+		// 	return $schema;
+		// }
 
 		return null;
 	}
diff --git a/lib/composer/composer/autoload_classmap.php b/lib/composer/composer/autoload_classmap.php
index e4e2aaf3aa3..f455e9b010e 100644
--- a/lib/composer/composer/autoload_classmap.php
+++ b/lib/composer/composer/autoload_classmap.php
@@ -895,6 +895,7 @@ return array(
     'OC\\Core\\BackgroundJobs\\CheckForUserCertificates' => $baseDir . '/core/BackgroundJobs/CheckForUserCertificates.php',
     'OC\\Core\\BackgroundJobs\\CleanupLoginFlowV2' => $baseDir . '/core/BackgroundJobs/CleanupLoginFlowV2.php',
     'OC\\Core\\BackgroundJobs\\LookupServerSendCheckBackgroundJob' => $baseDir . '/core/BackgroundJobs/LookupServerSendCheckBackgroundJob.php',
+    'OC\\Core\\BackgroundJobs\\MetadataMigrationJob' => $baseDir . '/core/BackgroundJobs/MetadataMigrationJob.php',
     'OC\\Core\\Command\\App\\Disable' => $baseDir . '/core/Command/App/Disable.php',
     'OC\\Core\\Command\\App\\Enable' => $baseDir . '/core/Command/App/Enable.php',
     'OC\\Core\\Command\\App\\GetPath' => $baseDir . '/core/Command/App/GetPath.php',
@@ -1476,6 +1477,7 @@ return array(
     'OC\\RepairException' => $baseDir . '/lib/private/RepairException.php',
     'OC\\Repair\\AddBruteForceCleanupJob' => $baseDir . '/lib/private/Repair/AddBruteForceCleanupJob.php',
     'OC\\Repair\\AddCleanupUpdaterBackupsJob' => $baseDir . '/lib/private/Repair/AddCleanupUpdaterBackupsJob.php',
+    'OC\\Repair\\AddMetadataMigrationJob' => $baseDir . '/lib/private/Repair/AddMetadataMigrationJob.php',
     'OC\\Repair\\CleanTags' => $baseDir . '/lib/private/Repair/CleanTags.php',
     'OC\\Repair\\CleanUpAbandonedApps' => $baseDir . '/lib/private/Repair/CleanUpAbandonedApps.php',
     'OC\\Repair\\ClearFrontendCaches' => $baseDir . '/lib/private/Repair/ClearFrontendCaches.php',
diff --git a/lib/composer/composer/autoload_static.php b/lib/composer/composer/autoload_static.php
index 1b448946a42..37affb1babc 100644
--- a/lib/composer/composer/autoload_static.php
+++ b/lib/composer/composer/autoload_static.php
@@ -928,6 +928,7 @@ class ComposerStaticInit749170dad3f5e7f9ca158f5a9f04f6a2
         'OC\\Core\\BackgroundJobs\\CheckForUserCertificates' => __DIR__ . '/../../..' . '/core/BackgroundJobs/CheckForUserCertificates.php',
         'OC\\Core\\BackgroundJobs\\CleanupLoginFlowV2' => __DIR__ . '/../../..' . '/core/BackgroundJobs/CleanupLoginFlowV2.php',
         'OC\\Core\\BackgroundJobs\\LookupServerSendCheckBackgroundJob' => __DIR__ . '/../../..' . '/core/BackgroundJobs/LookupServerSendCheckBackgroundJob.php',
+        'OC\\Core\\BackgroundJobs\\MetadataMigrationJob' => __DIR__ . '/../../..' . '/core/BackgroundJobs/MetadataMigrationJob.php',
         'OC\\Core\\Command\\App\\Disable' => __DIR__ . '/../../..' . '/core/Command/App/Disable.php',
         'OC\\Core\\Command\\App\\Enable' => __DIR__ . '/../../..' . '/core/Command/App/Enable.php',
         'OC\\Core\\Command\\App\\GetPath' => __DIR__ . '/../../..' . '/core/Command/App/GetPath.php',
@@ -1509,6 +1510,7 @@ class ComposerStaticInit749170dad3f5e7f9ca158f5a9f04f6a2
         'OC\\RepairException' => __DIR__ . '/../../..' . '/lib/private/RepairException.php',
         'OC\\Repair\\AddBruteForceCleanupJob' => __DIR__ . '/../../..' . '/lib/private/Repair/AddBruteForceCleanupJob.php',
         'OC\\Repair\\AddCleanupUpdaterBackupsJob' => __DIR__ . '/../../..' . '/lib/private/Repair/AddCleanupUpdaterBackupsJob.php',
+        'OC\\Repair\\AddMetadataMigrationJob' => __DIR__ . '/../../..' . '/lib/private/Repair/AddMetadataMigrationJob.php',
         'OC\\Repair\\CleanTags' => __DIR__ . '/../../..' . '/lib/private/Repair/CleanTags.php',
         'OC\\Repair\\CleanUpAbandonedApps' => __DIR__ . '/../../..' . '/lib/private/Repair/CleanUpAbandonedApps.php',
         'OC\\Repair\\ClearFrontendCaches' => __DIR__ . '/../../..' . '/lib/private/Repair/ClearFrontendCaches.php',
diff --git a/lib/private/Repair.php b/lib/private/Repair.php
index 330fa241b1e..2c6e44f0788 100644
--- a/lib/private/Repair.php
+++ b/lib/private/Repair.php
@@ -34,18 +34,13 @@
  */
 namespace OC;
 
-use OC\Repair\CleanUpAbandonedApps;
-use OCP\AppFramework\QueryException;
-use OCP\AppFramework\Utility\ITimeFactory;
-use OCP\Collaboration\Resources\IManager;
-use OCP\EventDispatcher\IEventDispatcher;
-use OCP\Migration\IOutput;
-use OCP\Migration\IRepairStep;
 use OC\DB\Connection;
 use OC\DB\ConnectionAdapter;
 use OC\Repair\AddBruteForceCleanupJob;
 use OC\Repair\AddCleanupUpdaterBackupsJob;
+use OC\Repair\AddMetadataMigrationJob;
 use OC\Repair\CleanTags;
+use OC\Repair\CleanUpAbandonedApps;
 use OC\Repair\ClearFrontendCaches;
 use OC\Repair\ClearGeneratedAvatarCache;
 use OC\Repair\Collation;
@@ -85,6 +80,12 @@ use OC\Repair\RepairInvalidShares;
 use OC\Repair\RepairMimeTypes;
 use OC\Repair\SqliteAutoincrement;
 use OC\Template\JSCombiner;
+use OCP\AppFramework\QueryException;
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\Collaboration\Resources\IManager;
+use OCP\EventDispatcher\IEventDispatcher;
+use OCP\Migration\IOutput;
+use OCP\Migration\IRepairStep;
 use Psr\Log\LoggerInterface;
 use Throwable;
 
@@ -210,6 +211,7 @@ class Repair implements IOutput {
 			\OCP\Server::get(AddTokenCleanupJob::class),
 			\OCP\Server::get(CleanUpAbandonedApps::class),
 			\OCP\Server::get(AddMissingSecretJob::class),
+			\OCP\Server::get(AddMetadataMigrationJob::class),
 		];
 	}
 
diff --git a/lib/private/Repair/AddMetadataMigrationJob.php b/lib/private/Repair/AddMetadataMigrationJob.php
new file mode 100644
index 00000000000..7bc3573a1a2
--- /dev/null
+++ b/lib/private/Repair/AddMetadataMigrationJob.php
@@ -0,0 +1,70 @@
+<?php
+/**
+ * @copyright Copyright (c) 2024 Louis Chmn <louis@chmn.me>
+ *
+ * @author Louis Chmn <louis@chmn.me>
+ *
+ * @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 OC\Repair;
+
+use OC\Core\BackgroundJobs\MetadataMigrationJob;
+use OCP\BackgroundJob\IJobList;
+use OCP\IDBConnection;
+use OCP\Migration\IOutput;
+use OCP\Migration\IRepairStep;
+
+class AddMetadataMigrationJob implements IRepairStep {
+	public function __construct(
+		private IJobList $jobList,
+		private IDBConnection $db,
+	) {
+	}
+
+	public function getName() {
+		return 'Queue a job to migrate the file_metadata table and delete the metadata column once empty';
+	}
+
+	public function run(IOutput $output) {
+		$schema = $this->db->createSchema();
+		$metadataTable = $schema->getTable('oc_file_metadata');
+
+		if (!$metadataTable->hasColumn('metadata')) {
+			return;
+		}
+
+		$selectQuery = $this->db->getQueryBuilder();
+		$result = $selectQuery->select('id', 'group_name', 'metadata')
+			->from('file_metadata')
+			->where($selectQuery->expr()->nonEmptyString('metadata'))
+			->setMaxResults(1)
+			->executeQuery();
+
+		if ($result->rowCount() === 0) {
+			$output->info('Removing metadata column from the file_metadata table.');
+			$metadataTable->dropColumn('metadata');
+			$this->db->migrateToSchema($schema);
+			return;
+		}
+
+		if ($this->jobList->has(MetadataMigrationJob::class, null)) {
+			return;
+		}
+
+		$this->jobList->add(MetadataMigrationJob::class);
+	}
+}