From 019a55afae68f555348d60cc197b0d188eaaf4af Mon Sep 17 00:00:00 2001 From: "Tschaen, Brendan" Date: Tue, 5 Feb 2019 15:12:48 -0500 Subject: Update sql parse interface Created junit tests Fix files code formatting Issue-ID: MUSIC-311 Change-Id: I9e11a17482a2567055fdee0da8e206ac92235d5f Signed-off-by: Tschaen, Brendan --- .../org/onap/music/mdbc/query/QueryProcessor.java | 377 ++++++++++----------- 1 file changed, 181 insertions(+), 196 deletions(-) (limited to 'mdbc-server/src/main/java') diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/query/QueryProcessor.java b/mdbc-server/src/main/java/org/onap/music/mdbc/query/QueryProcessor.java index 8e09065..4134540 100644 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/query/QueryProcessor.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/query/QueryProcessor.java @@ -29,6 +29,7 @@ import org.apache.calcite.avatica.util.Casing; import org.apache.calcite.avatica.util.Quoting; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlInsert; +import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlSelect; @@ -55,209 +56,193 @@ import net.sf.jsqlparser.util.TablesNamesFinder; public class QueryProcessor { - private static EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(QueryProcessor.class); + private static EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(QueryProcessor.class); - public List tables = null; + public List tables = null; - public QueryProcessor() { + public QueryProcessor() { - } + } - protected static SqlParserImplFactory parserImplFactory() { - return SqlParserImpl.FACTORY; - } + protected static SqlParserImplFactory parserImplFactory() { + return SqlParserImpl.FACTORY; + } - protected static SqlParser getSqlParser(String sql) { - Quoting quoting = Quoting.DOUBLE_QUOTE; - Casing unquotedCasing = Casing.TO_UPPER; - Casing quotedCasing = Casing.UNCHANGED; - SqlConformance conformance = SqlConformanceEnum.DEFAULT; - - return SqlParser.create(sql, SqlParser.configBuilder().setParserFactory(parserImplFactory()).setQuoting(quoting) - .setUnquotedCasing(unquotedCasing).setQuotedCasing(quotedCasing).setConformance(conformance).build()); - } + protected static SqlParser getSqlParser(String sql) { + Quoting quoting = Quoting.DOUBLE_QUOTE; + Casing unquotedCasing = Casing.TO_UPPER; + Casing quotedCasing = Casing.UNCHANGED; + SqlConformance conformance = SqlConformanceEnum.DEFAULT; - public static Map> parseSqlQuery(String query) throws SqlParseException { - logger.info(EELFLoggerDelegate.applicationLogger, "Parsing query: "+query); - Map> tableOpsMap = new HashMap<>(); - //for Create no need to check locks. - if(query.toUpperCase().startsWith("CREATE")) { - logger.error(EELFLoggerDelegate.errorLogger, "CREATE TABLE DDL not currently supported currently."); - return tableOpsMap; - } - - /*SqlParser parser = SqlParser.create(query); - SqlNode sqlNode = parser.parseQuery();*/ - SqlNode sqlNode = getSqlParser(query).parseStmt(); - - SqlBasicVisitor visitor = new SqlBasicVisitor() { - - public Void visit(SqlCall call) { - if (call.getOperator() instanceof SqlInOperator) { - throw new Util.FoundOne(call); - } - return super.visit(call); - } - - }; - - // sqlNode.accept(new SqlAnalyzer()); - sqlNode.accept(visitor); - - if (sqlNode instanceof SqlInsert) { - SqlInsert sqlInsert = (SqlInsert) sqlNode; - SqlNodeList targetColumnList = sqlInsert.getTargetColumnList(); - System.out.println("targetColumnList: "+targetColumnList); - String tableName = sqlInsert.getTargetTable().toString(); - List Ops = tableOpsMap.get(tableName); - if (Ops == null) - Ops = new ArrayList<>(); - Ops.add(Operation.INSERT.getOperation()); - tableOpsMap.put(tableName, Ops); - } else if (sqlNode instanceof SqlUpdate) { - SqlUpdate sqlUpdate = (SqlUpdate) sqlNode; - String tableName = sqlUpdate.getTargetTable().toString(); - List Ops = tableOpsMap.get(tableName); - if (Ops == null) - Ops = new ArrayList<>(); - Ops.add(Operation.UPDATE.getOperation()); - tableOpsMap.put(tableName, Ops); - } else if (sqlNode instanceof SqlSelect) { - SqlSelect sqlSelect = (SqlSelect) sqlNode; - SqlNodeList selectList = sqlSelect.getSelectList(); - String tables = sqlSelect.getFrom().toString(); - String[] tablesArr = tables.split(","); - - SqlNode where = sqlSelect.getWhere(); - - for (String table : tablesArr) { - - String tableName = null; - if(table.contains("`")) { - String[] split = table.split("`"); - tableName = split[1]; - } else - tableName = table; - - List Ops = tableOpsMap.get(tableName); - if (Ops == null) Ops = new ArrayList<>(); - if (where == null) { - Ops.add(Operation.TABLE.getOperation()); - tableOpsMap.put(tableName, Ops); - } else { - Ops.add(Operation.SELECT.getOperation()); - tableOpsMap.put(tableName, Ops); - } - } - } - - return tableOpsMap; - } + return SqlParser.create(sql, SqlParser.configBuilder().setParserFactory(parserImplFactory()).setQuoting(quoting) + .setUnquotedCasing(unquotedCasing).setQuotedCasing(quotedCasing).setConformance(conformance).build()); + } - @Deprecated - public static Map> extractTableFromQuery(String sqlQuery) { - List tables = null; - Map> tableOpsMap = new HashMap<>(); - try { - net.sf.jsqlparser.statement.Statement stmt = CCJSqlParserUtil.parse(sqlQuery); - if (stmt instanceof Insert) { - Insert s = (Insert) stmt; - String tbl = s.getTable().getName(); - List Ops = tableOpsMap.get(tbl); - if (Ops == null) - Ops = new ArrayList<>(); - Ops.add(Operation.INSERT.getOperation()); - tableOpsMap.put(tbl, Ops); - logger.debug(EELFLoggerDelegate.applicationLogger, "Inserting into table: " + tbl); - } else { - String tbl; - String where = ""; - if (stmt instanceof Update) { - Update u = (Update) stmt; - tbl = u.getTables().get(0).getName(); - List Ops = tableOpsMap.get(tbl); - if (Ops == null) - Ops = new ArrayList<>(); - if (u.getWhere() != null) { - where = u.getWhere().toString(); - logger.debug(EELFLoggerDelegate.applicationLogger, "Updating table: " + tbl); - Ops.add(Operation.UPDATE.getOperation()); - } else { - Ops.add(Operation.TABLE.getOperation()); - } - tableOpsMap.put(tbl, Ops); - } else if (stmt instanceof Delete) { - Delete d = (Delete) stmt; - tbl = d.getTable().getName(); - List Ops = tableOpsMap.get(tbl); - if (Ops == null) - Ops = new ArrayList<>(); - if (d.getWhere() != null) { - where = d.getWhere().toString(); - Ops.add(Operation.DELETE.getOperation()); - } else { - Ops.add(Operation.TABLE.getOperation()); - } - tableOpsMap.put(tbl, Ops); - logger.debug(EELFLoggerDelegate.applicationLogger, "Deleting from table: " + tbl); - } else if (stmt instanceof Select) { - TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); - tables = tablesNamesFinder.getTableList(stmt); - for (String table : tables) { - List Ops = tableOpsMap.get(table); - if (Ops == null) - Ops = new ArrayList<>(); - Ops.add(Operation.SELECT.getOperation()); - tableOpsMap.put(table, Ops); - } - } else if (stmt instanceof CreateTable) { - CreateTable ct = (CreateTable) stmt; - List Ops = new ArrayList<>(); - Ops.add(Operation.TABLE.getOperation()); - tableOpsMap.put(ct.getTable().getName(), Ops); - } else { - logger.error(EELFLoggerDelegate.errorLogger, "Not recognized sql type:" + stmt.getClass()); - tbl = ""; - } - } - } catch (JSQLParserException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - return tableOpsMap; - } + /** + * + * @param query + * @return map of table name to {@link org.onap.music.mdbc.query.Operation} + * @throws SqlParseException + */ + public static Map> parseSqlQuery(String query) throws SqlParseException { + logger.info(EELFLoggerDelegate.applicationLogger, "Parsing query: "+query); + Map> tableOpsMap = new HashMap<>(); + //for Create no need to check locks. + if(query.toUpperCase().startsWith("CREATE")) { + logger.error(EELFLoggerDelegate.errorLogger, "CREATE TABLE DDL not currently supported currently."); + return tableOpsMap; + } - public static void main(String[] args) throws SqlParseException { - - String sqlQuery = "CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20))"; - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "SELECT name, age FROM table1 t1, table2 t2 WHERE t1.id = t2.id"; - //Map> tableOpsMap = extractTableFromQuery(sqlQuery); - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "SELECT name, age FROM table1, table2 t2 WHERE id = t2.id"; - // Map> tableOpsMap = extractTableFromQuery(sqlQuery); - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "SELECT name, age FROM table1 t1"; - // Map> tableOpsMap = extractTableFromQuery(sqlQuery); - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "INSERT INTO Employees (id, name) values ('1','Vikram')"; - // tableOpsMap = extractTableFromQuery(sqlQuery); - // System.out.println(tableOpsMap); - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "UPDATE Employees SET id = 1 WHERE id = 2"; - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "UPDATE Employees SET id = 1"; - System.out.println(parseSqlQuery(sqlQuery)); - - sqlQuery = "UPDATE table1 SET id = 1"; - System.out.println(parseSqlQuery(sqlQuery)); - - } + /*SqlParser parser = SqlParser.create(query); + SqlNode sqlNode = parser.parseQuery();*/ + SqlNode sqlNode = getSqlParser(query).parseStmt(); + + SqlBasicVisitor visitor = new SqlBasicVisitor() { + + public Void visit(SqlCall call) { + if (call.getOperator() instanceof SqlInOperator) { + throw new Util.FoundOne(call); + } + return super.visit(call); + } + + }; + + // sqlNode.accept(new SqlAnalyzer()); + sqlNode.accept(visitor); + switch (sqlNode.getKind()) { + case INSERT: + parseInsert((SqlInsert) sqlNode, tableOpsMap); + break; + case UPDATE: + parseUpdate((SqlUpdate) sqlNode, tableOpsMap); + break; + case SELECT: + parseSelect((SqlSelect) sqlNode, tableOpsMap); + break; + default: + logger.error("Unhandled sql query type " + sqlNode.getKind() +" for query " + query); + } + return tableOpsMap; + } + + private static void parseInsert(SqlInsert sqlNode, Map> tableOpsMap) { + SqlInsert sqlInsert = (SqlInsert) sqlNode; + String tableName = sqlInsert.getTargetTable().toString(); + //handle insert into select query + if (sqlInsert.getSource().getKind()==SqlKind.SELECT) { + parseSelect((SqlSelect) sqlInsert.getSource(), tableOpsMap); + } + List Ops = tableOpsMap.get(tableName); + if (Ops == null) + Ops = new ArrayList<>(); + Ops.add(Operation.INSERT); + tableOpsMap.put(tableName, Ops); + } + + private static void parseUpdate(SqlUpdate sqlNode, Map> tableOpsMap) { + SqlUpdate sqlUpdate = (SqlUpdate) sqlNode; + String tableName = sqlUpdate.getTargetTable().toString(); + List Ops = tableOpsMap.get(tableName); + if (Ops == null) + Ops = new ArrayList<>(); + Ops.add(Operation.UPDATE); + tableOpsMap.put(tableName, Ops); + } + + private static void parseSelect(SqlSelect sqlNode, Map> tableOpsMap ) { + SqlSelect sqlSelect = (SqlSelect) sqlNode; + SqlNodeList selectList = sqlSelect.getSelectList(); + String tables = sqlSelect.getFrom().toString(); + String[] tablesArr = tables.split(","); + + for (String table : tablesArr) { + + String tableName = null; + if(table.contains("`")) { + String[] split = table.split("`"); + tableName = split[1]; + } else { + tableName = table; + } + List Ops = tableOpsMap.get(tableName); + if (Ops == null) Ops = new ArrayList<>(); + Ops.add(Operation.SELECT); + tableOpsMap.put(tableName, Ops); + } + } + + @Deprecated + public static Map> extractTableFromQuery(String sqlQuery) { + List tables = null; + Map> tableOpsMap = new HashMap<>(); + try { + net.sf.jsqlparser.statement.Statement stmt = CCJSqlParserUtil.parse(sqlQuery); + if (stmt instanceof Insert) { + Insert s = (Insert) stmt; + String tbl = s.getTable().getName(); + List Ops = tableOpsMap.get(tbl); + if (Ops == null) + Ops = new ArrayList<>(); + Ops.add(Operation.INSERT.getOperation()); + tableOpsMap.put(tbl, Ops); + logger.debug(EELFLoggerDelegate.applicationLogger, "Inserting into table: " + tbl); + } else { + String tbl; + String where = ""; + if (stmt instanceof Update) { + Update u = (Update) stmt; + tbl = u.getTables().get(0).getName(); + List Ops = tableOpsMap.get(tbl); + if (Ops == null) + Ops = new ArrayList<>(); + if (u.getWhere() != null) { + where = u.getWhere().toString(); + logger.debug(EELFLoggerDelegate.applicationLogger, "Updating table: " + tbl); + Ops.add(Operation.UPDATE.getOperation()); + } else { + Ops.add(Operation.TABLE.getOperation()); + } + tableOpsMap.put(tbl, Ops); + } else if (stmt instanceof Delete) { + Delete d = (Delete) stmt; + tbl = d.getTable().getName(); + List Ops = tableOpsMap.get(tbl); + if (Ops == null) + Ops = new ArrayList<>(); + if (d.getWhere() != null) { + where = d.getWhere().toString(); + Ops.add(Operation.DELETE.getOperation()); + } else { + Ops.add(Operation.TABLE.getOperation()); + } + tableOpsMap.put(tbl, Ops); + logger.debug(EELFLoggerDelegate.applicationLogger, "Deleting from table: " + tbl); + } else if (stmt instanceof Select) { + TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); + tables = tablesNamesFinder.getTableList(stmt); + for (String table : tables) { + List Ops = tableOpsMap.get(table); + if (Ops == null) + Ops = new ArrayList<>(); + Ops.add(Operation.SELECT.getOperation()); + tableOpsMap.put(table, Ops); + } + } else if (stmt instanceof CreateTable) { + CreateTable ct = (CreateTable) stmt; + List Ops = new ArrayList<>(); + Ops.add(Operation.TABLE.getOperation()); + tableOpsMap.put(ct.getTable().getName(), Ops); + } else { + logger.error(EELFLoggerDelegate.errorLogger, "Not recognized sql type:" + stmt.getClass()); + tbl = ""; + } + } + } catch (JSQLParserException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return tableOpsMap; + } } -- cgit 1.2.3-korg