Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r566359 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/jdbc/
 testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/
 testing/org/apache/derbyTesting/junit/ tools/org/apache/derby/impl/tools/ij/
Date: Wed, 15 Aug 2007 22:21:35 -0000
To: derby-commits@db.apache.org
From: kmarsden@apache.org
Message-Id: <20070815222136.D1F1F1A981C@eris.apache.org>

Author: kmarsden
Date: Wed Aug 15 15:21:32 2007
New Revision: 566359

URL: http://svn.apache.org/viewvc?view=rev&rev=566359
Log:
DERBY-3000 - getTables() call with 10.3 causes java.sql.SQLException: The parameter position '8' is out of range. The number of parameters for this prepared statement is '7'.

DERBY-1790 - DatabaseMetaData.getTables() with passed in table type of SYNONYM returns system tables and not SYNONYMS


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java?view=diff&rev=566359&r1=566358&r2=566359
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java Wed Aug 15 15:21:32 2007
@@ -1730,43 +1730,44 @@
 		//then the "if" statement below will use those types values
 		//for ?s. If there are still some ?s in the IN list that are left 
 		//with unassigned values, then we will set those ? to NULL.
-		//eg if getTables is called to only look for table types 'S' and 
-		//'A', then 'S' will be used for first ? in TABLETYPE IN (?, ?, ?, ?)
-		//'A' will be used for second ? in TABLETYPE IN (?, ?, ?, ?) and
-		//NULL will be used for third and fourth ?s in 
+		// So paramter 4 will be "T" for TABLE, 5 will be "V" for VIEW, 6 will be
+		// "A" for SYNONYM, 7 will be "S" for system table in 
 		//TABLETYPE IN (?, ?, ?, ?)
