/*
Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ColumnDefaultsTest

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.

 */
package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.functionTests.util.Formatters;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

public final class ColumnDefaultsTest extends BaseJDBCTestCase {

    /**
     * Public constructor required for running test as standalone JUnit.
     */
    public ColumnDefaultsTest(String name)
    {
        super(name);
    }

    public static Test suite()
    {
        BaseTestSuite suite = new BaseTestSuite("ColumnDefaultsTest Test");
        suite.addTest(TestConfiguration.defaultSuite(ColumnDefaultsTest.class));
        return suite;
    }

    public void testNegativeTestsForColumnDefaults() throws Exception
    {
        Statement st = createStatement();
        
        //? in default
        assertStatementError("42X01", st,
            "create table neg(c1 int default ?)");
        
        // column reference in default
        
        assertStatementError("42X01", st,
            "create table neg(c1 int, c2 int default c1)");
        
        // subquery in default
        
        assertStatementError("42X01", st,
            "create table neg(c1 int default (values 1))");
        
        // type incompatibility at compile time
        
        assertStatementError("42821", st,
            "create table neg(c1 date default 1)");
        
        // type incompatibility at execution time bug 5585 - 
        // should fail at create table statement because the 
        // default value '1' is not valid
        
        st.executeUpdate(
            "create table neg(c1 int, c2 date default '1')");
        
        assertStatementError("22007", st, " insert into neg (c1) values 1");
        
        st.executeUpdate( " drop table neg");
        
        // bug 5203 - built-in functions are not be allowed in a 
        // constantExpression because DB2 UDB returns SQLSTATE 42894
        
        st.executeUpdate(
            "CREATE FUNCTION ASDF (DATA DOUBLE) RETURNS DOUBLE "
            + "EXTERNAL NAME 'java.lang.Math.sin' LANGUAGE JAVA "
            + "PARAMETER STYLE JAVA");
        
        //DEFAULT value or IDENTITY attribute value is not valid 
        
        assertStatementError("42894", st,
            " create table neg(c1 int default asdf(0))");
        
        //drop a table which does not exists fails
        assertStatementError("42Y55", st, " drop table neg");
        
        // DEFAULT only valid in VALUES within an insert
        
        assertStatementError("42Y85", st, "values default");
        
        assertStatementError("42Y85", st, " values 1, default");
        
        // alter table modify default
        
        st.executeUpdate( "create table neg(c1 date)");
        
        assertStatementError("42X01", st,
            " alter table neg modify x default null");
        
        assertStatementError("42821", st,
            " alter table neg add column x date default 1");
        
        // bug 5585 - should fail at alter table statement because 
        // the default value '1' is not valid
        
        st.executeUpdate( "alter table neg add column x date default '1'");
        
        assertStatementError("22007", st,
            " insert into neg (c1) values default");
        
        st.executeUpdate( " drop table neg");
        
        // too many values in values clause
        
        st.executeUpdate( "create table neg(c1 int default 10)");
        
        assertStatementError("42X06", st,
            " insert into neg values (1, default)");
        
        assertStatementError("42802", st,
            " insert into neg values (default, 1)");


        // DERBY-4426
        assertStatementError("42Y85", st,
            " insert into neg values (default) union values (default)");

        assertStatementError("42Y85", st,
            " insert into neg values (default) except values (default)");

        // Make sure sub-queries are inspected for illegal DEFAULT also.
        assertStatementError("42Y85", st,
            " insert into neg select * from (values default) t");

        st.executeUpdate( " drop table neg");
        
        st.executeUpdate( "drop function asdf");
        
        rollback();
        st.close();
    }
    
