Derby
  1. Derby
  2. DERBY-4587

Add tools for improved analysis and understanding of query plans and execution statistics

    Details

      Description

      I think it would be great to see some work in the area of tools for helping
      with the analysis of complex query execution. Quite frequently, users of
      Derby have trouble comprehending (a) how their query is being translated
      into a query plan by the optimizer, and (b) what the execution-time resource
      usage of the various parts of the query is.

      There are low-level features in Derby which capture this information and
      record it, such as logQueryPlan, and the XPLAIN tables, but there is a lot
      of opportunity for designing higher-level tools which can process the query
      plan and execution statistics information and present it in a more
      comprehensible fashion.

      1. xml_doc_screenshot.jpg
        111 kB
        Nirmal Fernando
      2. xml_doc_screenshot.jpg
        118 kB
        Nirmal Fernando
      3. vanilla_html.xsl
        2 kB
        Nirmal Fernando
      4. vanilla_html.xsl
        2 kB
        Nirmal Fernando
      5. vanilla_html.xsl
        2 kB
        Nirmal Fernando
      6. vanilla_html.xsl
        2 kB
        Nirmal Fernando
      7. vanilla_html.xsl
        2 kB
        Nirmal Fernando
      8. test4.xsl
        3 kB
        Nirmal Fernando
      9. test.xml
        1 kB
        Nirmal Fernando
      10. Source.rar
        5 kB
        Nirmal Fernando
      11. Simple HTML View (Pure XSL).jpg
        91 kB
        Nirmal Fernando
      12. screenshot-3.jpg
        149 kB
        Nirmal Fernando
      13. screenshot-2.jpg
        149 kB
        Nirmal Fernando
      14. screenshot-1.jpg
        165 kB
        Nirmal Fernando
      15. Read_Me.txt
        1 kB
        Nirmal Fernando
      16. PostgreSQL license.jpg
        142 kB
        Nirmal Fernando
      17. Derby Query Plan Screen Shot 2.jpg
        117 kB
        Nirmal Fernando
      18. derby-logo.png
        4 kB
        Nirmal Fernando
      19. DERBY-4587-tool-test2.diff
        3 kB
        Nirmal Fernando
      20. DERBY-4587-tool-test1.diff
        3 kB
        Nirmal Fernando
      21. DERBY-4587-tool-9.diff
        73 kB
        Nirmal Fernando
      22. DERBY-4587-tool-9.9-formatting.diff
        84 kB
        Kristian Waagan
      23. DERBY-4587-tool-9.8-modified.diff
        33 kB
        Kristian Waagan
      24. DERBY-4587-tool-9.8.diff
        34 kB
        Nirmal Fernando
      25. DERBY-4587-tool-9.7.diff
        81 kB
        Nirmal Fernando
      26. DERBY-4587-tool-9.7.diff
        82 kB
        Nirmal Fernando
      27. DERBY-4587-tool-9.6.diff
        68 kB
        Nirmal Fernando
      28. DERBY-4587-tool-9.5.diff
        67 kB
        Nirmal Fernando
      29. DERBY-4587-tool-9.4.diff
        82 kB
        Nirmal Fernando
      30. DERBY-4587-tool-9.3.diff
        78 kB
        Nirmal Fernando
      31. DERBY-4587-tool-9.2.diff
        72 kB
        Nirmal Fernando
      32. DERBY-4587-tool-9.12.diff
        4 kB
        Nirmal Fernando
      33. DERBY-4587-tool-9.12.diff
        4 kB
        Nirmal Fernando
      34. DERBY-4587-tool-9.12.diff
        16 kB
        Nirmal Fernando
      35. DERBY-4587-tool-9.11-license_header_inXSL.diff
        3 kB
        Nirmal Fernando
      36. DERBY-4587-tool-9.10-eol_style_native.diff
        2 kB
        Kristian Waagan
      37. DERBY-4587-tool-9.1.diff
        72 kB
        Nirmal Fernando
      38. DERBY-4587-tool-8.diff
        25 kB
        Nirmal Fernando
      39. DERBY-4587-tool-7-b.diff
        19 kB
        Nirmal Fernando
      40. DERBY-4587-tool-7.diff
        18 kB
        Nirmal Fernando
      41. DERBY-4587-tool-6.diff
        17 kB
        Nirmal Fernando
      42. DERBY-4587-tool-5.diff
        17 kB
        Nirmal Fernando
      43. DERBY-4587-tool-4.diff
        16 kB
        Nirmal Fernando
      44. DERBY-4587-tool-3.diff
        9 kB
        Nirmal Fernando
      45. DERBY-4587-tool-2.diff
        9 kB
        Nirmal Fernando
      46. DERBY-4587-tool.diff
        9 kB
        Nirmal Fernando
      47. DERBY-4587-javadoc_fix.diff
        0.3 kB
        Nirmal Fernando
      48. Derby_Query_Plan_Screen_Shot.jpg
        20 kB
        Nirmal Fernando
      49. basic_html-4.2.jpg
        74 kB
        Nirmal Fernando
      50. basic_html-4.1.jpg
        81 kB
        Nirmal Fernando
      51. basic_html-3.jpg
        93 kB
        Nirmal Fernando
      52. basic_html-2.jpg
        75 kB
        Nirmal Fernando
      53. basic_html-2.3.jpg
        73 kB
        Nirmal Fernando
      54. basic_html-2.2.jpg
        90 kB
        Nirmal Fernando
      55. advancedXSL-3.jpg
        138 kB
        Nirmal Fernando
      56. advancedXSL-2.jpg
        118 kB
        Nirmal Fernando
      57. advancedXSL-1.jpg
        65 kB
        Nirmal Fernando
      58. advancedViewXSL2.xsl
        4 kB
        Nirmal Fernando
      59. advancedViewXSL.xsl
        4 kB
        Nirmal Fernando
      60. advancedViewXSL.xsl
        4 kB
        Nirmal Fernando
      61. AdavancedXSL-mouseover.jpg
        86 kB
        Nirmal Fernando

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Knut Anders Hatlen added a comment -

          I've converted the remaining open sub-task to standalone improvement requests and linked them to this issue. Marking this issue as fixed in 10.7.

          Thanks for all the work you've put into this, Nirmal!

          Show
          Knut Anders Hatlen added a comment - I've converted the remaining open sub-task to standalone improvement requests and linked them to this issue. Marking this issue as fixed in 10.7. Thanks for all the work you've put into this, Nirmal!
          Hide
          Nirmal Fernando added a comment -

          Thanks Bryan for adding those tests! They ran fine for me!

          Show
          Nirmal Fernando added a comment - Thanks Bryan for adding those tests! They ran fine for me!
          Hide
          Bryan Pendleton added a comment -

          I added a few simple test cases to the XplainStatisticsTest to cover
          some of the situations I thought might be interesting, and committed
          the patch to the trunk as revision 985762.

          Show
          Bryan Pendleton added a comment - I added a few simple test cases to the XplainStatisticsTest to cover some of the situations I thought might be interesting, and committed the patch to the trunk as revision 985762.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for reviewing the patch!

          Whoops, I interpreted a behavior incorrectly! I've modified the 9.12 patch,
          so that it will print an error message if the schema is incorrect.

          Regarding test cases I'll give a try soon!

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for reviewing the patch! Whoops, I interpreted a behavior incorrectly! I've modified the 9.12 patch, so that it will print an error message if the schema is incorrect. Regarding test cases I'll give a try soon! Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal, thanks for the latest patch.

          I don't understand the verifySchemaExistance() method in the latest
          9.12 patch. What is the intended method behavior if the schema
          name is not found in the getSchemas() list?

          Also, I think it would be good if the patch could include a new
          test case in XplainStatisticsTest, which tested the behavior when
          an incorrect schema name is provided to AccessDatabase.

          Similarly, could the patch include a new test case which tested
          the behavior when a security exception is thrown by writeTheXMLFile
          because it is trying to write to an invalid location? For example, we
          could try to write to the file "/DerbyXMLTest.xml", which should fail
          on all our platforms because Derby is generally not authorized to
          write to the root of the file system.

          Show
          Bryan Pendleton added a comment - Hi Nirmal, thanks for the latest patch. I don't understand the verifySchemaExistance() method in the latest 9.12 patch. What is the intended method behavior if the schema name is not found in the getSchemas() list? Also, I think it would be good if the patch could include a new test case in XplainStatisticsTest, which tested the behavior when an incorrect schema name is provided to AccessDatabase. Similarly, could the patch include a new test case which tested the behavior when a security exception is thrown by writeTheXMLFile because it is trying to write to an invalid location? For example, we could try to write to the file "/DerbyXMLTest.xml", which should fail on all our platforms because Derby is generally not authorized to write to the root of the file system.
          Hide
          Nirmal Fernando added a comment -

          Hi Kristian,

          No, a comment can be added as the first entry, I tested modified style sheets in my environment.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Kristian, No, a comment can be added as the first entry, I tested modified style sheets in my environment. Thanks.
          Hide
          Kristian Waagan added a comment -

          Thanks for catching and addressing the problem with the headers in the XSL files, Nirmal.
          I completely forgot about that...

          It's been a while since I worked with XSL/XML, I don't remember if there are rules about which element must be first in such a file - that is, is it okay to keep the license header where it is, or should it be moved to after some mandatory first tag?

          Show
          Kristian Waagan added a comment - Thanks for catching and addressing the problem with the headers in the XSL files, Nirmal. I completely forgot about that... It's been a while since I worked with XSL/XML, I don't remember if there are rules about which element must be first in such a file - that is, is it okay to keep the license header where it is, or should it be moved to after some mandatory first tag?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for committing. I have another patch which is addressed Kristian's comments on c) & n).
          So, now onwards I think we should work on separate jiras as you mentioned, I will create sub tasks for 1,3, & 4. I think with the help of Kristian the latest patch solved the 2.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for committing. I have another patch which is addressed Kristian's comments on c) & n). So, now onwards I think we should work on separate jiras as you mentioned, I will create sub tasks for 1,3, & 4. I think with the help of Kristian the latest patch solved the 2. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Committed patch 9.11 to the trunk as revision 985075.

          Show
          Bryan Pendleton added a comment - Committed patch 9.11 to the trunk as revision 985075.
          Hide
          Bryan Pendleton added a comment -

          Your latest patch looks good to me; I will commit it.

          If you haven't already done so, I think you could file separate sub-task Jira
          entries for the following ideas that were brought up during the reviews:
          1) Investigate character set and encoding issues.
          2) Investigate security and PrivilegedActionException handling
          3) Redesign the tool API for simplicity
          4) Allow exporting of multiple query plans in a single operation
          These issues seem sufficiently complicated that they would be easier
          to track in separate Jira entries.

          Show
          Bryan Pendleton added a comment - Your latest patch looks good to me; I will commit it. If you haven't already done so, I think you could file separate sub-task Jira entries for the following ideas that were brought up during the reviews: 1) Investigate character set and encoding issues. 2) Investigate security and PrivilegedActionException handling 3) Redesign the tool API for simplicity 4) Allow exporting of multiple query plans in a single operation These issues seem sufficiently complicated that they would be easier to track in separate Jira entries.
          Hide
          Nirmal Fernando added a comment -

          Kristian thanks for doing relevant changes and committing the patches.

          After updating my environment with the latest patches, I found there's an error when transforming to HTML. This has caused after adding the license header in XSL style sheets. In XSL a comment should be added inside <!-- & -->. This patch correct that error + a small variable change in CreateHTML.java.

          I highly appreciate if someone can commit this quickly!

          Thanks.

          Show
          Nirmal Fernando added a comment - Kristian thanks for doing relevant changes and committing the patches. After updating my environment with the latest patches, I found there's an error when transforming to HTML. This has caused after adding the license header in XSL style sheets. In XSL a comment should be added inside <!-- & -->. This patch correct that error + a small variable change in CreateHTML.java. I highly appreciate if someone can commit this quickly! Thanks.
          Hide
          Kristian Waagan added a comment -

          Nirmal, some answers to your questions about my comments:

          — AccessDatabase
          c) Well, the easiest is probably to use PreparedStatement and ps.setString(x, s).
          By specifying a system table and a comment as the schema, say "sys.systables --", or saying "<stmt_id> and 1 = 1" in a where clause, one can make the tool query tables it isn't supposed to access. Nothing severe in this case, but it's good to be aware of these issues in general. Luckily you cannot specify multiple SQL statements in (most?) JDBC calls, because then you could easily add delete or update statements as well.
          You could also use Connection.getMetaData(), and then use one of the DatabaseMetaData.getSchemas() methods to verify that the schema actually exists.

          — CreateXMLFile
          n) Since you know only an IOException can be thrown within the PrivilegedExceptionAction, you can add a try - catch block where you unwrap the IOException, something like this:
          try

          { // PrivilegedActionException wrapping IOException thrown here somewhere }

          catch (PrivilegedActionException pae)

          { throw (IOException)pae.getCause(); // or, according to the old API, pae.getException(). }

          You can then remove PrivilegedActionException from the throws clause of the method.

          p) I'm not quite sure. I don't have the cycles to follow up on this one right now, so it would be nice if someone else can give you more feedback on this. If you don't hear anything, it's probably not a big issue.

          I also noticed that CreateHTMLFile only contains a method that could have been static. If you want to keep the class, you should make the no-arg constructor private and the getHTML-method static. Alternatively, you could move the method into CreateXMLFile or somewhere else. Your call

          Regards,

          Show
          Kristian Waagan added a comment - Nirmal, some answers to your questions about my comments: — AccessDatabase c) Well, the easiest is probably to use PreparedStatement and ps.setString(x, s). By specifying a system table and a comment as the schema, say "sys.systables --", or saying "<stmt_id> and 1 = 1" in a where clause, one can make the tool query tables it isn't supposed to access. Nothing severe in this case, but it's good to be aware of these issues in general. Luckily you cannot specify multiple SQL statements in (most?) JDBC calls, because then you could easily add delete or update statements as well. You could also use Connection.getMetaData(), and then use one of the DatabaseMetaData.getSchemas() methods to verify that the schema actually exists. — CreateXMLFile n) Since you know only an IOException can be thrown within the PrivilegedExceptionAction, you can add a try - catch block where you unwrap the IOException, something like this: try { // PrivilegedActionException wrapping IOException thrown here somewhere } catch (PrivilegedActionException pae) { throw (IOException)pae.getCause(); // or, according to the old API, pae.getException(). } You can then remove PrivilegedActionException from the throws clause of the method. p) I'm not quite sure. I don't have the cycles to follow up on this one right now, so it would be nice if someone else can give you more feedback on this. If you don't hear anything, it's probably not a big issue. I also noticed that CreateHTMLFile only contains a method that could have been static. If you want to keep the class, you should make the no-arg constructor private and the getHTML-method static. Alternatively, you could move the method into CreateXMLFile or somewhere else. Your call Regards,
          Hide
          Kristian Waagan added a comment -

          Also discovered that the files were committed without the svn:eol-style property set, which should be set to native.
          Only committers have to worry about this (if you want to know more, search for svn:eol-style on http://svnbook.red-bean.com/en/1.1/ch07s02.html).

          Committed to trunk with revision 984912

          Show
          Kristian Waagan added a comment - Also discovered that the files were committed without the svn:eol-style property set, which should be set to native. Only committers have to worry about this (if you want to know more, search for svn:eol-style on http://svnbook.red-bean.com/en/1.1/ch07s02.html ). Committed to trunk with revision 984912
          Hide
          Kristian Waagan added a comment -

          Attached patch DERBY-4587-tool-9.9-formatting.diff.
          Committed to trunk with revision 984902.

          When preparing to add ASF license headers, I realized it would be appropriate to fix some formatting issues first. I'm sorry for doing this so quickly, Nirmal, it may cause some merge trouble if you have code changes in your sandbox.

          Here's what I did:
          o moved class JavaDoc to after the package and import statements (directly before the class statement). The first content of the files will be the ASF license header (for more details, see http://www.apache.org/legal/src-headers.html).
          o replaced all tab characters with spaces. Spaces are preferred for indentation in the Derby code base. Since the files are very fresh, and some of them mixed tabs and spaces (both within the files and on individual lines), I decided to proceed with the conversion sooner than later. You may have to adjust your editor settings to avoid tabs in future patches (just ask on the dev list if unsure).
          o removed some trailing whitespace

          Again, sorry for doing this so abruptly (and without involving you more).

          Next up is the addition of the Apache license headers.

          Show
          Kristian Waagan added a comment - Attached patch DERBY-4587 -tool-9.9-formatting.diff. Committed to trunk with revision 984902. When preparing to add ASF license headers, I realized it would be appropriate to fix some formatting issues first. I'm sorry for doing this so quickly, Nirmal, it may cause some merge trouble if you have code changes in your sandbox. Here's what I did: o moved class JavaDoc to after the package and import statements (directly before the class statement). The first content of the files will be the ASF license header (for more details, see http://www.apache.org/legal/src-headers.html ). o replaced all tab characters with spaces. Spaces are preferred for indentation in the Derby code base. Since the files are very fresh, and some of them mixed tabs and spaces (both within the files and on individual lines), I decided to proceed with the conversion sooner than later. You may have to adjust your editor settings to avoid tabs in future patches (just ask on the dev list if unsure). o removed some trailing whitespace Again, sorry for doing this so abruptly (and without involving you more). Next up is the addition of the Apache license headers.
          Hide
          Kristian Waagan added a comment -

          I decided to commit the latest patch (9.8), because I need to make some changes that would have conflicted with that patch.
          Committed to trunk with revision 984881.

          Note that I had to modify the patch (see DERBY-4587-tool-9.8-modified.diff), as it was not accepted by (g)patch. I removed one JavaDoc change from CreateHTMLFile, but manually made the other modifications. Parts of the JavaDoc comment seemed to be missing.
          Nirmal, please check the file to make sure I didn't break anything, and include the JavaDoc change in the next patch if appropriate.

          I'll post some other comments regarding the code a bit later, first I want to work on the potentially conflicting changes (shouldn't take long).

          Show
          Kristian Waagan added a comment - I decided to commit the latest patch (9.8), because I need to make some changes that would have conflicted with that patch. Committed to trunk with revision 984881. Note that I had to modify the patch (see DERBY-4587 -tool-9.8-modified.diff), as it was not accepted by (g)patch. I removed one JavaDoc change from CreateHTMLFile, but manually made the other modifications. Parts of the JavaDoc comment seemed to be missing. Nirmal, please check the file to make sure I didn't break anything, and include the JavaDoc change in the next patch if appropriate. I'll post some other comments regarding the code a bit later, first I want to work on the potentially conflicting changes (shouldn't take long).
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          I'm attaching a new patch which is included some of the modifications that are suggested by Kristian and Rick.
          PS: This is not intended to provide the support of using the tool without giving a STMT_ID. I think I should start to work
          on it in a separate JIRA after the community agrees to a suitable command line API to the PlanExporter tool.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, I'm attaching a new patch which is included some of the modifications that are suggested by Kristian and Rick. PS: This is not intended to provide the support of using the tool without giving a STMT_ID. I think I should start to work on it in a separate JIRA after the community agrees to a suitable command line API to the PlanExporter tool. Thanks.
          Hide
          Rick Hillegas added a comment -

          Hi Nirmal,

          I think your plan makes sense: agree on an api and then make the changes in a separate jira. Thanks.

          Show
          Rick Hillegas added a comment - Hi Nirmal, I think your plan makes sense: agree on an api and then make the changes in a separate jira. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Rick,

          Thanks for the replies, I'll add the STMT_ID.

          Yeah, you got it right! I think we should come to a conclusion about the API of the tool and it may be handle in a different jira linked to this, what do you think?

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Rick, Thanks for the replies, I'll add the STMT_ID. Yeah, you got it right! I think we should come to a conclusion about the API of the tool and it may be handle in a different jira linked to this, what do you think? Thanks.
          Hide
          Rick Hillegas added a comment -

          Hi Nirmal. Thanks for the explanation of the Java script usage. By the way, in my environment I notice that when I use the adv switch, the output contains a pointer to the original location of the stylesheet-so I don't have to copy the style sheet into my current directory. I think that's great.

          If I am understanding the tool correctly, it seems to me that it operates in two modes (depending on the switches you specify):

          o TransformMode – In this mode, the tool transforms the output, using the specified stylesheet. The output does not contain a pointer to a stylesheet.

          o PassthroughMode – In this mode, the tool does NOT transform the output. Instead, the tool outputs the raw xml, prefixing it with a pointer to the desired stylesheet. In this mode, the transformation happens inside the browser.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Nirmal. Thanks for the explanation of the Java script usage. By the way, in my environment I notice that when I use the adv switch, the output contains a pointer to the original location of the stylesheet -so I don't have to copy the style sheet into my current directory. I think that's great. If I am understanding the tool correctly, it seems to me that it operates in two modes (depending on the switches you specify): o TransformMode – In this mode, the tool transforms the output, using the specified stylesheet. The output does not contain a pointer to a stylesheet. o PassthroughMode – In this mode, the tool does NOT transform the output. Instead, the tool outputs the raw xml, prefixing it with a pointer to the desired stylesheet. In this mode, the transformation happens inside the browser. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Nirmal. The reason that I want to see a statement id is to cover the case when the output contains plans for multiple queries. In this scenario, you might want to run the same query more than once, changing the environment inbetween. I think it would be useful to have some way of matching the plans to the XPLAIN tables in case you want to drill-down. I think that the statement id could appear at the top along with the statement text. Thanks.

          Show
          Rick Hillegas added a comment - Hi Nirmal. The reason that I want to see a statement id is to cover the case when the output contains plans for multiple queries. In this scenario, you might want to run the same query more than once, changing the environment inbetween. I think it would be useful to have some way of matching the plans to the XPLAIN tables in case you want to drill-down. I think that the statement id could appear at the top along with the statement text. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Rick, do you really like to see STMT_ID in the HTML? Since the query is displayed I feel like it's not useful.
          If that addition is useful where you like it to be displayed? Is it good to be in the header section?

          Thanks.

          Show
          Nirmal Fernando added a comment - Rick, do you really like to see STMT_ID in the HTML? Since the query is displayed I feel like it's not useful. If that addition is useful where you like it to be displayed? Is it good to be in the header section? Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Rick,

          I have few comments regarding your latest comment.

          • If the using style sheet is advanced (i.e. contains JS), currently tool follows a different way of transforming as I've described in previous comment. In this mode it can only provides a XML which is transformable through a web browser, given that the provided style sheet is present in the relative location to XML.
          • Current behavior of the tool is if a user provided an incorrect style sheet path, the tool will automatically use the "vanilla_html.xsl" style sheet which is attached with derbytools.jar (this is not valid for advance feature).
          • The "outputFileName" you refers to should be without an extension (i.e. xml or html), I think you meant the same thing.

          These are the only comments that I have for now, regarding the API you have suggested.
          I think it's more simpler, thanks for suggesting.

          Show
          Nirmal Fernando added a comment - Hi Rick, I have few comments regarding your latest comment. If the using style sheet is advanced (i.e. contains JS), currently tool follows a different way of transforming as I've described in previous comment. In this mode it can only provides a XML which is transformable through a web browser, given that the provided style sheet is present in the relative location to XML. Current behavior of the tool is if a user provided an incorrect style sheet path, the tool will automatically use the "vanilla_html.xsl" style sheet which is attached with derbytools.jar (this is not valid for advance feature). The "outputFileName" you refers to should be without an extension (i.e. xml or html), I think you meant the same thing. These are the only comments that I have for now, regarding the API you have suggested. I think it's more simpler, thanks for suggesting.
          Hide
          Nirmal Fernando added a comment -

          Hi Rick,

          This is for your comment on 11th at 2.15pm.

          I can add all the information in XPLAIN tables into the XML, but since it will involve lot of database queries including JOINS, I am not sure will it affect the performance of the tool.

          No I haven't developed any style-sheet for non-html output.

          >Are the plus signs produced by the advanced style sheet supposed to allow you to expand and collapse elements? They don't work that way for me in Firefox, Chrome, or >Safari.

          Yeah, those + signs intended to allow collapsing and expanding behavior, but you have to use advance option (-adv) of the tool, if you want to use that style sheet (i.e. advancedViewXSL.xsl or advancedViewXSL2.xsl) and in this case you will not get a html output instead you can only get the XML output. Opening that XML in your browser will provide you the JS features (i.e. browser will do all the transformation). We had to do like this when we're using JS in our style sheet, since Xalan is not supporting transformation of style sheets with JS to HTML.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Rick, This is for your comment on 11th at 2.15pm. I can add all the information in XPLAIN tables into the XML, but since it will involve lot of database queries including JOINS, I am not sure will it affect the performance of the tool. No I haven't developed any style-sheet for non-html output. >Are the plus signs produced by the advanced style sheet supposed to allow you to expand and collapse elements? They don't work that way for me in Firefox, Chrome, or >Safari. Yeah, those + signs intended to allow collapsing and expanding behavior, but you have to use advance option (-adv) of the tool, if you want to use that style sheet (i.e. advancedViewXSL.xsl or advancedViewXSL2.xsl) and in this case you will not get a html output instead you can only get the XML output. Opening that XML in your browser will provide you the JS features (i.e. browser will do all the transformation). We had to do like this when we're using JS in our style sheet, since Xalan is not supporting transformation of style sheets with JS to HTML. Thanks.
          Hide
          Rick Hillegas added a comment -

          I would like to propose the following command line api for PlanExporter. Partly I am doing this because this api seems simpler to me. My other motivation is to verify that I am not missing any of the functionality provided by the tool. Thanks.

          java org.apache.derby.tools.PlanExporter dbURL schemaName outputFileName [ -xsl styleSheet ] [ -stmt statementID ]

          dbURL :== An url appropriate for connecting via DriverManager.getConnection()

          schemaName :== A case-sensitive schema name

          outputFileName :== Absolute or relative name of file to which output will be written

          styleSheet :== Absolute or relative path name to an xsl style sheet for transforming the output.

          statementID :== A 36 character statement id string from schemaName.SYSXPLAIN_STATEMENTS.STMT_ID

          If a stylesheet is specified, then PlanExporter will use that stylesheet to transform the output. Otherwise, no stylesheet will be applied. This means that, by default, raw xml output is produced. In order to get html output, you must specify a Derby-supplied html stylesheet or one of your own. Of course, you can also specify a stylesheet which does not produce html output.

          If a statement id is specified, then the output file will contain only that statement's plan. Otherwise, the output file will contain plans for all of the statements in schemaName.SYSXPLAIN_STATEMENTS.

          Show
          Rick Hillegas added a comment - I would like to propose the following command line api for PlanExporter. Partly I am doing this because this api seems simpler to me. My other motivation is to verify that I am not missing any of the functionality provided by the tool. Thanks. java org.apache.derby.tools.PlanExporter dbURL schemaName outputFileName [ -xsl styleSheet ] [ -stmt statementID ] dbURL :== An url appropriate for connecting via DriverManager.getConnection() schemaName :== A case-sensitive schema name outputFileName :== Absolute or relative name of file to which output will be written styleSheet :== Absolute or relative path name to an xsl style sheet for transforming the output. statementID :== A 36 character statement id string from schemaName.SYSXPLAIN_STATEMENTS.STMT_ID If a stylesheet is specified, then PlanExporter will use that stylesheet to transform the output. Otherwise, no stylesheet will be applied. This means that, by default, raw xml output is produced. In order to get html output, you must specify a Derby-supplied html stylesheet or one of your own. Of course, you can also specify a stylesheet which does not produce html output. If a statement id is specified, then the output file will contain only that statement's plan. Otherwise, the output file will contain plans for all of the statements in schemaName.SYSXPLAIN_STATEMENTS.
          Hide
          Rick Hillegas added a comment -

          Thanks again for this great tool, Nirmal. I have some more comments.

          I started experimenting with customized style sheets. I had to poke around the code to find the style sheets. I have logged DERBY-4768 to encourage us to publish the template style sheets. Once I found the style sheets, it appeared to me that PlanExporter only presents a subset of the information in the XPLAIN tables. I would like to see more information:

          a) I think the xml output should include all of the relevant fields/nodes represented in the XPLAIN tables. The xsl designer and other display tools can suppress and format the xml output as desired.

          b) The html output should be readable but it does not have to be complete. I would also appreciate seeing the following fields in the html output:

          i) SYSXPLAIN_STATEMENTS.STMT_ID
          ii) SYSXPLAIN_STATEMENTS.XPLAIN_TIME

          A couple questions:

          1) Are there any style sheets for non-html output?

          2) Are the plus signs produced by the advanced style sheet supposed to allow you to expand and collapse elements? They don't work that way for me in Firefox, Chrome, or Safari.

          Show
          Rick Hillegas added a comment - Thanks again for this great tool, Nirmal. I have some more comments. I started experimenting with customized style sheets. I had to poke around the code to find the style sheets. I have logged DERBY-4768 to encourage us to publish the template style sheets. Once I found the style sheets, it appeared to me that PlanExporter only presents a subset of the information in the XPLAIN tables. I would like to see more information: a) I think the xml output should include all of the relevant fields/nodes represented in the XPLAIN tables. The xsl designer and other display tools can suppress and format the xml output as desired. b) The html output should be readable but it does not have to be complete. I would also appreciate seeing the following fields in the html output: i) SYSXPLAIN_STATEMENTS.STMT_ID ii) SYSXPLAIN_STATEMENTS.XPLAIN_TIME A couple questions: 1) Are there any style sheets for non-html output? 2) Are the plus signs produced by the advanced style sheet supposed to allow you to expand and collapse elements? They don't work that way for me in Firefox, Chrome, or Safari.
          Hide
          Rick Hillegas added a comment -

          Linking to new issue DERBY-4768. We should publish template style sheets. The user documentation for PlanExporter should explain where the templates live (demo/templates).

          Show
          Rick Hillegas added a comment - Linking to new issue DERBY-4768 . We should publish template style sheets. The user documentation for PlanExporter should explain where the templates live (demo/templates).
          Hide
          Rick Hillegas added a comment -

          It would be great if the tool were flexible enough to handle a couple usage patterns. For instance:

          a) You get one plan in your output file if you specify a statement id.

          b) You get all plans in your output file if you ask for them.

          Show
          Rick Hillegas added a comment - It would be great if the tool were flexible enough to handle a couple usage patterns. For instance: a) You get one plan in your output file if you specify a statement id. b) You get all plans in your output file if you ask for them.
          Hide
          Nirmal Fernando added a comment -

          Thanks Rick for the verification I might need to submit a documentation patch removing the
          requirement of xalan.

          Show
          Nirmal Fernando added a comment - Thanks Rick for the verification I might need to submit a documentation patch removing the requirement of xalan.
          Hide
          Kristian Waagan added a comment -

          I'll answer Nirmal's replies to my comments in a separate post (after having looked into them), just want to comment on what Rick said.

          Rick, you're hoping all plans go into one file. I see this as both an advantage and a disadvantage. With the current state of the tool, I think it would be confusing to have like 5 - 40 queries in one file.

          So, here are some options I see (feel free to add your own!):
          a) Everything in one file, use horizontal separators and maybe add some navigational aids (i.e. table of contents, anchor at the top)
          b) Keep each query in its own file.
          c) As above, but additionally create a separate file for navigation with (relative) links to the individual plans.
          d) Add a(nother) tool to split up the single large output file.

          Now, personally I would like the only requirement for the tool to be a web browser (graphical or console based) - continuing along the lines above may move us in the direction of a standalone tool with it's own GUI...

          Another issue with selecting all available plans is security (leaking information). How is access control currently handled by the XPLAIN functionality?
          For instance, will internal procedure queries [1] be traced as well? Can access to those plans be limited? Does it make sense to do so?

          [1] I.e., if the user does 'call myBusinessLogicProcedure', will queries issued from inside myBusinessLogicProcedure be traced and accessible to the user issuing the call? This isn't directly related to the PlanExporter as such, more the XPLAIN functionality I guess.

          Show
          Kristian Waagan added a comment - I'll answer Nirmal's replies to my comments in a separate post (after having looked into them), just want to comment on what Rick said. Rick, you're hoping all plans go into one file. I see this as both an advantage and a disadvantage. With the current state of the tool, I think it would be confusing to have like 5 - 40 queries in one file. So, here are some options I see (feel free to add your own!): a) Everything in one file, use horizontal separators and maybe add some navigational aids (i.e. table of contents, anchor at the top) b) Keep each query in its own file. c) As above, but additionally create a separate file for navigation with (relative) links to the individual plans. d) Add a(nother) tool to split up the single large output file. Now, personally I would like the only requirement for the tool to be a web browser (graphical or console based) - continuing along the lines above may move us in the direction of a standalone tool with it's own GUI... Another issue with selecting all available plans is security (leaking information). How is access control currently handled by the XPLAIN functionality? For instance, will internal procedure queries [1] be traced as well? Can access to those plans be limited? Does it make sense to do so? [1] I.e., if the user does 'call myBusinessLogicProcedure', will queries issued from inside myBusinessLogicProcedure be traced and accessible to the user issuing the call? This isn't directly related to the PlanExporter as such, more the XPLAIN functionality I guess.
          Hide
          Rick Hillegas added a comment -

          Hi Nirmal,

          I have run PlanExporter successfully with just derby.jar and derbytools.jar in the classpath. The query being analyzed was a simple one: select * from countries where country like 'B%'

          Show
          Rick Hillegas added a comment - Hi Nirmal, I have run PlanExporter successfully with just derby.jar and derbytools.jar in the classpath. The query being analyzed was a simple one: select * from countries where country like 'B%'
          Hide
          Rick Hillegas added a comment -

          Thanks for the quick response, Nirmal. Concerning (2): The XPLAIN machinery fits a common pattern of log monitoring: you start recording, you run some experiments, you end recording, you analyze the results. In this pattern, it is common to want to analyze all of the experiments which were recorded. I'm not following your question about naming the generated files. I was hoping that all of the recorded plans would go into a single output file. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for the quick response, Nirmal. Concerning (2): The XPLAIN machinery fits a common pattern of log monitoring: you start recording, you run some experiments, you end recording, you analyze the results. In this pattern, it is common to want to analyze all of the experiments which were recorded. I'm not following your question about naming the generated files. I was hoping that all of the recorded plans would go into a single output file. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Rick,

          Thanks for your feedback.

          1) I'll modify the arguments, in my next patch.

          2) Don't you think that a user will dislike when plans for all the tracked statements get generated each and every time he executed the tool? It may take considerable amount of time given that there are lots of tracked queries. Also we may get into trouble when naming the generated files. What do you think?

          3) Pardon me about the comment, as I said my above reply to Kristian I'll modify it to "Apache Derby Query Explainer (DERBY-4587)" in my next patch.

          PS: I suspect that the tool does not need Xalan.jar in the CLASSPATH, both to pass tests and generate query plans. Instead it uses the inbuilt Xalan classes in java. Can you please verify this, if you got some time?

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Rick, Thanks for your feedback. 1) I'll modify the arguments, in my next patch. 2) Don't you think that a user will dislike when plans for all the tracked statements get generated each and every time he executed the tool? It may take considerable amount of time given that there are lots of tracked queries. Also we may get into trouble when naming the generated files. What do you think? 3) Pardon me about the comment, as I said my above reply to Kristian I'll modify it to "Apache Derby Query Explainer ( DERBY-4587 )" in my next patch. PS: I suspect that the tool does not need Xalan.jar in the CLASSPATH, both to pass tests and generate query plans. Instead it uses the inbuilt Xalan classes in java. Can you please verify this, if you got some time? Thanks.
          Hide
          Rick Hillegas added a comment -

          Thanks for this excellent contribution to Derby, Nirmal. Here are a couple initial comments:

          o The PlanExporter tool takes a connection URL argument as well as special username and password arguments. In contrast, our other tools only take a connection URL argument (see the api for dblook and SignatureChecker). For those tools, the username and password are part of the connection URL. Separating out the username and password arguments raises the awkward possibility that they may conflict with the corresponding attributes in the connection URL. In addition, for embedded databases which don't need authentication, it seems awkward to have to put dummy username/password arguments on the command line. In order to avoid these awkwardnesses and to promote a common api across our tools, I would rather see PlanExporter behave like dblook and SignatureChecker.

          o It would be nice to be able to run the tool without having to specify a statement id. In this mode, I could imagine that plans would be produced for all tracked statements. Our other tools do not require the user to query the system tables before use and I think it would be good to reproduce that friendly experience.

          o The xml output contains a header comment, giving credit to the author. In general, Derby follows the Apache way by not singling out individuals for special credit either in the code or in production artifacts. People who want to understand the contribution history of a component should be able to recover that information from JIRA. The header comment should be removed.

          Show
          Rick Hillegas added a comment - Thanks for this excellent contribution to Derby, Nirmal. Here are a couple initial comments: o The PlanExporter tool takes a connection URL argument as well as special username and password arguments. In contrast, our other tools only take a connection URL argument (see the api for dblook and SignatureChecker). For those tools, the username and password are part of the connection URL. Separating out the username and password arguments raises the awkward possibility that they may conflict with the corresponding attributes in the connection URL. In addition, for embedded databases which don't need authentication, it seems awkward to have to put dummy username/password arguments on the command line. In order to avoid these awkwardnesses and to promote a common api across our tools, I would rather see PlanExporter behave like dblook and SignatureChecker. o It would be nice to be able to run the tool without having to specify a statement id. In this mode, I could imagine that plans would be produced for all tracked statements. Our other tools do not require the user to query the system tables before use and I think it would be good to reproduce that friendly experience. o The xml output contains a header comment, giving credit to the author. In general, Derby follows the Apache way by not singling out individuals for special credit either in the code or in production artifacts. People who want to understand the contribution history of a component should be able to recover that information from JIRA. The header comment should be removed.
          Hide
          Nirmal Fernando added a comment -

          Hi Kathey & Rick,

          Thanks for looking into the tool, hope to get your
          feedback.

          Show
          Nirmal Fernando added a comment - Hi Kathey & Rick, Thanks for looking into the tool, hope to get your feedback.
          Hide
          Nirmal Fernando added a comment -

          Hi Kristian,

          Thanks for looking into the code and give me your feedback, I find
          them very useful, I'll attach a patch soon.
          Please see my comments below.

          a) Pardon me for not adding the license header. Thanks for
          looking into that.

          — AccessDatabase
          b) If it is the case I'll change to dbUrl.indexOf("://").
          c) It's better if you can help me a bit on this, I'm not that familiar
          of using meta data calls.
          d) Whoops, I forgot to removed those automatic generated @author
          tags.
          e) I'll modify this.
          f) Yep, it's better not to shutdown, instead we can only close the connection.

          — PlanExporter
          g) I'll make it to private. Tool runs with the permissions given to derbytools.jar.

          — TreeNode
          h) I'll make it to package-private.
          i) I'll make it implicit. No need to explicitly inherit.
          j) Removed it.
          k) I might had a reason earlier, but you're right no need of a String now.

          — CreateHTMLFile
          l) I'll add this.
          m) thanks for the idea.

          — CreateXMLFile
          n) Actually I can't think of a way of doing that. Can you please explain a bit.
          o) I think I should change it to "Apache Derby Query Explainer (DERBY-4587)".
          p) I'm afraid not, will using the default be a problem?

          Thank you very much!

          Show
          Nirmal Fernando added a comment - Hi Kristian, Thanks for looking into the code and give me your feedback, I find them very useful, I'll attach a patch soon. Please see my comments below. a) Pardon me for not adding the license header. Thanks for looking into that. — AccessDatabase b) If it is the case I'll change to dbUrl.indexOf("://"). c) It's better if you can help me a bit on this, I'm not that familiar of using meta data calls. d) Whoops, I forgot to removed those automatic generated @author tags. e) I'll modify this. f) Yep, it's better not to shutdown, instead we can only close the connection. — PlanExporter g) I'll make it to private. Tool runs with the permissions given to derbytools.jar. — TreeNode h) I'll make it to package-private. i) I'll make it implicit. No need to explicitly inherit. j) Removed it. k) I might had a reason earlier, but you're right no need of a String now. — CreateHTMLFile l) I'll add this. m) thanks for the idea. — CreateXMLFile n) Actually I can't think of a way of doing that. Can you please explain a bit. o) I think I should change it to "Apache Derby Query Explainer ( DERBY-4587 )". p) I'm afraid not, will using the default be a problem? Thank you very much!
          Hide
          Kristian Waagan added a comment -

          Hi,

          As part of some other work, I plan to take the PlanExporter for a spin.
          I think such a tool has the potential to significantly improve the user friendliness
          of Derby.

          First, I have not followed development closely, and since I'm short on time at
          the moment, I don't have the time to investigate too much. However, I'm posting
          my comments in case they can be useful.
          Below are some comments originating from looking at the code. Some of them are
          nits, use your own judgement.

          a) The classes have no ASF license header.
          I will address this under DERBY-4764 together with some other files.

          — AccessDatabase
          b) Would it be better to use dbUrl.indexOf("://") to reduce the chance of
          mistakenly using the client driver instead of the embedded driver?
          Getting // can easily happen during scripting, for instance.
          c) The code is suspectible to some forms of SQL injection attacks.
          A quick look doesn't reveal anything severe, but in general it is wise to
          use either prepared statements or to validate the user input (i.e.,
          verify through meta data calls that the specified schema is indeed an
          existing schema.
          d) @author tags aren't used in the Derby code base
          e) Seems to be the private final variables could be static as well (also,
          many people prefer constants to be in upper case).
          f) Is it correct to shut down the database?
          I'd say not, and would be more comfortable either simply not doing it or
          have an option for it.

          — PlanExporter
          g) deleteFile is a potential security hole, allowing users to
          delete Derby files at will (I don't know which permissions the tool is
          running with by default). At the very least it should be made private,
          since it is used only within that single class.

          — TreeNode
          h) Class should be package-private.
          i) All Java objects implicitly inherit java.lang.Object. Is there a reason why
          it has been made explicit?
          j) Use of new String() is discouraged.
          k) Is thre a reason why depth is a String?

          — CreateHTMLF l) Missing class JavaDoc (short description of what it does).
          m) For convenience, it may be better to convert the HTML file name to upper-
          case and do endsWith(".HTML"). That way, you support mixed case as well.

          — CreateXMLFile
          n) You may want to unwrap the PrivilegedActionException, casting it to
          IOException (since that's the only checked exception that can occur).
          It makes the API slightly cleaner.
          o) I'm not sure what the community's take on the contents of the variable
          "comment" is.
          p) Has using a specific character encoding for the output file been discussed?
          String.getBytes() uses the default encoding on the platform.

          I'm going to take the tool for a spin as well, but decided to post my comments
          so far right away. As you mentioned, the number of comments under this issue is
          getting very large, it might be better to track follow-up work in a separate
          Jira.

          I'm excited to see if I can understand why one of the Derby tests fails with a
          patch of mine by looking at the output of the PlanExporter. I just have to
          hook it into the test itself, and then there's the problem that the Derby
          JUnit framework (or rather SupportFilesSetup) deletes anything written to the
          support files directories...

          Thanks

          Show
          Kristian Waagan added a comment - Hi, As part of some other work, I plan to take the PlanExporter for a spin. I think such a tool has the potential to significantly improve the user friendliness of Derby. First, I have not followed development closely, and since I'm short on time at the moment, I don't have the time to investigate too much. However, I'm posting my comments in case they can be useful. Below are some comments originating from looking at the code. Some of them are nits, use your own judgement. a) The classes have no ASF license header. I will address this under DERBY-4764 together with some other files. — AccessDatabase b) Would it be better to use dbUrl.indexOf("://") to reduce the chance of mistakenly using the client driver instead of the embedded driver? Getting // can easily happen during scripting, for instance. c) The code is suspectible to some forms of SQL injection attacks. A quick look doesn't reveal anything severe, but in general it is wise to use either prepared statements or to validate the user input (i.e., verify through meta data calls that the specified schema is indeed an existing schema. d) @author tags aren't used in the Derby code base e) Seems to be the private final variables could be static as well (also, many people prefer constants to be in upper case). f) Is it correct to shut down the database? I'd say not, and would be more comfortable either simply not doing it or have an option for it. — PlanExporter g) deleteFile is a potential security hole, allowing users to delete Derby files at will (I don't know which permissions the tool is running with by default). At the very least it should be made private, since it is used only within that single class. — TreeNode h) Class should be package-private. i) All Java objects implicitly inherit java.lang.Object. Is there a reason why it has been made explicit? j) Use of new String() is discouraged. k) Is thre a reason why depth is a String? — CreateHTMLF l) Missing class JavaDoc (short description of what it does). m) For convenience, it may be better to convert the HTML file name to upper- case and do endsWith(".HTML"). That way, you support mixed case as well. — CreateXMLFile n) You may want to unwrap the PrivilegedActionException, casting it to IOException (since that's the only checked exception that can occur). It makes the API slightly cleaner. o) I'm not sure what the community's take on the contents of the variable "comment" is. p) Has using a specific character encoding for the output file been discussed? String.getBytes() uses the default encoding on the platform. I'm going to take the tool for a spin as well, but decided to post my comments so far right away. As you mentioned, the number of comments under this issue is getting very large, it might be better to track follow-up work in a separate Jira. I'm excited to see if I can understand why one of the Derby tests fails with a patch of mine by looking at the output of the PlanExporter. I just have to hook it into the test itself, and then there's the problem that the Derby JUnit framework (or rather SupportFilesSetup) deletes anything written to the support files directories... Thanks
          Hide
          Kim Haase added a comment -

          Kathey, you could try applying the latest patch to DERBY-4758 – you'll be sure to notice if there's missing or unclear information, and your feedback would be very valuable.

          Show
          Kim Haase added a comment - Kathey, you could try applying the latest patch to DERBY-4758 – you'll be sure to notice if there's missing or unclear information, and your feedback would be very valuable.
          Hide
          Kathey Marsden added a comment -

          I am interested in giving this a try, but have not been following along during development. What is a good documentation entry point?

          Show
          Kathey Marsden added a comment - I am interested in giving this a try, but have not been following along during development. What is a good documentation entry point?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          It seems that Hudson build is back to normal http://hudson.zones.apache.org/hudson/job/Derby-trunk/468/console

          Show
          Nirmal Fernando added a comment - Hi Bryan, It seems that Hudson build is back to normal http://hudson.zones.apache.org/hudson/job/Derby-trunk/468/console
          Hide
          Nirmal Fernando added a comment - - edited

          Thank Bryan, for committing I am hoping that Hudson build will pass now

          Show
          Nirmal Fernando added a comment - - edited Thank Bryan, for committing I am hoping that Hudson build will pass now
          Hide
          Bryan Pendleton added a comment -

          Committed the javadoc fixes to the trunk as revision 982856.

          Show
          Bryan Pendleton added a comment - Committed the javadoc fixes to the trunk as revision 982856.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Attached javadoc fix.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Attached javadoc fix.
          Hide
          Bryan Pendleton added a comment -

          Nirmal, can you provide a patch that is relative to the trunk, and just includes
          the javadoc fixes? The other changes in the 9.7 patch are already committed to the trunk.

          Show
          Bryan Pendleton added a comment - Nirmal, can you provide a patch that is relative to the trunk, and just includes the javadoc fixes? The other changes in the 9.7 patch are already committed to the trunk.
          Hide
          Bryan Pendleton added a comment -

          Thanks Nirmal! I'm backed up with DERBY-4757 right now, then I'll take a look.

          Show
          Bryan Pendleton added a comment - Thanks Nirmal! I'm backed up with DERBY-4757 right now, then I'll take a look.
          Hide
          Nirmal Fernando added a comment -

          Hi,
          Myrna, Thanks for your suggestion, I think the way you told is appropriate
          in this case and worked fine.

          Bryan, I've overwritten the patch 9.7 and attached it, I think this should solve
          the Hudson build failure, let's see.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, Myrna, Thanks for your suggestion, I think the way you told is appropriate in this case and worked fine. Bryan, I've overwritten the patch 9.7 and attached it, I think this should solve the Hudson build failure, let's see. Thanks.
          Hide
          Myrna van Lunteren added a comment -

          Perhaps add something like planexporter/*.xsl to tools/javadoc/derbydocs_exclusions.ant?
          Or perhaps you can just do */.xsl (only other .xsl is tools/ant/xsl/sysinfo_junitreport.xsl).
          disclaimer: I'm not certain of the syntax nor have I done any testing.

          Show
          Myrna van Lunteren added a comment - Perhaps add something like planexporter /*.xsl to tools/javadoc/derbydocs_exclusions.ant? Or perhaps you can just do * / .xsl (only other .xsl is tools/ant/xsl/sysinfo_junitreport.xsl). disclaimer: I'm not certain of the syntax nor have I done any testing.
          Hide
          Bryan Pendleton added a comment -

          I'm not totally sure, but it looks like the Ant <javadoc> task can take some
          nested elements that tell it what to process.

          Maybe try something like:

          <packageset dir="..." defaultexcludes="yes">
          <exclude name="org/apache/derby/impl/tools/planexporter/resources/**"/>
          </packageset>

          I'm not totally sure whether this is right; we probably need an <include>
          element inside the <packageset> to specify "org/apache/derby/**", and
          I'm not sure what should be the "dir=" part of <packageset>

          Maybe you can try some things and see if you can get the "testingdocs"
          target to work? The target is in the top-level build.xml, I think.

          Show
          Bryan Pendleton added a comment - I'm not totally sure, but it looks like the Ant <javadoc> task can take some nested elements that tell it what to process. Maybe try something like: <packageset dir="..." defaultexcludes="yes"> <exclude name="org/apache/derby/impl/tools/planexporter/resources/**"/> </packageset> I'm not totally sure whether this is right; we probably need an <include> element inside the <packageset> to specify "org/apache/derby/**", and I'm not sure what should be the "dir=" part of <packageset> Maybe you can try some things and see if you can get the "testingdocs" target to work? The target is in the top-level build.xml, I think.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          If the earlier errors were not the reason, is it something to do with this warning:

          [javadoc] /home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:621: warning - @param argument "s:" is not a parameter name.

          I have accidentally put a "colon" there after s.

          The final log at the end says following:

          javadoc:

          BUILD SUCCESSFUL
          Total time: 2 minutes 11 seconds
          [WARNINGS] Parsing warnings in console log...
          [WARNINGS] Using set difference to compute new warnings
          [WARNINGS] Found 1 new annotations (0 high, 1 normal, 0 low)
          [WARNINGS] Found 1 annotations (0 high, 1 normal, 0 low)
          [WARNINGS] Setting build status to FAILURE since total number of annotations exceeds the threshold 0
          Sending e-mails to: derby-dev@db.apache.org bpendleton@apache.org
          Finished: FAILURE

          I'm not quite sure what is this annotation refers to? You have any idea?

          Show
          Nirmal Fernando added a comment - Hi Bryan, If the earlier errors were not the reason, is it something to do with this warning: [javadoc] /home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:621: warning - @param argument "s:" is not a parameter name. I have accidentally put a "colon" there after s. The final log at the end says following: javadoc: BUILD SUCCESSFUL Total time: 2 minutes 11 seconds [WARNINGS] Parsing warnings in console log... [WARNINGS] Using set difference to compute new warnings [WARNINGS] Found 1 new annotations (0 high, 1 normal, 0 low) [WARNINGS] Found 1 annotations (0 high, 1 normal, 0 low) [WARNINGS] Setting build status to FAILURE since total number of annotations exceeds the threshold 0 Sending e-mails to: derby-dev@db.apache.org bpendleton@apache.org Finished: FAILURE I'm not quite sure what is this annotation refers to? You have any idea?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I think it's something to do with those three style sheets we've embedded.

          [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl"
          [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl"
          [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl"

          Is there a way to skip those style sheets?

          Show
          Nirmal Fernando added a comment - Hi Bryan, I think it's something to do with those three style sheets we've embedded. [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl" [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl" [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl" Is there a way to skip those style sheets?
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,

          The automated Hudson build appears to be having problems with this patch.

          If you look at this screen:
          http://hudson.zones.apache.org/hudson/job/Derby-trunk/467/changes
          and then click on "Console output", you should be able to see the error
          messages from the build.

          Can you have a look and see if you can figure out what is wrong with the build?

          Show
          Bryan Pendleton added a comment - Hi Nirmal, The automated Hudson build appears to be having problems with this patch. If you look at this screen: http://hudson.zones.apache.org/hudson/job/Derby-trunk/467/changes and then click on "Console output", you should be able to see the error messages from the build. Can you have a look and see if you can figure out what is wrong with the build?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for committing this! Yeah, I think we should keep this issue open for sometime
          till we get some feedback on how it works.

          Thanks Bryan for all the help you provided, and for your wonderful thought about this kind
          of a tool.

          PS: Bryan don't you think that we should commit the documentation patch too?

          Thanks

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for committing this! Yeah, I think we should keep this issue open for sometime till we get some feedback on how it works. Thanks Bryan for all the help you provided, and for your wonderful thought about this kind of a tool. PS: Bryan don't you think that we should commit the documentation patch too? Thanks
          Hide
          Bryan Pendleton added a comment -

          I've committed the 9.7 patch to the Derby trunk as revision 982637.

          I haven't marked this issue resolved just yet, as I thought we would leave it
          open for a short while in case any issues arise during early use of the tool.

          Thanks for the contribution of this tool to the Derby community, Nirmal!

          I am hopeful that this tool provides an important basis for continued
          improvement in the query visualization tools for Derby.

          Show
          Bryan Pendleton added a comment - I've committed the 9.7 patch to the Derby trunk as revision 982637. I haven't marked this issue resolved just yet, as I thought we would leave it open for a short while in case any issues arise during early use of the tool. Thanks for the contribution of this tool to the Derby community, Nirmal! I am hopeful that this tool provides an important basis for continued improvement in the query visualization tools for Derby.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          DERBY-4587-tool-9.7.diff shows the changes I've done after embedding
          XSL style sheets I've created to derbytools.jar.

          I think the new patch is ready for commit.

          Thanks

          Show
          Nirmal Fernando added a comment - Hi All, DERBY-4587 -tool-9.7.diff shows the changes I've done after embedding XSL style sheets I've created to derbytools.jar. I think the new patch is ready for commit. Thanks
          Hide
          Nirmal Fernando added a comment -

          I'm attaching a new xsl style sheet: advancedViewXSL2.xsl,
          slightly different from advancedViewXSL. This shows the
          details of a particular node only when the mouse pointer is over
          that node, when a user taking it out, details will be hidden.

          Any views do you like to have?

          PS: We can't use images, since users also needed them in
          their machines

          Thanks.

          Show
          Nirmal Fernando added a comment - I'm attaching a new xsl style sheet: advancedViewXSL2.xsl, slightly different from advancedViewXSL. This shows the details of a particular node only when the mouse pointer is over that node, when a user taking it out, details will be hidden. Any views do you like to have? PS: We can't use images, since users also needed them in their machines Thanks.
          Hide
          Nirmal Fernando added a comment -

          AdavancedXSL-mouseover: screen shot shows details of one node, when I moved the mouse pointer over that node.

          Show
          Nirmal Fernando added a comment - AdavancedXSL-mouseover: screen shot shows details of one node, when I moved the mouse pointer over that node.
          Hide
          Nirmal Fernando added a comment -

          Attaching a bit advanced XSL, developed using javascript
          as the scripting language.

          Show
          Nirmal Fernando added a comment - Attaching a bit advanced XSL, developed using javascript as the scripting language.
          Hide
          Nirmal Fernando added a comment -

          Attaching screen shots advancedXSL-1,2,3 to show the much advanced XSL features.

          Show
          Nirmal Fernando added a comment - Attaching screen shots advancedXSL-1,2,3 to show the much advanced XSL features.
          Hide
          Nirmal Fernando added a comment -

          Attaching a patch DERBY-4587-tool-9.6.diff.

          This patch provides the tool, the functionality of
          using advanced XSL style sheets which have used
          javascript.

          To run the tool in advanced mode, you have to invoke the
          tool as following example:

          java org.apache.derby.tools.PlanExporter jdbc:derby:test1 me mine
          MYSCHEMA 9ac8804c-0129-cc31-ca9a-00000047f1e8
          -adv -xml C:\derby\advance.xml -xsl advancedViewXSL.xsl

          Note: since the XML will be created in C:\derby folder, you must have
          your advancedViewXSL.xsl style sheet also in that folder.

          or you can give the relative path to the style sheet.

          Thanks

          Show
          Nirmal Fernando added a comment - Attaching a patch DERBY-4587 -tool-9.6.diff. This patch provides the tool, the functionality of using advanced XSL style sheets which have used javascript. To run the tool in advanced mode, you have to invoke the tool as following example: java org.apache.derby.tools.PlanExporter jdbc:derby:test1 me mine MYSCHEMA 9ac8804c-0129-cc31-ca9a-00000047f1e8 -adv -xml C:\derby\advance.xml -xsl advancedViewXSL.xsl Note: since the XML will be created in C:\derby folder, you must have your advancedViewXSL.xsl style sheet also in that folder. or you can give the relative path to the style sheet. Thanks
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Nice to hear that !! These days I'm into developing more advanced XSL style sheets using CSS and JS. I'll update the JIRA soon, with my findings!

          Show
          Nirmal Fernando added a comment - Hi Bryan, Nice to hear that !! These days I'm into developing more advanced XSL style sheets using CSS and JS. I'll update the JIRA soon, with my findings!
          Hide
          Bryan Pendleton added a comment -

          With the latest patch, I am able to build successfully with my normal build environment.

          When I run the XplainStatisticsTest with xalan.jar in my classpath, it appears to
          run the XML-variations of the tests.

          And when I run the test without xalan.jar in my classpath, it appears to skip the
          XML-variations of the tests, but runs the base tests successfully.

          So, at least in my environment, the latest patch appears to have resolved the build problems!

          Show
          Bryan Pendleton added a comment - With the latest patch, I am able to build successfully with my normal build environment. When I run the XplainStatisticsTest with xalan.jar in my classpath, it appears to run the XML-variations of the tests. And when I run the test without xalan.jar in my classpath, it appears to skip the XML-variations of the tests, but runs the base tests successfully. So, at least in my environment, the latest patch appears to have resolved the build problems!
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          Attached DERBY-4587-tool-9.5.diff.
          I modified tests added by me in XplainStatisticsTest class,
          such that they are compatible with Java-4.
          I ran tests and all were passed.

          I highly appreciate if someone (since Bryan will not be available)
          can apply this patch and confirm that the tests ran successfully.

          Thank you very much!

          Show
          Nirmal Fernando added a comment - Hi All, Attached DERBY-4587 -tool-9.5.diff. I modified tests added by me in XplainStatisticsTest class, such that they are compatible with Java-4. I ran tests and all were passed. I highly appreciate if someone (since Bryan will not be available) can apply this patch and confirm that the tests ran successfully. Thank you very much!
          Hide
          Knut Anders Hatlen added a comment -

          Will XplainStatisticsTest run on non-Sun JVMs now that imports com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl?

          Show
          Knut Anders Hatlen added a comment - Will XplainStatisticsTest run on non-Sun JVMs now that imports com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Whoops, yeah I should put "PlanExporter" instead
          "planexporter".
          You're right Bryan, I'm developing in Windows Vista,
          I'll update it in my environment too, thanks for
          pointing it out.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Whoops, yeah I should put "PlanExporter" instead "planexporter". You're right Bryan, I'm developing in Windows Vista, I'll update it in my environment too, thanks for pointing it out.
          Hide
          Nirmal Fernando added a comment -

          Thanks Bryan, I'm glad that you were able
          to set up your environment.

          Anyway I'm looking into an old version of xalan,
          let's see whether it contains the required functionalities.

          Thanks.

          Show
          Nirmal Fernando added a comment - Thanks Bryan, I'm glad that you were able to set up your environment. Anyway I'm looking into an old version of xalan, let's see whether it contains the required functionalities. Thanks.
          Hide
          Bryan Pendleton added a comment -

          To get derbytools.jar to build, I had to change tools/jar/tools.properties so it says

          derby.module.planexporter=org.apache.derby.tools.PlanExporter

          Nirmal, do you develop on Windows? I think maybe on Windows the file
          names are not case sensitive, but on my Ubuntu system the file name
          case has to match exactly.

          Show
          Bryan Pendleton added a comment - To get derbytools.jar to build, I had to change tools/jar/tools.properties so it says derby.module.planexporter=org.apache.derby.tools.PlanExporter Nirmal, do you develop on Windows? I think maybe on Windows the file names are not case sensitive, but on my Ubuntu system the file name case has to match exactly.
          Hide
          Bryan Pendleton added a comment -

          I successfully built after I did the following:
          1) Edited my ant.properties to remove j14lib and j15lib, leaving only jdk16
          2) Removed my GCJ installation(s) from my machine, so that JDK 1.6 was
          the only JDK available to the build

          This confirms Nirmal's observation that the build is successful in a pure JDK 1.6 environment

          Show
          Bryan Pendleton added a comment - I successfully built after I did the following: 1) Edited my ant.properties to remove j14lib and j15lib, leaving only jdk16 2) Removed my GCJ installation(s) from my machine, so that JDK 1.6 was the only JDK available to the build This confirms Nirmal's observation that the build is successful in a pure JDK 1.6 environment
          Hide
          Bryan Pendleton added a comment -

          Yes, you're right, it is there in my jdk 1.6 environment.

          I wonder if the problem is due to:

          Setting property java15compile.classpath to /usr/lib/jvm/java-1.5.0-gcj-4.4/jre/lib/rt.jar

          It seems that the build chose to use my GCJ version of 1.5, rather than my Sun version.

          I tried forcing j15lib to point to my jdk 1.6 JRE, but then I get
          JDBC interface compilation errors due to the differences between
          JDBC 3 and JDBC 4.

          The Derby build has become a tad complex of late, and I'm
          having a bit of confusion here, but I'll continue to investigate...

          Show
          Bryan Pendleton added a comment - Yes, you're right, it is there in my jdk 1.6 environment. I wonder if the problem is due to: Setting property java15compile.classpath to /usr/lib/jvm/java-1.5.0-gcj-4.4/jre/lib/rt.jar It seems that the build chose to use my GCJ version of 1.5, rather than my Sun version. I tried forcing j15lib to point to my jdk 1.6 JRE, but then I get JDBC interface compilation errors due to the differences between JDBC 3 and JDBC 4. The Derby build has become a tad complex of late, and I'm having a bit of confusion here, but I'll continue to investigate...
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          If you look at your Jdk1.6 folder isn't it containing the file you mentioned
          above (jdk1.6.0\com\sun\org\apache\xpath\internal\domapi\XPathEvaluatorImpl.java)?

          In my jdk1.6 folder it's there.

          I wonder why it failed in your environment.

          Show
          Nirmal Fernando added a comment - Hi Bryan, If you look at your Jdk1.6 folder isn't it containing the file you mentioned above (jdk1.6.0\com\sun\org\apache\xpath\internal\domapi\XPathEvaluatorImpl.java)? In my jdk1.6 folder it's there. I wonder why it failed in your environment.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal, I saw your note on derby-dev. Thanks for the investigation. I agree
          that if we can use an older version of xalan in our tests, that seems like a
          good solution. As one more data point, I tried removing the jdk14 and jdk15
          definitions from my ant.properties, and now I get:

          /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:63: package com.sun.org.apache.xpath.internal.domapi does not exist
          [javac] import com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl;

          So even with a pure jdk 1.6 configuration I am still having some build problems.

          Show
          Bryan Pendleton added a comment - Hi Nirmal, I saw your note on derby-dev. Thanks for the investigation. I agree that if we can use an older version of xalan in our tests, that seems like a good solution. As one more data point, I tried removing the jdk14 and jdk15 definitions from my ant.properties, and now I get: /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:63: package com.sun.org.apache.xpath.internal.domapi does not exist [javac] import com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl; So even with a pure jdk 1.6 configuration I am still having some build problems.
          Hide
          Bryan Pendleton added a comment -

          It doesn't seem to matter what I set my CLASSPATH to. My 'ant all'
          step always has this as its classpath for compiling the test code:

          '/home/bpendleton/src/derby/trunk/classes:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/charsets.jar:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jce.jar:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jsse.jar:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/plugin.jar:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/rt.jar:
          /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/sunrsasign.jar:
          /home/bpendleton/src/derby/trunk/tools/java/junit.jar'

          I tried to hunt through the build.xml files a bit, and I could see that
          it looks like the xalan entry in the classpath is supposed to
          come from the extrapath.properties file, but I guess that file isn't
          getting read in my build environment for some reason?

          Show
          Bryan Pendleton added a comment - It doesn't seem to matter what I set my CLASSPATH to. My 'ant all' step always has this as its classpath for compiling the test code: '/home/bpendleton/src/derby/trunk/classes: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/charsets.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jce.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jsse.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/plugin.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/rt.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/sunrsasign.jar: /home/bpendleton/src/derby/trunk/tools/java/junit.jar' I tried to hunt through the build.xml files a bit, and I could see that it looks like the xalan entry in the classpath is supposed to come from the extrapath.properties file, but I guess that file isn't getting read in my build environment for some reason?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Seems like you're missing the Xalan.jar in your
          classpath. Can you please check whether you
          got that in your classpath.

          Meanwhile I'll look into that more deeply.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Seems like you're missing the Xalan.jar in your classpath. Can you please check whether you got that in your classpath. Meanwhile I'll look into that more deeply. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal, I'm having trouble getting the latest patch to build. I think
          I'm not getting xalan.jar into the classpath properly. Are there any
          special build instructions for this patch? What do you have your
          CLASSPATH set to when you build the patch?

          The errors I get are, e.g.,

          [javac] /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:2039: cannot find symbol
          [javac] symbol : variable XPathResult
          [javac] location: class org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest
          [javac] XPathResult.UNORDERED_NODE_ITERATOR_TYPE));

          Show
          Bryan Pendleton added a comment - Hi Nirmal, I'm having trouble getting the latest patch to build. I think I'm not getting xalan.jar into the classpath properly. Are there any special build instructions for this patch? What do you have your CLASSPATH set to when you build the patch? The errors I get are, e.g., [javac] /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:2039: cannot find symbol [javac] symbol : variable XPathResult [javac] location: class org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest [javac] XPathResult.UNORDERED_NODE_ITERATOR_TYPE));
          Hide
          Nirmal Fernando added a comment -

          Hi,

          The new patch DERBY-4587-tool-9.4 is addressed following:
          01) It allows a user to export

          1) only the XML: -xml

          {path}

          2) only the HTML with default XSL: -html {path}


          **here we'll generate a XML and delete it after the execution.
          **XSL should be in the directory where the tool invoked from.

          3) XML and HTML with default XSL: -xml

          {path} -html {path}


          **XSL should be in the directory where the tool invoked from.

          4) only the HTML with user specified XSL: -xsl

          {path} -html {path}

          **here we might generate a XML and delete it after the execution.

          5) XML and HTML with user specified XSL: -xml

          {path} -xsl {path}

          -html

          {path}

          Order of arguments will not matter, each possible order
          is handled in the code.

          02) Patch takes the default XSL style sheet from derbytools.jar.
          Therefore, before using the default XSL style sheet, we must manually
          add the style sheet to the derbytools.jar.

          03) I ran the regression tests under XplainStatisticsTest class,
          all cases were passed.

          We might now look at the possibility of committing the new tool.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, The new patch DERBY-4587 -tool-9.4 is addressed following: 01) It allows a user to export 1) only the XML: -xml {path} 2) only the HTML with default XSL: -html {path} **here we'll generate a XML and delete it after the execution. **XSL should be in the directory where the tool invoked from. 3) XML and HTML with default XSL: -xml {path} -html {path} **XSL should be in the directory where the tool invoked from. 4) only the HTML with user specified XSL: -xsl {path} -html {path} **here we might generate a XML and delete it after the execution. 5) XML and HTML with user specified XSL: -xml {path} -xsl {path} -html {path} Order of arguments will not matter, each possible order is handled in the code. 02) Patch takes the default XSL style sheet from derbytools.jar. Therefore, before using the default XSL style sheet, we must manually add the style sheet to the derbytools.jar. 03) I ran the regression tests under XplainStatisticsTest class, all cases were passed. We might now look at the possibility of committing the new tool. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi,
          Attaching the patch DERBY-4587-tool-9.3.diff which has addressed
          the following:
          1) removing all try/catch blocks, and instead, throwing exceptions.
          2) Added a new class
          "java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java"
          which will convert the created XML into a HTML, using XSLT style sheet.
          This class will throw a FileNotFoundException if it can't find the style sheet
          at user's directory where the tool invoked. That is:
          if user invoked the tool from: C:\OtherNirmal\GSoC\Code
          the style sheet's path must be: C:\OtherNirmal\GSoC\Code\vanilla_html.xsl

          The HTML will be created at the same directory as XML which is specified
          by the user.
          The name of the HTML will be created as following example:
          if user specified xml path is:
          C:\OtherNirmal\GSoC\Code\test\query.xml
          the HTML will be created at:
          C:\OtherNirmal\GSoC\Code\test\query_html.html

          Your comments are highly appreciated.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, Attaching the patch DERBY-4587 -tool-9.3.diff which has addressed the following: 1) removing all try/catch blocks, and instead, throwing exceptions. 2) Added a new class "java/tools/org/apache/derby/impl/tools/planexporter/CreateHTMLFile.java" which will convert the created XML into a HTML, using XSLT style sheet. This class will throw a FileNotFoundException if it can't find the style sheet at user's directory where the tool invoked. That is: if user invoked the tool from: C:\OtherNirmal\GSoC\Code the style sheet's path must be: C:\OtherNirmal\GSoC\Code\vanilla_html.xsl The HTML will be created at the same directory as XML which is specified by the user. The name of the HTML will be created as following example: if user specified xml path is: C:\OtherNirmal\GSoC\Code\test\query.xml the HTML will be created at: C:\OtherNirmal\GSoC\Code\test\query_html.html Your comments are highly appreciated. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Yes, at the outermost level of PlanExporter I think it is appropriate
          to catch (Exception); we don't need to name each specific type
          of exception there unless they would have different handling.

          In the test code, we generally don't catch exceptions at all. We
          generally just declare our test methods as

          throws Exception

          and let the exception be thrown out to JUnit, which will catch it
          and report it. The one exception to this rule is for a test which
          is deliberately testing the throwing of an exception, in which case
          the test catches the exception (and, in fact, should call fail() if the
          exception is not thrown)

          So yes, it's fine to have the test methods declare that they throw
          exceptions, and the test code should only catch the exceptions
          that the test case is deliberately provoking with its testing.

          I thought we had written something about this in the Derby wiki,
          but I went searching under http://wiki.apache.org/db-derby/IntroToJUnit
          and I couldn't find it. Does anybody know if we wrote a wiki
          page describing these coding conventions for JUnit tests? If
          not, it would be great to have one.

          Show
          Bryan Pendleton added a comment - Yes, at the outermost level of PlanExporter I think it is appropriate to catch (Exception); we don't need to name each specific type of exception there unless they would have different handling. In the test code, we generally don't catch exceptions at all. We generally just declare our test methods as throws Exception and let the exception be thrown out to JUnit, which will catch it and report it. The one exception to this rule is for a test which is deliberately testing the throwing of an exception, in which case the test catches the exception (and, in fact, should call fail() if the exception is not thrown) So yes, it's fine to have the test methods declare that they throw exceptions, and the test code should only catch the exceptions that the test case is deliberately provoking with its testing. I thought we had written something about this in the Derby wiki, but I went searching under http://wiki.apache.org/db-derby/IntroToJUnit and I couldn't find it. Does anybody know if we wrote a wiki page describing these coding conventions for JUnit tests? If not, it would be great to have one.
          Hide
          Nirmal Fernando added a comment -

          Bryan, What about try/catch blocks used in
          XplainStatisticsTest?
          The reason behind catching the exceptions
          there is if I throw an exception, many methods should
          be updated as they throws those exceptions.

          If it's ok, I'll throw exceptions instead of
          catching them at "XplainStatisticsTest"

          Thanks.

          Show
          Nirmal Fernando added a comment - Bryan, What about try/catch blocks used in XplainStatisticsTest? The reason behind catching the exceptions there is if I throw an exception, many methods should be updated as they throws those exceptions. If it's ok, I'll throw exceptions instead of catching them at "XplainStatisticsTest" Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for reviewing it.
          Is it ok to add only
          catch(Exception e)

          { e.printStackTrace()},
          at the very outermost main() method
          rather than catching each exception by its
          name i.e.
          catch(SQLException e){ e.printStackTrace()}

          ,
          etc.?

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for reviewing it. Is it ok to add only catch(Exception e) { e.printStackTrace()}, at the very outermost main() method rather than catching each exception by its name i.e. catch(SQLException e){ e.printStackTrace()} , etc.? Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,

          I have a concern about the error-handling technique used in the patch.

          In a number of places, particularly in AccessDatabase.java, but also in
          some of the other classes, the code follows this pattern:

          try

          { ... do something ... }

          catch (SomeException e)

          { e.printStackTrace(); }

          I don't think this is the best pattern to use.

          Instead, I think we should (a) remove the try/catch code from the method,
          and (b) change the method's declaration to say "throws SomeException"

          I suspect that, in general, the only try / catch block in the entire PlanExporter
          code should probably be at the very outermost main() method.

          One exception to this is in the shutdown() method, where we do expect
          to routinely catch and ignore the shutdown exception.

          thanks,

          bryan

          Show
          Bryan Pendleton added a comment - Hi Nirmal, I have a concern about the error-handling technique used in the patch. In a number of places, particularly in AccessDatabase.java, but also in some of the other classes, the code follows this pattern: try { ... do something ... } catch (SomeException e) { e.printStackTrace(); } I don't think this is the best pattern to use. Instead, I think we should (a) remove the try/catch code from the method, and (b) change the method's declaration to say "throws SomeException" I suspect that, in general, the only try / catch block in the entire PlanExporter code should probably be at the very outermost main() method. One exception to this is in the shutdown() method, where we do expect to routinely catch and ignore the shutdown exception. thanks, bryan
          Hide
          Nirmal Fernando added a comment -

          Hi,
          DERBY-4587-tool-9.2.diff patch removed the assertEqual methods
          in JDBC.java file and used Assert.assertEquals method of Junit
          Framework.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, DERBY-4587 -tool-9.2.diff patch removed the assertEqual methods in JDBC.java file and used Assert.assertEquals method of Junit Framework. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,

          I don't understand the two new "assertEqual" methods in JDBC.java. How
          are they different from the assertEquals() methods already present in
          junit.framework.Test?

          Show
          Bryan Pendleton added a comment - Hi Nirmal, I don't understand the two new "assertEqual" methods in JDBC.java. How are they different from the assertEquals() methods already present in junit.framework.Test?
          Hide
          Nirmal Fernando added a comment -

          Removed the requirement of policy change, after using
          doPrivileged method, in DERBY-4587-tool-9.1.diff.

          Thanks

          Show
          Nirmal Fernando added a comment - Removed the requirement of policy change, after using doPrivileged method, in DERBY-4587 -tool-9.1.diff. Thanks
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          This patch contains new tests added to XplainStatisticsTest.java class.
          Hope now we got some solid tests which we can verify the content
          of the XML files generated from PlanExporter tool.

          These tests will only run if the needed Xalan and JAXP classes are
          in the classpath.

          Test files will be created in extinout folder under user's testing directory.

          Comments are highly appreciated.
          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, This patch contains new tests added to XplainStatisticsTest.java class. Hope now we got some solid tests which we can verify the content of the XML files generated from PlanExporter tool. These tests will only run if the needed Xalan and JAXP classes are in the classpath. Test files will be created in extinout folder under user's testing directory. Comments are highly appreciated. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Yes, we should try to use xalan for our XPath execution support.
          I'm pretty sure that we have other regression tests (the XML tests)
          that use XPath in them, for example XMLTypeAndOpsTest does
          some XPath-testing I believe.

          You can find a little bit of information about xalan and Derby here:
          http://db.apache.org/derby/docs/10.6/devguide/cdevstandardsxml.html

          Our utility test methods should definitely be put in a common superclass
          so that we can share and re-use them over time as we develop more tests.

          Ideally, it would be nice if our tests could dynamically detect whether
          the xalan classes were available, and, if they aren't, then we just
          skip the XPath part of the tests. I think that the XML code in the Derby
          engine has some logic for doing this that we can examine to see how it works.

          Show
          Bryan Pendleton added a comment - Yes, we should try to use xalan for our XPath execution support. I'm pretty sure that we have other regression tests (the XML tests) that use XPath in them, for example XMLTypeAndOpsTest does some XPath-testing I believe. You can find a little bit of information about xalan and Derby here: http://db.apache.org/derby/docs/10.6/devguide/cdevstandardsxml.html Our utility test methods should definitely be put in a common superclass so that we can share and re-use them over time as we develop more tests. Ideally, it would be nice if our tests could dynamically detect whether the xalan classes were available, and, if they aren't, then we just skip the XPath part of the tests. I think that the XML code in the Derby engine has some logic for doing this that we can examine to see how it works.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I think this way would be enough to support solid regression tests.

          Is it possible to use the XML libraries such as xalan.jar to execute XPath queries?
          or do I need to implement those XMLTagCount(..) etc. methods?

          If I'm not mistaken, I have to create assertEquals(...) in JDBC.java, isn't it?

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, I think this way would be enough to support solid regression tests. Is it possible to use the XML libraries such as xalan.jar to execute XPath queries? or do I need to implement those XMLTagCount(..) etc. methods? If I'm not mistaken, I have to create assertEquals(...) in JDBC.java, isn't it? Thanks.
          Hide
          Bryan Pendleton added a comment -

          It seems to me that the basic XML exporting functionality is
          coming along quite well!

          I agree with you that doing more work on the visualization is
          important, and I think you are already starting to look at that.

          I'd really like to concentrate on getting a more complete
          regression test in place. My overall feeling is that we are
          getting close enough to having the XML plan exporter operational
          that we can start to think about committing it.

          But in order to commit it, we need to have enough of a regression
          test harness in place, that we can be confident that others
          can work with the code and run the regression tests and know
          that the XML plan exporter is being adequately tested.

          So I'd like to see us concentrate on how we might extend the
          current XplainStatisticsTest inter-action, to implement a
          thorough regression test setup.

          From a code point of view, I'd like to see some sort of
          simple assertions in the XML portion of the tests, along the
          lines of this pseudocode:

          1) Set up the XPLAIN tables and run some sort of SQL to capture data
          2) Verify the XPLAIN table contents
          — steps (1) and (2) are in the current tests already
          3) Export the XPLAIN data to XML using PlanExporter
          — you have got this working in the latest patch already
          4) Verify the XML contents

          Step (4) is somewhat involved, and I think it probably involves
          writing a few common subroutines that we can use to perform
          simple assertions against the generated XML:

          • does the XML contain this tag?
          • how many occurrences of this tag are there?
          • check the contents of this tag against this string

          I've been thinking that we could perhaps have some utility routines
          that used XPath expressions to match portions and contents of
          the XML document, and use those to build up regression test assertions.

          So the test code would then contain lines like:

          assertEquals(1, XmlTagCount("//rowCount");
          assertEquals("TABLESCAN", XmlTagContents("//node[2]/resultNodeType"));

          What do you think? Would some sort of simple XPath-based utility
          code be enough to support some solid regression tests?

          Show
          Bryan Pendleton added a comment - It seems to me that the basic XML exporting functionality is coming along quite well! I agree with you that doing more work on the visualization is important, and I think you are already starting to look at that. I'd really like to concentrate on getting a more complete regression test in place. My overall feeling is that we are getting close enough to having the XML plan exporter operational that we can start to think about committing it. But in order to commit it, we need to have enough of a regression test harness in place, that we can be confident that others can work with the code and run the regression tests and know that the XML plan exporter is being adequately tested. So I'd like to see us concentrate on how we might extend the current XplainStatisticsTest inter-action, to implement a thorough regression test setup. From a code point of view, I'd like to see some sort of simple assertions in the XML portion of the tests, along the lines of this pseudocode: 1) Set up the XPLAIN tables and run some sort of SQL to capture data 2) Verify the XPLAIN table contents — steps (1) and (2) are in the current tests already 3) Export the XPLAIN data to XML using PlanExporter — you have got this working in the latest patch already 4) Verify the XML contents Step (4) is somewhat involved, and I think it probably involves writing a few common subroutines that we can use to perform simple assertions against the generated XML: does the XML contain this tag? how many occurrences of this tag are there? check the contents of this tag against this string I've been thinking that we could perhaps have some utility routines that used XPath expressions to match portions and contents of the XML document, and use those to build up regression test assertions. So the test code would then contain lines like: assertEquals(1, XmlTagCount("//rowCount"); assertEquals("TABLESCAN", XmlTagContents("//node [2] /resultNodeType")); What do you think? Would some sort of simple XPath-based utility code be enough to support some solid regression tests?
          Hide
          Nirmal Fernando added a comment -

          Whoops! I forgot to remove the "id", sorry about that.

          Show
          Nirmal Fernando added a comment - Whoops! I forgot to remove the "id", sorry about that.
          Hide
          Nirmal Fernando added a comment -

          I've modified the XML document generated by the tool, as it displays nodes
          as a tree structure, an according to that I've changed the XSL style sheet as well.
          Screen shots are attached to display the outcome after applying the style sheet,
          and to display how a XML file looks, after changing it to a tress structure.

          As Rick requested I've changed the name of the tool to "PlanExporter".
          So, now you have to run the new tool like this:
          java org.apache.derby.tools.PlanExporter jdbc:derby:nirmal
          me mine MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 xml_file

          *note the change of the name.

          And the new patch (8) is contain these modifications done and also the modifications
          on XplainStatisticsTest.java.

          I've run that test file and all 17 tests were passed, and I had a close look at
          all the XML file, to check whether they display as expected in the tests
          and if I believe my eyes I couldn't find any error.

          Anyway I highly appreciate if someone can have a look at the final out come,
          if you need any help to set up contact me or Bryan.

          And I'm hoping to get ideas of all the members of the community, please feel free
          to criticize on the tool.

          Thanks.

          Show
          Nirmal Fernando added a comment - I've modified the XML document generated by the tool, as it displays nodes as a tree structure, an according to that I've changed the XSL style sheet as well. Screen shots are attached to display the outcome after applying the style sheet, and to display how a XML file looks, after changing it to a tress structure. As Rick requested I've changed the name of the tool to "PlanExporter". So, now you have to run the new tool like this: java org.apache.derby.tools.PlanExporter jdbc:derby:nirmal me mine MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 xml_file *note the change of the name. And the new patch (8) is contain these modifications done and also the modifications on XplainStatisticsTest.java. I've run that test file and all 17 tests were passed, and I had a close look at all the XML file, to check whether they display as expected in the tests and if I believe my eyes I couldn't find any error. Anyway I highly appreciate if someone can have a look at the final out come, if you need any help to set up contact me or Bryan. And I'm hoping to get ideas of all the members of the community, please feel free to criticize on the tool. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi,
          basic_html-2.3 screen shot shows changes done to the tool
          as it shows SORT_TYPE and NO_OUTPUT_ROWS details of
          SYSXPLAIN_SORT_PROPS table, only for sort related nodes.

          Thanks

          Show
          Nirmal Fernando added a comment - Hi, basic_html-2.3 screen shot shows changes done to the tool as it shows SORT_TYPE and NO_OUTPUT_ROWS details of SYSXPLAIN_SORT_PROPS table, only for sort related nodes. Thanks
          Hide
          Bryan Pendleton added a comment -

          Similarly to scans, there is also data in SYSXPLAIN_SORT_PROPS
          in certain cases, and it would be great if we could get that data
          into the XML document. For example, GROUPBY nodes usually
          have associated SORT_PROPS rows.

          For a simple example of a statement which has both SCAN_PROPS
          and SORT_PROPS data in it, see testGroupBySortProps() in
          XplainStatisticsTest.java

          Show
          Bryan Pendleton added a comment - Similarly to scans, there is also data in SYSXPLAIN_SORT_PROPS in certain cases, and it would be great if we could get that data into the XML document. For example, GROUPBY nodes usually have associated SORT_PROPS rows. For a simple example of a statement which has both SCAN_PROPS and SORT_PROPS data in it, see testGroupBySortProps() in XplainStatisticsTest.java
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          This patch (7-b) created after modifying the code such that it creates
          an element for query executed, added more scan related details,
          and shows scan related details only in scan nodes.

          I'm attaching the modified test file as well (test2).

          Also the modified "vanilla_html.xsl" such that it displays query executed,
          as well.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, This patch (7-b) created after modifying the code such that it creates an element for query executed, added more scan related details, and shows scan related details only in scan nodes. I'm attaching the modified test file as well (test2). Also the modified "vanilla_html.xsl" such that it displays query executed, as well. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          This screen shot shows, after modifying the tool, as it only emits, scan related details
          only if it's a "scan" node.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, This screen shot shows, after modifying the tool, as it only emits, scan related details only if it's a "scan" node. Thanks.
          Hide
          Bryan Pendleton added a comment -

          I think we could go either way. My first reaction was to desire the
          data only for scan-type nodes, but I could see an argument for
          having the tags always present, but be empty for non-scan nodes.

          In general, my experience with XML is that optional data is
          implemented using optional tags, so I think most users would
          expect to see the tags appear only for scan-related nodes.

          Show
          Bryan Pendleton added a comment - I think we could go either way. My first reaction was to desire the data only for scan-type nodes, but I could see an argument for having the tags always present, but be empty for non-scan nodes. In general, my experience with XML is that optional data is implemented using optional tags, so I think most users would expect to see the tags appear only for scan-related nodes.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          You want to appear those values, in all nodes or only in "scan" related nodes?

          Thanks

          Show
          Nirmal Fernando added a comment - Hi Bryan, You want to appear those values, in all nodes or only in "scan" related nodes? Thanks
          Hide
          Bryan Pendleton added a comment -

          Good, I see all four nodes now. Thanks!

          For result sets of "scan" table, like TABLESCAN or HASHSCAN, it
          would be useful to include the information from the SYSXPLAIN_SCAN_PROPS
          row. In particular, it would be nice for the SCAN nodes to identify what
          table was being scanned; I think that information is in the SCAN_OBJECT_NAME
          column of the corresponding SYSXPLAIN_SCAN_PROPS row.

          At the top of this page, there is a sample query that shows a little bit
          about how to join between SYSXPLAIN_RESULTSETS and SYSXPLAIN_SCAN_PROPS:
          http://db.apache.org/derby/docs/10.6/ref/rrefsysxplain_scan_props.html

          Show
          Bryan Pendleton added a comment - Good, I see all four nodes now. Thanks! For result sets of "scan" table, like TABLESCAN or HASHSCAN, it would be useful to include the information from the SYSXPLAIN_SCAN_PROPS row. In particular, it would be nice for the SCAN nodes to identify what table was being scanned; I think that information is in the SCAN_OBJECT_NAME column of the corresponding SYSXPLAIN_SCAN_PROPS row. At the top of this page, there is a sample query that shows a little bit about how to join between SYSXPLAIN_RESULTSETS and SYSXPLAIN_SCAN_PROPS: http://db.apache.org/derby/docs/10.6/ref/rrefsysxplain_scan_props.html
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          There're 4 nodes, and all 4 boxes are displaying, only thing is it's too long,
          so in the screen shot latter part is not visible. I'll attach another screen shot.

          Show
          Nirmal Fernando added a comment - Hi Bryan, There're 4 nodes, and all 4 boxes are displaying, only thing is it's too long, so in the screen shot latter part is not visible. I'll attach another screen shot.
          Hide
          Bryan Pendleton added a comment -

          In basic_html-2, are there 4 nodes or 3? I see 3 "boxes" in the screen shot, but
          it looks like there are 4 node names listed (PROJECTION,LOHASHJOIN,
          TABLESCAN,HASHSCAN). Is there a missing box for "HASHSCAN" node?)

          Show
          Bryan Pendleton added a comment - In basic_html-2, are there 4 nodes or 3? I see 3 "boxes" in the screen shot, but it looks like there are 4 node names listed (PROJECTION,LOHASHJOIN, TABLESCAN,HASHSCAN). Is there a missing box for "HASHSCAN" node?)
          Hide
          Nirmal Fernando added a comment -

          Here's the modified XSL, after adding statement executed.

          Show
          Nirmal Fernando added a comment - Here's the modified XSL, after adding statement executed.
          Hide
          Nirmal Fernando added a comment -

          Hi,

          In this patch DERBY-4587-tool-7.diff, I modified the code as per comments of Bryan.
          1) I used indexOf() instead of contains(), in AccessDatabase class.

          2) I added the entry for new tool in "\tools\jar\tools.properties" and now the new tool
          is successfully added to derbytools.jar.

          3) --When I run XplainStatisticsTest, I get AccessDenied exceptions
          --trying to write the XML files.
          Bryan, I've no idea why this happened, in my environment it ran successfully.

          ---I'm not sure exactly where the test is
          ---trying to write these test files, but it seems the location is not acceptable to
          ---the security policy that is getting used by the tests.
          The xml files are created in the same directory, where you have executed
          the test file.

          4) I've included the statement executed and attached a screen shot
          "basic_html-2".

          Appreciate your comments.
          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, In this patch DERBY-4587 -tool-7.diff, I modified the code as per comments of Bryan. 1) I used indexOf() instead of contains(), in AccessDatabase class. 2) I added the entry for new tool in "\tools\jar\tools.properties" and now the new tool is successfully added to derbytools.jar. 3) --When I run XplainStatisticsTest, I get AccessDenied exceptions --trying to write the XML files. Bryan, I've no idea why this happened, in my environment it ran successfully. ---I'm not sure exactly where the test is ---trying to write these test files, but it seems the location is not acceptable to ---the security policy that is getting used by the tests. The xml files are created in the same directory, where you have executed the test file. 4) I've included the statement executed and attached a screen shot "basic_html-2". Appreciate your comments. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi, this screen shot shows the basic html, after adding the name of the query executed.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, this screen shot shows the basic html, after adding the name of the query executed. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal
          I'm having several small problems working with the patch:
          1) In my build environment, String.contains() is not available, since
          I'm building with JDK 1.4, not JDK 1.5. Perhaps we could change
          the contains() calls in AccessDatabase.java to use indexOf() instead?
          2) It appears that the new code is not getting added to derbytools.jar.
          Perhaps a build file adjustment is needed to enroll the new classes
          in derbytools.jar?
          3) When I run XplainStatisticsTest, I get AccessDenied exceptions
          trying to write the XML files. I'm not sure exactly where the test is
          trying to write these test files, but it seems the location is not acceptable to
          the security policy that is getting used by the tests.

          When I run the test using the 'classes' directory in my classpath, problems
          (2) and (3) do not arise.

          I took a quick look at the generated XML files in the output, and they
          are starting to look very good!

          Can we include the STATEMENT_TEXT data into the generated XML
          output, so that we can see the actual statement that is getting run,
          as part of the XML file?

          Show
          Bryan Pendleton added a comment - Hi Nirmal I'm having several small problems working with the patch: 1) In my build environment, String.contains() is not available, since I'm building with JDK 1.4, not JDK 1.5. Perhaps we could change the contains() calls in AccessDatabase.java to use indexOf() instead? 2) It appears that the new code is not getting added to derbytools.jar. Perhaps a build file adjustment is needed to enroll the new classes in derbytools.jar? 3) When I run XplainStatisticsTest, I get AccessDenied exceptions trying to write the XML files. I'm not sure exactly where the test is trying to write these test files, but it seems the location is not acceptable to the security policy that is getting used by the tests. When I run the test using the 'classes' directory in my classpath, problems (2) and (3) do not arise. I took a quick look at the generated XML files in the output, and they are starting to look very good! Can we include the STATEMENT_TEXT data into the generated XML output, so that we can see the actual statement that is getting run, as part of the XML file?
          Hide
          Nirmal Fernando added a comment -

          Forgot to mention that I ran the junit test for "org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest.java"
          without errors.

          Show
          Nirmal Fernando added a comment - Forgot to mention that I ran the junit test for "org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest.java" without errors.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          I've created a test only to check the generation of XML files not to test the correctness of XML values,
          inside "org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest.java",
          you can find them in DERBY-4587-tool-test1.diff attached.

          While creating the tests I found an important check for the zero columns returned
          in the sysxplain_resultsets for DDL statements is needed. DERBY-4587-tool-6.diff
          included that modification as well.

          Your comments are mostly welcome.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, I've created a test only to check the generation of XML files not to test the correctness of XML values, inside "org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest.java", you can find them in DERBY-4587 -tool-test1.diff attached. While creating the tests I found an important check for the zero columns returned in the sysxplain_resultsets for DDL statements is needed. DERBY-4587 -tool-6.diff included that modification as well. Your comments are mostly welcome. Thanks.
          Hide
          Bryan Pendleton added a comment -

          The new XML document screenshot looks very good.

          Show
          Bryan Pendleton added a comment - The new XML document screenshot looks very good.
          Hide
          Nirmal Fernando added a comment -

          Hi,
          I've modified the code adding Bryan's suggestions, " xml_doc_screenshot" is taken from the generated XML document, I think now I can move to create tests for the XML document generation code.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, I've modified the code adding Bryan's suggestions, " xml_doc_screenshot" is taken from the generated XML document, I think now I can move to create tests for the XML document generation code. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Yes, this seems to be making great progress! The XSL stylesheet is quite clean,
          and the XML source document seems much more full-featured and complete.

          I think that the <arrow> and <image> tags shouldn't be in the base XML document,
          but rather should be added by the presentation layer (XSL or JavaScript libraries, etc.)

          And I'd like to see

          <returned_rows>4</returned_rows>

          rather than

          <returned_rows> # of Rows Returned= 4</returned_rows>

          and the same for <no_opens> tag.

          Show
          Bryan Pendleton added a comment - Yes, this seems to be making great progress! The XSL stylesheet is quite clean, and the XML source document seems much more full-featured and complete. I think that the <arrow> and <image> tags shouldn't be in the base XML document, but rather should be added by the presentation layer (XSL or JavaScript libraries, etc.) And I'd like to see <returned_rows>4</returned_rows> rather than <returned_rows> # of Rows Returned= 4</returned_rows> and the same for <no_opens> tag.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,
          This screen shot shows the simple html page as you suggested. I hope this is what you meant, if not please correct me.
          I'm attaching the pure XSL style sheet as well.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, This screen shot shows the simple html page as you suggested. I hope this is what you meant, if not please correct me. I'm attaching the pure XSL style sheet as well. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          This patch (DERBY-4587-tool-5.diff) is providing the functionality of emitting a more detailed "XML file" using the new tool. As an example, following is a part extracted after generating a XML file, using this patch:

          <plan>
          <details>
          <image>projection.jpg</image>
          <node>PROJECTION</node>
          <id>29d8c03e-0128-e492-0a47-00000035b7e8</id>
          <depth>0</depth>
          <parent_id>null</parent_id>
          <input_rows> null</input_rows>
          <returned_rows> # of Rows Returned= 4</returned_rows>
          <no_opens> # of Opens= 1</no_opens>
          <visited_pages> null</visited_pages>
          <scan_qualifiers> null</scan_qualifiers>
          <next_qualifiers> null</next_qualifiers>
          </details>
          <details>
          <arrow>arrow.jpg</arrow>
          <image>lohashjoin.jpg</image>
          <node>LOHASHJOIN</node>
          <id>8a184042-0128-e492-0a47-00000035b7e8</id>
          <depth>1</depth>
          <parent_id>29d8c03e-0128-e492-0a47-00000035b7e8</parent_id>
          <input_rows> null</input_rows>
          <returned_rows> # of Rows Returned= 4</returned_rows>
          <no_opens> # of Opens= 1</no_opens>
          <visited_pages> null</visited_pages>
          <scan_qualifiers> null</scan_qualifiers>
          <next_qualifiers> null</next_qualifiers>
          </details>
          .................................................................................
          ...........................................

          You can start the new tool like this:

          C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAsXML jdbc:derby:nirmal username password MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 name_of_the_xml_file

          MYSCHEMA - schema you specified when taking statistics
          b9810038-0128-e492-0a47-00000035b7e8 - is the STMT_ID in SYSXPLAIN_STATEMENTS table

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, This patch ( DERBY-4587 -tool-5.diff) is providing the functionality of emitting a more detailed "XML file" using the new tool. As an example, following is a part extracted after generating a XML file, using this patch: <plan> <details> <image>projection.jpg</image> <node>PROJECTION</node> <id>29d8c03e-0128-e492-0a47-00000035b7e8</id> <depth>0</depth> <parent_id>null</parent_id> <input_rows> null</input_rows> <returned_rows> # of Rows Returned= 4</returned_rows> <no_opens> # of Opens= 1</no_opens> <visited_pages> null</visited_pages> <scan_qualifiers> null</scan_qualifiers> <next_qualifiers> null</next_qualifiers> </details> <details> <arrow>arrow.jpg</arrow> <image>lohashjoin.jpg</image> <node>LOHASHJOIN</node> <id>8a184042-0128-e492-0a47-00000035b7e8</id> <depth>1</depth> <parent_id>29d8c03e-0128-e492-0a47-00000035b7e8</parent_id> <input_rows> null</input_rows> <returned_rows> # of Rows Returned= 4</returned_rows> <no_opens> # of Opens= 1</no_opens> <visited_pages> null</visited_pages> <scan_qualifiers> null</scan_qualifiers> <next_qualifiers> null</next_qualifiers> </details> ................................................................................. ........................................... You can start the new tool like this: C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAsXML jdbc:derby:nirmal username password MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 name_of_the_xml_file MYSCHEMA - schema you specified when taking statistics b9810038-0128-e492-0a47-00000035b7e8 - is the STMT_ID in SYSXPLAIN_STATEMENTS table Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,

          I think the screen shots are helpful and clear, thanks for attaching them!

          Regarding the XSL file itself, it seems to be coming along well.

          I'm a little uncertain about whether we should be embedding the
          dynamic HTML JavaScript functionality directly into the XSL. Rather
          than building our own JavaScript for making the page be interactive,
          could we build upon some of the existing JavaScript libraries that
          are already out there?

          I'm wondering whether the XSL style sheet should concentrate
          on simply formatting the XML query plan data into a simple and clear
          HTML document for basic display in a browser, and then separately
          we could provide a small JavaScript file which uses a library such as
          JQuery to provide dynamic browser behaviors.

          I think the overall approach should be to build a collection of small,
          simple, and focused tools, which a motivated Derby user can combine
          in a variety of ways:

          • export tool extracts Derby XPLAIN data and formats it into clear XML,
            including all the relevant data from the XPLAIN tables for that query
          • XSL stylesheet formats the XML into simple vanilla HTML, which
            can be viewed in a browser but is not anything fancy. Probably, this
            HTML is mostly just dictionary lists (DL tags) nested within each other
            to reflect the nested structure of the query plan, with the query plan
            data provided in the list contents as tag and data items to
            encode the name=value type information from the query nodes
          • CSS and JS resources can add dynamic behaviors to the HTML page
            to improve comprehension. For example, your expand/collapse treeview,
            but there are many other possibilities out there we could consider

          So if the XSL stylesheet simply formatted the query plan data into something like

          <dl>
          <dt>node type</dt><dd>HASHSCAN</dd>
          <dt>no_opens</dt><dd>4</dd>
          <dt>returned_rows</dt><dd>4</dd>
          <dt>visited_pages</dt><dd>1</dd>
          <dt>scan_qualifiers</dt><dd>None</dd>
          </dl>

          I think that would be very solid, and then we could work on the formatting
          and dynamic behaviors separately.

          Show
          Bryan Pendleton added a comment - Hi Nirmal, I think the screen shots are helpful and clear, thanks for attaching them! Regarding the XSL file itself, it seems to be coming along well. I'm a little uncertain about whether we should be embedding the dynamic HTML JavaScript functionality directly into the XSL. Rather than building our own JavaScript for making the page be interactive, could we build upon some of the existing JavaScript libraries that are already out there? I'm wondering whether the XSL style sheet should concentrate on simply formatting the XML query plan data into a simple and clear HTML document for basic display in a browser, and then separately we could provide a small JavaScript file which uses a library such as JQuery to provide dynamic browser behaviors. I think the overall approach should be to build a collection of small, simple, and focused tools, which a motivated Derby user can combine in a variety of ways: export tool extracts Derby XPLAIN data and formats it into clear XML, including all the relevant data from the XPLAIN tables for that query XSL stylesheet formats the XML into simple vanilla HTML, which can be viewed in a browser but is not anything fancy. Probably, this HTML is mostly just dictionary lists (DL tags) nested within each other to reflect the nested structure of the query plan, with the query plan data provided in the list contents as tag and data items to encode the name=value type information from the query nodes CSS and JS resources can add dynamic behaviors to the HTML page to improve comprehension. For example, your expand/collapse treeview, but there are many other possibilities out there we could consider So if the XSL stylesheet simply formatted the query plan data into something like <dl> <dt>node type</dt><dd>HASHSCAN</dd> <dt>no_opens</dt><dd>4</dd> <dt>returned_rows</dt><dd>4</dd> <dt>visited_pages</dt><dd>1</dd> <dt>scan_qualifiers</dt><dd>None</dd> </dl> I think that would be very solid, and then we could work on the formatting and dynamic behaviors separately.
          Hide
          Nirmal Fernando added a comment -

          Hi Rick,

          Thanks for the quick reply.

          I just create a sample XML for testing purposes, that was not for an actual query, that's why you can't see a single root node there. I've attached a new screen shot showing for possibly a real scenario.

          Sorry if the screen shot was not clear in showing that you only can expand or collapse a single node, not everything to the left, I hope new screen shot is showing this more clearly.

          I'll look into your suggestion on a left-deep tree, thanks for the idea.

          Please note that these details displayed are just rubbish, since this is only a test work.

          Show
          Nirmal Fernando added a comment - Hi Rick, Thanks for the quick reply. I just create a sample XML for testing purposes, that was not for an actual query, that's why you can't see a single root node there. I've attached a new screen shot showing for possibly a real scenario. Sorry if the screen shot was not clear in showing that you only can expand or collapse a single node, not everything to the left, I hope new screen shot is showing this more clearly. I'll look into your suggestion on a left-deep tree, thanks for the idea. Please note that these details displayed are just rubbish, since this is only a test work.
          Hide
          Rick Hillegas added a comment -

          Thanks, Nirmal. These look good. In particular, I like seeing the ability to collapse and expand nodes. I was a little puzzled by the lack of a top level level, root node. Ultimately, I would expect that at run-time, the tuples would flow out of a single root node at the top.

          This visualization is an improvement over what you can do with xml documents in Firefox today:

          1) The visualization looks cleaner--it doesn't have the distracting xml tags mixed in with it

          2) Probably this approach will work in a broader range of browsers besides Firefox.

          I was wondering about how hard a different visualization would be: Instead of a structure which looks like a folder tree, how about something which looks more like a left-deep tree? That's what Derby query plans are today. Extra credit if you could expand and collapse a single node without forcing everthing to the left to expand and collapse at the same time.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Thanks, Nirmal. These look good. In particular, I like seeing the ability to collapse and expand nodes. I was a little puzzled by the lack of a top level level, root node. Ultimately, I would expect that at run-time, the tuples would flow out of a single root node at the top. This visualization is an improvement over what you can do with xml documents in Firefox today: 1) The visualization looks cleaner--it doesn't have the distracting xml tags mixed in with it 2) Probably this approach will work in a broader range of browsers besides Firefox. I was wondering about how hard a different visualization would be: Instead of a structure which looks like a folder tree, how about something which looks more like a left-deep tree? That's what Derby query plans are today. Extra credit if you could expand and collapse a single node without forcing everthing to the left to expand and collapse at the same time. Thanks! -Rick
          Hide
          Nirmal Fernando added a comment -

          Hi,

          I've attached a very basic XSL style sheet (without enabling displaying a picture), and two screen shots to show how it works and a hand written XML file for testing purposes.

          I decided to build upon this style sheet, adding more features and a look.

          Highly appreciate comments from all of you.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, I've attached a very basic XSL style sheet (without enabling displaying a picture), and two screen shots to show how it works and a hand written XML file for testing purposes. I decided to build upon this style sheet, adding more features and a look. Highly appreciate comments from all of you. Thanks.
          Hide
          Nirmal Fernando added a comment -

          This is before expanding the nodes of the tree.

          Show
          Nirmal Fernando added a comment - This is before expanding the nodes of the tree.
          Hide
          Nirmal Fernando added a comment -

          This shows a screen shot took by me for a hand written XML file, after applying the very basic XSL stylesheet.

          Show
          Nirmal Fernando added a comment - This shows a screen shot took by me for a hand written XML file, after applying the very basic XSL stylesheet.
          Hide
          Bryan Pendleton added a comment -

          Yes, that's precisely what I meant. With that change in place, it seems
          like we should be able to add something like the diff below, and quickly
          get a fairly substantial suite of tests which call the new XML generation tools.

          The diff below patches into a common subroutine in the existing XplainStatisticsTest
          test suite, and makes it so that, each time we capture a set of XPLAIN data for
          a query in that suite, we then immediately export that data as XML.

          Of course, this doesn't actually verify the correctness of the XML, but it does
          drive the new XML export code, and so it provides a basis for us to build
          the next level of tests with.

          Index: XplainStatisticsTest.java
          ===================================================================
          — XplainStatisticsTest.java (revision 949434)
          +++ XplainStatisticsTest.java (working copy)
          @@ -490,6 +490,20 @@
          throws SQLException
          {
          s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
          +
          + ResultSet rs = s.executeQuery(
          + "select stmt_id from xpltest.sysxplain_statements");
          + while ( rs.hasNext() )
          +

          { + String stmt_id = rs.getString(1); + AccessDatabase access = + new AccessDatabase(getConnection(), "XPLTEST", stmt_id); + access.initializeDataArray(); + access.createXMLFragment(); + CreateXMLFile xml_file = new CreateXMLFile(); + xml_file.writeTheXMLFile(access.getData(), stmt_id + ".xml", + stmt_id + ".xsl"); + }

          }
          private void verifyXplainUnset(Statement s)
          throws SQLException

          Show
          Bryan Pendleton added a comment - Yes, that's precisely what I meant. With that change in place, it seems like we should be able to add something like the diff below, and quickly get a fairly substantial suite of tests which call the new XML generation tools. The diff below patches into a common subroutine in the existing XplainStatisticsTest test suite, and makes it so that, each time we capture a set of XPLAIN data for a query in that suite, we then immediately export that data as XML. Of course, this doesn't actually verify the correctness of the XML, but it does drive the new XML export code, and so it provides a basis for us to build the next level of tests with. Index: XplainStatisticsTest.java =================================================================== — XplainStatisticsTest.java (revision 949434) +++ XplainStatisticsTest.java (working copy) @@ -490,6 +490,20 @@ throws SQLException { s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)"); + + ResultSet rs = s.executeQuery( + "select stmt_id from xpltest.sysxplain_statements"); + while ( rs.hasNext() ) + { + String stmt_id = rs.getString(1); + AccessDatabase access = + new AccessDatabase(getConnection(), "XPLTEST", stmt_id); + access.initializeDataArray(); + access.createXMLFragment(); + CreateXMLFile xml_file = new CreateXMLFile(); + xml_file.writeTheXMLFile(access.getData(), stmt_id + ".xml", + stmt_id + ".xsl"); + } } private void verifyXplainUnset(Statement s) throws SQLException
          Hide
          Nirmal Fernando added a comment -

          Ya, Bryan, it would be fine.
          I think you meant adding something like:

          /**

          • @param aConn
          • @param aSchema
          • @param aQuery
            */
            public AccessDatabase(Connection aConn, String aSchema, String aQuery) { conn = aConn; schema = aSchema; query = aQuery; }

          while keeping my original constructor.

          Show
          Nirmal Fernando added a comment - Ya, Bryan, it would be fine. I think you meant adding something like: /** @param aConn @param aSchema @param aQuery */ public AccessDatabase(Connection aConn, String aSchema, String aQuery) { conn = aConn; schema = aSchema; query = aQuery; } while keeping my original constructor.
          Hide
          Bryan Pendleton added a comment -

          I've been thinking a little bit about testing. I was wondering whether we
          could change the AccessDatabase class a little bit so that there is an
          alternate constructor which allows a Connection to be passed in. Then,
          it would be possible to extend the XplainStatisticsTest so that it invokes
          the AccessDatabase class to emit the XML format of the various captured
          query plans in each of the test cases, as we ran them.

          What would you think about adding an alternate AccessDatabase constructor
          which took a Connection rather than a jdbcUrl?

          Show
          Bryan Pendleton added a comment - I've been thinking a little bit about testing. I was wondering whether we could change the AccessDatabase class a little bit so that there is an alternate constructor which allows a Connection to be passed in. Then, it would be possible to extend the XplainStatisticsTest so that it invokes the AccessDatabase class to emit the XML format of the various captured query plans in each of the test cases, as we ran them. What would you think about adding an alternate AccessDatabase constructor which took a Connection rather than a jdbcUrl?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I think the tree structure should be built using the XSL style sheet, but as you mentioned we need to have a more richer data structure, which will have attributes such as id, parent_id etc, and in the XML file there will be new xml tags inside <details> tag which will give the values of node_id, parent_id, depth_level etc.

          These days I built a richer data structure called "TreeNode". This patch contains this new data structure. And I'm printing the nodes as a tree, following are two instances.

          C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs
          XML jdbc:derby:nirmal me mine ME 4d6f406a-0128-f969-2afb-00000035d458 abc

          ----------------- Nodes grouped by the depth--------------

          (0,0) <node>PROJECTION</node>
          (1,0) <node>LOHASHJOIN</node>
          (2,0) <node>PROJECTION</node>
          (2,1) <node>HASHSCAN</node>
          (3,0) <node>LOHASHJOIN</node>
          (4,0) <node>TABLESCAN</node>
          (4,1) <node>HASHSCAN</node>

          ----------TREE STRUCTURE----------------

          <node>PROJECTION</node>
          <node>LOHASHJOIN</node>
          <node>PROJECTION</node>
          <node>LOHASHJOIN</node>
          <node>TABLESCAN</node>
          <node>HASHSCAN</node>
          <node>HASHSCAN</node>

          C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs
          XML jdbc:derby:nirmal me mine MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 abc

          ----------------- Nodes grouped by the depth--------------

          (0,0) <node>PROJECTION</node>
          (1,0) <node>LOHASHJOIN</node>
          (2,0) <node>TABLESCAN</node>
          (2,1) <node>HASHSCAN</node>

          ----------TREE STRUCTURE----------------

          <node>PROJECTION</node>
          <node>LOHASHJOIN</node>
          <node>TABLESCAN</node>
          <node>HASHSCAN</node>

          Note:
          *The patch is not that well commented and clean, I just want to let you know that, now I can recognize the points of each node in the tree.
          *This will not write an xml document, though it takes the file name as input, I've just commented it, since I haven't done with processing new xml tags yet.

          Your ideas are highly appreciated.

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, I think the tree structure should be built using the XSL style sheet, but as you mentioned we need to have a more richer data structure, which will have attributes such as id, parent_id etc, and in the XML file there will be new xml tags inside <details> tag which will give the values of node_id, parent_id, depth_level etc. These days I built a richer data structure called "TreeNode". This patch contains this new data structure. And I'm printing the nodes as a tree, following are two instances. C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs XML jdbc:derby:nirmal me mine ME 4d6f406a-0128-f969-2afb-00000035d458 abc ----------------- Nodes grouped by the depth-------------- (0,0) <node>PROJECTION</node> (1,0) <node>LOHASHJOIN</node> (2,0) <node>PROJECTION</node> (2,1) <node>HASHSCAN</node> (3,0) <node>LOHASHJOIN</node> (4,0) <node>TABLESCAN</node> (4,1) <node>HASHSCAN</node> ---------- TREE STRUCTURE ---------------- <node>PROJECTION</node> <node>LOHASHJOIN</node> <node>PROJECTION</node> <node>LOHASHJOIN</node> <node>TABLESCAN</node> <node>HASHSCAN</node> <node>HASHSCAN</node> C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs XML jdbc:derby:nirmal me mine MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 abc ----------------- Nodes grouped by the depth-------------- (0,0) <node>PROJECTION</node> (1,0) <node>LOHASHJOIN</node> (2,0) <node>TABLESCAN</node> (2,1) <node>HASHSCAN</node> ---------- TREE STRUCTURE ---------------- <node>PROJECTION</node> <node>LOHASHJOIN</node> <node>TABLESCAN</node> <node>HASHSCAN</node> Note: *The patch is not that well commented and clean, I just want to let you know that, now I can recognize the points of each node in the tree. *This will not write an xml document, though it takes the file name as input, I've just commented it, since I haven't done with processing new xml tags yet. Your ideas are highly appreciated. Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,

          It seems that the current XML document built by the v3 patch is capturing all
          of the result set nodes, but it is "flattening" them into a simple list, which loses
          some of the information that is present in the query plan.

          I think that we will want to build a richer data structure in the AccessDatabase class,
          in order to capture the the natural tree structure of a query plan. Entries in a query
          plan are structured into parent-child relationships, (there's a nice writeup on the
          basic concepts here: http://en.wikipedia.org/wiki/Query_optimizer) and we need
          to be able to capture that structure into the natural tree structure of the
          resulting XML document.

          For example, consider this simple query:

          create table t1 (c1 int, c2 varchar(100));
          create table t2 (a int, b int);
          select t1.c1, t1,c2 from t1 inner join t2 on c1 = a;

          This query is executed using a 3-level query tree, which can be seen
          by looking at the RS_ID and PARENT_RS_ID columns in SYSXPLAN_RESULTSETS:

          ij> select rs_id, op_identifier, parent_rs_id from s2.sysxplain_resultsets;
          RS_ID |OP_IDENTIFIER |PARENT_RS_ID
          --------------------------------------------------------------------------------------------------------
          2589c06b-0128-ea8c-c9a5-000000e76260|PROJECTION |NULL
          addac06e-0128-ea8c-c9a5-000000e76260|HASHJOIN |2589c06b-0128-ea8c-c9a5-00000e76260
          862e0071-0128-ea8c-c9a5-000000e76260|TABLESCAN |addac06e-0128-ea8c-c9a5-000000e76260
          5e838074-0128-ea8c-c9a5-000000e76260|HASHSCAN |addac06e-0128-ea8c-c9a5-000000e76260

          The PROJECTION node is the "root" of the tree, and it has 1 child, the HASHJOIN node,
          which in turn has two children, the TABLESCAN and HASHSCAN nodes.

          As a tree, it looks something like:

          PROJECTION

          HASHJOIN

          ----------------------+

           

          TABLESCAN HASHSCAN

          When we format this query into an XML document, the tree structure needs to
          be preserved, so that we end up with an XML document which expresses
          the tree structure using nesting of XML nodes, so that we get something like:

          <node>
          <type>PROJECTION</type>
          <node>
          <type>HASHJOIN</type>
          <node>
          <type>TABLESCAN</type>
          </node>
          <node>
          <type>HASHSCAN</type>
          </node>
          </node>
          </node>

          I think that the tool will need a more sophisticated method for analyzing
          the contents of the SYSXPLAIN tables in order to deduce this query
          plan structure from the PARENT_RS_ID values.

          One idea is to use some of Java's built-in tree-structured collections
          classes, such as a java.util.TreeMap, to retrieve the data from the
          SYSXPLAIN tables, then after the data has been retrieved, traverse the
          TreeMap in order to emit the XML document with the structure expressed
          as XML node containment.

          Does this make sense?

          Show
          Bryan Pendleton added a comment - Hi Nirmal, It seems that the current XML document built by the v3 patch is capturing all of the result set nodes, but it is "flattening" them into a simple list, which loses some of the information that is present in the query plan. I think that we will want to build a richer data structure in the AccessDatabase class, in order to capture the the natural tree structure of a query plan. Entries in a query plan are structured into parent-child relationships, (there's a nice writeup on the basic concepts here: http://en.wikipedia.org/wiki/Query_optimizer ) and we need to be able to capture that structure into the natural tree structure of the resulting XML document. For example, consider this simple query: create table t1 (c1 int, c2 varchar(100)); create table t2 (a int, b int); select t1.c1, t1,c2 from t1 inner join t2 on c1 = a; This query is executed using a 3-level query tree, which can be seen by looking at the RS_ID and PARENT_RS_ID columns in SYSXPLAN_RESULTSETS: ij> select rs_id, op_identifier, parent_rs_id from s2.sysxplain_resultsets; RS_ID |OP_IDENTIFIER |PARENT_RS_ID -------------------------------------------------------------------------------------------------------- 2589c06b-0128-ea8c-c9a5-000000e76260|PROJECTION |NULL addac06e-0128-ea8c-c9a5-000000e76260|HASHJOIN |2589c06b-0128-ea8c-c9a5-00000e76260 862e0071-0128-ea8c-c9a5-000000e76260|TABLESCAN |addac06e-0128-ea8c-c9a5-000000e76260 5e838074-0128-ea8c-c9a5-000000e76260|HASHSCAN |addac06e-0128-ea8c-c9a5-000000e76260 The PROJECTION node is the "root" of the tree, and it has 1 child, the HASHJOIN node, which in turn has two children, the TABLESCAN and HASHSCAN nodes. As a tree, it looks something like: PROJECTION HASHJOIN --------- -------------+   TABLESCAN HASHSCAN When we format this query into an XML document, the tree structure needs to be preserved, so that we end up with an XML document which expresses the tree structure using nesting of XML nodes, so that we get something like: <node> <type>PROJECTION</type> <node> <type>HASHJOIN</type> <node> <type>TABLESCAN</type> </node> <node> <type>HASHSCAN</type> </node> </node> </node> I think that the tool will need a more sophisticated method for analyzing the contents of the SYSXPLAIN tables in order to deduce this query plan structure from the PARENT_RS_ID values. One idea is to use some of Java's built-in tree-structured collections classes, such as a java.util.TreeMap, to retrieve the data from the SYSXPLAIN tables, then after the data has been retrieved, traverse the TreeMap in order to emit the XML document with the structure expressed as XML node containment. Does this make sense?
          Hide
          Nirmal Fernando added a comment -

          Earlier patch is updated as the tool recognizes the embedded URLs and network URLs.

          Will this approach general enough?

          Thanks.

          Show
          Nirmal Fernando added a comment - Earlier patch is updated as the tool recognizes the embedded URLs and network URLs. Will this approach general enough? Thanks.
          Hide
          Nirmal Fernando added a comment - - edited

          DERBY-4587-tool-2.diff patch is created in the trunk folder, and improved readability in AccessDatabase.createXMLFragment() method.

          Show
          Nirmal Fernando added a comment - - edited DERBY-4587 -tool-2.diff patch is created in the trunk folder, and improved readability in AccessDatabase.createXMLFragment() method.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks.

          1) I'll look into this. Thanks for pointing it out.

          2) I'm sorry about that, I've submitted a new patch.

          3) I checked for a query like "select t.* from (tt left outer join t on tt.j = t.x)" it worked fine [0]. NOTE: If some XPLAIN table returns 0 rows, this will not emit that XML tag.

          4) Did you mean to catch them inside the ExportQueryPlanAsXML.main method? or just throw the error everywhere?

          5) Whoops, I thought to do that, but somehow forgot it. I've updated it in the new patch.

          6) I'm afraid I hadn't. But while reading your suggestion, a question popped up in my mind, whether we can/Is it better to, invoke a new tool after closing an ij connection, inside a same regression test? (Just thought)

          Thanks for reviewing the patch this quickly.

          [0] The XML file generated for the query "select t.* from (tt left outer join t on tt.j = t.x)":

          <!--
          Designed & coded by C.S.Nirmal J. Fernando, of University of Moratuwa, Sri Lanka, to prototype Apache Derby Query Explainer
          -->

          <plan>

          <details>
          <node>PROJECTION</node>
          <no_opens>1</no_opens>
          <returned_rows>4</returned_rows>
          </details>

          <details>
          <node>LOHASHJOIN</node>
          <no_opens>1</no_opens>
          <returned_rows>4</returned_rows>
          </details>

          <details>
          <node>TABLESCAN</node>
          <no_opens>1</no_opens>
          <returned_rows>4</returned_rows>
          <visited_pages>1</visited_pages>
          <scan_qualifiers>None</scan_qualifiers>
          </details>

          <details>
          <node>HASHSCAN</node>
          <no_opens>4</no_opens>
          <returned_rows>4</returned_rows>
          <visited_pages>1</visited_pages>
          <scan_qualifiers>None</scan_qualifiers>
          <next_qualifiers>
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          </next_qualifiers>
          </details>

          </plan>

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks. 1) I'll look into this. Thanks for pointing it out. 2) I'm sorry about that, I've submitted a new patch. 3) I checked for a query like "select t.* from (tt left outer join t on tt.j = t.x)" it worked fine [0] . NOTE: If some XPLAIN table returns 0 rows, this will not emit that XML tag. 4) Did you mean to catch them inside the ExportQueryPlanAsXML.main method? or just throw the error everywhere? 5) Whoops, I thought to do that, but somehow forgot it. I've updated it in the new patch. 6) I'm afraid I hadn't. But while reading your suggestion, a question popped up in my mind, whether we can/Is it better to, invoke a new tool after closing an ij connection, inside a same regression test? (Just thought) Thanks for reviewing the patch this quickly. [0] The XML file generated for the query "select t.* from (tt left outer join t on tt.j = t.x)": <!-- Designed & coded by C.S.Nirmal J. Fernando, of University of Moratuwa, Sri Lanka, to prototype Apache Derby Query Explainer --> <plan> <details> <node>PROJECTION</node> <no_opens>1</no_opens> <returned_rows>4</returned_rows> </details> <details> <node>LOHASHJOIN</node> <no_opens>1</no_opens> <returned_rows>4</returned_rows> </details> − <details> <node>TABLESCAN</node> <no_opens>1</no_opens> <returned_rows>4</returned_rows> <visited_pages>1</visited_pages> <scan_qualifiers>None</scan_qualifiers> </details> <details> <node>HASHSCAN</node> <no_opens>4</no_opens> <returned_rows>4</returned_rows> <visited_pages>1</visited_pages> <scan_qualifiers>None</scan_qualifiers> <next_qualifiers> Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false </next_qualifiers> </details> </plan>
          Hide
          Bryan Pendleton added a comment -

          I successfully applied and built the test, and ran the XML generation tool (once).
          Great work! This is very exciting!

          Some first comments:
          1) I had some trouble running the tool, until I realized that the tool was always
          loading the ClientDriver, and I was trying to create an embedded connection. I
          think we want to support both client-style URLs and embedded-style URLs, so
          we need a way to detect which connection URL is provided, and load the
          appropriate driver.

          2) I had to apply the patch from the 'java' directory; it is more conventional to
          create patches to be applied from the 'trunk' directory.

          3) I just tried the generation tool with a very simple query in my XPLAIN tables,
          and it seemed to work. If I get a chance, I'll try to run it against some other XPLAIN output.

          4) I'm not sure that it is helpful for the AccessDatabase class to be catching
          the SQLException errors; it might be better to declare those methods as
          throwing the exceptions and let the exceptions propagate out.

          5) I found the code in createXMLFragment() a bit hard to read; perhaps it
          would be a bit more legible if the long lines were broken up into multiple
          shorter lines?

          6) Have you had a chance to think about how we might go about building
          a regression test suite for the new tool? One possibility would be to extend
          the current XplainStatisticsTest so that, in addition to generating XPLAIN
          data for various query plans, that test also calls your new tool to format the
          data as XML, then checks the XML documents for correctness.

          Show
          Bryan Pendleton added a comment - I successfully applied and built the test, and ran the XML generation tool (once). Great work! This is very exciting! Some first comments: 1) I had some trouble running the tool, until I realized that the tool was always loading the ClientDriver, and I was trying to create an embedded connection. I think we want to support both client-style URLs and embedded-style URLs, so we need a way to detect which connection URL is provided, and load the appropriate driver. 2) I had to apply the patch from the 'java' directory; it is more conventional to create patches to be applied from the 'trunk' directory. 3) I just tried the generation tool with a very simple query in my XPLAIN tables, and it seemed to work. If I get a chance, I'll try to run it against some other XPLAIN output. 4) I'm not sure that it is helpful for the AccessDatabase class to be catching the SQLException errors; it might be better to declare those methods as throwing the exceptions and let the exceptions propagate out. 5) I found the code in createXMLFragment() a bit hard to read; perhaps it would be a bit more legible if the long lines were broken up into multiple shorter lines? 6) Have you had a chance to think about how we might go about building a regression test suite for the new tool? One possibility would be to extend the current XplainStatisticsTest so that, in addition to generating XPLAIN data for various query plans, that test also calls your new tool to format the data as XML, then checks the XML documents for correctness.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          I've attached a patch that will emit query plans to a .xml file. There may be lots of drawbacks, therefore I highly appreciate your comments.

          You can start the new tool like this:

          C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAsXML jdbc:derby:nirmal username password MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 name_of_the_xml_file

          MYSCHEMA - schema you specified when taking statistics
          b9810038-0128-e492-0a47-00000035b7e8 - is the STMT_ID in SYSXPLAIN_STATEMENTS table

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi All, I've attached a patch that will emit query plans to a .xml file. There may be lots of drawbacks, therefore I highly appreciate your comments. You can start the new tool like this: C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAsXML jdbc:derby:nirmal username password MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 name_of_the_xml_file MYSCHEMA - schema you specified when taking statistics b9810038-0128-e492-0a47-00000035b7e8 - is the STMT_ID in SYSXPLAIN_STATEMENTS table Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          I think it's time to get finalize the design or the process view of the implementation. We highly appreciate comments on this design from you. I have created a small wiki page mentioning the design we planned. The link to the wiki page is http://wiki.apache.org/db-derby/Nirmal/Derby-4587-%20Design%20Details

          Thanks in advance.

          Show
          Nirmal Fernando added a comment - Hi All, I think it's time to get finalize the design or the process view of the implementation. We highly appreciate comments on this design from you. I have created a small wiki page mentioning the design we planned. The link to the wiki page is http://wiki.apache.org/db-derby/Nirmal/Derby-4587-%20Design%20Details Thanks in advance.
          Hide
          Nirmal Fernando added a comment -

          Hi Knut,

          Thanks for the suggestion, it worked fine.

          Show
          Nirmal Fernando added a comment - Hi Knut, Thanks for the suggestion, it worked fine.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Nirmal,

          The extra blank space comes from the way the CHAR function converts integers to strings. See the paragraph with the title "Integer to character syntax" in the link I posted above. If you want to get rid of the blanks, I think you can use the TRIM function:

          TRIM(CHAR(no_opens))

          http://db.apache.org/derby/docs/10.5/ref/rreftrimfunc.html

          Show
          Knut Anders Hatlen added a comment - Hi Nirmal, The extra blank space comes from the way the CHAR function converts integers to strings. See the paragraph with the title "Integer to character syntax" in the link I posted above. If you want to get rid of the blanks, I think you can use the TRIM function: TRIM(CHAR(no_opens)) http://db.apache.org/derby/docs/10.5/ref/rreftrimfunc.html
          Hide
          Nirmal Fernando added a comment -

          Thanks Knut. That gave me the expected result. But I wonder why it left blank space between the value and the closing tag (This won't be an affect in this case, just curious though).

          Thanks Bryan for the reply but we cannot convert numeric type to varchar. Any way Knut's suggestion worked fine so we can use ,

          SELECT '<result_set>'||
          '<type>'|| CHAR (op_identifier) ||'</type>'||
          '<no_opens>'||CHAR(no_opens)||'</no_opens>'||
          ... repeat for the other columsn from sysxplain_resultsets...
          '</result_set>'
          FROM xxx.sysxplain_resultsets WHERE ...
          this way to produce small XML fragments, and then stitch them to an .xml file. (of course we need to add XML related lines up front)

          Show
          Nirmal Fernando added a comment - Thanks Knut. That gave me the expected result. But I wonder why it left blank space between the value and the closing tag (This won't be an affect in this case, just curious though). Thanks Bryan for the reply but we cannot convert numeric type to varchar. Any way Knut's suggestion worked fine so we can use , SELECT '<result_set>'|| '<type>'|| CHAR (op_identifier) ||'</type>'|| '<no_opens>'||CHAR(no_opens)||'</no_opens>'|| ... repeat for the other columsn from sysxplain_resultsets... '</result_set>' FROM xxx.sysxplain_resultsets WHERE ... this way to produce small XML fragments, and then stitch them to an .xml file. (of course we need to add XML related lines up front)
          Hide
          Knut Anders Hatlen added a comment -

          I don't think you can cast a numeric type to a character type. But there is a CHAR function (http://db.apache.org/derby/docs/10.5/ref/rrefbuiltchar.html) that converts various data types to CHAR.

          So for example: ... || '<no_opens>' || CHAR(no_opens) || '</no_opens>' || ...

          Show
          Knut Anders Hatlen added a comment - I don't think you can cast a numeric type to a character type. But there is a CHAR function ( http://db.apache.org/derby/docs/10.5/ref/rrefbuiltchar.html ) that converts various data types to CHAR. So for example: ... || '<no_opens>' || CHAR(no_opens) || '</no_opens>' || ...
          Hide
          Bryan Pendleton added a comment -

          Can you use

          CAST (column AS VARCHAR(20))

          to convert the numeric datatypes to strings?

          Show
          Bryan Pendleton added a comment - Can you use CAST (column AS VARCHAR(20)) to convert the numeric datatypes to strings?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for the reply.

          I think this approach looks fine. I checked for some queries and successful in VARCHAR, but not for INTEGER, DOUBLE etc. May be '||' is support only for VARCHAR, frankly I do not know about using '||', if you can guide me to a resource to read on '||', that would be nice. If the case is that, is there a way that we can convert other data types to varchar?

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for the reply. I think this approach looks fine. I checked for some queries and successful in VARCHAR, but not for INTEGER, DOUBLE etc. May be '||' is support only for VARCHAR, frankly I do not know about using '||', if you can guide me to a resource to read on '||', that would be nice. If the case is that, is there a way that we can convert other data types to varchar? Thanks.
          Hide
          Bryan Pendleton added a comment -

          I think that we should avoid changing the schema of the XPLAIN tables.

          For the time being, can we pursue an implementation where the XML
          generation is done in the client side of the tool, perhaps something like:

          SELECT '<result_set>'||
          '<type>'||op_identifier||'</type>'||
          '<no_opens>'||no_opens||'</no_opens>'||
          ... repeat for the other columsn from sysxplain_resultsets...
          '</result_set>'
          FROM xxx.sysxplain_resultsets WHERE ...

          Would an approach like this work to generate the XML format of the query plan data?

          Show
          Bryan Pendleton added a comment - I think that we should avoid changing the schema of the XPLAIN tables. For the time being, can we pursue an implementation where the XML generation is done in the client side of the tool, perhaps something like: SELECT '<result_set>'|| '<type>'||op_identifier||'</type>'|| '<no_opens>'||no_opens||'</no_opens>'|| ... repeat for the other columsn from sysxplain_resultsets... '</result_set>' FROM xxx.sysxplain_resultsets WHERE ... Would an approach like this work to generate the XML format of the query plan data?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I had a look on using XML operations in Derby more thoroughly and found out following important points:

          • In order to retrieve XML data using XMLSERIALIZE, we should first have a table column which is in XML data type.

          So is it possible to change SYSXPLAIN tables such that they store XML data of each row inserted, in a XML data type column?

          • When inserting XML data we have to insert some set of XML keywords, such as Document, Preserve Whitespace.

          eg: insert into t(a) values (XMLPARSE(DOCUMENT '<title>Derby</title>' PRESERVE WHITESPACE));

          • When using XMLSERIALIZE we should use 'as' sql key word,

          eg: select XMLSERIALIZE(a as CLOB) from t; //we can use varchar as well

          • Execution of XMLSERIALIZE statement will result a small XML fragment.

          eg: <title>Derby</title>

          I think this will be an easy way, if we can somehow add a XML data type columns to SYSXPLAIN tables.

          May be we can store these small xml fragments in another table with XML data type columns and retrieve the large XML document after the execution of the query. (Note: We may have to write those rows retrieved into a .xml file)

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi Bryan, I had a look on using XML operations in Derby more thoroughly and found out following important points: In order to retrieve XML data using XMLSERIALIZE, we should first have a table column which is in XML data type. So is it possible to change SYSXPLAIN tables such that they store XML data of each row inserted, in a XML data type column? When inserting XML data we have to insert some set of XML keywords, such as Document, Preserve Whitespace. eg: insert into t(a) values (XMLPARSE(DOCUMENT '<title>Derby</title>' PRESERVE WHITESPACE)); When using XMLSERIALIZE we should use 'as' sql key word, eg: select XMLSERIALIZE(a as CLOB) from t; //we can use varchar as well Execution of XMLSERIALIZE statement will result a small XML fragment. eg: <title>Derby</title> I think this will be an easy way, if we can somehow add a XML data type columns to SYSXPLAIN tables. May be we can store these small xml fragments in another table with XML data type columns and retrieve the large XML document after the execution of the query. (Note: We may have to write those rows retrieved into a .xml file) Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi! I think you are interpreting the XPLAIN table information accurately.
          You can find additional information about the tables and columns of the XPLAIN
          tables here:
          http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statements.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statement_timings.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultsets.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultset_timings.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_scan_props.html
          http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_sort_props.html

          > the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again

          Yes, this is expected behavior. To stop collecting XPLAIN information, call
          the SET_XPLAIN_SHCEMA system procedure again, with an empty argument:
          http://db.apache.org/derby/docs/dev/ref/rref_syscs_set_xplain_schema.html

          Show
          Bryan Pendleton added a comment - Hi! I think you are interpreting the XPLAIN table information accurately. You can find additional information about the tables and columns of the XPLAIN tables here: http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statements.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statement_timings.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultsets.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultset_timings.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_scan_props.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_sort_props.html > the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again Yes, this is expected behavior. To stop collecting XPLAIN information, call the SET_XPLAIN_SHCEMA system procedure again, with an empty argument: http://db.apache.org/derby/docs/dev/ref/rref_syscs_set_xplain_schema.html
          Hide
          Thiwanka Somasiri added a comment - - edited

          Hi Bryan,
          I just tried to figure out how the "sysxplain_resultsets" table behaves.I sent these SQL statements and checked the output.

          1. ij>select * from cities where country='Sri Lanka' order by city_name;
          2. ij> select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time;
          3. ij> select st.stmt_text, rs.op_identifier
          > from my_stats.sysxplain_statements st
          > join my_stats.sysxplain_resultsets rs
          > on st.stmt_id = rs.stmt_id;

          Then I sent the 3rd query again to the database and the output was :

          STMT_TEXT |OP_IDENTIFIER
          ---------------------------------------------------------------------------------------------------------------------------------------------------------------
          select * from cities where country='Sri Lanka' order by city_name |SORT
          select * from cities where country='Sri Lanka' order by city_name |TABLESCAN
          select stmt_text, xplain_time from MY_STATS.sysxplain_statements
          order by xplain_time |SORT
          select stmt_text, xplain_time from MY_STATS.sysxplain_statements
          order by xplain_time |TABLESCAN
          select st.stmt_text, rs.op_identifier
          from my_stats.sysxplain_statements st
          join my_stats.sysxplain_resultsets rs
          on st.stmt& |PROJECTION
          select st.stmt_text, rs.op_identifier
          from my_stats.sysxplain_statements st
          join my_stats.sysxplain_resultsets rs
          on st.stmt& |HASHJOIN
          select st.stmt_text, rs.op_identifier
          from my_stats.sysxplain_statements st
          join my_stats.sysxplain_resultsets rs
          on st.stmt& |TABLESCAN
          select st.stmt_text, rs.op_identifier
          from my_stats.sysxplain_statements st
          join my_stats.sysxplain_resultsets rs
          on st.stmt& |HASHSCAN

          8 rows selected
          ______________________________________________END OF THE OUTPUT__________________________________________________________

          (The third query was sent twice to convince you that the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again)

          This clearly figures out the what type of result sets are generated when running a SQL statement.Here the OP_IDENTIFIER indicates the result set type.

          Let's take the first query "select * from cities where country='Sri Lanka' order by city_name;". This has the result sets TABLESCAN and SORT. Since the first result set performs a scan on the table, the SCAN_RS_ID column in SYSXPLAIN_RESULTSETS can identify the particular row in SYSXPLAIN_SCAN statistics related to the scan behavior. Similarly, SORT statistics can also be retrieved.

          Hope your comments on this.

          Thanks.

          Show
          Thiwanka Somasiri added a comment - - edited Hi Bryan, I just tried to figure out how the "sysxplain_resultsets" table behaves.I sent these SQL statements and checked the output. 1. ij>select * from cities where country='Sri Lanka' order by city_name; 2. ij> select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time; 3. ij> select st.stmt_text, rs.op_identifier > from my_stats.sysxplain_statements st > join my_stats.sysxplain_resultsets rs > on st.stmt_id = rs.stmt_id; Then I sent the 3rd query again to the database and the output was : STMT_TEXT |OP_IDENTIFIER --------------------------------------------------------------------------------------------------------------------------------------------------------------- select * from cities where country='Sri Lanka' order by city_name |SORT select * from cities where country='Sri Lanka' order by city_name |TABLESCAN select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time |SORT select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time |TABLESCAN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |PROJECTION select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |HASHJOIN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |TABLESCAN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |HASHSCAN 8 rows selected ______________________________________________ END OF THE OUTPUT __________________________________________________________ (The third query was sent twice to convince you that the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again) This clearly figures out the what type of result sets are generated when running a SQL statement.Here the OP_IDENTIFIER indicates the result set type. Let's take the first query "select * from cities where country='Sri Lanka' order by city_name;". This has the result sets TABLESCAN and SORT. Since the first result set performs a scan on the table, the SCAN_RS_ID column in SYSXPLAIN_RESULTSETS can identify the particular row in SYSXPLAIN_SCAN statistics related to the scan behavior. Similarly, SORT statistics can also be retrieved. Hope your comments on this. Thanks.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Here I quote from Derby developer's guide:

          XML data types and operators

          Derby supports the XML data type and a set of operators that work with the XML data
          type, but does not provide JDBC support for the XML data type. The XML data type and
          operators are based on a small subset of the SQL/XML specification.
          The XML data type and operators are defined only in the SQL layer.
          There is no JDBC-side support for XML data types. It is not possible to bind directly
          into an XML value or to retrieve an XML value directly from a result set. Instead, you
          must bind and retrieve the XML data as Java strings or character streams by explicitly
          specifying the appropriate XML operator as part of the SQL statements:

          • Use the XMLPARSE operator for binding data into XML values.
          • Use the XMLSERIALIZE operator to retrieve XML values from a result set.

          Additionally, there is no JDBC metadata support for the XML data type.
          The XML data type is not allowed in any of the clauses or operations that are described
          in the section on expressions on LONG data types in Derby and standards.
          For the XML operators to work properly, Derby requires that a JAXP parser, such as
          Apache Xerces, and Apache Xalan are included in the Java classpath. If either the parser
          or Xalan are missing from the classpath, Derby disallows any XML-related operations.
          Classpath and version issues

          Most Java Virtual Machines (JVMs) that are version 1.4 or later have a JAXP parser
          embedded in the JVM. If you are using one of these JVMs, you may not need to add any
          classes to your classpath. Some exceptions exist:

          • In most version 1.4.2 JVMs, the version of Xalan that comes with the JVM is
          not new enough, so you must override the version of Xalan in the JVM with a
          newer version by using the Endorsed Standards Override Mechanism described
          at http://java.sun.com/j2se/1.4.2/docs/guide/standards/. To use this mechanism,
          download and install a binary distribution of Xalan from Apache and set the system
          property java.endorsed.dirs to point to the Xalan installation directory.

          • After JVM version 1.4, Sun renamed the JAXP packages. Derby cannot find these
          renamed packages. If you are using a Sun JVM later than version 1.4, download
          and install a binary distribution of Xalan from Apache and place the xalan.jar
          file in your classpath. The xalan.jar file automatically puts into the classpath the
          other required jars that are in the same directory.

          ===================================================================

          As you can see this needed XALAN.jar inserted into the CLASSPATH.

          >Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl
          >we'd get a little "XML fragment", is that right? Or would get get an entire
          >XML document at that point?

          I think it's a XML fragment not a whole document. Will look for further details.

          I'll try to come up with a pseudo code in coming days, I think it's better if we can first come to a conclusion on using XMLSERIALIZE considering its usability, I'll read up more on this regard.

          Thanks

          Show
          Nirmal Fernando added a comment - Hi Bryan, Here I quote from Derby developer's guide: XML data types and operators Derby supports the XML data type and a set of operators that work with the XML data type, but does not provide JDBC support for the XML data type. The XML data type and operators are based on a small subset of the SQL/XML specification. The XML data type and operators are defined only in the SQL layer. There is no JDBC-side support for XML data types. It is not possible to bind directly into an XML value or to retrieve an XML value directly from a result set. Instead, you must bind and retrieve the XML data as Java strings or character streams by explicitly specifying the appropriate XML operator as part of the SQL statements: • Use the XMLPARSE operator for binding data into XML values. • Use the XMLSERIALIZE operator to retrieve XML values from a result set. Additionally, there is no JDBC metadata support for the XML data type. The XML data type is not allowed in any of the clauses or operations that are described in the section on expressions on LONG data types in Derby and standards. For the XML operators to work properly, Derby requires that a JAXP parser, such as Apache Xerces, and Apache Xalan are included in the Java classpath. If either the parser or Xalan are missing from the classpath, Derby disallows any XML-related operations. Classpath and version issues Most Java Virtual Machines (JVMs) that are version 1.4 or later have a JAXP parser embedded in the JVM. If you are using one of these JVMs, you may not need to add any classes to your classpath. Some exceptions exist: • In most version 1.4.2 JVMs, the version of Xalan that comes with the JVM is not new enough, so you must override the version of Xalan in the JVM with a newer version by using the Endorsed Standards Override Mechanism described at http://java.sun.com/j2se/1.4.2/docs/guide/standards/ . To use this mechanism, download and install a binary distribution of Xalan from Apache and set the system property java.endorsed.dirs to point to the Xalan installation directory. • After JVM version 1.4, Sun renamed the JAXP packages. Derby cannot find these renamed packages. If you are using a Sun JVM later than version 1.4, download and install a binary distribution of Xalan from Apache and place the xalan.jar file in your classpath. The xalan.jar file automatically puts into the classpath the other required jars that are in the same directory. =================================================================== As you can see this needed XALAN.jar inserted into the CLASSPATH. >Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl >we'd get a little "XML fragment", is that right? Or would get get an entire >XML document at that point? I think it's a XML fragment not a whole document. Will look for further details. I'll try to come up with a pseudo code in coming days, I think it's better if we can first come to a conclusion on using XMLSERIALIZE considering its usability, I'll read up more on this regard. Thanks
          Hide
          Bryan Pendleton added a comment -

          Thanks Nirmal! It seems like there is general consensus that this technique
          can be used to build a useful tool.

          I had originally anticipated that the data would be fetched from the XPLAIN
          tables as simple Java data types, then emitted as a single XML document by
          the tool, but I think your suggestion to use XMLSERIALIZE is intriguing.

          Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl
          we'd get a little "XML fragment", is that right? Or would get get an entire
          XML document at that point?

          What would be needed to "stitch together" these XML fragments, or collection
          of smaller XML documents, into a single larger XML document representing
          the entire query plan in its XML-formatted form?

          That is, in pseudocode-outline, what might that process look like in the tool code?

          Show
          Bryan Pendleton added a comment - Thanks Nirmal! It seems like there is general consensus that this technique can be used to build a useful tool. I had originally anticipated that the data would be fetched from the XPLAIN tables as simple Java data types, then emitted as a single XML document by the tool, but I think your suggestion to use XMLSERIALIZE is intriguing. Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl we'd get a little "XML fragment", is that right? Or would get get an entire XML document at that point? What would be needed to "stitch together" these XML fragments, or collection of smaller XML documents, into a single larger XML document representing the entire query plan in its XML-formatted form? That is, in pseudocode-outline, what might that process look like in the tool code?
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          *Derby XPLAIN tables are clearly described in http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html and I had gone through it weeks ago.

          *And as we discussed way before and as I included in my GSoC proposal project can be sub divided into two, which I planned to implement in the two phases of GSoC-2010. These are the sub parts:

          1) Build a tool (a small Java program using standard JDBC) which can read the
          query execution data for a SQL statement from the Derby XPLAIN tables and emit
          the data in XML tagged format. This involves specifying the XML schema for the
          data, writing the program to produce the data, and building tests for it.

          2) Build a tool to format the XML-formatted data into visual information in a
          browser, using an XSLT stylesheet. This involves conceptualizing the visual
          display, designing and writing the XSLT stylesheet, and building tests for it.

          *In the phase 1 I have to build a small tool to get values of XPLAIN tables and emit XML data, but because none of the JDBC-side support for SQL/XML is implemented in Derby, it is not possible to bind directly into an XML value or to retrieve an XML value directly from a result set using JDBC. Instead, we must bind and retrieve the XML data as Java strings or character streams by explicitly specifying the appropriate XML operators, XMLPARSE and XMLSERIALIZE, as part of our SQL queries.

          To retrieve XML values from a Derby database using JDBC, we can use the XMLSERIALIZE operator in the SQL query.

          eg: SELECT XMLSERIALIZE (stmt_text as CLOB) FROM myschema.sysxplain_statements

          for more information we can refer http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html

          **I did not put any comments here other than this after the GSoC application deadline is simply because I thought it will be unfair to other applicants since this is a some kind of a competition.

          I hope to contribute for this project in this summer and also for the evolution of Derby in future.

          Thanks

          Show
          Nirmal Fernando added a comment - Hi Bryan, *Derby XPLAIN tables are clearly described in http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html and I had gone through it weeks ago. *And as we discussed way before and as I included in my GSoC proposal project can be sub divided into two, which I planned to implement in the two phases of GSoC-2010. These are the sub parts: 1) Build a tool (a small Java program using standard JDBC) which can read the query execution data for a SQL statement from the Derby XPLAIN tables and emit the data in XML tagged format. This involves specifying the XML schema for the data, writing the program to produce the data, and building tests for it. 2) Build a tool to format the XML-formatted data into visual information in a browser, using an XSLT stylesheet. This involves conceptualizing the visual display, designing and writing the XSLT stylesheet, and building tests for it. *In the phase 1 I have to build a small tool to get values of XPLAIN tables and emit XML data, but because none of the JDBC-side support for SQL/XML is implemented in Derby, it is not possible to bind directly into an XML value or to retrieve an XML value directly from a result set using JDBC. Instead, we must bind and retrieve the XML data as Java strings or character streams by explicitly specifying the appropriate XML operators, XMLPARSE and XMLSERIALIZE, as part of our SQL queries. To retrieve XML values from a Derby database using JDBC, we can use the XMLSERIALIZE operator in the SQL query. eg: SELECT XMLSERIALIZE (stmt_text as CLOB) FROM myschema.sysxplain_statements for more information we can refer http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html **I did not put any comments here other than this after the GSoC application deadline is simply because I thought it will be unfair to other applicants since this is a some kind of a competition. I hope to contribute for this project in this summer and also for the evolution of Derby in future. Thanks
          Hide
          Thiwanka Somasiri added a comment -

          Hi Bryan,
          As I found,here are the tables that have all the statistics information for our need.
          1. SYSXPLAIN_STATEMENTS
          2. SYSXPLAIN_STATEMENT_TIMINGS
          3. SYSXPLAIN_RESULTSETS
          4. SYSXPLAIN_RESULTSET_TIMINGS
          5. SYSXPLAIN_SCAN_PROPS
          6. SYSXPLAIN_SORT_PROPS

          Earlier we had an issue on "how to filter the result set nodes(or else the overall tree structure) to show in a graphical view". The SYSXPLAIN_RESULTSETS table captures the information about each result set which is a part of the statement. So we can grab the information about the result set nodes through this table and the timing related statistics through the SYSXPLAIN_RESULTSET_TIMINGS table and so on.

          For example if a statement(query) is used to access a database, the result set information are stored in the SYSXPLAIN_RESULTSETS table.Single statement may have more than one result set node. In such a case we can join the table 1 and table 3 to get the whole set of result sets for the statement executed.

          Like this we have to traverse through all these tables to get the values that we need to view the execution plan in the browser window.
          We can do all these activities through a Java Program and save them through setter methods to a certain Java Object, which has attributes for all the statistics in the above tables.These statistics are same as statistics information in the logQueryPlan. Additionally, in the Java Program,we should have a method to iterate through the result set nodes that we grab from the SYSXPLAIN_RESULTSETS table.Then only we can generate the XML format which maps with each result set node.(The XML should be separated, so that it describes each result set node.)Then we can use XSLT to convert XML to XHTML,etc.

          The other important milestone is to generate the XML from the above mentioned Java Object which owns the statistics information.I have a suggestion to use open source XStream for this task.I am not 100% percent sure whether it is possible.At least we can try it out.

          Thank you.

          Show
          Thiwanka Somasiri added a comment - Hi Bryan, As I found,here are the tables that have all the statistics information for our need. 1. SYSXPLAIN_STATEMENTS 2. SYSXPLAIN_STATEMENT_TIMINGS 3. SYSXPLAIN_RESULTSETS 4. SYSXPLAIN_RESULTSET_TIMINGS 5. SYSXPLAIN_SCAN_PROPS 6. SYSXPLAIN_SORT_PROPS Earlier we had an issue on "how to filter the result set nodes(or else the overall tree structure) to show in a graphical view". The SYSXPLAIN_RESULTSETS table captures the information about each result set which is a part of the statement. So we can grab the information about the result set nodes through this table and the timing related statistics through the SYSXPLAIN_RESULTSET_TIMINGS table and so on. For example if a statement(query) is used to access a database, the result set information are stored in the SYSXPLAIN_RESULTSETS table.Single statement may have more than one result set node. In such a case we can join the table 1 and table 3 to get the whole set of result sets for the statement executed. Like this we have to traverse through all these tables to get the values that we need to view the execution plan in the browser window. We can do all these activities through a Java Program and save them through setter methods to a certain Java Object, which has attributes for all the statistics in the above tables.These statistics are same as statistics information in the logQueryPlan. Additionally, in the Java Program,we should have a method to iterate through the result set nodes that we grab from the SYSXPLAIN_RESULTSETS table.Then only we can generate the XML format which maps with each result set node.(The XML should be separated, so that it describes each result set node.)Then we can use XSLT to convert XML to XHTML,etc. The other important milestone is to generate the XML from the above mentioned Java Object which owns the statistics information.I have a suggestion to use open source XStream for this task.I am not 100% percent sure whether it is possible.At least we can try it out. Thank you.
          Hide
          Bryan Pendleton added a comment -

          > there are all the statistic variables that are stored in the tables.So we can
          > grab them from the source code.So it will be very easy to get the values
          > from the XPLAIN tables rather that retrieving them from the derby.log file.

          Yes, I think it would be much better to generate the XML form of the query
          plan statistics by querying the XPLAIN tables, than by trying to parse the
          text output of the logged query plan.

          +1 to your suggestion!

          Show
          Bryan Pendleton added a comment - > there are all the statistic variables that are stored in the tables.So we can > grab them from the source code.So it will be very easy to get the values > from the XPLAIN tables rather that retrieving them from the derby.log file. Yes, I think it would be much better to generate the XML form of the query plan statistics by querying the XPLAIN tables, than by trying to parse the text output of the logged query plan. +1 to your suggestion!
          Hide
          Thiwanka Somasiri added a comment -

          Hi Bryan,
          I read the Derby reference manual and Derby tuning materials to some extent to get the scope for this project and got to know that all the statistics that are logged into the logQueryPlan are stored in the XPLAIN tables as well.One problem we had earlier was to find out all the statistic data that can occur for a query.I studied the code for XPLAIN tables to some extent and found that there are all the statistic variables that are stored in the tables.So we can grab them from the source code.So it will be very easy to get the values from the XPLAIN tables rather that retrieving them from the derby.log file.

          For example see the following package to understand the above desiption :
          org.apache.derby.iapi.sql.execute.xplain

          FYI : Read the "XPLAIN style tables" in Derby Reference Manual for more information.

          Thank you.

          Show
          Thiwanka Somasiri added a comment - Hi Bryan, I read the Derby reference manual and Derby tuning materials to some extent to get the scope for this project and got to know that all the statistics that are logged into the logQueryPlan are stored in the XPLAIN tables as well.One problem we had earlier was to find out all the statistic data that can occur for a query.I studied the code for XPLAIN tables to some extent and found that there are all the statistic variables that are stored in the tables.So we can grab them from the source code.So it will be very easy to get the values from the XPLAIN tables rather that retrieving them from the derby.log file. For example see the following package to understand the above desiption : org.apache.derby.iapi.sql.execute.xplain FYI : Read the "XPLAIN style tables" in Derby Reference Manual for more information. Thank you.
          Hide
          Nirmal Fernando added a comment -

          Hi All,

          I have attached files of the prototype of Derby Query Plan Explainer which is done to show the feasibility of the project.

          You can simply view the screen shot or you can open the .xml file inside the "Source" folder from your web browser.

          Highly appreciate your comments !!

          Thanks !!

          Show
          Nirmal Fernando added a comment - Hi All, I have attached files of the prototype of Derby Query Plan Explainer which is done to show the feasibility of the project. You can simply view the screen shot or you can open the .xml file inside the "Source" folder from your web browser. Highly appreciate your comments !! Thanks !!
          Hide
          Thiwanka Somasiri added a comment -

          Hi Bryan,

          This is what I got when capturing the query plan for query "SELECT * FROM cities WHERE city_name='New Orleans'; you have presented.(In which the demo database supported with the Derby source code)

          Table Scan ResultSet for CITIES at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 1
          open time (milliseconds) = 1
          next time (milliseconds) = 2
          close time (milliseconds) = 0
          next time in milliseconds/row = 2

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=6
          Number of pages visited=2
          Number of rows qualified=1
          Number of rows visited=87
          Scan type=heap
          start position:
          null
          stop position:
          null
          qualifiers:
          Column[0][0] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 8.80
          optimizer estimated cost: 47.82

          And I tried the RUNTIMESTATISTICS attribute for the query and got the following output :

          ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
          0 rows inserted/updated/deleted
          ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
          0 rows inserted/updated/deleted
          ij> select * from cities where city_name='New Orleans';
          CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU&
          ------------------------------------------------------------------------------------------
          75 |New Orleans |United States |MSY |English |US

          1 row selected
          ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
          1
          --------------------------------------------------------------------------------------------------------------------------------
          Statement Name:
          null
          Statement Text:
          select * from cities where city_name='New Orleans'
          Parse Time: 1
          Bind Time: 2
          Optimize&

          1 row selected

          In here it clearly states the the time to generate the query tree from the SQL query in Parse Time and in Bind Time
          it indicates the time to traverse the query tree,etc.

          Looking for an idea to move forward from this stage from you.

          Thanks...!

          Show
          Thiwanka Somasiri added a comment - Hi Bryan, This is what I got when capturing the query plan for query "SELECT * FROM cities WHERE city_name='New Orleans'; you have presented.(In which the demo database supported with the Derby source code) Table Scan ResultSet for CITIES at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 1 open time (milliseconds) = 1 next time (milliseconds) = 2 close time (milliseconds) = 0 next time in milliseconds/row = 2 scan information: Bit set of columns fetched=All Number of columns fetched=6 Number of pages visited=2 Number of rows qualified=1 Number of rows visited=87 Scan type=heap start position: null stop position: null qualifiers: Column [0] [0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 8.80 optimizer estimated cost: 47.82 And I tried the RUNTIMESTATISTICS attribute for the query and got the following output : ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); 0 rows inserted/updated/deleted ij> select * from cities where city_name='New Orleans'; CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE |COU& ------------------------------------------------------------------------------------------ 75 |New Orleans |United States |MSY |English |US 1 row selected ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 -------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from cities where city_name='New Orleans' Parse Time: 1 Bind Time: 2 Optimize& 1 row selected In here it clearly states the the time to generate the query tree from the SQL query in Parse Time and in Bind Time it indicates the time to traverse the query tree,etc. Looking for an idea to move forward from this stage from you. Thanks...!
          Hide
          Nirmal Fernando added a comment -

          Rather than print them out, I used derby.language.logQueryPlan = true (to get the more accurate statistics), and following is the derby.log file:

          ----------------------------------------------------------------
          2010-04-06 16:04:06.112 GMT:
          Booting Derby version The Apache Software Foundation - Apache Derby - 10.6.0.0 alpha - (exported): instance a816c00e-0127-d3dd-438c-0000003f3a18
          on database directory C:\OtherNirmal\GSoC\Code\jars\sane\nirmal

          Database Class Loader started - derby.database.classpath=''
          2010-04-06 16:04:06.906 GMT Thread[main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE = nirmal), (DRDAID = null), Committing
          2010-04-06 16:04:06.907 GMT Thread[main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE = nirmal), (DRDAID = null), Rolling backan
          2010-04-06 16:04:06.909 GMT Thread[main,5,main] (XID = 1431), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing
          2010-04-06 16:04:08.753 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Begin compiling prepared statement: select * from myTable where i=1 :End prepared statement
          2010-04-06 16:04:09.493 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), End compiling prepared statement: select * from myTable where i=1 :End prepared statement
          2010-04-06 16:04:09.671 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Executing prepared statement: select * from myTable where i=1 :End prepared statement
          2010-04-06 16:04:09.796 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing
          2010-04-06 16:04:09.989 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), select * from myTable where i=1 ******* Table Scan ResultSet for MYTABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 3
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of pages visited=1
          Number of rows qualified=3
          Number of rows visited=7
          Scan type=heap
          start position:
          null
          stop position:
          null
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 1.30
          optimizer estimated cost: 29.56

          2010-04-06 16:04:09.990 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing
          2010-04-06 16:10:07.514 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Rolling back

          2010-04-06 16:10:07.537 GMT:
          Shutting down instance a816c00e-0127-d3dd-438c-0000003f3a18
          ----------------------------------------------------------------

          I'm looking to retrieve XPlainTable values now.

          Show
          Nirmal Fernando added a comment - Rather than print them out, I used derby.language.logQueryPlan = true (to get the more accurate statistics), and following is the derby.log file: ---------------------------------------------------------------- 2010-04-06 16:04:06.112 GMT: Booting Derby version The Apache Software Foundation - Apache Derby - 10.6.0.0 alpha - (exported): instance a816c00e-0127-d3dd-438c-0000003f3a18 on database directory C:\OtherNirmal\GSoC\Code\jars\sane\nirmal Database Class Loader started - derby.database.classpath='' 2010-04-06 16:04:06.906 GMT Thread [main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE = nirmal), (DRDAID = null), Committing 2010-04-06 16:04:06.907 GMT Thread [main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE = nirmal), (DRDAID = null), Rolling backan 2010-04-06 16:04:06.909 GMT Thread [main,5,main] (XID = 1431), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing 2010-04-06 16:04:08.753 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Begin compiling prepared statement: select * from myTable where i=1 :End prepared statement 2010-04-06 16:04:09.493 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), End compiling prepared statement: select * from myTable where i=1 :End prepared statement 2010-04-06 16:04:09.671 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Executing prepared statement: select * from myTable where i=1 :End prepared statement 2010-04-06 16:04:09.796 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing 2010-04-06 16:04:09.989 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), select * from myTable where i=1 ******* Table Scan ResultSet for MYTABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=7 Scan type=heap start position: null stop position: null qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 1.30 optimizer estimated cost: 29.56 2010-04-06 16:04:09.990 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Committing 2010-04-06 16:10:07.514 GMT Thread [main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE = nirmal), (DRDAID = null), Rolling back 2010-04-06 16:10:07.537 GMT: Shutting down instance a816c00e-0127-d3dd-438c-0000003f3a18 ---------------------------------------------------------------- I'm looking to retrieve XPlainTable values now.
          Hide
          Nirmal Fernando added a comment -

          Ya, absolutely, we might just need to add additional <xsl:value-of select =..> tags to display new information.

          I'm already referring the Tuning Guide, and will look at the XPlainTables too (I had looked at only run time statistics).

          Thanks Bryan for your comments, I'll post the prototype of a Query Explainer soon (query plan, manually written XML file, XSLT style sheet, and a screen-shot of the Explainer).

          Show
          Nirmal Fernando added a comment - Ya, absolutely, we might just need to add additional <xsl:value-of select =..> tags to display new information. I'm already referring the Tuning Guide, and will look at the XPlainTables too (I had looked at only run time statistics). Thanks Bryan for your comments, I'll post the prototype of a Query Explainer soon (query plan, manually written XML file, XSLT style sheet, and a screen-shot of the Explainer).
          Hide
          Bryan Pendleton added a comment -

          I think this is a great way to approach the problem. Your proposal
          seems fine to me; we can always add more information to the
          graphical view over time as we find uses for it. So long as all the
          information is retained in the XML file, such enhancements would
          just involve more logic in the XSLT processing, right?

          You should also have a close look at the information in the Tuning Guide,
          as it describes a lot of the query plan statistics data and how to
          interpret it. In particular, you will probably want to have a look at
          the XPLAIN tables feature that was added to the trunk, and will
          be in the 10.6 release. You can find this feature documented in the
          current "development" manuals, at:
          http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html

          It seems that you are making very good progress with this idea.

          Show
          Bryan Pendleton added a comment - I think this is a great way to approach the problem. Your proposal seems fine to me; we can always add more information to the graphical view over time as we find uses for it. So long as all the information is retained in the XML file, such enhancements would just involve more logic in the XSLT processing, right? You should also have a close look at the information in the Tuning Guide, as it describes a lot of the query plan statistics data and how to interpret it. In particular, you will probably want to have a look at the XPLAIN tables feature that was added to the trunk, and will be in the 10.6 release. You can find this feature documented in the current "development" manuals, at: http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html It seems that you are making very good progress with this idea.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I captured the "query execution plan" by printing out the returning String from "getStatementExecutionPlanText()" method in RunTimeStatisticsImpl.java class.

          Following are the details:

          ==============================================================

          ij> select * from myTable;
          I |J
          -----------------------
          1 |2
          1 |3
          1 |4
          2 |4
          2 |0
          3 |6
          3 |3

          7 rows selected

          ij> select * from myTable where i=1;

          I |J
          -----------------------
          1 |2
          1 |3
          1 |4

          3 rows selected

          This the query plan I printed (starts by @@@@.. and ends with @@@@..)

          @@@@@@@@Table Scan ResultSet for MYTABLE at read committed isolation level using
          instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 3
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of pages visited=1
          Number of rows qualified=3
          Number of rows visited=7
          Scan type=heap
          start position:
          null
          stop position:
          null
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 1.20
          optimizer estimated cost: 30.90
          @@@@@@@

          Statement Name:
          null
          Statement Text:
          select * from myTable where i=1
          Parse Time: 0
          Bind Time: 0
          Optimize Time: 0
          Generate T&

          ====================================================================

          So, in the graphical explainer for this query, there should be only one image (of the myTable) and under that there are following table of details:

          ====================================================================

          Table Scan on myTable
          _______________________________

          Query: select * from myTable where i=1
          Rows: 3
          Optimizer Estimated Cost: 30.90

          ====================================================================

          I like to get your comments on this, and also if you need any other information to be included in the graphical explainer please suggest them as well.
          (This was done to do a prototype of the graphical explainer as you suggested. I have already created a sample XML & XSLT file and looking forward to modify it and create a prototype)

          Thanks!!

          Show
          Nirmal Fernando added a comment - Hi Bryan, I captured the "query execution plan" by printing out the returning String from "getStatementExecutionPlanText()" method in RunTimeStatisticsImpl.java class. Following are the details: ============================================================== ij> select * from myTable; I |J ----------------------- 1 |2 1 |3 1 |4 2 |4 2 |0 3 |6 3 |3 7 rows selected ij> select * from myTable where i=1; I |J ----------------------- 1 |2 1 |3 1 |4 3 rows selected This the query plan I printed (starts by @@@@.. and ends with @@@@..) @@@@@@@@Table Scan ResultSet for MYTABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=7 Scan type=heap start position: null stop position: null qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 1.20 optimizer estimated cost: 30.90 @@@@@@@ Statement Name: null Statement Text: select * from myTable where i=1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate T& ==================================================================== So, in the graphical explainer for this query, there should be only one image (of the myTable) and under that there are following table of details: ==================================================================== Table Scan on myTable _______________________________ Query: select * from myTable where i=1 Rows: 3 Optimizer Estimated Cost: 30.90 ==================================================================== I like to get your comments on this, and also if you need any other information to be included in the graphical explainer please suggest them as well. (This was done to do a prototype of the graphical explainer as you suggested. I have already created a sample XML & XSLT file and looking forward to modify it and create a prototype) Thanks!!
          Hide
          Nirmal Fernando added a comment -

          Ok, Bryan I'll attach those soon !!

          I'll post my GSoC 2010 proposal regarding this improvement to get your comments, next week.

          Show
          Nirmal Fernando added a comment - Ok, Bryan I'll attach those soon !! I'll post my GSoC 2010 proposal regarding this improvement to get your comments, next week.
          Hide
          Bryan Pendleton added a comment -

          I think these sound like great ideas. I think it would be good to work on this
          incrementally, starting with a fairly simple stylesheet and then improving it.

          I think this approach could be prototyped to show its feasibility, by
          doing something along the lines of:

          1) Capture a fairly simple query plan, for example capture the query plan for:

          select from cities where city_name = 'New Orleans';

          2) By hand, convert that captured query plan to an XML format using your text editor

          3) Develop a simple XSLT stylesheet which can display some of the basic query plan information

          4) Attach the original query plan, the XML formatted plan, and the stylesheet, so
          that the community can see a sample of your ideas.

          Show
          Bryan Pendleton added a comment - I think these sound like great ideas. I think it would be good to work on this incrementally, starting with a fairly simple stylesheet and then improving it. I think this approach could be prototyped to show its feasibility, by doing something along the lines of: 1) Capture a fairly simple query plan, for example capture the query plan for: select from cities where city_name = 'New Orleans'; 2) By hand, convert that captured query plan to an XML format using your text editor 3) Develop a simple XSLT stylesheet which can display some of the basic query plan information 4) Attach the original query plan, the XML formatted plan, and the stylesheet, so that the community can see a sample of your ideas.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Ya, even I expected a positive answer.

          Displaying in a browser sounds interesting. We can create a XSLT style sheet document separately as it templates the graphical query plan.

          XSLT Style Sheet: I think we should first recognize the all possible instances that can be occur in a query such as table_name, sort, hash-join etc. Next we should write the template, as it iteratively looks for the instance occurred at that particular step and displays the image + details (execution time, resource usage), for all the steps that were followed when executing the query. Here image and details can be obtained through the XML file emitted by the query plan.

          Raw XML Document: I think this XML document should contain a predefined set of tags for all possible instances of a query as mentioned above and each tag (Child) should contain sub-children which will describe the image + details. And this will create at the run time in order of the execution procedure.

          Next we should link Raw XML document and the XSL style sheet. This step is fairly easy, we just need to provide the reference to the XSL style sheet inside the XML document.

          After that XSLT compliant browser will transform XML document generated by query plan in to a XHTML page, which will show the graphical query plan + details.

          Like to get your comments on this.

          Show
          Nirmal Fernando added a comment - Hi Bryan, Ya, even I expected a positive answer. Displaying in a browser sounds interesting. We can create a XSLT style sheet document separately as it templates the graphical query plan. XSLT Style Sheet: I think we should first recognize the all possible instances that can be occur in a query such as table_name, sort, hash-join etc. Next we should write the template, as it iteratively looks for the instance occurred at that particular step and displays the image + details (execution time, resource usage), for all the steps that were followed when executing the query. Here image and details can be obtained through the XML file emitted by the query plan. Raw XML Document: I think this XML document should contain a predefined set of tags for all possible instances of a query as mentioned above and each tag (Child) should contain sub-children which will describe the image + details. And this will create at the run time in order of the execution procedure. Next we should link Raw XML document and the XSL style sheet. This step is fairly easy, we just need to provide the reference to the XSL style sheet inside the XML document. After that XSLT compliant browser will transform XML document generated by query plan in to a XHTML page, which will show the graphical query plan + details. Like to get your comments on this.
          Hide
          Thiwanka Somasiri added a comment -

          Hi All,

          I was also studying about how Derby works and how also query plans can be
          displayed in an informative way. Thought of implementing a way to output
          query plans to XML and convert into HTML like thing will be doable going through
          Bryan's idea.As he says there are tools to convert XML to HTML, such as XMLFox
          or Stylus XSLT Editor tools,etc.

          Thank you.

          Show
          Thiwanka Somasiri added a comment - Hi All, I was also studying about how Derby works and how also query plans can be displayed in an informative way. Thought of implementing a way to output query plans to XML and convert into HTML like thing will be doable going through Bryan's idea.As he says there are tools to convert XML to HTML, such as XMLFox or Stylus XSLT Editor tools,etc. Thank you.
          Hide
          Bryan Pendleton added a comment -

          Thanks for asking them, too bad about the answer but at least we tried.

          I'm sure there are other ways to build such tools. For example, another idea I
          thought of was to change the Derby code so that the query plans are
          emitted as XML, and then to use XSLT or a similar tool to transform the
          "raw" query plan XML data into a more readable HTML web page that
          can be displayed by a browser.

          Show
          Bryan Pendleton added a comment - Thanks for asking them, too bad about the answer but at least we tried. I'm sure there are other ways to build such tools. For example, another idea I thought of was to change the Derby code so that the query plans are emitted as XML, and then to use XSLT or a similar tool to transform the "raw" query plan XML data into a more readable HTML web page that can be displayed by a browser.
          Hide
          Nirmal Fernando added a comment -

          Regarding Postgres and licensing:

          Graphical query explainer was implemented by PgAdmin III which is a tool uses PostgreSQL at the back end as their server (http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html).

          So, I contacted PgAdmin hackers devs-mailing list to check the possibility of and following is the reply I got !!

          -----------------------------------------------------------------------------------------------------------------------
          Le 27/03/2010 07:14, Nirmal Fernando a écrit :
          > [...]
          > Is it possible to integrate pgadmin tool with a different DMS rather than
          > Postgres? If it possible it would be great help if someone can send some
          > helpful links.
          >

          Nope it's not possible.


          Guillaume.
          http://www.postgresqlfr.org
          http://dalibo.com
          ----------------------------------------------------------------------------------------

          Show
          Nirmal Fernando added a comment - Regarding Postgres and licensing: Graphical query explainer was implemented by PgAdmin III which is a tool uses PostgreSQL at the back end as their server ( http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html ). So, I contacted PgAdmin hackers devs-mailing list to check the possibility of and following is the reply I got !! ----------------------------------------------------------------------------------------------------------------------- Le 27/03/2010 07:14, Nirmal Fernando a écrit : > [...] > Is it possible to integrate pgadmin tool with a different DMS rather than > Postgres? If it possible it would be great help if someone can send some > helpful links. > Nope it's not possible. – Guillaume. http://www.postgresqlfr.org http://dalibo.com ----------------------------------------------------------------------------------------
          Hide
          Nirmal Fernando added a comment -

          Ya, it will be a problem if we upload an image!! But, since the image creation can be done through MSWord, I have a bit tedious solution, but not sure whether you all prefer anything other than editing in the wiki itself.

          Show
          Nirmal Fernando added a comment - Ya, it will be a problem if we upload an image!! But, since the image creation can be done through MSWord, I have a bit tedious solution, but not sure whether you all prefer anything other than editing in the wiki itself.
          Hide
          Bryan Pendleton added a comment -

          Regarding Postgres and licensing: Is there any way that we can avoid
          incorporating the Postgres code into our code base, by instead simply
          arranging to emit output which can be used by the Postgres tools as is?

          That way, an interested user could:
          a) download Derby and use it
          b) separately and independently download the Postgres tools
          c) Following our instructions, emit Derby query plans in the
          Postgres-style format
          d) Following our instructions, load those query plans into the Postgres tool for viewing

          Then we'd just continue to be two independent projects, with no confusion
          over source code sharing.

          Show
          Bryan Pendleton added a comment - Regarding Postgres and licensing: Is there any way that we can avoid incorporating the Postgres code into our code base, by instead simply arranging to emit output which can be used by the Postgres tools as is? That way, an interested user could: a) download Derby and use it b) separately and independently download the Postgres tools c) Following our instructions, emit Derby query plans in the Postgres-style format d) Following our instructions, load those query plans into the Postgres tool for viewing Then we'd just continue to be two independent projects, with no confusion over source code sharing.
          Hide
          Bryan Pendleton added a comment -

          I think the use of graphics in the wiki to improve the presentation
          of the material would be helpful. I'm not sure what tools our current
          wiki technology has for describing graphics. Simply pasting in an
          image might work, but how would others in the community edit that
          image in the future?

          Show
          Bryan Pendleton added a comment - I think the use of graphics in the wiki to improve the presentation of the material would be helpful. I'm not sure what tools our current wiki technology has for describing graphics. Simply pasting in an image might work, but how would others in the community edit that image in the future?
          Hide
          Nirmal Fernando added a comment -

          Bryan, I like to create some graphical views of processes that are used by Derby. As I know Derby has no graphical views in its documentation or wikis that will help to understand the concepts in a higher level. So, is it a worth thing to do?

          Show
          Nirmal Fernando added a comment - Bryan, I like to create some graphical views of processes that are used by Derby. As I know Derby has no graphical views in its documentation or wikis that will help to understand the concepts in a higher level. So, is it a worth thing to do?
          Hide
          Bryan Pendleton added a comment -

          There is some information in the Derby wiki:
          http://wiki.apache.org/db-derby/LanguageSystem

          There is also lots of good information in the Derby docs, particularly here:
          http://db.apache.org/derby/docs/10.5/tuning/ctunoptimz39739.html

          All of these documents can always be improved, so it would be great if you
          can improve and expand on these sections as you study Derby. You can
          edit the wiki directly; you can submit doc patches for improvement just
          as you would code patches. Thanks!

          Show
          Bryan Pendleton added a comment - There is some information in the Derby wiki: http://wiki.apache.org/db-derby/LanguageSystem There is also lots of good information in the Derby docs, particularly here: http://db.apache.org/derby/docs/10.5/tuning/ctunoptimz39739.html All of these documents can always be improved, so it would be great if you can improve and expand on these sections as you study Derby. You can edit the wiki directly; you can submit doc patches for improvement just as you would code patches. Thanks!
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          http://anoncvs.postgresql.org/cvsweb.cgi/~checkout~/pgsql/src/tools/backend/index.html shows how PostgreSQL processes a query.

          I would like to know how derby processes a query, can you please mention a resource document that I can study on?

          Thanks

          Show
          Nirmal Fernando added a comment - Hi Bryan, http://anoncvs.postgresql.org/cvsweb.cgi/~checkout~/pgsql/src/tools/backend/index.html shows how PostgreSQL processes a query. I would like to know how derby processes a query, can you please mention a resource document that I can study on? Thanks
          Hide
          Nirmal Fernando added a comment - - edited

          Hi Mike,

          This is the license of PostgreSQL (http://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F), I attached a screen shot.

          Show
          Nirmal Fernando added a comment - - edited Hi Mike, This is the license of PostgreSQL ( http://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F ), I attached a screen shot.
          Hide
          Shelan Perera added a comment -

          Hi Mike,

          PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.Since it is BSD like PostgreSQL is compatible with apache license.

          Show
          Shelan Perera added a comment - Hi Mike, PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.Since it is BSD like PostgreSQL is compatible with apache license.
          Hide
          Mike Matrigali added a comment -

          does anyone know about the software licence for postgres, it should be compatible with the apache license before any of it is used to include software in derby.

          Show
          Mike Matrigali added a comment - does anyone know about the software licence for postgres, it should be compatible with the apache license before any of it is used to include software in derby.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          PostgreSQL is developed mostly in the C programming language. So, will it be possible to combine/use PostgreSQL tools with Derby? Don't we need to look in to a different Java Open Source tool or Can we get the idea from PostgreSQL and implement a Java tool?

          Show
          Nirmal Fernando added a comment - Hi Bryan, PostgreSQL is developed mostly in the C programming language. So, will it be possible to combine/use PostgreSQL tools with Derby? Don't we need to look in to a different Java Open Source tool or Can we get the idea from PostgreSQL and implement a Java tool?
          Hide
          Nirmal Fernando added a comment -

          Hi Eranda,

          Thanks for the link !!!

          Show
          Nirmal Fernando added a comment - Hi Eranda, Thanks for the link !!!
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Nirmal,
          Here is a link of a screen-cast which done by Tiago which shows how to set Derby environment. This might help you to build the trunk.
          http://www.vimeo.com/5376690

          Show
          Eranda Sooriyabandara added a comment - Hi Nirmal, Here is a link of a screen-cast which done by Tiago which shows how to set Derby environment. This might help you to build the trunk. http://www.vimeo.com/5376690
          Hide
          Nirmal Fernando added a comment -

          Ya, that would be possible.
          I'll read on Postgres to see how they perform this task.

          Show
          Nirmal Fernando added a comment - Ya, that would be possible. I'll read on Postgres to see how they perform this task.
          Hide
          Bryan Pendleton added a comment -

          Perhaps we can somehow share the Postgres tool? That is, if we can figure out
          what input it analyzes to produce the graphical display, perhaps we could
          write a tool which re-processes the Derby query plan output into the format
          that the Postgres tool reads, and then just use the Postgres tool to display
          Derby query plans.

          Show
          Bryan Pendleton added a comment - Perhaps we can somehow share the Postgres tool? That is, if we can figure out what input it analyzes to produce the graphical display, perhaps we could write a tool which re-processes the Derby query plan output into the format that the Postgres tool reads, and then just use the Postgres tool to display Derby query plans.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          Thanks for the link !! I will look in to it.

          I had read Postgres's graphical explain plan (http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html) today to get an idea about the tool to be developed.

          Derby doesn't has a GUI, so where can we display the visual explain of a query?

          Show
          Nirmal Fernando added a comment - Hi Bryan, Thanks for the link !! I will look in to it. I had read Postgres's graphical explain plan ( http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html ) today to get an idea about the tool to be developed. Derby doesn't has a GUI, so where can we display the visual explain of a query?
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal,
          You can find lots of development-related information here:
          http://wiki.apache.org/db-derby/DerbyDev

          You can find information about compiling and testing Derby from source code
          in the BIULDING.html file in the root of the source tree that you checked out.

          Show
          Bryan Pendleton added a comment - Hi Nirmal, You can find lots of development-related information here: http://wiki.apache.org/db-derby/DerbyDev You can find information about compiling and testing Derby from source code in the BIULDING.html file in the root of the source tree that you checked out.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan,

          I read http://db.apache.org/derby/docs/dev/getstart/ to get an idea about how Derby works, because it's the most appropriate start in my view.
          But if you can direct me to some guides on developing Derby, that would be really great !! I've downloaded, both bin & src of the final release of Derby and also I've checked out Derby Trunk through SVN repository, but not quite sure about how can I modify and compile/build the source code.

          Thanks !!

          Regards,
          Nirmal

          Show
          Nirmal Fernando added a comment - Hi Bryan, I read http://db.apache.org/derby/docs/dev/getstart/ to get an idea about how Derby works, because it's the most appropriate start in my view. But if you can direct me to some guides on developing Derby, that would be really great !! I've downloaded, both bin & src of the final release of Derby and also I've checked out Derby Trunk through SVN repository, but not quite sure about how can I modify and compile/build the source code. Thanks !! Regards, Nirmal

            People

            • Assignee:
              Nirmal Fernando
              Reporter:
              Bryan Pendleton
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development