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.'); } }
public function testUnionQuery() { $query1 = new SelectQuery(); $query1->select(array('name', 'phone', 'address'))->from('contacts'); $query1->where('name LIKE :name', [':name' => '%John%']); $query2 = new SelectQuery(); $query2->select(array('name', 'phone', 'address'))->from('users'); $query2->where('name LIKE :name', [':name' => '%Mary%']); $mainQuery = new UnionQuery(); $mainQuery->union($query1, $query2); $this->assertSql('(SELECT name, phone, address FROM contacts WHERE name LIKE :name) UNION (SELECT name, phone, address FROM users WHERE name LIKE :name)', $mainQuery); }
public function testPaging() { $query = new SelectQuery(); $query->select(array('id', 'name', 'phone', 'address'))->from('users', 'u')->page(1); $query->where('u.name LIKE :name', [':name' => '%John%']); $this->assertSqlStrings($query, [[new MySQLDriver(), 'SELECT id, name, phone, address FROM users AS u WHERE u.name LIKE :name LIMIT 10'], [new PgSQLDriver(), 'SELECT id, name, phone, address FROM users AS u WHERE u.name LIKE :name LIMIT 10']]); }
public function testSimpleJoin() { $args = new ArgumentArray(); $driver = new MySQLDriver(); $driver->setQuoteColumn(true); $query = new SelectQuery(); $query->select(array('u.id', 'u.name', 'u.phone', 'u.address', 'p.title' => 'post_title'))->from('users', 'u')->join('posts')->as('p')->on('p.user_id = u.id'); $query->where('u.name LIKE :name', [':name' => '%John%']); $sql = $query->toSql($driver, $args); $this->assertEquals('SELECT u.id, u.name, u.phone, u.address, p.title AS post_title FROM users AS u JOIN posts AS p ON (p.user_id = u.id) WHERE u.name LIKE :name', $sql); }