db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r523935 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/triggerGeneral.out tests/lang/TriggerTest.java tests/lang/triggerGeneral.sql
Date Fri, 30 Mar 2007 04:49:11 GMT
Author: djd
Date: Thu Mar 29 21:49:09 2007
New Revision: 523935

URL: http://svn.apache.org/viewvc?view=rev&rev=523935
Log:
DERBY-1102 More progress in converting triggerGeneral to Junit, testing of ordering of triggers
with constraints.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=523935&r1=523934&r2=523935
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
Thu Mar 29 21:49:09 2007
@@ -213,41 +213,6 @@
 0 rows inserted/updated/deleted
 ij> create table t (x int, y int, c char(1));
 0 rows inserted/updated/deleted
-ij> --
--- Test trigger firing order
---
-create trigger t1 after insert on t for each row
-	values app.triggerFiresMin('3rd');
-0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on t for each statement
-	values app.triggerFiresMin('1st');
-0 rows inserted/updated/deleted
-ij> create trigger t3 no cascade before insert on t for each row
-	values app.triggerFiresMin('4th');
-0 rows inserted/updated/deleted
-ij> create trigger t4 after insert on t for each row
-	values app.triggerFiresMin('2nd');
-0 rows inserted/updated/deleted
-ij> create trigger t5 no cascade before insert on t for each statement
-	values app.triggerFiresMin('5th');
-0 rows inserted/updated/deleted
-ij> insert into t values (1,1,'1');
-TRIGGER: <4th>
-TRIGGER: <5th>
-TRIGGER: <3rd>
-TRIGGER: <1st>
-TRIGGER: <2nd>
-1 row inserted/updated/deleted
-ij> drop trigger t1;
-0 rows inserted/updated/deleted
-ij> drop trigger t2;
-0 rows inserted/updated/deleted
-ij> drop trigger t3;
-0 rows inserted/updated/deleted
-ij> drop trigger t4;
-0 rows inserted/updated/deleted
-ij> drop trigger t5;
-0 rows inserted/updated/deleted
 ij> -- try multiple values, make sure result sets don't get screwed up
 -- this time we'll print out result sets
 create trigger t1 after insert on t for each row
@@ -385,7 +350,7 @@
 	{4,4,4}
 3 rows inserted/updated/deleted
 ij> delete from t;
-4 rows inserted/updated/deleted
+3 rows inserted/updated/deleted
 ij> drop trigger t1;
 0 rows inserted/updated/deleted
 ij> drop trigger t2;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?view=diff&rev=523935&r1=523934&r2=523935
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Thu Mar 29 21:49:09 2007
@@ -83,12 +83,24 @@
         conn.setAutoCommit(false);
     }
     
+    protected void setUp() throws Exception
+    {
+        Statement s = createStatement();
+        s.executeUpdate("CREATE PROCEDURE TRIGGER_LOG_INFO(" +
+                "O VARCHAR(255)) " +
+                "NO SQL PARAMETER STYLE JAVA LANGUAGE JAVA " +
+                "EXTERNAL NAME " +
+                "'" + getClass().getName() + ".logTriggerInfo'");
+        s.close();
+
+    }
+    
     protected void tearDown() throws Exception
     {
         TRIGGER_INFO.set(null);
-        
         JDBC.dropSchema(getConnection().getMetaData(),
                 getTestConfiguration().getUserName());
+
         super.tearDown();
     }
     
