001: /*
002: * TableDependencyTest.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.Statement;
015: import java.util.List;
016: import junit.framework.TestCase;
017: import workbench.TestUtil;
018:
019: /**
020: *
021: * @author support@sql-workbench.net
022: */
023: public class TableDependencyTest extends TestCase {
024: public TableDependencyTest(String testName) {
025: super (testName);
026: }
027:
028: protected WbConnection createRegularDB() throws Exception {
029: super .setUp();
030: TestUtil util = new TestUtil("dependencyTest");
031: WbConnection dbConn = util.getConnection();
032: Statement stmt = dbConn.createStatement();
033: String baseSql = "CREATE TABLE base \n" + "( \n"
034: + " id1 INTEGER NOT NULL, \n"
035: + " id2 INTEGER NOT NULL, \n"
036: + " primary key (id1, id2) \n" + ")";
037: stmt.execute(baseSql);
038:
039: String child1Sql = "CREATE TABLE child1 \n"
040: + "( \n"
041: + " id INTEGER NOT NULL PRIMARY KEY, \n"
042: + " base_id1 INTEGER NOT NULL, \n"
043: + " base_id2 INTEGER NOT NULL, \n"
044: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
045: + ")";
046: stmt.executeUpdate(child1Sql);
047:
048: String child2Sql = "CREATE TABLE child2 \n"
049: + "( \n"
050: + " id INTEGER NOT NULL PRIMARY KEY, \n"
051: + " base_id1 INTEGER NOT NULL, \n"
052: + " base_id2 INTEGER NOT NULL, \n"
053: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
054: + ")";
055: stmt.executeUpdate(child2Sql);
056:
057: String child3Sql = "CREATE TABLE child2_detail \n" + "( \n"
058: + " id INTEGER NOT NULL PRIMARY KEY, \n"
059: + " child_id INTEGER NOT NULL, \n"
060: + " FOREIGN KEY (child_id) REFERENCES child2 (id) \n"
061: + ")";
062: stmt.executeUpdate(child3Sql);
063:
064: String sql = "CREATE TABLE child1_detail \n"
065: + "( \n"
066: + " id INTEGER NOT NULL PRIMARY KEY, \n"
067: + " child1_id INTEGER NOT NULL, \n"
068: + " FOREIGN KEY (child1_id) REFERENCES child1 (id) \n"
069: + ")";
070: stmt.executeUpdate(sql);
071:
072: sql = "CREATE TABLE child1_detail2 \n"
073: + "( \n"
074: + " id INTEGER NOT NULL PRIMARY KEY, \n"
075: + " detail_id INTEGER NOT NULL, \n"
076: + " FOREIGN KEY (detail_id) REFERENCES child1_detail (id) \n"
077: + ")";
078: stmt.executeUpdate(sql);
079: return dbConn;
080: }
081:
082: @Override
083: protected void tearDown() throws Exception {
084: ConnectionMgr.getInstance().disconnectAll();
085: super .tearDown();
086: }
087:
088: public void testDependency() throws Exception {
089: try {
090: TableIdentifier base = new TableIdentifier("BASE");
091: TableDependency dep = new TableDependency(
092: createRegularDB(), base);
093: dep.readTreeForChildren();
094: DependencyNode root = dep.getRootNode();
095: assertNotNull("No root returned", root);
096: List<DependencyNode> leafs = dep.getLeafs();
097: for (DependencyNode node : leafs) {
098: int level = node.getLevel();
099: String tbl = node.getTable().getTableName();
100: if (tbl.equalsIgnoreCase("base")) {
101: assertEquals("Wrong level for base table", 0, level);
102: }
103: if (tbl.equalsIgnoreCase("child1")
104: || tbl.equalsIgnoreCase("child2")) {
105: assertEquals("Wrong level for childX tables", 1,
106: level);
107: }
108: if (tbl.equalsIgnoreCase("child1_detail")
109: || tbl.equalsIgnoreCase("child2_detail")) {
110: assertEquals("Wrong level for detail tables", 2,
111: level);
112: }
113: }
114: } catch (Exception e) {
115: e.printStackTrace();
116: fail(e.getMessage());
117: } finally {
118: ConnectionMgr.getInstance().disconnectAll();
119: }
120: }
121:
122: protected WbConnection createTwoLevelCycleDB() throws Exception {
123: super .setUp();
124: TestUtil util = new TestUtil("dependencyCycleTest");
125: WbConnection dbConn = util.getConnection();
126: Statement stmt = dbConn.createStatement();
127:
128: String sql = null;
129:
130: sql = "CREATE TABLE base \n" + "( \n"
131: + " id INTEGER NOT NULL PRIMARY KEY " + ")";
132: stmt.executeUpdate(sql);
133:
134: sql = "CREATE TABLE tbl1 \n" + "( \n"
135: + " id INTEGER NOT NULL PRIMARY KEY, "
136: + " base_id INTEGER, "
137: + " tbl2_id integer,"
138: + " foreign key (base_id) references base (id) "
139: + ")";
140: stmt.executeUpdate(sql);
141:
142: sql = "CREATE TABLE tbl2 \n" + "( \n"
143: + " id INTEGER NOT NULL PRIMARY KEY,"
144: + " tbl1_id integer " + ")";
145: stmt.executeUpdate(sql);
146:
147: sql = "alter table tbl1 add foreign key (tbl2_id) references tbl2 (id)";
148: stmt.executeUpdate(sql);
149:
150: sql = "alter table tbl2 add foreign key (tbl1_id) references tbl1 (id)";
151: stmt.executeUpdate(sql);
152:
153: return dbConn;
154: }
155:
156: public void testCycle() {
157: try {
158: WbConnection con = createTwoLevelCycleDB();
159: TableIdentifier tbl = new TableIdentifier("base");
160: TableDependency dep = new TableDependency(con, tbl);
161: dep.readTreeForChildren();
162: assertEquals(false, dep.wasAborted());
163: assertNotNull("No root returned", dep.getRootNode());
164: // dep.getRootNode().printAll();
165: } catch (Exception e) {
166: e.printStackTrace();
167: fail(e.getMessage());
168: } finally {
169: ConnectionMgr.getInstance().disconnectAll();
170: }
171: }
172:
173: protected WbConnection createSingleLevelCycleDB() throws Exception {
174: super .setUp();
175: TestUtil util = new TestUtil("dependencyCycleTest");
176: WbConnection dbConn = util.getConnection();
177: Statement stmt = dbConn.createStatement();
178:
179: String sql = null;
180:
181: sql = "CREATE TABLE tbl1 \n" + "( \n"
182: + " id INTEGER NOT NULL PRIMARY KEY, "
183: + " tbl2_id integer" + ")";
184: stmt.executeUpdate(sql);
185:
186: sql = "CREATE TABLE tbl2 \n" + "( \n"
187: + " id INTEGER NOT NULL PRIMARY KEY,"
188: + " tbl1_id integer " + ")";
189: stmt.executeUpdate(sql);
190:
191: sql = "alter table tbl1 add foreign key (tbl2_id) references tbl2 (id)";
192: stmt.executeUpdate(sql);
193:
194: sql = "alter table tbl2 add foreign key (tbl1_id) references tbl1 (id)";
195: stmt.executeUpdate(sql);
196:
197: return dbConn;
198: }
199:
200: public void testDirectCycle() {
201: try {
202: WbConnection con = createSingleLevelCycleDB();
203: TableIdentifier tbl = new TableIdentifier("tbl1");
204: TableDependency dep = new TableDependency(con, tbl);
205: dep.readTreeForChildren();
206: assertEquals(false, dep.wasAborted());
207: assertNotNull("No root returned", dep.getRootNode());
208: dep.getRootNode().printAll();
209: } catch (Exception e) {
210: e.printStackTrace();
211: fail(e.getMessage());
212: } finally {
213: ConnectionMgr.getInstance().disconnectAll();
214: }
215: }
216: }
|