knox-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lmc...@apache.org
Subject [knox] branch master updated: KNOX-2016 - KnoxShellTable SQL Builder, Col Select, Sort
Date Thu, 19 Sep 2019 00:04:39 GMT
This is an automated email from the ASF dual-hosted git repository.

lmccay pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/knox.git


The following commit(s) were added to refs/heads/master by this push:
     new 6422b54  KNOX-2016 - KnoxShellTable SQL Builder, Col Select, Sort
6422b54 is described below

commit 6422b540f38627a9bc3982ef99e09a9d3fce4327
Author: lmccay <lmccay@apache.org>
AuthorDate: Wed Sep 18 20:04:14 2019 -0400

    KNOX-2016 - KnoxShellTable SQL Builder, Col Select, Sort
---
 .../apache/knox/gateway/shell/KnoxShellTable.java  | 199 ++++++++++++++++++++-
 .../knox/gateway/shell/KnoxShellTableTest.java     | 141 ++++++++++++++-
 2 files changed, 322 insertions(+), 18 deletions(-)

diff --git a/gateway-shell/src/main/java/org/apache/knox/gateway/shell/KnoxShellTable.java
b/gateway-shell/src/main/java/org/apache/knox/gateway/shell/KnoxShellTable.java
index 6c5e1b1..dfbbf09 100644
--- a/gateway-shell/src/main/java/org/apache/knox/gateway/shell/KnoxShellTable.java
+++ b/gateway-shell/src/main/java/org/apache/knox/gateway/shell/KnoxShellTable.java
@@ -24,7 +24,14 @@ import java.io.InputStreamReader;
 import java.net.URL;
 import java.net.URLConnection;
 import java.nio.charset.StandardCharsets;
+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.ArrayList;
+import java.util.Collections;
 import java.util.HashMap;
 import java.util.Iterator;
 import java.util.List;
@@ -90,6 +97,13 @@ public class KnoxShellTable {
     return col;
   }
 
