/*

Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_13

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.upgradeTests;

import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SupportFilesSetup;


/**
 * Upgrade test cases for 10.13.
 */
public class Changes10_13 extends UpgradeChange
{

    //////////////////////////////////////////////////////////////////
    //
    // CONSTANTS
    //
    //////////////////////////////////////////////////////////////////

    private static final String SYNTAX_ERROR = "42X01";
    private static final String UPGRADE_REQUIRED = "XCL47";
    private static final String CANNOT_ALTER_NON_IDENTITY_COLUMN = "42Z29";
    private static final String CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN = "42Z23";
    private static final String NO_SUCH_METHOD_ALIAS="42Y03";

    //////////////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    //////////////////////////////////////////////////////////////////

    public Changes10_13(String name) {
        super(name);
    }

    //////////////////////////////////////////////////////////////////
    //
    // JUnit BEHAVIOR
    //
    //////////////////////////////////////////////////////////////////

    /**
     * Return the suite of tests to test the changes made in 10.13.
     *
     * @param phase an integer that indicates the current phase in
     *              the upgrade test.
     * @return the test suite created.
     */
    public static Test suite(int phase) {
        return new BaseTestSuite(Changes10_13.class, "Upgrade test for 10.13");
    }

    //////////////////////////////////////////////////////////////////
    //
    // TESTS
    //
    //////////////////////////////////////////////////////////////////

