feat: add commands for exporting current and expected database schema

Signed-off-by: Robin Appelman <robin@icewind.nl>

3 files changed, 227 insertions, 0 deletions

diff --git a/core/Command/Db/ExpectedSchema.php b/core/Command/Db/ExpectedSchema.php
new file mode 100644
index 00000000000..1f35daba089
--- /dev/null
+++ b/core/Command/Db/ExpectedSchema.php
@@ -0,0 +1,68 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl>
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\Core\Command\Db;
+
+use Doctrine\DBAL\Schema\Schema;
+use OC\Core\Command\Base;
+use OC\DB\Connection;
+use OC\DB\MigrationService;
+use OC\DB\SchemaWrapper;
+use OC\Migration\NullOutput;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
+use Symfony\Component\Console\Output\OutputInterface;
+
+class ExpectedSchema extends Base {
+	public function __construct(
+		protected Connection $connection,
+	) {
+		parent::__construct();
+	}
+
+	protected function configure(): void {
+		$this
+			->setName('db:schema:expected')
+			->setDescription('Export the expected database schema for a fresh installation')
+			->setHelp("Note that the expected schema might not exactly match the exported live schema as the expected schema doesn't take into account any database wide settings or defaults.")
+			->addOption('sql', null, InputOption::VALUE_NONE, 'Dump the SQL statements for creating the expected schema');
+		parent::configure();
+	}
+
+	protected function execute(InputInterface $input, OutputInterface $output): int {
+		$schema = new Schema();
+
+		$this->applyMigrations('core', $schema);
+
+		$apps = \OC_App::getEnabledApps();
+		foreach ($apps as $app) {
+			$this->applyMigrations($app, $schema);
+		}
+
+		$sql = $input->getOption('sql');
+		if ($sql) {
+			$output->writeln($schema->toSql($this->connection->getDatabasePlatform()));
+		} else {
+			$encoder = new SchemaEncoder();
+			$this->writeArrayInOutputFormat($input, $output, $encoder->encodeSchema($schema, $this->connection->getDatabasePlatform()));
+		}
+
+		return 0;
+	}
+
+	private function applyMigrations(string $app, Schema $schema): void {
+		$output = new NullOutput();
+		$ms = new MigrationService($app, $this->connection, $output);
+		foreach ($ms->getAvailableVersions() as $version) {
+			$migration = $ms->createInstance($version);
+			$migration->changeSchema($output, function () use (&$schema) {
+				return new SchemaWrapper($this->connection, $schema);
+			}, []);
+		}
+	}
+}
diff --git a/core/Command/Db/ExportSchema.php b/core/Command/Db/ExportSchema.php
new file mode 100644
index 00000000000..581824eea5f
--- /dev/null
+++ b/core/Command/Db/ExportSchema.php
@@ -0,0 +1,44 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl>
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\Core\Command\Db;
+
+use OC\Core\Command\Base;
+use OCP\IDBConnection;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
+use Symfony\Component\Console\Output\OutputInterface;
+
+class ExportSchema extends Base {
+	public function __construct(
+		protected IDBConnection $connection,
+	) {
+		parent::__construct();
+	}
+
+	protected function configure(): void {
+		$this
+			->setName('db:schema:export')
+			->setDescription('Export the current database schema')
+			->addOption('sql', null, InputOption::VALUE_NONE, 'Dump the SQL statements for creating a copy of the schema');
+		parent::configure();
+	}
+
+	protected function execute(InputInterface $input, OutputInterface $output): int {
+		$schema = $this->connection->createSchema();
+		$sql = $input->getOption('sql');
+		if ($sql) {
+			$output->writeln($schema->toSql($this->connection->getDatabasePlatform()));
+		} else {
+			$encoder = new SchemaEncoder();
+			$this->writeArrayInOutputFormat($input, $output, $encoder->encodeSchema($schema, $this->connection->getDatabasePlatform()));
+		}
+
+		return 0;
+	}
+}
diff --git a/core/Command/Db/SchemaEncoder.php b/core/Command/Db/SchemaEncoder.php
new file mode 100644
index 00000000000..d36a34e8583
--- /dev/null
+++ b/core/Command/Db/SchemaEncoder.php
@@ -0,0 +1,115 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl>
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\Core\Command\Db;
+
+use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
+use Doctrine\DBAL\Platforms\AbstractPlatform;
+use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
+use Doctrine\DBAL\Schema\Schema;
+use Doctrine\DBAL\Schema\Table;
+use Doctrine\DBAL\Types\PhpIntegerMappingType;
+use Doctrine\DBAL\Types\Type;
+
+class SchemaEncoder {
+	/**
+	 * Encode a DBAL schema to json, performing some normalization based on the database platform
+	 *
+	 * @param Schema $schema
+	 * @param AbstractPlatform $platform
+	 * @return array
+	 */
+	public function encodeSchema(Schema $schema, AbstractPlatform $platform): array {
+		$encoded = ['tables' => [], 'sequences' => []];
+		foreach ($schema->getTables() as $table) {
+			$encoded[$table->getName()] = $this->encodeTable($table, $platform);
+		}
+		ksort($encoded);
+		return $encoded;
+	}
+
+	/**
+	 * @psalm-type ColumnArrayType =
+	 */
+	private function encodeTable(Table $table, AbstractPlatform $platform): array {
+		$encoded = ['columns' => [], 'indexes' => []];
+		foreach ($table->getColumns() as $column) {
+			/**
+			 * @var array{
+			 *     name: string,
+			 *     default: mixed,
+			 *     notnull: bool,
+			 *     length: ?int,
+			 *     precision: int,
+			 *     scale: int,
+			 *     unsigned: bool,
+			 *     fixed: bool,
+			 *     autoincrement: bool,
+			 *     comment: string,
+			 *     columnDefinition: ?string,
+			 *     collation?: string,
+			 *     charset?: string,
+			 *     jsonb?: bool,
+			 * } $data
+			 **/
+			$data = $column->toArray();
+			$data['type'] = Type::getTypeRegistry()->lookupName($column->getType());
+			$data['default'] = $column->getType()->convertToPHPValue($column->getDefault(), $platform);
+			if ($platform instanceof PostgreSQLPlatform) {
+				$data['unsigned'] = false;
+				if ($column->getType() instanceof PhpIntegerMappingType) {
+					$data['length'] = null;
+				}
+				unset($data['jsonb']);
+			} elseif ($platform instanceof AbstractMySqlPlatform) {
+				if ($column->getType() instanceof PhpIntegerMappingType) {
+					$data['length'] = null;
+				} elseif (in_array($data['type'], ['text', 'blob', 'datetime', 'float', 'json'])) {
+					$data['length'] = 0;
+				}
+				unset($data['collation']);
+				unset($data['charset']);
+			}
+			if ($data['type'] === 'string' && $data['length'] === null) {
+				$data['length'] = 255;
+			}
+			$encoded['columns'][$column->getName()] = $data;
+		}
+		ksort($encoded['columns']);
+		foreach ($table->getIndexes() as $index) {
+			$options = $index->getOptions();
+			if (isset($options['lengths']) && count(array_filter($options['lengths'])) === 0) {
+				unset($options['lengths']);
+			}
+			if ($index->isPrimary()) {
+				if ($platform instanceof PostgreSqlPlatform) {
+					$name = $table->getName() . '_pkey';
+				} elseif ($platform instanceof AbstractMySQLPlatform) {
+					$name = "PRIMARY";
+				} else {
+					$name = $index->getName();
+				}
+			} else {
+				$name = $index->getName();
+			}
+			if ($platform instanceof PostgreSqlPlatform) {
+				$name = strtolower($name);
+			}
+			$encoded['indexes'][$name] = [
+				'name' => $name,
+				'columns' => $index->getColumns(),
+				'unique' => $index->isUnique(),
+				'primary' => $index->isPrimary(),
+				'flags' => $index->getFlags(),
+				'options' => $options,
+			];
+		}
+		ksort($encoded['indexes']);
+		return $encoded;
+	}
+}