From 04ab8895cde445cb3895d6875e5040921f2a38ca Mon Sep 17 00:00:00 2001 From: "Tschaen, Brendan" Date: Tue, 30 Apr 2019 16:17:27 -0400 Subject: Fall back sql parsing option If we can't parse the sql query, use basic string analysis to attempt to find the tables Change-Id: Ibb81186e1fb768ee2c7c39b11922c99b79bf942d Issue-ID: MUSIC-391 Signed-off-by: Tschaen, Brendan --- .../java/org/onap/music/mdbc/MdbcConnection.java | 2 +- .../org/onap/music/mdbc/query/QueryProcessor.java | 44 +++++++++++++++-- .../onap/music/mdbc/query/QueryProcessorTest.java | 55 ++++++++++++++++++---- 3 files changed, 87 insertions(+), 14 deletions(-) diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java index 3db6c3f..cb2df7f 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java @@ -505,7 +505,7 @@ public class MdbcConnection implements Connection { public void preStatementHook(final String sql) throws MDBCServiceException, SQLException { //TODO: verify ownership of keys here //Parse tables from the sql query - Map> tableToInstruction = QueryProcessor.parseSqlQuery(sql); + Map> tableToInstruction = QueryProcessor.parseSqlQuery(sql, table_set); //Check ownership of keys List queryTables = MDBCUtils.getTables(tableToInstruction); if (this.partition!=null) { 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 fc41cf6..06ad252 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 @@ -25,7 +25,7 @@ import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; - +import java.util.Set; import org.apache.calcite.avatica.util.Casing; import org.apache.calcite.avatica.util.Quoting; import org.apache.calcite.sql.SqlBasicCall; @@ -73,10 +73,13 @@ public class QueryProcessor { /** * * @param query + * @param tables set of tables found in sql database. This is only used as a cross reference, + * the parser will try to find tables in the query first, regardless of whether they + * are in this set * @return map of table name to {@link org.onap.music.mdbc.query.SQLOperation} * @throws SqlParseException */ - public static Map> parseSqlQuery(String query) throws SQLException { + public static Map> parseSqlQuery(String query, Set tables) throws SQLException { logger.info(EELFLoggerDelegate.applicationLogger, "Parsing query: "+query); query = query.trim(); if (query.endsWith(";")) { @@ -94,7 +97,7 @@ public class QueryProcessor { sqlNode = getSqlParser(query).parseStmt(); } catch (SqlParseException e) { logger.error(EELFLoggerDelegate.errorLogger, "Unable to parse query: " + query +". " + e.getMessage()); - throw new SQLException("Unable to parse query: " + query); + return basicStringParser(query, tables); } SqlBasicVisitor visitor = new SqlBasicVisitor() { @@ -209,5 +212,40 @@ public class QueryProcessor { opList.add(op); tableOpsMap.put(identifier.toString(), opList); } + + /** + * Parse the string using basic string methods if parsing library fails + * @param query + * @return + * @throws SQLException + */ + private static Map> basicStringParser(String query, Set tables) throws SQLException { + if (tables==null) { + throw new SQLException("Unable to parse sql query: No tables to look for."); + } + Map> tableOpsMap = new HashMap<>(); + SQLOperation op; + if (query.toUpperCase().startsWith("INSERT")) { + op = SQLOperation.INSERT; + } else if (query.toUpperCase().startsWith("UPDATE")) { + op = SQLOperation.UPDATE; + } else if (query.toUpperCase().startsWith("DELETE")) { + op = SQLOperation.DELETE; + } else if (query.toUpperCase().startsWith("SELECT")) { + op = SQLOperation.SELECT; + } else { + throw new SQLException("Unable to parse sql query: " + query); + } + for (String table: tables) { + if (query.toLowerCase().contains(table.toLowerCase())) { + List opList = tableOpsMap.get(table); + if (opList == null) opList = new ArrayList<>(); + opList.add(op); + tableOpsMap.put(table.toString(), opList); + } + } + return tableOpsMap; + } + } diff --git a/mdbc-server/src/test/java/org/onap/music/mdbc/query/QueryProcessorTest.java b/mdbc-server/src/test/java/org/onap/music/mdbc/query/QueryProcessorTest.java index e76533e..8d851c7 100644 --- a/mdbc-server/src/test/java/org/onap/music/mdbc/query/QueryProcessorTest.java +++ b/mdbc-server/src/test/java/org/onap/music/mdbc/query/QueryProcessorTest.java @@ -19,7 +19,9 @@ import static org.junit.Assert.*; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; +import java.util.HashSet; import java.util.List; +import java.util.Set; import org.junit.Test; @@ -33,7 +35,7 @@ public class QueryProcessorTest { // no table ops for now // op.add(Operation.TABLE); // expectedOut.put("pet", op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -43,7 +45,7 @@ public class QueryProcessorTest { List t1op = new ArrayList<>(); t1op.add(SQLOperation.SELECT); expectedOut.put("DB.TABLE1", t1op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -53,7 +55,7 @@ public class QueryProcessorTest { List t1op = new ArrayList<>(); t1op.add(SQLOperation.SELECT); expectedOut.put("DB.TABLE1", t1op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -66,10 +68,10 @@ public class QueryProcessorTest { t2op.add(SQLOperation.SELECT); expectedOut.put("TABLE1", t1op); expectedOut.put("TABLE2", t2op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); sqlQuery = "SELECT name, age FROM table1, table2 t2 WHERE id = t2.id"; - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -79,7 +81,7 @@ public class QueryProcessorTest { List t1op = new ArrayList<>(); t1op.add(SQLOperation.INSERT); expectedOut.put("EMPLOYEES", t1op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -89,10 +91,10 @@ public class QueryProcessorTest { List t1op = new ArrayList<>(); t1op.add(SQLOperation.UPDATE); expectedOut.put("DB.EMPLOYEES", t1op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); sqlQuery = "UPDATE db.Employees SET id = 1"; - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -106,7 +108,7 @@ public class QueryProcessorTest { t2op.add(SQLOperation.SELECT); expectedOut.put("TABLE1", t1op); expectedOut.put("TABLE2", t2op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); } @Test @@ -123,6 +125,39 @@ public class QueryProcessorTest { t2op.add(SQLOperation.SELECT); expectedOut.put("ORDERS", t1op); expectedOut.put("DB.CUSTOMERS", t2op); - assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery)); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(sqlQuery, null)); + } + + @Test + public void userDefinedVariables() throws SQLException { + String query = "SELECT @start := 1, @finish := 10;"; + HashMap> expectedOut = new HashMap<>(); + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(query, new HashSet())); + } + + @Test + public void userDefinedVariables1() throws SQLException { + String query = "select @rn /*'*/:=/*'*/ @rn+1 AS rowId, notification_ID, is_for_online_users,is_for_all_roles, msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date, creator_ID,notification_hyperlink, active_YN from ( select notification_ID, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority,created_date, creator_ID,notification_hyperlink,active_YN from ( select user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN from ( select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN, a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.notification_hyperlink,a.created_date,b.role_id,b.recv_user_id from ep_notification a, ep_role_notification b where a.notification_id = b.notification_id and (end_time is null || SYSDATE() <= end_time ) and (start_time is null || SYSDATE() >= start_time) and a.is_for_all_roles = 'N' ) a, ( select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME from fn_user a, fn_user_role b, fn_role c, fn_app d where COALESCE(c.app_id,1) = d.app_id and a.user_id = b.user_id and a.user_id = ? and b.role_id = c.role_id and (d.enabled='Y' or d.app_id=1) )b where ( a.role_id = b.role_id ) union select ?, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN from ( select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN, a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, a.notification_hyperlink,b.role_id,b.recv_user_id from ep_notification a, ep_role_notification b where a.notification_id = b.notification_id and (end_time is null || SYSDATE() <= end_time ) and (start_time is null || SYSDATE() >= start_time) and a.is_for_all_roles = 'N' ) a where ( a.recv_user_id=? ) union ( select ? user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN from ep_notification a where a.notification_id and (end_time is null || SYSDATE() <= end_time ) and (start_time is null || SYSDATE() >= start_time) and a.is_for_all_roles = 'Y' ) ) a where active_YN = 'Y' and not exists ( select ID,User_ID,notification_ID,is_viewed,updated_time from ep_user_notification m where user_id = ? and m.notification_id = a.notification_id and is_viewed = 'Y' ) order by priority desc, created_date desc,start_Time desc ) t, (SELECT @rn /*'*/:=/*'*/ 0) t2 ;"; + HashMap> expectedOut = new HashMap<>(); + Set tables = new HashSet<>(); + tables.add("ep_notification"); + tables.add("ep_role_notification"); + tables.add("fn_user"); + tables.add("fn_user_role"); + tables.add("fn_role"); + tables.add("fn_app"); + tables.add("test_table"); + + //all reads for this query + for (String table: tables) { + if (table.equals("test_table")) { + continue; + } + List tableList = new ArrayList<>(); + tableList.add(SQLOperation.SELECT); + expectedOut.put(table, tableList); + } + + assertEquals(expectedOut, QueryProcessor.parseSqlQuery(query, tables)); } } -- cgit 1.2.3-korg