diff options
author | dartcafe <github@dartcafe.de> | 2022-01-22 18:44:41 +0300 |
---|---|---|
committer | dartcafe <github@dartcafe.de> | 2022-01-23 03:09:40 +0300 |
commit | 53f9ef47724c0152b04c899eadab4002a41eed27 (patch) | |
tree | f7e328f097f9e03572eb5c8aabad46877d9cbe50 /lib | |
parent | cf8bb11b2e1d484bdf47c6e24cab1a65490133c4 (diff) |
add db recreate command
Signed-off-by: dartcafe <github@dartcafe.de>
Diffstat (limited to 'lib')
-rw-r--r-- | lib/Command/Db/Rebuild.php | 308 | ||||
-rw-r--r-- | lib/Migration/TableSchema.php | 11 |
2 files changed, 309 insertions, 10 deletions
diff --git a/lib/Command/Db/Rebuild.php b/lib/Command/Db/Rebuild.php new file mode 100644 index 00000000..03810afa --- /dev/null +++ b/lib/Command/Db/Rebuild.php @@ -0,0 +1,308 @@ +<?php
+/**
+ * @copyright Copyright (c) 2021 René Gieling <github@dartcafe.de>
+ *
+ * @author René Gieling <github@dartcafe.de>
+ *
+ * @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 OCA\Polls\Command\Db;
+
+use OC\DB\Connection;
+use OC\DB\SchemaWrapper;
+use OCP\IDBConnection;
+use Doctrine\DBAL\Types\Type;
+use OCA\Polls\Migration\TableSchema;
+use Symfony\Component\Console\Command\Command;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Output\OutputInterface;
+use Symfony\Component\Console\Question\ConfirmationQuestion;
+
+class Rebuild extends Command {
+ /** @var Connection */
+ private $connection;
+
+ public function __construct(Connection $connection) {
+ parent::__construct();
+ $this->connection = $connection;
+ }
+
+ protected function configure(): void {
+ $this
+ ->setName('polls:db:rebuild')
+ ->setDescription('Rebuilds poll\'s table structure');
+ }
+
+ protected function execute(InputInterface $input, OutputInterface $output): int {
+ if ($this->requestConfirmation($input, $output)) {
+ return 1;
+ }
+
+ // remove constraints and indices
+ $this->removeForeignKeys($output);
+ $this->removeGenericIndices($output);
+ $this->removeUniqueIndices($output);
+
+ // remove old tables and columns
+ $this->removeObsoleteTables($output);
+ $this->removeObsoleteColumns($output);
+
+ // validate and fix/create current table layout
+ $this->createOrUpdateSchema($output);
+
+ // recreate indices and constraints
+ $this->createIndices($output);
+ $this->createForeignKeyConstraints($output);
+
+ return 0;
+ }
+
+ private function requestConfirmation(InputInterface $input, OutputInterface $output) {
+ if ($input->isInteractive()) {
+ $helper = $this->getHelper('question');
+ $output->writeln('<comment>All polls tables will get checked against the current schema.</comment>');
+ $output->writeln('<comment>NO data migration will be executed, so make sure you have a backup of your database.</comment>');
+ $output->writeln('');
+
+ $question = new ConfirmationQuestion('Continue with the conversion (y/n)? [n] ', false);
+ if (!$helper->ask($input, $output, $question)) {
+ return 1;
+ }
+ }
+ return 0;
+ }
+
+ /**
+ * add an on delete fk contraint to all tables referencing the main polls table
+ */
+ private function createForeignKeyConstraints(OutputInterface $output): void {
+ $output->writeln('<comment>Add foreign key constraints</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ $parentTable = $schema->getTable(TableSchema::FK_PARENT_TABLE);
+
+ foreach (TableSchema::FK_CHILD_TABLES as $childTable) {
+ $table = $schema->getTable($childTable);
+ $table->addForeignKeyConstraint($parentTable, ['poll_id'], ['id'], ['onDelete' => 'CASCADE']);
+ $output->writeln('<info> - Created ' . TableSchema::FK_PARENT_TABLE . '[\'poll_id\'] <- ' . $childTable . '[\'id\'] </info>');
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+
+
+ /**
+ * Create index for $table
+ */
+ private function createIndices(OutputInterface $output): void {
+ $output->writeln('<comment>Add indices</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ foreach (TableSchema::UNIQUE_INDICES as $tableName => $values) {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+ if (!$table->hasIndex($values['name'])) {
+ if ($values['unique']) {
+ $table->addUniqueIndex($values['columns'], $values['name']);
+ $output->writeln('<info> - Added unique index ' . $values['name'] . ' to ' . $tableName . '</info>');
+ } else {
+ $table->addIndex($values['columns'], $values['name']);
+ $output->writeln('<info> - Added index ' . $values['name'] . ' to ' . $tableName . '</info>');
+ }
+ }
+ }
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+ /**
+ * Iterate over tables and make sure, the are created or updated
+ * according to the schema
+ */
+ private function createOrUpdateSchema(OutputInterface $output): void {
+ $output->writeln('<comment>Set db structure</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ foreach (TableSchema::TABLES as $tableName => $columns) {
+ $tableCreated = false;
+
+ if ($schema->hasTable($tableName)) {
+ $output->writeln('<info> - Validating table ' . $tableName . '</info>');
+ $table = $schema->getTable($tableName);
+ } else {
+ $output->writeln('<info> - Creating table ' . $tableName . '</info>');
+ $table = $schema->createTable($tableName);
+ $tableCreated = true;
+ }
+
+ foreach ($columns as $columnName => $columnDefinition) {
+ if ($table->hasColumn($columnName)) {
+ $column = $table->getColumn($columnName);
+ $column->setOptions($columnDefinition['options']);
+ if ($column->getType()->getName() !== $columnDefinition['type']) {
+ $output->writeln('<comment> Migrated type of ' . $tableName . '[\'' . $columnName . '\'] from ' . $column->getType()->getName() . ' to ' . $columnDefinition['type'] . '</comment>');
+ $column->setType(Type::getType($columnDefinition['type']));
+ }
+
+ // force change to current options definition
+ $table->changeColumn($columnName, $columnDefinition['options']);
+ } else {
+ $table->addColumn($columnName, $columnDefinition['type'], $columnDefinition['options']);
+ $output->writeln('<comment> Added ' . $tableName . ', ' . $columnName . ' (' . $columnDefinition['type'] . ')</comment>');
+ }
+ }
+
+ if ($tableCreated) {
+ $table->setPrimaryKey(['id']);
+ }
+ }
+
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+ private function removeObsoleteColumns(OutputInterface $output): void {
+ $output->writeln('<comment>Drop orphaned columns</comment>');
+ $schema = new SchemaWrapper($this->connection);
+ $dropped = false;
+
+ foreach (TableSchema::GONE_COLUMNS as $tableName => $columns) {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+
+ foreach ($columns as $columnName) {
+ if ($table->hasColumn($columnName)) {
+ $dropped = true;
+ $table->dropColumn($columnName);
+ $output->writeln('<info> - Dropped ' . $columnName . ' from ' . $tableName .'</info>');
+ }
+ }
+ }
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+
+ if (!$dropped) {
+ $output->writeln('<info> - No orphaned columns found</info>');
+ }
+ }
+
+ /**
+ * Remove obsolete tables if they still exist
+ */
+ private function removeObsoleteTables(OutputInterface $output): void {
+ $output->writeln('<comment>Drop orphaned tables</comment>');
+ $schema = new SchemaWrapper($this->connection);
+ $dropped = false;
+
+ foreach (TableSchema::GONE_TABLES as $tableName) {
+ if ($schema->hasTable($tableName)) {
+ $dropped = true;
+ $schema->dropTable($tableName);
+ $output->writeln('<info> - Dropped' . $tableName .'</info>');
+ }
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ if (!$dropped) {
+ $output->writeln('<info> - No orphaned tables found</info>');
+ }
+ }
+
+ /**
+ * remove all UNIQUE indices from $table
+ */
+ private function removeUniqueIndices(OutputInterface $output): void {
+ $output->writeln('<comment>Remove unique indices</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ foreach (TableSchema::UNIQUE_INDICES as $tableName => $value) {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+
+ foreach ($table->getIndexes() as $index) {
+ if (strpos($index->getName(), 'UNIQ_') === 0) {
+ $this->removeIndex($schema, $tableName, $index->getName());
+ $output->writeln('<info> - Removed ' . $index->getName() . ' from '. $tableName .'</info>');
+ }
+ }
+ }
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+ /**
+ * remove all UNIQUE indices from $table
+ */
+ private function removeGenericIndices(OutputInterface $output): void {
+ $output->writeln('<comment>Remove indices</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ foreach (TableSchema::FK_CHILD_TABLES as $tableName) {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+
+ foreach ($table->getIndexes() as $index) {
+ if (strpos($index->getName(), 'IDX_') === 0) {
+ $this->removeIndex($schema, $tableName, $index->getName());
+ $output->writeln('<info> - Removed ' . $index->getName() . ' from '. $tableName .'</info>');
+ }
+ }
+
+ }
+ }
+
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+ private function removeIndex(SchemaWrapper $schema, string $tableName, string $indexName): void {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+ if ($table->hasIndex($indexName)) {
+ $table->dropIndex($indexName);
+ }
+ }
+ }
+
+ /**
+ * remove all foreign keys from $tableName
+ */
+ private function removeForeignKeys(OutputInterface $output): void {
+ $output->writeln('<comment>Remove foreign key constraints</comment>');
+ $schema = new SchemaWrapper($this->connection);
+
+ foreach (TableSchema::FK_CHILD_TABLES as $tableName) {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+
+ foreach ($table->getForeignKeys() as $foreignKey) {
+ $this->removeForeignKey($schema, $tableName, $foreignKey->getName());
+ $output->writeln('<info> - Remove ' . $foreignKey->getName() . ' from '. $tableName .'</info>');
+ }
+ }
+ }
+ $this->connection->migrateToSchema($schema->getWrappedSchema());
+ }
+
+ /**
+ * remove a foreign key with $foreignKeyName from $tableName
+ */
+ private function removeForeignKey(SchemaWrapper $schema, string $tableName, string $foreignKeyName): void {
+ if ($schema->hasTable($tableName)) {
+ $table = $schema->getTable($tableName);
+ $table->removeForeignKey($foreignKeyName);
+ }
+ }
+}
diff --git a/lib/Migration/TableSchema.php b/lib/Migration/TableSchema.php index b943c788..a1dc6fbc 100644 --- a/lib/Migration/TableSchema.php +++ b/lib/Migration/TableSchema.php @@ -58,15 +58,6 @@ abstract class TableSchema { Vote::TABLE, ]; - public const UNIQUE_TABLES = [ - Log::TABLE, - Subscription::TABLE, - Option::TABLE, - Preferences::TABLE, - Share::TABLE, - Vote::TABLE, - ]; - public const UNIQUE_INDICES = [ Option::TABLE => ['name' => 'UNIQ_options', 'unique' => true, 'columns' => ['poll_id', 'poll_option_text', 'timestamp']], Log::TABLE => ['name' => 'UNIQ_unprocessed', 'unique' => true, 'columns' => ['processed', 'poll_id', 'user_id', 'message_id']], @@ -232,7 +223,7 @@ abstract class TableSchema { 'id' => ['type' => Types::INTEGER, 'options' => ['autoincrement' => true, 'notnull' => true]], 'poll_id' => ['type' => Types::INTEGER, 'options' => ['notnull' => true, 'default' => 0]], 'user_id' => ['type' => Types::STRING, 'options' => ['notnull' => false, 'default' => '', 'length' => 256]], - 'comment' => ['type' => Types::STRING, 'options' => ['notnull' => false, 'default' => '', 'length' => 1024]], + 'comment' => ['type' => Types::TEXT, 'options' => ['notnull' => false, 'default' => '', 'length' => 1024]], 'timestamp' => ['type' => Types::INTEGER, 'options' => ['notnull' => true, 'default' => 0]], ], |