Author: Sergey Alexeev
Date: 2007-01-15 13:44:26 +0100 (Mon, 15 Jan 2007)
New Revision: 4507
Log:
- Fixed bug:#8240: ezcQueryExpression::in does not work with strings.
Modified:
trunk/Database/ChangeLog
trunk/Database/src/handler.php
trunk/Database/src/handlers/oracle.php
trunk/Database/src/handlers/pgsql.php
trunk/Database/src/handlers/sqlite.php
trunk/Database/src/sqlabstraction/expression.php
trunk/Database/src/sqlabstraction/implementations/expression_oracle.php
trunk/Database/src/sqlabstraction/implementations/expression_pgsql.php
trunk/Database/src/sqlabstraction/implementations/query_select_sqlite.php
trunk/Database/tests/sqlabstraction/expression_test.php
trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
Modified: trunk/Database/ChangeLog
===================================================================
--- trunk/Database/ChangeLog 2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/ChangeLog 2007-01-15 12:44:26 UTC (rev 4507)
@@ -2,8 +2,8 @@
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
- Implemented enhancement #8473: Oracle Limit/Offset optimization.
+- Fixed bug:#8240: ezcQueryExpression::in does not work with strings.
-
1.2 - Monday 18 December 2006
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Modified: trunk/Database/src/handler.php
===================================================================
--- trunk/Database/src/handler.php 2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/src/handler.php 2007-01-15 12:44:26 UTC (rev 4507)
@@ -271,7 +271,7 @@
*/
public function createExpression()
{
- return new ezcQueryExpression();
+ return new ezcQueryExpression( $this );
}
/**
Modified: trunk/Database/src/handlers/oracle.php
===================================================================
--- trunk/Database/src/handlers/oracle.php 2007-01-15 12:17:52 UTC (rev
4506)
+++ trunk/Database/src/handlers/oracle.php 2007-01-15 12:44:26 UTC (rev
4507)
@@ -90,7 +90,7 @@
*/
public function createExpression()
{
- return new ezcQueryExpressionOracle();
+ return new ezcQueryExpressionOracle( $this );
}
/**
Modified: trunk/Database/src/handlers/pgsql.php
===================================================================
--- trunk/Database/src/handlers/pgsql.php 2007-01-15 12:17:52 UTC (rev
4506)
+++ trunk/Database/src/handlers/pgsql.php 2007-01-15 12:44:26 UTC (rev
4507)
@@ -94,8 +94,7 @@
*/
public function createExpression()
{
- $version = $this->getAttribute( PDO::ATTR_SERVER_VERSION );
- return new ezcQueryExpressionPgsql( substr( $version, 0, 1 ) );
+ return new ezcQueryExpressionPgsql( $this );
}
/**
Modified: trunk/Database/src/handlers/sqlite.php
===================================================================
--- trunk/Database/src/handlers/sqlite.php 2007-01-15 12:17:52 UTC (rev
4506)
+++ trunk/Database/src/handlers/sqlite.php 2007-01-15 12:44:26 UTC (rev
4507)
@@ -110,7 +110,7 @@
*/
public function createExpression()
{
- return new ezcQueryExpressionSqlite();
+ return new ezcQueryExpressionSqlite( $this );
}
/**
Modified: trunk/Database/src/sqlabstraction/expression.php
===================================================================
--- trunk/Database/src/sqlabstraction/expression.php 2007-01-15 12:17:52 UTC
(rev 4506)
+++ trunk/Database/src/sqlabstraction/expression.php 2007-01-15 12:44:26 UTC
(rev 4507)
@@ -24,6 +24,13 @@
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')
@@ -32,10 +39,21 @@
private $aliases = null;
/**
+ * The flag that switch quoting mode for
+ * values provided by user in miscelaneous SQL functions.
+ *
+ * @var boolean
+ */
+ private $quoteValues = true;
+
+ /**
* Constructs an empty ezcQueryExpression
+ * @param PDO $db
+ * @param array(string=>string) $aliases
*/
- public function __construct( array $aliases = array() )
+ public function __construct( PDO $db, array $aliases = array() )
{
+ $this->db = $db;
if ( !empty( $aliases ) )
{
$this->aliases = $aliases;
@@ -117,6 +135,36 @@
}
/**
+ * 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
@@ -481,6 +529,11 @@
* $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 ezcDbAbstractionException if called with less than two
parameters..
* @param string $column the value that should be matched against
@@ -503,6 +556,19 @@
{
throw new ezcQueryVariableParameterException( 'in', count( $args
), 2 );
}
+
+ if ( $this->quoteValues )
+ {
+ foreach ( $values as $key => $value )
+ {
+ //check if value already quoted and do nothing if it is.
+ if ( !( substr( $value, 0, 1 ) == "'" && substr( $value, -1, 1
) == "'" ) )
+ {
+ $values[$key] = $this->db->quote( $value );
+ }
+ }
+ }
+
return "{$column} IN ( " . join( ', ', $values ) . ' )';
}
Modified:
trunk/Database/src/sqlabstraction/implementations/expression_oracle.php
===================================================================
--- trunk/Database/src/sqlabstraction/implementations/expression_oracle.php
2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/src/sqlabstraction/implementations/expression_oracle.php
2007-01-15 12:44:26 UTC (rev 4507)
@@ -19,10 +19,11 @@
{
/**
* Constructs an empty ezcQueryExpression
+ * @param PDO $db
*/
- public function __construct()
+ public function __construct( PDO $db )
{
- parent::__construct();
+ parent::__construct( $db );
}
/**
Modified: trunk/Database/src/sqlabstraction/implementations/expression_pgsql.php
===================================================================
--- trunk/Database/src/sqlabstraction/implementations/expression_pgsql.php
2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/src/sqlabstraction/implementations/expression_pgsql.php
2007-01-15 12:44:26 UTC (rev 4507)
@@ -23,17 +23,17 @@
* @var int
*/
private $version;
-
+
/**
- * Constructs an empty ezcQueryExpression with the versoin $version.
- *
- * @todo Provide version number or a pointer to $db?
- * @param int $version
+ * Constructs an empty ezcQueryExpression
+ * @param PDO $db
*/
- public function __construct( $version )
+ public function __construct( PDO $db )
{
- parent::__construct();
- $this->version = $version;
+ parent::__construct( $db );
+
+ $version = $db->getAttribute( PDO::ATTR_SERVER_VERSION );
+ $this->version = substr( $version, 0, 1 );
}
/**
Modified:
trunk/Database/src/sqlabstraction/implementations/query_select_sqlite.php
===================================================================
--- trunk/Database/src/sqlabstraction/implementations/query_select_sqlite.php
2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/src/sqlabstraction/implementations/query_select_sqlite.php
2007-01-15 12:44:26 UTC (rev 4507)
@@ -39,6 +39,7 @@
/**
* Constructs a new ezcQuerySelectSqlite object.
+ * @param PDO $db
*/
public function __construct( PDO $db )
{
Modified: trunk/Database/tests/sqlabstraction/expression_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/expression_test.php 2007-01-15
12:17:52 UTC (rev 4506)
+++ trunk/Database/tests/sqlabstraction/expression_test.php 2007-01-15
12:44:26 UTC (rev 4507)
@@ -248,16 +248,41 @@
public function testInSingle()
{
- $reference = 'id IN ( 1 )';
+ $reference = "id IN ( '1' )";
$this->assertEquals( $reference, $this->e->in( 'id', 1 ) );
}
public function testInMulti()
{
- $reference = 'id IN ( 1, 2 )';
+ $reference = "id IN ( '1', '2' )";
$this->assertEquals( $reference, $this->e->in( 'id', 1, 2 ) );
}
+
+ public function testInStringQuoting()
+ {
+ if ( $this->db->getName() == 'mysql' )
+ {
+ $reference = "id IN ( 'That\'s should be quoted correctly' )";
+ }
+ else
+ {
+ $reference = "id IN ( 'That''s should be quoted correctly' )";
+ }
+ $this->assertEquals( $reference, $this->e->in( 'id', "That's should be
quoted correctly" ) );
+ }
+ public function testInMultyString()
+ {
+ $reference = "id IN ( 'Hello', 'world' )";
+ $this->assertEquals( $reference, $this->e->in( 'id', 'Hello', 'world'
) );
+ }
+
+ public function testInAlreadyQuoted()
+ {
+ $reference = "id IN ( 'Hello', 'world' )";
+ $this->assertEquals( $reference, $this->e->in( 'id', "'Hello'",
"'world'" ) );
+ }
+
public function testIsNull()
{
$reference = 'id IS NULL';
@@ -554,6 +579,19 @@
$this->assertEquals( 1, $rows );
}
+ public function testInSingleStringImpl()
+ {
+ $this->q->select( '*' )->from( 'query_test' )
+ ->where( $this->e->in( 'section', 'Norway' ) );
+ $stmt = $this->db->query( $this->q->getQuery() );
+ $rows = 0;
+ foreach ( $stmt as $row )
+ {
+ $rows++;
+ }
+ $this->assertEquals( 2, $rows );
+ }
+
public function testInMultiImpl()
{
$this->q->select( '*' )->from( 'query_test' )
@@ -567,6 +605,32 @@
$this->assertEquals( 2, $rows );
}
+ public function testInMultyStringImpl()
+ {
+ $this->q->select( '*' )->from( 'query_test' )
+ ->where( $this->e->in( 'section', 'Norway', 'Ukraine' ) );
+ $stmt = $this->db->query( $this->q->getQuery() );
+ $rows = 0;
+ foreach ( $stmt as $row )
+ {
+ $rows++;
+ }
+ $this->assertEquals( 3, $rows );
+ }
+
+ public function testInAlreadyQuotedImpl()
+ {
+ $this->q->select( '*' )->from( 'query_test' )
+ ->where( $this->e->in( 'section', "'Norway'", "'Ukraine'" ) );
+ $stmt = $this->db->query( $this->q->getQuery() );
+ $rows = 0;
+ foreach ( $stmt as $row )
+ {
+ $rows++;
+ }
+ $this->assertEquals( 3, $rows );
+ }
+
public function testIsNullImpl()
{
$this->q->select( '*' )->from( 'query_test' )
@@ -897,8 +961,13 @@
public function testInMultiImplWithAlias()
{
$this->q->setAliases( array( 'identifier' => 'id' ) );
+ $this->q->expr->setValuesQuoting( false );
+
$this->q->select( '*' )->from( 'query_test' )
->where( $this->q->expr->in( 'identifier', 1 , 'identifier' ) );
+
+ $this->q->expr->setValuesQuoting( true );
+
$stmt = $this->db->query( $this->q->getQuery() );
$rows = 0;
foreach ( $stmt as $row )
Modified: trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-01-15 12:17:52 UTC (rev 4506)
+++ trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-01-15 12:44:26 UTC (rev 4507)
@@ -71,6 +71,7 @@
$name = 'IBM';
$name2 = 'company';
$q = new ezcQuerySelect( ezcDbInstance::get() );
+ $q->expr->setValuesQuoting( false );
// subselect
$q2 = $q->subSelect();
@@ -102,6 +103,7 @@
// subselect
$q2 = $q->subSelect();
+ $q->expr->setValuesQuoting( false );
// bind values
$q2->select('company')
@@ -111,7 +113,7 @@
$q->select('*')->from( 'query_test' )
->where( ' id >= 1 ', $q->expr->in( 'company',
$q2->getQuery() ) )
->orderBy( 'id' );
-
+
$stmt = $q->prepare();
$stmt->execute();
@@ -127,12 +129,15 @@
$name = 'IBM';
$name2 = 'company';
$q = new ezcQuerySelect( ezcDbInstance::get() );
+ $q->expr->setValuesQuoting( false );
// subselect
$q2 = $q->subSelect();
// sub subselect
$q3 = $q2->subSelect();
+ $q3->expr->setValuesQuoting( false );
+
$q3->select('*')
->from( 'query_test2' )
->where( $q3->expr->in( 'company', 'IBM', 'eZ systems' ) );
--
svn-components mailing list
svn-components@lists.ez.no
http://lists.ez.no/mailman/listinfo/svn-components
