You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@knox.apache.org by lm...@apache.org on 2019/09/19 00:04:39 UTC
[knox] branch master updated: KNOX-2016 - KnoxShellTable SQL
Builder, Col Select, Sort
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 <lm...@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);
}
}