/tests/Zend/Paginator/Adapter/DbSelectTest.php

<?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-2012 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 */

/**
 * @namespace
 */
namespace ZendTest\Paginator\Adapter;
use Zend\Db\Adapter\Pdo;
use Zend\Paginator\Adapter;
use Zend\Db;
use Zend\Db\Select;
use Zend\Paginator\Exception;

require_once __DIR__ . '/../_files/TestTable.php';

/**
 * @category   Zend
 * @package    Zend_Paginator
 * @subpackage UnitTests
 * @copyright  Copyright (c) 2005-2012 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()
    {
        $this->setExpectedException('Zend\Paginator\Adapter\Exception\InvalidArgumentException', 'Row count column not found');
        $this->_adapter->setRowCount($this->_db->select()->from('test'));
    }

    public function testThrowsExceptionIfInvalidQuerySuppliedForRowCount2()
    {
        $wrongcolumn = $this->_db->quoteIdentifier('wrongcolumn');
        $expr = new Db\Expr("COUNT(*) AS $wrongcolumn");
        $query = $this->_db->select($expr)->from('test');

        $this->setExpectedException('Zend\Paginator\Adapter\Exception\InvalidArgumentException', 'Row count column not found');
        $this->_adapter->setRowCount($query);
    }

    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()
    {
        $this->setExpectedException('Zend\Paginator\Adapter\Exception\InvalidArgumentException', 'Invalid row count');
        $this->_adapter->setRowCount('invalid');
    }

    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);

        $adapter = new Adapter\DbSelect($query);
        $this->assertEquals(1, $adapter->count());
    }

    /**
     * @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());
    }

    /**
     * @group ZF-10704
     */
    public function testObjectSelectWithBind()
    {
        $select = $this->_db->select();
        $select->from('test', array('number'))
               ->where('number = ?')
               ->distinct(true)
               ->bind(array(250));

        $adapter = new Adapter\DbSelect($select);
        $this->assertEquals(1, $adapter->count());

        $select->reset(\Zend\Db\Select::DISTINCT);
        $select2 = clone $select;
        $select2->reset(\Zend\Db\Select::WHERE)
                ->where('number = 500');

        $selectUnion = $this->_db
                           ->select()
                           ->bind(array(250));

        $selectUnion->union(array($select, $select2));
        $adapter = new Adapter\DbSelect($selectUnion);
        $this->assertEquals(2, $adapter->count());
    }
}