Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 41942 invoked from network); 22 Jun 2010 02:42:39 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 22 Jun 2010 02:42:39 -0000 Received: (qmail 20060 invoked by uid 500); 22 Jun 2010 02:42:39 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 19990 invoked by uid 500); 22 Jun 2010 02:42:38 -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 19982 invoked by uid 99); 22 Jun 2010 02:42:38 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jun 2010 02:42:38 +0000 X-ASF-Spam-Status: No, hits=-1251.0 required=10.0 tests=ALL_TRUSTED,AWL 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; Tue, 22 Jun 2010 02:42:35 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 51EAC23889B3; Tue, 22 Jun 2010 02:41:50 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r956763 [2/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/catalog/types/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/co... Date: Tue, 22 Jun 2010 02:41:49 -0000 To: derby-commits@db.apache.org From: mamta@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100622024150.51EAC23889B3@eris.apache.org> Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/memory/TriggerTests.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/memory/TriggerTests.java?rev=956763&r1=956762&r2=956763&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/memory/TriggerTests.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/memory/TriggerTests.java Tue Jun 22 02:41:48 2010 @@ -19,7 +19,8 @@ import org.apache.derbyTesting.junit.Tes public class TriggerTests extends BaseJDBCTestCase { final int lobsize = 300000*1024; - boolean isDerby1482Fixed = false; + boolean testWithLargeDataInLOB = true; + /** * Insert trigger tests * **************** @@ -216,11 +217,16 @@ public class TriggerTests extends BaseJD * clause, meaning that before and after values are not available to * the trigger action. * **************** - * 15)test5UpdateBeforeTriggerNoReferencingClause + * 16)test5UpdateBeforeTriggerNoReferencingClause * This test creates an BEFORE UPDATE trigger but has no REFERENCING * clause, meaning that before and after values are not available to * the trigger action. * **************** + * 17)test6UpdateAfterTriggerNoTriggerColumn + * This test create an AFTER UPDATE trigger but does not identify any + * trigger columns. It has REFERENCING clause. Void of trigger columns + * will cause all the columns to be read into memory. + * **************** */ public TriggerTests(String name) { super(name); @@ -230,7 +236,8 @@ public class TriggerTests extends BaseJD Test suite = new CleanDatabaseTestSetup(TestConfiguration .embeddedSuite(TriggerTests.class)); Properties p = new Properties(); - // use small pageCacheSize so we don't run out of memory on the insert. + // use small pageCacheSize so we don't run out of memory on the insert + // of large LOB columns. p.setProperty("derby.storage.pageCacheSize", "100"); return new SystemPropertyTestSetup(suite,p); } @@ -299,20 +306,18 @@ public class TriggerTests extends BaseJD /** * This test creates an AFTER INSERT trigger which inserts non-lob - * columns into another table. + * columns into another table. The triggering INSERT does not insert + * any value into LOB column * @throws SQLException */ - public void test1InsertAfterTrigger() throws SQLException{ - if (isDerby1482Fixed == false) - return; - + public void test1InsertAfterTrigger() throws SQLException{ basicSetup(); Statement s = createStatement(); s.execute("create trigger trigger1 AFTER INSERT on table1 referencing " + "new as n_row for each row " + "insert into table2(id, updates) values (n_row.id, -1)"); commit(); - runtest2InsertTriggerTest(); + runtest1InsertTriggerTest(); } /** @@ -323,20 +328,17 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1InsertAfterTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); s.execute("create procedure proc_test1_InsertAfterTrigger_update_table " + "(p1 int) parameter style java language "+ "java MODIFIES SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_InsertAfterTrigger_update_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_InsertAfterTrigger_update_table'"); s.execute("create trigger trigger1 after INSERT on table1 referencing " + "new as n_row for each row " + "call proc_test1_InsertAfterTrigger_update_table(n_row.id)"); commit(); - runtest2InsertTriggerTest(); + runtest1InsertTriggerTest(); } /** @@ -346,7 +348,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_InsertAfterTrigger_update_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the INSERT AFTER TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement( "insert into table2(id, updates) values (" + p1 + ",-1)"); @@ -357,10 +358,15 @@ public class TriggerTests extends BaseJD /** * This test creates an AFTER DELETE trigger which delets from another * table using non-lob from the triggering table in the where clause. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test1DeleteAfterTrigger() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -377,10 +383,15 @@ public class TriggerTests extends BaseJD * trigger action is a stored procedure call. The work done by the trigger * action SQL in test1DeleteAfterTrigger gets done inside the stored procedure * for this test. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test1DeleteAfterTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -388,7 +399,7 @@ public class TriggerTests extends BaseJD s.execute("create procedure proc_test1_DeleteAfterTrigger_update_table " + "(p1 int) parameter style java language "+ "java MODIFIES SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_DeleteAfterTrigger_update_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_DeleteAfterTrigger_update_table'"); s.execute("create trigger trigger1 after DELETE on table1 referencing " + "old as o_row for each row " + @@ -404,7 +415,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_DeleteAfterTrigger_update_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the DELETE AFTER TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement( "delete from table1 where id=" + p1); @@ -431,9 +441,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1UpdateAfterTrigger() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); s.execute("create trigger trigger1 after update of status on table1 referencing " + @@ -451,15 +458,12 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1UpdateAfterTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); s.execute("create procedure proc_test1_UpdateAfterTrigger_update_table " + "(p1 int) parameter style java language "+ "java MODIFIES SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_UpdateAfterTrigger_update_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_UpdateAfterTrigger_update_table'"); s.execute("create trigger trigger1 after update of status on table1 REFERENCING " + "NEW as n_row for each row call proc_test1_UpdateAfterTrigger_update_table(n_row.id)"); @@ -474,7 +478,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_UpdateAfterTrigger_update_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the UPDATE AFTER TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("update table2 "+ "set updates = updates + 1 where table2.id = " + p1); @@ -489,8 +492,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1InsertBeforeTrigger() throws SQLException{ - if (isDerby1482Fixed == false) - return; basicSetup(); Statement s = createStatement(); @@ -498,7 +499,7 @@ public class TriggerTests extends BaseJD "new as n_row for each row " + "select updates from table2 where table2.id = n_row.id"); commit(); - runtest2InsertTriggerTest(); + runtest1InsertTriggerTest(); } /** @@ -509,19 +510,16 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1InsertBeforeTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); s.execute("create procedure proc_test1_InsertBeforeTrigger_select_table " + "(p1 int) parameter style java language "+ "java READS SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_InsertBeforeTrigger_select_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_InsertBeforeTrigger_select_table'"); s.execute("create trigger trigger1 no cascade before INSERT on table1 referencing " + "new as n_row for each row call proc_test1_InsertBeforeTrigger_select_table(n_row.id)"); commit(); - runtest2InsertTriggerTest(); + runtest1InsertTriggerTest(); } /** @@ -531,7 +529,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_InsertBeforeTrigger_select_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the INSERT BEFORE TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("select updates from " + "table2 where table2.id = " + p1); @@ -543,10 +540,15 @@ public class TriggerTests extends BaseJD * This test creates a BEFORE DELETE trigger which selects * columns from another table using "new" non-lob column for * join clause. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test1DeleteBeforeTrigger() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -563,10 +565,15 @@ public class TriggerTests extends BaseJD * trigger action is a stored procedure call. The work done by the trigger * action SQL in test1DeleteBeforeTrigger gets done inside the stored procedure * for this test. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test1DeleteBeforeTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -575,7 +582,7 @@ public class TriggerTests extends BaseJD s.execute("create procedure proc_test1_DeleteBeforeTrigger_select_table " + "(p1 int) parameter style java language "+ "java READS SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_DeleteBeforeTrigger_select_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_DeleteBeforeTrigger_select_table'"); s.execute("create trigger trigger1 no cascade before DELETE on table1 referencing " + "old as o_row for each row call proc_test1_DeleteBeforeTrigger_select_table(o_row.id)"); @@ -590,7 +597,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_DeleteBeforeTrigger_select_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the DELETE BEFORE TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("select updates from " + "table2 where table2.id = " + p1); @@ -619,9 +625,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1UpdateBeforeTrigger() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); @@ -640,15 +643,12 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public void test1UpdateBeforeTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) - return; - basicSetup(); Statement s = createStatement(); s.execute("create procedure proc_test1_UpdateBeforeTrigger_select_table " + "(p1 int) parameter style java language "+ "java READS SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test1_UpdateBeforeTrigger_select_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test1_UpdateBeforeTrigger_select_table'"); s.execute("create trigger trigger1 no cascade before update of status on table1 REFERENCING " + "NEW as n_row for each row call proc_test1_UpdateBeforeTrigger_select_table(n_row.id)"); @@ -663,7 +663,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test1_UpdateBeforeTrigger_select_table(int p1) throws SQLException { - System.out.println("Test1 : Inside the procedure called by the UPDATE BEFORE TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("select updates from " + "table2 where table2.id = " + p1); @@ -676,10 +675,15 @@ public class TriggerTests extends BaseJD * inserts lob columns from triggering table into another table. So, this * test does access the LOB from the triggering table inside the trigger * action. + * + * INSERT trigger in this test is inserting large data in the LOB column + * which will be used in the INSERT trigger and hence it will run out of + * memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test2InsertAfterTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -707,10 +711,15 @@ public class TriggerTests extends BaseJD * deletes row from another table using triggering table's "new" LOB value * in the join clause. So, this test does access the LOB from the * triggering table inside the trigger action. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test2DeleteAfterTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -753,10 +762,14 @@ public class TriggerTests extends BaseJD * table1 but the update that caused the trigger is not updating * the BLOB column * + * UPDATE trigger in this test is working with large data in the LOB column + * inside the trigger action and hence it will run out of memory. For that + * reason, the test is disabled. + * * @throws SQLException */ public void test2UpdateAfterTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -783,10 +796,15 @@ public class TriggerTests extends BaseJD * updates a lob column from the row just inserted. So, this test does * update the LOB from the triggering table inside the trigger * action. + * + * INSERT trigger in this test is inserting large data in the LOB column + * which will be used in the INSERT trigger and hence it will run out of + * memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test2InsertAfterTriggerUpdatedLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -815,10 +833,15 @@ public class TriggerTests extends BaseJD * updates a lob column from the row that just got updated. So, this test * does update the LOB from the triggering table inside the trigger * action. + * + * UPDATE trigger in this test is working with large data in the LOB column + * inside the trigger action and hence it will run out of memory. For that + * reason, the test is disabled. + * * @throws SQLException */ public void test2UpdateAfterTriggerUpdatedLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -846,10 +869,15 @@ public class TriggerTests extends BaseJD * This test creates a BEFORE INSERT trigger which selects "new" * lob column from just inserted row. This test does access the * LOB. + * + * INSERT trigger in this test is inserting large data in the LOB column + * which will be used in the INSERT trigger action and hence it will run + * out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test2InsertBeforeTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -875,10 +903,15 @@ public class TriggerTests extends BaseJD * This test creates a BEFORE DELETE trigger which selects "old" * lob column from just deleted row. This test does access the * LOB. + * + * DELETE triggers read all the columns from the trigger table. Following + * test is on a trigger table with large data in LOB columns and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test2DeleteBeforeTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -905,10 +938,15 @@ public class TriggerTests extends BaseJD * This test creates a BEFORE UPDATE trigger which selects "new" * lob column from just updated row. This test does access the * LOB. + * + * UPDATE trigger in this test is working with large data in the LOB column + * inside the trigger action and hence it will run out of memory. For that + * reason, the test is disabled. + * * @throws SQLException */ public void test2UpdateBeforeTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -946,10 +984,13 @@ public class TriggerTests extends BaseJD * the trigger action will update table2 with non-LOB value from * table1 * + * UPDATE trigger is defined on LOB column with large data and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test3UpdateAfterTrigger() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -967,10 +1008,13 @@ public class TriggerTests extends BaseJD * action SQL in test3UpdateAfterTrigger gets done inside the stored procedure * for this test. * + * UPDATE trigger is defined on LOB column with large data and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test3UpdateAfterTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -979,7 +1023,7 @@ public class TriggerTests extends BaseJD s.execute("create procedure proc_test3_UpdateAfterTrigger_update_table " + "(p1 int, p2 int) parameter style java language "+ "java MODIFIES SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test3_UpdateAfterTrigger_update_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test3_UpdateAfterTrigger_update_table'"); s.execute("create trigger trigger1 after update of bl on table1 REFERENCING " + "NEW as n_row for each row call proc_test3_UpdateAfterTrigger_update_table(n_row.status, n_row.id)"); @@ -995,7 +1039,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test3_UpdateAfterTrigger_update_table(int p1, int p2) throws SQLException { - System.out.println("Test3 : Inside the procedure called by the UPDATE AFTER TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("update table2 "+ "set updates = " + p1 + " where table2.id = " + p2); @@ -1008,10 +1051,14 @@ public class TriggerTests extends BaseJD * from another table using "new" non-LOB column from the triggering * table. This test has update trigger defined on the LOB column * but does not access/update that LOB column in the trigger action. + * + * UPDATE trigger is defined on LOB column with large data and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test3UpdateBeforeTrigger() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -1029,10 +1076,14 @@ public class TriggerTests extends BaseJD * trigger action is a stored procedure call. The work done by the trigger * action SQL in test3UpdateBeforeTrigger gets done inside the stored procedure * for this test. + * + * UPDATE trigger is defined on LOB column with large data and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test3UpdateBeforeTriggerStoredProc() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -1041,7 +1092,7 @@ public class TriggerTests extends BaseJD s.execute("create procedure proc_test3_UpdateBeforeTrigger_select_table " + "(p1 int) parameter style java language "+ "java READS SQL DATA external name "+ - "'org.apache.derbyTesting.functionTests.tests.lang.derby1482TriggerTests.proc_test3_UpdateBeforeTrigger_select_table'"); + "'org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.proc_test3_UpdateBeforeTrigger_select_table'"); s.execute("create trigger trigger1 no cascade before update of bl on table1 REFERENCING " + "NEW as n_row for each row call proc_test3_UpdateBeforeTrigger_select_table(n_row.id)"); @@ -1056,7 +1107,6 @@ public class TriggerTests extends BaseJD * @throws SQLException */ public static void proc_test3_UpdateBeforeTrigger_select_table(int p1) throws SQLException { - System.out.println("Test3 : Inside the procedure called by the UPDATE BEFORE TRIGGER action"); Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement("select updates from " + "table2 where table2.id = " + p1); @@ -1077,10 +1127,13 @@ public class TriggerTests extends BaseJD * trigger got fired for update of LOB column on the triggering * table. * + * UPDATE trigger is defined on LOB column with large data and hence it + * will run out of memory. For that reason, the test is disabled. + * * @throws SQLException */ public void test4UpdateAfterTriggerAccessLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -1102,15 +1155,20 @@ public class TriggerTests extends BaseJD commit(); runtest2UpdateTrigger(); } - + /** * The after update trigger on LOB column which then gets updated in the * trigger action. So this test updates the LOB in the trigger action - * and is also the cause of the update trigger to fire. + * and is also the cause of the update trigger to fire. + * + * The UPDATE trigger access the large data in LOB column inside the + * trigger action which will cause the test to run out of memory. For + * this reason, this test is disabled. + * * @throws SQLException */ public void test4UpdateAfterTriggerUpdatedLOB() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -1140,10 +1198,15 @@ public class TriggerTests extends BaseJD * This test creates a BEFORE UPDATE trigger on LOB column and * the trigger action selects "new" lob column from just updated * row. This test does access the LOB. + * + * The UPDATE trigger access the large data in LOB column inside the + * trigger action and it is defined on the LOB column which will cause + * the test to run out of memory. For this reason, this test is disabled. + * * @throws SQLException */ public void test4UpdateBeforeTrigger() throws SQLException{ - if (isDerby1482Fixed == false) + if (testWithLargeDataInLOB) return; basicSetup(); @@ -1257,6 +1320,39 @@ public class TriggerTests extends BaseJD } /** + * This test create an AFTER UPDATE trigger but does not identify any + * trigger columns. It has REFERENCING clause. Void of trigger columns + * will cause all the columns to be read into memory. + * + * When no trigger columns are defined for an UPDATE trigger, all the + * columns get read into memory. Since the trigger table has large data + * in LOB columns, it will run out of memory. For that reason, the test + * is disabled. + */ + public void test6UpdateAfterTriggerNoTriggerColumn() throws SQLException{ + if (testWithLargeDataInLOB) + return; + + basicSetup(); + Statement s = createStatement(); + + //The default table2 created by basicSetup does not match the + //requirement of this test so dropping and recreating it. + s.execute("drop table table2"); + s.execute("create table table2 (id int, bl_table2 blob(2G))"); + s.execute("create trigger trigger1 after update on table1 referencing " + + "new as n_row for each row " + + "update table2 set bl_table2 = n_row.bl where table2.id = n_row.id"); + + PreparedStatement ps = prepareStatement( + "insert into table2 (id) values (?)"); + ps.setInt(1, 1); + ps.executeUpdate(); + commit(); + runtest2UpdateTrigger(); + } + + /** * Following will do an insert into table1 which will cause insert * trigger to fire. The insert does not involve the LOB column. * Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java?rev=956763&r1=956762&r2=956763&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java Tue Jun 22 02:41:48 2010 @@ -140,6 +140,133 @@ public class Changes10_7 extends Upgrade } /** + * Make sure that DERBY-1482 changes do not break backward compatibility + */ + public void testTriggers() throws SQLException + { + Statement s = createStatement(); + ResultSet rs; + boolean modeDb2SqlOptional = oldAtLeast(10, 3); + + switch ( getPhase() ) + { + case PH_CREATE: // create with old version + s.execute("CREATE TABLE DERBY1482_table1(c11 int, c12 int)"); + s.execute("INSERT INTO DERBY1482_table1 VALUES (1,10)"); + s.execute("CREATE TABLE DERBY1482_table2(c21 int, c22 int)"); + s.execute("CREATE TABLE DERBY1482_table3(c31 int, c32 int)"); + s.execute("CREATE TABLE DERBY1482_table4(c41 int, c42 int)"); + s.execute("CREATE TABLE DERBY1482_table5(c51 int, c52 int)"); + //Create the first trigger in the older release where the + //database has been created. Every update of DERBY1482_table1.c12 + //will cause an insert into DERBY1482_table2 through this trigger tr1. + s.execute("CREATE TRIGGER tr1 AFTER UPDATE OF c12 " + + "ON DERBY1482_table1 REFERENCING OLD AS oldt " + + "FOR EACH ROW " + + (modeDb2SqlOptional?"":"MODE DB2SQL ") + + "INSERT INTO DERBY1482_table2 VALUES(-1, oldt.c12)"); + + //Now do an update which will fire trigger tr1 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that trigger tr1 has inserted one row in DERBY1482_table2 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"1"}}); + break; + + case PH_SOFT_UPGRADE: // boot with new version and soft-upgrade + //Now do an update while in the soft upgrade. This should + //fire trigger tr1 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that now we have 2 rows in DERBY1482_table2 because trigger tr1 + //has fired twice so far. Once in PH_CREATE phase and once + //in PH_SOFT_UPGRADE phase + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"2"}}); + //Create trigger tr2 in soft upgrade mode. DERBY-1482 changes + //will not put anything about trigger action columns in + //SYSTRIGGERS to maintain backward compatibility. Only 10.7 + //and up recognize additional information about trigger action + //columns in SYSTRIGGERS. + s.execute("CREATE TRIGGER tr2 AFTER UPDATE OF c12 ON DERBY1482_table1 " + + "REFERENCING OLD AS oldt FOR EACH ROW " + + (modeDb2SqlOptional?"":"MODE DB2SQL ") + + "INSERT INTO DERBY1482_table3 VALUES(-1, oldt.c12)"); + //Now do an update which will fire triggers tr1 and tr2 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that trigger tr1 has inserted one more row in DERBY1482_table2 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"3"}}); + //Verify that trigger tr2 has inserted one row in DERBY1482_table3 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table3"); + JDBC.assertFullResultSet(rs, new String[][]{{"1"}}); + break; + + case PH_POST_SOFT_UPGRADE: // soft-downgrade: boot with old version after soft-upgrade + //Now do an update when we are back with the older release + //after the soft upgrade. This should fire trigger tr1 and tr2 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that now we have 4 rows in DERBY1482_table2 and 2 rows in DERBY1482_table3 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"4"}}); + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table3"); + JDBC.assertFullResultSet(rs, new String[][]{{"2"}}); + //Create trigger tr3 with the older release. Triggers created in + //soft-upgrade mode and with older release should work fine. + s.execute("CREATE TRIGGER tr3 AFTER UPDATE OF c12 ON DERBY1482_table1 " + + "REFERENCING OLD AS oldt FOR EACH ROW " + + (modeDb2SqlOptional?"":"MODE DB2SQL ") + + "INSERT INTO DERBY1482_table4 VALUES(-1, oldt.c12)"); + //Now do an update which will fire triggers tr1, tr2 and tr3 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that trigger tr1 has inserted one more row in DERBY1482_table2 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"5"}}); + //Verify that trigger tr2 has inserted one more row in DERBY1482_table3 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table3"); + JDBC.assertFullResultSet(rs, new String[][]{{"3"}}); + //Verify that trigger tr3 has inserted one row in DERBY1482_table4 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table4"); + JDBC.assertFullResultSet(rs, new String[][]{{"1"}}); + break; + + case PH_HARD_UPGRADE: // boot with new version and hard-upgrade + //Do an update after we have hard upgraded to 10.7 and make sure + //that all the triggers (created with older release and created + //in soft-upgrade mode) work fine. + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that now we have 6 rows in DERBY1482_table2, 4 rows in DERBY1482_table3, 2 rows in DERBY1482_table4 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"6"}}); + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table3"); + JDBC.assertFullResultSet(rs, new String[][]{{"4"}}); + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table4"); + JDBC.assertFullResultSet(rs, new String[][]{{"2"}}); + //Create trigger DERBY1482_table4 in the hard-upgraded db. + s.execute("CREATE TRIGGER tr4 AFTER UPDATE OF c12 ON DERBY1482_table1 " + + "REFERENCING OLD AS oldt FOR EACH ROW " + + (modeDb2SqlOptional?"":"MODE DB2SQL ") + + "INSERT INTO DERBY1482_table5 VALUES(-1, oldt.c12)"); + //All 4 triggers tr1, tr2, tr3 and tr4 should fire + //Now do an update which will fire all 4 triggers tr1,tr2,tr3,tr4 + s.executeUpdate("UPDATE DERBY1482_table1 SET c12=-1 WHERE c11=1"); + //Verify that trigger tr1 has inserted one more row in DERBY1482_table2 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table2"); + JDBC.assertFullResultSet(rs, new String[][]{{"7"}}); + //Verify that trigger tr2 has inserted one more row in DERBY1482_table3 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table3"); + JDBC.assertFullResultSet(rs, new String[][]{{"5"}}); + //Verify that trigger tr3 has inserted one more row in DERBY1482_table4 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table4"); + JDBC.assertFullResultSet(rs, new String[][]{{"3"}}); + //Verify that trigger tr4 has inserted one row in DERBY1482_table5 + rs = s.executeQuery("SELECT COUNT(*) FROM DERBY1482_table5"); + JDBC.assertFullResultSet(rs, new String[][]{{"1"}}); + break; + } + s.close(); + } + + /** * Make sure that that database is at level 10.7 in order to enjoy * routines with specified EXTERNAL SECURITY INVOKER or DEFINER. */