/**
* @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 null;
}
$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] = array(array(self::SQL_STAR));
break;
}
if (isset($columnParameters[1])) {
array_shift($columnParameters);
$selectParameters[0][$i] = $columnParameters;
}
}
if ($this->offset === null) {
$this->offset = 0;
}
// first, produce column list without compound names (using the AS portion only)
array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(array('SELECT %1$s FROM (SELECT b.%1$s, rownum b_rownum FROM (' => current($this->specifications[self::SELECT])), $selectParameters));
if ($parameterContainer) {
// create bottom part of query, with offset and limit using row_number
array_push($sqls, ') b WHERE rownum <= (:offset+:limit)) WHERE b_rownum >= (:offset + 1)');
$parameterContainer->offsetSet('offset', $this->offset, $parameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSet('limit', $this->limit, $parameterContainer::TYPE_INTEGER);
} else {
array_push($sqls, ') b WHERE rownum <= (' . (int) $this->offset . '+' . (int) $this->limit . ')) WHERE b_rownum >= (' . (int) $this->offset . ' + 1)');
}
$sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SELECT], $parameters[self::SELECT]);
}
/**
* @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 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 PlatformInterface $platform
* @param Adapter $adapter
* @param ParameterContainer $parameterContainer
* @param $sqls
* @param $parameters
* @return null
*/
protected function processLimitOffset(PlatformInterface $platform, Adapter $adapter = null, ParameterContainer $parameterContainer = null, &$sqls, &$parameters)
{
if ($this->limit === null && $this->offset === null) {
return null;
}
$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] = array(array(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(array('SELECT %1$s FROM (' => current($this->specifications[self::SELECT])), $selectParameters));
if ($parameterContainer) {
// create bottom part of query, with offset and limit using row_number
array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?');
$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 = 'SELECT 1';
}
// add a column for row_number() using the order specification
$parameters[self::SELECT][0][] = array('ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]');
$sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SELECT], $parameters[self::SELECT]);
}
protected function processJoins(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if (!$this->joins) {
return;
}
// process joins
$joinSpecArgArray = [];
foreach ($this->joins as $j => $join) {
$joinName = null;
$joinAs = null;
// table name
if (is_array($join['name'])) {
$joinName = current($join['name']);
$joinAs = $platform->quoteIdentifier(key($join['name']));
} else {
$joinName = $join['name'];
}
if ($joinName instanceof Expression) {
$joinName = $joinName->getExpression();
} elseif ($joinName instanceof TableIdentifier) {
$joinName = $joinName->getTableAndSchema();
$joinName = ($joinName[1] ? $platform->quoteIdentifier($joinName[1]) . $platform->getIdentifierSeparator() : '') . $platform->quoteIdentifier($joinName[0]);
} elseif ($joinName instanceof Select) {
$joinName = '(' . $this->processSubSelect($joinName, $platform, $driver, $parameterContainer) . ')';
} elseif (is_string($joinName) || is_object($joinName) && is_callable([$joinName, '__toString'])) {
$joinName = $platform->quoteIdentifier($joinName);
} else {
throw new Exception\InvalidArgumentException(sprintf('Join name expected to be Expression|TableIdentifier|Select|string, "%s" given', gettype($joinName)));
}
$joinSpecArgArray[$j] = [strtoupper($join['type']), $this->renderTable($joinName, $joinAs)];
// on expression
// note: for Expression objects, pass them to processExpression with a prefix specific to each join (used for named parameters)
$joinSpecArgArray[$j][] = $join['on'] instanceof ExpressionInterface ? $this->processExpression($join['on'], $platform, $driver, $parameterContainer, 'join' . ($j + 1) . 'part') : $platform->quoteIdentifierInFragment($join['on'], ['=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>']);
// on
}
return [$joinSpecArgArray];
}
protected function processSelect(PlatformInterface $platform, Adapter $adapter = null, ParameterContainer $parameterContainer = null)
{
$expr = 1;
if (!$this->table) {
return null;
}
$table = $this->table;
$schema = $alias = null;
if (is_array($table)) {
$alias = key($this->table);
$table = current($this->table);
}
// create quoted table name to use in columns processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
if ($alias) {
$fromTable = $platform->quoteIdentifier($alias);
$table .= ' AS ' . $fromTable;
} else {
$fromTable = $this->prefixColumnsWithTable ? $table : '';
}
$fromTable .= $platform->getIdentifierSeparator();
// process table columns
$columns = array();
foreach ($this->columns as $columnIndexOrAs => $column) {
$columnName = '';
if ($column === self::SQL_STAR) {
$columns[] = array($fromTable . self::SQL_STAR);
continue;
}
if ($column instanceof Expression) {
$columnParts = $this->processExpression($column, $platform, $adapter ? $adapter->getDriver() : null, is_string($columnIndexOrAs) ? $columnIndexOrAs : 'column');
if (count($columnParts['parameters']) > 0) {
$parameterContainer->merge($columnParts['parameters']);
}
$columnName .= $columnParts['sql'];
} else {
$columnName .= $fromTable . $platform->quoteIdentifier($column);
}
// process As portion
if (is_string($columnIndexOrAs)) {
$columnAs = $platform->quoteIdentifier($columnIndexOrAs);
} elseif (stripos($columnName, ' as ') === false) {
$columnAs = is_string($column) ? $platform->quoteIdentifier($column) : 'Expression' . $expr++;
}
$columns[] = isset($columnAs) ? array($columnName, $columnAs) : array($columnName);
}
$separator = $platform->getIdentifierSeparator();
// process join columns
foreach ($this->joins as $join) {
foreach ($join['columns'] as $jKey => $jColumn) {
$jColumns = array();
$name = is_array($join['name']) ? key($join['name']) : ($name = $join['name']);
$jColumns[] = $platform->quoteIdentifier($name) . $separator . $platform->quoteIdentifierInFragment($jColumn);
if (is_string($jKey)) {
$jColumns[] = $platform->quoteIdentifier($jKey);
} elseif ($jColumn !== self::SQL_STAR) {
$jColumns[] = $platform->quoteIdentifier($jColumn);
}
$columns[] = $jColumns;
}
}
return array($columns, $table);
}
protected function processJoins(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if (!$this->joins) {
return null;
}
// process joins
$joinSpecArgArray = array();
foreach ($this->joins as $j => $join) {
$joinSpecArgArray[$j] = array();
$joinName = null;
$joinAs = null;
// type
$joinSpecArgArray[$j][] = strtoupper($join['type']);
// table name
if (is_array($join['name'])) {
$joinName = current($join['name']);
$joinAs = $platform->quoteIdentifier(key($join['name']));
} else {
$joinName = $join['name'];
}
if ($joinName instanceof TableIdentifier) {
$joinName = $joinName->getTableAndSchema();
$joinName = $platform->quoteIdentifier($joinName[1]) . $platform->getIdentifierSeparator() . $platform->quoteIdentifier($joinName[0]);
} else {
if ($joinName instanceof Select) {
$joinName = '(' . $joinName->processSubSelect($joinName, $platform, $driver, $parameterContainer) . ')';
} else {
$joinName = $platform->quoteIdentifier($joinName);
}
}
$joinSpecArgArray[$j][] = isset($joinAs) ? $joinName . ' AS ' . $joinAs : $joinName;
// on expression
// note: for Expression objects, pass them to processExpression with a prefix specific to each join (used for named parameters)
$joinSpecArgArray[$j][] = $join['on'] instanceof ExpressionInterface ? $this->processExpression($join['on'], $platform, $driver, $this->processInfo['paramPrefix'] . 'join' . ($j + 1) . 'part') : $platform->quoteIdentifierInFragment($join['on'], array('=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>'));
// on
if ($joinSpecArgArray[$j][2] instanceof StatementContainerInterface) {
if ($parameterContainer) {
$parameterContainer->merge($joinSpecArgArray[$j][2]->getParameterContainer());
}
$joinSpecArgArray[$j][2] = $joinSpecArgArray[$j][2]->getSql();
}
}
return array($joinSpecArgArray);
}
