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;
009:
010: import java.io.*;
011: import org.apache.regexp.RE;
012: import org.apache.regexp.RESyntaxException;
013: import java.util.*;
014: import org.jahia.sqlparser.*;
015: import antlr.*;
016: import antlr.collections.AST;
017: import java.text.NumberFormat;
018: import org.apache.commons.cli.*;
019:
020: /**
021: * <p>Title: SQL Profiler log file parser/p>
022: * <p>Description: </p>
023: * <p>Copyright: Copyright (c) 2003</p>
024: * <p>Company: Jahia Ltd</p>
025: * @author Serge Huber
026: * @version 1.0
027: */
028:
029: public class LogFileParser {
030:
031: private static final String GENERATED_INDEXES_FILE = "indexes.sql";
032:
033: private QueryStatistics queryStats = new QueryStatistics();
034:
035: public LogFileParser() {
036: }
037:
038: public void parseFile(String fileName, boolean displayQueries,
039: boolean generateIndexes) throws FileNotFoundException,
040: IOException, RESyntaxException {
041:
042: File inputFile = new File(fileName);
043: long fileLength = inputFile.length();
044: BufferedReader in = new BufferedReader(new FileReader(fileName));
045: System.out.print("Processing file : " + fileName + "...");
046: RE selectWithOrderQuery = new RE(
047: "select[:blank:].*[:blank:]from[:blank:].*[:blank:]where[:blank:](.*)[:blank:]order[:blank:]by(.*)([:blank:](asc)|(desc))?");
048: RE selectQuery = new RE(
049: "select[:blank:].*[:blank:]from[:blank:].*[:blank:]where[:blank:](.*)");
050: String curLine = null;
051: String lowerCurLine = null;
052: int lineCount = 0;
053: long curFilePos = 0;
054: int lastCompletion = 0;
055: do {
056: curLine = in.readLine();
057: if (curLine == null) {
058: break;
059: }
060: curFilePos += curLine.getBytes().length;
061: lineCount++;
062: lowerCurLine = curLine.toLowerCase();
063: String occurenceString = null;
064: if (selectWithOrderQuery.match(lowerCurLine)) {
065: occurenceString = selectWithOrderQuery.getParen(0);
066: } else if (selectQuery.match(lowerCurLine)) {
067: occurenceString = selectQuery.getParen(0);
068: }
069: if (occurenceString != null) {
070: QueryEntry queryEntry = new QueryEntry();
071: queryEntry.setSqlStatement(occurenceString);
072: queryStats.processSQL(queryEntry);
073: }
074: double completionPercentage = (100.0 * curFilePos)
075: / fileLength;
076: int roundedCompletion = new Double(completionPercentage)
077: .intValue();
078: if (((roundedCompletion % 10) == 0)
079: && (lastCompletion != roundedCompletion)) {
080: System.out.print(roundedCompletion + "%...");
081: lastCompletion = roundedCompletion;
082: }
083:
084: } while (curLine != null);
085: System.out.println("100%");
086:
087: System.out.println("Lines read=" + lineCount
088: + " occurencesFound=" + queryStats.getOccurenceCount());
089: }
090:
091: private void displayOccurenceStats(boolean displayQueries) {
092: Iterator queryStatByOccurenceIter = queryStats
093: .getQueryStatsByOccurence().iterator();
094: while (queryStatByOccurenceIter.hasNext()) {
095: QueryStatEntry curQueryStat = (QueryStatEntry) queryStatByOccurenceIter
096: .next();
097: double occurencePourcentage = (100.0 * curQueryStat
098: .getOccurences())
099: / queryStats.getOccurenceCount();
100: NumberFormat nf = NumberFormat.getInstance();
101: nf.setMaximumFractionDigits(2);
102: System.out.println(nf.format(occurencePourcentage)
103: + "% Occurences=" + curQueryStat.getOccurences()
104: + " Table(s)=" + curQueryStat.getTableNames()
105: + " Column(s)=" + curQueryStat.getColumnNames());
106:
107: if (displayQueries) {
108: Iterator queryIter = curQueryStat.getQueries()
109: .iterator();
110: while (queryIter.hasNext()) {
111: String curQuery = (String) queryIter.next();
112: System.out.println(" " + curQuery);
113: }
114: }
115: }
116: }
117:
118: public static void main(String[] args) {
119: LogFileParser logfileParser = new LogFileParser();
120: // create the command line parser
121: CommandLineParser parser = new PosixParser();
122: boolean displayQueries = false;
123: boolean generateIndexes = false;
124: String indexesFileName = GENERATED_INDEXES_FILE;
125:
126: // create the Options
127: Options options = new Options();
128: Option indexesOption = OptionBuilder
129: .withLongOpt("indexes")
130: .hasOptionalArg()
131: .withArgName("filename")
132: .withDescription(
133: "the file name to which to output the indexes ")
134: .create('i');
135: options
136: .addOption("q", "with-queries", false,
137: "Display the queries along with the table and column statistics");
138: options.addOption(indexesOption);
139: options
140: .addOption("h", "help", false,
141: "Print this help message");
142:
143: try {
144: // parse the command line arguments
145: CommandLine line = parser.parse(options, args);
146:
147: if (line.hasOption('h') || (args.length == 0)) {
148: HelpFormatter helpFormatter = new HelpFormatter();
149: StringWriter strWriter = new StringWriter();
150: PrintWriter ptrWriter = new PrintWriter(strWriter);
151: helpFormatter
152: .printHelp(
153: ptrWriter,
154: 80,
155: "java -jar sqlprofiler.jar log_file_name [options]",
156: "", options, 10, 10, "");
157: System.out.println(strWriter.toString());
158: System.exit(0);
159: }
160:
161: if (line.hasOption('q')) {
162: displayQueries = true;
163: System.out.println("Query display activated.");
164: }
165:
166: if (line.hasOption('i')) {
167: generateIndexes = true;
168: String optionIndexesFileName = line.getOptionValue('i');
169: if (optionIndexesFileName != null) {
170: indexesFileName = optionIndexesFileName;
171: }
172: System.out.println("Indexes generation in file ["
173: + indexesFileName + "] activated.");
174: }
175:
176: } catch (ParseException exp) {
177: System.out.println("Unexpected exception:");
178: exp.printStackTrace();
179: System.exit(0);
180: }
181: try {
182: logfileParser.parseFile(args[0], displayQueries,
183: generateIndexes);
184: logfileParser.displayOccurenceStats(displayQueries);
185: } catch (FileNotFoundException fnfe) {
186: fnfe.printStackTrace();
187: } catch (IOException ioe) {
188: ioe.printStackTrace();
189: } catch (RESyntaxException rese) {
190: rese.printStackTrace();
191: }
192:
193: if (generateIndexes) {
194: System.out.print("Writing indexes SQL to file ["
195: + indexesFileName + "]...");
196: try {
197: FileWriter fileWriter = new FileWriter(indexesFileName);
198: PrintWriter writer = new PrintWriter(fileWriter);
199: Iterator indexNameIter = logfileParser.queryStats
200: .getGeneratedIndexes().keySet().iterator();
201: while (indexNameIter.hasNext()) {
202: String curIndexName = (String) indexNameIter.next();
203: String indexSql = (String) logfileParser.queryStats
204: .getGeneratedIndexes().get(curIndexName);
205: writer.println(indexSql);
206: }
207: writer.flush();
208: writer.close();
209: } catch (IOException ioe) {
210: ioe.printStackTrace();
211: }
212: System.out.println("done.");
213: }
214: }
215:
216: }
|