+  public List<String> values(String colName) {
+    int colIndex = headers.indexOf(colName);
+    ArrayList<String> col = new ArrayList<String>();
+    rows.forEach(row -> col.add(row.get(colIndex)));
+    return col;
+  }
+
   public KnoxShellTable apply(KnoxShellTableCell cell) {
     if (!headers.isEmpty()) {
       headers.set(cell.colIndex, cell.header);
@@ -265,6 +279,13 @@ public class KnoxShellTable {
   }
 
   public static class KnoxShellTableBuilder {
+    protected String title;
+
+    public KnoxShellTableBuilder title(String title) {
+      this.title = title;
+      return this;
+    }
+
     public CSVKnoxShellTableBuilder csv() {
       return new CSVKnoxShellTableBuilder();
     }
@@ -276,9 +297,13 @@ public class KnoxShellTable {
     public JoinKnoxShellTableBuilder join() {
       return new JoinKnoxShellTableBuilder();
     }
+
+    public JDBCKnoxShellTableBuilder jdbc() {
+      return new JDBCKnoxShellTableBuilder();
+    }
   }
 
-  public static class JoinKnoxShellTableBuilder {
+  public static class JoinKnoxShellTableBuilder extends KnoxShellTableBuilder {
     private KnoxShellTable left;
     private KnoxShellTable right;
     private int leftIndex = -1;
@@ -287,6 +312,12 @@ public class KnoxShellTable {
     public JoinKnoxShellTableBuilder() {
     }
 
+    @Override
+    public JoinKnoxShellTableBuilder title(String title) {
+      this.title = title;
+      return this;
+    }
+
     public JoinKnoxShellTableBuilder left(KnoxShellTable left) {
       this.left = left;
       return this;
@@ -299,6 +330,9 @@ public class KnoxShellTable {
 
     public KnoxShellTable on(int leftIndex, int rightIndex) {
       KnoxShellTable joined = new KnoxShellTable();
+      if (title != null) {
+        joined.title(title);
+      }
 
       this.leftIndex = leftIndex;
       this.rightIndex = rightIndex;
@@ -307,7 +341,7 @@ public class KnoxShellTable {
       for (List<String> row : left.rows) {
         joined.rows.add(new ArrayList<String>(row));
       }
-      List<String> col = right.values(leftIndex);
+      List<String> col = right.values(rightIndex);
       ArrayList<String> row;
       String leftKey;
       int matchedIndex;
@@ -330,9 +364,15 @@ public class KnoxShellTable {
     }
   }
 
-  public static class JSONKnoxShellTableBuilder {
+  public static class JSONKnoxShellTableBuilder extends KnoxShellTableBuilder {
     boolean withHeaders;
 
+    @Override
+    public JSONKnoxShellTableBuilder title(String title) {
+      this.title = title;
+      return this;
+    }
+
     public JSONKnoxShellTableBuilder withHeaders() {
       withHeaders = true;
       return this;
@@ -349,24 +389,29 @@ public class KnoxShellTable {
       return table;
     }
 
-    public static KnoxShellTable getKnoxShellTableFromJsonString(String json) {
+    public KnoxShellTable getKnoxShellTableFromJsonString(String json) throws IOException
{
       KnoxShellTable table = null;
       JsonFactory factory = new JsonFactory();
       ObjectMapper mapper = new ObjectMapper(factory);
       TypeReference<KnoxShellTable> typeRef
             = new TypeReference<KnoxShellTable>() {};
-      try {
-        table = mapper.readValue(json, typeRef);
-      } catch (IOException e) {
-        //LOG.failedToGetMapFromJsonString( json, e );
+      table = mapper.readValue(json, typeRef);
+      if (title != null) {
+        table.title(title);
       }
       return table;
     }
   }
 
-  public static class CSVKnoxShellTableBuilder {
+  public static class CSVKnoxShellTableBuilder extends KnoxShellTableBuilder {
     boolean withHeaders;
 
+    @Override
+    public CSVKnoxShellTableBuilder title(String title) {
+      this.title = title;
+      return this;
+    }
+
     public CSVKnoxShellTableBuilder withHeaders() {
       withHeaders = true;
       return this;
@@ -383,6 +428,9 @@ public class KnoxShellTable {
         csvReader = new BufferedReader(new InputStreamReader(
             connection.getInputStream(), StandardCharsets.UTF_8));
         table = new KnoxShellTable();
+        if (title != null) {
+          table.title(title);
+        }
         String row = null;
         while ((row = csvReader.readLine()) != null) {
             boolean addingHeaders = (withHeaders && rowIndex == 0);
@@ -409,6 +457,89 @@ public class KnoxShellTable {
     }
   }
 
+  public static class JDBCKnoxShellTableBuilder extends KnoxShellTableBuilder {
+    private String connect;
+    private String username;
+    private String pwd;
+    private String driver;
+    private Connection conn;
+    private boolean tableManagedConnection = true;
+
+    @Override
+    public JDBCKnoxShellTableBuilder title(String title) {
+      this.title = title;
+      return this;
+    }
+
+    public JDBCKnoxShellTableBuilder connect(String connect) {
+      this.connect = connect;
+      return this;
+    }
+
+    public JDBCKnoxShellTableBuilder username(String username) {
+      this.username = username;
+      return this;
+    }
+
+    public JDBCKnoxShellTableBuilder pwd(String pwd) {
+      this.pwd = pwd;
+      return this;
+    }
+
+    public JDBCKnoxShellTableBuilder driver(String driver) {
+      this.driver = driver;
+      return this;
+    }
+
+    public JDBCKnoxShellTableBuilder connection(Connection connection) {
+      this.conn = connection;
+      this.tableManagedConnection = false;
+      return this;
+    }
+
+    public KnoxShellTable sql(String sql) throws IOException, SQLException {
+      KnoxShellTable table = null;
+      Statement statement = null;
+      ResultSet result = null;
+      if (conn == null) {
+        conn = DriverManager.getConnection(connect);
+      }
+      try {
+        if (conn != null) {
+          statement = conn.createStatement();
+  //table.builder().jdbc().connect("jdbc:derby:codejava/webdb1").username("lmccay").password("xxxx").sql("SELECT
* FROM book");
+          result = statement.executeQuery(sql);
+          table = new KnoxShellTable();
+          ResultSetMetaData metadata = result.getMetaData();
+          table.title(metadata.getTableName(1));
+          int colcount = metadata.getColumnCount();
+          for(int i = 1; i < colcount + 1; i++) {
+            table.header(metadata.getColumnName(i));
+          }
+          while (result.next()) {
+            table.row();
+            for(int i = 1; i < colcount + 1; i++) {
+              table.value(result.getString(metadata.getColumnName(i)));
+            }
+          }
+        }
+      }
+      finally {
+        result.close();
+        if (conn != null && tableManagedConnection) {
+          conn.close();
+        }
+        if (statement != null) {
+          statement.close();
+        }
+        if (result != null && !result.isClosed()) {
+          result.close();
+        }
+      }
+      return table;
+    }
+  }
+
   public String toJSON() {
     return JsonUtils.renderAsJsonString(this);
   }
@@ -441,6 +572,56 @@ public class KnoxShellTable {
     return csv.toString();
   }
 
+  public KnoxShellTable select(String cols) {
+    KnoxShellTable table = new KnoxShellTable();
+    List<ArrayList<String>> columns = new ArrayList<ArrayList<String>>();
+    String[] colnames = cols.split(",");
+    for (String colName : colnames) {
+      table.header(colName);
+      columns.add((ArrayList<String>) values(headers.indexOf(colName)));
+    }
+    for (int i = 0; i < rows.size(); i ++) {
+      table.row();
+      for (List<String> col : columns) {
+        table.value(col.get(i));
+      }
+    }
+    return table;
+  }
+
+  public KnoxShellTable sort(String colName) {
+    KnoxShellTable table = new KnoxShellTable();
+
+    String value;
+    List<String> col = values(colName);
+    List<RowIndex> index = new ArrayList<RowIndex>();
+    for (int i = 0; i < col.size(); i++) {
+      value = col.get(i);
+      index.add(new RowIndex(value, i));
+    }
+    Collections.sort(index);
+    table.headers = new ArrayList<String>(headers);
+    for (RowIndex i : index) {
+      table.rows.add(new ArrayList<String>(this.rows.get(i.index)));
+    }
+    return table;
+  }
+
+  public static class RowIndex implements Comparable<RowIndex> {
+    String value;
+    int index;
+
+    public RowIndex(String value, int index) {
+      this.value = value;
+      this.index = index;
+    }
+
+    @Override
+    public int compareTo(RowIndex other) {
+      return (this.value.compareTo(other.value));
+    }
+  }
+
   public KnoxShellTableFilter filter() {
     return new KnoxShellTableFilter();
   }
diff --git a/gateway-shell/src/test/java/org/apache/knox/gateway/shell/KnoxShellTableTest.java
b/gateway-shell/src/test/java/org/apache/knox/gateway/shell/KnoxShellTableTest.java
index c036c3f..a2c7e0f 100644
--- a/gateway-shell/src/test/java/org/apache/knox/gateway/shell/KnoxShellTableTest.java
+++ b/gateway-shell/src/test/java/org/apache/knox/gateway/shell/KnoxShellTableTest.java
@@ -17,15 +17,26 @@
  */
 package org.apache.knox.gateway.shell;
 
+import static org.easymock.EasyMock.createMock;
+import static org.easymock.EasyMock.expect;
+import static org.easymock.EasyMock.expectLastCall;
+import static org.easymock.EasyMock.replay;
+import static org.easymock.EasyMock.verify;
+
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertNull;
 
 import java.io.File;
 import java.io.IOException;
 import java.nio.charset.StandardCharsets;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Statement;
 
 import org.apache.commons.io.FileUtils;
 import org.apache.knox.gateway.shell.KnoxShellTable.KnoxShellTableCell;
+import org.easymock.IAnswer;
 import org.junit.Test;
 
 public class KnoxShellTableTest {
@@ -77,6 +88,31 @@ public class KnoxShellTableTest {
   }
 
   @Test
+  public void testTableSelect() {
+    String expectedResult = "+------------+------------+--------------+\n"
+        + "|  Column A  |  Column B  |   Column C   |\n" + "+------------+------------+--------------+\n"
+        + "|    123     |    456     |  344444444   |\n"
+        + "|    789     |    012     |  844444444   |\n" + "+------------+------------+--------------+\n";
+
+    String expectedResult2 = "+------------+--------------+\n"
+        + "|  Column A  |   Column C   |\n" + "+------------+--------------+\n"
+        + "|    123     |  344444444   |\n"
+        + "|    789     |  844444444   |\n" + "+------------+--------------+\n";
+
+    KnoxShellTable table = new KnoxShellTable();
+
+    table.header("Column A").header("Column B").header("Column C");
+
+    table.row().value("123").value("456").value("344444444");
+    table.row().value("789").value("012").value("844444444");
+
+    assertEquals(expectedResult, table.toString());
+
+    table = table.select("Column A,Column C");
+    assertEquals(expectedResult2, table.toString());
+  }
+
+  @Test
   public void testMultipleRowsTableNoHeadersRendering() {
     String expectedResult = "+--------+--------+--------------+\n"
         + "|  123   |  456   |  344444444   |\n"
@@ -107,7 +143,6 @@ public class KnoxShellTableTest {
 
   @Test
   public void testLoadCSVToAndFromURL() {
-//  table = KnoxShellTable.builder().db().driver("HiveDriver2").connect("sdklfjsdjflsd").sql("select
* from test;");
     KnoxShellTable table = new KnoxShellTable();
     table.title("From URL");
 
@@ -149,6 +184,20 @@ public class KnoxShellTableTest {
   }
 
   @Test
+  public void testSort() throws IOException {
+    KnoxShellTable table = new KnoxShellTable();
+
+    table.header("Column A").header("Column B").header("Column C");
+
+    table.row().value("789").value("012").value("844444444");
+    table.row().value("123").value("456").value("344444444");
+
+    KnoxShellTable table2 = table.sort("Column A");
+    assertEquals(table2.getRows().get(0).get(0), "123");
+    assertEquals(table2.getRows().get(1).get(0), "789");
+  }
+
+  @Test
   public void testCells() throws IOException {
     KnoxShellTable table = new KnoxShellTable();
 
@@ -185,20 +234,20 @@ public class KnoxShellTableTest {
   public void testJoinTables() throws IOException {
     KnoxShellTable table = new KnoxShellTable();
 
-    table.title("Left Table").header("Column A").header("Column B").header("Column C");
+    table.title("Left Table").header("Column A").header("Column B").header("Column C").header("Column
D");
 
-    table.row().value("123").value("456").value("344444444");
-    table.row().value("789").value("012").value("844444444");
+    table.row().value("123").value("456").value("344444444").value("2");
+    table.row().value("789").value("012").value("844444444").value("2");
+    table.row().value("980").value("2").value("844444444").value("2");
 
     KnoxShellTable table2 = new KnoxShellTable();
 
-    table2.title("Right Table").header("Column D").header("Column E").header("Column F");
+    table2.title("Right Table").header("Column D").header("Column E").header("Column F").header("Column
G");
 
-    table2.row().value("123").value("367").value("244444444");
-    table2.row().value("780").value("908").value("944444444");
+    table2.row().value("123").value("367").value("244444444").value("2");
+    table2.row().value("780").value("908").value("944444444").value("3");
 
-    KnoxShellTable joined = KnoxShellTable.builder().join().left(table).right(table2).on(0,
0);
-    joined.title("Joined Table");
+    KnoxShellTable joined = KnoxShellTable.builder().join().title("Joined Table").left(table).right(table2).on(0,
0);
 
     assertEquals(joined.getRows().size(), 1);
     assertEquals(joined.getTitle(), "Joined Table");
@@ -211,5 +260,79 @@ public class KnoxShellTableTest {
     assertEquals(zombie.getRows().size(), 1);
     assertEquals(zombie.getTitle(), "Zombie Table");
     assertEquals(zombie.cell(0, 0).value(), "123");
+    KnoxShellTable joined2 = KnoxShellTable.builder().join().title("Joined Table 2").left(table).right(table2).on(1,
3);
+    assertEquals(1, joined2.getRows().size());
+  }
+
+  @Test
+  public void testJDBCBuilderUnManagedConnection() throws Exception {
+    Connection connection = createMock(Connection.class);
+    Statement statement = createMock(Statement.class);
+    ResultSet resultSet = createMock(ResultSet.class);
+    ResultSetMetaData metadata = createMock(ResultSetMetaData.class);
+    try {
+    expect(connection.createStatement()).andReturn(statement);
+    expect(statement.executeQuery("select * from book")).andReturn(resultSet);
+    expect(resultSet.getMetaData()).andReturn(metadata);
+    if (resultSet.next()) {
+      // intentionally empty in order to pass PMC and checkstyle!
+    }
+    expectLastCall().andAnswer(new IAnswer<Boolean>() {
+      int called;
+      @Override
+      public Boolean answer() {
+        called++;
+        if (called < 2) {
+          return true;
+        }
+        return false;
+      }
+    }).times(2);
+    expect(resultSet.isClosed()).andReturn(true);
+    expect(resultSet.getString("BOOK_ID")).andReturn("1").times(1);
+    expect(resultSet.getString("TITLE")).andReturn("Apache Knox: The Definitive Guide").times(1);
+    expect(metadata.getTableName(1)).andReturn("BOOK");
+    expect(metadata.getColumnCount()).andReturn(2);
+    expect(metadata.getColumnName(1)).andReturn("BOOK_ID").anyTimes();
+    expect(metadata.getColumnName(2)).andReturn("TITLE").anyTimes();
+    resultSet.close();
+    expectLastCall().andAnswer(new IAnswer<Object>() {
+      @Override
+      public Object answer() {
+          return null;
+      }
+    }).times(2);
+    // should close the statement
+    statement.close();
+    expectLastCall().andAnswer(new IAnswer<Object>() {
+      @Override
+      public Object answer() {
+          return null;
+      }
+    }).times(2);
+    connection.close();
+    expectLastCall().andAnswer(new IAnswer<Object>() {
+      @Override
+      public Object answer() {
+          return null;
+      }
+    });
+    replay(connection, statement, resultSet, metadata);
+
+    KnoxShellTable table = KnoxShellTable.builder().jdbc().connection(connection).sql("select
* from book");
+    assertEquals(1, table.getRows().size());
+    }
+    finally {
+      connection.close();
+//      expectLastCall().andAnswer(new IAnswer<Object>() {
+//        @Override
+//        public Object answer() {
+//            return null;
+//        }
+//      });
+      statement.close();
+      resultSet.close();
+    }
+    verify(connection, statement, resultSet, metadata);
   }
 }


Mime
View raw message