package org.apache.torque.generated.peer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.GregorianCalendar;
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.Torque;
import org.apache.torque.adapter.DerbyAdapter;
import org.apache.torque.adapter.HsqldbAdapter;
import org.apache.torque.adapter.MssqlAdapter;
import org.apache.torque.adapter.MysqlAdapter;
import org.apache.torque.adapter.OracleAdapter;
import org.apache.torque.adapter.PostgresAdapter;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.test.dbobject.CurrentDateTable;
import org.apache.torque.test.dbobject.CurrentTimeTable;
import org.apache.torque.test.dbobject.CurrentTimestampTable;
import org.apache.torque.test.dbobject.DatabaseDefaultValues;
import org.apache.torque.test.peer.CurrentDateTablePeer;
import org.apache.torque.test.peer.CurrentTimeTablePeer;
import org.apache.torque.test.peer.CurrentTimestampTablePeer;
import org.apache.torque.test.peer.DatabaseDefaultValuesPeer;
import org.apache.torque.util.ColumnValues;
import org.apache.torque.util.JdbcTypedValue;

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

/**
 * Tests whether the useDatabaseDefaultValue attribute works.
 *
 * @version $Id: DefaultValuesFromDatabaseTest.java 1395238 2012-10-07 07:30:25Z tfischer $
 */
public class DefaultValuesFromDatabaseTest extends BaseDatabaseTestCase
{
    private static Log log
            = LogFactory.getLog(DefaultValuesFromDatabaseTest.class);

    /** The default date format. */
    private static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";

    /**
     * Checks the java default values.
     */
    public void testJavaDefault() throws Exception
    {
        DatabaseDefaultValues databaseDefaultValues
                = new DatabaseDefaultValues();
        assertEquals(Integer.valueOf(2), databaseDefaultValues.getOInteger());
        assertEquals(4, databaseDefaultValues.getPInt());
        assertEquals("Default!", databaseDefaultValues.getVarcharField());
        // For date values, java fields are null if the default is read
        // from the database. This is because different databases
        // handle dates differently so it is difficult to predict
        // from java side what the database value will be.
        assertEquals(null, databaseDefaultValues.getDateField());
        assertEquals(null, databaseDefaultValues.getTimeField());
        assertEquals(null, databaseDefaultValues.getTimestampField());
    }

    /**
     * Checks that the buildColumnValues for an unchanged object
     * does not contain the values with database default.
     */
    public void testGetColumnValuesNewUnchangedObject() throws Exception
    {
        DatabaseDefaultValues databaseDefaultValues
                = new DatabaseDefaultValues();
        ColumnValues columnValues
                = DatabaseDefaultValuesPeer.buildColumnValues(
                        databaseDefaultValues);
        assertEquals(1, columnValues.size());
        assertEquals(
                columnValues.get(DatabaseDefaultValuesPeer.NORMAL_PAYLOAD),
                new JdbcTypedValue(0, Types.INTEGER));
    }

    /**
     * Checks the values in the database if a new object is saved.
     */
    public void testNewObjectDatabaseDefault() throws Exception
    {
        DatabaseDefaultValuesPeer.doDelete(new Criteria());
        DatabaseDefaultValues databaseDefaultValues
                = new DatabaseDefaultValues();

        databaseDefaultValues.save();

        // saved object should stay the same
        assertEquals(Integer.valueOf(2), databaseDefaultValues.getOInteger());
        assertEquals(4, databaseDefaultValues.getPInt());
        assertEquals("Default!", databaseDefaultValues.getVarcharField());
        assertEquals(null, databaseDefaultValues.getDateField());
        assertEquals(null, databaseDefaultValues.getTimeField());
        assertEquals(null, databaseDefaultValues.getTimestampField());
        // re-loading should give the database default values
        List<DatabaseDefaultValues> databaseDefaultValuesList
                = DatabaseDefaultValuesPeer.doSelect(new Criteria());
        assertEquals(1, databaseDefaultValuesList.size());
        DatabaseDefaultValues databaseDefaultValuesSaved
                = databaseDefaultValuesList.get(0);
        assertEquals(
                new Integer(2),
                databaseDefaultValuesSaved.getOInteger());
        assertEquals(4, databaseDefaultValuesSaved.getPInt());
        assertEquals("Default!", databaseDefaultValuesSaved.getVarcharField());
        assertEquals(toString(doSelect(toDate("2010-09-08 00:00:00"),
                        java.sql.Date.class)),
                toString(databaseDefaultValuesSaved.getDateField()));
        assertEquals(toString(doSelect(toDate("1970-01-01 10:20:30"),
                        java.sql.Time.class)),
                toString(databaseDefaultValuesSaved.getTimeField()));
        assertEquals(toString(doSelect(toDate("2010-09-08 11:12:13"),
                        java.sql.Timestamp.class)),
                toString(databaseDefaultValuesSaved.getTimestampField()));
    }

