/**
   * Expand a single row to multiple rows. One row per column.
   *
   * @param sql
   * @param rs
   * @return
   * @throws SQLException
   */
  public static ResultList expandSqlResultSet(Sql sql, ResultSet rs) throws SQLException {
    ResultList resList = new ResultList();
    if (rs == null) return resList;
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.addColumn("NAME", false, 1);
    desc.addColumn("VALUE", false, 2);
    resList.setColumnDescriptor(desc);

    java.sql.ResultSetMetaData meta = rs.getMetaData();
    int colCnt = meta.getColumnCount();

    int rowCnt = 0;
    while (rs
        .next()) // change to allow to expand multiple rows, with a row number separator and a row
    // count suffix
    {
      if (rowCnt > 0) {
        ResultRow row = new ResultRow();
        row.addColumn("--- ROW " + (rowCnt + 1) + "---");
        row.addColumn("");
        resList.addRow(row);
      }
      for (int i = 1; i <= colCnt; i++) {
        ResultRow row = new ResultRow();
        if (rowCnt > 0) row.addColumn(meta.getColumnName(i) + "(" + (rowCnt + 1) + ")");
        else row.addColumn(meta.getColumnName(i));

        row.addColumn(rs.getString(i));
        resList.addRow(row);
      }
      rowCnt++;
    }
    return resList;
  }
  /**
   * Construct a ResultList from SQL query ResultSet with at most maxCount rows.
   *
   * @param rs
   * @param maxCount If positive number, at most that number of records will be returned
   * @return
   * @throws SQLException
   */
  public static ResultList fromSqlResultSet(ResultSet rs, int maxCount) throws SQLException {
    logger.fine(new java.util.Date() + ": Process results ...");
    ResultList resList = new ResultList();
    if (rs == null) return resList;
    java.sql.ResultSetMetaData meta = rs.getMetaData();
    int colCnt = meta.getColumnCount();
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.setColumns(new java.util.ArrayList<ColumnInfo>(colCnt));
    for (int i = 1; i <= colCnt; i++) {
      // for now, we only record name
      ColumnInfo col = new ColumnInfo(meta.getColumnName(i));
      int sqlt = meta.getColumnType(i);
      if (sqlt == java.sql.Types.BIGINT
          || sqlt == java.sql.Types.DECIMAL
          || sqlt == java.sql.Types.DOUBLE
          || sqlt == java.sql.Types.FLOAT
          || sqlt == java.sql.Types.INTEGER
          || sqlt == java.sql.Types.NUMERIC
          || sqlt == java.sql.Types.TINYINT
          || sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
      col.setPosition(i);
      desc.getColumns().add(col);
    }
    resList.setColumnDescriptor(desc);
    int rowCnt = 0;
    List<ColumnInfo> cols = desc.getColumns();
    while (rs.next()) {
      // logger.info(new java.util.Date()+": process "+rowCnt+" rows");
      ResultRow row = new ResultRow();
      row.setColumnDescriptor(desc);
      java.util.ArrayList<String> cols2 = new java.util.ArrayList<String>(colCnt);
      row.setColumns(cols2);
      for (int i = 1; i <= colCnt; i++) {
        String val = rs.getString(i);

        if (cols.get(i - 1).isNumberType() && val != null && val.startsWith("."))
          val = "0" + val; // prefix Oracle float number with 0 if starting with "."
        else if (cols.get(i - 1).isNumberType() && val != null && val.startsWith("-."))
          val = val.replace("-.", "-0."); // prefix Oracle float number with 0 if starting with "."

        cols2.add(val);
      }
      resList.addRow(row);
      rowCnt++;
      if (maxCount > 0 && rowCnt >= maxCount) break;
    }
    logger.fine(new java.util.Date() + ": Process results done: " + resList.getRows().size());
    return resList;
  }
  /**
   * This method will flat the rows to columns if Sql is configured that way
   *
   * @param sql
   * @param rs
   * @param maxCount
   * @return
   * @throws SQLException
   */
  public static ResultList flatSqlResultSet(Sql sql, ResultSet rs, int maxCount)
      throws SQLException {
    if (sql.isExpandRow()) return expandSqlResultSet(sql, rs);
    else if (sql == null
        || sql.getFlatKey() == null
        || sql.getFlatKey().trim().length() == 0
        || sql.getFlatValueList().size() == 0) {
      return fromSqlResultSet(rs, maxCount);
    }

    ResultList resList = new ResultList();
    if (rs == null) return resList;
    java.sql.ResultSetMetaData meta = rs.getMetaData();
    int colCnt = meta.getColumnCount();
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.setColumns(new java.util.ArrayList<ColumnInfo>(colCnt));

    Map<String, Integer> typeMap = new HashMap<String, Integer>();
    for (int i = 1; i <= colCnt; i++) {
      typeMap.put(meta.getColumnName(i), meta.getColumnType(i));
    }
    Map<String, Integer> flatkeyIdx = new HashMap<String, Integer>();
    for (int i = 0; i < sql.getFlatValueList().size(); i++) {
      // logger.info("Add "+sql.getFlatValueList().get(i));
      flatkeyIdx.put(sql.getFlatValueList().get(i), i);
    }
    int colIndex = 1;
    // first all keys
    for (String k : sql.getKeyList()) {
      ColumnInfo col = new ColumnInfo(k);
      col.setPosition(colIndex);
      int sqlt = typeMap.get(k);
      if (sqlt == java.sql.Types.BIGINT
          || sqlt == java.sql.Types.DECIMAL
          || sqlt == java.sql.Types.DOUBLE
          || sqlt == java.sql.Types.FLOAT
          || sqlt == java.sql.Types.INTEGER
          || sqlt == java.sql.Types.NUMERIC
          || sqlt == java.sql.Types.TINYINT
          || sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
      desc.getColumns().add(col);
      colIndex++;
    }
    // next all metrics
    for (String s : sql.getFlatValueList()) {
      for (Map.Entry<String, String> e : sql.getMetrics().entrySet()) {
        ColumnInfo col = new ColumnInfo(sql.getFlatValueAbbrMap().get(s) + e.getValue());
        col.setPosition(colIndex);
        int sqlt = typeMap.get(e.getKey());
        if (sqlt == java.sql.Types.BIGINT
            || sqlt == java.sql.Types.DECIMAL
            || sqlt == java.sql.Types.DOUBLE
            || sqlt == java.sql.Types.FLOAT
            || sqlt == java.sql.Types.INTEGER
            || sqlt == java.sql.Types.NUMERIC
            || sqlt == java.sql.Types.TINYINT
            || sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
        desc.getColumns().add(col);
        colIndex++;
      }
    }

    resList.setColumnDescriptor(desc);
    int rowCnt = 0;
    List<ColumnInfo> cols = desc.getColumns();
    String[] prevkeys = new String[sql.getKeyList().size()];
    ResultRow row = null;
    while (rs.next()) {
      String[] newkeys = new String[sql.getKeyList().size()];
      for (int i = 0; i < sql.getKeyList().size(); ++i) {
        newkeys[i] = rs.getString(sql.getKeyList().get(i));
      }
      if (!isSame(prevkeys, newkeys)) // start a new row
      {
        row = new ResultRow();
        row.setColumnDescriptor(desc);
        row.setColumns(new java.util.ArrayList<String>(cols.size()));
        // initialize it
        for (int i = 0; i < cols.size(); i++) row.getColumns().add("");
        resList.addRow(row);
        rowCnt++;
        for (int i = 0; i < sql.getKeyList().size(); ++i) {
          String val = newkeys[i];

          if (cols.get(i).isNumberType() && val != null && val.startsWith("."))
            val = "0" + val; // prefix Oracle float number with 0 if starting with "."				
          else if (cols.get(i).isNumberType() && val != null && val.startsWith("-."))
            val =
                val.replace(
                    "-.", "-0."); // prefix Oracle float number with 0 if starting with "."				
          row.getColumns().set(i, val);
        }
      }
      // now we need get the flat record
      String flatVal = rs.getString(sql.getFlatKey());
      // check its index
      int idx = flatkeyIdx.get(flatVal.toUpperCase());
      int mi = 0;
      for (String s : sql.getMetrics().keySet()) {
        String val = rs.getString(s);
        int colIdx = sql.getKeyList().size() + idx * sql.getMetrics().size() + mi;
        if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("."))
          val = "0" + val; // prefix Oracle float number with 0 if starting with "."
        else if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("-."))
          val = val.replace("-.", "-0."); // prefix Oracle float number with 0 if starting with "."

        row.getColumns().set(colIdx, val);
        mi++;
      }
      prevkeys = newkeys;
      if (maxCount > 0 && rowCnt >= maxCount) break;
    }
    return resList;
  }
  public static ResultList paserBindList(ResultList rList) {
    if (rList != null && rList.getRows().size() > 0) {
      logger.fine("BindController returns " + rList.getRows().size());
      ResultList newList = new ResultList();
      ColumnDescriptor desc = new ColumnDescriptor();
      for (ColumnInfo col : rList.getColumnDescriptor().getColumns()) {
        if ("binds_xml".equalsIgnoreCase(col.getName())) continue;
        desc.addColumn(col.getName(), col.isNumberType(), desc.getColumns().size());
      }
      desc.addColumn("NAME", false, desc.getColumns().size());
      desc.addColumn("POS", false, desc.getColumns().size());
      desc.addColumn("DTYSTR", false, desc.getColumns().size());
      desc.addColumn("MAXLEN", false, desc.getColumns().size());
      desc.addColumn("LEN", false, desc.getColumns().size());
      desc.addColumn("VALUE", false, desc.getColumns().size());
      newList.setColumnDescriptor(desc);

      int idx = rList.getColumnDescriptor().getColumnIndex("BINDS_XML");
      XMLInputFactory inputFactory = XMLInputFactory.newInstance();
      for (ResultRow row : rList.getRows()) {
        // logger.info("binds_xml.idx="+idx+", "+ row.getColumns().size());
        List<BindVariable> binds = null;
        try {
          String bindXml = row.getColumns().get(idx);
          binds = parseBinds(bindXml, inputFactory);
        } catch (Exception iex) {
        }
        if (binds == null || binds.size() == 0) {
          ResultRow newRow = new ResultRow();
          newRow.setColumnDescriptor(desc);
          List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
          for (int i = 0; i < row.getColumns().size(); i++) {
            if (i == idx) continue;
            cols.add(row.getColumns().get(i));
          }
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          newRow.setColumns(cols);
          newList.addRow(newRow);
        } else {
          for (BindVariable var : binds) {
            ResultRow newRow = new ResultRow();
            newRow.setColumnDescriptor(desc);
            List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
            for (int i = 0; i < row.getColumns().size(); i++) {
              if (i == idx) continue;
              cols.add(row.getColumns().get(i));
            }
            cols.add(var.name);
            cols.add(var.pos);
            cols.add(var.dtystr);
            cols.add(var.maxLen);
            cols.add(var.len);
            cols.add(var.value);
            newRow.setColumns(cols);
            newList.addRow(newRow);
          }
        }
      }
      return newList;
    }
    return null;
  }