DRILL-1340: Add support for JDBC metadata.

3 files changed, 358 insertions, 6 deletions

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 5d8b6a791..86cdefe23 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() {
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 000000000..5c0a2e3d9
--- /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);
+      }
+    }
+  };
+}
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 000000000..24372a7d6
--- /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);
+  }
+}