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 ma...@apache.org on 2010/06/22 04:41:49 UTC
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...
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.
*/