001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: /*
034: * CascadeDeleteBug.java
035: *
036: * Created on June 24, 2002, 8:48 AM
037: */
038: import java.io.File;
039: import java.sql.Connection;
040: import java.sql.DriverManager;
041: import java.sql.ResultSet;
042: import java.sql.SQLException;
043: import java.sql.Statement;
044:
045: import junit.framework.TestCase;
046:
047: /**
048: * Test case to demonstrate catastrophic bug in cascade delete code.
049: *
050: * @version 1.0
051: * @author David Kopp
052: */
053: public class TestCascade extends TestCase {
054:
055: Connection con;
056:
057: public TestCascade(String name) {
058: super (name);
059: }
060:
061: protected void setUp() {
062:
063: try {
064: Class.forName("org.hsqldb.jdbcDriver");
065: createDatabase();
066:
067: con = DriverManager.getConnection("jdbc:hsqldb:testdb",
068: "sa", "");
069: } catch (Exception e) {
070: e.printStackTrace();
071: System.out.println(this + ".setUp() error: "
072: + e.getMessage());
073: }
074: }
075:
076: protected void tearDown() {
077:
078: try {
079: con.close();
080: } catch (SQLException e) {
081: }
082: }
083:
084: public void testDelete() {
085:
086: try {
087: insertData(con);
088:
089: Statement stmt = con.createStatement();
090: ResultSet rs = stmt
091: .executeQuery("SELECT COUNT(EIACODXA) FROM CA");
092:
093: rs.next();
094:
095: int origCount = rs.getInt(1);
096:
097: rs.close();
098: deleteXBRecord(con);
099:
100: rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
101:
102: rs.next();
103:
104: int newCount = rs.getInt(1);
105:
106: rs.close();
107: stmt.close();
108: assertEquals(9, newCount);
109: } catch (SQLException e) {
110: this .assertTrue("SQLException thrown", false);
111: }
112: }
113:
114: private static void createDatabase() throws SQLException {
115:
116: new File("testdb.backup").delete();
117: new File("testdb.data").delete();
118: new File("testdb.properties").delete();
119: new File("testdb.script").delete();
120:
121: Connection con = DriverManager.getConnection(
122: "jdbc:hsqldb:testdb", "sa", "");
123: String[] saDDL = {
124: "CREATE CACHED TABLE XB (EIACODXA VARCHAR(10) NOT NULL, LSACONXB VARCHAR(18) NOT NULL, ALTLCNXB VARCHAR(2) NOT NULL, LCNTYPXB VARCHAR(1) NOT NULL, LCNINDXB VARCHAR(1), LCNAMEXB VARCHAR(19), UPDT_BY VARCHAR(32), LST_UPDT TIMESTAMP, CONSTRAINT XPKXB PRIMARY KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB));",
125:
126: // "CREATE INDEX XIF2XB ON XB (EIACODXA);",
127: "CREATE CACHED TABLE CA ( EIACODXA VARCHAR(10) NOT NULL, LSACONXB VARCHAR(18) NOT NULL, ALTLCNXB VARCHAR(2) NOT NULL, LCNTYPXB VARCHAR(1) NOT NULL, TASKCDCA VARCHAR(7) NOT NULL, TSKFRQCA NUMERIC(7,4), UPDT_BY VARCHAR(32), LST_UPDT TIMESTAMP, CONSTRAINT XPKCA PRIMARY KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB, TASKCDCA), CONSTRAINT R_XB_CA FOREIGN KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB) REFERENCES XB ON DELETE CASCADE);",
128:
129: // "CREATE INDEX XIF26CA ON CA ( EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB);"
130: };
131: Statement stmt = con.createStatement();
132:
133: for (int index = 0; index < saDDL.length; index++) {
134: stmt.executeUpdate(saDDL[index]);
135: }
136:
137: stmt.execute("SHUTDOWN");
138: con.close();
139: } // createDatabase
140:
141: /**
142: * This method demonstrates the bug in cascading deletes. Before this method,
143: * the CA table has 12 records. After, it should have 9, but instead it has
144: * 0.
145: */
146: private static void deleteXBRecord(Connection con)
147: throws SQLException {
148:
149: Statement stmt = con.createStatement();
150:
151: stmt
152: .executeUpdate("DELETE FROM XB WHERE LSACONXB = 'LEAA' AND EIACODXA = 'T850' AND LCNTYPXB = 'P' AND ALTLCNXB = '00'");
153: stmt.close();
154: } // deleteXBRecord
155:
156: private static void insertData(Connection con) throws SQLException {
157:
158: String[] saData = {
159: "INSERT INTO XB VALUES('T850','LEAA','00','P',NULL,'LCN NAME','sa',NOW)",
160: "INSERT INTO XB VALUES('T850','LEAA01','00','P',NULL,'LCN NAME','sa',NOW)",
161: "INSERT INTO XB VALUES('T850','LEAA02','00','P',NULL,'LCN NAME','sa',NOW)",
162: "INSERT INTO XB VALUES('T850','LEAA03','00','P',NULL,'LCN NAME','sa',NOW)",
163: "INSERT INTO CA VALUES('T850','LEAA','00','P','ABCDEFG',3.14,'sa',NOW)",
164: "INSERT INTO CA VALUES('T850','LEAA','00','P','QRSTUJV',3.14,'sa',NOW)",
165: "INSERT INTO CA VALUES('T850','LEAA','00','P','ZZZZZZZ',3.14,'sa',NOW)",
166: "INSERT INTO CA VALUES('T850','LEAA01','00','P','ABCDEFG',3.14,'sa',NOW)",
167: "INSERT INTO CA VALUES('T850','LEAA01','00','P','QRSTUJV',3.14,'sa',NOW)",
168: "INSERT INTO CA VALUES('T850','LEAA01','00','P','ZZZZZZZ',3.14,'sa',NOW)",
169: "INSERT INTO CA VALUES('T850','LEAA02','00','P','ABCDEFG',3.14,'sa',NOW)",
170: "INSERT INTO CA VALUES('T850','LEAA02','00','P','QRSTUJV',3.14,'sa',NOW)",
171: "INSERT INTO CA VALUES('T850','LEAA02','00','P','ZZZZZZZ',3.14,'sa',NOW)",
172: "INSERT INTO CA VALUES('T850','LEAA03','00','P','ABCDEFG',3.14,'sa',NOW)",
173: "INSERT INTO CA VALUES('T850','LEAA03','00','P','QRSTUJV',3.14,'sa',NOW)",
174: "INSERT INTO CA VALUES('T850','LEAA03','00','P','ZZZZZZZ',3.14,'sa',NOW)" };
175: Statement stmt = con.createStatement();
176:
177: for (int index = 0; index < saData.length; index++) {
178: stmt.executeUpdate(saData[index]);
179: }
180: } // insertData
181: }
|