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 testFunctions()
{
$driver = new MySQLDriver();
$tests = $this->getFunctionTests();
foreach ($tests as $test) {
$args = new ArgumentArray();
list($func, $expectedSql) = $test;
$query = new SelectQuery();
$query->select($func);
$sql = $query->toSql($driver, $args);
$this->assertEquals($expectedSql, $sql);
}
}
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 testRightJoin()
{
$args = new ArgumentArray();
$driver = new MySQLDriver();
$query = new SelectQuery();
$query->select(array('id', 'name', 'phone', 'address'))->from('users', 'u');
$query->rightJoin('posts')->as('p')->on('p.user_id = u.id');
$sql = $query->toSql($driver, $args);
is('SELECT id, name, phone, address FROM users AS u RIGHT JOIN posts AS p ON (p.user_id = u.id)', $sql);
return $query;
}
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;
}
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.');
}
}
