Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r994946 -
 /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java
Date: Wed, 08 Sep 2010 09:46:24 -0000
To: derby-commits@db.apache.org
From: kahatlen@apache.org
Message-Id: <20100908094624.B4A05238890A@eris.apache.org>

Author: kahatlen
Date: Wed Sep  8 09:46:24 2010
New Revision: 994946

URL: http://svn.apache.org/viewvc?rev=994946&view=rev
Log:
DERBY-268: Test truncating tables with delete triggers and foreign keys

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java?rev=994946&r1=994945&r2=994946&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java Wed Sep  8 09:46:24 2010
@@ -21,6 +21,7 @@
 
 package org.apache.derbyTesting.functionTests.tests.lang;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
@@ -85,4 +86,72 @@ public class TruncateTableTest extends B
         JDBC.assertFullResultSet(rs, expRS);
 
     }
+
+    /**
+     * Test that TRUNCATE TABLE cannot be performed on a table with a
+     * delete trigger.
+     */
+    public void testTruncateWithDeleteTrigger() throws Exception {
+        Statement s = createStatement();
+
+        // Create two tables, t1 and t2, where deletes from t1 cause inserts
+        // into t2.
+        s.execute("create table deltriggertest_t1(x int)");
+        s.execute("create table deltriggertest_t2(y int)");
+        s.execute("create trigger deltriggertest_tr after delete on "
+                + "deltriggertest_t1 referencing old as old for each row "
+                + "insert into deltriggertest_t2 values old.x");
+
+        // Prepare a statement that checks the number of rows in the
+        // destination table (t2).
+        PreparedStatement checkDest = prepareStatement(
+                "select count(*) from deltriggertest_t2");
+
+        // Insert rows into t1, delete them, and verify that t2 has grown.
+        s.execute("insert into deltriggertest_t1 values 1,2,3");
+        JDBC.assertSingleValueResultSet(checkDest.executeQuery(), "0");
+        assertUpdateCount(s, 3, "delete from deltriggertest_t1");
+        JDBC.assertSingleValueResultSet(checkDest.executeQuery(), "3");
+
+        // Now do the same with TRUNCATE instead of DELETE. Expect it to fail
+        // because there is a delete trigger on the table.
+        s.execute("insert into deltriggertest_t1 values 4,5");
+        assertStatementError("XCL49", s, "truncate table deltriggertest_t1");
+        JDBC.assertSingleValueResultSet(checkDest.executeQuery(), "3");
+    }
+
+    /**
+     * Test that TRUNCATE TABLE isn't allowed on a table referenced by a
+     * foreign key constraint on another table.
+     */
+    public void testTruncateWithForeignKey() throws SQLException {
+        Statement s = createStatement();
+
+        // Create two tables with a foreign key relationship.
+        s.execute("create table foreignkey_t1(x int primary key)");
+        s.execute("create table foreignkey_t2(y int references foreignkey_t1)");
+        s.execute("insert into foreignkey_t1 values 1,2");
+        s.execute("insert into foreignkey_t2 values 2");
+
+        // Truncating the referenced table isn't allowed as that would
+        // break referential integrity.
+        assertStatementError("XCL48", s, "truncate table foreignkey_t1");
+
+        // Truncating the referencing table is OK.
+        s.execute("truncate table foreignkey_t2");
+        assertTableRowCount("FOREIGNKEY_T2", 0);
+    }
+
+    /**
+     * Test that TRUNCATE TABLE is allowed on a referenced table if it's only
+     * referenced by itself.
+     */
+    public void testSelfReferencing() throws SQLException {
+        Statement s = createStatement();
+        s.execute("create table self_referencing_t1(x int primary key, "
+                + "y int references self_referencing_t1)");
+        s.execute("insert into self_referencing_t1 values (1, null), (2, 1)");
+        s.execute("truncate table self_referencing_t1");
+        assertTableRowCount("SELF_REFERENCING_T1", 0);
+    }
 }