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 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 testSelectMultipleOptions() { $query = new SelectQuery(); $query->option(['SQL_SMALL_RESULT', 'SQL_CALC_FOUND_ROWS', 'MAX_STATEMENT_TIME = 20']); $query->select('id'); $query->from('users', 'u'); $this->assertSqlStrings($query, [[new MySQLDriver(), "SELECT SQL_SMALL_RESULT SQL_CALC_FOUND_ROWS MAX_STATEMENT_TIME = 20 id FROM users AS u"]]); }
public static function create(DeclareSchema $schema, $baseClass) { $cTemplate = new ClassFile($schema->getBaseModelClass()); $cTemplate->useClass('LazyRecord\\Schema\\SchemaLoader'); $cTemplate->useClass('LazyRecord\\Result'); $cTemplate->useClass('SQLBuilder\\Bind'); $cTemplate->useClass('SQLBuilder\\ArgumentArray'); $cTemplate->useClass('PDO'); $cTemplate->useClass('SQLBuilder\\Universal\\Query\\InsertQuery'); $cTemplate->addConsts(array('SCHEMA_PROXY_CLASS' => $schema->getSchemaProxyClass(), 'COLLECTION_CLASS' => $schema->getCollectionClass(), 'MODEL_CLASS' => $schema->getModelClass(), 'TABLE' => $schema->getTable(), 'READ_SOURCE_ID' => $schema->getReadSourceId(), 'WRITE_SOURCE_ID' => $schema->getWriteSourceId(), 'PRIMARY_KEY' => $schema->primaryKey)); $cTemplate->addProtectedProperty('table', $schema->getTable()); $cTemplate->addPublicProperty('readSourceId', $schema->getReadSourceId() ?: 'default'); $cTemplate->addPublicProperty('writeSourceId', $schema->getWriteSourceId() ?: 'default'); $cTemplate->addMethod('public', 'getSchema', [], ['if ($this->_schema) {', ' return $this->_schema;', '}', 'return $this->_schema = SchemaLoader::load(' . var_export($schema->getSchemaProxyClass(), true) . ');']); $cTemplate->addStaticVar('column_names', $schema->getColumnNames()); $cTemplate->addStaticVar('column_hash', array_fill_keys($schema->getColumnNames(), 1)); $cTemplate->addStaticVar('mixin_classes', array_reverse($schema->getMixinSchemaClasses())); if ($traitClasses = $schema->getModelTraitClasses()) { foreach ($traitClasses as $traitClass) { $cTemplate->useTrait($traitClass); } } $schemaReflection = new ReflectionClass($schema); $schemaDocComment = $schemaReflection->getDocComment(); // TODO: apply settings from schema... $codegenSettings = []; preg_match_all('/@codegen (\\w+)(?:\\s*=\\s*(\\S+))?$/m', $schemaDocComment, $allMatches); for ($i = 0; $i < count($allMatches[0]); $i++) { $key = $allMatches[1][$i]; $value = $allMatches[2][$i]; if ($value === "") { $value = true; } else { if (strcasecmp($value, "true") == 0 || strcasecmp($value, "false") == 0) { $value = filter_var($value, FILTER_VALIDATE_BOOLEAN); } else { if (preg_match('/^\\d+$/', $value)) { $value = intval($value); } } } $codegenSettings[$key] = $value; } /* if ($codegenSettings['validateColumn']) { $codegenSettings['handleValidationError'] = true; } */ if (!empty($codegenSettings)) { $reflectionModel = new ReflectionClass('LazyRecord\\BaseModel'); $createMethod = $reflectionModel->getMethod('create'); $methodFile = $createMethod->getFilename(); $startLine = $createMethod->getStartLine(); $endLine = $createMethod->getEndLine(); $lines = file($methodFile); $methodLines = array_slice($lines, $startLine + 1, $endLine - $startLine - 2); // exclude '{', '}' $blockRanges = array(); $blockLines = array(); // parse code blocks for ($i = 0; $i < count($methodLines); $i++) { $line = rtrim($methodLines[$i]); if (preg_match('/@codegenBlock (\\w+)/', $line, $matches)) { $blockId = $matches[1]; for ($j = $i; $j < count($methodLines); $j++) { $line = rtrim($methodLines[$j]); $blockLines[$blockId][] = $line; if (preg_match('/@codegenBlockEnd/', $line)) { $blockRanges[$blockId] = [$i, $j]; $i = $j; break; } } } } $overrideCreateMethod = $cTemplate->addMethod('public', 'create', ['array $args', 'array $options = array()']); $overrideBlock = $overrideCreateMethod->getBlock(); for ($i = 0; $i < count($methodLines); $i++) { $line = rtrim($methodLines[$i]); if (preg_match('/@codegenBlock (\\w+)/', $line, $matches)) { $blockId = $matches[1]; if (isset($codegenSettings[$matches[1]]) && isset($blockLines[$blockId])) { if ($codegenSettings[$matches[1]]) { $overrideBlock[] = $blockLines[$blockId]; list($startLine, $endLine) = $blockRanges[$blockId]; $i = $endLine; continue; } else { list($startLine, $endLine) = $blockRanges[$blockId]; $i = $endLine; continue; } } } $overrideBlock[] = $line; } } // TODO: refacory this into factory method // Generate findByPrimaryKey SQL query $arguments = new ArgumentArray(); $findByPrimaryKeyQuery = new SelectQuery(); $findByPrimaryKeyQuery->from($schema->getTable()); $primaryKey = $schema->primaryKey; $readFrom = $schema->getReadSourceId(); $readConnection = ConnectionManager::getInstance()->getConnection($readFrom); $readQueryDriver = $readConnection->createQueryDriver(); $primaryKeyColumn = $schema->getColumn($primaryKey); $findByPrimaryKeyQuery->select('*')->where()->equal($primaryKey, new Bind($primaryKey)); $findByPrimaryKeyQuery->limit(1); $findByPrimaryKeySql = $findByPrimaryKeyQuery->toSql($readQueryDriver, $arguments); $cTemplate->addConst('FIND_BY_PRIMARY_KEY_SQL', $findByPrimaryKeySql); foreach ($schema->getColumns() as $column) { if (!$column->findable) { continue; } $columnName = $column->name; $findMethodName = 'findBy' . ucfirst(Inflector::camelize($columnName)); $findMethod = $cTemplate->addMethod('public', $findMethodName, ['$value']); $block = $findMethod->block; $arguments = new ArgumentArray(); $findByColumnQuery = new SelectQuery(); $findByColumnQuery->from($schema->getTable()); $columnName = $column->name; $readFrom = $schema->getReadSourceId(); $findByColumnQuery->select('*')->where()->equal($columnName, new Bind($columnName)); $findByColumnQuery->limit(1); $findByColumnSql = $findByColumnQuery->toSql($readQueryDriver, $arguments); $block[] = '$conn = $this->getReadConnection();'; $block[] = 'if (!isset($this->_preparedFindStms[' . var_export($columnName, true) . '])) {'; $block[] = ' $this->_preparedFindStms[' . var_export($columnName, true) . '] = $conn->prepare(' . var_export($findByColumnSql, true) . ');'; $block[] = '}'; $block[] = '$this->_preparedFindStms[' . var_export($columnName, true) . ']->execute([' . var_export(":{$columnName}", true) . ' => $value ]);'; $block[] = 'if (false === ($this->_data = $this->_preparedFindStms[' . var_export($columnName, true) . ']->fetch(PDO::FETCH_ASSOC)) ) {'; $block[] = ' return $this->reportError("Record not found", ['; $block[] = ' "sql" => ' . var_export($findByColumnSql, true) . ','; $block[] = ' ]);'; $block[] = '}'; $block[] = '$this->_preparedFindStms[' . var_export($columnName, true) . ']->closeCursor();'; $block[] = 'return $this->reportSuccess( "Data loaded", array( '; $block[] = ' "sql" => ' . var_export($findByColumnSql, true) . ','; $block[] = ' "type" => Result::TYPE_LOAD,'; $block[] = '));'; } $cTemplate->extendClass('\\' . $baseClass); // interfaces if ($ifs = $schema->getModelInterfaces()) { foreach ($ifs as $iface) { $cTemplate->implementClass($iface); } } // Create column accessor if ($schema->enableColumnAccessors) { foreach ($schema->getColumnNames() as $columnName) { $accessorMethodName = 'get' . ucfirst(Inflector::camelize($columnName)); $cTemplate->addMethod('public', $accessorMethodName, [], [' return $this->get(' . var_export($columnName, true) . ');']); } } return $cTemplate; }
public function load($args) { if (!$this->currentUserCan($this->getCurrentUser(), 'load', $args)) { return $this->reportError("Permission denied. Can not load record.", array('args' => $args)); } $dsId = $this->readSourceId; $pk = static::PRIMARY_KEY; $query = new SelectQuery(); $query->from($this->table); $conn = $this->getReadConnection(); $driver = $this->getReadQueryDriver(); $kVal = null; // build query from array. if (is_array($args)) { $query->select($this->selected ?: '*')->where($args); } else { $kVal = $args; $column = $this->getSchema()->getColumn($pk); if (!$column) { // This should not happend, every schema should have it's own primary key // TODO: Create new exception class for this. throw new MissingPrimaryKeyException("Primary key {$pk} is not defined", $this->getSchema()); } $kVal = $column->deflate($kVal); $args = array($pk => $kVal); $query->select($this->selected ?: '*')->where($args); } $arguments = new ArgumentArray(); $sql = $query->toSql($driver, $arguments); // mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) $stm = $conn->prepare($sql); $stm->execute($arguments->toArray()); if (false === ($this->_data = $stm->fetch(PDO::FETCH_ASSOC))) { // Record not found is not an exception return $this->reportError("Record not found", ['sql' => $sql]); } return $this->reportSuccess('Data loaded', array('id' => isset($this->_data[$pk]) ? $this->_data[$pk] : null, 'sql' => $sql, 'type' => Result::TYPE_LOAD)); }
public function load($args) { if (!$this->currentUserCan($this->getCurrentUser(), 'load', $args)) { return $this->reportError("Permission denied. Can not load record.", array('args' => $args)); } $dsId = $this->getReadSourceId(); $pk = static::primary_key; $query = new SelectQuery(); $query->from($this->getTable()); $conn = $this->getReadConnection(); $driver = $conn->createQueryDriver(); $kVal = null; // build query from array. if (is_array($args)) { $query->select($this->selected ?: '*')->where($args); } else { $kVal = $args; $column = $this->getSchema()->getColumn($pk); if (!$column) { // This should not happend, every schema should have it's own primary key // TODO: Create new exception class for this. throw new MissingPrimaryKeyException("Primary key {$pk} is not defined", $this->getSchema()); } $kVal = $column->deflate($kVal); $args = array($pk => $kVal); $query->select($this->selected ?: '*')->where($args); } $arguments = new ArgumentArray(); $sql = $query->toSql($conn->createQueryDriver(), $arguments); // mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) try { $stm = $this->dbPrepareAndExecute($conn, $sql, $arguments->toArray()); // mixed PDOStatement::fetchObject ([ string $class_name = "stdClass" [, array $ctor_args ]] ) if (false === ($this->_data = $stm->fetch(PDO::FETCH_ASSOC))) { // Record not found is not an exception return $this->reportError("Record not found"); } } catch (PDOException $e) { throw new QueryException('Record load failed', $e, array('sql' => $sql)); } return $this->reportSuccess('Data loaded', array('id' => isset($this->_data[$pk]) ? $this->_data[$pk] : null, 'sql' => $sql)); }
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; }