    /**
     * Tests that values are saved if they are not equal to the default value
     * on a new object.
     */
    public void testNewObjectChangedValue() throws Exception
    {
        DatabaseDefaultValuesPeer.doDelete(new Criteria());
        DatabaseDefaultValues databaseDefaultValues
                = new DatabaseDefaultValues();
        databaseDefaultValues.setOInteger(1);
        databaseDefaultValues.setPInt(3);
        databaseDefaultValues.setVarcharField("Changed!");
        databaseDefaultValues.setDateField(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("2000-01-02 00:00:00"));
        databaseDefaultValues.setTimeField(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("1970-01-01 11:21:31"));
        databaseDefaultValues.setTimestampField(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("2010-09-08 12:13:14"));

        databaseDefaultValues.save();

        // saved object should stay the same
        assertEquals(Integer.valueOf(1), databaseDefaultValues.getOInteger());
        assertEquals(3, databaseDefaultValues.getPInt());
        assertEquals("Changed!", databaseDefaultValues.getVarcharField());
        assertEquals(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("2000-01-02 00:00:00"),
                databaseDefaultValues.getDateField());
        assertEquals(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("1970-01-01 11:21:31"),
                databaseDefaultValues.getTimeField());
        assertEquals(
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                    .parse("2010-09-08 12:13:14"),
                databaseDefaultValues.getTimestampField());
        List<DatabaseDefaultValues> defaultValuesList
                = DatabaseDefaultValuesPeer.doSelect(new Criteria());
        assertEquals(1, defaultValuesList.size());
        DatabaseDefaultValues databaseDefaultValuesSaved
                = defaultValuesList.get(0);
        assertEquals(
                new Integer(1),
                databaseDefaultValuesSaved.getOInteger());
        assertEquals(3, databaseDefaultValuesSaved.getPInt());
        assertEquals("Changed!", databaseDefaultValuesSaved.getVarcharField());
        assertEquals(toString(doSelect(toDate("2000-01-02 00:00:00"),
                    java.sql.Date.class)),
                toString(databaseDefaultValuesSaved.getDateField()));
        assertEquals(toString(doSelect(toDate("1970-01-01 11:21:31"),
                        java.sql.Time.class)),
                toString(databaseDefaultValuesSaved.getTimeField()));
        assertEquals(toString(doSelect(toDate("2010-09-08 12:13:14"),
                        java.sql.Timestamp.class)),
                toString(databaseDefaultValuesSaved.getTimestampField()));
    }

    /**
     * Checks the second save also saves unchanged values.
     */
    public void testNotNewObjectSavesUnchangedValues() throws Exception
    {
        DatabaseDefaultValuesPeer.doDelete(new Criteria());
        DatabaseDefaultValues databaseDefaultValues
                = new DatabaseDefaultValues();
        databaseDefaultValues.save();
        // modify object in db so we can check that the unchanged values
        // are saved
        List<DatabaseDefaultValues> databaseDefaultValuesList
                = DatabaseDefaultValuesPeer.doSelect(new Criteria());
        assertEquals(1, databaseDefaultValuesList.size());
        DatabaseDefaultValues changedValuesInDatabase
                = databaseDefaultValuesList.get(0);
        changedValuesInDatabase.setOInteger(1);
        changedValuesInDatabase.setPInt(3);
        changedValuesInDatabase.setVarcharField("Changed!");
        changedValuesInDatabase.setDateField(
                new GregorianCalendar(1990, 2, 4).getTime());
        changedValuesInDatabase.setTimeField(new Date(2500L));
        changedValuesInDatabase.setTimestampField(
                new GregorianCalendar(1990, 2, 4).getTime());
        changedValuesInDatabase.save();
        databaseDefaultValues.setModified(true);

        // second save behaves differently because object is not new any more
        // unchanged values should also be saved
        databaseDefaultValues.save();

        // saved object should stay the same
        assertEquals(Integer.valueOf(2), databaseDefaultValues.getOInteger());
        assertEquals(4, databaseDefaultValues.getPInt());
        assertEquals("Default!", databaseDefaultValues.getVarcharField());
        assertEquals(null, databaseDefaultValues.getDateField());
        assertEquals(null, databaseDefaultValues.getTimeField());
        assertEquals(null, databaseDefaultValues.getTimestampField());
        // re-loading should give the unchanged values
        // (changes in the db were overwritten by the second save)
        databaseDefaultValuesList
                = DatabaseDefaultValuesPeer.doSelect(new Criteria());
        assertEquals(1, databaseDefaultValuesList.size());
        DatabaseDefaultValues databaseDefaultValuesSaved
                = databaseDefaultValuesList.get(0);
        assertEquals(
                new Integer(2),
                databaseDefaultValuesSaved.getOInteger());
        assertEquals(4, databaseDefaultValuesSaved.getPInt());
        assertEquals("Default!", databaseDefaultValuesSaved.getVarcharField());
        assertEquals(null, databaseDefaultValuesSaved.getDateField());
        assertEquals(null, databaseDefaultValuesSaved.getTimeField());
        assertEquals(null, databaseDefaultValuesSaved.getTimestampField());
    }

