<?php
/**
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
 * @version //autogentag//
 * @filesource
 * @package Database
 * @subpackage Tests
 */

class TestSubSelect extends ezcQuerySelect
{
    public $db;

    // @todo: Do we need the below? We use them for testing now, but
    // they could come in handy if we want to manipulate SELECT queries in
    // Persistent Object.
    public function buildSelect()
    {
        return $this->selectString;
    }

    public function buildFrom()
    {
        return $this->fromString;
    }

    public function buildWhere()
    {
        return $this->whereString;
    }

    public function buildOrder()
    {
        return $this->orderString;
    }

    public function buildGroup()
    {
        return $this->groupString;
    }

    public function buildLimit()
    {
        return $this->limitString;
    }
}

/**
 * Testing the SQL abstraction layer.
 * This file tests that the methods actually produce correct output for the base
 * implementation regardless of how they methods are called. The _impl file tests
 * the same again, but with full SQL calls, only using one call type and on the database.
 *
 * @package Database
 * @subpackage Tests
 * @todo, test with null input values
 */
class ezcQuerySubSelectTest extends ezcTestCase
{
    private $q; // query
    private $e; // queryExpression
    protected function setUp()
    {
        try
        {
            $db = ezcDbInstance::get();
        }
        catch ( Exception $e )
        {
            $this->markTestSkipped();
        }

        $this->q = new TestSubSelect( $db );
        $this->e = $this->q->expr;
    }

    public function testSubSelect()
    {
        $reference = '( SELECT column FROM table WHERE id = 1 )';
        $q2 = $this->q->subSelect();
        $q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', 1 ) );

        $this->assertEquals( $reference, $q2->getQuery() );
    }

    public function testSubSelectNotQuoted()
    {
        $reference = 'SELECT * FROM test_table WHERE id IN ( SELECT column FROM sub_table WHERE id = 1 )';
        $this->q->select( '*' )
                ->from( 'test_table' );

        $subQ = $this->q->subSelect();
        $subQ->select( 'column' )
           ->from( 'sub_table' )
           ->where( $subQ->expr->eq( 'id', 1 ) );

        $this->q->where( $this->q->expr->in( 'id', $subQ ) );

        $this->assertEquals( $reference, $this->q->getQuery() );
    }

    public function testSubSubSelect()
    {
        $reference = '( SELECT column FROM table WHERE id = ( SELECT * FROM table2 ) )';
        $q2 = $this->q->subSelect();
        $q3 = $q2->subSelect();
        $q3->select( '*' )->from( 'table2' );
        $q2->select( 'column' )->from( 'table' )->where($q2->expr->eq('id', $q3->getQuery() ) );

        $this->assertEquals( $reference, $q2->getQuery() );
    }

    public function testDistinctSubSelect()
    {
        $reference = 'SELECT DISTINCT * FROM table WHERE id = ( SELECT DISTINCT column FROM table2 )';

        $q2 = $this->q->subSelect();
        $q2->selectDistinct( 'column' )->from( 'table2' );
        $this->q
            ->selectDistinct( '*' )
            ->from( 'table' )
            ->where( 
                $this->q->expr->eq( 'id', $q2->getQuery() )
            );

        $this->assertEquals( $reference, $this->q->getQuery() );

    }

    public function testSubSelectIn()
    {
        $reference = 'SELECT * FROM table WHERE id IN ( SELECT column FROM table2 )';

        $q2 = $this->q->subSelect();
        $q2->select( 'column' )->from( 'table2' );
        $this->q
            ->select( '*' )
            ->from( 'table' )
            ->where( 
                $this->q->expr->in( 'id', $q2 )
            );

        $this->assertEquals( $reference, $this->q->getQuery() );

    }

