Repository: zeppelin Updated Branches: refs/heads/master 524d5f2a3 -> c73899420
[Bug Fix][ZEPPELIN-2554] sql parser fix (backslash) ### What is this PR for? Fix of parser to correctly parse backslash in quotes. ### What type of PR is it? Bug Fix ### What is the Jira issue? https://issues.apache.org/jira/browse/ZEPPELIN-2554 ### How should this be tested? Execute `select '\n', ';'` ### Screenshots (if appropriate) before  text is parsed as 2 queries `select '\n', '` and `'` after  ### Questions: * Does the licenses files need update? no * Is there breaking changes for older versions? no * Does this needs documentation? no Author: Tinkoff DWH <[email protected]> Closes #2347 from tinkoff-dwh/ZEPPELIN-2554 and squashes the following commits: 778319a5c [Tinkoff DWH] Merge remote-tracking branch 'upstream/master' into ZEPPELIN-2554 b28ebbef6 [Tinkoff DWH] [ZEPPELIN-2554] added parameter key.splitQueries e8be7b36d [Tinkoff DWH] [ZEPPELIN-2554] fix parsing backslash and single quote, tests af508f1ca [Tinkoff DWH] [ZEPPELIN-2554] fix tests 3951aa705 [Tinkoff DWH] [ZEPPELIN-2554] sql parser fix (backslash) Project: http://git-wip-us.apache.org/repos/asf/zeppelin/repo Commit: http://git-wip-us.apache.org/repos/asf/zeppelin/commit/c7389942 Tree: http://git-wip-us.apache.org/repos/asf/zeppelin/tree/c7389942 Diff: http://git-wip-us.apache.org/repos/asf/zeppelin/diff/c7389942 Branch: refs/heads/master Commit: c73899420c93454ff689aa3c5c8e4c80df1c10ce Parents: 524d5f2 Author: Tinkoff DWH <[email protected]> Authored: Mon Jun 5 17:21:21 2017 +0500 Committer: Prabhjyot Singh <[email protected]> Committed: Tue Jun 6 20:26:48 2017 +0530 ---------------------------------------------------------------------- docs/interpreter/jdbc.md | 5 ++ .../apache/zeppelin/jdbc/JDBCInterpreter.java | 37 +++++---- .../src/main/resources/interpreter-setting.json | 6 ++ .../zeppelin/jdbc/JDBCInterpreterTest.java | 79 ++++++++++++++++++-- 4 files changed, 105 insertions(+), 22 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/zeppelin/blob/c7389942/docs/interpreter/jdbc.md ---------------------------------------------------------------------- diff --git a/docs/interpreter/jdbc.md b/docs/interpreter/jdbc.md index 9a4ffc8..0fe9d68 100644 --- a/docs/interpreter/jdbc.md +++ b/docs/interpreter/jdbc.md @@ -133,6 +133,11 @@ The JDBC interpreter properties are defined by default like below. <td>120</td> <td>Time to live sql completer in seconds (-1 to update everytime, 0 to disable update)</td> </tr> + <tr> + <td>default.splitQueries</td> + <td>false</td> + <td>Each query is executed apart and returns the result</td> + </tr> </table> If you want to connect other databases such as `Mysql`, `Redshift` and `Hive`, you need to edit the property values. http://git-wip-us.apache.org/repos/asf/zeppelin/blob/c7389942/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java index c5c00e8..72d7981 100644 --- a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java +++ b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java @@ -23,6 +23,7 @@ import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.Arrays; import java.util.HashMap; import java.util.HashSet; import java.util.List; @@ -106,6 +107,7 @@ public class JDBCInterpreter extends Interpreter { static final String COMPLETER_SCHEMA_FILTERS_KEY = "completer.schemaFilters"; static final String COMPLETER_TTL_KEY = "completer.ttlInSeconds"; static final String DEFAULT_COMPLETER_TTL = "120"; + static final String SPLIT_QURIES_KEY = "splitQueries"; static final String JDBC_JCEKS_FILE = "jceks.file"; static final String JDBC_JCEKS_CREDENTIAL_KEY = "jceks.credentialKey"; static final String PRECODE_KEY_TEMPLATE = "%s.precode"; @@ -538,7 +540,6 @@ public class JDBCInterpreter extends Interpreter { StringBuilder query = new StringBuilder(); char character; - Boolean antiSlash = false; Boolean multiLineComment = false; Boolean singleLineComment = false; Boolean quoteString = false; @@ -561,14 +562,8 @@ public class JDBCInterpreter extends Interpreter { continue; } - if (character == '\\') { - antiSlash = true; - } - if (character == '\'') { - if (antiSlash) { - antiSlash = false; - } else if (quoteString) { + if (quoteString) { quoteString = false; } else if (!doubleQuoteString) { quoteString = true; @@ -576,9 +571,7 @@ public class JDBCInterpreter extends Interpreter { } if (character == '"') { - if (antiSlash) { - antiSlash = false; - } else if (doubleQuoteString) { + if (doubleQuoteString && item > 0) { doubleQuoteString = false; } else if (!quoteString) { doubleQuoteString = true; @@ -598,7 +591,7 @@ public class JDBCInterpreter extends Interpreter { } } - if (character == ';' && !antiSlash && !quoteString && !doubleQuoteString) { + if (character == ';' && !quoteString && !doubleQuoteString) { queries.add(StringUtils.trim(query.toString())); query = new StringBuilder(); } else if (item == sql.length() - 1) { @@ -635,17 +628,29 @@ public class JDBCInterpreter extends Interpreter { String paragraphId = interpreterContext.getParagraphId(); String user = interpreterContext.getAuthenticationInfo().getUser(); - InterpreterResult interpreterResult = new InterpreterResult(InterpreterResult.Code.SUCCESS); + boolean splitQuery = false; + String splitQueryProperty = getProperty(String.format("%s.%s", propertyKey, SPLIT_QURIES_KEY)); + if (StringUtils.isNotBlank(splitQueryProperty) && splitQueryProperty.equalsIgnoreCase("true")) { + splitQuery = true; + } + InterpreterResult interpreterResult = new InterpreterResult(InterpreterResult.Code.SUCCESS); try { connection = getConnection(propertyKey, interpreterContext); if (connection == null) { return new InterpreterResult(Code.ERROR, "Prefix not found."); } - ArrayList<String> multipleSqlArray = splitSqlQueries(sql); - for (int i = 0; i < multipleSqlArray.size(); i++) { - String sqlToExecute = multipleSqlArray.get(i); + + List<String> sqlArray; + if (splitQuery) { + sqlArray = splitSqlQueries(sql); + } else { + sqlArray = Arrays.asList(sql); + } + + for (int i = 0; i < sqlArray.size(); i++) { + String sqlToExecute = sqlArray.get(i); statement = connection.createStatement(); if (statement == null) { return new InterpreterResult(Code.ERROR, "Prefix not found."); http://git-wip-us.apache.org/repos/asf/zeppelin/blob/c7389942/jdbc/src/main/resources/interpreter-setting.json ---------------------------------------------------------------------- diff --git a/jdbc/src/main/resources/interpreter-setting.json b/jdbc/src/main/resources/interpreter-setting.json index 2bc9b89..21ff685 100644 --- a/jdbc/src/main/resources/interpreter-setting.json +++ b/jdbc/src/main/resources/interpreter-setting.json @@ -46,6 +46,12 @@ "defaultValue": "", "description": "SQL which executes while opening connection" }, + "default.splitQueries": { + "envName": null, + "propertyName": "default.splitQueries", + "defaultValue": "false", + "description": "Each query is executed apart and returns the result" + }, "common.max_count": { "envName": null, "propertyName": "common.max_count", http://git-wip-us.apache.org/repos/asf/zeppelin/blob/c7389942/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java index e310837..e6f9598 100644 --- a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java +++ b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java @@ -27,8 +27,11 @@ import static org.junit.Assert.*; import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; -import java.sql.*; -import java.util.ArrayList; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; import java.util.List; import java.util.Properties; @@ -175,21 +178,61 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { String sqlQuery = "insert into test_table(id, name) values ('a', ';\"');" + "select * from test_table;" + "select * from test_table WHERE ID = \";'\";" + - "select * from test_table WHERE ID = ';'"; + "select * from test_table WHERE ID = ';';" + + "select '\n', ';';" + + "select replace('A\\;B', '\\', 'text');" + + "select '\\', ';';" + + "select '''', ';'"; Properties properties = new Properties(); JDBCInterpreter t = new JDBCInterpreter(properties); t.open(); - ArrayList<String> multipleSqlArray = t.splitSqlQueries(sqlQuery); - assertEquals(4, multipleSqlArray.size()); + List<String> multipleSqlArray = t.splitSqlQueries(sqlQuery); + assertEquals(8, multipleSqlArray.size()); assertEquals("insert into test_table(id, name) values ('a', ';\"')", multipleSqlArray.get(0)); assertEquals("select * from test_table", multipleSqlArray.get(1)); assertEquals("select * from test_table WHERE ID = \";'\"", multipleSqlArray.get(2)); assertEquals("select * from test_table WHERE ID = ';'", multipleSqlArray.get(3)); + assertEquals("select '\n', ';'", multipleSqlArray.get(4)); + assertEquals("select replace('A\\;B', '\\', 'text')", multipleSqlArray.get(5)); + assertEquals("select '\\', ';'", multipleSqlArray.get(6)); + assertEquals("select '''', ';'", multipleSqlArray.get(7)); } @Test - public void testSelectMultipleQuries() throws SQLException, IOException { + public void testQueryWithEsÑapedCharacters() throws SQLException, IOException { + String sqlQuery = "select '\\n', ';';" + + "select replace('A\\;B', '\\', 'text');" + + "select '\\', ';';" + + "select '''', ';'"; + + Properties properties = new Properties(); + properties.setProperty("common.max_count", "1000"); + properties.setProperty("common.max_retry", "3"); + properties.setProperty("default.driver", "org.h2.Driver"); + properties.setProperty("default.url", getJdbcConnection()); + properties.setProperty("default.user", ""); + properties.setProperty("default.password", ""); + properties.setProperty("default.splitQueries", "true"); + JDBCInterpreter t = new JDBCInterpreter(properties); + t.open(); + + InterpreterResult interpreterResult = t.interpret(sqlQuery, interpreterContext); + + assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType()); + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(1).getType()); + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(2).getType()); + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(3).getType()); + assertEquals("'\\n'\t';'\n\\n\t;\n", interpreterResult.message().get(0).getData()); + assertEquals("'Atext;B'\nAtext;B\n", interpreterResult.message().get(1).getData()); + assertEquals("'\\'\t';'\n\\\t;\n", interpreterResult.message().get(2).getData()); + assertEquals("''''\t';'\n'\t;\n", interpreterResult.message().get(3).getData()); + + } + + @Test + public void testSelectMultipleQueries() throws SQLException, IOException { Properties properties = new Properties(); properties.setProperty("common.max_count", "1000"); properties.setProperty("common.max_retry", "3"); @@ -197,6 +240,7 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); + properties.setProperty("default.splitQueries", "true"); JDBCInterpreter t = new JDBCInterpreter(properties); t.open(); @@ -214,6 +258,28 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { } @Test + public void testDefaultSplitQuries() throws SQLException, IOException { + Properties properties = new Properties(); + properties.setProperty("common.max_count", "1000"); + properties.setProperty("common.max_retry", "3"); + properties.setProperty("default.driver", "org.h2.Driver"); + properties.setProperty("default.url", getJdbcConnection()); + properties.setProperty("default.user", ""); + properties.setProperty("default.password", ""); + JDBCInterpreter t = new JDBCInterpreter(properties); + t.open(); + + String sqlQuery = "select * from test_table;" + + "select * from test_table WHERE ID = ';';"; + InterpreterResult interpreterResult = t.interpret(sqlQuery, interpreterContext); + assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); + assertEquals(1, interpreterResult.message().size()); + + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType()); + assertEquals("ID\tNAME\na\ta_name\nb\tb_name\nc\tnull\n", interpreterResult.message().get(0).getData()); + } + + @Test public void testSelectQueryWithNull() throws SQLException, IOException { Properties properties = new Properties(); properties.setProperty("common.max_count", "1000"); @@ -465,6 +531,7 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); + properties.setProperty("default.splitQueries", "true"); JDBCInterpreter t = new JDBCInterpreter(properties); t.open();
