Merge pull request #31304 from nextcloud/feature/dry_run_for_add_indices

Add --dry-run option for add-missing-* cmd

3 files changed, 130 insertions, 37 deletions

diff --git a/core/Command/Db/AddMissingColumns.php b/core/Command/Db/AddMissingColumns.php
index 8b168b21875..1e338e151df 100644
--- a/core/Command/Db/AddMissingColumns.php
+++ b/core/Command/Db/AddMissingColumns.php
@@ -31,6 +31,7 @@ use OC\DB\SchemaWrapper;
 use OCP\IDBConnection;
 use Symfony\Component\Console\Command\Command;
 use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
 use Symfony\Component\Console\Output\OutputInterface;
 use Symfony\Component\EventDispatcher\EventDispatcherInterface;
 use Symfony\Component\EventDispatcher\GenericEvent;
@@ -61,11 +62,12 @@ class AddMissingColumns extends Command {
 	protected function configure() {
 		$this
 			->setName('db:add-missing-columns')
-			->setDescription('Add missing optional columns to the database tables');
+			->setDescription('Add missing optional columns to the database tables')
+			->addOption('dry-run', null, InputOption::VALUE_NONE, "Output the SQL queries instead of running them.");
 	}
 
 	protected function execute(InputInterface $input, OutputInterface $output): int {
-		$this->addCoreColumns($output);
+		$this->addCoreColumns($output, $input->getOption('dry-run'));
 
 		// Dispatch event so apps can also update columns if needed
 		$event = new GenericEvent($output);
@@ -77,9 +79,10 @@ class AddMissingColumns extends Command {
 	 * add missing indices to the share table
 	 *
 	 * @param OutputInterface $output
+	 * @param bool $dryRun If true, will return the sql queries instead of running them.
 	 * @throws \Doctrine\DBAL\Schema\SchemaException
 	 */
-	private function addCoreColumns(OutputInterface $output) {
+	private function addCoreColumns(OutputInterface $output, bool $dryRun): void {
 		$output->writeln('<info>Check columns of the comments table.</info>');
 
 		$schema = new SchemaWrapper($this->connection);
@@ -93,7 +96,10 @@ class AddMissingColumns extends Command {
 					'notnull' => false,
 					'length' => 64,
 				]);
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Comments table updated successfully.</info>');
 			}
diff --git a/core/Command/Db/AddMissingIndices.php b/core/Command/Db/AddMissingIndices.php
index 489e44f4011..f1dba2f4c31 100644
--- a/core/Command/Db/AddMissingIndices.php
+++ b/core/Command/Db/AddMissingIndices.php
@@ -38,6 +38,7 @@ use OC\DB\Connection;
 use OC\DB\SchemaWrapper;
 use OCP\IDBConnection;
 use Symfony\Component\Console\Command\Command;
+use Symfony\Component\Console\Input\InputOption;
 use Symfony\Component\Console\Input\InputInterface;
 use Symfony\Component\Console\Output\OutputInterface;
 use Symfony\Component\EventDispatcher\EventDispatcherInterface;
@@ -69,11 +70,12 @@ class AddMissingIndices extends Command {
 	protected function configure() {
 		$this
 			->setName('db:add-missing-indices')
-			->setDescription('Add missing indices to the database tables');
+			->setDescription('Add missing indices to the database tables')
+			->addOption('dry-run', null, InputOption::VALUE_NONE, "Output the SQL queries instead of running them.");
 	}
 
 	protected function execute(InputInterface $input, OutputInterface $output): int {
-		$this->addCoreIndexes($output);
+		$this->addCoreIndexes($output, $input->getOption('dry-run'));
 
 		// Dispatch event so apps can also update indexes if needed
 		$event = new GenericEvent($output);
@@ -85,9 +87,10 @@ class AddMissingIndices extends Command {
 	 * add missing indices to the share table
 	 *
 	 * @param OutputInterface $output
+	 * @param bool $dryRun If true, will return the sql queries instead of running them.
 	 * @throws \Doctrine\DBAL\Schema\SchemaException
 	 */
-	private function addCoreIndexes(OutputInterface $output) {
+	private function addCoreIndexes(OutputInterface $output, bool $dryRun): void {
 		$output->writeln('<info>Check indices of the share table.</info>');
 
 		$schema = new SchemaWrapper($this->connection);
@@ -98,7 +101,10 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('share_with_index')) {
 				$output->writeln('<info>Adding additional share_with index to the share table, this can take some time...</info>');
 				$table->addIndex(['share_with'], 'share_with_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Share table updated successfully.</info>');
 			}
@@ -106,7 +112,10 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('parent_index')) {
 				$output->writeln('<info>Adding additional parent index to the share table, this can take some time...</info>');
 				$table->addIndex(['parent'], 'parent_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Share table updated successfully.</info>');
 			}
@@ -114,7 +123,10 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('owner_index')) {
 				$output->writeln('<info>Adding additional owner index to the share table, this can take some time...</info>');
 				$table->addIndex(['uid_owner'], 'owner_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Share table updated successfully.</info>');
 			}
@@ -122,7 +134,10 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('initiator_index')) {
 				$output->writeln('<info>Adding additional initiator index to the share table, this can take some time...</info>');
 				$table->addIndex(['uid_initiator'], 'initiator_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Share table updated successfully.</info>');
 			}
@@ -134,28 +149,40 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('fs_mtime')) {
 				$output->writeln('<info>Adding additional mtime index to the filecache table, this can take some time...</info>');
 				$table->addIndex(['mtime'], 'fs_mtime');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Filecache table updated successfully.</info>');
 			}
 			if (!$table->hasIndex('fs_size')) {
 				$output->writeln('<info>Adding additional size index to the filecache table, this can take some time...</info>');
 				$table->addIndex(['size'], 'fs_size');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Filecache table updated successfully.</info>');
 			}
 			if (!$table->hasIndex('fs_id_storage_size')) {
 				$output->writeln('<info>Adding additional size index to the filecache table, this can take some time...</info>');
 				$table->addIndex(['fileid', 'storage', 'size'], 'fs_id_storage_size');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Filecache table updated successfully.</info>');
 			}
 			if (!$table->hasIndex('fs_storage_path_prefix') && !$schema->getDatabasePlatform() instanceof PostgreSQL94Platform) {
 				$output->writeln('<info>Adding additional path index to the filecache table, this can take some time...</info>');
 				$table->addIndex(['storage', 'path'], 'fs_storage_path_prefix', [], ['lengths' => [null, 64]]);
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Filecache table updated successfully.</info>');
 			}
@@ -167,7 +194,10 @@ class AddMissingIndices extends Command {
 			if (!$table->hasIndex('twofactor_providers_uid')) {
 				$output->writeln('<info>Adding additional twofactor_providers_uid index to the twofactor_providers table, this can take some time...</info>');
 				$table->addIndex(['uid'], 'twofactor_providers_uid');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>Twofactor_providers table updated successfully.</info>');
 			}
@@ -191,7 +221,10 @@ class AddMissingIndices extends Command {
 				$table->addUniqueIndex(['poll_token'], 'poll_token');
 				$table->addUniqueIndex(['login_token'], 'login_token');
 				$table->addIndex(['timestamp'], 'timestamp');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>login_flow_v2 table updated successfully.</info>');
 			}
@@ -210,7 +243,10 @@ class AddMissingIndices extends Command {
 				}
 
 				$table->addUniqueIndex(['version'], 'version');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>whats_new table updated successfully.</info>');
 			}
@@ -230,7 +266,10 @@ class AddMissingIndices extends Command {
 					}
 				}
 
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$cardsUpdated = true;
 			}
 
@@ -244,7 +283,10 @@ class AddMissingIndices extends Command {
 				}
 
 				$table->addIndex(['addressbookid'], 'cards_abid');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$cardsUpdated = true;
 			}
 
