Return-Path: 
 <derby-commits-return-10913-apmail-db-derby-commits-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-commits-archive@www.apache.org
Received: (qmail 22841 invoked from network); 30 Oct 2008 22:26:46 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 30 Oct 2008 22:26:46 -0000
Received: (qmail 58032 invoked by uid 500); 30 Oct 2008 22:26:51 -0000
Delivered-To: apmail-db-derby-commits-archive@db.apache.org
Received: (qmail 58017 invoked by uid 500); 30 Oct 2008 22:26:51 -0000
Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-commits-help@db.apache.org>
list-unsubscribe: <mailto:derby-commits-unsubscribe@db.apache.org>
List-Post: <mailto:derby-commits@db.apache.org>
Reply-To: "Derby Development" <derby-dev@db.apache.org>
List-Id: <derby-commits.db.apache.org>
Delivered-To: mailing list derby-commits@db.apache.org
Received: (qmail 57893 invoked by uid 99); 30 Oct 2008 22:26:51 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Oct 2008 15:26:51 -0700
X-ASF-Spam-Status: No, hits=-2000.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Oct 2008 22:25:44 +0000
Received: by eris.apache.org (Postfix, from userid 65534)
	id 60A20238889E; Thu, 30 Oct 2008 15:25:47 -0700 (PDT)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r709266 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/diag/ engine/org/apache/derby/impl/sql/catalog/
 testing/org/apache/derbyTesting/functionTests/master/
 testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apac...
Date: Thu, 30 Oct 2008 22:25:30 -0000
To: derby-commits@db.apache.org
From: dag@apache.org
X-Mailer: svnmailer-1.0.8
Message-Id: <20081030222547.60A20238889E@eris.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org

Author: dag
Date: Thu Oct 30 15:25:30 2008
New Revision: 709266

URL: http://svn.apache.org/viewvc?rev=709266&view=rev
Log:
DERBY-3930 SQL roles: Add VTI for CONTAINED_ROLES

Patch derby-3930-2, which adds a VTI to facilitate queries involving
roles against the dictionary. (Derby does not support recursive queries).

SYSCS_DIAG.CONTAINED_ROLES(<role identifier string> [, <integer: if != 0, compute inverse])

Removes the less general existing VTI SYSCS_DIAG.ENABLED_ROLES (now redundant).

Added:
    db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java   (with props)
Removed:
    db/derby/code/trunk/java/engine/org/apache/derby/diag/EnabledRoles.java
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_dbo.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_usr.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_dbo.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_usr.sql
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj

