You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by kr...@apache.org on 2010/08/12 20:02:20 UTC
svn commit: r984881 - in /db/derby/code/trunk/java:
testing/org/apache/derbyTesting/functionTests/tests/lang/
tools/org/apache/derby/impl/tools/planexporter/
tools/org/apache/derby/impl/tools/planexporter/resources/
tools/org/apache/derby/tools/
Author: kristwaa
Date: Thu Aug 12 18:02:19 2010
New Revision: 984881
URL: http://svn.apache.org/viewvc?rev=984881&view=rev
Log:
DERBY-4587: Add tools for improved analysis and understanding of query plans and execution statistics
Various clean-ups and improvements, amongst others:
o print statement id in the output
o print time of statement execution in the output
o removed user and password arguments for PlanExporter (specified in JDBC URL instead)
o close connection instead of shutting down database
o removed @author tags
o made TreeNode package-private
o switched depth-representation from String to int
Patch contributed by C.S. Nirmal J. Fernando (nirmal070125 at gmail dot com).
Patch file: DERBY-4587-tool-9.8-modified.diff
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl
db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java Thu Aug 12 18:02:19 2010
@@ -514,39 +514,35 @@ public class XplainStatisticsTest extend
{
s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
- if(XML.classpathMeetsXMLReqs()){
- /*
- * Added by DERBY-4587 to test the generation of XML files
- * from PlanExporter tool.
- */
- String stmt_id="";
- ResultSet rs;
- AccessDatabase access;
+ /*
+ * Added by DERBY-4587 to test the generation of XML files
+ * from PlanExporter tool.
+ */
+ String stmt_id="";
+ ResultSet rs;
+ AccessDatabase access;
- rs = s.executeQuery(
- "select stmt_id from XPLTEST.sysxplain_statements");
- while (rs.next())
- {
- stmt_id = rs.getString(1);
- access =
- new AccessDatabase(getConnection(), "XPLTEST", stmt_id);
- if(access.initializeDataArray()){
- access.createXMLFragment();
- access.markTheDepth();
-
- CreateXMLFile xml_file = new CreateXMLFile(access);
- xml_file.writeTheXMLFile(
- access.statement(),
- access.getData(),
- SupportFilesSetup.getReadWriteURL(stmt_id + ".xml")
- .getPath(),
- null);
- }
- }
- }
- else{
- //skips the tests
- }
+ rs = s.executeQuery(
+ "select stmt_id from XPLTEST.sysxplain_statements");
+ while (rs.next())
+ {
+ stmt_id = rs.getString(1);
+ access =
+ new AccessDatabase(getConnection(), "XPLTEST", stmt_id);
+ if(access.initializeDataArray()){
+ access.createXMLFragment();
+ access.markTheDepth();
+
+ CreateXMLFile xml_file = new CreateXMLFile(access);
+ xml_file.writeTheXMLFile(
+ access.statement(),
+ access.time(),
+ access.getData(),
+ SupportFilesSetup.getReadWriteURL(stmt_id + ".xml")
+ .getPath(),
+ null);
+ }
+ }
}
private void verifyXplainUnset(Statement s)
@@ -658,12 +654,12 @@ public class XplainStatisticsTest extend
/**
* Added by DERBY-4587
- * gets the <statement> element from the XML
+ * gets the <statement> element from the XML
* @param file XML file
* @return statement mentioned in the XML
* @throws Exception
*/
- private String readStatement(final String file)//, short type)
+ private String readStatement(final String file)
throws Exception
{
Document document;
@@ -675,7 +671,7 @@ public class XplainStatisticsTest extend
/**
* Added by DERBY-4587
- * count the # of <node> elements
+ * count the # of <node> elements
* @param file XML file
* @return node count
* @throws Exception
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java Thu Aug 12 18:02:19 2010
@@ -12,11 +12,6 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
-
-/**
- * @author Nirmal
- *
- */
public class AccessDatabase {
private Connection conn = null;
@@ -25,6 +20,19 @@ public class AccessDatabase {
private String dbURL = null;
private String schema = null;
private String query = null;
+ /**
+ * @param query the stmt_id to set
+ */
+ public void setQuery(String query) {
+ this.query = query;
+ }
+
+ /**
+ * @return the stmt_id
+ */
+ public String getQuery() {
+ return query;
+ }
private int depth = 0;
public int getDepth() {
return depth;
@@ -32,19 +40,19 @@ public class AccessDatabase {
private String xmlDetails="";
//set of variables to identify values of XPlain tables
- private final int id =0 ;
- private final int p_id =1;
- private final int nodeType=2;
- private final int noOfOpens=3;
- private final int inputRows=4;
- private final int returnedRows=5;
- private final int visitedPages=6;
- private final int scanQualifiers=7;
- private final int nextQualifiers=8;
- private final int scannedObject=9;
- private final int scanType=10;
- private final int sortType=11;
- private final int noOfOutputRowsBySorter=12;
+ private static final int ID =0 ;
+ private static final int P_ID =1;
+ private static final int NODE_TYPE=2;
+ private static final int NO_OF_OPENS=3;
+ private static final int INPUT_ROWS=4;
+ private static final int RETURNED_ROWS=5;
+ private static final int VISITED_PAGES=6;
+ private static final int SCAN_QUALIFIERS=7;
+ private static final int NEXT_QUALIFIERS=8;
+ private static final int SCANNED_OBJECT=9;
+ private static final int SCAN_TYPE=10;
+ private static final int SORT_TYPE=11;
+ private static final int NO_OF_OUTPUT_ROWS_BY_SORTER=12;
/**
@@ -57,7 +65,7 @@ public class AccessDatabase {
dbURL = dburl;
schema = aSchema;
- query = aQuery;
+ setQuery(aQuery);
}
@@ -66,12 +74,13 @@ public class AccessDatabase {
* @param aConn
* @param aSchema
* @param aQuery
+ *
*/
public AccessDatabase(Connection aConn, String aSchema, String aQuery) {
conn = aConn;
schema = aSchema;
- query = aQuery;
+ setQuery(aQuery);
}
@@ -85,7 +94,7 @@ public class AccessDatabase {
public void createConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
{
- if(dbURL.indexOf("//") != -1)
+ if(dbURL.indexOf("://") != -1)
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
else
@@ -107,96 +116,96 @@ public class AccessDatabase {
createXMLData(
"select 'id=\"' ||RS_ID|| '\"' " +
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", id);
+ "where STMT_ID = '"+getQuery()+"'", ID);
createXMLData(
"select PARENT_RS_ID "+
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", p_id);
+ "where STMT_ID = '"+getQuery()+"'", P_ID);
createXMLData(
"select 'name=\"' ||OP_IDENTIFIER|| '\"' " +
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", nodeType);
+ "where STMT_ID = '"+getQuery()+"'", NODE_TYPE);
createXMLData(
"select 'no_opens=\"' " +
"|| TRIM(CHAR(NO_OPENS))|| '\"' " +
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", noOfOpens);
+ "where STMT_ID = '"+getQuery()+"'", NO_OF_OPENS);
createXMLData(
"select 'input_rows=\"' " +
"|| TRIM(CHAR(INPUT_ROWS))|| '\"' " +
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", inputRows);
+ "where STMT_ID = '"+getQuery()+"'", INPUT_ROWS);
createXMLData(
"select 'returned_rows=\"' " +
"|| TRIM(CHAR(RETURNED_ROWS))|| '\"' " +
"from "+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'", returnedRows);
+ "where STMT_ID = '"+getQuery()+"'", RETURNED_ROWS);
createXMLData(
"select 'visited_pages=\"'" +
"|| TRIM(CHAR(NO_VISITED_PAGES))|| '\"' " +
"from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", visitedPages);
+ "where STMT_ID = '"+getQuery()+"'", VISITED_PAGES);
createXMLData(
"select 'scan_qualifiers=\"'"+
"||SCAN_QUALIFIERS|| '\"' " +
"from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", scanQualifiers);
+ "where STMT_ID = '"+getQuery()+"'", SCAN_QUALIFIERS);
createXMLData(
"select 'next_qualifiers=\"'"+
"||NEXT_QUALIFIERS|| '\"' " +
"from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", nextQualifiers);
+ "where STMT_ID = '"+getQuery()+"'", NEXT_QUALIFIERS);
createXMLData(
"select 'scanned_object=\"'"+
"||SCAN_OBJECT_NAME|| '\"' " +
"from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", scannedObject);
+ "where STMT_ID = '"+getQuery()+"'", SCANNED_OBJECT);
createXMLData(
"select 'scan_type=\"'"+
"||TRIM(SCAN_TYPE)|| '\"' " +
"from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", scanType);
+ "where STMT_ID = '"+getQuery()+"'", SCAN_TYPE);
createXMLData(
"select 'sort_type=\"'"+
"||TRIM(SORT_TYPE)|| '\"' " +
"from ("+schema+".SYSXPLAIN_SORT_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", sortType);
+ "where STMT_ID = '"+getQuery()+"'", SORT_TYPE);
createXMLData(
"select 'sorter_output=\"'"+
"||TRIM(CHAR(NO_OUTPUT_ROWS))|| '\"' " +
"from ("+schema+".SYSXPLAIN_SORT_PROPS " +
"NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
- "where STMT_ID = '"+query+"'", noOfOutputRowsBySorter);
+ "where STMT_ID = '"+getQuery()+"'", NO_OF_OUTPUT_ROWS_BY_SORTER);
}
/**
- *
+ * Generating the XML tree
* @return all xml elements as a String
*/
public String getXmlString(){
for(int i=0;i<data.length;i++){
//assume only one root element for any query
- if(Integer.parseInt(data[i].getDepth())==0){//root element
+ if(data[i].getDepth()==0){//root element
xmlDetails += indent(1);
xmlDetails += data[i].toString();
getChildren(1, data[i].getId());
@@ -212,10 +221,10 @@ public class AccessDatabase {
* @param currentLevel level of the XML tree (0 based) of current node
* @param id current node's stmt_id
*/
- public void getChildren(int currentLevel,String id ){
+ private void getChildren(int currentLevel,String id ){
if(currentLevel <= depth){
for(int i=0;i<data.length;i++){
- if(Integer.parseInt(data[i].getDepth())== currentLevel &&
+ if(data[i].getDepth()== currentLevel &&
(id.indexOf(data[i].getParent()) != -1))
{
xmlDetails += indent(currentLevel+1);
@@ -247,7 +256,7 @@ public class AccessDatabase {
int i=0;
while(data[i].getParent().indexOf("null")== -1)
i++;
- data[i].setDepth(""+depth); //root
+ data[i].setDepth(depth); //root
findChildren(i,depth);
}
@@ -256,7 +265,7 @@ public class AccessDatabase {
* @param idx current element's index
* @param dep current examining depth
*/
- public void findChildren(int idx, int dep) {
+ private void findChildren(int idx, int dep) {
if(dep>depth)
depth =dep;
@@ -266,7 +275,7 @@ public class AccessDatabase {
if((data[idx].getId()).indexOf(data[i].getParent()) != -1
&& i != idx)
{
- data[i].setDepth(""+(dep +1));
+ data[i].setDepth(dep +1);
findChildren(i,dep+1);
}
}
@@ -308,43 +317,43 @@ public class AccessDatabase {
String text= results.getString(1);
if(text != null){
switch(x){
- case id:
+ case ID:
data[i].setId(text+" ");
break;
- case p_id:
+ case P_ID:
data[i].setParent(text);
break;
- case nodeType:
+ case NODE_TYPE:
data[i].setNodeType(text+" ");
break;
- case noOfOpens:
+ case NO_OF_OPENS:
data[i].setNoOfOpens(text+" ");
break;
- case inputRows:
+ case INPUT_ROWS:
data[i].setInputRows(text+" ");
break;
- case returnedRows:
+ case RETURNED_ROWS:
data[i].setReturnedRows(text+" ");
break;
- case visitedPages:
+ case VISITED_PAGES:
data[i].setVisitedPages(text+" ");
break;
- case scanQualifiers:
+ case SCAN_QUALIFIERS:
data[i].setScanQualifiers(text+" ");
break;
- case nextQualifiers:
+ case NEXT_QUALIFIERS:
data[i].setNextQualifiers(text+" ");
break;
- case scannedObject:
+ case SCANNED_OBJECT:
data[i].setScannedObject(text+" ");
break;
- case scanType:
+ case SCAN_TYPE:
data[i].setScanType(text+" ");
break;
- case sortType:
+ case SORT_TYPE:
data[i].setSortType(text+" ");
break;
- case noOfOutputRowsBySorter:
+ case NO_OF_OUTPUT_ROWS_BY_SORTER:
data[i].setSorterOutput(text+" ");
break;
}
@@ -352,10 +361,10 @@ public class AccessDatabase {
else{
/*Other attributes are omitted from the xml document
* if they're null.
- * p_id can be null at the root.
+ * P_ID can be null at the root.
* */
switch(x){
- case p_id:
+ case P_ID:
data[i].setParent(text+"");
break;
}
@@ -371,13 +380,13 @@ public class AccessDatabase {
* @return total # of nodes
* @throws SQLException
*/
- public int noOfNodes() throws SQLException{
+ private int noOfNodes() throws SQLException{
stmt = conn.createStatement();
ResultSet results = stmt.executeQuery(
"select count(*) from " +
""+schema+".SYSXPLAIN_RESULTSETS " +
- "where STMT_ID = '"+query+"'");
+ "where STMT_ID = '"+getQuery()+"'");
results.next();
int no = results.getInt(1);
results.close();
@@ -388,7 +397,7 @@ public class AccessDatabase {
/**
*
- * @return the <statement> element
+ * @return the <statement> element
* @throws SQLException
*/
public String statement() throws SQLException{
@@ -396,7 +405,7 @@ public class AccessDatabase {
ResultSet results = stmt.executeQuery(
"select STMT_TEXT "+
"from "+schema+".SYSXPLAIN_STATEMENTS " +
- "where STMT_ID = '"+query+"'");
+ "where STMT_ID = '"+getQuery()+"'");
results.next();
String statement = results.getString(1);
results.close();
@@ -419,7 +428,7 @@ public class AccessDatabase {
* @param replace string to be added
* @return modified string
*/
- public String replace(String stmt, String expr, String replace){
+ private String replace(String stmt, String expr, String replace){
String[] part=stmt.split(expr);
String newStmt= part[0];
for(int i=1;i<part.length;i++){
@@ -428,11 +437,38 @@ public class AccessDatabase {
return newStmt;
}
+
+ /**
+ *
+ * @return XPLAIN_TIME of SYSXPLAIN_STATEMENTS
+ * @throws SQLException
+ */
+ public String time() throws SQLException{
+ stmt = conn.createStatement();
+ ResultSet results = stmt.executeQuery(
+ "select '<time>'||TRIM(CHAR(XPLAIN_TIME))||" +
+ "'</time>' from "+schema+".SYSXPLAIN_STATEMENTS " +
+ "where STMT_ID = '"+getQuery()+"'");
+ results.next();
+ String time = results.getString(1);
+ results.close();
+ stmt.close();
+
+ return time;
+ }
+
+ /**
+ *
+ * @return stmt_id as a XML element
+ */
+ public String stmtID(){
+ return "<stmt_id>"+getQuery()+"</stmt_id>";
+ }
/**
- * shut downing the connection to the database
+ * closing the connection to the database
*/
- public void shutdown()
+ public void closeConnection()
{
try
{
@@ -442,7 +478,6 @@ public class AccessDatabase {
}
if (conn != null)
{
- DriverManager.getConnection(dbURL + ";shutdown=true");
conn.close();
}
}
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java Thu Aug 12 18:02:19 2010
@@ -9,12 +9,18 @@ import java.io.File;
import java.io.FileOutputStream;
import java.net.URL;
+/**
+ * This class is used by PlanExporter tool (DERBY-4587)
+ * in order to create HTML output of a query plan
+ * using a plain XSL style sheet and a XML data of
+ * a query plan.
+ */
public class CreateHTMLFile {
public void getHTML(String XMLFileName, String XSLSheetName,
String HTMLFile, boolean def) throws Exception{
- if(!HTMLFile.endsWith(".html") && !HTMLFile.endsWith(".HTML"))
+ if(!(HTMLFile.toUpperCase()).endsWith(".HTML"))
HTMLFile +=".html";
TransformerFactory transFactory = TransformerFactory.newInstance();
@@ -31,7 +37,7 @@ public class CreateHTMLFile {
transformer =
transFactory.newTransformer(new StreamSource(XSLSheetName));
else{
- URL url=getClass().getResource("resources/"+XSLSheetName);
+ URL url=getClass().getResource("resources/vanilla_html.xsl");
transformer =
transFactory.newTransformer(new StreamSource(url.openStream()));
}
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java Thu Aug 12 18:02:19 2010
@@ -16,10 +16,6 @@ import java.security.AccessController;
import java.security.PrivilegedActionException;
import org.apache.derby.impl.tools.planexporter.AccessDatabase;
-/**
- * @author Nirmal
- *
- */
public class CreateXMLFile {
AccessDatabase access;
@@ -29,26 +25,26 @@ public class CreateXMLFile {
}
/**
- *
+ * @param stmt statement executed
+ * @param time time which the statement was executed
* @param data large xml data string array
* @param file_name name of the file to be written
* @param xsl_sheet_name name of the style sheet
* @throws PrivilegedActionException
* @throws IOException
+ * @throws PrivilegedActionException
*
*/
- public void writeTheXMLFile(String stmt,
+ public void writeTheXMLFile(String stmt, String time,
TreeNode[] data, final String file_name, String xsl_sheet_name)
- throws PrivilegedActionException, IOException {
+ throws IOException, PrivilegedActionException {
String defaultXML = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";
String embedXSL="";
if(xsl_sheet_name != null)
embedXSL ="<?xml-stylesheet type=\"text/xsl\" href=\""
+xsl_sheet_name+"\"?>\n";
- String comment = "<!-- Designed & coded by C.S.Nirmal J. Fernando," +
- " of University of Moratuwa, Sri Lanka, to Apache " +
- "Derby Query Explainer (DERBY-4587)-->\n";
+ String comment = "<!-- Apache Derby Query Explainer (DERBY-4587)-->\n";
String parentTagStart = "<plan>\n";
String parentTagEnd = "</plan>\n";
String childTagStart = "<details>\n";
@@ -69,6 +65,8 @@ public class CreateXMLFile {
dos.write(comment.getBytes());
dos.write(parentTagStart.getBytes());
dos.write((access.indent(0)+stmt).getBytes());
+ dos.write((access.indent(0)+time).getBytes());
+ dos.write((access.indent(0)+access.stmtID()).getBytes());
dos.write((access.indent(0)+childTagStart).getBytes());
dos.write(access.getXmlString().getBytes());
dos.write((access.indent(0)+childTagEnd).getBytes());
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java Thu Aug 12 18:02:19 2010
@@ -1,6 +1,12 @@
+/**
+ * This class is used by PlanExporter tool (DERBY-4587)
+ * as a data structure to keep the values retrieved
+ * after querying XPLAIN tables and few other properties
+ * of a plan node in a query plan.
+ */
package org.apache.derby.impl.tools.planexporter;
-public class TreeNode extends Object{
+class TreeNode{
private String parentId = "";//PARENT_RS_ID
private String id = "";//RS_ID
@@ -15,7 +21,7 @@ public class TreeNode extends Object{
private String scanType = "";//SCAN_TYPE
private String sortType = "";//SORT_TYPE
private String sorterOutput = "";//NO_OUTPUT_ROWS
- private String depth ;
+ private int depth ;
/**
@@ -105,13 +111,13 @@ public class TreeNode extends Object{
/**
* @param depth the depth to set
*/
- public void setDepth(String depth) {
+ public void setDepth(int depth) {
this.depth = depth;
}
/**
* @return the depth
*/
- public String getDepth() {
+ public int getDepth() {
return depth;
}
/**
@@ -191,7 +197,7 @@ public class TreeNode extends Object{
* @return XML fragment for this TreeNode object
*/
public String toString(){
- String details = new String("<node ");
+ String details = "<node ";
details += getNodeType();
details += getInputRows();
details += getReturnedRows();
@@ -205,7 +211,5 @@ public class TreeNode extends Object{
details += getSorterOutput();
return details+">\n";
-
}
-
}
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl Thu Aug 12 18:02:19 2010
@@ -74,7 +74,9 @@
<body>
<H1>Apache Derby</H1>
<H1>Graphical Query Explainer</H1>
+ <H2>Executed Date & Time: <font color="#4E9258"> <xsl:value-of select="//time"/> </font></H2>
<H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2>
+ <H2>STMT_ID: <font color="#4E9258"> <xsl:value-of select="//stmt_id"/> </font></H2>
<br></br>
<br></br>
<xsl:apply-templates/>
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl Thu Aug 12 18:02:19 2010
@@ -85,7 +85,9 @@
<body>
<H1>Apache Derby</H1>
<H1>Graphical Query Explainer</H1>
+ <H2>Executed Date & Time: <font color="#4E9258"> <xsl:value-of select="//time"/> </font></H2>
<H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2>
+ <H2>STMT_ID: <font color="#4E9258"> <xsl:value-of select="//stmt_id"/> </font></H2>
<br></br>
<br></br>
<xsl:apply-templates/>
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl Thu Aug 12 18:02:19 2010
@@ -17,7 +17,9 @@
<IMG SRC="derby-logo.png" ALIGN="left"/>
<center><H1>Apache Derby</H1></center>
<center><H1>Graphical Query Explainer</H1></center>
+ <center><H2>Executed Date & Time: <font color="#4E9258"> <xsl:value-of select="//time"/> </font></H2></center>
<center><H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2></center>
+ <center><H2>STMT_ID: <font color="#4E9258"> <xsl:value-of select="//stmt_id"/> </font></H2></center>
<xsl:apply-templates select="plan/details/node"/>
</body>
</html>
Modified: db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java?rev=984881&r1=984880&r2=984881&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java Thu Aug 12 18:02:19 2010
@@ -10,77 +10,69 @@ import org.apache.derby.impl.tools.plane
import org.apache.derby.impl.tools.planexporter.CreateHTMLFile;
import org.apache.derby.impl.tools.planexporter.CreateXMLFile;
-/**
- * @author Nirmal
- *
- */
public class PlanExporter {
private static String dbURL = null; //connection URL
private static String xslStyleSheetName ="resources/vanilla_html.xsl";//default xsl
- private static final int xml=1;
- private static final int html=2;
- private static final int xsl=3;
+ private static final int XML=1;
+ private static final int HTML=2;
+ private static final int XSL=3;
/**
* @param args
- * 1) database string eg: jdbc:derby:myDB ---------
- * 2) username ------------------------------------
- * 3) password ------------------------------------
- * 4) database schema -----------------------------
- * 5) statement ID (36 characters) ----------------
+ * 1) database URL eg: jdbc:derby:myDB ---------
+ * 2) database schema -----------------------------
+ * 3) statement ID (36 characters) ----------------
* and user specified arguments.
*/
public static void main(String[] args) {
try{
- if(args.length>6 && args.length<12 ){
- dbURL = args[0]+";create=false"+
- ";user="+args[1]+";password="+args[2];
+ if(args.length>4 && args.length<10 ){
+ dbURL = args[0];
- AccessDatabase access = new AccessDatabase(dbURL, args[3], args[4]);
+ AccessDatabase access = new AccessDatabase(dbURL, args[1], args[2]);
access.createConnection();
if(access.initializeDataArray()){
access.createXMLFragment();
access.markTheDepth();
String stmt=access.statement();
- access.shutdown();
+ String time=access.time();
+ access.closeConnection();
//advanced XSL feature
//possible occurrences are
//-adv -xml {path} -xsl {path} or
//-adv -xsl {path} -xml {path}
- if(args.length==10 &&
- args[5].equalsIgnoreCase("-adv")){
- int opt1=selectArg(args[6]);
- int opt2=selectArg(args[8]);
+ if(args.length==8 &&
+ args[3].equalsIgnoreCase("-adv")){
+ int opt1=selectArg(args[4]);
+ int opt2=selectArg(args[6]);
if(opt1==1 && opt2==3){
- if(args[9].endsWith(".xsl")||
- args[9].endsWith(".XSL"))
- generateXML(access,args[7],stmt,args[9]);
+ if(args[7].toUpperCase().endsWith(".XSL"))
+ generateXML(access,args[5],stmt,time,args[7]);
else
- generateXML(access,args[7],stmt,args[9]+".xsl");
+ generateXML(access,args[5],stmt,time,args[7]+".xsl");
}
else if(opt1==3 && opt2==1){
- if(args[7].endsWith(".xsl")||
- args[7].endsWith(".XSL"))
- generateXML(access,args[9],stmt,args[7]);
+ if(args[5].toUpperCase().endsWith(".XSL"))
+ generateXML(access,args[7],stmt,time,args[5]);
else
- generateXML(access,args[9],stmt,args[7]+".xsl");
+ generateXML(access,args[7],stmt,time,args[5]+".xsl");
}
else
printHelp();
}
//possible occurrences are -xml {path} or -html {path}
- else if(args.length==7){
- int opt=selectArg(args[5]);
+ else if(args.length==5){
+ int opt=selectArg(args[3]);
if(opt==0 || opt==3)
printHelp();
else if(opt==1)
- generateXML(access,args[6],stmt,null);
+ generateXML(access,args[4],stmt,time,null);
else{
- generateXML(access,"temp.xml",stmt,null);
- generateHTML("temp.xml",args[6],xslStyleSheetName,true);
+ generateXML(access,"temp.xml",stmt,time,null);
+ generateHTML("temp.xml",args[4],xslStyleSheetName,true);
deleteFile("temp.xml");
}
}
@@ -89,27 +81,27 @@ public class PlanExporter {
//-html {path} and -xml {path}
//-html {path} and -xsl {path}
//-xsl {path} and -html {path}
- else if(args.length==9){
- int opt1=selectArg(args[5]);
- int opt2=selectArg(args[7]);
+ else if(args.length==7){
+ int opt1=selectArg(args[3]);
+ int opt2=selectArg(args[5]);
if(opt1==0 || opt2==0)
printHelp();
else if(opt1==1 && opt2==2){
- generateXML(access,args[6],stmt,null);
- generateHTML(args[6],args[8],xslStyleSheetName,true);
+ generateXML(access,args[4],stmt,time,null);
+ generateHTML(args[4],args[6],xslStyleSheetName,true);
}
else if(opt1==2 && opt2==1){
- generateXML(access,args[8],stmt,null);
- generateHTML(args[8],args[6],xslStyleSheetName,true);
+ generateXML(access,args[6],stmt,time,null);
+ generateHTML(args[6],args[4],xslStyleSheetName,true);
}
else if(opt1==2 && opt2==3){
- generateXML(access,"temp.xml",stmt,null);
- generateHTML("temp.xml",args[6],args[8],false);
+ generateXML(access,"temp.xml",stmt,time,null);
+ generateHTML("temp.xml",args[4],args[6],false);
deleteFile("temp.xml");
}
else if(opt1==3 && opt2==2){
- generateXML(access,"temp.xml",stmt,null);
- generateHTML("temp.xml",args[8],args[6],false);
+ generateXML(access,"temp.xml",stmt,time,null);
+ generateHTML("temp.xml",args[6],args[4],false);
deleteFile("temp.xml");
}
else
@@ -122,39 +114,41 @@ public class PlanExporter {
//-xml {path} and -xsl {path} and -html {path}
//-html {path} and -xml {path} and -xsl {path}
//-xsl {path} and -html {path} and -xml {path}
- else{
- int opt1=selectArg(args[5]);
- int opt2=selectArg(args[7]);
- int opt3=selectArg(args[9]);
+ else if(args.length==9){
+ int opt1=selectArg(args[3]);
+ int opt2=selectArg(args[5]);
+ int opt3=selectArg(args[7]);
if(opt1==0 || opt2==0 || opt3==0)
printHelp();
else if(opt1==1 && opt2==2 && opt3==3){
- generateXML(access,args[6],stmt,null);
- generateHTML(args[6],args[8],args[10],false);
+ generateXML(access,args[4],stmt,time,null);
+ generateHTML(args[4],args[6],args[8],false);
}
else if(opt1==2 && opt2==3 && opt3==1){
- generateXML(access,args[10],stmt,null);
- generateHTML(args[10],args[6],args[8],false);
+ generateXML(access,args[8],stmt,time,null);
+ generateHTML(args[8],args[4],args[6],false);
}
else if(opt1==3 && opt2==1 && opt3==2){
- generateXML(access,args[8],stmt,null);
- generateHTML(args[8],args[10],args[6],false);
+ generateXML(access,args[6],stmt,time,null);
+ generateHTML(args[6],args[8],args[4],false);
}
else if(opt1==1 && opt2==3 && opt3==2){
- generateXML(access,args[6],stmt,null);
- generateHTML(args[6],args[10],args[8],false);
+ generateXML(access,args[4],stmt,time,null);
+ generateHTML(args[4],args[8],args[6],false);
}
else if(opt1==2 && opt2==1 && opt3==3){
- generateXML(access,args[8],stmt,null);
- generateHTML(args[8],args[6],args[10],false);
+ generateXML(access,args[6],stmt,time,null);
+ generateHTML(args[6],args[4],args[8],false);
}
else if(opt1==3 && opt2==2 && opt3==1){
- generateXML(access,args[10],stmt,null);
- generateHTML(args[10],args[8],args[6],false);
+ generateXML(access,args[8],stmt,time,null);
+ generateHTML(args[8],args[6],args[4],false);
}
else
printHelp();
- }
+ }
+ else
+ printHelp();
}
else{
System.out.println(
@@ -169,9 +163,9 @@ public class PlanExporter {
);
}
}
- else{
+ else
printHelp();
- }
+
}catch(Exception ex){
ex.printStackTrace();
}
@@ -184,11 +178,11 @@ public class PlanExporter {
*/
private static int selectArg(String arg){
if(arg.equalsIgnoreCase("-xml"))
- return xml;
+ return XML;
else if(arg.equalsIgnoreCase("-html"))
- return html;
+ return HTML;
else if(arg.equalsIgnoreCase("-xsl"))
- return xsl;
+ return XSL;
else
return 0;
}
@@ -198,20 +192,21 @@ public class PlanExporter {
* @param access instance of AccessDatabase class
* @param arg path of XML
* @param stmt statement executed
+ * @param time time which the statement was executed
* @param xsl name of the style sheet
* @throws Exception
*/
private static void generateXML(AccessDatabase access,
- String arg, String stmt,String xsl) throws Exception{
+ String arg, String stmt, String time, String xsl) throws Exception{
CreateXMLFile xmlFile = new CreateXMLFile(access);
- if(arg.endsWith(".xml") || arg.endsWith(".XML")){
- xmlFile.writeTheXMLFile(stmt,
+ if(arg.toUpperCase().endsWith(".XML")){
+ xmlFile.writeTheXMLFile(stmt, time,
access.getData(),
arg, xsl);
}
else{
- xmlFile.writeTheXMLFile(stmt,
+ xmlFile.writeTheXMLFile(stmt, time,
access.getData(),
arg.concat(".xml"),
xsl);
@@ -230,7 +225,7 @@ public class PlanExporter {
String style, boolean def) throws Exception{
CreateHTMLFile htmlFile = new CreateHTMLFile();
- if(arg.endsWith(".xml") || arg.endsWith(".XML")){
+ if(arg.toUpperCase().endsWith(".XML")){
htmlFile.getHTML(arg, style, path, def);
}
else{
@@ -243,29 +238,27 @@ public class PlanExporter {
(
"================================================\n" +
"-------------- PlanExporter Tool ---------------\n" +
- "-- You can pass 7 arguments (minimum), or --\n" +
- "-- 9 arguments or 10 arguments or --\n" +
- "----------- 11 arguments (maximum) -----------\n" +
+ "-- You can pass 5 arguments (minimum), or --\n" +
+ "-- 7 arguments or 8 arguments or --\n" +
+ "----------- 9 arguments (maximum) ------------\n" +
"-- separated by a space. --\n" +
"---------------Mandatory Arguments--------------\n" +
- "1) database string eg: jdbc:derby:myDB ---------\n" +
- "2) username ------------------------------------\n" +
- "3) password ------------------------------------\n" +
- "4) database schema -----------------------------\n" +
- "5) statement ID (36 characters) ----------------\n" +
+ "1) database URL --------------------------------\n" +
+ "2) database schema -----------------------------\n" +
+ "3) statement ID (36 characters) ----------------\n" +
"---------------Optional Arguments---------------\n" +
"-----------Choose at least one option-----------\n" +
- "6) -xml {pathToXML} or -html {pathToHTML} ------\n" +
- "7) -xml {pathToXML} -html {pathToHTML} ---------\n" +
- "8) -xsl {pathToXSL} -html {pathToHTML} ---------\n" +
- "9) -xml {pathToXML} -xsl {pathToXSL} -----------\n" +
+ "a) -xml {pathToXML} or -html {pathToHTML} ------\n" +
+ "b) -xml {pathToXML} -html {pathToHTML} ---------\n" +
+ "c) -xsl {pathToXSL} -html {pathToHTML} ---------\n" +
+ "d) -xml {pathToXML} -xsl {pathToXSL} -----------\n" +
" -html {pathToHTML} -----------------------\n" +
- "10) -adv -xml {pathToXML} -xsl {pathToXSL} -----\n" +
+ "e) -adv -xml {pathToXML} -xsl {pathToXSL} ------\n" +
"================================================\n"
);
}
- public static void deleteFile(final String fileName)
+ private static void deleteFile(final String fileName)
{
AccessController.doPrivileged
(new java.security.PrivilegedAction() {