Exemple #1
0
    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]);
 }
Exemple #5
0
 /**
  * @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]);
 }
Exemple #7
0
 /**
  * @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();
     }
 }
Exemple #8
0
 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();
 }
Exemple #10
0
 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));
 }
Exemple #11
0
 /**
  * @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));
 }
Exemple #13
0
 /**
  * @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);
 }
Exemple #14
0
 /**
  * @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));
 }