From: HelenaLydon Date: Fri, 30 Aug 2019 14:38:50 +0000 (+0000) Subject: [DR] Remove DR code smells - additional X-Git-Tag: 2.1.3~5 X-Git-Url: https://gerrit.onap.org/r/gitweb?p=dmaap%2Fdatarouter.git;a=commitdiff_plain;h=cfad2b32bc8badb2e0a6e691920574e2b209c143 [DR] Remove DR code smells - additional Change-Id: Ibd27fc27530f7762809d5a6bd73a82c7973c74f4 Issue-ID: DMAAP-1195 Signed-off-by: HelenaLydon --- diff --git a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java index fcdca0a3..aba230b9 100755 --- a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java +++ b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java @@ -63,6 +63,21 @@ public class StatisticsServlet extends BaseServlet { private static final String FMT2 = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"; public static final String FEEDID = "FEEDID"; + //sql Strings + private static final String SQL_SELECT_NAME = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in("; + private static final String SQL_FEED_ID = ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, "; + private static final String SQL_SELECT_COUNT = "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in("; + private static final String SQL_TYPE_PUB = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,"; + private static final String SQL_SELECT_SUM = "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in("; + private static final String SQL_PUBLISH_LENGTH = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,"; + private static final String SQL_SUBSCRIBER_URL = " sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,"; + private static final String SQL_SUB_ID = " e.DELIVERY_SUBID as SUBID, "; + private static final String SQL_DELIVERY_TIME = " e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, "; + private static final String SQL_AVERAGE_DELAY = " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS"; + private static final String SQL_JOIN_RECORDS = " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN ("; + private static final String SQL_STATUS_204 = " AND m.STATUS=204 AND e.RESULT=204 "; + private static final String SQL_GROUP_SUB_ID = " group by SUBID"; + /** * DELETE a logging URL -- not supported. @@ -313,20 +328,12 @@ public class StatisticsServlet extends BaseServlet { if (endTime == null && startTime == null) { - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,(SELECT SUM(content_length)" - + " FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + " e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS" - + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204 group by SUBID"; + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + + feedids + ") " + subid + + SQL_STATUS_204 + SQL_GROUP_SUB_ID; return sql; } else if (startTime != null && endTime == null) { @@ -336,22 +343,12 @@ public class StatisticsServlet extends BaseServlet { long currentTimeInMilli = cal.getTimeInMillis(); long compareTime = currentTimeInMilli - inputTimeInMilli; - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED," - + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + " e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay " - + "FROM LOG_RECORDS e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time>=" - + compareTime + " group by SUBID"; - + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + + feedids + ") " + subid + + SQL_STATUS_204 + " and e.event_time>=" + compareTime + SQL_GROUP_SUB_ID; return sql; } else { @@ -362,23 +359,11 @@ public class StatisticsServlet extends BaseServlet { long startInMillis = startDate.getTime(); long endInMillis = endDate.getTime(); - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED," - + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH," - + "SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + "e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS" - + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ")" + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time between " + startInMillis - + " and " + endInMillis + " group by SUBID"; - + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL + + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + feedids + ")" + subid + SQL_STATUS_204 + +" and e.event_time between " + startInMillis + " and " + endInMillis + SQL_GROUP_SUB_ID; return sql; }