Derby
  1. Derby
  2. DERBY-6022

Add a system procedure for (un)registering optional packages of Derby tools.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.10.1.1
    • Fix Version/s: 10.10.1.1
    • Component/s: SQL, Tools
    • Labels:
      None
    • Urgency:
      Normal

      Description

      Now that vararg routines have been added to Derby (see DERBY-3069), I would like to add a new vararg system procedure for registering and unregistering optional packages of Derby tools. For starters, these would be tools which aren't checked into the Derby codeline but are just attached to various JIRAs. These tools are:

      o DBMDWrapper (DERBY-3973 and DERBY-5967) - This tool creates functions and table functions for all of the DatabaseMetaData methods so that you can write complicated queries which join and filter JDBC metadata.

      o ForeignTableVTI (DERBY-4962) - This tool creates views against foreign databases so that you can bulk-import foreign data into Derby without indirecting through csv files.

      It also may be possible to use this approach to expose the log and data file reading tools attached to DERBY-5195 and DERBY-5201.

      The new system procedure would look like this:

      create procedure syscs_util.syscs_register_tool
      (
      toolName varchar( 32672 ),
      boolean register,
      optionalArgs varchar( 32672 ) ...
      )
      language java parameter style derby modifies sql data
      external name 'willFigureOutWhereToPutThis';

      The arguments would have these meanings:

      o toolName - A name specific to the tool.

      o register - True means "register the tool" and false means "unregister the tool".

      o optionalArgs - Each tool could have its own variable set of additional configuration parameters.

      By default, only the DBO could run this procedure. The DBO could grant execute permission to other users.

      The known tool names and their optional parameters would be documented in the Derby Reference Manual in the section on syscs_util.syscs_register_tool.

      I am thinking that we should put the optional tools themselves in derbytools.jar. We might want to document all of the optional tools in the Tools Guide, although I can see arguments for documenting some tools in the Admin Guide.

      I would appreciate other people's thoughts about this proposal.

      Thanks,
      -Rick

      1. derby-6022-07-aa-optimizerTracingWithLogFile.diff
        3 kB
        Rick Hillegas
      2. derby-6022-06-aa-optimizerTracing.diff
        4 kB
        Rick Hillegas
      3. derby-6022-05-aa-schemaNotCreatedYet.diff
        2 kB
        Rick Hillegas
      4. derby-6022-04-aa-customTool.diff
        19 kB
        Rick Hillegas
      5. derby-6022-03-aa-foreignDBviews.diff
        49 kB
        Rick Hillegas
      6. derby-6022-02-aa-dbmdWrapper.diff
        50 kB
        Rick Hillegas
      7. derby-6022-01-aa-registerToolProc.diff
        17 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-01-aa-registerToolProc.diff. This patch adds the syscs_util.syscs_register_tool() described above. The tools can be added in subsequent patches. I am running regression tests now.

          Touches the following files:

          ------------------

          M java/engine/org/apache/derby/iapi/sql/build.xml
          A java/engine/org/apache/derby/iapi/sql/dictionary/OptionalTool.java

          Adds the OptionalTool interface to be implemented by the tools. The interface has varargs methods for registering and unregistering tools.

          ------------------

          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          A java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
          M java/engine/org/apache/derby/catalog/build.xml
          M tools/jar/extraDBMSclasses.properties

          Wires the new syscs_register_tool() procedure into the data dictionary.

          ------------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          Adds an error message flagging unrecognized tool names.

          ------------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_10.java

          Adds tests to verify that the new procedure only exists after hard-upgrade to 10.10.

          Show
          Rick Hillegas added a comment - Attaching derby-6022-01-aa-registerToolProc.diff. This patch adds the syscs_util.syscs_register_tool() described above. The tools can be added in subsequent patches. I am running regression tests now. Touches the following files: ------------------ M java/engine/org/apache/derby/iapi/sql/build.xml A java/engine/org/apache/derby/iapi/sql/dictionary/OptionalTool.java Adds the OptionalTool interface to be implemented by the tools. The interface has varargs methods for registering and unregistering tools. ------------------ M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java A java/engine/org/apache/derby/catalog/Java5SystemProcedures.java M java/engine/org/apache/derby/catalog/build.xml M tools/jar/extraDBMSclasses.properties Wires the new syscs_register_tool() procedure into the data dictionary. ------------------ M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java Adds an error message flagging unrecognized tool names. ------------------ M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_10.java Adds tests to verify that the new procedure only exists after hard-upgrade to 10.10.
          Hide
          Rick Hillegas added a comment -

          The tests tripped one error. After syncing with the development trunk, the error went away. Committed derby-6022-01-aa-registerToolProc.diff at subversion revision 1427859. This was the error:

          There was 1 failure:
          1) testPingWithWrongHost(org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest)junit.framework.AssertionFailedError: Could not find expectedString:Unable to find host in output:<STDOUT>Wed Jan 02 07:26:59 PST 2013 : Could not connect to Derby Network Server on host nothere.invalid, port 1527: Operation timed out
          <END STDOUT>
          <STDERR><END STDERR>

          at org.apache.derbyTesting.junit.BaseTestCase.assertExecJavaCmdAsExpected(BaseTestCase.java:524)
          at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.assertFailedPing(NetworkServerControlClientCommandTest.java:148)
          at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.testPingWithWrongHost(NetworkServerControlClientCommandTest.java:113)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:117)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:424)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:441)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)

          FAILURES!!!
          Tests run: 14079, Failures: 1, Errors: 0

          Show
          Rick Hillegas added a comment - The tests tripped one error. After syncing with the development trunk, the error went away. Committed derby-6022-01-aa-registerToolProc.diff at subversion revision 1427859. This was the error: There was 1 failure: 1) testPingWithWrongHost(org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest)junit.framework.AssertionFailedError: Could not find expectedString:Unable to find host in output:<STDOUT>Wed Jan 02 07:26:59 PST 2013 : Could not connect to Derby Network Server on host nothere.invalid, port 1527: Operation timed out <END STDOUT> <STDERR><END STDERR> at org.apache.derbyTesting.junit.BaseTestCase.assertExecJavaCmdAsExpected(BaseTestCase.java:524) at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.assertFailedPing(NetworkServerControlClientCommandTest.java:148) at org.apache.derbyTesting.functionTests.tests.derbynet.NetworkServerControlClientCommandTest.testPingWithWrongHost(NetworkServerControlClientCommandTest.java:113) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:117) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:424) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:441) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) FAILURES!!! Tests run: 14079, Failures: 1, Errors: 0
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-02-aa-dbmdWrapper.diff. This patch adds the database metadata wrapper routines to derbytools.jar and makes it possible to register them via syscs_util.syscs_register_tool(). I am running regression tests now.

          To register the database metadata wrapper routines, issue the following command:

          call syscs_util.syscs_register_tool( 'dbmd', true );

          To unregister the wrapper routines, issue the following command:

          call syscs_util.syscs_register_tool( 'dbmd', false );

          Touches the following files:

          ------------------

          A java/tools/org/apache/derby/impl/tools/optional
          A java/tools/org/apache/derby/impl/tools/optional/DBMDWrapper.java
          M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java

          Adds DBMDWrapper as an OptionalTool.

          ------------------

          M java/build/org/apache/derbyBuild/classlister.java
          M java/tools/org/apache/derby/impl/tools/build.xml
          M tools/jar/tools.properties

          Adds logic to build DBMDWrapper and put it into
          derbytools.jar. Adds logic to NOT pull OptionalTool into
          derbytools.jar. It should live in only one jar file, viz., derby.jar.

          ------------------

          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
          M java/engine/org/apache/derby/impl/services/reflect/JarLoader.java

          Adds logic to make it possible to run DBMDWrapper methods even though they live in the Derby namespace.

          ------------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java

          Adds a simple test to verify that the wrapper methods can be registered, run, and unregistered.

          Show
          Rick Hillegas added a comment - Attaching derby-6022-02-aa-dbmdWrapper.diff. This patch adds the database metadata wrapper routines to derbytools.jar and makes it possible to register them via syscs_util.syscs_register_tool(). I am running regression tests now. To register the database metadata wrapper routines, issue the following command: call syscs_util.syscs_register_tool( 'dbmd', true ); To unregister the wrapper routines, issue the following command: call syscs_util.syscs_register_tool( 'dbmd', false ); Touches the following files: ------------------ A java/tools/org/apache/derby/impl/tools/optional A java/tools/org/apache/derby/impl/tools/optional/DBMDWrapper.java M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java Adds DBMDWrapper as an OptionalTool. ------------------ M java/build/org/apache/derbyBuild/classlister.java M java/tools/org/apache/derby/impl/tools/build.xml M tools/jar/tools.properties Adds logic to build DBMDWrapper and put it into derbytools.jar. Adds logic to NOT pull OptionalTool into derbytools.jar. It should live in only one jar file, viz., derby.jar. ------------------ M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java M java/engine/org/apache/derby/impl/services/reflect/JarLoader.java Adds logic to make it possible to run DBMDWrapper methods even though they live in the Derby namespace. ------------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java Adds a simple test to verify that the wrapper methods can be registered, run, and unregistered.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Rick. Seems useful to me. Now,

          So, the dbmdwrapper tool wouldn't live in derbytools.jar - as an exception? If our first use case for an optional tool doesn't fit there, maybe we will have to solve this ad hoc going forward?

          Recapping a bit, in the description you say "tools which wouldn't be checked in the to main code line", but if they are to be bundled with our jars, shouldn't they be? Sorry if I am a little confused here.. next question, if they are checked into the main code line and
          bundled in one of our jars, what would be the argument for not auto-registering them? Name space pollution?

          Show
          Dag H. Wanvik added a comment - Thanks, Rick. Seems useful to me. Now, So, the dbmdwrapper tool wouldn't live in derbytools.jar - as an exception? If our first use case for an optional tool doesn't fit there, maybe we will have to solve this ad hoc going forward? Recapping a bit, in the description you say "tools which wouldn't be checked in the to main code line", but if they are to be bundled with our jars, shouldn't they be? Sorry if I am a little confused here.. next question, if they are checked into the main code line and bundled in one of our jars, what would be the argument for not auto-registering them? Name space pollution?
          Hide
          Rick Hillegas added a comment -

          Hi Dag,

          Thanks for looking at this approach. Sorry if my description was a bit confusing:

          1) Note that DBMDWrapper does live in derbytools.jar. It's just the OptionalTools interface which lives in derby.jar. OptionalTools is an interface shared by engine and tools code.

          2) Note that this issue deals with tools which weren't previously checked into the main codeline but which will be checked into the codeline when this work is done. There are several reasons for not auto-registering these tools:

          a) That would bloat up the namespace as you note.

          b) That would add significant further time to Derby database creation.

          c) It isn't clear where to register the tools. In the case of the DBMDWrapper routines, they end up in the current schema of the user who registers them.

          d) For the next tool I'm going to tackle (ForeignTableVTI), the foreign database isn't even known when the Derby database is created, so there is no way to auto-register those routines.

          Hope this makes more sense now. Thanks.

          Show
          Rick Hillegas added a comment - Hi Dag, Thanks for looking at this approach. Sorry if my description was a bit confusing: 1) Note that DBMDWrapper does live in derbytools.jar. It's just the OptionalTools interface which lives in derby.jar. OptionalTools is an interface shared by engine and tools code. 2) Note that this issue deals with tools which weren't previously checked into the main codeline but which will be checked into the codeline when this work is done. There are several reasons for not auto-registering these tools: a) That would bloat up the namespace as you note. b) That would add significant further time to Derby database creation. c) It isn't clear where to register the tools. In the case of the DBMDWrapper routines, they end up in the current schema of the user who registers them. d) For the next tool I'm going to tackle (ForeignTableVTI), the foreign database isn't even known when the Derby database is created, so there is no way to auto-register those routines. Hope this makes more sense now. Thanks.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-6022-02-aa-dbmdWrapper.diff except for the Heisenbug tracked by https://issues.apache.org/jira/browse/DERBY-5941. I have added a little diagnostic logic to InvalidLDAPServerAuthenticationTest. That may help us understand the Heisenbug better. Attaching derby-6022-02-aa-dbmdWrapper.diff. Committed at subversion revision 1428506.

          Touches the following additional file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/InvalidLDAPServerAuthenticationTest.java

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-6022-02-aa-dbmdWrapper.diff except for the Heisenbug tracked by https://issues.apache.org/jira/browse/DERBY-5941 . I have added a little diagnostic logic to InvalidLDAPServerAuthenticationTest. That may help us understand the Heisenbug better. Attaching derby-6022-02-aa-dbmdWrapper.diff. Committed at subversion revision 1428506. Touches the following additional file: M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/InvalidLDAPServerAuthenticationTest.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-03-aa-foreignDBviews.diff. This patch adds an optional tool for siphoning data out of foreign databases without indirecting through csv dumps. This is a productization of the ForeignTableVTI attached to DERBY-4962. I am running tests now.

          The tool creates schemas, table functions, and wrapping views against all user tables in a foreign database. The foreign database could be another Derby database or any other RDBMS. An optional argument lets you prefix the local schema names with a string to distinguish them from the local schemas that you are importing into.

          Suppose that you have a foreign database with two schemas S1 and S2 which contain, respectively, tables T1 and T2 and U1 and U2. If you issue the following command...

          call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB', 'XYZ_' )

          ...then Derby will create the following objects:

          schema XYZ_S1
          table function XYZ_S1.T1, which reads S1.T1 from the foreign database
          table function XYZ_S1.T2, which reads S1.T2 from the foreign database
          view XYZ_S1.T1, which wraps the corresponding table function
          view XYZ_S1.T2, which wraps the corresponding table function

          schema XYZ_S2
          table function XYZ_S2.U1, which reads S2.U1 from the foreign database
          table function XYZ_S2.U2, which reads S2.U2 from the foreign database
          view XYZ_S2.U1, which wraps the corresponding table function
          view XYZ_S2.U2, which wraps the corresponding table function

          The views hide the ugly arguments to the table functions. You can then populate your local schema via the following SELECTs:

          insert into S1.T1 select * from XYZ_S1.T1
          insert into S1.T2 select * from XYZ_S1.T2
          insert into S2.U1 select * from XYZ_S2.U1
          insert into S2.U2 select * from XYZ_S2.U2

          When you are done migrating the data, you can drop the objects added by the tool:

          call syscs_util.syscs_register_tool( 'fdbv', false, 'connectionUrlToForeignDB', 'XYZ_' )

          The trailing schema prefix argument is optional and can be omitted if there is no overlap between the names of your local schemas and the names of the foreign schemas:

          call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB' )
          call syscs_util.syscs_register_tool( 'fdbv', false, 'connectionUrlToForeignDB' )

          Touches the following files:

          -------------

          A java/engine/org/apache/derby/vti/ForeignTableVTI.java
          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
          M java/engine/org/apache/derby/vti/build.xml
          M tools/javadoc/publishedapi.ant

          Adds to the public API a RestrictedVTI for reading a table from a
          foreign database.

          -------------

          M java/build/org/apache/derbyBuild/classlister.java
          A java/tools/org/apache/derby/impl/tools/optional/ForeignDBViews.java
          M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
          M tools/jar/extraDBMSclasses.properties
          M tools/jar/tools.properties
          M java/tools/org/apache/derby/loc/toolsmessages.properties

          Adds the new fdbv OptionalTool.

          -------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java

          Adds some tests of the new fdbv OptionalTool.

          Show
          Rick Hillegas added a comment - Attaching derby-6022-03-aa-foreignDBviews.diff. This patch adds an optional tool for siphoning data out of foreign databases without indirecting through csv dumps. This is a productization of the ForeignTableVTI attached to DERBY-4962 . I am running tests now. The tool creates schemas, table functions, and wrapping views against all user tables in a foreign database. The foreign database could be another Derby database or any other RDBMS. An optional argument lets you prefix the local schema names with a string to distinguish them from the local schemas that you are importing into. Suppose that you have a foreign database with two schemas S1 and S2 which contain, respectively, tables T1 and T2 and U1 and U2. If you issue the following command... call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB', 'XYZ_' ) ...then Derby will create the following objects: schema XYZ_S1 table function XYZ_S1.T1, which reads S1.T1 from the foreign database table function XYZ_S1.T2, which reads S1.T2 from the foreign database view XYZ_S1.T1, which wraps the corresponding table function view XYZ_S1.T2, which wraps the corresponding table function schema XYZ_S2 table function XYZ_S2.U1, which reads S2.U1 from the foreign database table function XYZ_S2.U2, which reads S2.U2 from the foreign database view XYZ_S2.U1, which wraps the corresponding table function view XYZ_S2.U2, which wraps the corresponding table function The views hide the ugly arguments to the table functions. You can then populate your local schema via the following SELECTs: insert into S1.T1 select * from XYZ_S1.T1 insert into S1.T2 select * from XYZ_S1.T2 insert into S2.U1 select * from XYZ_S2.U1 insert into S2.U2 select * from XYZ_S2.U2 When you are done migrating the data, you can drop the objects added by the tool: call syscs_util.syscs_register_tool( 'fdbv', false, 'connectionUrlToForeignDB', 'XYZ_' ) The trailing schema prefix argument is optional and can be omitted if there is no overlap between the names of your local schemas and the names of the foreign schemas: call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB' ) call syscs_util.syscs_register_tool( 'fdbv', false, 'connectionUrlToForeignDB' ) Touches the following files: ------------- A java/engine/org/apache/derby/vti/ForeignTableVTI.java M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java M java/engine/org/apache/derby/vti/build.xml M tools/javadoc/publishedapi.ant Adds to the public API a RestrictedVTI for reading a table from a foreign database. ------------- M java/build/org/apache/derbyBuild/classlister.java A java/tools/org/apache/derby/impl/tools/optional/ForeignDBViews.java M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java M tools/jar/extraDBMSclasses.properties M tools/jar/tools.properties M java/tools/org/apache/derby/loc/toolsmessages.properties Adds the new fdbv OptionalTool. ------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java Adds some tests of the new fdbv OptionalTool.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-6022-03-aa-foreignDBviews.diff.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-6022-03-aa-foreignDBviews.diff.
          Hide
          Rick Hillegas added a comment -

          Committed derby-6022-03-aa-foreignDBviews.diff at subversion revision 1431041.

          Show
          Rick Hillegas added a comment - Committed derby-6022-03-aa-foreignDBviews.diff at subversion revision 1431041.
          Hide
          Dag H. Wanvik added a comment -

          Looks good, Rick. I was thrown off at first by your example syntax:

          call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB', 'XYZ_' )

          until I realized that while 'fdbv' is a literal string, 'connectionUrlToForeignDB' is not; connectionUrlToForeignDB is a meta-symbol to be replaced with an actual URL, i.e. '<connectionUrlToForeignDB>'. Isn't the name 'fdbv' a bit on the terse side, though? I'd prefer some more self-explanatory.

          Show
          Dag H. Wanvik added a comment - Looks good, Rick. I was thrown off at first by your example syntax: call syscs_util.syscs_register_tool( 'fdbv', true, 'connectionUrlToForeignDB', 'XYZ_' ) until I realized that while 'fdbv' is a literal string, 'connectionUrlToForeignDB' is not; connectionUrlToForeignDB is a meta-symbol to be replaced with an actual URL, i.e. '<connectionUrlToForeignDB>'. Isn't the name 'fdbv' a bit on the terse side, though? I'd prefer some more self-explanatory.
          Hide
          Rick Hillegas added a comment -

          Thanks for that feedback, Dag. I'll change the tool names to something less cryptic in a future patch. How about the following:

          dbmd -> databaseMetaData

          fdbv -> foreignViews

          Show
          Rick Hillegas added a comment - Thanks for that feedback, Dag. I'll change the tool names to something less cryptic in a future patch. How about the following: dbmd -> databaseMetaData fdbv -> foreignViews
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-04-aa-customTool.diff. This patch adds a way to load experimental tool packages. The patch also changes the tool names so that they are more self-explanatory. I am running tests now.

          This will give us the ability to provide customers experimental OptionalTools to perform monitoring/diagnosis. If an experimental tool turns out to be useful, we can consider including it as builtin, named tool in a future release.

          An experimental tool is simply a class which implements OptionalTool. To load an experimental tool, you must specify the name of the class along with any additional string parameters it needs:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          true,
          'mydiags.MyExperimentalTool',
          'arg0',
          'arg1'
          );

          Unloading the tool is similar:

          call syscs_util.syscs_register_tool
          (
          'customTool',
          false,
          'mydiags.MyExperimentalTool',
          'arg0',
          'arg1'
          );

          This patch also changes the names of the other tools as described in the previous comment on this issue.

          I think that we should document the databaseMetaData and foreignViews tools. I will open a doc issue for that. I don't see a need to document customTool. We can simply provide load/unload instructions when we give a customer an experimental tool.

          Touches the following files:

          -----------------

          M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java

          Logic to support custom tools.

          -----------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolExample.java

          Tests.

          Show
          Rick Hillegas added a comment - Attaching derby-6022-04-aa-customTool.diff. This patch adds a way to load experimental tool packages. The patch also changes the tool names so that they are more self-explanatory. I am running tests now. This will give us the ability to provide customers experimental OptionalTools to perform monitoring/diagnosis. If an experimental tool turns out to be useful, we can consider including it as builtin, named tool in a future release. An experimental tool is simply a class which implements OptionalTool. To load an experimental tool, you must specify the name of the class along with any additional string parameters it needs: call syscs_util.syscs_register_tool ( 'customTool', true, 'mydiags.MyExperimentalTool', 'arg0', 'arg1' ); Unloading the tool is similar: call syscs_util.syscs_register_tool ( 'customTool', false, 'mydiags.MyExperimentalTool', 'arg0', 'arg1' ); This patch also changes the names of the other tools as described in the previous comment on this issue. I think that we should document the databaseMetaData and foreignViews tools. I will open a doc issue for that. I don't see a need to document customTool. We can simply provide load/unload instructions when we give a customer an experimental tool. Touches the following files: ----------------- M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java Logic to support custom tools. ----------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml M java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolExample.java Tests.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-6022-04-aa-customTool.diff. Committed at subversion revision 1431526.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-6022-04-aa-customTool.diff. Committed at subversion revision 1431526.
          Hide
          Dag H. Wanvik added a comment -

          > How about the following:
          >
          > dbmd -> databaseMetaData
          >
          > fdbv -> foreignViews

          A clear improvement. Btw, is the name case significant here? If so, we we
          want it to be? Thanks!

          Show
          Dag H. Wanvik added a comment - > How about the following: > > dbmd -> databaseMetaData > > fdbv -> foreignViews A clear improvement. Btw, is the name case significant here? If so, we we want it to be? Thanks!
          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. Yes, case is significant. I don't have strong religion on this point, but that just seemed more straightforward than the other alternatives which came to mind:

          o case is insignificant

          o SQL identifier casing and escaping rules

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks, Dag. Yes, case is significant. I don't have strong religion on this point, but that just seemed more straightforward than the other alternatives which came to mind: o case is insignificant o SQL identifier casing and escaping rules Thanks, -Rick
          Hide
          Kristian Waagan added a comment -

          Looks like code from this issue causes/triggers a test failure on some platforms. See
          http://download.java.net/javadesktop/derby/javadb-5571080-report/javadb-5571080-3575124-details.html .

          Low-level cause reported as:
          Caused by: java.lang.NullPointerException
          at org.apache.derby.impl.sql.compile.AggregateNode.resolveAggregate(AggregateNode.java:457)
          at org.apache.derby.impl.sql.compile.StaticMethodCallNode.resolveRoutine(StaticMethodCallNode.java:664)
          at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(StaticMethodCallNode.java:210)
          at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:237)
          at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:737)
          at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:831)
          at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(RowResultSetNode.java:261)
          at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227)
          at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140)
          at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:267)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:400)
          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:134)

          Show
          Kristian Waagan added a comment - Looks like code from this issue causes/triggers a test failure on some platforms. See http://download.java.net/javadesktop/derby/javadb-5571080-report/javadb-5571080-3575124-details.html . Low-level cause reported as: Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.AggregateNode.resolveAggregate(AggregateNode.java:457) at org.apache.derby.impl.sql.compile.StaticMethodCallNode.resolveRoutine(StaticMethodCallNode.java:664) at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(StaticMethodCallNode.java:210) at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:237) at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:737) at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:831) at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(RowResultSetNode.java:261) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140) at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:267) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:400) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:134)
          Hide
          Rick Hillegas added a comment -

          Thanks for spotting this, Kristian. I am able to reproduce this problem if I order the test cases to occur in the order which they ran on that platform.

          Show
          Rick Hillegas added a comment - Thanks for spotting this, Kristian. I am able to reproduce this problem if I order the test cases to occur in the order which they ran on that platform.
          Hide
          Rick Hillegas added a comment -

          The following script reproduces the error which Kristian identified:

          – turn on authentication and authorization
          connect 'jdbc:derby:memory:db0;create=true;user=test_dbo;password=test_dbopassword';
          call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' );
          connect 'jdbc:derby:memory:db0;shutdown=true;user=test_dbo;password=test_dbopassword';
          connect 'jdbc:derby:memory:db0;user=test_dbo;password=test_dbopassword';

          – dies on NPE
          values toString( 100 );

          Show
          Rick Hillegas added a comment - The following script reproduces the error which Kristian identified: – turn on authentication and authorization connect 'jdbc:derby:memory:db0;create=true;user=test_dbo;password=test_dbopassword'; call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' ); connect 'jdbc:derby:memory:db0;shutdown=true;user=test_dbo;password=test_dbopassword'; connect 'jdbc:derby:memory:db0;user=test_dbo;password=test_dbopassword'; – dies on NPE values toString( 100 );
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-05-aa-schemaNotCreatedYet.diff. This fixes an NPE when Derby tries to lookup a function/aggregate name in a schema which hasn't been created yet. I am running regression tests now.

          This fixes the problem Kristian identified. Depending on the order that the OptionalToolsTest cases run, we may run a query which tries to lookup a function name in a schema which hasn't been created yet. I have added a piece of defensive logic so that Derby concludes that the function can't exist if its schema hasn't been created yet. This fixes the NPE.

          Touches the following files:

          --------------

          M java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java

          The fix.

          --------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDAPermsTest.java

          Added a regression test case for the bug.

          Show
          Rick Hillegas added a comment - Attaching derby-6022-05-aa-schemaNotCreatedYet.diff. This fixes an NPE when Derby tries to lookup a function/aggregate name in a schema which hasn't been created yet. I am running regression tests now. This fixes the problem Kristian identified. Depending on the order that the OptionalToolsTest cases run, we may run a query which tries to lookup a function name in a schema which hasn't been created yet. I have added a piece of defensive logic so that Derby concludes that the function can't exist if its schema hasn't been created yet. This fixes the NPE. Touches the following files: -------------- M java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java The fix. -------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDAPermsTest.java Added a regression test case for the bug.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-6022-05-aa-schemaNotCreatedYet.diff. Committed at subversion revision 1434363.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-6022-05-aa-schemaNotCreatedYet.diff. Committed at subversion revision 1434363.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-06-aa-optimizerTracing.diff. This patch adds an optional tool for turning on optimizer tracing as discussed in the following email thread: http://apache-database.10148.n7.nabble.com/Is-there-a-way-to-dump-all-the-plans-considered-by-optimizer-into-log-td127769.html . Committed at subversion revision 1454537.

          Here's an example of how to use this tool:

          – turn on optimizer tracing
          call syscs_util.syscs_register_tool( 'optimizerTracing', true );

          select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSALIASES';

          – dump the optimizer trace and turn off optimizer tracing
          call syscs_util.syscs_register_tool( 'optimizerTracing', false );

          I don't have strong feelings about whether we should document this tool.

          Touches the following files:

          A java/tools/org/apache/derby/impl/tools/optional/OptimizerTracer.java
          M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
          M tools/jar/tools.properties

          Show
          Rick Hillegas added a comment - Attaching derby-6022-06-aa-optimizerTracing.diff. This patch adds an optional tool for turning on optimizer tracing as discussed in the following email thread: http://apache-database.10148.n7.nabble.com/Is-there-a-way-to-dump-all-the-plans-considered-by-optimizer-into-log-td127769.html . Committed at subversion revision 1454537. Here's an example of how to use this tool: – turn on optimizer tracing call syscs_util.syscs_register_tool( 'optimizerTracing', true ); select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSALIASES'; – dump the optimizer trace and turn off optimizer tracing call syscs_util.syscs_register_tool( 'optimizerTracing', false ); I don't have strong feelings about whether we should document this tool. Touches the following files: A java/tools/org/apache/derby/impl/tools/optional/OptimizerTracer.java M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java M tools/jar/tools.properties
          Hide
          Kim Haase added a comment -

          I've no objection to reopening DERBY-6042 and documenting the new tool, if people think it might be useful to users.

          Show
          Kim Haase added a comment - I've no objection to reopening DERBY-6042 and documenting the new tool, if people think it might be useful to users.
          Hide
          Dag H. Wanvik added a comment -

          I tried this and it worked for me. I'll keep using the other method though, because it lets me direct the output to a specified file (instead of std out). Maybe we could add a file parameter?

          Show
          Dag H. Wanvik added a comment - I tried this and it worked for me. I'll keep using the other method though, because it lets me direct the output to a specified file (instead of std out). Maybe we could add a file parameter?
          Hide
          Rick Hillegas added a comment -

          I think adding a file name parameter is a good idea. Thanks.

          Show
          Rick Hillegas added a comment - I think adding a file name parameter is a good idea. Thanks.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6022-07-aa-optimizerTracingWithLogFile.diff. This patch lets you specify a file to which the optimizer trace should be dumped. Committed at subversion revision 1456579.

          With this patch, you can dump the optimizer trace either to standard out or to a file:


          -- Turn on optimizer tracing, run one statement, turn off tracing, and print trace to standard out.

          call syscs_util.syscs_register_tool( 'optimizerTracing', true );
          select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSROLES';
          call syscs_util.syscs_register_tool( 'optimizerTracing', false );


          -- Turn on optimizer tracing, run one statement, turn off tracing,
          – and print trace to a file named z1.txt.

          call syscs_util.syscs_register_tool( 'optimizerTracing', true );
          select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSALIASES';
          call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z1.txt' );

          In adding this functionality, I tripped across some unexpected behaviors of optimizer tracing:

          1) The knowledge of whether tracing is on is maintained in two state variables, one in GenericLanguageConnectionContext and the other in Level2OptimizerImpl. This seems brittle to me.

          2) At one point it seems that someone intended to make it possible to print the trace in html. But that effort wasn't completed. I'd be happy to add an xml format to optimizer tracing if people are interested. You'd use this feature like this:

          call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' );

          3) I can't tell whether optimizer tracing is only meant to operate on a single statement or on multi-statement batches. The latter doesn't seem to work, however. Again, if people are interested, I'd be happy to add multi-statement support to optimizer tracing. This would be particularly useful with xml output since it would then be easy to isolate the individual statements in the output.

          Touches the following files:

          M java/tools/org/apache/derby/impl/tools/optional/OptimizerTracer.java
          M java/engine/org/apache/derby/iapi/db/OptimizerTrace.java

          Show
          Rick Hillegas added a comment - Attaching derby-6022-07-aa-optimizerTracingWithLogFile.diff. This patch lets you specify a file to which the optimizer trace should be dumped. Committed at subversion revision 1456579. With this patch, you can dump the optimizer trace either to standard out or to a file: – -- Turn on optimizer tracing, run one statement, turn off tracing, and print trace to standard out. – call syscs_util.syscs_register_tool( 'optimizerTracing', true ); select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSROLES'; call syscs_util.syscs_register_tool( 'optimizerTracing', false ); – -- Turn on optimizer tracing, run one statement, turn off tracing, – and print trace to a file named z1.txt. – call syscs_util.syscs_register_tool( 'optimizerTracing', true ); select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSALIASES'; call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z1.txt' ); In adding this functionality, I tripped across some unexpected behaviors of optimizer tracing: 1) The knowledge of whether tracing is on is maintained in two state variables, one in GenericLanguageConnectionContext and the other in Level2OptimizerImpl. This seems brittle to me. 2) At one point it seems that someone intended to make it possible to print the trace in html. But that effort wasn't completed. I'd be happy to add an xml format to optimizer tracing if people are interested. You'd use this feature like this: call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' ); 3) I can't tell whether optimizer tracing is only meant to operate on a single statement or on multi-statement batches. The latter doesn't seem to work, however. Again, if people are interested, I'd be happy to add multi-statement support to optimizer tracing. This would be particularly useful with xml output since it would then be easy to isolate the individual statements in the output. Touches the following files: M java/tools/org/apache/derby/impl/tools/optional/OptimizerTracer.java M java/engine/org/apache/derby/iapi/db/OptimizerTrace.java
          Hide
          Dag H. Wanvik added a comment -

          Adding xml output and support for batches sounds a good idea, Rick. +1
          A question here though: with XML the output gets more useful for tools, and as such, thinking of this as an API is more natural. But then we need consider/state the stability or lack thereof for such (internal) interfaces.

          Show
          Dag H. Wanvik added a comment - Adding xml output and support for batches sounds a good idea, Rick. +1 A question here though: with XML the output gets more useful for tools, and as such, thinking of this as an API is more natural. But then we need consider/state the stability or lack thereof for such (internal) interfaces.
          Hide
          Dag H. Wanvik added a comment -

          Btw, buddy tested the new "overload" for specifying the output file; worked fine for me, thanks.

          Show
          Dag H. Wanvik added a comment - Btw, buddy tested the new "overload" for specifying the output file; worked fine for me, thanks.

            People

            • Assignee:
              Unassigned
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development