db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j..@apache.org
Subject svn commit: r520709 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/lang/UnaryArithmeticParameterTest.java functionTests/tests/lang/_Suite.java junit/JDBC.java
Date Wed, 21 Mar 2007 02:15:37 GMT
Author: jta
Date: Tue Mar 20 19:15:35 2007
New Revision: 520709

URL: http://svn.apache.org/viewvc?view=rev&rev=520709
Log:
DERBY-2458 (partial): Convert lang/unaryArithmeticDynamicParamter.java to junit.

Patch committed for Manjula Kutty <mkutty@remulak.net>.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java?view=auto&rev=520709
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java
Tue Mar 20 19:15:35 2007
@@ -0,0 +1,353 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.unaryArithmeticParameterTest
+
+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.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+	/**
+	 * This tests unary minus and unary plus as dynamic parameters in PreparedStatements.
+	 * @throws SQLException
+	 */
+
+public class UnaryArithmeticParameterTest extends BaseJDBCTestCase {
+	/**
+     * Public constructor required for running test as standalone JUnit.
+     */
+	public UnaryArithmeticParameterTest(String name) {
+		super(name);
+	}
+	/**
+     * Create a suite of tests.
+     */
+	public static Test suite() {
+		// This test should not get run under jdk13 because it uses ParameterMetaData calls
+		// to ensure that the parameter type for dynamic parameter 
+		// for unary minus and plus get set correctly from the context in which they are used.
+		if ( !JDBC.vmSupportsJDBC3())
+            return new TestSuite("empty UnaryArithmeticParameterTest - supported only from
JDBC3.0 and above");
+        else{
+            	return   TestConfiguration.defaultSuite(UnaryArithmeticParameterTest.class);
+        }
+	}
+	 /**
+     * Set the fixture up with tables 
+     */
+	protected void setUp() throws SQLException {
+		getConnection().setAutoCommit(false);
+		Statement stmt = createStatement();
+		stmt.executeUpdate("create table t1 (c11 int, c12 smallint, c13 double, c14 char(3))");
+		stmt.execute("insert into t1 values(1,1,1.1,'abc')");
+		stmt.execute("insert into t1 values(-1,-1,-1.0,'def')");
+		stmt.execute("create table t2 (c21 int)");
+		stmt.execute("insert into t2 values (-1),(-2)");
+		stmt.close();
+	}
+	/**
+     * Tear-down the fixture by removing the tables
+     */
+	protected void tearDown() throws Exception {
+		Statement stmt = createStatement();
+		stmt.executeUpdate("drop table t1");
+		stmt.executeUpdate("drop table t2");
+		stmt.close();
+		commit();
+		super.tearDown();
+	}
+	/**
+	 * Test using parameters with unary minus and unary plus
+	 * @throws SQLException
+	 */
+		public void testParametersForUnaryMinusAndPlus() throws SQLException {
+			PreparedStatement ps = prepareStatement("insert into t1 values(?,+?,-?,?)");
+			ps.setInt(1,3);
+			ps.setInt(2,3);
+			ps.setDouble(3,-3.1);
+			ps.setString(4,"ghi");
+			int[] expectedTypes={Types.INTEGER,Types.SMALLINT, Types.DOUBLE,Types.CHAR};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			ps.executeUpdate();
+			ps.setInt(1,-1);
+			ps.setInt(2,-1);
+			ps.setDouble(3,1.0);
+			ps.setString(4,"jkl");
+			ps.executeUpdate();
+			
+			ps = prepareStatement("select * from t1 where -? in (select c21 from t2)");
+			ps.setInt(1,1);
+			expectedTypes=new int[] {Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")},
+										{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")},
+										{new Integer(3),new Integer(3),new Double(3.1),new String("ghi")},
+										{new Integer(-1),new Integer(-1),new Double(-1.0),new String("jkl")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps =prepareStatement("select * from t1 where c11 = -? and c12 = +? and c13 = ?");
+			ps.setInt(1,-1);
+			ps.setInt(2,1);
+			ps.setDouble(3,1.1);
+			expectedTypes= new int[]{Types.INTEGER, Types.SMALLINT,Types.DOUBLE};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new String("abc")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			
+			ps.setShort(1,(short) 1);
+			ps.setInt(2,-1);		
+			ps.setInt(3,-1);
+			expectedRows = new Object[][]{{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")},
+										{new Integer(-1),new Integer(-1),new Double(-1.0),new String("jkl")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+			}
+		/**
+		 * Tests ABS function with Unary plus and Unary minus
+		 * @throws SQLException
+		 */
+		public void testABSWithUnaryMinusAndPlus() throws SQLException {
+			Statement s = createStatement();
+			s.execute("CREATE FUNCTION ABS_FUNCT(P1 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL
NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
+			PreparedStatement ps = prepareStatement("select * from t1 where -? = abs_funct(+?)");
+			ps.setInt(1,-1);
+			ps.setInt(2,1);
+			int[] expectedTypes= new int[]{Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")},
+										{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+			s.execute("Drop function ABS_FUNCT" );
+			s.close();
+		}	
+		/**
+		 * Tests MAX function with Unary plus and Unary minus
+		 * @throws SQLException
+		 */
+		public void testMAXWithUnaryMinusAndPlus() throws SQLException{
+			Statement s = createStatement();
+			s.execute("CREATE FUNCTION MAX_CNI(P1 INT, P2 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL
NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA");
+			PreparedStatement ps = prepareStatement("select * from t1 where -? = max_cni(-5,-1)");
+			ps.setInt(1,1);
+			int[] expectedTypes= new int[]{Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")},
+					{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			
+			ps = prepareStatement("select * from t1 where -? = max_cni(-?,+?)");
+			ps.setInt(1,-1);
+			ps.setInt(2,1);
+			ps.setInt(3,1);
+			expectedTypes= new int[]{Types.INTEGER,Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+
+			//Try the function again. But use, use sqrt(+?) & abs(-?) functions to send params
+			ps = prepareStatement("select * from t1 where -? = max_cni(abs(-?), sqrt(+?))");
+			ps.setInt(1,-2);
+			ps.setInt(2,1);
+			ps.setInt(3,4);
+			expectedTypes=new int[]{Types.INTEGER,Types.DOUBLE,Types.DOUBLE};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+			s.execute("Drop function MAX_CNI" );
+			s.close();
+			}
+		/**
+		 * Tests BETWEEN with unary minus and unary plus
+		 * @throws SQLException
+		 */
+		public void testBETWEENWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps = prepareStatement("select * from t1 where c11 between -? and +?");
+			ps.setInt(1,-1);
+			ps.setInt(2,1);
+			int[] expectedTypes= new int[]{Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+		}
+		/**
+		 * Tests NOT IN with unary minus and unary plus
+		 * @throws SQLException
+		 */
+		public void testNOTINWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps = prepareStatement("select * from t1 where +? not in (-?, +?, 2,
?)");
+			ps.setInt(1,-11);
+			ps.setInt(2,1);
+			ps.setInt(3,1);
+			ps.setInt(4,4);
+			int[] expectedTypes= new int[]{Types.INTEGER,Types.INTEGER, Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")},
+										{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+						
+			ps = prepareStatement("select * from t1 where -? not in (select c21+? from t2)");
+			ps.setInt(1,1);
+			ps.setInt(2,2);
+			expectedTypes = new int[]{Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+		}
+		/**
+		 * Tests operators with Unary plus and unary Minus
+		 * @throws SQLException
+		 */
+		public void testOperatorsWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps = prepareStatement("select * from t1 where +? < c12");
+			ps.setInt(1,0);
+			int[] expectedTypes=new int[]{Types.SMALLINT};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer(1),new Integer(1),new Double(1.1),new
String("abc")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+
+			ps = prepareStatement("select * from t1 where -? = c11 + ?");
+			ps.setInt(1,2);
+			ps.setInt(2,-1);
+			expectedTypes = new int[]{Types.INTEGER,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			expectedRows = new Object[][]{{new Integer(-1),new Integer(-1),new Double(-1.0),new String("def")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+
+			ps = prepareStatement("select * from t1 where c11 + ? = -?");
+			ps.setInt(1,-1);
+			ps.setInt(2,2);
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+
+			ps = prepareStatement("select * from t1 where c11 + c12 = -?");
+			ps.setInt(1,2);
+			expectedTypes= new int[]{Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+		}
+		/**
+		 * Tests Casting with unary plus and unary minus
+		 * @throws SQLException
+		 */
+		public void testCastWithUnaryMinusAndPlus()throws SQLException{
+			PreparedStatement ps = prepareStatement("select cast(-? as smallint), cast(+? as int)
from t1");
+			ps.setInt(1,2);
+			ps.setInt(2,2);
+			int[] expectedTypes= new int[]{Types.SMALLINT,Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			String [][] expectedRows = new String[][]{{"-2","2"},{"-2","2"}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, true);
+			ps.close();
+		}
+		/**
+		 * Tests NullIf with unary minus and unary plus
+		 * @throws SQLException
+		 */
+		public void testNullIfWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps = prepareStatement("select nullif(-?,c11) from t1");
+			ps.setInt(1,22);
+			int[] expectedTypes= new int[]{Types.INTEGER};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new String("-22")},{new String("-22")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, true);
+			ps.close();
+		}
+		/**
+		 * Tests SQRT with unary minus and unary plus
+		 * @throws SQLException
+		 */
+		public void testSQRTWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps = prepareStatement("select sqrt(-?) from t1");
+			ps.setInt(1,-64);
+			int[] expectedTypes= new int[]{Types.DOUBLE};
+			JDBC.assertParameterTypes(ps,expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new String("8.0")},{new String("8.0")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, true);
+			ps.close();
+		}
+		/**
+		 * Tests "select case when -?=c11 then -? else c12 end from t1"
+		 * @throws SQLException
+		 */
+		public void testSelectWithUnaryMinusAndPlus() throws SQLException{
+			PreparedStatement ps =prepareStatement("select case when -?=c11 then -? else c12 end from
t1");
+			ps.setInt(1,1);
+			ps.setInt(2,22);
+			int[] expectedTypes = new int[]{Types.INTEGER,Types.SMALLINT};
+			JDBC.assertParameterTypes(ps, expectedTypes);
+			Object[][] expectedRows = new Object[][]{{new Integer("1")},{new Integer("-22")}};
+			JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, false);
+			ps.close();
+		}
+		/**
+		 * Negative tests for unary minus and unary plus
+		 * @throws SQLException
+		 */
+		public void testExpectedErrors() throws SQLException{
+			assertCompileError("42X34","select * from t1 where c11 = any (select -? from t2)");
+			
+			// -?/+? at the beginning and/ at the end of where clause
+			assertCompileError("42X19","select * from t1 where -? and c11=c11 or +?");
+			
+			// -?/+? in like escape function
+			assertCompileError("42X37","select * from sys.systables where tablename like -? escape
+?");
+			
+			// -?/+? in binary timestamp function
+			assertCompileError("42X37","select timestamp(-?,+?) from t1");
+			
+			// -? in unary timestamp function
+			assertCompileError("42X36","select timestamp(-?) from t1");
+			
+			// -? in views
+			assertCompileError("42X98","create view v1 as select * from t1 where c11 = -?");
+			
+			// -? in inner join
+			assertCompileError("42X37","select * from t1 inner join t1 as t333 on -?");
+			
+			// -? by itself in where clause
+			assertCompileError("42X19","select * from t1 where -?");
+			
+			// -? is null not allowed because is null allowed on char types only
+			assertCompileError("42X37","select * from t1 where -? is null");
+			
+			// unary plus parameters on both sides of / operator
+			assertCompileError("42X35","select * from t1 where c11 = ?/-?");
+			
+			// unary plus in || operation
+			assertCompileError("42X37","select c11 || +? from t1");
+			
+			// unary minus for char column
+			assertCompileError("42X37","select * from t1 where c14 = -?");
+			
+			// unary plus for char column
+			assertCompileError("42X37","select * from t1 where c14 like +?");
+			
+					
+		}
+	}
+

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UnaryArithmeticParameterTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=520709&r1=520708&r2=520709
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Mar 20 19:15:35 2007
@@ -83,6 +83,7 @@
         suite.addTest(NullIfTest.suite());
         suite.addTest(InListMultiProbeTest.suite());
         suite.addTest(CurrentOfTest.suite());
+        suite.addTest(UnaryArithmeticParameterTest.suite());
 
         // Add the XML tests, which exist as a separate suite
         // so that users can "run all XML tests" easily.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?view=diff&rev=520709&r1=520708&r2=520709
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Tue Mar 20 19:15:35
2007
@@ -539,6 +539,30 @@
                     expectedTypes[i], rsmd.getColumnType(i+1));
         }
     }
+    /**
+     * Takes a Prepared Statement and an array of expected parameter types
+     * from java.sql.Types 
+     * and asserts that the column types in the ParamterMetaData
+     * match the number, order, and names of those
+     * in the array.
+     * @param ps PreparedStatement for which we're checking parameter names.
+     * @param expectedTypes Array of expected parameter types.
+     */
+    public static void assertParameterTypes (PreparedStatement ps,
+	        int[] expectedTypes) throws SQLException
+	    {
+		ParameterMetaData pmd = ps.getParameterMetaData();
+	        int actualCols = pmd.getParameterCount();
+
+	        Assert.assertEquals("Unexpected column count:",
+	                expectedTypes.length, pmd.getParameterCount());
+
+	        for (int i = 0; i < actualCols; i++)
+	        {
+	            Assert.assertEquals("Column types do not match for column " + (i+1),
+	                    expectedTypes[i], pmd.getParameterType(i+1));
+	        }
+	    }
     
     /**
      * Check the nullability of the column definitions for



Mime
View raw message