/** * @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)); }