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
[email protected]
http://lists.ez.no/mailman/listinfo/svn-components