Added: db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java?rev=709266&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java Thu Oct 30 15:25:30 2008
@@ -0,0 +1,176 @@
+/*
+
+   Derby - Class org.apache.derby.diag.ContainedRoles
+
+   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.derby.diag;
+
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Types;
+import org.apache.derby.iapi.sql.conn.ConnectionUtil;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.RoleClosureIterator;
+import org.apache.derby.iapi.sql.dictionary.RoleGrantDescriptor;
+import org.apache.derby.iapi.sql.ResultColumnDescriptor;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.reference.Limits;
+import org.apache.derby.iapi.error.PublicAPI;
+import org.apache.derby.iapi.util.IdUtil;
+import org.apache.derby.vti.VTITemplate;
+
+import org.apache.derby.impl.jdbc.EmbedResultSetMetaData;
+
+
+/**
+ * Contained roles shows all roles contained in the given identifier, or if the
+ * second argument, if given, is not 0, the inverse relation; all roles who
+ * contain the given role identifier.
+ *
+ * <p>To use it, query it as follows:
+ * </p>
+ * <pre> SELECT * FROM TABLE(SUSCS_DIAG.CONTAINED_ROLES('FOO')) t; </pre>
+ * <pre> SELECT * FROM TABLE(CONTAINED_ROLES('FOO', 1)) t; </pre>
+ *
+ * <p>The following columns will be returned:
+ *    <ul><li>ROLEID -- VARCHAR(128) NOT NULL
+ *    </ul>
+ * </p>
+ */
+public class ContainedRoles extends VTITemplate {
+
+    RoleClosureIterator rci;
+    String nextRole;
+    boolean initialized;
+    String role;
+    boolean inverse;
+
+    /**
+     * Constructor.
+     *
+     * @param roleid The role identifier for which we want to find the set of
+     *               contained roles (inclusive). The identifier is expected to
+     *               be in SQL form (not case normal form).
+     * @param inverse If != 0, use the inverse relation: find those roles which
+     *                all contain roleid (inclusive).
+     * @throws SQLException This is a public API, so the internal exception is
+     *                      wrapped in SQLException.
+     */
+    public ContainedRoles(String roleid, int inverse) throws SQLException {
+        try {
+            if (roleid != null) {
+                role = IdUtil.parseSQLIdentifier(roleid);
+            }
+
+            this.inverse = (inverse != 0);
+        } catch (StandardException e) {
+            throw PublicAPI.wrapStandardException(e);
+        }
+    }
+
+    /**
+     * Constructor.
+     *
+     * @param roleid The role identifier for which we want to find the set of
+     *               contained roles (inclusive). The identifier is expected to
+     *               be in SQL form (not case normal form).
+     * @throws SQLException This is a public API, so the internal exception is
+     *                      wrapped in SQLException.
+     */
+    public ContainedRoles(String roleid)  throws SQLException {
+        this(roleid, 0);
+    }
+
+    /**
+     * @see java.sql.ResultSet#next
+     */
+    public boolean next() throws SQLException {
+        try {
+            // Need to defer initialization here to make sure we have an
+            // activation.
+            if (!initialized) {
+                initialized = true;
+                LanguageConnectionContext lcc = ConnectionUtil.getCurrentLCC();
+                DataDictionary dd = lcc.getDataDictionary();
+                RoleGrantDescriptor rdDef =
+                    dd.getRoleDefinitionDescriptor(role);
+
+                if (rdDef != null) {
+                    lcc.beginNestedTransaction(true);
+                    try {
+                        int mode = dd.startReading(lcc);
+                        try {
+                            rci = dd.createRoleClosureIterator
+                                (lcc.getLastActivation().
+                                     getTransactionController(),
+                                 role, !inverse);
+                        } finally {
+                            dd.doneReading(mode, lcc);
+                        }
+                    } finally {
+                        // make sure we commit; otherwise, we will end up with
+                        // mismatch nested level in the language connection
+                        // context.
+                        lcc.commitNestedTransaction();
+                    }
+                }
+            }
+
+            return rci != null && ((nextRole = rci.next()) != null);
+
+        } catch (StandardException e) {
+            throw PublicAPI.wrapStandardException(e);
+        }
+    }
+
+
+    /**
+     * @see java.sql.ResultSet#close
+     */
+    public void close() {
+    }
+
+
+    /**
+     * @see java.sql.ResultSet#getMetaData
+     */
+    public ResultSetMetaData getMetaData() {
+        return metadata;
+    }
+
+    /**
+     * @see java.sql.ResultSet#getString
+     */
+    public String getString(int columnIndex) throws SQLException {
+        return nextRole;
+    }
+
+    /*
+     * Metadata
+     */
+    private static final ResultColumnDescriptor[] columnInfo = {
+        EmbedResultSetMetaData.getResultColumnDescriptor
+        ("ROLEID", Types.VARCHAR, false, Limits.MAX_IDENTIFIER_LENGTH)
+    };
+
+    private static final ResultSetMetaData metadata =
+        new EmbedResultSetMetaData(columnInfo);
+
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/diag/ContainedRoles.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Thu Oct 30 15:25:30 2008
@@ -12060,8 +12060,6 @@
 			{"STATEMENT_CACHE", "org.apache.derby.diag.StatementCache"},
 			{"TRANSACTION_TABLE", "org.apache.derby.diag.TransactionTable"},
 			{"ERROR_MESSAGES", "org.apache.derby.diag.ErrorMessages"},
-			{"ENABLED_ROLES", "org.apache.derby.diag.EnabledRoles"},
-			
 	};
 	
 	private String[][] DIAG_VTI_TABLE_FUNCTION_CLASSES =
@@ -12069,6 +12067,7 @@
 			{"SPACE_TABLE", "org.apache.derby.diag.SpaceTable"},
 			{"ERROR_LOG_READER", "org.apache.derby.diag.ErrorLogReader"},
 			{"STATEMENT_DURATION", "org.apache.derby.diag.StatementDuration"},
