+ private static final String SELECT_SQL =\r
+ // Note to use the time in the publish_id, use date(from_unixtime(substring(publish_id, 1, 10)))\r
+ // To just use month, substring(from_unixtime(event_time div 1000), 1, 7)\r
+ "select date(from_unixtime(event_time div 1000)) as date, type, feedid, delivery_subid, count(*) as count" +\r
+ " from LOG_RECORDS" +\r
+ " where type = 'pub' or type = 'del'" +\r
+ " group by date, type, feedid, delivery_subid";\r
+ private static final String SELECT_SQL_OLD =\r
+ "select PUBLISH_ID, TYPE, FEEDID, DELIVERY_SUBID from LOG_RECORDS where EVENT_TIME >= ? and EVENT_TIME <= ?";\r
+\r
+ @Override\r
+ public void run() {\r
+ boolean alg1 = true;\r
+ JSONObject jo = new JSONObject();\r
+ long start = System.currentTimeMillis();\r
+ StringBuilder sb = new StringBuilder();\r
+ try {\r
+ DB db = new DB();\r
+ @SuppressWarnings("resource")\r
+ Connection conn = db.getConnection();\r
+ try( PreparedStatement ps = conn.prepareStatement(SELECT_SQL)) {\r
+ try (ResultSet rs = ps.executeQuery()) {\r
+ while (rs.next()) {\r
+ String date = rs.getString("date");\r
+ String type = rs.getString("type");\r
+ int feedid = rs.getInt("feedid");\r
+ int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;\r
+ int count = rs.getInt("count");\r
+ sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");\r
+ }\r
+ }\r
+ }\r
+ db.release(conn);\r
+ } catch (SQLException e) {\r
+ logger.error(e.toString());\r
+ }\r
+ logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
+ try (PrintWriter os = new PrintWriter(outfile)) {\r
+ os.print("date,type,feedid,subid,count\n");\r
+ os.print(sb.toString());\r
+ } catch (FileNotFoundException e) {\r
+ System.err.println("File cannot be written: " + outfile);\r
+ logger.error(e.toString());\r
+ }\r
+ }\r
+\r
+ public void run2() {\r
+ JSONObject jo = new JSONObject();\r
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");\r
+ long start = System.currentTimeMillis();\r
+ try {\r
+ DB db = new DB();\r
+ @SuppressWarnings("resource")\r
+ Connection conn = db.getConnection();\r
+ try(PreparedStatement ps = conn.prepareStatement(SELECT_SQL_OLD)) {\r
+ ps.setLong(1, from);\r
+ ps.setLong(2, to);\r
+ ps.setFetchSize(100000);\r
+ try(ResultSet rs = ps.executeQuery()) {\r
+ while (rs.next()) {\r
+ String id = rs.getString("PUBLISH_ID");\r
+ String date = sdf.format(new Date(getPstart(id)));\r
+ JSONObject datemap = jo.optJSONObject(date);\r
+ if (datemap == null) {\r
+ datemap = new JSONObject();\r
+ jo.put(date, datemap);\r
+ }\r
+ int feed = rs.getInt("FEEDID");\r
+ JSONObject feedmap = datemap.optJSONObject("" + feed);\r
+ if (feedmap == null) {\r
+ feedmap = new JSONObject();\r
+ feedmap.put("pubcount", 0);\r
+ datemap.put("" + feed, feedmap);\r
+ }\r
+ String type = rs.getString("TYPE");\r
+ if (type.equals("pub")) {\r
+ try {\r
+ int n = feedmap.getInt("pubcount");\r
+ feedmap.put("pubcount", n + 1);\r
+ } catch (JSONException e) {\r
+ feedmap.put("pubcount", 1);\r
+ logger.error(e.toString());\r
+ }\r
+ } else if (type.equals("del")) {\r
+ String subid = "" + rs.getInt("DELIVERY_SUBID");\r
+ try {\r
+ int n = feedmap.getInt(subid);\r
+ feedmap.put(subid, n + 1);\r
+ } catch (JSONException e) {\r
+ feedmap.put(subid, 1);\r
+ logger.error(e.toString());\r
+ }\r
+ }\r
+ }\r
+ }\r
+ }\r
+ db.release(conn);\r
+ } catch (SQLException e) {\r
+ logger.error(e.toString());\r
+ }\r
+ logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
+ try {\r
+ PrintWriter os = new PrintWriter(outfile);\r
+ os.println(toHTML(jo));\r
+ os.close();\r
+ } catch (FileNotFoundException e) {\r
+ System.err.println("File cannot be written: " + outfile);\r
+ logger.error(e.toString());\r
+ }\r
+ }\r
+\r
+ private long getPstart(String t) {\r
+ if (t.indexOf('.') > 0)\r
+ t = t.substring(0, t.indexOf('.'));\r
+ return Long.parseLong(t);\r
+ }\r
+\r
+ @SuppressWarnings("unused")\r
+ private static String toHTMLNested(JSONObject jo) {\r
+ StringBuilder s = new StringBuilder();\r
+ s.append("<table>\n");\r
+ s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");\r
+ String[] dates = JSONObject.getNames(jo);\r
+ Arrays.sort(dates);\r
+ for (int i = dates.length - 1; i >= 0; i--) {\r
+ String date = dates[i];\r
+ JSONObject j2 = jo.getJSONObject(date);\r
+ String[] feeds = JSONObject.getNames(j2);\r
+ Arrays.sort(feeds);\r
+ s.append("<tr><td>" + date + "</td><td>");\r
+ s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");\r
+ s.append("<table>\n");\r
+ s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");\r
+ for (String feed : feeds) {\r
+ JSONObject j3 = j2.getJSONObject(feed);\r
+ String[] subs = JSONObject.getNames(j3);\r
+ Arrays.sort(subs);\r
+ s.append("<tr><td>" + feed + "</td>");\r
+ s.append("<td>" + j3.getInt("pubcount") + "</td>");\r
+ int scnt = j3.length() - 1;\r
+ s.append("<td>").append(scnt).append(" Subcription");\r
+ if (scnt > 1)\r
+ s.append("s");\r
+ s.append("<table>\n");\r
+ s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");\r
+ for (String sub : subs) {\r
+ if (!sub.equals("pubcount")) {\r
+ s.append("<tr><td>" + sub + "</td>");\r
+ s.append("<td>" + j3.getInt(sub) + "</td>");\r
+ s.append("</td></tr>\n");\r
+ }\r
+ }\r
+ s.append("</table>\n");\r