    /**
     * Test the addition of support for changing identity columns
     * from ALWAYS to BY DEFAULT and vice versa via
     * an ALTER TABLE statement. DERBY-6882.
     */
    public void testAlterTableSetGenerated() throws SQLException {
        Statement s = createStatement();

        // GENERATED BY DEFAULT not supported prior to 10.1
        if (!oldAtLeast(10, 1)) { return; }

        // 10.11 upgraded all identity columns to be backed by sequences
        boolean atLeast10_11 = oldAtLeast(10,11);
        
        switch (getPhase()) {
            case PH_CREATE:
                s.execute("create table t_always_6882(a int generated always as identity, b int)");
                s.execute("create table t_default_6882(a int generated by default as identity, b int)");
                s.execute("create table t_none_6882(a int, b int)");
                s.execute("insert into t_always_6882(b) values (100)");
                s.execute("insert into t_default_6882(b) values (100)");
                assertCompileError
                  (
                   CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN,
                   "insert into t_always_6882(a, b) values (-1, -100)"
                   );
                s.execute("insert into t_default_6882(a, b) values (-1, -100)");
                break;

            case PH_SOFT_UPGRADE:
                // We only support the SET GENERATED clause if the database
                // is at level 10.11 or higher.
                if (atLeast10_11)
                {
                    assertCompileError
                      (
                       CANNOT_ALTER_NON_IDENTITY_COLUMN,
                       "alter table t_none_6882 alter column a set generated by default"
                       );
                    s.execute("alter table t_always_6882 alter column a set generated by default");
                    s.execute("alter table t_default_6882 alter column a set generated always");
                    s.execute("insert into t_always_6882(a, b) values (-2, -200)");
                    assertCompileError
                      (
                       CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN,
                       "insert into t_default_6882(a, b) values (-2, -200)"
                       );
                    s.execute("insert into t_always_6882(b) values (200)");
                    s.execute("insert into t_default_6882(b) values (200)");
                    JDBC.assertFullResultSet(
                        s.executeQuery("select * from t_always_6882 order by a"),
                        new String[][]
                        {
                          { "-2", "-200" },
                          { "1", "100" },
                          { "2", "200" },
                        });
                    JDBC.assertFullResultSet(
                        s.executeQuery("select * from t_default_6882 order by a"),
                        new String[][]
                        {
                          { "-1", "-100" },
                          { "1", "100" },
                          { "2", "200" },
                        });
                } else {
                    assertCompileError
                      (
                       UPGRADE_REQUIRED,
                       "alter table t_none_6882 alter column a set generated by default"
                       );
                    assertCompileError
                      (
                       UPGRADE_REQUIRED,
                       "alter table t_always_6882 alter column a set generated by default"
                       );
                    assertCompileError
                      (
                       UPGRADE_REQUIRED,
                       "alter table t_default_6882 alter column a set generated always"
                       );
                }
                break;
                
            case PH_HARD_UPGRADE:
                assertCompileError
                  (
                   CANNOT_ALTER_NON_IDENTITY_COLUMN,
                   "alter table t_none_6882 alter column a set generated by default"
                   );
                s.execute("alter table t_always_6882 alter column a set generated always");
                s.execute("alter table t_default_6882 alter column a set generated by default");
                assertCompileError
                  (
                   CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN,
                   "insert into t_always_6882(a, b) values (-3, -300)"
                   );
                s.execute("insert into t_default_6882(a, b) values (-3, -300)");
                s.execute("insert into t_always_6882(b) values (300)");
                s.execute("insert into t_default_6882(b) values (300)");

                String[][] alwaysResults;
                String[][] defaultResults;
                if (atLeast10_11)
                {
                    alwaysResults =  new String[][]  
                      {
                        { "-2", "-200" },
                        { "1", "100" },
                        { "2", "200" },
                        { "3", "300" },
                      };
                    defaultResults = new String[][]
                      {
                        { "-3", "-300" },
                        { "-1", "-100" },
                        { "1", "100" },
                        { "2", "200" },
                        { "3", "300" },
                      };
                }
                else
                {
                    alwaysResults = new String[][]  
                      {
                        { "1", "100" },
                        { "2", "300" },
                      };
                    defaultResults = new String[][]
                      {
                        { "-3", "-300" },
                        { "-1", "-100" },
                        { "1", "100" },
                        { "2", "300" },
                      };
                }

                JDBC.assertFullResultSet
                  (
                   s.executeQuery("select * from t_always_6882 order by a"),
                   alwaysResults
                   );
                JDBC.assertFullResultSet
                  (
                   s.executeQuery("select * from t_default_6882 order by a"),
                   defaultResults);
                break;
        };
    }
	/**
	 * Test newly added system procedure to import table with header lines.
	 * DERBY-6892. 
	 */
     public void testDerby6892SkipHeaderLines() throws SQLException {
        Statement s = createStatement();

        switch (getPhase()) {
		case PH_CREATE:
			//table to export
			s.execute("create table ex_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");

			//table to import
			s.execute("create table imp_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
		
			s.execute("insert into ex_pet values('Rover', 'Dog' , 4)");

			s.execute("insert into ex_pet values('Spot', 'Cat' , 2)");
	
			s.execute("insert into ex_pet values('Squawky','Parrot',37)");
			

			SupportFilesSetup.deleteFile("pet.dat");
			s.execute("call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_PET', 'pet.dat' , null, null, null) ");
			
				
			//Show that SYSCS_IMPORT_TABLE_BULK 
			//does not exist
			assertCompileError
                  	(
                   	NO_SUCH_METHOD_ALIAS,
                   	"call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET' , 'pet.dat' , null, null, null, 0 , 1) ");
			break;
            

		case PH_SOFT_UPGRADE:		
           		//Show that SYSCS_IMPORT_TABLE_BULK
			//does not support with soft upgrade
			assertCompileError
                  	(
                   	NO_SUCH_METHOD_ALIAS,
                   	"call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat' , null, null, null, 0 , 1) "
                   	);
                	break;
                
                case PH_POST_SOFT_UPGRADE:
                	//Show that SYSCS_IMPORT_TABLE_BULK
			//does not exist
			assertCompileError
                  	(
                   	NO_SUCH_METHOD_ALIAS,
                   	"call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat' , null, null, null, 0 , 1) "
                   	);
                	break;


	       case PH_HARD_UPGRADE:

			s.execute("delete from imp_pet");
			//Show that SYSCS_IMPORT_TABLE_BULK 
			//accept CSV file with header lines
			s.execute("call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat', null, null, null, 0, 1)");

		
			JDBC.assertFullResultSet(
                	s.executeQuery("select * from IMP_PET"),
                        new String[][]
                        {
                              { "Spot", "Cat", "2"},
                              { "Squawky", "Parrot", "37"},
                        });

			break;
        };
    }

        /**
	 * Test newly added system procedure to import data with header lines.
	 * DERBY-6893. 
	 */
     public void testDerby6893SkipHeaderLines() throws SQLException {
        Statement s = createStatement();
       switch (getPhase()) {
		case PH_CREATE:
			//table to export
		        s.execute("create table exp_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");

			//table to import
			s.execute("create table im_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
			s.execute("insert into exp_pet values('Rover', 'Dog' , 4)");

			s.execute("insert into exp_pet values('Spot', 'Cat' , 2)");
	
			s.execute("insert into exp_pet values('Squawky','Parrot',37)");
		

			SupportFilesSetup.deleteFile("pet.dat");
			s.execute("call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'EXP_PET', 'pet.dat' , null, null, null) ");
			
		
			//Show that SYSCS_IMPORT_DATA_BULK 
			//does not exist
			assertCompileError
                  	(
                 	NO_SUCH_METHOD_ALIAS,
                   	"call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3',  'pet.dat' , null, null, null, 0 , 1) ");
			break;
           

		case PH_SOFT_UPGRADE:		
           		//Show that SYSCS_IMPORT_DATA_BULK
			//does not support with soft upgrade
			assertCompileError
                  	(
                  	NO_SUCH_METHOD_ALIAS,
                  	"call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3',  'pet.dat' , null, null, null, 0 , 1) "
                   	);
              		break;
                
                case PH_POST_SOFT_UPGRADE:
	               	//Show that SYSCS_IMPORT_DATA_BULK
			//does not exist
			assertCompileError
                 	(
                   	NO_SUCH_METHOD_ALIAS,
                  	"call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3',  'pet.dat' , null, null, null, 0 , 1) "
                   	);
                	break;

	       case PH_HARD_UPGRADE:
			s.execute("delete from im_pet");
			//Show that SYSCS_IMPORT_DATA_BULK 
			//accept CSV file with header lines
			s.execute("call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3', "
  			+" 'pet.dat' , null, null, null, 0 , 1) ");

			JDBC.assertFullResultSet(
                	s.executeQuery("select * from IM_PET"),
                        new String[][]
                        {
                              { "Spot", "Cat", "2"},
                              { "Squawky", "Parrot", "37"},
                        });

			break;
        };
    }

 
}