Database/tests/sqlabstraction/query_subselect_test.php - zetacomponents - Git at Google

 <?php
 /**
  *
  * 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.
  *
  * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
  * @version //autogentag//
  * @filesource
  * @package Database
  * @subpackage Tests
  */

 class TestSubSelect extends ezcQuerySelect
 {
     public $db;

     // @todo: Do we need the below? We use them for testing now, but
     // they could come in handy if we want to manipulate SELECT queries in
     // Persistent Object.
     public function buildSelect()
     {
         return $this->selectString;
     }

     public function buildFrom()
     {
         return $this->fromString;
     }

     public function buildWhere()
     {
         return $this->whereString;
     }

     public function buildOrder()
     {
         return $this->orderString;
     }

     public function buildGroup()
     {
         return $this->groupString;
     }

     public function buildLimit()
     {
         return $this->limitString;
     }
 }

 /**
  * Testing the SQL abstraction layer.
  * This file tests that the methods actually produce correct output for the base
  * implementation regardless of how they methods are called. The _impl file tests
  * the same again, but with full SQL calls, only using one call type and on the database.
  *
  * @package Database
  * @subpackage Tests
  * @todo, test with null input values
  */
 class ezcQuerySubSelectTest extends ezcTestCase
 {
     private $q; // query
     private $e; // queryExpression
     protected function setUp()
     {
         try
         {
             $db = ezcDbInstance::get();
         }
         catch ( Exception $e )
         {
             $this->markTestSkipped();
         }

         $this->q = new TestSubSelect( $db );
         $this->e = $this->q->expr;
     }

     public function testSubSelect()
     {
         $reference = '( SELECT column FROM table WHERE id = 1 )';
         $q2 = $this->q->subSelect();
         $q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', 1 ) );

         $this->assertEquals( $reference, $q2->getQuery() );
     }

     public function testSubSelectNotQuoted()
     {
         $reference = 'SELECT * FROM test_table WHERE id IN ( SELECT column FROM sub_table WHERE id = 1 )';
         $this->q->select( '*' )
                 ->from( 'test_table' );

         $subQ = $this->q->subSelect();
         $subQ->select( 'column' )
            ->from( 'sub_table' )
            ->where( $subQ->expr->eq( 'id', 1 ) );

         $this->q->where( $this->q->expr->in( 'id', $subQ ) );

         $this->assertEquals( $reference, $this->q->getQuery() );
     }

     public function testSubSubSelect()
     {
         $reference = '( SELECT column FROM table WHERE id = ( SELECT * FROM table2 ) )';
         $q2 = $this->q->subSelect();
         $q3 = $q2->subSelect();
         $q3->select( '*' )->from( 'table2' );
         $q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', $q3->getQuery() ) );

         $this->assertEquals( $reference, $q2->getQuery() );
     }

     public function testDistinctSubSelect()
     {
         $reference = 'SELECT DISTINCT * FROM table WHERE id = ( SELECT DISTINCT column FROM table2 )';

         $q2 = $this->q->subSelect();
         $q2->selectDistinct( 'column' )->from( 'table2' );
         $this->q
             ->selectDistinct( '*' )
             ->from( 'table' )
             ->where(
                 $this->q->expr->eq( 'id', $q2->getQuery() )
             );

         $this->assertEquals( $reference, $this->q->getQuery() );

     }

     public function testSubSelectIn()
     {
         $reference = 'SELECT * FROM table WHERE id IN ( SELECT column FROM table2 )';

         $q2 = $this->q->subSelect();
         $q2->select( 'column' )->from( 'table2' );
         $this->q
             ->select( '*' )
             ->from( 'table' )
             ->where(
                 $this->q->expr->in( 'id', $q2 )
             );

         $this->assertEquals( $reference, $this->q->getQuery() );

     }

     public function testBindAuto()
     {
         $val1 = '';
         $val2 = '';

         $reference = '( SELECT column FROM table WHERE id = :ezcValue1 AND id2 = :ezcValue2 )';
         $q2 = $this->q->subSelect();
         $q2->select( 'column' )
              ->from( 'table' )
                ->where( $q2->expr->eq( 'id', $q2->bindParam( $val1 ) ) )
                ->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2 ) ) );

         $this->assertEquals( $reference, $q2->getQuery() );
     }

     public function testBindManual()
     {
         $reference = '( SELECT column FROM table WHERE id = :test1 AND id2 = :test2 )';
         $val1 = '';
         $val2 = '';
         $q2 = $this->q->subSelect();
         $q2->select( 'column' )
              ->from( 'table' )
                ->where( $q2->expr->eq( 'id', $q2->bindParam( $val1, ':test1' ) ) )
                ->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2, ':test2' ) ) );

         $this->assertEquals( $reference, $q2->getQuery() );
     }

     public function testBug11784()
     {
         $db = ezcDbInstance::get();
         $q = $db->createSelectQuery();
         $q->select( 'somecol' )->from( 'quiz' );

         $qQuestions = $q->subSelect();
         $qQuestions->select( 'id' )->from( 'question' )->where(
             $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
         );

         $q->where(
             $q->expr->in( 'question', $qQuestions )
         );

         $this->assertEquals( "SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
     }

     public function testSubselectWithUpdate()
     {
         $db = ezcDbInstance::get();
         $q = $db->createUpdateQuery();
         $q->update( 'quiz' )->set( 'somecol', $q->bindValue( 'test' ) );

         $qQuestions = $q->subSelect();
         $qQuestions->select( 'id' )->from( 'question' )->where(
             $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
         );

         $q->where(
             $q->expr->in( 'question', $qQuestions )
         );

         $this->assertEquals( "UPDATE quiz SET somecol = :ezcValue1 WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue2 )", $q->getQuery() );
     }


     public function testSubselectWithDelete()
     {
         $db = ezcDbInstance::get();
         $q = $db->createDeleteQuery();
         $q->deleteFrom( 'quiz' );

         $qQuestions = $q->subSelect();
         $qQuestions->select( 'id' )->from( 'question' )->where(
             $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
         );

         $q->where(
             $q->expr->in( 'question', $qQuestions )
         );

         $this->assertEquals( "DELETE FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
     }

     // Verifies issue #11784 is fixed. Code taken from there.
     public function testSubselectNotQuotedInInExpr()
     {
         $db = ezcDbInstance::get();
         $q = $db->createSelectQuery();
         $q->select( 'somecol' )->from( 'quiz' );

         $qQuestions = $q->subSelect();
         $qQuestions->select( 'id' )->from( 'question' )->where(
             $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
         );

         $q->where(
             $q->expr->in( 'question', $qQuestions )
         );

         $this->assertEquals(
             'SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )',
             $q->getQuery()
         );
     }

     public function testSubselectWithAlias()
     {
         $db = ezcDbInstance::get();

         $q = $db->createSelectQuery();
         $q->setAliases( array( 'ID' => 'id' ) );
         $q = $q->select( 'ID', 'name' )->from( 'main' );

         $q2 = $q->subSelect();
             $q2->select( 'main_id' )->from( 'sub' );
             $q2->limit(20,0);

         $q->innerJoin( $q->alias( $q2, 'sub_items' ), 'sub_items.main_id', 'main.id' );

         $this->assertEquals( "SELECT id, name FROM main INNER JOIN ( SELECT main_id FROM sub LIMIT 20 OFFSET 0 ) AS sub_items ON sub_items.main_id = main.id", $q->getQuery() );
     }

     public static function suite()
     {
         return new PHPUnit_Framework_TestSuite( 'ezcQuerySubSelectTest' );
     }
 }
 ?>
	<?php
	/**
	*
	* 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.
	*
	* @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
	* @version //autogentag//
	* @filesource
	* @package Database
	* @subpackage Tests
	*/

	class TestSubSelect extends ezcQuerySelect
	{
	public $db;

	// @todo: Do we need the below? We use them for testing now, but
	// they could come in handy if we want to manipulate SELECT queries in
	// Persistent Object.
	public function buildSelect()
	{
	return $this->selectString;
	}

	public function buildFrom()
	{
	return $this->fromString;
	}

	public function buildWhere()
	{
	return $this->whereString;
	}

	public function buildOrder()
	{
	return $this->orderString;
	}

	public function buildGroup()
	{
	return $this->groupString;
	}

	public function buildLimit()
	{
	return $this->limitString;
	}
	}

	/**
	* Testing the SQL abstraction layer.
	* This file tests that the methods actually produce correct output for the base
	* implementation regardless of how they methods are called. The _impl file tests
	* the same again, but with full SQL calls, only using one call type and on the database.
	*
	* @package Database
	* @subpackage Tests
	* @todo, test with null input values
	*/
	class ezcQuerySubSelectTest extends ezcTestCase
	{
	private $q; // query
	private $e; // queryExpression
	protected function setUp()
	{
	try
	{
	$db = ezcDbInstance::get();
	}
	catch ( Exception $e )
	{
	$this->markTestSkipped();
	}

	$this->q = new TestSubSelect( $db );
	$this->e = $this->q->expr;
	}

	public function testSubSelect()
	{
	$reference = '( SELECT column FROM table WHERE id = 1 )';
	$q2 = $this->q->subSelect();
	$q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', 1 ) );

	$this->assertEquals( $reference, $q2->getQuery() );
	}

	public function testSubSelectNotQuoted()
	{
	$reference = 'SELECT * FROM test_table WHERE id IN ( SELECT column FROM sub_table WHERE id = 1 )';
	$this->q->select( '*' )
	->from( 'test_table' );

	$subQ = $this->q->subSelect();
	$subQ->select( 'column' )
	->from( 'sub_table' )
	->where( $subQ->expr->eq( 'id', 1 ) );

	$this->q->where( $this->q->expr->in( 'id', $subQ ) );

	$this->assertEquals( $reference, $this->q->getQuery() );
	}

	public function testSubSubSelect()
	{
	$reference = '( SELECT column FROM table WHERE id = ( SELECT * FROM table2 ) )';
	$q2 = $this->q->subSelect();
	$q3 = $q2->subSelect();
	$q3->select( '*' )->from( 'table2' );
	$q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', $q3->getQuery() ) );

	$this->assertEquals( $reference, $q2->getQuery() );
	}

	public function testDistinctSubSelect()
	{
	$reference = 'SELECT DISTINCT * FROM table WHERE id = ( SELECT DISTINCT column FROM table2 )';

	$q2 = $this->q->subSelect();
	$q2->selectDistinct( 'column' )->from( 'table2' );
	$this->q
	->selectDistinct( '*' )
	->from( 'table' )
	->where(
	$this->q->expr->eq( 'id', $q2->getQuery() )
	);

	$this->assertEquals( $reference, $this->q->getQuery() );

	}

	public function testSubSelectIn()
	{
	$reference = 'SELECT * FROM table WHERE id IN ( SELECT column FROM table2 )';

	$q2 = $this->q->subSelect();
	$q2->select( 'column' )->from( 'table2' );
	$this->q
	->select( '*' )
	->from( 'table' )
	->where(
	$this->q->expr->in( 'id', $q2 )
	);

	$this->assertEquals( $reference, $this->q->getQuery() );

	}

	public function testBindAuto()
	{
	$val1 = '';
	$val2 = '';

	$reference = '( SELECT column FROM table WHERE id = :ezcValue1 AND id2 = :ezcValue2 )';
	$q2 = $this->q->subSelect();
	$q2->select( 'column' )
	->from( 'table' )
	->where( $q2->expr->eq( 'id', $q2->bindParam( $val1 ) ) )
	->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2 ) ) );

	$this->assertEquals( $reference, $q2->getQuery() );
	}

	public function testBindManual()
	{
	$reference = '( SELECT column FROM table WHERE id = :test1 AND id2 = :test2 )';
	$val1 = '';
	$val2 = '';
	$q2 = $this->q->subSelect();
	$q2->select( 'column' )
	->from( 'table' )
	->where( $q2->expr->eq( 'id', $q2->bindParam( $val1, ':test1' ) ) )
	->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2, ':test2' ) ) );

	$this->assertEquals( $reference, $q2->getQuery() );
	}

	public function testBug11784()
	{
	$db = ezcDbInstance::get();
	$q = $db->createSelectQuery();
	$q->select( 'somecol' )->from( 'quiz' );

	$qQuestions = $q->subSelect();
	$qQuestions->select( 'id' )->from( 'question' )->where(
	$qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
	);

	$q->where(
	$q->expr->in( 'question', $qQuestions )
	);

	$this->assertEquals( "SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
	}

	public function testSubselectWithUpdate()
	{
	$db = ezcDbInstance::get();
	$q = $db->createUpdateQuery();
	$q->update( 'quiz' )->set( 'somecol', $q->bindValue( 'test' ) );

	$qQuestions = $q->subSelect();
	$qQuestions->select( 'id' )->from( 'question' )->where(
	$qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
	);

	$q->where(
	$q->expr->in( 'question', $qQuestions )
	);

	$this->assertEquals( "UPDATE quiz SET somecol = :ezcValue1 WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue2 )", $q->getQuery() );
	}


	public function testSubselectWithDelete()
	{
	$db = ezcDbInstance::get();
	$q = $db->createDeleteQuery();
	$q->deleteFrom( 'quiz' );

	$qQuestions = $q->subSelect();
	$qQuestions->select( 'id' )->from( 'question' )->where(
	$qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
	);

	$q->where(
	$q->expr->in( 'question', $qQuestions )
	);

	$this->assertEquals( "DELETE FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
	}

	// Verifies issue #11784 is fixed. Code taken from there.
	public function testSubselectNotQuotedInInExpr()
	{
	$db = ezcDbInstance::get();
	$q = $db->createSelectQuery();
	$q->select( 'somecol' )->from( 'quiz' );

	$qQuestions = $q->subSelect();
	$qQuestions->select( 'id' )->from( 'question' )->where(
	$qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
	);

	$q->where(
	$q->expr->in( 'question', $qQuestions )
	);

	$this->assertEquals(
	'SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )',
	$q->getQuery()
	);
	}

	public function testSubselectWithAlias()
	{
	$db = ezcDbInstance::get();

	$q = $db->createSelectQuery();
	$q->setAliases( array( 'ID' => 'id' ) );
	$q = $q->select( 'ID', 'name' )->from( 'main' );

	$q2 = $q->subSelect();
	$q2->select( 'main_id' )->from( 'sub' );
	$q2->limit(20,0);

	$q->innerJoin( $q->alias( $q2, 'sub_items' ), 'sub_items.main_id', 'main.id' );

	$this->assertEquals( "SELECT id, name FROM main INNER JOIN ( SELECT main_id FROM sub LIMIT 20 OFFSET 0 ) AS sub_items ON sub_items.main_id = main.id", $q->getQuery() );
	}

	public static function suite()
	{
	return new PHPUnit_Framework_TestSuite( 'ezcQuerySubSelectTest' );
	}
	}
	?>