src/main/java/net/jaekl/squelch/stmt/Tabular.java

   1 package net.jaekl.squelch.stmt;
   2
   3 import java.io.PrintWriter;
   4 import java.nio.ByteBuffer;
   5 import java.nio.charset.CharacterCodingException;
   6 import java.nio.charset.CodingErrorAction;
   7 import java.nio.charset.StandardCharsets;
   8 import java.sql.SQLException;
   9 import java.sql.SQLFeatureNotSupportedException;
  10 import java.sql.Types;
  11
  12 import javax.xml.bind.DatatypeConverter;
  13
  14 import net.jaekl.squelch.db.DbDriver;
  15 import net.jaekl.squelch.sql.Column;
  16 import net.jaekl.squelch.sql.Row;
  17
  18 // Copyright (C) 2016 by Chris Jaekl
  19 //
  20 // Tabular:  wrapper around a set of tabular data (such as a ResultSet or table's MetaData).
  21 // Includes routines to output the data as a human-friendly table, or as CSV.
  22
  23 abstract public class Tabular {
  24         private static final long BLOB_MAX = 65536;
  25
  26         private static class RowBuffer {
  27                 private final int ROW_BUF_SIZE = 50;
  28
  29                 private Row[] m_rowBuf;
  30                 private int m_pending;
  31
  32                 public RowBuffer() {
  33                         m_rowBuf = new Row[ROW_BUF_SIZE];
  34                         m_pending = 0;
  35                 }
  36
  37                 public void addRow(Row row) {
  38                         assert (m_pending < m_rowBuf.length);
  39                         m_rowBuf[m_pending] = row;
  40                         m_pending++;
  41                 }
  42                 public int getPending() { return m_pending; }
  43                 public Row getRow(int idx) {
  44                         assert (idx >= 0 && idx < m_pending);
  45                         return m_rowBuf[idx];
  46                 }
  47                 public boolean isFull() { return m_pending >= m_rowBuf.length; }
  48         }
  49
  50         abstract Column[] getCols() throws SQLException;
  51         abstract Row getNext() throws SQLException;
  52
  53         // Returns the number of (data) rows that were output
  54         public int printTable(DbDriver driver, PrintWriter pw, String noRowsMessage)
  55                 throws SQLException
  56         {
  57                 int rowCount = 0;
  58                 Column[] cols = getCols();
  59                 RowBuffer rowBuf;
  60
  61                 // Initialize colWidths to the widths of the column names
  62                 int[] colWidths = initColWidthsFromColNames(cols);
  63
  64                 // Examine and buffer up to ROW_BUF_SIZE rows, adjusting (widening) colWidths where needed
  65                 rowBuf = bufferRows(driver, colWidths);
  66                 boolean[] suppressed = suppressNulls(driver, rowBuf, cols);
  67
  68                 int pending = rowBuf.getPending();
  69                 if (pending > 0) {
  70                         writeHeader(pw, cols, colWidths, suppressed);
  71                         writeRowBuffer(pw, rowBuf, colWidths, suppressed);
  72                         rowCount = pending;
  73                 }
  74
  75                 rowBuf = bufferRows(driver, colWidths);
  76                 pending = rowBuf.getPending();
  77                 while (pending > 0) {
  78                         if (driver.isSet(DbDriver.SUPPRESS_NULLS)) {
  79                                 writeDivider(pw, colWidths, suppressed);
  80                                 pw.println("Row limit for suppress_nulls has been reached; output may have been truncated.");
  81                                 break;
  82                         }
  83                         writeRowBuffer(pw, rowBuf, colWidths, suppressed);
  84                         rowCount += pending;
  85                         rowBuf = bufferRows(driver, colWidths);
  86                         pending = rowBuf.getPending();
  87                 }
  88
  89                 if (rowCount > 0) {
  90                         writeDivider(pw, colWidths, suppressed);
  91                         // TODO:  Implement a StringTable for i18n
  92                         pw.println("" + rowCount + " row(s) returned.");
  93                 }
  94                 else {
  95                         pw.println(noRowsMessage);
  96                 }
  97
  98                 pw.flush();
  99
 100                 return rowCount;
 101         }
 102
 103         // Returns the number of (data) rows that were output
 104         public int printCsv(PrintWriter pw) throws SQLException {
 105                 int rowCount = 0;
 106                 Column[] cols = getCols();
 107                 Row row = null;
 108
 109                 StringBuilder sb = new StringBuilder();
 110                 boolean firstCol = true;
 111                 for (int idx = 1; idx <= cols.length; ++idx) {
 112                         if (firstCol) {
 113                                 firstCol = false;
 114                         }
 115                         else {
 116                                 sb.append(",");
 117                         }
 118                         sb.append("" + cols[idx - 1].getLabel());
 119                 }
 120                 sb.append("\n");
 121                 String header = sb.toString();
 122
 123                 while (null != (row = getNext())) {
 124                         if (null != header) {
 125                                 pw.print(header);
 126                                 header = null;
 127                         }
 128
 129                         firstCol = true;
 130                         for (int idx = 1; idx <= cols.length; ++idx) {
 131                                 if (firstCol) {
 132                                         firstCol = false;
 133                                 }
 134                                 else {
 135                                         pw.print(",");
 136                                 }
 137                                 pw.print("" + row.getValue(idx));
 138                         }
 139                         pw.println("");
 140                         rowCount++;
 141                 }
 142                 pw.flush();
 143
 144                 return rowCount;
 145         }
 146
 147         // Examine and buffer up to rowBuf.length rows.
 148         // Returns the number of actual rows that were buffered (zero if no more rows are available).
 149         RowBuffer bufferRows(DbDriver driver, int[] colWidths) throws SQLException
 150         {
 151                 RowBuffer rowBuf = new RowBuffer();
 152
 153                 while (!rowBuf.isFull()) {
 154                         Row row = getNext();
 155                         if (null == row) {
 156                                 // No more rows available
 157                                 return rowBuf;
 158                         }
 159
 160                         boolean allColsNull = true;
 161                         for (int idx = 1; idx <= colWidths.length; ++idx) {
 162                                 if (null != row.getValue(idx)) {
 163                                         allColsNull = false;
 164                                 }
 165                         }
 166
 167                         if ((!allColsNull) || (!driver.isSet(DbDriver.SUPPRESS_NULLS)))
 168                         {
 169                                 rowBuf.addRow(row);
 170
 171                                 // Check whether all values in this row will fit in the current column widths
 172                                 for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 173                                         int width = stringify(row.getValue(colIdx + 1)).length();
 174                                         if (width > colWidths[colIdx]) {
 175                                                 // Widen the column to fit this value
 176                                                 colWidths[colIdx] = width;
 177                                         }
 178                                 }
 179                         }
 180                 }
 181
 182                 return rowBuf;
 183         }
 184
 185         String centrePad(String value, int desiredWidth)
 186         {
 187                 if (null == value) {
 188                         return centrePad("NULL", desiredWidth);
 189                 }
 190                 if (value.length() >= desiredWidth) {
 191                         return value;
 192                 }
 193                 int left = (desiredWidth - value.length()) / 2;
 194                 int right = desiredWidth - value.length() - left;
 195                 return repChar(' ', left) + value + repChar(' ', right);
 196         }
 197
 198         int[] initColWidthsFromColNames(Column[] cols)
 199         {
 200                 int[] widths = new int[cols.length];
 201
 202                 for (int idx = 0; idx < cols.length; ++idx) {
 203                         widths[idx] = cols[idx].getLabel().length();
 204                 }
 205
 206                 return widths;
 207         }
 208
 209         Class<?> classForSqlType(int sqlType)
 210         {
 211                 switch(sqlType)
 212                 {
 213                 case Types.BOOLEAN:
 214                         return Boolean.class;
 215                 case Types.CHAR:
 216                         return Character.class;
 217                 case Types.DATE:
 218                 case Types.TIME:
 219                 case Types.TIMESTAMP:
 220                         return java.util.Date.class;
 221                 case Types.DECIMAL:
 222                 case Types.DOUBLE:
 223                 case Types.FLOAT:
 224                 case Types.NUMERIC:
 225                 case Types.REAL:
 226                         return Double.class;
 227                 case Types.INTEGER:
 228                         return Long.class;
 229                 case Types.ROWID:
 230                 case Types.SMALLINT:
 231                         return Integer.class;
 232                 case Types.TINYINT:
 233                         return Short.class;
 234                 case Types.LONGNVARCHAR:
 235                 case Types.LONGVARCHAR:
 236                 case Types.NCHAR:
 237                 case Types.NVARCHAR:
 238                 case Types.VARCHAR:
 239                         return String.class;
 240                 }
 241                 return Object.class;
 242         }
 243
 244         String repChar(char chr, int times)
 245         {
 246                 StringBuffer sb = new StringBuffer();
 247                 for (int idx = 0; idx < times; ++idx) {
 248                         sb.append(chr);
 249                 }
 250                 return sb.toString();
 251         }
 252
 253         String stringify(Object obj) throws SQLException
 254         {
 255                 if (obj instanceof java.sql.Blob) {
 256                         java.sql.Blob blob = null;
 257
 258                         try {
 259                                 blob = (java.sql.Blob)obj;
 260                                 long length = blob.length();
 261                                 if (length > BLOB_MAX) {
 262                                         length = BLOB_MAX;
 263                                 }
 264                                 byte[] content = blob.getBytes(1, (int)length);
 265
 266                                 try {
 267                                         // Assume that the BLOB is actually UTF-8 text, and attempt to return it that way.
 268                                         // This happens to work well for the databases that I deal with regularly.
 269                                         // Other users may want to change this assumption here...
 270                                         return StandardCharsets.UTF_8.newDecoder().onMalformedInput(CodingErrorAction.REPORT)
 271                                                                                                                           .onUnmappableCharacter(CodingErrorAction.REPORT)
 272                                                                                                                           .decode(ByteBuffer.wrap(content))
 273                                                                                                                           .toString();
 274                                 }
 275                                 catch (CharacterCodingException exc) {
 276                                         // If we get here, then the BLOB's content is not valid UTF-8.
 277                                         // This may be because it's in a different character set, or because it's non-text
 278                                         // (e.g., a bitmap image).
 279                                         // We'll hex-dump it instead.
 280                                         return DatatypeConverter.printHexBinary(content);
 281                                 }
 282                         }
 283                         finally {
 284                                 if (null != blob) {
 285                                         try {
 286                                                 blob.free();
 287                                         }
 288                                         catch (UnsupportedOperationException | SQLFeatureNotSupportedException exc) {
 289                                                 // free() was only a hint; if the hint is not welcome, then that's OK.
 290                                         }
 291                                 }
 292                         }
 293                 }
 294
 295                 return "" + obj;
 296         }
 297
 298         int stringWidth(Object obj)
 299         {
 300                 if (obj instanceof java.sql.Blob) {
 301                         return 1;
 302                 }
 303                 return ("" + obj).length();
 304         }
 305
 306         boolean[] suppressNulls(DbDriver driver, RowBuffer rowBuf, Column[] cols)
 307         {
 308                 boolean[] result = new boolean[cols.length];
 309
 310                 if (  !(driver.isSet(DbDriver.SUPPRESS_NULLS))
 311                    ||  (rowBuf.getPending() < 1) )
 312                 {
 313                         // Null-suppression is turned off, or
 314                         // there are no data rows,
 315                         // so do not suppress any columns.
 316                         for (int colIdx = 0; colIdx < cols.length; ++colIdx) {
 317                                 result[colIdx] = false;
 318                         }
 319                         return result;
 320                 }
 321
 322                 // Start with the assumption that we'll suppress all columns
 323                 for (int colIdx = 0; colIdx < cols.length; ++colIdx) {
 324                         result[colIdx] = true;
 325                 }
 326
 327                 for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) {
 328                         Row row = rowBuf.getRow(rowIdx);
 329                         for (int colIdx = 1; colIdx <= cols.length; ++colIdx) {
 330                                 if (null != row.getValue(colIdx)) {
 331                                         // This column has data, so do not suppress it
 332                                         result[colIdx - 1] = false;
 333                                 }
 334                         }
 335                 }
 336
 337                 return result;
 338         }
 339
 340         void writeDivider(PrintWriter pw, int[] colWidths, boolean[] suppressed) {
 341                 for (int idx = 0; idx < colWidths.length; ++idx) {
 342                         if (!suppressed[idx]) {
 343                                 pw.print("+" + repChar('-', colWidths[idx] + 2));
 344                         }
 345                 }
 346                 pw.println("+");
 347         }
 348
 349         void writeHeader(PrintWriter pw, Column[] cols, int[] colWidths, boolean[] suppressed) {
 350                 writeDivider(pw, colWidths, suppressed);
 351
 352                 for (int idx = 0; idx < cols.length; ++idx) {
 353                         if (!suppressed[idx]) {
 354                                 Column col = cols[idx];
 355                                 pw.print("| " + centrePad(col.getLabel(), colWidths[idx]) + " ");
 356                         }
 357                 }
 358                 pw.println("|");
 359
 360                 writeDivider(pw, colWidths, suppressed);
 361         }
 362
 363         void writeRowBuffer(PrintWriter pw, RowBuffer rowBuf, int[] colWidths, boolean[] suppressed) throws SQLException {
 364
 365                 for (int rowIdx = 0; rowIdx < rowBuf.getPending(); ++rowIdx) {
 366                         Row row = rowBuf.getRow(rowIdx);
 367                         for (int colIdx = 0; colIdx < colWidths.length; ++colIdx) {
 368                                 if (!suppressed[colIdx]) {
 369                                         Object obj = row.getValue(colIdx + 1);
 370                                         String value = stringify(obj);
 371                                         int width = stringWidth(value);
 372                                         String padding = repChar(' ', colWidths[colIdx] - width);
 373                                         pw.print("| " + value + padding + " ");
 374                                 }
 375                         }
 376                         pw.println("|");
 377                 }
 378         }
 379 }