eZ components PHP Cross Reference Developer Tools

Source: /Database/tests/sqlabstraction/query_select_join_test.php - 430 lines - 15748 bytes - Summary - Text - Print

   1  <?php
   2  /**
   3   * @copyright Copyright (C) 2005-2010 eZ Systems AS. All rights reserved.
   4   * @license http://ez.no/licenses/new_bsd New BSD License
   5   * @version 1.4.7
   6   * @filesource
   7   * @package Database
   8   * @subpackage Tests
   9   */
  10  
  11  /**
  12   * Testing the JOIN functionality in the SQL abstraction layer.
  13   * These tests are performed on a real database and tests that
  14   * the implementations return the correct result.
  15   *
  16   * @package Database
  17   * @subpackage Tests
  18   */
  19  class ezcQuerySelectJoinTestImpl extends ezcTestCase
  20  {
  21      private $q;
  22      private $e;
  23      private $db;
  24  
  25      protected function setUp()
  26      {
  27          try
  28          {
  29              $this->db = ezcDbInstance::get();
  30          }
  31          catch ( Exception $e )
  32          {
  33              $this->markTestSkipped();
  34          }
  35  
  36          $this->q = $this->db->createSelectQuery();
  37          $this->e = $this->q->expr;
  38          $this->assertNotNull( $this->db, 'Database instance is not initialized.' );
  39  
  40          try
  41          {
  42              $this->db->exec( 'DROP TABLE employees' );
  43          }
  44          catch ( Exception $e ) {} // eat
  45          try
  46          {
  47              $this->db->exec( 'DROP TABLE orders' );
  48          }
  49          catch ( Exception $e ) {} // eat
  50          try
  51          {
  52              $this->db->exec( 'DROP TABLE in_use' );
  53          }
  54          catch ( Exception $e ) {} // eat
  55  
  56          // insert some data
  57          $this->db->exec( 'CREATE TABLE employees ( id int, name VARCHAR(255) )' );
  58          $this->db->exec( "INSERT INTO employees VALUES ( 1, 'Raymond Bosman' )" );
  59          $this->db->exec( "INSERT INTO employees VALUES ( 2, 'Derick Rethans' )" );
  60          $this->db->exec( "INSERT INTO employees VALUES ( 3, 'Jan Borsodi' )" );
  61          $this->db->exec( "INSERT INTO employees VALUES ( 4, 'Frederik Holljen' )" );
  62  
  63          $this->db->exec( 'CREATE TABLE orders ( id int, product VARCHAR(255), employee_id int )' );
  64          $this->db->exec( "INSERT INTO orders VALUES ( 1001, 'Glass', 1 )" );
  65          $this->db->exec( "INSERT INTO orders VALUES ( 1002, 'Table', 3 )" );
  66          $this->db->exec( "INSERT INTO orders VALUES ( 1003, 'CPU', 3 )" );
  67          $this->db->exec( "INSERT INTO orders VALUES ( 1004, 'Cat', 5 )" );
  68          
  69          $this->db->exec( 'CREATE TABLE in_use ( id int, product_id int, employee_id int, amount int )' );
  70          $this->db->exec( "INSERT INTO in_use VALUES ( 2001, 1001, 1, 5 )" );
  71          $this->db->exec( "INSERT INTO in_use VALUES ( 2002, 1002, 3, 3 )" );
  72          $this->db->exec( "INSERT INTO in_use VALUES ( 2003, 1003, 3, 2 )" );
  73          $this->db->exec( "INSERT INTO in_use VALUES ( 2004, 1004, 4, 1 )" );
  74          $this->db->exec( "INSERT INTO in_use VALUES ( 2005, 1005, 1, 1 )" );
  75          $this->db->exec( "INSERT INTO in_use VALUES ( 2006, 1005, 2, 1 )" );
  76      }
  77  
  78      protected function tearDown()
  79      {
  80          if ( $this->db === null ) return;
  81  
  82          $this->db->exec( 'DROP TABLE employees' );
  83          $this->db->exec( 'DROP TABLE orders' );
  84          $this->db->exec( 'DROP TABLE in_use' );
  85      }
  86  
  87      public function testNormal()
  88      {
  89          $this->q->select( 'employees.name', 'orders.product' )->from( 'employees', 'orders' )
  90                  ->where( $this->e->eq( 'employees.id', 'orders.employee_id' ) );
  91          $stmt = $this->db->query( $this->q->getQuery() );
  92          $rows = 0;
  93          foreach ( $stmt as $row )
  94          {
  95              $rows++;
  96          }
  97          $this->assertEquals( 3, $rows );
  98      }
  99  
 100      public function testInnerJoinAsFromArgument()
 101      {
 102          $this->q->select( 'employees.name', 'orders.product' )
 103                   ->from( $this->q->innerJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
 104          $stmt = $this->db->query( $this->q->getQuery() );
 105          $rows = 0;
 106          foreach ( $stmt as $row )
 107          {
 108              $rows++;
 109          }
 110          $this->assertEquals( 3, $rows );
 111      }
 112  
 113      public function testInnerJoinAfterFrom()
 114      {
 115          $this->q->select( 'employees.name', 'orders.product' )
 116                   ->from( 'employees' )->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
 117          $stmt = $this->db->query( $this->q->getQuery() );
 118          $rows = 0;
 119          foreach ( $stmt as $row )
 120          {
 121              $rows++;
 122          }
 123          $this->assertEquals( 3, $rows );
 124      }
 125  
 126      public function testInnerJoinAfterFromSimplified()
 127      {
 128          $this->q->select( 'employees.name', 'orders.product' )
 129                   ->from( 'employees' )->innerJoin( 'orders', 'employees.id', 'orders.employee_id' );
 130          $stmt = $this->db->query( $this->q->getQuery() );
 131          $rows = 0;
 132          foreach ( $stmt as $row )
 133          {
 134              $rows++;
 135          }
 136          $this->assertEquals( 3, $rows );
 137      }
 138  
 139      public function testInnerMultiJoin()
 140      {
 141          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 142                   ->from( 'employees' )
 143                     ->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
 144                     ->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) );
 145          $stmt = $this->db->query( $this->q->getQuery() );
 146          $rows = 0;
 147          foreach ( $stmt as $row )
 148          {
 149              $rows++;
 150          }
 151          $this->assertEquals( 6, $rows );
 152      }
 153  
 154      public function testInnerMultiJoinWithWhere()
 155      {
 156          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 157                   ->from( 'employees' )
 158                     ->innerJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) )
 159                     ->innerJoin( 'in_use', $this->e->eq( 'in_use.employee_id', 'employees.id' ) )
 160                   ->where( $this->q->expr->not( $this->q->expr->eq( 'orders.product', "'CPU'" ) ) );
 161          $stmt = $this->db->query( $this->q->getQuery() );
 162          $rows = 0;
 163          foreach ( $stmt as $row )
 164          {
 165              $rows++;
 166          }
 167          $this->assertEquals( 4, $rows );
 168      }
 169  
 170      public function testInnerJoinNotAfterFrom()
 171      {
 172          try
 173          {
 174              $this->q->select( '*' )->innerJoin( 'table1', 'column1', 'column2' );
 175          }
 176          catch ( ezcQueryException $e )
 177          {
 178              return;
 179          }
 180          $this->fail( "Call to innerJoin() not after from() did not fail" );
 181      }
 182  
 183      public function testLeftJoinAsFromArgument()
 184      {
 185          $this->q->select( 'employees.name', 'orders.product' )
 186                   ->from( $this->q->leftJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
 187          $stmt = $this->db->query( $this->q->getQuery() );
 188          $rows = 0;
 189          foreach ( $stmt as $row )
 190          {
 191              $rows++;
 192          }
 193          $this->assertEquals( 5, $rows );
 194      }
 195  
 196      public function testLeftJoinAfterFrom()
 197      {
 198          $this->q->select( 'employees.name', 'orders.product' )
 199                      ->from( 'employees' )
 200                          ->leftJoin( 'orders', $this->e->eq( 'employees.id', 'orders.employee_id' ) );
 201          $stmt = $this->db->query( $this->q->getQuery() );
 202          $rows = 0;
 203          foreach ( $stmt as $row )
 204          {
 205              $rows++;
 206          }
 207          $this->assertEquals( 5, $rows );
 208      }
 209  
 210      public function testLeftJoinAfterFromSimplified()
 211      {
 212          $this->q->select( 'employees.name', 'orders.product' )
 213                          ->from( 'employees' )
 214                              ->leftJoin( 'orders', 'employees.id', 'orders.employee_id' );
 215          $stmt = $this->db->query( $this->q->getQuery() );
 216          $rows = 0;
 217          foreach ( $stmt as $row )
 218          {
 219              $rows++;
 220          }
 221          $this->assertEquals( 5, $rows );
 222      }
 223  
 224      public function testLeftMultiJoin()
 225      {
 226          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 227                   ->from( 'employees' )
 228                      ->leftJoin(  'orders', 'employees.id', 'orders.employee_id' )
 229                      ->leftJoin(  'in_use', 'in_use.product_id', 'orders.id' );
 230  
 231          $stmt = $this->db->query( $this->q->getQuery() );
 232          $rows = 0;
 233          foreach ( $stmt as $row )
 234          {
 235              $rows++;
 236          }
 237  
 238          $this->assertEquals( 5, $rows );
 239      }
 240  
 241      public function testLeftMultiJoinWithWhere()
 242      {
 243          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 244                   ->from( 'employees' )
 245                      ->leftJoin(  'orders', 'employees.id', 'orders.employee_id' )
 246                      ->leftJoin(  'in_use', 'in_use.product_id', 'orders.id' )
 247                   ->where( $this->q->expr->not( $this->q->expr->isNull( 'orders.product' ) ) );
 248          $stmt = $this->db->query( $this->q->getQuery() );
 249          $rows = 0;
 250          foreach ( $stmt as $row )
 251          {
 252              $rows++;
 253          }
 254          $this->assertEquals( 3, $rows );
 255      }
 256  
 257      public function testLeftJoinNotAfterFrom()
 258      {
 259          try
 260          {
 261              $this->q->select( '*' )->leftJoin( 'table1', 'column1', 'column2' );
 262          }
 263          catch ( ezcQueryException $e )
 264          {
 265              return;
 266          }
 267          $this->fail( "Call to leftJoin() not after from() did not fail" );
 268      }
 269  
 270  
 271      public function testRightJoinAsFromArgument()
 272      {
 273          $this->q->select( 'employees.name', 'orders.product' )
 274                   ->from( $this->q->rightJoin( 'employees', 'orders', 'employees.id', 'orders.employee_id' ) );
 275          $stmt = $this->db->query( $this->q->getQuery() );
 276          $rows = 0;
 277          foreach ( $stmt as $row )
 278          {
 279              $rows++;
 280          }
 281          $this->assertEquals( 4, $rows );
 282      }
 283  
 284      public function testRightJoinAfterFrom()
 285      {
 286          $this->q->select( 'employees.name', 'orders.product' )
 287                   ->from( 'employees' )->rightJoin( 'orders', $this->e->eq('employees.id', 'orders.employee_id') );
 288          $stmt = $this->db->query( $this->q->getQuery() );
 289          $rows = 0;
 290          foreach ( $stmt as $row )
 291          {
 292              $rows++;
 293          }
 294          $this->assertEquals( 4, $rows );
 295      }
 296  
 297      public function testRightJoinAfterFromSimplified()
 298      {
 299          $this->q->select( 'employees.name', 'orders.product' )
 300                   ->from( 'employees' )->rightJoin( 'orders', 'employees.id', 'orders.employee_id' );
 301          $stmt = $this->db->query( $this->q->getQuery() );
 302          $rows = 0;
 303          foreach ( $stmt as $row )
 304          {
 305              $rows++;
 306          }
 307          $this->assertEquals( 4, $rows );
 308      }
 309  
 310      public function testRightMultiJoin()
 311      {
 312          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 313                   ->from( 'employees' )
 314                     ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
 315                     ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' );
 316  
 317          $stmt = $this->db->query( $this->q->getQuery() );
 318  
 319          if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
 320          {
 321             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 322                              'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
 323                                          'LEFT JOIN employees ON employees.id = orders.employee_id';
 324          }
 325          else
 326          {
 327              $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 328                               'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
 329                                              'RIGHT JOIN in_use ON in_use.product_id = orders.id';
 330                  
 331          }
 332          $this->assertEquals( $reference, $this->q->getQuery() );
 333  
 334  
 335          $rows = 0;
 336          foreach ( $stmt as $row )
 337          {
 338              $rows++;
 339          }
 340  
 341          $this->assertEquals( 6, $rows );
 342      }
 343  
 344      public function testRightMultiJoinWithWhere()
 345      {
 346          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 347                   ->from( 'employees' )
 348                     ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
 349                     ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
 350                   ->where( $this->q->expr->gt( 'in_use.amount', '2' ) );
 351          $stmt = $this->db->query( $this->q->getQuery() );
 352  
 353          if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
 354          {
 355             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 356                              'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
 357                                          'LEFT JOIN employees ON employees.id = orders.employee_id '.
 358                                          'WHERE in_use.amount > 2';
 359          }
 360          else
 361          {
 362              $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 363                               'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
 364                                              'RIGHT JOIN in_use ON in_use.product_id = orders.id '.
 365                                              'WHERE in_use.amount > 2';
 366          }
 367          $this->assertEquals( $reference, $this->q->getQuery() );
 368  
 369  
 370          $rows = 0;
 371          foreach ( $stmt as $row )
 372          {
 373              $rows++;
 374          }
 375          $this->assertEquals( 2, $rows );
 376      }
 377  
 378      public function testRightMultiJoinSeveralTimesWithWhere()
 379      {
 380          // not touch database just checking query syntax
 381  
 382          $this->q->select( 'employees.name', 'orders.product', 'in_use.amount' )
 383                   ->from( 'employees' )
 384                     ->rightJoin( 'orders', 'employees.id', 'orders.employee_id' )
 385                     ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
 386                   ->from( 'orders' )
 387                      ->rightJoin( 'in_use', 'in_use.product_id', 'orders.id' )
 388                      ->rightJoin( 'employees', 'employees.id', 'orders.employee_id' )
 389                   ->where( $this->q->expr->gt( 'in_use.amount', '2' ) );
 390  
 391          if ( $this->db->getName() == 'sqlite' ) // right joins for SQLite are emulated by left joins for the tables in reverse order
 392          {
 393             $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 394                              'FROM in_use LEFT JOIN orders ON in_use.product_id = orders.id '.
 395                                          'LEFT JOIN employees ON employees.id = orders.employee_id, '.
 396                                   'employees LEFT JOIN in_use ON employees.id = orders.employee_id '. 
 397                                          'LEFT JOIN orders ON in_use.product_id = orders.id '.
 398                               'WHERE in_use.amount > 2';
 399          }
 400          else
 401          {
 402              $reference = 'SELECT employees.name, orders.product, in_use.amount '.
 403                               'FROM employees RIGHT JOIN orders ON employees.id = orders.employee_id '.
 404                                              'RIGHT JOIN in_use ON in_use.product_id = orders.id, '.
 405                                    'orders RIGHT JOIN in_use ON in_use.product_id = orders.id '.
 406                                              'RIGHT JOIN employees ON employees.id = orders.employee_id '.
 407                                              'WHERE in_use.amount > 2';
 408          }
 409          $this->assertEquals( $reference, $this->q->getQuery() );
 410      }
 411  
 412      public function testRightJoinNotAfterFrom()
 413      {
 414          try
 415          {
 416              $this->q->select( '*' )->rightJoin( 'table1', 'column1', 'column2' );
 417          }
 418          catch ( ezcQueryException $e )
 419          {
 420              return;
 421          }
 422          $this->fail( "Call to rightJoin() not after from() did not fail" );
 423      }
 424  
 425      public static function suite()
 426      {
 427          return new PHPUnit_Framework_TestSuite( 'ezcQuerySelectJoinTestImpl' );
 428      }
 429  }
 430  ?>

title

Description

title

Description

title

Description

title

title

Body