    public function testBindAuto()
    {
        $val1 = '';
        $val2 = '';
        
        $reference = '( SELECT column FROM table WHERE id = :ezcValue1 AND id2 = :ezcValue2 )';
        $q2 = $this->q->subSelect();
        $q2->select( 'column' )
             ->from( 'table' )
               ->where( $q2->expr->eq( 'id', $q2->bindParam( $val1 ) ) )
               ->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2 ) ) );

        $this->assertEquals( $reference, $q2->getQuery() );
    }

    public function testBindManual()
    {
        $reference = '( SELECT column FROM table WHERE id = :test1 AND id2 = :test2 )';
        $val1 = '';
        $val2 = '';
        $q2 = $this->q->subSelect();
        $q2->select( 'column' )
             ->from( 'table' )
               ->where( $q2->expr->eq( 'id', $q2->bindParam( $val1, ':test1' ) ) )
               ->where( $q2->expr->eq( 'id2', $q2->bindParam( $val2, ':test2' ) ) );

        $this->assertEquals( $reference, $q2->getQuery() );
    }

    public function testBug11784()
    {
        $db = ezcDbInstance::get();
        $q = $db->createSelectQuery();
        $q->select( 'somecol' )->from( 'quiz' );

        $qQuestions = $q->subSelect();
        $qQuestions->select( 'id' )->from( 'question' )->where(
            $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
        );

        $q->where(
            $q->expr->in( 'question', $qQuestions )
        );
        
        $this->assertEquals( "SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
    }

    public function testSubselectWithUpdate()
    {
        $db = ezcDbInstance::get();
        $q = $db->createUpdateQuery();
        $q->update( 'quiz' )->set( 'somecol', $q->bindValue( 'test' ) );

        $qQuestions = $q->subSelect();
        $qQuestions->select( 'id' )->from( 'question' )->where(
            $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
        );

        $q->where(
            $q->expr->in( 'question', $qQuestions )
        );

        $this->assertEquals( "UPDATE quiz SET somecol = :ezcValue1 WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue2 )", $q->getQuery() );
    }


    public function testSubselectWithDelete()
    {
        $db = ezcDbInstance::get();
        $q = $db->createDeleteQuery();
        $q->deleteFrom( 'quiz' );

        $qQuestions = $q->subSelect();
        $qQuestions->select( 'id' )->from( 'question' )->where(
            $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
        );

        $q->where(
            $q->expr->in( 'question', $qQuestions )
        );

        $this->assertEquals( "DELETE FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )", $q->getQuery() );
    }

    // Verifies issue #11784 is fixed. Code taken from there.
    public function testSubselectNotQuotedInInExpr()
    {
        $db = ezcDbInstance::get();
        $q = $db->createSelectQuery();
        $q->select( 'somecol' )->from( 'quiz' );

        $qQuestions = $q->subSelect();
        $qQuestions->select( 'id' )->from( 'question' )->where(
            $qQuestions->expr->eq( 'quiz', $qQuestions->bindValue( 1 ) )
        );

        $q->where(
            $q->expr->in( 'question', $qQuestions )
        );

        $this->assertEquals(
            'SELECT somecol FROM quiz WHERE question IN ( SELECT id FROM question WHERE quiz = :ezcValue1 )',
            $q->getQuery()
        );
    }

    public function testSubselectWithAlias()
    {
        $db = ezcDbInstance::get();

        $q = $db->createSelectQuery();
        $q->setAliases( array( 'ID' => 'id' ) );
        $q = $q->select( 'ID', 'name' )->from( 'main' );

        $q2 = $q->subSelect();
            $q2->select( 'main_id' )->from( 'sub' );
            $q2->limit(20,0);

        $q->innerJoin( $q->alias( $q2, 'sub_items' ), 'sub_items.main_id', 'main.id' );   

        $this->assertEquals( "SELECT id, name FROM main INNER JOIN ( SELECT main_id FROM sub LIMIT 20 OFFSET 0 ) AS sub_items ON sub_items.main_id = main.id", $q->getQuery() );
    }

    public static function suite()
    {
        return new PHPUnit_Framework_TestSuite( 'ezcQuerySubSelectTest' );
    }
}
?>