db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r892354 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: UDTPermsTest.java UDTTest.java
Date Fri, 18 Dec 2009 19:21:59 GMT
Author: rhillegas
Date: Fri Dec 18 19:21:58 2009
New Revision: 892354

URL: http://svn.apache.org/viewvc?rev=892354&view=rev
Log:
DERBY-651: Add tests verifying that you can't drop a UDT or revoke USAGE on it if this would
orphan a trigger.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java?rev=892354&r1=892353&r2=892354&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
Fri Dec 18 19:21:58 2009
@@ -384,6 +384,25 @@
              dropStatement,
              badRevokeSQLState
              );
+
+        // can't revoke USAGE if a trigger depends on it
+        goodStatement( aliceConnection, "create table t_03_a( a int )\n" );
+        goodStatement( aliceConnection, "create table t_03_b( a int )\n" );
+        createStatement =
+            "create trigger trig_03_a after insert on t_03_a\n" +
+            "  insert into t_03_b( a ) select a from t_03_a where ( cast( null as ruth.price_ruth_02_a
) ) is not null\n";
+        dropStatement = "drop trigger trig_03_a\n";
+        badRevokeSQLState = OPERATION_FORBIDDEN;
+        verifyRevokePrivilege
+            (
+             ruthConnection,
+             aliceConnection,
+             grantUsage,
+             revokeUsage,
+             createStatement,
+             dropStatement,
+             badRevokeSQLState
+             );
     }
     
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java?rev=892354&r1=892353&r2=892354&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Fri Dec 18 19:21:58 2009
@@ -52,6 +52,7 @@
     public static final String NONEXISTENT_OBJECT = "42Y55";
     public static final String SYNTAX_ERROR = "42X01";
     public static final String VIEW_DEPENDS_ON_TYPE = "X0Y23";
+    public static final String TRIGGER_DEPENDS_ON_TYPE = "X0Y24";
 
     ///////////////////////////////////////////////////////////////////////////////////
     //
