Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <20060324222300.21027.qmail@minotaur.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r388663 - in
 /db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc:
 EmbedDatabaseMetaData.java metadata.properties
Date: Fri, 24 Mar 2006 22:23:00 -0000
To: derby-commits@db.apache.org
From: bandaram@apache.org

Author: bandaram
Date: Fri Mar 24 14:22:57 2006
New Revision: 388663

URL: http://svn.apache.org/viewcvs?rev=388663&view=rev
Log:
DERBY-1124: Use 10.1 version of getColumnPrivileges and getTablePrivileges metadata queries during soft upgrade of pre-10.2 databases to software version 10.2 or beyond.

Submitted by Satheesh Bandaram (satheesh@sourcery.org)_

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java?rev=388663&r1=388662&r2=388663&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 Fri Mar 24 14:22:57 2006
@@ -3152,6 +3152,13 @@
 	 * soft upgrade mode. Because of this, if the database is in 
 	 * soft upgrade mode, get the queries from metadata.properties
 	 * file rather than from the system tables.
+	 *
+	 * Getting queries from metadata.properties might cause problems
+	 * if system catalogs have been changed between versions either by
+	 * addition of columns or have new catalogs. To continue
+	 * to support soft upgrade from older versions of database, find
+	 * query that most closely matches database dictionary version.
+	 *
 	 * @param queryName Name of the metadata query for which we need
 	 * a prepared statement
 	 * @return PreparedStatement
@@ -3168,7 +3175,7 @@
 			try {
 				//Can't use stored prepared statements because we are in soft upgrade
 				//mode and hence need to get metadata sql from metadata.properties file 
-				String queryText = getQueryDescriptions().getProperty(queryName);
+				String queryText = getQueryFromDescription(queryName);
 				s = getEmbedConnection().prepareMetaDataStatement(queryText);
 			} catch (Throwable t) {
 				throw handleException(t);
@@ -3176,6 +3183,42 @@
 		}
 		return s;
 	}	
+
+	/*
+	 * Given a queryName, find closest match in queryDescriptions. This method
+	 * should be called in soft-upgrade mode only, where current software version
+	 * doesn't match dictionary version. For these cases, there may be
+	 * multiple entries in queryDescriptions for given queryName. Find a
+	 * version of the query that closely matches dictionary version.
+	 *
+	 * This method is currently coded to handle two specific queries,
+	 * getColumnPrivileges and getTablePrivileges. Derby databases that are 10.1
+	 * or earlier will not have new system tables added for 10.2 for privileges.
+	 * 
+	 * It should be possible to automate finding closest match by generating
+	 * all Major_Minor versions between software version and dictionary version
+	 * and try each one from Dictionary version to current version. Since only
+	 * needed for two queries, overhead may not be worth it yet.
+	 */
+	private String getQueryFromDescription(String queryName)
+		throws StandardException
+	{
+		DataDictionary dd = getLanguageConnectionContext().getDataDictionary();
+
+		// If dictionary version is below 10.2, special case
+		// getColumnPrivileges and getTablePrivileges since new system tables
+		// for privileges wouldn't be present.
+		if (!dd.checkVersion(DataDictionary.DD_VERSION_DERBY_10_2, null))
+		{
+			if (queryName.equals("getColumnPrivileges"))
+				queryName = "getColumnPrivileges_10_1";
+
+			if (queryName.equals("getTablePrivileges"))
+				queryName = "getTablePrivileges_10_1";
+		}
+
+		return getQueryDescriptions().getProperty(queryName);
+	}
 
 	/*
 	** Given a SPS name and a query text it returns a 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties?rev=388663&r1=388662&r2=388663&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 Fri Mar 24 14:22:57 2006
@@ -340,6 +340,42 @@
                  OR (P.TRIGGERPRIV = 'y' AND X.PRIV = 'TRIGGER' AND X.GRANTABLE = 'NO')) \
           ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE
 
+# This query is set up to return 0 rows of the right shape, for pre-10.2
+# versions of Derby databases
+# parameter 1 = pattern for catalog name
+# parameter 2 = pattern for schema name
+# parameter 3 = pattern for table name
+# parameter 4 = pattern for column name
+getColumnPrivileges_10_1=\
+	SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, \
+		GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \
+	FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128))) ) \
+		AS COLUMNPRIVILEGES ( TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \
+			COLUMN_NAME, GRANTOR, GRANTEE, \
+			PRIVILEGE, IS_GRANTABLE ) \
+	WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=? OR ''=?)
+# ORDER BY COLUMN_NAME, PRIVILEGE
+
+# REMIND: this query is set up to return 0 rows of the right shape for
+# pre-10.2 versions of Derby databases
+# parameter 1 = pattern for catalog name
+# parameter 2 = pattern for schema name
+# parameter 3 = pattern for table name
+getTablePrivileges_10_1=\
+	SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \
+		GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \
+	FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \
+		CAST ('' AS VARCHAR(128))) ) \
+		AS TABLEPRIVILEGES (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \
+			GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE ) \
+	WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=?) \
+	ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE
+
 # REMIND: this query is set up to return 0 rows of the right shape, since
 # there are none of these or metadata about them in our system yet.
 # parameter 1 = pattern for catalog name