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.util.*;
011: import org.jahia.sqlparser.SqlLexer;
012: import java.io.*;
013: import org.jahia.sqlparser.SqlParser;
014: import antlr.collections.AST; //import antlr.DumpASTVisitor; //JPV : For debugging Tree
015: import org.jahia.sqlparser.SqlTreeParser;
016:
017: /**
018: * <p>Title: SQL Profiler</p>
019: * <p>Description: </p>
020: * <p>Copyright: Copyright (c) 2003</p>
021: * <p>Company: Jahia Ltd</p>
022: * @author Serge Huber
023: * @author Jean-Philippe Valentin
024: * @version 2.0
025: *
026: * 11/14/2003 : Add DML statements by Jean-Philippe VALENTIN (JPV)
027: */
028:
029: public class QueryStatistics {
030:
031: private static final org.apache.log4j.Logger logger = org.apache.log4j.Logger
032: .getLogger(QueryStatistics.class);
033:
034: Map occurences = new TreeMap();
035: Map queryStatEntries = new TreeMap();
036: private long totalElapsedQueryTime = 0;
037:
038: public QueryStatistics() {
039:
040: }
041:
042: public void clear() {
043: occurences.clear();
044: queryStatEntries.clear();
045: totalElapsedQueryTime = 0;
046: }
047:
048: /*JPV : Add DML statements*/
049: public Integer processSQL(QueryEntry queryEntry) {
050: Integer occurenceCount = null;
051: String sqlStatement = queryEntry.getSqlStatement()
052: .toLowerCase();
053: if (sqlStatement.trim().startsWith("select")
054: || sqlStatement.trim().startsWith("insert")
055: || sqlStatement.trim().startsWith("update")
056: || sqlStatement.trim().startsWith("delete")) {
057: occurenceCount = (Integer) occurences.get(sqlStatement);
058: if (occurenceCount == null) {
059: occurenceCount = new Integer(0);
060: }
061: occurenceCount = new Integer(occurenceCount.intValue() + 1);
062: occurences.put(sqlStatement, occurenceCount);
063: parseSQL(queryEntry);
064: }
065: return occurenceCount;
066: }
067:
068: public Set getQueryStatsByOccurence() {
069: Set queryStatByOccurence = new TreeSet();
070: Iterator queryStatIter = queryStatEntries.keySet().iterator();
071: while (queryStatIter.hasNext()) {
072: String curQueryStatKey = (String) queryStatIter.next();
073: QueryStatEntry curQueryStatEntry = (QueryStatEntry) queryStatEntries
074: .get(curQueryStatKey);
075: queryStatByOccurence.add(curQueryStatEntry);
076: }
077: return queryStatByOccurence;
078: }
079:
080: public Map getGeneratedIndexes() {
081: Map generatedIndexes = new TreeMap();
082: Iterator queryStatByOccurenceIter = getQueryStatsByOccurence()
083: .iterator();
084: while (queryStatByOccurenceIter.hasNext()) {
085: QueryStatEntry curQueryStat = (QueryStatEntry) queryStatByOccurenceIter
086: .next();
087:
088: if ((curQueryStat.getTableNames().size() == 1)
089: && (curQueryStat.getColumnNames().size() > 0)) {
090: // only generate index on one table for the moment.
091: // first let's generate an index name
092: String tableName = (String) curQueryStat
093: .getTableNames().iterator().next();
094: if (tableName.startsWith("SEL_"))
095: //JPV : only SELECT statements used by index generation
096: {
097: //JPV : don't keep the prefix SEL_
098: tableName = tableName.substring(4, tableName
099: .length());
100: String indexName = tableName + "_index";
101: String indexSuffix = "";
102: int indexSuffixNumber = 1;
103: while (generatedIndexes.containsKey(new String(
104: indexName + indexSuffix))) {
105: indexSuffixNumber++;
106: indexSuffix = Integer
107: .toString(indexSuffixNumber);
108: }
109: StringBuffer indexStrBuffer = new StringBuffer(
110: "CREATE INDEX ");
111: indexStrBuffer.append(indexName);
112: indexStrBuffer.append(indexSuffix);
113: indexStrBuffer.append(" ON ");
114: indexStrBuffer.append(tableName);
115: indexStrBuffer.append(" ( ");
116: Iterator columnNameIter = curQueryStat
117: .getColumnNames().iterator();
118: while (columnNameIter.hasNext()) {
119: String curColumnName = (String) columnNameIter
120: .next();
121: indexStrBuffer.append(curColumnName);
122: if (columnNameIter.hasNext()) {
123: indexStrBuffer.append(",");
124: }
125: }
126: indexStrBuffer.append(" ); ");
127: generatedIndexes.put(new String(indexName
128: + indexSuffix), indexStrBuffer.toString());
129: }
130: }
131: }
132: return generatedIndexes;
133: }
134:
135: private void parseSQL(QueryEntry queryEntry) {
136: try {
137: SqlLexer lexer = new SqlLexer(new StringReader(queryEntry
138: .getSqlStatement().toLowerCase()));
139: SqlParser parser = new SqlParser(lexer);
140: parser.start_rule();
141: AST resultTree = parser.getAST();
142:
143: /*
144: System.out.println("");
145: System.out.println("==> Dump of AST <==");
146: DumpASTVisitor visitor = new DumpASTVisitor();
147: visitor.visit(resultTree);
148: System.out.println("==> End Dump of AST <==");
149: */
150:
151: SqlTreeParser sqlTreeParser = new SqlTreeParser();
152: /*JPV : Add DML statements*/
153: String strSqlStatement = queryEntry.getSqlStatement()
154: .toLowerCase();
155: if (strSqlStatement.trim().startsWith("select")) {
156: sqlTreeParser.select_statement(resultTree);
157: } else if (strSqlStatement.trim().startsWith("update")) {
158: sqlTreeParser.update_command(resultTree);
159: } else if (strSqlStatement.trim().startsWith("delete")) {
160: sqlTreeParser.delete_command(resultTree);
161: } else if (strSqlStatement.trim().startsWith("insert")) {
162: sqlTreeParser.insert_command(resultTree);
163: }
164:
165: /*End JPV*/
166: QueryStatEntry newEntry = new QueryStatEntry(sqlTreeParser
167: .getTableNames(), sqlTreeParser.getColumnNames());
168: if (queryStatEntries.containsKey(newEntry.getKey())) {
169: newEntry = (QueryStatEntry) queryStatEntries
170: .get(newEntry.getKey());
171: }
172: newEntry.incOccurences();
173: newEntry.addQuery(queryEntry);
174: if (queryEntry.getElapsedTime() > 0) {
175: newEntry
176: .incTotalElapseTime(queryEntry.getElapsedTime());
177: totalElapsedQueryTime += queryEntry.getElapsedTime();
178: }
179: queryStatEntries.put(newEntry.getKey(), newEntry);
180:
181: } catch (Exception e) {
182: System.err.println("exception: " + e);
183: }
184: }
185:
186: public int getOccurenceCount() {
187: return occurences.size();
188: }
189:
190: public long getTotalElapsedQueryTime() {
191: return totalElapsedQueryTime;
192: }
193:
194: }
|