You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2007/03/30 06:49:11 UTC
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
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