Author: rhillegas
Date: Tue Dec 22 16:35:06 2009
New Revision: 893224
URL: http://svn.apache.org/viewvc?rev=893224&view=rev
Log:
DERBY-651: Adjust JDBC metadata to account for UDTs.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
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?rev=893224&r1=893223&r2=893224&view=diff
==============================================================================
--- 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
Tue Dec 22 16:35:06 2009
@@ -88,6 +88,8 @@
public class EmbedDatabaseMetaData extends ConnectionChild
implements DatabaseMetaData, java.security.PrivilegedAction {
+ private static final int ILLEGAL_UDT_TYPE = 0;
+
/*
** Property and values related to using
** stored prepared statements for metatdata.
@@ -3048,24 +3050,28 @@
*/
public ResultSet getUDTs(String catalog, String schemaPattern,
String typeNamePattern, int[] types)
- throws SQLException {
+ throws SQLException
+ {
//we don't have support for catalog names
- //we don't have java class types per schema, instead it's per database and hence
- //we ignore schemapattern.
//the only type of user-named types we support are JAVA_OBJECT
- int getClassTypes = 0;
- if (types != null && types.length >= 1) {
- for (int i=0; i<types.length; i++){
- if (types[i] == java.sql.Types.JAVA_OBJECT)
- getClassTypes = 1;
+
+ int getClassTypes = ILLEGAL_UDT_TYPE;
+
+ // null argument, means all supported UDT types
+ if ( types == null ) { getClassTypes = java.sql.Types.JAVA_OBJECT; }
+ else if ( types.length > 0 )
+ {
+ for ( int i=0; i< types.length; i++ )
+ {
+ if ( types[i] == java.sql.Types.JAVA_OBJECT )
+ { getClassTypes = java.sql.Types.JAVA_OBJECT; }
}
- } else
- getClassTypes = 1;
+ }
PreparedStatement s = getPreparedQuery("getUDTs");
- s.setInt(1, java.sql.Types.JAVA_OBJECT);
- s.setString(2, catalog);
- s.setString(3, schemaPattern);
+ s.setInt(1, java.sql.Types.JAVA_OBJECT); // thrown away. preserved for backward compatibility
with pre-10.6 clients
+ s.setString(2, swapNull(catalog));
+ s.setString(3, swapNull(schemaPattern));
s.setString(4, swapNull(typeNamePattern));
s.setInt(5, getClassTypes);
return s.executeQuery();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties?rev=893224&r1=893223&r2=893224&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties Tue Dec
22 16:35:06 2009
@@ -800,6 +800,8 @@
1,FALSE,2,TRUE,FALSE,FALSE,0,0,10),\
('TIMESTAMP',93,26,'TIMESTAMP''','''',CAST (NULL AS CHAR), \
1,FALSE,2,TRUE,FALSE,FALSE,0,6,10),\
+ ('OBJECT',2000,CAST (NULL AS INTEGER),CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL
AS CHAR), \
+ 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS
INTEGER)),\
('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \
1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \
CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\
@@ -1087,24 +1089,6 @@
AND (TABS.TABLENAME=?) \
AND ? BETWEEN 0 AND 2 \
AND (?<>0 OR NOT COLS.COLUMNDATATYPE.isNullable())
-# This one is added for new method getUDTs in jdbc for database metadata.
-# First 2 parameters are catalog name and schema name. We don't have catalog names
-# yet. Also, our class aliases are database wide and not schema wide and that's why
-# we ignore the parameter for schema too. Third parameter is the name of the class
-# alias to look for.
-getUDTs=\
- SELECT \
- '' AS TYPE_CAT, \
- '' AS TYPE_SCHEM, \
- ALIAS AS TYPE_NAME, \
- JAVACLASSNAME AS CLASS_NAME, \
- CAST (? AS INT) AS DATA_TYPE, \
- '' AS REMARKS, \
- CAST (NULL AS SMALLINT) AS BASE_TYPE \
- FROM SYS.SYSALIASES \
- WHERE ((1=1) OR ? IS NOT NULL) AND ((1=1) OR ? IS NOT NULL) AND ALIAS LIKE ? \
- AND ?<>0 AND ALIASTYPE = 'C' \
- ORDER BY DATA_TYPE, TYPE_SCHEM, TYPE_NAME
#
# getSuperTypes is not supported, so we return an empty result set of the right
@@ -1263,3 +1247,36 @@
AND A.ALIAS LIKE ? \
AND (V."COLUMN_NAME" LIKE ?) \
ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME, PARAMETER_ID, ORDINAL_POSITION
+
+# parameter 1 = thrown away. preserved here for backward compatibility with pre-10.6 clients.
+# parameter 2 = catalog name pattern
+# parameter 3 = schema name pattern (should have like comparison)
+# parameter 4 = type name pattern (should have like comparison)
+# parameter 5 = UDT type (JAVA_OBJECT, STRUCT, DISTINCT). In Derby 10.6, there is only one
UDT type (JAVA_OBJECT)
+#
+# IMPORTANT NOTE:
+#
+# When we add a new kind of user defined type (e.g., STRUCT or DISTINCT) to Derby, we should
+# also add a ? to the in TYPES IN clause. We should also adjust the
+# values of the DATA_TYPE and BASE_TYPE columns accordingly. In addition, we should
+# modify EmbedDatabaseMetaData.getUDTs to account for that new ?
+#
+getUDTs=\
+ SELECT CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \
+ CASE WHEN (SCHEMANAME IS NULL) THEN CAST (NULL AS VARCHAR(128)) ELSE SCHEMANAME END AS
TYPE_SCHEM, \
+ ALIAS AS TYPE_NAME, \
+ JAVACLASSNAME AS CLASS_NAME, \
+ 2000 AS DATA_TYPE, \
+ CAST (NULL AS VARCHAR(128)) AS REMARKS, \
+ CAST (NULL AS SMALLINT) AS BASE_TYPE \
+ FROM \
+ SYS.SYSALIASES, \
+ SYS.SYSSCHEMAS \
+ WHERE (ALIASTYPE='A') \
+ AND (SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \
+ AND ((1=1) OR ? IS NOT NULL) \
+ AND ((1=1) OR ? IS NOT NULL) \
+ AND (SCHEMANAME LIKE ?) \
+ AND (ALIAS LIKE ?) AND (CAST (java.sql.Types::JAVA_OBJECT AS INTEGER) IN (?)) \
+ ORDER BY DATA_TYPE, TYPE_SCHEM, TYPE_NAME
+
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out?rev=893224&r1=893223&r2=893224&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out
Tue Dec 22 16:35:06 2009
@@ -209,6 +209,7 @@
DATE
TIME
TIMESTAMP
+OBJECT
BLOB
CLOB
XML
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java?rev=893224&r1=893223&r2=893224&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
Tue Dec 22 16:35:06 2009
@@ -57,6 +57,7 @@
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
//import org.apache.derby.shared.common.reference.JDBC40Translation;
+import org.apache.derbyTesting.functionTests.tests.upgradeTests.Version;
/**
* Test the DatabaseMetaData api.
@@ -789,6 +790,108 @@
*/
/**
+ * Test UDT-related metadata methods.
+ *
+ */
+ public void testUDTs() throws Exception
+ {
+ //
+ // We only run this test if the database version is at least 10.6.
+ // Otherwise we can't create a UDT.
+ //
+ Version dataVersion = getDataVersion( getConnection() );
+ if ( dataVersion.compareTo( new Version( 10, 6, 0, 0 ) ) < 0 ) { return; }
+
+ DatabaseMetaData dmd = getDMD();
+
+ createObjectsForUDTTests();
+
+ ResultSet rs = dmd.getUDTs(null,null,null,null);
+ String[] columnNames = new String[] {
+ "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "CLASS_NAME",
+ "DATA_TYPE", "REMARKS", "BASE_TYPE"};
+ int[] columnTypes = new int[] {
+ Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.LONGVARCHAR,
+ Types.INTEGER, Types.VARCHAR, Types.SMALLINT
+ };
+ boolean[] nullability = new boolean[] {
+ true, true, false, false,
+ false, true, true
+ };
+ assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);
+
+ String[][] expectedRows = new String[][]
+ {
+ { null, "APP", "PRICE", "org.apache.derbyTesting.functionTests.tests.lang.Price",
"2000", null, null },
+ };
+ JDBC.assertFullResultSet( rs, expectedRows );
+
+ // now try the test, specifying a specific type of UDT
+ rs = dmd.getUDTs( null, null, null, new int[] { Types.JAVA_OBJECT } );
+ JDBC.assertFullResultSet( rs, expectedRows );
+
+ rs = dmd.getUDTs( null, null, null, new int[] { Types.DISTINCT, Types.JAVA_OBJECT
} );
+ JDBC.assertFullResultSet( rs, expectedRows );
+
+ // no UDTs of these types
+ rs = dmd.getUDTs( null, null, null, new int[] { Types.DISTINCT, Types.STRUCT } );
+ JDBC.assertEmpty(rs);
+
+ // try explicit schema and type name
+ rs = dmd.getUDTs( null, "APP", "PRICE", new int[] { Types.DISTINCT, Types.JAVA_OBJECT
} );
+ JDBC.assertFullResultSet( rs, expectedRows );
+
+ rs = dmd.getUDTs( null, "AP%", "PRI%", new int[] { Types.DISTINCT, Types.JAVA_OBJECT
} );
+ JDBC.assertFullResultSet( rs, expectedRows );
+
+ rs = dmd.getUDTs( null, "FOO", "PRICE", new int[] { Types.DISTINCT, Types.JAVA_OBJECT
} );
+ JDBC.assertEmpty(rs);
+
+ // now make sure that getColumns() returns the right data
+ rs = dmd.getColumns( null, "APP", "ORDERS", null );
+ expectedRows = new String[][]
+ {
+ {
+ "", "APP", "ORDERS", "TOTALPRICE",
+ "2000", "\"APP\".\"PRICE\"", "-1", null,
+ null, null, "1", "",
+ null, null, null, null,
+ "1", "YES", null, null,
+ null, null, "NO"
+ },
+ };
+ JDBC.assertFullResultSet( rs, expectedRows );
+ rs = dmd.getColumns( null, "APP", "ORDERS", null );
+ crossCheckGetColumnsAndResultSetMetaData( rs, false, 0 );
+
+ dropObjectsForUDTTests();
+ }
+ /** Create objects needed to test the UDT-related metadata calls */
+ private void createObjectsForUDTTests() throws SQLException
+ {
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+
+ s.execute( "create type price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java" );
+ s.execute( "create table orders( totalPrice price )" );
+
+ commit();
+ getConnection().setAutoCommit(true);
+ }
+ /** Drop the objects needed for testing the UDT-related metadata methods */
+ private void dropObjectsForUDTTests() throws SQLException
+ {
+ getConnection().setAutoCommit(false);
+ Statement s = createStatement();
+
+ s.execute( "drop table orders" );
+ s.execute( "drop type price restrict" );
+
+ commit();
+ getConnection().setAutoCommit(true);
+ }
+
+ /**
* Test methods that describe attributes of SQL Objects
* that are not supported by derby. In each case the
* metadata should return an empty ResultSet of the
@@ -859,17 +962,6 @@
assertMetaDataResultSet(rs, columnNames, columnTypes, nullability);
JDBC.assertEmpty(rs);
- rs = dmd.getUDTs(null,null,null,null);
- columnNames = new String[] {
- "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "CLASS_NAME",
- "DATA_TYPE", "REMARKS", "BASE_TYPE"};
- columnTypes = new int[] {
- Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};
- nullability = new boolean[] {
- true, true, true, true};
- assertMetaDataResultSet(rs, null, null, null);
- JDBC.assertEmpty(rs);
-
ResultSet rss[] = getVersionColumns(null,null, "No_such_table");
checkVersionColumnsShape(rss);
JDBC.assertEmpty(rss[0]);
@@ -2011,7 +2103,7 @@
Types.INTEGER, Types.LONGVARBINARY, Types.LONGVARCHAR,
Types.NUMERIC, Types.REAL, Types.SMALLINT,
Types.TIME, Types.TIMESTAMP, Types.VARBINARY,
- Types.VARCHAR, JDBC.SQLXML
+ Types.VARCHAR, JDBC.SQLXML, Types.JAVA_OBJECT
};
// Rows are returned from getTypeInfo in order of
@@ -2098,6 +2190,7 @@
case Types.VARCHAR:
precision = 32672;
break;
+ case Types.JAVA_OBJECT:
case JDBC.SQLXML:
precision = 0;
break;
@@ -2106,12 +2199,12 @@
precision, rs.getInt("PRECISION"));
/*
- Precision value is null for XML data type
+ Precision value is null for XML and OBJECT data type
*/
- if (typeName.equals("XML" ))
- assertTrue(rs.wasNull());
+ if ( (typeName.equals("XML" )) || (typeName.equals("OBJECT" )) )
+ { assertTrue(rs.wasNull()); }
else
- assertFalse(rs.wasNull());
+ { assertFalse(rs.wasNull()); }
// LITERAL_PREFIX (column 4)
@@ -2588,6 +2681,11 @@
while (rs.next())
{
String typeName = rs.getString("TYPE_NAME");
+
+ // skip the OBJECT type because this does not correspond to an
+ // actual data type but merely flags the fact that the database
+ // supports the creation of user defined types
+ if ( "OBJECT".equals( typeName ) ) { continue; }
String createParams = rs.getString("CREATE_PARAMS");
@@ -4695,4 +4793,41 @@
assertSQLState("XCL16", sqle);
}
}
+
+ /**
+ * <p>
+ * Get the version of the data in the database.
+ * </p>
+ */
+ private Version getDataVersion( Connection conn )
+ throws Exception
+ {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+
+ try {
+ ps = conn.prepareStatement( "values syscs_util.syscs_get_database_property('DataDictionaryVersion')"
);
+ rs = ps.executeQuery();
+
+ rs.next();
+
+ String retval = rs.getString( 1 );
+ int dotIdx = retval.indexOf( '.' );
+ int major = Integer.parseInt( retval.substring( 0, dotIdx ) );
+ int minor = Integer.parseInt( retval.substring( dotIdx + 1, retval.length() )
);
+
+ return new Version( major, minor, 0, 0 );
+ }
+ catch (Exception se)
+ {
+ printStackTrace( se );
+ return null;
+ }
+ finally
+ {
+ if ( rs != null ) { rs.close(); }
+ if ( ps != null ) { ps.close(); }
+ }
+ }
+
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java?rev=893224&r1=893223&r2=893224&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
Tue Dec 22 16:35:06 2009
@@ -766,10 +766,14 @@
ResultSet rs = dbmd.getTypeInfo();
int count = 0;
+ int expectedDataTypeCount = LEGAL_BOOLEAN_CASTS.length + ILLEGAL_BOOLEAN_CASTS.length;
+ // getTypeInfo() also returns a row for the generic OBJECT data type
+ expectedDataTypeCount++;
+
while ( rs.next() ) { count++; }
assertEquals( "You must add your new data type to LEGAL_BOOLEAN_CASTS or ILLEGAL_BOOLEAN_CASTS",
- LEGAL_BOOLEAN_CASTS.length + ILLEGAL_BOOLEAN_CASTS.length,
+ expectedDataTypeCount,
count );
rs.close();
|