package org.apache.torque.generated.peer;

/*
 * 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.
 */

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.BaseDatabaseTestCase;
import org.apache.torque.ColumnImpl;
import org.apache.torque.TorqueException;
import org.apache.torque.adapter.MysqlAdapter;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.test.dbobject.Author;
import org.apache.torque.test.dbobject.Book;
import org.apache.torque.test.peer.AuthorPeer;
import org.apache.torque.test.peer.BookPeer;

/**
 * Tests subselects in the where clause.
 *
 * @version $Id: WhereClauseSubselectTest.java 1395238 2012-10-07 07:30:25Z tfischer $
 */
public class WhereClauseSubselectTest extends BaseDatabaseTestCase
{
    private static Log log = LogFactory.getLog(WhereClauseSubselectTest.class);

    Author author1;
    Author author2;
    Author author2b;
    Author author3;
    Book book1;
    Book book3;

    @Override
    public void setUp() throws Exception
    {
        super.setUp();
        cleanBookstore();
        author1 = new Author();
        author1.setName("author1");
        author1.save();
        author2 = new Author();
        author2.setName("author2");
        author2.save();
        author2b = new Author();
        author2b.setName("author2");
        author2b.save();
        author3 = new Author();
        author3.setName("author3");
        author3.save();
        book1 = new Book();
        book1.setTitle("Book from author 1");
        book1.setAuthor(author1);
        book1.save();
        book3 = new Book();
        book3.setTitle("Book from author 3");
        book3.setAuthor(author3);
        book3.save();
    }

    /**
     * Tests whether we can execute subselects using an in clause with
     * integer values.
     *
     * @throws Exception if the test fails
     */
    public void testSubselectUsingInWithInteger() throws Exception
    {
        if (!supportsSubselects())
        {
            return;
        }

        Criteria subquery = new Criteria();
        subquery.addSelectColumn(AuthorPeer.AUTHOR_ID);
        List<String> authorIds = new ArrayList<String>();
        authorIds.add(author1.getName());
        authorIds.add(author2.getName());
        subquery.where(AuthorPeer.NAME, authorIds, Criteria.IN);
        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.AUTHOR_ID, subquery, Criteria.IN);
        criteria.addDescendingOrderByColumn(AuthorPeer.AUTHOR_ID);