@@ -421,42 +422,48 @@
     {
         Connection conn = getConnection();
 
+        String createTypeStatement;
+        String dropTypeStatement;
+        String createObjectStatement;
+        String dropObjectStatement;
+        String badDropSQLState;
+        
         // view with UDT in select list
-        goodStatement
-            ( conn,
-              "create type price_05_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
-            (
-             conn,
+        createTypeStatement = "create type price_05_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_05_a restrict\n";
+        createObjectStatement = 
              "create view udtView( a, b, c ) as\n" +
              "select tabletype, cast (null as price_05_a), cast( null as price_05_a)\n" +
-             "from sys.systables\n"
-              );
-        expectExecutionError( conn, VIEW_DEPENDS_ON_TYPE, "drop type price_05_a restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop view udtView\n" );
-        goodStatement
-            ( conn,
-              "drop type price_05_a restrict\n" );
+            "from sys.systables\n";
+        dropObjectStatement = "drop view udtView\n";
+        badDropSQLState = VIEW_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
         // view with UDT in where clause
-        goodStatement
-            ( conn,
-              "create type price_05_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
+        createTypeStatement = "create type price_05_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_05_b restrict\n";
+        createObjectStatement = 
+             "create view udtView_b( a ) as\n" +
+            "select tabletype from sys.systables where ( cast (null as price_05_b) ) is not
null\n";
+        dropObjectStatement = "drop view udtView_b\n";
+        badDropSQLState = VIEW_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
             (
              conn,
-             "create view udtView_b( a ) as\n" +
-             "select tabletype from sys.systables where ( cast (null as price_05_b) ) is
not null\n"
-              );
-        expectExecutionError( conn, VIEW_DEPENDS_ON_TYPE, "drop type price_05_b restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop view udtView_b\n" );
-        goodStatement
-            ( conn,
-              "drop type price_05_b restrict\n" );
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
     }
 
@@ -505,90 +512,138 @@
     {
         Connection conn = getConnection();
 
+        String createTypeStatement;
+        String dropTypeStatement;
+        String createObjectStatement;
+        String dropObjectStatement;
+        String badDropSQLState;
+        
         // function that returns a udt
-        goodStatement
-            ( conn,
-              "create type price_07_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
-            ( conn,
-              "create function makePrice_07_a( )\n" +
-              "returns price_07_a\n" +
-              "language java\n" +
-              "parameter style java\n" +
-              "no sql\n" +
-              "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"
-              );
-        expectExecutionError( conn, ROUTINE_DEPENDS_ON_TYPE, "drop type price_07_a restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop function makePrice_07_a\n" );
-        goodStatement
-            ( conn,
-              "drop type price_07_a restrict\n" );
+        createTypeStatement = "create type price_07_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_07_a restrict\n";
+        createObjectStatement = 
+            "create function makePrice_07_a( )\n" +
+            "returns price_07_a\n" +
+            "language java\n" +
+            "parameter style java\n" +
+            "no sql\n" +
+            "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n";
+        dropObjectStatement = "drop function makePrice_07_a\n";
+        badDropSQLState = ROUTINE_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
         // function with a udt arg
-        goodStatement
-            ( conn,
-              "create type price_07_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
-            ( conn,
-              "create function getCurrencyCode_07_b(  priceArg1 price_07_b  )\n" +
-              "returns char( 3 )\n" +
-              "language java\n" +
-              "parameter style java\n" +
-              "no sql\n" +
-              "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode'\n"
-              );
-        expectExecutionError( conn, ROUTINE_DEPENDS_ON_TYPE, "drop type price_07_b restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop function getCurrencyCode_07_b\n" );
-        goodStatement
-            ( conn,
-              "drop type price_07_b restrict\n" );
+        createTypeStatement = "create type price_07_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_07_b restrict\n";
+        createObjectStatement = 
+            "create function getCurrencyCode_07_b(  priceArg1 price_07_b  )\n" +
+            "returns char( 3 )\n" +
+            "language java\n" +
+            "parameter style java\n" +
+            "no sql\n" +
+            "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode'\n";
+        dropObjectStatement = "drop function getCurrencyCode_07_b\n";
+        badDropSQLState = ROUTINE_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
         // procedure with a udt arg
-        goodStatement
-            ( conn,
-              "create type price_07_c external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
-            ( conn,
-              "create procedure oneArgPriceProc_07( price1 price_07_c )\n" +
-              "language java\n" +
-              "parameter style java\n" +
-              "no sql\n" +
-              "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.oneArgPriceProc_07'\n"
-              );
-        expectExecutionError( conn, ROUTINE_DEPENDS_ON_TYPE, "drop type price_07_c restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop procedure oneArgPriceProc_07\n" );
-        goodStatement
-            ( conn,
-              "drop type price_07_c restrict\n" );
+        createTypeStatement = "create type price_07_c external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_07_c restrict\n";
+        createObjectStatement = 
+            "create procedure oneArgPriceProc_07( price1 price_07_c )\n" +
+            "language java\n" +
+            "parameter style java\n" +
+            "no sql\n" +
+            "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.oneArgPriceProc_07'\n";
+        dropObjectStatement = "drop procedure oneArgPriceProc_07\n";
+        badDropSQLState = ROUTINE_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
         // procedure with two udt args
-        goodStatement
-            ( conn,
-              "create type price_07_d external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n" );
-        goodStatement
-            ( conn,
-              "create procedure twoArgPriceProc_07( price1 price_07_d, price2 price_07_d
)\n" +
-              "language java\n" +
-              "parameter style java\n" +
-              "no sql\n" +
-              "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.twoArgPriceProc_07'\n"
-              );
-        expectExecutionError( conn, ROUTINE_DEPENDS_ON_TYPE, "drop type price_07_d restrict\n"
);
-        goodStatement
-            ( conn,
-              "drop procedure twoArgPriceProc_07\n" );
-        goodStatement
-            ( conn,
-              "drop type price_07_d restrict\n" );
+        createTypeStatement = "create type price_07_d external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_07_d restrict\n";
+        createObjectStatement = 
+            "create procedure twoArgPriceProc_07( price1 price_07_d, price2 price_07_d )\n"
+
+            "language java\n" +
+            "parameter style java\n" +
+            "no sql\n" +
+            "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.twoArgPriceProc_07'\n";
+        dropObjectStatement = "drop procedure twoArgPriceProc_07\n";
+        badDropSQLState = ROUTINE_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
 
     }
 
+    /**
+     * <p>
+     * Dependencies of triggers on UDTs.
+     * </p>
+     */
+    public void test_08_triggerDependencies() throws Exception
+    {
+        Connection conn = getConnection();
+
+        goodStatement( conn, "create table t_08_a( a int )" );
+        goodStatement( conn, "create table t_08_b( a int )" );
+
+        String createTypeStatement;
+        String dropTypeStatement;
+        String createObjectStatement;
+        String dropObjectStatement;
+        String badDropSQLState;
+        
+        // trigger that mentions a udt
+        createTypeStatement = "create type price_08_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price'
language java\n";
+        dropTypeStatement = "drop type price_08_a restrict\n";
+        createObjectStatement = 
+            "create trigger trig_08_a after insert on t_08_a\n" +
+            "  insert into t_08_b( a ) select ( a ) from t_08_a where ( cast( null as price_08_a
) ) is not null\n";
+        dropObjectStatement = "drop trigger trig_08_a";
+        badDropSQLState = TRIGGER_DEPENDS_ON_TYPE;
+        verifyDropRestrictions
+            (
+             conn,
+             createTypeStatement,
+             dropTypeStatement,
+             createObjectStatement,
+             dropObjectStatement,
+             badDropSQLState
+             );
+
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // PROCEDURES
@@ -604,6 +659,27 @@
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    /**
+     * Verify that a type can't be dropped if it is used by a schema object.
+     */
+    private void verifyDropRestrictions
+        (
+         Connection conn,
+         String createTypeStatement,
+         String dropTypeStatement,
+         String createObjectStatement,
+         String dropObjectStatement,
+         String badDropSQLState
+         )
+        throws Exception
+    {
+        goodStatement( conn, createTypeStatement );
+        goodStatement( conn, createObjectStatement );
+        expectExecutionError( conn, badDropSQLState, dropTypeStatement );
+        goodStatement( conn, dropObjectStatement );
+        goodStatement( conn, dropTypeStatement );
+    }
+
     /** Get the number of dependencies that a table has */
     private int countTableDependencies( Connection conn, String tableName ) throws Exception
     {



Mime
View raw message