001: /*
002: * ReferenceTableNavigationTest.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.Connection;
015: import java.sql.ResultSet;
016: import java.sql.Statement;
017: import java.util.LinkedList;
018: import java.util.List;
019: import junit.framework.TestCase;
020: import workbench.TestUtil;
021: import workbench.storage.ColumnData;
022:
023: /**
024: * @author support@sql-workbench.net
025: */
026: public class ReferenceTableNavigationTest extends TestCase {
027:
028: public ReferenceTableNavigationTest(String testName) {
029: super (testName);
030: }
031:
032: public void testChildNavigation() {
033: TestUtil util = new TestUtil("childNav");
034: try {
035: WbConnection con = util.getConnection();
036: Connection sqlCon = con.getSqlConnection();
037: Statement stmt = sqlCon.createStatement();
038: String baseSql = "CREATE TABLE base \n" + "( \n"
039: + " id1 INTEGER NOT NULL, \n"
040: + " id2 INTEGER NOT NULL, \n"
041: + " primary key (id1, id2) \n" + ")";
042: stmt.execute(baseSql);
043: String child1Sql = "CREATE TABLE child1 \n"
044: + "( \n"
045: + " id INTEGER NOT NULL PRIMARY KEY, \n"
046: + " base_id1 INTEGER NOT NULL, \n"
047: + " base_id2 INTEGER NOT NULL, \n"
048: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
049: + ")";
050: stmt.execute(child1Sql);
051:
052: String child2Sql = "CREATE TABLE child2 \n"
053: + "( \n"
054: + " id INTEGER NOT NULL PRIMARY KEY, \n"
055: + " base_id1 INTEGER NOT NULL, \n"
056: + " base_id2 INTEGER NOT NULL, \n"
057: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
058: + ")";
059: stmt.execute(child2Sql);
060:
061: String child3Sql = "CREATE TABLE child2_detail \n"
062: + "( \n"
063: + " id INTEGER NOT NULL PRIMARY KEY, \n"
064: + " child_id INTEGER NOT NULL, \n"
065: + " FOREIGN KEY (child_id) REFERENCES child2 (id) \n"
066: + ")";
067: stmt.execute(child3Sql);
068:
069: stmt.execute("insert into base (id1, id2) values (1,1)");
070: stmt.execute("insert into base (id1, id2) values (2,2)");
071: stmt.execute("insert into base (id1, id2) values (3,3)");
072:
073: // child records for base(1,1)
074: stmt
075: .execute("insert into child1 (id, base_id1,base_id2) values (1,1,1)");
076:
077: // child1 records for base(2,2)
078: stmt
079: .execute("insert into child1 (id, base_id1,base_id2) values (2,2,2)");
080: stmt
081: .execute("insert into child1 (id, base_id1,base_id2) values (3,2,2)");
082:
083: // child1 records for base(3,3)
084: stmt
085: .execute("insert into child1 (id, base_id1,base_id2) values (4,3,3)");
086: stmt
087: .execute("insert into child1 (id, base_id1,base_id2) values (5,3,3)");
088: stmt
089: .execute("insert into child1 (id, base_id1,base_id2) values (6,3,3)");
090:
091: // child records for base(1,1)
092: stmt
093: .execute("insert into child2 (id, base_id1,base_id2) values (201,1,1)");
094:
095: // child records for base(2,2)
096: stmt
097: .execute("insert into child2 (id, base_id1,base_id2) values (202,2,2)");
098: stmt
099: .execute("insert into child2 (id, base_id1,base_id2) values (203,2,2)");
100:
101: // child records for base(3,3)
102: stmt
103: .execute("insert into child2 (id, base_id1,base_id2) values (204,3,3)");
104: stmt
105: .execute("insert into child2 (id, base_id1,base_id2) values (205,3,3)");
106: stmt
107: .execute("insert into child2 (id, base_id1,base_id2) values (206,3,3)");
108:
109: List<List<ColumnData>> rows = new LinkedList<List<ColumnData>>();
110:
111: List<ColumnData> row = new LinkedList<ColumnData>();
112: row
113: .add(new ColumnData(new Integer(1),
114: new ColumnIdentifier("id1",
115: java.sql.Types.INTEGER)));
116: row
117: .add(new ColumnData(new Integer(1),
118: new ColumnIdentifier("id2",
119: java.sql.Types.INTEGER)));
120: rows.add(row);
121:
122: row = new LinkedList<ColumnData>();
123: row
124: .add(new ColumnData(new Integer(2),
125: new ColumnIdentifier("id1",
126: java.sql.Types.INTEGER)));
127: row
128: .add(new ColumnData(new Integer(2),
129: new ColumnIdentifier("id2",
130: java.sql.Types.INTEGER)));
131: rows.add(row);
132:
133: TableIdentifier base = new TableIdentifier("base");
134:
135: ReferenceTableNavigation nav = new ReferenceTableNavigation(
136: base, con);
137: nav.readTreeForChildren();
138:
139: TableIdentifier t1 = new TableIdentifier("child1");
140: t1.adjustCase(con);
141:
142: String select = nav.getSelectForChild(t1, rows);
143: assertNotNull("Select for Child1 not created", select);
144:
145: System.out.println("select child1 with:" + select);
146: ResultSet rs = stmt.executeQuery(select);
147: int count = 0;
148: while (rs.next()) {
149: count++;
150: int id = rs.getInt(1);
151: int bid1 = rs.getInt(2);
152: int bid2 = rs.getInt(3);
153: if (id == 1) {
154: assertEquals(1, bid1);
155: assertEquals(1, bid2);
156: } else if (id == 2 || id == 3) {
157: assertEquals(2, bid1);
158: assertEquals(2, bid2);
159: } else {
160: fail("Incorrect id = " + id
161: + " returned from SELECT");
162: }
163: }
164: assertEquals(3, count);
165: rs.close();
166:
167: TableIdentifier t2 = new TableIdentifier("child2");
168: t2.adjustCase(con);
169:
170: String select2 = nav.getSelectForChild(t2, rows);
171: assertNotNull("Child table 2 not found", select2);
172:
173: System.out.println("select child2 with:" + select2);
174: rs = stmt.executeQuery(select2);
175: count = 0;
176: while (rs.next()) {
177: count++;
178: int id = rs.getInt(1);
179: int bid1 = rs.getInt(2);
180: int bid2 = rs.getInt(3);
181: if (id == 201) {
182: assertEquals(1, bid1);
183: assertEquals(1, bid2);
184: } else if (id == 202 || id == 203) {
185: assertEquals(2, bid1);
186: assertEquals(2, bid2);
187: } else {
188: fail("Incorrect id = " + id
189: + " returned from SELECT");
190: }
191: }
192: assertEquals(3, count);
193:
194: } catch (Exception e) {
195: e.printStackTrace();
196: fail(e.getMessage());
197: }
198: }
199:
200: public void testParentNavigation() {
201: TestUtil util = new TestUtil("parentNav");
202: try {
203: WbConnection con = util.getConnection();
204: Connection sqlCon = con.getSqlConnection();
205: Statement stmt = sqlCon.createStatement();
206: String baseSql = "CREATE TABLE base \n" + "( \n"
207: + " id1 INTEGER NOT NULL, \n"
208: + " id2 INTEGER NOT NULL, \n"
209: + " data VARCHAR(10), \n"
210: + " primary key (id1, id2) \n" + ")";
211: stmt.execute(baseSql);
212: String child1Sql = "CREATE TABLE child1 \n"
213: + "( \n"
214: + " id INTEGER NOT NULL PRIMARY KEY, \n"
215: + " base_id1 INTEGER NOT NULL, \n"
216: + " base_id2 INTEGER NOT NULL, \n"
217: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
218: + ")";
219: stmt.execute(child1Sql);
220:
221: String child2Sql = "CREATE TABLE child2 \n"
222: + "( \n"
223: + " id INTEGER NOT NULL PRIMARY KEY, \n"
224: + " base_id1 INTEGER NOT NULL, \n"
225: + " base_id2 INTEGER NOT NULL, \n"
226: + " FOREIGN KEY (base_id1, base_id2) REFERENCES base (id1,id2) \n"
227: + ")";
228: stmt.execute(child2Sql);
229:
230: String child3Sql = "CREATE TABLE child2_detail \n"
231: + "( \n"
232: + " id INTEGER NOT NULL PRIMARY KEY, \n"
233: + " child_id INTEGER NOT NULL, \n"
234: + " FOREIGN KEY (child_id) REFERENCES child2 (id) \n"
235: + ")";
236: stmt.execute(child3Sql);
237:
238: stmt
239: .execute("insert into base (id1, id2, data) values (1,1, 'one')");
240: stmt
241: .execute("insert into base (id1, id2, data) values (2,2, 'two')");
242: stmt
243: .execute("insert into base (id1, id2, data) values (3,3, 'three')");
244:
245: // child records for base(1,1)
246: stmt
247: .execute("insert into child1 (id, base_id1,base_id2) values (1,1,1)");
248:
249: // child1 records for base(2,2)
250: stmt
251: .execute("insert into child1 (id, base_id1,base_id2) values (2,2,2)");
252: stmt
253: .execute("insert into child1 (id, base_id1,base_id2) values (3,2,2)");
254:
255: // child1 records for base(3,3)
256: stmt
257: .execute("insert into child1 (id, base_id1,base_id2) values (4,3,3)");
258: stmt
259: .execute("insert into child1 (id, base_id1,base_id2) values (5,3,3)");
260: stmt
261: .execute("insert into child1 (id, base_id1,base_id2) values (6,3,3)");
262:
263: // child records for base(1,1)
264: stmt
265: .execute("insert into child2 (id, base_id1,base_id2) values (201,1,1)");
266:
267: // child records for base(2,2)
268: stmt
269: .execute("insert into child2 (id, base_id1,base_id2) values (202,2,2)");
270: stmt
271: .execute("insert into child2 (id, base_id1,base_id2) values (203,2,2)");
272:
273: // child records for base(3,3)
274: stmt
275: .execute("insert into child2 (id, base_id1,base_id2) values (204,3,3)");
276: stmt
277: .execute("insert into child2 (id, base_id1,base_id2) values (205,3,3)");
278: stmt
279: .execute("insert into child2 (id, base_id1,base_id2) values (206,3,3)");
280:
281: List<List<ColumnData>> rows = new LinkedList<List<ColumnData>>();
282:
283: List<ColumnData> row = new LinkedList<ColumnData>();
284: row.add(new ColumnData(new Integer(1),
285: new ColumnIdentifier("base_id1",
286: java.sql.Types.INTEGER)));
287: row.add(new ColumnData(new Integer(1),
288: new ColumnIdentifier("base_id2",
289: java.sql.Types.INTEGER)));
290: rows.add(row);
291:
292: row = new LinkedList<ColumnData>();
293: row.add(new ColumnData(new Integer(2),
294: new ColumnIdentifier("base_id1",
295: java.sql.Types.INTEGER)));
296: row.add(new ColumnData(new Integer(2),
297: new ColumnIdentifier("base_id2",
298: java.sql.Types.INTEGER)));
299: rows.add(row);
300:
301: TableIdentifier base = new TableIdentifier("child1");
302: ReferenceTableNavigation nav = new ReferenceTableNavigation(
303: base, con);
304: nav.readTreeForParents();
305: TableDependency tree = nav.getTree();
306: assertNotNull("No parent found!", tree);
307: List<DependencyNode> leafs = tree.getLeafs();
308: assertNotNull("No leafs for parent!", leafs);
309: assertEquals("No leafs for parent!", 1, leafs.size());
310:
311: TableIdentifier tbl = new TableIdentifier("base");
312: String select1 = nav.getSelectForParent(tbl, rows);
313:
314: System.out.println("select parent with = " + select1);
315: ResultSet rs = stmt.executeQuery(select1);
316: int count = 0;
317: while (rs.next()) {
318: count++;
319: int bid1 = rs.getInt("id1");
320: int bid2 = rs.getInt("id2");
321: String data = rs.getString("data");
322:
323: if ("one".equals(data)) {
324: assertEquals(1, bid1);
325: assertEquals(1, bid2);
326: } else if ("two".equals(data)) {
327: assertEquals(2, bid1);
328: assertEquals(2, bid2);
329: } else {
330: fail("Incorrect row = with (" + bid1 + "," + bid2
331: + ") returned from base table");
332: }
333: }
334: assertEquals(2, count);
335: rs.close();
336:
337: } catch (Exception e) {
338: e.printStackTrace();
339: fail(e.getMessage());
340: }
341: }
342:
343: }
|