tests/Zend/Db/Select/StaticTest.php

   1 <?php
   2 /**
   3  * Zend Framework
   4  *
   5  * LICENSE
   6  *
   7  * This source file is subject to the new BSD license that is bundled
   8  * with this package in the file LICENSE.txt.
   9  * It is also available through the world-wide-web at this URL:
  10  * http://framework.zend.com/license/new-bsd
  11  * If you did not receive a copy of the license and are unable to
  12  * obtain it through the world-wide-web, please send an email
  13  * to license@zend.com so we can send you a copy immediately.
  14  *
  15  * @category   Zend
  16  * @package    Zend_Db
  17  * @subpackage UnitTests
  18  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  19  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  20  * @version    $Id$
  21  */
  22
  23
  24 /**
  25  * @see Zend_Db_Select_TestCommon
  26  */
  27 require_once 'Zend/Db/Select/TestCommon.php';
  28
  29
  30 PHPUnit_Util_Filter::addFileToFilter(__FILE__);
  31
  32
  33 /**
  34  * @category   Zend
  35  * @package    Zend_Db
  36  * @subpackage UnitTests
  37  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  38  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  39  * @group      Zend_Db
  40  * @group      Zend_Db_Select
  41  */
  42 class Zend_Db_Select_StaticTest extends Zend_Db_Select_TestCommon
  43 {
  44     /**
  45      * Test basic use of the Zend_Db_Select class.
  46      *
  47      * @return void
  48      */
  49     public function testSelect()
  50     {
  51         $select = $this->_select();
  52         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
  53         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts"', $sql);
  54     }
  55
  56     /**
  57      * Test basic use of the Zend_Db_Select class.
  58      *
  59      * @return void
  60      */
  61     public function testSelectQuery()
  62     {
  63         $select = $this->_select();
  64         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
  65         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts"', $sql);
  66         $stmt = $select->query();
  67         Zend_Loader::loadClass('Zend_Db_Statement_Static');
  68         $this->assertType('Zend_Db_Statement_Static', $stmt);
  69     }
  70
  71     /**
  72      * ZF-2017: Test bind use of the Zend_Db_Select class.
  73      */
  74     public function testSelectQueryWithBinds()
  75     {
  76         $select = $this->_select()->where('product_id = :product_id')
  77                                   ->bind(array(':product_id' => 1));
  78
  79         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
  80         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE (product_id = :product_id)', $sql);
  81
  82         $stmt = $select->query();
  83         Zend_Loader::loadClass('Zend_Db_Statement_Static');
  84         $this->assertType('Zend_Db_Statement_Static', $stmt);
  85     }
  86
  87     /**
  88      * Test Zend_Db_Select specifying columns
  89      *
  90      * @return void
  91      */
  92     public function testSelectColumnsScalar()
  93     {
  94         $select = $this->_selectColumnsScalar();
  95         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
  96         $this->assertEquals('SELECT "zfproducts"."product_name" FROM "zfproducts"', $sql);
  97     }
  98
  99     /**
 100      * Test Zend_Db_Select specifying columns
 101      *
 102      * @return void
 103      */
 104     public function testSelectColumnsArray()
 105     {
 106         $select = $this->_selectColumnsArray();
 107         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 108         $this->assertEquals('SELECT "zfproducts"."product_id", "zfproducts"."product_name" FROM "zfproducts"', $sql);
 109     }
 110
 111     /**
 112      * Test support for column aliases.
 113      * e.g. from('table', array('alias' => 'col1')).
 114      *
 115      * @return void
 116      */
 117     public function testSelectColumnsAliases()
 118     {
 119         $select = $this->_selectColumnsAliases();
 120         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 121         $this->assertEquals('SELECT "zfproducts"."product_name" AS "alias" FROM "zfproducts"', $sql);
 122     }
 123
 124     /**
 125      * Test syntax to support qualified column names,
 126      * e.g. from('table', array('table.col1', 'table.col2')).
 127      *
 128      * @return void
 129      */
 130     public function testSelectColumnsQualified()
 131     {
 132         $select = $this->_selectColumnsQualified();
 133         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 134         $this->assertEquals('SELECT "zfproducts"."product_name" FROM "zfproducts"', $sql);
 135     }
 136
 137     /**
 138      * Test support for columns defined by Zend_Db_Expr.
 139      *
 140      * @return void
 141      */
 142     public function testSelectColumnsExpr()
 143     {
 144         $select = $this->_selectColumnsExpr();
 145         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 146         $this->assertEquals('SELECT "zfproducts"."product_name" FROM "zfproducts"', $sql);
 147     }
 148
 149     /**
 150      * Test support for automatic conversion of SQL functions to
 151      * Zend_Db_Expr, e.g. from('table', array('COUNT(*)'))
 152      * should generate the same result as
 153      * from('table', array(new Zend_Db_Expr('COUNT(*)')))
 154      */
 155
 156     public function testSelectColumnsAutoExpr()
 157     {
 158         $select = $this->_selectColumnsAutoExpr();
 159         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 160         $this->assertEquals('SELECT COUNT(*) AS "count" FROM "zfproducts"', $sql);
 161     }
 162
 163     /**
 164      * Test adding the DISTINCT query modifier to a Zend_Db_Select object.
 165      */
 166
 167     public function testSelectDistinctModifier()
 168     {
 169         $select = $this->_selectDistinctModifier();
 170         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 171         $this->assertEquals('SELECT DISTINCT 327 FROM "zfproducts"', $sql);
 172     }
 173
 174     /**
 175      * Test support for schema-qualified table names in from()
 176      * e.g. from('schema.table').
 177      */
 178
 179     public function testSelectFromQualified()
 180     {
 181         $select = $this->_selectFromQualified();
 182         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 183         $this->assertEquals('SELECT "zfproducts".* FROM "dummy"."zfproducts"', $sql);
 184     }
 185
 186     public function testSelectColumnsReset()
 187     {
 188         $select = $this->_selectColumnsReset()
 189             ->reset(Zend_Db_Select::COLUMNS)
 190             ->columns('product_name');
 191         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 192         $this->assertEquals('SELECT "p"."product_name" FROM "zfproducts" AS "p"', $sql);
 193     }
 194
 195     public function testSelectFromForUpdate()
 196     {
 197         $select = $this->_db->select()
 198             ->from("zfproducts")
 199             ->forUpdate();
 200         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 201         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" FOR UPDATE', $sql);
 202     }
 203
 204     /**
 205      * Test adding a JOIN to a Zend_Db_Select object.
 206      */
 207
 208     public function testSelectJoin()
 209     {
 210         $select = $this->_selectJoin();
 211         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 212         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" INNER JOIN "zfbugs_products" ON "zfproducts"."product_id" = "zfbugs_products"."product_id"', $sql);
 213     }
 214
 215     /**
 216      * Test adding an INNER JOIN to a Zend_Db_Select object.
 217      * This should be exactly the same as the plain JOIN clause.
 218      */
 219
 220     public function testSelectJoinWithCorrelationName()
 221     {
 222         $select = $this->_selectJoinWithCorrelationName();
 223         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 224         $this->assertEquals('SELECT "xyz1".*, "xyz2".* FROM "zfproducts" AS "xyz1" INNER JOIN "zfbugs_products" AS "xyz2" ON "xyz1"."product_id" = "xyz2"."product_id" WHERE ("xyz1"."product_id" = 1)', $sql);
 225     }
 226
 227     /**
 228      * Test adding an INNER JOIN to a Zend_Db_Select object.
 229      * This should be exactly the same as the plain JOIN clause.
 230      */
 231
 232     public function testSelectJoinInner()
 233     {
 234         $select = $this->_selectJoinInner();
 235         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 236         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" INNER JOIN "zfbugs_products" ON "zfproducts"."product_id" = "zfbugs_products"."product_id"', $sql);
 237     }
 238
 239     /**
 240      * Test adding an outer join to a Zend_Db_Select object.
 241      */
 242
 243     public function testSelectJoinLeft()
 244     {
 245         $select = $this->_selectJoinLeft();
 246         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 247         $this->assertEquals('SELECT "zfbugs".*, "zfbugs_products".* FROM "zfbugs" LEFT JOIN "zfbugs_products" ON "zfbugs"."bug_id" = "zfbugs_products"."bug_id"', $sql);
 248     }
 249
 250     /**
 251      * Test adding an outer join to a Zend_Db_Select object.
 252      */
 253
 254     public function testSelectJoinRight()
 255     {
 256         $select = $this->_selectJoinRight();
 257         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 258         $this->assertEquals('SELECT "zfbugs_products".*, "zfbugs".* FROM "zfbugs_products" RIGHT JOIN "zfbugs" ON "zfbugs_products"."bug_id" = "zfbugs"."bug_id"', $sql);
 259     }
 260
 261     /**
 262      * Test adding a cross join to a Zend_Db_Select object.
 263      */
 264
 265     public function testSelectJoinCross()
 266     {
 267         $select = $this->_selectJoinCross();
 268         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 269         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" CROSS JOIN "zfbugs_products"', $sql);
 270     }
 271
 272     /**
 273      * Test support for schema-qualified table names in join(),
 274      * e.g. join('schema.table', 'condition')
 275      */
 276
 277     public function testSelectJoinQualified()
 278     {
 279         $select = $this->_selectJoinQualified();
 280         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 281         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" INNER JOIN "dummy"."zfbugs_products" ON "zfproducts"."product_id" = "zfbugs_products"."product_id"', $sql);
 282     }
 283
 284     /**
 285      * Test adding a JOIN USING to a Zend_Db_Select object.
 286      */
 287
 288     public function testSelectJoinUsing()
 289     {
 290         $select = $this->_selectJoinUsing();
 291         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 292         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" INNER JOIN "zfbugs_products" ON "zfbugs_products"."product_id" = "zfproducts"."product_id" WHERE ("zfbugs_products"."product_id" < 3)', $sql);
 293     }
 294
 295     /**
 296      * Test adding a JOIN INNER USING to a Zend_Db_Select object.
 297      */
 298
 299     public function testSelectJoinInnerUsing()
 300     {
 301         $select = $this->_selectJoinInnerUsing();
 302         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 303         $this->assertEquals('SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" INNER JOIN "zfbugs_products" ON "zfbugs_products"."product_id" = "zfproducts"."product_id" WHERE ("zfbugs_products"."product_id" < 3)', $sql);
 304     }
 305
 306     public function testSelectJoinWithNocolumns()
 307     {
 308         $select = $this->_selectJoinWithNocolumns();
 309         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 310         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" INNER JOIN "zfbugs" ON "zfbugs"."bug_id" = 1 INNER JOIN "zfbugs_products" ON "zfproducts"."product_id" = "zfbugs_products"."product_id" AND "zfbugs_products"."bug_id" = "zfbugs"."bug_id"', $sql);
 311     }
 312
 313     /**
 314      * Test adding a WHERE clause to a Zend_Db_Select object.
 315      */
 316
 317     public function testSelectWhere()
 318     {
 319         $select = $this->_selectWhere();
 320         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 321         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 2)', $sql);
 322     }
 323
 324     /**
 325      * Test adding an array in the WHERE clause to a Zend_Db_Select object.
 326      */
 327
 328     public function testSelectWhereArray()
 329     {
 330         $select = $this->_selectWhereArray();
 331         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 332         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" IN (1, 2, 3))', $sql);
 333     }
 334
 335     /**
 336      * test adding more WHERE conditions,
 337      * which should be combined with AND by default.
 338      */
 339
 340     public function testSelectWhereAnd()
 341     {
 342         $select = $this->_selectWhereAnd();
 343         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 344         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 2) AND ("product_id" = 1)', $sql);
 345     }
 346
 347     /**
 348      * Test support for where() with a parameter,
 349      * e.g. where('id = ?', 1).
 350      */
 351
 352     public function testSelectWhereWithParameter()
 353     {
 354         $select = $this->_selectWhereWithParameter();
 355         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 356         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 2)', $sql);
 357     }
 358
 359     /**
 360      * Test support for where() with a parameter,
 361      * e.g. where('id = ?', 1).
 362      */
 363
 364     public function testSelectWhereWithType()
 365     {
 366         $select = $this->_selectWhereWithType();
 367         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 368         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 2)', $sql);
 369     }
 370
 371     /**
 372      * Test support for where() with a float parameter,
 373      * e.g. where('id = ?', 1).
 374      */
 375
 376     public function testSelectWhereWithTypeFloat()
 377     {
 378         $select = $this->_selectWhereWithTypeFloat();
 379         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 380         $this->assertEquals('SELECT "zfprice".* FROM "zfprice" WHERE ("price_total" = 200.450000)', $sql);
 381     }
 382
 383     /**
 384      *      * Test adding an OR WHERE clause to a Zend_Db_Select object.
 385      */
 386
 387     public function testSelectWhereOr()
 388     {
 389         $select = $this->_selectWhereOr();
 390         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 391         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 1) OR ("product_id" = 2)', $sql);
 392     }
 393
 394     /**
 395      * Test support for where() with a parameter,
 396      * e.g. orWhere('id = ?', 2).
 397      */
 398
 399     public function testSelectWhereOrWithParameter()
 400     {
 401         $select = $this->_selectWhereOrWithParameter();
 402         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 403         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" WHERE ("product_id" = 1) OR ("product_id" = 2)', $sql);
 404     }
 405
 406     /**
 407      * Test adding a GROUP BY clause to a Zend_Db_Select object.
 408      */
 409
 410     public function testSelectGroupBy()
 411     {
 412         $select = $this->_selectGroupBy();
 413         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 414         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" ORDER BY "bug_id" ASC', $sql);
 415     }
 416
 417     /**
 418      * Test support for qualified table in group(),
 419      * e.g. group('schema.table').
 420      */
 421
 422     public function testSelectGroupByQualified()
 423     {
 424         $select = $this->_selectGroupByQualified();
 425         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 426         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "zfbugs_products"."bug_id" ORDER BY "bug_id" ASC', $sql);
 427     }
 428
 429     /**
 430      * Test support for Zend_Db_Expr in group(),
 431      * e.g. group(new Zend_Db_Expr('id+1'))
 432      */
 433
 434     public function testSelectGroupByExpr()
 435     {
 436         $select = $this->_selectGroupByExpr();
 437         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 438         $this->assertEquals('SELECT "bug_id"+1 AS "bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id"+1 ORDER BY "bug_id"+1', $sql);
 439     }
 440
 441     /**
 442      * Test support for automatic conversion of a SQL
 443      * function to a Zend_Db_Expr in group(),
 444      * e.g.  group('LOWER(title)') should give the same
 445      * result as group(new Zend_Db_Expr('LOWER(title)')).
 446      */
 447
 448
 449     public function testSelectGroupByAutoExpr()
 450     {
 451         $select = $this->_selectGroupByAutoExpr();
 452         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 453         $this->assertEquals('SELECT ABS("zfbugs_products"."bug_id") AS "bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY ABS("zfbugs_products"."bug_id") ORDER BY ABS("zfbugs_products"."bug_id") ASC', $sql);
 454     }
 455
 456     /**
 457      * Test adding a HAVING clause to a Zend_Db_Select object.
 458      */
 459
 460     public function testSelectHaving()
 461     {
 462         $select = $this->_selectHaving();
 463         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 464         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" HAVING (COUNT(*) > 1) ORDER BY "bug_id" ASC', $sql);
 465     }
 466
 467
 468     public function testSelectHavingAnd()
 469     {
 470         $select = $this->_selectHavingAnd();
 471         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 472         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" HAVING (COUNT(*) > 1) AND (COUNT(*) = 1) ORDER BY "bug_id" ASC', $sql);
 473     }
 474
 475     /**
 476      * Test support for parameter in having(),
 477      * e.g. having('count(*) > ?', 1).
 478      */
 479
 480
 481     public function testSelectHavingWithParameter()
 482     {
 483         $select = $this->_selectHavingWithParameter();
 484         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 485         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" HAVING (COUNT(*) > 1) ORDER BY "bug_id" ASC', $sql);
 486     }
 487
 488     /**
 489      * Test adding a HAVING clause to a Zend_Db_Select object.
 490      */
 491
 492
 493     public function testSelectHavingOr()
 494     {
 495         $select = $this->_selectHavingOr();
 496         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 497         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" HAVING (COUNT(*) > 1) OR (COUNT(*) = 1) ORDER BY "bug_id" ASC', $sql);
 498     }
 499
 500     /**
 501      * Test support for parameter in orHaving(),
 502      * e.g. orHaving('count(*) > ?', 1).
 503      */
 504
 505     public function testSelectHavingOrWithParameter()
 506     {
 507         $select = $this->_selectHavingOrWithParameter();
 508         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 509         $this->assertEquals('SELECT "zfbugs_products"."bug_id", COUNT(*) AS "thecount" FROM "zfbugs_products" GROUP BY "bug_id" HAVING (COUNT(*) > 1) OR (COUNT(*) = 1) ORDER BY "bug_id" ASC', $sql);
 510     }
 511
 512     /**
 513      * Test adding an ORDER BY clause to a Zend_Db_Select object.
 514      */
 515
 516     public function testSelectOrderBy()
 517     {
 518         $select = $this->_selectOrderBy();
 519         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 520         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC', $sql);
 521     }
 522
 523
 524     public function testSelectOrderByArray()
 525     {
 526         $select = $this->_selectOrderByArray();
 527         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 528         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_name" ASC, "product_id" ASC', $sql);
 529     }
 530
 531
 532     public function testSelectOrderByAsc()
 533     {
 534         $select = $this->_selectOrderByAsc();
 535         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 536         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC', $sql);
 537     }
 538
 539
 540     public function testSelectOrderByDesc()
 541     {
 542         $select = $this->_selectOrderByDesc();
 543         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 544         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" DESC', $sql);
 545     }
 546
 547     /**
 548      * Test support for qualified table in order(),
 549      * e.g. order('schema.table').
 550      */
 551
 552     public function testSelectOrderByQualified()
 553     {
 554         $select = $this->_selectOrderByQualified();
 555         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 556         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "zfproducts"."product_id" ASC', $sql);
 557     }
 558
 559     /**
 560      * Test support for Zend_Db_Expr in order(),
 561      * e.g. order(new Zend_Db_Expr('id+1')).
 562      */
 563
 564     public function testSelectOrderByExpr()
 565     {
 566         $select = $this->_selectOrderByExpr();
 567         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 568         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY 1', $sql);
 569     }
 570
 571     /**
 572      * Test automatic conversion of SQL functions to
 573      * Zend_Db_Expr, e.g. order('LOWER(title)')
 574      * should give the same result as
 575      * order(new Zend_Db_Expr('LOWER(title)')).
 576      */
 577
 578     public function testSelectOrderByAutoExpr()
 579     {
 580         $select = $this->_selectOrderByAutoExpr();
 581         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 582         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY ABS("zfproducts"."product_id") ASC', $sql);
 583     }
 584
 585     /**
 586      * Test ORDER BY clause that contains multiple lines.
 587      * See ZF-1822, which says that the regexp matching
 588      * ASC|DESC fails when string is multi-line.
 589      */
 590
 591     public function testSelectOrderByMultiLine()
 592     {
 593         $select = $this->_selectOrderByMultiLine();
 594         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 595         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" DESC', $sql);
 596     }
 597
 598     /**
 599      * Test adding a LIMIT clause to a Zend_Db_Select object.
 600      */
 601
 602     public function testSelectLimit()
 603     {
 604         $select = $this->_selectLimit();
 605         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 606         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC LIMIT 1 OFFSET 0', $sql);
 607     }
 608
 609     /**
 610      * Not applicable in static test
 611      * @group ZF-5263
 612      */
 613     public function testSelectLimitFetchCol()
 614     {}
 615
 616     public function testSelectLimitNone()
 617     {
 618         $select = $this->_selectLimitNone();
 619         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 620         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC', $sql);
 621     }
 622
 623
 624     public function testSelectLimitOffset()
 625     {
 626         $select = $this->_selectLimitOffset();
 627         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 628         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC LIMIT 1 OFFSET 1', $sql);
 629     }
 630
 631     /**
 632      * Test the limitPage() method of a Zend_Db_Select object.
 633      */
 634
 635     public function testSelectLimitPageOne()
 636     {
 637         $select = $this->_selectLimitPageOne();
 638         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 639         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC LIMIT 1 OFFSET 0', $sql);
 640     }
 641
 642
 643     public function testSelectLimitPageTwo()
 644     {
 645         $select = $this->_selectLimitPageTwo();
 646         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 647         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY "product_id" ASC LIMIT 1 OFFSET 1', $sql);
 648     }
 649
 650     public function testSelectUnionString()
 651     {
 652         $select = $this->_selectUnionString();
 653         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 654         $this->assertEquals('SELECT "bug_id" AS "id", "bug_status" AS "name" FROM "zfbugs" UNION SELECT "product_id" AS "id", "product_name" AS "name" FROM "zfproducts" ORDER BY "id" ASC', $sql);
 655     }
 656
 657     public function testSelectOrderByPosition()
 658     {
 659         $select = $this->_selectOrderByPosition();
 660
 661         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 662         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY 2 ASC', $sql);
 663     }
 664
 665     public function testSelectOrderByPositionAsc()
 666     {
 667         $select = $this->_selectOrderByPositionAsc();
 668
 669         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 670         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY 2 ASC', $sql);
 671     }
 672
 673     public function testSelectOrderByPositionDesc()
 674     {
 675         $select = $this->_selectOrderByPositionDesc();
 676
 677         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 678         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY 2 DESC', $sql);
 679     }
 680
 681     public function testSelectOrderByMultiplePositions()
 682     {
 683         $select = $this->_selectOrderByMultiplePositions();
 684
 685         $sql = preg_replace('/\\s+/', ' ', $select->__toString());
 686         $this->assertEquals('SELECT "zfproducts".* FROM "zfproducts" ORDER BY 2 DESC, 1 DESC', $sql);
 687     }
 688
 689     /**
 690      * @group ZF-7491
 691      */
 692     public function testPhp53Assembly()
 693     {
 694         if (version_compare(PHP_VERSION, 5.3) == -1 ) {
 695             $this->markTestSkipped('This test needs at least PHP 5.3');
 696         }
 697         $select = $this->_db->select();
 698         $select->from('table1', '*');
 699         $select->joinLeft(array('table2'), 'table1.id=table2.id');
 700         $target = 'SELECT "table1".*, "table2".* FROM "table1"'
 701                 . "\n" . ' LEFT JOIN "table2" ON table1.id=table2.id';
 702         $this->assertEquals($target, $select->assemble());
 703     }
 704
 705     /**
 706      * @group ZF-7223
 707      */
 708     public function testMaxIntegerValueWithLimit()
 709     {
 710         $select = $this->_db->select();
 711         $select->from('table1')->limit(0, 5);
 712         $target = 'SELECT "table1".* FROM "table1" LIMIT ' . PHP_INT_MAX . ' OFFSET 5';
 713         $this->assertEquals($target, $select->assemble());
 714     }
 715
 716     public function getDriver()
 717     {
 718         return 'Static';
 719     }
 720 }