package org.apache.torque;

/*
 * 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.sql.Connection;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.adapter.Adapter;
import org.apache.torque.adapter.DerbyAdapter;
import org.apache.torque.adapter.MssqlAdapter;
import org.apache.torque.adapter.MysqlAdapter;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.criteria.CriteriaInterface;
import org.apache.torque.criteria.Criterion;
import org.apache.torque.om.mapper.CompositeMapper;
import org.apache.torque.om.mapper.IntegerMapper;
import org.apache.torque.om.mapper.RecordMapper;
import org.apache.torque.test.InheritanceClassnameTestChild1;
import org.apache.torque.test.InheritanceClassnameTestChild2;
import org.apache.torque.test.dbobject.Author;
import org.apache.torque.test.dbobject.BigintType;
import org.apache.torque.test.dbobject.Book;
import org.apache.torque.test.dbobject.CompPkContainsFk;
import org.apache.torque.test.dbobject.IfcTable;
import org.apache.torque.test.dbobject.InheritanceChildB;
import org.apache.torque.test.dbobject.InheritanceChildC;
import org.apache.torque.test.dbobject.InheritanceChildD;
import org.apache.torque.test.dbobject.InheritanceClassnameTest;
import org.apache.torque.test.dbobject.InheritanceTest;
import org.apache.torque.test.dbobject.IntegerType;
import org.apache.torque.test.dbobject.LocalIfcTable;
import org.apache.torque.test.dbobject.LocalTestInterface;
import org.apache.torque.test.dbobject.MultiPk;
import org.apache.torque.test.dbobject.Nopk;
import org.apache.torque.test.dbobject.OIntegerPk;
import org.apache.torque.test.dbobject.VarcharType;
import org.apache.torque.test.peer.AuthorPeer;
import org.apache.torque.test.peer.BigintTypePeer;
import org.apache.torque.test.peer.BookPeer;
import org.apache.torque.test.peer.CompPkContainsFkPeer;
import org.apache.torque.test.peer.IfcTablePeer;
import org.apache.torque.test.peer.InheritanceClassnameTestPeer;
import org.apache.torque.test.peer.InheritanceTestPeer;
import org.apache.torque.test.peer.IntegerTypePeer;
import org.apache.torque.test.peer.LocalIfcTablePeer;
import org.apache.torque.test.peer.MultiPkPeer;
import org.apache.torque.test.peer.NopkPeer;
import org.apache.torque.test.peer.VarcharTypePeer;
import org.apache.torque.test.recordmapper.AuthorRecordMapper;
import org.apache.torque.test.recordmapper.BookRecordMapper;
import org.apache.torque.util.BasePeer;
import org.apache.torque.util.CountHelper;
import org.apache.torque.util.Transaction;

/**
 * Runtime tests.
 *
 * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
 * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
 * @author <a href="mailto:patrick.carl@web.de">Patrick Carl</a>
 * @author <a href="mailto:tv@apache.org">Thomas Vandahl</a>
 * @version $Id: DataTest.java 1439631 2013-01-28 21:03:33Z tfischer $
 */
public class DataTest extends BaseDatabaseTestCase
{
    private static Log log = LogFactory.getLog(DataTest.class);

    /**
     * test whether we can connect to the database at all
     * @throws Exception if no connection can be established
     */
    public void testConnect() throws Exception
    {
        Connection connection = null;
        try
        {
            connection = Torque.getConnection();
            connection.close();
            connection = null;
        }
        finally
        {
            if (connection != null)
            {
                connection.close();
            }
        }
    }

    /**
     * multiple pk test (TRQ12)
     * @throws Exception if the test fails
     */
    public void testMultiplePk() throws Exception
    {
        // clean table
        Criteria criteria = new Criteria();
        criteria.where(MultiPkPeer.PK1, (Object) null, Criteria.NOT_EQUAL);
        MultiPkPeer.doDelete(criteria);

        // do test
        MultiPk mpk = new MultiPk();
        mpk.setPrimaryKey("Svarchar:N5:Schar:N3:N-42:N3:N4:N5:N6:D9999999999:");
        mpk.save();
        // TODO assert saved values
    }

    private static final String[] validTitles = {
        "Book 6 - Author 4", "Book 6 - Author 5", "Book 6 - Author 6",
        "Book 6 - Author 7", "Book 6 - Author 8",
        "Book 7 - Author 4", "Book 7 - Author 5", "Book 7 - Author 6",
        "Book 7 - Author 7", "Book 7 - Author 8"
        };

