001: /*
002: * SelectAnalyzer.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.gui.completion;
013:
014: import java.util.ArrayList;
015: import java.util.LinkedList;
016: import java.util.List;
017: import java.util.regex.Pattern;
018: import workbench.db.TableIdentifier;
019: import workbench.db.WbConnection;
020: import workbench.log.LogMgr;
021: import workbench.resource.ResourceMgr;
022: import workbench.sql.formatter.SQLLexer;
023: import workbench.sql.formatter.SQLToken;
024: import workbench.sql.formatter.SqlFormatter;
025: import workbench.util.SqlUtil;
026: import workbench.util.StringUtil;
027: import workbench.util.TableAlias;
028:
029: /**
030: *
031: * @author support@sql-workbench.net
032: */
033: public class SelectAnalyzer extends BaseAnalyzer {
034: private final int NO_JOIN_ON = 0;
035: private final int JOIN_ON_TABLE_LIST = 1;
036: private final int JOIN_ON_COLUMN_LIST = 2;
037:
038: public SelectAnalyzer(WbConnection conn, String statement,
039: int cursorPos) {
040: super (conn, statement, cursorPos);
041: }
042:
043: @SuppressWarnings("unchecked")
044: protected void checkContext() {
045: this .context = NO_CONTEXT;
046:
047: String currentWord = getCurrentWord();
048:
049: setAppendDot(false);
050: setColumnPrefix(null);
051: int fromPos = SqlUtil.getFromPosition(this .sql);
052:
053: int wherePos = -1;
054:
055: if (fromPos > 0) {
056: wherePos = SqlUtil.getWherePosition(sql);
057: }
058:
059: int groupPos = SqlUtil.getKeywordPosition("GROUP BY", sql);
060: int havingPos = SqlUtil.getKeywordPosition("HAVING", sql);
061: int orderPos = SqlUtil.getKeywordPosition("ORDER BY", sql);
062:
063: // find the tables from the FROM clause
064: List<String> tables = SqlUtil.getTables(sql, true);
065:
066: boolean afterWhere = (wherePos > 0 && cursorPos > wherePos);
067: boolean afterGroup = (groupPos > 0 && cursorPos > groupPos);
068: if (havingPos > -1 && afterGroup) {
069: afterGroup = (cursorPos < havingPos);
070: }
071:
072: if (orderPos > -1 && afterGroup) {
073: afterGroup = (cursorPos < orderPos);
074: }
075:
076: boolean afterHaving = (havingPos > 0 && cursorPos > havingPos);
077: if (orderPos > -1 && afterHaving) {
078: afterHaving = (cursorPos < orderPos);
079: }
080:
081: boolean inTableList = (fromPos < 0
082: || (wherePos < 0 && cursorPos > fromPos) || (wherePos > -1
083: && cursorPos > fromPos && cursorPos <= wherePos));
084:
085: if (inTableList && afterGroup)
086: inTableList = false;
087: if (inTableList && orderPos > -1 && cursorPos > orderPos)
088: inTableList = false;
089:
090: int joinState = inJoinONPart();
091:
092: if (inTableList && joinState != JOIN_ON_TABLE_LIST) {
093: inTableList = false;
094: }
095:
096: if (inTableList) {
097: String q = getQualifierLeftOfCursor();
098: if (q != null) {
099: setOverwriteCurrentWord(true);//!this.dbConnection.getMetadata().isKeyword(q));
100: }
101:
102: // If no FROM is present but there is a word with a dot
103: // at the cursor position we will first try to use that
104: // as a table name (because usually you type the table name
105: // first in the SELECT list. If no columns for that
106: // name are found, BaseAnalyzer will try to use that as a
107: // schema name.
108: if (fromPos < 0 && q != null) {
109: context = CONTEXT_TABLE_OR_COLUMN_LIST;
110: this .tableForColumnList = new TableIdentifier(q);
111: } else {
112: context = CONTEXT_TABLE_LIST;
113: }
114:
115: // The schemaForTableList will be set anyway
116: // in order to allow BaseAnalyzer to retrieve
117: // the table list
118: if (q != null) {
119: this .schemaForTableList = q;
120: } else {
121: this .schemaForTableList = this .dbConnection
122: .getMetadata().getCurrentSchema();
123: }
124: } else {
125: context = CONTEXT_COLUMN_LIST;
126: // current cursor position is after the WHERE
127: // statement or before the FROM statement, so
128: // we'll try to find a proper column list
129:
130: int count = tables.size();
131: this .tableForColumnList = null;
132:
133: if (afterGroup) {
134: this .elements = getColumnsForGroupBy();
135: this .addAllMarker = true;
136: this .title = ResourceMgr.getString("TxtTitleColumns");
137: return;
138: }
139:
140: if (afterHaving) {
141: this .elements = getColumnsForHaving();
142: this .addAllMarker = false;
143: this .title = ResourceMgr
144: .getString("TxtTitleGroupFuncs");
145: return;
146: }
147:
148: this .addAllMarker = !afterWhere;
149:
150: // check if the current qualifier is either one of the
151: // tables in the table list or one of the aliases used
152: // in the table list.
153: TableAlias currentAlias = null;
154: String table = null;
155: if (currentWord != null) {
156: int pos = currentWord.indexOf('.');
157: if (pos > -1) {
158: table = currentWord.substring(0, pos);
159: }
160: }
161:
162: if (table != null) {
163: // for (String element : tables)
164: // {
165: // TableAlias tbl = new TableAlias(element);
166: //
167: // if (tbl.isTableOrAlias(table))
168: // {
169: // tableForColumnList = tbl.getTable();
170: // currentAlias = tbl;
171: // break;
172: // }
173: // }
174:
175: currentAlias = findAlias(table, tables);
176:
177: if (currentAlias != null) {
178: tableForColumnList = currentAlias.getTable();
179: } else if (this .parentAnalyzer != null) {
180: // if we didn't find the alias in the current SELECT's
181: // tables we check the "parent" statement as we might be inside
182: // a sub-select
183: List<TableAlias> outerTables = this .parentAnalyzer
184: .getTables();
185: if (outerTables != null) {
186: for (TableAlias outer : outerTables) {
187: if (outer.isTableOrAlias(table)) {
188: tableForColumnList = outer.getTable();
189: currentAlias = outer;
190: }
191: }
192: }
193: }
194: } else if (count == 1) {
195: TableAlias tbl = new TableAlias(tables.get(0));
196: tableForColumnList = tbl.getTable();
197: }
198:
199: if (tableForColumnList == null) {
200: context = CONTEXT_FROM_LIST;
201: this .elements = new ArrayList();
202: for (String entry : tables) {
203: TableAlias tbl = new TableAlias(entry);
204: this .elements.add(tbl);
205: setAppendDot(true);
206: }
207: } else if (currentAlias != null) {
208: setColumnPrefix(currentAlias.getNameToUse());
209: }
210: }
211: }
212:
213: private TableAlias findAlias(String toSearch,
214: List<String> possibleTables) {
215: for (String element : possibleTables) {
216: TableAlias tbl = new TableAlias(element);
217:
218: if (tbl.isTableOrAlias(toSearch)) {
219: return tbl;
220: }
221: }
222: return null;
223: }
224:
225: private int inJoinONPart() {
226: int result = NO_JOIN_ON;
227: try {
228: boolean afterFrom = false;
229: boolean inONPart = false;
230: int lastJoin = -1;
231: SQLLexer lexer = new SQLLexer(this .sql);
232: SQLToken token = lexer.getNextToken(false, false);
233: int bracketCount = 0;
234: while (token != null) {
235: String t = token.getContents();
236: if (afterFrom) {
237: if ("(".equals(t)) {
238: bracketCount++;
239: if (inONPart
240: && cursorPos >= token.getCharBegin())
241: result = JOIN_ON_COLUMN_LIST;
242: } else if (")".equals(t)) {
243: if (bracketCount > 0) {
244: if (inONPart
245: && cursorPos < token.getCharBegin())
246: return JOIN_ON_COLUMN_LIST;
247: }
248: bracketCount--;
249: } else if ("ON".equals(t)) {
250: inONPart = (cursorPos >= token.getCharBegin());
251: } else if (t.equals("JOIN")
252: || SqlUtil.JOIN_KEYWORDS.contains(t)) {
253: inONPart = false;
254: if (t.equals("JOIN")
255: && cursorPos > token.getCharEnd())
256: result = JOIN_ON_TABLE_LIST;
257: else
258: result = NO_JOIN_ON;
259: } else if (SqlFormatter.FROM_TERMINAL.contains(t)) {
260: return result;
261: }
262: } else {
263: if (SqlFormatter.FROM_TERMINAL.contains(t))
264: break;
265: if (t.equals("FROM")) {
266: if (cursorPos < token.getCharBegin())
267: return NO_JOIN_ON;
268: afterFrom = true;
269: result = JOIN_ON_TABLE_LIST;
270: }
271: }
272: token = lexer.getNextToken(false, false);
273: }
274: } catch (Exception e) {
275: LogMgr.logError("SelectAnalyzer.inJoinONPart()",
276: "Error parsing SQL Statement!", e);
277: }
278: return result;
279: }
280:
281: private List getColumnsForHaving() {
282: List<String> cols = SqlUtil.getSelectColumns(this .sql, false);
283: List<String> validCols = new LinkedList<String>();
284: for (int i = 0; i < cols.size(); i++) {
285: String col = cols.get(i);
286: if (col.indexOf('(') > -1 && col.indexOf(')') > -1) {
287: validCols.add(col);
288: }
289: }
290: return validCols;
291: }
292:
293: private List getColumnsForGroupBy() {
294: List<String> cols = SqlUtil.getSelectColumns(this .sql, false);
295: List<String> validCols = new LinkedList<String>();
296: String[] funcs = new String[] { "sum", "count", "avg", "min",
297: "max" };
298: StringBuilder regex = new StringBuilder(50);
299: for (int i = 0; i < funcs.length; i++) {
300: if (i > 0)
301: regex.append('|');
302: regex.append("\\s*");
303: regex.append(funcs[i]);
304: regex.append("\\s*\\(");
305: }
306: Pattern aggregate = Pattern.compile(regex.toString(),
307: Pattern.CASE_INSENSITIVE);
308: for (int i = 0; i < cols.size(); i++) {
309: String col = cols.get(i);
310: if (StringUtil.findPattern(aggregate, col, 0) == -1) {
311: validCols.add(col);
312: }
313: }
314: return validCols;
315: }
316:
317: /**
318: * This will only return any tables in the FROM clause to
319: * support correlated sub-queries
320: */
321: public List<TableAlias> getTables() {
322: List<String> tables = SqlUtil.getTables(sql, true);
323: List<TableAlias> result = new ArrayList<TableAlias>(tables
324: .size());
325: for (String s : tables) {
326: result.add(new TableAlias(s));
327: }
328: return result;
329: }
330: }
|