You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@zeppelin.apache.org by pr...@apache.org on 2017/06/06 14:56:56 UTC
zeppelin git commit: [Bug Fix][ZEPPELIN-2554] sql parser fix
(backslash)
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 <ti...@gmail.com>
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 <ti...@gmail.com>
Authored: Mon Jun 5 17:21:21 2017 +0500
Committer: Prabhjyot Singh <pr...@gmail.com>
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();