eZ components PHP Cross Reference Developer Tools

Source: /Database/tests/sqlabstraction/query_select_test_impl.php - 459 lines - 14615 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 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   * @todo, test with null input values
  19   */
  20  class ezcQuerySelectTestImpl extends ezcTestCase
  21  {
  22      private $q;
  23      private $e;
  24      private $db;
  25  
  26      protected function setUp()
  27      {
  28          try
  29          {
  30              $this->db = ezcDbInstance::get();
  31          }
  32          catch ( Exception $e )
  33          {
  34              $this->markTestSkipped();
  35          }
  36  
  37          $this->q = $this->db->createSelectQuery();
  38          $this->e = $this->q->expr;
  39          $this->assertNotNull( $this->db, 'Database instance is not initialized.' );
  40  
  41          try 
  42          {
  43              $this->db->exec( 'DROP TABLE query_test' );
  44              $this->db->exec( 'DROP TABLE query_test2' );
  45          }
  46          catch ( Exception $e ) {} // eat
  47  
  48          // insert some data
  49          $this->db->exec( 'CREATE TABLE query_test ( id int, company VARCHAR(255), section VARCHAR(255), employees int NULL )' );
  50          $this->db->exec( "INSERT INTO query_test VALUES ( 1, 'eZ systems', 'Norway', 20 )" );
  51          $this->db->exec( "INSERT INTO query_test VALUES ( 2, 'IBM', 'Norway', 500 )" );
  52          $this->db->exec( "INSERT INTO query_test VALUES ( 3, 'eZ systems', 'Ukraine', 10 )" );
  53          $this->db->exec( "INSERT INTO query_test VALUES ( 4, 'IBM', 'Germany', null )" );
  54          
  55          // insert some data
  56          $this->db->exec( 'CREATE TABLE query_test2 ( id int, company VARCHAR(255), section VARCHAR(255), employees int NULL )' );
  57          $this->db->exec( "INSERT INTO query_test2 VALUES ( 1, 'eZ systems', 'Norway', 20 )" );
  58          $this->db->exec( "INSERT INTO query_test2 VALUES ( 2, 'IBM', 'Norway', 500 )" );
  59          $this->db->exec( "INSERT INTO query_test2 VALUES ( 3, 'eZ systems', 'Ukraine', 10 )" );
  60          $this->db->exec( "INSERT INTO query_test2 VALUES ( 4, 'IBM', 'Germany', null )" );
  61      }
  62  
  63      protected function tearDown()
  64      {
  65          if ( $this->db === null ) return;
  66  
  67          $this->db->exec( 'DROP TABLE query_test' );
  68          $this->db->exec( 'DROP TABLE query_test2' );
  69      }
  70  
  71      public function testBindString()
  72      {
  73          $section = 'Norway';
  74          $this->q->select( 'COUNT(*)' )->from( 'query_test' )
  75                  ->where(
  76                  $this->e->eq( 'section', $this->q->bindParam( $section ) ) );
  77          $stmt = $this->q->prepare();
  78          $stmt->execute();
  79          $this->assertEquals( 2, (int)$stmt->fetchColumn( 0 ) );
  80  
  81          // set another value for section and try again.
  82          $section = 'Ukraine';
  83          $stmt->execute();
  84          $this->assertEquals( 1, (int)$stmt->fetchColumn( 0 ) );
  85      }
  86  
  87      public function testBindInteger()
  88      {
  89          $num = 0;
  90          $this->q->select( 'COUNT(*)' )->from( 'query_test' )
  91                  ->where(
  92                  $this->e->gt( 'employees', $this->q->bindParam( $num ) ) );
  93          $stmt = $this->q->prepare();
  94          $stmt->execute();
  95          $this->assertEquals( 3, (int)$stmt->fetchColumn( 0 ) );
  96  
  97          // set another value for section and try again.
  98          $num = 20;
  99          $stmt->execute();
 100          $this->assertEquals( 1, (int)$stmt->fetchColumn( 0 ) );
 101      }
 102  
 103      public function testBuildFrom()
 104      {
 105          $this->q->select( 'COUNT(*)' )->from( 'query_test' );
 106          $stmt = $this->db->query( $this->q->getQuery() );
 107          $this->assertEquals( 4, (int)$stmt->fetchColumn( 0 ) );
 108      }
 109  
 110      public function testBuildFromWhere()
 111      {
 112          $this->q->select( 'COUNT(*)' )->from( 'query_test' )
 113                  ->where( $this->e->eq( 'employees', 20 ) );
 114          $stmt = $this->db->query( $this->q->getQuery() );
 115          $this->assertEquals( 1, (int)$stmt->fetchColumn( 0 ) );
 116      }
 117  
 118      public function testBuildFromWhereGroup()
 119      {
 120          $this->q->select( 'COUNT(*)' )->from( 'query_test' )
 121                  ->where( $this->e->eq( 1, 1 ) )
 122                  ->groupBy( 'Company' );
 123          $stmt = $this->db->query( $this->q->getQuery() );
 124          $this->assertEquals( 2, (int)$stmt->fetchColumn( 0 ) );
 125      }
 126  
 127      public function testBuildFromWhereGroupOrder()
 128      {
 129          $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 130                  ->where( $this->e->eq( 1, 1 ) )
 131                  ->groupBy( 'company' )
 132                  ->orderBy( 'company', ezcQuerySelect::DESC );
 133          $stmt = $this->db->query( $this->q->getQuery() );
 134          $rows = 0;
 135          foreach ( $stmt as $row )
 136          {
 137              $rows++;
 138          }
 139          $this->assertEquals( 2, $rows );
 140      }
 141  
 142      public function testBuildFromWhereGroupOrderLimit()
 143      {
 144          $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 145                  ->where( $this->e->eq( 1, 1 ) )
 146                  ->groupBy( 'company' )
 147                  ->orderBy( 'company', ezcQuerySelect::DESC )
 148                  ->limit( 1 );
 149          $stmt = $this->db->query( $this->q->getQuery() );
 150          $rows = 0;
 151          foreach ( $stmt as $row )
 152          {
 153              $rows++;
 154          }
 155          $this->assertEquals( 1, $rows );
 156      }
 157  
 158      // bug #9466
 159      public function testBuildFromWhereGroupOrderLimit2()
 160      {
 161          $stmt = $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 162                  ->where( $this->e->eq( 1, 1 ) )
 163                  ->groupBy( 'company' )
 164                  ->orderBy( 'company', ezcQuerySelect::DESC )
 165                  ->limit( 1 )
 166                  ->prepare();
 167          $stmt->execute();
 168          $rows = 0;
 169          foreach ( $stmt as $row )
 170          {
 171              $rows++;
 172          }
 173          $this->assertEquals( 1, $rows );
 174      }
 175  
 176      public function testBuildFromWhereOrderLimit()
 177      {
 178          $this->q->select( '*' )->from( 'query_test' )
 179                  ->where( $this->e->eq( 1, 1 ) )
 180                  ->orderBy( 'id', ezcQuerySelect::DESC )
 181                  ->limit( 1 );
 182          $stmt = $this->db->query( $this->q->getQuery() );
 183          $rows = 0;
 184          foreach ( $stmt as $row )
 185          {
 186              $rows++;
 187          }
 188          $this->assertEquals( 1, $rows );
 189      }
 190  
 191      public function testBuildFromWhereGroupLimit()
 192      {
 193          $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 194                  ->where( $this->e->eq( 1, 1 ) )
 195                  ->groupBy( 'company' )
 196                  ->limit( 1 );
 197          $stmt = $this->db->query( $this->q->getQuery() );
 198          $rows = 0;
 199          foreach ( $stmt as $row )
 200          {
 201              $rows++;
 202          }
 203          $this->assertEquals( 1, $rows );
 204      }
 205  
 206      public function testBuildFromWhereGroupHaving()
 207      {
 208         $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 209              ->where( $this->e->eq( 1, 1 ) )
 210              ->groupBy( 'company' )
 211              ->having( $this->e->gt( 'SUM(employees)', 100 ) );
 212  
 213          $stmt = $this->db->query( $this->q->getQuery() );
 214          $rows = 0;
 215          foreach ( $stmt as $row )
 216          {
 217              $rows++;
 218          }
 219  
 220          $this->assertEquals( 1, $rows );
 221      }
 222  
 223      public function testBuildFromWhereGroupHavingMulti()
 224      {
 225         $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 226              ->where( $this->e->eq( 1, 1 ) )
 227              ->groupBy( 'company' )
 228              ->having( $this->e->gte( 'SUM(employees)', 10 ) )
 229              ->having( $this->e->lte( 'SUM(employees)', 500 ) );
 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( 2, $rows );
 239      }
 240  
 241      public function testBuildFromWhereGroupHavingBind()
 242      {
 243         $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 244              ->where( $this->e->eq( 1, 1 ) )
 245              ->groupBy( 'company' )
 246              ->having( $this->e->eq( 'company', $this->q->bindValue( 'eZ systems' ) ) );
 247  
 248          $stmt = $this->q->prepare();
 249          $stmt->execute();
 250          $rows = 0;
 251          foreach ( $stmt as $row )
 252          {
 253              $rows++;
 254          }
 255  
 256          $this->assertEquals( 1, $rows );
 257      }
 258  
 259      public function testBuildFromWhereOrderLimitOffset()
 260      {
 261          $this->q->select( '*' )->from( 'query_test' )
 262                  ->where( $this->e->eq( 1, 1 ) )
 263                  ->orderBy( 'id', ezcQuerySelect::DESC )
 264                  ->limit( 1 );
 265          $stmt = $this->db->query( $this->q->getQuery() );
 266          $result = $stmt->fetchAll();
 267          $this->assertEquals( 1, count( $result ) );
 268          $this->assertEquals( 'IBM', $result[0]['company'] );
 269          $this->assertEquals( 'Germany', $result[0]['section'] );
 270      }
 271  
 272  
 273      public function testBuildFromLimit()
 274      {
 275          $this->q->select( '*' )->from( 'query_test' )
 276                  ->where( $this->e->eq( 1, 1 ) )
 277                  ->limit( 1 );
 278          $stmt = $this->db->query( $this->q->getQuery() );
 279          $rows = 0;
 280          foreach ( $stmt as $row )
 281          {
 282              $rows++;
 283          }
 284          $this->assertEquals( 1, $rows );
 285      }
 286  
 287      public function testBuildFromDistinct()
 288      {
 289          $this->q->selectDistinct( 'section' )
 290                  ->from( 'query_test' );
 291  
 292          $stmt = $this->db->query( $this->q->getQuery() );
 293          $rows = 0;
 294          foreach ( $stmt as $row )
 295          {
 296              $rows++;
 297          }
 298          $this->assertEquals( 3, $rows );
 299      }
 300  
 301      // LOGIC TESTS
 302      public function testSelectNone()
 303      {
 304          try
 305          {
 306              $this->q->select( );
 307              $this->fail( "Expected exception" );
 308          }
 309          catch ( ezcQueryVariableParameterException $e ) {}
 310      }
 311  
 312      public function testSelectMultiInOne()
 313      {
 314          $this->q->select( 'id', 'company' )->from( 'query_test' );
 315          $stmt = $this->db->query( $this->q->getQuery() );
 316          $this->assertEquals( 2, $stmt->columnCount() );
 317      }
 318  
 319      public function testSelectMultiInMulti()
 320      {
 321          $this->q->select( 'id' )->from( 'query_test' )->select( 'company' );
 322          $stmt = $this->db->query( $this->q->getQuery() );
 323          $this->assertEquals( 2, $stmt->columnCount() );
 324      }
 325  
 326      public function testSelectMultiWithAliasInOne()
 327      {
 328          $this->q->setAliases( array( 'identifier' => 'id', 'text' => 'company' ) );
 329          $this->q->select( 'identifier', 'text' )->from( 'query_test' );
 330          $stmt = $this->db->query( $this->q->getQuery() );
 331          $this->assertEquals( 2, $stmt->columnCount() );
 332      }
 333  
 334      public function testSelectMultiWithAliasInMulti()
 335      {
 336          $this->q->setAliases( array( 'identifier' => 'id', 'text' => 'company' ) );
 337          $this->q->select( 'identifier')->select( 'text' )->from( 'query_test' );
 338          $stmt = $this->db->query( $this->q->getQuery() );
 339          $this->assertEquals( 2, $stmt->columnCount() );
 340      }
 341  
 342  
 343      public function testAliAs()
 344      {
 345          $this->q->select( $this->q->aliAs( 'id', 'other' ) )->from( 'query_test' );
 346          $stmt = $this->db->query( $this->q->getQuery() );
 347          $result = $stmt->fetchAll();
 348          if ( !isset( $result[0]['other'] ) ) 
 349          {
 350              $this->fail( 'Fail test testAliAs' );
 351          }
 352      }
 353  
 354      public function testAliAsWithAlias()
 355      {
 356          $this->q->setAliases( array( 'identifier' => 'id', 'text' => 'company' ) );
 357          $this->q->select( $this->q->aliAs( 'identifier', 'other' ) )->from( 'query_test' );
 358          $stmt = $this->db->query( $this->q->getQuery() );
 359          $result = $stmt->fetchAll();
 360          if ( !isset( $result[0]['other'] ) ) 
 361          {
 362              $this->fail( 'Test fail testAliAsWithAlias' );
 363          }
 364      }
 365  
 366      public function testMultipleFromInOne()
 367      {
 368          $this->q->select( 'query_test.id', 'query_test2.company' )->from( 'query_test', 'query_test2');
 369          $stmt = $this->db->query( $this->q->getQuery() );
 370          $this->assertEquals( 2, $stmt->columnCount() );
 371      }
 372  
 373      public function testMultipleFromInMulti()
 374      {
 375          $this->q->select( 'query_test.id', 'query_test2.company' )->from( 'query_test')->from( 'query_test2' );
 376          $stmt = $this->db->query( $this->q->getQuery() );
 377          $this->assertEquals( 2, $stmt->columnCount() );
 378      }
 379  
 380      public function testEmptyFrom()
 381      {
 382          try
 383          {
 384              $this->q->select( 'd' )->from();
 385              $this->fail( "Expected exception" );
 386          }
 387          catch ( ezcQueryVariableParameterException $e ) {}
 388      }
 389  
 390      public function testWhereMultiInOne()
 391      {
 392          $this->q->select( '*' )->from( 'query_test' )
 393              ->where( $this->e->eq( 1, 1 ), $this->e->eq( 1, 0 ) );
 394          $stmt = $this->db->query( $this->q->getQuery() );
 395          $rows = 0;
 396          foreach ( $stmt as $row )
 397          {
 398              $rows++;
 399          }
 400          $this->assertEquals( 0, $rows );
 401      }
 402  
 403      public function testWhereMultiInMulti()
 404      {
 405          $this->q->select( '*' )->from( 'query_test' )
 406              ->where( $this->e->eq( 1, 1 ))->where( $this->e->eq( 1, 0 ) );
 407          $stmt = $this->db->query( $this->q->getQuery() );
 408          $rows = 0;
 409          foreach ( $stmt as $row )
 410          {
 411              $rows++;
 412          }
 413          $this->assertEquals( 0, $rows );
 414      }
 415  
 416      public function testEmptyWhere()
 417      {
 418          try
 419          {
 420              $this->q->select( 'd' )->from('d')->where();
 421              $this->fail( "Expected exception" );
 422          }
 423          catch ( ezcQueryVariableParameterException $e ) {}
 424      }
 425  
 426      public function testEmptyGroupBy()
 427      {
 428          try
 429          {
 430              $this->q->select( 'd' )->from('d')->groupBy();
 431              $this->fail( "Expected exception" );
 432          }
 433          catch ( ezcQueryVariableParameterException $e ) {}
 434      }
 435  
 436      public function testReset()
 437      {
 438          $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 439                  ->where( $this->e->eq( 1, 1 ) )
 440                  ->groupBy( 'company' )
 441                  ->orderBy( 'company', ezcQuerySelect::DESC )
 442                  ->limit( 1 );
 443          $queryString = $this->q->getQuery();
 444          $this->q->reset();
 445  
 446          $this->q->select( 'company', 'SUM(employees)' )->from( 'query_test' )
 447                  ->where( $this->e->eq( 1, 1 ) )
 448                  ->groupBy( 'company' )
 449                  ->orderBy( 'company', ezcQuerySelect::DESC )
 450                  ->limit( 1 );
 451          $this->assertEquals( $queryString, $this->q->getQuery() );
 452      }
 453  
 454      public static function suite()
 455      {
 456          return new PHPUnit_Framework_TestSuite( 'ezcQuerySelectTestImpl' );
 457      }
 458  }
 459  ?>

title

Description

title

Description

title

Description

title

title

Body