declareglobaltemptablejava.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.declareGlobalTempTableJava

   Copyright 2003, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed 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.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;                                                                                     
import java.sql.SQLException;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;

/**
 * Test for declared global temporary tables introduced in Cloudscape 5.2
 * The temp table tests with holdable cursor and savepoints are in declareGlobalTempTableJavaJDBC30 class.
 * The reason for a different test class is that the holdability and savepoint support is under jdk14 and higher.
 * But we want to be able to run the non-jdk14 specific tests under all the jdks we support and hence splitting
 * the tests into 2 different classes
 */


public class declareGlobalTempTableJava {

	/*
	** There is a small description prior to each sub-test describing what is being tested.
	*/
	public static void main(String[] args) {
		boolean		passed = true;

		Connection con1 = null, con2 = null;
		Statement  s = null;

		/* Run all parts of this test, and catch any exceptions */
		try {
			System.out.println("Test declaredGlobalTempTableJava starting");

			/* Load the JDBC Driver class */
			// use the ij utility to read the property file and
			// make the initial connection.
      
			ij.getPropertyArg(args);
			con1 = ij.startJBMS();
			con2 = ij.startJBMS();

			s = con1.createStatement();
			con1.setAutoCommit(false);
			con2.setAutoCommit(false);

			/* Test various schema and grammar related cases */
			passed = testSchemaNameAndGrammar(con1, s) && passed;

			/* Test various unallowed operations */
			passed = testOtherOperations(con1, s, con2) && passed;

			con1.close();
			con2.close();

		} catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception "+e);
			JDBCDisplayUtil.ShowException(System.out, e);
			e.printStackTrace();
			passed = false;
		}

		if (passed)
			System.out.println("PASS");

		System.out.println("Test declaredGlobalTempTable finished");
	}

	/**
	 * Test various schema and grammar related cases
	 *
	 * @param conn	The Connection
	 * @param s		A Statement on the Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean testSchemaNameAndGrammar(Connection con1, Statement s)
					throws SQLException {
		boolean passed = true;

		try
		{
			System.out.println("TEST1 : global temporary tables can only be in SESSION schema");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE APP.t2(c21 int) on commit delete rows not logged");

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST1 FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			con1.commit();
			System.out.println("TEST1 PASSED");
		}

		try
		{
			System.out.print("TEST2A : Declaring a global temporary table while in SYS schema will pass ");
			System.out.println("because temp tables always go in SESSION schema and never in default schema");

			s.executeUpdate("set schema SYS");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t2(c21 int) on commit delete rows not logged");

			con1.commit();
			System.out.println("TEST2A PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: " + e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST2A FAILED");
		}

		try
		{
			System.out.println("TEST2B : Drop the declared global temporary table declared in TEST2A while in schema SYS");

			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("set schema APP");

			con1.commit();
			System.out.println("TEST2B PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: " + e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST2B FAILED");
		}

		try
		{
			System.out.println("TEST3A : positive grammar tests for DECLARE command");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tA(c1 int) not logged");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tB(c1 int) on commit delete rows not logged");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tC(c1 int) not logged on commit delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tD(c1 int) on commit preserve rows not logged");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tE(c1 int) not logged on commit preserve rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tF(c1 int) on rollback delete rows not logged");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tG(c1 int) not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tH(c1 int) on commit preserve rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tI(c1 int) not logged on commit preserve rows on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tJ(c1 int) not logged on rollback delete rows on commit preserve rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tK(c1 int) on commit delete rows not logged on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tL(c1 int) not logged on commit delete rows on rollback delete rows");
			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE tM(c1 int) not logged on rollback delete rows on commit delete rows");

			s.executeUpdate("DROP TABLE SESSION.tA");
			s.executeUpdate("DROP TABLE SESSION.tB");
			s.executeUpdate("DROP TABLE SESSION.tC");
			s.executeUpdate("DROP TABLE SESSION.tD");
			s.executeUpdate("DROP TABLE SESSION.tE");
			s.executeUpdate("DROP TABLE SESSION.tF");
			s.executeUpdate("DROP TABLE SESSION.tG");
			s.executeUpdate("DROP TABLE SESSION.tH");
			s.executeUpdate("DROP TABLE SESSION.tI");
			s.executeUpdate("DROP TABLE SESSION.tJ");
			s.executeUpdate("DROP TABLE SESSION.tK");
			s.executeUpdate("DROP TABLE SESSION.tL");
			s.executeUpdate("DROP TABLE SESSION.tM");
			con1.commit();
			System.out.println("TEST3A PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3A FAILED");
		}

		try
		{
			System.out.println("TEST3B : negative grammar tests for DECLARE command");

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int)");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table without NOT LOGGED. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED NOT LOGGED");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with multiple NOT LOGGED. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON COMMIT PRESERVE ROWS ON COMMIT DELETE ROWS");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with multiple ON COMMIT. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK DELETE ROWS ON ROLLBACK DELETE ROWS");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with multiple ON ROLLBACK. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) NOT LOGGED ON ROLLBACK PRESERVE ROWS");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table with syntax error ON ROLLBACK PRESERVE ROWS. " + e.getMessage());
			}

			try {
				s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE t1(c11 int) ON ROLLBACK DELETE ROWS ON COMMIT PRESERVE ROWS");
			} catch (Throwable e)
			{
				System.out.println("  Expected exception. Attempted to declare a temp table without NOT LOGGED. " + e.getMessage());
			}

			con1.commit();
			System.out.println("TEST3B PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST3B FAILED");
		}

		return passed;
	}

	/**
	 * Test various other operations on declared global temporary tables
	 *
	 * @param con1	Connection to the database
	 * @param s		A Statement on the Connection
	 * @param con2	Another Connection to the database
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean testOtherOperations(Connection con1, Statement s, Connection con2)
					throws SQLException {
		boolean passed = true;

		try
		{
			System.out.println("TEST4A : ALTER TABLE not allowed on global temporary tables");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) not logged on commit delete rows");
			s.executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST4A FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST4A PASSED");
		}

		try
		{
			System.out.println("TEST4B : ALTER TABLE on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
			s.executeUpdate("ALTER TABLE SESSION.t2 add column c22 int");
			s.executeUpdate("DROP TABLE SESSION.t2");
			s.executeUpdate("drop schema SESSION restrict");

			con1.commit();
			System.out.println("TEST4B PASSED");
		} catch (Throwable e)
		{
			System.out.println("Unexpected message: "+ e.getMessage());
			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST4B FAILED");
		}

		try
		{
			System.out.println("TEST5A : LOCK TABLE not allowed on global temporary tables");

			s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int) on commit delete rows not logged");
			s.executeUpdate("LOCK TABLE SESSION.t2 IN SHARE MODE");

			con1.rollback();
			passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
			System.out.println("TEST5A FAILED");
		} catch (Throwable e)
		{
			System.out.println("Expected message: "+ e.getMessage());
			s.executeUpdate("DROP TABLE SESSION.t2");
			con1.commit();
			System.out.println("TEST5A PASSED");
		}

		try
		{
			System.out.println("TEST5B : LOCK TABLE on physical table in SESSION schema should work");

			s.executeUpdate("CREATE schema SESSION");
			s.executeUpdate("CREATE TABLE SESSION.t2(c21 int)");
			s.executeUpdate("LOCK TABLE SESSION.t2 IN EXCLUSIVE MODE");
			s.executeUpdate("DROP TABLE SESSION.t2");