/Database/src/sqlabstraction/expression.php
PHP | 1109 lines | 412 code | 73 blank | 624 comment | 36 complexity | 2bf02f1d0d2b0032251279006a9030ab MD5 | raw file
- <?php
- /**
- * File containing the ezcQueryExpression 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 ezcQueryExpression class is used to create database independent SQL expression.
- *
- * The QueryExpression class is usually used through the 'expr' variable in
- * one of the Select, Insert, Update or Delete classes.
- *
- * Note that the methods for logical or and and are
- * named lOr and lAnd respectively. This is because and and or are reserved names
- * in PHP and can not be used in method names.
- *
- * @package Database
- * @version //autogentag//
- * @mainclass
- */
- class ezcQueryExpression
- {
- /**
- * A pointer to the database handler to use for this query.
- *
- * @var PDO
- */
- protected $db;
- /**
- * The column and table name aliases.
- *
- * Format: array('alias' => 'realName')
- * @var array(string=>string)
- */
- private $aliases = null;
- /**
- * The flag that switch quoting mode for
- * values provided by user in miscelaneous SQL functions.
- *
- * @var boolean
- */
- protected $quoteValues = true;
- /**
- * Contains an interval map from generic intervals to MySQL native intervals.
- *
- * @var array(string=>string)
- */
- protected $intervalMap = array(
- 'SECOND' => 'SECOND',
- 'MINUTE' => 'MINUTE',
- 'HOUR' => 'HOUR',
- 'DAY' => 'DAY',
- 'MONTH' => 'MONTH',
- 'YEAR' => 'YEAR',
- );
- /**
- * Constructs an empty ezcQueryExpression
- *
- * @param PDO $db
- * @param array(string=>string) $aliases
- */
- public function __construct( PDO $db, array $aliases = array() )
- {
- $this->db = $db;
- if ( !empty( $aliases ) )
- {
- $this->aliases = $aliases;
- }
- }
- /**
- * Sets the aliases $aliases for this object.
- *
- * The aliases can be used to substitute the column and table names with more
- * friendly names. E.g PersistentObject uses it to allow using property and class
- * names instead of column and table names.
- *
- * @param array(string=>string) $aliases
- * @return void
- */
- public function setAliases( array $aliases )
- {
- $this->aliases = $aliases;
- }
- /**
- * Returns true if this object has aliases.
- *
- * @return bool
- */
- public function hasAliases()
- {
- return $this->aliases !== null ? true : false;
- }
- /**
- * Returns the correct identifier for the alias $alias.
- *
- * If the alias does not exists in the list of aliases
- * it is returned unchanged.
- *
- * @param string $alias
- * @return string
- */
- protected function getIdentifier( $alias )
- {
- $aliasParts = explode( '.', $alias );
- $identifiers = array();
- // If the alias consists of one part, then we just look it up in the
- // array. If we find it, we use it, otherwise we return the name as-is
- // and assume it's just a column name. The alias target can be a fully
- // qualified name (table.column).
- if ( count( $aliasParts ) == 1 )
- {
- if ( $this->aliases !== null &&
- array_key_exists( $alias, $this->aliases ) )
- {
- $alias = $this->aliases[$alias];
- }
- return $alias;
- }
- // If the passed name consist of two parts, we need to check all parts
- // of the passed-in name for aliases, because an alias can be made for
- // both a table name and a column name. For each element we try to find
- // whether we have an alias mapping. Unlike the above case, the alias
- // target can in this case *not* consist of a fully qualified name as
- // this would introduce another part of the name (with two dots).
- for ( $i = 0; $i < count( $aliasParts ); $i++ )
- {
- if ( $this->aliases !== null &&
- array_key_exists( $aliasParts[$i], $this->aliases ) )
- {
- // We only use the found alias if the alias target is not a fully
- // qualified name (table.column).
- $tmpAlias = $this->aliases[$aliasParts[$i]];
- if ( count( explode( '.', $tmpAlias ) ) === 1 )
- {
- $aliasParts[$i] = $this->aliases[$aliasParts[$i]];
- }
- }
- }
- $alias = join( '.', $aliasParts );
- return $alias;
- }
- /**
- * Returns the correct identifiers for the aliases found in $aliases.
- *
- * This method is similar to getIdentifier except that it works on an array.
- *
- * @param array(string) $aliasList
- * @return array(string)
- */
- protected function getIdentifiers( array $aliasList )
- {
- if ( $this->aliases !== null )
- {
- foreach ( $aliasList as $key => $alias )
- {
- $aliasList[$key] = $this->getIdentifier( $alias );
- }
- }
- return $aliasList;
- }
- /**
- * Sets the mode of quoting for parameters passed
- * to SQL functions and operators.
- *
- * Quoting mode is set to ON by default.
- * $q->expr->in( 'column1', 'Hello', 'world' ) will
- * produce SQL "column1 IN ( 'Hello', 'world' )"
- * ( note quotes in SQL ).
- *
- * User must execute setValuesQuoting( false ) before call
- * to function where quoting of parameters is not desirable.
- * Example:
- * <code>
- * $q->expr->setValuesQuoting( false );
- * $q->expr->in( 'column1', 'SELECT * FROM table' )
- * </code>
- * This will produce SQL "column1 IN ( SELECT * FROM table )".
- *
- * Quoting mode will remain unchanged until next call
- * to setValuesQuoting().
- *
- * @param boolean $doQuoting - flag that switch quoting.
- * @return void
- */
- public function setValuesQuoting( $doQuoting )
- {
- $this->quoteValues = $doQuoting;
- }
- /**
- * Returns the SQL to bind logical expressions together using a logical or.
- *
- * lOr() accepts an arbitrary number of parameters. Each parameter
- * must contain a logical expression or an array with logical expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $e = $q->expr;
- * $q->select( '*' )->from( 'table' )
- * ->where( $e->lOr( $e->eq( 'id', $q->bindValue( 1 ) ),
- * $e->eq( 'id', $q->bindValue( 2 ) ) ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @return string a logical expression
- */
- public function lOr()
- {
- $args = func_get_args();
- if ( count( $args ) < 1 )
- {
- throw new ezcQueryVariableParameterException( 'lOr', count( $args ), 1 );
- }
- $elements = ezcQuerySelect::arrayFlatten( $args );
- if ( count( $elements ) == 1 )
- {
- return $elements[0];
- }
- else
- {
- return '( ' . join( ' OR ', $elements ) . ' )';
- }
- }
- /**
- * Returns the SQL to bind logical expressions together using a logical and.
- *
- * lAnd() accepts an arbitrary number of parameters. Each parameter
- * must contain a logical expression or an array with logical expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $e = $q->expr;
- * $q->select( '*' )->from( 'table' )
- * ->where( $e->lAnd( $e->eq( 'id', $q->bindValue( 1 ) ),
- * $e->eq( 'id', $q->bindValue( 2 ) ) ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @return string a logical expression
- */
- public function lAnd()
- {
- $args = func_get_args();
- if ( count( $args ) < 1 )
- {
- throw new ezcQueryVariableParameterException( 'lAnd', count( $args ), 1 );
- }
- $elements = ezcQuerySelect::arrayFlatten( $args );
- if ( count( $elements ) == 1 )
- {
- return $elements[0];
- }
- else
- {
- return '( ' . join( ' AND ', $elements ) . ' )';
- }
- }
- /**
- * Returns the SQL for a logical not, negating the $expression.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $e = $q->expr;
- * $q->select( '*' )->from( 'table' )
- * ->where( $e->eq( 'id', $e->not( 'null' ) ) );
- * </code>
- *
- * @param string $expression
- * @return string a logical expression
- */
- public function not( $expression )
- {
- $expression = $this->getIdentifier( $expression );
- return "NOT ( {$expression} )";
- }
- // math
- /**
- * Returns the SQL to perform the same mathematical operation over an array
- * of values or expressions.
- *
- * basicMath() accepts an arbitrary number of parameters. Each parameter
- * must contain a value or an expression or an array with values or
- * expressions.
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @param string $type the type of operation, can be '+', '-', '*' or '/'.
- * @param string|array(string) $...
- * @return string an expression
- */
- private function basicMath( $type )
- {
- $args = func_get_args();
- $elements = ezcQuerySelect::arrayFlatten( array_slice( $args, 1 ) );
- $elements = $this->getIdentifiers( $elements );
- if ( count( $elements ) < 1 )
- {
- throw new ezcQueryVariableParameterException( $type, count( $args ), 1 );
- }
- if ( count( $elements ) == 1 )
- {
- return $elements[0];
- }
- else
- {
- return '( ' . join( " $type ", $elements ) . ' )';
- }
- }
- /**
- * Returns the SQL to add values or expressions together.
- *
- * add() accepts an arbitrary number of parameters. Each parameter
- * must contain a value or an expression or an array with values or
- * expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->add( 'id', 2 ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @param string|array(string) $...
- * @return string an expression
- */
- public function add()
- {
- $args = func_get_args();
- return $this->basicMath( '+', $args );
- }
- /**
- * Returns the SQL to subtract values or expressions from eachother.
- *
- * subtract() accepts an arbitrary number of parameters. Each parameter
- * must contain a value or an expression or an array with values or
- * expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->subtract( 'id', 2 ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @param string|array(string) $...
- * @return string an expression
- */
- public function sub()
- {
- $args = func_get_args();
- return $this->basicMath( '-', $args );
- }
- /**
- * Returns the SQL to multiply values or expressions by eachother.
- *
- * multiply() accepts an arbitrary number of parameters. Each parameter
- * must contain a value or an expression or an array with values or
- * expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->multiply( 'id', 2 ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @param string|array(string) $...
- * @return string an expression
- */
- public function mul()
- {
- $args = func_get_args();
- return $this->basicMath( '*', $args );
- }
- /**
- * Returns the SQL to divide values or expressions by eachother.
- *
- * divide() accepts an arbitrary number of parameters. Each parameter
- * must contain a value or an expression or an array with values or
- * expressions.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->divide( 'id', 2 ) );
- * </code>
- *
- * @throws ezcDbAbstractionException if called with no parameters.
- * @param string|array(string) $...
- * @return string an expression
- */
- public function div()
- {
- $args = func_get_args();
- return $this->basicMath( '/', $args );
- }
- /**
- * Returns the SQL to check if two values are equal.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->eq( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function eq( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} = {$value2}";
- }
- /**
- * Returns the SQL to check if two values are unequal.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->neq( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function neq( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} <> {$value2}";
- }
- /**
- * Returns the SQL to check if one value is greater than another value.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->gt( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function gt( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} > {$value2}";
- }
- /**
- * Returns the SQL to check if one value is greater than or equal to
- * another value.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->gte( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function gte( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} >= {$value2}";
- }
- /**
- * Returns the SQL to check if one value is less than another value.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->lt( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function lt( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} < {$value2}";
- }
- /**
- * Returns the SQL to check if one value is less than or equal to
- * another value.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->lte( 'id', $q->bindValue( 1 ) ) );
- * </code>
- *
- * @param string $value1 logical expression to compare
- * @param string $value2 logical expression to compare with
- * @return string logical expression
- */
- public function lte( $value1, $value2 )
- {
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$value1} <= {$value2}";
- }
- /**
- * 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>
- *
- * Optimization note: Call setQuotingValues( false ) before using in() with
- * big lists of numeric parameters. This avoid redundant quoting of numbers
- * in resulting SQL query and saves time of converting strings to
- * numbers inside RDBMS.
- *
- * @throws ezcQueryVariableParameterException if called with less than two
- * parameters.
- * @throws ezcQueryInvalidParameterException if the 2nd parameter is an
- * empty array.
- * @param string $column the value that should be matched against
- * @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, 'an empty array', 'a non-empty array' );
- }
- $values = ezcQuerySelect::arrayFlatten( array_slice( $args, 1 ) );
- $column = $this->getIdentifier( $column );
-
- // Special handling of sub selects to avoid double braces
- if ( count( $values ) === 1 && $values[0] instanceof ezcQuerySubSelect )
- {
- return "{$column} IN " . $values[0]->getQuery();
- }
- $values = $this->getIdentifiers( $values );
- if ( count( $values ) == 0 )
- {
- throw new ezcQueryVariableParameterException( 'in', count( $args ), 2 );
- }
- if ( $this->quoteValues )
- {
- foreach ( $values as $key => $value )
- {
- switch ( true )
- {
- case $value instanceof ezcQuerySubSelect:
- $values[$key] = $value->getQuery(); // fix for PHP 5.1.6 because typecasting to string not working there.
- break;
- case is_int( $value ):
- case is_float( $value ):
- $values[$key] = (string) $value;
- break;
- default:
- $values[$key] = $this->db->quote( $value );
- }
- }
- }
-
- return "{$column} IN ( " . join( ', ', $values ) . ' )';
- }
- /**
- * Returns SQL that checks if a expression is null.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->isNull( 'id' ) );
- * </code>
- *
- * @param string $expression the expression that should be compared to null
- * @return string logical expression
- */
- public function isNull( $expression )
- {
- $expression = $this->getIdentifier( $expression );
- return "{$expression} IS NULL";
- }
- /**
- * Returns SQL that checks if an expression evaluates to a value between
- * two values.
- *
- * The parameter $expression is checked if it is between $value1 and $value2.
- *
- * Note: There is a slight difference in the way BETWEEN works on some databases.
- * http://www.w3schools.com/sql/sql_between.asp. If you want complete database
- * independence you should avoid using between().
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select( '*' )->from( 'table' )
- * ->where( $q->expr->between( 'id', $q->bindValue( 1 ), $q->bindValue( 5 ) ) );
- * </code>
- *
- * @param string $expression the value to compare to
- * @param string $value1 the lower value to compare with
- * @param string $value2 the higher value to compare with
- * @return string logical expression
- */
- public function between( $expression, $value1, $value2 )
- {
- $expression = $this->getIdentifier( $expression );
- $value1 = $this->getIdentifier( $value1 );
- $value2 = $this->getIdentifier( $value2 );
- return "{$expression} BETWEEN {$value1} AND {$value2}";
- }
- /**
- * Match a partial string in a column.
- *
- * Like will look for the pattern in the column given. Like accepts
- * the wildcards '_' matching a single character and '%' matching
- * any number of characters.
- *
- * @param string $expression the name of the expression to match on
- * @param string $pattern the pattern to match with.
- */
- public function like( $expression, $pattern )
- {
- $expression = $this->getIdentifier( $expression );
- return "{$expression} LIKE {$pattern}";
- }
- // aggregate functions
- /**
- * Returns the average value of a column
- *
- * @param string $column the column to use
- * @return string
- */
- public function avg( $column )
- {
- $column = $this->getIdentifier( $column );
- return "AVG( {$column} )";
- }
- /**
- * Returns the number of rows (without a NULL value) of a column
- *
- * If a '*' is used instead of a column the number of selected rows
- * is returned.
- *
- * @param string $column the column to use
- * @return string
- */
- public function count( $column )
- {
- $column = $this->getIdentifier( $column );
- return "COUNT( {$column} )";
- }
- /**
- * Returns the highest value of a column
- *
- * @param string $column the column to use
- * @return string
- */
- public function max( $column )
- {
- $column = $this->getIdentifier( $column );
- return "MAX( {$column} )";
- }
- /**
- * Returns the lowest value of a column
- *
- * @param string $column the column to use
- * @return string
- */
- public function min( $column )
- {
- $column = $this->getIdentifier( $column );
- return "MIN( {$column} )";
- }
- /**
- * Returns the total sum of a column
- *
- * @param string $column the column to use
- * @return string
- */
- public function sum( $column )
- {
- $column = $this->getIdentifier( $column );
- return "SUM( {$column} )";
- }
- // scalar functions
- /**
- * Returns the md5 sum of $column.
- *
- * Note: Not SQL92, but common functionality
- *
- * @param string $column
- * @return string
- */
- public function md5( $column )
- {
- $column = $this->getIdentifier( $column );
- return "MD5( {$column} )";
- }
- /**
- * Returns the length of text field $column
- *
- * @param string $column
- * @return string
- */
- public function length( $column )
- {
- $column = $this->getIdentifier( $column );
- return "LENGTH( {$column} )";
- }
- /**
- * Rounds a numeric field to the number of decimals specified.
- *
- * @param string $column
- * @param int $decimals
- * @return string
- */
- public function round( $column, $decimals )
- {
- $column = $this->getIdentifier( $column );
- return "ROUND( {$column}, {$decimals} )";
- }
- /**
- * Returns the remainder of the division operation
- * $expression1 / $expression2.
- *
- * @param string $expression1
- * @param string $expression2
- * @return string
- */
- public function mod( $expression1, $expression2 )
- {
- $expression1 = $this->getIdentifier( $expression1 );
- $expression2 = $this->getIdentifier( $expression2 );
- return "MOD( {$expression1}, {$expression2} )";
- }
- /**
- * Returns the current system date and time in the database internal
- * format.
- *
- * @return string
- */
- public function now()
- {
- return "NOW()";
- }
- // string functions
- /**
- * 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 "substring( {$value} from {$from} )";
- }
- else
- {
- $len = $this->getIdentifier( $len );
- return "substring( {$value} from {$from} for {$len} )";
- }
- }
- /**
- * Returns a series of strings concatinated
- *
- * concat() accepts an arbitrary number of parameters. Each parameter
- * must contain an expression or an array with expressions.
- *
- * @param string|array(string) $... strings that will be concatinated.
- */
- public function concat()
- {
- $args = func_get_args();
- $cols = ezcQuerySelect::arrayFlatten( $args );
- if ( count( $cols ) < 1 )
- {
- throw new ezcQueryVariableParameterException( 'concat', count( $args ), 1 );
- }
- $cols = $this->getIdentifiers( $cols );
- return "CONCAT( " . join( ', ', $cols ) . ' )';
- }
- /**
- * 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 "LOCATE( '{$substr}', {$value} )";
- }
- /**
- * Returns the SQL to change all characters to lowercase
- *
- * @param string $value
- * @return string
- */
- public function lower( $value )
- {
- $value = $this->getIdentifier( $value );
- return "LOWER( {$value} )";
- }
- /**
- * Returns the SQL to change all characters to uppercase
- *
- * @param string $value
- * @return string
- */
- public function upper( $value )
- {
- $value = $this->getIdentifier( $value );
- return "UPPER( {$value} )";
- }
- /**
- * Returns the SQL to calculate the next lowest integer value from the number.
- *
- * @param string $number
- * @return string
- */
- public function floor( $number )
- {
- $number = $this->getIdentifier( $number );
- return " FLOOR( {$number} ) ";
- }
- /**
- * Returns the SQL to calculate the next highest integer value from the number.
- *
- * @param string $number
- * @return string
- */
- public function ceil( $number )
- {
- $number = $this->getIdentifier( $number );
- return " CEIL( {$number} ) ";
- }
- /**
- * 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 "( {$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} )";
- }
- /**
- * 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} )";
- }
- /**
- * 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 );
- return " UNIX_TIMESTAMP( {$column} ) ";
- }
- /**
- * 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 );
- 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 );
- return " {$column} + INTERVAL {$expr} {$type} ";
- }
- /**
- * Returns the SQL that extracts parts from a timestamp value.
- *
- * @param string $column The column to operate on
- * @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 );
- return " EXTRACT( {$type} FROM {$column} ) ";
- }
- /**
- * Returns a searched CASE statement.
- *
- * Accepts an arbitrary number of parameters.
- * The first parameter (array) must always be specified, the last
- * parameter (string) specifies the ELSE result.
- *
- * Example:
- * <code>
- * $q = ezcDbInstance::get()->createSelectQuery();
- * $q->select(
- * $q->expr->searchedCase(
- * array( $q->expr->gte( 'column1', 20 ), 'column1' )
- * , array( $q->expr->gte( 'column2', 50 ), 'column2' )
- * , 'column3'
- * )
- * )
- * ->from( 'table' );
- * </code>
- *
- * @throws ezcQueryVariableParameterException
- * @return string
- */
- public function searchedCase()
- {
- $args = func_get_args();
- if ( count( $args ) === 0 )
- {
- throw new ezcQueryVariableParameterException( 'searchedCase', count( $args ), 1 );
- }
- $expr = ' CASE';
- foreach ( $args as $arg )
- {
- if ( is_array( $arg ) && count( $arg ) == 2 )
- {
- $column1 = $this->getIdentifier( $arg[0] );
- $column2 = $this->getIdentifier( $arg[1] );
- $expr .= " WHEN {$column1} THEN {$column2}";
- }
- else if ( is_scalar( $arg ) )
- {
- $column = $this->getIdentifier( $arg );
- $expr .= " ELSE {$column}";
- }
- }
- $expr .= ' END ';
- return $expr;
- }
- }
- ?>