@@ -107,11 +119,80 @@
     {
         Statement s = createStatement();
         s.executeUpdate("CREATE TABLE T(ID INT)");
-        s.executeUpdate("CREATE PROCEDURE TRIGGER_LOG_INFO(" +
-                "O VARCHAR(255)) " +
-                "NO SQL PARAMETER STYLE JAVA LANGUAGE JAVA " +
-                "EXTERNAL NAME " +
-                "'" + getClass().getName() + ".logTriggerInfo'");
+        
+        int triggerCount = createRandomTriggers()[0];
+        
+        List info = new ArrayList();
+        TRIGGER_INFO.set(info);
+        
+        // Check ordering with a single row.
+        s.execute("INSERT INTO T VALUES 1");
+        commit();
+        int fireCount = assertFiringOrder("INSERT", 1);
+        info.clear();
+        
+        s.execute("UPDATE T SET ID = 2");
+        commit();
+        fireCount += assertFiringOrder("UPDATE", 1);
+        info.clear();
+        
+        s.execute("DELETE FROM T");
+        commit();
+        fireCount += assertFiringOrder("DELETE", 1);
+        info.clear();
+           
+        assertEquals("All triggers fired?", triggerCount, fireCount);
+
+        // and now with multiple rows
+        s.execute("INSERT INTO T VALUES 1,2,3");
+        commit();
+        fireCount = assertFiringOrder("INSERT", 3);
+        info.clear();
+        
+        s.execute("UPDATE T SET ID = 2");
+        commit();
+        fireCount += assertFiringOrder("UPDATE", 3);
+        info.clear();
+        
+        s.execute("DELETE FROM T");
+        commit();
+        fireCount += assertFiringOrder("DELETE", 3);
+        info.clear();
+        
+        // cannot assume row triggers were created so can only
+        // say that at least all the triggers were fired.
+        assertTrue("Sufficient triggers fired?", fireCount >= triggerCount);
+        
+        
+        // and then with no rows
+        assertTableRowCount("T", 0);
+        s.execute("INSERT INTO T SELECT ID FROM T");
+        commit();
+        fireCount = assertFiringOrder("INSERT", 0);
+        info.clear();
+        
+        s.execute("UPDATE T SET ID = 2");
+        commit();
+        fireCount += assertFiringOrder("UPDATE", 0);
+        info.clear();
+        
+        s.execute("DELETE FROM T");
+        commit();
+        fireCount += assertFiringOrder("DELETE", 0);
+        info.clear();
+        
+        // can't assert anthing about fireCount, could be all row triggers.
+            
+        s.close();
+
+    }
+    
+    private int[] createRandomTriggers() throws SQLException
+    {
+        Statement s = createStatement();
+        
+        int beforeCount = 0;
+        int afterCount = 0;
         
         Random r = new Random();
         // Randomly generate a number of triggers.
@@ -127,10 +208,13 @@
             sb.append(" ");
             
             String before;
-            if (r.nextInt(2) == 0)
+            if (r.nextInt(2) == 0) {
                 before = "NO CASCADE BEFORE";
-            else
+                beforeCount++;
+            } else {
                 before = "AFTER";
+                afterCount++;
+            }
             sb.append(before);
             sb.append(" ");
             
@@ -167,69 +251,57 @@
             s.execute(sb.toString());
         }
         commit();
