/tests/Zend/Paginator/Adapter/DbSelectTest.php
PHP | 470 lines | 289 code | 83 blank | 98 comment | 1 complexity | ce6ea857bf78d168a33d68202ded3e1f MD5 | raw file
- <?php
- /**
- * Zend Framework
- *
- * LICENSE
- *
- * This source file is subject to the new BSD license that is bundled
- * with this package in the file LICENSE.txt.
- * It is also available through the world-wide-web at this URL:
- * http://framework.zend.com/license/new-bsd
- * If you did not receive a copy of the license and are unable to
- * obtain it through the world-wide-web, please send an email
- * to license@zend.com so we can send you a copy immediately.
- *
- * @category Zend
- * @package Zend_Paginator
- * @subpackage UnitTests
- * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
- * @license http://framework.zend.com/license/new-bsd New BSD License
- * @version $Id$
- */
- /**
- * @namespace
- */
- namespace ZendTest\Paginator\Adapter;
- use Zend\Db\Adapter\Pdo;
- use Zend\Paginator\Adapter;
- use Zend\Db;
- use Zend\Db\Select;
- require_once __DIR__ . '/../_files/TestTable.php';
- /**
- * @category Zend
- * @package Zend_Paginator
- * @subpackage UnitTests
- * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
- * @license http://framework.zend.com/license/new-bsd New BSD License
- * @group Zend_Paginator
- */
- class DbSelectTest extends \PHPUnit_Framework_TestCase
- {
- /**
- * @var Zend_Paginator_Adapter_DbSelect
- */
- protected $_adapter;
- /**
- * @var Zend_Db_Adapter_Pdo_Sqlite
- */
- protected $_db;
- /**
- * @var Zend_Db_Select
- */
- protected $_query;
- /**
- * @var Zend_Db_Table_Abstract
- */
- protected $_table;
- /**
- * Prepares the environment before running a test.
- */
- protected function setUp()
- {
- if (!extension_loaded('pdo_sqlite')) {
- $this->markTestSkipped('Pdo_Sqlite extension is not loaded');
- }
- parent::setUp();
- $this->_db = new Pdo\Sqlite(array(
- 'dbname' => __DIR__ . '/../_files/test.sqlite'
- ));
- $this->_table = new \ZendTest\Paginator\TestAsset\TestTable($this->_db);
- $this->_query = $this->_db->select()->from('test')
- ->order('number ASC'); // ZF-3740
- //->limit(1000, 0); // ZF-3727
- $this->_adapter = new Adapter\DbSelect($this->_query);
- }
- /**
- * Cleans up the environment after running a test.
- */
- protected function tearDown()
- {
- $this->_adapter = null;
- parent::tearDown();
- }
- public function testGetsItemsAtOffsetZero()
- {
- $actual = $this->_adapter->getItems(0, 10);
- $i = 1;
- foreach ($actual as $item) {
- $this->assertEquals($i, $item['number']);
- $i++;
- }
- }
- public function testGetsItemsAtOffsetTen()
- {
- $actual = $this->_adapter->getItems(10, 10);
- $i = 11;
- foreach ($actual as $item) {
- $this->assertEquals($i, $item['number']);
- $i++;
- }
- }
- public function testAcceptsIntegerValueForRowCount()
- {
- $this->_adapter->setRowCount(101);
- $this->assertEquals(101, $this->_adapter->count());
- }
- public function testThrowsExceptionIfInvalidQuerySuppliedForRowCount()
- {
- try {
- $this->_adapter->setRowCount($this->_db->select()->from('test'));
- } catch (\Exception $e) {
- $this->assertType('Zend\Paginator\Exception', $e);
- $this->assertContains('Row count column not found', $e->getMessage());
- }
- try {
- $wrongcolumn = $this->_db->quoteIdentifier('wrongcolumn');
- $expr = new Db\Expr("COUNT(*) AS $wrongcolumn");
- $query = $this->_db->select($expr)->from('test');
- $this->_adapter->setRowCount($query);
- } catch (\Exception $e) {
- $this->assertType('Zend\Paginator\Exception', $e);
- $this->assertEquals('Row count column not found', $e->getMessage());
- }
- }
- public function testAcceptsQueryForRowCount()
- {
- $row_count_column = $this->_db->quoteIdentifier(Adapter\DbSelect::ROW_COUNT_COLUMN);
- $expression = new Db\Expr("COUNT(*) AS $row_count_column");
- $rowCount = clone $this->_query;
- $rowCount->reset(Select::COLUMNS)
- ->reset(Select::ORDER) // ZF-3740
- ->reset(Select::LIMIT_OFFSET) // ZF-3727
- ->reset(Select::GROUP) // ZF-4001
- ->columns($expression);
- $this->_adapter->setRowCount($rowCount);
- $this->assertEquals(500, $this->_adapter->count());
- }
- public function testThrowsExceptionIfInvalidRowCountValueSupplied()
- {
- try {
- $this->_adapter->setRowCount('invalid');
- } catch (\Exception $e) {
- $this->assertType('Zend\Paginator\Exception', $e);
- $this->assertEquals('Invalid row count', $e->getMessage());
- }
- }
- public function testReturnsCorrectCountWithAutogeneratedQuery()
- {
- $expected = 500;
- $actual = $this->_adapter->count();
- $this->assertEquals($expected, $actual);
- }
- public function testDbTableSelectDoesNotThrowException()
- {
- $adapter = new Adapter\DbSelect($this->_table->select());
- $count = $adapter->count();
- $this->assertEquals(500, $count);
- }
- /**
- * @group ZF-4001
- */
- public function testGroupByQueryReturnsOneRow()
- {
- $query = $this->_db->select()->from('test')
- ->order('number ASC')
- ->limit(1000, 0)
- ->group('number');
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(500, $adapter->count());
- }
- /**
- * @group ZF-4001
- */
- public function testGroupByQueryOnEmptyTableReturnsRowCountZero()
- {
- $db = new Pdo\Sqlite(array(
- 'dbname' => __DIR__ . '/../_files/testempty.sqlite'
- ));
- $query = $db->select()->from('test')
- ->order('number ASC')
- ->limit(1000, 0);
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(0, $adapter->count());
- }
- /**
- * @group ZF-4001
- */
- public function testGroupByQueryReturnsCorrectResult()
- {
- $query = $this->_db->select()->from('test')
- ->order('number ASC')
- ->limit(1000, 0)
- ->group('testgroup');
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(2, $adapter->count());
- }
- /**
- * @group ZF-4032
- */
- public function testDistinctColumnQueryReturnsCorrectResult()
- {
- $query = $this->_db->select()->from('test', 'testgroup')
- ->order('number ASC')
- ->limit(1000, 0)
- ->distinct();
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(2, $adapter->count());
- }
- /**
- * @group ZF-4094
- */
- public function testSelectSpecificColumns()
- {
- $number = $this->_db->quoteIdentifier('number');
- $query = $this->_db->select()->from('test', array('testgroup', 'number'))
- ->where("$number >= ?", '1');
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(500, $adapter->count());
- }
- /**
- * @group ZF-4177
- */
- public function testSelectDistinctAllUsesRegularCountAll()
- {
- $query = $this->_db->select()->from('test')
- ->distinct();
- $adapter = new Adapter\DbSelect($query);
- $this->assertEquals(500, $adapter->count());
- }
- /**
- * @group ZF-5233
- */
- public function testSelectHasAliasedColumns()
- {
- $db = $this->_db;
- $db->query('DROP TABLE IF EXISTS `sandboxTransaction`');
- $db->query('DROP TABLE IF EXISTS `sandboxForeign`');
- // A transaction table
- $db->query(
- 'CREATE TABLE `sandboxTransaction` (
- `id` INTEGER PRIMARY KEY,
- `foreign_id` INT( 1 ) NOT NULL ,
- `name` TEXT NOT NULL
- ) '
- );
- // A foreign table
- $db->query(
- 'CREATE TABLE `sandboxForeign` (
- `id` INTEGER PRIMARY KEY,
- `name` TEXT NOT NULL
- ) '
- );
- // Insert some data
- $db->insert('sandboxTransaction',
- array(
- 'foreign_id' => 1,
- 'name' => 'transaction 1 with foreign_id 1',
- )
- );
- $db->insert('sandboxTransaction',
- array(
- 'foreign_id' => 1,
- 'name' => 'transaction 2 with foreign_id 1',
- )
- );
- $db->insert('sandboxForeign',
- array(
- 'name' => 'John Doe',
- )
- );
- $db->insert('sandboxForeign',
- array(
- 'name' => 'Jane Smith',
- )
- );
- $query = $db->select()->from(array('a'=>'sandboxTransaction'), array())
- ->join(array('b'=>'sandboxForeign'), 'a.foreign_id = b.id', array('name'))
- ->distinct(true);
- try {
- $adapter = new Adapter\DbSelect($query);
- $adapter->count();
- } catch (\Exception $e) {
- $this->fail($e->getMessage());
- }
- }
- /**
- * @group ZF-5956
- */
- public function testUnionSelect()
- {
- $union = $this->_db->select()->union(array(
- $this->_db->select()->from('test')->where('number <= 250'),
- $this->_db->select()->from('test')->where('number > 250')
- ));
- $adapter = new Adapter\DbSelect($union);
- $expected = 500;
- $actual = $adapter->count();
- $this->assertEquals($expected, $actual);
- }
- /**
- * @group ZF-7045
- */
- public function testGetCountSelect()
- {
- $union = $this->_db->select()->union(array(
- $this->_db->select()->from('test')->where('number <= 250'),
- $this->_db->select()->from('test')->where('number > 250')
- ));
- $adapter = new Adapter\DbSelect($union);
- $expected = 'SELECT COUNT(1) AS "zend_paginator_row_count" FROM (SELECT "test".* FROM "test" WHERE (number <= 250) UNION SELECT "test".* FROM "test" WHERE (number > 250)) AS "t"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- }
- /**
- * @group ZF-5295
- */
- public function testMultipleDistinctColumns()
- {
- $select = $this->_db->select()->from('test', array('testgroup', 'number'))
- ->distinct(true);
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(1) AS "zend_paginator_row_count" FROM (SELECT DISTINCT "test"."testgroup", "test"."number" FROM "test") AS "t"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(500, $adapter->count());
- }
- /**
- * @group ZF-5295
- */
- public function testSingleDistinctColumn()
- {
- $select = $this->_db->select()->from('test', 'testgroup')
- ->distinct(true);
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(DISTINCT "test"."testgroup") AS "zend_paginator_row_count" FROM "test"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(2, $adapter->count());
- }
- /**
- * @group ZF-6330
- */
- public function testGroupByMultipleColumns()
- {
- $select = $this->_db->select()->from('test', 'testgroup')
- ->group(array('number', 'testgroup'));
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(1) AS "zend_paginator_row_count" FROM (SELECT "test"."testgroup" FROM "test" GROUP BY "number"' . ",\n\t" . '"testgroup") AS "t"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(500, $adapter->count());
- }
- /**
- * @group ZF-6330
- */
- public function testGroupBySingleColumn()
- {
- $select = $this->_db->select()->from('test', 'testgroup')
- ->group('test.testgroup');
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(DISTINCT "test"."testgroup") AS "zend_paginator_row_count" FROM "test"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(2, $adapter->count());
- }
- /**
- * @group ZF-6562
- */
- public function testSelectWithHaving()
- {
- $select = $this->_db->select()->from('test')
- ->group('number')
- ->having('number > 250');
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(1) AS "zend_paginator_row_count" FROM (SELECT "test".* FROM "test" GROUP BY "number" HAVING (number > 250)) AS "t"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(250, $adapter->count());
- }
- /**
- * @group ZF-7127
- */
- public function testMultipleGroupSelect()
- {
- $select = $this->_db->select()->from('test')
- ->group('testgroup')
- ->group('number')
- ->where('number > 250');
- $adapter = new Adapter\DbSelect($select);
- $expected = 'SELECT COUNT(1) AS "zend_paginator_row_count" FROM (SELECT "test".* FROM "test" WHERE (number > 250) GROUP BY "testgroup"' . ",\n\t" . '"number") AS "t"';
- $this->assertEquals($expected, $adapter->getCountSelect()->__toString());
- $this->assertEquals(250, $adapter->count());
- }
- }