/** * Backup task */ protected function runMain() { // ################## // Option specific runners // ################## $runRsync = true; $runMysql = true; if ($this->input->getOption('mysql') || $this->input->getOption('rsync')) { // don't run rsync if not specifiecd $runRsync = $this->input->getOption('rsync'); // don't run mysql if not specifiecd $runMysql = $this->input->getOption('mysql'); } // ################## // Run tasks // ################## // Check database connection if ($runMysql && $this->contextConfig->exists('mysql')) { DatabaseConnection::ping(); } // Sync files with rsync to local storage if ($runRsync && $this->contextConfig->exists('rsync')) { $this->output->writeln('<h1>Starting FILE sync</h1>'); $this->runTaskRsync(); } // Sync database to local server if ($runMysql && $this->contextConfig->exists('mysql')) { $this->output->writeln('<h1>Starting MYSQL sync</h1>'); $this->runTaskDatabase(); } }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $database = $input->getArgument('db'); $dumpFile = $input->getArgument('file'); if (!is_file($dumpFile) || !is_readable($dumpFile)) { $output->writeln('<p-error>File is not readable</p-error>'); return 1; } $dumpFileType = PhpUtility::getMimeType($dumpFile); $output->writeln('<h2>Restoring dump "' . $dumpFile . '" into database "' . $database . '"</h2>'); if (DatabaseConnection::databaseExists($database)) { // Dropping $output->writeln('<p>Dropping database</p>'); $query = 'DROP DATABASE IF EXISTS ' . DatabaseConnection::sanitizeSqlDatabase($database); DatabaseConnection::exec($query); } // Creating $output->writeln('<p>Creating database</p>'); $query = 'CREATE DATABASE ' . DatabaseConnection::sanitizeSqlDatabase($database); DatabaseConnection::exec($query); // Inserting putenv('USER='******'MYSQL_PWD=' . DatabaseConnection::getDbPassword()); $commandMysql = new CommandBuilder('mysql', '--user=%s %s --one-database', array(DatabaseConnection::getDbUsername(), $database)); // Set server connection details if ($input->getOption('host')) { $commandMysql->addArgumentTemplate('-h %s', $input->getOption('host')); } if ($input->getOption('port')) { $commandMysql->addArgumentTemplate('-P %s', $input->getOption('port')); } $commandFile = new CommandBuilder(); $commandFile->addArgument($dumpFile); $commandFile->addPipeCommand($commandMysql); switch ($dumpFileType) { case 'application/x-bzip2': $output->writeln('<p>Using BZIP2 decompression</p>'); $commandFile->setCommand('bzcat'); break; case 'application/gzip': case 'application/x-gzip': $output->writeln('<p>Using GZIP decompression</p>'); $commandFile->setCommand('gzcat'); break; case 'application/x-lzma': case 'application/x-xz': $output->writeln('<p>Using LZMA decompression</p>'); $commandFile->setCommand('xzcat'); break; default: $output->writeln('<p>Using plaintext (no decompression)</p>'); $commandFile->setCommand('cat'); break; } $output->writeln('<p>Reading dump</p>'); $commandFile->executeInteractive(); $output->writeln('<h2>Database "' . $database . '" restored</h2>'); return 0; }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $database = $input->getArgument('db'); $output->writeln('<h2>Dropping Database "' . $database . '"...</h2>'); $query = 'DROP DATABASE IF EXISTS ' . DatabaseConnection::sanitizeSqlDatabase($database); DatabaseConnection::exec($query); $output->writeln('<p>Database dropped</p>'); return 0; }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $versionList = array(); // ############################ // System (LSB Version) // ############################ $versionRow = array('system' => 'System', 'version' => UnixUtility::lsbSystemDescription()); $versionList[] = array_values($versionRow); // ############################ // PHP // ############################ $versionList[] = array('PHP', phpversion()); // ############################ // MySQL // ############################ $query = 'SHOW VARIABLES LIKE \'version\''; $versionRow = DatabaseConnection::getList($query); $versionList[] = array('MySQL', $versionRow['version']); // ############################ // Apache // ############################ $versionRow = array('system' => 'Apache', 'version' => 'Unknown'); $command = new CommandBuilder('apache2ctl', '-v'); $command->setOutputRedirect(CommandBuilder::OUTPUT_REDIRECT_NO_STDERR); $execOutput = $command->execute()->getOutput(); foreach ($execOutput as $execOutputLine) { if (strpos($execOutputLine, ':') !== false) { list($tmpKey, $tmpVersion) = explode(':', trim($execOutputLine), 2); switch (strtolower($tmpKey)) { case 'server version': $versionRow['version'] = trim($tmpVersion); break; } } } $versionList[] = array_values($versionRow); // ############################ // Docker // ############################ $versionRow = array('system' => 'Docker', 'version' => \CliTools\Utility\UnixUtility::dockerVersion()); $versionList[] = array_values($versionRow); // ############################ // CliTools // ############################ $versionList[] = array('CliTools', CLITOOLS_COMMAND_VERSION); // ######################## // Output // ######################## /** @var \Symfony\Component\Console\Helper\Table $table */ $table = new Table($output); $table->setHeaders(array('System', 'Version')); foreach ($versionList as $versionRow) { $table->addRow(array_values($versionRow)); } $table->render(); return 0; }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $this->elevateProcess($input, $output); $debugLogLocation = $this->getApplication()->getConfigValue('db', 'debug_log_dir'); $debugLogDir = dirname($debugLogLocation); $output->writeln('<h2>Starting MySQL general query log</h2>'); // Create directory if not exists if (!is_dir($debugLogDir)) { if (!mkdir($debugLogDir, 0777, true)) { $output->writeln('<p-error>Could not create "' . $debugLogDir . '" directory</p-error>'); throw new \CliTools\Exception\StopException(1); } } if (!empty($debugLogLocation)) { $debugLogLocation .= 'mysql_' . getmypid() . '.log'; $query = 'SET GLOBAL general_log_file = ' . DatabaseConnection::quote($debugLogLocation); DatabaseConnection::exec($query); } // Fetch log file $query = 'SHOW VARIABLES LIKE \'general_log_file\''; $logFileRow = DatabaseConnection::getRow($query); if (!empty($logFileRow['Value'])) { // Enable general log $output->writeln('<p>Enabling general log</p>'); $query = 'SET GLOBAL general_log = \'ON\''; DatabaseConnection::exec($query); // Setup teardown cleanup $tearDownFunc = function () use($output) { // Disable general log $output->writeln('<p>Disabling general log</p>'); $query = 'SET GLOBAL general_log = \'OFF\''; DatabaseConnection::exec($query); }; $this->getApplication()->registerTearDown($tearDownFunc); // Read grep value $grep = null; if ($input->hasArgument('grep')) { $grep = $input->getArgument('grep'); } // Tail logfile $logList = array($logFileRow['Value']); $optionList = array('-n 0'); $this->showLog($logList, $input, $output, $grep, $optionList); return 0; } else { $output->writeln('<p-error>MySQL general_log_file not set</p-error>'); return 1; } }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $database = $input->getArgument('db'); $output->writeln('<h2>Clearing database "' . $database . '"</h2>'); if (DatabaseConnection::databaseExists($database)) { $output->writeln('<p>Dropping database</p>'); $query = 'DROP DATABASE ' . DatabaseConnection::sanitizeSqlDatabase($database); DatabaseConnection::exec($query); } $output->writeln('<p>Creating database</p>'); $query = 'CREATE DATABASE ' . DatabaseConnection::sanitizeSqlDatabase($database); DatabaseConnection::exec($query); $output->writeln('<h2>Database "' . $database . '" recreated</h2>'); return 0; }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $charset = 'utf8'; $collation = 'utf8_general_ci'; $stdout = false; $database = $input->getArgument('database'); if ($input->getOption('charset')) { $charset = (string) $input->getOption('charset'); } if ($input->getOption('collation')) { $collation = (string) $input->getOption('collation'); } if ($input->getOption('stdout')) { $stdout = true; } // ################## // Alter database // ################## $query = 'ALTER DATABASE %s CHARACTER SET %s COLLATE %s'; $query = sprintf($query, DatabaseConnection::sanitizeSqlDatabase($database), DatabaseConnection::quote($charset), DatabaseConnection::quote($collation)); if (!$stdout) { // Execute $output->writeln('<h2>Converting database ' . $database . '</h2>'); DatabaseConnection::exec($query); } else { // Show only $output->writeln($query . ';'); } // ################## // Alter tables // ################## $tableList = DatabaseConnection::tableList($database); foreach ($tableList as $table) { // Build statement $query = 'ALTER TABLE %s.%s CONVERT TO CHARACTER SET %s COLLATE %s'; $query = sprintf($query, DatabaseConnection::sanitizeSqlDatabase($database), DatabaseConnection::sanitizeSqlTable($table), DatabaseConnection::quote($charset), DatabaseConnection::quote($collation)); if (!$stdout) { // Execute $output->writeln('<p>Converting table ' . $table . '</p>'); DatabaseConnection::exec($query); } else { // Show only $output->writeln($query . ';'); } } return 0; }
/** * Cleanup MySQL * * @return string */ protected function cleanupMysql() { try { // ############################ // Clear general log // ############################ // Disable general log $query = 'SET GLOBAL general_log = \'OFF\''; DatabaseConnection::exec($query); // Fetch log file $query = 'SHOW VARIABLES LIKE \'general_log_file\''; $logFileRow = DatabaseConnection::getRow($query); if (!empty($logFileRow['Value'])) { $command = new CommandBuilder('rm'); $command->addArgument('-f')->addArgumentSeparator()->addArgument($logFileRow['Value'])->executeInteractive(); } } catch (\Exception $e) { // do nothing if no mysql is running } }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { // Get current connection id $query = 'SELECT CONNECTION_ID()'; $conId = DatabaseConnection::getOne($query); $query = 'SHOW PROCESSLIST'; $processList = DatabaseConnection::getAll($query); // ######################## // Output // ######################## /** @var \Symfony\Component\Console\Helper\Table $table */ $table = new Table($output); $table->setHeaders(array_keys(reset($processList))); foreach ($processList as $row) { // Exclude current connection id if ($row['Id'] === $conId) { continue; } $table->addRow(array_values($row)); } $table->render(); return 0; }
/** * Initializes the command just after the input has been validated. * * This is mainly useful when a lot of commands extends one main command * where some things need to be initialized based on the input arguments and options. * * @param InputInterface $input An InputInterface instance * @param OutputInterface $output An OutputInterface instance */ protected function initialize(InputInterface $input, OutputInterface $output) { parent::initialize($input, $output); $dsn = null; $user = null; $password = null; if ($this->input->hasOption('host') && $this->input->getOption('host')) { $host = $this->input->getOption('host'); $port = 3306; if ($this->input->getOption('port')) { $port = $this->input->getOption('port'); } $dsn = 'mysql:host=' . urlencode($host) . ';port=' . (int) $port; } if ($this->input->hasOption('user') && $this->input->getOption('user')) { $user = $this->input->getOption('user'); } if ($this->input->hasOption('password') && $this->input->getOption('password')) { $password = $this->input->getOption('password'); } if ($user !== null || $password !== null) { DatabaseConnection::setDsn($dsn, $user, $password); } }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { $this->elevateProcess($input, $output); $slowLogQueryTime = 1; $logNonIndexedQueries = false; // Slow log threshold if ($input->getOption('time')) { $slowLogQueryTime = $input->getOption('time'); } // Also show not using indexes queries if ($input->getOption('no-index')) { $logNonIndexedQueries = true; } $debugLogLocation = $this->getApplication()->getConfigValue('db', 'debug_log_dir'); $debugLogDir = dirname($debugLogLocation); $output->writeln('<h2>Starting MySQL slow query log</h2>'); // Create directory if not exists if (!is_dir($debugLogDir)) { if (!mkdir($debugLogDir, 0777, true)) { $output->writeln('<p-error>Could not create "' . $debugLogDir . '" directory</p-error>'); throw new \CliTools\Exception\StopException(1); } } if (!empty($debugLogLocation)) { $debugLogLocation .= 'mysql_' . getmypid() . '.log'; $query = 'SET GLOBAL slow_query_log_file = ' . DatabaseConnection::quote($debugLogLocation); DatabaseConnection::exec($query); } // Fetch log file $query = 'SHOW VARIABLES LIKE \'slow_query_log_file\''; $logFileRow = DatabaseConnection::getRow($query); if (!empty($logFileRow['Value'])) { // Enable slow log $output->writeln('<p>Enabling slow log</p>'); $query = 'SET GLOBAL slow_query_log = \'ON\''; DatabaseConnection::exec($query); // Enable slow log $output->writeln('<p>Set long_query_time to ' . (int) abs($slowLogQueryTime) . ' seconds</p>'); $query = 'SET GLOBAL long_query_time = ' . (int) abs($slowLogQueryTime); DatabaseConnection::exec($query); // Enable log queries without indexes log if ($logNonIndexedQueries) { $output->writeln('<p>Enabling logging of queries without using indexes</p>'); $query = 'SET GLOBAL log_queries_not_using_indexes = \'ON\''; DatabaseConnection::exec($query); } else { $output->writeln('<p>Disabling logging of queries without using indexes</p>'); $query = 'SET GLOBAL log_queries_not_using_indexes = \'OFF\''; DatabaseConnection::exec($query); } // Setup teardown cleanup $tearDownFunc = function () use($output, $logNonIndexedQueries) { // Disable general log $output->writeln('<p>Disable slow log</p>'); $query = 'SET GLOBAL slow_query_log = \'OFF\''; DatabaseConnection::exec($query); if ($logNonIndexedQueries) { // Disable log queries without indexes log $query = 'SET GLOBAL log_queries_not_using_indexes = \'OFF\''; DatabaseConnection::exec($query); } }; $this->getApplication()->registerTearDown($tearDownFunc); // Read grep value $grep = null; if ($input->hasArgument('grep')) { $grep = $input->getArgument('grep'); } // Tail logfile $logList = array($logFileRow['Value']); $optionList = array('-n 0'); $this->showLog($logList, $input, $output, $grep, $optionList); return 0; } else { $output->writeln('<p-error>MySQL general_log_file not set</p-error>'); return 1; } }
/** * Add filter to command * * @param CommandBuilderInterface $command Command * @param string $database Database * @param string $filter Filter name * * @return CommandBuilderInterface */ protected function addFilterArguments(CommandBuilderInterface $commandDump, $database, $filter) { $command = $commandDump; // get filter $filterList = $this->getApplication()->getConfigValue('mysql-backup-filter', $filter); if (empty($filterList)) { throw new \RuntimeException('MySQL dump filters "' . $filter . '" not available"'); } $this->output->writeln('<comment>Using filter "' . $filter . '"</comment>'); // Get filtered tables $tableList = DatabaseConnection::tableList($database); $ignoredTableList = FilterUtility::mysqlIgnoredTableFilter($tableList, $filterList, $database); // Dump only structure $commandStructure = clone $command; $commandStructure->addArgument('--no-data'); // Dump only data (only filtered tables) $commandData = clone $command; $commandData->addArgument('--no-create-info'); if (!empty($ignoredTableList)) { $commandData->addArgumentTemplateMultiple('--ignore-table=%s', $ignoredTableList); } // Combine both commands to one $command = new \CliTools\Shell\CommandBuilder\OutputCombineCommandBuilder(); $command->addCommandForCombinedOutput($commandStructure)->addCommandForCombinedOutput($commandData); return $command; }
/** * Set development domains for TYPO3 database * * @return void */ protected function manipulateDomains() { $devDomain = '.' . $this->getApplication()->getConfigValue('config', 'domain_dev'); $domainLength = strlen($devDomain); // ################## // Fix domains // ################## $query = 'UPDATE sys_domain SET domainName = CONCAT(domainName, ' . DatabaseConnection::quote($devDomain) . ') WHERE RIGHT(domainName, ' . $domainLength . ') <> ' . DatabaseConnection::quote($devDomain); DatabaseConnection::exec($query); }
/** * Initialize configuration */ protected function initializeConfiguration() { $isRunningAsRoot = $this->isRunningAsRoot(); //######################### // Database connection //######################### if (!empty($this->config['db'])) { $dsn = null; $username = null; $password = null; if (!empty($this->config['db']['dsn'])) { $dsn = $this->config['db']['dsn']; } if (!empty($this->config['db']['username'])) { $username = $this->config['db']['username']; } if (!empty($this->config['db']['password'])) { $password = $this->config['db']['password']; } DatabaseConnection::setDsn($dsn, $username, $password); } //######################### // Commands //######################### if (!empty($this->config['commands']['class'])) { // Load list foreach ($this->config['commands']['class'] as $class) { if ($this->checkCommandClass($class)) { // check OnlyRoot filter if (!$isRunningAsRoot && is_subclass_of($class, '\\CliTools\\Console\\Filter\\OnlyRootFilterInterface')) { // class only useable for root continue; } $this->add(new $class()); } } } }
/** * Return list of databases * * @return array */ public static function databaseList() { // Get list of databases $query = 'SELECT SCHEMA_NAME FROM information_schema.SCHEMATA'; $ret = DatabaseConnection::getCol($query); // Filter mysql specific databases $ret = array_diff($ret, array('mysql', 'information_schema', 'performance_schema')); return $ret; }
/** * Create new mysqldump command * * @param null|string $database Database name * * @return RemoteCommandBuilder */ protected function createLocalMySqlDumpCommand($database = null) { $command = new RemoteCommandBuilder('mysqldump'); // Add username if (DatabaseConnection::getDbUsername()) { $command->addArgumentTemplate('-u%s', DatabaseConnection::getDbUsername()); } // Add password if (DatabaseConnection::getDbPassword()) { $command->addArgumentTemplate('-p%s', DatabaseConnection::getDbPassword()); } // Add hostname if (DatabaseConnection::getDbHostname()) { $command->addArgumentTemplate('-h%s', DatabaseConnection::getDbHostname()); } // Add hostname if (DatabaseConnection::getDbPort()) { $command->addArgumentTemplate('-P%s', DatabaseConnection::getDbPort()); } // Add custom options if ($this->contextConfig->exists('mysql.mysqldump.option')) { $command->addArgumentRaw($this->contextConfig->get('mysql.mysqldump.option')); } if ($database !== null) { $command->addArgument($database); } // Transfer compression switch ($this->contextConfig->get('mysql.compression')) { case 'bzip2': // Add pipe compressor (bzip2 compressed transfer via ssh) $command->addPipeCommand(new CommandBuilder('bzip2', '--compress --stdout')); break; case 'gzip': // Add pipe compressor (gzip compressed transfer via ssh) $command->addPipeCommand(new CommandBuilder('gzip', '--stdout')); break; } return $command; }
/** * Set TYPO3 user for database * * @param string $database Database * @param string $username Username * @param string $password Password (salted/hashed) */ protected function setTypo3UserForDatabase($database, $username, $password) { // ################## // Update/insert user // ################## // Default UserTS $tsConfig = array('options.clearCache.system = 1', 'options.clearCache.all = 1', 'options.enableShowPalettes = 1', 'options.alertPopups = 254', 'options.pageTree.showPageIdWithTitle = 1', 'options.pageTree.showPathAboveMounts = 1', 'options.pageTree.showDomainNameWithTitle = 1', 'admPanel.enable.edit = 1', 'admPanel.module.edit.forceDisplayFieldIcons = 1', 'admPanel.hide = 0', 'setup.default.thumbnailsByDefault = 1', 'setup.default.enableFlashUploader = 0', 'setup.default.recursiveDelete = 1', 'setup.default.showHiddenFilesAndFolders = 1', 'setup.default.resizeTextareas_Flexible = 1', 'setup.default.copyLevels = 99', 'setup.default.rteResize = 99', 'setup.default.moduleData.web_list.bigControlPanel = 1', 'setup.default.moduleData.web_list.clipBoard = 1', 'setup.default.moduleData.web_list.localization = 1', 'setup.default.moduleData.web_list.showPalettes = 1', 'setup.default.moduleData.file_list.bigControlPanel = 1', 'setup.default.moduleData.file_list.clipBoard = 1', 'setup.default.moduleData.file_list.localization = 1', 'setup.default.moduleData.file_list.showPalettes = 1'); $tsConfig = implode("\n", $tsConfig); try { // Get uid from current dev user (if already existing) $query = 'SELECT uid FROM ' . DatabaseConnection::sanitizeSqlDatabase($database) . '.be_users WHERE username = '******' AND deleted = 0'; $beUserId = DatabaseConnection::getOne($query); // Insert or update user in TYPO3 database $query = 'INSERT INTO ' . DatabaseConnection::sanitizeSqlDatabase($database) . '.be_users (uid, tstamp, crdate, realName, username, password, TSconfig, admin, disable, starttime, endtime) VALUES( ' . DatabaseConnection::quote($beUserId) . ', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), ' . DatabaseConnection::quote('DEVELOPMENT') . ', ' . DatabaseConnection::quote($username) . ', ' . DatabaseConnection::quote($password) . ', ' . DatabaseConnection::quote($tsConfig) . ', 1, 0, 0, 0 ) ON DUPLICATE KEY UPDATE realName = VALUES(realName), password = VALUES(password), TSconfig = VALUES(TSconfig), disable = VALUES(disable), starttime = VALUES(starttime), endtime = VALUES(endtime)'; DatabaseConnection::exec($query); if ($beUserId) { $this->output->writeln('<p>User successfully updated to "' . $database . '"</p>'); } else { $this->output->writeln('<p>User successfully added to "' . $database . '"</p>'); } } catch (\Exception $e) { $this->output->writeln('<p-error>User adding failed</p-error>'); } }
/** * Cleanup TYPO3 database * * @param string $database Database */ protected function cleanupTypo3Database($database) { $cleanupTableList = array(); // Check if database is TYPO3 instance $tableList = DatabaseConnection::tableList($database); foreach ($tableList as $table) { $clearTable = false; // Caching und indexing tables switch (true) { case strpos($table, 'cache_') === 0: case strpos($table, 'cachingframework_') === 0: case strpos($table, 'cf_') === 0: // Caching framework $clearTable = true; break; case strpos($table, 'index_') === 0: // EXT:indexed_search $clearTable = true; break; } switch ($table) { case 'sys_history': case 'sys_log': // History/Log $clearTable = true; break; case 'sys_dmain': // EXT:direct_mail $clearTable = true; break; case 'tx_devlog': // EXT:devlog $clearTable = true; break; case 'tx_realurl_errorlog': case 'tx_realurl_pathcache': case 'tx_realurl_urldecodecache': case 'tx_realurl_urlencodecache': // EXT: realurl $clearTable = true; break; case 'tx_solr_cache': case 'tx_solr_cache_tags': // EXT:solr $clearTable = true; break; } if ($clearTable) { $cleanupTableList[] = $table; } } $this->output->writeln('<p>Starting cleanup of database "' . $database . '"</p>'); DatabaseConnection::switchDatabase(DatabaseConnection::sanitizeSqlDatabase($database)); foreach ($cleanupTableList as $table) { $query = 'TRUNCATE ' . DatabaseConnection::sanitizeSqlTable($table); DatabaseConnection::exec($query); if ($this->output->isVerbose()) { $this->output->writeln('<p>Truncating table ' . $table . '</p>'); } } $this->output->writeln('<p>finished</p>'); }
/** * Execute command * * @param InputInterface $input Input instance * @param OutputInterface $output Output instance * * @return int|null|void */ public function execute(InputInterface $input, OutputInterface $output) { // Get list of databases $databaseList = DatabaseConnection::databaseList(); if (!empty($databaseList)) { // ######################## // Fetch statistics // ######################## $databaseRowList = array(); foreach ($databaseList as $database) { // Get all tables $query = 'SELECT COUNT(*) AS count FROM information_schema.tables WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database) . ' AND TABLE_TYPE = \'BASE TABLE\''; $tableCount = DatabaseConnection::getOne($query); // Get all views $query = 'SELECT COUNT(*) AS count FROM information_schema.tables WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database) . ' AND TABLE_TYPE LIKE \'%VIEW\''; $viewCount = DatabaseConnection::getOne($query); // Get size of database $query = 'SELECT SUM(data_length) AS data_size, SUM(index_length) AS index_size, SUM(data_length + index_length) AS total_size FROM information_schema.tables WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database); $statsRow = DatabaseConnection::getRow($query); $databaseRowList[$database] = array('name' => $database, 'table_count' => $tableCount, 'view_count' => $viewCount, 'data_size' => $statsRow['data_size'], 'index_size' => $statsRow['index_size'], 'total_size' => $statsRow['total_size']); } // ######################## // Sorting // ######################## // Sort: default by name (natural sort) uasort($databaseRowList, function ($a, $b) { return strnatcmp($a['name'], $b['name']); }); // Sort: by table names if ($input->getOption('sort-name')) { uasort($databaseRowList, function ($a, $b) { return $a['table_count'] < $b['table_count']; }); } // Sort: by data size if ($input->getOption('sort-data')) { uasort($databaseRowList, function ($a, $b) { return $a['data_size'] < $b['data_size']; }); } // Sort: by index size if ($input->getOption('sort-index')) { uasort($databaseRowList, function ($a, $b) { return $a['index_size'] < $b['index_size']; }); } // Sort: by total size if ($input->getOption('sort-total')) { uasort($databaseRowList, function ($a, $b) { return $a['total_size'] < $b['total_size']; }); } // ######################## // Stats // ######################## $statsRow = array('name' => '', 'table_count' => 0, 'view_count' => 0, 'data_size' => 0, 'index_size' => 0, 'total_size' => 0); $databaseCount = count($databaseRowList); foreach ($databaseRowList as $databaseRow) { $statsRow['table_count'] += $databaseRow['table_count']; $statsRow['view_count'] += $databaseRow['view_count']; $statsRow['data_size'] += $databaseRow['data_size']; $statsRow['index_size'] += $databaseRow['index_size']; $statsRow['total_size'] += $databaseRow['total_size']; } // ######################## // Output // ######################## /** @var \Symfony\Component\Console\Helper\Table $table */ $table = new Table($output); $table->setHeaders(array('Database', 'Tables', 'Views', 'Data', 'Index', 'Total')); foreach ($databaseRowList as $databaseRow) { $databaseRow['table_count'] = FormatUtility::number($databaseRow['table_count']); $databaseRow['view_count'] = FormatUtility::number($databaseRow['view_count']); $databaseRow['data_size'] = FormatUtility::bytes($databaseRow['data_size']); $databaseRow['index_size'] = FormatUtility::bytes($databaseRow['index_size']); $databaseRow['total_size'] = FormatUtility::bytes($databaseRow['total_size']); $table->addRow(array_values($databaseRow)); } // Stats: average if ($databaseCount >= 1) { $table->addRow(new TableSeparator()); $statsAvgRow = array(); $statsAvgRow['name'] = 'Average'; $statsAvgRow['table_count'] = FormatUtility::number($statsRow['table_count'] / $databaseCount); $statsAvgRow['view_count'] = FormatUtility::number($statsRow['view_count'] / $databaseCount); $statsAvgRow['data_size'] = FormatUtility::bytes($statsRow['data_size'] / $databaseCount); $statsAvgRow['index_size'] = FormatUtility::bytes($statsRow['index_size'] / $databaseCount); $statsAvgRow['total_size'] = FormatUtility::bytes($statsRow['total_size'] / $databaseCount); $table->addRow(array_values($statsAvgRow)); } // Stats: total $statsTotalRow['name'] = 'Total'; $statsTotalRow['table_count'] = FormatUtility::number($statsRow['table_count']); $statsTotalRow['view_count'] = FormatUtility::number($statsRow['view_count']); $statsTotalRow['data_size'] = FormatUtility::bytes($statsRow['data_size']); $statsTotalRow['index_size'] = FormatUtility::bytes($statsRow['index_size']); $statsTotalRow['total_size'] = FormatUtility::bytes($statsRow['total_size']); $table->addRow(array_values($statsTotalRow)); $table->render(); } else { $output->writeln('<p-error>No databases found</p-error>'); } return 0; }