1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package com.ebay.carad.os.vitalsigns.util;
23
24 import java.io.BufferedReader;
25 import java.io.FileInputStream;
26 import java.io.IOException;
27 import java.io.InputStreamReader;
28 import java.sql.Connection;
29 import java.sql.DriverManager;
30 import java.sql.SQLException;
31 import java.text.DateFormat;
32 import java.text.Format;
33 import java.text.MessageFormat;
34 import java.text.SimpleDateFormat;
35 import java.util.Date;
36 import java.util.List;
37 import java.util.Map;
38 import java.util.Properties;
39
40 import org.apache.commons.dbutils.QueryRunner;
41 import org.apache.commons.dbutils.handlers.MapListHandler;
42 import org.apache.log4j.Logger;
43
44 /***
45 * @author Jeremy Thomerson
46 * @version $Id$
47 */
48 public class ReportCreationUtility implements Runnable {
49
50 private static final Logger LOGGER = Logger.getLogger(ReportCreationUtility.class);
51 private BufferedReader inRdr = new BufferedReader(new InputStreamReader(System.in));
52
53 public void run() {
54 String operation = getAnswer("What would you like to do? [create-table, import-report-data]");
55 if ("create-table".equals(operation)) {
56 createTable();
57 } else if ("import-report-data".equals(operation)) {
58 importReportData();
59 } else {
60 System.out.println("Invalid selection.");
61 run();
62 }
63 }
64
65 private void importReportData() {
66 System.out.println("\nSupply information about the DB where the report data will be stored:");
67 Connection conn = getConnection();
68
69 System.out.println("This utility will import data from an existing data source into your ReportData table of the data source you just supplied.");
70 Connection src = conn;
71 if (!getBooleanAnswer("Is the data from the same data source as you supplied for the destination? [y/n]")) {
72 src = getConnection();
73 }
74
75 int id = Integer.parseInt(getAnswer("What is the ID for the report you are importing data for? [integer]"));
76 int mins = Integer.parseInt(getAnswer("How many minutes would you like between each data point? [integer]"));
77 int points = Integer.parseInt(getAnswer("How many data points would you like to record? [integer]"));
78 boolean remove = getBooleanAnswer("Would you like to delete any old data associated with this report? [y/n]");
79
80 System.out.println("Your query will be formatted as a MessageFormat, with the following parameters: ");
81 System.out.println("{0} - start date of period being recorded");
82 System.out.println("{1} - end date of period being recorded");
83 String query = getAnswer("What is your query? [string, using messageformat rules, with params above if needed]\nQuery should return exactly one row with a column named \"data\" as the point of data that will be recorded.");
84
85 QueryRunner runner = new QueryRunner();
86 MapListHandler mlh = new MapListHandler();
87
88 if (remove) {
89 LOGGER.info("deleting data for report " + id);
90 try {
91 runner.update(conn, "DELETE FROM ReportData WHERE reportID = " + id);
92 } catch (SQLException se) {
93 LOGGER.error("error deleting data: " + se.getMessage(), se);
94 throw new RuntimeException("error deleting data: " + se.getMessage(), se);
95 }
96 }
97
98 long now = System.currentTimeMillis();
99 long start = now - (points * (mins * ITimeConstants.MINUTE));
100 long current = now;
101 LOGGER.info("Recording data for report " + id + " from " + new Date(start) + " to " + new Date(now));
102
103 Integer rID = new Integer(id);
104 Format insert = new MessageFormat("INSERT INTO ReportData (logtime, reportid, data) VALUES ({0,number,#}, {1,number,#}, {2,number,#})");
105 while (current > start) {
106 long periodEnd = current;
107 long periodStart = current - (mins * ITimeConstants.MINUTE);
108 Date periodEndDate = new Date(periodEnd);
109 Date periodStartDate = new Date(periodStart);
110 DateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.mmm");
111 String select = MessageFormatUtil.format(query, new Object[] { sqlFormat.format(periodStartDate), sqlFormat.format(periodEndDate) });
112
113 List rows;
114 try {
115 rows = (List) runner.query(src, select, null, mlh);
116 } catch (SQLException se) {
117 LOGGER.error("error selecting data: " + se.getMessage(), se);
118 throw new RuntimeException("error selecting data: " + se.getMessage(), se);
119 }
120 if (rows.size() > 0) {
121 Map row = (Map) rows.get(0);
122 int data = ((Number) row.get("data")).intValue();
123 System.out.println("Data for " + sqlFormat.format(periodStartDate) + " to " + sqlFormat.format(periodEndDate) + " = " + data);
124 try {
125 String insertSQL = insert.format(new Object[] { new Long(current), rID, new Integer(data) });
126 LOGGER.debug("insert: " + insertSQL);
127 runner.update(conn, insertSQL);
128 } catch (SQLException se) {
129 LOGGER.error("error saving data: " + se.getMessage(), se);
130 throw new RuntimeException("error saving data: " + se.getMessage(), se);
131 }
132 }
133
134 current = periodStart;
135 }
136
137 System.out.println("Done recording data.");
138 }
139
140 private void createTable() {
141 Connection conn = getConnection();
142 QueryRunner runner = new QueryRunner();
143
144 String sql = "CREATE TABLE ReportData ( \n" +
145 " rowID INTEGER PRIMARY KEY AUTO_INCREMENT, \n" +
146 " logtime LONG, \n" +
147 " reportid INT, \n" +
148 " data INT \n" +
149 ")";
150
151 try {
152 runner.update(conn, sql);
153
154
155 sql = "CREATE INDEX IX_reportdata_reportid ON ReportData ( reportid )";
156 runner.update(conn, sql);
157
158 } catch (SQLException se) {
159 LOGGER.error("error creating tables: " + se.getMessage(), se);
160 throw new RuntimeException("error creating tables: " + se.getMessage(), se);
161 }
162 System.out.println("Table ReportData created.");
163 }
164
165 private Connection getConnection(String driver, String url, String user, String pass) {
166 try {
167 Class.forName(driver);
168 } catch (ClassNotFoundException cnfe) {
169 LOGGER.error("error loading DB driver: " + cnfe.getMessage(), cnfe);
170 }
171
172 try {
173 return DriverManager.getConnection(url, user, pass);
174 } catch (SQLException se) {
175 LOGGER.error("error connecting to DB: " + se.getMessage(), se);
176 throw new RuntimeException("error connecting to DB: " + se.getMessage(), se);
177 }
178 }
179 private Connection getConnection() {
180 System.out.println("If you have configured a properties file with all of the information you need \nto connect to your databases, you can use it here.");
181 String useFile = getAnswer("Is there a properties file I can use for this connection? [y/n default:y]");
182 if (!useFile.equalsIgnoreCase("n")) {
183 String file = getAnswer("What is the path to the file? [default: ./vitalsigns.properties]");
184 String path = "./vitalsigns.properties";
185 if (!file.trim().equals("")) {
186 path = file;
187 }
188 String name = getAnswer("What is the name of the connection in your file? (i.e. 'dest' if you have 'dest.jdbc.username')");
189 Properties props = new Properties();
190 try {
191 FileInputStream fis = new FileInputStream(path);
192 props.load(fis);
193 } catch(IOException ioe) {
194 throw new RuntimeException("error loading properties: " + ioe.getMessage(), ioe);
195 }
196 return getConnection(
197 props.getProperty(name + ".jdbc.driver_class"),
198 props.getProperty(name + ".jdbc.url"),
199 props.getProperty(name + ".jdbc.username"),
200 props.getProperty(name + ".jdbc.password")
201 );
202 }
203 String driver = getAnswer("What JDBC driver should I use?");
204 String url = getAnswer("What is the URL to the database?");
205 String user = getAnswer("What is the username to use to connect to the DB?");
206 String pass = getAnswer("What is the password to use to connect to the DB?");
207 return getConnection(driver, url, user, pass);
208 }
209
210 private boolean getBooleanAnswer(String message) {
211 return "y".equalsIgnoreCase(getAnswer(message));
212 }
213
214 private String getAnswer(String message) {
215 System.out.println(message);
216 try {
217 return inRdr.readLine();
218 } catch (IOException ioe) {
219 LOGGER.error("error reading input: " + ioe.getMessage(), ioe);
220 }
221 return "";
222 }
223
224 public static void main(String[] args) {
225 new ReportCreationUtility().run();
226 }
227 }