001: /*
002: * DeleteScriptGeneratorTest.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db;
013:
014: import java.sql.ResultSet;
015: import java.sql.Statement;
016: import java.util.ArrayList;
017: import java.util.List;
018: import java.util.regex.Matcher;
019: import java.util.regex.Pattern;
020: import junit.framework.TestCase;
021: import workbench.TestUtil;
022: import workbench.sql.ScriptParser;
023: import workbench.storage.ColumnData;
024: import workbench.util.SqlUtil;
025:
026: /**
027: *
028: * @author support@sql-workbench.net
029: */
030: public class DeleteScriptGeneratorTest extends TestCase {
031: private WbConnection dbConnection;
032:
033: public DeleteScriptGeneratorTest(String testName) {
034: super (testName);
035: }
036:
037: public void testGenerateScript() {
038: try {
039: createSimpleTables();
040: DeleteScriptGenerator generator = new DeleteScriptGenerator(
041: dbConnection);
042: TableIdentifier table = new TableIdentifier("PERSON");
043: generator.setTable(table);
044: List<ColumnData> pk = new ArrayList<ColumnData>();
045: ColumnData id = new ColumnData(new Integer(1),
046: new ColumnIdentifier("ID"));
047: pk.add(id);
048: CharSequence sql = generator.getScriptForValues(pk);
049: // System.out.println("***");
050: // System.out.println(sql);
051: ScriptParser parser = new ScriptParser(sql.toString());
052: assertEquals(2, parser.getSize());
053: String addressDelete = parser.getCommand(0);
054: String addressTable = SqlUtil.getDeleteTable(addressDelete);
055: Pattern p = Pattern.compile("\\s*person_id\\s*=\\s*1",
056: Pattern.CASE_INSENSITIVE);
057: Matcher m = p.matcher(addressDelete);
058: assertEquals("ADDRESS", addressTable);
059: assertTrue(m.find());
060:
061: String personTable = SqlUtil.getDeleteTable(parser
062: .getCommand(1));
063: assertEquals("PERSON", personTable);
064: } catch (Exception e) {
065: e.printStackTrace();
066: fail(e.getMessage());
067: }
068: }
069:
070: public void testGenerateStatements() {
071: try {
072: createMultiColumnPkTables();
073: DeleteScriptGenerator generator = new DeleteScriptGenerator(
074: dbConnection);
075: TableIdentifier table = new TableIdentifier("BASE");
076: generator.setTable(table);
077: List<ColumnData> pk = new ArrayList<ColumnData>();
078: pk.add(new ColumnData(new Integer(1), new ColumnIdentifier(
079: "BASE_ID1")));
080: pk.add(new ColumnData(new Integer(1), new ColumnIdentifier(
081: "BASE_ID2")));
082:
083: List<String> statements = generator.getStatementsForValues(
084: pk, true);
085: // for (String s : statements)
086: // {
087: // System.out.println(s + ";\n");
088: // }
089:
090: assertEquals(4, statements.size());
091:
092: Statement stmt = dbConnection.createStatement();
093: for (String sql : statements) {
094: stmt.executeUpdate(sql);
095: }
096: dbConnection.commit();
097:
098: String[] tables = new String[] { "BASE", "CHILD1",
099: "CHILD2", "CHILD22" };
100:
101: for (String st : tables) {
102: ResultSet rs = stmt
103: .executeQuery("select count(*) from " + st);
104: int count = -1;
105: if (rs.next()) {
106: count = rs.getInt(1);
107: }
108: assertEquals("Wrong count in table: " + st, 1, count);
109: }
110:
111: stmt.close();
112:
113: String sql = statements.get(3);
114: String t = SqlUtil.getDeleteTable(sql);
115: assertEquals("BASE", t);
116:
117: sql = statements.get(2);
118: t = SqlUtil.getDeleteTable(sql);
119: assertEquals("CHILD1", t);
120:
121: // Test when root table should not be included
122: statements = generator.getStatementsForValues(pk, false);
123: assertEquals(3, statements.size());
124: sql = statements.get(2);
125: t = SqlUtil.getDeleteTable(sql);
126: assertEquals("CHILD1", t);
127: } catch (Exception e) {
128: e.printStackTrace();
129: fail(e.getMessage());
130: }
131: }
132:
133: private void createMultiColumnPkTables() throws Exception {
134: String sql = "CREATE TABLE base \n"
135: + "( \n"
136: + " base_id1 integer NOT NULL, \n"
137: + " base_id2 integer NOT NULL \n"
138: + "); \n"
139: + "ALTER TABLE base \n"
140: + " ADD CONSTRAINT base_pkey PRIMARY KEY (base_id1, base_id2); \n"
141: +
142:
143: "CREATE TABLE child1 \n"
144: + "( \n"
145: + " child1_id1 integer NOT NULL, \n"
146: + " child1_id2 integer NOT NULL, \n"
147: + " c1base_id1 integer NOT NULL, \n"
148: + " c1base_id2 integer NOT NULL \n"
149: + "); \n"
150: +
151:
152: "ALTER TABLE child1 \n"
153: + " ADD CONSTRAINT child1_pkey PRIMARY KEY (child1_id1, child1_id2); \n"
154: + " \n"
155: + "ALTER TABLE child1 \n"
156: + " ADD CONSTRAINT fk_child1 FOREIGN KEY (c1base_id1, c1base_id2) \n"
157: + " REFERENCES base (base_id1, base_id2); \n"
158: +
159:
160: "CREATE TABLE child2 \n"
161: + "( \n"
162: + " child2_id1 integer NOT NULL, \n"
163: + " child2_id2 integer NOT NULL, \n"
164: + " c2c1_id1 integer NOT NULL, \n"
165: + " c2c1_id2 integer NOT NULL \n"
166: + "); \n"
167: +
168:
169: "ALTER TABLE child2 \n"
170: + " ADD CONSTRAINT child2_pkey PRIMARY KEY (child2_id1, child2_id2); \n"
171: + " \n"
172: + "ALTER TABLE child2 \n"
173: + " ADD CONSTRAINT fk_child2 FOREIGN KEY (c2c1_id1, c2c1_id2) \n"
174: + " REFERENCES child1 (child1_id1, child1_id2); \n"
175: +
176:
177: "CREATE TABLE child22 \n"
178: + "( \n"
179: + " child22_id1 integer NOT NULL, \n"
180: + " child22_id2 integer NOT NULL, \n"
181: + " c22c1_id1 integer NOT NULL, \n"
182: + " c22c1_id2 integer NOT NULL \n"
183: + "); \n"
184: +
185:
186: "ALTER TABLE child22 \n"
187: + " ADD CONSTRAINT child22_pkey PRIMARY KEY (child22_id1, child22_id2); \n"
188: + " \n"
189: + "ALTER TABLE child22 \n"
190: + " ADD CONSTRAINT fk_child22 FOREIGN KEY (c22c1_id1, c22c1_id2) \n"
191: + " REFERENCES child1 (child1_id1, child1_id2); \n";
192:
193: TestUtil util = new TestUtil("DependencyDeleter");
194: this .dbConnection = util.getConnection();
195: TestUtil.executeScript(dbConnection, sql);
196: Statement stmt = this .dbConnection.createStatement();
197: stmt
198: .executeUpdate("insert into base (base_id1, base_id2) values (1,1)");
199: stmt
200: .executeUpdate("insert into base (base_id1, base_id2) values (2,2)");
201:
202: stmt
203: .executeUpdate("insert into child1 (child1_id1, child1_id2, c1base_id1, c1base_id2) values (11,11,1,1)");
204: stmt
205: .executeUpdate("insert into child1 (child1_id1, child1_id2, c1base_id1, c1base_id2) values (12,12,2,2)");
206:
207: stmt
208: .executeUpdate("insert into child2 (child2_id1, child2_id2, c2c1_id1, c2c1_id2) values (101,101,11,11)");
209: stmt
210: .executeUpdate("insert into child2 (child2_id1, child2_id2, c2c1_id1, c2c1_id2) values (102,102,12,12)");
211:
212: stmt
213: .executeUpdate("insert into child22 (child22_id1, child22_id2, c22c1_id1, c22c1_id2) values (201,201,11,11)");
214: stmt
215: .executeUpdate("insert into child22 (child22_id1, child22_id2, c22c1_id1, c22c1_id2) values (202,202,12,12)");
216: dbConnection.commit();
217: stmt.close();
218: }
219:
220: private void createSimpleTables() throws Exception {
221: String sql = "CREATE TABLE address \n" + "( \n"
222: + " id integer NOT NULL, \n"
223: + " address_data varchar(100) not null, \n"
224: + " person_id integer \n" + "); \n" +
225:
226: "ALTER TABLE address \n"
227: + " ADD CONSTRAINT address_pkey PRIMARY KEY (id); \n"
228: +
229:
230: "CREATE TABLE person \n" + "( \n"
231: + " id integer NOT NULL, \n"
232: + " firstname varchar(50), \n"
233: + " lastname varchar(50) \n" + "); \n" +
234:
235: "ALTER TABLE person \n"
236: + " ADD CONSTRAINT person_pkey PRIMARY KEY (id); \n"
237: + " \n" +
238:
239: "ALTER TABLE address \n"
240: + " ADD CONSTRAINT fk_pers FOREIGN KEY (person_id) \n"
241: + " REFERENCES person (id); \n";
242:
243: TestUtil util = new TestUtil("DeleteScriptGenerator");
244: this.dbConnection = util.getConnection();
245: TestUtil.executeScript(dbConnection, sql);
246: }
247:
248: }
|