/**
* @param Sql\Select $select
* @return null|ResultSet
* @throws \RuntimeException
*/
public function selectWith(Sql\Select $select)
{
$selectState = $select->getRawState();
if ($selectState['table'] != $this->tableName || $selectState['schema'] != $this->schema) {
throw new Exception\RuntimeException('The table name and schema of the provided select object must match that of the table');
}
if ($selectState['join'] != array()) {
throw new Exception\RuntimeException('The Select object provided to TableRowGateway cannot include join statements.');
}
$statement = $this->adapter->createStatement();
$select->prepareStatement($this->adapter, $statement);
$result = $statement->execute();
$resultSet = clone $this->selectResultPrototype;
$resultSet->setDataSource($result);
return $resultSet;
}
/**
* @testdox unit test: Test reset() resets internal stat of Select object, based on input
* @covers Zend\Db\Sql\Select::reset
*/
public function testReset()
{
$select = new Select();
// table
$select->from('foo');
$this->assertEquals('foo', $select->getRawState(Select::TABLE));
$select->reset(Select::TABLE);
$this->assertNull($select->getRawState(Select::TABLE));
// columns
$select->columns(array('foo'));
$this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS));
$select->reset(Select::COLUMNS);
$this->assertEmpty($select->getRawState(Select::COLUMNS));
// joins
$select->join('foo', 'id = boo');
$this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS));
$select->reset(Select::JOINS);
$this->assertEmpty($select->getRawState(Select::JOINS));
// where
$select->where('foo = bar');
$where1 = $select->getRawState(Select::WHERE);
$this->assertEquals(1, $where1->count());
$select->reset(Select::WHERE);
$where2 = $select->getRawState(Select::WHERE);
$this->assertEquals(0, $where2->count());
$this->assertNotSame($where1, $where2);
// group
$select->group(array('foo'));
$this->assertEquals(array('foo'), $select->getRawState(Select::GROUP));
$select->reset(Select::GROUP);
$this->assertEmpty($select->getRawState(Select::GROUP));
// having
$select->having('foo = bar');
$having1 = $select->getRawState(Select::HAVING);
$this->assertEquals(1, $having1->count());
$select->reset(Select::HAVING);
$having2 = $select->getRawState(Select::HAVING);
$this->assertEquals(0, $having2->count());
$this->assertNotSame($having1, $having2);
// limit
$select->limit(5);
$this->assertEquals(5, $select->getRawState(Select::LIMIT));
$select->reset(Select::LIMIT);
$this->assertNull($select->getRawState(Select::LIMIT));
// offset
$select->offset(10);
$this->assertEquals(10, $select->getRawState(Select::OFFSET));
$select->reset(Select::OFFSET);
$this->assertNull($select->getRawState(Select::OFFSET));
// order
$select->order('foo asc');
$this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER));
$select->reset(Select::ORDER);
$this->assertNull($select->getRawState(Select::ORDER));
}
/**
* @param Select $select
* @return ResultSet
* @throws \RuntimeException
*/
protected function executeSelect(Select $select)
{
/**
* ACL Enforcement
*/
$selectState = $select->getRawState();
$table = $this->getRawTableNameFromQueryStateTable($selectState['table']);
// Enforce field read blacklist on Select's main table
$this->acl->enforceBlacklist($table, $selectState['columns'], Acl::FIELD_READ_BLACKLIST);
// Enforce field read blacklist on Select's join tables
foreach ($selectState['joins'] as $join) {
$joinTable = $this->getRawTableNameFromQueryStateTable($join['name']);
$this->acl->enforceBlacklist($joinTable, $join['columns'], Acl::FIELD_READ_BLACKLIST);
}
try {
return parent::executeSelect($select);
} catch (\Zend\Db\Adapter\Exception\InvalidQueryException $e) {
if ('production' !== DIRECTUS_ENV) {
throw new \RuntimeException("This query failed: " . $this->dumpSql($select), 0, $e);
}
// @todo send developer warning
throw $e;
}
}
/**
* @param Select $select
* @return ResultSet
* @throws \RuntimeException
*/
protected function executeSelect(Select $select)
{
$selectState = $select->getRawState();
if ($selectState['table'] != $this->table) {
throw new \RuntimeException('The table name of the provided select object must match that of the table');
}
if ($selectState['columns'] == array(Select::SQL_STAR) && $this->columns !== array()) {
$select->columns($this->columns);
}
// apply preSelect features
$this->featureSet->apply('preSelect', array($select));
// prepare and execute
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
// build result set
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);
// apply postSelect features
$this->featureSet->apply('postSelect', array($statement, $result, $resultSet));
return $resultSet;
}
/**
* @testdox unit test: Test getRawState() returns information populated via group()
* @covers Zend\Db\Sql\Select::getRawState
* @depends testGroup
*/
public function testGetRawStateViaGroup(Select $select)
{
$this->assertEquals(array('col1', 'col2'), $select->getRawState('group'));
}
/**
* @param Select $select
* @return ResultSet
* @throws Exception\RuntimeException
*/
protected function executeSelect(Select $select)
{
$selectState = $select->getRawState();
if ($selectState['table'] != $this->table && (is_array($selectState['table']) && end($selectState['table']) != $this->table)) {
throw new Exception\RuntimeException('The table name of the provided Select object must match that of the table');
}
if ($selectState['columns'] == [Select::SQL_STAR] && $this->columns !== []) {
$select->columns($this->columns);
}
// apply preSelect features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_PRE_SELECT, [$select]);
// prepare and execute
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
// build result set
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);
// apply postSelect features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_POST_SELECT, [$statement, $result, $resultSet]);
return $resultSet;
}
/**
* @author Rob Allen
* @testdox unit test: Test order()
* @covers Zend\Db\Sql\Select::order
*/
public function testOrder()
{
$select = new Select();
$return = $select->order('id DESC');
$this->assertSame($select, $return);
// test fluent interface
$this->assertEquals(array('id DESC'), $select->getRawState('order'));
$select = new Select();
$select->order('id DESC')->order('name ASC, age DESC');
$this->assertEquals(array('id DESC', 'name ASC', 'age DESC'), $select->getRawState('order'));
$select = new Select();
$select->order(array('name ASC', 'age DESC'));
$this->assertEquals(array('name ASC', 'age DESC'), $select->getRawState('order'));
}
/**
* @param Sql\Select $select
* @return null|ResultSet
* @throws \RuntimeException
*/
public function selectWith(Sql\Select $select)
{
$selectState = $select->getRawState();
if ($selectState['table'] != $this->table) {
throw new \RuntimeException('The table name of the provided select object must match that of the table');
}
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
$resultSet = clone $this->selectResultPrototype;
$resultSet->setDataSource($result);
return $resultSet;
}
protected function executeSelect(Select $select)
{
$selectState = $select->getRawState();
$result = parent::executeSelect($select);
$result = $this->applyHook('table.select', [$result, $selectState]);
return $result;
}
protected function makeExternalSql(Select $selectSQL)
{
//create new Select - for aggregate func query
$fields = $selectSQL->getRawState(Select::COLUMNS);
$hasAggregateFilds = array_keys($fields) != range(0, count($fields) - 1) && !empty($fields);
if ($hasAggregateFilds) {
$externalSql = new Select();
$externalSql->columns($selectSQL->getRawState(Select::COLUMNS));
//change select column to all
$selectSQL->columns(['*']);
//create sub query without aggreagate func and with all fields
$from = "(" . $this->dbTable->getSql()->buildSqlString($selectSQL) . ")";
$externalSql->from(array('Q' => $from));
return $externalSql;
} else {
return $selectSQL;
}
}
/**
* @param Select $select
* @return ResultSet
* @throws \RuntimeException
*/
protected function executeSelect(Select $select)
{
/**
* ACL Enforcement
*/
$selectState = $select->getRawState();
$table = $this->getRawTableNameFromQueryStateTable($selectState['table']);
// Enforce field read blacklist on Select's main table
try {
// @TODO: Enforce must return a list of columns without the blacklist
// when asterisk (*) is used
// and only throw and error when all the selected columns are blacklisted
$this->acl->enforceBlacklist($table, $selectState['columns'], Acl::FIELD_READ_BLACKLIST);
} catch (\Exception $e) {
if ($selectState['columns'][0] != '*') {
throw $e;
}
$selectState['columns'] = TableSchema::getAllNonAliasTableColumns($table);
$this->acl->enforceBlacklist($table, $selectState['columns'], Acl::FIELD_READ_BLACKLIST);
}
// Enforce field read blacklist on Select's join tables
foreach ($selectState['joins'] as $join) {
$joinTable = $this->getRawTableNameFromQueryStateTable($join['name']);
$this->acl->enforceBlacklist($joinTable, $join['columns'], Acl::FIELD_READ_BLACKLIST);
}
try {
return $this->processSelect($selectState, parent::executeSelect($select));
} catch (\Zend\Db\Adapter\Exception\InvalidQueryException $e) {
if ('production' !== DIRECTUS_ENV) {
throw new \RuntimeException('This query failed: ' . $this->dumpSql($select), 0, $e);
}
// @todo send developer warning
throw $e;
}
}
/**
* Common operations for string/number/date filter functions
*
* This method determines the table and column to search and adds them to
* $select if necessary.
*
* @param \Zend\Db\Sql\Select $select Object to apply the filter to
* @param string $model Model class (without namespace) containing property
* @param string $property Property to search in
* @param bool $addSearchColumns Add columns with search criteria
* @return array Table gateway and column of search criteria
*/
protected function _filter($select, $model, $property, $addSearchColumns)
{
// Determine table name and column alias
switch ($model) {
case 'Client':
$table = 'Clients';
$hydrator = $this->_serviceLocator->get('Database\\Table\\Clients')->getHydrator();
$column = $hydrator->extractName($property);
$columnAlias = $column;
break;
case 'CustomFields':
$table = 'CustomFields';
$column = $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getColumnMap()[$property];
$columnAlias = 'customfields_' . $column;
$fk = 'hardware_id';
break;
case 'Registry':
$table = 'RegistryData';
$column = 'regvalue';
$columnAlias = 'registry_content';
$select->where(array('registry.name' => $property));
$fk = 'hardware_id';
break;
case 'Windows':
$table = 'WindowsInstallations';
$hydrator = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator();
$column = $hydrator->extractName($property);
$columnAlias = 'windows_' . $column;
$fk = 'client_id';
break;
default:
$tableGateway = $this->_serviceLocator->get('Model\\Client\\ItemManager')->getTable($model);
$column = $tableGateway->getHydrator()->extractName($property);
$columnAlias = strtolower($model) . '_' . $column;
$fk = 'hardware_id';
}
if (!isset($tableGateway)) {
$tableGateway = $this->_serviceLocator->get("Database\\Table\\{$table}");
}
$table = $tableGateway->getTable();
if ($table == 'clients') {
if ($addSearchColumns) {
// Add column if not already present with the same alias
$columns = $select->getRawState(Select::COLUMNS);
if (@$columns[$columnAlias] != $column) {
$columns[$columnAlias] = $column;
$select->columns($columns);
}
}
} else {
// Join table if not already present
$rewriteJoins = false;
$joinedTables = $select->getRawState(Select::JOINS)->getJoins();
$tablePresent = false;
foreach ($joinedTables as $joinedTable) {
if ($joinedTable['name'] == $table) {
$tablePresent = true;
break;
}
}
if (!$tablePresent) {
$rewriteJoins = true;
$joinedTable = array('name' => $table, 'on' => "{$table}.{$fk} = clients.id", 'columns' => array(), 'type' => Select::JOIN_INNER);
}
// Add column if not already present with the same alias
if ($addSearchColumns and @$joinedTable['columns'][$columnAlias] != $column) {
$rewriteJoins = true;
$joinedTable['columns'][$columnAlias] = $column;
}
// Rewrite joins
if ($rewriteJoins) {
$select->reset(Select::JOINS);
if (!$tablePresent) {
$joinedTables[] = $joinedTable;
}
foreach ($joinedTables as $table) {
if ($table['name'] == $joinedTable['name']) {
// Existing spec is out of date for updated tables.
// Always replace with new spec.
$table = $joinedTable;
}
$select->join($table['name'], $table['on'], $table['columns'], $table['type']);
}
}
}
return array($tableGateway, $column);
}