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