/*
 
 Derby - Class org.apache.derbyTesting.functionTests.tests.lang.PrimaryKeyTest
 
 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.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;

public class PrimaryKeyTest extends BaseJDBCTestCase
{	
	public PrimaryKeyTest(String name)      {
		super(name);
	}
	public static Test suite() {
        return new BaseTestSuite(PrimaryKeyTest.class);
	}

    @Override
	protected void setUp() throws Exception {
		super.setUp();
		getConnection().setAutoCommit(false);
	}

					/************ NEGATIVE TESTS ************/	
	/**
 	 * 
 	 * Tests that A table can't contain two primary keys.
 	 *
 	 *  @exception SQLException 
 	 */ 	
	public void testDuplicatePrimaryKey() throws SQLException {
		Statement s = createStatement();
		//duplicate primary keys
		assertStatementError("42X90" , s , "create table neg (c1 int not null primary key, c2 int, c3 int not null constraint asdf primary key)");
		assertStatementError("42X90" , s , "create table neg (c1 int not null primary key, c2 int not null, primary key(c1, c2))");
	}
	/**
	 * Tests the Invalid constraint Definations.
	 *
	 *  @exception SQLException 
	 */ 
	public void testInvalidConstaintDefs() throws SQLException {
		Statement s = createStatement();
		//-- duplicate constraint names
		assertStatementError("42X91" , s , "create table neg (c1 int not null constraint asdf primary key, c2 int, c3 int constraint asdf unique)");
		//-- duplicate column names in same constraint column list
		assertStatementError("42X92" , s , "create table neg (c1 int not null, c2 int not null, primary key(c1, c2, c1))");
		//-- non-existant columns in constraint column list
		assertStatementError("42X93" , s , "create table neg (c1 int not null, c2 int not null, primary key(c1, c2, cx))");
	}
	/**
	 *  Tests Invalid constraint schema name
	 *
	 *
	 *   @exception SQLException 
	 */
	public void testInvalidConstraintSchemaNames() throws SQLException
	{
		Statement s = createStatement();
		// constraint names are required to be in the same schema as the table on which they are constrained.
		assertStatementError("42X85" , s , "create table neg (c1 int not null, c2 int not null, constraint bar.pkneg primary key(c1, c2))");
		assertStatementError("42X85" , s , "create table neg (c1 int not null, c2 int not null, constraint sys.pkneg primary key(c1, c2))");
		assertStatementError("42X85" , s , "create table neg (c1 int not null constraint bar.pkneg primary key, c2 int)");
		assertStatementError("42X85" , s , "create table neg (c1 int not null constraint sys.pkneg primary key, c2 int)");
	}
	/**
	 * Constraint Names must be Unique with in a schema
	 *
	 * @exception SQLException 
	 */
	public void testDuplicateConstraintNames() throws SQLException {
		Statement s = createStatement();
		assertUpdateCount(s , 0 , "create table neg1(c1 int not null constraint asdf primary key)");
		//-- constraint names must be unique within a schema
		assertStatementError("X0Y32" , s , "create table neg2(c1 int not null constraint asdf primary key)");
		assertUpdateCount(s , 0 , "drop table neg1");
		assertUpdateCount(s , 0 , "create table neg2(c1 int not null constraint asdf primary key)");
		assertUpdateCount(s , 0 , "drop table neg2");

		//-- again with explict schema names, should fail
		assertUpdateCount(s , 0 , "create table neg1(c1 int not null constraint app.asdf primary key)");
		assertStatementError("X0Y32" , s , "create table neg2(c1 int not null constraint app.asdf primary key)");

		//-- again with mixing schema names
		assertStatementError("X0Y32" , s , "create table neg1(c1 int not null constraint asdf primary key)");
		assertStatementError("X0Y32" , s , "create table neg2(c1 int not null constraint app.asdf primary key)");
		assertUpdateCount(s , 0 , "drop table neg1");
		assertUpdateCount(s , 0 , "create table neg2(c1 int not null constraint app.asdf primary key)");
	}
	/**
	 * Tests that primary and Unique key constraint cannot be Explicitely Nullable.
	 *
	 *  @exception SQLException 
	 */ 
    public void testExplicitNullabilityOfConstraints() throws SQLException {
		Statement s = createStatement();
		//-- primary key cannot be explicitly nullable
		assertStatementError("42X01" , s , "create table neg2(c1 int null constraint asdf primary key)");
		assertStatementError("42X01" , s , "create table neg2(c1 int null, c2 int, constraint asdf primary key(c1, c2))");
		//-- test that a unique key can not be explicitly nullable
		assertStatementError("42X01" , s , "create table neg1(c1 int null unique)");
		assertStatementError("42X01" , s , "create table neg1(c1 int null, c2 int, constraint asdf unique(c1))");
	}
			
					/************* POSITIVE TESTS ************/
	/** 
	 * Tests that If a column is a part of Primary Key then it cann't contain NULL values.
	 * And also Unique key cann't contain nulls.
	 *
	 *  @exception SQLException 
	 */ 
	public void testKeyConstraintsImpliesNotNull() throws SQLException {
		//-- verify that a primary key implies not null
		Statement s = createStatement();
		assertUpdateCount(s , 0 , "create table pos1 (c1 int primary key)");
		assertUpdateCount(s , 1 , "insert into pos1(c1) values(1)");
		assertStatementError("23505" , s , "insert into pos1(c1) values(1)");
		assertStatementError("23502" , s , "insert into pos1(c1) values(null)");
		assertUpdateCount(s , 0 , "drop table pos1");
	}
	/**
	 * Tests that we can combile key constraints with not null.
	 *
	 *  @exception SQLException 
	 */ 
	public void testConstraintwithNotNull() throws SQLException
	{
		Statement s = createStatement();
		//-- verify that you can combine not null and unique/primary key constraints
		assertUpdateCount(s , 0 , "create table pos1 (c1 int not null unique, c2 int not null primary key)");
		assertStatementError("23502" , s , "insert into pos1 (c1) values (null)");
		assertStatementError("23502" , s , "insert into pos1 (c2) values (null)");
		assertUpdateCount(s , 0 , "drop table pos1");

		//-- verify that you can combine multiple column constraints
		ResultSet rs1 = s.executeQuery("select count(*) from sys.sysconstraints");
		JDBC.assertSingleValueResultSet(rs1 , "0");

		rs1 = s.executeQuery("select count(*) from sys.syskeys");
		JDBC.assertSingleValueResultSet(rs1 , "0");

		//-- we will be adding 6 rows to both sysconstraints and syskeys
		assertUpdateCount(s , 0 , "create table pos1 (c1 int not null unique, c2 int not null primary key)");
		assertStatementError("23502" , s , "insert into pos1 (c1) values (null)");
		assertStatementError("23502" , s , "insert into pos1 (c2) values (null)");
		assertStatementError("23505" , s , "insert into pos1 values (1, 1), (1, 2)");
		assertStatementError("23505" , s , "insert into pos1 values (1, 1), (2, 1)");

		rs1 = s.executeQuery("select count(*) from sys.sysconstraints");
		JDBC.assertSingleValueResultSet(rs1 , "2");

		rs1 = s.executeQuery("select count(*) from sys.syskeys");
		JDBC.assertSingleValueResultSet(rs1 , "2");

		assertUpdateCount(s , 0 , "drop table pos1");
	}
	/**
	 * tests that we can Delete from Primary Key
	 *
	 *  @exception SQLException 
	 */ 
	public void testDeleteFromPrimaryKey() throws SQLException {
		Statement s = createStatement();
		//-- verify that you can delete from a primary key
		assertUpdateCount(s , 0 , "create table pos1 (c1 int not null, c2 int not null, primary key(c2, c1))");
		assertUpdateCount(s , 1 , "insert into pos1 values (1, 2)");
		ResultSet rs = s.executeQuery("select count(*) from pos1");
		JDBC.assertSingleValueResultSet(rs , "1");

		s.executeUpdate("delete from pos1");

		rs = s.executeQuery("select count(*) from pos1");
		JDBC.assertSingleValueResultSet(rs , "0");
		assertUpdateCount(s , 0 , "drop table pos1");

	}
	/**
	 * verify the consistency of the indexes on the system catalogs
	 *
	 *  @exception SQLException 
	 */ 
	public void testCatalog() throws SQLException {
		Statement s = createStatement();
		assertUpdateCount(s , 0 , "create table pos1(c1 int primary key)");
		ResultSet rs = s.executeQuery("select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from sys.systables where CAST(tabletype AS CHAR(1)) = 'S'  and CAST(tablename AS VARCHAR(128)) != 'SYSDUMMY1' order by tablename");
                String[][] expectedCheckTables = new String[][]
                       {
                        {"SYSALIASES","1"},
                        {"SYSCHECKS","1"},
                        {"SYSCOLPERMS","1"},
                        {"SYSCOLUMNS","1"},
                        {"SYSCONGLOMERATES","1"},
                        {"SYSCONSTRAINTS","1"},
                        {"SYSDEPENDS","1"},
                        {"SYSFILES","1"},
                        {"SYSFOREIGNKEYS","1"},
                        {"SYSKEYS","1"},
                        {"SYSPERMS", "1"},
						{"SYSROLES", "1"},
                        {"SYSROUTINEPERMS","1"},
                        {"SYSSCHEMAS","1"},
                        {"SYSSEQUENCES", "1"},
                        {"SYSSTATEMENTS","1"},
                        {"SYSSTATISTICS","1"},
                        {"SYSTABLEPERMS","1"},
                        {"SYSTABLES","1"},
                        {"SYSTRIGGERS","1"},
                        {"SYSUSERS","1"},
                        {"SYSVIEWS","1"},
                       };
                JDBC.assertFullResultSet(rs,expectedCheckTables); 
		//-- drop tables
		assertUpdateCount(s , 0 , "drop table pos1");
		//-- verify it again
                rs = s.executeQuery("select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from sys.systables where CAST(tabletype AS CHAR(1)) = 'S'  and CAST(tablename AS VARCHAR(128)) != 'SYSDUMMY1' order by tablename");
                JDBC.assertFullResultSet(rs, expectedCheckTables);
	}
	/**
	 * Testing The Bug5420
	 *
	 *  @exception SQLException 
	 */ 
	public void testBug5420() throws SQLException
	{
		Statement s = createStatement();
		//-- bug 5420 - constraint names in new schemas.
		assertUpdateCount(s , 0 , "create table B5420_1.t1 (c1 int not null primary key)");
		assertUpdateCount(s , 0 , "create table B5420_2.t2 (c2 int not null constraint c2pk primary key)");
		//-- two part constraint names are not allowed
		assertUpdateCount(s , 0 , "create table B5420_3.t3 (c3 int not null constraint B5420_3.c3pk primary key)");
		assertUpdateCount(s , 0 , "create table B5420_4.t4 (c4 int not null, primary key (c4))");
		assertUpdateCount(s , 0 , "create table B5420_5.t5 (c5 int not null, constraint c5pk primary key (c5))");
		//-- two part constraint names are not allowed
		assertUpdateCount(s , 0 , "create table B5420_6.t6 (c6 int not null, constraint B5420_6.c6pk primary key (c6))");
		ResultSet rs = s.executeQuery("	SELECT CAST (S.SCHEMANAME AS VARCHAR(12)), CAST (C.CONSTRAINTNAME AS VARCHAR(36)), CAST (T.TABLENAME AS VARCHAR(12)) FROM SYS.SYSCONSTRAINTS C , SYS.SYSTABLES T, SYS.SYSSCHEMAS S WHERE C.SCHEMAID = S.SCHEMAID AND C.TABLEID = T.TABLEID AND T.SCHEMAID = S.SCHEMAID AND CAST(S.SCHEMANAME AS VARCHAR(128)) LIKE 'B5420_%' ORDER BY 1,2,3");
		rs.next();
		ResultSetMetaData rsmd = rs.getMetaData();
		assertEquals(3 , rsmd.getColumnCount());
		int rows = 0;
		do
		{
			rows++;
		}while(rs.next());
		assertEquals(6 , rows);
		//-- clean up
		assertUpdateCount(s , 0 , "drop table B5420_1.t1");
		assertUpdateCount(s , 0 , "drop table B5420_2.t2");
		assertUpdateCount(s , 0 , "drop table B5420_3.t3");
		assertUpdateCount(s , 0 , "drop table B5420_4.t4");
		assertUpdateCount(s , 0 , "drop table B5420_5.t5");
		assertUpdateCount(s , 0 , "drop table B5420_6.t6");
	}

    public void testDerby5111() throws SQLException {
        final Statement s = createStatement();
        s.executeUpdate("create table t1 (t1_id integer not null, " +
                "t0_id integer not null, value varchar(75) not null)");

        try {
            s.executeUpdate("create unique index ui1 on t1 (t1_id)");
            s.executeUpdate("alter table t1 add constraint pk1 " +
                    "                       primary key (t1_id)");
            s.executeUpdate("create unique index ui2 on t1 (t0_id, value)");

            s.executeUpdate("insert into t1 values(0, 0, 'Test')");

            // The next statement tries to insert a duplicate.  It used to
            // throw an NPE before the fix.
            assertStatementError(
                    "23505", s, "insert into t1 values(1, 0, 'Test')");
        } finally {
            try { s.executeUpdate("drop table t1"); } catch (SQLException e){}
        }
    }
}