    public void testPositiveTestsForColumnDefaults()
        throws SQLException
    {
        
        // positive 
        Statement st = createStatement();
        
        //create tables
        
        st.executeUpdate(
            "create table t1(c1 int, c2 int with default 5, c3 "
            + "date default current_date, c4 int)");
        
        // verify that defaults work
        
        st.executeUpdate( "insert into t1 (c1) values 1");
        
        st.executeUpdate( " insert into t1 (c4) values 4");
        
        ResultSet rs = st.executeQuery("select c1, c2, c4 from t1");
        
        String[] expColNames = {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        String[][] expRS = {
            {"1", "5", null},
            {null, "5", "4"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 = current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "5", null},
            {null, "5", "4"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // update default for column whose default is null
        
        assertUpdateCount(st, 2, "update t1 set c1 = default");
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 = current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {null, "5", null},
            {null, "5", "4"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // default for column that has explicit default
        
        assertUpdateCount(st, 2, "update t1 set c2 = 7");
        
        rs = st.executeQuery( " select c2 from t1");
        
        expColNames = new String [] {"C2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"7"},
            {"7"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        assertUpdateCount(st, 2, " update t1 set c2 = default");
        
        rs = st.executeQuery( " select c2 from t1");
        
        expColNames = new String [] {"C2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"5"},
            {"5"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // insert default
        
        assertUpdateCount(st, 2, "delete from t1");
        
        st.executeUpdate(
            " insert into t1 values (5, default, '1999-09-09', default)");
        
        st.executeUpdate(
            " insert into t1 values (default, 6, default, 5)");
        
        st.executeUpdate(
            " insert into t1 values (default, 6, default, 5), "
            + "(7, default, '1997-07-07', 3)");
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 = current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {null, "6", "5"},
            {null, "6", "5"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 <> current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"5", "5", null},
            {"7", "5", "3"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        assertUpdateCount(st, 4, " delete from t1");
        
        st.executeUpdate(
            " insert into t1 (c1, c3, c4) values (5, "
            + "'1999-09-09', default)");
        
        st.executeUpdate(
            " insert into t1 (c1, c3, c4) values (default, default, 5)");
        
        st.executeUpdate(
            " insert into t1 (c1, c3, c4) values (default, "
            + "default, default)");
        
        st.executeUpdate(
            " insert into t1 (c1, c3, c4) values (default, "
            + "default, 5), (7, '1997-07-07', 3)");
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 = current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {null, "5", "5"},
            {null, "5", null},
            {null, "5", "5"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select c1, c2, c4 from t1 where c3 <> current_date");
        
        expColNames = new String [] {"C1", "C2", "C4"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"5", "5", null},
            {"7", "5", "3"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // delimited identifiers this schema
        
        st.executeUpdate( "create table \"x1\" (\"c1\" int)");
        
        st.executeUpdate( " insert into \"x1\" values 1");
        
        st.executeUpdate(
            " alter table \"x1\" add column \"c2\" char(1) default 'x'");
        
        rs = st.executeQuery( " select * from \"x1\"");
        
        expColNames = new String [] {"c1", "c2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "x"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // another schema
        
        st.executeUpdate( "create schema \"otherschema\"");
        
        st.executeUpdate(
            " create table \"otherschema\".\"y1\" (\"c11\" int)");
        
        st.executeUpdate(
            " insert into \"otherschema\".\"y1\" values 2");
        
        st.executeUpdate(
            " alter table \"otherschema\".\"y1\" add column "
            + "\"c22\" char(1) default 'y'");
        
        rs = st.executeQuery(
            " select * from \"otherschema\".\"y1\"");
        
        expColNames = new String [] {"c11", "c22"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"2", "y"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // bug 3433
        
        st.executeUpdate( "create table t7(c1 int default 10)");
        
        st.executeUpdate( " insert into t7 values (default)");
        
        rs = st.executeQuery( " select * from t7");
        
        expColNames = new String [] {"C1"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"10"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // DERBY-4426: make sure we don't forbid inside a multi-value table
        // constructor since this is represented as a UnionNode
        st.executeUpdate( "delete from t7");
        st.executeUpdate( "insert into t7 values 1, default");
        rs = st.executeQuery( " select * from t7");

        expRS = new String [][]
        {
            {"1"},
            {"10"}
        };

        JDBC.assertFullResultSet(rs, expRS, true);

        st.executeUpdate( " drop table t1");
        
        st.executeUpdate( " drop table t7");
        
        st.executeUpdate( " drop table \"x1\"");
        
        st.executeUpdate( " drop table \"otherschema\".\"y1\"");
        
        st.executeUpdate( " drop schema \"otherschema\" restrict");
        
        rollback();
        st.close();
        
    }
    public void testJira331()
        throws SQLException
    {
        // JIRA issue Derby-331
        Statement st = createStatement();
        
        st.executeUpdate(
            "create table t_331 (a int not null, b int default "
            + "0, unique (a))");
        
        st.executeUpdate( " insert into t_331 values (4, default)");
        
        assertStatementError("23505", st,
            " insert into t_331 values (4, default)");
        
        ResultSet rs = st.executeQuery( " select * from t_331");
        
        String[] expColNames = {"A", "B"};
        JDBC.assertColumnNames(rs, expColNames);
        
        String[][] expRS = {
            {"4", "0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
         st.executeUpdate( " drop table t_331");
         
        rollback();
        st.close();
    }
    
    public void testJira3013()
        throws SQLException
    {
        // begin DERBY-3013
        Statement st = createStatement();
        
        st.executeUpdate(
            "create table tabWithUserAndSchemaDefaults("
            + "             cUser           CHAR(8) default user,"
            + "             cCurrent_user   CHAR(8) default current_user,"
            + "             cSession_user   CHAR(8) default session_user,"
            + "             cCurrent_schema CHAR(128) default "
            + "current schema)");
        
        // Should work
        
        st.executeUpdate(
            "insert into tabWithUserAndSchemaDefaults values "
            + "(default, default, default, default)");
        
        ResultSet rs = st.executeQuery(
            " select * from tabWithUserAndSchemaDefaults");
        
        String[] expColNames = {"CUSER", "CCURRENT_USER",
            "CSESSION_USER", "CCURRENT_SCHEMA"};
        JDBC.assertColumnNames(rs, expColNames);
        
        String[][] expRS = {
            {"APP", "APP", "APP", "APP"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Should fail:DEFAULT value or IDENTITY attribute value is not valid
        
        assertStatementError("42894", st,
            "create table tabWithUserDefaultTooNarrowColumn("
            + "       c1 CHAR(7) default user)");
        
        // Should fail:DEFAULT value or IDENTITY attribute value is not valid
        
        assertStatementError("42894", st,
            "create table tabWithSchemaDefaultTooNarrowColumn("
            + "       c1 CHAR(127) default current sqlid)");
        
        st.executeUpdate(
            " drop table tabWithUserAndSchemaDefaults");
               
        rollback();
        st.close();
    }

    public void testDerby118_and_5829 () throws SQLException {
        Statement st = createStatement();

        /*
         * Lift restriction that even VARCHAR can max have 254 chars in
         * DEFAULT, another DB2ism.
         */
        String sb = Formatters.repeatChar("-", 400);

        st.executeUpdate(
            "create table tabLongVarchar("
            + "    c varchar(400) default " + "'" + sb + "')");
        st.executeUpdate(
            "insert into  tabLongVarchar" +
            "    values default");

        ResultSet rs = st.executeQuery("select c from tabLongVarchar");
        JDBC.assertSingleValueResultSet(rs, sb);

        // Negative test: Make sure we still flag too long string for CHAR
        // (length > 254).  Note that the check will now only happen at insert
        // time, as other truncation checks do.
        st.executeUpdate(
            "create table tabLongChar("
            + "    c char(254) default " + "'" + sb + "')");
        try {
            st.executeUpdate(
                "insert into tabLongChar values default");
            fail();
        } catch (SQLException e) {
            assertSQLState("22001", e); // truncate
        }

        // Interestingly, a float literal for SMALLINT is not disabled for
        // assignment to integer types.  This is asymmetric, as it is forbidden
        // for the other integer types, as per the standard, btw.
        // Remove this test if we forbid this, or add tests for the other
        // integer types if we decide to allow it, cf discussions on
        // DERBY-118. For now, we just document this weirdness in this test.
        st.executeUpdate(
            "create table tabSmallIntFloat(si smallint default 3.14)");
        st.executeUpdate(
            "insert into  tabSmallIntFloat values default");
        rs = st.executeQuery("select si from tabSmallIntFloat");
        JDBC.assertSingleValueResultSet(rs, "3");

        // Compare with INT, which fails:
        try {
            st.executeUpdate(
                "create table tabIntFloat(i int default 3.14)");
            fail();
        } catch (SQLException e) {
            assertSQLState("42894", e);
        }
    }
}