Move oc_file_metadata.metadata migration to a background jobbackport/43580/stable26

Signed-off-by: Louis Chemineau <louis@chmn.me>

[skip ci]

8 files changed, 205 insertions, 20 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/LICENSE b/lib/composer/composer/LICENSE
index 62ecfd8d004..f27399a042d 100644
--- a/lib/composer/composer/LICENSE
+++ b/lib/composer/composer/LICENSE
@@ -1,3 +1,4 @@
+
 Copyright (c) Nils Adermann, Jordi Boggiano
 
 Permission is hereby granted, free of charge, to any person obtaining a copy
@@ -17,3 +18,4 @@ AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 THE SOFTWARE.
+
diff --git a/lib/composer/composer/autoload_classmap.php b/lib/composer/composer/autoload_classmap.php
index e4e2aaf3aa3..5282056eb7e 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',
diff --git a/lib/composer/composer/autoload_static.php b/lib/composer/composer/autoload_static.php
index 1b448946a42..315754a6471 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',
diff --git a/lib/private/Repair.php b/lib/private/Repair.php
index 330fa241b1e..26eab6c8e01 100644
--- a/lib/private/Repair.php
+++ b/lib/private/Repair.php
@@ -45,7 +45,10 @@ use OC\DB\Connection;
 use OC\DB\ConnectionAdapter;
 use OC\Repair\AddBruteForceCleanupJob;
 use OC\Repair\AddCleanupUpdaterBackupsJob;
+use OC\Repair\AddMetadataMigrationJob;
+use OC\Repair\AddRemoveOldTasksBackgroundJob;
 use OC\Repair\CleanTags;
+use OC\Repair\CleanUpAbandonedApps;
 use OC\Repair\ClearFrontendCaches;
 use OC\Repair\ClearGeneratedAvatarCache;
 use OC\Repair\Collation;
@@ -85,6 +88,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;
 
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);
+	}
+}