dbmanagerlimits.java

derby database source code.good for you.

/*

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

   Copyright 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.*;

import org.apache.derby.tools.ij;
import org.apache.derby.iapi.reference.Limits;
import org.apache.derbyTesting.functionTests.util.Formatters;

/**
  Test various data manager limits like in db2 here.
 */
public class dbManagerLimits
{

	public static void main (String[] argv) throws Throwable
	{
		ij.getPropertyArg(argv);
		Connection conn = ij.startJBMS();

		testStringAndHexConstants(conn);
		testMostColumnsInTable(conn);
		testMostColumnsInView(conn);
		testMostElementsInSelectList(conn);
		testMostElementsInOrderBy(conn);
		testMostParametersInStoredProcedures(conn);

		//not running Group By test because it gets out of memory error
		//testMostElementsInGroupBy(conn);

		//not running indexes test because it doesn't finish even after running for over 2 hours
		//ALSO, IF WE EVER ENABLE THIS TEST IN FUTURE, WE NEED TO REWRITE THE TEST SO THAT WE TRY TO CREATE OVER
		//32767 *DIFFERENT* INDEXES. AS PART OF DB2 COMPATIBILITY WORK, BUG - 5685 DISALLOWS CREATION OF AN INDEX
		//ON A COLUMN THAT ALREADY HAS A PRIMARY KEY OR UNIQUE CONSTRAINT ON IT.
		//testMostIndexesOnTable(conn);
	}

