|
1
8 package org.jahia.sqlprofiler;
9
10 import java.util.*;
11 import org.jahia.sqlparser.SqlLexer;
12 import java.io.*;
13 import org.jahia.sqlparser.SqlParser;
14 import antlr.collections.AST;
15 import org.jahia.sqlparser.SqlTreeParser;
17
18
29
30 public class QueryStatistics {
31
32 private static final org.apache.log4j.Logger logger = org.apache.log4j.
33 Logger.getLogger(QueryStatistics.class);
34
35 Map occurences = new TreeMap();
36 Map queryStatEntries = new TreeMap();
37 private long totalElapsedQueryTime = 0;
38
39 public QueryStatistics() {
40
41 }
42
43 public void clear() {
44 occurences.clear();
45 queryStatEntries.clear();
46 totalElapsedQueryTime = 0;
47 }
48
49
50 public Integer  processSQL(QueryEntry queryEntry) {
51 Integer occurenceCount = null;
52 String sqlStatement = queryEntry.getSqlStatement().toLowerCase();
53 if (sqlStatement.trim().startsWith("select") ||
54 sqlStatement.trim().startsWith("insert") ||
55 sqlStatement.trim().startsWith("update") ||
56 sqlStatement.trim().startsWith("delete")) {
57 occurenceCount = (Integer ) occurences.get(
58 sqlStatement);
59 if (occurenceCount == null) {
60 occurenceCount = new Integer (0);
61 }
62 occurenceCount = new Integer (occurenceCount.intValue() + 1);
63 occurences.put(sqlStatement, occurenceCount);
64 parseSQL(queryEntry);
65 }
66 return occurenceCount;
67 }
68
69 public Set getQueryStatsByOccurence() {
70 Set queryStatByOccurence = new TreeSet();
71 Iterator queryStatIter = queryStatEntries.keySet().iterator();
72 while (queryStatIter.hasNext()) {
73 String curQueryStatKey = (String ) queryStatIter.next();
74 QueryStatEntry curQueryStatEntry = (QueryStatEntry)
75 queryStatEntries.get(curQueryStatKey);
76 queryStatByOccurence.add(curQueryStatEntry);
77 }
78 return queryStatByOccurence;
79 }
80
81 public Map getGeneratedIndexes() {
82 Map generatedIndexes = new TreeMap();
83 Iterator queryStatByOccurenceIter = getQueryStatsByOccurence().iterator();
84 while (queryStatByOccurenceIter.hasNext()) {
85 QueryStatEntry curQueryStat = (QueryStatEntry)
86 queryStatByOccurenceIter.next();
87
88 if ((curQueryStat.getTableNames().size() == 1) &&
89 (curQueryStat.getColumnNames().size() > 0)) {
90 String tableName = (String ) curQueryStat.getTableNames().
93 iterator().next();
94 if (tableName.startsWith("SEL_"))
95 {
97 tableName = tableName.substring(4, tableName.length());
99 String indexName = tableName + "_index";
100 String indexSuffix = "";
101 int indexSuffixNumber = 1;
102 while (generatedIndexes.containsKey(new String (indexName +
103 indexSuffix))) {
104 indexSuffixNumber++;
105 indexSuffix = Integer.toString(indexSuffixNumber);
106 }
107 StringBuffer indexStrBuffer = new StringBuffer (
108 "CREATE INDEX ");
109 indexStrBuffer.append(indexName);
110 indexStrBuffer.append(indexSuffix);
111 indexStrBuffer.append(" ON ");
112 indexStrBuffer.append(tableName);
113 indexStrBuffer.append(" ( ");
114 Iterator columnNameIter = curQueryStat.getColumnNames().
115 iterator();
116 while (columnNameIter.hasNext()) {
117 String curColumnName = (String ) columnNameIter.next();
118 indexStrBuffer.append(curColumnName);
119 if (columnNameIter.hasNext()) {
120 indexStrBuffer.append(",");
121 }
122 }
123 indexStrBuffer.append(" ); ");
124 generatedIndexes.put(new String (indexName + indexSuffix),
125 indexStrBuffer.toString());
126 }
127 }
128 }
129 return generatedIndexes;
130 }
131
132 private void parseSQL(QueryEntry queryEntry) {
133 try {
134 SqlLexer lexer = new SqlLexer(new StringReader(queryEntry.getSqlStatement().toLowerCase()));
135 SqlParser parser = new SqlParser(lexer);
136 parser.start_rule();
137 AST resultTree = parser.getAST();
138
139
146
147 SqlTreeParser sqlTreeParser = new SqlTreeParser();
148
149 String strSqlStatement = queryEntry.getSqlStatement().toLowerCase();
150 if (strSqlStatement.trim().startsWith("select"))
151 {
152 sqlTreeParser.select_statement(resultTree);
153 }
154 else if (strSqlStatement.trim().startsWith("update"))
155 {
156 sqlTreeParser.update_command(resultTree);
157 }
158 else if (strSqlStatement.trim().startsWith("delete"))
159 {
160 sqlTreeParser.delete_command(resultTree);
161 }
162 else if (strSqlStatement.trim().startsWith("insert"))
163 {
164 sqlTreeParser.insert_command(resultTree);
165 }
166
167
168 QueryStatEntry newEntry = new QueryStatEntry(sqlTreeParser.
169 getTableNames(), sqlTreeParser.getColumnNames());
170 if (queryStatEntries.containsKey(newEntry.getKey())) {
171 newEntry = (QueryStatEntry) queryStatEntries.get(newEntry.
172 getKey());
173 }
174 newEntry.incOccurences();
175 newEntry.addQuery(queryEntry);
176 if (queryEntry.getElapsedTime() > 0) {
177 newEntry.incTotalElapseTime(queryEntry.getElapsedTime());
178 totalElapsedQueryTime += queryEntry.getElapsedTime();
179 }
180 queryStatEntries.put(newEntry.getKey(), newEntry);
181
182 } catch (Exception e) {
183 System.err.println("exception: " + e);
184 }
185 }
186
187 public int getOccurenceCount() {
188 return occurences.size();
189 }
190
191 public long getTotalElapsedQueryTime() {
192 return totalElapsedQueryTime;
193 }
194
195 }
|
Popular Tags
|
Java API By Example, From Geeks To Geeks.