    /**
     * test limit/offset
     *
     * @throws Exception if the test fails
     */
    public void testLimitOffset() throws Exception
    {
        cleanBookstore();
        insertBookstoreData();
        Set<String> titleSet = new HashSet<String>();
        for (int j = 0; j < validTitles.length; j++)
        {
            titleSet.add(validTitles[j]);
        }

        Criteria crit = new Criteria();
        Criterion c = new Criterion(BookPeer.TITLE,
                "Book 6 - Author 1", Criteria.GREATER_EQUAL);
        c.and(new Criterion(BookPeer.TITLE,
                "Book 8 - Author 3", Criteria.LESS_EQUAL));
        crit.where(c);
        crit.addDescendingOrderByColumn(BookPeer.BOOK_ID);
        crit.setLimit(10);
        crit.setOffset(5);
        List<Book> books = BookPeer.doSelect(crit);
        assertTrue("List should have 10 books, not " + books.size(),
                books.size() == 10);
        for (Book book : books)
        {
            String title = book.getTitle();
            assertTrue("Incorrect title: " + title,
                    titleSet.contains(title));
        }

        // Test limit of zero works
        crit = new Criteria();
        crit.setLimit(0);
        try
        {
            books = BookPeer.doSelect(crit);
            assertTrue("List should have 0 books, not " + books.size(),
                    books.size() == 0);
        }
        catch (TorqueException e)
        {
            if (defaultAdapter.supportsNativeLimit())
            {
                if (!(defaultAdapter instanceof DerbyAdapter))
                {
                    throw e;
                }
                else
                {
                    log.error("testLimitOffset(): "
                            + "A limit of 0 is not supported for Derby");
                }
            }
            else
            {
                log.error("testLimitOffset(): "
                        + "A limit of 0 is not supported for Databases "
                        + "without native limit support");
            }
        }

        // check that Offset also works without limit
        crit = new Criteria();
        crit.setOffset(5);
        books = BookPeer.doSelect(crit);
        assertTrue("List should have 95 books, not " + books.size(),
                books.size() == 95);

        // Check that limiting also works if a table with an equal column name
        // is joined. This is problematic for oracle, see TORQUE-10.

        crit = new Criteria();
        crit.setLimit(10);
        crit.setOffset(5);
        books = BookPeer.doSelectJoinAuthor(crit);
        assertTrue("List should have 10 books, not " + books.size(),
                books.size() == 10);
    }

    /**
     * Checks whether the setSingleRecord() method in criteria works
     */
    public void testSingleRecord() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.setSingleRecord(true);
        criteria.setLimit(1);
        criteria.setOffset(5);
        List<Book> books = BookPeer.doSelect(criteria);
        assertTrue("List should have 1 books, not " + books.size(),
                books.size() == 1);