+			{"CONTAINED_ROLES", "org.apache.derby.diag.ContainedRoles"},
 	};
 
 	/**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_dbo.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_dbo.out?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_dbo.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_dbo.out Thu Oct 30 15:25:30 2008
@@ -13,6 +13,33 @@
 ------------------------------
 A                             
 B                             
+ij> select * from table(syscs_diag.contained_roles(current_role, 0)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles(current_role)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles(current_role, 1)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a', 0)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a')) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a', 1)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
 ij> set role none;
 0 rows inserted/updated/deleted
 ij> show enabled_roles;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_usr.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_usr.out?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_usr.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij_show_roles_usr.out Thu Oct 30 15:25:30 2008
@@ -13,6 +13,33 @@
 ------------------------------
 A                             
 B                             
+ij> select * from table(syscs_diag.contained_roles(current_role, 0)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles(current_role)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles(current_role, 1)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+B                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a', 0)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a')) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+ij> select * from table(syscs_diag.contained_roles('a', 1)) t order by roleid;
+ROLEID                                                                                                                          
+--------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               
+B                                                                                                                               
 ij> set role none;
 0 rows inserted/updated/deleted
 ij> show enabled_roles;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java Thu Oct 30 15:25:30 2008
@@ -679,15 +679,23 @@
     }
 
     /**
-     * Basic sanity test for SYSCS_DIAG.ENABLED_ROLES. See also the
-     * tools/ij_show_roles.sql test for a test that actually defines roles.
+     * Basic sanity test for SYSCS_DIAG.CONTAINED_ROLES. See also the
+     * tools/ij_show_roles.sql test for a test that actually defines and uses
+     * roles with this VTI.
      */
-    public void testEnabledRoles() throws SQLException
+    public void testContainedRoles() throws SQLException
     {
         Statement   st = createStatement();
 
+        // 2-arg version
         ResultSet rs = st.executeQuery
-            ("select * from syscs_diag.enabled_roles");
+            ("select * from table(syscs_diag.contained_roles(null, 0))t");
+
+        JDBC.assertEmpty(rs);
+
+        // 1-arg version
+        rs = st.executeQuery
+            ("select * from table(syscs_diag.contained_roles(null))t");
 
         JDBC.assertEmpty(rs);
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_dbo.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_dbo.sql?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_dbo.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_dbo.sql Thu Oct 30 15:25:30 2008
@@ -2,6 +2,12 @@
 show roles;
 set role b;
 show enabled_roles;
+select * from table(syscs_diag.contained_roles(current_role, 0)) t order by roleid;
+select * from table(syscs_diag.contained_roles(current_role)) t order by roleid;
+select * from table(syscs_diag.contained_roles(current_role, 1)) t order by roleid;
+select * from table(syscs_diag.contained_roles('a', 0)) t order by roleid;
+select * from table(syscs_diag.contained_roles('a')) t order by roleid;
+select * from table(syscs_diag.contained_roles('a', 1)) t order by roleid;
 set role none;
 show enabled_roles;
 show settable_roles;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_usr.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_usr.sql?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_usr.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij_show_roles_usr.sql Thu Oct 30 15:25:30 2008
@@ -2,6 +2,13 @@
 show roles;
 set role b;
 show enabled_roles;
+select * from table(syscs_diag.contained_roles(current_role, 0)) t order by roleid;
+select * from table(syscs_diag.contained_roles(current_role)) t order by roleid;
+select * from table(syscs_diag.contained_roles(current_role, 1)) t order by roleid;
+select * from table(syscs_diag.contained_roles('a', 0)) t order by roleid;
+select * from table(syscs_diag.contained_roles('a')) t order by roleid;
+select * from table(syscs_diag.contained_roles('a', 1)) t order by roleid;
 set role none;
 show enabled_roles;
 show settable_roles;
+

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?rev=709266&r1=709265&r2=709266&view=diff
==============================================================================
--- 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 Thu Oct 30 15:25:30 2008
@@ -864,7 +864,8 @@
 
 	/**
 	 * Return a resultset of enabled roles, sorted on ROLEID. No information
-	 * schema is available, we select from VTI SYSCS_DIAG.ENABLED_ROLES instead.
+	 * schema is available, we select from VTI SYSCS_DIAG.CONTAINED_ROLES
+	 * instead.
 	 */
 	public ijResult showEnabledRoles() throws SQLException {
 		ResultSet rs = null;
@@ -874,7 +875,10 @@
 			if (currentConnEnv.getSession().getIsDNC() ||
 				currentConnEnv.getSession().getIsEmbeddedDerby()) {
 				rs = theConnection.createStatement().executeQuery
-					("SELECT * FROM SYSCS_DIAG.ENABLED_ROLES ORDER BY ROLEID");
+					("SELECT * FROM" +
+					"	 TABLE(" +
+					"	   SYSCS_DIAG.CONTAINED_ROLES(CURRENT_ROLE)) T " +
+					"ORDER BY ROLEID");
 
 				int[] displayColumns = new int[] {
 					rs.findColumn("ROLEID")