    /**
     * Checks that if CURRENT_DATE is used as default value
     * then the database saves the current date.
     *
     * @throws Exception if an error occurs.
     */
    public void testCurrentDateAsDefault() throws Exception
    {
        if (!canUseCurrentDateAsDefaultForDate())
        {
            return;
        }
        CurrentDateTablePeer.doDelete(new Criteria());
        CurrentDateTable currentDate = new CurrentDateTable();

        Date currentDateBefore = doSelect("CURRENT_DATE", java.sql.Date.class);
        currentDate.save();
        Date currentDateAfter = doSelect("CURRENT_DATE", java.sql.Date.class);

        List<CurrentDateTable> dbStateList
                = CurrentDateTablePeer.doSelect(new Criteria());
        assertEquals(1, dbStateList.size());
        CurrentDateTable dbState = dbStateList.get(0);
        assertFalse(
                "currentDate should be >= currentDateBefore",
                dbState.getCurrentDateValue().before(currentDateBefore));
        assertFalse(
                "currentDate should be <= currentDateAfter",
                dbState.getCurrentDateValue().after(currentDateAfter));
    }

    /**
     * Checks that if CURRENT_TIME is used as default value
     * then the database saves the current date.
     *
     * @throws Exception if an error occurs.
     */
    public void testCurrentTimeAsDefault() throws Exception
    {
        if (!canUseCurrentTimeAsDefaultForTime())
        {
            return;
        }
        CurrentTimeTablePeer.doDelete(new Criteria());
        CurrentTimeTable currentTime = new CurrentTimeTable();

        Date currentDateBefore = doSelect("CURRENT_TIME", Time.class);
        currentTime.save();
        Date currentDateAfter = doSelect("CURRENT_TIME", Time.class);

        List<CurrentTimeTable> dbStateList
                = CurrentTimeTablePeer.doSelect(new Criteria());
        assertEquals(1, dbStateList.size());
        CurrentTimeTable dbState = dbStateList.get(0);
        if (timezoneBuggyInCurrentTime())
        {
            return;
        }
        assertFalse(
                "currentDate should be >= currentDateBefore",
                dbState.getCurrentTimeValue().before(currentDateBefore));
        assertFalse(
                "currentDate should be <= currentDateAfter",
                dbState.getCurrentTimeValue().after(currentDateAfter));
    }

    /**
     * Checks that if CURRENT_TIMESTAMP is used as default value
     * then the database saves the current date.
     *
     * @throws Exception if an error occurs.
     */
    public void testCurrentTimestampAsDefault() throws Exception
    {
        if (!canUseCurrentTimestampAsDefaultForTimestamp())
        {
            return;
        }
        CurrentTimestampTablePeer.doDelete(new Criteria());
        CurrentTimestampTable currentTimestamp = new CurrentTimestampTable();

        Date currentTimestampBefore = doSelect("CURRENT_TIMESTAMP", Timestamp.class);
        currentTimestamp.save();
        Date currentTimestampAfter = doSelect("CURRENT_TIMESTAMP", Timestamp.class);

        List<CurrentTimestampTable> dbStateList
                = CurrentTimestampTablePeer.doSelect(new Criteria());
        assertEquals(1, dbStateList.size());
        CurrentTimestampTable dbState = dbStateList.get(0);
        assertFalse(
                "currentDate should be >= currentDateBefore",
                dbState.getCurrentTimestampValue().before(
                        currentTimestampBefore));
        assertFalse(
                "currentDate should be <= currentDateAfter",
                dbState.getCurrentTimestampValue().after(
                        currentTimestampAfter));
    }

