DRILL-1340: Add support for JDBC metadata.
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/967fef5a Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/967fef5a Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/967fef5a Branch: refs/heads/master Commit: 967fef5ade88d7be30f745cc884f34b911f0bbb3 Parents: 8bf3c4e Author: Jacques Nadeau <[email protected]> Authored: Thu Aug 28 22:58:31 2014 -0700 Committer: Jacques Nadeau <[email protected]> Committed: Fri Aug 29 08:53:41 2014 -0700 ---------------------------------------------------------------------- .../java/org/apache/drill/jdbc/MetaImpl.java | 135 +++++++++++++++++- .../drill/jdbc/test/JdbcTestActionBase.java | 136 +++++++++++++++++++ .../drill/jdbc/test/TestJdbcMetadata.java | 93 +++++++++++++ 3 files changed, 358 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/967fef5a/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java b/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java index 5d8b6a7..86cdefe 100644 --- a/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java +++ b/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java @@ -17,6 +17,7 @@ */ package org.apache.drill.jdbc; +import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.util.List; @@ -25,10 +26,15 @@ import net.hydromatic.avatica.AvaticaResultSet; import net.hydromatic.avatica.AvaticaStatement; import net.hydromatic.avatica.Cursor; import net.hydromatic.avatica.Meta; -import net.hydromatic.linq4j.Linq4j; + +import org.apache.commons.lang.StringEscapeUtils; +import org.apache.drill.common.exceptions.DrillRuntimeException; + public class MetaImpl implements Meta { - + + static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(MetaImpl.class); + static final Driver DRIVER = new Driver(); final DrillConnectionImpl connection; @@ -61,21 +67,138 @@ public class MetaImpl implements Meta { return null; } + private ResultSet s(String s){ + try{ + logger.debug("Running {}", s); + AvaticaStatement statement = connection.createStatement(); + statement.execute(s); + return statement.getResultSet(); + + }catch(Exception e){ + throw new DrillRuntimeException("Failure while attempting to get DatabaseMetadata.", e); + } + + } + public ResultSet getTables(String catalog, final Pat schemaPattern, final Pat tableNamePattern, final List<String> typeList) { - return getEmptyResultSet(); + StringBuilder sb = new StringBuilder(); + sb.append("select " + + "TABLE_CATALOG as TABLE_CAT, " + + "TABLE_SCHEMA as TABLE_SCHEM, " + + "TABLE_NAME, " + + "TABLE_TYPE, " + + "'' as REMARKS, " + + "'' as TYPE_CAT, " + + "'' as TYPE_SCHEM, " + + "'' as TYPE_NAME, " + + "'' as SELF_REFERENCING_COL_NAME, " + + "'' as REF_GENERATION " + + "FROM INFORMATION_SCHEMA.`TABLES` WHERE 1=1 "); + + if(catalog != null){ + sb.append(" AND TABLE_CATALOG = '" + StringEscapeUtils.escapeSql(catalog) + "' "); + } + + if(schemaPattern.s != null){ + sb.append(" AND TABLE_SCHEMA like '" + StringEscapeUtils.escapeSql(schemaPattern.s) + "'"); + } + + if(tableNamePattern.s != null){ + sb.append(" AND TABLE_NAME like '" + StringEscapeUtils.escapeSql(tableNamePattern.s) + "'"); + } + + if(typeList != null && typeList.size() > 0){ + sb.append("AND ("); + for(int t = 0; t < typeList.size(); t++){ + if(t != 0) sb.append(" OR "); + sb.append(" TABLE_TYPE LIKE '" + StringEscapeUtils.escapeSql(typeList.get(t)) + "' "); + } + sb.append(")"); + } + + sb.append(" ORDER BY TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME"); + + return s(sb.toString()); } public ResultSet getColumns(String catalog, Pat schemaPattern, Pat tableNamePattern, Pat columnNamePattern) { - return getEmptyResultSet(); + + StringBuilder sb = new StringBuilder(); + sb.append("select " + + "TABLE_CATALOG as TABLE_CAT, " + + "TABLE_SCHEMA as TABLE_SCHEM, " + + "TABLE_NAME, " + + "COLUMN_NAME, " + + "DATA_TYPE, " + + "CHARACTER_MAXIMUM_LENGTH as BUFFER_LENGTH, " + + "NUMERIC_PRECISION as DECIMAL_PRECISION, " + + "NUMERIC_PRECISION_RADIX as NUM_PREC_RADIX, " + + DatabaseMetaData.columnNullableUnknown + + " as NULLABLE, " + + "'' as REMARKS, " + + "'' as COLUMN_DEF, " + + "0 as SQL_DATA_TYPE, " + + "0 as SQL_DATETIME_SUB, " + + "4 as CHAR_OCTET_LENGTH, " + + "1 as ORDINAL_POSITION, " + + "'YES' as IS_NULLABLE, " + + "'' as SCOPE_CATALOG," + + "'' as SCOPE_SCHEMA, " + + "'' as SCOPE_TABLE, " + + "'' as SOURCE_DATA_TYPE, " + + "'' as IS_AUTOINCREMENT, " + + "'' as IS_GENERATEDCOLUMN " + + "FROM INFORMATION_SCHEMA.COLUMNS " + + "WHERE 1=1 "); + + if(catalog != null){ + sb.append(" AND TABLE_CATALOG = '" + StringEscapeUtils.escapeSql(catalog) + "' "); + } + if(schemaPattern.s != null){ + sb.append(" AND TABLE_SCHEMA like '" + StringEscapeUtils.escapeSql(schemaPattern.s) + "'"); + } + + if(tableNamePattern.s != null){ + sb.append(" AND TABLE_NAME like '" + StringEscapeUtils.escapeSql(tableNamePattern.s) + "'"); + } + + if(columnNamePattern.s != null){ + sb.append(" AND COLUMN_NAME like '" + StringEscapeUtils.escapeSql(columnNamePattern.s) + "'"); + } + + sb.append(" ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME"); + + return s(sb.toString()); } public ResultSet getSchemas(String catalog, Pat schemaPattern) { - return getEmptyResultSet(); + StringBuilder sb = new StringBuilder(); + sb.append("select " + + "SCHEMA_NAME as TABLE_SCHEM, " + + "CATALOG_NAME as TABLE_CAT " + + " FROM INFORMATION_SCHEMA.SCHEMATA WHERE 1=1 "); + + if(catalog != null){ + sb.append(" AND CATALOG_NAME = '" + StringEscapeUtils.escapeSql(catalog) + "' "); + } + if(schemaPattern.s != null){ + sb.append(" AND SCHEMA_NAME like '" + StringEscapeUtils.escapeSql(schemaPattern.s) + "'"); + } + sb.append(" ORDER BY CATALOG_NAME, SCHEMA_NAME"); + + return s(sb.toString()); } public ResultSet getCatalogs() { - return getEmptyResultSet(); + StringBuilder sb = new StringBuilder(); + sb.append("select " + + "CATALOG_NAME as TABLE_CAT " + + " FROM INFORMATION_SCHEMA.CATALOGS "); + + sb.append(" ORDER BY CATALOG_NAME"); + + return s(sb.toString()); } public ResultSet getTableTypes() { http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/967fef5a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/JdbcTestActionBase.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/JdbcTestActionBase.java b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/JdbcTestActionBase.java new file mode 100644 index 0000000..5c0a2e3 --- /dev/null +++ b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/JdbcTestActionBase.java @@ -0,0 +1,136 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.drill.jdbc.test; + +import java.io.IOException; +import java.nio.file.Paths; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.concurrent.TimeUnit; + +import org.apache.drill.common.util.TestTools; +import org.apache.drill.jdbc.Driver; +import org.apache.drill.jdbc.JdbcTest; +import org.junit.AfterClass; +import org.junit.Assert; +import org.junit.BeforeClass; +import org.junit.Rule; +import org.junit.rules.TestRule; +import org.junit.rules.TestWatcher; +import org.junit.runner.Description; + +import com.google.common.base.Stopwatch; + +public class JdbcTestActionBase extends JdbcTest { + // Set a timeout unless we're debugging. + @Rule + public TestRule TIMEOUT = TestTools.getTimeoutRule(40000); + + protected static final String WORKING_PATH; + static { + Driver.load(); + WORKING_PATH = Paths.get("").toAbsolutePath().toString(); + + } + + protected void testQuery(final String sql) throws Exception { + testAction(new JdbcAction() { + + @Override + public ResultSet getResult(Connection c) throws SQLException { + Statement s = c.createStatement(); + ResultSet r = s.executeQuery(sql); + return r; + } + + }); + } + + protected void testAction(JdbcAction action) throws Exception { + testAction(action, -1); + } + + protected void testAction(JdbcAction action, long rowcount) throws Exception { + int rows = 0; + Stopwatch watch = new Stopwatch().start(); + ResultSet r = action.getResult(connection); + boolean first = true; + while (r.next()) { + rows++; + ResultSetMetaData md = r.getMetaData(); + if (first == true) { + for (int i = 1; i <= md.getColumnCount(); i++) { + System.out.print(md.getColumnName(i)); + System.out.print('\t'); + } + System.out.println(); + first = false; + } + + for (int i = 1; i <= md.getColumnCount(); i++) { + System.out.print(r.getObject(i)); + System.out.print('\t'); + } + System.out.println(); + } + + System.out.println(String.format("Query completed in %d millis.", watch.elapsed(TimeUnit.MILLISECONDS))); + + if (rowcount != -1) + Assert.assertEquals((long) rowcount, (long) rows); + + System.out.println("\n\n\n"); + + } + + public static interface JdbcAction { + ResultSet getResult(Connection c) throws SQLException; + } + + static void resetConnection() throws Exception { + closeClient(); + openClient(); + } + + static Connection connection; + + @BeforeClass + public static void openClient() throws Exception { + connection = DriverManager.getConnection("jdbc:drill:zk=local", null); + } + + @AfterClass + public static void closeClient() throws IOException, SQLException { + connection.close(); + } + + public final TestRule resetWatcher = new TestWatcher() { + @Override + protected void failed(Throwable e, Description description) { + try { + resetConnection(); + } catch (Exception e1) { + throw new RuntimeException("Failure while resetting client.", e1); + } + } + }; +} http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/967fef5a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestJdbcMetadata.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestJdbcMetadata.java b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestJdbcMetadata.java new file mode 100644 index 0000000..24372a7 --- /dev/null +++ b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestJdbcMetadata.java @@ -0,0 +1,93 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.drill.jdbc.test; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; + +import org.junit.Test; + + +public class TestJdbcMetadata extends JdbcTestActionBase { + static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestJdbcMetadata.class); + + + @Test + public void catalogs() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getCatalogs(); + } + }, 1); + } + + @Test + public void allSchemas() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getSchemas(); + } + }); + } + + @Test + public void schemasWithConditions() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getSchemas("DRILL", "%fs%"); + } + }, 6); + } + + @Test + public void allTables() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getTables(null, null, null, null); + } + }); + } + + @Test + public void tablesWithConditions() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getTables("DRILL", "sys", "opt%", new String[]{"TABLE", "VIEW"}); + } + }, 1); + } + + @Test + public void allColumns() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getColumns(null, null, null, null); + } + }); + } + + @Test + public void columnsWithConditions() throws Exception{ + this.testAction(new JdbcAction(){ + public ResultSet getResult(Connection c) throws SQLException { + return c.getMetaData().getColumns("DRILL", "sys", "opt%", "%ame"); + } + }, 1); + } +}