-        
-        TRIGGER_INFO.set(new ArrayList());
-        
-        // Check ordering with a single row.
-        s.execute("INSERT INTO T VALUES 1");
-        commit();
-        int fireCount = assertFiringOrder("INSERT", 1);
-        ((List) TRIGGER_INFO.get()).clear();
-        
-        s.execute("UPDATE T SET ID = 2");
-        commit();
-        fireCount += assertFiringOrder("UPDATE", 1);
-        ((List) TRIGGER_INFO.get()).clear();
-        
-        s.execute("DELETE FROM T");
-        commit();
-        fireCount += assertFiringOrder("DELETE", 1);
-        ((List) TRIGGER_INFO.get()).clear();
-           
-        assertEquals("All triggers fired?", triggerCount, fireCount);
-
-        // and now with multiple rows
-        s.execute("INSERT INTO T VALUES 1,2,3");
-        commit();
-        fireCount = assertFiringOrder("INSERT", 3);
-        ((List) TRIGGER_INFO.get()).clear();
-        
-        s.execute("UPDATE T SET ID = 2");
-        commit();
-        fireCount += assertFiringOrder("UPDATE", 3);
-        ((List) TRIGGER_INFO.get()).clear();
-        
-        s.execute("DELETE FROM T");
-        commit();
-        fireCount += assertFiringOrder("DELETE", 3);
-        ((List) TRIGGER_INFO.get()).clear();
-        
-        // cannot assume row triggers were created so can only
-        // say that at least all the triggers were fired.
-        assertTrue("Sufficient triggers fired?", fireCount >= triggerCount);
-        
-        
-        // and then with no rows
-        assertTableRowCount("T", 0);
-        s.execute("INSERT INTO T SELECT ID FROM T");
+        s.close();
+        return new int[] {triggerCount, beforeCount, afterCount};
+    }
+    
+    
+    /**
+     * Test that a order of firing is before triggers,
+     * constraint checking and after triggers.
+     * @throws SQLException 
+     *
+     */
+    public void testFiringConstraintOrder() throws SQLException
+    {
+        Statement s = createStatement();
+        s.execute("CREATE TABLE T (I INT PRIMARY KEY," +
+                "U INT NOT NULL UNIQUE, C INT CHECK (C < 20))");
+        s.execute("INSERT INTO T VALUES(1,5,10)");
+        s.execute("INSERT INTO T VALUES(11,19,3)");
         commit();
-        fireCount = assertFiringOrder("INSERT", 0);
-        ((List) TRIGGER_INFO.get()).clear();
         
-        s.execute("UPDATE T SET ID = 2");
-        commit();
-        fireCount += assertFiringOrder("UPDATE", 0);
-        ((List) TRIGGER_INFO.get()).clear();
+        int beforeCount = createRandomTriggers()[1];
         
-        s.execute("DELETE FROM T");
-        commit();
-        fireCount += assertFiringOrder("DELETE", 0);
-        ((List) TRIGGER_INFO.get()).clear();
+        List info = new ArrayList();
+        TRIGGER_INFO.set(info);
         
-        // can't assert anthing about fireCount, could be all row triggers.
-            
-        s.close();
+        // constraint violation on primary key
+        assertStatementError("23505", s, "INSERT INTO T VALUES (1,6,10)");
+        assertFiringOrder("INSERT", 1, true);        
+        info.clear();
+        assertStatementError("23505", s, "UPDATE T SET I=1 WHERE I = 11");
+        assertFiringOrder("UPDATE", 1, true);        
+        info.clear();
+        
+        // constraint violation on unique key
+        assertStatementError("23505", s, "INSERT INTO T VALUES (2,5,10)");
+        assertFiringOrder("INSERT", 1, true);        
+        info.clear();
+        assertStatementError("23505", s, "UPDATE T SET U=5 WHERE I = 11");
+        assertFiringOrder("UPDATE", 1, true);        
+        info.clear();
+        
+        // check constraint
+        assertStatementError("23513", s, "INSERT INTO T VALUES (2,6,22)");
+        assertFiringOrder("INSERT", 1, true);        
+        info.clear();
+        assertStatementError("23513", s, "UPDATE T SET C=C+40 WHERE I = 11");
+        assertFiringOrder("UPDATE", 1, true);        
+        info.clear();
 
+        s.close();
+        commit();
     }
     
     /**
@@ -241,6 +313,11 @@
      */
     private int assertFiringOrder(String iud, int modifiedRowCount)
     {
+        return assertFiringOrder(iud, modifiedRowCount, false);
+    }
+    private int assertFiringOrder(String iud, int modifiedRowCount,
+            boolean noAfter)
+    {
         List fires = (List) TRIGGER_INFO.get();
         
         int lastOrder = -1;
@@ -263,6 +340,8 @@
             if (modifiedRowCount == 0)
                assertEquals("Row trigger firing on no rows",
                        "STATEMENT", row);
+            if (noAfter)
+                assertFalse("No AFTER triggers", "AFTER".equals(before));
             
             // First trigger.
             if (lastOrder == -1)
@@ -302,6 +381,7 @@
     
     /**
      * Record the trigger information in the thread local.
+     * Called as a SQL procedure.
      * @param info trigger information
       */
     public static void logTriggerInfo(String info)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=523935&r1=523934&r2=523935
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Thu Mar 29 21:49:09 2007
@@ -166,26 +166,6 @@
 
 create table t (x int, y int, c char(1));
 
---
--- Test trigger firing order
---
-create trigger t1 after insert on t for each row
-	values app.triggerFiresMin('3rd');
-create trigger t2 after insert on t for each statement
-	values app.triggerFiresMin('1st');
-create trigger t3 no cascade before insert on t for each row
-	values app.triggerFiresMin('4th');
-create trigger t4 after insert on t for each row
-	values app.triggerFiresMin('2nd');
-create trigger t5 no cascade before insert on t for each statement
-	values app.triggerFiresMin('5th');
-insert into t values (1,1,'1');
-drop trigger t1;
-drop trigger t2;
-drop trigger t3;
-drop trigger t4;
-drop trigger t5;
-
 -- try multiple values, make sure result sets don't get screwed up
 -- this time we'll print out result sets
 create trigger t1 after insert on t for each row



Mime
View raw message