        criteria = new Criteria();
        criteria.setSingleRecord(true);
        criteria.setLimit(2);
        try
        {
            books = BookPeer.doSelect(criteria);
            fail("doSelect should have failed "
                    + "because two records were selected "
                    + " and one was expected");
        }
        catch (TorqueException e)
        {
        }
    }

    /**
     * Tests whether selects work correctly if the value <code>null</code>
     * is used.
     * @throws Exception if the test fails
     */
    public void testNullSelects() throws Exception
    {
        // clean table
        VarcharTypePeer.doDelete(new Criteria());
        IntegerTypePeer.doDelete(new Criteria());

        // add test data
        VarcharType varcharType = new VarcharType();
        varcharType.setId("text2");
        varcharType.setVarcharValue("text2");
        varcharType.save();
        varcharType = new VarcharType();
        varcharType.setId("text");
        varcharType.save();

        IntegerType integerTypeNotNull = new IntegerType();
        integerTypeNotNull.setIntegerObjectValue(1);
        integerTypeNotNull.save();
        IntegerType integerTypeNull = new IntegerType();
        integerTypeNull.save();

        // check for comparison NOT_EQUAL and value null
        Criteria criteria = new Criteria();
        criteria.where(VarcharTypePeer.ID, null, Criteria.NOT_EQUAL)
            .and(VarcharTypePeer.VARCHAR_VALUE, null, Criteria.NOT_EQUAL);
        List<VarcharType> varcharResult = VarcharTypePeer.doSelect(criteria);
        assertEquals(1, varcharResult.size());
        assertEquals("text2", varcharResult.get(0).getId());

        criteria = new Criteria();
        criteria.where(IntegerTypePeer.ID, null, Criteria.NOT_EQUAL)
            .and(IntegerTypePeer.INTEGER_OBJECT_VALUE, null, Criteria.NOT_EQUAL);
        List<IntegerType> integerResult = IntegerTypePeer.doSelect(criteria);
        assertEquals(1, integerResult.size());
        assertEquals(integerTypeNotNull.getId(), integerResult.get(0).getId());

        // check for comparison EQUAL and value null
        criteria = new Criteria();
        criteria.where(VarcharTypePeer.VARCHAR_VALUE, null, Criteria.EQUAL);
        varcharResult = VarcharTypePeer.doSelect(criteria);
        assertEquals(1, varcharResult.size());
        assertEquals("text", varcharResult.get(0).getId());

        criteria = new Criteria();
        criteria.where(IntegerTypePeer.INTEGER_OBJECT_VALUE, null, Criteria.EQUAL);
        integerResult = IntegerTypePeer.doSelect(criteria);
        assertEquals(1, integerResult.size());
        assertEquals(integerTypeNull.getId(), integerResult.get(0).getId());
    }

    /**
     * Test whether an update works and whether it only affects the
     * specified record.
     * @throws Exception if anything in the test goes wrong.
     */
    public void testUpdate() throws Exception
    {
        cleanBookstore();

        Author otherAuthor = new Author();
        otherAuthor.setName("OtherName");
        otherAuthor.save();

        Author author = new Author();
        author.setName("Name");
        author.save();


        // Test doUpdate methods in Peer explicitly
        Connection connection = Transaction.begin(AuthorPeer.DATABASE_NAME);
        author.setName("NewName2");
        AuthorPeer.doUpdate(author);
        Transaction.commit(connection);

        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);

        List<Author> authors = AuthorPeer.doSelect(criteria);
        assertEquals("List should contain 2 authors", 2, authors.size());
        assertEquals("First Author's name should be \"NewName2\"",
                "NewName2",
                authors.get(0).getName());
        assertEquals("Second Author's name should be \"OtherName\"",
                "OtherName",
                authors.get(1).getName());

        author.setName("NewName3");
        AuthorPeer.doUpdate(author);

        criteria = new Criteria();
        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);

        authors = AuthorPeer.doSelect(criteria);
        assertEquals("List should contain 2 authors", 2, authors.size());
        assertEquals("First Author's name should be \"NewName3\"",
                "NewName3",
                authors.get(0).getName());
        assertEquals("Second Author's name should be \"OtherName\"",
                "OtherName",
                authors.get(1).getName());

        Nopk nopk = new Nopk();
        nopk.setName("name");
        nopk.save();

        // check the doPupdate Peer methods throw exceptions on a modified
        // object without primary keys
        try
        {
            NopkPeer.doUpdate(new Nopk());
            fail("A Torque exception should be thrown (2)");
        }
        catch (TorqueException e)
        {
        }

        connection = Transaction.begin(NopkPeer.DATABASE_NAME);
        try
        {
            NopkPeer.doUpdate(new Nopk(),connection);
            fail("A Torque exception should be thrown (3)");
        }
        catch (TorqueException e)
        {
        }
        Transaction.safeRollback(connection);

    }

    /**
     * test special cases in the select clause
     * @throws Exception if the test fails
     */
    public void testSelectClause() throws Exception
    {
        // test double functions in select columns
        Criteria criteria = new Criteria();
        criteria.addSelectColumn(
                new ColumnImpl("count(distinct(" + BookPeer.BOOK_ID + "))"));
        BasePeer.doSelect(criteria, new IntegerMapper());

        // test qualifiers in function in select columns
        criteria = new Criteria();
        criteria.addSelectColumn(
                new ColumnImpl("count(distinct " + BookPeer.BOOK_ID + ")"));
        BasePeer.doSelect(criteria, new IntegerMapper());
    }

    /**
     * test if a select from the "default" database works
     * @throws Exception (NPE) if the test fails
     */
    public void testSelectFromDefault() throws Exception
    {
        Criteria criteria = new Criteria("default");

        criteria.addSelectColumn(BookPeer.BOOK_ID);

        BasePeer.doSelect(criteria, new IntegerMapper());
    }

    /**
     * Test the behaviour if a connection is supplied to access the database,
     * but it is null. All methods on the user level should be fail
     * because these methods will be only needed if a method should be executed
     * in a transaction context. If one assumes that a transaction is open
     * (connection is not null), but it is not (connection == null),
     * it is a bad idea to silently start one as this behaviour is very
     * difficult to tell from the correct one. A clean failure is much easier
     * to test for.
     */
    public void testNullConnection() throws Exception
    {
        try
        {
            Criteria criteria = new Criteria();
            AuthorPeer.doSelect(criteria, new IntegerMapper(), null);
            fail("NullPointerException expected");
        }
        catch (NullPointerException e)
        {
            //expected
        }

        try
        {
            Criteria criteria = new Criteria();
            criteria.where(BookPeer.BOOK_ID, (Long) null, Criteria.NOT_EQUAL);
            BookPeer.doDelete(criteria, (Connection) null);
            fail("NullPointerException expected");
        }
        catch (NullPointerException e)
        {
            //expected
        }

        try
        {
            Author author = new Author();
            author.setName("name");
            author.save((Connection) null);
            fail("TorqueException expected");
        }
        catch (TorqueException e)
        {
            //expected
            assertEquals("connection is null", e.getMessage());
        }
    }

    /**
     * test the order by, especially in joins and with aliases
     * @throws Exception if the test fails
     */
    public void testOrderBy() throws Exception
    {
        cleanBookstore();

        // insert test data
        Author firstAuthor = new Author();
        firstAuthor.setName("Author 1");
        firstAuthor.save();
        Book book = new Book();
        book.setAuthor(firstAuthor);
        book.setTitle("Book 1");
        book.setIsbn("unknown");
        book.save();

        Author secondAuthor = new Author();
        secondAuthor.setName("Author 2");
        secondAuthor.save();
        for (int bookNr = 2; bookNr <=4; bookNr++)
        {
            book = new Book();
            book.setAuthor(secondAuthor);
            book.setTitle("Book " + bookNr);
            book.setIsbn("unknown");
            book.save();
        }

        // test simple ascending order by
        Criteria criteria = new Criteria();
        criteria.addAscendingOrderByColumn(BookPeer.TITLE);
        List<Book> bookList = BookPeer.doSelect(criteria);
        if (bookList.size() != 4)
        {
            fail("Ascending Order By: "
                     + "incorrect numbers of books found : "
                     + bookList.size()
                     + ", should be 4");
        }
        if (! "Book 1".equals(bookList.get(0).getTitle()))
        {
            fail("Ascending Order By: "
                     + "Title of first Book is "
                     + bookList.get(0).getTitle()
                     + ", should be \"Book 1\"");
        }
        if (! "Book 4".equals(bookList.get(3).getTitle()))
        {
            fail("Ascending Order By: "
                     + "Title of fourth Book is "
                     + bookList.get(3).getTitle()
                     + ", should be \"Book 4\"");
        }

        // test simple descending order by
        criteria = new Criteria();
        criteria.addDescendingOrderByColumn(BookPeer.TITLE);
        bookList = BookPeer.doSelect(criteria);
        if (bookList.size() != 4)
        {
            fail("Descending Order By: "
                     + "incorrect numbers of books found : "
                     + bookList.size()
                     + ", should be 4");
        }
        if (! "Book 1".equals(bookList.get(3).getTitle()))
        {
            fail("Descending Order By: "
                     + "Title of fourth Book is "
                     + bookList.get(3).getTitle()
                     + ", should be \"Book 1\"");
        }
        if (! "Book 4".equals((bookList.get(0)).getTitle()))
        {
            fail("Descending Order By: "
                     + "Title of first Book is "
                     + bookList.get(0).getTitle()
                     + ", should be \"Book 4\"");
        }

        criteria = new Criteria();
        criteria.addAlias("b", BookPeer.TABLE_NAME);
        criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
        criteria.addJoin(
                AuthorPeer.AUTHOR_ID,
                new ColumnImpl("b." + BookPeer.AUTHOR_ID.getColumnName()));
        criteria.addAscendingOrderByColumn(
                new ColumnImpl("b." + BookPeer.TITLE.getColumnName()));
        criteria.addDescendingOrderByColumn(BookPeer.TITLE);
        // the retrieved columns are
        // author    book   b
        // author1  book1   book1
        // author2  book4   book2
        // author2  book3   book2
        // author2  book2   book2
        // author2  book4   book3
        // ...
        bookList = BookPeer.doSelect(criteria);
        if (bookList.size() != 10)
        {
            fail("ordering by Aliases: "
                     + "incorrect numbers of books found : "
                     + bookList.size()
                     + ", should be 10");
        }
        if (!"Book 4".equals(bookList.get(1).getTitle()))
        {
            fail("ordering by Aliases: "
                     + "Title of second Book is "
                     + bookList.get(1).getTitle()
                     + ", should be \"Book 4\"");
        }
        if (!"Book 3".equals(bookList.get(2).getTitle()))
        {
            fail("ordering by Aliases: "
                     + "Title of third Book is "
                     + bookList.get(2).getTitle()
                     + ", should be \"Book 3\"");
        }

        criteria = new Criteria();
        criteria.addAlias("b", BookPeer.TABLE_NAME);
        criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
        criteria.addJoin(
                AuthorPeer.AUTHOR_ID,
                new ColumnImpl("b." + BookPeer.AUTHOR_ID.getColumnName()));
        criteria.addAscendingOrderByColumn(BookPeer.TITLE);
        criteria.addDescendingOrderByColumn(
                new ColumnImpl("b." + BookPeer.TITLE.getColumnName()));
        // the retrieved columns are
        // author    book   b
        // author1  book1   book1
        // author2  book2   book4
        // author2  book2   book3
        // author2  book2   book2
        // author2  book3   book4
        // ...
        bookList = BookPeer.doSelect(criteria);
        if (bookList.size() != 10)
        {
            fail("ordering by Aliases (2): "
                     + "incorrect numbers of books found : "
                     + bookList.size()
                     + ", should be 10");
        }
        if (!"Book 2".equals(bookList.get(1).getTitle()))
        {
            fail("ordering by Aliases (2, PS): "
                     + "Title of second Book is "
                     + bookList.get(1).getTitle()
                     + ", should be \"Book 2\"");
        }
        if (!"Book 2".equals(bookList.get(2).getTitle()))
        {
            fail("ordering by Aliases (2, PS): "
                     + "Title of third Book is "
                     + bookList.get(2).getTitle()
                     + ", should be \"Book 2\"");
        }

        // test usage of Expressions in order by
        criteria = new Criteria();
        criteria.addAscendingOrderByColumn(
                new ColumnImpl("UPPER(" + BookPeer.TITLE + ")"));
        criteria.setIgnoreCase(true);
        BookPeer.doSelect(criteria);
    }


    /**
     * Tests whether ignoreCase works correctly
     * @throws Exception if the test fails
     */
    public void testIgnoreCase() throws Exception
    {
        cleanBookstore();

        // check ignore case in selects
        Author author = new Author();
        author.setName("AuTHor");
        author.save();

        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.NAME, author.getName().toLowerCase());
        criteria.setIgnoreCase(true);
        List<Author> result = AuthorPeer.doSelect(criteria);
        assertTrue("Size of result is not 1, but " + result.size(),
                result.size() == 1);

        // LIKE treatment might be different (e.g. postgres), so check extra
        criteria = new Criteria();
        criteria.where(
                AuthorPeer.NAME,
                author.getName().toLowerCase().replace('r', '%'),
                Criteria.LIKE);
        criteria.setIgnoreCase(true);
        result = AuthorPeer.doSelect(criteria);
        assertTrue("Size of result is not 1, but " + result.size(),
                result.size() == 1);

        // Test ignore case in criterion
        criteria = new Criteria();
        Criterion criterion1 = new Criterion(
                AuthorPeer.NAME,
                author.getName().toLowerCase(),
                Criteria.EQUAL);
        criterion1.setIgnoreCase(true);
        Criterion criterion2 = new Criterion(
                AuthorPeer.AUTHOR_ID, null, Criteria.NOT_EQUAL);
        criterion1.and(criterion2);

        result = AuthorPeer.doSelect(criteria);

        // ignore case should not be set either in Criteria
        // nor in other criterions
        assertFalse(criteria.isIgnoreCase());
        assertFalse(criterion2.isIgnoreCase());
        assertTrue("Size of result is not 1, but " + result.size(),
                result.size() == 1);


        // Test ignore case in attached criterion
        criteria = new Criteria();
        criterion1 = new Criterion(
                AuthorPeer.AUTHOR_ID, null, Criteria.NOT_EQUAL);
        criterion2 = new Criterion(
                AuthorPeer.NAME,
                author.getName().toLowerCase(),
                Criteria.EQUAL);
        criterion2.setIgnoreCase(true);
        criterion1.and(criterion2);

        result = AuthorPeer.doSelect(criteria);

        // ignore case should not be set either in Criteria
        // nor in other criterions
        assertFalse(criteria.isIgnoreCase());
        assertFalse(criterion1.isIgnoreCase());

        assertTrue("Size of result is not 1, but " + result.size(),
                result.size() == 1);

        // ignore case in "in" query
        {
            criteria = new Criteria();
            Set<String> names = new HashSet<String>();
            names.add(author.getName().toLowerCase());
            criteria.where(AuthorPeer.NAME, names, Criteria.IN);
            criteria.setIgnoreCase(true);

            result = AuthorPeer.doSelect(criteria);
            assertEquals("Expected result of size 1 but got " + result.size(),
                    result.size(),
                    1);
        }

        // Check that case is not ignored if ignoreCase is not set
        // This is known not to work for mysql
        author = new Author();
        author.setName("author");
        author.save();

        Adapter adapter = Torque.getAdapter(Torque.getDefaultDB());
        if (adapter instanceof MysqlAdapter
                || adapter instanceof MssqlAdapter)
        {
            log.error("testIgnoreCase(): "
                    + "Case sensitive comparisons are known not to work"
                    + " with Mysql and MSSQL");
            // failing is "expected", so bypass without error
        }
        else
        {
            criteria = new Criteria();
            criteria.where(AuthorPeer.NAME, author.getName());
            result = AuthorPeer.doSelect(criteria);
            assertTrue("Size of result is not 1, but " + result.size(),
                    result.size() == 1);

            // again check LIKE treatment
            criteria = new Criteria();
            criteria.where(
                    AuthorPeer.NAME,
                    author.getName().replace('r', '%'),
                    Criteria.LIKE);
            result = AuthorPeer.doSelect(criteria);
            assertTrue("Size of result is not 1, but " + result.size(),
                    result.size() == 1);

            // Test different ignore cases in criterions
            criteria = new Criteria();
            criterion1 = new Criterion(
                    AuthorPeer.NAME,
                    author.getName().toLowerCase(),
                    Criteria.NOT_EQUAL);
            criterion2 = new Criterion(
                    AuthorPeer.NAME,
                    author.getName().toLowerCase(),
                    Criteria.EQUAL);
            criterion2.setIgnoreCase(true);
            criterion1.and(criterion2);
            criteria.where(criterion1);

            result = AuthorPeer.doSelect(criteria);
            assertTrue("Size of result is not 1, but " + result.size(),
                    result.size() == 1);

            // ignore case in "in" query
            {
                criteria = new Criteria();
                Set<String> names = new HashSet<String>();
                names.add(author.getName());
                criteria.where(AuthorPeer.NAME, names, Criteria.IN);

                result = AuthorPeer.doSelect(criteria);
                assertEquals("Expected result of size 1 but got " + result.size(),
                        result.size(),
                        1);
            }
        }

        cleanBookstore();
        author = new Author();
        author.setName("AA");
        author.save();
        author = new Author();
        author.setName("BB");
        author.save();
        author = new Author();
        author.setName("ba");
        author.save();
        author = new Author();
        author.setName("ab");
        author.save();

        // check ignoreCase in Criteria
        criteria = new Criteria();
        criteria.setIgnoreCase(true);
        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
        result = AuthorPeer.doSelect(criteria);
        assertTrue("Size of result is not 4, but " + result.size(),
                result.size() == 4);
        assertEquals(result.get(0).getName(), "AA");
        assertEquals(result.get(1).getName(), "ab");
        assertEquals(result.get(2).getName(), "ba");
        assertEquals(result.get(3).getName(), "BB");

        // check ignoreCase in orderBy
        criteria = new Criteria();
        criteria.addAscendingOrderByColumn(AuthorPeer.NAME, true);
        result = AuthorPeer.doSelect(criteria);
        assertTrue("Size of result is not 4, but " + result.size(),
                result.size() == 4);
        assertEquals(result.get(0).getName(), "AA");
        assertEquals(result.get(1).getName(), "ab");
        assertEquals(result.get(2).getName(), "ba");
        assertEquals(result.get(3).getName(), "BB");
    }

    /**
     * tests whether AsColumns produce valid SQL code
     * @throws Exception if the test fails
     */
    public void testAsColumn() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.addAsColumn("ALIASNAME", AuthorPeer.NAME);
        // we need an additional column to select from,
        // to indicate the table we want use
        criteria.addSelectColumn(AuthorPeer.AUTHOR_ID);
        BasePeer.doSelect(criteria, new DoNothingMapper());
    }

    /**
     * Test whether same column name in different tables
     * are handled correctly
     * @throws Exception if the test fails
     */
    public void testSameColumnName() throws Exception
    {
        cleanBookstore();
        Author author = new Author();
        author.setName("Name");
        author.save();

        author = new Author();
        author.setName("NotCorrespondingName");
        author.save();

        Book book = new Book();
        book.setTitle("Name");
        book.setAuthor(author);
        book.setIsbn("unknown");
        book.save();

        Criteria criteria = new Criteria();
        criteria.addJoin(BookPeer.TITLE, AuthorPeer.NAME);
        BookPeer.addSelectColumns(criteria);
        AuthorPeer.addSelectColumns(criteria);
        // basically a BaseBookPeer.setDbName(criteria);
        // and BasePeer.doSelect(criteria);
        CompositeMapper mapper = new CompositeMapper();
        mapper.addMapper(new BookRecordMapper(), 0);
        mapper.addMapper(
                new AuthorRecordMapper(),
                BookPeer.numColumns);

        List<List<Object>> queryResult
                = BookPeer.doSelect(criteria, mapper);
        List<Object> mappedRow = queryResult.get(0);
        book = (Book) mappedRow.get(0);
        author = (Author) mappedRow.get(1);

        if (book.getAuthorId() == author.getAuthorId())
        {
            fail("wrong Ids read");
        }
    }

    /**
     * tests whether large primary keys are inserted and read correctly
     * @throws Exception if the test fails
     */
    public void testLargePk() throws Exception
    {
        if (defaultAdapter instanceof MssqlAdapter) {
            log.error("testLargePk(): "
                    + "MSSQL does not support inserting defined PK values");
                return;
        }
        BigintTypePeer.doDelete(new Criteria());

        long longId = 8771507845873286l;
        BigintType bigintType = new BigintType();
        bigintType.setId(longId);
        bigintType.save();

        List<BigintType> bigintTypeList = BigintTypePeer.doSelect(new Criteria());
        BigintType readBigintType = bigintTypeList.get(0);
        assertEquals(bigintType.getId(), readBigintType.getId());
        assertEquals(longId, readBigintType.getId());
    }

    /**
     * tests whether large bigint values are inserted and read correctly
     * @throws Exception if the test fails
     */
    public void testLargeValue() throws Exception
    {
        BigintTypePeer.doDelete(new Criteria());

        long longValue = 8771507845873286l;
        BigintType bigintType = new BigintType();
        bigintType.setBigintValue(longValue);
        bigintType.save();

        List<BigintType> bigintTypeList = BigintTypePeer.doSelect(new Criteria());
        BigintType readBigintType = bigintTypeList.get(0);
        assertEquals(bigintType.getId(), readBigintType.getId());
        assertEquals(longValue, readBigintType.getBigintValue());
    }

    /**
     * Tests the CountHelper class
     * @throws Exception if the test fails
     */
    public void testCountHelper() throws Exception
    {
        cleanBookstore();
        Author author = new Author();
        author.setName("Name");
        author.save();

        author = new Author();
        author.setName("Name2");
        author.save();

        author = new Author();
        author.setName("Name");
        author.save();

        Criteria criteria = new Criteria();
        int count = new CountHelper().count(
                criteria,
                null,
                AuthorPeer.AUTHOR_ID);

        if (count != 3) {
            fail("counted " + count + " datasets, should be 3 ");
        }

        criteria = new Criteria();
        criteria.setDistinct();
        count = new CountHelper().count(criteria, null, AuthorPeer.NAME);

        if (count != 2) {
            fail("counted " + count + " distinct datasets, should be 2 ");
        }

        criteria = new Criteria();
        criteria.where(AuthorPeer.NAME, "Name2");
        count = new CountHelper().count(criteria);

        if (count != 1) {
            fail("counted " + count + " datasets with name Name2,"
                 + " should be 1 ");
        }
    }


    /**
     * Tests whether we can handle multiple primary keys some of which are
     * also foreign keys
     * @throws Exception if the test fails
     */
    public void testMultiplePrimaryForeignKey() throws Exception
    {
        ForeignKeySchemaData.clearTablesInDatabase();

        OIntegerPk oIntegerPk = new OIntegerPk();
        oIntegerPk.save();
        CompPkContainsFk compPkContainsFk = new CompPkContainsFk();
        compPkContainsFk.setId1(oIntegerPk.getId());
        compPkContainsFk.setId2("test");
        compPkContainsFk.save();

        List<CompPkContainsFk> selectedList
                = CompPkContainsFkPeer.doSelect(new Criteria());
        assertEquals(1, selectedList.size());
        CompPkContainsFk selected = selectedList.get(0);
        assertEquals(oIntegerPk.getId(), selected.getId1());
        assertEquals("test", selected.getId2());
    }

    /**
     * Tests inserting single quotes in Strings.
     * This may not crash now, but in a later task like datasql,
     * so the data has to be inserted in a table which does not get cleaned
     * during the runtime test.
     * @throws Exception if inserting the test data fails
     */
    public void testSingleQuotes() throws Exception
    {
        cleanBookstore();

        Author author = new Author();
        author.setName("has Single ' Quote");
        author.save();
    }

    /**
     * Test whether equals() is working correctly
     * @throws Exception
     */
    public void testEquals() throws Exception
    {
        Author author = new Author();
        author.setAuthorId(1000);

        Book book = new Book();
        book.setBookId(1000);

        Book bookNotEqual = new Book();
        bookNotEqual.setBookId(2000);

        Book bookEqual = new Book();
        bookEqual.setBookId(1000);

        assertFalse("Author and Book should not be equal",
                author.equals(book));
        assertTrue("Book compared with itself should be equal",
                book.equals(book));
        assertTrue("Book compared with book with same id should be equal",
                book.equals(bookEqual));
        assertFalse("Book compared with book with different id "
                + "should not be equal",
                book.equals(bookNotEqual));
    }

    /**
     * Tests whether a table implementing an interface actually
     * returns an instance of this interface
     * @throws Exception if the test fails
     */
    public void testInterface() throws Exception
    {
        Criteria criteria = new Criteria();
        criteria.where(IfcTablePeer.ID, -1, Criteria.NOT_EQUAL);
        IfcTablePeer.doDelete(criteria);

        IfcTable ifc = new IfcTable();

        assertTrue("IfcTable should be an instance of TestInterface", ifc instanceof TestInterface);

        ifc.setID(1);
        ifc.setName("John Doe");
        ifc.save();

        List<IfcTable> results = IfcTablePeer.doSelect(new Criteria());

        for (IfcTable ifcTable : results)
        {
            assertTrue("IfcTablePeer.doSelect should return"
                    + " instances of TestInterface",
                ifcTable instanceof TestInterface);
        }

        LocalIfcTable localIfc = new LocalIfcTable();

        assertTrue("LocalIfcTable should be an instance of LocalTestInterface",
                localIfc instanceof LocalTestInterface);

        List<LocalIfcTable> results2 = LocalIfcTablePeer.doSelect(new Criteria());

        for (LocalIfcTable readLocalIfcTable : results2)
        {
            assertTrue("IfcTable2Peer.doSelect should return"
                    + " instances of LocalTestInterface",
                    readLocalIfcTable instanceof LocalTestInterface);
        }
    }

    public void testInheritanceWithKeys() throws Exception
    {
        // make sure that the InheritanceTest table is empty before the test
        Criteria criteria = new Criteria();
        criteria.where(
                InheritanceTestPeer.INHERITANCE_TEST,
                (Object) null,
                Criteria.ISNOTNULL);
        InheritanceTestPeer.doDelete(criteria);
        criteria = new Criteria();
        criteria.where(
                InheritanceTestPeer.INHERITANCE_TEST,
                (Object) null,
                Criteria.ISNOTNULL);
        assertEquals(0,
                new CountHelper().count(criteria));

        // create & save test data
        InheritanceTest inheritanceTest = new InheritanceTest();
        inheritanceTest.setPayload("payload1");
        inheritanceTest.save();
        InheritanceChildB inheritanceChildB = new InheritanceChildB();
        inheritanceChildB.setPayload("payload 2");
        inheritanceChildB.save();
        InheritanceChildC inheritanceChildC = new InheritanceChildC();
        inheritanceChildC.setPayload("payload 3");
        inheritanceChildC.save();
        InheritanceChildD inheritanceChildD = new InheritanceChildD();
        inheritanceChildD.setPayload("payload 4");
        inheritanceChildD.save();

        // Check that all objects are saved into the InheritanceTest table
        criteria = new Criteria();
        criteria.where(
                InheritanceTestPeer.INHERITANCE_TEST,
                null,
                Criteria.ISNOTNULL);
        assertEquals("InheritanceTestTable should contain 4 rows",
                4,
                new CountHelper().count(criteria));
        criteria = new Criteria();
        criteria.addAscendingOrderByColumn(
                InheritanceTestPeer.INHERITANCE_TEST);

        // Check that the class of the object is retained when loading
        List<InheritanceTest> inheritanceObjects
                = InheritanceTestPeer.doSelect(criteria);
        assertEquals(
                InheritanceTest.class,
                inheritanceObjects.get(0).getClass());
        assertEquals(
                InheritanceChildB.class,
                inheritanceObjects.get(1).getClass());
        assertEquals(
                InheritanceChildC.class,
                inheritanceObjects.get(2).getClass());
        assertEquals(
                InheritanceChildD.class,
                inheritanceObjects.get(3).getClass());
    }

    public void testInheritanceWithClassname() throws Exception
    {
        // make sure that the InheritanceTest table is empty before the test
        Criteria criteria = new Criteria();
        InheritanceClassnameTestPeer.doDelete(criteria);
        criteria = new Criteria();
        criteria.where(
                InheritanceClassnameTestPeer.INHERITANCE_TEST,
                null,
                Criteria.ISNOTNULL);
        assertEquals(0,
                new CountHelper().count(criteria));

        // create & save test data
        InheritanceClassnameTest inheritanceClassnameTest
                = new InheritanceClassnameTest();
        inheritanceClassnameTest.setPayload("0 parent");
        inheritanceClassnameTest.save();
        InheritanceClassnameTestChild1 inheritanceClassnameChild1
                = new InheritanceClassnameTestChild1();
        inheritanceClassnameChild1.setPayload("1 child");
        inheritanceClassnameChild1.save();
        InheritanceClassnameTestChild2 inheritanceClassnameChild2
                = new InheritanceClassnameTestChild2();
        inheritanceClassnameChild2.setPayload("2 child");
        inheritanceClassnameChild2.save();

        // Check that all objects are saved into the InheritanceTest table
        criteria = new Criteria();
        criteria.where(
                InheritanceClassnameTestPeer.INHERITANCE_TEST,
                null,
                Criteria.ISNOTNULL);
        assertEquals("InheritanceClassnameTest table should contain 3 rows",
                3,
                new CountHelper().count(criteria));
        criteria = new Criteria();
        criteria.addAscendingOrderByColumn(
                InheritanceClassnameTestPeer.PAYLOAD);

        // Check that the class of the object is retained when loading
        List<InheritanceClassnameTest> inheritanceObjects
                = InheritanceClassnameTestPeer.doSelect(criteria);
        assertEquals(
                InheritanceClassnameTest.class,
                inheritanceObjects.get(0).getClass());
        assertEquals("0 parent", inheritanceObjects.get(0).getPayload());
        assertEquals(
                InheritanceClassnameTestChild1.class,
                inheritanceObjects.get(1).getClass());
        assertEquals("1 child", inheritanceObjects.get(1).getPayload());
        assertEquals(
                InheritanceClassnameTestChild2.class,
                inheritanceObjects.get(2).getClass());
        assertEquals("2 child", inheritanceObjects.get(2).getPayload());
    }

    /**
     * Checks whether selects with unqualified column names work.
     *
     * @throws Exception if a problem occurs.
     */
    public void testUnqualifiedColumnNames() throws Exception
    {
        cleanBookstore();
        Author author = new Author();
        author.setName("Joshua Bloch");
        author.save();

        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.AUTHOR_ID, (Object) null, Criteria.NOT_EQUAL);
        criteria.and(new ColumnImpl("name"), "Joshua Bloch", Criteria.EQUAL);
        List<Author> authors = AuthorPeer.doSelect(criteria);
        assertEquals(1, authors.size());
    }

    public void testLikeClauseEscaping() throws Exception
    {
        String[] authorNames
                = {"abc", "bbc", "a_c", "a%c", "a\\c",
                   "a\"c", "a'c", "a?c", "a*c" };

        Map<String, String> likeResults = new HashMap<String, String>();

        likeResults.put("a\\_c", "a_c");
        likeResults.put("a\\_%", "a_c");
        likeResults.put("%\\_c", "a_c");

        likeResults.put("a\\%c", "a%c");
        likeResults.put("a\\%%", "a%c");
        likeResults.put("%\\%c", "a%c");

        likeResults.put("a\\\\c", "a\\c");
        likeResults.put("a\\\\%", "a\\c");
        likeResults.put("%\\\\c", "a\\c");

        likeResults.put("a\\*c", "a*c");
        likeResults.put("a\\*%", "a*c");
        likeResults.put("%\\*c", "a*c");

        likeResults.put("a\\?c", "a?c");
        likeResults.put("a\\?%", "a?c");
        likeResults.put("%\\?c", "a?c");

        likeResults.put("a\"c", "a\"c");
        likeResults.put("a\"%", "a\"c");
        likeResults.put("%\"c", "a\"c");

        likeResults.put("a'c", "a'c");
        likeResults.put("a'%", "a'c");
        likeResults.put("%'c", "a'c");
        cleanBookstore();

        // Save authors
        for (int i = 0; i < authorNames.length; ++i)
        {
            Author author = new Author();
            author.setName(authorNames[i]);
            author.save();
        }

        // Check authors are in the database
        for (int i = 0; i < authorNames.length; ++i)
        {
            Criteria criteria = new Criteria();
            criteria.where(AuthorPeer.NAME, authorNames[i]);
            List<Author> authorList = AuthorPeer.doSelect(criteria);
            assertEquals(
                    "AuthorList should contain one author"
                        + " when querying for " + authorNames[i],
                    1,
                    authorList.size());
            Author author = authorList.get(0);
            assertEquals("Name of author should be " + authorNames[i],
                    authorNames[i],
                    author.getName());
        }

        for (Map.Entry<String, String> likeResult : likeResults.entrySet())
        {
            Criteria criteria = new Criteria();
            criteria.where(
                    AuthorPeer.NAME,
                    likeResult.getKey(),
                    Criteria.LIKE);
            List<Author> authorList;
            try
            {
                authorList = AuthorPeer.doSelect(criteria);
            }
            catch (Exception e)
            {
                throw new Exception(
                        "error rxecuting select using like content "
                        + likeResult.getKey(),
                    e);
            }
            assertEquals(
                    "AuthorList should contain one author"
                        + " when querying for " + likeResult.getKey(),
                    1,
                    authorList.size());
            Author author = authorList.get(0);
            assertEquals("Name of author should be "
                        + likeResult.getValue()
                        + " when querying for "
                        + likeResult.getKey(),
                    likeResult.getValue(),
                    author.getName());
        }

        // check that case insensitivity is maintained if
        // a like is replaced with an equals (no wildcard present)
        // This might be a problem for databases which use ILIKE
        Criteria criteria = new Criteria();
        criteria.where(AuthorPeer.NAME, "AbC", Criteria.LIKE);
        criteria.setIgnoreCase(true);
        List<Author> authorList = AuthorPeer.doSelect(criteria);
        assertEquals(
                "AuthorList should contain one author",
                1,
                authorList.size());
        Author author = authorList.get(0);
        assertEquals("Name of author should be abc",
                "abc",
                author.getName());

        // check that the escape clause (where needed) also works
        // with limit, offset and order by
        criteria = new Criteria();
        Criterion criterion1 = new Criterion(
                AuthorPeer.NAME,
                "b%",
                Criteria.LIKE);
        Criterion criterion2 = new Criterion(
                AuthorPeer.NAME,
                "a\\%%",
                Criteria.LIKE);
        Criterion criterion3 = new Criterion(
                AuthorPeer.NAME,
                "cbc",
                Criteria.LIKE);
        criteria.where(criterion1.or(criterion2).or(criterion3));
        criteria.addAscendingOrderByColumn(AuthorPeer.NAME);
        criteria.setOffset(1);
        criteria.setLimit(1);
        authorList = AuthorPeer.doSelect(criteria);
        assertEquals(
                "AuthorList should contain one author",
                1,
                authorList.size());
        author = authorList.get(0);
        assertEquals("Name of author should be bbc",
                "bbc",
                author.getName());
    }


    /**
     * Strips the schema and table name from a fully qualified colum name
     * This is useful for creating Query with aliases, as the constants
     * for the colum names in the data objects are fully qualified.
     * @param fullyQualifiedColumnName the fully qualified column name, not null
     * @return the column name stripped from the table (and schema) prefixes
     */
    public static String getRawColumnName(String fullyQualifiedColumnName)
    {
        int dotPosition = fullyQualifiedColumnName.lastIndexOf(".");
        if (dotPosition == -1)
        {
            return fullyQualifiedColumnName;
        }
        String result = fullyQualifiedColumnName.substring(
                dotPosition + 1,
                fullyQualifiedColumnName.length());
        return result;
    }

    static class DoNothingMapper implements RecordMapper<Object>
    {

        public Object processRow(
                    ResultSet resultSet,
                    int rowOffset,
                    CriteriaInterface<?> criteria)
                throws TorqueException
        {
            return null;
        }
    }
}
