001: /*
002: * ReferenceTableNavigation.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.util.Iterator;
015: import java.util.List;
016: import java.util.Map;
017: import workbench.log.LogMgr;
018: import workbench.storage.ColumnData;
019: import workbench.storage.SqlLiteralFormatter;
020:
021: /**
022: * A class to generate SQL SELECT statements that will retrieve the parent
023: * or child records regarding the foreign key constraints for the given
024: * table.
025: *
026: * @author support@sql-workbench.net
027: */
028: public class ReferenceTableNavigation {
029: private TableIdentifier baseTable;
030: private WbConnection dbConn;
031: private SqlLiteralFormatter formatter;
032: private TableDependency dependencyTree;
033:
034: public ReferenceTableNavigation(TableIdentifier table,
035: WbConnection con) {
036: this .baseTable = table;
037: this .dbConn = con;
038: this .formatter = new SqlLiteralFormatter(dbConn);
039: }
040:
041: public void readTreeForChildren() {
042: readDependencyTree(true);
043: }
044:
045: public void readTreeForParents() {
046: readDependencyTree(false);
047: }
048:
049: protected void readDependencyTree(boolean forChildren) {
050: dependencyTree = new TableDependency(this .dbConn,
051: this .baseTable);
052: dependencyTree.setRetrieveDirectChildrenOnly(true);
053: if (forChildren) {
054: dependencyTree.readTreeForChildren();
055: } else {
056: dependencyTree.readTreeForParents();
057: }
058: }
059:
060: public DependencyNode getNodeForTable(TableIdentifier tbl) {
061: if (this .dependencyTree == null)
062: return null;
063:
064: TableIdentifier table = tbl.createCopy();
065: if (table.getSchema() == null) {
066: table.setSchema(this .dbConn.getMetadata().getSchemaToUse());
067: }
068: if (table.getCatalog() == null) {
069: table.setCatalog(this .dbConn.getMetadata()
070: .getCurrentCatalog());
071: }
072: table.adjustCase(dbConn);
073: return dependencyTree.findLeafNodeForTable(table);
074: }
075:
076: public TableDependency getTree() {
077: return this .dependencyTree;
078: }
079:
080: public String getSelectForChild(TableIdentifier tbl,
081: List<List<ColumnData>> values) {
082: return generateSelect(tbl, true, values);
083: }
084:
085: public String getSelectForParent(TableIdentifier tbl,
086: List<List<ColumnData>> values) {
087: return generateSelect(tbl, false, values);
088: }
089:
090: private String generateSelect(TableIdentifier tbl,
091: boolean forChildren, List<List<ColumnData>> values) {
092: String result = null;
093: try {
094: if (this .dependencyTree == null)
095: this .readDependencyTree(forChildren);
096:
097: DependencyNode node = getNodeForTable(tbl);
098:
099: StringBuilder sql = new StringBuilder(100);
100: sql.append("SELECT * \nFROM ");
101: sql.append(node.getTable().getTableExpression(this .dbConn));
102: sql.append("\nWHERE ");
103: addWhere(sql, node, values);
104: result = sql.toString();
105: } catch (Exception e) {
106: LogMgr.logError("TableNavigation.getSelectsForParents()",
107: "Error retrieving parent tables", e);
108: }
109: return result;
110: }
111:
112: private void addWhere(StringBuilder sql, DependencyNode node,
113: List<List<ColumnData>> values) {
114: Map<String, String> colMapping = node.getColumns();
115:
116: Iterator<List<ColumnData>> rowItr = values.iterator();
117: while (rowItr.hasNext()) {
118: List<ColumnData> row = rowItr.next();
119: sql.append('(');
120: Iterator<Map.Entry<String, String>> colItr = colMapping
121: .entrySet().iterator();
122: while (colItr.hasNext()) {
123: Map.Entry<String, String> entry = colItr.next();
124: String childColumn = entry.getKey();
125: String parentColumn = entry.getValue();
126: ColumnData data = getPkValue(row, parentColumn);
127: if (data == null)
128: continue;
129: sql.append(childColumn);
130: if (data.isNull()) {
131: sql.append(" IS NULL");
132: } else {
133: sql.append(" = ");
134: sql.append(formatter.getDefaultLiteral(data));
135: }
136: if (colItr.hasNext()) {
137: sql.append(" AND ");
138: }
139: }
140: sql.append(')');
141: if (rowItr.hasNext()) {
142: sql.append("\n OR ");
143: }
144: }
145: }
146:
147: private ColumnData getPkValue(List<ColumnData> colData,
148: String column) {
149: for (ColumnData data : colData) {
150: if (data.getIdentifier().getColumnName().equalsIgnoreCase(
151: column))
152: return data;
153: }
154: return null;
155: }
156: }
|