Exemplo n.º 1
0
 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.');
     }
 }
Exemplo n.º 2
0
 protected function createStatusSummaryQuery()
 {
     $query = new SelectQuery();
     $query->select(['CONCAT(table_schema, \'.\', table_name) AS name', 'CONCAT(ROUND(SUM(table_rows) / 1000000, 2), \'M\') AS rows', 'CASE WHEN SUM(data_length) > 1024 * 1024 * 1024 THEN CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), \'G\')
               WHEN SUM(data_length) > 1024 * 1024        THEN CONCAT(ROUND(SUM(data_length) / (1024 * 1024), 2), \'M\')
                                                     ELSE CONCAT(ROUND(SUM(data_length) / 1024, 2), \'K\')
                                                     END AS data_size', 'CASE WHEN SUM(index_length) > 1024 * 1024 * 1024 THEN CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), \'G\')
               WHEN SUM(index_length) > 1024 * 1024        THEN CONCAT(ROUND(SUM(index_length) / (1024 * 1024), 2), \'M\')
                                                     ELSE CONCAT(ROUND(SUM(index_length) / (1024), 2), \'K\')
                                                     END AS index_size', 'CASE WHEN SUM(data_length+index_length) > 1024 * 1024 * 1024 THEN CONCAT(ROUND(SUM(data_length+index_length) / (1024 * 1024 * 1024), 2), \'G\')
               WHEN SUM(data_length+index_length) > 1024 * 1024        THEN CONCAT(ROUND(SUM(data_length+index_length) / (1024 * 1024), 2), \'M\')
                                                     ELSE CONCAT(ROUND(SUM(data_length+index_length) / (1024), 2), \'K\')
                                                     END AS total_size']);
     $query->from('information_schema.TABLES');
     $query->groupBy('name');
     return $query;
 }
Exemplo n.º 3
0
 /**
  * @expectedException InvalidArgumentException
  */
 public function testInvalidGroupBy()
 {
     $args = new ArgumentArray();
     $driver = new MySQLDriver();
     $query = new SelectQuery();
     $query->groupBy(1);
     $query->toSql($driver, $args);
 }