/**
* Used for text output
*
* @param rList
*/
public static void updateColumnWidth(ResultList rList) {
if (rList == null || rList.getColumnDescriptor() == null || rList.getRows().size() == 0) return;
List<ColumnInfo> cols = rList.getColumnDescriptor().getColumns();
for (ColumnInfo col : cols) {
col.setMaxLength(col.getName().length());
}
for (ResultRow row : rList.getRows()) {
List<String> l = row.getColumns();
for (int i = 0; i < l.size(); i++) {
String s = l.get(i);
if (s != null && s.length() > cols.get(i).getMaxLength())
cols.get(i).setMaxLength(s.length());
}
}
}
/**
* Display diff data by key. Metrics will be displayed as {metric}_a and {metric}_b
*
* @param rsA
* @param rsB
* @param qpsA
* @param qpsB
* @param status
* @param message
* @return
*/
public static String toJSONDiffFlatString(
boolean diffOnly,
Sql sqlA,
Sql sqlB,
ResultList rsA,
ResultList rsB,
QueryParameters qpsA,
QueryParameters qpsB,
int status,
String message) {
ResultList combined = ResultListMerger.mergeByKey(sqlA, sqlB, rsA, rsB, diffOnly);
StringBuilder sb = new StringBuilder();
sb.append("{\"resp\":{\"status\":").append(status); // start and status
sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line
// requests
sb.append(",\"request\":{");
if (qpsA != null) // parameter from a
{
sb.append("\"a_group\":\"").append(qpsA.getGroup()).append("\"");
sb.append(",\"a_host\":\"").append(qpsA.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"a_sql\":\"").append(qpsA.getSql()).append("\"");
for (Map.Entry<String, String> e : qpsA.getSqlParams().entrySet()) {
sb.append(",\"a_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
if (qpsB != null) sb.append(",");
}
if (qpsB != null) // parameter from b
{
sb.append("\"b_group\":\"").append(qpsB.getGroup()).append("\"");
sb.append(",\"b_host\":\"").append(qpsB.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"b_sql\":\"").append(qpsB.getSql()).append("\"");
for (Map.Entry<String, String> e : qpsB.getSqlParams().entrySet()) {
sb.append(",\"b_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
}
sb.append("}\r\n"); // end of request
int cnt = combined != null ? combined.getRows().size() : 0;
List<ColumnInfo> cols = combined != null ? combined.getColumnDescriptor().getColumns() : null;
sb.append(",\"results\":{");
sb.append("\"total\":\"").append(cnt).append("\","); // end of count
// column names
sb.append("\"columns\":["); // start of column names
if (cols != null) {
boolean first = true;
for (int i = 0; i < cols.size(); i++) {
if (!first) sb.append(",");
else first = false;
sb.append("\"").append(cols.get(i).getName() + "\"");
}
}
sb.append("],\r\n"); // end of column names
boolean firstRow = true;
sb.append("\"results\":["); // start of the result list
if (combined != null)
for (ResultRow row : combined.getRows()) {
if (!firstRow) sb.append(",");
int len = row.getColumns().size();
boolean first = true;
sb.append("{"); // start of one row
for (int i = 0; i < len; i++) {
if (!first) sb.append(",");
sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
if (cols.get(i).isNumberType()) {
if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
sb.append("\"\"");
else {
if (row.getColumns().get(i).startsWith("-."))
sb.append(row.getColumns().get(i).replace("-.", "-0."));
else sb.append(escapeJson(row.getColumns().get(i)));
}
} else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
first = false;
}
sb.append("}\r\n"); // end of one row
firstRow = false;
}
sb.append("]"); // end of result list
sb.append("}"); // end of outer results
sb.append("}"); // end of resp
sb.append("}"); // end of all
return sb.toString();
}
public static String toJSONDiffListString(
ResultList rsA,
ResultList rsB,
QueryParameters qpsA,
QueryParameters qpsB,
int status,
String message) {
StringBuilder sb = new StringBuilder();
sb.append("{\"resp\":{\"status\":").append(status); // start and status
sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line
// requests
sb.append(",\"request\":{");
if (qpsA != null) {
sb.append("\"a_group\":\"").append(qpsA.getGroup()).append("\"");
sb.append(",\"a_host\":\"").append(qpsA.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"a_sql\":\"").append(qpsA.getSql()).append("\"");
for (Map.Entry<String, String> e : qpsA.getSqlParams().entrySet()) {
sb.append(",\"a_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
if (qpsB != null) sb.append(",");
}
if (qpsB != null) {
sb.append("\"b_group\":\"").append(qpsB.getGroup()).append("\"");
sb.append(",\"b_host\":\"").append(qpsB.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"b_sql\":\"").append(qpsB.getSql()).append("\"");
for (Map.Entry<String, String> e : qpsB.getSqlParams().entrySet()) {
sb.append(",\"b_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
}
sb.append("}\r\n");
int cnt = 0;
List<ColumnInfo> cols = null;
if (rsA != null || rsB != null) {
if (rsA != null) {
cnt += rsA.getRows().size();
cols = rsA.getColumnDescriptor().getColumns();
}
if (rsB != null) {
cnt += rsB.getRows().size();
if (cols == null) cols = rsB.getColumnDescriptor().getColumns();
}
sb.append(",\"results\":{");
sb.append("\"total\":\"").append(cnt).append("\",");
// column names
sb.append("\"columns\":[\"DB\"");
{
for (int i = 0; i < cols.size(); i++) {
sb.append(",");
sb.append("\"").append(cols.get(i).getName() + "\"");
}
}
sb.append("],\r\n");
sb.append("\"results\":[");
}
boolean firstRow = true;
if (rsA != null) {
for (ResultRow row : rsA.getRows()) {
if (!firstRow) sb.append(",");
int len = row.getColumns().size();
sb.append("{\"DB\":\"A\"");
for (int i = 0; i < len; i++) {
sb.append(",");
sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
if (cols.get(i).isNumberType()) {
if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
sb.append("\"\"");
else sb.append(escapeJson(row.getColumns().get(i)));
} else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
}
sb.append("}\r\n");
firstRow = false;
}
}
if (rsB != null) {
for (ResultRow row : rsB.getRows()) {
if (!firstRow) sb.append(",");
int len = row.getColumns().size();
sb.append("{\"DB\":\"B\"");
for (int i = 0; i < len; i++) {
sb.append(",");
sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
if (cols.get(i).isNumberType()) {
if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
sb.append("\"\"");
else sb.append(escapeJson(row.getColumns().get(i)));
} else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
}
sb.append("}\r\n");
firstRow = false;
}
}
if (rsA != null || rsB != null) sb.append("]}");
sb.append("}}");
return sb.toString();
}
/**
* We will filter data by the provided columns. Also make all column names in uppercase. If no
* filter provided, only make column names uppercase
*
* @param rs
* @param qps
* @param status
* @param message
* @param subset
* @return
*/
public static String toJSONStringSubset(
ResultList rs, QueryParameters qps, int status, String message, String[] subset) {
HashSet<String> ms = new HashSet<String>();
if (subset != null) for (int i = 0; i < subset.length; i++) ms.add(subset[i]);
boolean filter = ms.size() > 0;
StringBuilder sb = new StringBuilder();
sb.append("{\"resp\":{\"status\":").append(status); // start and status
if (rs != null && rs.getTotalResponseTime() > 0) {
sb.append(",\"totalTime\":\"").append(rs.getTotalResponseTime()).append("ms\"");
sb.append(",\"execTime\":\"").append(rs.getTotalExecutionTime()).append("ms\"");
sb.append(",\"fetchTime\":\"").append(rs.getTotalFetchTime()).append("ms\"");
}
sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line
// requests
if (qps != null) {
sb.append(",\"request\":{");
sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"sql\":\"").append(qps.getSql()).append("\"");
for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
sb.append("}\r\n");
}
if (rs != null) {
List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();
sb.append(",\"results\":{");
sb.append("\"total\":\"").append(rs.getRows().size()).append("\",");
// column names
sb.append("\"columns\":[");
{
boolean first = true;
for (int i = 0; i < cols.size(); i++) {
if (filter && !ms.contains(cols.get(i).getName())) continue;
if (!first) sb.append(",");
first = false;
sb.append("\"").append(cols.get(i).getName().toUpperCase() + "\"");
}
}
sb.append("],\r\n");
boolean firstRow = true;
sb.append("\"results\":[");
for (ResultRow row : rs.getRows()) {
if (!firstRow) sb.append(",");
int len = row.getColumns().size();
boolean first = true;
sb.append("{");
for (int i = 0; i < len; i++) {
if (filter && !ms.contains(cols.get(i).getName())) continue;
if (!first) sb.append(",");
sb.append("\"").append(escapeJson(cols.get(i).getName().toUpperCase())).append("\":");
if (cols.get(i).isNumberType()) {
if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
sb.append("\"\"");
else sb.append(escapeJson(row.getColumns().get(i)));
} else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
first = false;
}
sb.append("}\r\n");
firstRow = false;
}
sb.append("]}");
}
sb.append("}}");
return sb.toString();
}
/**
* JSON Output metrics with multiple rows, with each row represent different entity such as disk,
* username, table anme, etc.
*
* @param rs
* @param keyColumn
* @param groupByColumns columns used to group metrics, such as ["SNAP_ID", "TS"]
* @param metricName we only handle a single metric for now
* @param qps
* @param status
* @param message
* @return
*/
public static String toMetricsJSONStringWithMultiRowsKeys(
ResultList rs,
String keyColumn,
String[] groupByColumns,
String metricName,
QueryParameters qps,
int status,
String message) {
String[] keys = getDistinctKeys(keyColumn, rs);
if (keys == null
|| keys.length == 0
|| groupByColumns == null
|| groupByColumns.length
== 0) // we don't want to bother about the case without multiple key values
return toJSONString(rs, qps, status, message);
Map<String, String> keyMap = new HashMap<String, String>(keys.length);
for (int i = 0; i < keys.length; i++) keyMap.put(keys[i], "k" + i);
StringBuilder sb = new StringBuilder();
sb.append("{\"resp\":{\"status\":").append(status); // start and status
if (rs != null && rs.getTotalResponseTime() > 0) {
sb.append(",\"totalTime\":\"").append(rs.getTotalResponseTime()).append("ms\"");
sb.append(",\"execTime\":\"").append(rs.getTotalExecutionTime()).append("ms\"");
sb.append(",\"fetchTime\":\"").append(rs.getTotalFetchTime()).append("ms\"");
}
sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line
// requests
if (qps != null) {
sb.append(",\"request\":{");
sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
// sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
sb.append(",\"sql\":\"").append(qps.getSql()).append("\"");
for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
sb.append("}\r\n");
}
if (rs != null) {
List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();
sb.append(",\"results\":{");
sb.append("\"total\":\"").append(rs.getRows().size()).append("\",");
// column names
sb.append("\"columns\":[");
{
boolean first = true;
for (int i = 0; i < cols.size(); i++) {
// ignore key column
if (keyColumn.equalsIgnoreCase(cols.get(i).getName())) continue;
if (!first) sb.append(",");
first = false;
sb.append("\"").append(cols.get(i).getName() + "\"");
}
}
sb.append("],\r\n");
// add keys
sb.append("\"keys\":[");
for (int i = 0; i < keys.length; i++) {
if (i > 0) sb.append(",");
sb.append("{")
.append("\"name\":\"")
.append(keys[i])
.append("\", ")
.append("\"shortName\":\"")
.append("k")
.append(i)
.append("\"")
.append("}");
}
sb.append("],\r\n");
if (rs.getCustomObjects() != null && rs.getCustomObjects().size() > 0) {
for (Map.Entry<String, CustomResultObject> e : rs.getCustomObjects().entrySet()) {
sb.append("\"").append(e.getKey()).append("\":");
sb.append(e.getValue().getValueJsonString());
sb.append(",\r\n");
}
}
boolean firstRow = true;
String[] prevGrpKey = new String[groupByColumns.length];
String[] newGrpKey = new String[groupByColumns.length];
int keyIdx = rs.getColumnIndex(keyColumn);
int mtrIdx = rs.getColumnIndex(metricName);
int[] grpKeyIdx = new int[groupByColumns.length];
for (int i = 0; i < groupByColumns.length; i++) {
prevGrpKey[i] = null;
newGrpKey[i] = null;
grpKeyIdx[i] = rs.getColumnIndex(groupByColumns[i]); // don't expect invalid
}
sb.append("\"results\":[");
Map<String, String> valueMap = new TreeMap<String, String>(); // to store values temporarily
for (ResultRow row : rs.getRows()) {
for (int i = 0; i < groupByColumns.length; i++) {
newGrpKey[i] = row.getColumns().get(grpKeyIdx[i]);
}
// if we have a new group by key, we should generate output for previous one
// and clear the temp storage
if (valueMap.size() > 0 && isDiff(prevGrpKey, newGrpKey)) {
if (!firstRow) sb.append(",");
firstRow = false;
sb.append("{");
for (int i = 0; i < groupByColumns.length; i++) {
sb.append("\"")
.append(groupByColumns[i])
.append("\":")
.append(prevGrpKey[i])
.append(",");
}
sb.append("\"").append(metricName).append("\":{");
boolean firstKey = true;
for (Map.Entry<String, String> e : valueMap.entrySet()) {
if (e.getValue() == null) continue;
String jkey = keyMap.get(e.getKey());
if (!firstKey) sb.append(",");
firstKey = false;
sb.append("\"").append(jkey).append("\":").append(e.getValue());
}
sb.append("}"); // end metric
sb.append("}"); // end the row
valueMap.clear();
}
// otherwise, just store it
valueMap.put(row.getColumns().get(keyIdx), row.getColumns().get(mtrIdx));
for (int i = 0; i < groupByColumns.length; i++) {
prevGrpKey[i] = newGrpKey[i];
}
}
// todo: output last group
if (!firstRow) sb.append(",");
{
sb.append("{");
for (int i = 0; i < groupByColumns.length; i++) {
sb.append("\"").append(groupByColumns[i]).append("\":").append(newGrpKey[i]).append(",");
}
sb.append("\"").append(metricName).append("\":{");
boolean firstKey = true;
for (Map.Entry<String, String> e : valueMap.entrySet()) {
if (e.getValue() == null) continue;
String jkey = keyMap.get(e.getKey());
if (!firstKey) sb.append(",");
firstKey = false;
sb.append("\"").append(jkey).append("\":").append(e.getValue());
}
sb.append("}"); // end metric
sb.append("}"); // end the row
}
sb.append("]}");
}
sb.append("}}");
return sb.toString();
}
public static String toMultiListJSONString(
LinkedHashMap<String, ResultList> resultMap,
QueryParameters qps,
int status,
String message) {
StringBuilder sb = new StringBuilder();
sb.append("{\"resp\":{\"status\":").append(status); // start and status
sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line
// requests
if (qps != null) {
sb.append(",\"request\":{");
sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
}
sb.append("}");
}
// for(String key: resultMap.keySet())
for (Map.Entry<String, ResultList> e : resultMap.entrySet()) {
String key = e.getKey();
ResultList rs = e.getValue();
List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();
sb.append(",\"").append(key).append("\":{");
sb.append("\"total\":\"").append(rs.getRows().size()).append("\",");
// column names
sb.append("\"columns\":[");
{
boolean first = true;
for (int i = 0; i < cols.size(); i++) {
if (!first) sb.append(",");
first = false;
sb.append("\"").append(cols.get(i).getName() + "\"");
}
}
sb.append("],");
boolean firstRow = true;
sb.append("\"results\":[");
for (ResultRow row : rs.getRows()) {
if (!firstRow) sb.append(",");
int len = row.getColumns().size();
boolean first = true;
sb.append("{");
for (int i = 0; i < len; i++) {
if (!first) sb.append(",");
sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
if (cols.get(i).isNumberType()) {
if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
sb.append("\"\"");
else sb.append(escapeJson(row.getColumns().get(i)));
} else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
first = false;
}
sb.append("}");
firstRow = false;
}
sb.append("]}");
}
sb.append("}}");
return sb.toString();
}
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;
}