From 3abcd13979660309f9a6d672d3dc64a7c6d784ab Mon Sep 17 00:00:00 2001 From: Bart Visscher Date: Tue, 11 Feb 2014 18:01:41 +0100 Subject: Allow converting from any db type --- core/command/db/convertfromsqlite.php | 237 ---------------------------------- core/command/db/converttype.php | 230 +++++++++++++++++++++++++++++++++ core/register_command.php | 2 +- 3 files changed, 231 insertions(+), 238 deletions(-) delete mode 100644 core/command/db/convertfromsqlite.php create mode 100644 core/command/db/converttype.php diff --git a/core/command/db/convertfromsqlite.php b/core/command/db/convertfromsqlite.php deleted file mode 100644 index 71706580382..00000000000 --- a/core/command/db/convertfromsqlite.php +++ /dev/null @@ -1,237 +0,0 @@ - - * This file is licensed under the Affero General Public License version 3 or - * later. - * See the COPYING-README file. - * - */ - -namespace OC\Core\Command\Db; - -use Symfony\Component\Console\Command\Command; -use Symfony\Component\Console\Input\InputArgument; -use Symfony\Component\Console\Input\InputInterface; -use Symfony\Component\Console\Input\InputOption; -use Symfony\Component\Console\Output\OutputInterface; - -class ConvertFromSqlite extends Command { - /** - * @var \OC\Config $config - */ - protected $config; - - /** - * @param \OC\Config $config - */ - public function __construct($config) { - $this->config = $config; - parent::__construct(); - } - - protected function configure() { - $this - ->setName('db:convert-from-sqlite') - ->setDescription('Convert the owncloud sqlite database to the newly configured one') - ->addArgument( - 'type', - InputArgument::REQUIRED, - 'the type of the database to convert to' - ) - ->addArgument( - 'username', - InputArgument::REQUIRED, - 'the username of the database to convert to' - ) - ->addArgument( - 'hostname', - InputArgument::REQUIRED, - 'the hostname of the database to convert to' - ) - ->addArgument( - 'database', - InputArgument::REQUIRED, - 'the name of the database to convert to' - ) - ->addOption( - 'port', - null, - InputOption::VALUE_REQUIRED, - 'the port of the database to convert to' - ) - ->addOption( - 'password', - null, - InputOption::VALUE_REQUIRED, - 'the password of the database to convert to. Will be asked when not specified' - ) - ->addOption( - 'clear-schema', - null, - InputOption::VALUE_NONE, - 'remove all tables from the destination database' - ) - ; - } - - private static $type2driver = array( - 'mysql' => 'pdo_mysql', - 'pgsql' => 'pdo_pgsql', - 'oci' => 'oci8', - 'mssql' => 'pdo_sqlsrv', - ); - protected function execute(InputInterface $input, OutputInterface $output) { - // connect 'from' database - $datadir = $this->config->getValue( "datadirectory", \OC::$SERVERROOT.'/data' ); - $name = $this->config->getValue( "dbname", "owncloud" ); - $dbfile = $datadir.'/'.$name.'.db'; - $connectionParams = array( - 'path' => $dbfile, - 'driver' => 'pdo_sqlite', - ); - $fromDB = \Doctrine\DBAL\DriverManager::getConnection($connectionParams); - - // connect 'to' database - $type = $input->getArgument('type'); - $username = $input->getArgument('username'); - $hostname = $input->getArgument('hostname'); - $dbname = $input->getArgument('database'); - - if (!isset(self::$type2driver[$type])) { - throw new InvalidArgumentException('Unknown type: '.$type); - } - if ($input->getOption('password')) { - $password = $input->getOption('password'); - } else { - // TODO: should be moved to the interact function - $dialog = $this->getHelperSet()->get('dialog'); - $password = $dialog->askHiddenResponse( - $output, - 'What is the database password?', - false - ); - } - $connectionParams = array( - 'driver' => self::$type2driver[$type], - 'user' => $username, - 'password' => $password, - 'host' => $hostname, - 'dbname' => $dbname, - ); - if ($input->getOption('port')) { - $connectionParams['port'] = $input->getOption('port'); - } - switch ($type) { - case 'mysql': - case 'mssql': - $connectionParams['charset'] = 'UTF8'; - break; - case 'oci': - $connectionParams['charset'] = 'AL32UTF8'; - break; - } - - $toDB = \Doctrine\DBAL\DriverManager::getConnection($connectionParams); - - // Clearing schema in new database - if ($input->getOption('clear-schema')) { - $schemaManager = $toDB->getSchemaManager(); - $toTables = $schemaManager->listTableNames(); - if (!empty($toTables)) { - $output->writeln('Clearing schema in new database'); - } - foreach($toTables as $table) { - $schemaManager->dropTable($table); - } - } - - // create tables in new database - $output->writeln('Creating schema in new database'); - $schemaManager = new \OC\DB\MDB2SchemaManager($toDB); - $schemaManager->createDbFromStructure(\OC::$SERVERROOT.'/db_structure.xml'); - $apps = \OC_App::getEnabledApps(); - foreach($apps as $app) { - if(file_exists(\OC_App::getAppPath($app).'/appinfo/database.xml')) { - $schemaManager->createDbFromStructure(\OC_App::getAppPath($app).'/appinfo/database.xml'); - } - } - - // get tables from 'to' database - $toTables = $this->getTables($toDB); - // get tables from 'from' database - $fromTables = $this->getTables($fromDB); - // warn/fail if there are more tables in 'from' database - $tables = array_diff($fromTables, $toTables); - if (!empty($tables)) { - $output->writeln('The following tables do NOT exist any more: '.join(', ', $tables).''); - $dialog = $this->getHelperSet()->get('dialog'); - if (!$dialog->askConfirmation( - $output, - 'Continue with the convertion?', - false - )) { - return; - } - } - // enable maintenance mode to prevent changes - $this->config->setValue('maintenance', true); - try { - // copy table rows - $tables = array_intersect($toTables, $fromTables); - foreach($tables as $table) { - $output->writeln($table); - $this->copyTable($fromDB, $toDB, $table, $output); - } - if ($type == 'pgsql') { - $sequences = $toDB->getSchemaManager()->listSequences(); - foreach($sequences as $sequence) { - $info = $toDB->fetchAssoc('SELECT table_schema, table_name, column_name ' - .'FROM information_schema.columns ' - .'WHERE column_default = ? AND table_catalog = ?', - array("nextval('".$sequence->getName()."'::regclass)", $dbname)); - $table_name = $info['table_name']; - $column_name = $info['column_name']; - $toDB->executeQuery("SELECT setval('" . $sequence->getName() . "', (SELECT MAX(" . $column_name . ") FROM " . $table_name . ")+1)"); - } - } - // save new database config - $dbhost = $hostname; - if ($input->getOption('port')) { - $dbhost = $hostname.':'.$input->getOption('port'); - } - $this->config->setValue('dbtype', $type); - $this->config->setValue('dbname', $dbname); - $this->config->setValue('dbhost', $dbhost); - $this->config->setValue('dbuser', $username); - $this->config->setValue('dbpassword', $password); - } catch(Exception $e) { - $this->config->setValue('maintenance', false); - throw $e; - } - $this->config->setValue('maintenance', false); - } - - private function getTables($db) { - $schemaManager = $db->getSchemaManager(); - return $schemaManager->listTableNames(); - } - - private function copyTable($fromDB, $toDB, $table, $output) { - $progress = $this->getHelperSet()->get('progress'); - $query = 'SELECT COUNT(*) FROM '.$table; - $count = $fromDB->fetchColumn($query); - $query = 'SELECT * FROM '.$table; - $statement = $fromDB->executeQuery($query); - $progress->start($output, $count); - $progress->setRedrawFrequency($count > 100 ? 5 : 1); - while($row = $statement->fetch()) { - $progress->advance(); - $data = array(); - foreach ($row as $columnName => $value) { - $data[$toDB->quoteIdentifier($columnName)] = $value; - } - $toDB->insert($table, $data); - } - $progress->finish(); - } -} diff --git a/core/command/db/converttype.php b/core/command/db/converttype.php new file mode 100644 index 00000000000..38527d3d55a --- /dev/null +++ b/core/command/db/converttype.php @@ -0,0 +1,230 @@ + + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + * + */ + +namespace OC\Core\Command\Db; + +use Symfony\Component\Console\Command\Command; +use Symfony\Component\Console\Input\InputArgument; +use Symfony\Component\Console\Input\InputInterface; +use Symfony\Component\Console\Input\InputOption; +use Symfony\Component\Console\Output\OutputInterface; + +class ConvertType extends Command { + /** + * @var \OC\Config $config + */ + protected $config; + + /** + * @param \OC\Config $config + */ + public function __construct($config) { + $this->config = $config; + parent::__construct(); + } + + protected function configure() { + $this + ->setName('db:convert-type') + ->setDescription('Convert the owncloud database to the newly configured one') + ->addArgument( + 'type', + InputArgument::REQUIRED, + 'the type of the database to convert to' + ) + ->addArgument( + 'username', + InputArgument::REQUIRED, + 'the username of the database to convert to' + ) + ->addArgument( + 'hostname', + InputArgument::REQUIRED, + 'the hostname of the database to convert to' + ) + ->addArgument( + 'database', + InputArgument::REQUIRED, + 'the name of the database to convert to' + ) + ->addOption( + 'port', + null, + InputOption::VALUE_REQUIRED, + 'the port of the database to convert to' + ) + ->addOption( + 'password', + null, + InputOption::VALUE_REQUIRED, + 'the password of the database to convert to. Will be asked when not specified' + ) + ->addOption( + 'clear-schema', + null, + InputOption::VALUE_NONE, + 'remove all tables from the destination database' + ) + ; + } + + private static $type2driver = array( + 'mysql' => 'pdo_mysql', + 'pgsql' => 'pdo_pgsql', + 'oci' => 'oci8', + 'mssql' => 'pdo_sqlsrv', + ); + protected function execute(InputInterface $input, OutputInterface $output) { + // connect 'from' database + $fromDB = \OC_DB::getConnection(); + + // connect 'to' database + $type = $input->getArgument('type'); + $username = $input->getArgument('username'); + $hostname = $input->getArgument('hostname'); + $dbname = $input->getArgument('database'); + + if (!isset(self::$type2driver[$type])) { + throw new InvalidArgumentException('Unknown type: '.$type); + } + if ($input->getOption('password')) { + $password = $input->getOption('password'); + } else { + // TODO: should be moved to the interact function + $dialog = $this->getHelperSet()->get('dialog'); + $password = $dialog->askHiddenResponse( + $output, + 'What is the database password?', + false + ); + } + $connectionParams = array( + 'driver' => self::$type2driver[$type], + 'user' => $username, + 'password' => $password, + 'host' => $hostname, + 'dbname' => $dbname, + ); + if ($input->getOption('port')) { + $connectionParams['port'] = $input->getOption('port'); + } + switch ($type) { + case 'mysql': + case 'mssql': + $connectionParams['charset'] = 'UTF8'; + break; + case 'oci': + $connectionParams['charset'] = 'AL32UTF8'; + break; + } + + $toDB = \Doctrine\DBAL\DriverManager::getConnection($connectionParams); + + // Clearing schema in new database + if ($input->getOption('clear-schema')) { + $schemaManager = $toDB->getSchemaManager(); + $toTables = $schemaManager->listTableNames(); + if (!empty($toTables)) { + $output->writeln('Clearing schema in new database'); + } + foreach($toTables as $table) { + $schemaManager->dropTable($table); + } + } + + // create tables in new database + $output->writeln('Creating schema in new database'); + $schemaManager = new \OC\DB\MDB2SchemaManager($toDB); + $schemaManager->createDbFromStructure(\OC::$SERVERROOT.'/db_structure.xml'); + $apps = \OC_App::getEnabledApps(); + foreach($apps as $app) { + if(file_exists(\OC_App::getAppPath($app).'/appinfo/database.xml')) { + $schemaManager->createDbFromStructure(\OC_App::getAppPath($app).'/appinfo/database.xml'); + } + } + + // get tables from 'to' database + $toTables = $this->getTables($toDB); + // get tables from 'from' database + $fromTables = $this->getTables($fromDB); + // warn/fail if there are more tables in 'from' database + $tables = array_diff($fromTables, $toTables); + if (!empty($tables)) { + $output->writeln('The following tables do NOT exist any more: '.join(', ', $tables).''); + $dialog = $this->getHelperSet()->get('dialog'); + if (!$dialog->askConfirmation( + $output, + 'Continue with the convertion?', + false + )) { + return; + } + } + // enable maintenance mode to prevent changes + $this->config->setValue('maintenance', true); + try { + // copy table rows + $tables = array_intersect($toTables, $fromTables); + foreach($tables as $table) { + $output->writeln($table); + $this->copyTable($fromDB, $toDB, $table, $output); + } + if ($type == 'pgsql') { + $sequences = $toDB->getSchemaManager()->listSequences(); + foreach($sequences as $sequence) { + $info = $toDB->fetchAssoc('SELECT table_schema, table_name, column_name ' + .'FROM information_schema.columns ' + .'WHERE column_default = ? AND table_catalog = ?', + array("nextval('".$sequence->getName()."'::regclass)", $dbname)); + $table_name = $info['table_name']; + $column_name = $info['column_name']; + $toDB->executeQuery("SELECT setval('" . $sequence->getName() . "', (SELECT MAX(" . $column_name . ") FROM " . $table_name . ")+1)"); + } + } + // save new database config + $dbhost = $hostname; + if ($input->getOption('port')) { + $dbhost = $hostname.':'.$input->getOption('port'); + } + $this->config->setValue('dbtype', $type); + $this->config->setValue('dbname', $dbname); + $this->config->setValue('dbhost', $dbhost); + $this->config->setValue('dbuser', $username); + $this->config->setValue('dbpassword', $password); + } catch(Exception $e) { + $this->config->setValue('maintenance', false); + throw $e; + } + $this->config->setValue('maintenance', false); + } + + private function getTables($db) { + $schemaManager = $db->getSchemaManager(); + return $schemaManager->listTableNames(); + } + + private function copyTable($fromDB, $toDB, $table, $output) { + $progress = $this->getHelperSet()->get('progress'); + $query = 'SELECT COUNT(*) FROM '.$table; + $count = $fromDB->fetchColumn($query); + $query = 'SELECT * FROM '.$table; + $statement = $fromDB->executeQuery($query); + $progress->start($output, $count); + $progress->setRedrawFrequency($count > 100 ? 5 : 1); + while($row = $statement->fetch()) { + $progress->advance(); + $data = array(); + foreach ($row as $columnName => $value) { + $data[$toDB->quoteIdentifier($columnName)] = $value; + } + $toDB->insert($table, $data); + } + $progress->finish(); + } +} diff --git a/core/register_command.php b/core/register_command.php index 736953094b1..a3833214c21 100644 --- a/core/register_command.php +++ b/core/register_command.php @@ -9,7 +9,7 @@ /** @var $application Symfony\Component\Console\Application */ $application->add(new OC\Core\Command\Status); $application->add(new OC\Core\Command\Db\GenerateChangeScript()); -$application->add(new OC\Core\Command\Db\ConvertFromSqlite(OC_Config::getObject())); +$application->add(new OC\Core\Command\Db\ConvertType(OC_Config::getObject())); $application->add(new OC\Core\Command\Upgrade()); $application->add(new OC\Core\Command\Maintenance\SingleUser()); $application->add(new OC\Core\Command\App\Disable()); -- cgit v1.2.3