001: package net.sourceforge.squirrel_sql.plugins.codecompletion.completionfunctions;
002:
003: import java.sql.SQLException;
004: import java.util.Arrays;
005: import java.util.Enumeration;
006: import java.util.HashMap;
007: import java.util.Hashtable;
008: import java.util.Iterator;
009: import java.util.StringTokenizer;
010: import java.util.Vector;
011:
012: import net.sourceforge.squirrel_sql.client.session.ISession;
013: import net.sourceforge.squirrel_sql.fw.sql.ForeignKeyInfo;
014: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
015: import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
016: import net.sourceforge.squirrel_sql.fw.util.StringManager;
017: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
018: import net.sourceforge.squirrel_sql.plugins.codecompletion.CodeCompletionInfo;
019:
020: public abstract class AbstractJoin extends CodeCompletionFunction {
021:
022: private static final StringManager s_stringMgr = StringManagerFactory
023: .getStringManager(AbstractJoin.class);
024:
025: private ISession _session;
026:
027: public AbstractJoin(ISession session) {
028: _session = session;
029: }
030:
031: public CodeCompletionInfo[] getFunctionResults(String functionSting) {
032: try {
033: if (false == functionMatches(functionSting)) {
034: return null;
035: }
036:
037: StringTokenizer st = new StringTokenizer(functionSting, ",");
038:
039: if (3 > st.countTokens()) {
040: // i18n[codecompletion.function.needsTwoArgs=function needs at least two arguments]
041: _session
042: .showMessage(s_stringMgr
043: .getString("codecompletion.function.needsTwoArgs"));
044: return null;
045: }
046:
047: if (false == functionSting.trim().endsWith(",")) {
048: // i18n[codecompletion.function.mustEndWith=function must end with ',']
049: _session
050: .showMessage(s_stringMgr
051: .getString("codecompletion.function.mustEndWith"));
052: return null;
053: }
054:
055: st.nextToken(); // remove the function name
056:
057: String catalog = _session.getSQLConnection().getCatalog();
058: SQLDatabaseMetaData jdbcMetaData = _session
059: .getSQLConnection().getSQLMetaData();
060: Vector<String> tables = new Vector<String>();
061: HashMap<String, String> schemas = new HashMap<String, String>();
062: while (st.hasMoreTokens()) {
063: String[] catSchemTab = st.nextToken().trim().split(
064: "\\.");
065: String schema = null;
066: String table = catSchemTab[catSchemTab.length - 1];
067: if (2 <= catSchemTab.length) {
068: schema = catSchemTab[catSchemTab.length - 2];
069: schemas.put(schema, schema);
070: }
071:
072: table = _session.getSchemaInfo()
073: .getCaseSensitiveTableName(table);
074: if (null == table) {
075: // i18n[codecompletion.unknowntable=unknown table {0}]
076: _session.showMessage(s_stringMgr.getString(
077: "codecompletion.unknowntable", table));
078: return null;
079: }
080: tables.add(table);
081:
082: if (null == schema) {
083: if (null == catalog) {
084: catalog = _session.getSQLConnection()
085: .getCatalog();
086: }
087: ITableInfo[] infos = _session.getSchemaInfo()
088: .getITableInfos(catalog, null,
089: tables.get(0),
090: new String[] { "TABLE" });
091:
092: if (0 == infos.length) {
093: // Needed for example on PostgreSQL
094: infos = _session.getSchemaInfo()
095: .getITableInfos(null, null,
096: tables.get(0),
097: new String[] { "TABLE" });
098: }
099:
100: for (int i = 0; i < infos.length; i++) {
101: String schemBuf = infos[i].getSchemaName();
102: schemas.put(schemBuf, schemBuf);
103: }
104: }
105: }
106:
107: Vector<CodeCompletionInfo> ret = new Vector<CodeCompletionInfo>();
108:
109: for (Iterator<String> i = schemas.keySet().iterator(); i
110: .hasNext();) {
111: CodeCompletionInfo[] buf = getResultsForSchema(tables,
112: jdbcMetaData, catalog, i.next());
113: ret.addAll(Arrays.asList(buf));
114: }
115:
116: return ret.toArray(new CodeCompletionInfo[ret.size()]);
117: } catch (SQLException e) {
118: throw new RuntimeException(e);
119: }
120: }
121:
122: private CodeCompletionInfo[] getResultsForSchema(
123: Vector<String> tables, SQLDatabaseMetaData jdbcMetaData,
124: String catalog, String schema) throws SQLException {
125: Vector<String> completions = new Vector<String>();
126: completions.add("");
127:
128: for (int i = 1; i < tables.size(); i++) {
129: Hashtable<String, Vector<String>> conditionByFkName = new Hashtable<String, Vector<String>>();
130: Hashtable<String, Vector<ColBuffer>> colBuffersByFkName = new Hashtable<String, Vector<ColBuffer>>();
131: ForeignKeyInfo[] infos = jdbcMetaData.getImportedKeysInfo(
132: catalog, schema, tables.get(i - 1));
133: fillConditionByFkName(infos, tables.get(i - 1), tables
134: .get(i), conditionByFkName, colBuffersByFkName);
135:
136: infos = jdbcMetaData.getExportedKeysInfo(catalog, schema,
137: tables.get(i - 1));
138: fillConditionByFkName(infos, tables.get(i - 1), tables
139: .get(i), conditionByFkName, colBuffersByFkName);
140:
141: Vector<String> twoTableCompletions = new Vector<String>();
142: for (Enumeration<String> e = conditionByFkName.keys(); e
143: .hasMoreElements();) {
144: String fkName = e.nextElement();
145:
146: String joinClause = getJoinClause(fkName, tables
147: .get(i - 1), tables.get(i), colBuffersByFkName);
148:
149: StringBuffer sb = new StringBuffer();
150: sb.append(joinClause).append(tables.get(i)).append(
151: " ON ");
152:
153: Vector<String> conditions = conditionByFkName
154: .get(fkName);
155: if (1 == conditions.size()) {
156: sb.append(conditions.get(0));
157: } else if (1 < conditions.size()) {
158: sb.append("(");
159: sb.append(conditions.get(0));
160: for (int j = 1; j < conditions.size(); j++) {
161: sb.append(" AND ").append(conditions.get(j));
162: }
163: sb.append(")");
164: }
165: sb.append("\n");
166:
167: twoTableCompletions.add(sb.toString());
168: }
169:
170: if (0 == conditionByFkName.size()) {
171: String joinClause = getJoinClause(null, tables
172: .get(i - 1), tables.get(i), colBuffersByFkName);
173: twoTableCompletions.add(joinClause + tables.get(i)
174: + " ON " + tables.get(i - 1) + ". = "
175: + tables.get(i) + ".\n");
176: }
177:
178: Vector<String> newCompletions = new Vector<String>();
179:
180: for (int j = 0; j < completions.size(); j++) {
181: String begin = completions.get(j);
182: for (int k = 0; k < twoTableCompletions.size(); k++) {
183: String end = twoTableCompletions.get(k);
184: newCompletions.add(begin + end);
185: }
186: }
187: completions = newCompletions;
188: }
189:
190: GenericCodeCompletionInfo[] ret = new GenericCodeCompletionInfo[completions
191: .size()];
192:
193: for (int i = 0; i < completions.size(); i++) {
194: ret[i] = new GenericCodeCompletionInfo(completions.get(i));
195: }
196:
197: return ret;
198: }
199:
200: protected abstract String getJoinClause(String fkName,
201: String table1, String table2,
202: Hashtable<String, Vector<ColBuffer>> colBuffersByFkName);
203:
204: private void fillConditionByFkName(ForeignKeyInfo[] infos,
205: String table1, String table2,
206: Hashtable<String, Vector<String>> conditionByFkName,
207: Hashtable<String, Vector<ColBuffer>> colBuffersByFkName)
208: throws SQLException {
209: for (int i = 0; i < infos.length; i++) {
210: String pkTableName = infos[i].getPrimaryKeyTableName();
211: String pkColumnName = infos[i].getPrimaryKeyColumnName();
212: String fkTableName = infos[i].getForeignKeyTableName();
213: String fkColumnName = infos[i].getForeignKeyColumnName();
214: String fkName = infos[i].getForeignKeyName();
215: if ((pkTableName.equalsIgnoreCase(table2) && fkTableName
216: .equalsIgnoreCase(table1))
217: || (fkTableName.equalsIgnoreCase(table2) && pkTableName
218: .equalsIgnoreCase(table1))) {
219:
220: Vector<String> conditions = conditionByFkName
221: .get(fkName);
222: if (null == conditions) {
223: conditions = new Vector<String>();
224: conditionByFkName.put(fkName, conditions);
225: }
226:
227: StringBuilder tmp = new StringBuilder(pkTableName);
228: tmp.append(".");
229: tmp.append(pkColumnName);
230: tmp.append(" = ");
231: tmp.append(fkTableName);
232: tmp.append(".");
233: tmp.append(fkColumnName);
234:
235: conditions.add(tmp.toString());
236:
237: Vector<ColBuffer> cols = colBuffersByFkName.get(fkName);
238: if (null == cols) {
239: cols = new Vector<ColBuffer>();
240: colBuffersByFkName.put(fkName, cols);
241: }
242: cols.add(new ColBuffer(fkTableName, fkColumnName));
243: }
244: }
245: }
246:
247: static class ColBuffer {
248: String tableName;
249: String colName;
250:
251: public ColBuffer(String tableName, String colName) {
252: this.tableName = tableName;
253: this.colName = colName;
254: }
255: }
256: }
|