        List<?> result = AuthorPeer.doSelect(criteria);
        assertEquals("Expected result of size 2 but got " + result.size(),
                result.size(),
                3);
        Author author = (Author) result.get(0);
        assertEquals("Expected author with Id "
                + author2b.getAuthorId()
                + " at first position but got "
                + author.getAuthorId(),
                author2b.getAuthorId(),
                author.getAuthorId());
        author = (Author) result.get(1);
        assertEquals("Expected author with Id "
                + author2.getAuthorId()
                + " at second position but got "
                + author.getAuthorId(),
                author2.getAuthorId(),
                author.getAuthorId());
        author = (Author) result.get(2);
        assertEquals("Expected author with Id "
                + author1.getAuthorId()
                + " at second position but got "
                + author.getAuthorId(),
                author1.getAuthorId(),
                author.getAuthorId());
    }

    /**
     * Tests whether we can execute subselects using an equals comparison with
     * integer values, with the subselects as left hand side of the comparison.
     *
     * @throws Exception if the test fails
     */
    public void testSubselectAsLvalue() throws Exception
    {
        if (!supportsSubselects())
        {
            return;
        }

        Criteria subquery = new Criteria();
        subquery.addSelectColumn(AuthorPeer.AUTHOR_ID);
        subquery.where(author1.getName(), AuthorPeer.NAME);
        Criteria criteria = new Criteria();
        criteria.where(subquery, AuthorPeer.AUTHOR_ID);
        criteria.addDescendingOrderByColumn(AuthorPeer.AUTHOR_ID);

        List<?> result = AuthorPeer.doSelect(criteria);
        assertEquals("Expected result of size 1 but got " + result.size(),
                1,
                result.size());
        Author author = (Author) result.get(0);
        assertEquals("Expected author with Id "
                + author1.getAuthorId()
                + " at first position but got "
                + author.getAuthorId(),
                author1.getAuthorId(),
                author.getAuthorId());
    }

    /**
     * Tests whether we can execute subselects using an equals clause.
     *
     * @throws Exception if the test fails
     */
    public void testSubselectUsingEquals() throws Exception
    {
        if (!supportsSubselects())
        {
            return;
        }

        Criteria subquery = new Criteria();
        subquery.addSelectColumn(AuthorPeer.AUTHOR_ID);
        subquery.where(AuthorPeer.NAME, author1.getName());
        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.AUTHOR_ID, subquery);

        List<Author> result = AuthorPeer.doSelect(criteria);
        assertEquals("Expected result of size 1 but got " + result.size(),
                result.size(),
                1);
        Author author = result.get(0);
        assertEquals("Expected author with Id "
                + author1.getAuthorId()
                + " but got "
                + author.getAuthorId(),
                author1.getAuthorId(),
                author.getAuthorId());
    }

    /**
     * Tests whether we can execute subqueries using in with Strings.
     *
     * @throws Exception if the test fails
     */
    public void testSubselectUsingInWithStrings() throws Exception
    {
        if (!supportsSubselects())
        {
            return;
        }

        Criteria subquery = new Criteria();
        subquery.addSelectColumn(AuthorPeer.AUTHOR_ID);
        List<String> nameList = new ArrayList<String>();
        nameList.add(author1.getName());
        nameList.add(author2.getName());
        subquery.where(AuthorPeer.NAME, nameList, Criteria.IN);
        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.AUTHOR_ID, subquery, Criteria.IN);
        criteria.addAscendingOrderByColumn(AuthorPeer.AUTHOR_ID);

        List<Author> result = AuthorPeer.doSelect(criteria);
        assertEquals("Expected result of size 2 but got " + result.size(),
                result.size(),
                3);
        Author author = result.get(0);
        assertEquals("Expected author with Id "
                + author1.getAuthorId()
                + " but got "
                + author.getAuthorId(),
                author1.getAuthorId(),
                author.getAuthorId());
        author = result.get(1);
        assertEquals("Expected author with Id "
                + author2.getAuthorId()
                + " but got "
                + author.getAuthorId(),
                author2.getAuthorId(),
                author.getAuthorId());
        author = result.get(2);
        assertEquals("Expected author with Id "
                + author2b.getAuthorId()
                + " but got "
                + author.getAuthorId(),
                author2b.getAuthorId(),
                author.getAuthorId());
    }

    /**
     * Tests whether we can execute subselects which reference the outer select
     * in the subselect.
     *
     * @throws Exception if the test fails
     */
    public void testSubselectReferencingOuterSelect() throws Exception
    {
        if (!supportsSubselects())
        {
            return;
        }

        Criteria subquery = new Criteria();
        subquery.addSelectColumn(new ColumnImpl("count(*)"));
        subquery.where(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
        subquery.and(BookPeer.TITLE, book3.getTitle());
        subquery.addFrom(BookPeer.TABLE_NAME);
        Criteria criteria = new Criteria();
        criteria.where(subquery, 1);

        List<?> result = AuthorPeer.doSelect(criteria);
        assertEquals("Expected result of size 1 but got " + result.size(),
                1,
                result.size());
        Author author = (Author) result.get(0);
        assertEquals("Expected author with Id "
                + author3.getAuthorId()
                + " but got "
                + author.getAuthorId(),
                author3.getAuthorId(),
                author.getAuthorId());
    }

    /**
     * Returns whether the database supports subselects.
     * If not a warning is written to the logs.
     *
     * @return true if the database supports subselects, false otherwise.
     *
     * @throws TorqueException If an error occurs.
     */
    private boolean supportsSubselects() throws TorqueException
    {
        if (!(defaultAdapter instanceof MysqlAdapter))
        {
            return true;
        }
        int majorVersion = getMysqlMajorVersion();
        int minorVersion = getMysqlMinorVersion();
        if (majorVersion < 4 || (majorVersion == 4 && minorVersion == 0))
        {
            log.warn("supportsSubselects(): "
                    + "Subselects are not supported by Mysql < 4.1");
            return false;
        }
        return true;
    }
}
