001: /*
002: * Copyright (C) Jahia Ltd. All rights reserved.
003: *
004: * This software is published under the terms of the Jahia Open Software
005: * License version 1.1, a copy of which has been included with this
006: * distribution in the LICENSE.txt file.
007: */
008: package org.jahia.sqlprofiler.gui;
009:
010: import javax.swing.table.*;
011: import java.util.*;
012: import org.jahia.sqlprofiler.QueryEntry;
013: import java.text.DateFormat;
014: import javax.swing.JTable;
015: import java.awt.event.*;
016: import javax.swing.*;
017: import org.jahia.sqlprofiler.QueryStatistics;
018: import java.io.StringWriter;
019: import java.io.PrintWriter;
020: import org.jahia.sqlprofiler.QueryStatEntry;
021: import java.text.NumberFormat;
022: import java.io.FileWriter;
023: import java.io.IOException;
024: import java.text.SimpleDateFormat;
025:
026: /**
027: * <p>Title: SQL Profiler</p>
028: * <p>Description: </p>
029: * <p>Copyright: Copyright (c) 2003</p>
030: * <p>Company: Jahia Ltd</p>
031: * @author Serge Huber
032: * @author Jean-Philippe Valentin
033: * @version 1.0
034: *
035: * 11/14/2003 : Add save table profile statistics as report file CSV by Jean-Philippe VALENTIN (JPV)
036: */
037:
038: public class ProfileStatementTableModel extends AbstractTableModel
039: implements Comparator {
040:
041: private QueryStatistics queryStats = new QueryStatistics();
042:
043: private static final org.apache.log4j.Logger logger = org.apache.log4j.Logger
044: .getLogger(ProfileStatementTableModel.class);
045:
046: private LoggerTableModel loggerModel;
047: private JLabel statementCountValueLabel = null;
048:
049: private ArrayList queryEntries = new ArrayList();
050: private SortedSet sortedQueryEntries = new TreeSet();
051: protected int currCol = 0;
052: protected Vector ascendCol = new Vector(); // this vector stores the state (ascending or descending) of each column
053: protected Integer one = new Integer(1);
054: protected Integer minusOne = new Integer(-1);
055:
056: private long lowestQueryTime = Long.MAX_VALUE;
057: private long highestQueryTime = Long.MIN_VALUE;
058: private long totalQueryTime = 0;
059:
060: // String logEntry = now + "|"+ elapsed + "|"+(connectionId==-1 ? "" : String.valueOf(connectionId))+"|"+category+"|"+prepared+"|"+sql;
061: private static final String[] COLUMN_NAMES = { "Date", "Time[ms]",
062: "ID", "Category", "Prepared", "SQL" };
063:
064: private static final SimpleDateFormat DATE_FORMATTER = new SimpleDateFormat(
065: "yyyy.MM.dd hh:mm:ss.SSS");
066: private javax.swing.JLabel statementTimeValueLabel;
067: private ProfileResultTableModel profileResultModel;
068:
069: public ProfileStatementTableModel(LoggerTableModel loggerModel) {
070: this .loggerModel = loggerModel;
071: for (int i = 0; i < COLUMN_NAMES.length; i++) {
072: if (i == 0) {
073: ascendCol.add(minusOne);
074: } else {
075: ascendCol.add(one);
076: }
077: }
078: }
079:
080: public void processP6Event(String eventText) {
081: try {
082: QueryEntry queryEntry = new QueryEntry(eventText);
083: addQuery(queryEntry);
084: queryStats.processSQL(queryEntry);
085: } catch (NoSuchElementException nsee) {
086: logger.error("Error while parsing p6spy format", nsee);
087: }
088: }
089:
090: public void displayOccurenceStats(boolean displayQueries) {
091: StringWriter strWriter = new StringWriter();
092: PrintWriter ptrWriter = new PrintWriter(strWriter);
093:
094: profileResultModel.clear();
095:
096: Set sortedQueryStats = queryStats.getQueryStatsByOccurence();
097:
098: Iterator queryStatByOccurenceIter = queryStats
099: .getQueryStatsByOccurence().iterator();
100: while (queryStatByOccurenceIter.hasNext()) {
101: QueryStatEntry curQueryStat = (QueryStatEntry) queryStatByOccurenceIter
102: .next();
103: double curPercentage = 0.0;
104: if (queryStats.getTotalElapsedQueryTime() > 0) {
105: curPercentage = (100.0 * curQueryStat
106: .getTotalElapsedTime())
107: / queryStats.getTotalElapsedQueryTime();
108: } else {
109: curPercentage = (100.0 * curQueryStat.getOccurences())
110: / queryStats.getOccurenceCount();
111: }
112: NumberFormat nf = NumberFormat.getInstance();
113: nf.setMaximumFractionDigits(2);
114: ProfileReportResult profileResult = new ProfileReportResult();
115: profileResult.setPercentage(curPercentage);
116: profileResult.setOccurences(curQueryStat.getOccurences());
117: profileResult.setTotalElapsedTime(curQueryStat
118: .getTotalElapsedTime());
119: profileResult.setTableNames(curQueryStat.getTableNames());
120: profileResult.setColumnNames(curQueryStat.getColumnNames());
121:
122: profileResultModel.addProfileReportResult(profileResult);
123: /*
124: ptrWriter.println(nf.format(curPercentage) +
125: "% Occurences=" + curQueryStat.getOccurences() +
126: " Total time=" + curQueryStat.getTotalElapsedTime() +
127: " Table(s)=" + curQueryStat.getTableNames() +
128: " Column(s)=" + curQueryStat.getColumnNames() +
129: "<br>");
130: if (displayQueries) {
131: Iterator queryIter = curQueryStat.getQueries().iterator();
132: while (queryIter.hasNext()) {
133: String curQuery = (String) queryIter.next();
134: ptrWriter.println(" " + curQuery + "<br>");
135: }
136: }
137: */
138: }
139: profileResultModel.fireTableDataChanged();
140: }
141:
142: public void saveSQLIndexFile(String indexesFileName) {
143: logger.debug("Writing indexes SQL to file [" + indexesFileName
144: + "]...");
145: try {
146: FileWriter fileWriter = new FileWriter(indexesFileName);
147: PrintWriter writer = new PrintWriter(fileWriter);
148: Map generatedIndexes = queryStats.getGeneratedIndexes();
149: Iterator indexNameIter = generatedIndexes.keySet()
150: .iterator();
151: while (indexNameIter.hasNext()) {
152: String curIndexName = (String) indexNameIter.next();
153: String indexSql = (String) generatedIndexes
154: .get(curIndexName);
155: writer.println(indexSql);
156: }
157: writer.flush();
158: writer.close();
159: } catch (IOException ioe) {
160: ioe.printStackTrace();
161: }
162: logger.debug("done.");
163:
164: }
165:
166: //JPV : method called on the button to save SQL table profile statistics generated in a file
167: public void saveReportFile(String reportFileName) {
168: logger.debug("Writing report to file [" + reportFileName
169: + "]...");
170: try {
171: FileWriter fileWriter = new FileWriter(reportFileName);
172: PrintWriter writer = new PrintWriter(fileWriter);
173:
174: Set sortedQueryStats = queryStats
175: .getQueryStatsByOccurence();
176:
177: Iterator queryStatByOccurenceIter = queryStats
178: .getQueryStatsByOccurence().iterator();
179:
180: while (queryStatByOccurenceIter.hasNext()) {
181: QueryStatEntry curQueryStat = (QueryStatEntry) queryStatByOccurenceIter
182: .next();
183: double curPercentage = 0.0;
184: if (queryStats.getTotalElapsedQueryTime() > 0) {
185: curPercentage = (100.0 * curQueryStat
186: .getTotalElapsedTime())
187: / queryStats.getTotalElapsedQueryTime();
188: } else {
189: curPercentage = (100.0 * curQueryStat
190: .getOccurences())
191: / queryStats.getOccurenceCount();
192: }
193: NumberFormat nf = NumberFormat.getInstance();
194: nf.setMaximumFractionDigits(2);
195:
196: //Find throws SQL Statement Attach to curQueryStat
197: Set setQueries = curQueryStat.getQueries();
198: Iterator setQueriesIter = setQueries.iterator();
199: QueryEntry querySQL = (QueryEntry) setQueriesIter
200: .next();
201:
202: //First get prepared statement or Sql Statement if null
203: String strQuerySQL = querySQL.getPreparedSQL();
204: if (strQuerySQL == null) {
205: strQuerySQL = querySQL.getSqlStatement();
206: }
207: /*
208: while (setQueriesIter.hasNext()) {
209: QueryEntry querySQL = (QueryEntry) setQueriesIter.next();
210: writer.println(querySQL.getSqlStatement());
211: }
212: */
213: writer.println(curPercentage + ","
214: + curQueryStat.getTotalElapsedTime() + ","
215: + curQueryStat.getOccurences() + ",\""
216: + curQueryStat.getTableNames() + "\",\""
217: + curQueryStat.getColumnNames() + "\",\""
218: + strQuerySQL + "\"");
219: }
220: writer.flush();
221: writer.close();
222: } catch (IOException ioe) {
223: ioe.printStackTrace();
224: }
225: logger.debug("done.");
226:
227: }
228:
229: //End JPV
230:
231: public int getRowCount() {
232: return queryEntries.size();
233: }
234:
235: public int getColumnCount() {
236: return COLUMN_NAMES.length;
237: }
238:
239: public String getColumnName(int aCol) {
240: // does not need to be synchronized
241: return COLUMN_NAMES[aCol];
242: }
243:
244: public void addQuery(QueryEntry queryEntry) {
245: queryEntry.setReceptionRank(queryEntries.size());
246: queryEntries.add(queryEntry);
247: sortedQueryEntries.add(queryEntry);
248: long entryTime = queryEntry.getTime();
249: if (entryTime > highestQueryTime) {
250: highestQueryTime = entryTime;
251: }
252: if (entryTime < lowestQueryTime) {
253: lowestQueryTime = entryTime;
254: }
255: if (queryEntry.getElapsedTime() > 0) {
256: totalQueryTime += queryEntry.getElapsedTime();
257: }
258: }
259:
260: public void updateQueryStatsDisplay() {
261: if (statementCountValueLabel != null) {
262: statementCountValueLabel.setText(Integer
263: .toString(queryEntries.size()));
264: }
265: if (statementTimeValueLabel != null) {
266: statementTimeValueLabel.setText(Long
267: .toString(totalQueryTime));
268: }
269: }
270:
271: public void clear() {
272: queryEntries.clear();
273: sortedQueryEntries.clear();
274: lowestQueryTime = Long.MAX_VALUE;
275: highestQueryTime = Long.MIN_VALUE;
276: totalQueryTime = 0;
277: currCol = 0;
278: if (statementCountValueLabel != null) {
279: statementCountValueLabel.setText(Integer
280: .toString(queryEntries.size()));
281: }
282: if (statementTimeValueLabel != null) {
283: statementTimeValueLabel.setText("0");
284: }
285: queryStats.clear();
286: }
287:
288: public Object getValueAt(int rowIndex, int columnIndex) {
289:
290: QueryEntry curQueryEntry = (QueryEntry) queryEntries
291: .get(rowIndex);
292: return getFormattedQueryEntryColumn(curQueryEntry, columnIndex);
293: }
294:
295: private Object getFormattedQueryEntryColumn(
296: QueryEntry curQueryEntry, int columnIndex) {
297: Object result = null;
298: switch (columnIndex) {
299: case 0:
300: result = DATE_FORMATTER.format(new Date(curQueryEntry
301: .getTime()));
302: break;
303: case 1:
304: result = new Long(curQueryEntry.getElapsedTime());
305: break;
306: case 2:
307: result = curQueryEntry.getConnectionID();
308: break;
309: case 3:
310: result = curQueryEntry.getCategory();
311: break;
312: case 4:
313: result = curQueryEntry.getPreparedSQL();
314: break;
315: case 5:
316: result = curQueryEntry.getSqlStatement();
317: break;
318:
319: }
320: return result;
321: }
322:
323: private Object getRawQueryEntryColumn(QueryEntry curQueryEntry,
324: int columnIndex) {
325: Object result = null;
326: switch (columnIndex) {
327: case 0:
328: result = new Date(curQueryEntry.getTime());
329: break;
330: case 1:
331: result = new Long(curQueryEntry.getElapsedTime());
332: break;
333: case 2:
334: result = curQueryEntry.getConnectionID();
335: break;
336: case 3:
337: result = curQueryEntry.getCategory();
338: break;
339: case 4:
340: result = curQueryEntry.getPreparedSQL();
341: break;
342: case 5:
343: result = curQueryEntry.getSqlStatement();
344: break;
345:
346: }
347: return result;
348: }
349:
350: /*
351: * This method is the implementation of the Comparator interface.
352: * It is used for sorting the rows
353: */
354: public int compare(Object v1, Object v2) {
355:
356: // the comparison is between 2 vectors, each representing a row
357: // the comparison is done between 2 objects from the different rows that are in the column that is being sorted
358:
359: int ascending = ((Integer) ascendCol.get(currCol)).intValue();
360: if (v1 == null && v2 == null) {
361: return 0;
362: } else if (v2 == null) { // Define null less than everything.
363: return 1 * ascending;
364: } else if (v1 == null) {
365: return -1 * ascending;
366: }
367:
368: QueryEntry left = (QueryEntry) v1;
369: QueryEntry right = (QueryEntry) v2;
370:
371: Object o1 = getRawQueryEntryColumn(left, currCol);
372: Object o2 = getRawQueryEntryColumn(right, currCol);
373:
374: // If both values are null, return 0.
375: if (o1 == null && o2 == null) {
376: return 0;
377: } else if (o2 == null) { // Define null less than everything.
378: return 1 * ascending;
379: } else if (o1 == null) {
380: return -1 * ascending;
381: }
382:
383: if (o1 instanceof Number && o2 instanceof Number) {
384: Number n1 = (Number) o1;
385: double d1 = n1.doubleValue();
386: Number n2 = (Number) o2;
387: double d2 = n2.doubleValue();
388:
389: if (d1 == d2) {
390: return 0;
391: } else if (d1 > d2) {
392: return 1 * ascending;
393: } else {
394: return -1 * ascending;
395: }
396:
397: } else if (o1 instanceof Boolean && o2 instanceof Boolean) {
398: Boolean bool1 = (Boolean) o1;
399: boolean b1 = bool1.booleanValue();
400: Boolean bool2 = (Boolean) o2;
401: boolean b2 = bool2.booleanValue();
402:
403: if (b1 == b2) {
404: return 0;
405: } else if (b1) {
406: return 1 * ascending;
407: } else {
408: return -1 * ascending;
409: }
410:
411: } else if (o1 instanceof Date && o2 instanceof Date) {
412:
413: Date date1 = (Date) o1;
414: Date date2 = (Date) o2;
415: return date1.compareTo(date2) * ascending;
416:
417: } else {
418: // default case
419: if (o1 instanceof Comparable && o2 instanceof Comparable) {
420: Comparable c1 = (Comparable) o1;
421: Comparable c2 = (Comparable) o2; // superflous cast, no need for it!
422:
423: try {
424: return c1.compareTo(c2) * ascending;
425: } catch (ClassCastException cce) {
426: // forget it... we'll deal with them like 2 normal objects below.
427: }
428: }
429:
430: String s1 = o1.toString();
431: String s2 = o2.toString();
432: return s1.compareTo(s2) * ascending;
433: }
434: }
435:
436: /*
437: * This method sorts the rows using Java's Collections class.
438: * After sorting, it changes the state of the column -
439: * if the column was ascending, its new state is descending, and vice versa.
440: */
441: public void sort() {
442: Integer val = (Integer) ascendCol.get(currCol);
443: ascendCol.remove(currCol);
444: if (val.equals(one)) // change the state of the column
445: ascendCol.add(currCol, minusOne);
446: else
447: ascendCol.add(currCol, one);
448: Collections.sort(queryEntries, this );
449: }
450:
451: public void sortByColumn(int column) {
452: this .currCol = column;
453: sort();
454: fireTableDataChanged();
455: }
456:
457: // Add a mouse listener to the Table to trigger a table sort
458: // when a column heading is clicked in the JTable.
459: public void addMouseListenerToHeaderInTable(JTable table) {
460: final ProfileStatementTableModel sorter = this ;
461: final JTable tableView = table;
462: tableView.setColumnSelectionAllowed(false);
463: MouseAdapter listMouseListener = new MouseAdapter() {
464: public void mouseClicked(MouseEvent e) {
465: TableColumnModel columnModel = tableView
466: .getColumnModel();
467: int viewColumn = columnModel
468: .getColumnIndexAtX(e.getX());
469: int column = tableView
470: .convertColumnIndexToModel(viewColumn);
471: if (e.getClickCount() == 1 && column != -1) {
472: int shiftPressed = e.getModifiers()
473: & InputEvent.SHIFT_MASK;
474: boolean ascending = (shiftPressed == 0);
475: sorter.sortByColumn(column);
476: }
477: }
478: };
479: JTableHeader th = tableView.getTableHeader();
480: th.addMouseListener(listMouseListener);
481: }
482:
483: public void sortAndUpdateTable() {
484: Collections.sort(queryEntries, this );
485: fireTableDataChanged();
486: }
487:
488: public JLabel getStatementCountValueLabel() {
489: return statementCountValueLabel;
490: }
491:
492: public QueryEntry getStatementDetails(int rowIndex) {
493: return (QueryEntry) queryEntries.get(rowIndex);
494: }
495:
496: public void setStatementCountValueLabel(
497: JLabel statementCountValueLabel) {
498: this .statementCountValueLabel = statementCountValueLabel;
499: }
500:
501: public javax.swing.JLabel getStatementTimeValueLabel() {
502: return statementTimeValueLabel;
503: }
504:
505: public void setStatementTimeValueLabel(
506: javax.swing.JLabel statementTimeValueLabel) {
507: this .statementTimeValueLabel = statementTimeValueLabel;
508: }
509:
510: public ProfileResultTableModel getProfileResultModel() {
511: return profileResultModel;
512: }
513:
514: public void setProfileResultModel(
515: ProfileResultTableModel profileResultModel) {
516: this .profileResultModel = profileResultModel;
517: }
518:
519: public long getLowestQueryTime() {
520: return lowestQueryTime;
521: }
522:
523: public long getHighestQueryTime() {
524: return highestQueryTime;
525: }
526:
527: public ArrayList getQueriesBetweenTime(long lowTime, long highTime) {
528: ArrayList resultList = new ArrayList();
529: for (int i = 0; i < queryEntries.size(); i++) {
530: QueryEntry curEntry = (QueryEntry) queryEntries.get(i);
531: long entryTime = curEntry.getTime();
532: if ((entryTime >= lowTime) && (entryTime <= highTime)) {
533: resultList.add(curEntry);
534: }
535: }
536: return resultList;
537: }
538:
539: public ArrayList getQueryEntries() {
540: return queryEntries;
541: }
542:
543: public SortedSet getSortedQueryEntries() {
544: return sortedQueryEntries;
545: }
546:
547: }
|