From 4f01486da0e6d2d67e0ec7fd8b9dd8bf9df6af67 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Fri, 28 Jun 2024 17:08:46 +0200 Subject: [PATCH] feat: add commands for exporting current and expected database schema Signed-off-by: Robin Appelman --- core/Command/Db/ExpectedSchema.php | 68 ++++++++++++ core/Command/Db/ExportSchema.php | 44 ++++++++ core/Command/Db/SchemaEncoder.php | 115 ++++++++++++++++++++ core/register_command.php | 2 + lib/composer/composer/autoload_classmap.php | 3 + lib/composer/composer/autoload_static.php | 3 + lib/private/DB/MigrationService.php | 2 +- lib/private/DB/SchemaWrapper.php | 8 +- 8 files changed, 242 insertions(+), 3 deletions(-) create mode 100644 core/Command/Db/ExpectedSchema.php create mode 100644 core/Command/Db/ExportSchema.php create mode 100644 core/Command/Db/SchemaEncoder.php 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 @@ + + * 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 @@ + + * 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 @@ + + * 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; + } +} diff --git a/core/register_command.php b/core/register_command.php index 5185da496b6..fbf59ebfc06 100644 --- a/core/register_command.php +++ b/core/register_command.php @@ -66,6 +66,8 @@ if ($config->getSystemValueBool('installed', false)) { $application->add(Server::get(Command\Db\AddMissingColumns::class)); $application->add(Server::get(Command\Db\AddMissingIndices::class)); $application->add(Server::get(Command\Db\AddMissingPrimaryKeys::class)); + $application->add(Server::get(Command\Db\ExpectedSchema::class)); + $application->add(Server::get(Command\Db\ExportSchema::class)); if ($config->getSystemValueBool('debug', false)) { $application->add(Server::get(Command\Db\Migrations\StatusCommand::class)); diff --git a/lib/composer/composer/autoload_classmap.php b/lib/composer/composer/autoload_classmap.php index ae747f8d248..0f3ef42952e 100644 --- a/lib/composer/composer/autoload_classmap.php +++ b/lib/composer/composer/autoload_classmap.php @@ -1107,10 +1107,13 @@ return array( 'OC\\Core\\Command\\Db\\ConvertFilecacheBigInt' => $baseDir . '/core/Command/Db/ConvertFilecacheBigInt.php', 'OC\\Core\\Command\\Db\\ConvertMysqlToMB4' => $baseDir . '/core/Command/Db/ConvertMysqlToMB4.php', 'OC\\Core\\Command\\Db\\ConvertType' => $baseDir . '/core/Command/Db/ConvertType.php', + 'OC\\Core\\Command\\Db\\ExpectedSchema' => $baseDir . '/core/Command/Db/ExpectedSchema.php', + 'OC\\Core\\Command\\Db\\ExportSchema' => $baseDir . '/core/Command/Db/ExportSchema.php', 'OC\\Core\\Command\\Db\\Migrations\\ExecuteCommand' => $baseDir . '/core/Command/Db/Migrations/ExecuteCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\GenerateCommand' => $baseDir . '/core/Command/Db/Migrations/GenerateCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\MigrateCommand' => $baseDir . '/core/Command/Db/Migrations/MigrateCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\StatusCommand' => $baseDir . '/core/Command/Db/Migrations/StatusCommand.php', + 'OC\\Core\\Command\\Db\\SchemaEncoder' => $baseDir . '/core/Command/Db/SchemaEncoder.php', 'OC\\Core\\Command\\Encryption\\ChangeKeyStorageRoot' => $baseDir . '/core/Command/Encryption/ChangeKeyStorageRoot.php', 'OC\\Core\\Command\\Encryption\\DecryptAll' => $baseDir . '/core/Command/Encryption/DecryptAll.php', 'OC\\Core\\Command\\Encryption\\Disable' => $baseDir . '/core/Command/Encryption/Disable.php', diff --git a/lib/composer/composer/autoload_static.php b/lib/composer/composer/autoload_static.php index 45393c5481e..4ba270d8644 100644 --- a/lib/composer/composer/autoload_static.php +++ b/lib/composer/composer/autoload_static.php @@ -1140,10 +1140,13 @@ class ComposerStaticInit749170dad3f5e7f9ca158f5a9f04f6a2 'OC\\Core\\Command\\Db\\ConvertFilecacheBigInt' => __DIR__ . '/../../..' . '/core/Command/Db/ConvertFilecacheBigInt.php', 'OC\\Core\\Command\\Db\\ConvertMysqlToMB4' => __DIR__ . '/../../..' . '/core/Command/Db/ConvertMysqlToMB4.php', 'OC\\Core\\Command\\Db\\ConvertType' => __DIR__ . '/../../..' . '/core/Command/Db/ConvertType.php', + 'OC\\Core\\Command\\Db\\ExpectedSchema' => __DIR__ . '/../../..' . '/core/Command/Db/ExpectedSchema.php', + 'OC\\Core\\Command\\Db\\ExportSchema' => __DIR__ . '/../../..' . '/core/Command/Db/ExportSchema.php', 'OC\\Core\\Command\\Db\\Migrations\\ExecuteCommand' => __DIR__ . '/../../..' . '/core/Command/Db/Migrations/ExecuteCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\GenerateCommand' => __DIR__ . '/../../..' . '/core/Command/Db/Migrations/GenerateCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\MigrateCommand' => __DIR__ . '/../../..' . '/core/Command/Db/Migrations/MigrateCommand.php', 'OC\\Core\\Command\\Db\\Migrations\\StatusCommand' => __DIR__ . '/../../..' . '/core/Command/Db/Migrations/StatusCommand.php', + 'OC\\Core\\Command\\Db\\SchemaEncoder' => __DIR__ . '/../../..' . '/core/Command/Db/SchemaEncoder.php', 'OC\\Core\\Command\\Encryption\\ChangeKeyStorageRoot' => __DIR__ . '/../../..' . '/core/Command/Encryption/ChangeKeyStorageRoot.php', 'OC\\Core\\Command\\Encryption\\DecryptAll' => __DIR__ . '/../../..' . '/core/Command/Encryption/DecryptAll.php', 'OC\\Core\\Command\\Encryption\\Disable' => __DIR__ . '/../../..' . '/core/Command/Encryption/Disable.php', diff --git a/lib/private/DB/MigrationService.php b/lib/private/DB/MigrationService.php index 4b91b6f0996..19d1b240736 100644 --- a/lib/private/DB/MigrationService.php +++ b/lib/private/DB/MigrationService.php @@ -459,7 +459,7 @@ class MigrationService { * @return IMigrationStep * @throws \InvalidArgumentException */ - protected function createInstance($version) { + public function createInstance($version) { $class = $this->getClass($version); try { $s = \OCP\Server::get($class); diff --git a/lib/private/DB/SchemaWrapper.php b/lib/private/DB/SchemaWrapper.php index 8ff952b8710..5720e10fbdb 100644 --- a/lib/private/DB/SchemaWrapper.php +++ b/lib/private/DB/SchemaWrapper.php @@ -20,9 +20,13 @@ class SchemaWrapper implements ISchemaWrapper { /** @var array */ protected $tablesToDelete = []; - public function __construct(Connection $connection) { + public function __construct(Connection $connection, ?Schema $schema = null) { $this->connection = $connection; - $this->schema = $this->connection->createSchema(); + if ($schema) { + $this->schema = $schema; + } else { + $this->schema = $this->connection->createSchema(); + } } public function getWrappedSchema() { -- 2.39.5