@@ -258,7 +300,10 @@ class AddMissingIndices extends Command {
 				}
 
 				$table->addIndex(['addressbookid', 'uri'], 'cards_abiduri');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$cardsUpdated = true;
 			}
 
@@ -281,7 +326,10 @@ class AddMissingIndices extends Command {
 				}
 
 				$table->addIndex(['addressbookid'], 'cards_prop_abid');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>cards_properties table updated successfully.</info>');
 			}
@@ -294,7 +342,10 @@ class AddMissingIndices extends Command {
 				$output->writeln('<info>Adding calendarobject_calid_index index to the calendarobjects_props table, this can take some time...</info>');
 
 				$table->addIndex(['calendarid', 'calendartype'], 'calendarobject_calid_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>calendarobjects_props table updated successfully.</info>');
 			}
@@ -307,7 +358,10 @@ class AddMissingIndices extends Command {
 				$output->writeln('<info>Adding schedulobj_principuri_index index to the schedulingobjects table, this can take some time...</info>');
 
 				$table->addIndex(['principaluri'], 'schedulobj_principuri_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>schedulingobjects table updated successfully.</info>');
 			}
@@ -322,14 +376,20 @@ class AddMissingIndices extends Command {
 				$output->writeln('<info>Adding properties_path_index index to the oc_properties table, this can take some time...</info>');
 
 				$table->addIndex(['userid', 'propertypath'], 'properties_path_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$propertiesUpdated = true;
 			}
 			if (!$table->hasIndex('properties_pathonly_index')) {
 				$output->writeln('<info>Adding properties_pathonly_index index to the oc_properties table, this can take some time...</info>');
 
 				$table->addIndex(['propertypath'], 'properties_pathonly_index');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$propertiesUpdated = true;
 			}
 
@@ -346,7 +406,10 @@ class AddMissingIndices extends Command {
 				$output->writeln('<info>Adding job_lastcheck_reserved index to the oc_jobs table, this can take some time...</info>');
 
 				$table->addIndex(['last_checked', 'reserved_at'], 'job_lastcheck_reserved');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>oc_properties table updated successfully.</info>');
 			}
@@ -359,7 +422,10 @@ class AddMissingIndices extends Command {
 				$output->writeln('<info>Adding direct_edit_timestamp index to the oc_direct_edit table, this can take some time...</info>');
 
 				$table->addIndex(['timestamp'], 'direct_edit_timestamp');
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>oc_direct_edit table updated successfully.</info>');
 			}
diff --git a/core/Command/Db/AddMissingPrimaryKeys.php b/core/Command/Db/AddMissingPrimaryKeys.php
index 106a5901015..cbb99a69251 100644
--- a/core/Command/Db/AddMissingPrimaryKeys.php
+++ b/core/Command/Db/AddMissingPrimaryKeys.php
@@ -31,6 +31,7 @@ use OC\DB\SchemaWrapper;
 use OCP\IDBConnection;
 use Symfony\Component\Console\Command\Command;
 use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
 use Symfony\Component\Console\Output\OutputInterface;
 use Symfony\Component\EventDispatcher\EventDispatcherInterface;
 use Symfony\Component\EventDispatcher\GenericEvent;
@@ -61,11 +62,12 @@ class AddMissingPrimaryKeys extends Command {
 	protected function configure() {
 		$this
 			->setName('db:add-missing-primary-keys')
-			->setDescription('Add missing primary keys to the database tables');
+			->setDescription('Add missing primary keys to the database tables')
+			->addOption('dry-run', null, InputOption::VALUE_NONE, "Output the SQL queries instead of running them.");
 	}
 
 	protected function execute(InputInterface $input, OutputInterface $output): int {
-		$this->addCorePrimaryKeys($output);
+		$this->addCorePrimaryKeys($output, $input->getOption('dry-run'));
 
 		// Dispatch event so apps can also update indexes if needed
 		$event = new GenericEvent($output);
@@ -77,9 +79,10 @@ class AddMissingPrimaryKeys extends Command {
 	 * add missing indices to the share table
 	 *
 	 * @param OutputInterface $output
+	 * @param bool $dryRun If true, will return the sql queries instead of running them.
 	 * @throws \Doctrine\DBAL\Schema\SchemaException
 	 */
-	private function addCorePrimaryKeys(OutputInterface $output) {
+	private function addCorePrimaryKeys(OutputInterface $output, bool $dryRun): void {
 		$output->writeln('<info>Check primary keys.</info>');
 
 		$schema = new SchemaWrapper($this->connection);
@@ -93,7 +96,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('share_id_index')) {
 					$table->dropIndex('share_id_index');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>federated_reshares table updated successfully.</info>');
 			}
@@ -107,7 +113,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('mapping')) {
 					$table->dropIndex('mapping');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>systemtag_object_mapping table updated successfully.</info>');
 			}
@@ -121,7 +130,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('comments_marker_index')) {
 					$table->dropIndex('comments_marker_index');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>comments_read_markers table updated successfully.</info>');
 			}
@@ -135,7 +147,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('collres_unique_res')) {
 					$table->dropIndex('collres_unique_res');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>collres_resources table updated successfully.</info>');
 			}
@@ -149,7 +164,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('collres_unique_user')) {
 					$table->dropIndex('collres_unique_user');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>collres_accesscache table updated successfully.</info>');
 			}
@@ -163,7 +181,10 @@ class AddMissingPrimaryKeys extends Command {
 				if ($table->hasIndex('fce_fileid_idx')) {
 					$table->dropIndex('fce_fileid_idx');
 				}
-				$this->connection->migrateToSchema($schema->getWrappedSchema());
+				$sqlQueries = $this->connection->migrateToSchema($schema->getWrappedSchema(), $dryRun);
+				if ($dryRun && $sqlQueries !== null) {
+					$output->writeln($sqlQueries);
+				}
 				$updated = true;
 				$output->writeln('<info>filecache_extended table updated successfully.</info>');
 			}