	public static void testStringAndHexConstants( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - maximum length of character constant is 32672 and that of hex constant is 16336");
			String stringConstant32671 = Formatters.repeatChar("a",32671);
			String hexConstant16334 = Formatters.repeatChar("a",16334);
			Statement s = conn.createStatement();
			s.executeUpdate("create table t1 (c11 long varchar, c12 long varchar for bit data)");

			System.out.println("First testing less than maximum constant lengths through insert statement");
			s.executeUpdate("insert into t1(c11) values ('" +  stringConstant32671 + "')");
			s.executeUpdate("insert into t1(c12) values (X'" +  hexConstant16334 + "')");
      
			System.out.println("Next testing less than maximum constant lengths through values");
			s.execute("values ('" +  stringConstant32671 + "')");
			s.execute("values (X'" +  hexConstant16334 + "')");

			System.out.println("Next testing maximum constant lengths through insert statement");
			s.executeUpdate("insert into t1(c11) values ('" +  stringConstant32671 + "a')");
			s.executeUpdate("insert into t1(c12) values (X'" +  hexConstant16334 + "ab')");
      
			System.out.println("Next testing maximum constant lengths through values");
			s.execute("values ('" +  stringConstant32671 + "a')");
			s.execute("values (X'" +  hexConstant16334 + "ab')");

			System.out.println("Next testing maximum constant lengths + 1 through insert statement");
			try {
				s.executeUpdate("insert into t1(c11) values ('" +  stringConstant32671 + "ab')");
				System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("insert into t1(c12) values (X'" +  hexConstant16334 + "abcd')");
				System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Next testing maximum constant lengths + 1 through values");
			try {
				s.executeUpdate("values ('" +  stringConstant32671 + "ab')");
				System.out.println("FAIL - should have gotten string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("values (X'" +  hexConstant16334 + "abcd')");
				System.out.println("FAIL - should have gotten string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Next testing maximum constant lengths + n through insert statement");
			try {
				s.executeUpdate("insert into t1(c11) values ('" +  stringConstant32671 + "bcdef')");
				System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("insert into t1(c12) values (X'" +  hexConstant16334 + "abcdef')");
				System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Next testing maximum constant lengths + n through values");
			try {
				s.executeUpdate("values ('" +  stringConstant32671 + "bcdef')");
				System.out.println("FAIL - should have gotten string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			try {
				s.executeUpdate("values (X'" +  hexConstant16334 + "abcdef')");
				System.out.println("FAIL - should have gotten string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54002"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Next testing odd number of hex digits in a hex constant through insert statement");
			try {
				s.executeUpdate("insert into t1(c12) values (X'" +  hexConstant16334 + "a')");
				System.out.println("FAIL - should have gotten hex constant invalid string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42606"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			System.out.println("And finally testing odd number of hex digits in a hex constant through values statement");
			try {
				s.executeUpdate("values (X'" +  hexConstant16334 + "a')");
				System.out.println("FAIL - should have gotten string constant too long error for this values statement");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42606"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop table t1");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testMostColumnsInTable( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most columns allowed in a table");

			StringBuffer sbTableElements = new StringBuffer();
			String tempString = new String();
			int i = 0;
			sbTableElements.append("create table t1 (");
			for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE-2; i++)
				sbTableElements.append("c" + i +" int, ");

			Statement s = conn.createStatement();
			System.out.println("First create a table with one column less than maximum allowed number of columns");
			tempString = (sbTableElements.toString()).concat("c" + i + " int)");
			s.executeUpdate(tempString);
			System.out.println("  Try alter table on it to have table with maximum allowed number of columns");
			s.executeUpdate("alter table t1 add column c" + (i+1) + " int");
			System.out.println("  Try another alter table to have table with one column more than maximum allowed number of columns");
			try {
				s.executeUpdate("alter table t1 add column c" + (i+2) + " int");
				System.out.println("FAIL - The alter table should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			s.executeUpdate("drop table t1");

			System.out.println("Next create a table with maximum allowed number of columns");
			tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int)");
			s.executeUpdate(tempString);
			System.out.println("  Try alter table to have table with more columns than maximum allowed number of columns");
			try {
				s.executeUpdate("alter table t1 add column c" + (i+2) + " int");
				System.out.println("FAIL - The alter table should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			//just some basic sanity check 
			DatabaseMetaData met = conn.getMetaData();
			getCount(met.getColumns("", "APP", "T1", null));
			s.executeUpdate("insert into t1(c1, c2) values (1,1)");
			s.executeUpdate("drop table t1");

			System.out.println("Next create a table with one column more than maximum allowed number of columns");
			tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int, c" + (i+2) + " int)");
			try {
				s.executeUpdate(tempString);
				System.out.println("FAIL - The create table should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Finally, create a table with 2 columns more than maximum allowed number of columns");
			tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int, c" + (i+2) + " int, c" + (i+3) + " int)");
			try {
				s.executeUpdate(tempString);
				System.out.println("FAIL - The create table should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	private static void getCount( ResultSet s) throws Throwable
	{
		int counter = 0; // Display data, fetching until end of the result set
		while (s.next())
			counter++;
		System.out.println("Found " + counter + " columns/parameters through meta data");
	}

	public static void testMostColumnsInView( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most columns allowed in a view");

			StringBuffer sbValuesClause = new StringBuffer();
			StringBuffer sbViewColumnNames = new StringBuffer();
			String tempString = new String();
			int i = 0;
			for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-2; i++) {
				sbValuesClause.append(1 + ", ");
				sbViewColumnNames.append("c" + i + ", ");
			}

			Statement s = conn.createStatement();
			System.out.println("First create a view with one column less than maximum allowed number of columns");
			tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ") as values (" + sbValuesClause.toString() + "1)";
			s.executeUpdate(tempString);
			s.executeUpdate("drop view v1");

			System.out.println("Next create a view with maximum allowed number of columns");
			tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ", c" + (i+1)+ ") as values (" + sbValuesClause.toString() + "1,1)";
			s.executeUpdate(tempString);
			//just some basic sanity check 
			DatabaseMetaData met = conn.getMetaData();
			getCount(met.getColumns("", "APP", "V1", null));
			s.executeUpdate("drop view v1");

			System.out.println("Next create a view with one column more than that maximum allowed number of columns");