Source code: org/hsqldb/test/TestCascade.java
1 /* Copyrights and Licenses
2 *
3 * This product includes Hypersonic SQL.
4 * Originally developed by Thomas Mueller and the Hypersonic SQL Group.
5 *
6 * Copyright (c) 1995-2000 by the Hypersonic SQL Group. All rights reserved.
7 * Redistribution and use in source and binary forms, with or without modification, are permitted
8 * provided that the following conditions are met:
9 * - Redistributions of source code must retain the above copyright notice, this list of conditions
10 * and the following disclaimer.
11 * - Redistributions in binary form must reproduce the above copyright notice, this list of
12 * conditions and the following disclaimer in the documentation and/or other materials
13 * provided with the distribution.
14 * - All advertising materials mentioning features or use of this software must display the
15 * following acknowledgment: "This product includes Hypersonic SQL."
16 * - Products derived from this software may not be called "Hypersonic SQL" nor may
17 * "Hypersonic SQL" appear in their names without prior written permission of the
18 * Hypersonic SQL Group.
19 * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
20 * product includes Hypersonic SQL."
21 * This software is provided "as is" and any expressed or implied warranties, including, but
22 * not limited to, the implied warranties of merchantability and fitness for a particular purpose are
23 * disclaimed. In no event shall the Hypersonic SQL Group or its contributors be liable for any
24 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but
25 * not limited to, procurement of substitute goods or services; loss of use, data, or profits;
26 * or business interruption). However caused any on any theory of liability, whether in contract,
27 * strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this
28 * software, even if advised of the possibility of such damage.
29 * This software consists of voluntary contributions made by many individuals on behalf of the
30 * Hypersonic SQL Group.
31 *
32 *
33 * For work added by the HSQL Development Group:
34 *
35 * Copyright (c) 2001-2002, The HSQL Development Group
36 * All rights reserved.
37 *
38 * Redistribution and use in source and binary forms, with or without
39 * modification, are permitted provided that the following conditions are met:
40 *
41 * Redistributions of source code must retain the above copyright notice, this
42 * list of conditions and the following disclaimer, including earlier
43 * license statements (above) and comply with all above license conditions.
44 *
45 * Redistributions in binary form must reproduce the above copyright notice,
46 * this list of conditions and the following disclaimer in the documentation
47 * and/or other materials provided with the distribution, including earlier
48 * license statements (above) and comply with all above license conditions.
49 *
50 * Neither the name of the HSQL Development Group nor the names of its
51 * contributors may be used to endorse or promote products derived from this
52 * software without specific prior written permission.
53 *
54 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
55 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
56 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
57 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
58 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
59 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
60 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
61 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
62 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
63 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
64 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
65 */
66
67
68 package org.hsqldb.test;
69
70 /*
71 * CascadeDeleteBug.java
72 *
73 * Created on June 24, 2002, 8:48 AM
74 */
75 import java.io.*;
76 import java.sql.*;
77
78 /**
79 * Test case to demonstrate catastrophic bug in cascade delete code.
80 *
81 * @version 1.0
82 * @author David Kopp
83 */
84 public class TestCascade {
85
86 /**
87 * @param args the command line arguments
88 */
89 public static void main(String[] args) {
90
91 try {
92 Class.forName("org.hsqldb.jdbcDriver");
93 createDatabase();
94
95 Connection con = DriverManager.getConnection("jdbc:hsqldb:testdb",
96 "sa", "");
97
98 insertData(con);
99 System.out.println("should print 12");
100 printNumberOfCARecords(con);
101 deleteXBRecord(con);
102 System.out.println("should print 9");
103 printNumberOfCARecords(con);
104 con.close();
105 } catch (Exception e) {
106 e.printStackTrace();
107 }
108 } // main
109
110 private static void createDatabase() throws SQLException {
111
112 new File("testdb.backup").delete();
113 new File("testdb.data").delete();
114 new File("testdb.properties").delete();
115 new File("testdb.script").delete();
116
117 Connection con = DriverManager.getConnection("jdbc:hsqldb:testdb",
118 "sa", "");
119 String[] saDDL = {
120 "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));",
121 "CREATE INDEX XIF2XB ON XB (EIACODXA);",
122 "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);",
123 "CREATE INDEX XIF26CA ON CA ( EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB);"
124 };
125 Statement stmt = con.createStatement();
126
127 for (int index = 0; index < saDDL.length; index++) {
128 stmt.executeUpdate(saDDL[index]);
129 }
130
131 con.close();
132 } // createDatabase
133
134 /**
135 * This method demonstrates the bug in cascading deletes. Before this method,
136 * the CA table has 12 records. After, it should have 9, but instead it has
137 * 0.
138 */
139 private static void deleteXBRecord(Connection con) throws SQLException {
140
141 Statement stmt = con.createStatement();
142
143 stmt.executeUpdate(
144 "DELETE FROM XB WHERE LSACONXB = 'LEAA' AND EIACODXA = 'T850' AND LCNTYPXB = 'P' AND ALTLCNXB = '00'");
145 stmt.close();
146 } // deleteXBRecord
147
148 private static void insertData(Connection con) throws SQLException {
149
150 String[] saData = {
151 "INSERT INTO XB VALUES('T850','LEAA','00','P',NULL,'LCN NAME','sa','NOW')",
152 "INSERT INTO XB VALUES('T850','LEAA01','00','P',NULL,'LCN NAME','sa','NOW')",
153 "INSERT INTO XB VALUES('T850','LEAA02','00','P',NULL,'LCN NAME','sa','NOW')",
154 "INSERT INTO XB VALUES('T850','LEAA03','00','P',NULL,'LCN NAME','sa','NOW')",
155 "INSERT INTO CA VALUES('T850','LEAA','00','P','ABCDEFG',3.14,'sa','NOW')",
156 "INSERT INTO CA VALUES('T850','LEAA','00','P','QRSTUJV',3.14,'sa','NOW')",
157 "INSERT INTO CA VALUES('T850','LEAA','00','P','ZZZZZZZ',3.14,'sa','NOW')",
158 "INSERT INTO CA VALUES('T850','LEAA01','00','P','ABCDEFG',3.14,'sa','NOW')",
159 "INSERT INTO CA VALUES('T850','LEAA01','00','P','QRSTUJV',3.14,'sa','NOW')",
160 "INSERT INTO CA VALUES('T850','LEAA01','00','P','ZZZZZZZ',3.14,'sa','NOW')",
161 "INSERT INTO CA VALUES('T850','LEAA02','00','P','ABCDEFG',3.14,'sa','NOW')",
162 "INSERT INTO CA VALUES('T850','LEAA02','00','P','QRSTUJV',3.14,'sa','NOW')",
163 "INSERT INTO CA VALUES('T850','LEAA02','00','P','ZZZZZZZ',3.14,'sa','NOW')",
164 "INSERT INTO CA VALUES('T850','LEAA03','00','P','ABCDEFG',3.14,'sa','NOW')",
165 "INSERT INTO CA VALUES('T850','LEAA03','00','P','QRSTUJV',3.14,'sa','NOW')",
166 "INSERT INTO CA VALUES('T850','LEAA03','00','P','ZZZZZZZ',3.14,'sa','NOW')"
167 };
168 Statement stmt = con.createStatement();
169
170 for (int index = 0; index < saData.length; index++) {
171 stmt.executeUpdate(saData[index]);
172 }
173 } // insertData
174
175 private static void printNumberOfCARecords(Connection con)
176 throws SQLException {
177
178 Statement stmt = con.createStatement();
179 ResultSet rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
180
181 rs.next();
182 System.out.println(rs.getInt(1));
183 System.out.flush();
184 stmt.close();
185 } // printNumberOfCARecords
186 } // CascadeDeleteBug
187