Mercurial > hg > monetdb-java

changeset 219:4572f0694fde

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved DatabaseMetaData methods getTablePrivileges() and getColumnPrivileges() by returning also any combination of privileges for the table or column in the PRIVILEGE result column. Previously only single privileges (SELECT or UPDATE or INSERT or DELETE or EXECUTE or GRANT) would be returned.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 26 Apr 2018 18:43:55 +0200 (2018-04-26)
parents 5cc7101c5c8d
children 34f9ddd1a4e5
files ChangeLog src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
diffstat 3 files changed, 68 insertions(+), 18 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,16 +1,23 @@
 # ChangeLog file for monetdb-java
 # This file is updated with Maddlog
 
+* Thu Apr 26 2018 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
+- Improved DatabaseMetaData methods getTablePrivileges() and
+  getColumnPrivileges() by returning also any combination of privileges
+  for the table or column in the PRIVILEGE result column. Previously only
+  single privileges (SELECT or UPDATE or INSERT or DELETE or EXECUTE or
+  GRANT) would be returned.
+
 * Thu Apr 19 2018 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Corrected method DatabaseMetaData.getFunctions() for result column
   FUNCTION_TYPE.  It used to return DatabaseMetaData.functionResultUnknown
   value for when the internal function type was 6 (Analytic function) or 7
   (Loader function).  It now returns DatabaseMetaData.functionNoTable value
-  for those functions.
+  for those function types.
 - DatabaseMetaData methods getTables(), getColumns(), getProcedures() and
   getFunctions() now return the comment in the REMARKS result column when a
   comment has been set for the table / view / column / procedure / function
-  via the SQL command COMMENT ON <db-object> IS 'comment-text'.
+  via the SQL command COMMENT ON <db-object type> <qname> IS 'comment-text'.
 
 * Mon Oct 23 2017 Sjoerd Mullender <sjoerd@acm.org>
 - Compiled and released new jars: monetdb-jdbc-2.27.jar, monetdb-mcl-1.16.jar
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
@@ -147,6 +147,10 @@ public class MonetConnection
 	/** Whether or not CLOB is mapped to Types.VARCHAR instead of Types.CLOB within this connection */
 	private boolean treatClobAsVarChar = false;
 
+	// Internal cache for determining if system table sys.privilege_codes (new as of Jul2017 release) exists on server
+	private boolean queriedPrivilege_codesTable = false;
+	private boolean hasPrivilege_codesTable = false;
+
 	// Internal cache for determining if system table sys.comments (new as of Mar2018 release) exists on server
 	private boolean queriedCommentsTable = false;
 	private boolean hasCommentsTable = false;
@@ -1682,19 +1686,42 @@ public class MonetConnection
 	 * This method is used by methods from MonetDatabaseMetaData.
 	 */
 	boolean commentsTableExists() {
-		if (queriedCommentsTable)
-			return hasCommentsTable;
+		if (!queriedCommentsTable) {
+			hasCommentsTable = existsSysTable("comments");
+			queriedCommentsTable = true;	// set flag, so the querying is done only at first invocation.
+		}
+		return hasCommentsTable;
+	}
 
-		queriedCommentsTable = true;	// set flag, so the querying part below is done only once, at first invocation.
+	/**
+	 * Internal utility method to query the server to find out if it has
+	 * the system table sys.privilege_codes (which is new as of Jul2017 release).
+	 * The result is cached and reused, so that we only test the query once per connection.
+	 * This method is used by methods from MonetDatabaseMetaData.
+	 */
+	boolean privilege_codesTableExists() {
+		if (!queriedPrivilege_codesTable) {
+			hasPrivilege_codesTable = existsSysTable("privilege_codes");
+			queriedPrivilege_codesTable = true;	// set flag, so the querying is done only at first invocation.
+		}
+		return hasPrivilege_codesTable;
+	}
+
+	/**
+	 * Internal utility method to query the server to find out if it has the system table sys.<tablename>.
+	 */
+	private boolean existsSysTable(String tablename) {
+		boolean exists = false;
 		Statement stmt = null;
 		ResultSet rs = null;
 		try {
 			stmt = createStatement();
 			if (stmt != null) {
-				rs = stmt.executeQuery( "SELECT \"id\", \"remark\" FROM \"sys\".\"comments\" LIMIT 1");
+				rs = stmt.executeQuery("SELECT id FROM sys._tables WHERE name = '"
+							+ tablename
+							+ "' AND schema_id IN (SELECT id FROM sys.schemas WHERE name = 'sys')");
 				if (rs != null) {
-					rs.next();
-					hasCommentsTable = true;
+					exists = rs.next(); // if a row is available it exists, else not
 				}
 			}
 		} catch (SQLException se) {
@@ -1711,8 +1738,8 @@ public class MonetConnection
 				} catch (SQLException e) { /* ignore */ }
 			}
 		}
