/**
* 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;
}