    private Date doSelect(Date toSelect, Class<?> classToSelect)
            throws Exception
    {
        String dateFormat;
        if (defaultAdapter instanceof OracleAdapter)
        {
            dateFormat = "'to_timestamp('''yyyy-MM-dd HH:mm:ss''',''yyyy-mm-dd hh24:mi:ss'')'";
        }
        else if (defaultAdapter instanceof MysqlAdapter
                && (classToSelect == java.sql.Time.class))
        {
            dateFormat = "''HH:mm:ss''";
        }
        else if (defaultAdapter instanceof MssqlAdapter)
        {
            dateFormat = "''yyyyMMdd HH:mm:ss''";
        }
        else if (defaultAdapter instanceof HsqldbAdapter)
        {
            if (classToSelect == java.sql.Date.class)
            {
                dateFormat = "''yyyy-MM-dd''";
            }
            else if (classToSelect == java.sql.Time.class)
            {
                dateFormat = "''HH:mm:ss''";
            }
            else
            {
                dateFormat = "''yyyy-MM-dd HH:mm:ss''";
            }
        }
        else
        {
            dateFormat = "''yyyy-MM-dd HH:mm:ss''";
        }
        String dateString = new SimpleDateFormat(dateFormat).format(toSelect);
        return doSelect(dateString, classToSelect);
    }

    private Date doSelect(String toSelect, Class<?> classToSelect)
            throws Exception
    {
        String sql;
        if (defaultAdapter instanceof OracleAdapter)
        {
            sql = "select " + toSelect + " from dual";
        }
        else if (defaultAdapter instanceof DerbyAdapter)
        {
            sql = "values(" + toSelect + ")";
        }
        else if (defaultAdapter instanceof HsqldbAdapter)
        {
            sql = "call " + toSelect;
        }
        else if (defaultAdapter instanceof MssqlAdapter)
        {
            sql = "select convert(datetime," + toSelect +")";
        }
        else
        {
            sql = "select " + toSelect;
        }
        Connection connection = null;
        try
        {
            connection = Torque.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if (!resultSet.next())
            {
                fail("Statement " + sql + " returned 0 rows");
            }
            Date result;
            if (Time.class == classToSelect)
            {
                result= resultSet.getTime(1);
            }
            else if (java.sql.Date.class == classToSelect)
            {
                result= resultSet.getDate(1);
            }
            else if (Timestamp.class == classToSelect)
            {
                result= resultSet.getTimestamp(1);
            }
            else
            {
                throw new IllegalArgumentException("unknown classToSelect "
                        + classToSelect.getName());
            }
            if (resultSet.next())
            {
                fail("Statement " + sql + " returned more than 1 row");
            }
            return result;
        }
        finally
        {
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (Exception e)
                {
                    // ignore
                }
            }
        }
    }

    private static boolean canUseCurrentDateAsDefaultForDate()
    {
        if (defaultAdapter instanceof MysqlAdapter
                || defaultAdapter instanceof MssqlAdapter)
        {
            log.warn("canUseCurrentDateAsDefaultForDate(): "
                    + "CURRENT_DATE cannot be used as default value "
                    + "for Date columns for MySQL and MSSQL");
            return false;
        }
        return true;
    }

    private static boolean canUseCurrentTimeAsDefaultForTime()
    {
        if (defaultAdapter instanceof MysqlAdapter
                || defaultAdapter instanceof OracleAdapter
                || defaultAdapter instanceof MssqlAdapter)
        {
            log.warn("canUseCurrentTimeAsDefaultForTime(): "
                    + "CURRENT_TIME cannot be used as default value "
                    + "for Time columns for MySQL, Oracle and MSSQL");
            return false;
        }
        return true;
    }

    private static boolean canUseCurrentTimestampAsDefaultForTimestamp()
    {
        if (defaultAdapter instanceof MysqlAdapter)
        {
            log.warn("canUseCurrentDateAsDefaultForDate(): "
                    + "CURRENT_TIMESTAMP cannot be used as default value "
                    + "for Timestamp columns for MySQL");
            return false;
        }
        return true;
    }

    private static boolean timezoneBuggyInCurrentTime()
    {
        if (defaultAdapter instanceof PostgresAdapter)
        {
            log.warn("Timezone is buggy in CURRENT_TIME in Postgres");
            return true;
        }
        return false;
    }

    private static Date toDate(String toConvert) throws ParseException
    {
        return new SimpleDateFormat(DATE_FORMAT).parse(toConvert);
    }

    private static String toString(Date toConvert)
    {
        return new SimpleDateFormat(DATE_FORMAT).format(toConvert);
    }
}