-// for debug: System.out.println("commentsTableExists returns: " + hasCommentsTable);
-		return hasCommentsTable;
+// for debug: System.out.println("testTableExists(" + tablename + ") returns: " + exists);
+		return exists;
 	}
 
 	/**
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -2306,13 +2306,15 @@ public class MonetDatabaseMetaData exten
 		String columnNamePattern
 	) throws SQLException
 	{
+		boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
 		StringBuilder query = new StringBuilder(1100);
 		query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
 			"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
 			"\"tables\".\"name\" AS \"TABLE_NAME\", " +
 			"\"columns\".\"name\" AS \"COLUMN_NAME\", " +
 			"\"grantors\".\"name\" AS \"GRANTOR\", " +
-			"\"grantees\".\"name\" AS \"GRANTEE\", " +
+			"\"grantees\".\"name\" AS \"GRANTEE\", ")
+		.append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" :
 			"cast(CASE \"privileges\".\"privileges\" " +
 				"WHEN 1 THEN 'SELECT' " +
 				"WHEN 2 THEN 'UPDATE' " +
@@ -2321,7 +2323,7 @@ public class MonetDatabaseMetaData exten
 				"WHEN 16 THEN 'EXECUTE' " +
 				"WHEN 32 THEN 'GRANT' " +
 				"ELSE NULL " +
-			"END AS varchar(7)) AS \"PRIVILEGE\", " +
+			"END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
 			"cast(CASE \"privileges\".\"grantable\" " +
 				"WHEN 0 THEN 'NO' " +
 				"WHEN 1 THEN 'YES' " +
@@ -2332,12 +2334,18 @@ public class MonetDatabaseMetaData exten
 			"\"sys\".\"schemas\", " +
 			"\"sys\".\"columns\", " +
 			"\"sys\".\"auths\" AS \"grantors\", " +
-			"\"sys\".\"auths\" AS \"grantees\" " +
-		"WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
+			"\"sys\".\"auths\" AS \"grantees\" ");
+		if (usePrivilege_codesTable) {
+			query.append(", \"sys\".\"privilege_codes\" ");
+		}
+		query.append("WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
 			"AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
 			"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
 			"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
 			"AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
+		if (usePrivilege_codesTable) {
+			query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
+		}
 
 		if (catalog != null && !catalog.isEmpty()) {
 			// none empty catalog selection.
@@ -2395,12 +2403,14 @@ public class MonetDatabaseMetaData exten
 		String tableNamePattern
 	) throws SQLException
 	{
+		boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists();
 		StringBuilder query = new StringBuilder(1000);
 		query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
 			"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
 			"\"tables\".\"name\" AS \"TABLE_NAME\", " +
 			"\"grantors\".\"name\" AS \"GRANTOR\", " +
-			"\"grantees\".\"name\" AS \"GRANTEE\", " +
+			"\"grantees\".\"name\" AS \"GRANTEE\", ")
+		.append(usePrivilege_codesTable ? "\"privilege_codes\".\"privilege_code_name\"" :
 			"cast(CASE \"privileges\".\"privileges\" " +
 				"WHEN 1 THEN 'SELECT' " +
 				"WHEN 2 THEN 'UPDATE' " +
@@ -2409,7 +2419,7 @@ public class MonetDatabaseMetaData exten
 				"WHEN 16 THEN 'EXECUTE' " +
 				"WHEN 32 THEN 'GRANT' " +
 				"ELSE NULL " +
-			"END AS varchar(7)) AS \"PRIVILEGE\", " +
+			"END AS varchar(7))").append(" AS \"PRIVILEGE\", " +
 			"cast(CASE \"privileges\".\"grantable\" " +
 				"WHEN 0 THEN 'NO' " +
 				"WHEN 1 THEN 'YES' " +
@@ -2419,11 +2429,17 @@ public class MonetDatabaseMetaData exten
 			"\"sys\".\"tables\", " +
 			"\"sys\".\"schemas\", " +
 			"\"sys\".\"auths\" AS \"grantors\", " +
-			"\"sys\".\"auths\" AS \"grantees\" " +
-		"WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
+			"\"sys\".\"auths\" AS \"grantees\" ");
+		if (usePrivilege_codesTable) {
+			query.append(", \"sys\".\"privilege_codes\" ");
+		}
+		query.append("WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
 			"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
 			"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" " +
 			"AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
+		if (usePrivilege_codesTable) {
+			query.append(" AND \"privileges\".\"privileges\" = \"privilege_codes\".\"privilege_code_id\"");
+		}
 
 		if (catalog != null && !catalog.isEmpty()) {
 			// none empty catalog selection.