Author: dr Date: Fri Jan 4 14:17:25 2008 New Revision: 7072 Log: - Fixed issue #11786: quoteIdentifier() incorrectly quotes unambiguous identifiers. #- Also added more test cases and updated the setAliases() documentation.
Modified: trunk/Database/ChangeLog trunk/Database/src/sqlabstraction/expression.php trunk/Database/src/sqlabstraction/query.php trunk/Database/tests/sqlabstraction/expression_test.php trunk/Database/tests/sqlabstraction/query_test.php Modified: trunk/Database/ChangeLog ============================================================================== --- trunk/Database/ChangeLog [iso-8859-1] (original) +++ trunk/Database/ChangeLog [iso-8859-1] Fri Jan 4 14:17:25 2008 @@ -1,8 +1,10 @@ 1.3.4 - [RELEASEDATE] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -- Fixed bug #11266: ezcQueryExpression->in( 'colname', array() ) produces a +- Fixed issue #11266: ezcQueryExpression->in( 'colname', array() ) produces a strange exception message. +- Fixed issue #11786: quoteIdentifier() incorrectly quotes unambiguous + identifiers. 1.3.3 - Wednesday 05 December 2007 Modified: trunk/Database/src/sqlabstraction/expression.php ============================================================================== --- trunk/Database/src/sqlabstraction/expression.php [iso-8859-1] (original) +++ trunk/Database/src/sqlabstraction/expression.php [iso-8859-1] Fri Jan 4 14:17:25 2008 @@ -114,19 +114,40 @@ { $aliasParts = explode( '.', $alias ); $identifiers = array(); - foreach ( $aliasParts as $singleAliasName ) + // 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( $singleAliasName, $this->aliases ) ) + array_key_exists( $alias, $this->aliases ) ) { - $identifiers[]= $this->aliases[$singleAliasName]; + $alias = $this->aliases[$alias]; } - else + 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 ) ) { - $identifiers[]= $singleAliasName; + // 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( '.', $identifiers ); + $alias = join( '.', $aliasParts ); return $alias; } Modified: trunk/Database/src/sqlabstraction/query.php ============================================================================== --- trunk/Database/src/sqlabstraction/query.php [iso-8859-1] (original) +++ trunk/Database/src/sqlabstraction/query.php [iso-8859-1] Fri Jan 4 14:17:25 2008 @@ -116,8 +116,9 @@ /** * Sets the aliases $aliases for this object. * - * The aliases should be in the form array( "aliasName" => "databaseName" ). Table and - * column aliases can be mixed in the array. + * The aliases should be in the form array( "aliasName" => "databaseName" ) + * Each alias defines a relation between a user-defined name and a name + * in the database. This is supported for table names as column names. * * The aliases can be used to substitute the column and table names with more * friendly names. The substitution is done when the query is built, not using @@ -125,14 +126,66 @@ * * Example of a select query with aliases: * <code> + * <?php * $q->setAliases( array( 'Identifier' => 'id', 'Company' => 'company' ) ); - * $this->q->select( 'Company' )->from( 'table' )->where( $q->expr->eq( 'Identifier', 5 ) ); + * $q->select( 'Company' ) + * ->from( 'table' ) + * ->where( $q->expr->eq( 'Identifier', 5 ) ); * echo $q->getQuery(); + * ?> * </code> * * This example will output SQL similar to: * <code> * SELECT company FROM table WHERE id = 5 + * </code> + * + * Aliasses also take effect for composite names in the form + * tablename.columnname as the following example shows: + * <code> + * <?php + * $q->setAliases( array( 'Order' => 'orders', 'Recipient' => 'company' ) ); + * $q->select( 'Order.Recipient' ) + * ->from( 'Order' ); + * echo $q->getQuery(); + * ?> + * </code> + * + * This example will output SQL similar to: + * <code> + * SELECT orders.company FROM orders; + * </code> + * + * It is even possible to have an alias point to a table name/column name + * combination. This will only work for alias names without a . (dot): + * <code> + * <?php + * $q->setAliases( array( 'Order' => 'orders', 'Recipient' => 'orders.company' ) ); + * $q->select( 'Recipient' ) + * ->from( 'Order' ); + * echo $q->getQuery(); + * ?> + * </code> + * + * This example will output SQL similar to: + * <code> + * SELECT orders.company FROM orders; + * </code> + * + * In the following example, the Recipient alias will not be used, as it is + * points to a fully qualified name - the Order alias however is used: + * <code> + * <?php + * $q->setAliases( array( 'Order' => 'orders', 'Recipient' => 'orders.company' ) ); + * $q->select( 'Order.Recipient' ) + * ->from( 'Order' ); + * echo $q->getQuery(); + * ?> + * </code> + * + * This example will output SQL similar to: + * <code> + * SELECT orders.Recipient FROM orders; * </code> * * @param array(string=>string) $aliases @@ -169,19 +222,40 @@ { $aliasParts = explode( '.', $alias ); $identifiers = array(); - foreach ( $aliasParts as $singleAliasName ) + // 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( $singleAliasName, $this->aliases ) ) - { - $identifiers[]= $this->aliases[$singleAliasName]; - } - else - { - $identifiers[]= $singleAliasName; - } - } - $alias = join( '.', $identifiers ); + 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; } Modified: trunk/Database/tests/sqlabstraction/expression_test.php ============================================================================== --- trunk/Database/tests/sqlabstraction/expression_test.php [iso-8859-1] (original) +++ trunk/Database/tests/sqlabstraction/expression_test.php [iso-8859-1] Fri Jan 4 14:17:25 2008 @@ -1596,6 +1596,32 @@ $this->assertEquals( $reference, $this->q->getQuery() ); } + public function testTableAlias() + { + $reference = 'SELECT * FROM table1, table2 WHERE table1.column < table2.id'; + + $this->q->setAliases( array( 't_alias' => 'table1' ) ); + + $this->q->select( '*' ) + ->from( 't_alias', 'table2' ) + ->where( $this->q->expr->lt('t_alias.column', 'table2.id' ) ); + + $this->assertEquals( $reference, $this->q->getQuery() ); + } + + public function testColumnAlias() + { + $reference = 'SELECT * FROM table1, table2 WHERE table1.column < table2.id'; + + $this->q->setAliases( array( 'c_alias' => 'column' ) ); + + $this->q->select( '*' ) + ->from( 'table1', 'table2' ) + ->where( $this->q->expr->lt('table1.c_alias', 'table2.id' ) ); + + $this->assertEquals( $reference, $this->q->getQuery() ); + } + public function testPositionImplWithAlias() { $this->q->setAliases( array( 'text' => 'company' ) ); Modified: trunk/Database/tests/sqlabstraction/query_test.php ============================================================================== --- trunk/Database/tests/sqlabstraction/query_test.php [iso-8859-1] (original) +++ trunk/Database/tests/sqlabstraction/query_test.php [iso-8859-1] Fri Jan 4 14:17:25 2008 @@ -84,6 +84,93 @@ $this->assertEquals( 'MyTable', $this->q->id( 'MyTable' ) ); } + public function testGetIdentifierWithAlias() + { + $aliases = array( 'id' => 'table2.id' ); + $this->q->setAliases( $aliases ); + + $this->assertEquals( 'table2.id', $this->q->id( 'id' ) ); + $this->assertEquals( 'table1.id', $this->q->id( 'table1.id' ) ); + } + + public function testTableAlias() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT column FROM table1'; + + $q->setAliases( array( 't_alias' => 'table1' ) ); + $q->select( 'column' )->from( 't_alias' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testColumnAlias1() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT column1 FROM table1'; + + $q->setAliases( array( 'c_alias1' => 'column1' ) ); + $q->select( 'c_alias1' )->from( 'table1' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testColumnAlias2() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT column1, column2 FROM table1'; + + $q->setAliases( array( 'c_alias1' => 'column1' ) ); + $q->select( 'c_alias1', 'column2' )->from( 'table1' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testColumnAlias3() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT column1, column2 FROM table1'; + + $q->setAliases( array( 'c_alias1' => 'column1', 'c_alias2' => 'column2' ) ); + $q->select( 'c_alias1', 'c_alias2' )->from( 'table1' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testTableAndColumnAlias1() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT column1 FROM table1'; + + $q->setAliases( array( 't_column1' => 'column1', 't_alias' => 'table1' ) ); + $q->select( 't_column1' )->from( 't_alias' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testTableAndColumnAlias2() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT table1.column1 FROM table1'; + + $q->setAliases( array( 't_column1' => 'column1', 't_alias' => 'table1' ) ); + $q->select( 't_alias.t_column1' )->from( 't_alias' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + + public function testTableAndColumnAlias3() + { + $q = ezcDbInstance::get()->createSelectQuery(); + $reference = 'SELECT orders.Recipient FROM orders'; + + $q->setAliases( array( 'Order' => 'orders', 'Recipient' => 'orders.company' ) ); + $q->select( 'Order.Recipient' ) + ->from( 'Order' ); + + $this->assertEquals( $reference, $q->getQuery() ); + } + public function testBindValue() { $value = 42; -- svn-components mailing list svn-components@lists.ez.no http://lists.ez.no/mailman/listinfo/svn-components