001: package de.ixdb.squirrel_sql.plugins.cache;
002:
003: import com.intersys.cache.CacheObject;
004: import com.intersys.cache.Dataholder;
005: import com.intersys.cache.jbind.JBindDatabase;
006: import com.intersys.classes.CharacterStream;
007: import com.intersys.objects.CacheDatabase;
008: import com.intersys.objects.CacheReader;
009: import com.intersys.objects.Database;
010: import com.intersys.objects.CacheException;
011: import net.n3.nanoxml.*;
012: import net.sourceforge.squirrel_sql.client.IApplication;
013: import net.sourceforge.squirrel_sql.client.action.SquirrelAction;
014: import net.sourceforge.squirrel_sql.client.session.ISession;
015: import net.sourceforge.squirrel_sql.client.session.action.ISessionAction;
016: import net.sourceforge.squirrel_sql.fw.util.Resources;
017:
018: import javax.swing.*;
019: import java.awt.event.ActionEvent;
020: import java.io.StringReader;
021: import java.util.Vector;
022: import java.util.regex.Matcher;
023: import java.util.regex.Pattern;
024: import java.sql.Statement;
025:
026: public class ShowQueryPlanCommand {
027: private ISession _session;
028:
029: public static final String HREF_CLOSE_QUERY_PLAN = "#close query plan";
030: private QueryPlanTab _queryPlanTab;
031:
032: public ShowQueryPlanCommand(ISession session) {
033: _session = session;
034: }
035:
036: public void execute() {
037: try {
038:
039: String selectSQL = _session.getSessionInternalFrame()
040: .getSQLPanelAPI().getSQLScriptToBeExecuted();
041:
042: if (null == selectSQL || 0 == selectSQL.trim().length()) {
043: JOptionPane
044: .showMessageDialog(
045: _session.getApplication()
046: .getMainFrame(),
047: "No statement selected.\nCan not retrieve query plan.\nPlease select a statement");
048: return;
049: }
050:
051: String xml = getExecutionPlanXmlFromCache(selectSQL);
052:
053: if (null == xml || 0 == xml.trim().length()) {
054: JOptionPane
055: .showMessageDialog(
056: _session.getApplication()
057: .getMainFrame(),
058: "Cache returned an empty execution plan.\nThere is probably something wrong with your query.\nTry to execute your query first to test it.");
059: return;
060: }
061:
062: String excutionPlanHTML;
063:
064: Exception toThrowAtEnde = null;
065: try {
066: excutionPlanHTML = createExcutionPlanHTML(xml,
067: selectSQL);
068: } catch (Exception e) {
069: _session
070: .showErrorMessage("Failed to create a HTML page from Cache's query plan xml:\n"
071: + e.toString());
072: excutionPlanHTML = createPlainXmlPlanHTML(xml);
073: toThrowAtEnde = e;
074: }
075:
076: QueryPlanTabListener qtl = new QueryPlanTabListener() {
077: public void closeRequested() {
078: _session.getSessionSheet().removeMainTab(
079: _queryPlanTab);
080: }
081: };
082:
083: _queryPlanTab = new QueryPlanTab(excutionPlanHTML, qtl);
084: int index = _session.getSessionSheet().addMainTab(
085: _queryPlanTab);
086: _session.getSessionSheet().selectMainTab(index);
087:
088: if (null != toThrowAtEnde) {
089: throw new RuntimeException(toThrowAtEnde);
090: }
091:
092: } catch (Exception e) {
093: throw new RuntimeException(e);
094: }
095: }
096:
097: private String createPlainXmlPlanHTML(String xml) {
098:
099: xml = xml.replaceAll("<", "<");
100: xml = xml.replaceAll(">", ">");
101:
102: StringBuffer html = new StringBuffer("<HTML>");
103:
104: html.append("<h4 align=\"right\"><a href=\""
105: + HREF_CLOSE_QUERY_PLAN
106: + "\">close query plan</a></h4>\n");
107: html
108: .append("<h3>Failed to create a HTML page from Cache's query plan xml. Please inform de.ixdb about the error. Below is the raw xml.</h3>\n");
109: html.append("<h4><pre>\n").append(xml).append("\n</pre></h4>");
110: html.append("</HTML>");
111:
112: return html.toString();
113:
114: }
115:
116: private String getExecutionPlanXmlFromCache(String selectSQL) {
117: try {
118: Dataholder[] argv = new Dataholder[1];
119: argv[0] = Dataholder.create(selectSQL);
120:
121: Database conn = (JBindDatabase) CacheDatabase
122: .getDatabase(_session.getSQLConnection()
123: .getConnection());
124: //Dataholder res = conn.runClassMethod("%Library.CMUtilities", "getExecutionPlan", argv, Database.RET_OBJECT);
125:
126: Dataholder res = null;
127: try {
128: res = conn.runClassMethod("CM.methM2", "M2", argv,
129: Database.RET_OBJECT);
130: } catch (CacheException e) {
131: Statement stat = _session.getSQLConnection()
132: .createStatement();
133: stat
134: .executeUpdate(
135:
136: "CREATE METHOD CM.M2(IN sql %String)"
137: + " RETURNS %GlobalCharacterStream"
138: + " LANGUAGE COS"
139: + " {"
140: + " quit:$get(sql)=\"\" \"\""
141: + " kill %plan\n"
142: + " set outStream = ##class(%GlobalCharacterStream).%New()"
143: + " set sql(1)=sql,sql=1"
144: + " do ShowPlan^%apiSQL(.sql,1)"
145: + " set nSub = 1,line = \"\""
146: + " set to = +$g(%plan)+1"
147: + " while nSub<to "
148: + " {"
149: + " set line = $g(%plan(nSub))"
150: + " do outStream.WriteLine(line)"
151: + " set nSub = nSub + 1"
152: + " }" + " kill %plan\n"
153: + " quit outStream" + " }");
154: stat.close();
155:
156: res = conn.runClassMethod("CM.methM2", "M2", argv,
157: Database.RET_OBJECT);
158: }
159:
160: CacheObject cobj = res.getCacheObject();
161: CharacterStream characterStream = (CharacterStream) (cobj
162: .newJavaInstance());
163:
164: CacheReader reader = characterStream.getReader();
165:
166: StringBuffer sb = new StringBuffer();
167: sb.append("");
168:
169: char[] buf = new char[50];
170: int count = reader.read(buf);
171: while (true) {
172:
173: for (int i = 0; i < count; i++) {
174: sb.append(buf[i]);
175: }
176:
177: if (count < 50) {
178: break;
179: }
180:
181: count = reader.read(buf);
182: }
183: return sb.toString();
184: } catch (Exception e) {
185: _session
186: .showErrorMessage("Failed to retrieve execution plan from Cache:\n"
187: + e);
188: throw new RuntimeException(e);
189: }
190: }
191:
192: private String createExcutionPlanHTML(String execPlanXml,
193: String selectSql) throws ClassNotFoundException,
194: InstantiationException, IllegalAccessException,
195: XMLException {
196: StringReader sr = new StringReader(execPlanXml);
197: IXMLParser parser = XMLParserFactory.createDefaultXMLParser();
198: parser.setReader(new StdXMLReader(sr));
199: IXMLElement element = (IXMLElement) parser.parse();
200:
201: StringBuffer html = new StringBuffer("<HTML>");
202:
203: html.append("<h4 align=\"right\"><a href=\""
204: + HREF_CLOSE_QUERY_PLAN
205: + "\">close query plan</a></h4>\n");
206:
207: Vector vKids = element.getChildren();
208: IXMLElement ePlan = (IXMLElement) vKids.get(2);
209: if (null != ePlan.getName()) {
210: throw new IllegalStateException(
211: "Could not find untaged text element containing the plan. Expected this element to be #3 of the plan tag's kids.");
212: }
213: html.append(getHeader("query plan", "#66FF99"));
214: String plan = ePlan.getContent();
215: html.append(createHtmlFromText(plan));
216:
217: Vector vModules = element.getChildrenNamed("module");
218: for (int i = 0; i < vModules.size(); i++) {
219: IXMLElement eModule = (IXMLElement) vModules.elementAt(i);
220: String module = eModule.getContent();
221: html.append(getHeader("module "
222: + eModule.getAttribute("name", ""), "#FFFF99"));
223: html.append(createHtmlFromText(module));
224: }
225:
226: Vector vSubqueries = element.getChildrenNamed("subquery");
227: for (int i = 0; i < vSubqueries.size(); i++) {
228: IXMLElement eSubquery = (IXMLElement) vSubqueries
229: .elementAt(i);
230: String subquery = eSubquery.getContent();
231: html.append(getHeader("subquery", "#C0C0C0"));
232: html.append(createHtmlFromText(subquery));
233: }
234:
235: Vector vExpressions = element.getChildrenNamed("expression");
236: for (int i = 0; i < vExpressions.size(); i++) {
237: IXMLElement eExpression = (IXMLElement) vExpressions
238: .elementAt(i);
239: String expression = eExpression.getContent();
240: html.append(getHeader("expression", "#7FFFD4"));
241: html.append(createHtmlFromText(expression));
242: }
243:
244: Vector vCost = element.getChildrenNamed("cost");
245: String cost = "unknown";
246: if (1 == vCost.size()) {
247: IXMLElement eCost = (IXMLElement) vCost.get(0);
248: cost = eCost.getAttribute("value", "");
249: }
250: html.append(getHeader("cost", "#FAEBD7"));
251: html.append("<h4><ul><li>" + cost + "</li></ul></h4>");
252:
253: html.append(getHeader("sql", "#CCCCFF"));
254: html.append("<h4><ul><li><pre>").append(selectSql).append(
255: "</pre></li></ul></h4>");
256:
257: html.append("</HTML>");
258:
259: return html.toString();
260: }
261:
262: private String createHtmlFromText(String text) {
263: Pattern pattern = Pattern.compile("module [A-Z]");
264: Matcher matcher = pattern.matcher(text);
265:
266: while (matcher.find()) {
267: String group = matcher.group();
268: text = text.replaceAll(group, "<a href=\"#" + group + "\">"
269: + group + "</a>");
270: }
271:
272: String[] lines = text.split("\n");
273:
274: StringBuffer ret = new StringBuffer();
275:
276: ret.append("<h4>");
277: ret.append("<ul><li>");
278: int indentDepth = getIndentDepth(lines[0]);
279: for (int i = 0; i < lines.length; i++) {
280:
281: int newIndentDepth = indentDepth;
282: if (i < lines.length - 1) {
283: newIndentDepth = getIndentDepth(lines[i + 1]);
284: }
285:
286: if (newIndentDepth > indentDepth) {
287: if (lines[i].trim().endsWith(":")) {
288: ret.append("<br><br>").append(lines[i])
289: .append("\n");
290: } else {
291: ret.append(lines[i]).append("\n");
292: }
293:
294: ret.append("</li><ul><li>");
295: } else if (newIndentDepth < indentDepth) {
296: ret.append(lines[i]).append("\n");
297: ret.append("</li></ul><li>");
298: } else // newIndentDepth == indentDepth
299: {
300: ret.append(lines[i]).append("\n");
301: }
302:
303: indentDepth = newIndentDepth;
304: }
305:
306: for (int i = 0; i < indentDepth; ++i) {
307: ret.append("</ul>");
308: }
309: ret.append("</h4>");
310:
311: return ret.toString();
312: }
313:
314: private int getIndentDepth(String line) {
315: int leadingspaceCount = 0;
316:
317: for (int i = 0; i < line.length() && ' ' == line.charAt(i); i++) {
318: ++leadingspaceCount;
319: }
320:
321: return leadingspaceCount / 4 + 1;
322:
323: }
324:
325: private String getHeader(String name, String bgColor) {
326: return "<P>\n"
327: + "<A NAME=\"#"
328: + name
329: + "\"></A>\n"
330: + "<TABLE BORDER=\"1\" WIDTH=\"100%\" CELLPADDING=\"3\" CELLSPACING=\"0\" SUMMARY=\"\">\n"
331: + "<TR BGCOLOR=\""
332: + bgColor
333: + "\" CLASS=\"TableHeadingColor\">\n"
334: + "<TH ALIGN=\"left\" COLSPAN=\"2\"><FONT SIZE=\"+2\">\n"
335: + "<B>" + name + "</B></FONT></TH>\n" + "</TR>\n"
336: + "</TABLE>\n" + "</P>\n";
337:
338: }
339:
340: }
|