Author: rhillegas
Date: Sun Dec 20 18:00:02 2009
New Revision: 892627

URL: http://svn.apache.org/viewvc?rev=892627&view=rev
Log:
DERBY-4485: Add dblook support for UDTs and for USAGE privileges on them.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/AliasInfo.java
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
    db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties
    db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/AliasInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/AliasInfo.java?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/AliasInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/AliasInfo.java Sun Dec 20 18:00:02 2009
@@ -28,6 +28,7 @@
  * In a Derby system, an alias can be one of the following:
  * <ul>
  * <li>method alias
+ * <li>UDT alias
  * <li>class alias
  * <li>synonym
  * <li>user-defined aggregate

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java Sun Dec 20 18:00:02 2009
@@ -110,8 +110,12 @@
 	 */
 	public	int	getTypeFormatId()	{ return StoredFormatIds.UDT_INFO_V01_ID; }
 
+    /**
+     * This is used by dblook to reconstruct the UDT-specific parts of the ddl
+     * needed to recreate this alias.
+     */
 	public String toString() {
-		return "";
+		return "LANGUAGE JAVA";
 	}
 
 	public String getMethodName()

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Sun Dec 20 18:00:02 2009
@@ -5799,6 +5799,10 @@
 -- ----------------------------------------------
 CALL SQLJ.INSTALL_JAR(<maskedOutJarDesc>)
 -- ----------------------------------------------
