| <?php |
| /** |
| * File containing the ezcQueryExpressionOracle class. |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| * @package Database |
| * @version //autogentag// |
| * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0 |
| */ |
| |
| /** |
| * The ezcQueryExpressionOracle class is used to create SQL expression for Oracle. |
| * |
| * This class reimplements the methods that have a different syntax in Oracle. |
| * |
| * @package Database |
| * @version //autogentag// |
| */ |
| class ezcQueryExpressionOracle extends ezcQueryExpression |
| { |
| /** |
| * Constructs an empty ezcQueryExpression |
| * |
| * @param PDO $db |
| */ |
| public function __construct( PDO $db ) |
| { |
| parent::__construct( $db ); |
| } |
| |
| /** |
| * Returns a series of strings concatinated |
| * |
| * concat() accepts an arbitrary number of parameters. Each parameter |
| * must contain an expression or an array with expressions. |
| * |
| * @throws ezcQueryVariableException if no parameters are provided |
| * @param string|array(string) $... strings that will be concatinated. |
| * @return string |
| */ |
| public function concat() |
| { |
| $args = func_get_args(); |
| $cols = ezcQuery::arrayFlatten( $args ); |
| if ( count( $cols ) < 1 ) |
| { |
| throw new ezcQueryVariableParameterException( 'concat', count( $args ), 1 ); |
| } |
| |
| $cols = $this->getIdentifiers( $cols ); |
| return join( ' || ' , $cols ); |
| } |
| |
| /** |
| * Returns part of a string. |
| * |
| * Note: Not SQL92, but common functionality. |
| * |
| * @param string $value the target $value the string or the string column. |
| * @param int $from extract from this characeter. |
| * @param int $len extract this amount of characters. |
| * @return string sql that extracts part of a string. |
| */ |
| public function subString( $value, $from, $len = null ) |
| { |
| $value = $this->getIdentifier( $value ); |
| if ( $len === null ) |
| { |
| return "substr( {$value}, {$from} )"; |
| } |
| else |
| { |
| $len = $this->getIdentifier( $len ); |
| return "substr( {$value}, {$from}, {$len} )"; |
| } |
| } |
| |
| /** |
| * Returns the current system date and time in the database internal |
| * format. |
| * |
| * Note: The returned timestamp is a SYSDATE. |
| * The format can be set after connecting with e.g.: |
| * ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' |
| * |
| * @return string |
| */ |
| public function now() |
| { |
| return "LOCALTIMESTAMP"; |
| } |
| |
| /** |
| * Returns the SQL to locate the position of the first occurrence of a substring |
| * |
| * @param string $substr |
| * @param string $value |
| * @return string |
| */ |
| public function position( $substr, $value ) |
| { |
| $value = $this->getIdentifier( $value ); |
| return "INSTR( {$value}, '{$substr}' )"; |
| } |
| |
| /** |
| * Returns the SQL that performs the bitwise AND on two values. |
| * |
| * @param string $value1 |
| * @param string $value2 |
| * @return string |
| */ |
| public function bitAnd( $value1, $value2 ) |
| { |
| $value1 = $this->getIdentifier( $value1 ); |
| $value2 = $this->getIdentifier( $value2 ); |
| return "bitand( {$value1}, {$value2} )"; |
| } |
| |
| /** |
| * Returns the SQL that performs the bitwise OR on two values. |
| * |
| * @param string $value1 |
| * @param string $value2 |
| * @return string |
| */ |
| public function bitOr( $value1, $value2 ) |
| { |
| $value1 = $this->getIdentifier( $value1 ); |
| $value2 = $this->getIdentifier( $value2 ); |
| return "( {$value1} + {$value2} - bitand( {$value1}, {$value2} ) )"; |
| } |
| |
| /** |
| * Returns the SQL that performs the bitwise XOR on two values. |
| * |
| * @param string $value1 |
| * @param string $value2 |
| * @return string |
| */ |
| public function bitXor( $value1, $value2 ) |
| { |
| $value1 = $this->getIdentifier( $value1 ); |
| $value2 = $this->getIdentifier( $value2 ); |
| return "( {$value1} + {$value2} - bitand( {$value1}, {$value2} ) * 2 )"; |
| } |
| |
| /** |
| * Returns the SQL that converts a timestamp value to a unix timestamp. |
| * |
| * @param string $column |
| * @return string |
| */ |
| public function unixTimestamp( $column ) |
| { |
| $column = $this->getIdentifier( $column ); |
| |
| if ( $column != 'NOW()' ) |
| { |
| $column = "CAST( {$column} AS TIMESTAMP )"; |
| // // alternative |
| // if ( preg_match( '/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/', $column ) ) { |
| // $column = "TO_TIMESTAMP( {$column}, 'YYYY-MM-DD HH24:MI:SS' )"; |
| // } |
| } |
| |
| $date1 = "CAST( SYS_EXTRACT_UTC( {$column} ) AS DATE )"; |
| $date2 = "TO_DATE( '19700101000000', 'YYYYMMDDHH24MISS' )"; |
| return " ROUND( ( {$date1} - {$date2} ) / ( 1 / 86400 ) ) "; |
| } |
| |
| /** |
| * Returns the SQL that subtracts an interval from a timestamp value. |
| * |
| * @param string $column |
| * @param numeric $expr |
| * @param string $type one of SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR |
| * @return string |
| */ |
| public function dateSub( $column, $expr, $type ) |
| { |
| $type = $this->intervalMap[$type]; |
| $column = $this->getIdentifier( $column ); |
| |
| if ( $column != 'NOW()' ) |
| { |
| $column = "CAST( {$column} AS TIMESTAMP )"; |
| } |
| |
| return " {$column} - INTERVAL '{$expr}' {$type} "; |
| } |
| |
| /** |
| * Returns the SQL that adds an interval to a timestamp value. |
| * |
| * @param string $column |
| * @param numeric $expr |
| * @param string $type one of SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR |
| * @return string |
| */ |
| public function dateAdd( $column, $expr, $type ) |
| { |
| $type = $this->intervalMap[$type]; |
| $column = $this->getIdentifier( $column ); |
| |
| if ( $column != 'NOW()' ) |
| { |
| $column = "CAST( {$column} AS TIMESTAMP )"; |
| } |
| |
| return " {$column} + INTERVAL '{$expr}' {$type} "; |
| } |
| |
| /** |
| * Returns the SQL that extracts parts from a timestamp value. |
| * |
| * @param string $column |
| * @param string $type one of SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR |
| * @return string |
| */ |
| public function dateExtract( $column, $type ) |
| { |
| $type = $this->intervalMap[$type]; |
| $column = $this->getIdentifier( $column ); |
| |
| if ( $column != 'NOW()' ) |
| { |
| $column = "CAST( {$column} AS TIMESTAMP )"; |
| } |
| |
| if ( $type == 'SECOND' ) |
| { |
| return " FLOOR( EXTRACT( {$type} FROM {$column} ) ) "; |
| } |
| else |
| { |
| return " EXTRACT( {$type} FROM {$column} ) "; |
| } |
| } |
| |
| /** |
| * Returns the SQL to check if a value is one in a set of |
| * given values. |
| * |
| * in() accepts an arbitrary number of parameters. The first parameter |
| * must always specify the value that should be matched against. Successive |
| * parameters must contain a logical expression or an array with logical |
| * expressions. These expressions will be matched against the first |
| * parameter. |
| * |
| * Example: |
| * <code> |
| * $q->select( '*' )->from( 'table' ) |
| * ->where( $q->expr->in( 'id', 1, 2, 3 ) ); |
| * </code> |
| * |
| * Oracle limits the number of values in a single IN() to 1000. This |
| * implementation creates a list of combined IN() expressions to bypass |
| * this limitation. |
| * |
| * @throws ezcQueryVariableParameterException if called with less than two |
| * parameters. |
| * @throws ezcQueryInvalidParameterException if the 2nd parameter is an |
| * empty array. |
| * @param string|array(string) values that will be matched against $column |
| * @return string logical expression |
| */ |
| public function in( $column ) |
| { |
| $args = func_get_args(); |
| if ( count( $args ) < 2 ) |
| { |
| throw new ezcQueryVariableParameterException( 'in', count( $args ), 2 ); |
| } |
| |
| if ( is_array( $args[1] ) && count( $args[1] ) == 0 ) |
| { |
| throw new ezcQueryInvalidParameterException( 'in', 2, 'empty array', 'non-empty array' ); |
| } |
| |
| $values = ezcQuerySelect::arrayFlatten( array_slice( $args, 1 ) ); |
| $values = $this->getIdentifiers( $values ); |
| $column = $this->getIdentifier( $column ); |
| |
| if ( count( $values ) == 0 ) |
| { |
| throw new ezcQueryVariableParameterException( 'in', count( $args ), 2 ); |
| } |
| |
| if ( $this->quoteValues ) |
| { |
| foreach ( $values as $key => $value ) |
| { |
| switch ( true ) |
| { |
| case is_int( $value ): |
| case is_float( $value ): |
| case $value instanceof ezcQuerySubSelect: |
| $values[$key] = (string) $value; |
| break; |
| default: |
| $values[$key] = $this->db->quote( $value ); |
| } |
| } |
| } |
| |
| if ( count( $values ) <= 1000 ) |
| { |
| return "{$column} IN ( " . join( ', ', $values ) . ' )'; |
| } |
| else |
| { |
| $expression = '( '; |
| |
| do { |
| $bunch = array_slice( $values, 0, 1000 ); |
| $values = array_slice( $values, 1000 ); |
| |
| $expression .= "{$column} IN ( " . join( ', ', $bunch ) . ' ) OR '; |
| } while ( count( $values ) > 1000 ); |
| |
| $expression .= "{$column} IN ( " . join( ', ', $values ) . ' ) )'; |
| |
| return $expression; |
| } |
| } |
| } |
| ?> |
