db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From krist...@apache.org
Subject 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/
Date Thu, 12 Aug 2010 18:02:20 GMT
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 &lt;statement&gt; 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 &lt;node&gt; 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 &lt;statement&gt; 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 &amp; 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 &amp; 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 &amp; 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() {



Mime
View raw message