Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r433111 - in
 /db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting:
 functionTests/suites/ functionTests/tests/lang/ junit/
Date: Mon, 21 Aug 2006 01:11:21 -0000
To: derby-commits@db.apache.org
From: djd@apache.org
Message-Id: <20060821011121.EEC1B1A981A@eris.apache.org>

Author: djd
Date: Sun Aug 20 18:11:20 2006
New Revision: 433111

URL: http://svn.apache.org/viewvc?rev=433111&view=rev
Log:
DERBY-766 DERBY-1714 Convert largeCodeGen to a JUnit test, add it to the lang._Suite and add that to
the derbylang.runall old harness suite. Added tests for insert a large number of rows with a VALUES
clause. Test needs further improvements due to errors from DERBY-1315 and stack overflow with
a large INSERT VALUES clause.
Merge of 433085 from trunk

Removed:
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen_app.properties
Modified:
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/largeDataTests.runall
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/junit/JDBC.java

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=433111&r1=433110&r2=433111&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Sun Aug 20 18:11:20 2006
@@ -150,3 +150,4 @@
 lang/views.sql
 lang/wisconsin.java
 lang/ShutdownDatabase.java
+lang/_Suite.junit

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/largeDataTests.runall
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/largeDataTests.runall?rev=433111&r1=433110&r2=433111&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/largeDataTests.runall (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/largeDataTests.runall Sun Aug 20 18:11:20 2006
@@ -1,3 +1,2 @@
-lang/largeCodeGen.java
 largedata/lobLengthTests.java
 largedata/LobLimits.java

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=433111&r1=433110&r2=433111&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Sun Aug 20 18:11:20 2006
@@ -44,6 +44,8 @@
 	public static Test suite() {
 
 		TestSuite suite = new TestSuite("lang");
+        
+        suite.addTest(largeCodeGen.suite());
 
 		// suite.addTest(PrepareExecuteDDL.suite());
 		// suite.addTest(LangScripts.suite());

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen.java?rev=433111&r1=433110&r2=433111&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/largeCodeGen.java Sun Aug 20 18:11:20 2006
@@ -18,10 +18,15 @@
 
 package org.apache.derbyTesting.functionTests.tests.lang;
 
-import java.sql.Connection;
 import java.sql.*;
+import java.util.Random;
 
-import org.apache.derby.tools.ij;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
 
 
 // This test tries to push byte code generation to the limit.
@@ -30,42 +35,49 @@
 // There are only a few types of cases now. Other areas need to be tested such as large in clauses, etc.
 // 
 
-public class largeCodeGen
+public class largeCodeGen extends BaseJDBCTestCase
 {
 	private static boolean TEST_QUERY_EXECUTION = true;
-	private static boolean PRINT_FAILURE_EXCEPTION = false;
-	
-    public static void main(String argv[]) 
-       throws Exception
+    
+   
+    
+    public largeCodeGen(String name)
     {
-    	ij.getPropertyArg(argv); 
-        Connection con = ij.startJBMS();
-        con.setAutoCommit(false);
-        createTestTable(con);
-        testLogicalOperators(con);
-        testInClause(con);
-        testUnions(con);
-        con.commit();
-        con.close();
+        super(name);
     }
     
-    private static void createTestTable(Connection con) throws SQLException
+    public static Test suite() {
+        TestSuite suite = new TestSuite();
+        
+        // Code generation test, just invoke on embedded
+        // as the main function is to test the byte code compiler.
+        if (usingEmbedded()) {
+            suite.addTestSuite(largeCodeGen.class);
+            return new CleanDatabaseTestSetup(suite);
+        }
+        return suite;
+    }
+       
+    protected void setUp() throws SQLException
     {
-    	 Statement stmt = null;		
-    	 stmt = con.createStatement();
-    	 try {
-			stmt.executeUpdate("drop table t0 ");
-		}catch (SQLException se)
-		{
-			// drop error ok.
-			if (!se.getSQLState().equals("42Y55"))
-				throw se;
-		}	
+        getXConnection().setAutoCommit(false);
+    	Statement stmt = createStatement();
 		
 		String createSQL = 	"create table t0 " +
 		"(si smallint,i int, bi bigint, r real, f float, d double precision, n5_2 numeric(5,2), dec10_3 decimal(10,3), ch20 char(3),vc varchar(20), lvc long varchar)";
 		stmt.executeUpdate(createSQL);	
 		stmt.executeUpdate("insert into t0 values(2,3,4,5.3,5.3,5.3,31.13,123456.123, 'one','one','one')");
+        stmt.close();
+        commit();
+    }
+    
+    protected void tearDown() throws Exception
+    {
+        Statement stmt = createStatement();
+        stmt.execute("DROP TABLE T0");
+        stmt.close();
+        commit();
+        super.tearDown();
     }
     
     
@@ -74,19 +86,17 @@
 	 * The assumption is that the query will always return our one row
 	 * of data inserted into the t0 table.
 	 * 
-	 * @param con
 	 * @param testName
 	 * @param sqlBuffer  - StringBuffer with SQL Text
 	 * @param numParams  - Number of parameters
 	 * @param paramValue - Parameter value
 	 * @return true if the check fails
 	 */
-	private static boolean checkT0Query(Connection con, String testName, 
+	private boolean checkT0Query(String testName, 
 				StringBuffer sqlBuffer, int numParams, int paramValue) {
 		PreparedStatement ps;
 		try {
-			ps = con.prepareStatement(sqlBuffer.toString());
-			System.out.println("PASS: PREPARE: " + testName);
+			ps = prepareStatement(sqlBuffer.toString());
 			if (TEST_QUERY_EXECUTION)
 			{
 				for (int i = 1; i <= numParams; i++)
@@ -99,32 +109,41 @@
 				rs.close();
 			}
 			ps.close();
-			System.out.println("PASS: " + testName);
+            commit();
 			return false;
-		}catch (Exception e)
+		}catch (SQLException e)
 		{
-			reportFailure(testName, e);
+            // The top level exception is expected to be
+            // the "user-friendly" query is too complex
+            // rather than some linkage error.
+            JDBC.assertSQLState("42ZA0", e);
 			return true;
 			
 		}
 	}
 
 	/**
-     * Test many parameters in the where clause
-     * e.g. 
-	 * @param con  
+     * Test many logical operators in the where clause.  
 	 */
-	private static void testLogicalOperators(Connection con)  throws SQLException {
+	public void testLogicalOperators()  throws SQLException {
 		 
-		// svn 372388 trunk - passed @ 400
-		// Fix to DERBY-921 - passed @ 800
-		//   DERBY-921 - support 32bit branch offsets
-		 for (int count = 200; count <= 10000 ; count += 100)
+       int passCount = 0;
+		 for (int count = 700; count <= 10000 ; count += 100)
 		 {
-			 // keep testing until it fails with linkage error
-			 if (testLogicalOperators(con, count))
+			 // keep testing until it fails
+			 if (logicalOperators(count))
 				 break;
+             
+             passCount = count;
 		 }
+         
+        // svn 372388 trunk - passed @ 400
+        // Fix to DERBY-921 - passed @ 800
+        // DERBY-921 - support 32bit branch offsets
+        assertEquals("logical operators change from previous limit",
+                800, passCount);
+        
+     
 		 // 10,000 causes Stack overflow and database corruption
 		 //testLogicalOperators(con, 10000);
 	}
@@ -133,11 +152,9 @@
 	/**
 	 * Tests numParam parameter markers in a where clause
 	 * 
-	 * @param con          
 	 * @param  numOperands 
 	 */
-	private static boolean testLogicalOperators(Connection con, 
-				int numOperands) throws SQLException {
+	private boolean logicalOperators(int numOperands) throws SQLException {
 		
 		// First with parameters
 		String pred =  "(si = ? AND si = ? )";
@@ -148,26 +165,32 @@
 		{
 			sqlBuffer.append(" OR " + pred);
 		}
-		return checkT0Query(con, testName, sqlBuffer, numOperands, 2);
+		return checkT0Query(testName, sqlBuffer, numOperands, 2);
 		
 		
 		
 		
 	}
 	
-	private static void testInClause(Connection con)  throws SQLException {
+	public void testInClause()  throws SQLException {
 	  
 		// DERBY-739 raised number of parameters from 2700 to 3400
         // svn 372388 trunk - passed @ 3400
-        // fixes for DERBY-766 to split methods with individual statements
-        // bumps the limit to 98,000 parameters.
-        testInClause(con, 3400);
+        // So perform a quick check there.
+        assertFalse("IN clause with 3400 parameters ", inClause(3400));
+        
+        int passCount = 0;
 		 for (int count = 97000; count <= 200000 ; count += 1000)
 		 {
-			 // keep testing until it fails.
-			 if (testInClause(con, count))
+ 			 // keep testing until it fails.
+			 if (inClause(count))
 			 	break;
+             passCount = count;
 		 }
+         
+        // fixes for DERBY-766 to split methods with individual statements
+        // bumps the limit to 98,000 parameters.
+        assertEquals("IN clause change from previous limit", 98000, passCount);
 	}	
 	
 	/**
@@ -178,7 +201,7 @@
 	 * @return true if the test fails
 	 * @throws SQLException
 	 */
-	private static boolean testInClause(Connection con, int numParams) throws SQLException {
+	private boolean inClause(int numParams) throws SQLException {
 		String testName = "IN clause with " + numParams + " parameters";
 		StringBuffer sqlBuffer = new StringBuffer((numParams * 20) + 512);
 		sqlBuffer.append("SELECT * FROM T0 WHERE SI IN ("  );
@@ -187,27 +210,14 @@
 			sqlBuffer.append("?, ");
 		}
 		sqlBuffer.append("?)");
-		return checkT0Query(con, testName, sqlBuffer, numParams, 2); 	
+		return checkT0Query(testName, sqlBuffer, numParams, 2); 	
 	}
 	
-	private static void testUnions(Connection con) throws Exception
-	{
-		Statement stmt = null;
-        PreparedStatement pstmt = null; 
-        createTestTable(con);
-		
+	public void testUnions() throws SQLException
+	{ 		
 		String viewName = "v0";		
-		stmt = con.createStatement();
-
-		
-		try {
-			stmt.executeUpdate("drop view " + viewName);
-		}catch (SQLException se)
-		{
-			// drop error ok.
-		}
-
-				  
+		Statement stmt = createStatement();
+        
 		StringBuffer createView = new StringBuffer("create view " + viewName + 
 												   " as select * from t0 " );
 		for (int i = 1; i < 100; i ++)
@@ -216,24 +226,39 @@
 		}
 		//System.out.println(createViewString);
 		stmt.executeUpdate(createView.toString());
+        commit();
 		
-		// svn 372388 trunk - passed @ 900
-		for (int count = 800; count <= 10000; count += 100)
+       int passCount = 0;
+		for (int count = 1000; count <= 1000; count += 1000)
 		{
-			// keep testing until it fails
-			if (largeUnionSelect(con, viewName, count))
+ 			// keep testing until it fails
+			if (largeUnionSelect(viewName, count))
 				break;
+            passCount = count;
+           
 		}
-		// 10000 gives a different constant pool error
-		largeUnionSelect(con, viewName, 10000);
+        
+        // 10000 gives a different constant pool error
+        // DERBY-1315 gives out of memory error.
+        //assertTrue("10000 UNION passed!",
+        //        largeUnionSelect(viewName, 10000));
+
+        createStatement().executeUpdate("DROP VIEW " + viewName);
+
+        // svn 372388 trunk - passed @ 900
+        // trunk now back to 700
+        //
+        assertEquals("UNION operators change from previous limit",
+                1000, passCount);
+        
+        
     }
     
-    private static boolean largeUnionSelect(Connection con, String viewName,
-    		int numUnions) throws Exception
+    private boolean largeUnionSelect(String viewName,
+    		int numUnions) throws SQLException
 	{
 
     	// There are 100 unions in each view so round to the nearest 100
-    	String testName = "SELECT with " + numUnions/100 * 100 + " unions";
 		
 		String unionClause = " UNION ALL (SELECT * FROM " + viewName + ")";
 
@@ -251,12 +276,10 @@
 		// Ready to execute the problematic query 
 		String selectSQL = selectSQLBuffer.toString();
 		//System.out.println(selectSQL);
-        PreparedStatement pstmt = con.prepareStatement(selectSQL);
-        System.out.println("PASS: PREPARE: " + testName);
+        PreparedStatement pstmt = prepareStatement(selectSQL);
         if (largeCodeGen.TEST_QUERY_EXECUTION)
         {
 	        ResultSet rs = pstmt.executeQuery();
-			int numRowsExpected = (numUnions/100 * 100);
 			int numRows = 0;
 			while (rs.next())
 			{
@@ -264,15 +287,19 @@
 				if ((numRows % 100) == 0)
 				checkRowData(rs);
 			}
-			System.out.println("PASS: EXECUTE " + testName + " Row data check ok");
-	        con.commit();
+            rs.close();
+	        commit();
         }
         pstmt.close();
         return false;
      
 		} catch (SQLException sqle)
 		{
-			reportFailure(testName, sqle);
+            // The top level exception is expected to be
+            // the "user-friendly" query is too complex
+            // rather than some linkage error.
+            JDBC.assertSQLState("42ZA0", sqle);
+
 			return true;
 			
 		}
@@ -280,41 +307,113 @@
       }
 
 	// Check the data on the positioned row against what we inserted.
-	private static void checkRowData(ResultSet rs) throws Exception
+	private static void checkRowData(ResultSet rs) throws SQLException
 	{
 		//" values(2,3,4,5.3,5.3,5.3,31.13,123456.123, 'one','one','one')");
 		String[] values = {"2", "3", "4", "5.3","5.3","5.3","31.13","123456.123",
 						   "one","one","one"};
 		for (int i = 1; i <= 11; i ++)
 		{
-			String rsValue = rs.getString(i);
-			String expectedValue = values[i - 1];
-			if (!rsValue.equals(values[i-1]))
-				throw new Exception("Result set data value: " + rsValue +
-									" does not match " + values[i-1] +
-					                " for column " + i);				
+            assertEquals("Result set data value: ",
+                    values[i-1], rs.getString(i));
 		}
 	}
+    
+    /**
+     * Test an INSERT statement with a large number of rows in the VALUES clause.
+     * Reported as DERBY-1714.
+     * @throws SQLException 
+     *
+     */
+    public void testInsertValues() throws SQLException {
+       int passCount = 0;
+        for (int count = 1500; count <= 1700; count += 200) {
+            // keep testing until it fails
+            if (insertValues(count))
+                break;
+            passCount = count;
 
-	/**
-	 * Show failure message and exception stack trace
-	 * @param testName
-	 * @param e
-	 */
-	private static void reportFailure(String testName, Exception e)
-	{
-		System.out.println("FAILED QUERY: " + testName +".");
-		if (e instanceof SQLException)
-		{
-			SQLException se = (SQLException) e;
-			while (se != null  && PRINT_FAILURE_EXCEPTION)
-			{
-				se.printStackTrace(System.out);
-				se = se.getNextException();
-			}
-		}	
-		else e.printStackTrace();
-	
-	}
-	
+        }
+
+        // Final fixes for DERBY-766 pushed the limit to 1700
+        // Beyond that a StackOverflow occurs.
+        assertEquals("INSERT VALUES change from previous limit", 1700, passCount);
+    }
+
+    /**
+     * Create a large insert statement with rowCount rows all with
+     * constants. Prepare and execute it and then rollback to leave
+     * the table unchanged.
+     * @param rowCount
+     * @return
+     * @throws SQLException
+     */
+    private boolean insertValues(int rowCount) throws SQLException {
+        Random r = new Random(3457245435L);
+
+        StringBuffer insertSQL = new StringBuffer(
+                "INSERT INTO T0(SI,I,BI,R,F,D,N5_2,DEC10_3,CH20,VC,LVC) VALUES\n");
+
+        for (int i = 0; i < rowCount; i++) {
+            if (i != 0)
+                insertSQL.append(',');
+
+            insertSQL.append('(');
+
+            insertSQL.append(((short) r.nextInt()));
+            insertSQL.append(',');
+            insertSQL.append(i);
+            insertSQL.append(',');
+            insertSQL.append(r.nextLong());
+            insertSQL.append(',');
+
+            insertSQL.append(r.nextFloat());
+            insertSQL.append(',');
+            insertSQL.append(r.nextFloat());
+            insertSQL.append(',');
+            insertSQL.append(r.nextDouble());
+            insertSQL.append(',');
+
+            insertSQL.append("462.54");
+            insertSQL.append(',');
+            insertSQL.append("9324324.34");
+            insertSQL.append(',');
+
+            insertSQL.append('\'');
+            insertSQL.append("c");
+            insertSQL.append(r.nextInt() % 10);
+            insertSQL.append('\'');
+            insertSQL.append(',');
+
+            insertSQL.append('\'');
+            insertSQL.append("vc");
+            insertSQL.append(r.nextInt() % 1000000);
+            insertSQL.append('\'');
+            insertSQL.append(',');
+
+            insertSQL.append('\'');
+            insertSQL.append("lvc");
+            insertSQL.append(r.nextInt());
+            insertSQL.append('\'');
+
+            insertSQL.append(')');
+
+            insertSQL.append('\n');
+        }
+
+        try {
+            PreparedStatement ps = prepareStatement(insertSQL.toString());
+            assertEquals("Incorrect update count", rowCount, ps.executeUpdate());
+            ps.close();
+            rollback();
+            return false;
+        } catch (SQLException e) {
+            // The top level exception is expected to be
+            // the "user-friendly" query is too complex
+            // rather than some linkage error.
+            JDBC.assertSQLState("42ZA0", e);
+        }
+
+        return true;
+    }
 }

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=433111&r1=433110&r2=433111&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/junit/JDBC.java Sun Aug 20 18:11:20 2006
@@ -299,7 +299,7 @@
 	 * rows and columns. Each column is accessed using
 	 * getString() and asserted that the returned value
 	 * matches the state of ResultSet.wasNull().
-	 * Provides simple testing of the ResultSet when then contents
+	 * Provides simple testing of the ResultSet when the contents
 	 * are not important.
 	 * @param rs
 	 * @throws SQLException
@@ -318,6 +318,16 @@
 		}
 		rs.close();
 	}
+    
+    /**
+     * Assert a SQL state is the expected value.
+     * @param expected Expected SQLState.
+     * @param sqle SQLException caught
+     */
+    public static void assertSQLState(String expected, SQLException sqle)
+    {
+        Assert.assertEquals("Unexpected SQL State", expected, sqle.getSQLState());
+    }
 	
 	/**
 	 * Escape a non-qualified name so that it is suitable