From 14b727ac09bf5a635b49a50864be3904f6c03d89 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Tue, 1 Nov 2016 22:13:09 +0900 Subject: [PATCH] adds support for null suppression This is controlled via the "\pset suppress_nulls=on" command. Rows with all null values will be suppressed. Columns with all null values will be suppressed. Suppression only applies to formatted (tabular) output, not to CSV output. When null-suppression mode is on, only one RowBuffer worth of (non-suppressed) rows will be fetched; this limit is currently hard-coded to 50 rows. --- src/main/java/net/jaekl/squelch/Squelch.java | 2 + .../java/net/jaekl/squelch/db/DbDriver.java | 9 ++ .../java/net/jaekl/squelch/stmt/Describe.java | 16 +-- .../java/net/jaekl/squelch/stmt/PSet.java | 96 ++++++++++++++ .../java/net/jaekl/squelch/stmt/Select.java | 6 +- .../java/net/jaekl/squelch/stmt/Tabular.java | 114 ++++++++++++----- .../net/jaekl/squelch/stmt/DescribeTest.java | 9 +- .../java/net/jaekl/squelch/stmt/PSetTest.java | 120 ++++++++++++++++++ .../net/jaekl/squelch/stmt/TabularTest.java | 4 +- 9 files changed, 331 insertions(+), 45 deletions(-) create mode 100644 src/main/java/net/jaekl/squelch/stmt/PSet.java create mode 100644 src/test/java/net/jaekl/squelch/stmt/PSetTest.java diff --git a/src/main/java/net/jaekl/squelch/Squelch.java b/src/main/java/net/jaekl/squelch/Squelch.java index 7b43bd2..953a179 100644 --- a/src/main/java/net/jaekl/squelch/Squelch.java +++ b/src/main/java/net/jaekl/squelch/Squelch.java @@ -12,6 +12,7 @@ import net.jaekl.squelch.db.MySqlDriver; import net.jaekl.squelch.db.OracleDriver; import net.jaekl.squelch.db.PostgresqlDriver; import net.jaekl.squelch.stmt.Describe; +import net.jaekl.squelch.stmt.PSet; import net.jaekl.squelch.stmt.Select; import net.jaekl.squelch.stmt.Stmt; import net.jaekl.squelch.util.ConsoleInput; @@ -28,6 +29,7 @@ public class Squelch { }; private static final Stmt[] READ_ONLY_STATEMENTS = { new Describe(), + new PSet(), new Select() }; diff --git a/src/main/java/net/jaekl/squelch/db/DbDriver.java b/src/main/java/net/jaekl/squelch/db/DbDriver.java index 931fcf1..72472de 100644 --- a/src/main/java/net/jaekl/squelch/db/DbDriver.java +++ b/src/main/java/net/jaekl/squelch/db/DbDriver.java @@ -6,11 +6,20 @@ import java.sql.SQLException; import java.util.Locale; public abstract class DbDriver { + private boolean m_suppressNulls = false; + // Returns true iff. this DbDriver knows how to connect to the given JDBC URL abstract public boolean handles(String jdbcUrl); // Execute line as a statement of this type abstract String getJdbcDriverClassName(); + + // ------------------- + // Getters and setters + + public boolean isSuppressNulls() { return m_suppressNulls; } + public void setSuppressNulls(boolean value) { m_suppressNulls = value; } + // Open a new Connection to the database. Note that the caller must close() this at some point. public Connection connect(String jdbcUrl, String userName, String password) throws ClassNotFoundException, SQLException diff --git a/src/main/java/net/jaekl/squelch/stmt/Describe.java b/src/main/java/net/jaekl/squelch/stmt/Describe.java index a76e5f0..e75b364 100644 --- a/src/main/java/net/jaekl/squelch/stmt/Describe.java +++ b/src/main/java/net/jaekl/squelch/stmt/Describe.java @@ -56,27 +56,27 @@ public class Describe extends Stmt { tablePattern = driver.adjustCase(tablePattern); if (tablePattern.length() < 1) { - return describeAll(pw, metaData); + return describeAll(driver, pw, metaData); } else { - return describeTable(pw, metaData, tablePattern); + return describeTable(driver, pw, metaData, tablePattern); } } - int describeAll(PrintWriter pw, DatabaseMetaData metaData) + int describeAll(DbDriver driver, PrintWriter pw, DatabaseMetaData metaData) throws SQLException { try (ResultSet rs = metaData.getTables(null, null, "%", null)) { TabularResultSet tabular = new TabularResultSet(rs); // TODO: StringTable i18n - tabular.printTable(pw, "???"); + tabular.printTable(driver, pw, "???"); } return 0; } - int describeTable(PrintWriter pw, DatabaseMetaData metaData, String tablePattern) + int describeTable(DbDriver driver, PrintWriter pw, DatabaseMetaData metaData, String tablePattern) throws SQLException { boolean found = false; @@ -86,7 +86,7 @@ public class Describe extends Stmt { { while (rs.next()) { found = true; - describe(pw, metaData, rs); + describe(driver, pw, metaData, rs); } } @@ -97,7 +97,7 @@ public class Describe extends Stmt { return 0; } - void describe(PrintWriter pw, DatabaseMetaData metaData, ResultSet tableRs) + void describe(DbDriver driver, PrintWriter pw, DatabaseMetaData metaData, ResultSet tableRs) throws SQLException { String catalogueName = tableRs.getString(1); @@ -123,7 +123,7 @@ public class Describe extends Stmt { { TabularColumnInfo tabular = new TabularColumnInfo(colsRs); // TODO: StringTable i18n - tabular.printTable(pw, "???"); + tabular.printTable(driver, pw, "???"); } } } diff --git a/src/main/java/net/jaekl/squelch/stmt/PSet.java b/src/main/java/net/jaekl/squelch/stmt/PSet.java new file mode 100644 index 0000000..a64edc2 --- /dev/null +++ b/src/main/java/net/jaekl/squelch/stmt/PSet.java @@ -0,0 +1,96 @@ +package net.jaekl.squelch.stmt; + +import java.io.IOException; +import java.io.PrintWriter; +import java.sql.Connection; +import java.sql.SQLException; +import java.util.Locale; + +import net.jaekl.squelch.db.DbDriver; + +public class PSet extends Stmt { + private static final String SUPPRESS_NULLS = "suppress_nulls"; + + @Override + public boolean handles(String line) { + if (null == line) { + return false; + } + + return line.toLowerCase(Locale.CANADA).startsWith("\\pset "); + } + + @Override + public int exec(DbDriver driver, Connection conn, PrintWriter pw, String line) + throws IOException, SQLException + { + String trimmed = line.substring(6).trim(); + + int equPos = trimmed.indexOf('='); + + if (equPos > 0) { + return setValue(driver, pw, trimmed, equPos); + } + else if (trimmed.length() > 0) { + return displayValue(driver, pw, trimmed); + } + else { + // TODO: StringTable + pw.println("???"); + return 0; + } + } + + private int displayValue(DbDriver driver, PrintWriter pw, String trimmed) + { + String lcName = trimmed.toLowerCase(Locale.CANADA); + + if (SUPPRESS_NULLS.equals(lcName)) { + // TODO: StringTable + pw.println(SUPPRESS_NULLS + ": " + (driver.isSuppressNulls() ? "on" : "off")); + } + else { + // TODO: StringTable + pw.println("??? Unrecognized setting: \"" + trimmed + "\". Ignored."); + } + + return 0; + } + + private boolean parseBoolean(String value) + { + if (null == value) { + return false; + } + + String setting = value.trim().toLowerCase(Locale.CANADA); + + if (setting.equals("yes") || setting.equals("true") || setting.equals("on") || setting.equals("1")) { + return true; + } + if (setting.equals("no") || setting.equals("false") || setting.equals("off") || setting.equals("0")) { + return false; + } + + throw new IllegalArgumentException("UnrecognizedBooleanValue: \"" + value + "\""); + } + + private int setValue(DbDriver driver, PrintWriter pw, String trimmed, int equPos) + { + String name = trimmed.substring(0, equPos).trim(); + String value = trimmed.substring(equPos + 1).trim(); + + String lcName = name.toLowerCase(Locale.CANADA); + + if (lcName.equals("suppress_nulls")) { + driver.setSuppressNulls(parseBoolean(value)); + } + else { + // TODO: Stringtable + pw.println("??? Unrecognized setting name \"" + name + "\" ignored."); + return 0; + } + + return 1; + } +} diff --git a/src/main/java/net/jaekl/squelch/stmt/Select.java b/src/main/java/net/jaekl/squelch/stmt/Select.java index c5f785e..173138d 100644 --- a/src/main/java/net/jaekl/squelch/stmt/Select.java +++ b/src/main/java/net/jaekl/squelch/stmt/Select.java @@ -37,17 +37,17 @@ public class Select extends Query { { try (ResultSet rs = ps.executeQuery()) { - rowCount = printFormatted(pw, rs); + rowCount = printFormatted(driver, pw, rs); } } return rowCount; } - private int printFormatted(PrintWriter pw, ResultSet rs) throws IOException, SQLException + private int printFormatted(DbDriver driver, PrintWriter pw, ResultSet rs) throws IOException, SQLException { TabularResultSet trs = new TabularResultSet(rs); // TODO: StringTable i18n - int rowCount = trs.printTable(pw, "No rows returned."); + int rowCount = trs.printTable(driver, pw, "No rows returned."); pw.flush(); return rowCount; diff --git a/src/main/java/net/jaekl/squelch/stmt/Tabular.java b/src/main/java/net/jaekl/squelch/stmt/Tabular.java index 582b26a..eef757d 100644 --- a/src/main/java/net/jaekl/squelch/stmt/Tabular.java +++ b/src/main/java/net/jaekl/squelch/stmt/Tabular.java @@ -11,6 +11,7 @@ import java.sql.Types; import javax.xml.bind.DatatypeConverter; +import net.jaekl.squelch.db.DbDriver; import net.jaekl.squelch.sql.Column; import net.jaekl.squelch.sql.Row; @@ -50,7 +51,7 @@ abstract public class Tabular { abstract Row getNext() throws SQLException; // Returns the number of (data) rows that were output - public int printTable(PrintWriter pw, String noRowsMessage) + public int printTable(DbDriver driver, PrintWriter pw, String noRowsMessage) throws SQLException { int rowCount = 0; @@ -61,25 +62,33 @@ abstract public class Tabular { int[] colWidths = initColWidthsFromColNames(cols); // Examine and buffer up to ROW_BUF_SIZE rows, adjusting (widening) colWidths where needed - rowBuf = bufferRows(colWidths); + rowBuf = bufferRows(driver, colWidths); + boolean[] suppressed = suppressNulls(driver, rowBuf, cols); int pending = rowBuf.getPending(); if (pending > 0) { - writeHeader(pw, cols, colWidths); - writeRowBuffer(pw, rowBuf, colWidths); + writeHeader(pw, cols, colWidths, suppressed); + writeRowBuffer(pw, rowBuf, colWidths, suppressed); rowCount = rowBuf.getPending(); } - while (pending > 0) { - rowBuf = bufferRows(colWidths); - writeRowBuffer(pw, rowBuf, colWidths); - pending = rowBuf.getPending(); - rowCount += pending; + if (driver.isSuppressNulls()) { + // TODO: StringTable + pw.println("Row limit for suppress_nulls has been reached; output may have been truncated."); + writeDivider(pw, colWidths, suppressed); + } + else { + while (pending > 0) { + rowBuf = bufferRows(driver, colWidths); + writeRowBuffer(pw, rowBuf, colWidths, suppressed); + pending = rowBuf.getPending(); + rowCount += pending; + } } if (rowCount > 0) { - writeDivider(pw, colWidths); - // TODO: Implement a String table for i18n + writeDivider(pw, colWidths, suppressed); + // TODO: Implement a StringTable for i18n pw.println("" + rowCount + " row(s) returned."); } else { @@ -137,7 +146,7 @@ abstract public class Tabular { // Examine and buffer up to rowBuf.length rows. // Returns the number of actual rows that were buffered (zero if no more rows are available). - RowBuffer bufferRows(int[] colWidths) throws SQLException + RowBuffer bufferRows(DbDriver driver, int[] colWidths) throws SQLException { RowBuffer rowBuf = new RowBuffer(); @@ -147,14 +156,25 @@ abstract public class Tabular { // No more rows available return rowBuf; } - rowBuf.addRow(row); + + boolean allColsNull = true; + for (int idx = 1; idx <= colWidths.length; ++idx) { + if (null != row.getValue(idx)) { + allColsNull = false; + } + } - // Check whether all values in this row will fit in the current column widths - for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) { - int width = ("" + row.getValue(colIdx + 1)).length(); - if (width > colWidths[colIdx]) { - // Widen the column to fit this value - colWidths[colIdx] = width; + if ((!allColsNull) || (!driver.isSuppressNulls())) + { + rowBuf.addRow(row); + + // Check whether all values in this row will fit in the current column widths + for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) { + int width = ("" + row.getValue(colIdx + 1)).length(); + if (width > colWidths[colIdx]) { + // Widen the column to fit this value + colWidths[colIdx] = width; + } } } } @@ -283,15 +303,47 @@ abstract public class Tabular { return ("" + obj).length(); } - void writeDivider(PrintWriter pw, int[] colWidths) { + boolean[] suppressNulls(DbDriver driver, RowBuffer rowBuf, Column[] cols) + { + boolean[] result = new boolean[cols.length]; + + if (rowBuf.getPending() < 1) { + // No data rows, so do not suppress any columns. + for (int colIdx = 0; colIdx < cols.length; ++colIdx) { + result[colIdx] = false; + } + return result; + } + + // Start with the assumption that we'll suppress all columns + for (int colIdx = 0; colIdx < cols.length; ++colIdx) { + result[colIdx] = true; + } + + for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) { + Row row = rowBuf.getRow(rowIdx); + for (int colIdx = 1; colIdx <= cols.length; ++colIdx) { + if (null != row.getValue(colIdx)) { + // This column has data, so do not suppress it + result[colIdx - 1] = false; + } + } + } + + return result; + } + + void writeDivider(PrintWriter pw, int[] colWidths, boolean[] suppressed) { for (int idx = 0; idx < colWidths.length; ++idx) { - pw.print("+" + repChar('-', colWidths[idx] + 2)); + if (!suppressed[idx]) { + pw.print("+" + repChar('-', colWidths[idx] + 2)); + } } pw.println("+"); } - void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths) { - writeDivider(pw, colWidths); + void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths, boolean[] suppressed) { + writeDivider(pw, colWidths, suppressed); for (int idx = 0; idx < cols.length; ++idx) { Column col = cols[idx]; @@ -299,19 +351,21 @@ abstract public class Tabular { } pw.println("|"); - writeDivider(pw, colWidths); + writeDivider(pw, colWidths, suppressed); } - void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths) throws SQLException { + void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths, boolean[] suppressed) throws SQLException { for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) { Row row = rowBuf.getRow(rowIdx); for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) { - Object obj = row.getValue(colIdx + 1); - String value = stringify(obj); - int width = stringWidth(obj); - String padding = repChar(' ', colWidths[colIdx] - width); - pw.print("| " + value + padding + " "); + if (!suppressed[colIdx]) { + Object obj = row.getValue(colIdx + 1); + String value = stringify(obj); + int width = stringWidth(obj); + String padding = repChar(' ', colWidths[colIdx] - width); + pw.print("| " + value + padding + " "); + } } pw.println("|"); } diff --git a/src/test/java/net/jaekl/squelch/stmt/DescribeTest.java b/src/test/java/net/jaekl/squelch/stmt/DescribeTest.java index 607a80d..0407040 100644 --- a/src/test/java/net/jaekl/squelch/stmt/DescribeTest.java +++ b/src/test/java/net/jaekl/squelch/stmt/DescribeTest.java @@ -50,6 +50,7 @@ public class DescribeTest { public void testDescribeAll_noTables() throws IOException, SQLException { DatabaseMetaDataMock dbmdm = new DatabaseMetaDataMock(); + DbDriverMock driver = new DbDriverMock(); Describe describe = new Describe(); try ( @@ -57,7 +58,7 @@ public class DescribeTest { PrintWriter pw = new PrintWriter(new OutputStreamWriter(baos, StandardCharsets.UTF_8)); ) { - describe.describeAll(pw, dbmdm); + describe.describeAll(driver, pw, dbmdm); pw.close(); baos.close(); String actual = baos.toString(); @@ -71,6 +72,7 @@ public class DescribeTest { throws IOException, SQLException { DatabaseMetaDataMock dbmdm = new DatabaseMetaDataMock(); + DbDriverMock driver = new DbDriverMock(); Describe describe = new Describe(); try ( @@ -78,7 +80,7 @@ public class DescribeTest { PrintWriter pw = new PrintWriter(new OutputStreamWriter(baos, StandardCharsets.UTF_8)); ) { - describe.describeTable(pw, dbmdm, "%"); + describe.describeTable(driver, pw, dbmdm, "%"); pw.close(); baos.close(); String actual = baos.toString(); @@ -214,6 +216,7 @@ public class DescribeTest { throws IOException, SQLException { DatabaseMetaDataMock dbmdm = construct_runs_dbmdm(); + DbDriverMock driver = new DbDriverMock(); Describe describe = new Describe(); try ( @@ -221,7 +224,7 @@ public class DescribeTest { PrintWriter pw = new PrintWriter(new OutputStreamWriter(baos, StandardCharsets.UTF_8)); ) { - describe.describeTable(pw, dbmdm, "runs"); + describe.describeTable(driver, pw, dbmdm, "runs"); pw.close(); baos.close(); String actual = baos.toString(); diff --git a/src/test/java/net/jaekl/squelch/stmt/PSetTest.java b/src/test/java/net/jaekl/squelch/stmt/PSetTest.java new file mode 100644 index 0000000..c33e00a --- /dev/null +++ b/src/test/java/net/jaekl/squelch/stmt/PSetTest.java @@ -0,0 +1,120 @@ +package net.jaekl.squelch.stmt; + +import static org.junit.Assert.*; + +import java.io.ByteArrayOutputStream; + +import java.io.IOException; +import java.io.PrintWriter; +import java.sql.SQLException; + +import net.jaekl.squelch.db.DbDriverMock; +import net.jaekl.squelch.sql.ConnectionMock; + +import org.junit.Test; + +public class PSetTest { + + @Test + public void testHandles() + { + String[] valid = { + "\\pset foo=bar", + "\\PSet foo=bar", + "\\Pset foo=bar", + "\\PSET foo=bar", + "\\pSET foo=bar" + }; + + String[] invalid = { + null, + "", + "SELECT * FROM SPQR WHERE NAME LIKE '%CAESAR%'", + "\\psetfoo=bar", + "\\pse foo=bar" + }; + + PSet pset = new PSet(); + + for (String input : valid) { + assertTrue(pset.handles(input)); + } + + for (String input : invalid) { + assertFalse(pset.handles(input)); + } + } + + @Test + public void testExec_setValue() throws IOException, SQLException + { + ByteArrayOutputStream baos = new ByteArrayOutputStream(); + + ConnectionMock conn = new ConnectionMock(); + DbDriverMock driver = new DbDriverMock(); + PrintWriter pw = new PrintWriter(baos); + PSet pset = new PSet(); + + String[] on = { "true", "on", "yes", "TRUE", "On", "Yes", "YES", "yEs" }; + String[] off = { "false", "off", "no", "FALSE", "Off", "No", "FaLsE", "nO" }; + + for (String value : on) { + pset.exec(driver, conn, pw, "\\pset suppress_nulls=" + value); + assertTrue(driver.isSuppressNulls()); + + pset.exec(driver, conn, pw, "\\pset Suppress_NULLS=" + value); + assertTrue(driver.isSuppressNulls()); + } + + for (String value : off) { + pset.exec(driver, conn, pw, "\\pset suppress_nulls=" + value); + assertFalse(driver.isSuppressNulls()); + + pset.exec(driver, conn, pw, "\\pset Suppress_NULLS=" + value); + assertFalse(driver.isSuppressNulls()); + } + + for (String value : on) { + pset.exec(driver, conn, pw, "\\PSET SuPPreSS_NuLLs=" + value); + assertTrue(driver.isSuppressNulls()); + } + + pw.close(); + String output = baos.toString(); + assertEquals("", output); + } + + @Test + public void testExec_displayState() throws IOException, SQLException + { + String[] cmds = { "\\pset suppress_nulls", "\\pset suppress_nulls", "\\pset suppress_nulls ", + "\\pset SUPPRESS_NULLS", "\\PsET SUPPress_NuLLS", "\\PSET SUPPRESS_NULLS " }; + + for (String cmd : cmds) { + DbDriverMock driver = new DbDriverMock(); + + driver.setSuppressNulls(true); + String output = doExec(driver, cmd); + assertEquals("suppress_nulls: on\n", output); + + driver.setSuppressNulls(false); + output = doExec(driver, cmd); + assertEquals("suppress_nulls: off\n", output); + } + } + + private String doExec(DbDriverMock driver, String cmd) throws IOException, SQLException + { + ByteArrayOutputStream baos = new ByteArrayOutputStream(); + + ConnectionMock conn = new ConnectionMock(); + PrintWriter pw = new PrintWriter(baos); + PSet pset = new PSet(); + + pset.exec(driver, conn, pw, cmd); + pw.close(); + + String output = baos.toString(); + return output; + } +} diff --git a/src/test/java/net/jaekl/squelch/stmt/TabularTest.java b/src/test/java/net/jaekl/squelch/stmt/TabularTest.java index b72add3..ddfe3d4 100644 --- a/src/test/java/net/jaekl/squelch/stmt/TabularTest.java +++ b/src/test/java/net/jaekl/squelch/stmt/TabularTest.java @@ -15,6 +15,7 @@ import javax.sql.rowset.serial.SerialException; import junit.framework.Assert; +import net.jaekl.squelch.db.DbDriverMock; import net.jaekl.squelch.sql.Column; import net.jaekl.squelch.sql.Row; @@ -104,6 +105,7 @@ public class TabularTest { @Test public void test_printTable_empTable() throws IOException, SQLException { + DbDriverMock driver = new DbDriverMock(); TabularMock tabular = createEmpTable(); try ( @@ -111,7 +113,7 @@ public class TabularTest { PrintWriter pw = new PrintWriter(new OutputStreamWriter(baos, StandardCharsets.UTF_8)); ) { - tabular.printTable(pw, "No rows returned."); + tabular.printTable(driver, pw, "No rows returned."); pw.close(); baos.close(); String actual = baos.toString(); -- 2.30.2