001: /*
002: * Copyright 2007 Roy van der Kuil (roy@vanderkuil.nl) and Stefan Rotman (stefan@rotman.net)
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package nl.improved.sqlclient;
017:
018: import java.util.List;
019: import java.util.Arrays;
020: import java.util.ArrayList;
021: import java.util.Iterator;
022:
023: /**
024: * A SQL Utility class.
025: * This class helps in tab completion.
026: */
027: public class SQLUtil {
028:
029: /**
030: * A regular expression statement for name chars.
031: */
032: private static final String NAMING_CHAR = "(_|-|[0-9]|[A-Z])";
033: /**
034: * A table name regular expression statement.
035: */
036: private static final String TABLE = "[A-Z]+" + NAMING_CHAR + "*";
037: /**
038: * A column name regular expression statement.
039: */
040: private static final String COLUMN = "(\\*|[A-Z]+" + NAMING_CHAR
041: + "*)";
042: /**
043: * A variable (table name + columnname) regular expression statement.
044: */
045: private static final String VAR = "(|" + TABLE + "\\.)" + COLUMN;
046: /**
047: * SQL Comparators.
048: */
049: private static final String COMPARATOR = "(=|<>|<|>)";
050: /**
051: * A sql variable description.
052: */
053: private static final String VALUE = "('.*'|[0-9]+|" + VAR + ")";
054:
055: public static List<String> KEYWORDS = Arrays
056: .asList(new String[] { "SELECT", "UPDATE", "FROM", "WHERE",
057: "VALUES", "SET", "INSERT", "INTO", "DELETE",
058: "GROUP BY", "ORDER BY", "DESC" });
059:
060: /**
061: * Private constructor.
062: * Utility classes only have static methods.
063: */
064: private SQLUtil() {
065: }
066:
067: /**
068: * Fetch the last sql keyword from the command string.
069: * @param s the sql command string
070: * @return the last sql keyword
071: */
072: public static String getLastKeyWord(CharSequence s) {
073: String seq = s.toString();
074: String seqUpper = seq.toUpperCase();
075: String lastKeyWord = null;
076: int maxIndex = -1;
077: Iterator<String> iKeywords = KEYWORDS.iterator();
078: while (iKeywords.hasNext()) {
079: String keyword = iKeywords.next();
080: int tmpIndex = seqUpper.indexOf(keyword);
081: int tmpEndIndex = tmpIndex + keyword.length();
082:
083: //Ensuring the keyword is not a in-word match
084: if (tmpIndex > 0
085: && seqUpper.substring(tmpIndex - 1, tmpIndex)
086: .matches(NAMING_CHAR)) {
087: continue;
088: } else if (tmpIndex != -1
089: && tmpEndIndex < seqUpper.length()
090: && seqUpper.substring(tmpEndIndex, tmpEndIndex + 1)
091: .matches(NAMING_CHAR)) {
092: continue;
093: }
094:
095: if (tmpIndex > maxIndex) {
096: maxIndex = tmpIndex;
097: lastKeyWord = seq.substring(maxIndex, maxIndex
098: + keyword.length());
099: }
100: }
101: return lastKeyWord;
102: }
103:
104: /**
105: * Parse the table names from the sql command lines.
106: * @param commandInfo a list of commandlines (parts)
107: * @param point the cursor position
108: */
109: protected static List<String> parseTableNames(
110: List<? extends CharSequence> commandInfo, Point point) {
111: String command = getStartOfCommand(
112: commandInfo,
113: new Point(commandInfo.get(commandInfo.size() - 1)
114: .length(), commandInfo.size() - 1)).toString();
115: String commandUpper = command.toUpperCase();
116: int startIndex;
117: if (commandUpper.indexOf("FROM") > 0) {
118: startIndex = commandUpper.indexOf("FROM") + "FROM".length();
119: } else /*if (commandUpper.indexOf("INTO") > 0)*/{
120: startIndex = commandUpper.indexOf("INTO") + "INTO".length();
121: }
122: List<String> returnValue = new ArrayList<String>();
123: int tmpIndex;
124: if (command.indexOf(' ', startIndex + 1) > command.indexOf(',',
125: startIndex)
126: && command.indexOf(',', startIndex) > 0) {
127: while ((tmpIndex = command.indexOf(',', startIndex)) > 0) {
128: returnValue.add(command.substring(startIndex, tmpIndex)
129: .trim());
130: startIndex = tmpIndex + 1;
131: }
132: }
133: int endIndex = command.indexOf(' ', startIndex + 1);
134: if (endIndex < 0) {
135: endIndex = command.length();
136: }
137: returnValue.add(command.substring(startIndex, endIndex).trim());
138:
139: return returnValue;
140: }
141:
142: public static TabCompletionInfo getTabCompletionInfo(
143: SQLCommand command, Point commandPoint) {
144: return getTabCompletionInfo(command.getLines(), commandPoint);
145:
146: }
147:
148: /**
149: * Return tab completion info for the provided command lines.
150: * @param commandInfo the sql command lines
151: * @param commandPoint the cursor position for tab completion
152: * @return tab completion info for the provided command lines.
153: */
154: public static TabCompletionInfo getTabCompletionInfo(
155: List<? extends CharSequence> commandInfo, Point commandPoint) {
156: if (commandInfo.size() == 1 && commandInfo.get(0).length() == 0) {
157: return new TabCompletionInfo(
158: TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays
159: .asList(new String[] { "SELECT",
160: "INSERT INTO", "DELETE FROM",
161: "UPDATE" }));
162: }
163: String startOfCommand = getStartOfCommand(commandInfo,
164: commandPoint);
165: //System.out.println("Startof command: "+ startOfCommand);
166: String lastKeyword = getLastKeyWord(startOfCommand);
167: //System.out.println("Last keyword: "+ lastKeyword);
168: if (lastKeyword == null) {
169: return new TabCompletionInfo(
170: TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays
171: .asList(new String[] { "SELECT",
172: "INSERT INTO", "DELETE FROM",
173: "UPDATE" }), startOfCommand);
174: }
175: if (lastKeyword.equalsIgnoreCase("UPDATE")) {
176: String end = startOfCommand.substring(startOfCommand
177: .lastIndexOf(' ') + 1);
178: String upCommand = startOfCommand.toUpperCase();
179: if (upCommand.matches("UPDATE[\\s]+" + TABLE
180: + "[\\s]+(|S|SE|SET)")) {
181: return new TabCompletionInfo(
182: TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays
183: .asList(new String[] { "SET" }), end);
184: }
185: if (upCommand.matches("UPDATE[\\s]+" + TABLE
186: + "[\\s]+SET[\\s]+")) {
187: String tableName = upCommand.substring("UPDATE"
188: .length());
189: tableName = tableName.substring(0,
190: tableName.indexOf("SET")).trim();
191: return new TabCompletionInfo(
192: TabCompletionInfo.MatchType.COLUMN_NAMES,
193: Arrays.asList(tableName), end);
194: }
195: return new TabCompletionInfo(
196: TabCompletionInfo.MatchType.TABLE_NAMES,
197: new ArrayList<String>(), end);
198: } else if (lastKeyword.equalsIgnoreCase("SET")) {
199: String end = startOfCommand.substring(startOfCommand
200: .lastIndexOf(' ') + 1);
201: String upCommand = startOfCommand.toUpperCase();
202: if (upCommand.matches("UPDATE[\\s]+" + TABLE
203: + "[\\s]+SET[\\s]+")) {
204: String tableName = upCommand.substring("UPDATE"
205: .length());
206: tableName = tableName.substring(0,
207: tableName.indexOf("SET")).trim();
208: return new TabCompletionInfo(
209: TabCompletionInfo.MatchType.COLUMN_NAMES,
210: Arrays.asList(tableName), end);
211: }
212: } else if (lastKeyword.equalsIgnoreCase("SELECT")) {
213: // if it looks like:
214: // SELECT x
215: // return "FROM"
216: String upCommand = startOfCommand.toUpperCase();
217: if (upCommand.length() > "SELECT".length()) {
218: if (upCommand
219: .substring(
220: upCommand.indexOf("SELECT")
221: + "SELECT".length())
222: .matches(
223: "[\\s]+"
224: + VAR
225: + "(|[\\s]*,[\\s]*"
226: + VAR
227: + "[\\s]*)[\\s]+(|F|FR|FRO|FROM)")) {
228: String end = startOfCommand
229: .substring(startOfCommand.lastIndexOf(' ') + 1);
230: return new TabCompletionInfo(
231: TabCompletionInfo.MatchType.SQL_KEYWORD,
232: Arrays.asList(new String[] { "FROM" }), end);
233: }
234: }
235: // if it looks like:
236: // SELECT
237: // or
238: // SELECT A,
239: // return matching columnnames when table names are provided in from clause
240: // other wise return table names
241: // TODO check for WHERE Clause
242: String sub = startOfCommand.substring(
243: startOfCommand.indexOf(lastKeyword)
244: + lastKeyword.length()).trim();
245: String tableName;
246: if (sub.indexOf(',') > 0) {
247: tableName = sub.substring(sub.lastIndexOf(',') + 1)
248: .trim();
249: } else {
250: tableName = sub;
251: }
252: if (tableName.indexOf('.') > 0) {
253: return new TabCompletionInfo(
254: TabCompletionInfo.MatchType.COLUMN_NAMES,
255: Arrays
256: .asList(new String[] { tableName
257: .substring(0, tableName
258: .indexOf('.')) }),
259: tableName.substring(tableName.indexOf('.') + 1));
260: }
261: return new TabCompletionInfo(
262: TabCompletionInfo.MatchType.TABLE_NAMES, Arrays
263: .asList(new String[0]), tableName);
264: } else if (lastKeyword.equalsIgnoreCase("FROM")) {
265: // find out if it looks like
266: // 'SELECT x FROM '
267: // or
268: // 'SELECT x FROM A,'
269: if (startOfCommand.trim().endsWith(lastKeyword)
270: || startOfCommand.trim().endsWith(",")) {
271: int beginIndex = startOfCommand
272: .lastIndexOf(lastKeyword)
273: + lastKeyword.length();
274: return new TabCompletionInfo(
275: TabCompletionInfo.MatchType.TABLE_NAMES,
276: Arrays.asList(new String[] { startOfCommand
277: .substring(beginIndex).trim() }));
278: }
279: // (if) it looks like:
280: // 'SELECT x FROM A '
281: // return WHERE
282: String tmpCommand = startOfCommand.substring(
283: startOfCommand.indexOf(lastKeyword)
284: + lastKeyword.length() + 1).toUpperCase();
285: String WHERE = "W|WH|WHE|WHER|WHERE";
286: String GROUP_BY = "G|GR|GRO|GROU|GROUP|GROUP |GROUP B|GROUP BY";
287: String ORDER_BY = "O|OR|ORD|ORDE|ORDER|ORDER |ORDER B|ORDER BY";
288: //String regexpSQL = "[\\s]*"+VAR+"+([\\s]*,[\\s]*"+VAR+"+)*[\\s]+(|"+WHERE+"|"+GROUP_BY+"|"+ORDER_BY+")";
289: String regexpSQL = ".*" + VAR + "[\\s]+(|" + WHERE + "|"
290: + GROUP_BY + "|" + ORDER_BY + ")";
291: if (tmpCommand.matches(regexpSQL)) {
292: String end = startOfCommand.substring(startOfCommand
293: .lastIndexOf(' ') + 1);
294: return new TabCompletionInfo(
295: TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays
296: .asList(new String[] { "WHERE",
297: "GROUP BY", "ORDER BY" }), end);
298: }
299: String end;
300: startOfCommand = startOfCommand.substring(startOfCommand
301: .toUpperCase().indexOf("FROM"));
302: if (startOfCommand.indexOf(',') > 0) {
303: end = startOfCommand.substring(
304: startOfCommand.lastIndexOf(',') + 1).trim();
305: } else {
306: end = startOfCommand.substring(
307: startOfCommand.lastIndexOf(' ') + 1).trim();
308: }
309: return new TabCompletionInfo(
310: TabCompletionInfo.MatchType.TABLE_NAMES,
311: new ArrayList<String>(), end);
312: } else if (lastKeyword.equalsIgnoreCase("WHERE")) {
313: // find out if it looks like
314: // 'SELECT x FROM A,B WHERE'
315: // or
316: // 'SELECT x FROM A,B WHERE A.x='x' AND/OR '
317: String tmpCommand = startOfCommand.substring(startOfCommand
318: .indexOf(lastKeyword)
319: + lastKeyword.length() + 1);
320: String upperCommandString = tmpCommand.toUpperCase();
321: String VAR = "(|" + TABLE + "\\.)(|" + COLUMN + ")";
322: String VALUE = "('.*'|[0-9]+|" + VAR + ")";
323: // VALUE COMPERATOR VALUE
324: // VALUE IN (.*)
325:
326: String regExp = "(|" + VALUE + "(|[\\s]*" + COMPARATOR
327: + "[\\s]*(|" + VALUE + "(|([\\s]+(AND|OR)[\\s]+(|"
328: + VALUE + "(|[\\s]*" + COMPARATOR + "[\\s]*(|"
329: + VALUE + "))))*))|[\\s]*IN[\\s]*(.*)[\\s]*))";
330: if (upperCommandString.matches(regExp)) {
331: int lastIndex = tmpCommand.lastIndexOf(' ');
332: lastIndex = Math.max(lastIndex, tmpCommand
333: .lastIndexOf('.'));
334: lastIndex = Math.max(lastIndex, tmpCommand
335: .lastIndexOf('\t'));
336: lastIndex = Math.max(lastIndex, tmpCommand
337: .lastIndexOf('='));
338: lastIndex = Math.max(lastIndex, tmpCommand
339: .lastIndexOf('>'));
340: lastIndex = Math.max(lastIndex, tmpCommand
341: .lastIndexOf('>'));
342: String end = tmpCommand.substring(lastIndex + 1);
343: return new TabCompletionInfo(
344: TabCompletionInfo.MatchType.COLUMN_NAMES,
345: parseTableNames(commandInfo, commandPoint), end);
346: }
347: //else System.out.println("'"+upperCommandString +"'\n not matches\n"+regExp);
348: String end = "";
349: if (upperCommandString.matches(".*[\\s]+")) {
350: end = "";
351: } else {
352: int lastIndex = tmpCommand.lastIndexOf(' ');
353: lastIndex = Math.max(lastIndex, tmpCommand
354: .lastIndexOf('\t'));
355: lastIndex = Math.max(lastIndex, tmpCommand
356: .lastIndexOf('='));
357: lastIndex = Math.max(lastIndex, tmpCommand
358: .lastIndexOf('>'));
359: lastIndex = Math.max(lastIndex, tmpCommand
360: .lastIndexOf('>'));
361: end = tmpCommand.substring(lastIndex + 1);
362: }
363: return new TabCompletionInfo(
364: TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays
365: .asList(new String[] { "AND", "OR", "IN",
366: "GROUP BY", "ORDER BY" }), end);
367: } else if (lastKeyword.equalsIgnoreCase("GROUP BY")
368: || lastKeyword.equalsIgnoreCase("ORDER BY")) {
369: String upperStart = startOfCommand.toUpperCase();
370: String columns = startOfCommand.substring(
371: upperStart.indexOf("SELECT") + "SELECT".length(),
372: upperStart.indexOf("FROM")).replaceAll(" ", "");
373: return new TabCompletionInfo(
374: TabCompletionInfo.MatchType.COLUMN_NAMES, Arrays
375: .asList(columns.split(",")));
376: }
377: return new TabCompletionInfo(
378: TabCompletionInfo.MatchType.UNKNOWN, Arrays
379: .asList(new String[0]));
380: }
381:
382: public static String getStartOfCommand(
383: List<? extends CharSequence> commandInfo, Point commandPoint) {
384: StringBuffer buff = new StringBuffer();
385: for (int i = 0; i <= commandPoint.y; i++) {
386: if (i == commandPoint.y) {
387: buff.append(commandInfo.get(i).subSequence(0,
388: commandPoint.x));
389: } else {
390: buff.append(commandInfo.get(i));
391: }
392: if (i < commandPoint.y && commandPoint.x > 0) {
393: buff.append(' ');
394: }
395: }
396: return buff.toString();
397: }
398:
399: public static int getLastBreakIndex(String s) {
400: int spaceIndex = 0;
401: char[] breakCharacters = new char[] { ' ', '\t', '.', ',' };
402: for (int i = 0; i < breakCharacters.length; i++) {
403: int tmpIndex = s.lastIndexOf(breakCharacters[i]);
404: if (tmpIndex > 0 && tmpIndex > spaceIndex) {
405: spaceIndex = tmpIndex;
406: }
407: }
408: return spaceIndex;
409: }
410: }
|