/**
* @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]);
}
/**
* @testdox unit test: Test rewind() resets the iterators pointer
* @covers Zend\Db\Adapter\ParameterContainer::rewind
*/
public function testRewind()
{
$this->parameterContainer->offsetSet('bar', 'baz');
$this->parameterContainer->next();
$this->assertEquals('bar', $this->parameterContainer->key());
$this->parameterContainer->rewind();
$this->assertEquals('foo', $this->parameterContainer->key());
}
/**
* @param PlatformInterface $platform
* @param DriverInterface $driver
* @param ParameterContainer $parameterContainer
* @param array $sqls
* @param array $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];
$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;
}
}
if ($this->offset === null) {
$this->offset = 0;
}
// first, produce column list without compound names (using the AS portion only)
array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(['SELECT %1$s FROM (SELECT b.%1$s, rownum b_rownum FROM (' => current($this->specifications[self::SELECT])], $selectParameters));
if ($parameterContainer) {
$number = $this->processInfo['subselectCount'] ? $this->processInfo['subselectCount'] : '';
if ($this->limit === null) {
array_push($sqls, ') b ) WHERE b_rownum > (:offset' . $number . ')');
$parameterContainer->offsetSet('offset' . $number, $this->offset, $parameterContainer::TYPE_INTEGER);
} else {
// create bottom part of query, with offset and limit using row_number
array_push($sqls, ') b WHERE rownum <= (:offset' . $number . '+:limit' . $number . ')) WHERE b_rownum >= (:offset' . $number . ' + 1)');
$parameterContainer->offsetSet('offset' . $number, $this->offset, $parameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSet('limit' . $number, $this->limit, $parameterContainer::TYPE_INTEGER);
}
$this->processInfo['subselectCount']++;
} else {
if ($this->limit === null) {
array_push($sqls, ') b ) WHERE b_rownum > (' . (int) $this->offset . ')');
} 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]);
}
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);
}
protected function processOffset(PlatformInterface $platform, Adapter $adapter = null, ParameterContainer $parameterContainer = null)
{
if ($this->offset === null) {
return null;
}
if ($adapter) {
$parameterContainer->offsetSet('offset', $this->offset, ParameterContainer::TYPE_INTEGER);
return array($adapter->getDriver()->formatParameterName('offset'));
} else {
return array($this->offset);
}
}
/**
* @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::SPECIFICATION_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::SPECIFICATION_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::SPECIFICATION_ORDER])) {
$orderBy = $sqls[self::SPECIFICATION_ORDER];
unset($sqls[self::SPECIFICATION_ORDER]);
} else {
$orderBy = 'SELECT 1';
}
// add a column for row_number() using the order specification
$parameters[self::SPECIFICATION_SELECT][0][] = array('ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]');
$sqls[self::SPECIFICATION_SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SPECIFICATION_SELECT], $parameters[self::SPECIFICATION_SELECT]);
}
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)];
}
public static function getColumnSchemas()
{
$acl = Bootstrap::get('acl');
$zendDb = Bootstrap::get('ZendDb');
$sql = '(
SELECT
C.table_name,
C.column_name AS column_name,
ifnull(sort, ORDINAL_POSITION) as sort,
UCASE(C.data_type) as type,
CHARACTER_MAXIMUM_LENGTH as char_length,
IS_NULLABLE as is_nullable,
COLUMN_DEFAULT as default_value,
ifnull(comment, COLUMN_COMMENT) as comment,
ui,
ifnull(system,0) as system,
ifnull(master,0) as master,
ifnull(hidden_list,0) as hidden_list,
ifnull(hidden_input,0) as hidden_input,
relationship_type,
table_related,
junction_table,
junction_key_left,
junction_key_right,
ifnull(D.required,0) as required,
COLUMN_TYPE as column_type,
COLUMN_KEY as column_key
FROM
INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN
INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN
directus_columns AS D ON (C.COLUMN_NAME = D.column_name AND C.TABLE_NAME = D.table_name)
WHERE
C.TABLE_SCHEMA = :schema AND (T.TABLE_SCHEMA = :schema AND T.TABLE_TYPE = "BASE TABLE")
) UNION ALL (
SELECT
`table_name`,
`column_name` AS column_name,
sort,
UCASE(data_type) as type,
NULL AS char_length,
"NO" as is_nullable,
NULL AS default_value,
comment,
ui,
system,
master,
hidden_list,
hidden_input,
relationship_type,
table_related,
junction_table,
junction_key_left,
junction_key_right,
DC.required,
NULL as column_type,
NULL as column_key
FROM
`directus_columns` DC
WHERE
`data_type` IN ("alias", "MANYTOMANY", "ONETOMANY")
) ORDER BY `table_name`';
$sth = $zendDb->query($sql);
$parameterContainer = new ParameterContainer();
$parameterContainer->offsetSet(':schema', $zendDb->getCurrentSchema(), ParameterContainer::TYPE_STRING);
$result = $sth->execute($parameterContainer);
// Group columns by table name
$tables = array();
$tableName = null;
foreach ($result as $row) {
$tableName = $row['table_name'];
$columnName = $row['column_name'];
// Create nested array by table name
if (!array_key_exists($tableName, $tables)) {
$tables[$tableName] = array();
}
// @todo getTablePrivilegeList is called in excess,
// should just be called when $tableName changes
$readFieldBlacklist = $acl->getTablePrivilegeList($tableName, $acl::FIELD_READ_BLACKLIST);
$writeFieldBlacklist = $acl->getTablePrivilegeList($tableName, $acl::FIELD_WRITE_BLACKLIST);
// Indicate if the column is blacklisted for writing
$row["is_writable"] = !in_array($columnName, $writeFieldBlacklist);
// Don't include a column that is blacklisted for reading
if (in_array($columnName, $readFieldBlacklist)) {
continue;
}
$row = self::formatColumnRow($row);
$tables[$tableName][$columnName] = $row;
}
// UI's
$directusUiTableGateway = new DirectusUiTableGateway($acl, $zendDb);
$uis = $directusUiTableGateway->fetchExisting()->toArray();
foreach ($uis as $ui) {
$uiTableName = $ui['table_name'];
$uiColumnName = $ui['column_name'];
// Does the table for the UI settings still exist?
if (array_key_exists($uiTableName, $tables)) {
// Does the column for the UI settings still exist?
if (array_key_exists($uiColumnName, $tables[$uiTableName])) {
$column =& $tables[$uiTableName][$uiColumnName];
$column['options']['id'] = $ui['ui_name'];
$column['options'][$ui['name']] = $ui['value'];
}
}
}
return $tables;
}
/**
* @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]);
}
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));
}
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));
}
/**
* @requires OS LINUX|DARWIN
* @testdox LOB value passed through a parameter container
*/
public function testLOBWithParamContainer()
{
$fp = fopen('/dev/random', 'rb');
$this->pdoStatementMock->expects($this->any())->method('bindParam')->with($this->equalTo('lob'), $this->equalTo($fp), $this->equalTo(\PDO::PARAM_LOB));
$paramContainer = new ParameterContainer();
$paramContainer->offsetSet('lob', $fp, ParameterContainer::TYPE_LOB);
$this->statement->setParameterContainer($paramContainer);
$this->statement->execute(['lob' => $fp]);
}
/**
* @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 processOption(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if (empty($this->option)) {
return null;
}
// process options
$options = array();
foreach ($this->option as $optName => $optValue) {
$optionSql = '';
if ($optValue instanceof Expression) {
$parameterPrefix = $this->processInfo['paramPrefix'] . 'option';
$optionParts = $this->processExpression($optValue, $platform, $driver, $parameterPrefix);
if ($parameterContainer) {
$parameterContainer->merge($optionParts->getParameterContainer());
}
$optionSql .= $optionParts->getSql();
} else {
if ($driver && $parameterContainer) {
$parameterContainer->offsetSet('option_' . $optName, $optValue);
$optionSql .= $driver->formatParameterName('option_' . $optName);
} else {
$optionSql .= $platform->quoteValue($optValue);
}
}
$options[] = array($platform->quoteIdentifier($optName), $optionSql);
}
return array($options);
}
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));
}