+-- DDL Statements for user defined types
+-- ----------------------------------------------
+CREATE TYPE "APP"."PRICE" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.Price' LANGUAGE JAVA
+-- ----------------------------------------------
 -- DDL Statements for stored procedures
 -- ----------------------------------------------
 CREATE PROCEDURE "APP"."PROC1" (INOUT A CHAR(10),IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA CONTAINS SQL DYNAMIC RESULT SETS 4 EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3' 
@@ -5930,6 +5934,10 @@
 -- ----------------------------------------------
 CALL SQLJ.INSTALL_JAR(<maskedOutJarDesc>)
 -- ----------------------------------------------
+-- DDL Statements for user defined types
+-- ----------------------------------------------
+CREATE TYPE "APP"."PRICE" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.Price' LANGUAGE JAVA #
+-- ----------------------------------------------
 -- DDL Statements for stored procedures
 -- ----------------------------------------------
 CREATE PROCEDURE "APP"."PROC1" (INOUT A CHAR(10),IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA CONTAINS SQL DYNAMIC RESULT SETS 4 EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3'  #

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out Sun Dec 20 18:00:02 2009
@@ -5799,6 +5799,10 @@
 -- ----------------------------------------------
 CALL SQLJ.INSTALL_JAR(<maskedOutJarDesc>)
 -- ----------------------------------------------
+-- DDL Statements for user defined types
+-- ----------------------------------------------
+CREATE TYPE "APP"."PRICE" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.Price' LANGUAGE JAVA
+-- ----------------------------------------------
 -- DDL Statements for stored procedures
 -- ----------------------------------------------
 CREATE PROCEDURE "APP"."PROC1" (INOUT A CHAR(10),IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA CONTAINS SQL DYNAMIC RESULT SETS 4 EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3' 
@@ -5930,6 +5934,10 @@
 -- ----------------------------------------------
 CALL SQLJ.INSTALL_JAR(<maskedOutJarDesc>)
 -- ----------------------------------------------
+-- DDL Statements for user defined types
+-- ----------------------------------------------
+CREATE TYPE "APP"."PRICE" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.Price' LANGUAGE JAVA #
+-- ----------------------------------------------
 -- DDL Statements for stored procedures
 -- ----------------------------------------------
 CREATE PROCEDURE "APP"."PROC1" (INOUT A CHAR(10),IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA CONTAINS SQL DYNAMIC RESULT SETS 4 EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3'  #

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql Sun Dec 20 18:00:02 2009
@@ -87,3 +87,10 @@
 -- ----------------------------------------------
 
 create trigger trigOne after insert on bar.t1 for each row update bar.t1 set i = 4 where i = 2;
+
+-- ----------------------------------------------
+-- UDTs
+-- ----------------------------------------------
+
+create type price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java;
+

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java Sun Dec 20 18:00:02 2009
@@ -31,43 +31,74 @@
 import java.util.HashMap;
 import org.apache.derby.tools.dblook;
 
-public class DB_Alias {
+public class DB_Alias
+{
+    private static final char UDT_TYPE = 'A';
+    private static final char PROCEDURE_TYPE = 'P';
+    private static final char FUNCTION_TYPE = 'F';
 
 	// Prepared statements use throughout the DDL
 	// generation process.
 
 	/* ************************************************
-	 * Generate the DDL for all stored procedures and
-	 * functions in a given database and write it to
+	 * Generate the DDL for all stored procedures,
+	 * functions, and UDTs in a given database and write it to
 	 * output via Logs.java.
 	 * @param conn Connection to the source database.
 	 ****/
 
-	public static void doProceduresAndFunctions(Connection conn)
+	public static void doProceduresFunctionsAndUDTs(Connection conn)
 		throws SQLException {
 
 		// First do stored procedures.
-		Statement stmt = conn.createStatement();
-		ResultSet rs = stmt.executeQuery("SELECT ALIAS, ALIASINFO, " +
-			"ALIASID, SCHEMAID, JAVACLASSNAME, SYSTEMALIAS FROM SYS.SYSALIASES " +
-			"WHERE ALIASTYPE='P'");
-		generateDDL(rs, 'P');	// 'P' => for PROCEDURES
-
-		// Now do functions.
-		rs = stmt.executeQuery("SELECT ALIAS, ALIASINFO, " +
-			"ALIASID, SCHEMAID, JAVACLASSNAME, SYSTEMALIAS FROM SYS.SYSALIASES " +
-			"WHERE ALIASTYPE='F'");
-		generateDDL(rs, 'F');	// 'F' => for FUNCTIONS
+		PreparedStatement ps = conn.prepareStatement
+            (
+             "SELECT ALIAS, ALIASINFO, " +
+             "ALIASID, SCHEMAID, JAVACLASSNAME, SYSTEMALIAS FROM SYS.SYSALIASES " +
+             "WHERE ALIASTYPE=?");
+
+        //
+        // UDTs come before procedures and functions right now because
+        // procedures and functions can have args and return values which
+        // have UDT types. If we add functions to the signatures of UDTs,
+        // then we will have to do some trickier dependency analysis in order
+        // to interleave routine and UDT ddl.
+        //
+
+        generateDDL( ps, UDT_TYPE ); // UDT_TYPE => for UDTs
+        generateDDL( ps, PROCEDURE_TYPE );	// PROCEDURE_TYPE => for PROCEDURES
+		generateDDL( ps, FUNCTION_TYPE );	// FUNCTION_TYPE => for FUNCTIONS
 
-		rs.close();
-		stmt.close();
-		return;
+        ps.close();
 
+		return;
 	}
 
+    /* ************************************************
+	 * Generate the DDL for the stored procedures,
+	 * functions, or UDTs in a given database, depending on the
+	 * the received aliasType.
+	 * @param rs Result set holding either stored procedures
+	 *  or functions.
+	 * @param aliasType Indication of whether we're generating
+	 *  stored procedures or functions.
+	 ****/
+	private static void generateDDL(PreparedStatement ps, char aliasType)
+		throws SQLException
+	{
+        ps.setString( 1, new String( new char[] { aliasType } ) );
+
+        ResultSet rs = ps.executeQuery();
+
+        generateDDL( rs, aliasType );
+
+        rs.close();
+    }
+
+
 	/* ************************************************
-	 * Generate the DDL for either stored procedures or
-	 * functions in a given database, depending on the
+	 * Generate the DDL for the stored procedures,
+	 * functions, or UDTs in a given database, depending on the
 	 * the received aliasType.
 	 * @param rs Result set holding either stored procedures
 	 *  or functions.
@@ -91,9 +122,12 @@
 
 			if (firstTime) {
 				Logs.reportString("----------------------------------------------");
-				Logs.reportMessage((aliasType == 'P')
-					? "DBLOOK_StoredProcHeader"
-					: "DBLOOK_FunctionHeader");
+                switch( aliasType )
+                {
+                case UDT_TYPE: Logs.reportMessage( "DBLOOK_UDTHeader" ); break;
+                case PROCEDURE_TYPE: Logs.reportMessage( "DBLOOK_StoredProcHeader" ); break;
+                case FUNCTION_TYPE: Logs.reportMessage( "DBLOOK_FunctionHeader" ); break;
+                }
 				Logs.reportString("----------------------------------------------\n");
 			}
 
@@ -102,7 +136,7 @@
 				dblook.expandDoubleQuotes(aliasName));
 			fullName = procSchema + "." + fullName;
 
-			String creationString = createProcOrFuncString(
+			String creationString = createProcFuncOrUDTString(
 				fullName, rs, aliasType);
 			Logs.writeToNewDDL(creationString);
 			Logs.writeStmtEndToNewDDL();
@@ -124,31 +158,46 @@
 	 *   returned, as a String.
 	 ****/
 
-	private static String createProcOrFuncString(String aliasName,
+	private static String createProcFuncOrUDTString(String aliasName,
 		ResultSet aliasInfo, char aliasType) throws SQLException
 	{
 
 		StringBuffer alias = new StringBuffer("CREATE ");
-		if (aliasType == 'P')
-			alias.append("PROCEDURE ");
-		else if (aliasType == 'F')
-			alias.append("FUNCTION ");
+
+        switch( aliasType )
+        {
+        case UDT_TYPE: alias.append( "TYPE " ); break;
+        case PROCEDURE_TYPE: alias.append("PROCEDURE "); break;
+        case FUNCTION_TYPE: alias.append("FUNCTION "); break;
+        }
 		alias.append(aliasName);
 		alias.append(" ");
 
 		String params = aliasInfo.getString(2);
 
-		// Just grab the parameter part; we'll get the method name later.
-		alias.append(params.substring(params.indexOf("("), params.length()));
-		alias.append(" ");
+        if ( aliasType != UDT_TYPE )
+        {
+            // Just grab the parameter part; we'll get the method name later.
+            alias.append(params.substring(params.indexOf("("), params.length()));
+            alias.append(" ");
+        }
 
 		// Now add the external name.
 		alias.append("EXTERNAL NAME '");
 		alias.append(aliasInfo.getString(5));
-		alias.append(".");
-		// Get method name from parameter string fetched above.
-		alias.append(params.substring(0, params.indexOf("(")));
-		alias.append("' ");
+
+        if ( aliasType == UDT_TYPE )
+        {
+            alias.append("' ");
+            alias.append( params );
+        }
+        else
+        {
+            alias.append(".");
+            // Get method name from parameter string fetched above.
+            alias.append(params.substring(0, params.indexOf("(")));
+            alias.append("' ");
+        }
 
 		return alias.toString();
 

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java Sun Dec 20 18:00:02 2009
@@ -54,6 +54,12 @@
 			"S.SCHEMAID AND T.TABLEID = P.TABLEID");
 		generateColumnPrivs(rs, conn);
 
+		// Generate udt privilege statements
+		rs = stmt.executeQuery("SELECT P.GRANTEE, S.SCHEMANAME, A.ALIAS, P.PERMISSION, P.OBJECTTYPE FROM " +
+			"SYS.SYSPERMS P, SYS.SYSALIASES A, SYS.SYSSCHEMAS S WHERE A.SCHEMAID = " +
+			"S.SCHEMAID AND P.OBJECTID = A.ALIASID AND A.ALIASTYPE='A'");
+		generateUDTPrivs(rs);
+
 		// Generate routine privilege statements
 		rs = stmt.executeQuery("SELECT GRANTEE, SCHEMANAME, ALIAS, ALIASTYPE FROM " +
 			"SYS.SYSROUTINEPERMS P, SYS.SYSALIASES A, SYS.SYSSCHEMAS S WHERE A.SCHEMAID = " +
@@ -276,6 +282,53 @@
 	}
 
 	/** ************************************************
+	 * Generate udt privilege statements
+	 *
+	 * @param rs ResultSet holding required information
+	 ****/
+	public static void generateUDTPrivs(ResultSet rs) throws SQLException
+	{
+		boolean firstTime = true;
+		while (rs.next()) {
+			String authName = dblook.addQuotes
+				(dblook.expandDoubleQuotes(rs.getString(1)));
+			String schemaName = dblook.addQuotes
+				(dblook.expandDoubleQuotes(rs.getString(2)));
+			String aliasName = dblook.addQuotes
+				(dblook.expandDoubleQuotes(rs.getString(3)));
+			String fullName = schemaName + "." + aliasName;
+			String permission = rs.getString(4);
+			String objectType = rs.getString(5);
+
+			if (dblook.isIgnorableSchema(schemaName))
+				continue;
+
+			if (firstTime) {
+				Logs.reportString("----------------------------------------------");
+				Logs.reportMessage("DBLOOK_UDTPrivHeader");
+				Logs.reportString("----------------------------------------------\n");
+			}
+
+			Logs.writeToNewDDL(genericPrivStatement(fullName, authName, permission, objectType ));
+			Logs.writeStmtEndToNewDDL();
+			Logs.writeNewlineToNewDDL();
+			firstTime = false;
+		}
+	}
+	private static String genericPrivStatement(String fullName, String authName, String permission, String objectType )
+		throws SQLException
+	{
+		boolean addSeparator = false;
+		StringBuffer grantStmt = new StringBuffer("GRANT " + permission + " ON " + objectType + " " );
+
+		grantStmt.append(fullName);
+		grantStmt.append(" TO ");
+		grantStmt.append(authName);
+
+		return grantStmt.toString();
+	}
+
+	/** ************************************************
 	 * Generate routine privilege statements
 	 *
 	 * @param rs ResultSet holding required information

Modified: db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/loc/toolsmessages.properties Sun Dec 20 18:00:02 2009
@@ -245,10 +245,12 @@
 DBLOOK_StoredProcHeader=DDL Statements for stored procedures
 DBLOOK_SynonymHeader=DDL Statements for Synonyms
 DBLOOK_TriggersHeader=DDL Statements for triggers
+DBLOOK_UDTHeader=DDL Statements for user defined types
 DBLOOK_ViewsHeader=DDL Statements for views
 DBLOOK_TablePrivHeader=GRANT statements for tables
 DBLOOK_ColumnPrivHeader=GRANT statements for columns
 DBLOOK_RoutinePrivHeader=GRANT statements for routines
+DBLOOK_UDTPrivHeader=GRANT statements for user defined types
 DBLOOK_Role_definitions_header=CREATE statements for roles
 DBLOOK_Role_grants_header=GRANT statements for roles
 DBLOOK_Jar_Note=\

Modified: db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java?rev=892627&r1=892626&r2=892627&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java Sun Dec 20 18:00:02 2009
@@ -526,7 +526,7 @@
 			if (tableList == null) {
 			// Don't do these if user just wants table-related objects.
 				DB_Jar.doJars(sourceDBName, this.conn);
-				DB_Alias.doProceduresAndFunctions(this.conn);
+				DB_Alias.doProceduresFunctionsAndUDTs(this.conn);
 			}
 
 			DB_Table.doTables(this.conn, tableIdToNameMap);