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