Menu
▾
▴
[Sqlshell-svn] /devel/src/main/java/nl/improved/sqlclient/util
|
From: SVN by r. <sv...@ca...> - 2009-02-27 08:10:59
|
Author: roy
Date: 2009-02-27 09:10:51 +0100 (Fri, 27 Feb 2009)
New Revision: 380
Added:
src/main/java/nl/improved/sqlclient/util/SQLParser.java
src/main/java/nl/improved/sqlclient/util/SQLPart.java
Log:
new sql parsing api
Added: src/main/java/nl/improved/sqlclient/util/SQLParser.java
===================================================================
--- src/main/java/nl/improved/sqlclient/util/SQLParser.java 2009-02-20 21:59:19 UTC (rev 379)
+++ src/main/java/nl/improved/sqlclient/util/SQLParser.java 2009-02-27 08:10:51 UTC (rev 380)
@@ -0,0 +1,396 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+
+package nl.improved.sqlclient.util;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import nl.improved.sqlclient.Point;
+import nl.improved.sqlclient.SQLCommand;
+import nl.improved.sqlclient.TabCompletionInfo;
+
+/**
+ *
+ * @author roy
+ */
+public class SQLParser {
+ final static char[] breakCharacters = new char[]{' ', '\t', '.', ',', '(',')'};
+ /**
+ * A regular expression statement for name chars.
+ */
+ private static final String NAMING_CHAR = "(_|-|[0-9]|[A-Z])";
+ /**
+ * A table name regular expression statement.
+ */
+ private static final String TABLE = "[A-Z]+"+NAMING_CHAR+"*";
+ /**
+ * A column name regular expression statement.
+ */
+ private static final String COLUMN = "(\\*|[A-Z]+"+NAMING_CHAR+"*)";
+ /**
+ * A variable (table name + columnname) regular expression statement.
+ */
+ private static final String VAR = "(|"+TABLE+"\\.)"+COLUMN;
+ /**
+ * SQL Comparators.
+ */
+ private static final String COMPARATOR = "(=|<>|<|>|LIKE)";
+ /**
+ * A sql variable description.
+ */
+ private static final String VALUE = "('.*'|[0-9]+|"+VAR+")";
+
+ public static TabCompletionInfo getTabCompletionInfo(SQLCommand command, Point commandPoint) {
+ List<? extends CharSequence> lines = command.getLines();
+ return getTabCompletionInfo(lines, commandPoint);
+ }
+ public static TabCompletionInfo getTabCompletionInfo(List<? extends CharSequence> lines, Point commandPoint) {
+ StringBuffer buf = new StringBuffer();
+ int position = 0;
+ for (int y = 0; y < lines.size(); y++) {
+ CharSequence seq = lines.get(y);
+ if (y < commandPoint.y) {
+ position+=seq.length();
+ }
+ if (y == commandPoint.y) {
+ position+=commandPoint.x;
+ }
+ buf.append(seq);
+ }
+ return getTabCompletionInfo(buf, position);
+ }
+
+ public static TabCompletionInfo getTabCompletionInfo(CharSequence sql, int position) {
+
+ List<SQLPart> parts = parse(sql);
+ System.err.println("PARTS: "+ parts);
+ int charsLeft = position;
+ for (SQLPart part : parts) {
+ if (charsLeft <= part.getPart().length()) {
+ switch (part.getSQLType()) {
+ case SELECT :
+ return getSelectTabCompletionInfo(part, parts, charsLeft);
+ case FROM :
+ return getFromTabCompletionInfo(part, parts, charsLeft);
+ case WHERE :
+ return getWhereTabCompletionInfo(part, parts, charsLeft);
+ case GROUPBY :
+ return getGroupByTabCompletionInfo(part, parts, charsLeft);
+ case ORDERBY :
+ return getOrderByTabCompletionInfo(part, parts, charsLeft);
+ case DELETE :
+ return getDeleteTabCompletionInfo(part, parts, charsLeft);
+ case UPDATE :
+ return getUpdateTabCompletionInfo(part, parts, charsLeft);
+ case SET :
+ return getSetTabCompletionInfo(part, parts, charsLeft);
+ }
+ } else {
+ charsLeft -=part.getPart().length();
+ }
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"SELECT", "INSERT INTO", "UPDATE", "DELETE FROM", "ALTER TABLE"})
+ , sql);
+ }
+
+
+ static List<SQLPart> parse(CharSequence sqlSequence) {
+ List<SQLPart> returnValue = new ArrayList<SQLPart>();
+ if (sqlSequence.length() == 0) {
+ return returnValue;
+ }
+ SQLPart.SQLType sqlType;
+ int start = 0;
+ int end = sqlSequence.length();
+ String sql = sqlSequence.toString().toUpperCase();
+ //System.out.println("SQL: "+ sql);
+ //System.out.println("MATCHES: "+ sql.matches("SELECT.*"));
+ if (sql.matches("[\\s]*SELECT.*")) {
+ sqlType = SQLPart.SQLType.SELECT;
+ if (sql.matches("[\\s]*SELECT.*FROM.*")) {
+ end = sql.indexOf("FROM");
+ } else {
+ end = sqlSequence.length();
+ }
+ } else if (sql.matches("[\\s]*FROM.*")) {
+ sqlType = SQLPart.SQLType.FROM;
+ if (sql.matches("[\\s]*FROM.*WHERE.*")) {
+ end = sql.indexOf("WHERE");
+ }
+ } else if (sql.matches("[\\s]*WHERE.*")) {
+ sqlType = SQLPart.SQLType.WHERE;
+ if (sql.matches("[\\s]*WHERE.*ORDER BY.*")) {
+ end = sql.indexOf(" ORDER BY");
+ } else if (sql.matches("[\\s]*WHERE.*GROUP BY.*")) {
+ end = sql.indexOf("GROUP BY");
+ }
+ } else if (sql.matches("[\\s]*ORDER BY.*")) {
+ sqlType = SQLPart.SQLType.ORDERBY;
+ if (sql.matches("[\\s]*ORDER BY.*GROUP BY.*")) {
+ end = sql.indexOf("GROUP BY");
+ }
+ } else if (sql.matches("[\\s]*GROUP BY.*")) {
+ sqlType = SQLPart.SQLType.GROUPBY;
+ } else if (sql.matches("[\\s]*DELETE.*")) {
+ sqlType = SQLPart.SQLType.DELETE;
+ if (sql.matches("[\\s]*DELETE FROM.*")) {
+ end = sql.indexOf("FROM");
+ } else {
+ end = sqlSequence.length();
+ }
+ } else if (sql.matches("[\\s]*UPDATE.*")) {
+ sqlType = SQLPart.SQLType.UPDATE;
+ if (sql.matches("[\\s]*UPDATE.*SET.*")) {
+ end = sql.indexOf("SET");
+ } else {
+ end = sqlSequence.length();
+ }
+ } else if (sql.matches("[\\s]*SET.*")) {
+ sqlType = SQLPart.SQLType.SET;
+ if (sql.matches("[\\s]*WHERE.*")) {
+ end = sql.indexOf("WHERE");
+ } else {
+ end = sqlSequence.length();
+ }
+ } else {
+ sqlType = SQLPart.SQLType.OTHER;
+ }
+ returnValue.add(new SQLPart(sqlType, sqlSequence.subSequence(start, end)));
+ returnValue.addAll(parse(sqlSequence.subSequence(end, sqlSequence.length())));
+ return returnValue;
+ }
+
+ private static TabCompletionInfo getDeleteTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ throw new UnsupportedOperationException("Not yet implemented");
+ }
+
+ private static TabCompletionInfo getFromTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ String stringPart = part.getPart().toString();
+ String subPart = stringPart.substring(stringPart.toUpperCase().indexOf("FROM") +"FROM".length(), charsLeft);
+ String end;
+ if (subPart.indexOf(',') > 0) {
+ end = subPart.substring(subPart.lastIndexOf(',')+1);
+ } else {
+ end = subPart;
+ }
+ String WHERE = "W|WH|WHE|WHER|WHERE";
+ String GROUP_BY = "G|GR|GRO|GROU|GROUP|GROUP |GROUP B|GROUP BY";
+ String ORDER_BY = "O|OR|ORD|ORDE|ORDER|ORDER |ORDER B|ORDER BY";
+ if (subPart.trim().length() > 0 && (end.endsWith(" ") || subPart.matches(".*"+TABLE+"[\\s]+("+WHERE+"|"+GROUP_BY+"|"+ORDER_BY+")")) &&
+ !subPart.trim().endsWith(",")) {
+ if (subPart.lastIndexOf(" ") >0) {
+ end = end.substring(end.lastIndexOf(' ')+1);
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD, Arrays.asList(new String[]{"WHERE", "GROUP BY", "ORDER BY", ","}), end.trim());
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.TABLE_NAMES, Arrays.asList(new String[]{}), end.trim());
+ }
+
+ private static TabCompletionInfo getGroupByTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ String stringPart = part.getPart().toString();
+ String subPart = stringPart.substring(stringPart.toUpperCase().indexOf("GROUP BY")+"GROUP BY".length(), charsLeft);
+ List<String> possibleMatches = new ArrayList<String>();
+ for (SQLPart pPart : parts) {
+ if (pPart.getSQLType() == SQLPart.SQLType.SELECT) {
+ String pSubPart = pPart.getPart().toString().substring("SELECT".length()).trim();
+ int lastIndex = 0;
+ int newIndex;
+ while ( (newIndex = pSubPart.indexOf(',', lastIndex)) > 0) {
+ String colName = pSubPart.substring(lastIndex, newIndex).trim();
+ if (colName.indexOf(' ') >=0) {
+ colName = colName.substring(colName.lastIndexOf(' '));
+ }
+ if (!possibleMatches.contains(colName)) {
+ possibleMatches.add(colName);
+ }
+ lastIndex = newIndex+1;
+ }
+ String colName = pSubPart.substring(lastIndex).trim();
+ if (colName.indexOf(' ') >=0) { // alias
+ colName = colName.substring(colName.lastIndexOf(' '));
+ }
+ if (!possibleMatches.contains(colName) && colName.length() > 0) {
+ possibleMatches.add(colName);
+ }
+ }
+ }
+ if (subPart.trim().length() == 0 || subPart.trim().endsWith(",")) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.OTHER, possibleMatches);
+ }
+ String end;
+ if (subPart.indexOf(',') > 0) {
+ end = subPart.substring(subPart.lastIndexOf(',')).trim();
+ } else {
+ end = subPart.trim();
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.OTHER, possibleMatches, end);
+ }
+
+ private static TabCompletionInfo getOrderByTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ String stringPart = part.getPart().toString();
+ String subPart = stringPart.substring(stringPart.toUpperCase().indexOf("ORDER BY")+"ORDER BY".length(), charsLeft);
+ List<String> possibleMatches = new ArrayList<String>();
+ for (SQLPart pPart : parts) {
+ }
+ if (subPart.trim().length() == 0 || subPart.trim().endsWith(",")) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.OTHER, possibleMatches);
+ }
+ String end;
+ if (subPart.indexOf(',') > 0) {
+ end = subPart.substring(subPart.lastIndexOf(',')).trim();
+ } else {
+ end = subPart.trim();
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.OTHER, possibleMatches, end);
+ }
+
+ private static TabCompletionInfo getSelectTabCompletionInfo(SQLPart part, List<SQLPart> parts, int position) {
+ // if it looks like:
+ // SELECT x
+ // return "FROM"
+ String startOfCommand = part.getPart().toString();
+ String upCommand = startOfCommand.toUpperCase();
+ if (upCommand.substring(upCommand.indexOf("SELECT") + "SELECT".length())
+ .matches("[\\s]+"+VAR+"(|[\\s]*,[\\s]*"+VAR+"[\\s]*)[\\s]+(|F|FR|FRO|FROM)")) {
+ String end = startOfCommand.substring(startOfCommand.lastIndexOf(' ')+1);
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"FROM"}), end);
+ }
+ // if it looks like:
+ // SELECT
+ // or
+ // SELECT A,
+ // return matching columnnames when table names are provided in from clause
+ // other wise return table names
+ // TODO check for WHERE Clause
+ String lastKeyword = "SELECT";
+ String sub = startOfCommand.substring(upCommand.indexOf(lastKeyword)+lastKeyword.length()).trim();
+ String tableName;
+ if (sub.indexOf(',') > 0) {
+ tableName = sub.substring(sub.lastIndexOf(',')+1).trim();
+ } else {
+ tableName = sub;
+ }
+ if (tableName.indexOf('.') > 0) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.COLUMN_NAMES
+ , Arrays.asList(new String[]{tableName.substring(0,tableName.indexOf('.'))})
+ , tableName.substring(tableName.indexOf('.')+1));
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.TABLE_NAMES
+ , Arrays.asList(new String[0]), tableName);
+ }
+
+ private static TabCompletionInfo getSetTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ String startOfCommand = part.getPart().toString().substring(0, charsLeft);
+ String tmpCommand = startOfCommand.substring(startOfCommand.toUpperCase().indexOf("SET")+"SET".length()+1);
+ if (tmpCommand.indexOf(',') >0) {
+ tmpCommand = tmpCommand.substring(tmpCommand.lastIndexOf(',')+1);
+ }
+ if (tmpCommand.endsWith(" ")) {
+ if (tmpCommand.matches(".*"+COMPARATOR+"[\\s]*")) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.COLUMN_NAMES
+ , getTableNames(parts));
+ }
+ if (tmpCommand.matches(".*"+COMPARATOR+"[\\s]*"+VAR+"[\\s]*")) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{",", "WHERE"}));
+ }
+ if (tmpCommand.trim().length() > 0) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"=", "<>", "LIKE", "<", ">"}));
+ }
+ }
+ if (tmpCommand.trim().length() == 0) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.COLUMN_NAMES
+ , getTableNames(parts));
+ }
+ String end;
+ if (tmpCommand.indexOf(' ') > 0) {
+ end = tmpCommand.substring(tmpCommand.lastIndexOf(' ')+1).trim();
+ } else {
+ end = tmpCommand.trim();
+ }
+ if (tmpCommand.trim().length() > 0) {
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"=", "<>", "LIKE", "<", ">"}), end);
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{",", "WHERE"})); // TODO END
+ }
+
+ private static List<String> getTableNames(List<SQLPart> parts) {
+ SQLPart fromSqlPart = null;
+ for (SQLPart tmpPart : parts) {
+ if (tmpPart.getSQLType() == SQLPart.SQLType.FROM || tmpPart.getSQLType() == SQLPart.SQLType.UPDATE) {
+ fromSqlPart = tmpPart;
+ }
+ }
+ return fromSqlPart.getTableNames();
+ }
+
+ private static TabCompletionInfo getUpdateTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ String startOfCommand = part.getPart().toString().substring(0, charsLeft);
+ String tmpCommand = startOfCommand.substring(startOfCommand.toUpperCase().indexOf("UPDATE")+"UPDATE".length()+1);
+ if (tmpCommand.trim().length() > 0 && tmpCommand.lastIndexOf(' ') > 0) {
+ String end = tmpCommand.substring(tmpCommand.lastIndexOf(' ') +1);
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"SET"}), end);
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.TABLE_NAMES
+ , new ArrayList(), tmpCommand.trim());
+ }
+
+ private static TabCompletionInfo getWhereTabCompletionInfo(SQLPart part, List<SQLPart> parts, int charsLeft) {
+ // find out if it looks like
+ // 'SELECT x FROM A,B WHERE'
+ // or
+ // 'SELECT x FROM A,B WHERE A.x='x' AND/OR '
+ String startOfCommand = part.getPart().toString().substring(0, charsLeft);
+ String tmpCommand = startOfCommand.substring(startOfCommand.toUpperCase().indexOf("WHERE")+"WHERE".length()+1);
+ String upperCommandString = tmpCommand.toUpperCase();
+ //String VAR = "(|"+TABLE+"\\.)(|"+COLUMN+")";
+ //String VALUE = "('.*'|[0-9]+|"+VAR+")";
+ // VALUE COMPERATOR VALUE
+ // VALUE IN (.*)
+ String regExp = "(|"+VALUE+"(|[\\s]*"+COMPARATOR+"[\\s]*(|"+VALUE+"(|([\\s]+(AND|OR)[\\s]+(|"+VALUE+"(|[\\s]*"+COMPARATOR+"[\\s]*(|"+VALUE+"))))*))|[\\s]*IN[\\s]*(.*)[\\s]*))";
+ if (upperCommandString.matches(regExp) || upperCommandString.endsWith(".")) {
+ System.err.println("****** MATCHES: "+ upperCommandString);
+ int lastIndex = tmpCommand.lastIndexOf(' ');
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('\t'));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('='));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('>'));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('>'));
+ int lastBreakIndex = lastIndex;
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('.'));
+ String end = tmpCommand.substring(lastIndex+1);
+ if (lastBreakIndex < 0) {
+ lastBreakIndex = 0;
+ }
+ List tableNames = (lastIndex > lastBreakIndex) && lastBreakIndex >=0 ?
+ Arrays.asList(new String[]{tmpCommand.substring(lastBreakIndex,lastIndex).trim()}) :
+ getTableNames(parts);
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.COLUMN_NAMES
+ , tableNames, end);
+ }
+ System.err.println("****** NOT MATCHES: "+ upperCommandString+"\n"+ regExp);
+ //else System.out.println("'"+upperCommandString +"'\n not matches\n"+regExp);
+ String end;
+ if (upperCommandString.matches(".*[\\s]+")) {
+ end = "";
+ } else {
+ int lastIndex = tmpCommand.lastIndexOf(' ');
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('\t'));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('='));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('>'));
+ lastIndex = Math.max(lastIndex, tmpCommand.lastIndexOf('>'));
+ end = tmpCommand.substring(lastIndex+1);
+ }
+ return new TabCompletionInfo(TabCompletionInfo.MatchType.SQL_KEYWORD
+ , Arrays.asList(new String[]{"AND", "OR", "IN", "GROUP BY", "ORDER BY"}), end);
+ }
+
+}
Added: src/main/java/nl/improved/sqlclient/util/SQLPart.java
===================================================================
--- src/main/java/nl/improved/sqlclient/util/SQLPart.java 2009-02-20 21:59:19 UTC (rev 379)
+++ src/main/java/nl/improved/sqlclient/util/SQLPart.java 2009-02-27 08:10:51 UTC (rev 380)
@@ -0,0 +1,84 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+
+package nl.improved.sqlclient.util;
+
+import java.util.ArrayList;
+import java.util.List;
+import nl.improved.sqlclient.TabCompletionInfo;
+
+/**
+ *
+ * @author roy
+ */
+public class SQLPart {
+
+ enum SQLType {SELECT, INSERT, UPDATE, DELETE, WHERE, VALUES, FROM, GROUPBY, ORDERBY, OTHER, SET}
+
+ private SQLType sqlType;
+ private CharSequence part;
+
+ public SQLPart(SQLType sqlType, CharSequence part) {
+ this.sqlType = sqlType;
+ this.part = part;
+ }
+
+ public SQLType getSQLType() {
+ return sqlType;
+ }
+
+ public CharSequence getPart() {
+ return part;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ if (obj == null || !(obj instanceof SQLPart)) {
+ return false;
+ }
+ SQLPart other = (SQLPart) obj;
+ return other.part.equals(part) && other.sqlType == sqlType;
+ }
+
+ @Override
+ public String toString() {
+ return sqlType+": "+ part.toString();
+ }
+
+ public List<String> getTableNames() {
+ if (sqlType == SQLType.FROM || sqlType == SQLType.UPDATE) {
+ List<String> returnValue = new ArrayList<String>();
+ String stringPart = getPart().toString();
+ String sub;
+ if (sqlType == SQLType.FROM) {
+ sub = stringPart.substring(stringPart.toUpperCase().indexOf("FROM")+"FROM".length());
+ } else {
+ sub = stringPart.substring(stringPart.toUpperCase().indexOf("UPDATE")+"UPDATE".length());
+ }
+ int lastIndex = 0;
+ int newIndex;
+ while ( (newIndex = sub.indexOf(',', lastIndex)) > 0) {
+ String tableName = sub.substring(lastIndex, newIndex).trim();
+ if (tableName.indexOf(" ") > 0) {
+ tableName = tableName.substring(0, tableName.indexOf(" ")).trim();
+ }
+ System.err.println("********ADDTN: "+ tableName);
+ returnValue.add(tableName);
+ lastIndex = newIndex+1;
+ }
+ String tmp = sub.substring(lastIndex).trim();
+ if (tmp.length() > 0) {
+ if (tmp.indexOf(" ") > 0) {
+ tmp = tmp.substring(0, tmp.indexOf(" ")).trim();
+ }
+ System.err.println("********ADD: "+ tmp);
+ returnValue.add(tmp);
+ }
+ return returnValue;
+ }
+ throw new IllegalStateException("Table names are only available for FROM,UPDATE ("+ sqlType+")");
+ }
+
+}
\ No newline at end of file
|
