public function execute()
{
$tables = func_get_args();
$dataSource = $this->getCurrentDataSourceId();
$conn = $this->getCurrentConnection();
$driver = $this->getCurrentQueryDriver();
if ($driver instanceof PDOMySQLDriver) {
$dbName = $conn->query('SELECT database();')->fetchColumn();
$query = new SelectQuery();
$query->select(['stat.TABLE_NAME', 'CONCAT(stat.INDEX_NAME, " (", GROUP_CONCAT(DISTINCT stat.COLUMN_NAME ORDER BY stat.SEQ_IN_INDEX ASC), ")")' => 'COLUMNS', 'stat.INDEX_TYPE', 'stat.NULLABLE', 'stat.NON_UNIQUE', 'stat.COMMENT', 'SUM(index_stat.stat_value)' => 'pages', 'CONCAT(ROUND((SUM(stat_value) * @@innodb_page_size) / 1024 / 1024, 1), "MB")' => 'page_size']);
$query->from('information_schema.STATISTICS stat');
$query->join('mysql.innodb_index_stats', 'index_stat', 'LEFT')->on('index_stat.database_name = stat.TABLE_SCHEMA
AND index_stat.table_name = stat.TABLE_NAME
AND index_stat.index_name = stat.INDEX_NAME');
$query->where()->equal('stat.TABLE_SCHEMA', 'bossnet');
if (!empty($tables)) {
$query->where()->in('stat.TABLE_NAME', $tables);
}
$query->groupBy('stat.INDEX_NAME');
$query->groupBy('stat.TABLE_NAME');
$query->groupBy('stat.TABLE_SCHEMA');
$query->orderBy('stat.TABLE_SCHEMA', 'ASC');
$query->orderBy('stat.TABLE_NAME', 'ASC');
$query->orderBy('stat.INDEX_NAME', 'ASC');
$query->orderBy('stat.SEQ_IN_INDEX', 'ASC');
$args = new ArgumentArray();
$sql = $query->toSql($driver, $args);
$this->logger->debug($sql);
$stm = $conn->prepare($sql);
$stm->execute($args->toArray());
$rows = $stm->fetchAll();
$this->displayRows($rows);
/*
$status = new MySQLTableStatus($conn, $driver);
$this->logger->info("Table Status:");
$rows = $status->queryDetails($tables);
$this->displayRows($rows);
$this->logger->newline();
$this->logger->info("Table Status Summary:");
$rows = $status->querySummary($tables);
*/
} else {
$this->logger->error('Driver not supported.');
}
}
protected function createStatusDetailQuery()
{
$query = new SelectQuery();
$query->select(['CONCAT(table_schema, \'.\', table_name) AS name', 'CONCAT(ROUND(table_rows / 1000000, 2), \'M\') AS rows', 'CASE WHEN data_length > 1024 * 1024 * 1024 THEN CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 2), \'G\')
WHEN data_length > 1024 * 1024 THEN CONCAT(ROUND(data_length / (1024 * 1024), 2), \'M\')
ELSE CONCAT(ROUND(data_length / (1024), 2), \'K\')
END AS data_size', 'CASE WHEN index_length > 1024 * 1024 * 1024 THEN CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 2), \'G\')
WHEN index_length > 1024 * 1024 THEN CONCAT(ROUND(index_length / (1024 * 1024), 2), \'M\')
ELSE CONCAT(ROUND(index_length / (1024), 2), \'K\')
END AS index_size', 'CASE WHEN (data_length+index_length) > 1024 * 1024 * 1024 THEN CONCAT(ROUND((data_length+index_length) / (1024 * 1024 * 1024), 2), \'G\')
WHEN (data_length+index_length) > 1024 * 1024 THEN CONCAT(ROUND((data_length+index_length) / (1024 * 1024), 2), \'M\')
ELSE CONCAT(ROUND((data_length+index_length) / (1024), 2), \'K\')
END AS total_size', 'ROUND(index_length / data_length, 2) AS index_frac']);
$query->from('information_schema.TABLES');
$query->orderBy('data_length + index_length', 'DESC');
return $query;
}
public function testSelectWithOrderBy()
{
$q = new SelectQuery();
$q->select(array('name'))->from('products');
$q->orderBy('name', 'ASC');
$this->assertSqlStrings($q, [[new MySQLDriver(), 'SELECT name FROM products ORDER BY name ASC']]);
}
public function createReadQuery()
{
$dsId = $this->getSchema()->getReadSourceId();
$conn = ConnectionManager::getInstance()->getConnection($dsId);
$driver = $conn->createQueryDriver();
$q = new SelectQuery();
// Read from class consts
$q->from($this->getTable(), $this->getAlias());
// main table alias
$selection = $this->getSelected();
$q->select($selection ? $selection : $this->explictSelect ? $this->getExplicitColumnSelect($driver) : $this->getAlias() . '.*');
// Setup Default Ordering.
if (!empty($this->defaultOrdering)) {
foreach ($this->defaultOrdering as $ordering) {
$q->orderBy($ordering[0], $ordering[1]);
}
}
return $q;
}
