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