JDBCInterpreter extends KerberosInterpreter { @Override public void open() { super.open(); for (String propertyKey : properties.stringPropertyNames()) { LOGGER.debug("propertyKey: {}", propertyKey); String[] keyValue = propertyKey.split("\\.", 2); if (2 == keyValue.length) { LOGGER.debug("key: {}, value: {}", keyValue[0], keyValue[1]); Properties prefixProperties; if (basePropertiesMap.containsKey(keyValue[0])) { prefixProperties = basePropertiesMap.get(keyValue[0]); } else { prefixProperties = new Properties(); basePropertiesMap.put(keyValue[0].trim(), prefixProperties); } prefixProperties.put(keyValue[1].trim(), getProperty(propertyKey)); } } Set<String> removeKeySet = new HashSet<>(); for (String key : basePropertiesMap.keySet()) { if (!COMMON_KEY.equals(key)) { Properties properties = basePropertiesMap.get(key); if (!properties.containsKey(DRIVER_KEY) || !properties.containsKey(URL_KEY)) { LOGGER.error("{} will be ignored. {}.{} and {}.{} is mandatory.", key, DRIVER_KEY, key, key, URL_KEY); removeKeySet.add(key); } } } for (String key : removeKeySet) { basePropertiesMap.remove(key); } LOGGER.debug("JDBC PropertiesMap: {}", basePropertiesMap); setMaxLineResults(); setMaxRows(); this.sqlSplitter = new SqlSplitter(); } JDBCInterpreter(Properties property); @Override ZeppelinContext getZeppelinContext(); @Override void open(); @Override void close(); JDBCUserConfigurations getJDBCConfiguration(String user); Connection getConnection(String dbPrefix, InterpreterContext context); InterpreterResult executePrecode(InterpreterContext interpreterContext); @Override InterpreterResult internalInterpret(String cmd, InterpreterContext context); @Override void cancel(InterpreterContext context); String getDBPrefix(InterpreterContext context); @Override FormType getFormType(); @Override int getProgress(InterpreterContext context); @Override Scheduler getScheduler(); @Override List<InterpreterCompletion> completion(String buf, int cursor,
      InterpreterContext interpreterContext); int getMaxResult();  }
