Database/tests/sqlabstraction/query_select_join_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
  */

 /**
  * Testing the JOIN functionality in the SQL abstraction layer.
  * These tests are performed on a real database and tests that
  * the implementations return the correct result.
  *
  * @package Database
  * @subpackage Tests
  */
 class ezcQuerySelectJoinTestImpl extends ezcTestCase
 {
     private $q;
     private $e;
     private $db;

     protected function setUp()
     {
         try
         {
             $this->db = ezcDbInstance::get();
         }
         catch ( Exception $e )
         {
             $this->markTestSkipped();
         }

         $this->q = $this->db->createSelectQuery();
         $this->e = $this->q->expr;
         $this->assertNotNull( $this->db, 'Database instance is not initialized.' );

         try
         {
             $this->db->exec( 'DROP TABLE employees' );
         }
         catch ( Exception $e ) {} // eat
         try
         {
             $this->db->exec( 'DROP TABLE orders' );
         }
         catch ( Exception $e ) {} // eat
         try
         {
             $this->db->exec( 'DROP TABLE in_use' );
         }
         catch ( Exception $e ) {} // eat

         // insert some data
         $this->db->exec( 'CREATE TABLE employees ( id int, name VARCHAR(255) )' );
         $this->db->exec( "INSERT INTO employees VALUES ( 1, 'Raymond Bosman' )" );
         $this->db->exec( "INSERT INTO employees VALUES ( 2, 'Derick Rethans' )" );
         $this->db->exec( "INSERT INTO employees VALUES ( 3, 'Jan Borsodi' )" );
         $this->db->exec( "INSERT INTO employees VALUES ( 4, 'Frederik Holljen' )" );

         $this->db->exec( 'CREATE TABLE orders ( id int, product VARCHAR(255), employee_id int )' );
         $this->db->exec( "INSERT INTO orders VALUES ( 1001, 'Glass', 1 )" );
         $this->db->exec( "INSERT INTO orders VALUES ( 1002, 'Table', 3 )" );
         $this->db->exec( "INSERT INTO orders VALUES ( 1003, 'CPU', 3 )" );
         $this->db->exec( "INSERT INTO orders VALUES ( 1004, 'Cat', 5 )" );

         $this->db->exec( 'CREATE TABLE in_use ( id int, product_id int, employee_id int, amount int )' );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2001, 1001, 1, 5 )" );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2002, 1002, 3, 3 )" );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2003, 1003, 3, 2 )" );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2004, 1004, 4, 1 )" );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2005, 1005, 1, 1 )" );
         $this->db->exec( "INSERT INTO in_use VALUES ( 2006, 1005, 2, 1 )" );
     }

     protected function tearDown()
     {
         if ( $this->db === null ) return;

         $this->db->exec( 'DROP TABLE employees' );
         $this->db->exec( 'DROP TABLE orders' );
         $this->db->exec( 'DROP TABLE in_use' );
     }

     public function testNormal()
     {
         $this->q->select( 'employees.name', 'orders.product' )->from( 'employees', 'orders' )
                 ->where( $this->e->eq( 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 3, $rows );
     }

     public function testInnerJoinAsFromArgument()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( $this->q->innerJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 3, $rows );
     }

     public function testInnerJoinAfterFrom()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( 'employees' )->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 3, $rows );
     }

     public function testInnerJoinAfterFromSimplified()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( 'employees' )->innerJoin( 'orders', 'employees.id', 'orders.employee_id' );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 3, $rows );
     }

     public function testInnerMultiJoin()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                    ->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
                    ->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 6, $rows );
     }

     public function testInnerMultiJoinWithWhere()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                    ->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
                    ->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) )
                  ->where( $this->q->expr->not( $this->q->expr->eq( 'orders.product', "'CPU'" ) ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 4, $rows );
     }

     public function testInnerJoinNotAfterFrom()
     {
         try
         {
             $this->q->select( '*' )->innerJoin( 'table1', 'column1', 'column2' );
         }
         catch ( ezcQueryException $e )
         {
             return;
         }
         $this->fail( "Call to innerJoin() not after from() did not fail" );
     }

     public function testLeftJoinAsFromArgument()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( $this->q->leftJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 5, $rows );
     }

     public function testLeftJoinAfterFrom()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                     ->from( 'employees' )
                         ->leftJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 5, $rows );
     }

     public function testLeftJoinAfterFromSimplified()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                         ->from( 'employees' )
                             ->leftJoin( 'orders', 'employees.id', 'orders.employee_id' );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 5, $rows );
     }

     public function testLeftMultiJoin()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                     ->leftJoin(  'orders', 'employees.id', 'orders.employee_id' )
                     ->leftJoin(  'in_use', 'in_use.product_id', 'orders.id' );

         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }

         $this->assertEquals( 5, $rows );
     }

     public function testLeftMultiJoinWithWhere()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                     ->leftJoin(  'orders', 'employees.id', 'orders.employee_id' )
                     ->leftJoin(  'in_use', 'in_use.product_id', 'orders.id' )
                  ->where( $this->q->expr->not( $this->q->expr->isNull( 'orders.product' ) ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 3, $rows );
     }

     public function testLeftJoinNotAfterFrom()
     {
         try
         {
             $this->q->select( '*' )->leftJoin( 'table1', 'column1', 'column2' );
         }
         catch ( ezcQueryException $e )
         {
             return;
         }
         $this->fail( "Call to leftJoin() not after from() did not fail" );
     }


     public function testRightJoinAsFromArgument()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( $this->q->rightJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 4, $rows );
     }

     public function testRightJoinAfterFrom()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( 'employees' )->rightJoin( 'orders', $this->e->eq('employees.id', 'orders.employee_id') );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 4, $rows );
     }

     public function testRightJoinAfterFromSimplified()
     {
         $this->q->select( 'employees.name', 'orders.product' )
                  ->from( 'employees' )->rightJoin( 'orders', 'employees.id', 'orders.employee_id' );
         $stmt = $this->db->query( $this->q->getQuery() );
         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 4, $rows );
     }

     public function testRightMultiJoin()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                    ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
                    ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' );

         $stmt = $this->db->query( $this->q->getQuery() );

         if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
         {
            $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                             'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
                                         'LEFT JOIN employees ON employees.id = orders.employee_id';
         }
         else
         {
             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                              'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
                                             'RIGHT JOIN in_use ON in_use.product_id = orders.id';

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


         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }

         $this->assertEquals( 6, $rows );
     }

     public function testRightMultiJoinWithWhere()
     {
         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                    ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
                    ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
                  ->where( $this->q->expr->gt( 'in_use.amount', '2' ) );
         $stmt = $this->db->query( $this->q->getQuery() );

         if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
         {
            $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                             'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
                                         'LEFT JOIN employees ON employees.id = orders.employee_id '.
                                         'WHERE in_use.amount > 2';
         }
         else
         {
             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                              'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
                                             'RIGHT JOIN in_use ON in_use.product_id = orders.id '.
                                             'WHERE in_use.amount > 2';
         }
         $this->assertEquals( $reference, $this->q->getQuery() );


         $rows = 0;
         foreach ( $stmt as $row )
         {
             $rows++;
         }
         $this->assertEquals( 2, $rows );
     }

     public function testRightMultiJoinSeveralTimesWithWhere()
     {
         // not touch database just checking query syntax

         $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
                  ->from( 'employees' )
                    ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
                    ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
                  ->from( 'orders' )
                     ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
                     ->rightJoin( 'employees', 'employees.id', 'orders.employee_id' )
                  ->where( $this->q->expr->gt( 'in_use.amount', '2' ) );

         if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
         {
            $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                             'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
                                         'LEFT JOIN employees ON employees.id = orders.employee_id, '.
                                  'employees LEFT JOIN in_use ON employees.id = orders.employee_id '.
                                         'LEFT JOIN orders ON in_use.product_id = orders.id '.
                              'WHERE in_use.amount > 2';
         }
         else
         {
             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
                              'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
                                             'RIGHT JOIN in_use ON in_use.product_id = orders.id, '.
                                   'orders RIGHT JOIN in_use ON in_use.product_id = orders.id '.
                                             'RIGHT JOIN employees ON employees.id = orders.employee_id '.
                                             'WHERE in_use.amount > 2';
         }
         $this->assertEquals( $reference, $this->q->getQuery() );
     }

     public function testRightJoinNotAfterFrom()
     {
         try
         {
             $this->q->select( '*' )->rightJoin( 'table1', 'column1', 'column2' );
         }
         catch ( ezcQueryException $e )
         {
             return;
         }
         $this->fail( "Call to rightJoin() not after from() did not fail" );
     }

     public static function suite()
     {
         return new PHPUnit_Framework_TestSuite( 'ezcQuerySelectJoinTestImpl' );
     }
 }
 ?>
	<?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
	*/

	/**
	* Testing the JOIN functionality in the SQL abstraction layer.
	* These tests are performed on a real database and tests that
	* the implementations return the correct result.
	*
	* @package Database
	* @subpackage Tests
	*/
	class ezcQuerySelectJoinTestImpl extends ezcTestCase
	{
	private $q;
	private $e;
	private $db;

	protected function setUp()
	{
	try
	{
	$this->db = ezcDbInstance::get();
	}
	catch ( Exception $e )
	{
	$this->markTestSkipped();
	}

	$this->q = $this->db->createSelectQuery();
	$this->e = $this->q->expr;
	$this->assertNotNull( $this->db, 'Database instance is not initialized.' );

	try
	{
	$this->db->exec( 'DROP TABLE employees' );
	}
	catch ( Exception $e ) {} // eat
	try
	{
	$this->db->exec( 'DROP TABLE orders' );
	}
	catch ( Exception $e ) {} // eat
	try
	{
	$this->db->exec( 'DROP TABLE in_use' );
	}
	catch ( Exception $e ) {} // eat

	// insert some data
	$this->db->exec( 'CREATE TABLE employees ( id int, name VARCHAR(255) )' );
	$this->db->exec( "INSERT INTO employees VALUES ( 1, 'Raymond Bosman' )" );
	$this->db->exec( "INSERT INTO employees VALUES ( 2, 'Derick Rethans' )" );
	$this->db->exec( "INSERT INTO employees VALUES ( 3, 'Jan Borsodi' )" );
	$this->db->exec( "INSERT INTO employees VALUES ( 4, 'Frederik Holljen' )" );

	$this->db->exec( 'CREATE TABLE orders ( id int, product VARCHAR(255), employee_id int )' );
	$this->db->exec( "INSERT INTO orders VALUES ( 1001, 'Glass', 1 )" );
	$this->db->exec( "INSERT INTO orders VALUES ( 1002, 'Table', 3 )" );
	$this->db->exec( "INSERT INTO orders VALUES ( 1003, 'CPU', 3 )" );
	$this->db->exec( "INSERT INTO orders VALUES ( 1004, 'Cat', 5 )" );

	$this->db->exec( 'CREATE TABLE in_use ( id int, product_id int, employee_id int, amount int )' );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2001, 1001, 1, 5 )" );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2002, 1002, 3, 3 )" );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2003, 1003, 3, 2 )" );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2004, 1004, 4, 1 )" );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2005, 1005, 1, 1 )" );
	$this->db->exec( "INSERT INTO in_use VALUES ( 2006, 1005, 2, 1 )" );
	}

	protected function tearDown()
	{
	if ( $this->db === null ) return;

	$this->db->exec( 'DROP TABLE employees' );
	$this->db->exec( 'DROP TABLE orders' );
	$this->db->exec( 'DROP TABLE in_use' );
	}

	public function testNormal()
	{
	$this->q->select( 'employees.name', 'orders.product' )->from( 'employees', 'orders' )
	->where( $this->e->eq( 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 3, $rows );
	}

	public function testInnerJoinAsFromArgument()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( $this->q->innerJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 3, $rows );
	}

	public function testInnerJoinAfterFrom()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 3, $rows );
	}

	public function testInnerJoinAfterFromSimplified()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )->innerJoin( 'orders', 'employees.id', 'orders.employee_id' );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 3, $rows );
	}

	public function testInnerMultiJoin()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
	->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 6, $rows );
	}

	public function testInnerMultiJoinWithWhere()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
	->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) )
	->where( $this->q->expr->not( $this->q->expr->eq( 'orders.product', "'CPU'" ) ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 4, $rows );
	}

	public function testInnerJoinNotAfterFrom()
	{
	try
	{
	$this->q->select( '*' )->innerJoin( 'table1', 'column1', 'column2' );
	}
	catch ( ezcQueryException $e )
	{
	return;
	}
	$this->fail( "Call to innerJoin() not after from() did not fail" );
	}

	public function testLeftJoinAsFromArgument()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( $this->q->leftJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 5, $rows );
	}

	public function testLeftJoinAfterFrom()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )
	->leftJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 5, $rows );
	}

	public function testLeftJoinAfterFromSimplified()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )
	->leftJoin( 'orders', 'employees.id', 'orders.employee_id' );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 5, $rows );
	}

	public function testLeftMultiJoin()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->leftJoin( 'orders', 'employees.id', 'orders.employee_id' )
	->leftJoin( 'in_use', 'in_use.product_id', 'orders.id' );

	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}

	$this->assertEquals( 5, $rows );
	}

	public function testLeftMultiJoinWithWhere()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->leftJoin( 'orders', 'employees.id', 'orders.employee_id' )
	->leftJoin( 'in_use', 'in_use.product_id', 'orders.id' )
	->where( $this->q->expr->not( $this->q->expr->isNull( 'orders.product' ) ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 3, $rows );
	}

	public function testLeftJoinNotAfterFrom()
	{
	try
	{
	$this->q->select( '*' )->leftJoin( 'table1', 'column1', 'column2' );
	}
	catch ( ezcQueryException $e )
	{
	return;
	}
	$this->fail( "Call to leftJoin() not after from() did not fail" );
	}


	public function testRightJoinAsFromArgument()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( $this->q->rightJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 4, $rows );
	}

	public function testRightJoinAfterFrom()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )->rightJoin( 'orders', $this->e->eq('employees.id', 'orders.employee_id') );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 4, $rows );
	}

	public function testRightJoinAfterFromSimplified()
	{
	$this->q->select( 'employees.name', 'orders.product' )
	->from( 'employees' )->rightJoin( 'orders', 'employees.id', 'orders.employee_id' );
	$stmt = $this->db->query( $this->q->getQuery() );
	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 4, $rows );
	}

	public function testRightMultiJoin()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
	->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' );

	$stmt = $this->db->query( $this->q->getQuery() );

	if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
	'LEFT JOIN employees ON employees.id = orders.employee_id';
	}
	else
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
	'RIGHT JOIN in_use ON in_use.product_id = orders.id';

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


	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}

	$this->assertEquals( 6, $rows );
	}

	public function testRightMultiJoinWithWhere()
	{
	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
	->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
	->where( $this->q->expr->gt( 'in_use.amount', '2' ) );
	$stmt = $this->db->query( $this->q->getQuery() );

	if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
	'LEFT JOIN employees ON employees.id = orders.employee_id '.
	'WHERE in_use.amount > 2';
	}
	else
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
	'RIGHT JOIN in_use ON in_use.product_id = orders.id '.
	'WHERE in_use.amount > 2';
	}
	$this->assertEquals( $reference, $this->q->getQuery() );


	$rows = 0;
	foreach ( $stmt as $row )
	{
	$rows++;
	}
	$this->assertEquals( 2, $rows );
	}

	public function testRightMultiJoinSeveralTimesWithWhere()
	{
	// not touch database just checking query syntax

	$this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
	->from( 'employees' )
	->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
	->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
	->from( 'orders' )
	->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
	->rightJoin( 'employees', 'employees.id', 'orders.employee_id' )
	->where( $this->q->expr->gt( 'in_use.amount', '2' ) );

	if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
	'LEFT JOIN employees ON employees.id = orders.employee_id, '.
	'employees LEFT JOIN in_use ON employees.id = orders.employee_id '.
	'LEFT JOIN orders ON in_use.product_id = orders.id '.
	'WHERE in_use.amount > 2';
	}
	else
	{
	$reference = 'SELECT employees.name, orders.product, in_use.amount '.
	'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
	'RIGHT JOIN in_use ON in_use.product_id = orders.id, '.
	'orders RIGHT JOIN in_use ON in_use.product_id = orders.id '.
	'RIGHT JOIN employees ON employees.id = orders.employee_id '.
	'WHERE in_use.amount > 2';
	}
	$this->assertEquals( $reference, $this->q->getQuery() );
	}

	public function testRightJoinNotAfterFrom()
	{
	try
	{
	$this->q->select( '*' )->rightJoin( 'table1', 'column1', 'column2' );
	}
	catch ( ezcQueryException $e )
	{
	return;
	}
	$this->fail( "Call to rightJoin() not after from() did not fail" );
	}

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