protected function processExpression(ExpressionInterface $expression, PlatformInterface $platform, DriverInterface $driver = null, $namedParameterPrefix = null)
{
// static counter for the number of times this method was invoked across the PHP runtime
static $runtimeExpressionPrefix = 0;
if ($driver && ((!is_string($namedParameterPrefix) || $namedParameterPrefix == ''))) {
$namedParameterPrefix = sprintf('expr%04dParam', ++$runtimeExpressionPrefix);
}
$return = array(
'sql' => '',
'parameters' => array()
);
// initialize variables
$parts = $expression->getExpressionData();
$expressionParamIndex = 1;
foreach ($parts as $part) {
// if it is a string, simply tack it onto the return sql "specification" string
if (is_string($part)) {
$return['sql'] .= $part;
continue;
}
if (!is_array($part)) {
throw new Exception\RuntimeException('Elements returned from getExpressionData() array must be a string or array.');
}
// process values and types (the middle and last position of the expression data)
$values = $part[1];
$types = (isset($part[2])) ? $part[2] : array();
foreach ($values as $vIndex => $value) {
if (isset($types[$vIndex]) && $types[$vIndex] == ExpressionInterface::TYPE_IDENTIFIER) {
$values[$vIndex] = $platform->quoteIdentifierInFragment($value);
} elseif (isset($types[$vIndex]) && $types[$vIndex] == ExpressionInterface::TYPE_VALUE) {
// if prepareType is set, it means that this particular value must be
// passed back to the statement in a way it can be used as a placeholder value
if ($driver) {
$name = $namedParameterPrefix . $expressionParamIndex++;
$return['parameters'][$name] = $value;
$values[$vIndex] = $driver->formatParameterName($name);
continue;
}
// if not a preparable statement, simply quote the value and move on
$values[$vIndex] = $platform->quoteValue($value);
} elseif (isset($types[$vIndex]) && $types[$vIndex] == ExpressionInterface::TYPE_LITERAL) {
$values[$vIndex] = $value;
}
}
// after looping the values, interpolate them into the sql string (they might be placeholder names, or values)
$return['sql'] .= vsprintf($part[0], $values);
}
return $return;
}
public function setUp()
{
$this->driver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
$this->connection = $this->getMock('Zend\\Db\\Adapter\\Driver\\ConnectionInterface');
$this->statement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
$this->platform = $this->getMock('Zend\\Db\\Adapter\\Platform\\PlatformInterface');
$this->mapper = $this->getMockForAbstractClass('OnticBase\\Database\\Mapper\\AbstractMapper');
$this->driver->expects($this->any())->method('checkEnvironment')->will($this->returnValue(true));
$this->driver->expects($this->any())->method('getConnection')->will($this->returnValue($this->connection));
$this->driver->expects($this->any())->method('createStatement')->will($this->returnValue($this->statement));
}
protected function processOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($this->offset === null) {
return null;
}
if ($driver) {
$parameterContainer->offsetSet('offset', (int) $this->offset, ParameterContainer::TYPE_INTEGER);
return array($driver->formatParameterName('offset'));
}
return array($this->offset);
}
/**
* @param PlatformInterface $platform
* @param DriverInterface $driver
* @param ParameterContainer $parameterContainer
* @param $sqls
* @param $parameters
* @return null
*/
protected function processLimitOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null, &$sqls, &$parameters)
{
if ($this->limit === null && $this->offset === null) {
return;
}
$selectParameters = $parameters[self::SELECT];
/** if this is a DISTINCT query then real SELECT part goes to second element in array **/
$parameterIndex = 0;
if ($selectParameters[0] === 'DISTINCT') {
unset($selectParameters[0]);
$selectParameters = array_values($selectParameters);
$parameterIndex = 1;
}
$starSuffix = $platform->getIdentifierSeparator() . self::SQL_STAR;
foreach ($selectParameters[0] as $i => $columnParameters) {
if ($columnParameters[0] == self::SQL_STAR || isset($columnParameters[1]) && $columnParameters[1] == self::SQL_STAR || strpos($columnParameters[0], $starSuffix)) {
$selectParameters[0] = [[self::SQL_STAR]];
break;
}
if (isset($columnParameters[1])) {
array_shift($columnParameters);
$selectParameters[0][$i] = $columnParameters;
}
}
// first, produce column list without compound names (using the AS portion only)
array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(['SELECT %1$s FROM (' => current($this->specifications[self::SELECT])], $selectParameters));
if ($parameterContainer) {
// create bottom part of query, with offset and limit using row_number
$limitParamName = $driver->formatParameterName('limit');
$offsetParamName = $driver->formatParameterName('offset');
$offsetForSumParamName = $driver->formatParameterName('offsetForSum');
array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ' . $offsetParamName . '+1 AND ' . $limitParamName . '+' . $offsetForSumParamName);
$parameterContainer->offsetSet('offset', $this->offset);
$parameterContainer->offsetSet('limit', $this->limit);
$parameterContainer->offsetSetReference('offsetForSum', 'offset');
} else {
array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ' . (int) $this->offset . '+1 AND ' . (int) $this->limit . '+' . (int) $this->offset);
}
if (isset($sqls[self::ORDER])) {
$orderBy = $sqls[self::ORDER];
unset($sqls[self::ORDER]);
} else {
$orderBy = 'ORDER BY (SELECT 1)';
}
// add a column for row_number() using the order specification
$parameters[self::SELECT][$parameterIndex][] = ['ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]'];
$sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SELECT], $parameters[self::SELECT]);
}
/**
* @param Driver\DriverInterface $driver
* @return Platform\PlatformInterface
*/
protected function createPlatform($parameters)
{
if (isset($parameters['platform'])) {
$platformName = $parameters['platform'];
} elseif ($this->driver instanceof Driver\DriverInterface) {
$platformName = $this->driver->getDatabasePlatformName(Driver\DriverInterface::NAME_FORMAT_CAMELCASE);
} else {
throw new Exception\InvalidArgumentException('A platform could not be determined from the provided configuration');
}
$options = isset($parameters['platform_options']) ? $parameters['platform_options'] : array();
switch ($platformName) {
case 'Mysql':
return new Platform\Mysql($options);
case 'SqlServer':
return new Platform\SqlServer($options);
case 'Oracle':
return new Platform\Oracle($options);
case 'Sqlite':
return new Platform\Sqlite($options);
case 'Postgresql':
return new Platform\Postgresql($options);
case 'IbmDb2':
return new Platform\IbmDb2($options);
default:
return new Platform\Sql92($options);
}
}
/**
* @param PlatformInterface $platform
* @param DriverInterface $driver
* @param ParameterContainer $parameterContainer
* @param array $sqls
* @param array $parameters
*/
protected function processLimitOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null, &$sqls, &$parameters)
{
if ($this->limit === null && $this->offset === null) {
return;
}
$selectParameters = $parameters[self::SELECT];
$starSuffix = $platform->getIdentifierSeparator() . self::SQL_STAR;
foreach ($selectParameters[0] as $i => $columnParameters) {
if ($columnParameters[0] == self::SQL_STAR || isset($columnParameters[1]) && $columnParameters[1] == self::SQL_STAR || strpos($columnParameters[0], $starSuffix)) {
$selectParameters[0] = [[self::SQL_STAR]];
break;
}
if (isset($columnParameters[1])) {
array_shift($columnParameters);
$selectParameters[0][$i] = $columnParameters;
}
}
// first, produce column list without compound names (using the AS portion only)
array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(['SELECT %1$s FROM (' => current($this->specifications[self::SELECT])], $selectParameters));
if (preg_match('/DISTINCT/i', $sqls[0])) {
$this->setIsSelectContainDistinct(true);
}
if ($parameterContainer) {
// create bottom part of query, with offset and limit using row_number
$limitParamName = $driver->formatParameterName('limit');
$offsetParamName = $driver->formatParameterName('offset');
array_push($sqls, sprintf(") AS ZEND_IBMDB2_SERVER_LIMIT_OFFSET_EMULATION WHERE ZEND_IBMDB2_SERVER_LIMIT_OFFSET_EMULATION.ZEND_DB_ROWNUM BETWEEN %s AND %s", $offsetParamName, $limitParamName));
if ((int) $this->offset > 0) {
$parameterContainer->offsetSet('offset', (int) $this->offset + 1);
} else {
$parameterContainer->offsetSet('offset', (int) $this->offset);
}
$parameterContainer->offsetSet('limit', (int) $this->limit + (int) $this->offset);
} else {
if ((int) $this->offset > 0) {
$offset = (int) $this->offset + 1;
} else {
$offset = (int) $this->offset;
}
array_push($sqls, sprintf(") AS ZEND_IBMDB2_SERVER_LIMIT_OFFSET_EMULATION WHERE ZEND_IBMDB2_SERVER_LIMIT_OFFSET_EMULATION.ZEND_DB_ROWNUM BETWEEN %d AND %d", $offset, (int) $this->limit + (int) $this->offset));
}
if (isset($sqls[self::ORDER])) {
$orderBy = $sqls[self::ORDER];
unset($sqls[self::ORDER]);
} else {
$orderBy = '';
}
// add a column for row_number() using the order specification //dense_rank()
if ($this->getIsSelectContainDistinct()) {
$parameters[self::SELECT][0][] = ['DENSE_RANK() OVER (' . $orderBy . ')', 'ZEND_DB_ROWNUM'];
} else {
$parameters[self::SELECT][0][] = ['ROW_NUMBER() OVER (' . $orderBy . ')', 'ZEND_DB_ROWNUM'];
}
$sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SELECT], $parameters[self::SELECT]);
}
/**
* @param Driver\DriverInterface $driver
* @return Platform\PlatformInterface
*/
protected function createPlatform($parameters)
{
if (isset($parameters['platform'])) {
$platformName = $parameters['platform'];
} elseif ($this->driver instanceof Driver\DriverInterface) {
$platformName = $this->driver->getDatabasePlatformName(Driver\DriverInterface::NAME_FORMAT_CAMELCASE);
} else {
throw new Exception\InvalidArgumentException('A platform could not be determined from the provided configuration');
}
// currently only supported by the IbmDb2 & Oracle concrete implementations
$options = isset($parameters['platform_options']) ? $parameters['platform_options'] : array();
switch ($platformName) {
case 'Mysql':
// mysqli or pdo_mysql driver
$driver = $this->driver instanceof Driver\Mysqli\Mysqli || $this->driver instanceof Driver\Pdo\Pdo ? $this->driver : null;
return new Platform\Mysql($driver);
case 'SqlServer':
// PDO is only supported driver for quoting values in this platform
return new Platform\SqlServer($this->driver instanceof Driver\Pdo\Pdo ? $this->driver : null);
case 'Oracle':
// oracle does not accept a driver as an option, no driver specific quoting available
return new Platform\Oracle($options);
case 'Sqlite':
// PDO is only supported driver for quoting values in this platform
return new Platform\Sqlite($this->driver instanceof Driver\Pdo\Pdo ? $this->driver : null);
case 'Postgresql':
// pgsql or pdo postgres driver
$driver = $this->driver instanceof Driver\Pgsql\Pgsql || $this->driver instanceof Driver\Pdo\Pdo ? $this->driver : null;
return new Platform\Postgresql($driver);
case 'IbmDb2':
// ibm_db2 driver escaping does not need an action connection
return new Platform\IbmDb2($options);
default:
return new Platform\Sql92();
}
}
protected function processOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($this->offset === null) {
return;
}
if ($parameterContainer) {
$parameterContainer->offsetSet('offset', $this->offset, ParameterContainer::TYPE_INTEGER);
return [$driver->formatParameterName('offset')];
}
return [$platform->quoteValue($this->offset)];
}
/**
* Undoes results of current transaction
*/
public function rollbackTransaction()
{
$this->_driver->getConnection()->rollback();
}
protected function processInsert(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($this->select) {
return;
}
if (!$this->columns) {
throw new Exception\InvalidArgumentException('values or select should be present');
}
$columns = [];
$values = [];
foreach ($this->columns as $column => $value) {
$columns[] = $platform->quoteIdentifier($column);
if (is_scalar($value) && $parameterContainer) {
$values[] = $driver->formatParameterName($column);
$parameterContainer->offsetSet($column, $value);
} else {
$values[] = $this->resolveColumnValue($value, $platform, $driver, $parameterContainer);
}
}
return sprintf($this->specifications[static::SPECIFICATION_INSERT], $this->resolveTable($this->table, $platform, $driver, $parameterContainer), implode(', ', $columns), implode(', ', $values));
}
/**
* @param Driver\DriverInterface $driver
* @return Platform\PlatformInterface
*/
protected function createPlatformFromDriver(Driver\DriverInterface $driver)
{
// consult driver for platform implementation
$platformName = $driver->getDatabasePlatformName(Driver\DriverInterface::NAME_FORMAT_CAMELCASE);
switch ($platformName) {
case 'Mysql':
return new Platform\Mysql();
case 'SqlServer':
return new Platform\SqlServer();
case 'Sqlite':
return new Platform\Sqlite();
default:
return new Platform\Sql92();
}
}
protected function processUpdate(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
$setSql = [];
foreach ($this->set as $column => $value) {
$prefix = $platform->quoteIdentifier($column) . ' = ';
if (is_scalar($value) && $parameterContainer) {
$setSql[] = $prefix . $driver->formatParameterName($column);
$parameterContainer->offsetSet($column, $value);
} else {
$setSql[] = $prefix . $this->resolveColumnValue($value, $platform, $driver, $parameterContainer);
}
}
return sprintf($this->specifications[static::SPECIFICATION_UPDATE], $this->resolveTable($this->table, $platform, $driver, $parameterContainer), implode(', ', $setSql));
}
/**
* @param PlatformInterface $platform
* @param DriverInterface $driver
* @param ParameterContainer $parameterContainer
* @return array|null
*/
protected function processLimitOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($this->limit === null && $this->offset === null) {
return null;
}
$offset = (int) $this->offset;
$limit = (int) $this->limit;
if ($driver && $parameterContainer) {
$parameterContainer->offsetSet('limit', $limit, ParameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSet('offset', $offset, ParameterContainer::TYPE_INTEGER);
return array($driver->formatParameterName('offset'), $driver->formatParameterName('limit'));
}
return array($offset, $limit);
}
/**
* @param PlatformInterface $platform
* @param DriverInterface $driver
* @param ParameterContainer $pContainer
* @return array|null
*/
protected function processLike(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $pContainer = null)
{
if (!$this->like) {
return null;
}
$like = (string) $this->like;
if ($driver && $pContainer) {
$pContainer->offsetSet('like', $like, ParameterContainer::TYPE_STRING);
return array($driver->formatParameterName('like'));
}
return array($platform->quoteValue($like));
}
protected function processInsert(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($this->select) {
return;
}
if (!$this->columns) {
throw new \Zend\Db\Exception\InvalidArgumentException('values or select should be present');
}
$columns = array();
$values = array();
if (empty($this->valueRows)) {
return '';
//TODO Test that
}
$prepareColumns = true;
foreach ($this->valueRows as $row) {
if (!is_array($row)) {
throw new \Zend\Db\Exception\InvalidArgumentException('values must be arrays for multi-insertion');
}
$subValues = array();
ksort($row);
// Make sure columns always appear in the same order
foreach ($row as $col => $subValue) {
if ($prepareColumns) {
$columns[] = $platform->quoteIdentifier($col);
}
if (is_scalar($subValue) && $parameterContainer) {
$subValues[] = $driver->formatParameterName($col);
$parameterContainer->offsetSet($col, $subValue);
} else {
$subValues[] = $this->resolveColumnValue($subValue, $platform, $driver, $parameterContainer);
}
}
$values[] = implode(', ', $subValues);
$prepareColumns = false;
}
return sprintf($this->specifications[static::SPECIFICATION_INSERT], $this->resolveTable($this->table, $platform, $driver, $parameterContainer), implode(', ', $columns), implode('), (', $values));
}