@Test public void testForMapPrefix() throws IOException, InterpreterException { 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"; Map<String, String> localProperties = new HashMap<>(); localProperties.put("db", "fake"); InterpreterContext context = InterpreterContext.builder() .setAuthenticationInfo(new AuthenticationInfo("testUser")) .setLocalProperties(localProperties) .build(); InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.ERROR, interpreterResult.code()); assertEquals("Prefix not found.", interpreterResult.message().get(0).getData()); }
@Test public void testSelectQuery() throws IOException, InterpreterException { 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 WHERE ID in ('a', 'b'); "; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\na\ta_name\nb\tb_name\n", resultMessages.get(0).getData()); context = getInterpreterContext(); context.getLocalProperties().put("limit", "1"); interpreterResult = t.interpret(sqlQuery, context); resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\na\ta_name\n", resultMessages.get(0).getData()); }
@Test public void testInvalidSelectWithRefresh() throws IOException, InterpreterException { 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(); context.getLocalProperties().put("refreshInterval", "1000"); String sqlQuery = "select * from invalid_table;"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.ERROR, interpreterResult.code()); assertTrue(interpreterResult.toString(), interpreterResult.message() .get(0).getData().contains("Table \"INVALID_TABLE\" not found;")); }
@Test public void testColumnAliasQuery() throws IOException, InterpreterException { 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 NAME as SOME_OTHER_NAME from test_table limit 1"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(interpreterResult.toString(), InterpreterResult.Code.SUCCESS, interpreterResult.code()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("SOME_OTHER_NAME\na_name\n", resultMessages.get(0).getData()); }
@Test public void testQueryWithEscapedCharacters() throws IOException, InterpreterException { 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, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(1).getType()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(2).getType()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(3).getType()); assertEquals("'\\n'\t';'\n\\n\t;\n", resultMessages.get(0).getData()); assertEquals("'Atext;B'\nAtext;B\n", resultMessages.get(1).getData()); assertEquals("'\\'\t';'\n\\\t;\n", resultMessages.get(2).getData()); assertEquals("''''\t';'\n'\t;\n", resultMessages.get(3).getData()); }
@Test public void testSelectMultipleQueries() throws IOException, InterpreterException { 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(); String sqlQuery = "select * from test_table;" + "select * from test_table WHERE ID = ';';"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(2, resultMessages.size()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\na\ta_name\nb\tb_name\nc\tnull\n", resultMessages.get(0).getData()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(1).getType()); assertEquals("ID\tNAME\n", resultMessages.get(1).getData()); }
@Test public void testDefaultSplitQuries() throws IOException, InterpreterException { 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, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(2, resultMessages.size()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\na\ta_name\nb\tb_name\nc\tnull\n", resultMessages.get(0).getData()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(1).getType()); assertEquals("ID\tNAME\n", resultMessages.get(1).getData()); }
@Test public void testSelectQueryWithNull() throws IOException, InterpreterException { 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 WHERE ID = 'c'"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\nc\tnull\n", resultMessages.get(0).getData()); }
@Test public void testSelectQueryMaxResult() throws IOException, InterpreterException { Properties properties = new Properties(); properties.setProperty("common.max_count", "1"); 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"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("ID\tNAME\na\ta_name\n", resultMessages.get(0).getData()); assertEquals(InterpreterResult.Type.HTML, resultMessages.get(1).getType()); assertTrue(resultMessages.get(1).getData().contains("Output is truncated")); }
@Test public void testStatementPrecode() throws IOException, InterpreterException { Properties properties = new Properties(); properties.setProperty("default.driver", "org.h2.Driver"); properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); properties.setProperty(DEFAULT_STATEMENT_PRECODE, "set @v='statement'"); JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); jdbcInterpreter.open(); String sqlQuery = "select @v"; InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("@V\nstatement\n", resultMessages.get(0).getData()); }
@Test public void testIncorrectStatementPrecode() throws IOException, InterpreterException { Properties properties = new Properties(); properties.setProperty("default.driver", "org.h2.Driver"); properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); properties.setProperty(DEFAULT_STATEMENT_PRECODE, "set incorrect"); JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); jdbcInterpreter.open(); String sqlQuery = "select 1"; InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.ERROR, interpreterResult.code()); assertEquals(InterpreterResult.Type.TEXT, interpreterResult.message().get(0).getType()); assertTrue(interpreterResult.toString(), interpreterResult.message().get(0).getData().contains("Syntax error")); }
@Test public void testStatementPrecodeWithAnotherPrefix() throws IOException, InterpreterException { Properties properties = new Properties(); properties.setProperty("anotherPrefix.driver", "org.h2.Driver"); properties.setProperty("anotherPrefix.url", getJdbcConnection()); properties.setProperty("anotherPrefix.user", ""); properties.setProperty("anotherPrefix.password", ""); properties.setProperty(String.format(STATEMENT_PRECODE_KEY_TEMPLATE, "anotherPrefix"), "set @v='statementAnotherPrefix'"); JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); jdbcInterpreter.open(); Map<String, String> localProperties = new HashMap<>(); localProperties.put("db", "anotherPrefix"); InterpreterContext context = InterpreterContext.builder() .setAuthenticationInfo(new AuthenticationInfo("testUser")) .setInterpreterOut(new InterpreterOutput(null)) .setLocalProperties(localProperties) .build(); String sqlQuery = "select @v"; InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(InterpreterResult.Type.TABLE, resultMessages.get(0).getType()); assertEquals("@V\nstatementAnotherPrefix\n", resultMessages.get(0).getData()); }
@Test public void testSplitSqlQueryWithComments() throws IOException, InterpreterException { 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(); String sqlQuery = "\n" + "-- \n" + "\n" + "-- a ; b\n" + "select * from test_table WHERE ID = ';--';\n" + "select * from test_table WHERE ID = '/*'; -- test"; InterpreterResult interpreterResult = t.interpret(sqlQuery, context); assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); List<InterpreterResultMessage> resultMessages = context.out.toInterpreterResultMessage(); assertEquals(3, resultMessages.size()); }