1<?php 2/** 3 * @author Andreas Fischer <bantu@owncloud.com> 4 * @author Bart Visscher <bartv@thisnet.nl> 5 * @author Joas Schilling <coding@schilljs.com> 6 * @author Morris Jobke <hey@morrisjobke.de> 7 * @author tbelau666 <thomas.belau@gmx.de> 8 * @author Thomas Müller <thomas.mueller@tmit.eu> 9 * 10 * @copyright Copyright (c) 2018, ownCloud GmbH 11 * @license AGPL-3.0 12 * 13 * This code is free software: you can redistribute it and/or modify 14 * it under the terms of the GNU Affero General Public License, version 3, 15 * as published by the Free Software Foundation. 16 * 17 * This program is distributed in the hope that it will be useful, 18 * but WITHOUT ANY WARRANTY; without even the implied warranty of 19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 20 * GNU Affero General Public License for more details. 21 * 22 * You should have received a copy of the GNU Affero General Public License, version 3, 23 * along with this program. If not, see <http://www.gnu.org/licenses/> 24 * 25 */ 26 27namespace OC\Core\Command\Db; 28 29use Doctrine\DBAL\DBALException; 30use Doctrine\DBAL\Schema\Table; 31use Doctrine\DBAL\Types\Type; 32use OC\DB\MDB2SchemaManager; 33use OCP\App\IAppManager; 34use OCP\IConfig; 35use OC\DB\Connection; 36use OC\DB\ConnectionFactory; 37use OC\DB\MigrationService; 38use OCP\DB\QueryBuilder\IQueryBuilder; 39use Symfony\Component\Console\Command\Command; 40use Symfony\Component\Console\Helper\ProgressBar; 41use Symfony\Component\Console\Input\InputArgument; 42use Symfony\Component\Console\Input\InputInterface; 43use Symfony\Component\Console\Input\InputOption; 44use Symfony\Component\Console\Output\OutputInterface; 45use Symfony\Component\Console\Question\Question; 46 47class ConvertType extends Command { 48 /** @var \OCP\IConfig */ 49 protected $config; 50 51 /** @var string */ 52 protected $targetType; 53 54 /** @var string */ 55 protected $targetHostname; 56 57 /** @var string */ 58 protected $targetPort; 59 60 /** @var string */ 61 protected $targetUsername; 62 63 /** @var string */ 64 protected $targetPassword; 65 66 /** @var string */ 67 protected $targetDatabase; 68 69 /** @var string */ 70 protected $targetTablePrefix; 71 72 /** 73 * @var \OC\DB\ConnectionFactory 74 */ 75 protected $connectionFactory; 76 77 /** @var IAppManager */ 78 protected $appManager; 79 80 /** @var string[][] */ 81 protected $tableColumnTypes; 82 83 /** 84 * @param \OCP\IConfig $config 85 * @param \OC\DB\ConnectionFactory $connectionFactory 86 * @param IAppManager $appManager 87 */ 88 public function __construct(IConfig $config, ConnectionFactory $connectionFactory, IAppManager $appManager) { 89 $this->config = $config; 90 $this->connectionFactory = $connectionFactory; 91 $this->appManager = $appManager; 92 parent::__construct(); 93 } 94 95 protected function configure() { 96 $this 97 ->setName('db:convert-type') 98 ->setDescription('Convert the ownCloud database to the newly configured one. This feature is currently experimental.') 99 ->addArgument( 100 'type', 101 InputArgument::REQUIRED, 102 'The type of the database to convert to.' 103 ) 104 ->addArgument( 105 'username', 106 InputArgument::REQUIRED, 107 'The username of the database to convert to.' 108 ) 109 ->addArgument( 110 'hostname', 111 InputArgument::REQUIRED, 112 'The hostname of the database to convert to.' 113 ) 114 ->addArgument( 115 'database', 116 InputArgument::REQUIRED, 117 'The name of the database to convert to.' 118 ) 119 ->addOption( 120 'port', 121 null, 122 InputOption::VALUE_REQUIRED, 123 'The port of the database to convert to.' 124 ) 125 ->addOption( 126 'password', 127 null, 128 InputOption::VALUE_REQUIRED, 129 'The password of the database to convert to. Will be asked when not specified. Can also be passed via stdin.' 130 ) 131 ->addOption( 132 'clear-schema', 133 null, 134 InputOption::VALUE_NONE, 135 'Remove all tables from the destination database.' 136 ) 137 ->addOption( 138 'all-apps', 139 null, 140 InputOption::VALUE_NONE, 141 'Whether to create schema for all apps instead of only installed apps.' 142 ) 143 ->addOption( 144 'chunk-size', 145 null, 146 InputOption::VALUE_REQUIRED, 147 'The maximum number of database rows to handle in a single query, bigger tables will be handled in chunks of this size. Lower this if the process runs out of memory during conversion.', 148 1000 149 ) 150 ; 151 } 152 153 /** 154 * @param InputInterface $input 155 */ 156 protected function validateInput(InputInterface $input) { 157 if ($this->targetType === 'sqlite3') { 158 throw new \InvalidArgumentException( 159 'Converting to SQLite (sqlite3) is currently not supported.' 160 ); 161 } 162 163 if ($this->targetType === $this->config->getSystemValue('dbtype', '')) { 164 throw new \InvalidArgumentException( 165 \sprintf('Can not convert from %1$s to %1$s.', $this->targetType) 166 ); 167 } 168 if ($this->targetType === 'oci' && $input->getOption('clear-schema')) { 169 // Doctrine unconditionally tries (at least in version 2.3) 170 // to drop sequence triggers when dropping a table, even though 171 // such triggers may not exist. This results in errors like 172 // "ORA-04080: trigger 'OC_STORAGES_AI_PK' does not exist". 173 throw new \InvalidArgumentException( 174 'The --clear-schema option is not supported when converting to Oracle (oci).' 175 ); 176 } 177 } 178 179 /** 180 * @param InputInterface $input 181 * @param OutputInterface $output 182 * return string|null 183 */ 184 protected function readPassword(InputInterface $input, OutputInterface $output) { 185 // Explicitly specified password 186 if ($input->getOption('password')) { 187 return $input->getOption('password'); 188 } 189 190 // Read from stdin. stream_set_blocking is used to prevent blocking 191 // when nothing is passed via stdin. 192 \stream_set_blocking(STDIN, 0); 193 $password = \file_get_contents('php://stdin'); 194 \stream_set_blocking(STDIN, 1); 195 if (\trim($password) !== '') { 196 return $password; 197 } 198 199 // Read password by interacting 200 if ($input->isInteractive()) { 201 /** @var $dialog \Symfony\Component\Console\Helper\QuestionHelper */ 202 $dialog = $this->getHelperSet()->get('question'); 203 '@phan-var \Symfony\Component\Console\Helper\QuestionHelper $dialog'; 204 $q = new Question('<question>Enter a password to access a target database: </question>', false); 205 $q->setHidden(true); 206 $password = $dialog->ask($input, $output, $q); 207 return $password; 208 } 209 } 210 211 /** 212 * @param InputInterface $input 213 * @param OutputInterface $output 214 * @return int|null|void 215 * @throws \Exception 216 */ 217 protected function execute(InputInterface $input, OutputInterface $output) { 218 $output->writeln('<info>This feature is currently experimental.</info>'); 219 $this->targetType = $this->connectionFactory->normalizeType($input->getArgument('type')); 220 $this->targetHostname = $input->getArgument('hostname'); 221 $this->targetPort = $input->getOption('port'); 222 $this->targetUsername = $input->getArgument('username'); 223 $this->targetDatabase = $input->getArgument('database'); 224 $this->targetTablePrefix = $this->config->getSystemValue('dbtableprefix', 'oc_'); 225 226 $this->validateInput($input); 227 $this->targetPassword = $this->readPassword($input, $output); 228 229 $fromDB = \OC::$server->getDatabaseConnection(); 230 $toDB = $this->getToDBConnection(); 231 232 if ($input->getOption('clear-schema')) { 233 $this->clearSchema($toDB, $input, $output); 234 } 235 236 $this->createSchema($fromDB, $toDB, $input, $output); 237 238 $toTables = $this->getTables($toDB); 239 $fromTables = $this->getTables($fromDB); 240 241 // warn/fail if there are more tables in 'from' database 242 $extraFromTables = \array_diff($fromTables, $toTables); 243 if (!empty($extraFromTables)) { 244 $output->writeln('<comment>The following tables will not be converted:</comment>'); 245 $output->writeln($extraFromTables); 246 if (!$input->getOption('all-apps')) { 247 $output->writeln('<comment>Please note that tables belonging to available but currently not installed apps</comment>'); 248 $output->writeln('<comment>can be included by specifying the --all-apps option.</comment>'); 249 } 250 /** @var $dialog \Symfony\Component\Console\Helper\QuestionHelper */ 251 $dialog = $this->getHelperSet()->get('question'); 252 '@phan-var \Symfony\Component\Console\Helper\QuestionHelper $dialog'; 253 $continue = $dialog->ask($input, $output, new Question('<question>Continue with the conversion (y/n)? [n] </question>', false)); 254 if ($continue !== 'y') { 255 return; 256 } 257 } 258 $intersectingTables = \array_intersect($toTables, $fromTables); 259 $this->convertDB($fromDB, $toDB, $intersectingTables, $input, $output); 260 } 261 262 /** 263 * @param Connection $fromDB 264 * @param Connection $toDB 265 * @param InputInterface $input 266 * @param OutputInterface $output 267 */ 268 protected function createSchema(Connection $fromDB, Connection $toDB, InputInterface $input, OutputInterface $output) { 269 $output->writeln('<info>Creating schema in new database</info>'); 270 $schemaManager = new MDB2SchemaManager($toDB); 271 $schemaManager->createDbFromStructure(\OC::$SERVERROOT.'/db_structure.xml'); 272 273 $this->replayMigrations($fromDB, $toDB, 'core'); 274 275 $apps = $this->getExistingApps($input->getOption('all-apps')); 276 foreach ($apps as $app) { 277 // Some apps has a cheat initial migration that creates schema from database.xml 278 // So the app can have database.xml and use migrations in the same time 279 if ($this->appHasMigrations($app)) { 280 $this->replayMigrations($fromDB, $toDB, $app); 281 } elseif (\file_exists($this->appManager->getAppPath($app).'/appinfo/database.xml')) { 282 $schemaManager->createDbFromStructure($this->appManager->getAppPath($app).'/appinfo/database.xml'); 283 } 284 } 285 } 286 287 /** 288 * @param bool $enabledOnly 289 * @return string[] 290 */ 291 protected function getExistingApps($enabledOnly) { 292 $apps = $enabledOnly ? $this->appManager->getInstalledApps() : $this->appManager->getAllApps(); 293 // filter apps with missing code 294 $existingApps = \array_filter( 295 $apps, 296 function ($appId) { 297 return $this->appManager->getAppPath($appId) !== false; 298 } 299 ); 300 301 return $existingApps; 302 } 303 304 /** 305 * @param Connection $fromDB 306 * @param Connection $toDB 307 * @param $app 308 * @throws \Exception 309 * @throws \OC\NeedsUpdateException 310 */ 311 protected function replayMigrations(Connection $fromDB, Connection $toDB, $app) { 312 if ($app !== 'core') { 313 \OC_App::loadApp($app); 314 } 315 $sourceMigrationService = new MigrationService($app, $fromDB); 316 $currentMigration = $sourceMigrationService->getMigration('current'); 317 if ($currentMigration !== '0') { 318 $targetMigrationService = new MigrationService($app, $toDB); 319 $targetMigrationService->migrate($currentMigration); 320 } 321 } 322 323 /** 324 * @param string $app 325 * @return bool 326 */ 327 protected function appHasMigrations($app) { 328 return \is_dir($this->appManager->getAppPath($app).'/appinfo/Migrations'); 329 } 330 331 /** 332 * @param Connection $db 333 * @param InputInterface $input 334 * @param OutputInterface $output 335 */ 336 protected function clearSchema(Connection $db, InputInterface $input, OutputInterface $output) { 337 $toTables = $this->getTables($db); 338 if (!empty($toTables)) { 339 $output->writeln('<info>Clearing schema in new database</info>'); 340 foreach ($toTables as $table) { 341 $db->getSchemaManager()->dropTable($table); 342 } 343 } 344 } 345 346 /** 347 * @param Connection $db 348 * @return string[] 349 */ 350 protected function getTables(Connection $db) { 351 $filterExpression = '/^' . \preg_quote($this->targetTablePrefix) . '/'; 352 $db->getConfiguration()-> 353 setFilterSchemaAssetsExpression($filterExpression); 354 return $db->getSchemaManager()->listTableNames(); 355 } 356 357 /** 358 * @param Connection $fromDB 359 * @param Connection $toDB 360 * @param Table $table 361 * @param InputInterface $input 362 * @param OutputInterface $output 363 */ 364 protected function copyTable(Connection $fromDB, Connection $toDB, Table $table, InputInterface $input, OutputInterface $output) { 365 $tableName = $table->getName(); 366 $chunkSize = $input->getOption('chunk-size'); 367 368 $progress = new ProgressBar($output); 369 370 $query = $fromDB->getQueryBuilder(); 371 $query->automaticTablePrefix(false); 372 $query->selectAlias($query->createFunction('COUNT(*)'), 'num_entries') 373 ->from($tableName); 374 $result = $query->execute(); 375 $count = $result->fetchColumn(); 376 $result->closeCursor(); 377 378 $numChunks = \ceil($count/$chunkSize); 379 if ($numChunks > 1) { 380 $output->writeln('chunked query, ' . $numChunks . ' chunks'); 381 } 382 383 $progress->start($count); 384 $redraw = $count > $chunkSize ? 100 : ($count > 100 ? 5 : 1); 385 $progress->setRedrawFrequency($redraw); 386 387 $query = $fromDB->getQueryBuilder(); 388 $query->automaticTablePrefix(false); 389 $query->select('*') 390 ->from($tableName) 391 ->setMaxResults($chunkSize); 392 393 try { 394 // Primary key is faster 395 $orderColumns = $table->getPrimaryKeyColumns(); 396 } catch (DBALException $e) { 397 // But the table can have no primary key in this case we fallback to the column order 398 $orderColumns = []; 399 foreach ($table->getColumns() as $column) { 400 $orderColumns[] = $column->getName(); 401 } 402 } 403 404 foreach ($orderColumns as $column) { 405 $query->addOrderBy($column); 406 } 407 408 $insertQuery = $toDB->getQueryBuilder(); 409 $insertQuery->automaticTablePrefix(false); 410 $insertQuery->insert($tableName); 411 $parametersCreated = false; 412 413 for ($chunk = 0; $chunk < $numChunks; $chunk++) { 414 $query->setFirstResult($chunk * $chunkSize); 415 416 $result = $query->execute(); 417 418 while ($row = $result->fetch()) { 419 $progress->advance(); 420 if (!$parametersCreated) { 421 foreach ($row as $key => $value) { 422 $insertQuery->setValue($key, $insertQuery->createParameter($key)); 423 } 424 $parametersCreated = true; 425 } 426 427 foreach ($row as $key => $value) { 428 $insertQuery->setParameter($key, $value, $this->tableColumnTypes[$tableName][$key]); 429 } 430 $insertQuery->execute(); 431 } 432 $result->closeCursor(); 433 } 434 $progress->finish(); 435 $output->writeln(""); 436 } 437 438 /** 439 * @param Table $table 440 * @return mixed 441 */ 442 protected function getColumnTypes(Table $table) { 443 $tableName = $table->getName(); 444 foreach ($table->getColumns() as $column) { 445 $columnName = $column->getName(); 446 $type = $table->getColumn($columnName)->getType()->getName(); 447 switch ($type) { 448 case Type::BLOB: 449 case Type::TEXT: 450 $this->tableColumnTypes[$tableName][$columnName] = IQueryBuilder::PARAM_LOB; 451 break; 452 default: 453 $this->tableColumnTypes[$tableName][$columnName] = null; 454 } 455 } 456 return $this->tableColumnTypes[$tableName]; 457 } 458 459 /** 460 * @param Connection $fromDB 461 * @param Connection $toDB 462 * @param string[] $tables 463 * @param InputInterface $input 464 * @param OutputInterface $output 465 * @throws \Exception 466 */ 467 protected function convertDB(Connection $fromDB, Connection $toDB, array $tables, InputInterface $input, OutputInterface $output) { 468 $this->config->setSystemValue('maintenance', true); 469 try { 470 $fromSchema = $fromDB->createSchema(); 471 // copy table rows 472 foreach ($tables as $tableName) { 473 $table = $fromSchema->getTable($tableName); 474 if ($tableName === $toDB->getPrefix() . 'migrations') { 475 $output->writeln( 476 \sprintf( 477 '<info>Skipping copying data for the table "%s", it will be populated later.</info>', 478 $tableName 479 ) 480 ); 481 continue; 482 } 483 $output->writeln($tableName); 484 $this->tableColumnTypes[$tableName] = $this->getColumnTypes($table); 485 $this->copyTable($fromDB, $toDB, $table, $input, $output); 486 } 487 if ($this->targetType === 'pgsql') { 488 $tools = new \OC\DB\PgSqlTools($this->config); 489 $tools->resynchronizeDatabaseSequences($toDB); 490 } 491 // save new database config 492 $this->saveDBInfo(); 493 } catch (\Exception $e) { 494 $this->config->setSystemValue('maintenance', false); 495 throw $e; 496 } 497 $this->config->setSystemValue('maintenance', false); 498 } 499 500 /** 501 * @return Connection 502 */ 503 protected function getToDBConnection() { 504 $connectionParams = [ 505 'host' => $this->targetHostname, 506 'user' => $this->targetUsername, 507 'password' => $this->targetPassword, 508 'dbname' => $this->targetDatabase, 509 'tablePrefix' => $this->targetTablePrefix, 510 ]; 511 if ($this->targetPort !== null) { 512 $connectionParams['port'] = $this->targetPort; 513 } 514 return $this->connectionFactory->getConnection($this->targetType, $connectionParams); 515 } 516 517 /** 518 * 519 */ 520 protected function saveDBInfo() { 521 $dbHost = $this->targetHostname; 522 if ($this->targetPort !== null) { 523 $dbHost .= ':' . $this->targetPort; 524 } 525 526 $this->config->setSystemValues([ 527 'dbtype' => $this->targetType, 528 'dbname' => $this->targetDatabase, 529 'dbhost' => $dbHost, 530 'dbuser' => $this->targetUsername, 531 'dbpassword' => $this->targetPassword, 532 ]); 533 } 534} 535