Derby
  1. Derby
  2. DERBY-3809

Add a SHOW FUNCTIONS command to ij

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.6.1.0
    • Component/s: Tools
    • Labels:
      None
    • Issue & fix info:
      Newcomer

      Description

      Currently, the ij SHOW command can give you information on a number of schema objects, including procedures. It should be fairly easy to add SHOW FUNCTIONS as well. This request surfaced on the user list: http://www.nabble.com/How-can-i-see-my-self-defined-functions--td18773980.html#a18773980

      1. show-functions.diff
        4 kB
        Sylvain Leroux
      2. show-functions-2.diff
        7 kB
        Sylvain Leroux
      3. show-functions-3.diff
        9 kB
        Sylvain Leroux
      4. DERBY-3809.patch
        1 kB
        Sylvain Leroux

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Sylvain! I reformatted the comments so the lines didn't exceed 80 characters, and committed the patch to trunk with revision 880680.

          Show
          Knut Anders Hatlen added a comment - Thanks, Sylvain! I reformatted the comments so the lines didn't exceed 80 characters, and committed the patch to trunk with revision 880680.
          Hide
          Sylvain Leroux added a comment -

          Surprisingly, it was not that instantaneous!

          AbstractMethodError should be wrapped in a InvocationTargetException. But, according to http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6531596, with some Sun VM this is not the case.

          So, I wrote the patch defensively against this by catching "direct" AbstractMethodError as well as those wrapped inside an InvocationTargetException.

          While running against Postgre-8.3 I now have the expected behavior:
          ij> DRIVER 'org.postgresql.Driver';
          ij> CONNECT 'jdbc:postgresql:ij?user=ij&password=ijpw';
          ij> SHOW FUNCTIONS;
          ij> IJ ERROR: command not available for this driver: PostgreSQL Native Driver

          Pass ToolScripts without error.

          Show
          Sylvain Leroux added a comment - Surprisingly, it was not that instantaneous! AbstractMethodError should be wrapped in a InvocationTargetException. But, according to http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6531596 , with some Sun VM this is not the case. So, I wrote the patch defensively against this by catching "direct" AbstractMethodError as well as those wrapped inside an InvocationTargetException. While running against Postgre-8.3 I now have the expected behavior: ij> DRIVER 'org.postgresql.Driver'; ij> CONNECT 'jdbc:postgresql:ij?user=ij&password=ijpw'; ij> SHOW FUNCTIONS; ij> IJ ERROR: command not available for this driver: PostgreSQL Native Driver Pass ToolScripts without error.
          Hide
          Knut Anders Hatlen added a comment -

          +1. Catching AbstractMethodError and throwing notAvailableForDriver sounds like a reasonable approach.

          Show
          Knut Anders Hatlen added a comment - +1. Catching AbstractMethodError and throwing notAvailableForDriver sounds like a reasonable approach.
          Hide
          Sylvain Leroux added a comment -

          I re-open this issue due to to a slightly noisy behavior discovered when using SHOW FUNCTION against postgre-8.3:

          ij> SHOW FUNCTIONS;
          JAVA ERROR: java.lang.ClassCastException: java.lang.AbstractMethodError cannot be cast to java.sql.SQLException

          I think any JDBC driver build against JDBC3 but running on JDBC4 will exhibit the same behavior. The only thing I saw right now is to wrap AbstractMethodError in a "notAvailableForDriver" SQLException.

          Show
          Sylvain Leroux added a comment - I re-open this issue due to to a slightly noisy behavior discovered when using SHOW FUNCTION against postgre-8.3: ij> SHOW FUNCTIONS; JAVA ERROR: java.lang.ClassCastException: java.lang.AbstractMethodError cannot be cast to java.sql.SQLException I think any JDBC driver build against JDBC3 but running on JDBC4 will exhibit the same behavior. The only thing I saw right now is to wrap AbstractMethodError in a "notAvailableForDriver" SQLException.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks a lot for working on this, Sylvain!

          I've verified that the ICLA was recorded and committed the patch with revision 828950.

          I also added SHOW FUNCTIONS to the 10.6.1 feature list on the wiki: http://wiki.apache.org/db-derby/DerbyTenSixOneRelease

          Show
          Knut Anders Hatlen added a comment - Thanks a lot for working on this, Sylvain! I've verified that the ICLA was recorded and committed the patch with revision 828950. I also added SHOW FUNCTIONS to the 10.6.1 feature list on the wiki: http://wiki.apache.org/db-derby/DerbyTenSixOneRelease
          Hide
          Sylvain Leroux added a comment -

          Hi,

          My ICLA has finally been recorded! So I think all is OK now to commit the patch.

          Show
          Sylvain Leroux added a comment - Hi, My ICLA has finally been recorded! So I think all is OK now to commit the patch.
          Hide
          Sylvain Leroux added a comment -

          Hi,

          I didn't have much time these weeks to deal with that, but since I still don't appear in the contributors list, I send a mail to secretary@ in order to know what's going on.

          By the way, I would like to thank you again Knut for your help during the process of submitting this patch. I'm looking forward to work again on Derby, as soon I'll have more time - and that ICLA will be registered!

          Sylvain

          Show
          Sylvain Leroux added a comment - Hi, I didn't have much time these weeks to deal with that, but since I still don't appear in the contributors list, I send a mail to secretary@ in order to know what's going on. By the way, I would like to thank you again Knut for your help during the process of submitting this patch. I'm looking forward to work again on Derby, as soon I'll have more time - and that ICLA will be registered! Sylvain
          Hide
          Knut Anders Hatlen added a comment -

          Thanks! The patch looks good to me. I intend to commit it when your ICLA has been recorded here: http://people.apache.org/~jim/committers.html

          I've verified that it works fine with Java 1.4.2, both on with the embedded driver and the client driver, so the reflection does the trick on the JDBC 3.0 drivers. It also appears to work with MySQL's JDBC driver. The column widths looked fine too.

          Show
          Knut Anders Hatlen added a comment - Thanks! The patch looks good to me. I intend to commit it when your ICLA has been recorded here: http://people.apache.org/~jim/committers.html I've verified that it works fine with Java 1.4.2, both on with the embedded driver and the client driver, so the reflection does the trick on the JDBC 3.0 drivers. It also appears to work with MySQL's JDBC driver. The column widths looked fine too.
          Hide
          Sylvain Leroux added a comment -

          This time, I tried to narrow the result columns as much as possible to fit in 80 columns wide terminal. Giving as much space as possible to the REMARKS columns. This is better than the result of SHOW PROCEDURE I think, but class names are still truncated.

          Concerning InvocationTargetException of course you were right: I now re-throw the underlying exception.

          I add as test case for SHOW FUNCTIONS without an IN SCHEMA clause. Both JUnit and old tests pass well.

          Show
          Sylvain Leroux added a comment - This time, I tried to narrow the result columns as much as possible to fit in 80 columns wide terminal. Giving as much space as possible to the REMARKS columns. This is better than the result of SHOW PROCEDURE I think, but class names are still truncated. Concerning InvocationTargetException of course you were right: I now re-throw the underlying exception. I add as test case for SHOW FUNCTIONS without an IN SCHEMA clause. Both JUnit and old tests pass well.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for the new patch.

          The ij.sql test has not been converted to the JUnit harness yet, so it only runs as part of the old regression test suite (derbyall). To run the test, you could try one of these commands (you'll need jakarta-oro-2.0.8.jar in the classpath in addition to the jar files you need when you run the JUnit tests):

          • Single test: java org.apache.derbyTesting.functionTests.harness.RunTest tools/ij.sql
          • All tools tests: java org.apache.derbyTesting.functionTests.harness.RunSuite derbytools
          • All tests in the old harness: java org.apache.derbyTesting.functionTests.harness.RunSuite derbyall

          As to the column widths, I think SHOW PROCEDURES tried to make them fit in a standard terminal window (max 80 chars). But I kind of agree that having columns so narrow that they don't show the useful information doesn't make much sense.

          I'm wondering about the code that catches InvocationTargetException, if it should re-throw the underlying exception instead of throwing notAvailableForDriver(). I think the ITE will be thrown only if getFunctions() raises an SQLException, which indicates that some database failure occurred, and not that the driver doesn't support the call.

          For completeness, perhaps we should also have a test case for "SHOW FUNCTIONS" without the "IN <SCHEMA>" clause.

          Show
          Knut Anders Hatlen added a comment - Thanks for the new patch. The ij.sql test has not been converted to the JUnit harness yet, so it only runs as part of the old regression test suite (derbyall). To run the test, you could try one of these commands (you'll need jakarta-oro-2.0.8.jar in the classpath in addition to the jar files you need when you run the JUnit tests): Single test: java org.apache.derbyTesting.functionTests.harness.RunTest tools/ij.sql All tools tests: java org.apache.derbyTesting.functionTests.harness.RunSuite derbytools All tests in the old harness: java org.apache.derbyTesting.functionTests.harness.RunSuite derbyall As to the column widths, I think SHOW PROCEDURES tried to make them fit in a standard terminal window (max 80 chars). But I kind of agree that having columns so narrow that they don't show the useful information doesn't make much sense. I'm wondering about the code that catches InvocationTargetException, if it should re-throw the underlying exception instead of throwing notAvailableForDriver(). I think the ITE will be thrown only if getFunctions() raises an SQLException, which indicates that some database failure occurred, and not that the driver doesn't support the call. For completeness, perhaps we should also have a test case for "SHOW FUNCTIONS" without the "IN <SCHEMA>" clause.
          Hide
          Sylvain Leroux added a comment - - edited

          Here is a new version of the patch.

          As you suggested, I used Java reflection to call the getFunctions method. As a side effect, I no longer need to escape the schema name.

          Concerning unit tests, I'm not quite sure of myself: I add some lines to ij7.sql and ij7.out. It appears to do the job, but I don't know if this is enough.
          On that matter, after I have changed the help message in toolsmessages.properties, I noticed all tests in org.apache.derbyTesting.functionTests.tests.tools._Suite still pass. Even before updating ij.out (whose last test check this help message) ???

          Finally, note that I use wider columns for the result set than showProcedures. I think those are are far too narrow, especially for the "REMARKS" column. As you will notice in the following example, the Java class name are truncated. So the result is not that useful.
          ij> SHOW PROCEDURES IN SQLJ;
          PROCEDURE_SCHEM |PROCEDURE_NAME |REMARKS
          ------------------------------------------------------------------------
          SQLJ |INSTALL_JAR |org.apache.derby.ca&
          SQLJ |REMOVE_JAR |org.apache.derby.ca&
          SQLJ |REPLACE_JAR |org.apache.derby.ca&

          On the other hand, from the user point of view, I think that both showProcedures and showFunctions should produce a similar display. So I should normalize that one side or the other...

          Sylvain.

          Show
          Sylvain Leroux added a comment - - edited Here is a new version of the patch. As you suggested, I used Java reflection to call the getFunctions method. As a side effect, I no longer need to escape the schema name. Concerning unit tests, I'm not quite sure of myself: I add some lines to ij7.sql and ij7.out. It appears to do the job, but I don't know if this is enough. On that matter, after I have changed the help message in toolsmessages.properties, I noticed all tests in org.apache.derbyTesting.functionTests.tests.tools._Suite still pass. Even before updating ij.out (whose last test check this help message) ??? Finally, note that I use wider columns for the result set than showProcedures. I think those are are far too narrow, especially for the "REMARKS" column. As you will notice in the following example, the Java class name are truncated. So the result is not that useful. ij> SHOW PROCEDURES IN SQLJ; PROCEDURE_SCHEM |PROCEDURE_NAME |REMARKS ------------------------------------------------------------------------ SQLJ |INSTALL_JAR |org.apache.derby.ca& SQLJ |REMOVE_JAR |org.apache.derby.ca& SQLJ |REPLACE_JAR |org.apache.derby.ca& On the other hand, from the user point of view, I think that both showProcedures and showFunctions should produce a similar display. So I should normalize that one side or the other... Sylvain.
          Hide
          Knut Anders Hatlen added a comment -

          Escaping the schema name sounds like a good idea, since we may make the syntax more liberal later. There's a method called IdUtil.normalToDelimited() that does this in the embedded driver. Unfortunately, this code cannot be called from ij, since derby.jar is not guaranteed to be on the classpath, but you can look at the method to see how to escape the identifier. Using a prepared statement sounds simpler and cleaner, though, so I think I would have preferred that.

          One minor issue with the patch: Derby's code uses tab size 4. The patch uses spaces for indentation, which is fine, but to match the surrounding code, it should use four spaces for each indentation level, not eight.

          By the way, JDBC 4.0 has a method in DatabaseMetaData called getFunctions() which is similar to the getProcedures() method used by SHOW PROCEDURES. This method is implemented in Derby's JDBC 3.0 drivers too, so we may consider using that method instead of querying the system tables directly. The benefit of using getFunctions() is that it will be more portable and work against other databases that have implemented that meta-data call. Since ij is compiled against the JDBC 3.0 libraries, we will need to use reflection to call it. I think it would be something along these lines:

          DatabaseMetaData dbmd = theConnection.getMetaData();
          Method getFuncs;
          try

          { getFuncs = dbmd.getClass().getMethod("getFunctions", ...); }

          catch (NoSuchMethodException nsme)

          { throw new ijException.notAvailableForDriver(...); }

          ResultSet rs = (ResultSet) getFuncs.invoke(dbmd, ...);

          Show
          Knut Anders Hatlen added a comment - Escaping the schema name sounds like a good idea, since we may make the syntax more liberal later. There's a method called IdUtil.normalToDelimited() that does this in the embedded driver. Unfortunately, this code cannot be called from ij, since derby.jar is not guaranteed to be on the classpath, but you can look at the method to see how to escape the identifier. Using a prepared statement sounds simpler and cleaner, though, so I think I would have preferred that. One minor issue with the patch: Derby's code uses tab size 4. The patch uses spaces for indentation, which is fine, but to match the surrounding code, it should use four spaces for each indentation level, not eight. By the way, JDBC 4.0 has a method in DatabaseMetaData called getFunctions() which is similar to the getProcedures() method used by SHOW PROCEDURES. This method is implemented in Derby's JDBC 3.0 drivers too, so we may consider using that method instead of querying the system tables directly. The benefit of using getFunctions() is that it will be more portable and work against other databases that have implemented that meta-data call. Since ij is compiled against the JDBC 3.0 libraries, we will need to use reflection to call it. I think it would be something along these lines: DatabaseMetaData dbmd = theConnection.getMetaData(); Method getFuncs; try { getFuncs = dbmd.getClass().getMethod("getFunctions", ...); } catch (NoSuchMethodException nsme) { throw new ijException.notAvailableForDriver(...); } ResultSet rs = (ResultSet) getFuncs.invoke(dbmd, ...);
          Hide
          Sylvain Leroux added a comment -

          Thanks Knut,

          I send the ICLA as soon as possible.


          Here is a first work on this issue.

          I've changed the ij grammar to add the following commands:
          SHOW FUNCTIONS;
          SHOW FUNCTIONS IN <schema>

          This has required to add the new FUNCTIONS token.

          Concerning the Java method "ijResult showFunctions(String schema) throws SQLException", the request was borrowed from http://www.nabble.com/Re%3A-How-can-i-see-my-self-defined-functions--p18774587.html

          Despite the fact the parser only allows an identifier in the "IN" clause, I assume I should somehow escape the schema name. But I don't really know how to do that. Using a prepared statement seems to be a little bit excessive, isn't it?


          At this point, I'm having hard time running the test suites (most of the tests failed when using "ant junit-all". I will check my configuration in order to find why).

          Sylvain

          Show
          Sylvain Leroux added a comment - Thanks Knut, I send the ICLA as soon as possible. Here is a first work on this issue. I've changed the ij grammar to add the following commands: SHOW FUNCTIONS; SHOW FUNCTIONS IN <schema> This has required to add the new FUNCTIONS token. Concerning the Java method "ijResult showFunctions(String schema) throws SQLException", the request was borrowed from http://www.nabble.com/Re%3A-How-can-i-see-my-self-defined-functions--p18774587.html Despite the fact the parser only allows an identifier in the "IN" clause, I assume I should somehow escape the schema name. But I don't really know how to do that. Using a prepared statement seems to be a little bit excessive, isn't it? At this point, I'm having hard time running the test suites (most of the tests failed when using "ant junit-all". I will check my configuration in order to find why). Sylvain
          Hide
          Knut Anders Hatlen added a comment -

          Hi Sylvain,

          Thanks for volunteering to work on this issue. I have added you to the derby-developers group in JIRA, so you should now be able to assign this issue to yourself. The wiki contains some useful information for new contributors: http://wiki.apache.org/db-derby/DerbyDev. You'll probably need to fill out the ICLA form and send it to Apache for this contribution. Since it usually takes a while for the ICLA to be recorded, it's best if you do this as soon as possible. You'll find a link to the ICLA on the wiki.

          Show
          Knut Anders Hatlen added a comment - Hi Sylvain, Thanks for volunteering to work on this issue. I have added you to the derby-developers group in JIRA, so you should now be able to assign this issue to yourself. The wiki contains some useful information for new contributors: http://wiki.apache.org/db-derby/DerbyDev . You'll probably need to fill out the ICLA form and send it to Apache for this contribution. Since it usually takes a while for the ICLA to be recorded, it's best if you do this as soon as possible. You'll find a link to the ICLA on the wiki.
          Hide
          Sylvain Leroux added a comment -

          Hi there,

          I'd looked at enabled-roles-3.diff. The code doesn't seem to be too frightening. So, if this issue is still open, I'll be happy to work on it.

          Sylvain

          Show
          Sylvain Leroux added a comment - Hi there, I'd looked at enabled-roles-3.diff. The code doesn't seem to be too frightening. So, if this issue is still open, I'll be happy to work on it. Sylvain
          Hide
          Rick Hillegas added a comment -

          Hi Kevin,

          Thanks for your interest in this issue. A good place to start would be to look at the enabled-roles-3.diff patch which Dag attached to DERBY-3886. In that patch, Dag added 2 new ij SHOW commands for displaying information about ROLES. The patch steps you through all of the work which you need to do to add a new ij command, from adding your new command to the ij grammar in ij.jj all the way through to writing a regression test to verify that your command works. I would recommend starting in the ij.jj grammar and follow what Dag did for SHOW SETTABLE ROLES.

          Show
          Rick Hillegas added a comment - Hi Kevin, Thanks for your interest in this issue. A good place to start would be to look at the enabled-roles-3.diff patch which Dag attached to DERBY-3886 . In that patch, Dag added 2 new ij SHOW commands for displaying information about ROLES. The patch steps you through all of the work which you need to do to add a new ij command, from adding your new command to the ij grammar in ij.jj all the way through to writing a regression test to verify that your command works. I would recommend starting in the ij.jj grammar and follow what Dag did for SHOW SETTABLE ROLES.
          Hide
          Kevin S Mowry added a comment -

          Hello, I was planning on undertaking this task as part of a project in my DataBase System Implementation course and was looking for a few tips, possibly. Maybe just a reference location for where I should look for the commands available to the IJ tool. Thank you for any tips or guidance.

          Show
          Kevin S Mowry added a comment - Hello, I was planning on undertaking this task as part of a project in my DataBase System Implementation course and was looking for a few tips, possibly. Maybe just a reference location for where I should look for the commands available to the IJ tool. Thank you for any tips or guidance.
          Hide
          Junjie Peng added a comment -

          I'm sorry to not resolve this problem. I have do some hacking, but have not gotten what I expected. Considering I'm not free recently, I reassign the issue.

          Show
          Junjie Peng added a comment - I'm sorry to not resolve this problem. I have do some hacking, but have not gotten what I expected. Considering I'm not free recently, I reassign the issue.

            People

            • Assignee:
              Sylvain Leroux
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development