-		//If the user hasn't asked for any specific table types, then the
-		//"else" statement below will kick in. When the control comes to 
-		//"else" statement, it means that the user wants to see all the
-		//table types supported by Derby. And hence, we simply set first
-		//? to 'T', second ? to 'S', third ? to 'V' and fourth ? to 'A'.
-		//When a new table type is added to Derby in future, we will have
-		//to do another setString for that in the "else" statement for that
-		//new table type.
-		if (types != null  &&  types.length >= 1) {
-			int i=0;
-			final int numberOfTableTypesInDerby = 4;
-			for (; i<types.length; i++){
-				/*
-				 * Let's assume for now that the table type first char 
-				 * corresponds to JBMS table type identifiers.
-				 * 
-				 * The reason I have i+4 is because there are already 3 ?s in
-				 * the getTables sql before the ?s in the IN clause. Hence
-				 * setString for table types should be done starting 4th 
-				 * parameter.
-				 */
-				s.setString(i+4, types[i].substring(0, 1));					
-			}
-			for (; i<numberOfTableTypesInDerby; i++) {
-				s.setNull(i+4, Types.CHAR);
-			}
-		} else {
-			s.setString(4, "T");
-			s.setString(5, "S");
-			s.setString(6, "V");
-			s.setString(7, "A");				
+		// If the user hasn't asked for any specific table types then all
+		// four values will be set.
+		// When a new table type is added to Derby we will have to add a 
+		// parameter to the metadata statement and handle it here.
+		
+		// Array for type parameters
+		final int numberOfTableTypesInDerby = 4;
+		if (types == null)  {// null means all types 
+			types = new String[] {"TABLE","VIEW","SYNONYM","SYSTEM TABLE"};
 		}
+		String[] typeParams = new String[numberOfTableTypesInDerby];
+		for (int i=0; i < numberOfTableTypesInDerby;i++)
+			typeParams[i] = null;
+		
+		for (int i = 0; i<types.length; i++){
+			if ("TABLE".equals(types[i]))
+				typeParams[0] = "T";
+			else if ("VIEW".equals(types[i]))
+				typeParams[1] = "V";
+			else if ("SYNONYM".equals(types[i]))
+				typeParams[2] = "A";
+			else if ("SYSTEM TABLE".equals(types[i]) ||
+					"SYSTEM_TABLE".equals(types[i])) // Keep SYSTEM_TABLE since this is how we have been testing
+					typeParams[3] = "S";
+			// If user puts in other types we simply ignore.
+			}
+		
+		// 	TABLETYPE IN (?,?,?,?) starts at parameter 4 so we add 4
+		// Set to value passed in or null if no value was given.
+		for (int i=0; i < numberOfTableTypesInDerby; i++)
+			if (typeParams[i] == null)
+				s.setNull(i+4,Types.CHAR);
+			else
+				s.setString(i+4,typeParams[i]);	
+					
 		return s.executeQuery();
 	}
 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java?view=auto&rev=566359
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java Wed Aug 15 15:21:32 2007
@@ -0,0 +1,97 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.DMDBugsTest
+
+   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.jdbcapi;
+
+import java.io.IOException;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+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;
+
+public class DMDBugsTest extends BaseJDBCTestCase {
+
+	public DMDBugsTest(String name) {
+		super(name);
+		// TODO Auto-generated constructor stub
+	}
+	
+	public void testDerby3000() throws SQLException, IOException {
+		ResultSet rs;
+		// Derby-3000 make sure we process only valid TableType values and
+		// process them correctly.
+		DatabaseMetaData dmd = getConnection().getMetaData();
+	
+		Statement s = createStatement();
+		s.executeUpdate("CREATE TABLE APP.TAB (i int)");
+		s.executeUpdate("CREATE VIEW  APP.V  as SELECT * FROM TAB");
+		s.executeUpdate("CREATE SYNONYM TSYN FOR APP.TAB");
+	
+		String[] withInvalidTableTypes = {"SYNONYM","TABLE","VIEW",
+		"GLOBAL TEMPORARY"};
+		// just ignore invalid types
+		rs = dmd.getTables( "%", "%", "%", withInvalidTableTypes);			
+		JDBC.assertFullResultSet(rs,
+			new String[][] {{"","APP","TSYN","SYNONYM","",null,null,null,null,null},
+			{"","APP","TAB","TABLE","",null,null,null,null,null},
+			{"","APP","V","VIEW","",null,null,null,null,null}});
+
+
+		rs = dmd.getTables("%", "%", "%", new String[] {"GLOBAL TEMPORARY"});
+		JDBC.assertEmpty(rs);
+		
+		rs = dmd.getTables("%", "%", "%", new String[] {"VIEW"});
+		JDBC.assertUnorderedResultSet(rs, new String[][] 
+		            {{"","APP","V","VIEW","",null,null,null,null,null}});
+
+		
+		rs = dmd.getTables("%", "%", "%", new String[] {"TABLE"});
+		JDBC.assertUnorderedResultSet(rs,new String[][]
+		          {{"","APP","TAB","TABLE","",null,null,null,null,null}} );
+		
+		rs = dmd.getTables("%", "%", "%", new String[] {"SYNONYM"});
+		JDBC.assertUnorderedResultSet(rs, new String[][]
+	                  {{"","APP","TSYN","SYNONYM","",null,null,null,null,null}});
+
+		rs = dmd.getTables( "%", "%", "%", new String[] {"SYSTEM TABLE"});
+		assertEquals(19, JDBC.assertDrainResults(rs));
+		s.executeUpdate("DROP VIEW APP.V");
+		s.executeUpdate("DROP TABLE APP.TAB");
+		s.executeUpdate("DROP SYNONYM APP.TSYN");
+	}
+	   
+		
+	/* Default suite for running this test.
+	 */
+	public static Test suite() {
+		TestSuite suite = new TestSuite("DMDBugsTest");
+		suite.addTest(
+				TestConfiguration.defaultSuite(DMDBugsTest.class));
+		return suite;
+	        	
+	}
+}
\ No newline at end of file

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java?view=diff&rev=566359&r1=566358&r2=566359
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java Wed Aug 15 15:21:32 2007
@@ -81,6 +81,7 @@
         suite.addTest(BlobUpdatableStreamTest.suite());
         suite.addTest(AIjdbcTest.suite());
         suite.addTest(LargeDataLocksTest.suite());
+        suite.addTest(DMDBugsTest.suite());
 
         // Old harness .java tests that run using the HarnessJavaTest
         // adapter and continue to use a single master file.

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=566359&r1=566358&r2=566359
==============================================================================
--- 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 Wed Aug 15 15:21:32 2007
@@ -66,11 +66,10 @@
     public static final String[] GET_TABLES_VIEW = new String[] {"VIEW"};
     /**
      * Constant to pass to DatabaseMetaData.getTables() to fetch
-     * just synonyms. Need work around for DERBY-1790 where
-     * passing a table type of SYNONYM fails.
+     * just synonyms.
      */
     public static final String[] GET_TABLES_SYNONYM =
-        new String[] {"AA_DERBY-1790-SYNONYM"};
+        new String[] {"SYNONYM"};
     
     /**
      * Types.SQLXML value without having to compile with JDBC4.

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj?view=diff&rev=566359&r1=566358&r2=566359
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj Wed Aug 15 15:21:32 2007
@@ -1747,7 +1747,7 @@
 		else if(v!=null)
 			types = new String[] { "VIEW" };
 		else
-			types = new String[] { "ALIAS" };
+			types = new String[] { "SYNONYM" };
 		return showTables(schema, types);
 	}
 |	<SHOW> <INDEXES>