/**
* @var \Zend\Db\Adapter\Driver\Pdo\Result $results
* @var \Zend\Db\Adapter\Driver\StatementInterface $statement
*/
function renderResults(Result $results, StatementInterface $statement = null)
{
$headers = [];
$queryInformation = null;
$outputData = null;
$resultContents = null;
if ($statement) {
$queryInformation = SqlFormatter::format($statement->getSql());
if ($statement->getParameterContainer()->count()) {
$queryInformation .= createTable(array_keys($statement->getParameterContainer()->getNamedArray()), [array_values($statement->getParameterContainer()->getNamedArray())]);
}
}
if ($results->count()) {
foreach ($results as $result) {
$headers = array_keys($result);
$outputData[] = $result;
}
}
// Results
if ($outputData) {
$resultContents = createTable([$headers], $outputData);
}
// Wrapper Table
$table = new Table(new ConsoleOutput());
$table->setHeaders([['Query Results', 'Generated SQL']])->setRows([[$resultContents, $queryInformation]])->render();
}
/**
* query() is a convenience function
*
* @param string $sql
* @param string|array|ParameterContainer $parametersOrQueryMode
* @throws Exception\InvalidArgumentException
* @return Driver\StatementInterface|ResultSet\ResultSet
*/
public function query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE)
{
if (is_string($parametersOrQueryMode) && in_array($parametersOrQueryMode, array(self::QUERY_MODE_PREPARE, self::QUERY_MODE_EXECUTE))) {
$mode = $parametersOrQueryMode;
$parameters = null;
} elseif (is_array($parametersOrQueryMode) || $parametersOrQueryMode instanceof ParameterContainer) {
$mode = self::QUERY_MODE_PREPARE;
$parameters = $parametersOrQueryMode;
} else {
throw new Exception\InvalidArgumentException('Parameter 2 to this method must be a flag, an array, or ParameterContainer');
}
if ($mode == self::QUERY_MODE_PREPARE) {
$this->lastPreparedStatement = null;
$this->lastPreparedStatement = $this->driver->createStatement($sql);
$this->lastPreparedStatement->prepare();
if (is_array($parameters) || $parameters instanceof ParameterContainer) {
$this->lastPreparedStatement->setParameterContainer(is_array($parameters) ? new ParameterContainer($parameters) : $parameters);
$result = $this->lastPreparedStatement->execute();
} else {
return $this->lastPreparedStatement;
}
} else {
$result = $this->driver->getConnection()->execute($sql);
}
if ($result instanceof Driver\ResultInterface && $result->isQueryResult()) {
$resultSet = clone $this->queryResultSetPrototype;
$resultSet->initialize($result);
return $resultSet;
}
return $result;
}
/**
* Prepare statement
*
* @param \Zend\Db\Adapter\Adapter $adapter
* @param \Zend\Db\Adapter\Driver\StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
$prepareType = $driver->getPrepareType();
$parts = parent::getWhereParts();
$wherePart = '';
$whereParamIndex = 1;
foreach ($parts as $part) {
if (is_string($part)) {
$wherePart .= $part;
} elseif (is_array($part)) {
$values = $part[1];
$types = isset($part[2]) ? $part[2] : array();
foreach ($values as $vIndex => $value) {
if (isset($types[$vIndex]) && $types[$vIndex] == self::TYPE_IDENTIFIER) {
$values[$vIndex] = $platform->quoteIdentifierInFragment($value);
} elseif (isset($types[$vIndex]) && $types[$vIndex] == self::TYPE_VALUE) {
if ($prepareType == 'positional') {
$parameterContainer->offsetSet(null, $value);
$values[$vIndex] = $driver->formatParameterName(null);
} elseif ($prepareType == 'named') {
$name = 'where' . $whereParamIndex++;
$values[$vIndex] = $driver->formatParameterName($name);
$parameterContainer->offsetSet($name, $value);
}
}
}
$wherePart .= vsprintf($part[0], $values);
}
}
$sql = $statement->getSql();
$sql .= sprintf($this->specification, $wherePart);
$statement->setSql($sql);
}
/**
* Prepare the delete statement
*
* @param Adapter $adapter
* @param StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statement->setParameterContainer($parameterContainer);
}
$table = $platform->quoteIdentifier($this->table);
$sql = sprintf($this->specifications[self::SPECIFICATION_DELETE], $table);
// process where
if ($this->where->count() > 0) {
$whereParts = $this->processExpression($this->where, $platform, $adapter->getDriver(), 'where');
if (count($whereParts['parameters']) > 0) {
$parameterContainer->merge($whereParts['parameters']);
}
$sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts['sql']);
}
$statement->setSql($sql);
}
/**
* Prepare statement
*
* @param \Zend\Db\Adapter\Adapter $adapter
* @param \Zend\Db\Adapter\Driver\StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
// ensure statement has a ParameterContainer
$parameterContainer = $statement->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statement->setParameterContainer($parameterContainer);
}
$sqls = array();
$parameters = array();
$platform = $adapter->getPlatform();
foreach ($this->specifications as $name => $specification) {
$parameters[$name] = $this->{'process' . $name}($platform, $adapter, $parameterContainer, $sqls, $parameters);
if ($specification && is_array($parameters[$name])) {
$sqls[$name] = $this->createSqlFromSpecificationAndParameters($specification, $parameters[$name]);
}
}
$sql = implode(' ', $sqls);
$statement->setSql($sql);
return;
}
/**
* Logging debug information
*
* @param int $type
* @param string $sql
* @param array $bind
* @param StatementInterface $result
* @return $this
*/
protected function _debugStat($type, $sql, $bind = array(), $result = null)
{
if (!$this->_debug) {
return $this;
}
$code = '## ' . getmypid() . ' ## ';
$nl = "\n";
$time = sprintf('%.4f', microtime(true) - $this->_debugTimer);
if (!$this->_logAllQueries && $time < $this->_logQueryTime) {
return $this;
}
switch ($type) {
case self::DEBUG_CONNECT:
$code .= 'CONNECT' . $nl;
break;
case self::DEBUG_TRANSACTION:
$code .= 'TRANSACTION ' . $sql . $nl;
break;
case self::DEBUG_QUERY:
$code .= 'QUERY' . $nl;
$code .= 'SQL: ' . $sql . $nl;
if ($bind) {
$code .= 'BIND: ' . var_export($bind, true) . $nl;
}
if ($result instanceof StatementInterface) {
$code .= 'AFF: ' . $result->rowCount() . $nl;
}
break;
}
$code .= 'TIME: ' . $time . $nl;
if ($this->_logCallStack) {
$code .= 'TRACE: ' . Debug::backtrace(true, false) . $nl;
}
$code .= $nl;
$this->_debugWriteToFile($code);
return $this;
}
/**
* Prepare statement
*
* @param Adapter $adapter
* @param StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statement->setParameterContainer($parameterContainer);
}
$table = $platform->quoteIdentifier($this->table);
$columns = array();
$values = array();
foreach ($this->columns as $cIndex => $column) {
$columns[$cIndex] = $platform->quoteIdentifier($column);
$values[$cIndex] = $driver->formatParameterName($column);
$parameterContainer->offsetSet($column, $this->values[$cIndex]);
}
$sql = sprintf(
$this->specifications[self::SPECIFICATION_INSERT],
$table,
implode(', ', $columns),
implode(', ', $values)
);
$statement->setSql($sql);
}
/**
* Prepare statement
*
* @param \Zend\Db\Adapter\Adapter $adapter
* @param \Zend\Db\Adapter\Driver\StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statement->setParameterContainer($parameterContainer);
}
$prepareType = $driver->getPrepareType();
$table = $platform->quoteIdentifier($this->table);
// if ($this->schema != '') {
// $table = $platform->quoteIdentifier($this->schema)
// . $platform->getIdentifierSeparator()
// . $table;
// }
$set = $this->set;
if (is_array($set)) {
$setSql = array();
$values = array();
foreach ($set as $column => $value) {
$parameterContainer->offsetSet($column, $value);
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column);
}
$set = implode(', ', $setSql);
}
$sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $set);
// process where
if ($this->where->count() > 0) {
$whereParts = $this->processExpression($this->where, $platform, $adapter->getDriver(), 'where');
if (count($whereParts['parameters']) > 0) {
$parameterContainer->merge($whereParts['parameters']);
}
$sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts['sql']);
}
$statement->setSql($sql);
}
/**
* Prepare statement
*
* @param \Zend\Db\Adapter\Adapter $adapter
* @param \Zend\Db\Adapter\Driver\StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$platform = $adapter->getPlatform();
$separator = $platform->getIdentifierSeparator();
$columns = array();
foreach ($this->columns as $columnKey => $column) {
$columns[] = $platform->quoteIdentifierInFragment($column);
/* if (is_int($columnKey)) {
$columns = $platform->quoteIdentifierInFragment($column);
} else {
$columns = $platform->quoteIdentifierInFragment($column);
} */
}
$table = $platform->quoteIdentifier($this->table);
if ($this->databaseOrSchema != '') {
$dbSchema = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator();
$table = $dbSchema . $table;
} else {
$dbSchema = '';
}
if ($this->joins) {
$jArgs = array();
foreach ($this->joins as $j => $join) {
$jArgs[$j] = array();
$jArgs[$j][] = strtoupper($join[3]);
// type
$jArgs[$j][] = $platform->quoteIdentifier($join[0]);
// table
$jArgs[$j][] = $platform->quoteIdentifierInFragment($join[1], array('=', 'AND', 'OR', '(', ')'));
// on
foreach ($join[2] as $jColumn) {
$columns[] = $jArgs[$j][1] . $separator . $platform->quoteIdentifierInFragment($jColumn);
}
}
}
$columns = implode(', ', $columns);
$sql = sprintf($this->specification1, $columns, $table);
if (isset($jArgs)) {
foreach ($jArgs as $jArg) {
$sql .= ' ' . vsprintf($this->specification2, $jArg);
}
}
if ($this->where->count() > 0) {
$statement->setSql($sql);
$this->where->prepareStatement($adapter, $statement);
$sql = $statement->getSql();
}
$order = null;
// @todo
$limit = null;
// @todo
$sql .= isset($order) ? sprintf($this->specification3, $order) : '';
$sql .= isset($limit) ? sprintf($this->specification4, $limit) : '';
$statement->setSql($sql);
}
/**
* @expectedException \Link0\Profiler\PersistenceHandler\Exception
* @expectedExceptionMessage Multiple results for Profile[identifier=foo] found
*/
public function testRetrieveMultiple()
{
$resultInterface = new \ArrayIterator(array(array('identifier' => 'foo', 'data' => 'bar'), array('identifier' => 'baz', 'data' => 'boo')));
$this->statement->shouldReceive('execute')->once()->andReturn($resultInterface);
$this->handler->retrieve('foo');
}
/**
* Prepare the delete statement
*
* @param Adapter $adapter
* @param StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$table = $platform->quoteIdentifier($this->table);
if ($this->databaseOrSchema != '') {
$table = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator() . $table;
}
$sql = sprintf($this->specification, $table);
$statement->setSql($sql);
$this->where->prepareStatement($adapter, $statement);
}
/**
* Prepare statement
*
* @param Adapter $adapter
* @param StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
$prepareType = $driver->getPrepareType();
$table = $platform->quoteIdentifier($this->table);
if ($this->databaseOrSchema != '') {
$table = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator() . $table;
}
$columns = array();
$values = array();
foreach ($this->columns as $cIndex => $column) {
$columns[$cIndex] = $platform->quoteIdentifier($column);
if ($prepareType == 'positional') {
$parameterContainer->offsetSet(null, $this->values[$cIndex]);
$values[$cIndex] = $driver->formatParameterName(null);
} elseif ($prepareType == 'named') {
$values[$cIndex] = $driver->formatParameterName($column);
$parameterContainer->offsetSet($column, $this->values[$cIndex]);
}
}
$sql = sprintf($this->specification, $table, implode(', ', $columns), implode(', ', $values));
$statement->setSql($sql);
}
/**
* Prepare statement
*
* @param \Zend\Db\Adapter\Adapter $adapter
* @param \Zend\Db\Adapter\Driver\StatementInterface $statement
* @return void
*/
public function prepareStatement(Adapter $adapter, StatementInterface $statement)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statement->getParameterContainer();
$prepareType = $driver->getPrepareType();
$table = $platform->quoteIdentifier($this->table);
if ($this->databaseOrSchema != '') {
$table = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator() . $table;
}
$set = $this->set;
if (is_array($set)) {
$setSql = array();
$values = array();
foreach ($set as $column => $value) {
if ($prepareType == 'positional') {
$parameterContainer->offsetSet(null, $value);
$name = $driver->formatParameterName(null);
} elseif ($prepareType == 'named') {
$parameterContainer->offsetSet($column, $value);
$name = $driver->formatParameterName($column);
}
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $name;
}
$set = implode(', ', $setSql);
}
$sql = sprintf($this->specification, $table, $set);
$statement->setSql($sql);
$this->where->prepareStatement($adapter, $statement);
}
