Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 89520 invoked from network); 30 Mar 2007 04:49:35 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 30 Mar 2007 04:49:35 -0000 Received: (qmail 2696 invoked by uid 500); 30 Mar 2007 04:49:41 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 2668 invoked by uid 500); 30 Mar 2007 04:49:41 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 2657 invoked by uid 99); 30 Mar 2007 04:49:41 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 29 Mar 2007 21:49:41 -0700 X-ASF-Spam-Status: No, hits=-99.5 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 29 Mar 2007 21:49:32 -0700 Received: by eris.apache.org (Postfix, from userid 65534) id 05FC11A983E; Thu, 29 Mar 2007 21:49:12 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: djd@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070330044912.05FC11A983E@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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