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 bp...@apache.org on 2010/08/05 16:25:39 UTC
svn commit: r982637 - in /db/derby/code/trunk: ./
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
java/tools/org/apache/derby/impl/tools/planexporter/
java/tools/org/apache/derby/impl/tools/planexporter/resources/
java/tools/org/apache/d...
Author: bpendleton
Date: Thu Aug 5 14:25:39 2010
New Revision: 982637
URL: http://svn.apache.org/viewvc?rev=982637&view=rev
Log:
DERBY-4587: Add tools for query plan analysis
This patch was contributed by C.S. Nirmal J. Fernando (nirmal070125 at gmail dot com)
This patch adds a new tool, PlanExporter, to derbytools.jar. The PlanExporter
tool's primary purpose is to gather the information about a particular query
execution from the Derby XPLAIN tables, and to format that data into an
export format for usage by other tools. The most important export format is
XML, but the tool can also format the query into HTML or other formats by
running an appropriate XSL Stylesheet across the XML data.
The tool is run as follows:
java org.apache.derby.tools.PlanExporter dbURL username pwd schema stmt_id
-xml outputFileName
This patch includes the tool itself, and changes to the XplainStatisticsTest
to incorporate regression testing of the PlanExporter tool. Documentation of
the new tool is being developed under DERBY-4758.
The basic implementation of the tool is complete and operational, and ready
for use by the Derby community. Depending on community feedback, possible
future enhancements to the tool might include improved formatting stylesheets,
additional output formats, more detailed XML output, etc.
Added:
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/
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/
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/build.xml
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
db/derby/code/trunk/tools/jar/tools.properties
Modified: db/derby/code/trunk/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/build.xml?rev=982637&r1=982636&r2=982637&view=diff
==============================================================================
--- db/derby/code/trunk/build.xml (original)
+++ db/derby/code/trunk/build.xml Thu Aug 5 14:25:39 2010
@@ -1462,6 +1462,7 @@
includesfile="${derby.jar.dir}/lists/derbytools.list"
compress="true"
filesonly="true"/>
+
<jar destfile="${derby.jar.dir}/derbytools.jar"
manifest="${derby.jar.dir}/lists/smftools.mf"
compress="true"
@@ -1471,10 +1472,11 @@
includes="org/apache/derby/loc/sysinfoMessages.properties,
org/apache/derby/loc/toolsmessages.properties,
org/apache/derby/info/tools.properties"/>
+ <fileset dir="${derby.tools.src.dir}"
+ includes="org/apache/derby/impl/tools/planexporter/resources/*.xsl"/>
<fileset dir="${derby.jar.dir}/lists"
includes="META-INF/**"/>
</jar>
-
</target>
<!-- - - - - - - - - - - - - - derbynet.jar target - - - - - - - - - - - -->
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=982637&r1=982636&r2=982637&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 5 14:25:39 2010
@@ -21,34 +21,43 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.io.FileInputStream;
+import java.io.IOException;
+import java.io.InputStream;
+import java.net.MalformedURLException;
+import java.security.AccessController;
+import java.security.PrivilegedActionException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
-import java.sql.PreparedStatement;
import java.sql.Timestamp;
-
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.Collection;
import java.util.Date;
-import java.util.HashSet;
-import java.util.Iterator;
-import java.util.List;
+import javax.xml.parsers.DocumentBuilder;
+import javax.xml.parsers.DocumentBuilderFactory;
+
+import org.xml.sax.InputSource;
+
+import junit.framework.Assert;
import junit.framework.Test;
import junit.framework.TestSuite;
-import org.apache.derby.shared.common.sanity.SanityManager;
-
+import org.apache.derby.impl.tools.planexporter.AccessDatabase;
+import org.apache.derby.impl.tools.planexporter.CreateXMLFile;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
-import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
-import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+import org.apache.derbyTesting.junit.XML;
+
+import org.w3c.dom.Document;
+import org.w3c.dom.NodeList;
/**
* This suite contains a set of tests for the new XPLAIN style of
- * runtime statistics capturing which was added as part of DERBY-2487.
+ * runtime statistics capturing which was added as part of DERBY-2487
+ * and also a set of tests within tests generated under DERBY-2487
+ * are added as part of DERBY-4587-PlanExporter tool.
*
* There are a large number of tests which follow the pattern of:
* - enable capture
@@ -64,7 +73,7 @@ import org.apache.derbyTesting.junit.SQL
* all tested at least once
*/
public class XplainStatisticsTest extends BaseJDBCTestCase {
-
+
public XplainStatisticsTest(String name) {
super(name);
}
@@ -73,7 +82,11 @@ public class XplainStatisticsTest extend
timeSuiteStarted = (new Date()).getTime();
TestSuite allTests = new TestSuite(XplainStatisticsTest.class,
"XplainStatisticsTest");
- return new CleanDatabaseTestSetup(allTests) {
+
+ Test test = allTests;
+ test = new SupportFilesSetup(test); //added by DERBY-4587
+
+ return new CleanDatabaseTestSetup(test) {
protected void decorateSQL(Statement s)
throws SQLException
{
@@ -452,6 +465,7 @@ public class XplainStatisticsTest extend
st.executeUpdate(
"delete from t where x = 3");
}
+
private boolean hasTable(String schemaName, String tableName)
throws SQLException
{
@@ -461,6 +475,7 @@ public class XplainStatisticsTest extend
rs.close();
return tableFound;
}
+
private String []tableNames = {
"SYSXPLAIN_STATEMENTS",
"SYSXPLAIN_STATEMENT_TIMINGS",
@@ -469,6 +484,7 @@ public class XplainStatisticsTest extend
"SYSXPLAIN_SORT_PROPS",
"SYSXPLAIN_SCAN_PROPS",
};
+
private void enableXplainStyle(Statement s)
throws SQLException
{
@@ -480,17 +496,59 @@ public class XplainStatisticsTest extend
s.execute("call syscs_util.syscs_set_xplain_schema('XPLTEST')");
s.execute("call syscs_util.syscs_set_xplain_mode(0)");
}
+
private void enableXplainStyleWithTiming(Statement s)
throws SQLException
{
enableXplainStyle(s);
s.execute("call syscs_util.syscs_set_statistics_timing(1)");
}
+
+ /**
+ *
+ * @param s
+ * @throws Exception
+ */
private void disableXplainStyle(Statement s)
- throws SQLException
+ throws Exception
{
- s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+ 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;
+
+ 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
+ }
}
+
private void verifyXplainUnset(Statement s)
throws SQLException
{
@@ -501,6 +559,7 @@ public class XplainStatisticsTest extend
s.executeQuery("values SYSCS_UTIL.syscs_get_xplain_mode()"),
new String[][]{{"0"}});
}
+
private void verifyNonNullDRDA_ID(Statement s)
throws SQLException
{
@@ -552,17 +611,134 @@ public class XplainStatisticsTest extend
}
rs.close();
}
- /**
- * Verify that XPLAIN style captures basic statistics and timings.
- *
- * This test runs
- *
- * SELECT Country FROM Countries WHERE Region = 'Central America'
- *
- * and verifies that there are some reasonable values captured
- * into the XPLAIN system tables.
- */
- public void testSimpleQuery() throws SQLException
+
+ /**
+ * Added by DERBY-4587
+ * Returns the stmt_id for this particular statement
+ * @param s: Statement
+ * @return stmt_id
+ * */
+ private String getStmtID(Statement s) throws SQLException{
+ ResultSet rs;
+ String stmt_id;
+ rs = s.executeQuery(
+ "select stmt_id from XPLTEST.sysxplain_statements");
+ rs.next();
+ stmt_id = rs.getString(1);
+ rs.close();
+ return stmt_id;
+ }
+
+ /**
+ * Added by DERBY-4587
+ * @param file name of the XML file - without extension
+ * @return a Document object
+ * @throws Exception
+ */
+ private Document getADocument(final String file) throws Exception{
+ Document document;
+ DocumentBuilderFactory factory =
+ DocumentBuilderFactory.newInstance();
+ DocumentBuilder builder = factory.newDocumentBuilder();
+
+ InputSource xml = new InputSource(
+ (InputStream)AccessController.doPrivileged
+ (new java.security.PrivilegedExceptionAction(){
+ public Object run()throws Exception{
+ return new FileInputStream(
+ SupportFilesSetup.getReadWriteURL(file+".xml").getPath()
+ );}})
+ );
+
+ document = builder.parse(xml);
+ document.getDocumentElement().normalize();
+
+ return document;
+ }
+
+ /**
+ * Added by DERBY-4587
+ * 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)
+ throws Exception
+ {
+ Document document;
+ document = getADocument(file);
+
+ return document.getElementsByTagName("statement").item(0).getChildNodes().item(0).getNodeValue();
+
+ }
+
+ /**
+ * Added by DERBY-4587
+ * count the # of <node> elements
+ * @param file XML file
+ * @return node count
+ * @throws Exception
+ */
+ private int countNode(final String file) throws Exception{
+ Document document;
+ document = getADocument(file);
+
+ return document.getElementsByTagName("node").getLength();
+ }
+
+ /**
+ * Added by DERBY-4587
+ * gets the "name" attributes of all nodes separated by a "|"
+ * @param file XML file
+ * @return "name" attribute's value of all nodes
+ * eg: "PROJECTION|TABLESCAN|"
+ * @throws Exception
+ */
+ private String getNodeName(final String file) throws Exception{
+ Document document;
+ document = getADocument(file);
+ NodeList lst=document.getElementsByTagName("node");
+ String name= "";
+ for(int i=0;i<lst.getLength();i++)
+ name+=lst.item(i).getAttributes().getNamedItem("name").getNodeValue()+"|";
+ return name;
+ }
+
+ /**
+ * Added by DERBY-4587
+ * gets attributes other than "name"
+ * @param file XML file
+ * @param attribute name of the attribute
+ * @param node node which this attribute belongs to
+ * @return if attribute doesn't exist ""
+ * else value of the attribute
+ * @throws Exception
+ */
+ private String getNodeAttribute(final String file, String attribute, int node) throws Exception{
+ Document document;
+ document = getADocument(file);
+ NodeList lst=document.getElementsByTagName("node");
+ if(lst.item(node).getAttributes().getNamedItem(attribute)==null)
+ return "";
+ return lst.item(node).getAttributes().getNamedItem(attribute).getNodeValue();
+ }
+
+
+ /**
+ * Verify that XPLAIN style captures basic statistics and timings.
+ *
+ * This test runs
+ *
+ * SELECT Country FROM Countries WHERE Region = 'Central America'
+ *
+ * and verifies that there are some reasonable values captured
+ * into the XPLAIN system tables.
+ * @throws IOException
+ * @throws PrivilegedActionException
+ * @throws MalformedURLException
+ */
+ public void testSimpleQuery() throws Exception
{
Statement s = createStatement();
@@ -576,7 +752,7 @@ public class XplainStatisticsTest extend
{"Guatemala"}, {"Honduras"}, {"Nicaragua"} } );
disableXplainStyle(s);
-
+
// The statement should have been executed as a PROJECTION
// wrapped around a TABLESCAN. The TABLESCAN should have had
// scan properties. The TABLESCAN should have filtered 114 rows
@@ -660,8 +836,44 @@ public class XplainStatisticsTest extend
verifySensibleStatementTimings(s);
verifySensibleResultSetTimings(s);
+
+ /*
+ * This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ * */
+
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(2,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for TABLESCAN node test whether the scan related entries
+ //are exist and verify their values.
+ Assert.assertEquals("COUNTRIES",
+ getNodeAttribute(stmt_id,"scanned_object",1));
+ Assert.assertEquals("HEAP",
+ getNodeAttribute(stmt_id,"scan_type",1));
+ Assert.assertEquals("2",
+ getNodeAttribute(stmt_id,"visited_pages",1));
+
+ }
}
-
+
+
+
/**
* Make some basic first-order checks on the STATEMENT_TIMINGS rows.
*
@@ -844,7 +1056,7 @@ public class XplainStatisticsTest extend
* This test runs a query against FLIGHTS using the dest_airport
* index, and verifies the captured query plan statistics.
*/
- public void testIndexScan() throws SQLException
+ public void testIndexScan() throws Exception
{
Statement s = createStatement();
@@ -900,6 +1112,38 @@ public class XplainStatisticsTest extend
"4", "3", "1", "-1", "16", "2", "ALL"} } );
verifySensibleStatementTimings(s);
verifySensibleResultSetTimings(s);
+
+
+ /* This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(3,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|ROWIDSCAN|INDEXSCAN|",
+ getNodeName(stmt_id));
+
+ //for INDEXSCAN node test whether the scan related entries
+ //are exist and verify their values.
+ Assert.assertEquals("DESTINDEX",
+ getNodeAttribute(stmt_id,"scanned_object",2));
+ Assert.assertEquals("BTREE",
+ getNodeAttribute(stmt_id,"scan_type",2));
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"visited_pages",2));
+ }
}
/**
@@ -909,7 +1153,7 @@ public class XplainStatisticsTest extend
* COUNTRY_ISO_CODE constraint,
* and verifies the captured query plan statistics.
*/
- public void testConstraintScan() throws SQLException
+ public void testConstraintScan() throws Exception
{
Statement s = createStatement();
@@ -936,6 +1180,38 @@ public class XplainStatisticsTest extend
"where rs.op_identifier='CONSTRAINTSCAN'"),
new String[][] { {"COUNTRIES_UNQ_NM", "C", "BTREE",
"RC", "1", "1", "1", "SH", "R", "2", "ALL"} } );
+
+ /*
+ * This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(3,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|ROWIDSCAN|CONSTRAINTSCAN|",
+ getNodeName(stmt_id));
+
+ //for CONSTRAINTSCAN node test whether the scan related entries
+ //are exist and verify their values.
+ Assert.assertEquals("COUNTRIES_UNQ_NM",
+ getNodeAttribute(stmt_id,"scanned_object",2));
+ Assert.assertEquals("BTREE",
+ getNodeAttribute(stmt_id,"scan_type",2));
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"visited_pages",2));
+ }
}
/**
@@ -945,7 +1221,7 @@ public class XplainStatisticsTest extend
* verifies that reasonable values are captured for the sort properties.
*/
public void testGroupBySortProps()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
@@ -1010,16 +1286,46 @@ public class XplainStatisticsTest extend
{"IN","114","12",null, null, null,"N","N"} } );
verifySensibleStatementTimings(s);
verifySensibleResultSetTimings(s);
+
+
+ /* * This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|GROUPBY|PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for GROUPBY node test whether the sort related entries
+ //are exist and verify their values.
+ Assert.assertEquals("IN",
+ getNodeAttribute(stmt_id,"sort_type",1));
+ Assert.assertEquals("12",
+ getNodeAttribute(stmt_id,"sorter_output",1));
+ }
}
/**
- * Verify XPLAIN style handling of DISINCT_AGGREGATE sort properties.
+ * Verify XPLAIN style handling of DISTINCT_AGGREGATE sort properties.
*
* This test runs a query which involves a distinct aggreagte and
* verifies that the DISTINCT_AGGREGATE field in SORT_PROPS gets set.
*/
public void testDistinctAggregateSortProps()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
@@ -1048,12 +1354,46 @@ public class XplainStatisticsTest extend
{"IN","10","10",null, null, null,"N","Y"} } );
verifySensibleStatementTimings(s);
verifySensibleResultSetTimings(s);
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ // This statement is executed as a PROJECTION with a child GROUPBY
+ // with a child PROJECTION with a child TABLESCAN.
+
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|GROUPBY|PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for GROUPBY node test whether the sort related entries
+ //are exist and verify their values.
+ Assert.assertEquals("IN",
+ getNodeAttribute(stmt_id,"sort_type",1));
+ Assert.assertEquals("10",
+ getNodeAttribute(stmt_id,"sorter_output",1));
+ }
}
/**
* A simple test of an AGGREGATION result set.
*/
public void testAggregationResultSet()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1075,14 +1415,44 @@ public class XplainStatisticsTest extend
new String[][] { {"AGGREGATION", "DISTINCT", "1"} } );
//
// FIXME -- why are INPUT_ROWS, SEEN_ROWS, FILTERED_ROWS, and
- // RETURNED_ROWS apparently meaninglyess for an AGGREGATION RS?
+ // RETURNED_ROWS apparently meaningless for an AGGREGATION RS?
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|AGGREGATION|PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for AGGREGATION node test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",1));
+ }
+
}
/**
* A simple test of an INSERT result set.
*/
public void testInsertResultSet()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
// Make sure we don't have the tuple to be inserted already:
@@ -1130,13 +1500,47 @@ public class XplainStatisticsTest extend
"where op_identifier = 'ROW'"),
new String[][] {
{"ROW",null,"1","0","0","1"} } );
+
+
+ /* * This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ insertStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(3,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("INSERT|NORMALIZE|ROW|",
+ getNodeName(stmt_id));
+
+ //for ROW node, test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",1));
+
+ //for NORMALIZE node, test returned_rows entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"returned_rows",1));
+ }
}
/**
* A simple test of an UPDATE result set.
*/
public void testUpdateResultSet()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
s.executeUpdate("delete from AIRLINES");
@@ -1229,13 +1633,73 @@ public class XplainStatisticsTest extend
"select count(*) from xpltest.sysxplain_scan_props " +
" where start_position is not null " +
" and stop_position is not null"), "1");
+
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ updateStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("UPDATE|PROJECTION|ROWIDSCAN|CONSTRAINTSCAN|",
+ getNodeName(stmt_id));
+
+ //for UPDATE node, test no_opens entry.
+ //Since it's null, there should be no such entry.
+ Assert.assertEquals("",
+ getNodeAttribute(stmt_id,"no_opens",0));
+
+ //for PROJECTION node, test returned_rows entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"returned_rows",1));
+
+ //for PROJECTION node, test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",1));
+
+ //for ROWIDSCAN node, test returned_rows entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"returned_rows",2));
+
+ //for ROWIDSCAN node, test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",2));
+
+ //for CONSTRAINTSCAN node, test scan_qualifiers entry
+ //is exist and verify its value.
+ Assert.assertEquals("None",
+ getNodeAttribute(stmt_id,"scan_qualifiers",3));
+
+ //for CONSTRAINTSCAN node, test visited_pages entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"visited_pages",3));
+ }
}
/**
* A simple test of an DELETE result set.
*/
public void testDeleteResultSet()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
s.executeUpdate("delete from AIRLINES");
@@ -1278,13 +1742,42 @@ public class XplainStatisticsTest extend
"where op_identifier = 'DELETE'"),
new String[][] {
{"DELETE",null,null,"1",null,"R",null,"1","N"} } );
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ deleteStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("DELETE|PROJECTION|PROJECTION|CONSTRAINTSCAN|",
+ getNodeName(stmt_id));
+
+ //for DELETE node, test no_opens entry.
+ //Since it's null, there should be no such entry.
+ Assert.assertEquals("",
+ getNodeAttribute(stmt_id,"no_opens",0));
+ }
}
/**
* A simple test of a SORT result set.
*/
public void testSortResultSet()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1324,13 +1817,47 @@ public class XplainStatisticsTest extend
new String[][] {
{selectStatement, "SORT", "114", "114", "IN", "N", "N", null}
} );
+
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ **/
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals("PROJECTION|SORT|PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for SORT node test whether the sort related entries
+ //are exist and verify their values.
+ Assert.assertEquals("IN",
+ getNodeAttribute(stmt_id,"sort_type",1));
+ Assert.assertEquals("114",
+ getNodeAttribute(stmt_id,"sorter_output",1));
+ Assert.assertEquals("114",
+ getNodeAttribute(stmt_id,"input_rows",1));
+ }
}
/**
* A simple test of a UNION query.
*/
public void testUnionQuery()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1389,13 +1916,54 @@ public class XplainStatisticsTest extend
new String[][] {
{selectStatement, "SORT", "25", "25", "IN", "Y", "N", null}
} );
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(6,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals(
+ "SORT|UNION|PROJECTION|TABLESCAN|PROJECTION|TABLESCAN|",
+ getNodeName(stmt_id));
+
+ //for SORT node test whether the sort related entries
+ //are exist and verify their values.
+ Assert.assertEquals("IN",
+ getNodeAttribute(stmt_id,"sort_type",0));
+ Assert.assertEquals("25",
+ getNodeAttribute(stmt_id,"sorter_output",0));
+ Assert.assertEquals("25",
+ getNodeAttribute(stmt_id,"input_rows",0));
+
+ //for UNION node test whether the related entries
+ //are exist and verify their values.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",1));
+ Assert.assertEquals("25",
+ getNodeAttribute(stmt_id,"returned_rows",1));
+ }
}
/**
* A simple test of capturing statistics for a DDL statement.
*/
public void testDDLCreateTable()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1416,7 +1984,7 @@ public class XplainStatisticsTest extend
* A simple test of the INDEX_KEY_OPT special situation.
*/
public void testMaxFromIndex()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1468,13 +2036,63 @@ public class XplainStatisticsTest extend
{"COUNTRIES_PK", "I",
// null,
"RC", null, null, null, null, null, null, null} } );
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(4,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals(
+ "PROJECTION|AGGREGATION|PROJECTION|LASTINDEXKEYSCAN|",
+ getNodeName(stmt_id));
+
+ //for LASTINDEXKEYSCAN node, test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",3));
+
+ //for LASTINDEXKEYSCAN node, test returned_rows entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"returned_rows",3));
+
+ //for LASTINDEXKEYSCAN node, test scanned_object entry
+ //is exist and verify its value.
+ Assert.assertEquals("COUNTRIES_PK",
+ getNodeAttribute(stmt_id,"scanned_object",3));
+
+ //for LASTINDEXKEYSCAN node, test visited_pages entry.
+ //Since it's null, there should be no entry in xml.
+ Assert.assertEquals("",
+ getNodeAttribute(stmt_id,"visited_pages",3));
+
+ //for LASTINDEXKEYSCAN node, test scan_qualifiers entry.
+ //Since it's null, there should be no entry in xml.
+ Assert.assertEquals("",
+ getNodeAttribute(stmt_id,"scan_qualifiers",3));
+ }
}
/**
* A simple test of a LEFT OUTER JOIN and EMPTY_RIGHT_ROWS values.
*/
public void testOuterJoin()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1514,13 +2132,48 @@ public class XplainStatisticsTest extend
new String[][] {
{"LONLJOIN", "(1), Nested Loop Left Outer Join ResultSet",
"1", "10", "0", "0", "10", "0"} } );
+
+
+ /** This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(5,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals(
+ "PROJECTION|LONLJOIN|INDEXSCAN|ROWIDSCAN|CONSTRAINTSCAN|",
+ getNodeName(stmt_id));
+
+ //for LONLJOIN node, test no_opens entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"no_opens",1));
+
+ //for LONLJOIN node, test returned_rows entry
+ //is exist and verify its value.
+ Assert.assertEquals("10",
+ getNodeAttribute(stmt_id,"returned_rows",1));
+ }
}
/**
* A simple test to verify that startPosition and stopPosition work.
*/
public void testScanPositions()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
// Try several different syntaxes of index scans, to see what we get:
@@ -1593,7 +2246,7 @@ public class XplainStatisticsTest extend
* A simple test of a non-zero value for numDeletedRowsVisited.
*/
public void testScanDeletedRows()
- throws SQLException
+ throws Exception
{
Statement s = createStatement();
enableXplainStyle(s);
@@ -1601,7 +2254,7 @@ public class XplainStatisticsTest extend
JDBC.assertUnorderedResultSet(s.executeQuery(selectStatement),
new String[][] { {"1"},{"2"},{"4"} });
disableXplainStyle(s);
-
+
// There should be a CONSTRAINTSCAN result set with a SCAN PROPS
// which indicates that we visited 1 deleted row while scanning
// the index.
@@ -1618,6 +2271,47 @@ public class XplainStatisticsTest extend
"where rs.op_identifier='CONSTRAINTSCAN'"),
new String[][] {
{"C","BTREE","RC","4","3","1","1","1","{0}","None"}});
+
+
+
+ /* * This test is added by DERBY-4587, to verify the content
+ * of the XML file generated by the new tool, PlanExporter.
+ *
+ * */
+ if(XML.classpathMeetsXMLReqs()){
+ //getting the stmt_id, because files are generated using
+ //stmt_id as their name.
+ String stmt_id = getStmtID(s);
+
+ //testing the <statement> element
+ Assert.assertEquals(
+ selectStatement,
+ readStatement(stmt_id));
+
+ //testing the count of <node> elements
+ Assert.assertEquals(1,
+ countNode(stmt_id));
+
+ //testing the root <node> element's name attributes
+ Assert.assertEquals(
+ "CONSTRAINTSCAN|",
+ getNodeName(stmt_id));
+
+ //for CONSTRAINTSCAN node, test scan_type entry
+ //is exist and verify its value.
+ Assert.assertEquals("BTREE",
+ getNodeAttribute(stmt_id,"scan_type",0));
+
+ //for CONSTRAINTSCAN node, test visited_pages entry
+ //is exist and verify its value.
+ Assert.assertEquals("1",
+ getNodeAttribute(stmt_id,"visited_pages",0));
+
+ //for CONSTRAINTSCAN node, test visited_pages entry
+ //is exist and verify its value.
+ Assert.assertEquals("None",
+ getNodeAttribute(stmt_id,"scan_qualifiers",0));
+ }
}
/**
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/AccessDatabase.java Thu Aug 5 14:25:39 2010
@@ -0,0 +1,459 @@
+/**
+ * This class will perform the database connection establishment,
+ * querying the database, shut downing the database.
+ * Created under DERBY-4587-PlanExporter tool
+ */
+
+package org.apache.derby.impl.tools.planexporter;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+
+/**
+ * @author Nirmal
+ *
+ */
+public class AccessDatabase {
+
+ private Connection conn = null;
+ private Statement stmt = null;
+ private TreeNode[] data;
+ private String dbURL = null;
+ private String schema = null;
+ private String query = null;
+ private int depth = 0;
+ public int getDepth() {
+ return depth;
+ }
+ 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;
+
+
+ /**
+ *
+ * @param dburl
+ * @param aSchema
+ * @param aQuery
+ */
+ public AccessDatabase(String dburl, String aSchema, String aQuery) {
+
+ dbURL = dburl;
+ schema = aSchema;
+ query = aQuery;
+
+ }
+
+ /**
+ *
+ * @param aConn
+ * @param aSchema
+ * @param aQuery
+ */
+ public AccessDatabase(Connection aConn, String aSchema, String aQuery) {
+
+ conn = aConn;
+ schema = aSchema;
+ query = aQuery;
+
+ }
+
+ /**
+ *
+ * @throws InstantiationException
+ * @throws IllegalAccessException
+ * @throws ClassNotFoundException
+ * @throws SQLException
+ */
+ public void createConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
+ {
+
+ if(dbURL.indexOf("//") != -1)
+ Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
+
+ else
+ Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
+
+ //Get a connection
+ conn = DriverManager.getConnection(dbURL);
+
+ }
+
+ /**
+ * <p>
+ * This method creates the queries such that after execution
+ * of the query it will return XML data fragments.
+ * </P>
+ * @throws SQLException
+ * */
+ public void createXMLFragment() throws SQLException{
+ createXMLData(
+ "select 'id=\"' ||RS_ID|| '\"' " +
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", id);
+
+ createXMLData(
+ "select PARENT_RS_ID "+
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", p_id);
+
+ createXMLData(
+ "select 'name=\"' ||OP_IDENTIFIER|| '\"' " +
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", nodeType);
+
+ createXMLData(
+ "select 'no_opens=\"' " +
+ "|| TRIM(CHAR(NO_OPENS))|| '\"' " +
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", noOfOpens);
+
+ createXMLData(
+ "select 'input_rows=\"' " +
+ "|| TRIM(CHAR(INPUT_ROWS))|| '\"' " +
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", inputRows);
+
+ createXMLData(
+ "select 'returned_rows=\"' " +
+ "|| TRIM(CHAR(RETURNED_ROWS))|| '\"' " +
+ "from "+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'", returnedRows);
+
+ 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);
+
+ createXMLData(
+ "select 'scan_qualifiers=\"'"+
+ "||SCAN_QUALIFIERS|| '\"' " +
+ "from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
+ "NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
+ "where STMT_ID = '"+query+"'", scanQualifiers);
+
+ createXMLData(
+ "select 'next_qualifiers=\"'"+
+ "||NEXT_QUALIFIERS|| '\"' " +
+ "from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
+ "NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
+ "where STMT_ID = '"+query+"'", nextQualifiers);
+
+ createXMLData(
+ "select 'scanned_object=\"'"+
+ "||SCAN_OBJECT_NAME|| '\"' " +
+ "from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
+ "NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
+ "where STMT_ID = '"+query+"'", scannedObject);
+
+ createXMLData(
+ "select 'scan_type=\"'"+
+ "||TRIM(SCAN_TYPE)|| '\"' " +
+ "from ("+schema+".SYSXPLAIN_SCAN_PROPS " +
+ "NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
+ "where STMT_ID = '"+query+"'", scanType);
+
+ createXMLData(
+ "select 'sort_type=\"'"+
+ "||TRIM(SORT_TYPE)|| '\"' " +
+ "from ("+schema+".SYSXPLAIN_SORT_PROPS " +
+ "NATURAL RIGHT OUTER JOIN "+schema+".SYSXPLAIN_RESULTSETS) " +
+ "where STMT_ID = '"+query+"'", sortType);
+
+ 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);
+
+ }
+
+ /**
+ *
+ * @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
+ xmlDetails += indent(1);
+ xmlDetails += data[i].toString();
+ getChildren(1, data[i].getId());
+ xmlDetails += indent(1)+"</node>\n";
+ break;
+ }
+ }
+ return xmlDetails;
+ }
+
+ /**
+ *
+ * @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 ){
+ if(currentLevel <= depth){
+ for(int i=0;i<data.length;i++){
+ if(Integer.parseInt(data[i].getDepth())== currentLevel &&
+ (id.indexOf(data[i].getParent()) != -1))
+ {
+ xmlDetails += indent(currentLevel+1);
+ xmlDetails += data[i].toString();
+ getChildren(currentLevel+1, data[i].getId());
+ xmlDetails += indent(currentLevel+1)+"</node>\n";
+ }
+ }
+ }
+ }
+
+ /**
+ *
+ * @param j indent needed
+ * @return indent as a string
+ */
+ public String indent(int j){
+ String str="";
+ for(int i=0;i<=j+1;i++)
+ str +=" ";
+
+ return str;
+ }
+
+ /**
+ * marking the depth of each element
+ */
+ public void markTheDepth(){
+ int i=0;
+ while(data[i].getParent().indexOf("null")== -1)
+ i++;
+ data[i].setDepth(""+depth); //root
+ findChildren(i,depth);
+ }
+
+ /**
+ *
+ * @param idx current element's index
+ * @param dep current examining depth
+ */
+ public void findChildren(int idx, int dep) {
+ if(dep>depth)
+ depth =dep;
+
+ for(int i=0;i<data.length;i++){
+
+ if(data[i].getParent().indexOf("null")== -1){
+ if((data[idx].getId()).indexOf(data[i].getParent()) != -1
+ && i != idx)
+ {
+ data[i].setDepth(""+(dep +1));
+ findChildren(i,dep+1);
+ }
+ }
+ }
+ }
+
+
+ /**
+ *
+ * @return whether the initialization is successful or not
+ * @throws SQLException
+ */
+ public boolean initializeDataArray() throws SQLException{
+ if(noOfNodes()==0)
+ return false;
+ else{
+ data = new TreeNode[noOfNodes()];
+ for(int i=0; i<data.length;i++){
+ data[i] = new TreeNode();
+ }
+ return true;
+ }
+
+ }
+
+ /**
+ *
+ * @param qry query to be executed
+ * @throws SQLException
+ */
+ private void createXMLData(String qry, int x) throws SQLException{
+
+ stmt = conn.createStatement();
+ ResultSet results = stmt.executeQuery(qry);
+
+ int i=0;
+ while(results.next())
+ {
+ String text= results.getString(1);
+ if(text != null){
+ switch(x){
+ case id:
+ data[i].setId(text+" ");
+ break;
+ case p_id:
+ data[i].setParent(text);
+ break;
+ case nodeType:
+ data[i].setNodeType(text+" ");
+ break;
+ case noOfOpens:
+ data[i].setNoOfOpens(text+" ");
+ break;
+ case inputRows:
+ data[i].setInputRows(text+" ");
+ break;
+ case returnedRows:
+ data[i].setReturnedRows(text+" ");
+ break;
+ case visitedPages:
+ data[i].setVisitedPages(text+" ");
+ break;
+ case scanQualifiers:
+ data[i].setScanQualifiers(text+" ");
+ break;
+ case nextQualifiers:
+ data[i].setNextQualifiers(text+" ");
+ break;
+ case scannedObject:
+ data[i].setScannedObject(text+" ");
+ break;
+ case scanType:
+ data[i].setScanType(text+" ");
+ break;
+ case sortType:
+ data[i].setSortType(text+" ");
+ break;
+ case noOfOutputRowsBySorter:
+ data[i].setSorterOutput(text+" ");
+ break;
+ }
+ }
+ else{
+ /*Other attributes are omitted from the xml document
+ * if they're null.
+ * p_id can be null at the root.
+ * */
+ switch(x){
+ case p_id:
+ data[i].setParent(text+"");
+ break;
+ }
+ }
+ i++;
+ }
+ results.close();
+ stmt.close();
+ }
+
+ /**
+ *
+ * @return total # of nodes
+ * @throws SQLException
+ */
+ public int noOfNodes() throws SQLException{
+
+ stmt = conn.createStatement();
+ ResultSet results = stmt.executeQuery(
+ "select count(*) from " +
+ ""+schema+".SYSXPLAIN_RESULTSETS " +
+ "where STMT_ID = '"+query+"'");
+ results.next();
+ int no = results.getInt(1);
+ results.close();
+ stmt.close();
+ return no;
+ }
+
+
+ /**
+ *
+ * @return the <statement> element
+ * @throws SQLException
+ */
+ public String statement() throws SQLException{
+ stmt = conn.createStatement();
+ ResultSet results = stmt.executeQuery(
+ "select STMT_TEXT "+
+ "from "+schema+".SYSXPLAIN_STATEMENTS " +
+ "where STMT_ID = '"+query+"'");
+ results.next();
+ String statement = results.getString(1);
+ results.close();
+ stmt.close();
+ /*Removing possible less than and greater than characters
+ * in a query statement with XML representation.*/
+ if(statement.indexOf('<')!= -1){
+ statement = replace(statement, "<","<");
+ }
+ if(statement.indexOf('>')!= -1){
+ statement = replace(statement, ">",">");
+ }
+ return "<statement>"+statement+"</statement>\n";
+ }
+
+ /**
+ *
+ * @param stmt statement to be changed
+ * @param expr string to be removed
+ * @param replace string to be added
+ * @return modified string
+ */
+ public 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++){
+ newStmt += " "+replace+" "+part[i];
+ }
+
+ return newStmt;
+ }
+
+ /**
+ * shut downing the connection to the database
+ */
+ public void shutdown()
+ {
+ try
+ {
+ if (stmt != null)
+ {
+ stmt.close();
+ }
+ if (conn != null)
+ {
+ DriverManager.getConnection(dbURL + ";shutdown=true");
+ conn.close();
+ }
+ }
+ catch (SQLException sqlExcept){}
+ }
+
+ /**
+ *
+ * @return data array of TreeNode Objects
+ */
+ public TreeNode[] getData() {
+ return data;
+ }
+}
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java Thu Aug 5 14:25:39 2010
@@ -0,0 +1,43 @@
+package org.apache.derby.impl.tools.planexporter;
+
+import javax.xml.transform.TransformerFactory;
+import javax.xml.transform.Transformer;
+import javax.xml.transform.stream.StreamSource;
+import javax.xml.transform.stream.StreamResult;
+
+import java.io.File;
+import java.io.FileOutputStream;
+import java.net.URL;
+
+public class CreateHTMLFile {
+
+ public void getHTML(String XMLFileName, String XSLSheetName,
+ String HTMLFile, boolean def) throws Exception{
+
+ if(!HTMLFile.endsWith(".html") && !HTMLFile.endsWith(".HTML"))
+ HTMLFile +=".html";
+
+ TransformerFactory transFactory = TransformerFactory.newInstance();
+ Transformer transformer;
+
+ if(def){
+ URL url=getClass().getResource(XSLSheetName);
+ transformer =
+ transFactory.newTransformer(new StreamSource(url.openStream()));
+ }
+ else{
+ File style=new File(XSLSheetName);
+ if(style.exists())
+ transformer =
+ transFactory.newTransformer(new StreamSource(XSLSheetName));
+ else{
+ URL url=getClass().getResource("resources/"+XSLSheetName);
+ transformer =
+ transFactory.newTransformer(new StreamSource(url.openStream()));
+ }
+ }
+
+ transformer.transform(new StreamSource(XMLFileName),
+ new StreamResult(new FileOutputStream(HTMLFile)));
+ }
+}
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/CreateXMLFile.java Thu Aug 5 14:25:39 2010
@@ -0,0 +1,78 @@
+/**
+ * This class is to create the final xml file, that will be used
+ * by the Graphical Query Explainer.
+ * This is called from org.apache.derby.tools.PlanExporter.
+ *
+ * */
+
+package org.apache.derby.impl.tools.planexporter;
+
+import java.io.BufferedOutputStream;
+import java.io.DataOutputStream;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.OutputStream;
+import java.security.AccessController;
+import java.security.PrivilegedActionException;
+import org.apache.derby.impl.tools.planexporter.AccessDatabase;
+
+/**
+ * @author Nirmal
+ *
+ */
+public class CreateXMLFile {
+
+ AccessDatabase access;
+
+ public CreateXMLFile(AccessDatabase access) {
+ this.access = access;
+ }
+
+ /**
+ *
+ * @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
+ *
+ */
+ public void writeTheXMLFile(String stmt,
+ TreeNode[] data, final String file_name, String xsl_sheet_name)
+ throws PrivilegedActionException, IOException {
+
+ 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 parentTagStart = "<plan>\n";
+ String parentTagEnd = "</plan>\n";
+ String childTagStart = "<details>\n";
+ String childTagEnd = "</details>\n";
+
+ DataOutputStream dos =
+ new DataOutputStream(
+ new BufferedOutputStream(
+ (OutputStream)AccessController.doPrivileged
+ (new java.security.PrivilegedExceptionAction(){
+ public Object run() throws IOException{
+ return new FileOutputStream(file_name);
+ }
+ })));
+
+ dos.write(defaultXML.getBytes());
+ dos.write(embedXSL.getBytes());
+ dos.write(comment.getBytes());
+ dos.write(parentTagStart.getBytes());
+ dos.write((access.indent(0)+stmt).getBytes());
+ dos.write((access.indent(0)+childTagStart).getBytes());
+ dos.write(access.getXmlString().getBytes());
+ dos.write((access.indent(0)+childTagEnd).getBytes());
+ dos.write(parentTagEnd.getBytes());
+ dos.close();
+ }
+}
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/TreeNode.java Thu Aug 5 14:25:39 2010
@@ -0,0 +1,211 @@
+package org.apache.derby.impl.tools.planexporter;
+
+public class TreeNode extends Object{
+
+ private String parentId = "";//PARENT_RS_ID
+ private String id = "";//RS_ID
+ private String nodeType = "";//OP_IDENTIFIER
+ private String noOfOpens = "";//NO_OPENS
+ private String inputRows = "";//INPUT_ROWS
+ private String returnedRows = "";//RETURNED_ROWS
+ private String visitedPages = "";//NO_VISITED_PAGES
+ private String scanQualifiers = "";//SCAN_QUALIFIERS
+ private String nextQualifiers = "";//NEXT_QUALIFIERS
+ private String scannedObject = "";//SCAN_OBJECT_NAME
+ private String scanType = "";//SCAN_TYPE
+ private String sortType = "";//SORT_TYPE
+ private String sorterOutput = "";//NO_OUTPUT_ROWS
+ private String depth ;
+
+
+ /**
+ * @param id the id to set
+ */
+ public void setId(String id) {
+ this.id = id;
+ }
+ /**
+ * @return the id
+ */
+ public String getId() {
+ return id;
+ }
+ /**
+ * @param parentId the parentId to set
+ */
+ public void setParent(String parentId) {
+ this.parentId = parentId;
+ }
+ /**
+ * @return the parentId
+ */
+ public String getParent() {
+ return parentId;
+ }
+ /**
+ * @param nodeType the nodeType to set
+ */
+ public void setNodeType(String nodeType) {
+ this.nodeType = nodeType;
+ }
+ /**
+ * @return the nodeType
+ */
+ public String getNodeType() {
+ return nodeType;
+ }
+ /**
+ * @param noOfOpens the noOfOpens to set
+ */
+ public void setNoOfOpens(String noOfOpens) {
+ this.noOfOpens = noOfOpens;
+ }
+ /**
+ * @return the noOfOpens
+ */
+ public String getNoOfOpens() {
+ return noOfOpens;
+ }
+ /**
+ * @param inputRows the inputRows to set
+ */
+ public void setInputRows(String inputRows) {
+ this.inputRows = inputRows;
+ }
+ /**
+ * @return the inputRows
+ */
+ public String getInputRows() {
+ return inputRows;
+ }
+ /**
+ * @param returnedRows the returnedRows to set
+ */
+ public void setReturnedRows(String returnedRows) {
+ this.returnedRows = returnedRows;
+ }
+ /**
+ * @return the returnedRows
+ */
+ public String getReturnedRows() {
+ return returnedRows;
+ }
+ /**
+ * @param visitedPages the visitedPages to set
+ */
+ public void setVisitedPages(String visitedPages) {
+ this.visitedPages = visitedPages;
+ }
+ /**
+ * @return the visitedPages
+ */
+ public String getVisitedPages() {
+ return visitedPages;
+ }
+ /**
+ * @param depth the depth to set
+ */
+ public void setDepth(String depth) {
+ this.depth = depth;
+ }
+ /**
+ * @return the depth
+ */
+ public String getDepth() {
+ return depth;
+ }
+ /**
+ * @param scanQualifiers the scanQualifiers to set
+ */
+ public void setScanQualifiers(String scanQualifiers) {
+ this.scanQualifiers = scanQualifiers;
+ }
+ /**
+ * @return the scanQualifiers
+ */
+ public String getScanQualifiers() {
+ return scanQualifiers;
+ }
+ /**
+ * @param nextQualifiers the nextQualifiers to set
+ */
+ public void setNextQualifiers(String nextQualifiers) {
+ this.nextQualifiers = nextQualifiers;
+ }
+ /**
+ * @return the nextQualifiers
+ */
+ public String getNextQualifiers() {
+ return nextQualifiers;
+ }
+ /**
+ * @param scannedObject the scannedObject to set
+ */
+ public void setScannedObject(String scannedObject) {
+ this.scannedObject = scannedObject;
+ }
+ /**
+ * @return the scannedObject
+ */
+ public String getScannedObject() {
+ return scannedObject;
+ }
+ /**
+ * @param scanType the scanType to set
+ */
+ public void setScanType(String scanType) {
+ this.scanType = scanType;
+ }
+ /**
+ * @return the scanType
+ */
+ public String getScanType() {
+ return scanType;
+ }
+ /**
+ * @param sortType the sortType to set
+ */
+ public void setSortType(String sortType) {
+ this.sortType = sortType;
+ }
+ /**
+ * @return the sortType
+ */
+ public String getSortType() {
+ return sortType;
+ }
+ /**
+ * @param sorterOutput the sorterOutput to set
+ */
+ public void setSorterOutput(String sorterOutput) {
+ this.sorterOutput = sorterOutput;
+ }
+ /**
+ * @return the sorterOutput
+ */
+ public String getSorterOutput() {
+ return sorterOutput;
+ }
+
+ /**
+ * @return XML fragment for this TreeNode object
+ */
+ public String toString(){
+ String details = new String("<node ");
+ details += getNodeType();
+ details += getInputRows();
+ details += getReturnedRows();
+ details += getNoOfOpens();
+ details += getVisitedPages();
+ details += getScanQualifiers();
+ details += getNextQualifiers();
+ details += getScannedObject();
+ details += getScanType();
+ details += getSortType();
+ details += getSorterOutput();
+
+ return details+">\n";
+
+ }
+
+}
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl Thu Aug 5 14:25:39 2010
@@ -0,0 +1,168 @@
+<xsl:stylesheet
+ xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
+ version="1.0">
+ <!-- Designed & coded by C.S.Nirmal J. Fernando, of University of Moratuwa, Sri Lanka -->
+ <xsl:output method="html" indent="yes"
+ doctype-public="-//W3C//DTD HTML 4.01//EN"
+ doctype-system="http://www.w3.org/TR/html4/strict.dtd"/>
+
+ <xsl:strip-space elements="*"/>
+
+ <xsl:template match="/">
+ <html>
+ <head>
+ <title>Derby Graphical Query Explainer</title>
+ <style type="text/css">
+ H1,H2{text-align:center;}
+ ul{list-style-type: none;}
+ table.hide { display: none; }
+ table,td,th
+ {
+ border:2px solid black;
+ font-size:18px;
+ position:relative;
+ left:160px;
+ }
+ th
+ {
+ text-align:left;
+ background-color:#999933;
+ color:white;
+ }
+ td{text-align:center;}
+ span.expand, span.collapse
+ {
+ color:#3B5998;
+ font-size:20px;
+ position:relative;
+ left:150px;
+ }
+ span.plus, span.minus
+ {
+ color:#000000;
+ font-size:20px;
+ }
+ span.expand, span.collapse { cursor: pointer; }
+ span.expand span.minus { display: none; }
+ span.collapse span.plus { display: none }
+ </style>
+ <script type="text/javascript">
+ <!--[CDATA[-->
+ window.onload = function()
+ {
+ var ul = document.getElementById('main-ul');
+ var childUls = ul.getElementsByTagName('table');
+ for (var i = 0, l = childUls.length; i < l; i++)
+ {
+ childUls[i].className = 'hide';
+ }
+ }
+
+ function toggle(el)
+ {
+ do
+ {
+ var ul = el.nextSibling;
+ }
+ while (ul.tagName.toLowerCase() !== 'table');
+ ul.className = ul.className === '' ? 'hide' : '';
+ el.className = el.className === 'expand' ? 'collapse' : 'expand';
+ }
+ <!-- ]]-->
+ </script>
+ </head>
+ <body>
+ <H1>Apache Derby</H1>
+ <H1>Graphical Query Explainer</H1>
+ <H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2>
+ <br></br>
+ <br></br>
+ <xsl:apply-templates/>
+ </body>
+ </html>
+ </xsl:template>
+
+ <xsl:template match="plan">
+ <ul id="main-ul">
+ <xsl:apply-templates select="details/node"/>
+ </ul>
+ </xsl:template>
+
+ <xsl:template match="node">
+ <li>
+ <span class="expand" onclick="toggle(this);">
+ <span class="plus">+ </span>
+ <span class="minus">- </span>
+ <xsl:value-of select="@name"/>
+ <br></br>
+ </span>
+ <table frame="border" rules="all">
+ <xsl:if test="count(@input_rows)!=0">
+ <tr>
+ <xsl:apply-templates select="@input_rows"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@returned_rows)!=0">
+ <tr>
+ <xsl:apply-templates select="@returned_rows"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@no_opens)!=0">
+ <tr>
+ <xsl:apply-templates select="@no_opens"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@visited_pages)!=0">
+ <tr>
+ <xsl:apply-templates select="@visited_pages"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scan_qualifiers)!=0">
+ <tr>
+ <xsl:apply-templates select="@scan_qualifiers"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@next_qualifiers)!=0">
+ <tr>
+ <xsl:apply-templates select="@next_qualifiers"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scanned_object)!=0">
+ <tr>
+ <xsl:apply-templates select="@scanned_object"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scan_type)!=0">
+ <tr>
+ <xsl:apply-templates select="@scan_type"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@sort_type)!=0">
+ <tr>
+ <xsl:apply-templates select="@sort_type"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@sorter_output)!=0">
+ <tr>
+ <xsl:apply-templates select="@sorter_output"/>
+ </tr>
+ </xsl:if>
+ </table>
+ <br></br>
+ <ul>
+ <xsl:apply-templates select="node"/>
+ </ul>
+
+ </li>
+ </xsl:template>
+
+ <xsl:template match="node/@*">
+ <th align="left">
+ <xsl:value-of select="name()"/>
+ </th>
+ <td>
+ <xsl:value-of select="."/>
+ </td>
+ </xsl:template>
+
+</xsl:stylesheet>
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl Thu Aug 5 14:25:39 2010
@@ -0,0 +1,183 @@
+<xsl:stylesheet
+ xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
+ version="1.0">
+ <!-- Designed & coded by C.S.Nirmal J. Fernando, of University of Moratuwa, Sri Lanka -->
+ <xsl:output method="html" indent="yes"
+ doctype-public="-//W3C//DTD HTML 4.01//EN"
+ doctype-system="http://www.w3.org/TR/html4/strict.dtd"/>
+
+ <xsl:strip-space elements="*"/>
+
+ <xsl:template match="/">
+ <html>
+ <head>
+ <title>Derby Graphical Query Explainer</title>
+ <style type="text/css">
+ H1,H2{text-align:center;}
+ ul{list-style-type: none;}
+ table.hide { display: none; }
+ table,td,th
+ {
+ border:2px solid black;
+ font-size:18px;
+ position:relative;
+ left:160px;
+ }
+ th
+ {
+ text-align:left;
+ background-color:#999933;
+ color:white;
+ }
+ td,tr{text-align:center;}
+ span.expand, span.collapse
+ {
+ color:#3B5998;
+ font-size:20px;
+ position:relative;
+ left:150px;
+ }
+ span.plus, span.minus
+ {
+ color:#000000;
+ font-size:20px;
+ }
+ span.expand, span.collapse { cursor: pointer; }
+ span.expand span.minus { display: none; }
+ span.collapse span.plus { display: none }
+ </style>
+ <script type="text/javascript">
+ <!--[CDATA[-->
+ window.onload = function()
+ {
+ var ul = document.getElementById('main-ul');
+ var childUls = ul.getElementsByTagName('table');
+ for (var i = 0, l = childUls.length; i < l; i++)
+ {
+ childUls[i].className = 'hide';
+ }
+ }
+
+ function toggle(el)
+ {
+ do
+ {
+ var ul = el.nextSibling;
+ }
+ while (ul.tagName.toLowerCase() !== 'table');
+ ul.className = ul.className === '' ? 'hide' : '';
+ el.className = el.className === 'collapse' ? 'expand' : '';
+ }
+
+ function hide(el)
+ {
+ do
+ {
+ var ul = el.nextSibling;
+ }
+ while (ul.tagName.toLowerCase() !== 'table');
+ ul.className = ul.className === '' ? 'hide' : '';
+ el.className = el.className === 'expand' ? 'collapse' : '';
+ }
+ <!-- ]]-->
+ </script>
+ </head>
+ <body>
+ <H1>Apache Derby</H1>
+ <H1>Graphical Query Explainer</H1>
+ <H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2>
+ <br></br>
+ <br></br>
+ <xsl:apply-templates/>
+ </body>
+ </html>
+ </xsl:template>
+
+ <xsl:template match="plan">
+ <ul id="main-ul">
+ <xsl:apply-templates select="details/node">
+ <xsl:with-param name="i" select="0"/>
+ </xsl:apply-templates>
+ </ul>
+ </xsl:template>
+
+ <xsl:template match="node">
+ <xsl:param name="i"/>
+ <li>
+ <span class="collapse" onmouseover="toggle(this);" onmouseout="hide(this);">
+ <xsl:if test="$i!=0">
+ |_
+ </xsl:if>
+ <xsl:value-of select="@name"/>
+ <br></br>
+ </span>
+ <table frame="border" rules="all">
+ <xsl:if test="count(@input_rows)!=0">
+ <tr>
+ <xsl:apply-templates select="@input_rows"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@returned_rows)!=0">
+ <tr>
+ <xsl:apply-templates select="@returned_rows"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@no_opens)!=0">
+ <tr>
+ <xsl:apply-templates select="@no_opens"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@visited_pages)!=0">
+ <tr>
+ <xsl:apply-templates select="@visited_pages"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scan_qualifiers)!=0">
+ <tr>
+ <xsl:apply-templates select="@scan_qualifiers"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@next_qualifiers)!=0">
+ <tr>
+ <xsl:apply-templates select="@next_qualifiers"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scanned_object)!=0">
+ <tr>
+ <xsl:apply-templates select="@scanned_object"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@scan_type)!=0">
+ <tr>
+ <xsl:apply-templates select="@scan_type"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@sort_type)!=0">
+ <tr>
+ <xsl:apply-templates select="@sort_type"/>
+ </tr>
+ </xsl:if>
+ <xsl:if test="count(@sorter_output)!=0">
+ <tr>
+ <xsl:apply-templates select="@sorter_output"/>
+ </tr>
+ </xsl:if>
+ </table>
+ <br></br>
+ <ul>
+ <xsl:apply-templates select="node"/>
+ </ul>
+
+ </li>
+ </xsl:template>
+
+ <xsl:template match="node/@*">
+ <th align="left">
+ <xsl:value-of select="name()"/>
+ </th>
+ <td>
+ <xsl:value-of select="."/>
+ </td>
+ </xsl:template>
+
+</xsl:stylesheet>
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl Thu Aug 5 14:25:39 2010
@@ -0,0 +1,77 @@
+<xsl:stylesheet
+ xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
+ version="1.0">
+
+ <xsl:output method="html" indent="yes"
+ doctype-public="-//W3C//DTD HTML 4.01//EN"
+ doctype-system="http://www.w3.org/TR/html4/strict.dtd"/>
+
+ <xsl:output method="html" indent="yes"/>
+
+ <xsl:template match="/">
+ <html>
+ <head>
+ <title>Derby Graphical Query Explainer</title>
+ </head>
+ <body>
+ <IMG SRC="derby-logo.png" ALIGN="left"/>
+ <center><H1>Apache Derby</H1></center>
+ <center><H1>Graphical Query Explainer</H1></center>
+ <center><H2>Query: <font color="#4E9258"> <xsl:value-of select="//statement"/> </font></H2></center>
+ <xsl:apply-templates select="plan/details/node"/>
+ </body>
+ </html>
+ </xsl:template>
+
+ <xsl:template match="node">
+ <ul>
+ <br/>
+ <h3><font face="verdana" color="#E56717"><xsl:value-of select="@name"/></font></h3>
+
+ <table frame="border" rules="all">
+ <xsl:if test="count(@input_rows)!=0">
+ <tr>
+ <xsl:apply-templates select="@input_rows"/>
+ </tr>
+ </xsl:if>
+ <tr>
+ <xsl:apply-templates select="@returned_rows"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@no_opens"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@visited_pages"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@scan_qualifiers"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@next_qualifiers"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@scanned_object"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@scan_type"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@sort_type"/>
+ </tr>
+ <tr>
+ <xsl:apply-templates select="@sorter_output"/>
+ </tr>
+ </table>
+ <xsl:apply-templates select="node"/>
+ </ul>
+ </xsl:template>
+
+ <xsl:template match="node/@*">
+ <th align="left">
+ <xsl:value-of select="name()"/>
+ </th>
+ <td>
+ <xsl:value-of select="."/>
+ </td>
+ </xsl:template>
+</xsl:stylesheet>
Added: 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=982637&view=auto
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java (added)
+++ db/derby/code/trunk/java/tools/org/apache/derby/tools/PlanExporter.java Thu Aug 5 14:25:39 2010
@@ -0,0 +1,285 @@
+/**
+ * This class is the main entry point to the tool Graphical Query Explainer.
+ *
+ */
+package org.apache.derby.tools;
+
+import java.io.File;
+import java.security.AccessController;
+import org.apache.derby.impl.tools.planexporter.AccessDatabase;
+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;
+
+ /**
+ * @param args
+ * 1) database string eg: jdbc:derby:myDB ---------
+ * 2) username ------------------------------------
+ * 3) password ------------------------------------
+ * 4) database schema -----------------------------
+ * 5) 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];
+
+ AccessDatabase access = new AccessDatabase(dbURL, args[3], args[4]);
+ access.createConnection();
+ if(access.initializeDataArray()){
+ access.createXMLFragment();
+ access.markTheDepth();
+ String stmt=access.statement();
+ access.shutdown();
+
+ //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(opt1==1 && opt2==3){
+ if(args[9].endsWith(".xsl")||
+ args[9].endsWith(".XSL"))
+ generateXML(access,args[7],stmt,args[9]);
+ else
+ generateXML(access,args[7],stmt,args[9]+".xsl");
+ }
+ else if(opt1==3 && opt2==1){
+ if(args[7].endsWith(".xsl")||
+ args[7].endsWith(".XSL"))
+ generateXML(access,args[9],stmt,args[7]);
+ else
+ generateXML(access,args[9],stmt,args[7]+".xsl");
+ }
+ else
+ printHelp();
+ }
+ //possible occurrences are -xml {path} or -html {path}
+ else if(args.length==7){
+ int opt=selectArg(args[5]);
+ if(opt==0 || opt==3)
+ printHelp();
+ else if(opt==1)
+ generateXML(access,args[6],stmt,null);
+ else{
+ generateXML(access,"temp.xml",stmt,null);
+ generateHTML("temp.xml",args[6],xslStyleSheetName,true);
+ deleteFile("temp.xml");
+ }
+ }
+ //possible occurrences are
+ //-xml {path} and -html {path}
+ //-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]);
+ 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);
+ }
+ else if(opt1==2 && opt2==1){
+ generateXML(access,args[8],stmt,null);
+ generateHTML(args[8],args[6],xslStyleSheetName,true);
+ }
+ else if(opt1==2 && opt2==3){
+ generateXML(access,"temp.xml",stmt,null);
+ generateHTML("temp.xml",args[6],args[8],false);
+ deleteFile("temp.xml");
+ }
+ else if(opt1==3 && opt2==2){
+ generateXML(access,"temp.xml",stmt,null);
+ generateHTML("temp.xml",args[8],args[6],false);
+ deleteFile("temp.xml");
+ }
+ else
+ printHelp();
+ }
+ //possible occurrences are
+ //-xml {path} and -html {path} and -xsl {path}
+ //-html {path} and -xsl {path} and -xml {path}
+ //-xsl {path} and -xml {path} and -html {path}
+ //-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]);
+ 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);
+ }
+ else if(opt1==2 && opt2==3 && opt3==1){
+ generateXML(access,args[10],stmt,null);
+ generateHTML(args[10],args[6],args[8],false);
+ }
+ else if(opt1==3 && opt2==1 && opt3==2){
+ generateXML(access,args[8],stmt,null);
+ generateHTML(args[8],args[10],args[6],false);
+ }
+ else if(opt1==1 && opt2==3 && opt3==2){
+ generateXML(access,args[6],stmt,null);
+ generateHTML(args[6],args[10],args[8],false);
+ }
+ else if(opt1==2 && opt2==1 && opt3==3){
+ generateXML(access,args[8],stmt,null);
+ generateHTML(args[8],args[6],args[10],false);
+ }
+ else if(opt1==3 && opt2==2 && opt3==1){
+ generateXML(access,args[10],stmt,null);
+ generateHTML(args[10],args[8],args[6],false);
+ }
+ else
+ printHelp();
+ }
+ }
+ else{
+ System.out.println(
+ "====================================================\n" +
+ "--- An Error Occured: No Statistics has Captured ---\n" +
+ "-- Possible reasons: --\n" +
+ "-- 1) The statement executed is a DDL statement. --\n" +
+ "-- Statistics will not capture for DDL statements --\n" +
+ "-- by the Derby. --\n" +
+ "-- 2) The statement ID entered is incorrect. --\n" +
+ "====================================================\n"
+ );
+ }
+ }
+ else{
+ printHelp();
+ }
+ }catch(Exception ex){
+ ex.printStackTrace();
+ }
+ }
+
+ /**
+ * Reading the user's option
+ * @param arg user's option
+ * @return the argument type
+ */
+ private static int selectArg(String arg){
+ if(arg.equalsIgnoreCase("-xml"))
+ return xml;
+ else if(arg.equalsIgnoreCase("-html"))
+ return html;
+ else if(arg.equalsIgnoreCase("-xsl"))
+ return xsl;
+ else
+ return 0;
+ }
+
+ /**
+ *
+ * @param access instance of AccessDatabase class
+ * @param arg path of XML
+ * @param stmt statement executed
+ * @param xsl name of the style sheet
+ * @throws Exception
+ */
+ private static void generateXML(AccessDatabase access,
+ String arg, String stmt,String xsl) throws Exception{
+ CreateXMLFile xmlFile = new CreateXMLFile(access);
+
+ if(arg.endsWith(".xml") || arg.endsWith(".XML")){
+ xmlFile.writeTheXMLFile(stmt,
+ access.getData(),
+ arg, xsl);
+ }
+ else{
+ xmlFile.writeTheXMLFile(stmt,
+ access.getData(),
+ arg.concat(".xml"),
+ xsl);
+ }
+ }
+
+ /**
+ *
+ * @param arg path to xml
+ * @param path path of HTML
+ * @param style path to xsl
+ * @param def whether the default xsl or not
+ * @throws Exception
+ */
+ private static void generateHTML(String arg, String path,
+ String style, boolean def) throws Exception{
+ CreateHTMLFile htmlFile = new CreateHTMLFile();
+
+ if(arg.endsWith(".xml") || arg.endsWith(".XML")){
+ htmlFile.getHTML(arg, style, path, def);
+ }
+ else{
+ htmlFile.getHTML(arg.concat(".xml"), style, path, def);
+ }
+ }
+
+ private static void printHelp(){
+ System.out.println
+ (
+ "================================================\n" +
+ "-------------- PlanExporter Tool ---------------\n" +
+ "-- You can pass 7 arguments (minimum), or --\n" +
+ "-- 9 arguments or 10 arguments or --\n" +
+ "----------- 11 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" +
+ "---------------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" +
+ " -html {pathToHTML} -----------------------\n" +
+ "10) -adv -xml {pathToXML} -xsl {pathToXSL} -----\n" +
+ "================================================\n"
+ );
+ }
+
+ public static void deleteFile(final String fileName)
+ {
+ AccessController.doPrivileged
+ (new java.security.PrivilegedAction() {
+
+ public Object run() {
+ File delFile = new File(fileName);
+ if (!delFile.exists())
+ return null;
+ delFile.delete();
+ return null;
+ }
+ }
+ );
+
+ }
+
+}
Modified: db/derby/code/trunk/tools/jar/tools.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/tools/jar/tools.properties?rev=982637&r1=982636&r2=982637&view=diff
==============================================================================
--- db/derby/code/trunk/tools/jar/tools.properties (original)
+++ db/derby/code/trunk/tools/jar/tools.properties Thu Aug 5 14:25:39 2010
@@ -28,3 +28,6 @@ derby.module.Attribute=org.apache.derby.
derby.module.cslook=org.apache.derby.tools.dblook
derby.module.sysinfo=org.apache.derby.tools.sysinfo
derby.module.SignatureChecker=org.apache.derby.tools.SignatureChecker
+derby.module.planexporter=org.apache.derby.tools.PlanExporter
+
+